西华师范大学计算机学院

1. 西华师范大学计算机学院 第三章 关系数据库标准语言SQL

2. 第三章关系数据库标准语言SQL 3.1 SQL概述 3.2 数据定义 3.3 查询 3.4 数据更新 3.5 视图 3.6 数据控制 3.7 嵌入式SQL 3.8 小结

3. SQL的产生与发展 • 1970年，美国IBM研究中心的E.F.Codd连续发表多篇论文，提出关系模型。1972年，IBM公司开始研制实验型关系数据库管理系统SYSTEM R，配制的查询语言称为SQUARE (Specifying Queries As Relational Expression )语言，在语言中使用了较多的数学符号。1974年，Boyce和Chamberlin把SQUARE修改为SEQUEL (Structured English QUEry Language )语言。后来SEQUEL简称为SQL (Structured Query Language )，即“结构式查询语言”，SQL的发音仍为“sequel”。 • 1986年10月美国国家标准局（ANSI）数据库委员会批准了SQL作为关系数据库语言的美国标准。 同年公布了SQL标准文本（简称SQL-86）。 1987年6月， 国际标准化组织（ISO）将其采纳为国际标准。 在此之后， 对SQL进行修改和扩充的标准化工作不断进行着， 相继出现了SQL-89、 SQL2（1992）和SQL3（1999）。

4. SQL数据库的体系结构 • 从图中可以看出，模式与基本表相对应，外模式与视图相对应，内模式对应于存储文件。基本表和视图都是关系。

5. 视图与基本表 1．基本表（Base Table） 基本表是模式的基本内容。每个基本表都是一个实际存在的关系。 2．视图（View) 视图是外模式的基本单位，用户通过视图使用数据库中基于基本表的数据（基本表也可作为外模式使用）。一个视图虽然也是一个关系，但是它与基本表有着本质的区别。任何一个视图都是从已有的若干关系导出的关系，它只是逻辑上的定义，实际并不存在。在导出时，给出一个视图的定义（从哪几个关系中，根据什么标准选取数据，组成一个什么

6. 视图与基本表（续） 名称的关系等），此定义存放在数据库（数据字典）中，但没有真正执行此定义（并未真正生成此关系）。当使用某一视图查询时，将实时从数据字典中调出此视图的定义；根据此定义以及现场查询条件，从规定的若干关系中取出数据，组织成查询结果，展现给用户。 因此，视图是虚表，实际并不存在，只有定义存放在数据字典中。 当然，用户可在视图上再定义视图，就像在基本表上定义视图一样，因为视图也是关系。

7. 3．存储文件 存储文件是内模式的基本单位。每一个存储文件存储一个或多个基本表的内容。一个基本表可有若干索引，索引也存储在存储文件中。存储文件的存储结构对用户是透明的。 下面将介绍SQL的基本语句。各厂商的RDBMS实际使用的SQL语言，与标准SQL语言都有所差异及扩充。因此，具体使用时，应参阅实际系统的有关手册。

8. 3.1 SQL概述 • SQL的特点 • 1. 综合统一 • 2. 高度非过程化 • 3. 面向集合的操作方式 • 4. 以同一种语法结构提供两种使用方法 • 5. 语言简洁，易学易用

9. 5. 语言简捷，易学易用

10. 第三章关系数据库标准语言SQL 3.1 SQL概述 3.2 数据定义 3.3 查询 3.4 数据更新 3.5 视图 3.6 数据控制 3.7 嵌入式SQL 3.8 小结

11. 3.2 数 据 定 义

12. 3.2.1 表定义语句格式 CREATE TABLE <表名> （<列名> <数据类型>[ <列级完整性约束条件> ] [，<列名> <数据类型>[ <列级完整性约束条件>] ] … [，<表级完整性约束条件> ] ）； • <表名>：所要定义的基本表的名字。在一个数据库中，不允许有两个基本表同名（应该更严格的说，任何两个关系都不能同名，包括视图)。 • <列名>：组成该表的各个属性（列）。一个表中不能有两列同名。

