数据查询语言DQL

chengyuan_3278 · 2024 年6 月 27 日 04:13

一、简介

DQL（Data Query Language）是数据库查询语言，用于从数据库中检索数据。DQL允许用户使用SELECT语句来查询数据库中的表，并根据指定的条件检索数据。
DQL主要用于对数据库中的表进行数据查询操作，而不对数据进行修改或更改。通过DQL，用户可以执行各种查询操作，例如选择特定列、执行聚合函数、使用条件过滤等。
DQL还支持联接、子查询和排序等复杂查询操作，使得用户能够灵活地检索所需的数据。

二、数据准备

测试数据库：
- GitHub - datacharmer/test_db: A sample MySQL database with an integrated test suite, used to test your applications and database servers
网盘下载：
- 网盘下载地址
- 提取码：gxow

使用方法：

1.克隆项目/下载项目

在 github 中克隆或者直接从网盘下载 zip 压缩包。

git clone https://github.com/datacharmer/test_db.git

2.查看项目

下载完成后使用 employees.sql 。

3.导入数据

命令行操作：

先进入数据的目录

cd data目录

执行导入命令

mysql -h 127.0.0.1 -uroot -p < employees.sql

密码：hogwarts

注意：此处采用的是命令行方式导入，如果有客户端工具的话，是可以直接导入sql类型的数据，但是这种通过客户端导入sql文件的方式，并不适用于导入官方数据集，因为employees.sql文件里使用source命令去调用dump文件，客户端不支持这个操作，所以只能使用命令去执行。

三、单表查询

单表查询：从一张表中查询所需要的数据，所有查询操作都比较简单。
* 代表所有的列；
语法：SELECT * FROM 表名;

-- 基本单表查询
SELECT * FROM 表名;
-- 查询部门表中的信息
SELECT * FROM departments;

3.1 字段查询

查询多个字段，可以使用，对字段进行分隔。

SELECT 列名 FROM 表名;
-- 查询部门的名称
SELECT dept_name FROM departments;

3.2 起别名

-- 为表起别名
SELECT 列名 FROM 表名 表别名;
-- 为字段起别名
SELECT 列名 AS 别名 FROM 表名;

示例：

-- 查询员工信息，并将列名改为中文
SELECT
    emp_no AS '工号',
    first_name AS '名',
    last_name AS '姓',
    gender AS '性别',
    hire_date AS '入职时间'
FROM
    employees emp;

3.3 去重

DISTINCT关键字：去掉重复部门信息。

SELECT DISTINCT 列名 FROM 表名;
-- 去掉重复职级信息
SELECT DISTINCT title FROM titles;

3.4 运算查询

-- 查询结果参与运算
SELECT (列名 运算表达式)  FROM 表名;

-- 所有员工的工资 +1000 元进行显示
SELECT emp_no , salary + 1000 FROM salaries;

四、条件查询语法

4.1 比较运算符

运算符	说明
> < <= >= = <> !=	大于、小于、小于等于、大于等于、等于、不等于
BETWEEN…AND…	范围限定
IN	子集限定
LIKE ‘%or%’	模糊查询
IS NULL	为空

4.2 比较大小

语法：WHERE <列表> [> < <= >= = <> !=] <值>

-- 查询出生日期晚于 1965-01-01 的员工编号、姓名和生日
SELECT
    emp_no, first_name, last_name, birth_date
FROM
    employees
WHERE
    birth_date > '1965-01-01';

4.3 使用BETWEEN进行模糊查询

语法：WHERE <列名> [NOT] BETWEEN <起始表达式> AND <结束表达式>
注意：<起始表达式> 和 <结束表达式> 的顺序不能颠倒。

-- 查询年薪介于 70000 到 70003 之间的员工编号和年薪
SELECT
    emp_no, salary
FROM
    salaries
WHERE
    salary BETWEEN 70000 AND 70003;

4.4 使用IN进行模糊查询

语法：WHERE <列名> IN <(常量列表)>
(常量列表)中各常量值用逗号隔开。

