SQL结构查询语言课件

1、1 SQL概述 SQL的全称为structured query language（结构化查询语言）。最早是在1974年，IBM的San Jose实验室的研究人员D.Chamberlin定义了“结构化英语查询语言”，简称SEQUEL。1976年，Chamberlin将其改称为SQL，正确的发音为s_q_l。SQL是一种类英语的语言，用一些简单的英语句子构成基本的语法结构，具有简单易学、功能较强、操作灵活的特点。 1976年，在IBM公司研制的关系数据库管理系统System R上，实现了这种用英语描述关系代数运算的语言的研究。在20世纪70年代末，ORACLE公司开发出基于SQL商业化的关系数据库

2、管理系统。 11 SQL概述 SQL的全称为structured 1 SQL概述 SQL是一种非过程化的语言，它与通常的高级语言不同，使用SQL时，只要说明做什么，不需要说明怎么做，具体的操作全部由DBMS自动完成。例如，要查询所有女同学的姓名，只要写出SQL语句： SELECT 姓名 FROM 学生 WHERE 性别=女 2 1 SQL概述 SQL是一种非过程化的语言，它与通常的高1 SQL概述 SQL语言按用途可划分为三个组成部分：数据定义语言（DDL）：在数据库系统中，数据库、表、视图、索引等都是对象，用于定义这些对象的SQL语句称为DDL。数据处理语言（DML）：用于插入、修改、删除和

3、查询数据的SQL语句称为DML。数据控制语言（DCL）：用于实现数据完整性、安全性、一致性等控制的SQL语句称为DCL。 31 SQL概述 SQL语言按用途可划分为三个组成部分2 数据定义本章将结合下面一组关系模式和样本数据说明SQL语言的应用实例。关系和样本数据如图1所示。 系（系编号，系名称） 教工（教工号，姓名，性别，职称，工资，系编号） 学生（学号，姓名，性别，出生年月，专业，家庭地址，系编号） 课程（课程编号，课程名称，学时，系编号） 成绩（学号，课程编号，分数） 42 数据定义本章将结合下面一组关系模式和样本数据说明SQL2 数据定义系课程图1 一组关系模式和样本数据（a）系编号系

4、名称101数学102计算机103外语104经济课程号课程名学时系编号C1数学681C2英语853C3计算机682C4经济学51452 数据定义系课程图1 一组关系模式和样本数据（a）系编号 2 数据定义 图1 一组关系模式和样本数据（b）教工号姓 名性别职称工 资系编号2101葛小平女教授3420.001012203李长江男副教授3190.001022405姜立伟男副教授3140.001042104张丽丽女讲师224001012302康立华女教授3740.001032205王伟平男讲师2130.00102教工 6 2 数据定义 图1 一组关系模式和样本数据（b）教工号姓 2 数据定义 学 号课

5、程编号分数1022C1881022C2672124C1772124C2952124C3454021C4874021C2784021C3671223C1661223C2893012C2933012C384成绩 图1 一组关系模式和样本数据（c）72 数据定义 学 号课程编号分数1022C18810222 数据定义 图1 一组关系模式和样本数据（d）学 号姓 名性别出生年月系编号1022田平平女08/05/19801012124郭黎明男03/04/19811024021何明慧女04/12/19821041223姜明明男12/05/19801013012何漓江男10/05/19791032104康纪

6、平女03/04/19811024125康嘉家男07/05/19801041134包立琪女03/14/19811014115王海洋男04/13/19821041354王立平女12/05/1981101学生 82 数据定义 图1 一组关系模式和样本数据（d）学 号2 数据定义说明：本书选用SQL Server2000系统作为上机实验平台，所有例题和上机操作实验案例都在SQL Server2000查询分析器中运行通过，并且所选用的题目尽量符合SQL的标准格式，如果读者选用其他数据库管理系统作为实验环境，可能有微小的区别。 92 数据定义说明：本书选用SQL Server2000系统2 数据定义2.1

7、 建立数据库 绝大多数关系DBMS，使用CREATE DATABASE命令建立数据库，其语法格式：CREATE DATABASE 102 数据定义2.1 建立数据库 绝大多数关系DBMS，2 数据定义2.1 建立数据库例1 根据图1给出的实例，用SQL语言创建一个教学数据库。 CREATE DATABASE 教学 当进入SQL_Server2000系统的查询分析器后，如图2所示，在查询窗口中输入命令：CREATE DATABASE 教学，然后，点击绿色的按钮执行命令，在窗格中显示运行的结果，创建了教学数据库。 112 数据定义2.1 建立数据库例1 根据图1给出的实2 数据定义2.1 建立数据

8、库 图2 用SQL语句创建数据库 122 数据定义2.1 建立数据库 122 数据定义2.2 定义表结构 在SQL语言中，利用CREATE TABLE语句创建表，其基本语法格式是： CREATE TABLE （ ， ， ， PRIMARY KEY ()， FOREIGN KEY () REFERENCES 表名()） 注：PRIMARY KEY子句定义主码，实现实体完整性定义；FOREIGN KEY子句定义外码，实现参照完整性定义。 132 数据定义2.2 定义表结构 在SQL语言中，利用C2 数据定义2.2 定义表结构 表1 常见的数据类型 数据类型说明范例CHAR(N)固定长度的字符串CH

