DQL表查询操作

DQL 表查询操作

简介

DQL（Data Query Language）是数据库查询语言，用于从数据库中检索数据。DQL 允许用户使用 SELECT 语句来查询数据库中的表，并根据指定的条件检索数据。

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

需要注意的是，DQL 只负责查询数据，而不涉及数据的插入、更新或删除等操作。这些操作通常由 DML（Data Manipulation Language）完成。

数据准备

MySQL 官方提供的工具：

地址：

https://github.com/datacharmer/test_db

一个用于练习的数据库

使用方法：

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 departments;

字段查询

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

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

起别名

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

示例

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

去重

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

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

运算查询

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

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

条件查询语法

SELECT 列名 FROM 表名 WHERE 条件表达式

比较运算符

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

比较大小

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

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

使用 BETWEEN 进行模糊查询

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

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

使用 IN 进行模糊查询

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

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

判断是否为空

语法：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;

逻辑运算符

运算符	说明
`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';

通配符

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

在查询的时候，字段中的内容并不一定与查询内容完全匹配。这个时候就使用 LIKE 关键字搭配通配符进行模糊查询。

语法：WHERE <列名> [NOT] LIKE <字符表达式>

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

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

排序

排序语法

ASC 表示升序排序(默认)
DESC 表示降序排序

SELECT 字段名 FROM 表名 [WHERE 条件表达式] ORDER BY 字段名 [ASC / DESC]

排序是通过 ORDER BY 子句,可以将查询出的结果进行排序(排序只是显示效果,不会影响真实数据)

使用关键字 ORDER BY 可以对查询的结果进行升序（ASC）和降序（DESC）排列，在默认情况下，ORDER BY 按升序输出结果。如果要按降序排列可以使用 DESC 来实现。语法格式如下。

对含有 NULL 值的列进行排序时，如果是按升序排列，NULL 值将出现在最前面，如果是按降序排列，NULL 值将出现在最后。

单列排序

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

-- 使用 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;

组合排序

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

-- 组合排序
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；

聚合查询

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;

使用聚合方法 sum：返回结果记录集中某个字段里最高的数据

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

使用聚合方法 avg：返回结果记录集中某个字段的平均数据

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

分组查询语法

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

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

分组查询指的是使用 GROUP BY 语句,对查询的信息进行分组,相同数据作为一组。

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

分组往往和聚合函数一起时候，对数据进行分组，分完组之后在各个组内进行聚合统计分析

注意

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

示例

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

子句区别

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;

单标查询总结

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

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 中选择指定数量的行，并生成最终结果返回。

总结

DQL 是一种用于查询数据库中表的官方定义语言，主要用于检索数据，并提供了各种查询操作和功能来满足用户的需求。