数据库技术基础课件

,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,单击此处编辑母版标题样式,数据库技术基础,第,4,章 结构化查询语言,SQL,数据库技术基础第4章 结构化查询语言SQL,第,4,章,结构化查询语言,SQL,4.1,学习目标,4.2 SQL,概述,4.3,单表查询,4.4,多表查询,4.5,集合操作,4.6,嵌套查询,4.7,小结,第4章 结构化查询语言SQL4.1 学习目标,4.1,学习目标,学完本章后，读者应具备以下能力：,了解,SQL,的发展过程。,熟练掌握单表查询的,SELECT,子句、,WHERE,子句、,ORDER,子句。,理解分组的概念，掌握常用聚集函数和,GROUP BY,子句的使用方法。,理解连接的过程，掌握条件连接和外连接的使用方法。,了解嵌套查询的概念，掌握简单嵌套查询的使用方法。,了解常用的集合操作的实现。,4.1 学习目标学完本章后，读者应具备以下能力：,第,4,章,结构化查询语言,SQL,4.1,学习目标,4.2,SQL,概述,4.3,单表查询,4.4,多表查询,4.5,集合操作,4.6,嵌套查询,4.7,小结,第4章 结构化查询语言SQL4.1 学习目标,4.2 SQL,概述,4.2.1 SQL,的产生与发展,4.2.2 SQL,的组成,4.2.3 SQL,的特点,4.2 SQL概述 4.2.1 SQL的产生与发展,4.2.1 SQL,的产生与发展,标准 发布年份,SQUARE 1972,年,SEQUEL 1974,年,SQL/86,1986,年,SQL/89(FIPS 127-1) 1989,年,SQL/92 1992,年,SQL99 1999,年,SQL2003 2003,年,4.2.1 SQL的产生与发展 标准,4.2.2 SQL,的组成,1.,操作对象,表,关系模型中的关系。,表名、表结构（关系模式）和数据三部分组成,表的名字和结构存储在,DBMS,的数据字典中，表中的数据保存在数据库中。,(2),视图,是一个特殊的表，基本上可以把它当作表使用。,视图的概念将在下一章中介绍 。,4.2.2 SQL的组成1. 操作对象,4.2.2 SQL,的组成,(,续,),2.,操作分类,数据定义语言（,Data Definition Language,DDL,）,定义数据库的逻辑结构，包括定义表、视图和索引。,只是定义结构，不涉及具体的数据。,执行结果是在数据字典中记录下了这些定义。,数据操纵语言 （,Data Manipulation Lauguage,DML,）,数据更新包括插入、删除和修改操作。,数据操纵就是指对数据库中数据进行上述这些存取操作。,4.2.2 SQL的组成(续)2. 操作分类,4.2.2 SQL,的组成,(,续,),数据控制语言,（,Data Control Language,DCL,）,包括对数据的安全性控制、完整性规则的描述以及对事务的控制语句,。,嵌入式,SQL,（,Embeded SQL,）,规定如何在诸如,C,、,Fortan,、,Cobol,等宿主语言中使用,SQL,的规则,。,4.2.2 SQL的组成(续)数据控制语言 （Data Co,4.2.3 SQL,的特点,1.,综合统一,集数据定义语言、数据操纵语言、数据控制语言的功能于一身，语言风格统一。,可以独立完成数据库生命周期中的全部活动，为数据库应用系统的开发提供了良好的环境 。,数据库系统投入运行后，还可根据需要随时地逐步地修改模式，且不影响数据库的运行。,数据结构的单一性带来了数据操作符的统一，克服了非关系系统由于信息表示方式的多样性带来的操作复杂性。,4.2.3 SQL的特点1. 综合统一,4.2.3 SQL,的特点,(,续,),2.,高度非过程化,需指明“做什么”，而无须指明“怎么做”。,3.,面向集合的操作方式,SQL,语言采用集合操作方式，不仅操作对象、查找结果可以是元组的集合，而且一次插入、删除、更新操作的对象也可以是元组的集合。,4.,以同一种语法结构提供两种使用方式,SQL,语言既是自含式语言，又是嵌入式语言,。,4.2.3 SQL的特点(续)2. 高度非过程化,4.2.3 SQL,的特点（续）,5.,语言简捷，易学易用,SQL,语言接近英语口语，因此容易学习，容易使用。语言十分简捷，完成核心功能只用了,9,个动词，如表,4.1,所示。,SQL,功能,动 词,数 据 查 询,SELECT,数 据 定 义,CREATE,，,DROP,，,ALTER,数 据 操 纵,INSERT,，,UPDATE,，,DELETE,数 据 控 制,GRANT,，,REVOKE,表,4.1 SQL,语言的动词,4.2.3 SQL的特点（续）5. 语言简捷，易学易用 S,第,4,章,结构化查询语言,SQL,4.1,学习目标,4.2 SQL,概述,4.3,单表查询,4.4,多表查询,4.5,集合操作,4.6,嵌套查询,4.7,小结,第4章 结构化查询语言SQL4.1 学习目标,4.3,单表查询,单表查询就是从一个表生成另外一个表 。可以从横向和纵向对表进行操纵。,请注意：,查询操作的目的是将表的全部或部分内容显示给用户，或者做其他处理。,查询操作不影响原表，其结构和内容不会发生任何变化。,新表是一个临时结果，没有名字，表中的内容也没有保存到数据库中。,4.3 单表查询 单表查询就是从一个表生成另外一个表 。,4.3,单表查询（续）,Sno,Sname,Sgender,Sage,Sdept,20007012,马翔,男,19,计算机学院,2007113,刘大方,男,18,管理学院,2007256,史玉明,女,19,管理学院,2007278,龚兵,男,19,管理学院,2007014,张晓敏,女,18,计算机学院,Sno,Sname,Sdept,20007012,马翔,计算机学院,2007113,刘大方,管理学院,2007256,史玉明,管理学院,2007278,龚兵,管理学院,2007014,张晓敏,计算机学院,图,4.1,表的生成,-,选择列,student,4.3 单表查询（续）SnoSnameSgenderSag,4.3,单表查询 （续）,student,Sno,Sname,Sgender,Sage,Sdept,20007012,马翔,男,19,计算机学院,2007113,刘大方,男,18,管理学院,2007256,史玉明,女,19,管理学院,2007278,龚兵,男,19,管理学院,2007014,张晓敏,女,18,计算机学院,Sno,Sname,Sgender,Sage,Sdept,2007113,刘大方,男,18,管理学院,2007256,史玉明,女,19,管理学院,2007278,龚兵,男,19,管理学院,图,4.2,表的生成,-,选择行,4.3 单表查询 （续）studentSnoSnameS,4.3,单表查询,(,续,),Sno,Sname,Sgender,Sage,Sdept,20007012,马翔,男,19,计算机学院,2007113,刘大方,男,18,管理学院,2007256,史玉明,女,19,管理学院,2007278,龚兵,男,19,管理学院,2007014,张晓敏,女,18,计算机学院,Sno,Sname,Sdept,2007113,刘大方,管理学院,2007256,史玉明,管理学院,2007278,龚兵,管理学院,student,图,4.3,表的生成,-,选择行和列,4.3 单表查询(续)SnoSnameSgenderSa,4.3,单表查询,4.3.1 SELECT,语句简介,4.3.2,选择列,4.3.3,选择行,4.3.4,排序,4.3.5,聚集函数,4.3.6,分组,4.3.7,分组选择,4.3 单表查询 4.3.1 SELECT语句简介,4.3.1 SELECT,语句简介,SELECT,语句是,SQL,的核心语句，格式如下：,SELECT,ALL|DISTINCT ,别名, , ,别名, ,FROM, ,别名, , ,别名, ,WHERE,GROUP BY,HAVING,ORDER BY, ASC|DESC, ASC|DESC,；,4.3.1 SELECT语句简介SELECT语句是SQL的核,4.3,单表查询,4.3.1 SELECT,语句简介,4.3.2,选择列,4.3.3,选择行,4.3.4,排序,4.3.5,聚集函数,4.3.6,分组,4.3.7,分组选择,4.3 单表查询 4.3.1 SELECT语句简介,4.3.2,选择列,1.,查询指定列,例,4.1,查询全体学生的学号、姓名和所在学院。,SELECT Sno, Sname, Sdept,-,新表中要出现的列名,FROM Student,-,原表的名称,20007012,马翔,男,19,计算机学院,2007113,刘大方,男,18,管理学院,2007256,史玉明,女,19,管理学院,2007278,龚兵,男,19,管理学院,2007014,张晓敏,女,18,计算机学院,2000712,马翔,计算机学院,2007113,刘大方,管理学院,2007256,史玉明,管理学院,2007278,龚兵,管理学院,2007014,张晓敏,计算机学院,图,4.4,例,4.1,的执行结果,4.3.2 选择列1. 查询指定列20007012马翔男1,4.3.2,选择列,(,续,),2.,查询全部列,例,4.2,查询全体学生的全部信息,SELECT Sno, Sname,Sgender, Sage, Sdept,-,新表中要出现原表的全部列,FROM Student,-,原表的名称,由于经常会遇到要罗列表的全部列的场合，,SQL,提供了一个简单方法，用符号*表示枚举表的全部列。用这种方法，例,2,可以表述为：,SELECT *,-*,代表,Student,的全部列名,FROM Student,-,原表的名称,4.3.2 选择列(续)2. 查询全部列,4.3.2,选择列,(,续,),3.,查询经过计算的值,例,4.3,查询全体学生的姓名及其出生年份。,由于,Student,表中存放的是每个学生的年龄（假设每年年初由应用程序自动将年龄增加,1,岁），因此，无法直接得到出生年份。但是，可以通过将今年的年份减去年龄的运算，计算出每个学生的出生年份。,SELECT Sname, 2007 - Sage,-2007 Sage,是一个表达式,FROM Student,-,原表的名称,图,4.5,表达式作为列,4.3.2 选择列(续)3.查询经过计算的值 图4.5 表,4.3.2,选择列,(,续,),3.,查询经过计算的值（续）,SQL,提供了别名机制。,例如，将表达式,2007 Sage,命名为,BirthDay,，,Sname,命名为姓名，则例,4.3,可以表达为：,SELECT Sname AS,姓名, 2007,Sage AS BirthDay -2007,Sage,是一个表达式,FROM Student,-,原表的名称,图,4.6,别名的作用,4.3.2 选择列(续)3.查询经过计算的值（续）图4.6,4.3.2,选择列,(,续,),3.,查询经过计算的值（续）,使用函数,getdate(),返回系统当前的日期和时间。,使用函数,datepart(),返回日期的年份部分,。,如：,SELECT Sname, datepart(getdate() ,year),-,表达式中可以出现函数,FROM Student,-,原表的名称,图,4.7,增加新列,4.3.2 选择列(续)3.查询经过计算的值（续）图4.7,4.3.2,选择列,(,续,),4.,过滤掉重复元组,SELECT,语句提供了消除重复元组的手段，在,SELECT,子句中加上,DISTINCT,关键词。如果没有,DISTINCT,则保留重复值。,例,4.4,给出学校中每个学院的名称。,在,Student,表中记录了每个学生所在的学院，因此，可以通过取出每个学生在列,Sdept,上的值完成题目要求的操作：,SELECT Sdept -,只取出列,Sdept,的值,FROM Student -,原表的名称,查询结果如右图,4.8(a),所示：,Sdept,计算机学院,管理学院,管理学院,管理学院,计算机学院,(a),图,4.8,消除重复元组,Sdept,计算机学院,管理学院,(b),4.3.2 选择列(续)4.过滤掉重复元组 Sdept计算,4.3.2,选择列,(,续,),4.,过滤掉重复元组（续）,在,SELECT,子句中加上,DISTINCT,关键词,将例,4.4,改写为：,SELECT DISTINCT Sdept,-,只取出列,Sdept,的值,FROM Student,-,原表的名称,查询结果如,4.8(b),所示：,4.3.2 选择列(续)4.过滤掉重复元组（续）,4.3,单表查询,4.3.1 SELECT,语句简介,4.3.2,选择列,4.3.3,选择行,4.3.4,排序,4.3.5,聚集函数,4.3.6,分组,4.3.7,分组选择,4.3 单表查询 4.3.1 SELECT语句简介,4.3.3,选择行,WHERE,子句是在行方向上对表进行操作，返回满足条件表达式的元组集。常用的运算符见表,4.2,。,查 询 条 件,谓 词,比 较,=,，,，,=,，,（大于），,=,（大于等于），,=,（小于等于），,!=,或,（不等于）。,用比较运算符构成的条件表达式的形式是：,列名 运算符 常数,列名 运算符 列名,常数 运算符 列名,4.3.3 选择行(续)1. 比较大小,4.3.3,选择行（续）,1.,比较大小（续）,例,4.5,查询管理学院学生的信息,SELECT *,-,包含,Student,的全部列,FROM Student,-,原表的名称,WHERE,Sdept,= ,管理学院,-,过滤条件,例,4.6,查询年龄在,20,岁以下（不包含,20,岁）的学生信息。,SELECT *,-,包含,Student,的全部列,FROM Student,-,原表的名称,WHERE Sage 20,-,过滤条件,例,4.7,查询管理学院学生的,Sno,、,Sname,和,Sdept,的信息，,SELECT Sno, Sname, Sdept,-,仅包含指定的列,FROM Student,-,原表的名称,WHERE Sdept = ,管理学院,-,过滤条件,4.3.3 选择行（续）1. 比较大小（续）,4.3.3,选择行（续）,2.,确定范围,用谓词,BETWEENAND,和,NOT BETWEENAND,来查找属性值在（或不在）指定范围内的元组。,例,8,查询年龄在,1820,岁（包括,18,岁和,20,岁）之间的学生的姓名、所在学院和年龄。,SELECT Sname,Sdept,Sage,FROM Student,WHERE Sage BETWEEN 18 AND 20,4.3.3 选择行（续）2.确定范围,4.3.3,选择行（续）,2.,确定范围（续）,例,4.9,查询年龄不在,1820,岁（包括,18,岁和,20,岁）之间的学生的姓名、学院和年龄。,SELECT Sname,Sdept,Sage,FROM Student,WHERE Sage NOT BETWEEN 18 AND 20,4.3.3 选择行（续）2.确定范围（续）,4.3.3,选择行,3.,确定成员关系,谓词,IN,用来判断某个值是否是一个集合中的成员。语句格式为：,v NOT IN e1, e2,en,例,4.10,查询计算机学院和管理学院的学生的姓名和年龄。,对,Student,中的任意一个元组,t,，用,t.Sdept,表示,t,在列,Sdept,上的值，如果,t.Sdept ,计算机学院,，,管理学院,，则,t,就应该是查询的结果。,SQL,语句如下：,SELECT Sname, Sage,FROM Student,WHERE Sdept IN ( ,计算机学院, ,管理学院, ),4.3.3 选择行3.确定成员关系,4.3.3,选择行,(,续,),3.,确定成员关系（续）,例,4.11,查询既不是算机学院也不是管理学院的学生的姓名和年龄。,这个题目的过滤条件和例,4.10,的相反，与,IN,相对的谓词是,NOT IN,，因此，,SQL,语句为：,SELECT Sname, Sage,FROM Student,WHERE Sdept NOT IN ( ,计算机学院, ,管理学院, ),4.3.3 选择行(续)3.确定成员关系（续）,4.3.3,选择行,(,续,),4.,字符匹配,谓词,LIKE,可以用来进行字符串的匹配。其一般语法格式如下：,列名, NOT LIKE ESCAPE ,例,4.12,查询课程编号以,113,开头的课程名称。查询所有不姓王的学生的信息。,SELECT Cname,FROM Course,WHERE Cno LIKE 113_,查询课程编号以,113,开头的课程名称,SELECT *,FROM Student,WHERE Sname NOT LIKE ,王,%;_,查询所有不姓王的学生的信息,4.3.3 选择行(续)4.字符匹配,4.3.3,选择行,(,续,),4.,字符匹配（续）,例,4.13,查询课程名称包含字符串,DB_Design,的课程信息 。,SELECT *,FROM Course,WHERE Cname LIKE %DB_Design% ESCAPE ,4.3.3 选择行(续)4.字符匹配（续）,4.3.3,选择行（续）,5.,涉及空值的查询,在,SQL,中，判断一个值是否为空值要用专门的谓词,IS NOT NULL,。,注意：不能使用,Cpno = NULL,代替上面的过滤条件。,例,4.14,查询没有先修课的课程信息。,SELECT *,FROM Course,WHERE Cpno IS NULL -Cpno,的值是空值,如果要查询有先修课程的课程信息应该怎样表达过滤条件？,SELECT *,FROM Course,WHERE Cpno IS NOT NULL -Cpno,的值不是空值,4.3.3 选择行（续）5. 涉及空值的查询,4.3.3,选择行,(,续,),6.,多重条件查询,逻辑运算符,AND,和,OR,可用来连接多个查询条件。,AND,的优先级高于,OR,，但可以用括号改变优先级。,例,4.15,查询计算机学院年龄在,19,岁以下（不包括,19,岁）的学生姓名,。,SELECT Sname,FROM Student,WHERE Sdept= ,计算机学院, AND Sage 19,4.3.3 选择行(续)6.多重条件查询,4.3.3,选择行,(,续,),6.,多重条件查询 （续）,例,4.16,查询计算机学院和管理学院的学生的姓名和年龄。,SELECT Sname,FROM Student,WHERE Sdept= ,计算机学院, OR Sdept= ,管理学院,;,4.3.3 选择行(续)6.多重条件查询 （续）,4.3,单表查询,4.3.1 SELECT,语句简介,4.3.2,选择列,4.3.3,选择行,4.3.4,排序,4.3.5,聚集函数,4.3.6,分组,4.3.7,分组选择,4.3 单表查询 4.3.1 SELECT语句简介,4.3.4,排序,使用,ORDER BY,子句对查询结果进行排序。,ORDER BY,子句的格式：,ORDER BY ASC|DESC, ASC|DESC,例,4.17,查询全部学生的信息，按照年龄从小到大排序。,SELECT *,FROM Student,ORDER BY Sage -,按照,Sage,的值从小到大排序,语句的执行过程分为两个步骤：,首先完成查询,SELECT * FROM Student,，得到一个中间结果，如图,4.10(a),所示。,然后再对中间结果进行排序,作为最终的结果,如图,4.10( b),所示。,4.3.4 排序使用ORDER BY子句对查询结果进行排序,4.3.4,排序（续）,(a),(b),图,4.10,单列排序的过程,4.3.4 排序（续）(a)(b)图4.10 单列排序的过,4.3.4,排序,(,续,),例,4.18,查询所有的选课信息，要求先按照课程编号从大到小排序，然后再按学号从小到大排序。,SELECT *,FROM SC,ORDER BY Cno DESC, Sno,语句的执行过程分为三个步骤：,首先完成查询,SELECT * FROM SC,，得到一个初始结果如图（,a,）,然后对初始结果按照,Cno,从大到小排序得到如图,4.11,（,b,）所示的结果,在保持,Cno,的次序的基础上，再按照,Sno,从小到大排序。即对图,4.11,（,b,）所示的灰度不同的区域，分别按照,Sno,从小到大排序。,最后的结果如图,4.11,（,c,）所示。,4.3.4 排序(续) 例4.18 查询所有,Sno,Cno,Grade,2007012,1024,80,2007012,1136,78,2007012,1137,70,2007012,1156,80,2007014,1024,88,2007014,1136,90,2007014,1156,88,2007113,1156,89,2007256,1137,77,2007256,1156,93,2007278,1137,89,Sno,Cno,Grade,2007012,1156,80,2007014,1156,88,2007113,1156,89,2007256,1156,93,2007256,1137,77,2007278,1137,89,2007012,1137,70,2007012,1136,78,2007014,1136,90,2007014,1024,88,2007012,1024,80,Sno,Cno,Grade,2007012,1156,80,2007014,1156,88,2007113,1156,89,2007256,1156,93,2007012,1137,70,2007256,1137,77,2007278,1137,89,2007012,1136,78,2007014,1136,90,2007012,1024,80,2007014,1024,88,（,a,）,（,b,）,（,c,）,4.3.4,排序（续）,图,4.11,两列排序的过程,SnoCnoGrade2007012102480200701,4.3,单表查询,4.3.1 SELECT,语句简介,4.3.2,选择列,4.3.3,选择行,4.3.4,排序,4.3.5,聚集函数,4.3.6,分组,4.3.7,分组选择,4.3 单表查询 4.3.1 SELECT语句简介,4.3.5,聚集函数,为了方便用户，增强检索功能，,SQL,提供了一些聚集函数：,COUNT,（,DISTINCT|ALL *,）,统计元组个数,COUNT,（,DISTINCT|ALL ,统计一列中值的个数,SUM,（,DISTINCT|ALL ,）,计算一列值的总和（此列必须是数值型）,AVG,（,DISTINCT|ALL ,）,计算一列值的平均值（此列必须是数值型）,MAX,（,DISTINCT|ALL ,）,求一列值中的最大值,MIN,（,DISTINCT|ALL ,）,求一列值中的最小值,4.3.5 聚集函数 为了方便用户，增强检索功能，SQL提,4.3.5,聚集函数,(,续,),例,4.19,查询学生总人数。,学生人数即表,Student,中元组的个数，使用,COUNT(*),函数，,SQL,语句为：,SELECT COUNT(*),-,列为一个聚集函数,FROM Student,语句的执行结果是一个数值,5,，如图,4.12,。,图,4.12 SELECT,子句中的聚集函数,4.3.5 聚集函数(续) 例4.19 查询,4.3.5,聚集函数,(,续,),例,4.20,求出所有学生的平均年龄。,SELECT AVG(Sage),FROM Student,例,4.21,查询学生的最大年龄。,SELECT MAX(Sage),FROM Student,例,4.22,查询选修了课程编号为,1156,或者,1136,的学生人数。,依题意，即使一个学生选修了两门课程也只能被计数一次。因此，在,COUNT,函数中用,DISTINCT,短语去除重复值。,SELECT COUNT(DISTINCT Sno),FROM SC,WHERE Cno= 1156 OR Cno= 1136,执行结果如图,4.13,所示。,4.3.5 聚集函数(续) 例4.20 求出,4.3.5,聚集函数（续）,（,a,）,（,b,）,图,4.13,用,Distirct,去掉集中的重复元组,4.3.5 聚集函数（续）（a）（b）图4.13 用Dis,4.3.5,聚集函数,(,续,),例,4.23,实例数据库中有几门课程？,题目要求表,Course,中的元组的个数，以下三种表达方式中，哪一个是错误的？,SELECT COUNT(*) SELECT COUNT(Cno) SELECT COUNT(Cpno),FROM Course FROM Course FROM Course,（,a,） （,b,） （,c,）,（,c,）的结果是错误的 。,因为，,Cpno=1136, NULL, 1128, NULL, NULL,，,COUNT,函数执行时，首先从自变量值的集合中去除,NULL,值，,COUNT(Cpno)=count(1136, NULL, 1128, NULL, NULL)=COUNT(1136, 1128)=2,。而,Cno=1024, 1128, 1136, 1137, 1156,，所以,COUNT(Cno)=5,。,4.3.5 聚集函数(续)例4.23 实例数据库中有几,4.3,单表查询,4.3.1 SELECT,语句简介,4.3.2,选择列,4.3.3,选择行,4.3.4,排序,4.3.5,聚集函数,4.3.6,分组,4.3.7,分组选择,4.3 单表查询 4.3.1 SELECT语句简介,4.3.6,分组,分组就是将具有,相同特征,（在一个或多个列上的值相同）的元组分配到同一个组，作为,一个整体,进行处理。,SQL,用,GROUP BY,子句实现分组功能，格式为：,GROUP BY ,列名表描述了分组特征，同一组的元组在这些列上的值一定相同。列名表又叫做,分组列,。,4.3.6 分组分组就是将具有相同特征（在一个或多个列上的,4.3.6,分组,(,续,),例,4.24,统计每门课程的选课人数。,下图,4.14,（,a,）展现了,SC,表中的所有元组。将在,Cno,列上有相同值的元组划分到同一组，得到,4,个分组，每个分组在图,4.14,（,b,）用不同灰度的区域表示（请想一下，用什么办法实现分组呢？）。然后，将同一组每个元组在,Sno,列上的值集合传送给聚集函数,COUNT,，函数,COUNT,的值即为答案。,SQL,语句如下，其执行过程和结果如图,4.14,（,b,）所示。,SELECT Cno AS,课程编号, COUNT(Sno) AS,人数,FROM SC,GROUP BY Cno,4.3.6 分组(续)例4.24 统计每门课程的选课人,4.3.6,分组（续）,图,4.14,分组和聚集的过程,(a),(b),4.3.6 分组（续）图4.14 分组和聚集的过程(a)(,4.3.6,分组,(,续,),请思考，下面的三种表达方法和上面的结果相同吗？,1,、,SELECT Cno AS,课程编号, COUNT(,*),AS,人数,FROM SC,GROUP BY Cno,2,、,SELECT Cno AS,课程编号, COUNT(,Cno),AS,人数,FROM SC,GROUP BY Cno,3,、,SELECT Cno AS,课程编号, COUNT(,Grade),AS,人数,FROM SC,GROUP BY Cno,4.3.6 分组(续)请思考，下面的三种表达方法和上面的结,4.3.6,分组,(,续,),例,4.25,统计各个学院男生和女生的人数。,Sno,Sname,Sgender,Sage,Sdept,2007012,马翔,男,19,计算机学院,2007014,张晓敏,女,18,计算机学院,2007113,刘大方,男,18,管理学院,2007256,史玉明,女,19,管理学院,2007278,龚兵,男,19,管理学院,Sno,Sname,Sgender,Sage,Sdept,2007012,马翔,男,19,计算机学院,2007014,张晓敏,女,18,计算机学院,2007113,刘大方,男,18,管理学院,2007278,龚兵,男,19,管理学院,2007256,史玉明,女,19,管理学院,(a),先要按照,Sdept,对学生进行分组,(b),再对每个分组按照,Sgender,分组,图,4.15,两个分组列的分组过程,结果如图,4.15,所示。,4.3.6 分组(续)例4.25 统计各个学院男生和女,4.3.6,分组,(,续,),（,a,）,（,b,）,完成例,4.25,的,SQL,语句及查询结果请见图,4.16,（,a,）所示。从图看出，同一个学院的元组没有相邻。解决办法是加上,ORDER BY,子句，,SQL,语句和结果如图,4.16,（,b,）所示。,图,4.16,子句的执行次序,4.3.6 分组(续)（a）（b）完成例4.25的SQL语,4.3,单表查询,4.3.1 SELECT,语句简介,4.3.2,选择列,4.3.3,选择行,4.3.4,排序,4.3.5,聚集函数,4.3.6,分组,4.3.7,分组选择,4.3 单表查询 4.3.1 SELECT语句简介,4.3.,分组选择,对分组进行选择操作由,HAVING,子句完成。格式如下：,HAVING ,例,4.26,求选课人数超过,2,人的课程编号和具体的选课人数。,SELECT Cno AS,课程编号, COUNT(Sno) AS,人数,FROM SC,GROUP BY Cno,HAVING COUNT(Sno) 2,例,4.27,求选课人数超过,2,人并且课程编号中包含字符,7,的课程编号和具体的选课人数。,SELECT Cno, COUNT(Sno) AS,人数,FROM SC,GROUP BY Cno,HAVING Cno LIKE %7% AND COUNT(Sno) 2,4.3. 分组选择对分组进行选择操作由HAVING子句完,第,4,章,结构化查询语言,SQL,4.1,学习目标,4.2 SQL,的产生和发展,4.3,单表查询,4.4,多表查询,4.5,集合操作,4.6,嵌套查询,4.7,小结,第4章 结构化查询语言SQL4.1 学习目标,4.4,多表查询,4.4.1,交叉连接,4.4.2,条件连接,4.4.3,外连接,4.4 多表查询 4.4.1 交叉连接,交叉连接是连接操作的最一般情形。,交叉连接是将两个表变成了一个表，两个表的全部列名作为新表的列名，两个表的元组两两首尾相连得到新表中的一个元组。,假设两个表分别有,m,和,n,个元组，则新表有,m,n,个元组。,Sno,Sname,Sgender,Sage,Sdept,2007012,马翔,男,19,计算机学院,2007014,张晓敏,女,18,计算机学院,2007113,刘大方,男,18,管理学院,2007256,史玉明,女,19,管理学院,2007278,龚兵,男,19,管理学院,Cno,Cname,Cpno,Ccredit,1024,数据库原理,1136,4,1128,高等数学,NULL,6,1136,离散数学,1128,4,1137,管理学,NULL,4,1156,英语,NULL,6,4.4.1,交叉连接,图,4.17,交叉连接的操作过程,交叉连接是连接操作的最一般情形。SnoSnameSgende,4.4.1,交叉连接,（续）,Sno,Sname,Sgender,Sage,Sdept,Cno,Cname,Cpno,Ccredit,2007012,马翔,男,19,计算机学院,1024,数据库原理,1136,4,2007012,马翔,男,19,计算机学院,1128,高等数学,NULL,6,2007012,马翔,男,19,计算机学院,1136,离散数学,1128,4,2007012,马翔,男,19,计算机学院,1137,管理学,NULL,4,2007012,马翔,男,19,计算机学院,1156,英语,NULL,6,2007014,张晓敏,女,18,计算机学院,1024,数据库原理,1136,4,2007014,张晓敏,女,18,计算机学院,1128,高等数学,NULL,6,2007014,张晓敏,女,18,计算机学院,1136,离散数学,1128,4,2007014,张晓敏,女,18,计算机学院,1137,管理学,NULL,4,2007014,张晓敏,女,18,计算机学院,1156,英语,NULL,6,2007113,刘大方,男,18,管理学院,1024,数据库原理,1136,4,2007113,刘大方,男,18,管理学院,1128,高等数学,NULL,6,2007113,刘大方,男,18,管理学院,1136,离散数学,1128,4,2007113,刘大方,男,18,管理学院,1137,管理学,NULL,4,2007113,刘大方,男,18,管理学院,1156,英语,NULL,6,2007256,史玉明,女,19,管理学院,1024,数据库原理,1136,4,2007256,史玉明,女,19,管理学院,1128,高等数学,NULL,6,2007256,史玉明,女,19,管理学院,1136,离散数学,1128,4,2007256,史玉明,女,19,管理学院,1137,管理学,NULL,4,2007256,史玉明,女,19,管理学院,1156,英语,NULL,6,2007278,龚兵,男,19,管理学院,1024,数据库原理,1136,4,2007278,龚兵,男,19,管理学院,1128,高等数学,NULL,6,2007278,龚兵,男,19,管理学院,1136,离散数学,1128,4,2007278,龚兵,男,19,管理学院,1137,管理学,NULL,4,2007278,龚兵,男,19,管理学院,1156,英语,NULL,6,表,4.3,表,Student,和,Course,的交叉连接,4.4.1 交叉连接（续）SnoSnameSgender,4.4.1,交叉连接,(续,),观察,4.3,表，可以得出以下几个结论：,交叉连接的输入是两个表（,Student,，,Course,），输出是一个新表。,新表的一列是某个表中的一列，二者的名称也一样。新表的列数是两个表列数之和。,Student,的一个元组和,Course,的一个元组首尾相连形成了新表的一个元组,Student,和,Course,中各有,5,条元组，新表有,25,条元组。,4.4.1 交叉连接(续)观察4.3表，可以得出以下几个,4.4.1,交叉连接,（续）,产生表,4.3,的,SQL,语句是：,SELECT *,FROM Student , Course,语法符合,SQL86,以后的标准。在,SQL-92,及以后的标准中，用下面的语法格式：,Student CROSS JOIN Course,下面语句的部分结果如表,4.4,所示。,SELECT *,FROM Course CROSS JOIN SC,4.4.1 交叉连接（续）产生表4.3的SQL语句是：,4.4.1,交叉连接,（续）,Cno,Cname,Cpno,Ccredit,Sno,Cno,Grade,1024,数据库原理,1136,4,2007012,1024,80,1024,数据库原理,1136,4,2007012,1136,78,1024,数据库原理,1136,4,2007012,1137,70,1024,数据库原理,1136,4,2007012,1156,80,1024,数据库原理,1136,4,2007014,1024,88,1024,数据库原理,1136,4,2007014,1136,90,1024,数据库原理,1136,4,2007014,1156,88,1024,数据库原理,1136,4,2007113,1156,89,1024,数据库原理,1136,4,2007256,1137,77,1024,数据库原理,1136,4,2007256,1156,93,1024,数据库原理,1136,4,2007278,1137,89,1128,高等数学,NULL,6,2007012,1024,80,1128,高等数学,NULL,6,2007012,1136,78,1128,高等数学,NULL,6,2007012,1137,70,1128,高等数学,NULL,6,2007012,1156,80,1128,高等数学,NULL,6,2007014,1024,88,1128,高等数学,NULL,6,2007014,1136,90,1128,高等数学,NULL,6,2007014,1156,88,1128,高等数学,NULL,6,2007113,1156,89,1128,高等数学,NULL,6,2007256,1137,77,1128,高等数学,NULL,6,2007256,1156,93,1128,高等数学,NULL,6,2007278,1137,89,表,4.4,表,Course,和,SC,的交叉连接（部分结果）,4.4.1 交叉连接（续）CnoCnameCpnoCcre,4.4,多表查询,4.4.1,交叉连接,4.4.2,条件连接,4.4.3,外连接,4.4 多表查询 4.4.1 交叉连接,4.4.2,条件,连接,Sno,Cno,Grade,2007012,1024,80,2007012,1136,78,2007012,1137,70,2007012,1156,80,2007014,1024,88,2007014,1136,90,2007014,1156,88,2007113,1156,89,Cno,Cname,Cpno,Ccredit,1024,数据库原理,1136,4,1128,高等数学,NULL,6,1136,离散数学,1128,4,1137,管理学,NULL,4,图,4.18,条件连接的操作过程,4.4.2 条件连接SnoCnoGrade20070121,4.4.2,条件,连接,（续）,Cno,Cname,Cpno,Ccredit,Sno,Cno,Grade,1024,数据库原理,1136,4,2007012,1024,80,1024,数据库原理,1136,4,2007014,1024,88,1136,离散数学,1128,4,2007014,1136,90,1136,离散数学,1128,4,2007012,1136,78,1137,管理学,NULL,4,2007012,1137,70,1137,管理学,NULL,4,2007256,1137,77,1137,管理学,NULL,4,2007278,1137,89,1156,英语,NULL,6,2007256,1156,93,1156,英语,NULL,6,2007012,1156,80,表,4.5 Course,和,SC,条件连接的结果,4.4.2 条件连接（续）CnoCnameCpnoCcre,4.4.2,条件,连接,（续）,例,4.28,查询每门课程的选修情况。,SELECT *,FROM,Course, SC,-,参与连接的两个表名,WHERE,Course.Cno = SC.Cno,-,连接条件,4.4.2 条件连接（续）例4.28 查询每门课程的选,4.4.2,条件,连接,(续,),例,4.29,查询某门课程考试成绩为优的学生的学号、姓名以及所在院系。,图,4.19,复合连接条件,4.4.2 条件连接(续) 例4.29 查询某门课,4.4.2,条件,连接,例,4.30,查询学生,2007012,所选修的课程的课程名称、学分和考试成绩，并要求按考试成绩排序。,SELECT Cname, Ccredit, Grade,FROM,Course JOIN SC ON Course.Cno = SC.Cno,-,条件连接的结果是一个表,WHERE Sno = 2007012 -,对连接结果进行过滤的条件,ORDER BY Grade -,对过滤后的元组再排序,SQL-89,表示形式如下：,SELECT Cname, Ccredit, Grade,FROM,Course, SC,-WHERE,子句中，加黑部分是连接条件，斜体部分是过滤条件,WHERE,Course.Cno = SC.Cno,AND,Sno = 2007012,-AND,连接两个条件,ORDER BY Grade,4.4.2 条件连接 例4.30 查询学生2,4.4.2,条件,连接,(续,),例,4.31,查询某门课程考试成绩为优的学生的学号、姓名以及所在院系。,SELECT Cname, Ccredit, Grade,FROM,Course JOIN SC ON Course.Cno = SC.Cno,AND Sno = 2007012,ORDER BY Grade,图,4.20,连接条件和过滤条件可以互相转换,4.4.2 条件连接(续) 例4.31 查询,4.4.2,条件,连接,（续）,例,4.32,查询每个学生的学号、姓名、选修的课程名及成绩。,SELECT Student.Sno, Sname, Cname, Grade,FROM Student,SC, Course,WHERE,Student.Sno = SC.Sno,AND,SC.Cno = Course.Cno,Sno,Sname,Sgender,Sage,Sdept,2007012,马翔,男,19,计算机学院,2007014,张晓敏,女,18,计算机学院,2007113,刘大方,男,18,管理学院,2007256,史玉明,女,19,管理学院,Cno,Cname,Cpno,Ccredit,1024,数据库原理,1136,4,1128,高等数学,NULL,6,1136,离散数学,1128,4,1137,管理学,NULL,4,1156,英语,NULL,6,Sno,Cno,Grade,2007012,1024,80,2007012,1136,78,2007012,1137,70,2007012,1156,80,2007014,1024,88,2007014,1136,90,2007014,1156,88,2007113,1156,89,2007256,1137,77,2007256,1156,93,2007278,1137,89,图,4.21 3,个表的条件连接过程,4.4.2 条件连接（续）例4.32 查询每个学生的学,4.4.2,条件,连接,(续,),例,4.33,查询每一门课的间接先修课（即先修课的先修课）。,SQL-89,的,SQL,语句如下：,SELECT A.Cno, B.Cpno,FROM Course A, Course B,WHERE A.Cpno = B.Cno -,连接条件,下面的语句中，应该用,a,替换加黑的,student,。,SELECT,student.,Sno,FROM student a,WHERE,student.,sno=2007012,4.4.2 条件连接(续)例4.33 查询每一门课的间,4.4.2,条件,连接,(续,),a,b,图,4.22 Course,自连接的结果,4.4.2 条件连接(续)ab图4.22 Course 自,4.4,多表查询,4.4.1,交叉连接,4.4.2,条件连接,4.4.3,外连接,4.4 多表查询 4.4.1 交叉连接,4.4.3,外连接,外连接分为左连接（,LEFT OUTER JOIN,）、右连接（,RIGHT OUTER JOIN,）和全连接（,FULL OUTER JOIN,）。,左连接的语法格式为：,Table1 LEFT OUTER JOIN Table2 ON Conditio