9、AR(8)：长度是8个字节INT整数类型SMALLINT短整型数类型NUMERIC(P,D)共P位，其中小数位占d位NUMERIC(8,2)：整数6位，小数2位DATE日期型，包括年(4位)月日2001/10/13：2001年10月13日142 数据定义2.2 定义表结构数据类型说明范例CHAR2 数据定义2.2 定义表结构举例说明CREATE TABLE语句的使用方法。 例2 创建一个简单的表，该表的关系模式是：系（系编号，系名称）。 CREATE TABLE 系 ( 系编号 SMALLINT,系名称 CHAR(12) 152 数据定义2.2 定义表结构举例说明CREATE T2 数据定义2

10、.2 定义表结构例3 在创建系表的定义中包括实体完整性定义。 CREATE TABLE 系 ( 系编号 SMALLINT NOT NULL, 系名称 CHAR(12), PRIMARY KEY(系编号) 162 数据定义2.2 定义表结构例3 在创建系表的定义2 数据定义2.2 定义表结构 说明：在SQL-89中，规定PRIMARY KEY子句定义主码，NOT NULL关键词定义属性非空，所以，要用这两个定义来实现实体完整性规则。在SQL-92中，规定PRIMARY KEY子句定义主码且主码非空，所以，用一个子句就可以定义实体完整性。172 数据定义2.2 定义表结构 说明：在SQL-89中2

11、 数据定义2.2 定义表结构例4 在创建教工表的定义中包括实体完整性和参照完整性的定义。 CREATE TABLE 教工 (教工号 SMALLINT NOT NULL, 姓名 CHAR(8) NOT NULL, 性别 CHAR(2), 职称 CHAR(8), 工资 NUMERIC(8,2), 系编号 SMALLINT, PRIMARY KEY (教工号), FOREIGN KEY (系编号) REFERENCES 系(系编号) 182 数据定义2.2 定义表结构例4 在创建教工表的定2 数据定义2.2 定义表结构 例5 创建成绩表，包括实体完整性和参照完整性的定义。 CREATE TABLE

12、成绩 ( 学号 CHAR(6) NOT NULL, 课程编号 CHAR(8) NOT NULL, 分数 NUMERIC(4,1), PRIMARY KEY (学号,课程编号), FOREIGN KEY (学号) REFERENCES 学生(学号), FOREIGN KEY (课程编号) REFERENCES 课程(课程编号) 192 数据定义2.2 定义表结构 例5 创建成绩表，2 数据定义2.2 定义表结构注意：在上机操作时，创建表的顺序是先创建被参照关系，再创建参照关系，即“先父后子”。如果要反复练习创建表的过程，需要删除已经建立的表时，删除的顺序与创建表的顺序正相反，即“先子后父”。 2

13、02 数据定义2.2 定义表结构注意：在上机操作时，创建2 数据定义2.3 修改表结构 用SQL语言的ALTER TABLE 语句可以修改表的结构。下面结合实例说明ALTER TABLE 语句的使用方法。 1.增加新的属性，所有元组在这个新属性上都将赋值NULL。 语句格式是： ALTER TABLE ADD 例6 在系表中添加一个电话号码属性。 ALTER TABLE 系 ADD 电话号码 CHAR(8) 212 数据定义2.3 修改表结构 用SQL语言的ALTE2 数据定义2.3 修改表结构 2.修改属性的性质，语句格式是： ALTER TABLE MODIFY 注意：在SQL SERVE

14、R 2000系统中规定修改属性的性质，语句格式为： ALTER TABLE ALTER COLUMN 例7 修改系表中电话号码属性的宽度。 ALTER TABLE 系 ALTER COLUMN 电话号码 CHAR(13) 222 数据定义2.3 修改表结构 2.修改属性的性质，语2 数据定义2.3 修改表结构 删除一个属性，语句格式是： ALTER TABLE DROP 注意：在SQL SERVER 2000系统中应该写成： ALTER TABLE DROP COLUMN 例8 删除系表中电话号码属性。SQL 语句是： ALTER TABLE 系 DROP COLUMN 电话号码 232 数据

15、定义2.3 修改表结构 删除一个属性，语句格2 数据定义2.4 删除表 SQL语言中，删除表的语句格式是： DROP TABLE 242 数据定义2.4 删除表 242 数据定义2.4 删除表 例9 删除教工表。 DROP TABLE 教工 注意：系统不允许删除已由REFERENCES子句定义的被参照表。假设在教工表中定义外码参照系表，若要删除系表，就必须先删除教工表，否则系统拒绝删除操作。 252 数据定义2.4 删除表 例9 删除教工表。22 数据定义2.4 删除表例10 假设在教学数据库中创建了系、教工、学生、课程、成绩5个表，并且定义了参照完整性规则。如果要删除学生表，则必须先删除成绩

16、表。 DROP TABLE 成绩 DROP TABLE 学生 262 数据定义2.4 删除表例10 假设在教学数据库中2 数据定义2.4 删除表同理，要删除系表，必须先删除参照系表的其他表。即先删除成绩表，再删除学生、教工和课程表（这三个表之间的删除顺序无关紧要），最后删除系表。 DROP TABLE 成绩 DROP TABLE 学生 DROP TABLE 教工 DROP TABLE 课程 DROP TABLE 系272 数据定义2.4 删除表同理，要删除系表，必须先删除2 数据定义2.5 创建索引 在表的属性项上创建索引，可以加快查询速度。SQL语言中，创建索引的语句格式： CREATE I

17、NDEX ON () 282 数据定义2.5 创建索引 282 数据定义2.5 创建索引例11 对教授表的教工号属性建立一个名为SCODEX的索引。 CREATE INDEX SCODEX ON 教授(教工号) SQL语言允许建立唯一性索引。对侯选码建立唯一性索引，可以防止输入重复的数据。 292 数据定义2.5 创建索引例11 对教授表的教工号属2 数据定义2.5 创建索引 例12 对学生表的学号属性建立唯一性索引。 CREATE UNIQUE INDEX SCODEX ON 学生(学号) 说明：建立了唯一性索引之后，若输入一个重复的学号时，系统将产生错误提示： duplicate valu

18、e in index. 302 数据定义2.5 创建索引 例12 对学生表的学号3 数据的基本操作 本节将介绍SQL语言的数据操作功能，包括插入、更新和删除数据的操作命令。 313 数据的基本操作 本节将介绍SQL语言的数据操作功能，3 数据的基本操作 1 插入数据 在SQL 语言中，数据插入语句有三种格式。第一种格式是在表中插入一个元组，语法格式是：INSERT INTO VALUES (,.,) 323 数据的基本操作 1 插入数据 323 数据的基本操作 1 插入数据 例13 在系表中插入一行数据。 INSERT INTO 系 VALUES (102,管理科学) 显示所插入的数据 ： S

19、ELECT * FROM 系 显示结果： 系编号系名称102管理科学333 数据的基本操作 1 插入数据 例13 在系表中插3 数据的基本操作 1 插入数据 例14 向教工表中插入一行数据。 INSERT INTO 教工 VALUES (2001,葛小平,女,教授,3420.00,102) 显示所插入的数据 ： SELECT * FROM 教工 显示结果： 教工号姓名性别职称工资系编号2001葛小平女教授3420.00102343 数据的基本操作 1 插入数据 例14 向教工表中插入3 数据的基本操作 1 插入数据说明：这种格式适合向表中插入一整行数据。值得注意的是所列值的顺序必须与属性的顺序

20、和类型一致。 353 数据的基本操作 1 插入数据说明：这种格式适合向表3 数据的基本操作 1 插入数据 第二种格式用于在表中插入部分属性的值，语法格式是： INSERT INTO （）VALUES () 363 数据的基本操作 1 插入数据363 数据的基本操作 1 插入数据例15 向教工表中插入一个教工号、姓名和工资。 INSERT INTO 教工(教工号,姓名,工资) VALUES (2109,田新民,2650) 显示所插入的数据： SELECT * FROM 教工 显示结果： 教工号姓名性别职称工资系编号2001葛小平女教授3420.001022109田新民2650.00373 数据的

21、基本操作 1 插入数据例15 向教工表中插入3 数据的基本操作 1 插入数据 说明：这种格式适合在表的部分属性上插入数据。应注意的是值的顺序应该与所列属性的顺序和类型相一致。 383 数据的基本操作 1 插入数据 说明：这种格式适合在3 数据的基本操作 1 插入数据 第三种格式是将VALUES子句换成一个查询语句， 语法格式是： INSERT INTO （） SELECT 一个查询语句 FROM 393 数据的基本操作 1 插入数据 第三种格3 数据的基本操作 1 插入数据 例16 将教工表的姓名和工资拷贝到一个酬金表中。 403 数据的基本操作 1 插入数据 例16 将教工表的姓名3 数据的

22、基本操作 1 插入数据 （1） 创建一个酬金表。 CREATE TABLE 酬金 (教工姓名 CHAR(8), 工资 NUMERIC(8,2) 413 数据的基本操作 1 插入数据 （1） 创3 数据的基本操作 1 插入数据（2） 将教工表中姓名和工资两列数据拷贝到酬金表中。 INSERT INTO 酬金 (教工姓名,工资) SELECT 姓名,工资 FROM 教工 423 数据的基本操作 1 插入数据（2） 将教工表中姓名和3 数据的基本操作 1 插入数据（3）显示操作的结果： SELECT * FROM 酬金 显示结果： 教工姓名工资葛小平3420.00田新民2650.00433 数据的基

23、本操作 1 插入数据（3）显示操作的结果：教3 数据的基本操作 2 更新数据 SQL语言的数据更新语句的格式是： UPDATE SET =，=， WHERE 443 数据的基本操作 2 更新数据 SQL语言的数3 数据的基本操作 2 更新数据 例17 将教工表中职称为“教授”的工资增加10%。 UPDATE 教工 SET 工资=工资*1.1 WHERE 职称=教授 只修改职称为教授的元组的工资属性值。 453 数据的基本操作 2 更新数据 例17 将教工3 数据的基本操作 2 更新数据 例18 将教工表中所有人的工资增加5%。 UPDATE 教工 SET 工资=工资*1.05 修改所有元组的工

24、资属性的值。 463 数据的基本操作 2 更新数据 例18 将教3 数据的基本操作 3 删除数据 SQL语言中，删除数据语句的格式是： DELETE FROM WHERE 473 数据的基本操作 3 删除数据 473 数据的基本操作 3 删除数据 例19 从教工表中删除教工号为2001的教工。 DELETE FROM 教工 WHERE 教工号=2001 查看删除的结果： SELECT * FROM 教工 显示结果： 教工号姓名性别职称工资系编号2109田新民2650.00483 数据的基本操作 3 删除数据 例19 从教工3 数据的基本操作 3 删除数据 例20 删除酬金表的所有数据。 DEL

25、ETE FROM 酬金 查看删除的结果： SELECT * FROM 酬金 显示结果： 说明：这个语句只删除酬金表的数据，不删除表的结构，此时，酬金表是一个空表。 教工姓名工资493 数据的基本操作 3 删除数据 例20 删除酬金表4 数据的查询操作 本节将介绍SQL语言的数据查询的基本功能。其中包括查询语句的基本结构、更名运算、元组变量、字符串操作、排序操作和分组操作。 504 数据的查询操作 本节将介绍SQL语言的数据查询的基本4 数据的查询操作 4.1查询语句的基本结构 SQL查询语句的一般格式： SELECT , FROM , WHERE 514 数据的查询操作 4.1查询语句的基本结

26、构 4 数据的查询操作 4.1查询语句的基本结构查询语句的三个子句分别实现关系代数的一种运算：SELECT子句对应关系代数中的投影运算，列出查询结果中的属性。FROM子句对应关系代数中的笛卡儿积，列出查询需要搜索的关系。WHERE子句对应关系代数中的选择运算，条件表达式中所涉及的属性将属于FROM子句所列的关系。 524 数据的查询操作 4.1查询语句的基本结构查询语句的三4 数据的查询操作 4.1查询语句的基本结构 查询语句等价于关系代数表达式：A1,A2,.An(p(R1R2Rm) 其中：Ai表示属性i，Rj表示关系j，P表示选择条件。 这个表达式的含义是：首先计算R1、R2到Rm的笛卡儿

27、乘积，然后，选择满足条件P的元组，最后对选择的结果进行投影。 534 数据的查询操作 4.1查询语句的基本结构 查询4 数据的查询操作 4.1查询语句的基本结构 从查询语句等价的关系代数表达式，可以分析SQL查询语句的执行过程：构造FROM子句中关系的笛卡儿积。根据WHERE子句中的条件表达式，进行选择操作。根据SELECT子句给出的列名进行投影操作。 544 数据的查询操作 4.1查询语句的基本结构 从查询语4 数据的查询操作 4.1查询语句的基本结构 假设有关系R、S和一个SQL查询语句，如图3所示。分析SQL查询语句与关系代数运算的对应关系。 R S 图3 关系R、S和一个SQL查询语句

28、 A B C a1 b1 c2 a2 b2 c2 a3 b1 c4 A D E a1 d1 e2 a2 d2 e3查询语句：SELECT R.A, B, D, E FROM R , SWHERE R.A=S.A554 数据的查询操作 4.1查询语句的基本结构 假设有4 数据的查询操作 4.1查询语句的基本结构分析这个查询语句执行的过程：1.先计算 RS ABCADEa1b1c2a1d1e2a1b1c2a2d2e3a2b2c2a1d1e2a2b2c2a2d2e3a3b1c4a1d1e2a3b1c4a2d2e3564 数据的查询操作 4.1查询语句的基本结构分析这个查询4 数据的查询操作 4.1查

29、询语句的基本结构 2.依据条件 进行选择操作 ABCADEa1b1c2a1d1e2A2b2c2a2d2e3574 数据的查询操作 4.1查询语句的基本结构 2.依4 数据的查询操作 4.1查询语句的基本结构 进行投影操作 R.ABDEa1b1d1e2a2b2d2e3584 数据的查询操作 4.1查询语句的基本结构 进行投4 数据的查询操作 4.1查询语句的基本结构 说明：实际上，SQL不一定完全按照这个步骤进行操作。通常是采取某种等价的算法，例如，先选择，再做笛卡儿乘积，最后投影，用减少笛卡儿乘积次数的方法，来提高查询效率。 594 数据的查询操作 4.1查询语句的基本结构 说明：实4 数据的

30、查询操作 4.2 SELECT 子句 SQL查询语句的运算结果仍然是一个关系，这个关系的属性由SELECT子句的列名表给出。SELECT子句的参数有多种形式。 604 数据的查询操作 4.2 SELECT 子句 4 数据的查询操作 4.2 SELECT 子句 1. 指定某个或某一些属性。 例21 列出所有教授的姓名。 SELECT 姓名 FROM 教工 WHERE 职称=教授 查询结果：姓名葛小平康立华614 数据的查询操作 4.2 SELECT 子句 4 数据的查询操作 4.2 SELECT 子句 例22 列出所有教授的姓名和工资： SELECT 姓名,工资 FROM 教工 WHERE 职称

31、=教授 查询结果：姓名工资葛小平3420.00康立华3740.00624 数据的查询操作 4.2 SELECT 子句 例24 数据的查询操作 4.2 SELECT 子句 2. 指定FROM子句中所有关系的属性都将出现在查询结果中。 例23 列出教授的所有信息。 SELECT * FROM 教工 WHERE 职称=教授 查询结果： 教工号姓名性别职称工资系编号2001葛小平女教授3420.001022302康立华女教授3740.00103634 数据的查询操作 4.2 SELECT 子句 4 数据的查询操作 4.2 SELECT 子句 利用关键词DISTINCT消除重复出现的元组。 例24 列出

32、教工表中的系编号。（注意有重复的系编号） SELECT 系编号 FROM 教工 查询结果：系编号101102104101103102644 数据的查询操作 4.2 SELECT 子句 4 数据的查询操作 4.2 SELECT 子句 例25 列出教工表中的系编号并消除重复的元组。 SELECT DISTINCT 系编号 FROM 教工 查询结果： 系编号101102104103654 数据的查询操作 4.2 SELECT 子句 例4 数据的查询操作 4.2 SELECT 子句 4. 在SELECT子句中还可以对输出的属性重命名。 例26 已知学分=学时/17，计算每一门课程的学分数。 SELEC

33、T 课程名,学时/17 AS 学分 FROM 课程 查询结果： 课程名学分数学4英语5计算机6经济学3664 数据的查询操作 4.2 SELECT 子句 4 数据的查询操作 4.2 SELECT 子句 5. SELECT子句中可以包含表达式 例27 显示教授的工资和提高10%的工资额。SELECT 姓名,工资,工资*1.1 AS 工资*1.1FROM 教工 WHERE 职称=教授 查询结果： 姓名工资工资*1.1葛小平3420.003762.00康立华3740.002915.00674 数据的查询操作 4.2 SELECT 子句 4 数据的查询操作 4.2 SELECT 子句 例28 显示田平

34、平同学出生100天的日期。 SELECT 姓名, 出生年月+100 AS 出生年月+100 FROM 学生 WHERE 姓名=田平平 查询结果： 姓名出生年月出生年月+100田平平08/05/198011/13/1980684 数据的查询操作 4.2 SELECT 子句 4 数据的查询操作 4.2 SELECT 子句 在后面的章节还会介绍SELECT子句的一些用法，例如，SELECT子句的参数还可以是算术函数。 694 数据的查询操作 4.2 SELECT 子句 在后4 数据的查询操作 4.3 WHERE子句 WHERE子句中将给出查询语句的选择条件，条件表达式中可使用逻辑运算符、比较运算符和

35、算术运算符。WHERE子句中使用的运算符如下： 1. 比较运算符 、=、=、，用于字符串表达式、算术表达式，以及特殊的类型（如日期类型）的比较。比较表达式运算的结果是逻辑值真（T）或假（F），即表达式成立为真，否则为假。 704 数据的查询操作 4.3 WHERE子句 4 数据的查询操作 4.3 WHERE子句 例29 列出教工表中工资在3000元以上的名单。 SELECT 姓名 FROM 教工 WHERE 工资= 3000 714 数据的查询操作 4.3 WHERE子句 例4 数据的查询操作 4.3 WHERE子句 例30 列出学生表中1980年1月1日之后出生的学生名单。 SELECT 姓

36、名 FROM 学生 WHERE 出生年月= 1980-1-1 注意：进行比较的数据类型必须一致。 724 数据的查询操作 4.3 WHERE子句 例30 4 数据的查询操作 4.3 WHERE子句 2. 逻辑运算符AND（逻辑与）、OR（逻辑或）、NOT（逻辑非），可将多个比较表达式连接起来，构成复杂的逻辑表达式，表示复杂的条件。逻辑表达式运算的结果仍是逻辑值真（T）或假（F）。 734 数据的查询操作 4.3 WHERE子句 24 数据的查询操作 4.3 WHERE子句 例31 列出学生表中在1980年1月1日之后出生的男同学名单。 SELECT 姓名 FROM 学生 WHERE 出生年月=

37、 1980-1-1 AND 性别=男 744 数据的查询操作 4.3 WHERE子句 例31 4 数据的查询操作 4.3 WHERE子句 例32 列出教工表中教授或副教授中工资低于3000元的名单。 SELECT 姓名 FROM 教工 WHERE (职称=教授 OR 职称=副教授)AND 工资=1000 AND 工资=2000 885 字符串操作 例38检索工资在1000元到2000元范围6 排列元组的显示顺序 在SQL的查询语句中，可以利用ORDER BY子句，对查询结果进行排序。ORDER BY子句是查询语句的可选项，语法格式是： SELECT , FROM , WHERE ORDER B

38、Y DESC ASC 其中：DESC 表示降序，ASC表示升序，若缺省默认为升序。 896 排列元组的显示顺序 在SQL的查询语句中，可以利用O6 排列元组的显示顺序 例39 按学号的升序，显示系编号等于101的学生信息： SELECT * FROM 学生 WHERE 系编号=101 ORDER BY 学号 查询结果： 学号姓名性别出生年月系编号1022田平平女08/05/801011134包立琪女03/14/811011223姜明明男12/05/801011354王立平女12/05/81101906 排列元组的显示顺序 例39 按学号的升序，显示系编号等7 集合查询 在第2章介绍了关系代数并

39、、交和差运算的概念。本节将用SQL语言实现这些关系代数的运算。SQL-92在关系上的UNION、INTERSECT和EXCEPT操作对应于关系代数中并（）、交（）、差（）运算。 917 集合查询 在第2章介绍了关系代数并、交和差运算的概7 集合查询 设有关系R和S如图4所示。 R S 图4 关系R和S 下面举例说明在SQL语言中，实现并、交、差运算的方法。 ABCDa1b1c1d1a2b2c2d2a3b2c3d1a4b4c4d3ABCa1b1c1a5b1c2a6b3c3a4b4c4927 集合查询 设有关系R和S如图4所示。ABCDa1b17 集合查询7.1 并操作 例40 求R和S在属性A、

40、B、C上投影的并集， 其关系代数表达式是： A , B,C(R) A , B,C(S) SQL语句是： ( SELECT A,B,C FROM R ) UNION ( SELECT A,B,C FROM S ) 937 集合查询7.1 并操作 例40 求R和S在属性A、B7 集合查询7.1 并操作 运行结果： 说明：与SELECT语句不同，UNION操作自动消除重复的元组。如果想保留所有重复，可以用UNION ALL代替UNION。 ABCa1b1c1a2b2c2a3b2c3a4b4c4a5b1c2a6b3c3947 集合查询7.1 并操作 运行结果：ABC7 集合查询7.1 并操作 例41求

41、关系R和S在属性B上投影的并操作，要保留重复元组。关系代数表达式是： B(R)B(S) SQL语句是： ( SELECT B FROM R) UNION ALL ( SELECT B FROM S) 957 集合查询7.1 并操作 例41求关系R和S在属性B7 集合查询7.1 并操作 运行结果： 说明：UNION ALL操作可以保留重复的元组。 Bb1b2b2b4b1b1b3b4967 集合查询7.1 并操作 运行结果：Bb1b2b27 集合查询7.2 交操作 例42 求关系R和S在属性A、B、C上投影的交集。 关系代数表达式是： A,B,C(R) A,B,C(S) SQL语句： ( SELE

42、CT A,B,C FROM R ) INTERSECT ( SELECT A,B,C FROM S )977 集合查询7.2 交操作 例42 求关系R和S7 集合查询7.2 交操作 运行结果： 说明：INTERSECT操作自动消除重复。如果想保留所有重复，可以用INTERSECT ALL代替INTERSECT。 ABCa1b1c1a4b4c4987 集合查询7.2 交操作 运行结果：ABCa1b17 集合查询7.2 交操作 例43 求属于R且也属于S的B值，要求保留重复值。 关系代数表达式是： B(R) B(S)） SQL语句是： ( SELECT B FROM R ) INTERSECT A

43、LL ( SELECT B FROM S ) 997 集合查询7.2 交操作 例43 求属于R且也属于S7 集合查询7.2 交操作 运行结果： 说明：INTERSECT ALL操作可以保留重复的元组。注：SQL SERVER2000的T-SQL语言并不直接支持INTERSECT操作符，可用EXISTS可以模拟INTERSECT操作。 Bb1b4b1b1b41007 集合查询7.2 交操作 运行结果：Bb1b4b7 集合查询7.3 差操作 例44 求关系R和S在属性A、B、C上投影的差集。 关系代数表达式是： A,B,C(R)-A,B,C(S) SQL语句是： ( SELECT A , B, C

44、 FROM R ) EXCEPT ( SELECT A , B, C FROM S ) 1017 集合查询7.3 差操作 例44 求关系R和S在属7 集合查询 7.3 差操作 运行结果是： 说明：EXCEPT操作能够自动消除重复。 如果想保留所有重复，可以用EXCEPT ALL 代替EXCEPT。 ABCa2b2c2a3b2c31027 集合查询 7.3 差操作 运行结果是：ABCa27 集合查询 7.3 差操作 例45 找出属于R，且不属于S的B值，要求保留重复值。关系代数表达式： B(R)-B(S) SQL语句： ( SELECT B FROM R ) EXCEPT ALL ( SELEC

45、T B FROM S ) 1037 集合查询 7.3 差操作 例45 找出属于R，且7 集合查询 7.3 差操作运行结果： 说明：EXCEPT ALL将保留重复。在SQL SERVER2000的TRANSACTION_SQL语言并不直接支持EXCEPT操作符，可以用NOT EXISTS模拟EXCEPT操作。 Bb2b21047 集合查询 7.3 差操作运行结果：Bb2b21048 聚集函数 聚集函数是对一组值进行计算，并返回单个值的函数。聚集函数经常与查询语句的GROUP BY子句一同使用，在查询结果中生成汇总值。表2 中列举SQL的聚集函数。 1058 聚集函数 聚集函数是对一组值进行计算，

46、并返回单个值的函8 聚集函数 表2 SQL的聚集函数 函数名功能参数类型AVG求平均值数值SUM求总和数值MAX求最大值数值、其他类型MIN求最小值数值、其他类型COUNT计数数值、其他类型1068 聚集函数 表2 SQL的聚集8 聚集函数 其中AVG和SUM函数的参数必须是数值型，其他函数的参数还可以是非数值型，如字符串。聚集函数只能作为SELECT和HAVING子句的参数。除了COUNT函数之外，其他聚集函数忽略空值。 1078 聚集函数 其中AVG和SUM函数的参数必须是数值型，8 聚集函数 1. 求平均值函数 聚集函数AVG用于计算列中数值的平均值，所以函数AVG的参数必须是数值型。

47、1088 聚集函数 1. 求平均值函数1088 聚集函数 例46 求教工表中教授工资的平均值。 SELECT AVG (工资) AS 平均工资 FROM 教工 WHERE 职称=教授 查询结果： 平均工资35801098 聚集函数 例46 求教工表中教授工资的平均值。 平均工8 聚集函数 2. 求总和函数 聚集函数SUM用于计算列中数值的总和，函数SUM的参数必须是数值型。1108 聚集函数 2. 求总和函数1108 聚集函数 例47 求所有教工的工资总和。 SELECT SUM (工资) AS 工资总和 FROM 教工 查询结果：工资总和1786001118 聚集函数 例47 求所有教工的工

48、资总和。工资总和17868 聚集函数 求最大值和最小值函数 聚集函数MAX用于求列中的最大值，MIN求列中的最小值，这两个的参数允许是数值型的，也可以是其他数据类型（如字符型和时间型的数据）。 1128 聚集函数 求最大值和最小值函数1128 聚集函数 例48 找出教工中最高工资和最低工资。 SELECT MAX(工资)，MIN(工资) FROM 教工 查询结果： MAX (工资)MIN (工资)3740.002130.001138 聚集函数 例48 找出教工中最高工资和最低工资。8 聚集函数 例49 显示学生中最早和最晚的出生日期。 SELECT MIN (出生日期), MAX (出生日期)

49、 FROM 学生 查询结果： 注意：出生日期是日期型数据，日期型数据的特点， 最大数据恰好是最小年龄。 MIN(出生日期)MAX (出生日期)10/05/197904/13/19821148 聚集函数 例49 显示学生中最早和最晚的出生日期。MI8 聚集函数 4. 计数器函数聚集函数COUNT统计表中的行数。COUNT函数有3种格式： COUNT（*）统计所有行数，包括含有空值的行。COUNT（表达式）计算每一行中表达式的值，并返回非空值的行数。COUNT(DISTINCT (表达式)计算每一行表达式的值，并返回唯一值且非空值的行数。 1158 聚集函数 4. 计数器函数1158 聚集函数 例

50、50 查询学生的总人数。 SELECT COUNT(*)AS 人数 FROM 学生 查询结果： 人数101168 聚集函数 例50 查询学生的总人数。 人数1018 聚集函数 例51 假设在学生表中增加一名新生，只插入该生的学号和姓名。执行下列插入语句： INSERT INTO STUDENT(SNO,SNAME) VALUES(991033,葛小燕) 分别利用COUNT（）函数的三种格式， 观察COUNT（）函数的使用方法。 1178 聚集函数 例51 假设在学生表中增加一名新8 聚集函数 （1） 如果要统计学生表的行数，即包括系编号为空值的行在内，则使用第一种格式。 SELECT COUN

51、T (*) FROM 学生 查询结果： COUNT(*)111188 聚集函数 （1） 如果要统计学生表的行数，即包括系编8 聚集函数（2）如果要统计学生表中系编号非空值的行数，即不包括系编号为空值的行，则使用第二种格式。 SELECT COUNT(系编号) FROM 学生 查询结果： COUNT(系编号) 101198 聚集函数（2）如果要统计学生表中系编号非空值的行数，即不8 聚集函数（3）如果要统计成绩表中所有选课的学生人数，即不管一名学生选了几门课程，都只计算一次，则使用第三种格式。 SELECT COUNT (DISTINCT (学号) AS 人数 FROM 成绩 查询结果： 人数5

52、1208 聚集函数（3）如果要统计成绩表中所有选课的学生人数，即不9 GROUP BY 和HAVING子句 在SQL语言的查询语句中，可以用GROUP BY子句实现对元组的分组功能。还可以利用HAVING子句对GROUP BY分组的结果进行筛选，保留满足条件的分组。GROUP BY子句HAVING子句是查询语句的可选项，语法格式是： SELECT , FROM , WHERE GROUP BY HAVING 1219 GROUP BY 和HAVING子句 在SQL语言9 GROUP BY 和HAVING子句 9.1 GROUP BY子句 在GROUP BY子句中的分组表达式可以是一个属性或者多

53、个属性，其功能是将在分组表达式上具有相同值的元组放在一个组内。 1229 GROUP BY 和HAVING子句 9.1 G9 GROUP BY 和HAVING子句 9.1 GROUP BY子句例52 统计学生表中男生和女生的人数。 SELECT 性别, COUNT (*) FROM 学生 GROUP BY 性别 查询结果： 性别COUNT (*)男5女51239 GROUP BY 和HAVING子句 9.1 G9 GROUP BY 和HAVING子句 9.1 GROUP BY子句 例52 统计成绩表中，每一门课程的平均成绩。 SELECT 课程编号,AVG (分数) FROM 成绩 GROUP

54、 BY 课程编号 查询结果： 注意：带有GROUP BY子句的查询语句中，SELECT子句的列名中必须包括分组表达式，还可以包括集聚函数，除此而外不能有其他列名。 课程编号AVG (分数)C170C284C365C4871249 GROUP BY 和HAVING子句 9.1 G9 GROUP BY 和HAVING子句 9.1 GROUP BY子句 例53 查询教工表中每一种职称的最高工资和最低工资。 SELECT 职称, MAX (工资), MIN (工资) FROM 教工 GROUP BY 职称 查询结果： 职称MAX(工资)MIN(工资)教授3740.003420.00副教授3190.00

55、3140.00讲师224002130.001259 GROUP BY 和HAVING子句 9.1 G9 GROUP BY 和HAVING子句 9.2 HAVING子句 使用GROUP BY子句时，可以利用HAVING子句对GROUP BY分组的结果进行筛选，保留满足条件的分组。HAVING子句的格式： HAVING 1269 GROUP BY 和HAVING子句 9.2 HA9 GROUP BY 和HAVING子句 9.2 HAVING子句 HAVING与WHERE子句都有，注意两者之间的区别。WHERE子句中的是在GROUP BY分组之前起作用，而HAVING子句的是在形成分组后起作用，所以

56、，在HAVING的条件表达式中可以使用聚集函数（这一点与WHERE不同）。 1279 GROUP BY 和HAVING子句 9.2 HA9 GROUP BY 和HAVING子句 9.2 HAVING子句 例54 对于成绩表中分数在60以上的行按照学号分组，其中只包含选课数大于2且平均分超过70的学号、选课数和平均分。 SELECT 学号,COUNT(课程编号 ), AVG (分数) FROM 成绩 WHERE 分数=60 GROUP BY 学号 HAVING COUNT(课程编号)2 AND AVG(分数) 70 分析这个语句的WHERE、GROUP BY和HAVING子句执行的顺序。已知成绩

57、表如图4所示。 1289 GROUP BY 和HAVING子句 9.2 HA9 GROUP BY 和HAVING子句 9.2 HAVING子句 成绩 图4 成绩表 学号课程号分数1022C101881022C102672124C101562124C102952124C103454021C104874021C102784021C103671223C101661223C102893012C102933012C103841299 GROUP BY 和HAVING子句 9.2 HA9 GROUP BY 和HAVING子句 9.2 HAVING子句（1） 这个语句首先执行WHERE子句，选择满足条件的1

58、0行（去除不及格的2行）。 学号课程号分数1022C101881022C102672124C102954021C104874021C102784021C103671223C101661223C102893012C102933012C103841309 GROUP BY 和HAVING子句 9.2 HA9 GROUP BY 和HAVING子句 9.2 HAVING子句（2） 然后执行GROUP BY子句，将10行按照学号分成5组。 学号课程号分数1022C101881022C102672124C102954021C104874021C102784021C103671223C101661223C1

59、02893012C102933012C103841319 GROUP BY 和HAVING子句 9.2 HA9 GROUP BY 和HAVING子句 9.2 HAVING子句 （3）最后执行HAVING子句，以“选课数大于2且平均分超过70”为条件筛选分组的结果，最后满足条件的组只有1组。 运行结果 ： 学号课程号分数4021C104874021C102784021C10367学号COUNT(课程号) AVG(分数) 4021377.331329 GROUP BY 和HAVING子句 9.2 HA9 GROUP BY 和HAVING子句 9.2 HAVING子句 例55 统计成绩表中选修人数超

60、过2以上的课程编号和人数。 SELECT 课程编号,COUNT (*) FROM 成绩 GROUP BY 课程编号 HAVING COUNT(*)2 首先，按照课程编号分成4组，再过滤掉选修人数小于或等于2的组。 1339 GROUP BY 和HAVING子句 9.2 HA10 空值 在SQL语言中，允许使用NULL值表示某个属性的值为空（即没有值）。对于空值的处理方法和原则是一个容易混淆的问题。 13410 空值 在SQL语言中，允许使用NULL值表示某个属10 空值 10.1 IS NULL和IS NOT NULL运算符 在SQL语言中，允许在条件表达式中使用特殊的运算符号IS NULL测