第 3 章 关系数据库标准语言 SQL

1. 第3章 关系数据库标准语言SQL

2. 3.1 SQL语言的基本概念与特点 3.1.1 语言的发展及标准化 • 在70年代初，E.F.Codd首先提出了关系模型。70年代中期，IBM公司在研制 SYSTEM R关系数据库管理系统中研制了SQL语言，最早的SQL语言（叫SEQUEL2）是在1976 年 11 月的IBM Journal of R&D上公布的。 • 1979年ORACLE公司首先提供商用的SQL，IBM公司在DB2 和SQL/DS数据库系统中也实现了SQL。 • 1986年10月，美国ANSI采用SQL作为关系数据库管理系统的标准语言(ANSI X3. 135-1986），后为国际标准化组织（ISO）采纳为国际标准。

3. 1989年，美国ANSI采纳在ANSI X3.135-1989报告中定义的关系数据库管理系统的SQL标准语言，称为ANSI SQL 89。 • 1992年，ISO又推出了SQL92标准，也称为SQL2。 • 目前SQL99(也称为SQL3)在起草中，增加了面向对象的功能。 • 结构化查询语言SQL(Structured Query Language)是一种介于关系代数与关系演算之间的语言，其功能包括查询、操纵、定义和控制四个方面，是一个通用的、功能极强的关系数据库语言。目前已成为关系数据库的标准语言，广泛应用于各种数据库。

4. SQL 视图1 视图2 外模式 基本表3 模式 基本表4 基本表2 基本表1 存储文件1 存储文件2 内模式 一、关系数据库三级模式结构 3.1.2 SQL语言的基本概念

5. 外模式对应于视图（View）和部分基本表（Base Table）; • 模式对应于基本表; • 内模式对应于存储文件。 • 基本表是本身独立存在的表，在SQL中一个关系就对应一个表。一些基本表对应一个存储文件，一个表可以有若干索引，索引也存放在存储文件中。 • 视图是从基本表或其他视图中导出的表，它本身不独立存储在数据库中，也就是说数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中，因此视图是一个虚表。 • 存储文件的物理结构及存储方式等组成了关系数据库的内模式。存储文件的物理结构及存储方式等不同数据库管理系统往往是不同的，一般也是不公开的。 • 视图和基本表是SQL语言的主要操作对象，用户可以用SQL语言对视图和基本表进行各种操作。在用户眼中，视图和基本表都是关系表，而存储文件对用户是透明的。

6. 3.1.3 SQL语言的主要特点 SQL集数据查询、数据操纵、数据定义 和数据控制功能于一体，主要特点包括： 1、综合统一 SQL集数据定义语言DDL、数据操纵语言DML、数据控制语言DCL的功能于一体，语言风格统一，可以独立完成数据库生命周期中的全部活动，包括： • 定义关系模式，插入数据，建立数据库; • 对数据库中的数据进行查询和更新; • 数据库重构和维护; • 数据库安全性、完整性控制。

7. 非关系数据模型的数据操纵语言是“面向过程”的，用“过程化”语言完成某项请求，必须指定存储路径。非关系数据模型的数据操纵语言是“面向过程”的，用“过程化”语言完成某项请求，必须指定存储路径。 SQL进行数据操作，只要提出“做什么”，而无须指明“怎么做”，因此无需了解存储路径。存储路径的选择以及SQL的操作过程由系统自动完成。这样可以减轻用户的负担，也提高了数据独立性。 2、高度非过程化 3、面向集合的操作方式 非关系数据模型得采用提面向记录的操作方式，操作对象是一条记录。 SQL采用集合操作方式，不仅操作对象、查找结果可以是元组的集合，而且一次插入、删除、更新操作的对象也可以是元组的集合。 4、以同一种语法结构提供多种使用方式

8. 4、以同一种语法结构提供多种使用方式 SQL既是独立的语言，又是嵌入式语言。 作为独立的语言，它能够独立地用于联机交互的使用方式，用户可以在终端键盘上直接键入SQL命令对数据库进行操作; 作为嵌入式语言，SQL语句能够嵌入到高级语言（如C、C++、Java）程序中，供程序员设计程序时使用。 而在两种不同的使用方式下，SQL的语法结构基本上是一致的。 5、语言简洁，易学易用 SQL功能极强，完成核心功能只用了9个动词，接近英语口语，所以容易学习，易于使用。 数据查询数据定义数据操纵数据控制 SELECTCREATE DROP ALTERINSERT UPDATE DELETEGRANT REVOKE

9. 3.2 SQL数据定义 3.2.1 字段数据类型 • 整数数据类型: bigint,int,smallint,tinyint • 精确数值类型: numeric,decimal • 近似浮点数值数据类型: float,real • 日期时间数据类型 : datetime,smalldatetime • 字符串数据类型: char,varchar,text • Unicode字符串数据类型: nchar,nvarchar,ntext • 二进制数据类型: binary、varbinary、image • 货币数据类型: money,smallmoney • 标记数据类型: timestamp,uniqueidentifier具体见书P74－表3.1

10. 3.2.2 创建、修改和删除数据表 1、定义基本表 CREATE TABLE <表名>( <列名> <数据类型> [列级完整性约束条件] [, <列名> <数据类型> [列级完整性约束条件]] … [,<表级完整性约束条件>]) 建表的同时通常还可以定义与该表有关的完整性约束条件，这些完整性约束条件被存入系统的数据字典中，当用户操作表中数据时由DBMS自动检查该操作是否违背这些完整性约束条件。如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。

11. （1）实体完整性 • 主码(Primary Key) • 要求： ① 一个基本表中只能定义一个Primary Key约束; ② 作为Primary Key的任何列不能出现空值; ③ 定义为表级约束的语法格式：[constraint 约束名] Primary Key (列1 [ASC|DESC]［，……n]) • 空值(Null/Not Null) • 要求：该约束只能用于列级约束。 • 唯一值(Unique) • 要求：① 表示某一列或多个列的组合上的取值必须唯一，系统会自动 为其建立唯一索引; ② 定义为表级约束的语法格式：[constraint 约束名] Unique [Clustered|NonClustered] (列1 [ASC|DESC]［，……n])