-- 查询入职日期为 1995-01-27 和 1995-03-20 日的员工信息
SELECT
    *
FROM
    employees
WHERE
    hire_date IN ('1995-01-27', '1995-03-20');

4.5 判断是否为空

语法：WHERE <列名> IS [NOT] NULL

-- 选择 hog_demo 为当前数据库
USE hog_demo;

-- 更新 student 表中 id 为 2 的 age 值为 NULL
UPDATE student SET age = NULL WHERE id = 2;

-- 查询学生表中年龄为 NULL 的学生信息
SELECT
    *
FROM
    student
WHERE
    age IS NULL;

4.6 逻辑运算符

运算符	说明
AND &&	多个条件同时成立
OR ｜｜	多个条件任一成立
NOT	不成立

逻辑运算符用来判断表达式的真假。如果表达式为真，结果返回1；如果表达式为假，结果返回0。
逻辑运算符又称布尔运算符。
示例：

USE employees;
-- 查询名字为 Lillian 并且姓氏为 Haddadi 的员工信息
SELECT
    *
FROM
    employees
WHERE
    first_name = 'Lillian'
        AND last_name = 'Haddadi';

-- 查询名字为 Lillian 或者姓氏为 Terkki 的员工信息
SELECT
    *
FROM
    employees
WHERE
    first_name = 'Lillian'
        OR last_name = 'Terkki';

-- 查询名字为 Lillian 并且性别不是女的员工信息
SELECT
    *
FROM
    employees
WHERE
   first_name = 'Lillian'
   and not gender='F';

4.7 通配符

运算符	说明
%	匹配任意多个字符
-	匹配一个字符

在查询时，字段中的内容并不一定与查询内容完全匹配，需要使用LIKE关键字搭配通配符进行模糊查询。
语法：WHERE <列名> [NOT] LIKE <字符表达式>
示例：

-- 查询名字中包含 fai 的员工的信息
SELECT
    *
FROM
    employees
WHERE
    first_name LIKE '%fai%';

-- 查询名字中 fa 开头的名字长度为 3 位的员工信息
SELECT
    *
FROM
    employees
WHERE
    first_name LIKE 'fa_';

五、排序

5.1 排序语法

语法：SELECT 字段名 FROM 表名 [WHERE 条件表达式] ORDER BY 字段名 [ASC / DESC]
- ASC表示升序排序（默认）；
- DESC表示降序排序。
排序是通过ORDER BY字句，将查询出来的结果进行排序（排序只是显示效果，不会影响真实数据）。
使用关键字ORDER BY可以对查询结果进行升序（ASC）和降序（DESC）排列，在默认情况下，ORDER BY按升序输出结果。
对含有NULL值的列进行排序时，如果是按升序排列，NULL值将出现在最前面；如果是降序，NULL值将出现在最后面。

5.2 单列排序

只按照某一个字段进行排序，就是单列排序。

-- 使用 salary 字段,对 salaries 表数据进行升序排序
SELECT * FROM salaries ORDER BY salary;

-- 使用 salary 字段,对 salaries 表数据进行降序排序
SELECT * FROM salaries ORDER BY salary DESC;

-- 查询员工的编号和入职日期，按照员工入职日期从晚到早排序
SELECT
    emp_no, hire_date
FROM
    employees
ORDER BY hire_date DESC;

5.3 组合排序

同时对多个字段进行排序，如果第一个字段相同，就按照第二个字段进行排序，以此类推。

- 组合排序
SELECT
    emp_no, hire_date
FROM
    employees
ORDER BY hire_date DESC, emp_no DESC;

六、聚合函数

COUNT()：统计指定列不为NULL的记录行数；常用的有count(_)，count(列)。count(_)是针对于全表的，而count(列)是针对于某一列的，如果此列为空，则count(列)不会统计这一行。
MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算。
MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算。
SUM()：计算指定列的数值和，如果执行列不是数值类型，那么计算结果为0。
AVG()：计算指定列的平均值，如果指定列不是数值类型，那么计算结果为0。

