SQL结构查询语言课件.ppt

1、 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商业化的关系数据库管理系统。SQL

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

3、语言的应用实例。关系和样本数据如图1所示。系（系编号，系名称）教工（教工号，姓名，性别，职称，工资，系编号）学生（学号，姓名，性别，出生年月，专业，家庭地址，系编号）课程（课程编号，课程名称，学时，系编号）成绩（学号，课程编号，分数）系课程图1 一组关系模式和样本数据（a）系编号系名称101数学102计算机103外语104经济课程号 课程名学时系编号C1数学681C2英语853C3计算机682C4经济学514 图1 一组关系模式和样本数据（b）教工号姓 名性别职称工 资系编号2101葛小平女教授3420.001012203李长江男副教授3190.001022405姜立伟男副教授3140.001

4、042104张丽丽女讲师224001012302康立华女教授3740.001032205王伟平男讲师2130.00102教工 学 号课程编号分数1022C1881022C2672124C1772124C2952124C3454021C4874021C2784021C3671223C1661223C2893012C2933012C384成绩 图1 一组关系模式和样本数据（c）图1 一组关系模式和样本数据（d）学 号姓 名性别出生年月系编号1022田平平女08/05/19801012124郭黎明男03/04/19811024021何明慧女04/12/19821041223姜明明男12/05/198

5、01013012何漓江男10/05/19791032104康纪平女03/04/19811024125康嘉家男07/05/19801041134包立琪女03/14/19811014115王海洋男04/13/19821041354王立平女12/05/1981101学生 说明：本书选用SQL Server2000系统作为上机实验平台，所有例题和上机操作实验案例都在SQL Server2000查询分析器中运行通过，并且所选用的题目尽量符合SQL的标准格式，如果读者选用其他数据库管理系统作为实验环境，可能有微小的区别。绝 大 多 数 关 系 D B M S，使 用CREATE DATABASE命令建立数

6、据库，其语法格式：CREATE DATABASE 例例1 1 根据图1给出的实例，用SQL语言创建一个教学数据库。CREATE DATABASE 教学 当进入SQL_Server2000系统的查询分析器后，如图2所示，在查询窗口中输入命令：CREATE DATABASE 教学，然后，点击绿色的 按钮执行命令，在窗格中显示运行的结果，创建了教学数据库。图2 用SQL语句创建数据库 在SQL语言中，利用CREATE TABLE语句创建表，其基本语法格式是：CREATE TABLE （，PRIMARY KEY()，FOREIGN KEY()REFERENCES 表名()）注：PRIMARY KEY子