12. 例1、建立Student表(Sno char(9) 主码、Sname char(20) 不为空、Ssex char(2)、Sage smallint、Sdept char(20)) CREATE TABLE Student ( Sno char(9) PRIMARY KEY,/*在列级定义主码*/ Sname char(20) NOT NULL, Ssex char(2), Sage smallint, Sdept char(20) ) CREATE TABLE Student ( Sno char(9), Sname char(20) NOT NULL, Ssex char(2), Sage smallint, Sdept char(20),/*在表级定义主码*/PRIMARY KEY (Sno))

13. 例2、建立SC表( Sno char(9) 不为空、Cno char(4) 不为空、Grade Smallint)，将Sno,Cno属性组定义为主码。 CREATE TABLE SC ( Sno char(9) NOT NULL, Cno char(4) NOT NULL, Grade smallint,PRIMARY KEY (Sno,Cno) /*只能在表级定义主码*/)

14. （2）参照完整性 通过FOREIGN KEY短语定义哪些列为外码，用REFERENCES短语指明这些外码参照哪些表的主码。 表级约束的语法格式为：[constraint 约束名] Foreign Key (子表列1,……n) References 主表名 (主表列1,……n) [ON DELETE {CASCADE|NO ACTION}] [ON UPDATE {CASCADE|NO ACTION}] 其中：CASCADE 是级联操作; NO ACTION 是拒绝操作。

15. 例1、建立SC表( Sno char(9) 不为空、Cno char(4) 不为空、Grade Smallint)，(Sno,Cno)是主码，Sno和Cno分 别参照引用Student表的主码和Course表的主码。 CREATE TABLE SC ( Sno char(9) NOT NULL, Cno char(4) NOT NULL, Grade smallint,PRIMARY KEY (Sno,Cno)/*在表级上定义实体完整性*/FOREIGN KEY (Sno) REFERENCES Student (Sno),/*在表级上定义参照完整性*/FOREIGN KEY (Cno) REFERENCES Course(Cno))

