MySQL之轻松拿下 DQL

DQL


DQL(Data Query Language)就是数据查询语言,顾名思义,用于查询数据库中的数据的一种 语言,非常的重要,接下来我们会花大量的篇幅讲解 DQL 中常见的语句

-- 首先先看看官方给出了 SELECT Statement,我们重点介绍其中的 WHERE、GROUP BY、JOIN、ORDER BY、LIMIT 子句

-- 看看就行!
-- 看看就行!
-- 看看就行!

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr] ...
    [into_option]
    [FROM table_references
      [PARTITION partition_list]]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
    [HAVING where_condition]
    [WINDOW window_name AS (window_spec)
        [, window_name AS (window_spec)] ...]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [into_option]
    [FOR {UPDATE | SHARE}
        [OF tbl_name [, tbl_name] ...]
        [NOWAIT | SKIP LOCKED]
      | LOCK IN SHARE MODE]
    [into_option]

into_option: {
    INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
  | INTO DUMPFILE 'file_name'
  | INTO var_name [, var_name] ...
}


请记住以下三张表,之后大部分的例子都会反复用到!

student 表:

13

teacher 表:

14


course 表:

15

1、SELECT - 入门

我们先往这三张表中插入数据:

student 表:

16

teacher 表:

17

course 表:

18

1、我们现在要在 student 表中查询所有学生的信息

SELECT * FROM `student`;

查询结果(可以看到和上边 student 表的数据一致):

19

2、在 student 表中查询计算机科学与技术 2 班的学生姓名

-- 需要使用到 WHERE 子句,但是没关系,之后会细讲
-- 我们只要查询 name 字段的值
SELECT `name` FROM `student`
WHERE `professional`='计算机科学与技术';

查询结果:

20

3、在 teacher 表中查询所有老师的 name 和 birth,并将 birth 重命名为 '出生日期’

-- 这里我们使用 AS 对字段重命名
SELECT `name`, `birth` AS '出生日期' FROM teacher;

查询结果:

21

通过上述的例子,让我们对数据库查询有了基本的了解,接下来我们会学习更复杂的查询语句


2、DISTINCT - 去重

DISTINCT 翻译过来就是 “清楚的”,其实就是在查询显示结果的时候,去掉重复的数据,只留下唯一的数据,让查询结果更加简洁

-- 现在我们要从 student 表中查询学生涉及到哪些学院

-- 不添加 DISTINCT
SELECT `college` FROM `student`;

查询结果(可以看到学院很多都重复了):
202201051334795.png

-- 使用 DISTINCT,注意 DISTINCT 的关键字位置
SELECT DISTINCT `college` FROM `student`;

查询结果(重复的学院都被隐藏了):

23

3、WERER - 条件子句

WHERE 子句是一个大内容,里面包含了很多条件判断,也是 SELECT 查询最主要的助手之一,WHRER 大致可以包含以下几种:

  • 比较运算符
  • 逻辑运算符
  • 模糊查询

3.1、比较运算符

运算符描述语法
=左右相等,结果为 truevalue_1 = value_2
<> / !=左右不相等,结果为 truevalue_1 != value_2
>左边大于右边,结果为 truevalue_1 > value_2
<左边小于右边,结果为 truevalue_1 < value_2
>=左边大于相等右边,结果为 truevalue_1 >= value_2
<=左边小于相等右边,结果为 truevalue_1 <= value_2

这个就举几个简单的例子吧,应该不能不会吧。。。

-- 查询 course 表中限选人数在 >45 的课程名
SELECT `name` FROM `course`
WHERE `max_number`>45;

查询结果:

24

3.2、逻辑运算符

运算符描述语法
AND / &&只有二者同时为真,结果才为 truecondition_1 AND condition_2
OR / ||只有二者同时为为假,结果才为 falsecondition_1 OR condition_2
NOT / !结果取反NOT condition
-- 1、查询 course 表中限选人数在(45,90)之间的课程名
SELECT `name` FROM `course`
WHERE `max_number`>45 AND `max_number`<90;