7、句定义主码，实现实体完整性定义；FOREIGN KEY子句定义外码，实现参照完整性定义。表1 常见的数据类型 数据类型说明范例CHAR(N)固定长度的字符串CHAR(8)：长度是8个字节INT整数类型SMALLINT短整型数类型NUMERIC(P,D)共P位，其中小数位占d位NUMERIC(8,2)：整数6位，小数2位DATE日期型，包括年(4位)月日 2001/10/13：2001年10月13日举例说明CREATE TABLE语句的使用方法。例例2 2 创建一个简单的表，该表的关系模式是：系（系编号，系名称）。CREATE TABLE 系 (系编号 SMALLINT,系名称系名称 CHAR(

8、12)例例3 3 在创建系表的定义中包括实体完整性定义。CREATE TABLE 系 (系编号 SMALLINT NOT NULL,系名称 CHAR(12),PRIMARY KEY(系编号)说明：在SQL-89中，规定PRIMARY KEY子句定义主码，NOT NULL关键词定义属性非空，所以，要用这两个定义来实现实体完整性规则。在SQL-92中，规定PRIMARY KEY子句定义主码且主码非空，所以，用一个子句就可以定义实体完整性。例例4 4 在创建教工表的定义中包括实体完整性和参照完整性的定义。CREATE TABLE 教工 (教工号 SMALLINT NOT NULL,姓名 CHAR(8

9、)NOT NULL,性别 CHAR(2),职称 CHAR(8),工资 NUMERIC(8,2),系编号 SMALLINT,PRIMARY KEY (教工号),FOREIGN KEY(系编号)REFERENCES 系(系编号)例例5 5 创建成绩表，包括实体完整性和参照完整性的定义。CREATE TABLE 成绩 (学号 CHAR(6)NOT NULL,课程编号 CHAR(8)NOT NULL,分数 NUMERIC(4,1),PRIMARY KEY(学号,课程编号),FOREIGN KEY(学号)REFERENCES 学生(学号),FOREIGN KEY(课程编号)REFERENCES 课程(课

10、程编号)注意：在上机操作时，创建表的顺序是先创建被参照关系，再创建参照关系，即“先父后子”。如果要反复练习创建表的过程，需要删除已经建立的表时，删除的顺序与创建表的顺序正相反，即“先子后父”。用SQL语言的ALTER TABLE 语句可以修改表的结构。下面结合实例说明ALTER TABLE 语句的使用方法。1.增加新的属性，所有元组在这个新属性上都将赋值NULL。语句格式是：ALTER TABLE ADD 例6 在系表中添加一个电话号码属性。ALTER TABLE 系 ADD 电话号码 CHAR(8)2.2.修改属性的性质，语句格式是：ALTER TABLE MODIFY 注意：在SQL SE

11、RVER 2000系统中规定修改属性的性质，语句格式为：ALTER TABLE ALTER COLUMN 例例7 7 修改系表中电话号码属性的宽度。ALTER TABLE 系 ALTER COLUMN 电话号码 CHAR(13)删除一个属性，语句格式是：ALTER TABLE DROP 注意：在SQL SERVER 2000系统中应该写成：ALTER TABLE DROP COLUMN 例例8 8 删除系表中电话号码属性。SQL 语句是：ALTER TABLE 系 DROP COLUMN 电话号码 SQL语言中，删除表的语句格式是：DROP TABLE 例例9 9 删除教工表。DROP TAB

12、LE 教工 注意：系统不允许删除已由REFERENCES子句定义的被参照表。假设在教工表中定义外码参照系表，若要删除系表，就必须先删除教工表，否则系统拒绝删除操作。例例10 10 假设在教学数据库中创建了系、教工、学生、课程、成绩5个表，并且定义了参照完整性规则。如果要删除学生表，则必须先删除成绩表。DROP TABLE 成绩 DROP TABLE 学生 同理，要删除系表，必须先删除参照系表的其他表。即先删除成绩表，再删除学生、教工和课程表（这三个表之间的删除顺序无关紧要），最后删除系表。DROP TABLE 成绩 DROP TABLE 学生 DROP TABLE 教工 DROP TABLE

13、课程 DROP TABLE 系 在表的属性项上创建索引，可以加快查询速度。SQL语言中，创建索引的语句格式：CREATE INDEX ON()例例11 对教授表的教工号属性建立一个名为SCODEX的索引。CREATE INDEX SCODEX ON 教授(教工号)SQL语言允许建立唯一性索引。对侯选码建立唯一性索引，可以防止输入重复的数据。例例12 12 对学生表的学号属性建立唯一性索引。CREATE UNIQUE INDEX SCODEX ON 学生(学号)说明：建立了唯一性索引之后，若输入一个重复的学号时，系统将产生错误提示：duplicate value in index.本节将介绍SQ

14、L语言的数据操作功能，包括插入、更新和删除数据的操作命令。在SQL 语言中，数据插入语句有三种格式。第一种格式是在表中插入一个元组，语法格式是：INSERT INTO VALUES(,.,)例例13 13 在系表中插入一行数据。INSERT INTO 系 VALUES(102,管理科学)显示所插入的数据：SELECT*FROM 系 显示结果：系编号系名称102管理科学 例例14 14 向教工表中插入一行数据。INSERT INTO 教工 VALUES(2001,葛小平,女,教授,3420.00,102)显示所插入的数据：SELECT*FROM 教工 显示结果：教工号姓名性别 职称工资系编号20

15、01葛小平女教授 3420.00102说明：这种格式适合向表中插入一整行数据。值得注意的是所列值的顺序必须与属性的顺序和类型一致。第二种格式用于在表中插入部分属性的值，语法格式是：INSERT INTO INSERT INTO （）VALUES(VALUES()例例15 15 向教工表中插入一个教工号、姓名和工资。INSERT INTO 教工(教工号,姓名,工资)VALUES(2109,田新民,2650)显示所插入的数据：SELECT*FROM 教工 显示结果：教工号姓名性别 职称工资系编号2001葛小平女教授 3420.001022109田新民2650.00 说明：这种格式适合在表的部分属性

16、上插入数据。应注意的是值的顺序应该与所列属性的顺序和类型相一致。第三种格式是将VALUES子句换成一个查询语句，语法格式是：INSERT INTO（）SELECT 一个查询语句 FROM 例例16 将教工表的姓名和工资拷贝到一个酬金表中。（1）创建一个酬金表。CREATE TABLE 酬金 (教工姓名 CHAR(8),工资 NUMERIC(8,2)（2）将教工表中姓名和工资两列数据拷贝到酬金表中。INSERT INTO 酬金(教工姓名,工资)SELECT 姓名,工资 FROM 教工 （3）显示操作的结果：SELECT*FROM 酬金 显示结果：教工姓名工资葛小平3420.00田新民2650.0

17、0 SQL语言的数据更新语句的格式是：UPDATE SET=，=，WHERE 例例17 17 将教工表中职称为“教授”的工资增加10%。UPDATE 教工 SET 工资=工资*1.1 WHERE 职称=教授 只修改职称为教授的元组的工资属性值。例例18 18 将教工表中所有人的工资增加5%。UPDATE 教工 SET 工资=工资*1.05 修改所有元组的工资属性的值。SQL语言中，删除数据语句的格式是：DELETE FROM WHERE 例例19 19 从教工表中删除教工号为2001的教工。DELETE FROM 教工 WHERE 教工号=2001 查看删除的结果：SELECT*FROM 教工

18、 显示结果：教工号姓名性别职称工资系编号2109田新民2650.00 例例20 20 删除酬金表的所有数据。DELETE FROM 酬金 查看删除的结果：SELECT*FROM 酬金 显示结果：说明：这个语句只删除酬金表的数据，不删除表的结构，此时，酬金表是一个空表。教工姓名工资 本节将介绍SQL语言的数据查询的基本功能。其中包括查询语句的基本结构、更名运算、元组变量、字符串操作、排序操作和分组操作。SQL查询语句的一般格式：SELECT ,FROM ,WHERE 查询语句的三个子句分别实现关系代数的一种运算：1.SELECT子句对应关系代数中的投影运算，列出查询结果中的属性。2.FROM子句

19、对应关系代数中的笛卡儿积，列出查询需要搜索的关系。3.WHERE子句对应关系代数中的选择运算，条件表达式中所涉及的属性将属于FROM子句所列的关系。查询语句等价于关系代数表达式：A1,A2,.An(p(R1R2Rm)其中：Ai表示属性i，Rj表示关系j，P表示选择条件。这个表达式的含义是：首先计算R1、R2到Rm的笛卡儿乘积，然后，选择满足条件P的元组，最后对选择的结果进行投影。从查询语句等价的关系代数表达式，可以分析SQL查询语句的执行过程：1.构造FROM子句中关系的笛卡儿积。2.根据WHERE子句中的条件表达式，进行选择操作。3.根据SELECT子句给出的列名进行投影操作。假设有关系R、

20、S和一个SQL查询语句，如图3所示。分析SQL查询语句与关系代数运算的对应关系。R S 图3 关系R、S和一个SQL查询语句 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.A分析这个查询语句执行的过程：1.先计算 RS ABCADEa1b1c2a1d1e2a1b1c2a2d2e3a2b2c2a1d1e2a2b2c2a2d2e3a3b1c4a1d1e2a3b1c4a2d2e3 2.依据条件 进行选择操作 ABCADEa1b1c2a1d1e2A2b2

21、c2a2d2e3 进行投影操作 R.ABDEa1b1d1e2a2b2d2e3 说明：实际上，SQL不一定完全按照这个步骤进行操作。通常是采取某种等价的算法，例如，先选择，再做笛卡儿乘积，最后投影，用减少笛卡儿乘积次数的方法，来提高查询效率。SQL查询语句的运算结果仍然是一个关系，这个关系的属性由SELECT子句的列名表给出。SELECT子句的参数有多种形式。1.指定某个或某一些属性。指定某个或某一些属性。例例21 21 列出所有教授的姓名。SELECT 姓名 FROM 教工 WHERE 职称=教授 查询结果：姓名葛小平康立华 例例22 22 列出所有教授的姓名和工资：SELECT 姓名,工资

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

23、系编号101102104101103102 例例25 25 列出教工表中的系编号并消除重复的元组。SELECT DISTINCT 系编号 FROM 教工 查询结果：系编号101102104103 4.在在SELECT子句中还可以对输出的属性重命名。子句中还可以对输出的属性重命名。例例26 26 已知学分=学时/17，计算每一门课程的学分数。SELECT 课程名,学时/17 AS 学分 FROM 课程 查询结果：课程名学分数学4英语5计算机6经济学3 5.SELECT子句中可以包含表达式 例例27 27 显示教授的工资和提高10%的工资额。SELECT 姓名,工资,工资*1.1 AS 工资*1.

24、1FROM 教工 WHERE 职称=教授 查询结果：姓名工资工资*1.1葛小平3420.003762.00康立华3740.002915.00 例例28 28 显示田平平同学出生100天的日期。SELECT 姓名,出生年月+100 AS 出生年月+100 FROM 学生 WHERE 姓名=田平平 查询结果：姓名出生年月出生年月+100田平平08/05/198011/13/1980 在后面的章节还会介绍SELECT子句的一些用法，例如，SELECT子句的参数还可以是算术函数。WHERE子句中将给出查询语句的选择条件，条件表达式中可使用逻辑运算符、比较运算符和算术运算符。WHERE子句中使用的运算符

25、如下：1.比较运算符、=、=、，用于字符串表达式、算术表达式，以及特殊的类型（如日期类型）的比较。比较表达式运算的结果是逻辑值真（T）或假（F），即表达式成立为真，否则为假。例例29 29 列出教工表中工资在3000元以上的名单。SELECT 姓名 FROM 教工 WHERE 工资=3000 例例30 30 列出学生表中1980年1月1日之后出生的学生名单。SELECT 姓名 FROM 学生 WHERE 出生年月=1980-1-1 注意：进行比较的数据类型必须一致。2.逻辑运算符AND（逻辑与）、OR（逻辑或）、NOT（逻辑非），可将多个比较表达式连接起来，构成复杂的逻辑表达式，表示复杂的条件

26、。逻辑表达式运算的结果仍是逻辑值真（T）或假（F）。例例31 31 列出学生表中在1980年1月1日之后出生的男同学名单。SELECT 姓名 FROM 学生 WHERE 出生年月=1980-1-1 AND 性别=男 例例32 32 列出教工表中教授或副教授中工资低于3000元的名单。SELECT 姓名 FROM 教工 WHERE(职称=教授 OR 职称=副教授)AND 工资=1000 AND 工资=2000 在SQL的查询语句中，可以利用ORDER BY子句，对查询结果进行排序。ORDER BY子句是查询语句的可选项，语法格式是：SELECT ,FROM ,WHERE ORDER BY DES

27、C ASC 其中：DESC 表示降序，ASC表示升序，若缺省默认为升序。例例39 39 按学号的升序，显示系编号等于101的学生信息：SELECT *FROM 学生 WHERE 系编号=101 ORDER BY 学号 查询结果：学号姓名性别出生年月系编号1022田平平女08/05/801011134包立琪女03/14/811011223姜明明男12/05/801011354王立平女12/05/81101 在第2章介绍了关系代数并、交和差运算的概念。本节将用SQL语言实现这些关系代数的运算。SQL-92在关系上的UNION、INTERSECT和EXCEPT操作对应于关系代数中并（）、交（）、差（

28、）运算。设有关系R和S如图4所示。R S 图4 关系R和S 下面举例说明在SQL语言中，实现并、交、差运算的方法。ABCDa1b1c1d1a2b2c2d2a3b2c3d1a4b4c4d3ABCa1b1c1a5b1c2a6b3c3a4b4c4例例40 40 求R和S在属性A、B、C上投影的并集，其关系代数表达式是：A,B,C(R)A,B,C(S)SQL语句是：(SELECT A,B,C FROM R)UNION (SELECT A,B,C FROM S)运行结果：说明：与SELECT语句不同，UNION操作自动消除重复的元组。如果想保留所有重复，可以用UNION ALL代替UNION。AB BC

29、 Ca1b1c1a2b2c2a3b2c3a4b4c4a5b1c2a6b3c3 例例4141求关系R和S在属性B上投影的并操作，要保留重复元组。关系代数表达式是：B B(R)(R)B B(S)(S)SQL语句是：(SELECT B FROM R)UNION ALL (SELECT B FROM S)运行结果：说明：UNION ALL操作可以保留重复的元组。Bb1b2b2b4b1b1b3b4 例例42 42 求关系R和S在属性A、B、C上投影的交集。关系代数表达式是：A,B,C(R)A,B,C(S)SQL语句：(SELECT A,B,C FROM R)INTERSECT (SELECT A,B,C

30、 FROM S)运行结果：说明：INTERSECT操作自动消除重复。如果想保留所有重复，可以用INTERSECT ALL代替INTERSECT。ABCa1b1c1a4b4c4 例例4343 求属于R且也属于S的B值，要求保留重复值。关系代数表达式是：B(R)B(S)）SQL语句是：(SELECT B FROM R)INTERSECT ALL (SELECT B FROM S)运行结果：说明：INTERSECT ALL操作可以保留重复的元组。注：SQL SERVER2000的T-SQL语言并不直接支持INTERSECT操作符，可用EXISTS可以模拟INTERSECT操作。Bb1b4b1b1b4

31、 例例44 44 求关系R和S在属性A、B、C上投影的差集。关系代数表达式是：A,B,C(R)-A,B,C(S)SQL语句是：(SELECT A,B,C FROM R)EXCEPT (SELECT A,B,C FROM S)运行结果是：说明：EXCEPT操作能够自动消除重复。如果想保留所有重复，可以用EXCEPT ALL 代替EXCEPT。A AB BC Ca2b2c2a3b2c3 例例4545 找出属于R，且不属于S的B值，要求保留重复值。关系代数表达式：B(R)-B(S)SQL语句：(SELECT B FROM R)EXCEPT ALL (SELECT B FROM S)运行结果：说明：E

32、XCEPT ALL将保留重复。在SQL SERVER2000的TRANSACTION_SQL语言并不直接支持EXCEPT操作符，可以用NOT EXISTS模拟EXCEPT操作。B Bb2b2b2b2 聚集函数是对一组值进行计算，并返回单个值的函数。聚集函数经常与查询语句的GROUP BY子句一同使用，在查询结果中生成汇总值。表2 中列举SQL的聚集函数。表2 SQL的聚集函数 函数名功能参数类型AVG求平均值数值SUM求总和数值MAX求最大值数值、其他类型MIN求最小值数值、其他类型COUNT计数数值、其他类型 其中AVG和SUM函数的参数必须是数值型，其他函数的参数还可以是非数值型，如字符串

33、。聚集函数只能作为SELECT和HAVING子句的参数。除了COUNT函数之外，其他聚集函数忽略空值。1.求平均值函数求平均值函数 聚集函数AVG用于计算列中数值的平均值，所以函数AVG的参数必须是数值型。例例46 46 求教工表中教授工资的平均值。SELECT AVG(工资)AS 平均工资 FROM 教工 WHERE 职称=教授 查询结果：平均工资3580 2.求总和函数求总和函数 聚集函数SUM用于计算列中数值的总和，函数SUM的参数必须是数值型。例例47 47 求所有教工的工资总和。SELECT SUM(工资)AS 工资总和 FROM 教工 查询结果：工资总和178600 求最大值和最小

34、值函数求最大值和最小值函数 聚集函数MAX用于求列中的最大值，MIN求列中的最小值，这两个的参数允许是数值型的，也可以是其他数据类型（如字符型和时间型的数据）。例例48 48 找出教工中最高工资和最低工资。SELECT MAX(工资)，MIN(工资)FROM 教工 查询结果：MAX(工资)MIN(工资)3740.002130.00 例例49 49 显示学生中最早和最晚的出生日期。SELECT MIN(出生日期),MAX(出生日期)FROM 学生 查询结果：注意：出生日期是日期型数据，日期型数据的特点，最大数据恰好是最小年龄。MIN(出生日期)MAX(出生日期)10/05/197904/13/1

35、982 4.计数器函数计数器函数聚集函数COUNT统计表中的行数。COUNT函数有3种格式：l COUNT（*）统计所有行数，包括含有空值的行。l COUNT（表达式）计算每一行中表达式的值，并返回非空值的行数。l COUNT(DISTINCT(表达式)计算每一行表达式的值，并返回唯一值且非空值的行数。例例50 50 查询学生的总人数。SELECT COUNT(*)AS 人数 FROM 学生 查询结果：人数10 例例5151 假设在学生表中增加一名新生，只插入该生的学号和姓名。执行下列插入语句：INSERT INTO STUDENT(SNO,SNAME)VALUES(991033,葛小燕)分别

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

37、语言的查询语句中，可以用GROUP BY子句实现对元组的分组功能。还可以利用HAVING子句对GROUP BY分组的结果进行筛选，保留满足条件的分组。GROUP BY子句HAVING子句是查询语句的可选项，语法格式是：SELECT ,FROM ,WHERE GROUP BY HAVING 在GROUP BY子句中的分组表达式可以是一个属性或者多个属性，其功能是将在分组表达式上具有相同值的元组放在一个组内。例例5252 统计学生表中男生和女生的人数。SELECT 性别,COUNT(*)FROM 学生 GROUP BY 性别 查询结果：性别COUNT(*)男5女5 例例52 52 统计成绩表中，每

38、一门课程的平均成绩。SELECT 课程编号,AVG(分数)FROM 成绩 GROUP BY 课程编号 查询结果：注意：带有GROUP BY子句的查询语句中，SELECT子句的列名中必须包括分组表达式，还可以包括集聚函数，除此而外不能有其他列名。课程编号AVG(分数)C170C284C365C487 例例5353 查询教工表中每一种职称的最高工资和最低工资。SELECT 职称,MAX(工资),MIN(工资)FROM 教工 GROUP BY 职称 查询结果：职称MAX(工资)MIN(工资)教授3740.003420.00副教授3190.003140.00讲师224002130.00 使用GROUP

39、 BY子句时，可以利用HAVING子句对GROUP BY分组的结果进行筛选，保留满足条件的分组。HAVING子句的格式：HAVING HAVING与WHERE子句都有，注意两者之间的区别。WHERE子句中的是在GROUP BY分组之前起作用，而HAVING子句的是在形成分组后起作用，所以，在HAVING的条件表达式中可以使用聚集函数（这一点与WHERE不同）。例例5454 对于成绩表中分数在60以上的行按照学号分组，其中只包含选课数大于2且平均分超过70的学号、选课数和平均分。SELECT 学号,COUNT(课程编号),AVG(分数)FROM 成绩 WHERE 分数=60 GROUP BY 学

40、号 HAVING COUNT(课程编号)2 AND AVG(分数)70 分析这个语句的WHERE、GROUP BY和HAVING子句执行的顺序。已知成绩表如图4所示。成绩 图4 成绩表 学号课程号分数1022C101881022C102672124C101562124C102952124C103454021C104874021C102784021C103671223C101661223C102893012C102933012C10384（1）这个语句首先执行WHERE子句，选择满足条件的10行（去除不及格的2行）。学号课程号分数1022C101881022C102672124C10295402

41、1C104874021C102784021C103671223C101661223C102893012C102933012C10384（2）然后执行GROUP BY子句，将10行按照学号分成5组。学号课程号分数1022C101881022C102672124C102954021C104874021C102784021C103671223C101661223C102893012C102933012C10384 （3）最后执行HAVING子句，以“选课数大于2且平均分超过70”为条件筛选分组的结果，最后满足条件的组只有1组。运行结果：学号课程号分数4021C104874021C102784021C

42、10367学号COUNT(课程号)AVG(分数)4021377.33 例例5555 统计成绩表中选修人数超过2以上的课程编号和人数。SELECT 课程编号,COUNT(*)FROM 成绩 GROUP BY 课程编号 HAVING COUNT(*)2 首先，按照课程编号分成4组，再过滤掉选修人数小于或等于2的组。在SQL语言中，允许使用NULL值表示某个属性的值为空（即没有值）。对于空值的处理方法和原则是一个容易混淆的问题。在SQL语言中，允许在条件表达式中使用特殊的运算符号IS NULL测试属性值是否为空值，或者用IS NOT NULL测试是否为非空值。下面举例说明这两个运算符的使用方法。假设

43、有贷款表如图5所示。图5贷款关系 帐号姓名金额G10020关平之200300T20078李连玉K65743姜海洋30092.00H89765田平平30200.60H76890方一华19080.40 例例56 56 找出贷款表中金额为空值的帐号和姓名。SELECT 帐号,姓名 FROM 贷款 WHERE 金额 IS NULL 查询结果：帐号姓名T20078李连玉 例例5757找出贷款表中贷款金额非空的帐号和姓名。SELECT 帐号,姓名 FROM 贷款 WHERE 金额 IS NOT NULL 查询结果：帐号姓名G10020关平之K65743姜海洋H89765田平平H76890方一华 1.1.在

44、算术运算和比较运算中对在算术运算和比较运算中对NULLNULL的处理方法：的处理方法：l 若算术运算（+、-、*或/）的参数中含有NULLNULL，则该算术表达式的运算结果是NULLNULL。l 若比较运算中有NULLNULL作为比较对象，则比较的结果视为假（false）。l SQL92规定，比较运算中有NULLNULL作为比较对象，则比较的结果是“不知道（unknown）”，而不用假（false）来表示。l 几乎其他所有情况下，将unknown 作为false。2.2.聚集函数对聚集函数对NULL的处理原则：的处理原则：l SUM、AVG、MIN、MAX函数对参数中的空值（NULL）忽略不计

45、。若所有参数值都是空值（NULL），则函数的返回值是NULL。l COUNT函数对参数中的空值（NULL）也计数。若所有参数值都是空值（NULL），则函数的返回值是0。利用连接操作，可以根据表与表之间的逻辑联系从两个或多个表中查询数据。早期的SQL版本，实现连接方法是在FROM子句中给出连接操作的表名，在WHERE子句中给出连接和选择的条件。例例58 58 从学生、课程和成绩表中，产生数据库课程的成绩单。SELECT 姓名,课程名,分数 FROM 学生 S,课程 C,成绩 G WHERE S.学号=G.学号 AND G.课程编号=C.课程号 AND C.课程名=数据库 SQL-92提供更丰富的

46、连接操作，包括：内连接、条件连接、自然连接、左外连接、右外连接、全连接，同时增加了在FROM子句定义连接条件的方法。FROM子句的语法格式：FROM ON()例58题的SQL语句也可以等价为：SELECT 姓名，课程名，分数 FROM 学生 S JOIN 成绩 G ON(S.学号=G.学号)JOIN课程 C ON(G.课程号=C.课程号)WHERE C.课程名称=数据库 说明：利用FROM子句定义连接条件，不仅可以简化连接条件的表达方法，而且能够提高查询效率。因为，在SQL系统中FROM、WHERE和HAVING子句执行的逻辑顺序是：l FROM子句中的连接条件。l WHERE子句中的连接条件

47、与选择条件。l HAVING子句中的筛选条件。在使用支持SQL-92标准的系统时，建议用FROM子句定义连接条件。表3中列出各种连接操作的类型。表3 连接操作一览表 连接名称连接名称SQL-92SQL-92关键字内连接INNER JOIN自然连接NATURAL JOIN交叉连接CROSS JOIN左外连接LEFT OUTER JOIN右外连接RIGHT OUTER JOIN全连接FULL OUTER JOIN自连接SELF JOIN 关系R和S进行连接操作，且连接条件为P。若连接操作的结果中只包含R与S在P上相匹配的行，这种连接属于内连接（INNER JOIN）。用FROM子句表示内连接的子句

48、：FROM R INNER JOIN S ON()其中，INNER可省略。假设有导师与研究生2个表，如图6所示。导师 研究生 图6 导师与研究生表 教工号 姓名 性别T001江海男T002代宁女T005潘涛男T008田立女学号姓名 导师号99001李南T00199002刘星T00299003王海T00299004张力T00199006郭天 例例5959 查询研究生与其导师的情况。此查询涉及教师和研究生2个表，连接条件是：。关系代数表达式是：教师 研究生 SELECT *FROM 研究生A INNER JOIN教师 B ON A.导师号=B.教工号 也可以写成等价的SQL语句：SELECT *F

49、ROM 研究生A,导师 B WHERE A.导师号=B.教工号 查询结果是：这是一个内连接的例子，查询结果中只包含满足连接条件的4行。学号 姓名 导师号 教工号 姓名 性别 99001李南T001T001江海男99002刘星T002T002代宁女99003王海T002T002代宁女99004张力T001T001江海男 设关系R和S进行连接操作，其连接条件为P。如果P是R与S中连接属性的等值比较，称为等值连接。若R与S进行等值连接，且R与S的连接属性名相同，称为自然连接。自然连接是等值连接的特例。自然连接的实现方法是在内连接的基础上，用SELECT子句消除重复列。例例60 60 根据学生、课程和

50、成绩表，输出“计算机”课程的成绩单，包括姓名和分数。这个查询是三个表的自然连接，其关系代数表达式是：姓名，分数（课程名=数据库（学生成绩课程）SELECT 姓名，分数 FROM 学生 A JOIN 成绩 B ON（A.学号=B.学号）JOIN课程 C ON（B.课程号=C.课程号）WHERE C.课程名=计算机 等价的SQL语句：SELECT 姓名,分数 FROM 学生 A,成绩 B,课程 C WHERE A.学号=B.学号 AND B.课程编号=C.课程号 AND C.课程名=计算机 查询结果：这是一个自然连接的实例，查询结果中包含满足连接条件元组，且没有重复列。姓名分数 郭黎明45何明慧6