16. 例2、显示说明参照完整性的违约处理示例。 CREATE TABLE SC( Sno char(9) NOT NULL, Cno char(4) NOT NULL, Grade smallint , PRIMARY KEY(Sno,Cno), Foreign Key (Sno) References Student(Sno) ON DELETE CASCADE ON UPDATE CASCADE, Foreign Key (Cno) References Course(Cno) ON DELETE NO ACTION/*当删除Course表中的元组造成了与SC表不一致时拒绝删除*/ON UPDATE CASCADE/*当更新Course表中的Cno时，级连更新SC表中相应的元组*/)

17. （3）用户定义完整性 Check可用于定义用户自定义的完整性约束规则。语法格式为： [Constraint 约束名] CHECK (条件) 例1、建立Student表，要求Ssex列只允许取“男”或“女”。 CREATE TABLE Student ( Sno char(9) PRIMARY KEY,/*在列级定义主码*/ Sname char(20) NOT NULL,Ssex char(2) CHECK (Ssex IN (‘男’,’女’)), Sage smallint, Sdept char(20) )

18. 例2、建立SC表，要求Grade的值应该在0和100之间。例2、建立SC表，要求Grade的值应该在0和100之间。 CREATE TABLE SC ( Sno char(9) NOT NULL, Cno char(4) NOT NULL,Grade smallint CHECK (Grade>=0 and Grade<=100),PRIMARY KEY (Sno,Cno) /*只能在表级定义主码*/) 例3、建立Student表，要求当学生的性别是男时，其名字不能以Ms.打头。 CREATE TABLE Student ( Sno char(9), Sname char(20) NOT NULL, Ssex char(2), Sage smallint, Sdept char(20)，PRIMARY KEY (Sno),CHECK (Ssex=‘女’ OR Sname NOT LIKE ‘Ms.%’ ) )

19. （4）完整性约束的命名 SQL在CREATE TABLE语句中提供了完全性约束命名子句Constraint，用来对完整性约束条件命名。从而可以灵活地增加、删除一个完整性约束条件。 例、建立学生登记表Student，要求学号在90000～99999之间，姓名不能取空值，年龄小于30,性别只能是“男”或“女”，主码为Sno。 CREATE TABLE Student ( Sno numeric(6)CONSTRAINT C1 Check (Sno BETWEEN 90000 AND 99999), Sname char(20) NOT NULL, Ssex char(2)CONSTRAINT C2 Check (Ssex IN (‘男’,’女’)) Sage smallint CONSTRAINT C3 Check (Sage<30), Sdept char(20)，Constraint StuentKey PRIMARY KEY (Sno) )

20. 完整性示例 S “学生-课程”数据库中包括三个表：

21. C

22. SC

23. 例1、建立一个“学生”表S，它由学号SNO、姓名SN、性别SEX、年龄AGE、所在系DEPT五个属性组成，其中学号属性为主键，姓名、年龄与性别不为空，假设姓名没有唯一并建立惟一索引，并且性别只能在“男”与“女”中选一个，年龄不能小于0。例1、建立一个“学生”表S，它由学号SNO、姓名SN、性别SEX、年龄AGE、所在系DEPT五个属性组成，其中学号属性为主键，姓名、年龄与性别不为空，假设姓名没有唯一并建立惟一索引，并且性别只能在“男”与“女”中选一个，年龄不能小于0。 CREATE TABLE S ( SNO CHAR(5) PRIMARY KEY, SN VARCHAR(8) NOT NULL, SEX CHAR(2) NOT NULL CHECK (SEX IN ('男','女')), AGE INT NOT NULL CHECK (AGE>0), DEPT VARCHAR(20), CONSTRAINT SN_U UNIQUE(SN) ) 返回本节首页

24. CREATE TABLE C ( CNO CHAR(5) NOT NULL PRIMARY KEY, CN VARCHAR(20), CT INT CHECK (CT>=1) ) [例2] 建立“课程”表C，它由课程号（CNO）、课程名（CN）、学分（CT）三个属性组成。CNO为该表主键，学分大于等于1。 [例3] 建立“选修”关系表SC，定义SNO,CNO为SC的外部键，(SNO,CNO)为该表的主键。 CREATE TABLE SC ( SNO CHAR(5) NOT NULL CONSTRAINT S_F FOREIGN KEY REFERENCES S(SNO), CNO CHAR(5) NOT NULL, SCORE NUMERIC(3), CONSTRAINT S_C_P PRIMARY KEY (SNO,CNO), CONSTRAINT C_F FOREIGN KEY(CNO) REFERENCES C(CNO) )