-- 2、查询 course 表中限选人数 <45 或者 >90 之间的课程名
SELECT `name` FROM `course`
WHERE `max_number`<45 OR `max_number`>90;

-- 3、查询 course 表中限选人数在(45,90)之间的课程名(使用 NOT)
SELECT `name` FROM `course`
WHERE NOT (`max_number`<45 OR `max_number`>90); -- 注意需要使用(),否则 NOT 只会作用在第一个条件上

查询结果1:

25

查询结果2:

26

查询结果3:

27

3.3、模糊查询

运算符描述语法
IS NULL值为空,结果为 truevalue IS NULL
IS NOT NULL值不为空,结果为 truevalue IS NOT NULL
BETWEEN AND在某个区间范围内,结果为 trueBETWEEN value_1 AND value_2
IN在某些特定取值内,结果为 truevalue IN(value_1,value_2,…,value_n)
LIKE匹配特定的字符串value_1 LIKE value_2

这里面的查询语句除了 LIKE,都比较好理解,下面还是先看例子:


我们新增一个学生选课信息表(student_course),用于管理学生选课具体信息

28

student_course 插入数据:

202201051335427.png

-- 1、查询 student_course 中 course_id 和 student_id 都不为 null 的记录
SELECT `student_id`, `course_id` FROM `student_course`
WHERE `student_id` IS NOT NULL AND `course_id` IS NOT NULL;

-- 2、查询 student_course 中 course_id 或 student_id 为 null 的记录
SELECT `student_id`, `course_id` FROM `student_course`
WHERE `student_id` IS NULL OR `course_id` IS NULL;

-- 3、查询 course 表中限选人数在(45,90)之间的课程名
SELECT `name` FROM `course`
WHERE `max_number`BETWEEN 45 AND 90;

-- 4、查询信息科学与工程学院和管理学院的所有学生信息
SELECT * FROM `student`
WHERE `college` IN('信息科学与工程学院','管理学院');

查询结果1:

30

查询结果2:

31

查询结果3:

32

查询结果4:

33

现在我们来讲一下稍微难以理解的 LIKE 子句,LIKE 就是用来匹配符合规则的字符串,具体匹配将使用 %_,% 可以匹配 0~n 个字符,_ 可以匹配 1 个字符

如果需要包含 % 或者 _ 该怎么办,我们可以使用转义字符 \

-- 5、查询所有包含 'Java' 字符的课程名
SELECT `name` FROM `course`
WHERE `name` LIKE '%Java%';

-- 6、查询所有以 'C' 开头,长度为 3 的课程名
SELECT `name` FROM `course`
WHERE `name` LIKE 'C__';

查询结果5:

34

查询结果6:

35


4、JOIN - 多表查询

先来看看 SQL 中 JOIN 的七种类型,其实看着吧,无非就是排列组合的几种情况(没有全白的情况,毕竟全都不选,那你查什么)。从中我们也得到信息,查询区域可以分为左中右三种:

  • LEFT JOIN:只读取左边数据表的全部数据,右边不读
  • INNER JOIN:只读取两表相同的数据,不同的不读
  • RIGHT JOIN:只读取右边数据表的全部数据,左边不读

202201051339183.png

实在看不懂也没事(重点看 1、3、4),下面我们一个个来讲

首先 student、course、student_course 这几张表都有点忘了吧,来,给你们摆上

student 表

37

course表

38

student_course 表(稍微完善了一下):

39

4.1、LEFT JOIN

LEFT JOIN 的重点在于 LEFT,即 MySQL 只会读取左边数据表中的数据

从图中反映就是左+中(我们将区域分成三部分,左 + 中 = 左表;中 + 右 = 右表;中 就是两表公共的数据区域),没有右!没有右!没有右!

-- 目标:查询所有学生选取课程的 id 是多少(包括没选课的)
SELECT stu.name AS '学生姓名', sc.course_id AS '课程号'	-- 取别名
FROM `student` stu										-- 从 student 表中查询
LEFT JOIN `student_course` sc							-- 左连接,使用 LEFT JOIN
ON stu.id = sc.student_id;								-- 注意,用 ON,不用 WHERE

