第4章SQLServer数据表的管理

1、第第4章章 SQL Server数据表的管理数据表的管理 41 创建表创建表 42 表的管理和维护表的管理和维护 43 表数据的操作表数据的操作Page 12021年11月23日星期二2 教学要求：通过本章学习，读者应掌握以教学要求：通过本章学习，读者应掌握以下内容：下内容： 创建数据库表的方法创建数据库表的方法 维护数据库表的方法维护数据库表的方法 对表数据进行操作的方法对表数据进行操作的方法Page 22021年11月23日星期二34.1 创创 建建 表表 4.1.1 表的设计表的设计 表是数据库的数据对象，是用于存储和操作数据的一种逻表是数据库的数据对象，是用于存储和操作数据的一种逻辑结

2、构，是一系列列的集合。表由表头和若干行数据构成辑结构，是一系列列的集合。表由表头和若干行数据构成 1创建表的步骤创建表的步骤 (1)定义表结构。给表的每一列取字段名，并确定每一列定义表结构。给表的每一列取字段名，并确定每一列的数据类型、数据长度、列数据是否可以为空等。的数据类型、数据长度、列数据是否可以为空等。 (2)设置约束。设置约束来限制该列输入值的取值范围，设置约束。设置约束来限制该列输入值的取值范围，以保证输入数据的正确性和一致性。主码约束体现实体完以保证输入数据的正确性和一致性。主码约束体现实体完整性，即主码各列不能为空且主码作为行的唯一标识，外整性，即主码各列不能为空且主码作为行的

3、唯一标识，外键约束体现参照完整性，默认值和规则等体现用户定义完键约束体现参照完整性，默认值和规则等体现用户定义完整性。整性。 (3)添加数据。表结构建立完成之后，应该向表中输入数添加数据。表结构建立完成之后，应该向表中输入数据。据。4 2 SQL Server创建表的限制创建表的限制 (1)每个数据库里最多有每个数据库里最多有20亿个表。亿个表。 (2)每个表上最多可以创建一个聚集索引，每个表上最多可以创建一个聚集索引，249个非聚集索引。个非聚集索引。 (3)每个表最多可以设置每个表最多可以设置1024个字段。个字段。 (4)每条记录最多占每条记录最多占8064B，但不包括，但不包括text

4、字段和字段和image字段。字段。5 4.1.2 数据类型数据类型 数据类型是指数据所代表信息的类型。数据类型是指数据所代表信息的类型。SQL Server中预定义了中预定义了24种数据类型，而种数据类型，而且也允许用户自己定义数据类型，如表且也允许用户自己定义数据类型，如表4.1所示。所示。P87Page 52021年11月23日星期二6 表是数据库中的重要组成部分，创建了数据库之表是数据库中的重要组成部分，创建了数据库之后就应创建表。创建数据表主要有两种方法，分后就应创建表。创建数据表主要有两种方法，分别是利用图形界面方式创建表和利用别是利用图形界面方式创建表和利用SQL命令方命令方式创建

5、表。式创建表。 4.1.3 使用使用SQL Server Management Studio创创建表建表Page 62021年11月23日星期二7Page 72021年11月23日星期二84.1.4 使用使用SQL命令方式创建表命令方式创建表 创建表的命令格式：创建表的命令格式：CREATE TABLE datebase_name.owner.table_name ( |column_name AS computed_column_expression|:=CONSTRAINT constrint_name|PRIMARY KEY|UNIQUE ,n)ON filegroup|DEFAULTT

6、EXTIMAGE_ONfilegroup|DEFAULT 各参数含义如下。各参数含义如下。 (1)database_name。指定新建表所置于的数据库名，若。指定新建表所置于的数据库名，若该名不指定就会置于当前数据库中。该名不指定就会置于当前数据库中。 (2)owner。指定数据库所有者名称，它必须是。指定数据库所有者名称，它必须是data_name所指定的数据库中现有的用户所指定的数据库中现有的用户ID，默认为当，默认为当前注册用户名。前注册用户名。Page 82021年11月23日星期二9 (3)table_name。指定新建表的名称，需在一个数据库中。指定新建表的名称，需在一个数据库中是