25. 练习：建立如下表 Student(Sno,Sname,Ssex,Sage,Sdept) 要求：Sno为主码，Sage在15～45间(包括15和45)，Ssex只能取男或女，默认值为男。 Course(Cno,Cname,Cpno,Ccredit)要求：Cno为主码。 Sc(Sno,Cno,Grade)要求：Sno和Cno为主码，利用Sno建立与表Student的关联、Cno建立与表Course的关联。Grade只能接收空值或0～100(包括0和100)的值。

26. CREATE TABLE Student ( Sno CHAR(5) NOT NULL PRIMARY KEY, Sname VARCHAR(8), Ssex CHAR(2) DEFAULT ‘男’ CHECK (Ssex IN (‘男’,‘女’)), Sage SMALLINT CHECK(Sage>=15 AND Sage<=45), Sdept CHAR(2) ) CREATE TABLE Course ( Cno CHAR(2) NOT NULL PRIMARY KEY, Cname VARCHAR(20), Cpno CHAR(2), Ccredit SMALLINT) CREATE TABLE Sc ( Sno CHAR(5) NOT NULL FOREIGN KEY REFERENCES Student(Sno), Cno CHAR(2) NOT NULL, Grade SMALLINT CHECK((Grade IS NULL) OR (Grade BETWEEN 0 AND 100)), PRIMARY KEY(Sno,Cno), FOREIGN KEY(Cno) REFERENCES Course(Cno))

27. 2、修改基本表 ALTER TABLE 表名 ALTER COLUMN 列名 新类型 [ (长度 [ ,小数位 ] ) ][ NULL | NOT NULL ]] ADD 新列名 AS 表达式或别名[ ,...n ] [ WITH CHECK | WITH NOCHECK ] ADD 完整性约束 DROP [ CONSTRAINT ] 约束名 | COLUMN 列名 } [ ,...n ] [CHECK | NOCHECK] CONSTRAINT { 约束名 [ ,...n ] } 其中:<表名> 指定需要修改的基本表， ADD子句用于增加新列和新的完整性约束条件， DROP子句用于删除指定的完整性约束条件或原有列， ALTER子句用于修改原有的列定义。 {CHECK|NOCHECK}CONSTRAINT指定启用或禁用 constraint_name。如果禁用，将来插入或更新该列时将不用该约束条件进行验证。此选项只能与 FOREIGN KEY 和 CHECK 约束一起使用。

28. [例4]向S表增加“入学时间”列，其数据类型为日期型。[例4]向S表增加“入学时间”列，其数据类型为日期型。 ALTER TABLE S ADD SCOME DATETIME [例5] 将年龄的数据类型改为半字长整数。 ALTER TABLE S ALTERCOLUMN AGE SMALLINT [例6]删除例4增加的“入学时间”列。 ALTER TABLE S DROPCOLUMN SCOME [例7]禁止SC中的参照完整性C_F。 ALTER TABLE S NOCHECK CONSTRAINT C_F

29. 练习： • 对学生表进行如下操作： • 将姓名列的长度先改为6，不允许有空值。 • 增加两列，性别(char(2)) 电话(varchar(11))； • 再将新建的两列删除。 alter table学生alter column 姓名 char(6) not nullgo alter table学生add 性别 char(2),电话 varchar(11)go alter table学生drop column 性别,电话go

30. 例: 1) 为xs表添加主键约束,该主键约束由学号单列组成,约束 名为pk_xh. 2) 修改xs表的主键约束,该主键约束由学号和姓名两列 组成,约束名为pk_xh_xm. 1)) ALTER TABLE xs ADD CONSTRAINT pk_xh PRIMARY KEY (学号) 2) ALTER TABLE xs DROP CONSTRAINT pk_xh GO ALTER TABLE xs ADD CONSTRAINT pk_xh_xm PRIMARY KEY(学号,姓名)

31. 例:为选课表添加外键约束,约束名为FK_kcbh,使之与课程表建 立级联删除和级联更新操作. ALTER TABLE选课表 ADD CONSTRAINT FK_kcbh FOREIGN KEY (课程编号) REFERENCES课程表(课程编号)ON DELETE CASCADEON UPDATE CASCADE UPDATE课程表 SET课程编号='1111' WHERE课程编号='0001' DELETE 课程表 WHERE 课程编号='1111' ALTER TABLE选课表 DROP CONSTRAINT FK_kcbh

