SQL课件第03章2.ppt

1、第三章 管理表,第二部分,本章知识要点,了解表的类型及创建 熟悉各种数据类型 掌握列的各种属性 掌握修改表和列的操作 熟悉保持数据完整性的各种约束 了解表的索引和视图,1修改表的结构,1）命令格式 ALTER TABLE ALTER COLUMN 类型（宽度）NULL|NOT NULL |ADD 类型（宽度）NULL|NOT NULL完整性约束,n |DROP COLUMN CASCADERESTRICT,n |DROP CONSTRAINT|ALL ,n,3.4 维护数据表,2）功能 ALTER TABLE：将要修改的当前数据库中的指定数据表的表名。 ALTER COLUMN：修改当前数据库

2、中的指定数据表的指定属性。 ADD：向当前数据库中的指定数据表增加指定属性或列级完整性约束。 DROP COLUMN：删除当前数据库中的指定数据表中的指定属性。 DROP：删除当前数据库中的指定数据表中的指定列级完整性约束。,【例3.15】修改当前数据库“studentcourse”中S表的系属性改成char(25)，增加一个入学时间字段，它的数据类型为date time，并设置默认值为getdate()，最后删除入学时间字段。 方法一：使用SQL Server Management Studio 方法二：使用命令。 Use studentcourse Go ALTER TABLE s ALT