查询结果:

40

可以看到,即使在 student_course 表中没有赵六~吴十的选课记录,他们依旧能出现在查询结果中。因为 LEFT JOIN 会读取 student 表的所有内容,然后将每一条记录都拿出来在 student_course 表中进行匹配,不存在用 null 替代(一定要好好品这句话)


假如我们使用 WHERE 子句进行上述目标查询

SELECT stu.name AS '学生姓名', sc.course_id AS '课程号'	-- 取别名
FROM `student` stu, `student_course` sc					-- 多表查询
WHERE stu.id = sc.student_id;							-- WHERE 子句

查询结果:

41

可以看到,没有出现在 student_course 表中的学生,并不会被查询!这就是LEFT JOIN 和 WHERE 的区别之一,LEFT JOIN 最后的记录条数和左表一定相同,而 WHERE 展示的是两个表中均出现的记录


4.2、INNER JOIN

我们还是使用上边的代码,只是将 LEFT 改成 INNER,观察区别

-- 目标:查询所有学生选取课程的 id 是多少(包括没选课的)
SELECT stu.name AS '学生姓名', sc.course_id AS '课程号'
FROM `student` stu
INNER JOIN `student_course` sc							-- 内连接,使用 INNER JOIN
ON stu.id = sc.student_id;

查询结果:

42

这次的结果和直接使用 WHERE 子句一摸一样,这是肯定的,因为 INNER JOIN 选取的数据是两个表共有的数据,而 WHERE 选取的也是共有的数据,所以结果必然是一样的,这样是不是能更加理解 LEFT JOIN 为什么能查出上述的结果了吧


4.3、RIGHT JOIN

最后是 RIGHT JOIN,原理类似,直接上代码

-- 目标:查询所有学生选取课程的 id 是多少(包括没选课的)
SELECT stu.name AS '学生姓名', sc.course_id AS '课程号'
FROM `student` stu
RIGHT JOIN `student_course` sc							-- 右连接,使用 RIGHT JOIN
ON stu.id = sc.student_id;

查询结果:

43

可以看到,查询结果数和 student_course 表的数量一致,毕竟 RIGHT JOIN 会选取右表的所有记录,而不选取左表!


4.4、自连接

自连接是一种特殊的多表查询,简单来说就是将一张表和自身连接,进行查询。听起来是不是有点搞笑,为什么自己要和自己连接,那为什么不直接查询呢?别急,看例子说话

我们将 course 表进行字段补充,添加了 pre_course_id(前置课程号)

44

目标:查询 CSS 课程的前置课名,注意,是名称

/* 步骤
1、给表取两个别名
2、给出想要查询的列,注意:不要搞错别名
3、选取条件,WHERE 子句应用*/

/* 思路:(利用反推的思想)CSS 的前置课是不是 HTML,那么要查询到 HTML,就需要知道 id=3,那么
id=3 在 CSS 的 pre_course_id 出现了,而查询 pre_course_id 很简单,只需要通过 name 字段匹配 CSS 即可 */

SELECT cou_2.`name` FROM `course` cou_1, `course` cou_2
WHERE cou_1.name = 'CSS' && cou_1.pre_course_id = cou_2.id;

查询结果:

45

那能不能不使用自连接,做到同样的查询结果呢?答案是可以的,利用嵌套查询,这个之后会讲,结果和上边一样(中国人不骗中国人)

-- 嵌套查询,非常强大的功能
SELECT `name` FROM `course` cou_1
WHERE cou_1.id IN (
    SELECT `pre_course_id` FROM `course` cou_2
    WHERE cou_2.`name` = 'CSS'
);

5、GROUP BY - 分组

