第三章——关系数据库标准语言SQL——数据查询

数据查询

• SQL提供了SELECT语句进行数据查询，该语句具有灵活的使用方式和丰富的功能。其一般格式为：

SELECT [ALL|DISTINCT] <目标列表达式>[,<目标列表达式>]…
FROM <表名或视图名> [,<表名或视图名>…] | (<SELET语句>) [AS] <别名>
[WHERE <条件表达式>]
[GROUP BY <列名1> [HAVING <条件表达式>]] [ORDER BY <列名2> [ASC | DESC]];

单表查询

• 单表查询是指仅涉及一个表的查询
  

选择表中的若干列

• 选择表中的全部或部分列即关系代数的投影运算

(1)查询指定列

例1：查询全体学生的姓名、学号、所在系
SELECT Sname,Sno,Sdept
FROM Student;

• <目标列表达式>中各个列的先后顺序可以与表中的顺序不一致

(2)查询全部列

• 将表中的所有属性列都选出来有两种方法：一种方法就是在SELECT关键字后列出所有列名；如果列的显示顺序与其在基表中的顺序相同，也可以简单地将<目标列表达式>指定为*

例2：查询全体学生的详细记录
SELECT *
FROM Student;
等价于：
SELECT Sno,Sname,Ssex,Sage,Sdept
FROM Student;

(3)查询经过计算的值

• SELECT子句的<目标列表达式>不仅可以是表中的属性列，也可以是表达式

例3：查询全体学生的姓名、出生年份和所在院系，要求用小写字母表示系名
SELECT Sname NAME,‘Year of Birth:’ BIRTH,2014-Sage BIRTHDAY,LOWER(Sdept) DEPARTMENT
FROM Student;

• <目标表达式>不仅可以是算术表达式，还可以是字符串常量、函数等
• 用户可以通过指定别名来改变查询结果的标题
  

选择表中的若干元组

(1)消除取值重复的行

• 两个本来并不完全相同的元组在投影到指定的某些列上后，可能会变成相同的行。可以用DISTINCT消除它们
• 如果没有指定DISTINCT关键词，则默认为ALL，即保留结果表中取值重复的行

(2)查询满足条件的元组

• 查询满足条件的元组可以通过WHERE子句实现
  
  ①比较大小
• 用于进行比较的运算符一般包括=(等于)，>(大于)，<(小于)，>=(大于等于)，<=(小于等于)，!=或<>(不等于)，!>(不大于)，!<(不小于)

例4：查询考试成绩不及格的学生的学号
SELECT DISTINCT Sno
FROM SC
WHERE Grade<60;

②确定范围

• 谓词BETWWEN…AND…和NOT BETWEEN…AND…可以用来查找属性值在(或不在)指定范围内的元组，其中BETWEEN后是范围的下限(即低值)，AND后是范围的上限(即高值)

例5：查询年龄不在20~23岁之间的学生姓名、系别和年龄
SELECT Sname,Sdept,Sage
FROM Student
WHERE Sage NOT BETWEEN 20 AND 23;

③确定集合

• 谓词IN可以用来查找属性值属于指定集合的元组

例6：查询计算机科学系(CS)、数学系(MA)和信息系(IS)学生的姓名和性别
SELECT Sname,Ssex
FROM Student
WHERE Sdept IN(‘CS’,‘MA’,‘IS’);

• 与IN相对的谓词是NOT IN，用于查找属性值不属于指定集合的元组

④字符匹配

• 谓词LIKE可以用来进行字符串的匹配。其一般语法格式如下：[NOT] LIKE ‘<匹配串>’ [ESCAPE ‘<换码字符>’]
  其含义是查找指定的属性列值与<匹配串>相匹配的元组。<匹配串>可以是一个完整的字符串，也可以含有通配符%和_。其中：
• %(百分号)代表任意长度(长度可以为0)的字符串
  例如a%b表示以a开头，以b结尾的任意长度的字符串。如acb、addgb、ab等都满足该匹配串
• _(下划线)代表单个字符
  例如a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb、afb等都满足该匹配串

例7：查询名字中第二个字为"阳"的学生的姓名和学号
SELECT Sname
FROM Student
WHERE Sname LIKE ‘_阳%’;

• 如果LIKE后面的匹配串中不含通配符，则可以用=(等于)运算符取代LIKE谓词，用!=或<>(不等于)运算符取代NOT LIKE谓词
• 数据库字符集为ASCII时，一个汉字需要两个_；当字符集为GBK时，只需要一个_。