13. <类型>：规定了该列的数据类型。各具体DBMS所提供的数据类型是不同的。但下面的数据类型几乎都是支持的：<类型>：规定了该列的数据类型。各具体DBMS所提供的数据类型是不同的。但下面的数据类型几乎都是支持的： INT或INTEGER 全字长二进制整数 SMALLINT 半字长二进制整数 DEC(p［,q］) 压缩十进制数，共p位，其中小数点后有q位，

14. FLOAT 双字长的浮点数 CHAR(n)或CHARTER(n) 长度为n的定长字符串 VARCHAR(n) 最大长度为n的变长字符串 DATE 日期型，格式为YYYY―MM―DD TIME 时间型，格式为HH.MM.SS TIMESTAMP 日期加时间

15. <列的完整性约束>：该列上数据必须符合的条件。<列的完整性约束>：该列上数据必须符合的条件。 • <表级完整性约束>：对整个表的一些约束条件，常见的有定义主码(外码)，各列上数据必须符合的关联条件等。 • SQL只要求语句的语法正确就可以，对格式不作特殊规定，不区分大小写。一条语句可以放在多行上，字和符号间有一个或多个空格分隔。一般每个列定义单独占一行(或数行)，每个列定义中相似的部分对齐(这不是必须的)，从而增加可读性，一目了然。

16. 例题 [例1] 建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。 CREATE TABLE Student (Sno CHAR(5) NOT NULL UNIQUE， Sname CHAR(20) UNIQUE， Ssex CHAR(1) ， Sage INT， Sdept CHAR(15))；

17. 定义基本表（续） • 常用完整性约束 • 主码约束： PRIMARY KEY • 唯一性约束：UNIQUE • 非空值约束：NOT NULL • 参照完整性约束: FOREIGN KEY PRIMARY KEY与UNIQUE的区别？ 在一个表中只能定义一个PRIMARYKEY，不允许空值；而UNIQUE可定义多个，且允许空值

18. 例题 （续） • [例2] 建立一个“学生选课”表SC，它由学号Sno、课程号Cno，修课成绩Grade组成，其中(Sno, Cno)为主码。 • CREATE TABLE SC( • Sno CHAR(5) , • Cno CHAR(3) , • Grade int, • Primary key (Sno, Cno));

19. 例题 （续）

20. [例] 创建职工表。 CREATE TABLE Employee (Eno CHAR(4) NOT NULL UNIQUE Ename CHAR(8), Sex CHAR(2), Age INT, Marry CHAR(1), Title CHAR(6), Dno CHAR(2)); • 执行后，数据库中就新建立了一个名为Employee的表，此表尚无元组(即为空表)。此表的定义及各约束条件都自动存放进数据字典。

21. 二、修改基本表 ALTER TABLE <表名> [ ADD <新列名> <数据类型> [ 完整性约束 ] ] [ DROP <完整性约束名> ] [ MODIFY <列名> <数据类型> ]； • <表名>：要修改的基本表 • ADD子句：增加新列和新的完整性约束条件 • DROP子句：删除指定的完整性约束条件 • MODIFY子句：用于修改列名和数据类型

22. 例题 • 例 在基本表S中增加一个地址（ADDRESS）列，可用下列语句： ALTER TABLE S ADD ADDRESS VARCHAR（30）； 【例】在学校School表中增加“联系电话”列， 数据类型为字符型， 长度为15。 ALTER TABLE School ADD Phone CHAR(15) • 注意： 新增加的列的约束不能定义为“NOT NULL”， 因为基本表在增加一列后， 原有元组在新增加的列上的值都被定义为空值（NULL）。

23. 语句格式（续） • 删除属性列 直接/间接删除 • 把表中要保留的列及其内容复制到一个新表中 • 删除原表 • 再将新表重命名为原表名 直接删除属性列:(新) 例：ALTER TABLE Student Drop Scome； • 例 在基本表S中删除年龄（AGE）列，并且把引用该列的所有视图和约束也一起删除，可用下列语句： ALTER TABLE S DROP AGE CASCADE;

24. [例3] 将年龄的数据类型改为半字长整数。 ALTER TABLE Student MODIFY Sage SMALLINT； • 例 在基本表S中S# 的长度修改为6，可用下列语句： ALTER TABLE S MODIFY S# CHAR（6）； • 注：修改原有的列定义有可能会破坏已有数据

25. 例题 [例4] 删除学生姓名必须取唯一值的约束。 ALTER TABLE Student DROP UNIQUE(Sname)；