6.1 聚合查询

语法：SELECT 聚合函数(列名) FROM 表名;

示例：

使用聚合方法count：返回结果记录集的数目。

SELECT
    COUNT(*)
FROM
    titles
WHERE
    title = 'Senior Engineer';

使用聚合方法sum：返回结果记录集中某个字段的数据总和。

SELECT
    SUM(salary)
FROM
    salaries
WHERE
    emp_no = 10002;

使用聚合方法min：返回结果记录集中某个字段里最小的值。

SELECT
    MIN(salary)
FROM
    salaries
WHERE
    emp_no = 10002;

使用聚合方法max：返回结果记录集中某个字段里最大的值。

    MAX(salary)
FROM
    salaries
WHERE
    emp_no = 10002;

使用聚合方法avg：返回结果集中某个字段的平均值。

SELECT
    AVG(salary)
FROM
    salaries
WHERE
    emp_no = 10002;

七、分组查询语法

分组列：按照哪些列进行分组；
HAVING：对分组结果再次过滤。

-- 分组查询
SELECT 分组列/聚合函数 FROM 表名
GROUP BY 分组列
[HAVING 条件];

注：分组往往和聚合函数结合使用，对数据进行分组，分组后在各个组内进行聚合统计分析。

7.1 注意事项

分组的列必须在select关键字的后面。
select后，除了group by的列和聚合函数之外，不能写其他的列。

示例：

SELECT
    emp_no, SUM(salary)
FROM
    salaries
WHERE
    emp_no < 10010
GROUP BY emp_no
HAVING SUM(salary) < 400000;

7.2 子句区别

WHERE子句：从数据源中去掉不符合其搜索条件的数据。
GROUP BY子句：搜集数据行到各个组中，统计函数为各个组计算统计值。
HAVING子句：去掉不符合其组搜索条件的各行数据行。

八、LIMIT关键字

限制查询结果的数量：
- 开始的行数：从0开始计数，如省略则默认为0；
- 查询记录的条数：返回的行数。

-- 限制查询结果行数
SELECT 列名1, 列名2...
FROM 表名
LIMIT [开始的行数], <查询记录的条数>

示例：

分页查看员工信息，每页展示 10 条记录。

-- 展示前 10 条员工信息
SELECT * FROM employees LIMIT 10;
SELECT * FROM employees LIMIT 0, 10;
SELECT * FROM employees LIMIT 10 OFFSET 0;

- 显示年薪从高到低排序，第 15 位到第 20 位员工的编号和年薪。

SELECT
    emp_no, salary
FROM
    salaries
ORDER BY salary DESC
LIMIT 6 OFFSET 14;

----

SELECT
    emp_no, salary
FROM
    salaries
ORDER BY salary DESC
LIMIT 14 , 6;

8.1 单标查询总结

-- 基础查询语法
SELECT DISTINCT <列名>
FROM <表名>
WHERE <查询条件表达式>
GROUP BY <分组的列名>
HAVING <分组后的查询条件表达式>
ORDER BY <排序的列名> [ASC / DESC]
LIMIT [开始的行数], <查询记录的条数>

8.2 SQL语句执行顺序

FROM：对 FROM 子句中的表进行查询，生成虚拟表 1。
WHERE：对虚拟表 1 应用 WHERE 筛选器。只有使 where 子句中的条件为 true 的行才被插入虚拟表 2。
GROUP BY：按 GROUP BY 子句中的列对虚拟表 2 中的行分组，生成虚拟表 3。
HAVING：对虚拟表 3 应用 HAVING 筛选器。只有使 HAVING 子句中的条件为 true 的组才会被插入虚拟表 4。
SELECT：处理 SELECT 列表，产生虚拟表 5。
DISTINCT：将重复的行从虚拟表 5 中移除，产生虚拟表 6。
ORDER BY：将虚拟表 6 中的行按 ORDER BY 子句中的列排序，生成虚拟表 7。
LIMIT：从虚拟表 7 中选择指定数量的行，并生成最终结果返回。