第4章 数据表与表数据.ppt

1、第4章 数据表与表数据,2020/8/12,2 / 39,在SQL Server 2005中，数据表是一种很重要的数据库对象，是组成数据库的基本元素。假若你已经创建了数据库，那么，怎么样在数据库中创建数据表？又怎么样向数据表中输入表数据呢？,第4章 数据表与表数据,2020/8/12,3 / 39,本章学习任务 数据表的设计和创建 数据表设计与创建 修改表结构 插入、更新和删除表数据 定义约束 数据的完整性 约束的类型 约束的创建、查看、删除 默认值和规则 默认值的使用与规则的使用,第4章 数据表与表数据,2020/8/12,4 / 39,数据表与电子表格类似，数据在表中是按照行和列的格式来组

2、织排列的。每行代表一唯一的记录，每列代表记录中的一个域。如学生基本信息的数据表，表中的每一行代表一个学生，每一列分别表示学生的详细资料，如学号、姓名等。 数据表由若干个列组成，每一个列的列名在表中应该是唯一的；其列值为SQL Server可选的数据类型；大小则规定列值的最大长度；小数位用于定义数值型的小数位数；是否为空说明列值是否允许为空值；默认值用于定义不经输入所确定的列值。,数据表的设计和创建,2020/8/12,5 / 39,设计表,数据库中通常包含多个表，每个表是一个存储数据的实体，具有唯一的名称。 表是对数据进行存储和操作的一种逻辑结构，每个表代表一个对象。 例如：在学生管理数据库中