26. 三、删除基本表 DROP TABLE <表名>; 基本表删除 数据、表上的索引都删除 表上的视图往往仍然保留，但无法引用 删除基本表时，系统会从数据字典中删去有关该 基本表及其索引的描述

27. 例题 [例5] 删除Student表 DROP TABLEStudent ;

28. 3.2.2 建立与删除索引 • 建立索引是加快查询速度的有效手段 • 建立索引 • DBA或表的属主（即建立表的人）根据需要建立 • 有些DBMS自动建立以下列上的索引 • PRIMARY KEY • UNIQUE • 维护索引 • DBMS自动完成 • 使用索引 • DBMS自动选择是否使用索引以及使用哪些索引

29. 一、建立索引 • 语句格式 CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名>(<列名>[<次序>][,<列名>[<次序>] ]…)； • 用<表名>指定要建索引的基本表名字 • 索引可以建立在该表的一列或多列上，各列名之间用逗号分隔 • 用<次序>指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASC • UNIQUE表明此索引的每一个索引值只对应唯一的数据记录 • CLUSTER表示要建立的索引是聚簇索引 • 本语句建立索引的排列方式为：首先以第一个列的值排序；该列值相同的记录，按下一列名的值排序；以此类推。

30. 例题 [例6] 为学生-课程数据库中的Student，Course，SC三个表建立索引。其中Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。 CREATE UNIQUE INDEX Stusno ON Student(Sno)； CREATE UNIQUE INDEX Coucno ON Course(Cno)； CREATE UNIQUE INDEX SCno ON SC(Sno ASC，Cno DESC)；

31. 建立索引 （续） • 唯一值索引 • 对于已含重复值的属性列不能建UNIQUE索引 • 对某个列建立UNIQUE索引后，插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。这相当于增加了一个UNIQUE约束

32. 建立索引 （续） • 聚簇索引 • 建立聚簇索引后，基表中数据也需要按指定的聚簇属性值的升序或降序存放。也即聚簇索引的索引项顺序与表中记录的物理顺序一致 例： CREATE CLUSTER INDEX Stusname ON Student(Sname)； 在Student表的Sname（姓名）列上建立一个聚簇索引，而 且Student表中的记录将按照Sname值的升序存放

33. 建立索引 （续） • 在一个基本表上最多只能建立一个聚簇索引 • 聚簇索引的用途：对于某些类型的查询，如经常执行对某些列的查询，可提高查询效率 • 聚簇索引的适用范围 • 很少对基表进行增删操作 • 很少对其中的变长列进行修改操作

34. 二、删除索引 DROP INDEX <索引名>； • 删除索引时，系统会从数据字典中删去有关该索引的描述。 [例7] 删除Student表的Stusname索引。 DROP INDEX Stusname；

35. 3.3 查 询 3.3.1 概述 3.3.2 单表查询 3.3.3 连接查询 3.3.4 嵌套查询 3.3.5 集合查询 3.3.6 小结

36. SELECT—FROM—WHERE句型 在关系代数中最常用的式子是下列表达式： πA1,…,An(σF(R1×…×Rm)) 这里R1、…、Rm为关系，F是公式，A1、…、An为属性。 针对上述表达式，SQL为此设计了SELECT—FROM—WHERE句型： SELECT A1，…，An FROM R1，…，Rm WHERE F 这个句型是从关系代数表达式演变来的，但WHERE子句中的条件表达式F要比关系代数中公式更灵活。

37. 3.3.1 概述 • 语句格式 SELECT [ALL|DISTINCT] <目标列表达式> [，<目标列表达式>] … FROM <表名或视图名>[， <表名或视图名> ] … [ WHERE <条件表达式> ] [ GROUP BY <列名1> [ HAVING <条件表达式> ] ] [ ORDER BY <列名2> [ ASC|DESC ] ]；

38. 语句格式 • SELECT子句：指定要显示的属性列 • FROM子句：指定查询对象(基本表或视图) • WHERE子句：指定查询条件 • GROUP BY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中使用集函数。 • HAVING短语：筛选出只有满足指定条件的组 • ORDER BY子句：对查询结果表按指定列值的升序或降序排序