在介绍 GROUP BY 之前,我们先来看看聚合函数。其实聚合函数也是函数的一种,我们知道在 MySQL 中有许许多多的函数,很多 Java 使用的函数在 MySQL 中都能使用,例如 rand()、substr()、insert() 等等,但是往往不太常用。下面介绍的几种聚合函数在 SQl 语句中出现的频率很高


5.1、聚合函数

函数名作用用法
COUNT()求总个数SELECT COUNT(`字段名`) FROM … WHERE …
SUM()求和SELECT SUM(`字段名`) FROM … WHERE …
AVG()求平均值SELECT AVG(`字段名`) FROM … WHERE …
MAX()求最大值SELECT MAX(`字段名`) FROM … WHERE …
MIN()求最小值SELECT MIN(`字段名`) FROM … WHERE …

上代码,直接看例子:

-- 1、查询信息科学与工程学院一共有多少学生
SELECT COUNT(*) AS '学生人数' FROM `student`
WHERE `college` = '信息科学与工程学院';

-- 2、查询人数最多的学院及其人数,这里使用了 GROUP BY,不理解没关系,下面会介绍
SELECT `college`, MAX(`学生人数`) AS '学生人数'
-- 使用了子查询,详见 4.8
FROM (SELECT college, count(*) AS '学生人数' FROM student GROUP BY `college`) AS tmp;

查询结果1:

image-20220108160903834

查询结果2:

image-20220108160931773

5.2、GROUP BY

首先每一个关键字都不是瞎取名的,GROUP BY 翻译过来就是 ”根据…进行分组“,是不是瞬间知道这是用来干什么了,必然是对字段的属性进行分组的操作。我们需要注意的是这个关键词需要放在 WHERE 后面。

-- GROUP BY 语句格式
GROUP BY `列名`;

-- 我们根据学院对学生进行分类
SELECT * FROM student
GROUP BY `college`;

分类结果:

image-20220108162411801

有些人可能会疑问,为什么分组完毕后,少了很多人?其实 GROUP BY 分组的时候,如果字段属性相同的记录,只会选取第一条,剩下的都会被隐藏,而不是丢弃!这就是为什么最终的结果并不是全部记录的原因

往往 GROUP BY 都是搭配聚合函数使用,可以增加很多查询的手段


6、ORDER BY - 排序

ORDER BY 语句很简单,记住两个常用的关键字 ASCDESC,分别代表升序和降序

-- ORDER BY 语句格式
ORDER BY `列名` [DESC/ASC];

-- 1、按照最大选课人数对 course 表进行降序排序
SELECT * FROM `course`
ORDER BY `max_number` DESC;

-- 2、按照最大选课人数对 course 表进行升序排序
SELECT * FROM `course`
ORDER BY `max_number` ASC;

查询结果1:

46

查询结果2:

47

注意:

  • 不写 ASC 或者 DESC,默认为 ASC(升序)
  • 可以使用 ORDER BY 对字符集进行排序,有些字符集需要进行转码,再排序,例如 utf8

7、LIMIT - 分页

LIMIT 常用于数据分页,有时候查询结果记录数量太多,不能在一页显示,那么我们就需要进行分页操作

-- LIMIT 格式:LIMIT 偏移量, 显示条数

-- 显示 student 表,按照两条记录/页显示查询记录(这里暂先查询第一面)
SELECT * FROM `student`
LIMIT 0, 2;					-- 从第 0 条记录开始,显示两条

查询结果:

48

8、子查询(嵌套查询)

子查询也称为嵌套查询,一般利用 内层查询 得到的结果,作为 外层查询 的条件。在之前的学习中,我们接触了 IN 关键字,实际上 IN 在子查询中应用广泛,同时我们还会学习另外几个子查询常用关键字,如下表:

运算符描述语法
IN在某些特定取值内,结果为 truevalue IN(value_1,value_2,…,value_n)
ANY(SOME)只要有一个值符合,就返回 truevalue>ANY(value_1,value_2,…,value_n)
ALL全部值符合,才返回 truevalue>ALL(value_1,value_2,…,value_n)
EXISTS存在查询结果,返回 trueEXISTS(SELECT Statement)
NOT EXISTS不存在查询结果,返回 trueNOT EXISTS(SELECT Statement)