• 如果用户要查询的字符串本身就含有通配符%或_，这时就要使用ESCAPE ‘<换码字符>’ 短语对通配符进行转义了
  例8：查询DB_Design课程的课程号和学分
  SELECT Cno,Ccredit
  FROM Course
  WHERE Sname LIKE ‘DB_Design’ ESCAPE ‘’;
• ESCAPE '\'表示"\“为换码字符。这样匹配串中紧跟在”\“后面的字符”_“不再具有通配符的含有，转义为普通的”_"字符

⑤涉及空值的查询

例9：某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号
SELECT Sno,Cno
FROM SC
WHERE Grade IS NULL;

• 注意这里的"IS"不能用等号(=)代替

⑥多重条件查询

• 逻辑运算符AND和OR可用来连接多个查询条件。AND的优先级高于OR，但用户可以用括号改变优先级

• 在例6中的IN谓词实际上是多个OR运算符的缩写，也可以用OR运算符写成如下等价形式：
  SELECT Sname,Ssex
  FROM Student
  WHERE Sdept=‘CS’ OR Sdept=‘MA’ OR Sdept=‘IS’;

ORDER BY子句

• 用户可以用ORDER BY子句对查询结果按照一个或多个属性列的升序(ASC)或降序(DESC)排序，默认值为升序

例10：查询全体学生情况，查询结果按所在系的系号升序排序，同一系中的学生按年龄降序排列
SELECT *
FROM Student
ORDER BY Sdept,Sage DESC;

聚集函数

• 为了进一步方便用户，增强检索功能，SQL提供了许多聚集函数，主要有：

COUNT(*)	统一元组个数
COUNT([DISTINCT|ALL] <列名>)	统计一列中值的个数
SUM([DISTINCT|ALL] <列名>)	计算一列值的总和(此列必须是数值型)
AVG([DISTINCT|ALL] <列名>)	计算一列值的平均值(此列必须是数值型)
MAX([DISTINCT|ALL] <列名>)	求一列值中的最大值
MIN([DISTINCT|ALL] <列名>)	求一列值中的最小值

• 如果指定DISTINCT短语，则表示在计算时要取消列中的重复值。如果不指定DISTINCT短语或指定ALL短语(ALL为默认值)，则表示不取消重复值。

例11：查询学生201215012选修课程的总学分数
SELECT SUM(Ccredit)
FROM SC,Course
WHERE Sno=‘201215012’ AND SC.Cno=Course.Cno;

• 当聚集函数遇到空值时，除COUNT(*)外，都跳过空值而只处理非空值。COUNT(*)是对元组进行计数，某个元组的一个或部分列取空值不影响COUNT的统计结果。
• WHERE子句中是不能用聚集函数作为条件表达式的。聚集函数只能用于SELECT子句和GROUP BY中的HAVING子句。

GROUP BY子句

• GROUP BY子句将查询结果按某一列或多列的值分组，值相等的为一组
• 对查询结果分组的目的是为了细化聚集函数的作用对象。如果未对查询结果分组，聚集函数将作用于整个查询结果。分组后聚集函数将作用于每一个组，即每一个组都有一个函数值

• 如果分组后还要求按一定的条件对这些组进行筛选，最终只输出满足指定条件的组，则可以使用HAVING短语指定筛选条件
  例12：查询选修了三门以上课程的学生学号
  SELECT Sno
  FROM SC
  GROUP BY Sno
  HAVING COUNT(*)>3;
• 这里先用GROUP BY子句按Sno进行分组，再用聚集函数COUNT对每一组计数；HAVING短语给出了选择组的条件，只有满足条件(即元组个数>3，表示此学生选修的课超过3门)的组才会被选出来。
• WHERE子句与HAVING短语的区别在于作用对象不同。WHERE子句作用于基本表或视图，从中选择满足条件的元组。HAVING短语作用于组，从中选择满足条件的组。
• WHERE子句中不能用聚集函数作为条件表达式

连接查询

• 若一个查询同时涉及两个以上的表，则称之为连接查询

等值与非等值连接查询

• 连接查询的WHERE子句中用来连接两个表的条件称为连接条件或连接谓词，其一般格式为：[<表名 1>.] <列名 1><比较运算符> [<表名 2>.] <列名 2>
• 此外连接谓词还可以使用下面形式：[<表名 1>.] <列名 1> BETWEEN [<表名 2>.] <列名 2> AND [<表名 3>.] <列名 3>
• 当连接运算符为=时，称为等值连接。使用其他运算符称为非等值连接。
• 连接谓词中的列名称为连接字段。连接条件中的各连接字段类型必须是可比的，但名字不必相同。

例13：查询每个学生及其选修课程的情况
SELECT Student.*,SC.*
FROM Student,SC
WHERE Student.Sno=SC.sno;