39. 整个语句的执行过程如下： （1）先构造from子句中关系（如基本表或视图）的笛卡儿积。 （2）根据where 子句中的条件表达式进行关系代数的选择运算，即选取满足WHERE子句中给出的条件表达式的元组。 （3）按SELECT子句中给出的列名或列表达式求值输出。 （4）按GROUP子句中指定列的值分组，同时提取满足HAVING子句中组条件表达式的那些组。 （5）ORDER子句对输出的目标表进行排序，按附加说明ASC升序排列，或按DESC降序排列。

40. WHERE 子句中的条件表达式中常使用下列操作符： • 算术比较运算符： =（等于）, ＜（小于）, ＞（大于）, ＜=（小于等于）, ＞=（大于等于）, !=或＜＞（不等于）。 • 逻辑运算符： AND（与）， OR（或）， NOT（非）。 • 合运算符： UNION（并）, INTERSECT（交） , EXCEPT（差）。 • 集合成员资格运算符： IN, NOT IN。 • 谓词： EXISTS（存在量词）, ALL, ANY, UNIQUE。 • 聚合函数： AVG（平均值）， MIN（最小值）MAX（最大值）， SUM（和）， COUNT(计数) • 确定范围： BETWEEN AND， NOT BETWEEN AND。 • 字符匹配： LIKE， NOT LIKE。 • 空值： IS NULL， IS NOT NULL。

41. 示例 • 例 对教学数据库的基本表S、SC、C中数据进行查询和计算。 ①统计每一年龄选修课程的学生人数 SELECT AGE，COUNT（DISTINCT S.S#） FROM S，SC WHERE S.S#=SC.S# GROUP BY AGE； 由于要统计每一个年龄的学生人数，因此要把满足WHERE子句中条件的查询结果按年龄分组，在每一组中的学生年龄相同。此时的SELECT子句应对每一组分开进行操作，在每一组中，年龄只有一个值，统计的人数是这一组中的学生人数。

42. 示例 • ②求基本表S中男同学的每一年龄组（超过50人）有多少人？要求查询结果按人数升序排列，人数相同按年龄降序排列。 SELECT AGE，COUNT（S#）NUMBER FROM S WHERE SEX='M' GROUP BY AGE HAVING COUNT（*）> 50 ORDER BY NUMBER，AGE DESC；

43. 示例数据库 学生-课程数据库 • 学生表：Student(Sno，Sname，Ssex，Sage，Sdept) • 课程表：Course(Cno，Cname，Cpno，Ccredit) • 学生选课表：SC(Sno，Cno，Grade)

44. 3.3 查 询 3.3.1 概述 3.3.2 单表查询 3.3.3 连接查询 3.3.4 嵌套查询 3.3.5 集合查询 3.3.6 小结

45. 3.3.2 单表查询 查询仅涉及一个表，是一种最简单的查询操作 一、选择表中的若干列（即是对表作投影运算） 二、选择表中的若干元组 三、对查询结果排序 四、使用集函数 五、对查询结果分组

46. 查询指定列 [例1] 查询全体学生的学号与姓名。 SELECT Sno，Sname FROM Student； [例2] 查询全体学生的姓名、学号、所在系。 SELECT Sname，Sno，Sdept FROM Student； 【例】查询所有学校的代码和计划招生人数。 SELECT Sccode， Plnum FROM School

47. 查询全部列 [例3] 查询全体学生的详细记录。 SELECT Sno，Sname，Ssex，Sage，Sdept FROM Student； 或 SELECT * FROM Student； 这里“*”号代表所有列。

48. 【例】 查询全体考生来自哪些民族。 SELECT DISTINCT Nation FROM Examinee 由于许多考生来自同一民族（如汉族）， 所以查询结果有重复项。 本例SELECT子句中加上了DISTINCT短语（缺省为ALL）， 即可消除重复项。

49. 3. 查询经过计算的值 SELECT子句的<目标列表达式>为表达式 • 算术表达式 • 字符串常量 • 函数 • 列别名 • 等

50. 3. 查询经过计算的值 [例4] 查全体学生的姓名及其出生年份。 SELECT Sname，2005-Sage FROM Student； 输出结果： Sname 2005-Sage --------- ------------- 李勇 1976 刘晨 1977 王名 1978 张立 1978