3、ER COLUMN 系char(25) Go ALTER TABLE s ADD 入学时间 datetime Go ALTER TABLE s ADD CONSTRAINT DF_sj DEFAULT(getdate() for 入学时间,3.4 维护数据表,2修改表的名称,1）命令格式 Sp_rename , 2）功能 重命名当前数据库中的指定数据表名。 【例3.16】重命名数据库bookshop的数据表book名称，改为“书籍资料”。 方法一：使用SQL Server Management Studio 方法二：使用SQL命令。 Exec Sp_rename book, 书籍资料,3.2.

4、6 修改数据表,1）命令格式 DROP TABLE 表名 2）功能 删除表 【例3.17】删除当前数据库中的表S。 方法一：使用SQL Server Management Studio 方法二：使用SQL命令。 DROP TABLE S 注意: 如果只删除表中数据行，而保留表结构 Delete table s,3.2.7 删除数据表,3.5 表数据完整性,数据完整性是为了防止数据库中出现不符合语义的数据，为了维护数据的完整性，数据库管理系统必须提供一种机制来检查数据库的数据是否满足语义规定的条件，这些加在数据库之上的语义约束条件就是数据库中的数据完整性约束条件。,8,3.5.1 表主键,主键（

5、PRIMARY KEY）约束使用数据表中的一列数据或者多列数据来惟一标识一行数据。也就是说，在数据表中不能存在主键相同的两行数据。而且，位于主键约束下的数据应使用确定的数据，不能输入NULL来代替确定的数值。在管理数据时，应确保每一个数据表都拥有自己惟一的主键，从而实现数据的实体完整性。,9,CREATE database bookshop Go CREATE TABLE book (书号 int identity(1000,1) PRIMARY KEY CLUSTERED, 书名 char(20) not null, 出版社 char(20), 出版日期 datetime DEFAULT (

6、getdate(), 单价 smallint check(单价0), 数量 smallint check(数量=0), 总价 as 单价*数量, 电子邮件地址 varchar(25), check(电子邮件地址 like %), check (出版社 in (高教,浙大,电子,中央),3.5.2 外键,外键（FOREIGN KEY）约束定义了表之间的关系，主要用来维护两个表之间的一致性。当一个表中的一个列或者多个列的组合和其他表中的主关键字定义相同时，就可以将这些列或者列的组合定义为外关键字，并设定它适合与哪个表中的哪些列相关联。,11,3.5.3 CHECK约束,CHECK约束通过检查输入表

7、列的数据的值来维护值域的完整性，它就像一个过滤器依次检查每一个要进入数据库的数据，只有符合条件的数据才允许通过。,12,第13页,【例3.2.4】要在当前数据库StudentsInfo中的加入Enrollment表，表中各列的要求见表3-3。,第14页,CREATE TABLE Enrollment( Sno CHAR(10) NOT NULL, Cno CHAR(6) NOT NULL, Grade INT, CONSTRAINT EPK PRIMARY KEY ( Sno, Cno ), CONSTRAINT ESlink FOREIGN KEY (Sno) REFERENCES Stud

8、ents ( Sno ), CONSTRAINT EClink FOREIGN KEY (Cno ) REFERENCES Courses ( Cno ) ),定义该表的SQL语句：,第15页,等价于： CREATE TABLE Enrollment( Sno CHAR(10) NOT NULL FOREIGN KEY ( Sno) REFERENCES Students ( Sno ), Cno CHAR(6) NOT NULL FOREIGN KEY ( Cno ) REFERENCES Courses( Cno ), Grade INT, PRIMARY KEY ( Sno, Cno )

9、 ),第16页,等价于： CREATE TABLE Enrollment( Sno CHAR(10) NOT NULL REFERENCES Students ( Sno ), Cno CHAR(6) NOT NULL REFERENCES Courses( Cno ), Grade INT, PRIMARY KEY ( Sno, Cno ) ),3.5.4 表关系,可以在数据库关系图中的表间创建关系，以显示某个表中的列如何链接到另一表中的列。在关系数据库中，关系可以防止冗余数据，关系通过匹配键列（通常是两个表中同名的列）中的数据来发挥作用。在大多数情况下，关系将一个表的主键（它为每行提供唯一

10、标识符）与另一表中外键的项相匹配。,17,1索引的作用 1）加速数据检索 2）优化查询 3）强制数据完整性。 2索引的分类 1）聚集索引 2）非聚集索引 3）唯一索引,3.4 索引管理,3.4.1 索引概述,SQL Server 访问数据的方式,表扫描 SQL Server 扫描表的 所有页 索引 SQL Server 使用索引 页找到行,6.1.1 SQL Server 访问数据的方式,1）命令格式 CREATEUNIQUECLUSTERED|NOCLUSTERED INDEX 索引名 ON数据表名|视图名（字段名表ASC|DESC,n） 2）功能 只有在指定的索引名称存在时，才能使用DRO

11、PEXISTING选项，该项说明首先删除指定表的索引后再重新构造它。 “UNIQUE”表示建立唯一索引。 CLUSTERED表示建立聚集索引，NOCLUSTERED表示建立非聚集索引。,3.4.2 创建索引,3.4.2 创建索引,表3.17 “studentcourse”索引情况表,唯一索引,确保在索引键上不存在相同的值,CREATE UNIQUE NONCLUSTERED INDEX AK_Employee_LoginID ON HumanResources.Employee ( LoginID ASC),不允许存在相同值,6.2.2 唯一索引,多媒体：新建索引,用户反映对于Sales.Sa

12、lesOrderDetail表的访问相当缓慢，你发现用户经产需要查询OrderQty列。请为该表创建索引，同时索引应当尽可能小。,【例3.28】使用SQL命令，在数据库“studentcourse”中的数据表S中，关于“学号”建立聚集索引，关于“姓名”建立非聚集索引。 CREATE INDEX IN_姓名 on s(姓名) CREATE unique clustered INDEX IN_学号 on s(学号) WITH pad_index,fillfactor=100 -填充因子为100 【例3.29】 为数据库“studentcourse”中的数据表关于c.课程名降序建立唯一索引IN_课程

13、名。 IF EXISTS(select name from sysindexes where name=IN_课程名) DROP INDEX c.IN_课程名 Go USE studentcourse CREATE unique INDEX IN_课程名 on c(课程名 desc) 。,3.4.2 创建索引,1）命令格式 DROP INDEX 索引名,n 2）功能 删除指定的索引。可以列出多个要删除的索引名。利用DROP INDEX命令删除通过定义PRIMARY KEY或UNIQUE约束创建的索引，必须先删除指定的约束。在系统表的索引不能使用DROP INDEX删除。删除表中的聚集索引，将使

14、表中的所在非聚集索引重建。 【例3.32】删除数据库“studentcourse”中，数据表sc中的索引IN_成绩、数据表c中的索引IN_课程名。 Use studentcourse DROP INDEX sc. IN_成绩,c.IN_课程名,3.4.3 删除索引,习题（续）,6.订单表Orders的列OrderID的类型是小整型（smallint），根据业务的发展需要改为整型（integer），应该使用下面的哪条语句？ A. ALTER COLUMN OrderID integer FROM Orders B. ALTER TABLE Orders ( OrderID integer ) C

15、. ALTER TABLE Orders ALTER COLUMN OrderID integer D. ALTER COLUMN Orders.OrderID integer,3.5习题,习题（续）,7.有家企业要用表tblCustomerInfo来存储客户的信息。客户的信息包括：代号（整型IDENTITY，从100001开始，每次增加5），名称（最长40个汉字），电话（20个字符），传真（20个字符），备注（最长1 000个汉字）。电话号码和传真号码要用同一自定义类型type_TelphoneNum。 （1）请写出创建该表的SQL语句。 （2）后来因手机流行，需要在表tblCustomerInfo中再添加 “手机”列，该列的类型也是type_TelphoneNum。请写出添加该列的SQL语句。,3.5习题,（1）要注意存放一个汉字要用两个字节，所以“名称”和“备注”的长度要乘2。具体的SQL语句如下： CREATE TABLE tblCustomerInfo ( 代号 integer IDENTITY(10001,5) NOT NULL, 名称 varchar(80) NOT NULL, 电话 type_TelphoneNum , 传真 type_TelphoneNum , 备注 varc