关系数据库SQL数据查询(超全的查询案例）
数据查询
一、单表查询
1.查询仅涉及一个表，选择表中的若干列
用到的数据——>点这里下载数据
查询指定列
[例1] 查询全体学生的学号与姓名。
SELECT Sno,Sname
FROM Student;
[例2] 查询全体学生的姓名、学号、所在系。
SELECT Sname,Sno,Sdept
FROM Student;
查询全部列
- 选出所有属性列：
- 在SELECT关键字后面列出所有列名
- 将<目标列表达式>指定为 *
[例3] 查询全体学生的详细记录
SELECT Sno,Sname,Ssex,Sage,Sdept
FROM Student;
或
SELECT *
FROM Student;
查询经过计算的值
-
SELECT子句的<目标列表达式>不仅可以为表中的属性列，也可以是表达式
[例4] 查全体学生的姓名及其出生年份。 SELECT Sname,2014-Sage /*假设当时为2014年*/ FROM Student; 输出结果： Sname 2014-Sage 李勇 1994 刘晨 1995 王敏 1996 张立 1995
使用列别名改变查询结果的列标题:
SELECT Sname NAME,'Year of Birth:' BIRTH,
2014-Sage BIRTHDAY,LOWER(Sdept) DEPARTMENT
FROM Student;
输出结果：
NAME BIRTH BIRTHDAY DEPARTMENT
李勇 Year of Birth: 1994 cs
刘晨 Year of Birth: 1995 cs
王敏 Year of Birth: 1996 ma
张立 Year of Birth: 1995 is
2.选择表中的若干元组
消除取值重复的行
如果没有指定DISTINCT关键词，则缺省为ALL
[例5] 查询选修了课程的学生学号。
SELECT Sno FROM SC;
等价于：
SELECT ALL Sno FROM SC;
执行上面的SELECT语句后，结果为：
Sno
201215121
201215121
201215121
201215122
201215122
指定DISTINCT关键词，去掉表中重复的行
SELECT DISTINCT Sno
FROM SC;
执行结果：
Sno
201215121
201215122
常用的查询条件：
1. 比较大小：
[例6] 查询计算机科学系全体学生的名单。
SELECT Sname
FROM Student
WHERE Sdept=‘CS’;
[例7]查询所有年龄在20岁以下的学生姓名及其年龄。
SELECT Sname,Sage
FROM Student
WHERE Sage < 20;
[例8]查询考试成绩有不及格的学生的学号。
SELECT DISTINCT Sn
FROM SC
WHERE Grade<60;
2. 确定范围：
谓词: BETWEEN … AND …
NOT BETWEEN … AND …
[例9] 查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄
SELECT Sname, Sdept, Sage
FROM Student
WHERE Sage BETWEEN 20 AND 23;
[例10] 查询年龄不在20~23岁之间的学生姓名、系别和年龄
SELECT Sname, Sdept, Sage
FROM Student
WHERE Sage NOT BETWEEN 20 AND 23;
3. 确定集合：
谓词：IN <值表>, NOT IN <值表>
[例11]查询计算机科学系（CS）、数学系（MA）和信息系（IS）学生的姓名和性别。
SELECT Sname, Ssex
FROM Student
WHERE Sdept IN ('CS','MA’,'IS' );
[例12]查询既不是计算机科学系、数学系，也不是信息系的学生的姓名和性别。
SELECT Sname, Ssex
FROM Student
WHERE Sdept NOT IN ('IS','MA’,'CS' );
4. 字符匹配：
-
谓词： [NOT] LIKE ‘<匹配串>’ [ESCAPE ‘ <换码字符>’]
-
<匹配串>可以是一个完整的字符串，也可以含有通配符%和 _
-
% （百分号） 代表任意长度（长度可以为0）的字符串
- 例如a%b表示以a开头，以b结尾的任意长度的字符串
-
_ （下横线） 代表任意单个字符。
- 例如a_b表示以a开头，以b结尾的长度为3的任意字符串
匹配串为固定字符串 [例13] 查询学号为201215121的学生的详细情况。 SELECT * FROM Student WHERE Sno LIKE ‘201215121'; 等价于： SELECT * FROM Student WHERE Sno = ' 201215121 '; 匹配串为含通配符的字符串 [例14] 查询所有姓刘学生的姓名、学号和性别。 SELECT Sname, Sno, Ssex FROM Student WHERE Sname LIKE '刘%'; [例15] 查询姓"欧阳"且全名为三个汉字的学生的姓名。 SELECT Sname FROM Student WHERE Sname LIKE '欧阳__';
使用换码字符将通配符转义为普通字符
[例16] 查询DB_Design课程的课程号和学分。
SELECT Cno，Ccredit
FROM Course
WHERE Cname LIKE 'DB\_Design' ESCAPE '\ ' ;
[例17] 查询以"DB_"开头，且倒数第3个字符为 i的课程的详细情况。
SELECT *
FROM Course
WHERE Cname LIKE 'DB\_%i_ _' ESCAPE '\ ' ;
ESCAPE '＼' 表示“ ＼” 为换码字符
5. 涉及空值的查询：
谓词： IS NULL 或 IS NOT NULL
-
“IS” 不能用 “=” 代替
[例18] 某些学生选修课程后没有参加考试，所以有选课记录，但没 有考试成绩。查询缺少成绩的学生的学号和相应的课程号。 SELECT Sno，Cno FROM SC WHERE Grade IS NULL [例19] 查所有有成绩的学生学号和课程号。 SELECT Sno，Cno FROM SC WHERE Grade IS NOT NULL;
6.多重条件查询
- 逻辑运算符：AND和 OR来连接多个查询条件
-
AND的优先级高于OR
-
可以用括号改变优先级
[例20] 查询计算机系年龄在20岁以下的学生姓名。
SELECT Sname
FROM Student
WHERE Sdept= ‘CS’ AND Sage<20;
-
3.ORDER BY子句
-
ORDER BY子句
- 可以按一个或多个属性列排序
- 升序：ASC;降序：DESC;缺省值为升序
-
对于空值，排序时显示的次序由具体系统实现来决定
[例21]查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。 SELECT Sno, Grade FROM SC WHERE Cno= ' 3 ' ORDER BY Grade DESC; [例22]查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。 SELECT * FROM Student ORDER BY Sdept, Sage DESC;
4. 聚集函数
-
统计元组个数
- COUNT(*)
-
统计一列中值的个数
- COUNT([DISTINCT|ALL] <列名>)
-
计算一列值的总和（此列必须为数值型）
- SUM([DISTINCT|ALL] <列名>)
-
计算一列值的平均值（此列必须为数值型）
- AVG([DISTINCT|ALL] <列名>)
-
求一列中的最大值和最小值
- MAX([DISTINCT|ALL] <列名>)
- MIN([DISTINCT|ALL] <列名>)
[例23] 查询学生总人数。 SELECT COUNT(*) FROM Student; [例24] 查询选修了课程的学生人数。 SELECT COUNT(DISTINCT Sno) FROM SC; [例25] 计算1号课程的学生平均成绩。 SELECT AVG(Grade) FROM SC WHERE Cno= ' 1 '; [例26] 查询选修1号课程的学生最高分数。 SELECT MAX(Grade) FROM SC WHERE Cno='1'; [例27 ] 查询学生201215012选修课程的总学分数。 SELECT SUM(Ccredit) FROM SC,Course WHERE Sno='201215012' AND SC.Cno=Course.Cno;
5.GROUP BY子句
GROUP BY子句分组：
细化聚集函数的作用对象
-
如果未对查询结果分组，聚集函数将作用于整个查询结果
-
对查询结果分组后，聚集函数将分别作用于每个组
-
按指定的一列或多列值分组，值相等的为一组
[例28] 求各个课程号及相应的选课人数。 SELECT Cno，COUNT(Sno) FROM SC GROUP BY Cno; 查询结果可能为： Cno COUNT(Sno) 1 22 2 34 3 44 4 33 5 48
HAVING短语与WHERE子句的区别：
- 作用对象不同
- WHERE子句作用于基表或视图，从中选择满足条件的元组
- HAVING短语作用于组，从中选择满足条件的组。
二、连接查询
-
连接查询：同时涉及两个以上的表的查询
-
连接条件或连接谓词：用来连接两个表的条件
一般格式：- [<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>
- [<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2> AND [<表名2>.]<列名3>
-
连接字段：连接谓词中的列名称
- 连接条件中的各连接字段类型必须是可比的，但名字不必相同
1.等值与非等值连接查询
等值连接：连接运算符为=
[例 29] 查询每个学生及其选修课程的情况
SELECT Student.*, SC.*
FROM Student, SC
WHERE Student.Sno = SC.Sno;
查询结果：
（1）嵌套循环法（NESTED-LOOP）
- 首先在表1中找到第一个元组，然后从头开始扫描表2，逐一查找满足连接件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。
- 表2全部查找完后，再找表1中第二个元组，然后再从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第二个元组与该元组拼接起来，形成结果表中一个元组。
- 重复上述操作，直到表1中的全部元组都处理完毕
（2）排序合并法（SORT-MERGE）
- 常用于=连接
- 首先按连接属性对表1和表2排序
- 对表1的第一个元组，从头开始扫描表2，顺序查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。当遇到表2中第一条大于表1连接字段值的元组时，对表2的查询不再继续
- 找到表1的第二条元组，然后从刚才的中断点处继续顺序扫描表2，查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。直接遇到表2中大于表1连接字段值的元组时，对表2的查询不再继续
- 重复上述操作，直到表1或表2中的全部元组都处理完毕为止
（3）索引连接（INDEX-JOIN）
- 对表2按连接字段建立索引
- 对表1中的每个元组，依次根据其连接字段值查询表2的索引，从中找到满足条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组
自然连接
[例 30] 对[例 3.49]用自然连接完成。
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student,SC
WHERE Student.Sno = SC.Sno;
-
一条SQL语句可以同时完成选择和连接查询，这时WHERE子句是由连接谓词和选择谓词组成的复合条件。
[例 31]查询选修2号课程且成绩在90分以上的所有学生的学号和姓名。 SELECT Student.Sno, Sname FROM Student, SC WHERE Student.Sno=SC.Sno AND SC.Cno=' 2 ' AND SC.Grade>90; -
执行过程:
- 先从SC中挑选出Cno='2’并且Grade>90的元组形成一个中间关系
- 再和Student中满足连接条件的元组进行连接得到最终的结果关系
2.自身连接
-
自身连接：一个表与其自己进行连接
-
需要给表起别名以示区别
-
由于所有属性名都是同名属性，因此必须使用别名前缀
[例 32]查询每一门课的间接先修课（即先修课的先修课） SELECT FIRST.Cno, SECOND.Cpno FROM Course FIRST, Course SECOND WHERE FIRST.Cpno = SECOND.Cno;
FIRST表（Course表）
SECOND表（Course表)
查询结果：
3.外连接
- 外连接与普通连接的区别
- 普通连接操作只输出满足连接条件的元组
- 外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出
- 左外连接
- 列出左边关系中所有的元组
- 右外连接
- 列出右边关系中所有的元组
[例 33] 改写[例 29]
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student LEFT OUT JOIN SC ON
(Student.Sno=SC.Sno);
执行结果：
4.多表连接
多表连接：两个以上的表进行连接
[例34]查询每个学生的学号、姓名、选修的课程名及成绩
SELECT Student.Sno, Sname, Cname, Grade
FROM Student, SC, Course /*多表连接*/
WHERE Student.Sno = SC.Sno
AND SC.Cno = Course.Cno;
三、嵌套查询
嵌套查询概述
-
一个SELECT-FROM-WHERE语句称为一个查询块
-
将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询
SELECT Sname /*外层查询/父查询*/ FROM Student WHERE Sno IN ( SELECT Sno /*内层查询/子查询*/ FROM SC WHERE Cno= ' 2 '); -
上层的查询块称为外层查询或父查询
-
下层查询块称为内层查询或子查询
-
SQL语言允许多层嵌套查询
- 即一个子查询中还可以嵌套其他子查询
-
子查询的限制
- 不能使用ORDER BY子句
不相关子查询：
子查询的查询条件不依赖于父查询
- 由里向外 逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。
相关子查询：子查询的查询条件依赖于父查询
- 首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表
- 然后再取外层表的下一个元组
- 重复这一过程，直至外层表全部检查完为止
1.带有IN谓词的子查询
[例 35] 查询与“刘晨”在同一个系学习的学生。
此查询要求可以分步来完成
① 确定“刘晨”所在系名
SELECT Sdept
FROM Student
WHERE Sname= ' 刘晨 ';
结果为： CS
② 查找所有在CS系学习的学生。
SELECT Sno, Sname, Sdept
FROM Student
WHERE Sdept= ' CS ';
结果为：
2.带有比较运算符的子查询
当能确切知道内层查询返回单值时，可用比较运算符（>，<，=，>=，<=，!=或< >）。
在[例 36]中，由于一个学生只可能在一个系学习，则可以用 = 代替IN ：
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept =
(SELECT Sdept
FROM Student
WHERE Sname= '刘晨');
[例 37 ]找出每个学生超过他选修课程平均成绩的课程号。
SELECT Sno, Cno
FROM SC x
WHERE Grade >=(SELECT AVG（Grade）
FROM SC y
WHERE y.Sno=x.Sno);
3.带有ANY（SOME）或ALL谓词的子查询
使用ANY或ALL谓词时必须同时使用比较运算
语义为：
-
ANY 大于子查询结果中的某个值
-
ALL 大于子查询结果中的所有值
-
< ANY 小于子查询结果中的某个值
-
< ALL 小于子查询结果中的所有值
-
= ANY 大于等于子查询结果中的某个值
-
= ALL 大于等于子查询结果中的所有值
-
<= ANY 小于等于子查询结果中的某个值
-
<= ALL 小于等于子查询结果中的所有值
-
= ANY 等于子查询结果中的某个值
-
=ALL 等于子查询结果中的所有值（通常没有实际意义）
-
!=（或<>）ANY 不等于子查询结果中的某个值
-
!=（或<>）ALL 不等于子查询结果中的任何一个值
[例 38] 查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄 SELECT Sname,Sage FROM Student WHERE Sage < ANY (SELECT Sage FROM Student WHERE Sdept= ' CS ') AND Sdept <> ‘CS ' ; /*父查询块中的条件 */
结果：
执行过程：
（1）首先处理子查询，找出CS系中所有学生的年龄，构成一个集合（20,19）
（2）处理父查询，找所有不是CS系且年龄小于 20 或 19的学生
ANY（或SOME），ALL谓词与聚集函数、IN谓词的等价转换关系
4.带有EXISTS谓词的子查询
EXISTS谓词
- 存在量词 E
- 带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。
- 若内层查询结果非空，则外层的WHERE子句返回真值
- 若内层查询结果为空，则外层的WHERE子句返回假值
- 由EXISTS引出的子查询，其目标列表达式通常都用 * ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义。
NOT EXISTS谓词
-
若内层查询结果非空，则外层的WHERE子句返回假值
-
若内层查询结果为空，则外层的WHERE子句返回真值
[例 39] 查询没有选修1号课程的学生姓名。 SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno = Student.Sno AND Cno='1'); -
不同形式的查询间的替换
- 一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换
- 所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换
-
用EXISTS/NOT EXISTS实现全称量词（难点）
- SQL语言中没有全称量词 （For all）
- 可以把带有全称量词的谓词转换为等价的带有存在量词的谓词：
用EXISTS/NOT EXISTS实现逻辑蕴涵
- SQL语言中没有蕴涵（Implication）逻辑运算
- 可以利用谓词演算将逻辑蕴涵谓词等价转换为：
[例40]查询至少选修了学生201215122选修的全部课程的学生号码。
解题思路：
-
用逻辑蕴涵表达：查询学号为x的学生，对所有的课程y，只要201215122学生选修了课程y，则x也选修了y。
-
形式化表示：
用P表示谓词 “学生201215122选修了课程y”
用q表示谓词 “学生x选修了课程y”
则上述查询为: （y） p q -
等价变换：
-
变换后语义：不存在这样的课程y，学生201215122选修了y，而学生x没有选。
用NOT EXISTS谓词表示：
SELECT DISTINCT Sno
FROM SC SCX
WHERE NOT EXISTS
(SELECT *
FROM SC SCY
WHERE SCY.Sno = ' 201215122 ' AND
NOT EXISTS
(SELECT *
FROM SC SCZ
WHERE SCZ.Sno=SCX.Sno AND
SCZ.Cno=SCY.Cno));
四、集合查询
-
集合操作的种类
- 并操作UNION
- 交操作INTERSECT
- 差操作EXCEPT
-
参加集合操作的各查询结果的列数必须相同;对应项的数据类型也必须相同
[例41] 查询计算机科学系的学生及年龄不大于19岁的学生。 SELECT * FROM Student WHERE Sdept= 'CS' UNION SELECT * FROM Student WHERE Sage<=19; -
UNION：将多个查询结果合并起来时，系统自动去掉重复元组
-
UNION ALL：将多个查询结果合并起来时，保留重复元组
[例 42] 查询计算机科学系的学生与年龄不大于19岁的学生的差集。 SELECT * FROM Student WHERE Sdept='CS' EXCEPT SELECT * FROM Student WHERE Sage <=19;
五、基于派生表的查询
子查询不仅可以出现在WHERE子句中，还可以出现在FROM子句中，这时子查询生成的临时派生表（Derived Table）成为主查询的查询对象
[例43]找出每个学生超过他自己选修课程平均成绩的课程号
SELECT Sno, Cno
FROM SC, (SELECTSno, Avg(Grade)
FROM SC
GROUP BY Sno)
AS Avg_sc(avg_sno,avg_grade)
WHERE SC.Sno = Avg_sc.avg_sno
and SC.Grade >=Avg_sc.avg_grade
如果子查询中没有聚集函数，派生表可以不指定属性列，子查询SELECT子句后面的列名为其缺省属性。
[例44]查询所有选修了1号课程的学生姓名，可以用如下查询完成：
SELECT Sname
FROM Student,
(SELECT Sno FROM SC WHERE Cno=' 1 ') AS SC1
WHERE Student.Sno=SC1.Sno;
六、SELECT语句的一般形式
1. 目标列表达式的可选格式
-
SELECT [ALL|DISTINCT]
<目标列表达式> [别名] [ ,<目标列表达式> [别名]] …
-
FROM <表名或视图名> [别名]
[ ,<表名或视图名> [别名]] …
|(<SELECT语句>)[AS]<别名> -
[WHERE <条件表达式>]
-
[GROUP BY <列名1>[HAVING<条件表达式>]]
-
[ORDER BY <列名2> [ASC|DESC]];
目标列表达式格式
（1） *
（2） <表名>.*
（3） COUNT([DISTINCT|ALL]* )
（4） [<表名>.]<属性列名表达式>[,<表名>.]<属性列名表达式>]…
其中<属性列名表达式>可以是由属性列、作用于属性列
的聚集函数和常量的任意算术运算（+，-，*，/）组成的
运算公式
2. 聚集函数的一般格式
上一篇： Prometheus监控系部署配置过程
推荐阅读
-
关系数据库SQL数据查询(超全的查询案例）
-
记录一个sql优化后大大提高查询效率的情形 博客分类： 数据库 sql优化索引concat和substr拆分
-
Java的MyBatis框架中对数据库进行动态SQL查询的教程
-
Java的MyBatis框架中对数据库进行动态SQL查询的教程
-
数据库查询-编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary）
-
关系型数据库基础及应用（三）——SQL查询（以Oracle为主）
-
SQL-(8)数据库的创建、查询、修改和删除
-
MySQL中查询所有数据库占用磁盘空间大小和单个库中所有表的大小的sql语句
-
MySQL中查询所有数据库占用磁盘空间大小和单个库中所有表的大小的sql语句
-
mysql查询所有数据库和每个表磁盘所占空间大小容量的方法sql