32. 例:为选课表添加外键约束,约束名为FK_xh,使之与学生表建 立级联删除和级联更新操作. ALTER TABLE选课表 ADD CONSTRAINT FK_xh FOREIGN KEY (学号) REFERENCES学生表(学号)ON DELETE CASCADEON UPDATE CASCADE ALTER TABLE选课表 DROP CONSTRAINT FK_xh

33. 例：1）建立XS表，包括学号 char(2) 姓名 char(6)，同时以学 号建立唯一性约束，约束名为UK_xh. 2）为XS表以姓名建立唯一性约束，约束名为UK_xm。3）删除XS表中的UK_xh的唯一性约束。 CREATE TABLE XS (学号 char(2) CONSTRAINT UK_xh UNIQUE, 姓名 char(6)) ALTER TABLE XS ADDCONSTRAINT UK_xm UNIQUE(姓名) ALTER TABLE XS DROP CONSTRAINT UK_xh

34. 例：建立score表，包括学号char(4) 课号char(1) 分数 decimal(5,2)，为分数建立检查约束，约束名为CK_cj，要求分数只能在0~100间 CREATE TABLE score (学号 char(4), 课号 char(1), 分数 decimal(5,2) CONSTRAINT CK_cj CHECK (分数>=0 and 分数<=100)) INSERT score VALUES('0001','1',120) ALTER TABLE score DROP CONSTRAINT CK_cj ALTER TABLE score ADD CONSTRAINT CK_fs CHECK (分数>=0 and 分数<=150)

35. 8.4.5 默认约束 例 员工表employee的sex列添加默认约束，默认值是“男”。 ALTER TABLE employee ADD CONSTRAINT sex_default DEFAULT ‘男’FOR sex 更改表employee为hire_date列定义默认约束。 • ALTER TABLE employee • ADD CONSTRAINT hire_date_df DEFAULT getdate( ) FOR hire_date

36. 3、删除基本表 DROP TABLE <表名> [例8]删除S表。 DROP TABLE S

37. 3.2.2 设计、创建和维护索引 1、索引的概念 • 建立索引是加快查询速度的有效手段。可在基本表上建立一个或多个索引，以提供多种存取路径，加快查询速度。 • 系统在存取数据时会自动选择合适的索引作为存取路径，用户不必也不能显式地选择索引。 • 按照索引记录的存放位置不同，可分为聚集索引(Clustered Index)与非聚集索引(Non-Clustered Index)两类。 • 聚集索引是指索引项的顺序与表中记录的物理顺序一致的索引组织； • 非聚集索引按照索引的字段排列记录，但是排列的结果并不会存储在表中，而是另外存储。在检索记录时，聚集索引会比非聚集索引速度快，一个表中只能有一个聚集索引，而非聚集索引可以有多个。

38. 2、创建索引 CREATE[UNIQUE] [CLUSTERED|NONCLUSTERED ]INDEX <索引名> ON <表名> | <视图名>(<列名> [ ASC | DESC ] [ ,...n ] ) [例9] 为学生-课程数据库中的S、C、SC三个表建立索引。其中S表按学号升序建惟一索引，C表按课程号降序建立聚簇索引，SC表按学号升序和课程号降序建非聚簇索引。 • CREATE UNIQUE INDEX S_SNO ON S(SNO) • CREATE CLUSTERED INDEX C_CNO ON C(CNO DESC) • CREATE NONCLUSTERED INDEX SC_SNO_CNO ON SC(SNO ASC,CNO DESC)

39. 1、按employee表的employee_name列建立非聚集索引。 CREATE NONCLUSTERED INDEX name_idx ON employee(employee_name) 2、在学生表上创建一个名为学号_index的唯一性聚簇索 引，索引关键字为学号，降序。 • CREATE UNIQUE CLUSTERED • INDEX学号_index • ON 学生表 (学号DESC ) 3、在选课表上创建一个名为学号_课号_index的非聚簇复合索引，索引关键字为学号,课号，升序 • CREATE NONCLUSTERED INDEX学号_课号_index • ON学生表 (学号 ASC ,课号 ASC)

