第三章关系数据库标准语言SQL数据定义

1、第三章 关系数据库标准语言SQL 3.3 数据定义一、SQL Server 2000中的数据类型1、整数bigint: 从 -263 (-9223372036854775808) 到 263-1 (9223372036854775807) 的整型数据（所有数字）int: 从 -231 (-2,147,483,648) 到 231 1 (2,147,483,647)的整型数据（所有数字）。smallint: 从 -215 (-32,768) 到 215 - 1 (32,767) 的整数数据。 tinyint: 从 0 到 255 的整数数据。2、decimal and numericdecima

2、l ：从 -1038 +1 到 1038 1 的固定精度 和小数位的数字数据。numeric ：功能上等同于 decimal。第三章 关系数据库标准语言SQL 3.3 数据定义3、money and smallmoneymoney ：货币数据值介于 -263 (-922,337,203,685,477.5808) 与 263 - 1 (+922,337,203,685,477.5807)之间，精确到货币 单位的千分之十。smallmoney ：货币数据值介于-214,748.3648与 +214,748.3647之间，精确到货币单位的千分之十 4、Approximate Numericsflo

3、at ：从-1.79E+308到1.79E+308 的浮点精度数字real ：从-3.40E+38到3.40E+38 的浮点精度数字。第三章 关系数据库标准语言SQL 3.3 数据定义5、datetime and smalldatetimedatetime ：从 1753 年 1 月 1 日到 9999 年 12 月 31日的日期和时间数据，精确到百分之三秒（或 3.33 毫秒）。smalldatetime ：从 1900 年 1 月 1 日到 2079 年 6 月 6 日的日期和时间数据，精确到分钟。6、Character Stringschar ：固定长度的非 Unicode 字符数据，最

4、大 长度为 8,000 个字符。varchar ：可变长度的非 Unicode 数据，最长为 8,000 个字符。text ：可变长度的非 Unicode 数据，最大长度 为231 - 1 (2,147,483,647) 个字符。第三章 关系数据库标准语言SQL 3.3 数据定义7、Binary Stringsbinary ：固定长度的二进制数据，其最大长度为 8,000 个字节。varbinary ：可变长度的二进制数据，其最大长度 为 8,000 个字节。image ：可变长度的二进制数据，其最大长度为 231 - 1 (2,147,483,647) 个字节。8、创建和删除用户自定义的数据

