数据库系统课件：第3章 关系数据库标准语言

1、第3章 关系数据库标准语言本章重要概念（1）SQL语言简介：SQL发展历史与特点、组成结构（2）SQL的数据定义：SQL模式、基本表和索引的创建和撤销。（3）SQL的数据查询；SELECT语句的句法，SELECT语句的三种形式及各种限定，基本表的联接操作，SQL3中的递归查询。本章重要概念（4）SQL的数据操纵：添加、更新和删除记录。（5）SQL数据控制：对数据库用户或角色授权、收权（6）视图管理：创建、修改和删除视图，对视图更新操作的限制。（7）嵌入式SQL：运行环境，使用规定，使用技术，卷游标，动态SQL语句。本章内容概述SQL全称是“结构化查询语言(Structured Query La

2、nguage)”，是一种通用的功能极强的关系数据库标准语言。 SQL语言具有结构简洁，功能强大，简单易学，被确定为关系数据库系统的国际标准。本章详细介绍SQL的核心部分内容：数据定义、数据查询、数据更新和嵌入式SQL。 3.1 SQL语言简介SQL发展历史与特点SQL语言特点3.1.1 SQL语言简介SQL语言是当前最为成功、应用最为广泛的关系数据库语言，其发展主要经历了以下几个阶段：1) 1974年由CHAMBERLIN和BOYEE提出，当时称为SEQUEL(STUCTURED ENGLISH QUERY LANGUAGE)；2) IBM公司对其进行了修改，并用于其SYSTEM R关系数据库

3、系统中；3) 1981年 IBM推出其商用关系关系数据库SQL/DS，并将其名字改为SQL，由于SQL语言功能强大，简洁易用，因此得到了广泛的使用； 4)今天广泛应用于各种大型数据库，如SYBASE、Microsoft SQL Server、 ORACLE、DB2等，也用于各种小型数据库，如FOXPRO、ACCESS。3.1.1 SQL语言简介SQL语言特点非过程化语言 SQL是一个非过程化的语言，因为它一次处理一个记录，对数据提供自动导航。SQL允许用户在高层的数据结构上工作，而不对单个记录进行操作，可操作记录集。统一的语法结构 SQL为许多任务提供了命令，包括： 查询数据 在表中插入、修改

4、和删除记录 建立、修改和删除数据对象 控制对数据和数据对象的存取 保证数据库一致性和完整性 是所有关系数据库的公共语言3.1.2 SQL的组成结构 SQL 事实上是操作和检索关系型数据库的标准语言，它允许程序员和数据库管理员做如下的工作：更改数据库的结构；更改系统的安全设置；增加用户对数据库或表的许可权限；在数据库中检索需要的信息；对数据库的信息进行更新；1.数据定义语言(Data Definition Language，简称DDL)，DDL用于定义数据库的逻辑结构，是对关系模式一级的定义，包括基本表、视图及索引的定义。2. 数据查询语言(Data Query Language，简称DQL)，

5、DQL用于查询数据。SQL主要定义了以下四类语言：3.1.2 SQL的组成结构3.数据操纵语言(Data Manipulation Language，简称DML)，DML用于对关系模式中的具体数据的增、删、改等操作。4. 数据控制语言(Data Control Language，简称DCL)，DCL用于数据访问权限的控制。 SQL 语言的组成对应的命令动词建表 SQL的组成结构SQL的各部分对应的命令动词表SQL组成部分 命令动词数据定义(DDL)CREATE, DROP, ALTER数据查询(DQL)SELECT数据操纵(DML)INSERT, UPDATE, DELETE数

6、据控制(DCL)GRANT, REVOKESQL语言的组成对应的命令动词 表3.13.2 SQL数据定义 SQL支持关系数据库的外模式、模式、内模式这三级 模式结构，如图3.1所示。 外模式：由视图和部分基本表构成。视图是为满足用户和应用程序的数据格式要求而定义的，当基本表不适合于用户直接查询的操作要求时，需要定义视图，以便用户的查询操作。 模式：由全体基本表构成。基本表是本身独立的表，SQL中一个关系对应一个基本表。 内模式：由数据库的存储文件和他们的索引文件构成。在SQL中，一个关系对应一个表；一个或多个表对应一个存储文件；一个表可以带若干个索引。存储文件的逻辑结构组成了关系数据库的内模式

7、。3.2 SQL数据定义SQL基本表b3基本表b4存储文件f2存储文件f3视图v1视图v2基本表b1基本表b2存储文件f1外模式模式内模式图3.1 数据库的三级模式结构3.2.1 数据库模式的定义 1.数据库模式的创建 对于SQL,数据库的创建用CREATE语句实现，典型的数据库创建语句如下： CREATE DATABASE 数据库名 【参数】 例3.1 创建一个名称为“教务管理系统”的数据库，参 数保持系统默认。 解： CREATE DATABASE 教务管理系统2. 数据库的删除原先所建数据库进行删除，此时对应的语句为DROP语句，具体语法如下：DROP DATABASE 数据库名例3.2