40. 3、删除索引 删除索引的命令语法: DROP INDEX <索引名> [例10]删除S表的S_SNO索引。 DROP INDEX S_SNO

41. 3.3 SQL数据查询 3.3.1 SELECT命令的格式及其含义 SELECT [ALL|DISTINCT] 目标列表达式[,目标列表达式]... [INTO 新表名] FROM 表名或视图名[,表名或视图名] ... [WHERE 条件表达式] [GROUP BY 列名1 [HAVING 条件表达式]] [ORDER BY 列名2 [ASC|DESC]] ...

42. 3.3.2 单表查询 1、查询指定列 • [例11]查询全体学生的学号与姓名。 SELECT SNO,SN FROM S • [例12]查询全体学生的姓名、学号、所在系。 SELECT SN,SNO,DEPT FROM S 在Select子句中给出包含所选字段的一个列表，各个字段之间用逗号分隔，字段的次序可以任意指定。

43. 如在From子句中指定了两个表，而这两个表中又有同名的字段，使用这些字段时就应在其字段名前冠以表名。如在From子句中指定了两个表，而这两个表中又有同名的字段，使用这些字段时就应在其字段名前冠以表名。 USE JWGL Selectstudent.student_id, student_name,monitor From student,class Where class.class_id=student.class_id

44. 2、查询全部列 [例13]查询全体学生的详细记录。 SELECT * FROM S 等价于： SELECT SNO,SN,SEX,AGE,DEPT FROM S

45. 3、查询经过计算的列 SELECT子句的<目标列表达式>不仅可以是表中的属性列，也可以是有关表达式。 [例14]查全体学生的姓名及其出生年份。 SELECT SN, 2005-AGE FROM S 输出的结果为： SN -------- --------- 李涛 1986 王林 1987 陈高 1984 张杰 1988 吴小丽 1986 徐敏敏 1985

46. [例15]查全体学生的姓名、出生年份和所在系，要求用小写字母表示所有系名。[例15]查全体学生的姓名、出生年份和所在系，要求用小写字母表示所有系名。 SELECT SN, ‘出生年份:’,2005-AGE,lower(DEPT) FROM S 输出的结果为： SN -------- ------------- --------- --------- 李涛 出生年份: 1986 信息 王林 出生年份: 1987 计算机 陈高 出生年份: 1984 自动化 张杰 出生年份: 1988 自动化 吴小丽 出生年份: 1986 信息 徐敏敏 出生年份: 1985 计算机

47. 设置字段别名： 显示选择查询的结果时，第一行（即表头）中显示的是各个输出字段的名称。为了便于阅读，也可指定更容易理解的字段名来取代原来的字段名。设置别名的方法： 原字段名 字段别名 原字段名 AS 字段别名 字段别名 = 原字段名

48. 用户可以通过别名来改变查询结构的列标题，这对于含自述表达式、函数名的目标列表达式尤为重要。用户可以通过别名来改变查询结构的列标题，这对于含自述表达式、函数名的目标列表达式尤为重要。 上例可以定义如下： SELECT SN, ‘出生年份:’ BIRTH , BIRTHDAY =2005-AGE, lower(DEPT) AS DEPARTMENT FROM S 输出的结果为： SN BIRTH BIRTHDAY DEPARTMENT -------- ------------- --------------- ---------------------- 李涛 出生年份: 1986 信息 王林 出生年份: 1987 计算机 陈高 出生年份: 1984 自动化 张杰 出生年份: 1988 自动化 吴小丽 出生年份: 1986 信息 徐敏敏 出生年份: 1985 计算机

49. 3.3.3 WHERE子句的基本使用 1. 消除取值重复的行 2. 指定WHERE查询条件 返回本节首页

50. 消除取值重复的行 该查询结果里包含了许多重复的行。如果想去掉结果表中的重复行，必须指定DISTINCT短语： SELECTDISTINCT SNO FROM SC 执行结果为： SNO ---- S1 S2 S3 S4 S5 [例16]查所有选修过课的学生的学号。 SELECT SNO FROM SC 结果为： SNO ---- S1 S1 S2 S2 S2 S3 S3 S3 S3 S4 S4 S4 S5