7、唯一的，且遵循是唯一的，且遵循T-SQL语言中的标识符规则，表名长度语言中的标识符规则，表名长度不能超过不能超过128个字符，对于临时表则表名长度不能超过个字符，对于临时表则表名长度不能超过116个字符。个字符。 (4)column_name。指定列的名称，在表内必须唯一。指定列的名称，在表内必须唯一。 (5)computed_column_expression。指定该计算列定义。指定该计算列定义的表达式。的表达式。 (6)ONfilegroup|DEFAULT。指定存储新建表的数据库。指定存储新建表的数据库文件组名称。如果使用文件组名称。如果使用DEFAULT或省略了或省略了ON子句，则新子

8、句，则新建的表会存储在数据库的默认文件组中。建的表会存储在数据库的默认文件组中。 (7)TEXTIMAGE_ON。指定。指定TEXT、NTEXT和和IMAGE列的列的数据存储的数据库文件组。若省略该子句，这些类型的数数据存储的数据库文件组。若省略该子句，这些类型的数据就和表一起存储在相同的文件组中。如果表中没有据就和表一起存储在相同的文件组中。如果表中没有TEXT、NTEXT和和IMAGE列，则可以省略列，则可以省略TEXTIMAGE_ON子句。子句。10 (8) 是某列的列定义，语法格式如是某列的列定义，语法格式如下：下： :=column_name data_type NULL|NOT N

9、ULL DEFAULT CONSTRAINT_EXPRESSION |IDENTITY(seed,increment)NOT FOR REPLICATION ROWGUIDCOLCOLLATE ,n 11data_type。指定列的数据类型，可以是系统数据类型或者用户自定义。指定列的数据类型，可以是系统数据类型或者用户自定义数据类型。数据类型。NULL|NOT NULL。说明列值是否允许为。说明列值是否允许为NULL。在。在SQL Server中，中，NULL既不是既不是0也不是空格，它意味着用户还没有为列输入数据或是明确地也不是空格，它意味着用户还没有为列输入数据或是明确地插入了插入了NUL

10、L。IDENTITY。指定列为一个标识列，一个表中只能有一个。指定列为一个标识列，一个表中只能有一个IDENTITY标识标识列，且数据类型必须为整数类型。当用户向数据表中插入新数据行时，系列，且数据类型必须为整数类型。当用户向数据表中插入新数据行时，系统将为该列赋予唯一的、递增的值。统将为该列赋予唯一的、递增的值。IDENTITY列通常与列通常与PRIMARY KEY约束一起使用，该列值不能由用户更新，不能为空值，也不能绑定默认值约束一起使用，该列值不能由用户更新，不能为空值，也不能绑定默认值和和DEFAULT约束。约束。seed。指定。指定IDENTITY列初始值，默认值为列初始值，默认值为

11、1。incrment。指定。指定IDENTITY列的列值增量，默认值为列的列值增量，默认值为1。NOT FOR REPLICATION。指定列的。指定列的IDENTITY属性，在把从其他表中属性，在把从其他表中复制的数据插入到表中时不发生作用。复制的数据插入到表中时不发生作用。ROWGUIDCOL。指定列为全局唯一标识符列。此列的数据类型必须为。指定列为全局唯一标识符列。此列的数据类型必须为UNIQUEIDENTIFIER类型，一个表中数据类型为类型，一个表中数据类型为UNIQUEIDENTIFIER的的列中只能有一个列被定义为列中只能有一个列被定义为ROWGUIDCOL列。列。ROWGUID

12、COL属性不会属性不会使列值具有唯一性，也不会自动生成一个新的数值给插入的行。使列值具有唯一性，也不会自动生成一个新的数值给插入的行。12 列约束的定义格式为：列约束的定义格式为： (1) CONSTRAINT 约束名约束名 PRIMARY KEY (列名列名)：指：指定列为主键。定列为主键。 (2) CONSTRAINT 约束名约束名 UNIQUE KEY (列名列名)：指定：指定列为唯一键。列为唯一键。 (3) CONSTRAINT 约束名约束名 FOREIGN KEY (外键列外键列) REFERENCES 引用表名引用表名(引用列引用列)：指定列为外键，并说：指定列为外键，并说明引用的