• 本例中，SELECT子句与WHERE子句中的属性名前都加上了表名前缀，这是为了避免混淆。如果属性名在参加连接的各表中是唯一的，则可以省略表名前缀。
• 若在等值连接中把目标列中重复的属性列去掉则为自然连接

自身连接

• 连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接，称为表的自身连接

例14：查询每一门课的间接先修课(即先修课的先修课)

• 在Course表中只有每门课的直接先修课信息，而没有先修课的先修课。要找到这个信息，必须先对一门课找到其先修课，再按此先修课的课程号，查找它的先修课程。这就要将Course表与其自身连接。
• 为此，要为Course表取两个别名，一个是FIRST，另一个是SECOND
  完成该查询的SQL语句为：
  SELECT FIRST.Cno,SECOND.Cpno
  FROM Course FIRST,Course SECOND
  WHERE FRIST.Cpno=SECOND.Cno;

外连接

• 例13的结果表中没有201215123和201215125两个学生的信息，原因在于他们没有选课，在SC表中没有相应的元组，导致Student中这些元组在连接时被舍弃了
• 有时想以Student表为主体列出每个学生的基本情况及其选课情况。若某个学生没有选课，仍把Student的悬浮元组保存在结果关系中，而在SC表的属性上填空值NULL，这时就需要使用外连接。
  SELECT Student.Sno,Sname,Ssex,Sdept,Cno,Grade
  FROM Student LEFT OUTER JOIN SC ON(Student.Sno=SC.Sno);
• 左外连接列出左边关系中所有的元组，右外连接列出右边关系中所有的元组

多表连接

• 连接操作除了可以是两表连接、一个表与其自身连接外，还可以是两个以上的表进行连接，后者通常称为多表连接

例15：查询每个学生的学号、姓名、选修的课程名及成绩
SELECT Student.Sno,Sname,Cname,Grade
FROM Student,SC,Course
WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno;

• 关系数据库管理系统在执行多表连接时，通常是先进行两个表的连接操作，再将其连接结果与第三个表进行连接

嵌套查询

• 在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询(nested query)
• 上层的查询块称为外层查询或父查询，下层查询块称为内层查询或子查询
• 需要特别指出的是，子查询的SELCT语句中不能使用ORDER BY子句，ORDER BY子句只能对最终查询结果排序

带有IN谓词的子查询

• 在嵌套查询中，子查询的结果往往是一个集合，所以谓词IN是嵌套查询中最经常使用的谓词
  

带有比较运算符的子查询

• 带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接。当用户能确切知道内层查询返回的是单个值时，可以用>、<、=、>=、<=、!=或<>等比较运算符。
  
  

带有ANY(SOME)或ALL谓词的子查询

• 子查询返回单值时可以用比较运算符，但返回多值时要用ANY(有的系统用SOME)或ALL谓词修饰符。而使用ANY或ALL谓词时必须同时使用比较运算符。其语义如下：

• >ANY 大于子查询结果中的某个值
• >ALL 小于子查询结果中的所有值
• <ANY 小于子查询结果中的某个值
• <ALL 小于子查询结果中的所有值
• >=ANY 大于等于子查询结果中的某个值
• >=ALL 小于等于子查询结果中的所有值
• <=ANY 小于等于子查询结果中的某个值
• <=ALL 小于等于子查询结果中的所有值
• =ANY 等于子查询结果中的某个值
• =ALL 等于子查询结果中的所有值(通常没有实际意义)
• !=(或<>) ANY 不等于子查询结果中的某个值
• !=(或<>) ALL 不等于子查询结果中的任何一个值

• 事实上，用聚集函数实现子查询通常比直接用ANY或ALL查询效率要高。ANY，ALL与聚集函数的对应关系如表：

	＝	<>或!=	＜	<=	＞	>=
ANY	IN	- -	<MAX	<=MAX	>MIN	>=MIN
ALL	- -	NOT IN	<MIN	<=MIN	>MAX	>=MAX

带有EXISTS谓词的子查询

• EXISTS代表存在量词∃。常有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值"true"或逻辑假值"false"
• 可以利用EXISTS来判断x∈S、S⊆R、S=R、S∩R非空等是否成立
  

还可以基于派生表的查询：
SELECT Sname
FROM Student,(SELECT Sno FROM SC WHERE Cno=‘1’) AS SC1
WHERE Student.Sno=SC1.Sno;

• 使用存在量词EXISTS后，若内层查询结果非空，则外层的WHERE子句返回真值，否则返回假值
• 与EXISTS谓词相对应的是NOT EXIST谓词。使用存在量词NOT EXISTS后，若内层查询结果为空，则外层的WHERE子句返回真值，否则返回假值