第4章 关系数据库标准语言-SQL-2

单击此处编辑母版标题样式,Click to edit Master text stylesgood1,Second levelgood2,Third levelgood3,Fourth levelgood4,Fifth levelgood5,Shandong University,*,/54,Hui,Yuan,第四章 关系数据库标准语言,-SQL (,II),1,/54,查询,􀂄概述,􀂄单表查询,􀂄连接查询,􀂄嵌套查询,􀂄集合查询,2,查询语句概述,语句格式,SELECTALL|DISTINCT ,，, ,FROM ,，, , WHERE , GROUP BY HAVING , ORDER BY ASC|DESC ,；,3,查询语句（续）,SELECT,子句：指定要显示的属性列,FROM,子句：指定查询对象,(,基本表或视图,),WHERE,子句：指定查询条件,GROUP BY,子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数。,HAVING,短语：筛选出只有满足指定条件的组,ORDER BY,子句：对查询结果表按指定列值的升序或降序排序,4,单表查询,查询仅涉及一个表，是一种最简单的查询操作,1,、选择表中的若干列,2,、选择表中的若干元组,3,、对查询结果排序,4,、使用集函数,5,、对查询结果分组,5,查询表中指定列,例,1,查询全体学生的学号与姓名。,SELECT,Sno,，,Sname,FROM Student,；,例,2,查询全体学生的姓名、学号、所在系。,SELECT,Sname,，,Sno,，,Sdept,FROM Student,；,注：,中的各列用逗号分开，他们之间的顺序可以与表中的顺序不一致。,6,查询全部列,例,3,查询全体学生的详细记录。,SELECT,Sno,，,Sname,，,Ssex,，,Sage,，,Sdept,FROM Student,；,或,SELECT *,FROM Student,；,7,查询指定列,在多数情况下，用户只对一部分列信息感兴趣，此时就可以在,SELECT,子句后面指定要查询的属性列名。,例,4.39,查询出,USER,表中用户名及其编号。,SELECT ID,，,NAME,FROM USER;,例,4.40,查询出商品表中的商品名及商品价格。,SELECT NAME,，,PRICE,FROM PRODUCT;,8,/54,查询经过计算的值,SELECT,子句的,为表达式,算术表达式,字符串常量,函数,列别名,等,9,查询经过计算的值,例子,例,4,查全体学生的姓名及其出生年份。,SELECT,Sname,，,2000-Sage,FROM Student,；,输出结果：,Sname,2000-Sage,-,李勇,1986,刘晨,1987,王名,1988,张立,1988,10,查询经过计算的值,例子,例,5,查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。,SELECT,Sname,，,Year of Birth: ,，,2000-Sage,，,ISLOWER(Sdept,),FROM Student,；,11,查询经过计算的值,例子（续）,输出结果：,Sname,Year of Birth: 2000-Sage,ISLOWER(Sdept,),-,李勇,Year of Birth: 1976,cs,刘晨,Year of Birth: 1977 is,王名,Year of Birth: 1978 ma,张立,Year of Birth: 1977 is,12,查询经过计算的值,SELECT,关键字后面的,既可是表中的属性列，也可以是表达式。,例,4.41,查询出商品名及其九折价格。,SELECT NAME,，,PIECE*0.9,FROM PRODUCT;,不仅可以是表达式，还可以是字符串常量和函数等。,例,4.42,查询出所有用户名及其地址，并且要求用小写字母来表示地址信息。,SELECT NAME,，,LOWER,（,ADDRESS,）,FROM USER;,13,/54,2.,选择表中的若干元组,（,1,）消除重复行,两个并不相同的元组，投影到某些列后会出现相同的几个元组，此时一般就需要消除重复元组。,例,4.43,查询出订购了商品的用户编号。,SELECT USER_ID,FROM USER_ORDER;,14,/54,由于同一个用户可能订购多种商品，所以上例中得到的,USER_ID,可能会有重复值，如果要去掉重复值必须用,DISTINCT,关键字。若没有,DISTINC,关键字，则为,ALL,，不消除重复值。要特别注意,DISTINCT,修饰的是其后面的所有列。,SELECT DISTINCT USER_ID,FROM USER_ORDER;,SELECT USER_ID,FROM USER_ORDER;,等价于,SELECT ALL USER_ID,FROM USER_ORDER;,15,/54,消除查询结果中的重复行,在,SELECT,子句中使用,DISTINCT,短语,假设,SC,表中有下列数据,Sno,Cno,Grade,-,95001 1 92,95001 2 85,95001 3 88,95002 2 90,95002 3 80,16,消除查询结果中的重复行（续）,例,6,查询选修了课程的学生学号。,(1) SELECT,Sno,FROM SC;,或,(,默认,ALL),SELECT ALL,Sno,FROM SC;,结果：,Sno,-,95001,95001,95001,95002,95002,17,消除查询结果中的重复行（续）,(2) SELECT DISTINCT,Sno,FROM SC;,结果：,Sno,-,95001,95002,18,消除查询结果中的重复行（续）,注意,DISTINCT,短语的作用范围是,所有目标列,例：查询选修课程的各种成绩,错误的写法,SELECT DISTINCT,Cno,，,DISTINCT Grade,FROM SC;,正确的写法,SELECT DISTINCT,Cno,，,Grade,FROM SC;,19,查询表中符合条件的元组,20,WHERE,子句常用的查询条件,（,1,）比较大小,在,WHERE,子句的,中使用比较运算符,=,，,，,=,，,，,!,，,逻辑运算符,NOT +,比较运算符,例,8,查询所有年龄在,20,岁以下的学生姓名及其年龄。,SELECT,Sname,，,Sage,FROM Student,WHERE Sage = 20,；,21,（,2,）确定范围,使用谓词,BETWEEN AND ,NOT BETWEEN AND ,例,10,查询年龄在,2023,岁（包括,20,岁和,23,岁）之间的学生的姓名、系别和年龄。,SELECT,Sname,，,Sdept,，,Sage,FROM Student,WHERE Sage BETWEEN 20 AND 23,；,22,确定范围（续）,例,11,查询年龄不在,2023,岁之间的学生姓名、系别和年龄。,SELECT,Sname,，,Sdept,，,Sage,FROM Student,WHERE Sage NOT BETWEEN 20 AND 23,；,23,（,3,）确定集合,使用谓词,IN , NOT IN ,：用逗号分隔的一组取值,例,12,查询信息系（,IS,）、数学系（,MA,）和计,算机科学系（,CS,）学生的姓名和性别。,SELECT,Sname,，,Ssex,FROM Student,WHERE,Sdept,IN ( IS,，,MA,，,CS );,24,确定集合（续）,例,13,查询既不是信息系、数学系，也不是计算,机科学系的学生的姓名和性别。,SELECT,Sname,，,Ssex,FROM Student,WHERE,Sdept,NOT IN ( IS,，,MA,，,CS ),；,25,（,4,）字符串匹配,NOT LIKE ,ESCAPE ,：指定匹配模板,匹配模板：固定字符串或含通配符的字符串,当匹配模板为固定字符串时，,可以用,=,运算符取代,LIKE,谓词,用,!=,或,运算符取代,NOT LIKE,谓词,26,通配符,% (,百分号,),：代表,任意长度,（长度可以为,0,）的字符串,例：,a%b,表示以,a,开头，以,b,结尾的任意长度的字符串。如,acb,，,addgb,，,ab,等都满足该匹配串,_ (,下横线,),：代表任意,单个字符,例：,a_b,表示以,a,开头，以,b,结尾的长度为,3,的任意字符串。如,acb,，,afb,等都满足该匹配串,当用户要查询的字符串本身就含有,%,或,_,时,，要使用,ESCAPE ,短语对通配符进行转义，从而跟在换码字符后面的字符不再具有通配符意义。,27,字符串匹配,例子,1),匹配模板为固定字符串,例,14,查询学号为,95001,的学生的详细情况。,SELECT *,FROM Student,WHERE,Sno,LIKE 95001,；,等价于：,SELECT *,FROM Student,WHERE,Sno,= 95001,；,28,字符串匹配,例子（续）,2),匹配模板为含通配符的字符串,例,15,查询所有姓刘学生的姓名、学号和性别。,SELECT,Sname,，,Sno,，,Ssex,FROM Student,WHERE,Sname,LIKE ,刘,%,；,29,字符串匹配,例子（续）,匹配模板为含通配符的字符串（续）,例,16,查询姓,欧阳,且全名为三个汉字的学生的姓名。,SELECT,Sname,FROM Student,WHERE,Sname,LIKE ,欧阳,_,；,30,字符串匹配,例子（续）,匹配模板为含通配符的字符串（续）,例,17,查询名字中第,2,个字为,阳,字的学生的姓名和学号。,SELECT,Sname,，,Sno,FROM Student,WHERE,SnameLIKE,_,阳,%,；,31,字符串匹配,例子（续）,匹配模板为含通配符的字符串（续）,例,18,查询所有不姓刘的学生姓名。,SELECT,Sname,，,Sno,，,Ssex,FROM Student,WHERE,Sname,NOT LIKE ,刘,%,；,32,字符串匹配,例子（续）,3),使用换码字符将通配符转义为普通字符,例,19,查询,DB_Design,课程的课程号和学分。,SELECT,Cno,，,Ccredit,FROM Course,WHERE,Cname,LIKE DB_Design ESCAPE ,；,33,字符串匹配,例子（续）,使用换码字符将通配符转义为普通字符,(,续,),例,20,查询以,DB_,开头，且倒数第,3,个字符为,i,的课程的,详细情况,。,SELECT,*,FROM Course,WHERE,Cname,LIKE,DB_%i_ _ ESCAPE ,；,34,（,5,）涉及空值的查询,使用谓词,IS NULL,或,IS NOT NULL,“,IS NULL”,不能用“,= NULL”,代替,例,21,某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。,SELECT,Sno,，,Cno,FROM SC,WHERE Grade IS NULL,；,35,涉及空值的查询,(,续,),例,22,查所有有成绩的学生学号和课程号。,SELECT,Sno,，,Cno,FROM SC,WHERE Grade IS NOT NULL,；,36,（,6,）多重条件查询,用逻辑运算符,AND,和,OR,来联结多个查询条件,􀂄,AND,的优先级高于,OR,􀂄可以用括号改变优先级,可用来实现多种其他谓词,􀂄,NOT IN,􀂄,NOT BETWEEN AND ,37,多重条件查询,例子,例,23,查询信息系（,IS,）、数学系（,MA,）和计算机科学系（,CS,）学生的姓名和性别。,SELECT,Sname,，,Ssex,FROM Student,WHERE,Sdept,IN ( IS,，,MA,，,CS ),；,可改写为：,SELECT,Sname,，,Ssex,FROM Student,WHERE,Sdept,= IS OR,Sdept,= MA OR,Sdept,= CS,；,38,多重条件查询,例子（续）,例,查询年龄在,2023,岁（包括,20,岁和,23,岁）,之间的学生的姓名、系别和年龄。,SELECT,Sname,，,Sdept,，,Sage,FROM Student,WHERE Sage BETWEEN 20 AND 23,；,可改写为：,SELECT,Sname,，,Sdept,，,Sage,FROM Student,WHERE Sage=20 AND Sage=23,；,39,三、对查询结果排序,使用,ORDER BY,子句,可以按,一个或多个,属性列排序,升序：,ASC,；降序：,DESC,；缺省值为升序,当排序列含空值时,ASC,：排序列为空值的元组最后显示,DESC,：排序列为空值的元组最先显示,40,对查询结果排序,例子,例,24,查询选修了,3,号课程的学生的学号及其成绩，查询结果按分数降序排列。,SELECT,Sno,，,Grade,FROM SC,WHERE,Cno,= 3 ,ORDER BY Grade DESC,；,41,查询结果,Sno,Grade,-,95010,95024,95007 92,95003 82,95010 82,95009 75,95014 61,95002 55,42,对查询结果排序,例子,例,25,查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。,SELECT *,FROM Student,ORDER BY,Sdept,，,Sage DESC,；,43,四、使用集函数（聚合函数）,5,类主要集函数,计数,COUNT,（,DISTINCT|ALL *,）,COUNT,（,DISTINCT|ALL ,）,计算总和,SUM,（,DISTINCT|ALL ,）,计算平均值,AVG,（,DISTINCT|ALL ,）,44,使用集函数,（聚合函数）,（续）,求最大值,MAX,（,DISTINCT|ALL ,）,求最小值,MIN,（,DISTINCT|ALL ,）,DISTINCT,短语：在计算时要取消指定列中的重复值,ALL,短语：不取消重复值,ALL,为缺省值,45,使用集函数,例子,例,26,查询学生总人数。,SELECT COUNT(*),FROM Student,；,例,27,查询选修了课程的学生人数。,SELECT COUNT(DISTINCT,Sno,),FROM SC,；,注：用,DISTINCT,以避免重复计算学生人数,46,使用集函数,例子,例,28,计算,1,号课程的学生平均成绩。,SELECT,AVG(Grade,),FROM SC,WHERE,Cno,= 1 ,；,例,29,查询选修,1,号课程的学生最高分数。,SELECT,MAX(Grade,),FROM SC,WHERE,Cno,= 1 ,；,47,五、对查询结果分组,使用,GROUP BY,子句分组,细化集函数的作用对象,未对查询结果分组，集函数将作用于整个查询结果,对查询结果分组后，集函数将分别作用于每个组,48,对查询结果分组,例子,例,30,求各个课程号及相应的选课人数。,SELECT,Cno,，,COUNT(Sno,),FROM SC,GROUP BY,Cno,；,结果,Cno,COUNT(Sno,),1 22,2 34,3 44,4 33,5 48,49,对查询结果分组（续）,GROUP BY,子句的作用对象是查询的中间结果表,分组方法：,按指定的一列或多列值分组，值相等的为一组,使用,GROUP BY,子句后，,SELECT,子句的列名列表中只能出现分组属性和集函数,50,使用,HAVING,短语筛选输出结果,例,31,查询选修了,3,门以上课程的学生学号。,SELECT,Sno,FROM SC,GROUP BY,Sno,HAVING COUNT(*) 3,51,使用,HAVING,短语筛选输出结果（续）,例,32,查询有,3,门以上课程是,90,分以上的,学生的学号及（,90,分以上的）课程数,SELECT,Sno, COUNT(*),FROM SC,WHERE Grade=90,GROUP BY,Sno,HAVING COUNT(*)=3;,52,HAVING,子句说明,上例先用,GROUP BY,子句按,Sno,进行分组，再用集函数,COUNT,对每一组进行计数。,HAVING,短语指定选择组的条件，只有满足条件（即元组个数,3,，表示此学生选修的课程超过,3,门）的组才会选出来。,WHERE,子句与,HAVING,短语的区别在于作用对象不同。,WHERE,子句作用于基本表或者视图，从中选择满足条件的元组。,HAVING,短语作用于组，从中选择满足条件的组。,53,54,下课！,