13、源表及在该表中所用的列名。明引用的源表及在该表中所用的列名。 (4) CONSTRAINT 约束名约束名 CHECK (检查表达式检查表达式)：指定：指定列的检查约束。列的检查约束。 (5) CONSTRAINT 约束名约束名 DEFAULT 默认值：指定列的默认值：指定列的默认值。默认值。13 【例【例4.1】 使用命令方式在使用命令方式在jxgl数据库中创建数据库中创建“课程课程”表表，并将课程号定义为主键，并将课程号定义为主键。 USE jxglGOCREATE TABLE 课程课程(课程号课程号 char(4) not null PRIMARY KEY,课程名课程名 char(16)

14、not null,学分学分 smallint,学时数学时数 smallint)GOPage 132021年11月23日星期二14 【例【例4.2】 使用命令方式在使用命令方式在jxgl数据库中创建数据库中创建“学生成绩学生成绩”表，并将学号和课程号定义为主键表，并将学号和课程号定义为主键。 USE jxglGOCREATE TABLE 学生成绩学生成绩(学号学号 char(10) not null,课程号课程号 char(4) not null,成绩成绩 numericPRIMARY KEY(学号学号,课程号课程号)GOPage 142021年11月23日星期二1542 表管理和维护表管理和维

15、护 当表创建完成之后，我们可以查看、修改或删除当表创建完成之后，我们可以查看、修改或删除已经存在的表。例如可以查看表的定义信息，修已经存在的表。例如可以查看表的定义信息，修改表的结构、内容，以及与其他表的依赖关系等。改表的结构、内容，以及与其他表的依赖关系等。 4.2.1 查看表的属性查看表的属性 1使用使用SQL Server Management Studio查看查看表的属性表的属性 2使用存储过程查看表结构信息使用存储过程查看表结构信息 EXECUTE sp_help 表名表名 【例例4.4】查看查看kc表的结构。表的结构。 EXEC sp_help kcPage 152021年11月2

16、3日星期二164.2.2 修改表结构修改表结构1使用对象资源管理器修改表使用对象资源管理器修改表2使用使用T-SQL语句修改表语句修改表ALTER TABLE table_name ALTER COLUMN column_name new_data_type(precision,scale)collate collation_name NULL|NOT NULL | ADD|DROP ROWGUIDCOL |ADD |column_name AS computed_column_expression ,n |WITH CHECK | WITH NOCHECK ADD ,n |DROP CONS

17、TRAINT constraint_name | COLUMN column_name ,n |CHECK|NOCHECK CONSTRAINT ALL|constraint_name,n |ENABLE|DISABLE TRIGGER ALL|trigger_name,n17 语法中的各参数说明如下：语法中的各参数说明如下： (1) table_name。要更改的表的名称。若表不在当前数据。要更改的表的名称。若表不在当前数据库中或表不属于当前用户，就必须指定其列所属的数据库库中或表不属于当前用户，就必须指定其列所属的数据库名称和所有者名称。名称和所有者名称。 (2) ALTER COLUMN

18、。指定要更改的列。指定要更改的列。 (3) new_data_type。指定新的数据类型名称。指定新的数据类型名称。 (4) precision。指定新数据类型的精度。指定新数据类型的精度。 (5) scale。指定新数据类型的小数位。指定新数据类型的小数位。 (6) WITH CHECK | WITH NOCHECK。指定向表中添加。指定向表中添加新的或者打开原有的新的或者打开原有的FOREIGN KEY约束或约束或CHECK约束约束的时候，是否对表中已有的数据进行约束验证。对于新添的时候，是否对表中已有的数据进行约束验证。对于新添加的约束，系统默认为加的约束，系统默认为WITH CHECK

19、，WITH NOCHECK作为启用旧约束的默认选项。该参数对于主关作为启用旧约束的默认选项。该参数对于主关键字约束和唯一性约束无效。键字约束和唯一性约束无效。18 (7) ADD | DROP ROWGUIDCOL。添加或删除列的。添加或删除列的ROWGUIDCOL属性。属性。ROWGUIDCOL属性只能指定给一属性只能指定给一个个UNIQUEIDENTIFIER列。列。 (8) ADD。添加一个或多个列。添加一个或多个列。 (9) computed_column_expression。计算列的计算表达。计算列的计算表达式。式。 (10) DROP CONSTRAINT constraint_

20、name | COLUMN column_name 指定要删除的约束或列的名称指定要删除的约束或列的名称。 (11) CHECK | NOCHECK CONSTRAINT。启用或禁。启用或禁用某约束，若设置用某约束，若设置ALL则启用或禁用所有的约束。但该参则启用或禁用所有的约束。但该参数只适用于数只适用于CHECK和和FOREIGN KEY约束。约束。 (12) ENABLE | DISABLE TRIGGER。启用或禁用触。启用或禁用触发器。发器。19 【例【例4.5】 向向“学生信息学生信息”表中添加如表表中添加如表4.2所示的字段。所示的字段。USE jxglGOALTER TABLE

21、 学生信息学生信息ADD 电话电话 char(8) nullGOALTER TABLE学生信息学生信息ADD 电子邮件电子邮件 char(40) nullGOPage 192021年11月23日星期二列列 名名类类 型型是否为空是否为空电话电话char(8)允许允许电子邮件电子邮件char(40)允许允许20 注意：向已有记录的表中添加列时，新添加字注意：向已有记录的表中添加列时，新添加字段通常设置为允许为空，否则必须为该列指定默段通常设置为允许为空，否则必须为该列指定默认值。这样就将默认值传递给现有记录的新增字认值。这样就将默认值传递给现有记录的新增字段，否则添加列的操作将失败。一个段，否则

22、添加列的操作将失败。一个ALTER TABLE命令一次只能对表进行一项修改操作。命令一次只能对表进行一项修改操作。将一个原来允许为空的列改为不允许为空时，必将一个原来允许为空的列改为不允许为空时，必须满足列中没有存放空值的记录的条件，并且在须满足列中没有存放空值的记录的条件，并且在该列上没有创建索引。该列上没有创建索引。21 【例【例4.6】 将将“学生信息学生信息”表中的电子邮件字段的长度修表中的电子邮件字段的长度修改为改为20。 ALTER TABLE学生信息学生信息ALTER COLUMN 电子邮件电子邮件 char(20) null 【例【例4.7】 将将“学生信息学生信息”表中的电话

23、列删除表中的电话列删除。 ALTER TABLE学生信息学生信息DROP COLUMN 电话电话 可以使用系统存储过程可以使用系统存储过程sp_rename对表和表中的列进行对表和表中的列进行重命名重命名。 【例【例4.8】 将表将表“学生信息学生信息”改为改为“XS”，将其中的，将其中的“电电子邮件子邮件”列改名为列改名为“E-mail”。 EXEC sp_rename 学生信息学生信息,XSEXEC sp_rename 学生信息学生信息.电子邮件电子邮件,E-mailPage 212021年11月23日星期二224.2.3 删除数据表删除数据表 删除表就是将表的数据和表的结构从数据库中永久

24、的移除。删除表就是将表的数据和表的结构从数据库中永久的移除。也就是说，一个表一旦被删除，则该表的数据、结构定义、也就是说，一个表一旦被删除，则该表的数据、结构定义、约束、索引等都被永久删除，就无法恢复，除非还原数据约束、索引等都被永久删除，就无法恢复，除非还原数据库。因此执行此操作时应该慎重。库。因此执行此操作时应该慎重。 在在SQL Server Management Studio的的“对象资源管理器对象资源管理器”中，选择要删除的数据表，单击右键，在弹出的快捷菜单中，选择要删除的数据表，单击右键，在弹出的快捷菜单中选择中选择“删除删除” DROP TABLE 表名表名,.n 【例【例4.9

25、】 删除删除jxgl数据库中的数据库中的cj表表。 USE jxglGODROP TABLE 成绩成绩GOPage 222021年11月23日星期二23 在使用在使用DROP TABLE语句删除数据库表时，需注意以下语句删除数据库表时，需注意以下几点。几点。 （1）DROP TABLE语句不能删除系统表。语句不能删除系统表。 （2）DROP TABLE语句不能删除正被其他表中的外键约语句不能删除正被其他表中的外键约束参考的表。当需要删除这种有外键约束参考的表时，必束参考的表。当需要删除这种有外键约束参考的表时，必须先删除外键约束，然后才能删除表。须先删除外键约束，然后才能删除表。 （3）当删除

26、表时，属于该表的约束和触发器也会自动被）当删除表时，属于该表的约束和触发器也会自动被删除。如果重新创建该表，必须注意创建相应的规则、约删除。如果重新创建该表，必须注意创建相应的规则、约束和触发器等。束和触发器等。 （4）使用）使用DROP TABLE命令一次可以删除多个表，多个命令一次可以删除多个表，多个表名之间用逗号分开。表名之间用逗号分开。Page 232021年11月23日星期二244.3 表数据的操作表数据的操作 表创建以后，往往只是一个没有数据的空表。因此，向表表创建以后，往往只是一个没有数据的空表。因此，向表中输入数据应当是创建表之后首先要执行的操作。无论表中输入数据应当是创建表之

27、后首先要执行的操作。无论表中是否有数据，都可以根据需要向表中添加数据，如果表中是否有数据，都可以根据需要向表中添加数据，如果表中的数据不再需要，则可以删除这些数据。中的数据不再需要，则可以删除这些数据。 4.3.1 图形界面方式操作表数据图形界面方式操作表数据Page 242021年11月23日星期二25 注意：在编辑表中数据的过程中，输入的注意：在编辑表中数据的过程中，输入的各列的内容一定要和所定义的数据类型一各列的内容一定要和所定义的数据类型一致，如果有其他定义或约束等要求，也一致，如果有其他定义或约束等要求，也一定要符合，否则将出现错误。定要符合，否则将出现错误。Page 252021年

28、11月23日星期二264.3.2 使用使用INSERT命令向表中添加数据命令向表中添加数据 在数据库的使用过程中更多的是采用在数据库的使用过程中更多的是采用INSERT命令向命令向表中添加数据。其基本的语法格式如下。表中添加数据。其基本的语法格式如下。INSERT INTO 表名表名 （列名（列名l, 列名列名2,） VALUES （表达式（表达式1, 表达式表达式2,） 其中其中INSERT子句指定要插入数据的表名，并且可以子句指定要插入数据的表名，并且可以同时指定表中的列名；同时指定表中的列名；VALUES子句指定要插入的数子句指定要插入的数据。所给数据的顺序要与表定义中列的顺序一致。据。

29、所给数据的顺序要与表定义中列的顺序一致。注意：注意：VALUES中给出的数据顺序和数据类型必须与中给出的数据顺序和数据类型必须与表中列的顺序和数据类型一致。向表中插入一条记录表中列的顺序和数据类型一致。向表中插入一条记录时，可以给某些列赋空值，但这些列必须是可以为空时，可以给某些列赋空值，但这些列必须是可以为空的列。的列。Page 262021年11月23日星期二27【例【例4.10】 向向“学生信息学生信息”表中插入记录。表中插入记录。 插入包含空值的数据插入包含空值的数据。 USE jxglGOINSERT 学生信息学生信息(stu_id,stu_name,stu_sex,stu_birt

30、h, stu_birthplace,dept_id,stu_telephone,credit)VALUES(2013030101, 秦建兴秦建兴, 男男, 95/08/09, 北京市北京市, d001,null,null) 插入表中所有列的数据。插入表中所有列的数据。INSERT 学生信息学生信息VALUES(2013030112, 张吉哲张吉哲, 男男, 94/12/1,null, 沈阳市沈阳市,null,null) 插入表中指定列的数据插入表中指定列的数据。 INSERT 学生信息学生信息(stu_id,stu_name,stu_sex,stu_birth)VALUES(201303011

31、6, 马艳玲马艳玲, 女女, 94/12/20)GOPage 272021年11月23日星期二284.3.3 使用使用UPDATE修改表中的数据修改表中的数据 语法格式如下。语法格式如下。UPDATE table_name SET column_name=expression|DEFAULT|NULL,nFROM ,nWHERE :=table_nameAStable_zliasWITH ( ,n )语法中的各参数说明如下：语法中的各参数说明如下：table_name。需要更新的表的名称。需要更新的表的名称。SET。指定要更新的列或变量名称的列表。指定要更新的列或变量名称的列表。column_name。含有要更改数据的列的名称。含有要更改数据的列的名称。expression | DEFAULT | NULL。列值表达式。列值表达式。修改数据表源。修改数据表源表表Page 282021年11月23日星期二29 注意：在使用注意：在使用UPDATE语句时，如果没有语句时，如果没有使用使用WHERE子句，那么就对表中所有的行子句，那么就对表中所有的行进行修改。如果使用进行修改。如果使用UPDATE语句修改数语句修改数据时与数据完整性约束有冲突，修改将不据时与数据完整性约束有冲突，修改将不会发生。会发生。 【例【例4