MySQL之轻松拿下 DQL

最新推荐文章于 2024-09-04 09:00:00 发布

畅谈、

最新推荐文章于 2024-09-04 09:00:00 发布

阅读量345

点赞数 3

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

本文链接：https://blog.csdn.net/qq_52174675/article/details/122409303

版权

MySQL 专栏收录该内容

5 篇文章 1 订阅

订阅专栏

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 表：

teacher 表：

course 表：

1、SELECT - 入门

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

student 表：

teacher 表：

course 表：

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

SELECT * FROM `student`;

查询结果（可以看到和上边 student 表的数据一致）：

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

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

查询结果：

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

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

查询结果：

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

2、DISTINCT - 去重

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

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

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

查询结果（可以看到学院很多都重复了）：

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

查询结果（重复的学院都被隐藏了）：

3、WERER - 条件子句

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

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

3.1、比较运算符

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

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

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

查询结果：

3.2、逻辑运算符

运算符	描述	语法
AND / &&	只有二者同时为真，结果才为 true	condition_1 AND condition_2
OR / \|\|	只有二者同时为为假，结果才为 false	condition_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：

查询结果2：

查询结果3：

3.3、模糊查询

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

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

我们新增一个学生选课信息表（student_course），用于管理学生选课具体信息

student_course 插入数据：

-- 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：

查询结果2：

查询结果3：

查询结果4：

现在我们来讲一下稍微难以理解的 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：

查询结果6：

4、JOIN - 多表查询

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

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

实在看不懂也没事（重点看 1、3、4），下面我们一个个来讲

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

student 表：

course表：

student_course 表（稍微完善了一下）：

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

查询结果：

可以看到，即使在 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 子句

查询结果：

可以看到，没有出现在 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;

查询结果：

这次的结果和直接使用 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;

查询结果：

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

4.4、自连接

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

我们将 course 表进行字段补充，添加了 pre_course_id（前置课程号）

目标：查询 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;

查询结果：

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

-- 嵌套查询，非常强大的功能
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：

查询结果2：

5.2、GROUP BY

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

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

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

分类结果：

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

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

6、ORDER BY - 排序

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

-- 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：

查询结果2：

注意：

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

7、LIMIT - 分页

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

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

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

查询结果：

8、子查询（嵌套查询）

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

运算符	描述	语法
IN	在某些特定取值内，结果为 true	value IN(value_1,value_2,…,value_n)
ANY（SOME）	只要有一个值符合，就返回 true	value>ANY(value_1,value_2,…,value_n)
ALL	全部值符合，才返回 true	value>ALL(value_1,value_2,…,value_n)
EXISTS	存在查询结果，返回 true	EXISTS(SELECT Statement)
NOT EXISTS	不存在查询结果，返回 true	NOT 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;

三种方式的查询结果（均相同）：

我们再举一个例子，加深一下理解：

-- 查询 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
);

查询结果：

我们可以看到，一般的子查询都是从内到外，里层的查询结果作为外层的查询条件，但是这并不是绝对的，例如 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 表的第二条记录（即第二个学生），重复步骤二... 
注意：如果步骤二匹配失败，则会直接进入第三步 */

查询结果：

9、DQL 小结

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