资源描述
,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,单击此处编辑母版标题样式,数据库技术基础,第,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
展开阅读全文