3、，有学生基本信息表，教师基本信息表，学生成绩表，课程信息表，专业代码表，教研室信息表，教室信息表等。,2020/8/12,6 / 39,学生基本信息表,数据表的设计和创建,2020/8/12,7 / 39,学生基本信息表结构,数据表的设计和创建,2020/8/12,8 / 39,使用对象资源管理器创建数据表,2020/8/12,9 / 39,语法格式： CREATE TABLE database_name. owner .| owner. table_name ( | column_name AS computed_column_expression | := CONSTRAINT const

4、raint_name | PRIMARY KEY | UNIQUE ,.n ON filegroup | DEFAULT TEXTIMAGE_ON filegroup | DEFAULT := column_name data_type COLLATE ,使用T-SQL语句创建数据表,2020/8/12,10 / 39,主要参数：,使用T-SQL语句创建数据表,2020/8/12,11 / 39,使用T-SQL语句创建数据表,2020/8/12,12 / 39,数据表创建以后，在使用过程中可能需要对原先定义的表的结构进行修改。修改表的结构也可以通过对象资源管理器和T-SQL语句两种方法进行。对

5、表结构的修改包括：更改表名、增加列、删除列、修改已有列的属性等。,修改数据表结构,2020/8/12,13 / 39,使用对象资源管理器修改表,修改表名 增加列 删除列 修改已有列的属性,2020/8/12,14 / 39,ALTER TABLE table_name ALTER COLUMN column_name new_data_type ( precision , scale ) COLLATE NULL | NOT NULL | ADD ,.n | DROP CONSTRAINT constraint_name | COLUMN column_name ,.n ,使用T-SQL语句修

6、改表结构,2020/8/12,15 / 39,使用T-SQL语句修改表结构,2020/8/12,16 / 39,用ALTER TABLE的ADD子句向表中添加列 ALTER TABLE table_name ADD column_name new_data_type (precision ,scale) NULL | NOT NULL 用ALTER TABLE的DROP COLUMN删除列 ALTER TABLE table_name DROP COLUMN column_name 使用DROP语句删除数据表 DROP TABLE table_name,使用T-SQL语句修改表结构,2020/

7、8/12,17 / 39,使用T-SQL语句修改表结构,2020/8/12,18 / 39,使用DROP删除数据表,DROP TABLE 表名,2020/8/12,19 / 39,使用对象资源管理器查看表结构 在“对象资源管理器”中，右击需要查看结构的表，在弹出的快捷菜单中选择“修改”命令，打开数据表窗口，即可查看数据表结构信息。 使用系统存储过程sp_help查看表结构 EXECUTE sp_help table_name 使用对象资源管理器查看表中的数据 在“对象资源管理器”中，右击需要查看数据的表，弹出快捷菜单，单击“打开表”命令，用户即可根据表格线和使用滚动条来查看到表中的数据。 使用

8、SELECT语句查看表中的数据,插入、更新和删除表数据,2020/8/12,20 / 39,向数据表中插入数据，就是将一条或多条记录添加到数据表中。T-SQL中使用INSERT命令完成数据插入。 INSERT INTO table_name (column1,column2) values(value1,value2),使用INSERT语句插入数据,2020/8/12,21 / 39,使用INSERT语句插入数据,2020/8/12,22 / 39,UPDATE语句可以更新表中现存记录中的数据UPDATE table_name SET column1=modified_value1 ,colu

9、mn2=modified_values, WHERE column1=value1,column2=value2,使用UPDATE语句更新数据,2020/8/12,23 / 39,使用UPDATE语句更新数据,2020/8/12,24 / 39,使用DELETE语句可以从表中删除一行或多行记录。 DELETE FROM table_name WHERE column1=value1,column2=value2 其中：WHERE子句用来指定删除行的条件。,使用DELETE语句删除数据,2020/8/12,25 / 39,本章学习任务 数据表的设计和创建 数据表设计与创建 修改表结构 插入、更新

10、和删除表数据 定义约束 数据的完整性 约束的类型 约束的创建、查看、删除 默认值和规则 默认值的使用与规则的使用,第4章 数据表与表数据,2020/8/12,26 / 39,什么是数据完整性呢？ 存储在数据库中数据的一致性和正确性。 为了保证数据完整性，SQL Server提供了定义、检查和控制数据完整性的机制。 表列中除了具有数据类型和大小属性之外，还有其他属性。其他属性是保证数据库中数据完整性和表的引用完整性的重要部分。 根据数据完整性措施所作用的数据库对象和范围不同，数据完整性分为：实体完整性、参照完整性、域完整性和用户定义完整性四种。,定义约束,2020/8/12,27 / 39,实体

11、完整性,实体完整性也称为行完整性，指数据表中的所有行都是唯一确定的。 实体完整性规定表中的主键值必须唯一，所有的主属性不能为空,2020/8/12,28 / 39,参照完整性,参照完整性保证参照表和被参照表中的数据一致性。 通过定义外键和主键之间的引用，保证数据库中不会含有无效外键,2020/8/12,29 / 39,域完整性,域完整性也称为列完整性，用以指定列数据具有正确的数据类型、各式和有效的取值范围，保证数据的正确性。,2020/8/12,30 / 39,用户定义完整性,用户定义完整性允许用户定义不属于其它任何完整性分类为的特定规则,2020/8/12,31 / 39,CHECK约束 限

12、制输入到一列或多列的值的范围 DEFAULT约束 如果没有为列指定数据，系统将默认值赋给列 PRIMARY KEY约束 如果某列或多列的值能惟一标识表中的每一行，这样的列称为表的主键，通过它可以强制表的实体完整性。,约束的类型,2020/8/12,32 / 39,FOREIGN KEY约束 外键(Foreign Key)是用于建立和加强两个表(主表与从表)的一列或多列数据之间的链接，当数据添加、修改或删除时，通过参照完整性保证它们之间数据的一致性。 定义表之间的参照完整性是先定义主表的主键，再对从表定义外键约束 UNIQUE约束 确保表中的两个数据行在非主键列中没有相同的列值,约束的类型,20

13、20/8/12,33 / 39,PRIMARY KEY约束标识列或列集，这些列或列集的值唯一标识表中的行。一个表只能有一个主键，而且主键不能接收空值，不能重复。 PRIMARY KEY约束可以在下面情况下使用： 作为表定义的一部分在创建表时创建。 添加到尚没有PRIMARY KEY约束的表中（一个表只能有一个PRIMARY KEY约束）。 如果已有PRIMARY KEY约束，则可对其进行修改或删除。,2020/8/12,34 / 39,使用对象资源管理器设置、删除和修改主键。 在为表指定主键时，SQL Server自动创建一个前缀为“PK_“的主键。,2020/8/12,35 / 39,使用S

14、QL语句设置，删除主键。,在设计表时指定主键 语法; Create table （列名 数据类型 CONSTRAINT 约束名 PRIMARY KEY） |CONSTRAINT 约束名 PRIMARY KEYCLUSTERED|NONCLUSTERED（列名） 说明：CLUSTERED|NONCLUSTERED：是否建立聚簇索引,2020/8/12,36 / 39,例： create table sc2 (sno char(10), cno char(10), grade int, primary key (sno,cno),2020/8/12,37 / 39,为表添加主键,语法 alter

15、table add constraint primary key CLUSTERED|NONCLUSTERED（列名）,2020/8/12,38 / 39,例： alter table sc2 add primary key (sno,cno) 或： alter table sc2 add constraint PK1 primary key (sno,cno),2020/8/12,39 / 39,删除主键,语法： alter table drop constraint 例 alter table sc2 drop constraint PK1,2020/8/12,40 / 39,FOREIG

16、N KEY约束可以在下面情况下使用： 作为表定义的一部分在创建表时创建。 如果FOREIGN KEY约束与另一个表（或同一表）已有的PRIMARY KEY约束或UNIQUE约束相关联，则可向现有表添加FOREIGN KEY约束。一个表可以有多个FOREIGN KEY约束。 对已有的FOREIGN KEY约束进行修改或删除。例如，要使一个表的FOREIGN KEY约束引用其他列。定义了FOREIGN KEY约束列的列宽不能更改。,2020/8/12,41 / 39,外键是用于建立两个表之间的联系。 主键所在的表称为主键表，外键所在的表的表称为外键表，使用约束为 FOREIGN KEY。 外键约束

17、可以参照其它表中的列，还可以参照自己，称为自参照。,2020/8/12,42 / 39,使用FOREIGN KEY约束，还应注意以下几个问题： 一个表中最多可以有253个可以参照的表，因此每个表最多可以有253个FOREIGN KEY约束。 FOREIGN KEY约束中，只能参照同一个数据库中的表，而不能参照其他数据库中的表。 FOREIGN KEY子句中的列数目和每个列指定的数据类型必须和REFERENCE子句中的列相同。 FOREIGN KEY约束不能自动创建索引。 参照同一个表中的列时，必须只使用REFERENCE子句，而不能使用FOREIGN KEY子句。 在临时表中，不能使用FORE

18、IGN KEY约束。,2020/8/12,43 / 39,使用管理器创建，修改和删除外健关联：,2020/8/12,44 / 39,建立外键时，外键所约束的列的数据类型及长度必须与主键所在的列的数据类型及长度一致或可以由SQL Server自动转换。 创建外键时，系统会自动为外键命名“FK_“ 创建中检查现存数据 对复制强制关系 对INSERT和UPDATE强制关系 级联更新相关字段 级联删除相关字段,2020/8/12,45 / 39,1）创建表时定义外键关联 语法 Create table （列名 数据类型 CONSTRAINT 约束名 FOREIGN KEY）REFERRECES 主键表

19、（主键列） ） |CONSTRAINT 约束名 FOREIGN KEY 列名 REFERRECES 主键表（主键列） ON DELETE CASCADE|NO ACTION ON UPDATECASCADE|NO ACTION NOT FOR REPLICATION,2020/8/12,46 / 39,说明： ON DELETE CASCADE|NO ACTION：表示删除外键相对应的主键所在的行时，级联删除（CASCADE）外键所在的行的数据或不作操作（NO ACTION）。 ON UPDATECASCADE|NO ACTION：表示修改外键相对应的主键所在的行时，级联修改（CASCADE）

20、外键所在的行的数据或不作操作（NO ACTION）。 NO ACTION是默认设置,2020/8/12,47 / 39,例： create table sc2 (sno char(10), cno char(10), grade int, primary key (sno,cno), foreign key (sno) references student(sno), foreign key (cno) references course(cno),2020/8/12,48 / 39,为已有表添加关联 语法： alter table add constraint FOREIGN KEY 列名

21、REFERRECES 主键表（主键列） ON DELETE CASCADE|NO ACTION ON UPDATECASCADE|NO ACTION NOT FOR REPLICATION,2020/8/12,49 / 39,例： alter table sc2 add constraint hh foreign key (sno) references student(sno),2020/8/12,50 / 39,删除关联 语法： alter table drop constraint 例 alter table sc2 drop constraint hh,2020/8/12,51 / 3

22、9,利用系统存储过程察看外键 语法： Sp_fkeys 主键表名 例：查看引用表student的主键的外键信息 sp_fkeys s 使用关系图管理外键,2020/8/12,52 / 39,UNIQUE约束,可使用 UNIQUE 约束确保在非主键列中不输入重复值。尽管 UNIQUE 约束和 PRIMARY KEY约束都强制唯一性，但在强制下面的唯一性时应使用 UNIQUE 约束而不是 PRIMARY KEY 约束： 非主键的一列或列组合：一个表可以定义多个 UNIQUE 约束，而只能定义一个 PRIMARY KEY 约束。 允许空值的列：允许空值的列上可以定义 UNIQUE 约束，而不能定义

23、PRIMARY KEY 约束。 FOREIGN KEY 约束也可引用 UNIQUE 约束。,2020/8/12,53 / 39,使用管理器设置UNIQUE约束,在添加UNIQUE约束时，SQL Server会检查现有的数据，确保除NULL外所有值唯一，否则不能创建此约束,2020/8/12,54 / 39,使用SQL设置UNIQUE约束1）在创建表时设置UNIQUE约束 语法： Create table （列名 数据类型 CONSTRAINT 约束名 UNIQUE） |CONSTRAINT 约束名 UNIQUECLUSTERED|NONCLUSTERED（列名）,2020/8/12,55 /

24、39,例： create table sc3 (sno char(10), cno char(10), grade int, unique(sno,cno),2020/8/12,56 / 39,为已有的表添加UNIQUE约束 语法： alter table add constraint UNIQUE CLUSTERED|NONCLUSTERED（列名） 例： alter table student add constraint IX1 unique (sname),2020/8/12,57 / 39,删除UNIQUE约束 语法： alter table drop constraint 例： a

25、lter table student drop constraint IX1,2020/8/12,58 / 39,CHECK约束通过限制用户输入的值来加强域完整性。它指定应用于列中输入的所有值的布尔（取值为TRUE或FALSE）搜索条件，拒绝所有不取值为TRUE的值。可以为每列指定多个CHECK约束。 例如，下面的SQL语句创建一个成绩（score）表，其中使用CHECK约束来限定成绩只能为0100分： CREATE TABLE score (sutdent_number int, score int NOT NULL CHECK(score=0 AND score =100) ),2020/

26、8/12,59 / 39,CHECK 约束通过限制输入到列中的值来强制域的完整性。 CHECK 约束从逻辑表达式判断而非基于其它列的数据。例如，通过创建 CHECK 约束可将 salary 列的取值范围限制在 $15,000 至 $100,000 之间，从而防止输入的薪金值超出正常的薪金范围。 可以通过任何基于逻辑运算符返回结果 TRUE 或 FALSE 的逻辑（布尔）表达式来创建 CHECK 约束。对上例，逻辑表达式为： salary = 15000 AND salary = 100000 对单独一列可使用多个 CHECK 约束。按约束创建的顺序对其取值。通过在表一级上创建 CHECK 约束

27、，可以将该约束应用到多列上。当一列受到多个核查约束时，所有的约束按照创建的顺序依次进行数据有效性的核查。,2020/8/12,60 / 39,使用对象资源管理器创建、修改和删除CHECK约束,默认的情况下，核查约束将同时应用于新的数据和已经存在的数据,2020/8/12,61 / 39,在创建表时，定义CHECK约束 Create table （列名 数据类型 CONSTRAINT 约束名 CHECKNOT FOR REPLICATION（约束表达式） |CONSTRAINT 约束名 CHECK NOT FOR REPLICATION（约束表达式）,2020/8/12,62 / 39,例： c

28、reate table sc2 (sno char(10), cno char(10), grade int , check (grade =0 and grade =100) ),2020/8/12,63 / 39,在已存在的表上，添加约束 alter table add constraint CHECK （约束表达式） 例： alter table sc2 add constraint c1 check (grade =0 and grade =100),2020/8/12,64 / 39,删除check约束 alter table drop constraint ,2020/8/12,6

29、5 / 39,约束可以是列约束或表约束： 列约束被指定为列定义的一部分，并且仅适用于那个列（前面的score表中的约束就是列约束）。 表约束的声明与列的定义无关，可以适用于表中一个以上的列。,2020/8/12,66 / 39,默认值,在使用SQL Server Management Studio创建表时，可以在输入字段名称后，设定该字段的默认值，如图所示。,2020/8/12,67 / 39,默认值：DEFAULT,当将某行装载到为行定义了 DEFAULT 的表中时，若没有指定列值，则是隐性要求 Microsoft SQL Server 将默认值装载到该列中。 默认值必须与 DEFAULT

30、定义适用的列的数据类型相一致。例如，int 列的默认值必须是整数，而不是字符串。 如下定义的列不能创建 DEFAULT 定义： timestamp 数据类型。 IDENTITY 或 ROWGUIDCOL 属性。 已有 DEFAULT 定义或 DEFAULT 对象。,2020/8/12,68 / 39,利用SQL语句指定默认值,在创建表时指定默认值： create table sc2 (sno char(10), cno char(10), grade int default 60）,2020/8/12,69 / 39,为已有表添加默认值,alter table sc2 Add constrai

31、nt dd default 60 for score,2020/8/12,70 / 39,补充：IDENTITY（标识列）,标识符列可用 IDENTITY 属性建立，使应用程序开发人员得以对表中所插入的第一行指定标识数字（Identity Seed 属性），并确定要添加到种子上的增量（Identity Increment 属性）以决定后面的标识数字。在向具有标识符列的表中插入值时，Microsoft SQL Server通过递增种子值的方法自动生成下一个标识值。 每个表中只能创建一个标识符列和一个全局唯一标识符列。 在用 IDENTITY 属性定义标识符列时应注意以下几点： 一个表只能有一列定

32、义为 IDENTITY 属性，而且该列必须以 decimal、int、numeric、smallint、bigint 或 tinyint 数据类型定义。 可指定种子和增量值。二者的默认值均为 1。 标识符列不允许空值，也不能包含 DEFAULT 定义或对象。,2020/8/12,71 / 39,1、使用管理器设置，修改，删除标识符,注意： 当表中的数据行的数目超过了标识符列的上界或下界时，就不能再向表中插入新的数据。 服务器不会自动去寻找因为删除或其他原因跳过而没有使用的标识符的值。 一般情况下不允许认为的向标识列插入和修改值。,2020/8/12,72 / 39,2、使用SQL语句设置标识符

33、列,1）在创建表时定义标识列 语法： create table (列名 数据类型 identity, ) 例 create table school (ID1 tinyint identity, sname varchar(30) not null, address varchar(30) null) 向表中插入数据： insert into school values(邢台学院,邢台),2020/8/12,73 / 39,再插入数据 insert into school values(3,邯郸学院,邯郸) 报错： 仅当使用了列的列表，并且 IDENTITY_INSERT 为 ON 时，才能在

34、表 school 中为标识列指定显式值。,2020/8/12,74 / 39,2）当表格的IDENTITY_INSERT属性被激活后，表的拥有者、数据库拥有者或SA可以直接向表格中插入IDENTITY列的数据 例 Set IDENTITY_INERT school ON insert into school (ID1,sname,address) 注意必须给出列表 values(3,邯郸学院,邯郸) Set IDENTITY_INERT school OFF 可以执行,2020/8/12,75 / 39,本章学习任务 数据表的设计和创建 数据表设计与创建 修改表结构 插入、更新和删除表数据 定

35、义约束 数据的完整性 约束的类型 约束的创建、查看、删除 默认值和规则 默认值的使用与规则的使用,第4章 数据表与表数据,2020/8/12,76 / 39,默认值可以先定义好，需要时将它绑定到一列或多列上，在表中插入数据行时，系统自动为没有指定数据的列提供事先定义的默认值。 与DEFAULT约束不同的是，默认值是一种数据库对象，在数据库中只需定义一次后，就可以被一次或多次应用于任意表中的一列或多列，还可以用于用户自定义的数据类型。,使用默认值,2020/8/12,77 / 39,创建默认值和将其绑定到表上的某列 CREATE DEFAULT default_name AS constrain

36、t_expression 其中： default_name：表示所建立的默认值名称。 Constraint_expression：指定的默认值。 EXEC sp_bindefault default_name table_name.column_name,|user_datetype 其中： default_name是CREATE DEFAULT创建默认值的名称。 table_name.column_name, |user_datetype为表名和列名或者绑定默认值的别名数据类型。,使用默认值,2020/8/12,78 / 39,1）创建默认值： 例 create default ss as

37、20 2）将默认值绑定到指定列 例： Sp_bindefault ss,sc.grade,2020/8/12,79 / 39,3）解除绑定 Sp_unbindefault 例： Sp_unbindefault sc.grade 4）删除默认值 drop default 默认值名 例 drop default ss 重命名默认值：直接在企业管理器中修改 删除默认值：删除默认值之前，必须确定默认对象没有绑定到任何对象上。,2020/8/12,80 / 39,规则这种数据库对象的作用就是当向数据表中某列插入数据时，指定该列接受数据值的范围。 规则的作用与CHECK约束一样，只不过规则不固定于那个列，

38、而是创建好以后可以随便地绑定于表中某个列上。 创建和绑定规则 CREATE RULE rule_name AS condition_expression 其中：rule_name为规则名，condition_expression定义规则的条件 EXEC sp_bindrule rule_name, table_name.column_name, | user_datetype,使用规则,2020/8/12,81 / 39,使用管理器创建规则,在规则的表达式中，不能出现列名，只能包含一个变量，该变量可以出现多次，必须以“”开头。该变量将引用更新记录或者插入记录时输入的值。 类似默认值的操作，需要对