关系数据库标准语言最新课件.ppt

南京航空航天大学金城学院第三章第三章 关系数据库关系数据库标准语言标准语言SQLSQL关系数据库标准语言 最新Review•关系代数运算关系代数运算 并、差、交、笛卡尔积、投影、选择、连接、除并、差、交、笛卡尔积、投影、选择、连接、除–基本运算基本运算并、差、笛卡尔积、投影、选择并、差、笛卡尔积、投影、选择–组合运算组合运算 交、连接、除交、连接、除 关系数据库标准语言 最新练习•关系关系R(A,B,C)和和S(B,C,D)，下列各关系表达式不成立的是（），下列各关系表达式不成立的是（）A. B. C. D. •设关系设关系R和和S的属性个数分别为的属性个数分别为2和和3，那么，那么 等价于等价于 A. B. C. D. 关系数据库标准语言 最新练习•对学生对学生-课程数据库，简要说明关系代数式课程数据库，简要说明关系代数式R1，，R2 的具体含义的具体含义关系数据库标准语言 最新SQL发展简史•1970：： E.J. Codd 发表了关系数据库理论发表了关系数据库理论•1974-79：： IBM 以以Codd的理论为基础开发了的理论为基础开发了““Sequel”,并并重命名为重命名为"SQL"；； •1979：： Oracle 发布了商业版发布了商业版SQL •1981-84：： 出现了其他商业版本，分别来自出现了其他商业版本，分别来自 IBM(DB2),Data General(DG/SQL),Relational Technology(INGRES)•SQL/86：：ANSI 跟跟 ISO的第一个标准；的第一个标准； •SQL/89：：增加了引用完整性增加了引用完整性(referential integrity)；； •SQL/92(aka SQL2)：：被数据库管理系统（被数据库管理系统（DBMS））生产商广生产商广泛接受；泛接受； •1997+：成为动态网站：成为动态网站(Dynamic web content)的后台支持；的后台支持； 关系数据库标准语言 最新SQL发展简史•SQL/99：：Core level跟其他跟其他8种相应的种相应的level，，包括递归查询，包括递归查询，程序跟流程控制，基本的对象（程序跟流程控制，基本的对象（object））支持包括支持包括oids•SQL/2003：：包含了包含了XML相关内容相关内容,自动生成列值自动生成列值(column values)；； •2005-09-30：：“Data is the next generation inside...SQL is the new HTML”! Tim O‘eilly提出了提出了Web 2.0理念，称数据将是核理念，称数据将是核心，心，SQL将成为将成为““新的新的 HTML"；； •SQL/2006：：定义了定义了SQL与与XML(包含包含XQuery)的关联应用；的关联应用； •2006：：Sun公司将以公司将以SQL基础的数据库管理系统嵌入基础的数据库管理系统嵌入Java V6关系数据库标准语言 最新SQL语言概述•1986年批准成为关系数据库语言的美国标准年批准成为关系数据库语言的美国标准•绝大多数绝大多数流行的关系型数据库管理系统流行的关系型数据库管理系统，如，如Oracle, Sybase, Microsoft SQL Server, Access等都等都采用采用SQL语言标准语言标准•虽然很多数据库都对虽然很多数据库都对SQL语句进行了再开发和扩展，但是语句进行了再开发和扩展，但是包括包括Select, Insert, Update, Delete, Create, 以及以及Drop在内的在内的标准的标准的SQL命令仍然被用来完成几乎所有的数据库操作命令仍然被用来完成几乎所有的数据库操作关系数据库标准语言 最新SQL的特点•SQL的特点的特点1.1.综合统一综合统一–集数据定义、数据操纵和数据控制语言功能于一体集数据定义、数据操纵和数据控制语言功能于一体2. 高度非过程化高度非过程化3. 面向集合的操作方式面向集合的操作方式4. 以同一种语法结构提供两种使用方法以同一种语法结构提供两种使用方法–既是独立语言，又是嵌入式语言既是独立语言，又是嵌入式语言5. 语言简洁，易学易用语言简洁，易学易用关系数据库标准语言 最新学生-课程数据库学学  号号Sno姓姓  名名Sname性性  别别Ssex年年  龄龄Sage所所 在在 系系Sdept95001李勇李勇男男20CS95002刘晨刘晨女女19IS95003王敏王敏女女18MA95004张立张立男男19IS(a)  Student例例1例例2例例4例例3关系数据库标准语言 最新学生-课程数据库（续）(b)Course课程号课程号课程名课程名先行课先行课学分学分CnoCnameCpnoCcredit1数据库数据库542数学数学 23信息系统信息系统144操作系统操作系统635数据结构数据结构746数据处理数据处理 27PASCAL语言语言64例例35关系数据库标准语言 最新学生-课程数据库（续） (c)SC学学  号号课课 程程 号号成成  绩绩SnoCnoGrade9500119295001285950013889500229095002380例例6例例31例例33关系数据库标准语言 最新第三章 关系数据库标准语言SQL3.1 SQL3.1 SQL概述概述3.2 3.2 学生学生- -课程数据库课程数据库3.3 3.3 数据定义数据定义3.4 3.4 查询查询3.5 3.5 数据更新数据更新3.6 3.6 视图视图3.8 3.8 小结小结关系数据库标准语言 最新SQL语句•语句格式语句格式 SELECT [ALL|DISTINCT] <目标列表达式目标列表达式>[，，<目标列表达式目标列表达式>] …FROM <表名表名>[，， <表名表名> ] …[ WHERE <条件表达式条件表达式> ]关系数据库标准语言 最新单表查询 •选择表中的若干列选择表中的若干列•选择表中的若干元组选择表中的若干元组•对查询结果排序对查询结果排序•使用集函数使用集函数•对查询结果分组对查询结果分组 关系数据库标准语言 最新查询指定列[例例1] 查询全体学生的学号与姓名查询全体学生的学号与姓名SELECT Sno，，SnameFROM Student；； [例例2] 查询全体学生的姓名、学号、所在系查询全体学生的姓名、学号、所在系SELECT Sname, Sno, SdeptFROM Student；；关系数据库标准语言 最新查询全部列[例例3] 查询全体学生的详细记录查询全体学生的详细记录SELECT *FROM Student；； 等价于等价于SELECT Sno, Sname, Ssex, Sage, SdeptFROM Student；； 关系数据库标准语言 最新查询经过计算的值 •<目标列表达式目标列表达式>为表达式为表达式–算术表达式算术表达式–字符串常量字符串常量–函数函数–列别名列别名关系数据库标准语言 最新查询经过计算的值[例例4] 查全体学生的姓名及其出生年份查全体学生的姓名及其出生年份SELECT Sname，，2010-Sage Year of BirthFROM Student；； 输出结果：输出结果： Sname Year of Birth --------- -------------------- 李勇李勇 1989 刘晨刘晨 1990 王名王名 1991 张立张立 1990 算术表达式算术表达式列别名列别名关系数据库标准语言 最新查询经过计算的值[例例5] 查查询询全全体体学学生生的的姓姓名名、、出出生生年年份份和和所所在在系系，，要要求用小写字母表示所有系名求用小写字母表示所有系名SELECT Sname，，'Year of Birth: ‘ Birth，，2009-Sage，，ISLOWER(Sdept)FROM Student；； 字符串常量字符串常量关系数据库标准语言 最新练习•SELECT语句执行结果是语句执行结果是________。

