数据库讲解---(数据查询)【MySQL版本】

是洋洋a

已于 2024-04-10 20:11:21 修改

阅读量3k

点赞数 29

分类专栏：数据库文章标签：数据库 mysql

于 2024-04-09 21:28:18 首次发布

本文链接：https://blog.csdn.net/zheshiyangyang/article/details/137563024

版权

数据库专栏收录该内容

8 篇文章 0 订阅

订阅专栏

1.4.1查询所有不及格课程的学生的学号、课程号及成绩

1.4.2在学生表中找出信电学院2000年后出生的学生的记录

1.4.3查询出生年份在“1996-1998”年出生的学生的姓名、性别、学院、出生年份

1.4.4查询信电学院、理学院、计算机学院的学生的学号、姓名、学院

1.4.4查询学院不是“信电学院”的学生的学号、姓名、学院

1.5字符匹配(模糊匹配)

1.5.1查询所有姓王的学生的姓名、学号、性别

1.5.2查询名字中，第二个字为“小”字的学生的姓名和学号

1.5.3查找课程名是“DB_Design”课程的课程号、课程性质

1.5.4空值

1.6GROUP BY

1.6.1查询学号在091501~091506至少选修了三门课程的学生的学号和选修课程的课程数

1.7集函数

1.7.1查询学生总人数

1.7.2查询计算机学院学生的平均年龄

1.7.3查询学习180101号课程的学生的最高分数

1.8对查询结果排序

1.8.1查询选修了180102号课程的学生的学号和成绩，查询结果按照成绩从高到低排序

1.8.2查询全体学生情况，查询结果按所在学院的名称升序排列，对同一学院中的学生按年龄降序排列

二.多表查询(连接查询)

2.1等值与非等值连接查询

2.1.1查询每个学生及其选修课程的情况

2.1.2使用自然连接“学生”表和“学习”表

2.2自身连接查询

2.2.1求每一门课程的间接选修课(先修课的先修课)

2.3外连接查询

2.3.2查询所有学生的姓名以及他们选修课程的课程号和成绩

2.3.3查询所有的课程信息及选修该课程的学生的学号及成绩

2.4复合条件连接查询

2.4.1查询选修180101号课程且成绩在90分以上的学生学号，姓名及成绩

2.5多表连接

2.6集合运算连接查询

2.6.1查询选修了180101号或180102号课程或二者都选修了的学生学号、课程号和成绩

三.嵌套查询

3.1简介

3.1.1查询选修了180101号课程的学生姓名

3.2带有ANY或ALL谓词的子查询

3.2.1查询其它学院中比计算机学院某个学生年龄小的学生名单

3.3带有EXISTS谓词的子查询

3.3.1查询选修了180102号课程的学生学号和姓名

3.3.2查询没有选修180102号课程的学生学号和姓名

四.除法运算的实现（困难）

4.1查询至少选修了091501号课程选修的全部课程的学生学号

4.1.1第一步：明确两个集合

4.1.2第二步：表示A包含B

4.1.3第三步：筛选出结果集合

零.前言

数据库讲解（MySQL版）（超详细）【第一章】-CSDN博客

数据库-ER图教程_e-r图数据库-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.课程号”目的是：“让课程号相同的元组作比较，不相同的元组不在比较范围内”

是洋洋a

关注

29
点赞
踩
26

收藏

觉得还不错? 一键收藏
打赏
0
评论
数据库讲解---(数据查询)【MySQL版本】

COLUMN AS 或者 COLUMN SELECT 学号 year(now()) - 出生年份 AS 年龄FROM 学生;结果：在这里，我们将：“year(now()) - 出生年份”的名字修改为：“年龄。
复制链接

扫一扫