阅读量:3
目录
1.4.2在学生表中找出信电学院2000年后出生的学生的记录
1.4.3查询出生年份在“1996-1998”年出生的学生的姓名、性别、学院、出生年份
1.4.4查询信电学院、理学院、计算机学院的学生的学号、姓名、学院
1.5.3查找课程名是“DB_Design”课程的课程号、课程性质
1.6.1查询学号在091501~091506至少选修了三门课程的学生的学号和选修课程的课程数
1.8.1查询选修了180102号课程的学生的学号和成绩，查询结果按照成绩从高到低排序
1.8.2查询全体学生情况，查询结果按所在学院的名称升序排列，对同一学院中的学生按年龄降序排列
2.4.1查询选修180101号课程且成绩在90分以上的学生学号，姓名及成绩
2.6.1查询选修了180101号或180102号课程或二者都选修了的学生学号、课程号和成绩
3.2.1查询其它学院中比计算机学院某个学生年龄小的学生名单
4.1查询至少选修了091501号课程选修的全部课程的学生学号
零.前言
数据库讲解（MySQL版）（超详细）【第一章】-CSDN博客
数据库讲解（MySQL版）（超详细）【第二章】【上】-CSDN博客
数据库讲解---(SQL语句--表的使用)【MySQL版本】-CSDN博客
一.单表查询
1.1SELECT语句
SELECT语句用于查询数据库中数据：
语法：
SELECT [ALL | DISTINCT] <属性列表> FROM <表1或视图1>,<表2或视图2>,<表3或视图3>,.... [WHERE <>条件表达式] [GROUP BY <列名>] [HAVING <条件表达式>] [ORDER BY <列名> [ASC | DESC]];SELECT完成“投影运算”、WHERE完成“选择运算”
至于“WHERE”、“GROUP BY”、”HAVING“、“ORDER BY”，我们之后再说
“DISTINCT”和“ALL”用来表示“是否对相同列去重”
- “DISTINCT”：“对具有相同属性值的记录去重”
- “ALL”：“保留相同属性值记录”
1.2例子说明
在接下来的例子中，我们统一使用下面的三张“表”作为数据来源：
学生表：
课程表：
学习表：
1.3SELECT使用
SQL查询可以归纳为下面五种操作：
- 选择表中的若干列
- 选择表中的若干元组(记录)
- 对查询进行分组
- 使用集函数
- 对查询结果排序
1.3.1在学生表中找出所有学生的籍贯
SELECT DISTINCT 籍贯 FROM 学生;1.3.2查询学生表中的所有信息
两种方法：
一种是手动列出所有属性
SELECT 学号,姓名,性别,籍贯,出生年份,院系 FROM 学生; 另一种使用：“*”代指所有属性
SELECT * FROM 学生; 1.3.3给列定义别名返回
如果一个属性名字不是我们想要的，我们可以设置这个属性名字并返回，方法：
COLUMN AS <别名> 或者 COLUMN <别名>
例如：
SELECT 学号 year(now()) - 出生年份 AS 年龄 FROM 学生;结果：
在这里，我们将：“year(now()) - 出生年份”的名字修改为：“年龄”
1.4WHERE的使用
查询满足指定条件的元组可以通过WHERE实现，可以这么理解，WHERE的作用是“编程语言中的if”
可用连接词：
1.4.1查询所有不及格课程的学生的学号、课程号及成绩
SELECT 学号,课程号,成绩 FROM 学习 WHERE 成绩 < 60;
1.4.2在学生表中找出信电学院2000年后出生的学生的记录
SELECT * FROM 学生 WHERE 学院 = '信电' AND 出生年份 >= 2000;1.4.3查询出生年份在“1996-1998”年出生的学生的姓名、性别、学院、出生年份
SELECT 姓名,性别,学院,出生年份 FROM 学生 WHERE 出生年份 BETWEEN 1996 AND 1998;这条查询语句等价于：
SELECT 姓名,性别,学院,出生年份 FROM 学生 WHERE 出生年份 >= 1996 AND 出生年份 <= 1998;1.4.4查询信电学院、理学院、计算机学院的学生的学号、姓名、学院
SELECT 学号,姓名,学院 FROM 学生 WHERE 学院 IN ('信电','理学院','计算机');这条查询语句等价于：
SELECT 学号,姓名,学院 FROM 学生 WHERE 学院 = '信电' OR 学院 = '理学院' OR 学院 = '计算机';
可以看到“IN”在某个“()”中起到选择的作用，可以是“()”中的任意一个，即相当于“OR”(或)，因为我们查询的是三种学院，所以只要是三种学院中的一种即可。
1.4.4查询学院不是“信电学院”的学生的学号、姓名、学院
在某个学院，我们可以使用“IN”，那么不在某个学院，我们可以使用“NOT IN”，表示“除了..”
SELECT * FROM 学生 WHERE 学院 NOT IN ('信电');1.5字符匹配(模糊匹配)
字符匹配可以帮助我们从字符串中筛选“符合要求”的“子串”
语法：
[NOT] LIKE '<匹配串>' [ESCAPE '<转码字符>']“匹配串”：可以是一个完整的字符串，也可以含有通配符“%”和“_”
- “%”：表示任意长度(可以为0)的字符串
- “_”：表示任意单个字符
1.5.1查询所有姓王的学生的姓名、学号、性别
SELECT 姓名,学号,是性别 FROM 学生 WHERE 姓名 LIKE '王%';
1.5.2查询名字中，第二个字为“小”字的学生的姓名和学号
SELECT 姓名,学号 FROM 学生 WHERE 姓名 LIKE '_王%';因为是“第二个字”，所以前面一定还有一个字，为此我们需要使用一个“_”来代指第一个字，那么名字可能有第三个字、第四个字...因此我们使用“%”来表示后面的第“3”、“4”、“5”...个字
1.5.3查找课程名是“DB_Design”课程的课程号、课程性质
SELECT 课程号,课程性质 FROM 课程 WHERE 课程名 LIKE 'DB\_Design' ESCAPE '\';在这里，因为“_”是特殊字符，所以需要使用“\”(转义字符)特别标出
1.5.4空值
如果某条元组的某个属性，是一个“空值”即“NULL”，那么就需要用“IS”来指出，其实使用”=“也是可以的，不过不建议使用
SELECT 学号,课程号,成绩 FROM 学习 WHERE 成绩 IS NULL;1.6GROUP BY
SELECT 课程号,COUNT(学号) AS 选课人数 FROM 学习 GROUP BY 课程号;“COUNT()”函数用来计算每个组的人数，“COUNT()”函数只能和GROUP BY搭配使用，而“GROUP BY 课程号”，将相同的课程号分为一组
1.6.1查询学号在091501~091506至少选修了三门课程的学生的学号和选修课程的课程数
SELECT FROM 学号,COUNT(课程号) AS 选课数 FROM 学习 WHERE 学号 BETWEEN '091501' AND '091506' GROUP BY 学号 HAVING COUNT(课程号) >= 3;我们来一步一步分析：
- 我们需要返回：“学号”、“课程数”，而课程数就是“课程号”的数量
- 学号在091501~091506之间是一个范围，因此我们需要使用WHERE 筛选出符合条件的学号
- 因为一个学生可能会修多门学科，所以一个学生就是一个组，每组中的元素是“课程”
- HAVING用来对组筛选，只有组内课程(元素)大于等于3的组才会被展示出来
怎么样，这么一分析，是不是很简单了？
1.7集函数
SQL有许多集函数：
- COUNT([DISTINCT|ALL] *) ：统计元组个数
- COUNT([DISTINCT|ALL] <列名>)：统计一列中值的个数
- SUM([DISTINCT|ALL] <列名>)：计算一列值的总和
- AVG([DISTINCT|ALL] <列名>)：计算一列值的平均值(此列必须是数值型)
- MAX([DISTINCT|ALL] <列名>)：求一列值中最大值
- MIN([DISTINCT|ALL] <列名>)：求一列值中最小值
“DISTINCT”和“ALL”还是老作用，用来指定是否对重复值去重
1.7.1查询学生总人数
SELECT COUNT(*) AS 总人数 FROM 学生;
COUNT(*)表示对所有元组统计，值得注意的是，使用COUNT之后，返回的元组只有一个(30)，这是因为COUNT(*)是对所有属性筛选并对所有属性别名为：“总人数”，并且计算后的结果只有一个“30”
1.7.2查询计算机学院学生的平均年龄
SELECT AVG(year(now())-出生年龄) AS 平均年龄 FROM 学生 WHERE 学院 = '计算机';
1.7.3查询学习180101号课程的学生的最高分数
SELECT MAX(成绩) AS 最高分 FROM 学习 WHERE 课程号 = '180101';
1.8对查询结果排序
如果没有指定查询结果的显示顺序，SQL默认将其按照最方便的顺序(即先后顺序)输出结果
如果需要对结果排序，我们可以使用：“ORDER BY ASC(升序) | DESC(降序)”进行排序
注意：“ORDER BY只对最终查询结果排序，不能对中间结果排序！！！”
1.8.1查询选修了180102号课程的学生的学号和成绩，查询结果按照成绩从高到低排序
SELECT 学号,成绩 FROM 学习 WHERE 课程号 = '180102' ORDER BY 成绩 DESC;
1.8.2查询全体学生情况，查询结果按所在学院的名称升序排列，对同一学院中的学生按年龄降序排列
SELECT * FROM 学生 ORDER BY 学院 ASC,year(now()) - 出生年份 DESC;
二.多表查询(连接查询)
2.1等值与非等值连接查询
用来连接两个表的条件称为“连接条件”或“连接谓词”，一般格式为：
[<表1>] <列1> <比较运算符> [<表2>] <列2>
比较运算符主要有：
“=”、“>”、“<”、“>=”、“<=”、“!=”
除此之外，连接谓词还可以使用以下形式：
[<表1>] <列1> BETWEEN [<表2>] <列2> AND [<表2>] <列3>
当连接谓词为“=”时，称为等值连接，使用其他运算符时为非等值连接。
2.1.1查询每个学生及其选修课程的情况
SELECT 学生.*,学习.* FROM 学生,学习 WHERE 学生.学号 = 学习.学号;
2.1.2使用自然连接“学生”表和“学习”表
自然连接是等值连接运算中的一种特殊情况，即按照两个表中的相同属性进行等值连接，且目标列中去掉了重复的属性列，但保留了所有不重复的属性列
使用自然连接，相同的属性会去重一列
SELECT 学生.学号,姓名,性别,出生年份,学院,课程号,成绩 FROM 学生,学习 WHERE 学生.学号 = 学习.学号;
2.2自身连接查询
自身连接即将一张表与自身连接在一起
2.2.1求每一门课程的间接选修课(先修课的先修课)
分析：“题目要求先修课的先修课，而表中只有先修课的信息，因此我们需要先找到一门课再按此先修课的课程号，查找它的先修课程，这相当于将表自身连接”
SELECT FIRST.课程号 AS 课程号,FIRST.课程名 AS 课程名,SECOND.先修课程号 AS 间接先修课程号 FROM 课程 AS FIRST,课程 AS SECOND WHERE FIRST.先修课程号 = SECOND.课程号;
2.3外连接查询
外连接分为：“左外连接”和“右外连接”
左外连接
规定所有记录都应该从连接语句左侧的表中返回。
当右侧表中没有匹配的记录时，左标中的记录依然会返回，而对应的右侧表中的列值会自动填充NULL值
2.3.2查询所有学生的姓名以及他们选修课程的课程号和成绩
SELECT 姓名,课程号,成绩 FROM 学生 LEFT OUTER JOIN 学习 ON 学生.学号 = 学习.学号;
右外连接
规定所有记录都应该从连接语句右侧的表中返回，当左侧表中没有匹配的记录时，右侧表中的值依然返回，而对应的左侧表中的值将自动填充NULL值
2.3.3查询所有的课程信息及选修该课程的学生的学号及成绩
SELECT 课程名,学号,成绩 FROM 学习 RIGHT OUTER JOIN 课程 ON 学习.课程号 = 课程.课程号;
2.4复合条件连接查询
WHERE字句中有多个条件的连接操作，称为复合条件连接
2.4.1查询选修180101号课程且成绩在90分以上的学生学号，姓名及成绩
SELECT 学生.学号,姓名,成绩 FROM 学生,学习 WHERE 学生.学号 = 学习.学号 AND 学习.课程号 = '180101' AND 学习.成绩 > 90;
2.5多表连接
有时我们可能需要两张以上的表来查询，这时叫作“多表连接”
SELECT 学生.学号,姓名,课程名,学习.成绩 FROM 学生,学习,课程 WHERE 学生.学号 = 学习.学号 AND 学习.课程号 = 课程.课程号;
2.6集合运算连接查询
当我们希望使用SQL语句完成：“集合运算(并、交、差)”来查询时，我们可以使用“UNION”、“INTERSECT”、“EXCEPT”
而MySQL只支持并运算，所以我们在这里只介绍“并运算”：
2.6.1查询选修了180101号或180102号课程或二者都选修了的学生学号、课程号和成绩
( SELECT 学号,课程号,成绩 FROM 学习 WHERE 课程号 = '180101' ) UNION ( SELECT 学号,课程号,成绩 FROM 学习 WHERE 课程号 = '180102' )注意：UNION运算自动去除重复
如果想保留重复，可以使用UNION ALL来代替UNION
UNION是在查询完结果之后，对两次结果进行并集
而OR是在中间结果中进行并集！！
三.嵌套查询
3.1简介
在SQL中，一个SELECT...FROM...WHERE语句被称为一个查询块，将一个查询块嵌套在另一个查询块的WHERE字句或HAVING条件中的查询称为嵌套查询：
3.1.1查询选修了180101号课程的学生姓名
SELECT 姓名 FROM 学生 WHERE 学号 IN( SELECT 学号 FROM 学习 WHERE 课程号 = '180101' );在上面这个例子中：“SELECT 学号 FROM 学习 WHERE 课程号 = '180101' ”叫作“下层查询块”
“SELECT 姓名 FROM 学生 WHERE 学号 IN”叫作“上层查询块”
“上层查询块”又叫做：“父查询”、“主查询”
“下层查询块”又叫做：“内层查询”、“子查询”
注意：“子查询的SELECT语句中不能使用ORDER BY语句，ORDER BY只能对最终结果排序”
3.2带有ANY或ALL谓词的子查询
子查询返回单值时可以使用比较运算符。但当返回的结果有可能不止一个时，则不能够使用比较运算符，此时可以使用“ALL”或者“ANY”加比较运算符实现比较操作：
3.2.1查询其它学院中比计算机学院某个学生年龄小的学生名单
SELECT 姓名 FROM 学生 WHERE year(now()) - 出生年份 < ANY ( SELECT year(now()) - 出生年份 FROM 学生 WHERE 学院 = '计算机' ) AND 学院 != '计算机' ORDER BY year(now()) - 出生年份 DESC;同时，我们也可以使用：“集函数”来代替连接谓词，比如在这里“比计算机学院中某个学生的年龄小”可以转换为“比计算机学院中年龄最大的学生小，那么该学生一定满足条件了”
SELECT 姓名,year(now()) - 出生年份 FROM 学生 WHERE year(now()) - 出生年份 < ( SELECT MAX(year(now()) - 出生年份) FROM 学生 WHERE 学院 = '计算机' ) AND 学院 != '计算机' ORDER BY year(now()) - 出生年份 DESC;“ANY”、“ALL”与“集函数”的关系如下表所示：
3.3带有EXISTS谓词的子查询
EXISTS代表存在量词，带有EXISTS谓词的子查询不返回任何数据，只产生逻辑值“True”或者“False”
3.3.1查询选修了180102号课程的学生学号和姓名
SELECT 学号,姓名 FROM 学生 WHERE EXISTS ( SELECT * FROM 学习 WHERE 学生.学号 = 学习.学号 AND 学习.课程号 = '180102' );
3.3.2查询没有选修180102号课程的学生学号和姓名
SELECT 学号,姓名 FROM 学生 WHERE NOT EXISTS ( SELECT * FROM 学习 WHERE 学生.学号 = 学习.学号 AND 课程号 = '180102' );四.除法运算的实现（困难）
4.1查询至少选修了091501号课程选修的全部课程的学生学号
查询用关系代数可以表示为：
有一个概念：“能使用除法的查询，必有一个集合包含另一个集合”
在这里，我们需要的结果集合包含了091501号课程集合
也可以这样说：“我们设有两个集合A、B，B - A得到的是在B集合中有的，而A集合中没有的，如果该结果集为空，说明B集合中的元素都存在于A集合中，即A包含B”
“而在本题中，A是表示某个学生选修课程的课程号集合，B集合表示090101号学生选修的课程号集合”
“如果A集合大于等于B集合，则当前学生是要查找的学生”
4.1.1第一步：明确两个集合
B集合：
SELECT 课程号 FROM 学习 WHERE 学习.学号 = 'XXX';“XXX”表示某个结果学生的学号A集合：
SELECT 课程号 FROM 学习 WHERE 学习.学号 = '091501';4.1.2第二步：表示A包含B
A包含B可以这样表示：
即：“不存在一个B减A有结果的集合”，在MySQL中“减运算”等于“EXISTS”
NOT EXISTS( ( SELECT 课程号 FROM 学习 AS FIRST WHERE FIRST.学号 = '091501' ) NOT EXISTS ( SELECT 课程号 FROM 学习 AS SECOND WHERE SECOND.学号 = 'XXX' ) );4.1.3第三步：筛选出结果集合
SELECT 学号 FROM 学生 WHERE NOT EXISTS( SELECT 课程号 FROM 学习 AS FIRST WHERE FIRST.学号 = '091501' AND NOT EXISTS( SELECT 课程号 FROM 学习 AS SECOND WHERE SECOND.学号 = 学生.学号 AND SECOND.课程号 = FIRST.课程号 ) );最后的“SECOND.课程号 = FIRST.课程号”目的是：“让课程号相同的元组作比较，不相同的元组不在比较范围内”