A.数据项数据项 B.元组元组 C.表表 D.数据库数据库•用用SQL针对第二章习题针对第二章习题3中的四个表完成查询中的四个表完成查询–找出所有供应商的姓名和所在城市找出所有供应商的姓名和所在城市–找出所有零件的名称、颜色、重量找出所有零件的名称、颜色、重量 关系数据库标准语言 最新练习•在在SQL的查询语句中，对应关系代数中的查询语句中，对应关系代数中““投影投影””运算的语运算的语句是句是________ A.SELECT B.INSERT C.UPDATE D.DELETE•在在SQL语句中使用语句中使用*表示表示________ A.选择任何属性选择任何属性 B.选择全部属性选择全部属性 C.选择全部元组选择全部元组 D.选择主码选择主码关系数据库标准语言 最新单表查询 •选择表中的若干列选择表中的若干列•选择表中的若干元组选择表中的若干元组•对查询结果排序对查询结果排序•使用集函数使用集函数•对查询结果分组对查询结果分组 关系数据库标准语言 最新选择表中的若干元组 •消除取值重复的行消除取值重复的行•查询满足条件的元组查询满足条件的元组 关系数据库标准语言 最新消除取值重复的行•在在SELECT子句中使用子句中使用DISTINCT短语短语SELECT [ALL|DISTINCT] <目标列表达式目标列表达式>[，，<目标列表达式目标列表达式>] …FROM <表名表名>[，， <表名表名> ] …[ WHERE <条件表达式条件表达式> ]关系数据库标准语言 最新ALL 与 DISTINCT [例例6] 查询选修了课程的学生学号查询选修了课程的学生学号(1) SELECT Sno FROM SC; (默认默认 ALL) (2) SELECT DISTINCT Sno FROM SC;关系数据库标准语言 最新查询满足条件的元组•WHERE子句常用的查询条件子句常用的查询条件关系数据库标准语言 最新(1) 比较大小在在WHERE子句子句的的<比较条件比较条件>中使用比较运算符中使用比较运算符–=，，>，，<，，>=，，<=，，!= 或或 <>[例例7] 查询计算机科学系全体学生的名称查询计算机科学系全体学生的名称 SELECT Sname FROM Student WHERE Sdept=‘CS’关系数据库标准语言 最新(1) 比较大小（续）[例例8] 查询所有年龄在查询所有年龄在20岁以下的学生姓名及其年龄岁以下的学生姓名及其年龄[例例9] 查询考试成绩有不及格的学生的学号查询考试成绩有不及格的学生的学号 关系数据库标准语言 最新(2) 确定范围•使用谓词使用谓词 BETWEEN … AND … NOT BETWEEN … AND …[例例10] 查询年龄在查询年龄在20~23岁（包括岁（包括20岁和岁和23岁）岁）之间的学生的姓名、系别和年龄之间的学生的姓名、系别和年龄 SELECT Sname，，Sdept，，SageFROM StudentWHERE Sage BETWEEN 20 AND 23；； 关系数据库标准语言 最新(3) 确定集合使用谓词使用谓词 IN <值表值表>, NOT IN <值表值表> <值表值表>：用逗号分隔的一组取值：用逗号分隔的一组取值[例例12]查询查询计算机系、数学系、信息系计算机系、数学系、信息系学生学生的姓名和性别的姓名和性别SELECT Sname，，SsexFROM StudentWHERE Sdept IN ( 'IS'，，'MA'，，'CS' );关系数据库标准语言 最新(4) 字符串匹配• [NOT] LIKE '<匹配串匹配串>' [ESCAPE '<换码字符换码字符>']–% (百分号百分号) 代表代表任意长度任意长度（长度可以为（长度可以为0）的字符串）的字符串–_ (下横线下横线) 代表任意代表任意单个字符单个字符–当用户要查询的字符串本身就含有当用户要查询的字符串本身就含有 % 或或 _ 时，要使用时，要使用ESCAPE '<换码字符换码字符>' 短语对通配符进行转义短语对通配符进行转义•使用换码字符将使用换码字符将通配符转义为普通字符通配符转义为普通字符关系数据库标准语言 最新例题[例例15] 查询所有姓刘的学生的姓名、学号和性别查询所有姓刘的学生的姓名、学号和性别 SELECT Sname，，Sno，，Ssex FROM Student WHERE Sname LIKE '刘刘% ' ；；关系数据库标准语言 最新例题[例例19] 查询查询DB_Design课程的课程号和学分课程的课程号和学分 SELECT Cno，，Ccredit FROM Course WHERE Cname LIKE 'DB\_Design ' ESCAPE '\'–ESCAPE ‘\’表示表示““\””为换码字符。