下面我们举个例子:

-- 查询选了课程 ID = 3 的所有学生的姓名

-- 首先我们不使用子查询,按照我们之前学的联表查询完成查询目标
SELECT `stu`.name AS '姓名' 
FROM `student` `stu`, `student_course` `sc`
WHERE `stu`.id = `sc`.student_id && `sc`.course_id = 3;

-- 接下来我们使用子查询(嵌查询),其实子查询无非就是一个 SELECT 语句中包含另一个 SELECT 语句
SELECT `name` AS '姓名' 
FROM `student`
WHERE `id` IN (
    -- 子查询语句,使用了 IN
	SELECT `student_id` FROM `student_course`
    WHERE `course_id` = 3
);

-- 上面这种称为 WHERE 子查询,我们还可以写在 FROM 中,称为 FROM 子查询(一般不用)
SELECT `name` AS '姓名'
-- 注意:我们需要将子查询所得的表起一个别名!
FROM (SELECT `stu`.name , `sc`.course_id FROM `student` `stu`, `student_course` `sc` WHERE `stu`.id = `sc`.student_id) AS tmp
WHERE `course_id` = 3;

三种方式的查询结果(均相同):

image-20220108120552012

我们再举一个例子,加深一下理解:

-- 查询 student_course 表中所有选课学生所在的学院(去重)

-- 首先我们还是不使用子查询,完成查询目标
SELECT DISTINCT `stu`.college
FROM `student` `stu`, `student_course` `sc`
WHERE `stu`.id = `sc`.student_id && `sc`.course_id IS NOT NULL && `sc`.student_id IS NOT NULL;

-- 下面使用子查询,还是用到 IN
SELECT DISTINCT `college` FROM `student`
WHERE `student`.id IN (
	SELECT `student_id` FROM `student_course`
    WHERE `student_id` IS NOT NULL && `course_id` IS NOT NULL
);

查询结果:

image-20220108124935706

我们可以看到,一般的子查询都是从内到外,里层的查询结果作为外层的查询条件,但是这并不是绝对的,例如 EXISTS,它是将外层的每一条记录,扔到里层看看符不符合要求,如果符合要求,则进行显示,否则开始下一条记录的判断


下面我们继续看例子(子查询还是很重要的)

-- 查询所有选课学生的出生日期,按照 name + birth 的格式显示(很奇葩的例子,懒得想其他例子了 [doge])
SELECT `name`, `birth` FROM `student`
WHERE EXISTS(
    SELECT `student_id` AS 'id' FROM `student_course` 
    -- 这里两张表的字段名称并没有重复,所以不用取别名,但是在以后的书写中,还是推荐对每一张表取别名
    WHERE `student_id` IS NOT NULL && `course_id` IS NOT NULL && `id` = `student_id`
);

/* 我们对上面的查询过程做一个详解:
1、首先系统会找到 student 表的第一条记录(即第一个学生)
2、取出来在 student_course 表中匹配和这位学生 id 相同的记录,并且判断 course_id 字段是否不为空,只有当二者都符合条件的时候,系统才会认为这条记录符合查询的条件,将其显示
3、接下来找到 student 表的第二条记录(即第二个学生),重复步骤二... 
注意:如果步骤二匹配失败,则会直接进入第三步 */

查询结果:

54

9、DQL 小结

本文,我们接触了 DQL(数据查询语言),依次介绍了 WHERE、JOIN、GROUP BY、ORDER BY、LIMIT 子句;学习了单层查询、嵌套查询、多表查询等。上边所有的例子都不难,真正写 SQL 查询语句的时候,往往逻辑都是非常复杂的,所以我们需要学习思路,而不是死磕代码。
需要注意还有一些子句我并没有提及,例如 having、case 等,不代表没有用,只是可能用的没有那么频繁
那么就先写这么多吧,自己想例子也是很费脑子的 [doge]

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

畅谈、

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值