8、 删除”教务管理系统”数据库. 解： DROP DATABASE 教务管理系统3.2.1 数据库模式的定义 1. 创建基本表 SQL语言创建基本表的一般格式为： CREATE TABLE (列级完整性约束, )； 3.2.2基本表的定义完整性约束 列级完整性约束仅作用在该列上，表级完整性约束是作用在整个表的，这些完整性约束条件都保存在系统的数据字典中，并由关系数据库管理系统自动检查 。 例3.3 创建一个学生表S，包括如下几个字段Snum、Sname、Ssex、Sage、Sphone、Dnum，其中Snum是学号，Sname是姓名，Ssex是性别， Sage是年龄，Sphone是电话，Dnum

9、是学生所在的院系编号。Snum是主码，不能为空值且是唯一 。 解：CREATE TABLE S(Snum CHAR(10) NOT NULL UNIQUE, Sname VARCHAR(8), Ssex CHAR(2), Sage SMALLINT, Sphone VARCHAR(20), Dnum CHAR(4), PRIMARY KEY (Snum),FOREIGN KEY(Dnum) REFERENCES D(Dnum); 这里定义的学生表有六个属性，其中NOT NULL和UNIQUE是列级完整性约束，作用在Snum上，NOT NULL要求Snum不能为空值，UNIQUE要求Snum取值

10、唯一，而PRIMARY KEY(Snum)是表级完整性约束，FOREIGN KEY(Dnum) REFERENCES D(Dnum)则说明该学生表S的字段Dnum为院系表D的外键。 一个课程表C(Cnum，Cname，Cfreq)和一个选修表SC(Snum，Cnum，score)，其中Cnum是课程号，Cname是课程名称， Cfreq是该门课程的学分，Snum是学号， score是成绩。Cnum是C表的主码，(Snum，Cnum)是SC表的主码。要求学分Cfreq可以是空值或者是0到10之间的定点数。而SC表中的Cnum和Snum分别是课程表C和学生表S的外码。 例3.4CREATE TAB

11、LE C(Cnum CHAR(4),Cname VARCHAR(20),Cfreq NUMERIC(2,1),PRIMARY KEY (Cnum),CHECK(Cfreq IS NULL) OR (Cfreq BETWEEN 0 AND 10);解：CREATE TABLE SC(Snum CHAR(10),Cnum CHAR(4),Score SMALLINT,PRIMARY KEY (Snum,Cnum),FOREIGN KEY(Snum) REFERENCES S(Snum),FOREIGN KEY(Cnum) REFERENCES C(Cnum);ALTER TABLE 语句的语法如下

12、: ALTER TABLE ADD COLUMN 完整性约束ADD ,DROP COLUMN ,DROP ,ALTER COLUMN ；2. 修改基本表 12. 修改基本表 2ALTER TABLE 语句可以实现如下三种功能： （1）加入一列到已经存在的表中； （2）修改已经存在的表中的某一列； （3）删除表中已经存在的一列。 例3.6 在已存在的学生表S中增加一个家庭地址Saddress的新的属性列： 解：ALTER TABLE S ADD Saddress VARCHAR（50）；要注意的是，新增加的属性列的值都是空值。(1) 增加一列 例3.7 修改学生表S中Sname的数据类型，变为C

13、HAR类型。 解：ALTER TABLE S ALTER COLUMN Sname CHAR(15); （2）修改列的属性（3）删除列例3.8 删除学生表S中的Sage字段。解：ALTER TABLE S DROP COLUMN Sage； 例3.8 在学生表S中限定年龄Sage小于100大于0. 解： ALTER TABLE S ADD CONSTRAINT Chk_Sage CHECK (Sage0 and Sage100);（4）增加完整性约束 (5) 删除完整性约束 例3.9 删除学生表S中完整性约束 Uni_Snum 和Chk_Sage. 解： ALTER TABLE S DROP

14、Uni_Snum, Chk_Sage; SQL 提供了一个可以从数据库彻底地移去某个表的命令DROP TABLE，它可以从数据库中删除一个指定的表以及与之相关联的索引和视图，需要注意的是，一旦这个命令发出以后就没有办法可以取消它。SQL语言删除基本表的一般格式为： DROP TABLE CASCADE|RESTRICT； 例3.11 删除学生表S。 DROP TABLE S3. 删除基本表1. 建立索引 SQL语言建立索引的一般格式为： CREATE UNIQUECLUSTER INDEX ON (,)；3.2.3索引的定义建立索引示例例3.12 在学生表S的姓名列Sname上建索引 CREA

15、TE INDEX idx_Sname ON S(Sname)；例3.13 在选修表SC的课程号Cnum和成绩Score上建索引，要求课程号为主索引且为升序，课程号相同时成绩为降序。 CREATE INDEX idx_sc_s ON SC(Cnum, Score DESC); SQL语言删除索引的一般格式为： DROP INDEX ； 例3.13 在学生表S上的索引idx_Sname。 DROP INDEX idx_Sname ON S ;2. 删除索引3.2.3 索引的定义3.3 SQL数据查询 SELECT命令的格式与基本使用SELECT语句是SQL语言中功能最强大、用途最广泛的数据库操纵语

16、句，也是关系运算理论在SQL语言中的主要体现。一个完整的SELECT语句包括SELECT，FROM，WHERE，GROUP BY和ORDER BY共五个子句，前两个必不可少，后面三个可以省略。SELECT语句的完整格式为：SELECT DISTINCTFROM WHERE GROUP BY HAVINGORDER BY ASC|DESC3.3 SQL数据查询 该SELECT格式的含义是： 根据 WHERE 子句的行条件表达式 , 从 FROM 子句指定的基本表或视图中找出满足条件的行 , 再按 SELECT 子句中的目标列表达式选出元组中的属性值形成结果表。3.3 SQL数据查询 如果有 GR

17、OUP 子句 ,则将结果按 的值进行分组 , 该属性列值相等的元组为一个组 , 每个组产生结果表中的一条记录。通常会在每组中进行聚合操作。如果 GROUP 子句带 HAVING 短语 , 则只有满足指定条件的组才予输出。如果有 ORDER 子句 , 则结果表还要按的值的升序或降序排序。 3.3 SQL数据查询 我们接下来列出本章所举的各个例子中要用到的教学数据库中各个基本表的结构，表中加下划线的字段为表的主码，其中表S、T、D、C、SC的示范数据请读者参看第二章图2.6，新增加的授课表TC示范数据如下所示：3.3 SQL数据查询 学生S (学号Snum, 姓名Sname , 性别Ssex ,

18、年龄Sage , 联系电话Sphone， 系编号Dnum )教师T (教师号Tnum, 姓名Tname , 性别Tsex , 出生日期Tbirth , 职称Ttitle , 工资Tsalary ,联系电话Tphone， 系编号Dnum )本章数据表用例本章数据表用例院系D（系编号Dnum,系名称Dname,负责人Ddirector）课程C (课程号Cnum, 课程名Cname , 课时Cfreq )选课SC (学号Snum , 课程号Cnum , 成绩Score )授课TC ( 教师号Tnum , 课程号Cnum )例 3.15 查询学生关系表S中所有年龄大于等于18岁的学生的姓名、性别和年龄

19、。 解： SELECT Sname, Ssex, Sage FROM S WHERE Sage=18;3.3.1 简单查询执行的结果见表3.3:SnameSsexSage张山男18李波涛男22王劲松男24表3.3例3.15 的运行结果1.SELECT 子句SELECT 子句的基本功能是选择表中的全部列或部分列 , 类似关系代数中的投影运算 ,其语法格式为: SELECT SELECT-FROM-WHERE的具体功能 例 3.16 查询课程表D中的全部列。 解：SELECT Dnum, Dname, Director FROM D; 也可以简单的写成 : SELECT * FROM D;(1)

20、查询全部列 执行结果：Dnum Dname DirectorD001计算机王森林D002通信 方维伟D003自控张小龙D004外语叶文 也可以是常量 , 此时查询结果中对应列位置的值就都是该常量值。 例3.17 SELECT 子句目标列为常量示例。 SELECT Tname, 工资：，Tsalary FROM T；(2) 常量执行结果见表3.5Tname（无列名）Tsalary梁文博工资：2600王芳工资：3200李刚工资：2200王森林工资：3500余雪梅工资：1880方维伟工资：2680表3.5例3.17 的运行结果SELECT 子句中的 还可以是与属性列有关的表达式 , 即可以是查询出来

21、的属性列经过一定的计算后列出结果。 例 3.18 查询所有老师的税后工资情况，为简单起见，我们假定对不同的工资的个人所得税征收比率都为6%。 SELECT Tname, Tsalary*(1-0.06) FROM T; (3) 计算列执行结果见表3.6Tname（无列名）梁文博2444王芳3008李刚2068王森林3290余雪梅1767.2方维伟2519.2表3.6例3.18 的运行结果SELECT 子句中的第二项为Tsalary*(1-0.06), 这是一个表达式 ,即把查询结果中每一行的 Tsalary 列的值乘以 (1-0.06), 并将计算的结果值作为查询的结果。显然 , 查询结果中有

22、多少行 , 该表达式就有多少个计算结果 , 所以这种计算有时也被称为 水平计算 或 按行计算 。 (3) 计算列1) 算术运算符:算术操作符在 SQL 语句中表达数学运算操作 , 参与运算的值都是数值型的。 SQL 的数学运算操作符只有四种 , 它们是 :+( 加 ) 、 -( 减 ) 、 *( 乘 ) 和 /( 除 ) 。SQL Server 中的常用运算符 2) 字符运算符:字符运算符在 SQL 语句中表达字符运算操作 , 参与运算的值必须都是字符串类型的。在 SQL Server 中 , 最常见的字符运算符是 +( 字符串连接 ), 该运算可以把两个字符串连接成一个字符串。SQL Ser

23、ver 中的常用运算符 例319 选出老师工资情况，但对每位老师都带上称呼：老师。SELECT Tname老师, Tsalary FROM T;执行结果见表3.17Tname（无列名）梁文博老师2600王芳老师3200李刚老师2200王森林老师3500余雪梅老师1880方维伟老师2680例3.19 的运行结果表3.17SQL Server 中的常用运算符 日期运算符在SQL 语句中表示日期运算操作。常用的日期运算符是 +, 该运算允许一个日期值 d和一个 n 相加 , 运算结果为一个日期 d 后 第 n天的日期值 , 也就是说 , 若 x 的值为日期值2009-1-15, 则 x+10 的值为

24、2009-1-25, 类 似的 ,x-10 的值为2009-1-5。(3) 日期运算符 除了运算符外 , 还可以在目标列表达式中使用函数以支持更多的功能 ,常见的函数包括数学函数、字符函数、日期函数等，这部分函数往往依赖于具体的关系数据库管理系统，读者可以依据自己所采用的系统，查询相关的技术手册来使用。 用户可以通过指定别名来改变查询结果中的列标题 ,这样我们往往可以将表中英文列名转换成相应的中文名称来显示，同时这对于含计算列、常量的目标列表达式尤为有用。 别名的定义格式为 : AS 其中AS可以省略(4) 列的别名 例3.20 在前面的例3., 可以看到其查询结果中第二列( 计算列 Tsal

25、ary *(1-0.0) 的列标题为 “ 无列名 ” ，现希望将其改为：税后工资，同时将Tname列改为：教师姓名。 SELECT Tname AS 教师姓名, Tsalary*(1-0.0) AS 税后工资 FROM T； 或 SELECTTname 教师姓名，Tsalary*（1-0.06）AS 税后工资 FROMT T;(4) 列的别名执行结果为见下表教师姓名税后工资梁文博2444王芳3008李刚2068王森林3290余雪梅1767.2方维伟2519.2例3.20 的运行结果 按照关系的一般理论，重复的元组是不会出现在关系 中的。但是，在实践中重复元组的删除是相当费时的，所以SQL允许在

26、关系和查询结果中出现重复。在SELECT语句中缺省或ALL是保留重复元组的，因此一般ALL不写。如果想要强迫删除重复，则必须使用DISTINCT短语。重复元组的产生多数是在投影的情况下。(5) 取消重复行例3.21 列出已经选课了的同学的学号。如果用语句 : SELECT Snum FROM SC;则执行结果为 :Snum 0903330001 0903330001 0903330001 09033300020903330004 0903330005 0903330005 (5) 取消重复行 显然，这不是我们希望得到的 结果，比如学号0903330001出现了3次，我们只是关心那位同学已经选课

27、，而不是关心选了几次课，所以，我们不应该让它重复出现。 于是代码改为： SELECT DISTINCT Snum FROM SC; 这时执行结果为：Snum0903330001090333000209033300040903330005(5) 取消重复行 DISTINCT 关键字的作用就是取消重复的行。 其语法格式为 : SELECT DISTINCT|ALL与DISTINCT相对应的关键字是ALL, 表示在查询结果中保留重复的行 , 如果 SELECT子句中没有指明是DISTINCT 还是 ALL, 则默认为 ALL。(5) 取消重复行FROM 子句的基本功能用于指明查询所涉及的表 , 其语

28、法结为 : FROM , 如果FROM关键字后只有一个基本表名 , 这种查询称为单表查询 , 即仅涉及一个基本表的查询。2. FROM 选择表如果 FROM 关键字后有多个基本表名 , 这种查询往往称为多表查询 , 查询结果中包括了多张基本表中的列 , 这几张基本表之间还要进行连接运算 , 而连接条件则要写在WHERE 子句中。2. FROM 选择表WHERE 子句的基本功能用于指明查询的条件以选择行 , 只有满足查询条件的行才会出现在查询结果中。如前所述 ,WHERE 子句的功能类似于关系运算中的选择运算。在例3.14中 , 使用的SQL 语句为 : SELECT Tname, Ttitle

29、, Tsalary FROM T WHERE Tsalary=2500; 3.WHERE 选择行行选择条件中常用的运算符如下表：作用运算符比较=,=,=,!=范围BETWEEN AND, NOT BETWEEN AND集合IN,NOT IN模式匹配LIKE,NOT LIKE空值IS NULL,IS NOT NULL逻辑运算NOT,AND,ORSQL提供的比较运算符主要有：=、!=、 、=，以及 BETWEEN AND。注意： 和！=都表示不等于。SQL允许使用比较运算符比较算术表达式和字符串以及特殊类型，如日期等。 例3.22 查询所有男学生的信息。 解： SELECT * FROM S WH

30、ERE Ssex= 男;(1) 比较运算SnumSnameSsexSageSphoneDnum0903330001张山男1845673434D0020903330002李波涛男2289457321D0030903330005王劲松男2413098765892D003表 3.10执行结果见表3.10例3.22 的运行结果(1) 比较运算 例3.23 查询成绩大于80分的学生学号,课程号,成绩。 解： SELECT Snum, Cnum, Score FROM SC WHERE Score80; 执行结果见表3.11SnumCnumScore0903330001C004890903330004C0

31、01850903330005C00192表 3.11例3.23 的运行结果(1) 比较运算 例3.24 列出工资在20002600之间的老师姓名和工资情况。 解： SELECT Tname, Tsalary FROM T WHERE Tsalary BETWEEN 2000 AND 2600； 执行结果见表3.12：TnameTsalary梁文博2600李刚2200表3.12例3.24 的运行结果(1) 比较运算SQL 中的逻辑运算符有 :NOT非 ( 取反)AND与 ( 并且 )OR 或 ( 或者 )如果两个逻辑运算符同时出现在同一个WHERE子句中 , 则 NOT 的优先级最高 ,AND的

32、优先级高于 OR, 当然也可以通过括号改变优先级。(2) 逻辑运算 例3.25 查出年龄不大于18岁的同学的学号、姓名、年龄。 解：SELECT Snum, Sname,SageFROM SWHERE NOT Sage18;执行结果见表3.13SnumSnameSage0903330003岳岚170903330004王燕170903330006赵玉兰16表3.13例3.25 的运行结果(2) 逻辑运算 例3.26 查询工资在1960年1月1日以前和1970年12月30 日以后出生的教师信息。 解： SELECT * FROM T WHERE Tbirth1970-12-31; 执行结果见表3.

33、14：TnumTnameTsexTbirthTtitleTsalaryTphoneDnumT003李刚男1979-05-23讲师220013809097865D003T004王森林男1950-11-03教授350056784325D001T005余雪梅女1979-03-23助教188013387650643D001表3.14例3.26 的运行结果(2) 逻辑运算 空值 NULL 是一个特殊的数据 , 在基本表中 ,如果某个列中没有输入数据 , 则它的值就为空。如果要判断某一列的值是否为空 , 不能使用比较运算符 = 和 , 而应该使用 IS NULL 或 IS NOT NULL 运算符。 (3

34、) 空值条件NULL的意义 再次说明，空值NULL是一个特殊值，它看起来和任何其它值一样，但它与一般意义上的值又不同： NULL的值是未知的，应该有某个值属于它， 但不知道。 NULL的值是无意义的，只是表示一种空缺。 NULL不是常量。尽管NULL值可以出现在元组 中，但不能显式地将NULL作为操作数使用。 NULL值与其它任何值(包括另一个NULL值) 进行算术运算，其结果仍为NULL。 NULL值与其它任何值(包括另一个NULL值) 进行比较运算，其结果为FALSE。 即使两个同名分量的值都是NULL值，也不 能认为它们相等。例如，当我们连接两个 关系时，并不认为两个NULL分量彼此相等

35、。NULL的意义SnumSname0903330003岳岚0903330006赵玉兰 例3.27 列出所有没有登记联系电话的同学的姓名和学号。 解： SELECT Snum, Sname FROM S WHRER Sphone IS NULL; 执行结果见表3.15表3.15例3.27 的运行结果NULL的意义运算符 IN 可以用来表示数据是否属于指定集合 ,其语法格式为 : NOT IN其中 的格式为 ( 元素值 , 元素值 ), 也就是说在SQL中 , 集合的表示法是用圆括号括起来的一组元素值 , 元素值之间使用逗号隔开。与 IN 相对的运算符是 NOT IN, 用于表示数据不属于指定集合

36、。 (4) 集合运算例3.28 查询 张山和李波涛两位学生的年龄情况。SELECT Sname, Sage FROM SWHERE Sname IN (张山, 李波涛);等价于SELECT Sname, Sage FROM SWHERE Sname= 张山 OR Sname= 李波涛;执行结果见表3.16。SnameSage张山18李波涛22表3.16例3.28 的运行结果(4) 集合运算运算符 LIKE 可以用来进行字符串的模式匹配 ,其语法格式为 : NOT LIKE 其含义是查找指定的属性列值与 相匹配的行 , 可以是一个普通的字符串 ( 此时的 LIKE 运算就等同于 = 运算 , 也

37、可以含有通配符 % 和_。含义分别为：% ( 百分号)：代表任意长度的字符串。_ (下划线)： 代表任意的单个字符。(5) 字符串的模式匹配例3.29 列出所有姓王并且姓名为两个字的学生的学号、姓名和年龄。解：SELECT Snum, Sname, Sage FROM SWHERE Sname LIKE 王_;执行结果见表3.17SnumSnameSage0903330004王燕17表3.17例3.29 的运行结果(5) 字符串的模式匹配例3.30 查询联系电话不是133开头的教师的姓名和电话。解：SELECT Tname, Tphone FROM TWHERE Tphone NOT LIKE

38、 133%;执行结果见表3.18TnameTphone梁文博8765078王森林56784325表3.18例3.30 的运行结果(5) 字符串的模式匹配 使用 ORDER BY 子句来指定按照一个或多个属性列的升序(ASC) 或降序 (DESC) 重新排列查询结果中的行 ,其中升序(ASC)为默认值。格式如下： ORDER BY 列名1ASC|DESC, 列名2ASC|DESC, 首先排列按列名1的值，若列名1的值相等则按列名2的值排列，依次类推。 (6) ORDER BY排序例3.31 按照年龄从小到大列出所有学生的学号、姓名、年龄。SEL

39、ECT Snum,Sname, Sage FROM S ORDER BY Sage;执行结果见表3.19Snum Sname Sage 0903330006赵玉兰16 0903330003 岳岚170903330004 王燕170903330001张山180903330002李波涛220903330005王劲松24表3.19例3.31 的运行结果(6) ORDER BY排序例3.32 查询教师的姓名、职称和工资。查询结果按照职称排列，职称相同的按照工资从多到少排列。SELECT Tname, Ttitle, Tsalary FROM TORDER BY Ttitle, Tsalary DESC

40、;执行结果见表3.20TnameTtitleTsalary方维伟副教授2680梁文博副教授2600李刚讲师2200王森林教授3500王芳教授3200余雪梅助教1880表3.20例3.32 的运行结果(6) ORDER BY排序一个数据库中的多个表之间一般都存在某种内在联系 , 它们共同提供有用的信息。前面介绍的简单查询都是针对一个表进行的, 而在实际使用中 , 许多有用的查询要求从两个或更多的表中取得数据 , 这种查询就被称为多表查询。 3.3.2 多表查询 连接查询是指两个或两个以上的基本表或视图的连接操作来实现的查询。SQL提供了一种简单的方法把几个关系连到一个查询中，即在FROM子句中列

41、出每个关系，然后在SELECT子句和WHERE子句中引用FROM子句中的关系的属性，而WHERE子句中用来连接两个关系的条件称为连接条件。 FROM子句中多个表可以用逗号或者CROSSJOIN分隔。还有一种连接方式采用JOIN ON进行条件连接。 1. 连接查询 当一个查询请求涉及到数据库中的多个表时 , 必须按照一定的条件把这些表连接在一起 , 以便能够共同提供用户需要的信息。用来连接两个表的条件称为连接条件 , 其一般格式为：. 其中的比较运算符主要有： = 、 、 = 、 = 、 。 1. 连接查询例3.33 查询各个系中所有老师的姓名和职称。解：系的情况存放在院系表D中 , 而教师的情

42、况存放在表T中 , 所以本查询实际上同时涉及 D 表和 T表。这两张表的联系是通过两张表具有的属性列Dnum和Dnum实现的。要查询教师及其所在系的情况 , 就必须将这两个表中Dnum和Dnum相同的行连接起来。 这是一个标准的等值连接 , 连接条件是D.Dnum=T.Dnum,在标准 SQL 中 , 连接条件在WHERE 子句中给出 , 所以完成本查询的 SQL 语句为: 1. 连接查询SELECT D.Dname, T.Tname,T. TtitleFROM D, TWHERE D.Dnum=T.Dnum;执行结果见表3.21DnameTnameTtitle计算机梁文博副教授自控王芳教授自

43、控李刚讲师计算机王森林教授计算机余雪梅助教通信方维伟副教授表3.21 例3.33 的运行结果 1. 连接查询例3.33 中的T表和D表是通过“=”连接的，这种连接运算符为“=”的连接称为等值连接，若用其它比较运算符(如、=、=、)连接，则称为非等值连接。除了连接条件外，WHERE子句中还可以有其它限制条件，如例3.34。 1. 连接查询例3.34 查询自控系所有老师的工资情况。解： SELECT T.Tname, T. Tsalary FROM T, D WHERE T.Dnum=D.Dnum AND D.Dname=自控; 1. 连接查询执行结果见表3.22TnameTsalary王芳320

44、0李刚2200表3.22例3.34 运行结果 1. 连接查询例3.35 无条件连接院系表D 和教师表T。SELECT D.*, Tnum,Tname,Tsex, Tbirth,TtitleTsalary,TphoneFROM D ,T;执行结果 1. 连接查询DnumDnameDdirectorTnumTnameTsexTbirthTtitleTsalaryTphoneD001计算机王森林T001梁文博男1970-04-09副教授1600.087659078D001计算机王森林T002王芳女1967-08-05教授2200.013556789056D001计算机王森林T003李刚男1979-0

45、5-23讲师1200.013809097865D001计算机王森林T004王森林男1950-11-03教授2500.056784325D001计算机王森林T005余雪梅女1979-03-23助教880.013387650643D001计算机王森林T006方维伟男1965-05-03副教授1680.013345678932D002通信方维伟T001梁文博男1970-04-09副教授1600.087659078D002通信方维伟T002王芳女1967-08-05教授2200.013556789056D002通信方维伟T003李刚男1979-05-23讲师1200.013809097865D002通

46、信方维伟T004王森林男1950-11-03教授2500.056784325D002通信方维伟T005余雪梅女1979-03-23助教880.013387650643D002通信方维伟T006方维伟男1965-05-03副教授1680.013345678932D003自控王芳T001梁文博男1970-04-09副教授2200.087659078D003自控张小龙T002王芳女1967-08-05教授1200.013556789056D003自控张小龙T003李刚男1979-05-23讲师2500.013809097865D003自控张小龙T004王森林男1950-11-03教授880.0567

47、84325D003自控张小龙T005余雪梅女1979-03-23助教1680.013387650643D0033自控张小龙T006方维伟男1965-05-03副教授2200.013345678932例3.36 从学生表S、课程表C和选修表SC三个表中，查询学生姓名、所修课程及成绩。解：SELECT Sname, Cname, scoreFROM S, C, SCWHERE S.Snum=SC.Snum AND SC.Cnum=C.Cnum;二个以上表的连接 执行结果见表3.24 SnumSname Cname Score 0903330001张山 C程序设计 530903330001张山 自动

48、控制原理 890903330001张山 数据结构 650903330002李波涛 自动控制原理 75 0903330004王燕 数据库系统原理 85 0903330005王劲松数据库系统原理92 0903330005王劲松计算机体系结构76 表3.24例3.36 的运行结果二个以上表的连接 例3.37 查询所有年龄相同的学生的姓名和年龄。解：SELECT S1.Sname,S2.Sname,S1.SageFROM S S1，S S2WHERE S1. Sage=S2. SageAND S1.SnumS2.Snum;自身连接执行结果见表3.25SnameSnameSage岳岚王燕17表 3.25

49、例3.37 的运行结果自身连接在前面的连接示例中 , 结果集中只保留了符合连接条件的元组 , 而排除了两个表中没有对应的或匹配的元组情况 , 这种连接称为内连接。如果要求查询结果集中保留非匹配的元组 , 就要执行外部连接操作。外连接外连接分类 SQL 的外部连接分左外部连接和右外部连接和全外连接三种 : 左外部连接操作是在结果集中保留连接表达式左表中的非匹配记录 ；右外部连接操作是在结果集中保留连接表达式右表中的非匹配记录。外部连接中不匹配的列用NULl表示。SQL 中左外部连接符号为 *=,右外部连接符号为 =* 。外部连接中不匹 配的列用 NULL 表示。SQL-2006标准所定义的FRO

50、M子句的连接语法格式为：FROM join_table1 join_type join_table2 ON (join_condition)。其中join_table1和join_table2指出参与连接操作的表名，连接可以对同一个表操作，也可以对多表操作，对同一个表操作的连接就是自身连接。join_type 指出连接类型是内连接还是外连接。 内连接用INNER JOIN或者JOIN.外连接分类外连接分为左外连接(LEFT OUTER JOIN或LEFT JOIN)、右外连接(RIGHT OUTER JOIN或RIGHT JOIN)和全外连接(FULL OUTER JOIN或FULL JOIN

51、)三种。 连接操作中的ON (join_condition) 子句指出连接条件，它由被连接表中的列和比较运算符、逻辑运算符等构成。 外连接分类例3.38 查询各个系中所有老师的系名、系编号、姓名、教师号和职称，用左连接完成。解:SELECT D.Dname,T.Tnum, T.Tname,T. TtitleFROM DLEFT OUTER JOIN ON TON D.Dnum=T.Dnum;外连接分类执行结果见表3.25DnameTnumTnameTtitle计算机T001梁文博副教授计算机T004王森林教授计算机T005余雪梅助教通信T006方维伟副教授自控T002王芳教授自控T003李刚讲

52、师外语NULLNULLNULL表3.25例3.38 的运行结果外连接分类1. 聚合函数聚合(Aggregation)是指把关系中的某一列的一系列的值形成单一值的运算。它是对关系整体的一种运算。SQL提供了五个聚合函数，可以用于列信息的统计或汇总。 COUNT(DISTINCT|ALL * ) 统计一列中的值的个数 AVG(DISTINCT |ALL) 求某一列值的平均值 ( 此列必须是数值型 ) 3.3.3 聚合和分组 SUM(DISTINCT |ALL)求某一列值的总和 ( 此列必须是数值型 ) MIN(DISTINCT |ALL)求某一列值的最小值 MAX(DISTINCT |ALL)求某

53、一列值的最大值如果指定 DISTINCT, 则表示在计算时忽略指定列的重复值。如果不指定 DISTINCT或指定了 ALL (ALL 为默认值 ), 则不忽略重复值。1. 聚合函数（无列名）6 例3.39 查询学生总数。解： SELECT COUNT(distinct snum) FROM Sc; 执行结果： 这里的星号(*)表示整个元组。这是COUNT()函数特有的，表示统计元组的个数，其它聚合函数的作用超过一列则是没有意义的。注意星号（*）和DISTINCT|ALL 不能同时出现。 1. 聚合函数例3.40 对教师的工资差异进行比较，找到最高工资和最低工资。解： SELECT MAX(Ts

54、alary) AS 最高工资, MIN(Tsalary) AS 最低工资 FROM T;执行结果如下:最高工资最低工资 350018801. 聚合函数前面的几个例子完成的是对表中所有选出的元组进行统计运算,其计算结果中只有一行,但在实际应用中,经常需要将查询结果进行分组,然后再对每个分组进行统计,如:按系统计学生人数,按工厂统计工人平均工资等。这些查询的结果中一般都会有多行。SQL语言提供了GROUP BY子句和HAVING子句来实现分组统计。2.分组统计例3.41计算不同职称的老师的工资情况。SELECT Ttitle AS 职称, AVG(Tsalary) AS 平均工资,count(tn

55、um) as FROM TGROUP BY Ttitle;执行结果见表3.27人数职称平均工资2副教授26401讲师22002教授33501助教1880表3.27例3.41 的运行结果2.分组统计例3.42 按照性别统计学生人数。解： SELECT Ssex, COUNT(*) FROM S GROUP BY Ssex;执行结果见表3.28Ssex(无列名)男3女3表3.28例3.42 的运行结果2.分组统计例3.42 每个学生可能选了多门课程，查询每个学生的学号、姓名和相应的课程平均成绩。SELECT S.Snum, Sname,AVG(score)FROM S,SCWHERE S.Snum

56、=SC.SnumGROUP BY S.Snum,sname;执行结果:2.分组统计Snum Sname (无列名) S001 张山 69.0 S002李新 75.0 S004 王燕 85.0 S005 王力 84.0 2.分组统计例3.43 统计各系的学生人数 , 显示各系的名称和学生人数。解: 本例需要使用多表查询 ,SQL 语句为:SELECT D.Dname AS 系,COUNT(*) AS 学生人数FROM D,SWHERE D.Dnum=S.DnumGROUP BY D.Dname在分组查询中使用多表连接 , 进行统计2.分组统计执行结果 系 学生人数 计算机 3通信 1自控 22.

57、分组统计有时我们希望对所分的组按某一条件进行选择，可以用HAVING子句实现。例3.44 查询平均成绩低于80分的学生学号。 SELECT Snum FROM SC GROUP BY Snum HAVING AVG(score) 、 = 、 = 、 等.2. 使用比较运算符的子查询例3.49 列出工资低于教师平均工资的老师的编号、姓名、工资情况。SELECT Tnum,Tname,Tsalary FROM T WHERE TsalaryANY R为真，否则为假；若s比R中至少一个值小，则sALL R为真，否则为假；若s比R中每个值小，则sALL R为真，否则为假。同理其它比较运算符可与ALL搭

58、配。如sANY R和s NOT IN R是相同的，可以互相替换，但一般多采用NOT IN，因为容易理解。2. 使用比较运算符的子查询例350 查询教师中工资比自控系任一教师低的教师编号、姓名和工资。SELECT Tnum,Tname, Tsalary FROM T WHERE Tsalary ANY (SELECT Tsalary FROM T WHERE Dnum IN (SELECT Dnum FROM D WHERE Dname=自控 )2. 使用比较运算符的子查询执行结果：TnumTnameTsalaryT001梁文博2600.0T003李刚2200.0T005余雪梅1880.0T00

59、6方维伟2680.02. 使用比较运算符的子查询例 3.51查询年龄最低的学生的学号，姓名和年龄 SELECT Snum,Sname,SageFROM SWHERE Sage 19) 5 组合查询SnumSname0903330001张山0903330002李波涛0903330004王燕0903330005王劲松从上面的例子可以看出，UNION 对查询块 1 和查询块 2 的结果集合并为一个结果集 , 要求这两个结果集的结构相同。所谓结构相同就是指集合中列的个数、类型和顺序必须一样。 5 组合查询 (2) 交操作交操作(INTERSECT)是把同时出现在这些查询结果中的元组取出。INTERSE

60、CT操作也会自动去掉重复元组，如果要保留所有重复，必须用INTERSECT ALL代替INTERSECT。因为交操作可以在WHERE子句中加一逻辑运算符AND简单实现，所以实际上很少直接用到交操作。 5 组合查询 (3) 差操作差操作(MINUS)是把出现在第一个查询结果中，但不出现在第二个查询结果中的元组取出。因为差也可以在WHERE子句中加一逻辑运算符AND简单实现，只是将某个逻辑条件取反即可，所以实际上也很少直接用到交操作。 5 组合查询递归查询的概念是新标准SQL3中引入的，SQL3 实现递归查询的方法是以关系逻辑为基础的。在SQL3 中 , 可以通过关键字WITH 引导的语句来实现关