这样紧跟在为换码字符这样紧跟在““\””后后的通配符的通配符““_””转义为普通字转义为普通字关系数据库标准语言 最新(5) 涉及空值的查询– 使用谓词使用谓词 IS NULL 或或 IS NOT NULL– “IS NULL” 不能用不能用 “= NULL” 代替代替[例例22] 查所有有成绩的学生学号和课程号查所有有成绩的学生学号和课程号SELECT Sno，，Cno FROM SC WHERE Grade IS NOT NULL；；关系数据库标准语言 最新(6) 多重条件查询•用逻辑运算符用逻辑运算符AND和和 OR来联接多个查询条件来联接多个查询条件– AND的优先级高于的优先级高于OR– 可以用括号改变优先级可以用括号改变优先级关系数据库标准语言 最新例题[例例23] 查询计算机系年龄在查询计算机系年龄在20岁以下的学生姓名岁以下的学生姓名 SELECT Sname FROM Student WHERE Sdept= 'CS' AND Sage<20；；关系数据库标准语言 最新练习•用用SQL针对第二章习题针对第二章习题5中的四个表完成查询中的四个表完成查询–求供应工程求供应工程J1零件的供应商号码零件的供应商号码SNO–求使用供应商求使用供应商S1所供应零件的工程号码所供应零件的工程号码–找出工程项目找出工程项目J2使用的各种零件的号码及其数量使用的各种零件的号码及其数量–将工程项目将工程项目J2使用的零件按种类统计各零件使用的总数量使用的零件按种类统计各零件使用的总数量–找出天津的工程项目使用的各种零件的号码、数量及其对找出天津的工程项目使用的各种零件的号码、数量及其对应的工程号应的工程号 关系数据库标准语言 最新单表查询 •选择表中的若干列选择表中的若干列•选择表中的若干元组选择表中的若干元组•对查询结果排序对查询结果排序•使用集函数使用集函数•对查询结果分组对查询结果分组 关系数据库标准语言 最新对查询结果排序 •使用使用ORDER BY子句子句– 可以按一个或多个属性列排序可以按一个或多个属性列排序– 升序：升序：ASC；降序：；降序：DESC；；缺省值为升序缺省值为升序–当排序列当排序列含空值含空值时时•若按升序排，含空值的元组将最后显示若按升序排，含空值的元组将最后显示•若按降序排，含空值的元组将最先显示若按降序排，含空值的元组将最先显示关系数据库标准语言 最新对查询结果排序（续） [例例24] 查查询询选选修修了了3号号课课程程的的学学生生的的学学号号及及其其成成绩绩，，查询结果按分数降序排列查询结果按分数降序排列 SELECT Sno，，Grade FROM SC WHERE Cno= ' 3 ' ORDER BY Grade DESC；； 关系数据库标准语言 最新单表查询 •选择表中的若干列选择表中的若干列•选择表中的若干元组选择表中的若干元组•对查询结果排序对查询结果排序•使用集函数使用集函数•对查询结果分组对查询结果分组 关系数据库标准语言 最新使用集函数 •计数计数COUNT•计算总和计算总和SUM• 计算平均值计算平均值AVG•求最大值求最大值MAX•求最小值求最小值MIN关系数据库标准语言 最新使用集函数 （续）[例例26] 查询学生总人数查询学生总人数 SELECT COUNT(*) FROM Student；； [例例27] 查询选修了课程的学生人数查询选修了课程的学生人数 SELECT COUNT(DISTINCT Sno) FROM SC；；关系数据库标准语言 最新使用集函数 （续）[例例29] 查询选修查询选修1号课程的学生最高分数号课程的学生最高分数SELECT MAX(Grade) FROM SC WHER Cno= ' 1 '；； 关系数据库标准语言 最新使用集函数 （续）[例例28] 计算计算1号课程的学生平均成绩号课程的学生平均成绩 SELECT AVG(Grade) FROM SC WHERE Cno= ' 1 '；；–查询每门课程的平均成绩查询每门课程的平均成绩 关系数据库标准语言 最新单表查询 •选择表中的若干列选择表中的若干列•选择表中的若干元组选择表中的若干元组•对查询结果排序对查询结果排序•使用集函数使用集函数•对查询结果分组对查询结果分组 关系数据库标准语言 最新对查询结果分组 •使用使用GROUP BY子句分组子句分组 –分分组组方方法法：：按按指指定定的的一一列列或或多多列列值值分分组组，，值值相相等等的的为为一一组组 –未对查询结果分组，未对查询结果分组，集函数集函数将作用于整个查询结果将作用于整个查询结果– 对查询结果分组后，对查询结果分组后，集函数集函数将分别作用于每个组将分别作用于每个组 关系数据库标准语言 最新对查询结果分组（续）[例例31] 求各门课程的课程号及相应的选课人数求各门课程的课程号及相应的选课人数 SELECT Cno，，COUNT(Sno) 选课人数选课人数 FROM SC GROUP BY Cno；；  结果结果 Cno 选课人数选课人数 ------------- ---------------- 1 1 2 2 3 2 关系数据库标准语言 最新使用HAVING短语•筛选最终输出结果筛选最终输出结果HAVING[例例32] 查询选修了查询选修了3门以上课程的学生学号门以上课程的学生学号 SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) >3；；   关系数据库标准语言 最新使用HAVING短语•只有满足只有满足HAVING短语指定条件的组才输出短语指定条件的组才输出•HAVING短语与短语与WHERE子句子句的区别：作用对象不同的区别：作用对象不同–WHERE子子句句作作用用于于基基表表或或视视图图，，从从中中选选择择满满足足条件的元组条件的元组–HAVING短语作用于短语作用于组组，从中选择满足条件的组，从中选择满足条件的组 关系数据库标准语言 最新小结SELECT [ALL|DISTINCT] <目标列表达式目标列表达式> [，，<目标列表达式目标列表达式>] …FROM <表名或视图名表名或视图名>[，， <表名或视图名表名或视图名> ] …[ WHERE <条件表达式条件表达式> ][ GROUP BY <列名列名1> [ HAVING <条件表达式条件表达式> ] ][ ORDER BY <列名列名2> [ ASC|DESC ] ]；；关系数据库标准语言 最新练习•用用SQL针对第二章习题针对第二章习题5中的四个表完成查询中的四个表完成查询–将工程项目将工程项目J2使用的零件按种类统计各零件使用的总数量使用的零件按种类统计各零件使用的总数量–找出天津的工程项目使用的各种零件的号码、数量及其对找出天津的工程项目使用的各种零件的号码、数量及其对应的工程号应的工程号 关系数据库标准语言 最新3.4 查 询 3.4.1 单表查询单表查询3.4.2 连接查询连接查询3.4.3 嵌套查询嵌套查询3.4.4 集合查询集合查询3.4.5 SELECT语句的一般格式语句的一般格式关系数据库标准语言 最新连接查询 •用来连接两个表的条件称为用来连接两个表的条件称为连接条件或连接谓词连接条件或连接谓词 •一般格式：一般格式：–[<表名表名1>.]<列名列名1> <比较运算符比较运算符> [<表名表名2>.]<列名列名2>–连接谓词的列名称为连接谓词的列名称为连接字段连接字段。

