数据库中表的基本操作课件

第四章第四章 数据库中表的基本操作数据库中表的基本操作（表）考试数据库学生数据、试题数据、教师数据、试卷数据等要存储起来学生关系模型、试题关系模型、教师关系模型、试卷关系模型第四章 数据库中表的基本操作学生数据、试题数据、教师数据、试1第四章第四章 数据库中表的基本操作数据库中表的基本操作v4.1SQLServer表的概述v4.2数据库中表的创建v4.3表的修改与删除v4.4数据的添加与表的查看v4.5案例应用举例第四章 数据库中表的基本操作 4.1 SQL Server表2第四章第四章 数据库中表的基本操作数据库中表的基本操作v4.1SQLServer表的概述表的概述一、一、1、表的概念、表的概念 在在SQLServer数据库中，数据库中，表定表定义为列的集合，与列的集合，与Excel电子表格子表格相似，数据在表中是按行和列的格式相似，数据在表中是按行和列的格式组织排列的。每行代表唯一的一排列的。每行代表唯一的一条条记录，而每列代表，而每列代表记录中的一个域。中的一个域。学号姓名性别出生日期系部入学时间000101张三男1980计算机2000000201李伟男1981经 济 管理2000010101王丽女1982数学2001关系名属性（列）属性（列）和属性名关系模式关系元组主键（码）学生表学生表第四章 数据库中表的基本操作 4.1 SQL Server表3第四章第四章 数据库中表的基本操作数据库中表的基本操作v4.1SQLServer表的概述表的概述2、SQL Server表与关系模型的对应表与关系模型的对应关系模型关系模型SQLServer表表关系名关系名表名表名关系关系表表关系模式关系模式表的定义表的定义属性属性表的列或字段表的列或字段属性名属性名字段名或列名字段名或列名值值列值或字段值列值或字段值元组元组表的行或记录表的行或记录码码主键主键关系完整性关系完整性SQLServer的约束的约束第四章 数据库中表的基本操作 4.1 SQL Server表4第四章第四章 数据库中表的基本操作数据库中表的基本操作v4.1SQLServer表的概述3、表的设计过程中，、表的设计过程中，在在SQLServer创建表有如下限制：创建表有如下限制：1、每个数据库里最多有、每个数据库里最多有20亿个表。亿个表。2、每每个个表表上上最最多多可可以以创创建建一一个个聚聚集集索索引引和和249个个非非聚聚集集索引。索引。3、每个表最多可以配置、每个表最多可以配置1024个字段。个字段。4、每每条条记记录录最最多多可可以以占占8060B，但但不不包包括括text字字段段和和image字段。字段。第四章 数据库中表的基本操作 4.1 SQL Server表5第四章第四章 数据库中表的基本操作数据库中表的基本操作v4.1SQLServer表的概述表的概述二、基本的数据类型二、基本的数据类型在创建数据库中的表的时候，牵涉到表体结构，也就在创建数据库中的表的时候，牵涉到表体结构，也就是说牵涉到表中的字段数据的格式，所以为数据列选择数是说牵涉到表中的字段数据的格式，所以为数据列选择数据类型是一项非常重要的工作。在定义了表列的数据类型据类型是一项非常重要的工作。在定义了表列的数据类型以后，它将作为一项永久的特性被保留下来，而无法改变。以后，它将作为一项永久的特性被保留下来，而无法改变。所以精心选择表列的数据类型是建立性能良好的表格的前所以精心选择表列的数据类型是建立性能良好的表格的前提。提。数据类型包含数据类型包含8大类，及几种新的数据类型。大类，及几种新的数据类型。第四章 数据库中表的基本操作 4.1 SQL Server表6第四章第四章 数据库中表的基本操作数据库中表的基本操作v4.1SQLServer表的概述1、整型数据类型、整型数据类型能够存储精确的不带小数点的数字值的方法，可以分为五大类。能够存储精确的不带小数点的数字值的方法，可以分为五大类。bit取值范围只能是取值范围只能是0或或1，通常用作真假逻辑。，通常用作真假逻辑。bigint占用占用8个字节个字节int占用占用4个字节个字节占用字节数决定存储范围的大小占用字节数决定存储范围的大小smallint占用占用2个字节个字节tiny占用占用1个字节个字节第四章 数据库中表的基本操作 4.1 SQL Server表7第四章第四章 数据库中表的基本操作数据库中表的基本操作v掌握基本的数据类型掌握基本的数据类型2、数字、数字数据类型数据类型可以精确指定小数点两边的总位数可以精确指定小数点两边的总位数(精度精度)和小数点右面的位数和小数点右面的位数(刻度刻度).1=精度精度=380=刻度刻度=精度精度numeric唯一的区别就是在主键为唯一的区别就是在主键为indentity列，只能定义列，只能定义numeric类型类型decimaldecimal(7,3)9999.994/9999.995第四章 数据库中表的基本操作 掌握基本的数据类型8第四章第四章 数据库中表的基本操作数据库中表的基本操作v掌握基本的数据类型掌握基本的数据类型3、浮点数据类型、浮点数据类型能够存储范围非常大的数字，一般应用于科学计算或者统计计算能够存储范围非常大的数字，一般应用于科学计算或者统计计算等，不要求绝对精度的运算场合。等，不要求绝对精度的运算场合。real存储范围占用存储范围占用4个字节，精度可达个字节，精度可达7位。位。float(n)可以用可以用n来确定科学计数法中的数据尾数的位数，范围可来确定科学计数法中的数据尾数的位数，范围可达达53位。当位。当n的取值为的取值为124时，可达到的精度是时，可达到的精度是7位；当位；当n的取值是的取值是2553时，精度是时，精度是15位，用位，用8个字节来存储。个字节来存储。第四章 数据库中表的基本操作 掌握基本的数据类型9第四章第四章 数据库中表的基本操作数据库中表的基本操作v掌握基本的数据类型掌握基本的数据类型4、字符数据类型、字符数据类型char每个字符使用一个字节的存储空间，使用固定长度来存储字每个字符使用一个字节的存储空间，使用固定长度来存储字符，最长可容纳符，最长可容纳8000个。如果实际的字符串小于给定的最大长度，则个。如果实际的字符串小于给定的最大长度，则会以空格填充，如果实际的字符串大于给定的最大长度，则截断。会以空格填充，如果实际的字符串大于给定的最大长度，则截断。varchar可以存储到可以存储到8000个字符的变长字符型数据，存储为实际的字个字符的变长字符型数据，存储为实际的字符串的长度。符串的长度。text当要存储的字符的数据量非常庞大时，则使用当要存储的字符的数据量非常庞大时，则使用text数据类型。数据类型。他的最大长度可以达到他的最大长度可以达到2G左右。左右。第四章 数据库中表的基本操作 掌握基本的数据类型10第四章第四章 数据库中表的基本操作数据库中表的基本操作v掌握基本的数据类型掌握基本的数据类型5、二进制数据类型、二进制数据类型二进制数据在二进制数据在SQL中用十六进制进行表示，前边用中用十六进制进行表示，前边用0 x标记。标记。binary具有固定长度具有固定长度,最大长度可达最大长度可达8kb，当输入的实际数据小于定，当输入的实际数据小于定义的长度时，后面补义的长度时，后面补0。varbinary具有不固定的长度。具有不固定的长度。image用于存储字节数超过用于存储字节数超过8kb的数据，例如图象数据。的数据，例如图象数据。第四章 数据库中表的基本操作 掌握基本的数据类型11第四章第四章 数据库中表的基本操作数据库中表的基本操作v掌握基本的数据类型掌握基本的数据类型对对text、image数据类型：数据类型：1、不可使用、不可使用select命令中的命令中的orderby、groupby命令。命令。2、不可作为索引文件的关键字。、不可作为索引文件的关键字。3、也不可使用在、也不可使用在where语句中，但是对于语句中，但是对于text数据可以数据可以使用使用where语句中的模糊查询。语句中的模糊查询。第四章 数据库中表的基本操作 掌握基本的数据类型12第四章第四章 数据库中表的基本操作数据库中表的基本操作v掌握基本的数据类型掌握基本的数据类型6、货币数据类型、货币数据类型money占用占用8个字节，前面个字节，前面4个表示货币的整数，后面个表示货币的整数，后面4个表示小数。个表示小数。smallmoney占用占用4个字节。个字节。7、日期、日期/时间数据类型时间数据类型datetime表示范围表示范围1753年年1月月1日日9999年年12月月31日，占用日，占用8个字节可个字节可以精确到毫秒以精确到毫秒smalldatetime表示范围表示范围1900年年1月月1日日2079年年6月月6日，占用日，占用4个字节个字节可以精确到分。可以精确到分。第四章 数据库中表的基本操作 掌握基本的数据类型13第四章第四章 数据库中表的基本操作数据库中表的基本操作v掌握基本的数据类型掌握基本的数据类型8、双字节数据类型、双字节数据类型用于存储要用两个字节才能存储的双字节字符。用于存储要用两个字节才能存储的双字节字符。nchar(n)固定长度的双字节，可以存储固定长度的双字节，可以存储14000字符。字符。nvarchar(n)可变长度的双字节。可变长度的双字节。ntext(n)可存储大量的字符。可存储大量的字符。9、SQL-variant数据类型数据类型可以存储除可以存储除text、ntext、image以外的各种类型数据，也不可以自身以外的各种类型数据，也不可以自身转储。转储。第四章 数据库中表的基本操作 掌握基本的数据类型14第四章第四章 数据库中表的基本操作数据库中表的基本操作v掌握基本的数据类型掌握基本的数据类型10、table数据类型数据类型可以用来定义变量或者用于用户自定义函数的返回值，一般用来存储可以用来定义变量或者用于用户自定义函数的返回值，一般用来存储从数据库中提取出来得结果。从数据库中提取出来得结果。11、图象、文本数据使用时涉及到的几个函数、图象、文本数据使用时涉及到的几个函数12、用户自定义数据类型、用户自定义数据类型null建立语法：建立语法：sp_addtypetype_name,systemtype,notnull删除语法：删除语法：sp_droptypetype_namenonnull第四章 数据库中表的基本操作 掌握基本的数据类型15第四章第四章 数据库中表的基本操作数据库中表的基本操作v表的创建表的创建1.使用企业管理器创建表使用企业管理器创建表 的步骤：的步骤：1）定定义义表表结结构构：给给表表的的每每一一列列取取字字段段名名，并并确确定定每每一一列列的的数数据据类型、数据长度、列数据是否可以为空等。类型、数据长度、列数据是否可以为空等。2）设设置置约约束束：设设置置约约束束是是为为了了限限制制该该列列输输入入值值的的取取值值范范围围，以以保证输入数据的正确性和一致性。保证输入数据的正确性和一致性。3）添加数据：表结构建立完成之后，就可以向表中输入数据了。）添加数据：表结构建立完成之后，就可以向表中输入数据了。第四章 数据库中表的基本操作 表的创建16第四章第四章 数据库中表的基本操作数据库中表的基本操作v表的创建表的创建2.使用使用T-SQL语句创建表的基本语句创建表的基本语法：语法：CREATE TABLE database_name.owner .|owner.table_name (|PRIMARY KEY|UNIQUE ,.n ):=column_name data_type COLLATE DEFAULT constant_expression|IDENTITY (seed,increment)NOT FOR REPLICATION .n 第四章 数据库中表的基本操作 表的创建17第四章第四章 数据库中表的基本操作数据库中表的基本操作v表的创建表的创建语法注释：语法注释：l l database_name：指定创建的表所在的数据库指定创建的表所在的数据库 l lowner：指定表的所有者指定表的所有者 l ltable_name：指定新建表的名称指定新建表的名称 l lcolumn_definition：表中字段的定义表达式表中字段的定义表达式 l lcolumn_name：表中的字段名表中的字段名 l ldata_type：字段的数据类型字段的数据类型 l lseed：自动标识的开始值自动标识的开始值 l lincrement：自动编号的步长自动编号的步长 l lCREATETABLE：创建表语句的关键字创建表语句的关键字 l lPRIMARYKEY：主键约束主键约束 l lUNIQUE：字段唯一性约束字段唯一性约束 l lDEFAULT：字段的默认值约束字段的默认值约束 l lIDENTITY：自动编号标识自动编号标识第四章 数据库中表的基本操作 表的创建18第四章第四章 数据库中表的基本操作数据库中表的基本操作v表的创建表的创建例例:创创建建带带有有参参照照约约束束的的学学生生表表，学学生生表表的的表表结结构构定定义义如如下下表表所所示示。“学学号号”字字段段为为学学生生表表的的主主键键，“班班级级代代码码”字字段段为为学学生生表表的的外外键，它必须参照班级表中的键，它必须参照班级表中的“班级代码班级代码”字段的值。字段的值。字段名字段数据类型长度是否为空约束学号char12否主键姓名char8是性别char2是出生日期datetime8是入学时间datetime8是班级代码char9否外键系部代码char2是专业代码char2是第四章 数据库中表的基本操作 表的创建例:创建带有参照约束的19v表的创建表的创建创建创建“专业专业”表的代码如下：表的代码如下：USEStudentGOCREATETABLE专业专业(专业代码专业代码char(2)notnull,专业名称专业名称Varchar(20)notnull,系部代码系部代码char(2)notnull)GO创建创建“班级班级”表的代码如下：表的代码如下：USEStudentGOCREATETABLE班级班级(班级代码班级代码char(9)constraintpk_bjdmprimarykey,班级名称班级名称Varchar(20)notnull,专业代码专业代码char(2)notnull,系部代码系部代码char(2)notnull,备注备注Varchar(50)GO 表的创建20第四章第四章 数据库中表的基本操作数据库中表的基本操作v表的创建表的创建创建创建“学生学生”表的代码如下：表的代码如下：USESTUDENTGOCREATETABLE学生学生(学号学号char(12)CONSTRAINTpk_xhPRIMARYKEY,姓名姓名char(8),性别性别char(2),出生日期出生日期datetime,入学时间入学时间datetime,班级代码班级代码char(9)CONSTRAINTfk_bjdmREFERENCES班级班级(班班级代码级代码),专业代码专业代码char(2),系部代码系部代码char(2)GO第四章 数据库中表的基本操作 表的创建21第四章第四章 数据库中表的基本操作数据库中表的基本操作v表的修改表的修改使用企业管理器修改表使用企业管理器修改表 1)打开企业管理器，选择相应的数据库，展开表节点。打开企业管理器，选择相应的数据库，展开表节点。2)在在企企业业管管理理器器的的明明细细窗窗格格中中，右右击击要要修修改改的的表表，在在弹弹出出的的快快捷菜单中选择捷菜单中选择“设计表设计表”命令，打开表设计器。命令，打开表设计器。3)在在表表设设计计器器中中修修改改各各字字段段的的定定义义，如如字字段段名名、字字段段类类型型、字字段段长度、是否为空等。长度、是否为空等。4)添加删除字段。添加删除字段。第四章 数据库中表的基本操作 表的修改22第四章第四章 数据库中表的基本操作数据库中表的基本操作v表的修改表的修改语法：语法：ALTERTABLEtable_nameALTERCOLUMNcolumn_namenew_data_type(precision,scale)COLLATENULL|NOTNULL|ADD|DROPROWGUIDCOL|ADD,.n|DROPCONSTRAINTconstraint_name|COLUMNcolumn,.n语法：语法：:=column_namedata_typeDEFAULTconstant_expressionWITHVALUES|IDENTITY(seed,increment)NOTFORREPLICATIONROWGUIDCOLCOLLATE.n第四章 数据库中表的基本操作 表的修改语法：23第四章第四章 数据库中表的基本操作数据库中表的基本操作v表的修改表的修改语法注释：语法注释：l l ALTERCOLUMN：修改表列属性的子句修改表列属性的子句 l lADD：增加列或约束的子句增加列或约束的子句 l lDROPCOLUMN：删除表列的子句删除表列的子句 l ltable_name：需要修改表的表格名称需要修改表的表格名称 l lcolumn_name：希望增加的字段名希望增加的字段名 l ldata_type：需要增加的字段的数据类型名需要增加的字段的数据类型名 l lcollation_name：排序规则名排序规则名第四章 数据库中表的基本操作 表的修改24v例：表的修改例：表的修改在学生表中增加在学生表中增加“家庭住址家庭住址”：USEStudentGOALTERTABLE学生学生ADD家庭住址家庭住址Varchar(30)nullGO将将“家庭住址家庭住址”字段属性修改代码如下：字段属性修改代码如下：USEStudentGOALTERTABLE学生学生ALTERCOLUMN家庭住址家庭住址Varchar(50)nullGO 例：表的修改25第四章第四章 数据库中表的基本操作数据库中表的基本操作v表的删除表的删除使用企业管理器删除表使用企业管理器删除表操作步骤如下：操作步骤如下：1)打开企业管理器，选择相应的数据库并展开表节点。打开企业管理器，选择相应的数据库并展开表节点。2)在企业管理器的明细窗格中，右击要删除的表，在弹出的快捷在企业管理器的明细窗格中，右击要删除的表，在弹出的快捷菜单中选择菜单中选择“删除删除”命令，出现命令，出现 除去对象对话框时，单击除去对象对话框时，单击“全部除全部除去去”按钮即可删除表。按钮即可删除表。使用使用DROP TABLE语句删除表语句删除表USESTUDENTGODROPTABLE专业专业GO第四章 数据库中表的基本操作 表的删除26第四章第四章 数据库中表的基本操作数据库中表的基本操作v表的查看表的查看1)使用企业管理器查看使用企业管理器查看在在企企业业管管理理器器，右右击击需需要要查查看看结结构构的的表表，在在弹弹出出的的快快捷捷菜菜单单中中选选择择“属属性性”命命令令，打打开开表表属属性性对对话话框框，。单单击击“常常规规”和和“全全文文索索引引”标标签签查查看看表表信息。信息。2)使用系统存储过程使用系统存储过程sp_help查看查看语法格式：语法格式：EXECUTEsp_help表名表名例如，查看例如，查看“班级班级”表的结构，可以使用下列语句：表的结构，可以使用下列语句：EXECUTEsp_help班级班级 第四章 数据库中表的基本操作 表的查看27第四章第四章 数据库中表的基本操作数据库中表的基本操作v查看表中的数据查看表中的数据1）使用企业管理器查看表中的数据）使用企业管理器查看表中的数据在在企企业业管管理理器器中中，右右击击需需要要查查看看数数据据的的表表，弹弹出出快快捷捷菜菜单单，单单击击“打打开开表表返回所有的行返回所有的行”命令，打开查询设计器的结果窗口查看数据命令，打开查询设计器的结果窗口查看数据.2）使用查询分析器查看表中的数据）使用查询分析器查看表中的数据打打开开查查询询分分析析器器，在在“对对象象浏浏览览器器”中中选选择择相相应应的的数数据据库库，展展开开数数据据库库目目录录和和用用户户表表目目录录，右右击击要要查查看看的的表表，从从弹弹出出快快捷捷菜菜单单中中选选择择“打打开开”命命令令，就会打开表窗口，在此窗口中用户可以查看、修改和删除表中的数据。就会打开表窗口，在此窗口中用户可以查看、修改和删除表中的数据。第四章 数据库中表的基本操作 查看表中的数据28第四章第四章 数据库中表的基本操作数据库中表的基本操作v向表中的添加数据向表中的添加数据1）在在企企业业管管理理器器中中，展展开开相相关关数数据据库库节节点点，单单击击“表表”图图标标，在在明明细细窗窗格格中中右右击击需需要要添添加加数数据据的的表表，弹弹出出快快捷捷菜菜单单，单单击击“打打开开表表返返回所有的行回所有的行”命令，打开查询设计器的结果窗口命令，打开查询设计器的结果窗口.2）查查询询设设计计器器的的表表中中可可以以输输入入新新记记录录，也也可可以以修修改改和和删删除除已已经经输输入入的记录。的记录。3）使用）使用INSERTINTO语句添加数据语句添加数据第四章 数据库中表的基本操作 向表中的添加数据29第四章第四章 数据库中表的基本操作数据库中表的基本操作4、5案例应用举例案例应用举例usestudentgocreatetable系部系部(系部代码系部代码char(2)constraintpk_xbdmprimarykey,系部名称系部名称varchar(30)notnull,系主任系主任char(8)go第四章 数据库中表的基本操作30第四章第四章 数据库中表的基本操作数据库中表的基本操作usestudentgocreatetable专业专业(专业代码专业代码char(4)constraintpk_zydmprimarykey,专业名称专业名称varchar(20)notnull,系部代码系部代码char(2)constraintfp_zyxbdmreferences系部系部(系部代码系部代码)go第四章 数据库中表的基本操作use student31第四章第四章 数据库中表的基本操作数据库中表的基本操作usestudentgocreatetable班级班级(班级代码班级代码char(9)constraintpk_bjdmprimarykey,班级名称班级名称varchar(20),专业代码专业代码char(4)constraintfp_bjzydmreferences专业专业(专业代码专业代码),系部代码系部代码char(2)constraintfp_bjxbdmreferences系部系部(系部代码系部代码),备注备注varchar(5)go第四章 数据库中表的基本操作use student32第四章第四章 数据库中表的基本操作数据库中表的基本操作USESTUDENTGOCREATETABLE学生学生(学号学号char(12)CONSTRAINTpk_xhPRIMARYKEY,姓名姓名char(8),性别性别char(2),出生日期出生日期datetime,入学时间入学时间datetime,班级代码班级代码char(9)CONSTRAINTfk_xsbjdmREFERENCES班级班级(班级代码班级代码),专业代码专业代码char(4)constraintfp_xszydmreferences专业专业(专业代码专业代码),系部代码系部代码char(2)constraintfp_xsxbdmreferences系部系部(系部代码系部代码)GO第四章 数据库中表的基本操作USE STUDENT33第四章第四章 数据库中表的基本操作数据库中表的基本操作USESTUDENTGOCREATETABLE课程课程(课程号课程号char(4)CONSTRAINTpk_kcPRIMARYKEY,课程名课程名char(20)notnull,备注备注varchar(50)GO第四章 数据库中表的基本操作USE STUDENT34第四章第四章 数据库中表的基本操作数据库中表的基本操作USESTUDENTGOCREATETABLE教师教师(教师编号教师编号char(12)CONSTRAINTpk_jsbhPRIMARYKEY,姓名姓名char(8)notnull,性别性别char(2),出生日期出生日期datetime,学历学历char(10),职务职务char(10),职称职称char(10),系部代码系部代码char(2)constraintfp_jsxbdmreferences系部系部(系部代码系部代码),专业专业char(2),备注备注varchar(50)GO第四章 数据库中表的基本操作USE STUDENT35第四章第四章 数据库中表的基本操作数据库中表的基本操作USESTUDENTGOCREATETABLE教学计划教学计划(课程号课程号char(4)CONSTRAINTpk_jxjhkchreferences课程课程(课程号课程号),专业代码专业代码char(4)constraintpk_jxjhzydmreferences专业专业(专业代码专业代码),专业学级专业学级char(4)notnull,课程类型课程类型char(8),开学学期开学学期tinyint,学分学分tinyint,起始周起始周tinyint,结束周结束周tinyint,教材编号教材编号char(6),备注备注char(20)GO第四章 数据库中表的基本操作USE STUDENT36第四章第四章 数据库中表的基本操作数据库中表的基本操作USESTUDENTGOCREATETABLE教师任课教师任课(教师编号教师编号char(12)CONSTRAINTfk_jsrkjsbhreferences教师教师(教师编号教师编号),课程号课程号char(4)CONSTRAINTpk_jsrkkchreferences课程课程(课程号课程号),专业代码专业代码char(4),专业学级专业学级char(4),学年学年char(4),学期学期tinyint,学生数学生数smallint,学时数学时数smallint,酬金酬金smallint,起始周起始周tinyint,结束周结束周tinyint,)GO第四章 数据库中表的基本操作USE STUDENT37第四章第四章 数据库中表的基本操作数据库中表的基本操作USESTUDENTGOCREATETABLE课程注册课程注册(注册号注册号bigintidentity(010000000,1)notforreplicationconstraintpk_zchprimarykey,学号学号char(12)constraintfk_kczcxhreferences学生学生(学号学号),课程号课程号char(4)CONSTRAINTfk_kczckchreferences课程课程(课程号课程号),教师编号教师编号char(12)CONSTRAINTfk_kczcjsbhreferences教师教师(教师编号教师编号),专业代码专业代码char(4)constraintfk_kczczydmreferences专业专业(专业代码专业代码),专业学级专业学级char(4),选课类型选课类型char(4),学年学年char(4),学期学期tinyint,收费否收费否bit,注册注册bit,成绩成绩tinyint,学分学分tinyint)GO第四章 数据库中表的基本操作USE STUDENT38