5、类型 EXEC sp_addtype telephone, varchar(24), NOT NULL EXEC sp_addtype birthday, datetime, NULLEXEC sp_droptype birthdayEXEC sp_droptype telephone第三章 关系数据库标准语言SQL 3.3 数据定义二、创建数据库CREATE DATABASE Students ON PRIMARY( NAME = Students_dat, FILENAME = c:mssql7datastudents.mdf, SIZE = 10MB, MAXSIZE = 50MB, F

6、ILEGROWTH = 5MB )LOG ON( NAME = Students_log, FILENAME = c:mssql7datastudents.ldf, SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB )第三章 关系数据库标准语言SQL 3.3 数据定义三、创建、修改和删除表CREATE TABLE ( ， ， ); 如果完整性约束条件涉及到该表的多个属性列多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。 第三章 关系数据库标准语言SQL 3.3 数据定义1、创建表带实体完整性CREATE TABLE Student(

7、 Sno INT CONSTRAINT PK_Student PRIMARY KEY, - 列级约束 Sname CHAR(8) UNIQUE, Sage TINYINT)上述语句与下面的语句等价CREATE TABLE Student( Sno INT, Sname CHAR(8) UNIQUE, Sage TINYINT , - 下面的是表级约束 CONSTRAINT PK_Student PRIMARY KEY(Sno) 当主码是由多个属性组成时，必须用表级约束第三章 关系数据库标准语言SQL 3.3 数据定义2、创建表带参照完整性 CREATE TABLE Course ( Cno I

8、NT PRIMARY KEY, Cname CHAR(12), Cpno INT FOREIGN KEYREFERENCES Course(Cno), Ccredit TINYINT)这里参照表与被参照表是同一张表SQL 2000 中被参照的表的列上必须有UNIQUE约束或者是主码第三章 关系数据库标准语言SQL 3.3 数据定义CREATE TABLE SC( Sno INT, Cno INT, Grade TINYINT , - 下面的是表级约束 CONSTRAINT PK_SC PRIMARY KEY(Sno,Cno), CONSTRAINT FK_Sno FOREIGN KEY(Sno

9、)REFERENCES Student(Sno), CONSTRAINT FK_Cno FOREIGN KEY(Cno)REFERENCES Course(Cno)本例中也可以用列级约束定义参照完整性本例中定义主码必须用表级约束不能用列级约束第三章 关系数据库标准语言SQL 3.3 数据定义 3、创建表带用户自定义完整性CREATE TABLE Student( Sno INT PRIMARY KEY, Sname CHAR(8) NOT NULL， Sage TINYINT CHECK (Sage=10) ) 4、修改表ALTER TABLE Student ALTER COLUMN Sna

10、me CHAR(20)ALTER TABLE Student ADD Ssex CHAR(2)ALTER TABLE Student DROP COLUMN SsexALTER TABLE Student ADD CONSTRAINT Sname_uni UNIQUE(Sname)ALTER TABLE Student DROP CONSTRAINT Sname_uni 第三章 关系数据库标准语言SQL 3.3 数据定义5、删除表DROP TABLE RESTRICT|CASCADE ;nRESTRICT：删除该表时是有限制的。欲删除的基本表不能被其他表的约束所引用如果存在依赖该表的对象，则此

11、表不能被删除nCASCADE：删除该表时没有限制。在删除基本表的同时，相关的依赖对象一起删除 例如：DROP TABLE Student CASCADE ;基本表定义被删除，数据被删除表上建立的索引、视图、触发器等一般也将被删除 第三章 关系数据库标准语言SQL 3.3 数据定义 DROP TABLE时，SQL99 与 3个RDBMS的处理策略比较序号 标准及主流数据库的处理方式 依赖基本表的对象SQL99Kingbase ESORACLE 9iSQLServer 2000RCRCC1.索引无规定2.视图保留保留保留3.DEFAULT，PRIMARY KEY，CHECK（只含该表的列）NOT

12、NULL 等约束4.Foreign Key5.TRIGGER6.函数或存储过程保留保留保留保留保留R表示RESTRICT , C表示CASCADE 表示不能删除基本表，表示能删除基本表，保留表示删除基本表后，还保留依赖对象 第三章 关系数据库标准语言SQL 3.3 数据定义四、建立和删除索引1、建立索引CREATE UNIQUE CLUSTERED INDEX sno_index ON student(sno) CREATE NONCLUSTERED INDEX sname_indexON student(sname) SQL 2000中对PRIMARY KEY约束缺省建立簇索引 SQL 20

13、00中对UNIQUE约束缺省建立非簇索引 SQL 2000中每个表最多 可有1个簇索引 249个非簇索引2、删除索引DROP INDEX sno_index SQL 2000中本语句不能DROP由PRIMARY KEY或 UNIQUE约束自动创建的索引有关索引更多的信息参见Online Help第三章 关系数据库标准语言SQL 3.4 查询七、小结1、有些查询只能用连接不能用嵌套2、有些查询只能用嵌套不能用连接3、有些查询既可用连接也可用嵌套4、IN子查询一定能用EXISTS子查询代替5、EXISTS子查询不一定能用IN子查询代替6、ANY和ALL子查询不一定能用集函数子查询代替第三章 关系数

14、据库标准语言SQL 3.4 查询八、常见错误1、多表连接时，对不唯一的属性忘记了表名前缀2、WHERE条件中出现统计函数（集函数）3、有HAVING，但没有GROUP BYSELECT COUNT(*) FROM StudentHAVING Sdept=IS4、非GROUP BY中的属性出现在SELECT后面，且未用统计函数SELECT Sno, Cno, AVG(Grade)FROM SCGROUP BY Sno第三章 关系数据库标准语言SQL 3.4 查询5、HAVING中出现没有统计函数的属性SELECT COUNT(*) FROM StudentGROUP BY SdeptHAVING

15、 Sdept=ISSELECT AVG(Grade)FROM SCGROUP BY SnoHAVING Cno16、子查询（还包括视图定义中，导出关系中）中出现ORDER BY子句对本例MS SQL-Server 2000报错报错!对本例MS SQL-Server 2000不报错！不报错！第三章 关系数据库标准语言SQL 3.6 视图 什么是视图 ，视图与基本表的联系与区别从一个或几个基本表(或视图)导出的表，是一个虚表，在数据库中只存放它的定义，不存放对应的数据。视图的数据存放在对应的基表中，若基表的数据发生变化，从视图中查询出的数据也随之发生变化。 使用时与基本表类似一、视图的定义1、建立

16、视图 CREATE VIEW ( , ) AS WITH CHECK OPTION 第三章 关系数据库标准语言SQL 3.6 视图CREATE VIEW IS_S ASSELECT Sno, Sname, Sage, SdeptFROM StudentWHERE Sdept=ISCREATE VIEW IS_S_CHECK ASSELECT Sno, Sname, Sage, SdeptFROM StudentWHERE Sdept=ISWITH CHECK OPTIONCREATE VIEW S_G(Sno,Gavg) ASSELECT Sno, AVG(Grade)FROM SCGROUP

17、 BY Sno行列子集视图分组视图第三章 关系数据库标准语言SQL 3.6 视图2、删除视图 DROP VIEW 例：DROP VIEW IS_S二、查询视图SELECT Sno, SageFROM IS_SWHERE Sage86视图消解后SELECT Sno, SageFROM StudentWHERE Sdept=IS AND Sage86第三章 关系数据库标准语言SQL 3.6 视图三、更新视图对视图的更新最终是转换为对基表的更新UPDATE IS_SSET Sdept=MAWHERE Sno=15002SELECT *FROM StudentUPDATE IS_SSET Sdept=

18、ISWHERE Sno=15003SELECT *FROM Student对本例MS SQL-Server 2000执行成功因为15002原来的系科是IS对本例MS SQL-Server 2000不报错,但15003的系科还是原来的MA第三章 关系数据库标准语言SQL 3.6 视图UPDATE IS_S_CHECKSET Sdept=MAWHERE Sno=15004SELECT *FROM StudentUPDATE S_GSET Gavg=90WHERE Sno=15001不可更新的视图：理论上不可以，该问题至今还没有解决不允许更新的视图：实际的系统不支持，但理论上可能行DB2不允许更新的几种视图：教科书P.124125对本例MS SQL-Server 2000报错,因为定义视图时有WITH CHECK子句对本例MS SQL-Server 2000报错,因为该视图是不允许更新的视图第三章 关系数据库标准语言SQL 3.6 视图INSERT INTO IS_S(Sno, Sname, Sage)VALUE