连接条件中的各连接连接条件中的各连接字段类型必须是可比的，但名字不必相同字段类型必须是可比的，但名字不必相同关系数据库标准语言 最新连接查询•SQL中连接查询的主要类型中连接查询的主要类型– 广义笛卡尔积广义笛卡尔积– 等值连接等值连接(含自然连接含自然连接)–自身连接查询自身连接查询– 外连接查询外连接查询– 复合条件连接查询复合条件连接查询关系数据库标准语言 最新广义笛卡尔积 •不带连接谓词的连接不带连接谓词的连接例：例： SELECT Student.* , SC.* FROM Student, SC关系数据库标准语言 最新等值连接查询 •等值连接、自然连接等值连接、自然连接[例例33] 查询每个学生及其选修课程的情况查询每个学生及其选修课程的情况SELECT Student.*，，SC.*FROM Student，，SCWHERE Student.Sno = SC.Sno；；关系数据库标准语言 最新等值连接 Student.Sno Sname Ssex Sage Sdept SC.Sno Cno Grade 95001 李勇李勇 男男 20 CS95001 1 92 95001 李勇李勇 男男 20CS95001 2 85 95001 李勇李勇 男男 20CS95001 3 88 95002 刘晨刘晨 女女 19IS 95002 2 90 95002 刘晨刘晨 女女 19IS95002 3 80   关系数据库标准语言 最新DBMS的执行过程•先在先在Student表中找到第一个元组表中找到第一个元组•从头开始扫描从头开始扫描SC表，逐一查找与表，逐一查找与Student表第一个表第一个元组的元组的Sno相等相等的的SC元组元组•找到后将找到后将Student的第一个元组与该元组的第一个元组与该元组拼接拼接，形，形成结果表中一个元组成结果表中一个元组•SC全部查找完后，再找全部查找完后，再找Student表第二个元组表第二个元组关系数据库标准语言 最新自然连接•把目标列中重复的属性列去掉把目标列中重复的属性列去掉[例例34] 对对[例例33]用自然连接完成用自然连接完成 SELECT Student.Sno，，Sname，，Ssex，，Sage，， Sdept，，Cno，，Grade FROM Student，，SC WHERE Student.Sno = SC.Sno；；关系数据库标准语言 最新自身连接 •连连接接操操作作可可以以是是一一个个表表与与其其自自己己进进行行连连接接，，称称为为表的表的自身连接自身连接[例例35] 查查询询每每一一门门课课的的间间接接先先修修课课（（即即先先修修课课的的先先修课）修课） SELECT FIRST.Cno，，SECOND.Cpno FROM Course FIRST，，Course SECOND WHERE FIRST.Cpno = SECOND.Cno；； 关系数据库标准语言 最新外连接（Outer Join） •外连接外连接–把把舍舍弃弃的的元元组组也也保保存存在在结结果果关关系系中中，，而而在在其其他他属性上填属性上填null–SELECT 列名列名 FROM 表名表名 LEFT(/RIGHT) OUT JOIN 表名表名 ON (连接条件连接条件)关系数据库标准语言 最新外连接（续） [例例 33] 查查询询每每个个学学生生及及其其选选修修课课程程的的情情况况包包括括没没有有选修课程选修课程的学生的学生SELECT Student.Sno，，Sname，，Ssex，， Sage，，Sdept，，Cno，，Grade FROM Student LEFT OUT JOIN SC ON(Student.Sno = SC.Sno)；； 关系数据库标准语言 最新外连接（续） 结果：结果： Student.Sno Sname Ssex Sage Sdept Cno Grade 95001 李李勇勇 男男 20 CS 1 92 95001 李李勇勇 男男 20 CS 2 85 95001 李李勇勇 男男 20 CS 3 88 95002 刘刘晨晨 女女 19 IS 2 90 95002 刘刘晨晨 女女 19 IS 3 80 95003 王敏王敏 女女 18 MA 95004 张立张立 男男 19 IS关系数据库标准语言 最新复合条件连接•WHERE 子句可以有多个连接条件，称为子句可以有多个连接条件，称为复合条件复合条件连接连接[例例35]查询选修查询选修2号课程且成绩在号课程且成绩在90分以上的所有学分以上的所有学生的学号、姓名生的学号、姓名SELECT Student.Sno, student.SnameFROM Student, SCWHERE Student.Sno = SC.Sno AND SC.Cno= ' 2 ' AND SC.Grade > 90；；关系数据库标准语言 最新多表连接[例例36] 查查询询每每个个学学生生的的学学号号、、姓姓名名、、选选修修的的课课程程名名及及成成绩绩 SELECT Student.Sno,Sname,Cname,Grade FROM Student，，SC，，Course WHERE Student.Sno = SC.Sno and SC.Cno = Course.Cno关系数据库标准语言 最新多表连接[例例36] 查查询询每每个个学学生生的的学学号号、、姓姓名名、、选选修修的的课课程名及成绩程名及成绩结果：结果：Student.Sno Sname Cname Grade 95001 李勇李勇 数据库数据库 92 95001 李勇李勇 数学数学 85 95001 李勇李勇 信息系统信息系统 88 95002 刘晨刘晨 数学数学 90 95002 刘晨刘晨 信息系统信息系统 80 关系数据库标准语言 最新练习•用用SQL针对第二章习题针对第二章习题5中的四个表完成查询中的四个表完成查询•找出天津的工程项目使用的各种零件的号码、数量及找出天津的工程项目使用的各种零件的号码、数量及其对应的工程号其对应的工程号•找出工程项目找出工程项目J2使用的各种零件的名称及其数量使用的各种零件的名称及其数量•找出上海厂商供应的所有零件号码找出上海厂商供应的所有零件号码•找出使用上海产的零件的工程名称找出使用上海产的零件的工程名称关系数据库标准语言 最新综合练习•用用SQL针对针对““学生学生-课程数据库课程数据库””完成查询完成查询•显示每个系的学生的平均年龄显示每个系的学生的平均年龄•查询查询““计算机计算机””系所有选修了系所有选修了““数据库原理数据库原理””课程的学生课程的学生姓名姓名•显示至少有显示至少有5名学生选修的课程的平均分数及其课程名称名学生选修的课程的平均分数及其课程名称•查询每个人选修的课程数，显示其学号及课程数查询每个人选修的课程数，显示其学号及课程数•查查询询有有3门门以以上上课课程程是是90分分以以上上的的学学生生的的学学号号及及（（90分分以以上的）课程数上的）课程数•查询查询““数据结构数据结构””这门课程的学生成绩这门课程的学生成绩关系数据库标准语言 最新Review•SQL一般格式一般格式SELECT [ALL|DISTINCT] <目标列表达式目标列表达式> [，，<目标列表达式目标列表达式>] …FROM <表名或视图名表名或视图名>[，， <表名或视图名表名或视图名> ] …[ WHERE <条件表达式条件表达式> ][ GROUP BY <列名列名1> [ HAVING <条件表达式条件表达式> ] ][ ORDER BY <列名列名2> [ ASC|DESC ] ]；；•多表查询多表查询——连接字段连接字段关系数据库标准语言 最新练习•统计每门课程的学生选修人数（超过统计每门课程的学生选修人数（超过10人的课程人的课程才统计），要求输出课程号和选修人数，查询结才统计），要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序果按人数降序排列，若人数相同，按课程号升序排列排列•查询与查询与“刘晨刘晨”在同一个系学习的学生在同一个系学习的学生关系数据库标准语言 最新3.4 查 询 3.4.1 单表查询单表查询3.4.2 连接查询连接查询3.4.3 嵌套查询嵌套查询3.4.4 集合查询集合查询3.4.5 SELECT语句的一般格式语句的一般格式关系数据库标准语言 最新嵌套查询•嵌套查询概述嵌套查询概述–查询块查询块SELECT-FROM-WHERE–嵌套在另一个查询块的嵌套在另一个查询块的WHERE子句或子句或HAVING短语中短语中 关系数据库标准语言 最新引出子查询的谓词•带有带有ININ谓词谓词的子查询的子查询•带有带有比较运算符比较运算符的子查询的子查询•带有带有ANYANY或或ALLALL谓词谓词的子查询的子查询•带有带有EXISTSEXISTS谓词谓词的子查询的子查询* *关系数据库标准语言 最新带有IN谓词的子查询[例例39] 查询与查询与“刘晨刘晨”在同一个系学习的学生在同一个系学习的学生① ① 确定确定“刘晨刘晨”所在系名所在系名 ——CS系系 ② 查找所有在查找所有在CS系学习的学生系学习的学生 ③③将第一步查询将第一步查询嵌入嵌入到第二步查询的条件中到第二步查询的条件中 关系数据库标准语言 最新 构造嵌套查询③③将第一步查询嵌入到第二步查询的条件中将第一步查询嵌入到第二步查询的条件中 SELECT Sno，，Sname FROM Student WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname= ‘刘晨刘晨 ‘)；；父查询或父查询或外层查询外层查询子查询或子查询或内层查询内层查询子查询中子查询中的查询条的查询条件不依赖件不依赖于父查询，于父查询，称为称为不相不相关子查询关子查询关系数据库标准语言 最新带有IN谓词的子查询（续）[例例40]查询选修了课程名为查询选修了课程名为“信息系统信息系统”的学生学号和姓名的学生学号和姓名 (SELECT Cno ①① 首先在首先在Course关系中找出关系中找出“信信 FROM Course 息系统息系统”的课程号，结果为的课程号，结果为3号号 WHERE Cname= ‘信息系统信息系统’));(SELECT Sno ②② 然后在然后在SC关系中找出选关系中找出选 FROM SC 修了修了3号课程的学生学号号课程的学生学号 WHERE Cno INSELECT Sno，，Sname ③③ 最后在最后在Student关系中关系中 FROM Student 取出取出Sno和和Sname WHERE Sno IN关系数据库标准语言 最新练习•对第二章习题对第二章习题5中的四个表完成下列查询（用嵌套中的四个表完成下列查询（用嵌套 查询）查询）–求供应工程求供应工程J1红色零件的供应商号码红色零件的供应商号码SNO–找出上海厂商供应的所有零件号码找出上海厂商供应的所有零件号码关系数据库标准语言 最新带有比较运算符的子查询• 当能确切知道内层查询当能确切知道内层查询返回单值返回单值时，可用比较运时，可用比较运算符（算符（>，，<，，=，，>=，，<=，，!=或或< >））•如果内查询的结果是一个值，可以用如果内查询的结果是一个值，可以用=代替代替IN–[例例39] 查询与查询与“刘晨刘晨”在同一个系学习的学生在同一个系学习的学生关系数据库标准语言 最新带有比较运算符的子查询（续）[例例41]找出每个学生找出每个学生超过他选修课程平均成绩超过他选修课程平均成绩的课的课程号程号SELECT Sno，，Cno FROM SC x WHERE Grade >= (SELECT AVG(Grade) //课程平均成绩课程平均成绩 FROM SC y WHERE y.Sno=x.Sno)；；子查询中子查询中的查询条的查询条件依赖于件依赖于父查询，父查询，称为称为相关相关子查询子查询关系数据库标准语言 最新相关子查询•相关子查询：子查询的查询条件依赖于父查询相关子查询：子查询的查询条件依赖于父查询①从外层查询中取出从外层查询中取出SC的一个元组，将值传给内层的一个元组，将值传给内层 SELECT AVG(Grade) FROM SC y WHERE y.Sno=‘95001’②执行内层查询，得到值执行内层查询，得到值88，代替内层查询，得到外层查询，代替内层查询，得到外层查询SELECT Sno，，Cno FROM SC x WHERE Grade >= 88③执行查询，得到执行查询，得到(95001,1)④依次重复步骤依次重复步骤1至至3关系数据库标准语言 最新练习•在学生信息关系中完成下列查询：在学生信息关系中完成下列查询：–求年龄大于学生平均年龄的男学生姓名和年龄求年龄大于学生平均年龄的男学生姓名和年龄–查询成绩高于该门课程平均分的学生学号和课查询成绩高于该门课程平均分的学生学号和课程号程号–查询每门课程最高分的学生学号查询每门课程最高分的学生学号关系数据库标准语言 最新带有ANY或ALL谓词的子查询•谓词语义谓词语义–ANY：任意一个值；：任意一个值；ALL：所有值：所有值•ANY和和ALL谓词有时可以用集函数实现谓词有时可以用集函数实现ANY与与ALL与集函数的对应关系与集函数的对应关系  = <>或或!= < <= > >=ANY IN -- MIN>= MINALL -- NOT IN MAX>= MAX关系数据库标准语言 最新带有ANY或ALL谓词的子查询[例例]查询每门课程最高分的学生学号查询每门课程最高分的学生学号 SELECT Sno,Cno FROM SC x WHERE Grade >= ALL (SELECT Grade FROM SC y WHERE x.Cno=y.Cno)•如何用集函数实现？如何用集函数实现？关系数据库标准语言 最新带有EXISTS谓词的子查询*•EXISTS谓词谓词–子查询子查询不返回任何数据不返回任何数据，只产生逻辑真值，只产生逻辑真值““true”true”或逻辑假值或逻辑假值““false”false”–其子查询其子查询目标列表达式目标列表达式通常都用通常都用 * *–子查询的查询条件子查询的查询条件依赖于外层父查询依赖于外层父查询的某个的某个属性值属性值关系数据库标准语言 最新带有EXISTS谓词的子查询(续）[ [例例44] 44] 查询所有选修了查询所有选修了1 1号课程的学生姓名号课程的学生姓名 SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno= ' 1 ')；；–查询没有选修查询没有选修1 1号课程的学生姓名号课程的学生姓名 关系数据库标准语言 最新带有EXISTS谓词的子查询(续）[ [例例45]45]查询没有选修查询没有选修1 1号课程的学生姓名号课程的学生姓名 SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno = Student.Sno AND Cno='1')；；–NOT EXISTS ，若内层查询结果为空，则外层的，若内层查询结果为空，则外层的WHERE子句返回真值，否则返回假值子句返回真值，否则返回假值关系数据库标准语言 最新带有EXISTS谓词的子查询(续）分析：查询没有选修分析：查询没有选修1 1号课程的学生姓名号课程的学生姓名–取外层查询取外层查询(Student)(Student)中的第一个元组中的第一个元组–根据其根据其与内层查询相关的属性值与内层查询相关的属性值(Sno)(Sno)处理内层查询处理内层查询–若若返回值为真返回值为真，取外层查询该元组的对应属性，取外层查询该元组的对应属性(Sname)(Sname)放入结果放入结果–取下一个元组，重复以上步骤取下一个元组，重复以上步骤关系数据库标准语言 最新练习•对第二章习题对第二章习题5中的四个表完成查询中的四个表完成查询–找出没有使用天津产的零件的工程号码找出没有使用天津产的零件的工程号码关系数据库标准语言 最新Review•嵌套查询嵌套查询–相关子查询相关子查询 & & 不相关子查询不相关子查询–带有带有ININ谓词谓词的子查询的子查询–带有带有比较运算符比较运算符的子查询的子查询–带有带有ANYANY或或ALLALL谓词谓词的子查询的子查询–带有带有EXISTSEXISTS谓词谓词的子查询的子查询* *关系数据库标准语言 最新练习•对第二章习题对第二章习题5中的四个表完成查询中的四个表完成查询–找出北京的任何工程都不购买的零件的零件号找出北京的任何工程都不购买的零件的零件号–求至少使用了供应商求至少使用了供应商S1所供应的全部零件的工所供应的全部零件的工程号程号JNO关系数据库标准语言 最新带有EXISTS谓词的子查询•用用EXISTS/NOT EXISTS实现实现全称量词全称量词的查询的查询–SQL语言中没有全称量词语言中没有全称量词关系数据库标准语言 最新•[ [例例46]46]查询选修了全部课程的学生姓名查询选修了全部课程的学生姓名–查询这样的学生，没有一门课程是他不选修的查询这样的学生，没有一门课程是他不选修的 SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM Course WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno= Student.Sno AND Cno= Course.Cno))–如果两个如果两个NOT EXISTS调换位置，含义是否相同？调换位置，含义是否相同？关系数据库标准语言 最新带有EXISTS谓词的子查询•用用EXISTS/NOT EXISTS实现实现逻辑蕴函逻辑蕴函*–SQL语言中没有蕴函词语言中没有蕴函词→→关系数据库标准语言 最新 [ [例例47] 47] 查询至少选修了学生查询至少选修了学生9500295002选修的全部课程的选修的全部课程的学生号码学生号码●查询学号为查询学号为x x的学生，对所有的课程的学生，对所有的课程y y，只要，只要9500295002学生选修学生选修了课程了课程y y，则，则x x也选修了也选修了y y●形式化表示：形式化表示：用用p p表示谓词表示谓词 “学生学生9500295002选修了课程选修了课程y y”用用q q表示谓词表示谓词 “学生学生x x选修了课程选修了课程y y”●等价变换等价变换−不存在这样的课程不存在这样的课程y，学生，学生95002选修了选修了y，而学生，而学生x没有选没有选 关系数据库标准语言 最新•查询至少选修了学生查询至少选修了学生9500295002选修的全部课程的学生号码选修的全部课程的学生号码 SELECT DISTINCT Sno FROM SC SCX WHERE NOT EXISTS (SELECT * FROM SC SCY WHERE SCY.Sno = ' 95002 ' AND NOT EXISTS (SELECT * FROM SC SCZ WHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno))关系数据库标准语言 最新综合练习•对第二章习题对第二章习题5中的四个表完成查询中的四个表完成查询–求至少使用了供应商求至少使用了供应商S1所供应的全部零件的工程号所供应的全部零件的工程号JNO–求没有使用天津供应商生产的红色零件的工程号求没有使用天津供应商生产的红色零件的工程号JNO–为所有工程供应了零件的供应商号，供应商名和他们为所有工程供应了零件的供应商号，供应商名和他们所在的城市所在的城市关系数据库标准语言 最新3.4 查 询 3.4.1 单表查询单表查询3.4.2 连接查询连接查询3.4.3 嵌套查询嵌套查询3.4.4 集合查询集合查询3.4.5 SELECT语句的一般格式语句的一般格式关系数据库标准语言 最新集合操作•<查询块查询块> UNION [ALL] / INTERSECT / EXCEPT <查询块查询块>–各结果表的列数必须相同各结果表的列数必须相同–对应项的数据类型也必须相同对应项的数据类型也必须相同关系数据库标准语言 最新并操作•[例例48]查询选修了课程查询选修了课程1或者选修了课程或者选修了课程2的学生的学生SELECT SNO FROM SCWHERE Cno=‘1’UNIONSELECT SNO FROM SCWHERE Cno=‘2’关系数据库标准语言 最新综合练习•对对P75 5完成下列查询完成下列查询ü取出所有工程的全部细节取出所有工程的全部细节ü取出重量最轻的零件的代号取出重量最轻的零件的代号ü取出供应商取出供应商S1提供的零件的颜色提供的零件的颜色ü取出为工程取出为工程J1和和J2提供零件的供应商号提供零件的供应商号ü取出为所在城市为上海的工程提供零件的供应商号取出为所在城市为上海的工程提供零件的供应商号ü取出供应商与工程所在城市相同的供应商提供的零取出供应商与工程所在城市相同的供应商提供的零件号件号关系数据库标准语言 最新。