数据库操作-DQL
一、DQL-概述-语法
1. 概述
- DQL英文全称是Data Query Language(数据查询语言)，用来查询数据库表中的记录。
- 关键字：SELECT
2. 语法结构
3. 数据准备
-- DQL（数据查询语言）
-- 数据准备
# 1. 员工管理(带约束)
create table tb_emp (
id int unsigned primary key auto_increment comment 'ID',
username varchar(20) not null unique comment '用户名',
password varchar(32) default '123456' comment '密码',
name varchar(10) not null comment '姓名',
gender tinyint unsigned not null comment '性别, 说明: 1 男, 2 女',
image varchar(300) comment '图像',
job tinyint unsigned comment '职位, 说明: 1 班主任,2 讲师, 3 学工主管, 4 教研主管',
entrydate date comment '入职时间',
create_time datetime not null comment '创建时间',
update_time datetime not null comment '修改时间'
) comment '员工表';
# 2. 准备测试数据
INSERT INTO tb_emp (id, username, password, name, gender, image, job, entrydate, create_time, update_time) VALUES
(1, 'jinyong', '123456', '金庸', 1, '1.jpg', 4, '2000-01-01', '2022-10-27 16:35:33', '2022-10-27 16:35:35'),
(2, 'zhangwuji', '123456', '张无忌', 1, '2.jpg', 2, '2015-01-01', '2022-10-27 16:35:33', '2022-10-27 16:35:37'),
(3, 'yangxiao', '123456', '杨逍', 1, '3.jpg', 2, '2008-05-01', '2022-10-27 16:35:33', '2022-10-27 16:35:39'),
(4, 'weiyixiao', '123456', '韦一笑', 1, '4.jpg', 2, '2007-01-01', '2022-10-27 16:35:33', '2022-10-27 16:35:41'),
(5, 'changyuchun', '123456', '常遇春', 1, '5.jpg', 2, '2012-12-05', '2022-10-27 16:35:33', '2022-10-27 16:35:43'),
(6, 'xiaozhao', '123456', '小昭', 2, '6.jpg', 3, '2013-09-05', '2022-10-27 16:35:33', '2022-10-27 16:35:45'),
(7, 'jixiaofu', '123456', '纪晓芙', 2, '7.jpg', 1, '2005-08-01', '2022-10-27 16:35:33', '2022-10-27 16:35:47'),
(8, 'zhouzhiruo', '123456', '周芷若', 2, '8.jpg', 1, '2014-11-09', '2022-10-27 16:35:33', '2022-10-27 16:35:49'),
(9, 'dingminjun', '123456', '丁敏君', 2, '9.jpg', 1, '2011-03-11', '2022-10-27 16:35:33', '2022-10-27 16:35:51'),
(10, 'zhaomin', '123456', '赵敏', 2, '10.jpg', 1, '2013-09-05', '2022-10-27 16:35:33', '2022-10-27 16:35:53'),
(11, 'luzhangke', '123456', '鹿杖客', 1, '11.jpg', 2, '2007-02-01', '2022-10-27 16:35:33', '2022-10-27 16:35:55'),
(12, 'hebiweng', '123456', '鹤笔翁', 1, '12.jpg', 2, '2008-08-18', '2022-10-27 16:35:33', '2022-10-27 16:35:57'),
(13, 'fangdongbai', '123456', '方东白', 1, '13.jpg', 1, '2012-11-01', '2022-10-27 16:35:33', '2022-10-27 16:35:59'),
(14, 'zhangsanfeng', '123456', '张三丰', 1, '14.jpg', 2, '2002-08-01', '2022-10-27 16:35:33', '2022-10-27 16:36:01'),
(15, 'yulianzhou', '123456', '俞莲舟', 1, '15.jpg', 2, '2011-05-01', '2022-10-27 16:35:33', '2022-10-27 16:36:03'),
(16, 'songyuanqiao', '123456', '宋远桥', 1, '16.jpg', 2, '2010-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:05'),
(17, 'chenyouliang', '12345678', '陈友谅', 1, '17.jpg', null, '2015-03-21', '2022-10-27 16:35:33', '2022-10-27 16:36:07'),
(18, 'zhang1', '123456', '张一', 1, '2.jpg', 2, '2015-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:09'),
(19, 'zhang2', '123456', '张二', 1, '2.jpg', 2, '2012-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:11'),
(20, 'zhang3', '123456', '张三', 1, '2.jpg', 2, '2018-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:13'),
(21, 'zhang4', '123456', '张四', 1, '2.jpg', 2, '2015-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:15'),
(22, 'zhang5', '123456', '张五', 1, '2.jpg', 2, '2016-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:17'),
(23, 'zhang6', '123456', '张六', 1, '2.jpg', 2, '2012-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:19'),
(24, 'zhang7', '123456', '张七', 1, '2.jpg', 2, '2006-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:21'),
(25, 'zhang8', '123456', '张八', 1, '2.jpg', 2, '2002-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:23'),
(26, 'zhang9', '123456', '张九', 1, '2.jpg', 2, '2011-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:25'),
(27, 'zhang10', '123456', '张十', 1, '2.jpg', 2, '2004-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:27'),
(28, 'zhang11', '123456', '张十一', 1, '2.jpg', 2, '2007-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:29'),
(29, 'zhang12', '123456', '张十二', 1, '2.jpg', 2, '2020-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:31');
二、DQL-基本查询
1. 语法
-
查询多个字段：
select 字段1, 字段2, 字段3 from 表名; -
查询所有字段（通配符）：
select 字段1, 字段2, ..., 字段n from 表名; select * from 表名; -
设置别名：
- as可以省略
select 字段1 [ as 别名1 ] , 字段2 [ as 别名2 ] from 表名; -
去除重复记录：
select distinct 字段列表 from 表名;
2. DQL操作
-
查询多个字段：
-
查询所有字段：
-
起别名：
-
去除重复记录：
3. 注意事项
① * 号代表查询所有字段，在实际开发中尽量少用（不直观、影响效率）。
三、DQL-条件查询
1. 语法
-
条件查询：
select 字段列表 from 表名 where 条件列表 ;
2. 条件运算符
(1) 比较运算符
| 比较运算符 | 功能 |
|---|---|
| > | 大于 |
| >= | 大于等于 |
| < | 小于 |
| <= | 小于等于 |
| = | 等于 |
| <> 或 != | 不等于 |
| between … and … | 在某个范围之内(含最小、最大值) |
| in(…) | 在in之后的列表中的值，多选一 |
| like 占位符 | 模糊匹配(_匹配单个字符, %匹配任意个字符) |
| is null | 是null |
| is not null | 不是null |
(2) 逻辑运算符
| 逻辑运算符 | 功能 |
|---|---|
| and 或 && | 并且 (多个条件同时成立) |
| or 或 || | 或者 (多个条件任意一个成立) |
| not 或 ! | 非 , 不是 |
3. DQL操作
-
查询 姓名 为 杨逍 的员工
-
查询 id小于等于5 的员工信息
-
查询 没有分配职位 的员工信息
-
查询 有职位 的员工信息
-
查询 密码不等于 ‘123456’ 的员工信息
-
查询 入职日期 在’2000-01-01’(包含) 到 ‘2010-01-01’(包含) 之间 的员工信息
-
查询 入职日期 在’2000-01-01’(包含) 到 ‘2010-01-01’(包含) 之间 且 性别为女 的员工信息
-
查询 职位是2(讲师), 3(学工主管), 4(教研主管) 的员工信息
-
查询 姓名 为两个字的员工信息
-
查询 姓’张’ 的员工信息
四、DQL-分组查询
1. 聚合函数
(1) 概述
| 函数 | 功能 |
|---|---|
| count | 统计数量 |
| max | 最大值 |
| min | 最小值 |
| avg | 平均值 |
| sum | 求和 |
- 介绍：将一列数据作为一个整体，进行纵向计算。
(2) 语法
select 聚合函数(字段列表) from 表名;
(3) DQL操作
-
count：
-
min：
-
max：
-
avg：
-
sum：
(4) 注意事项
注意事项：
-
null值不参与所有聚合函数的运算。
-
统计数量可以使用：
count(*) count(非空字段) count(常量)，推荐使用count(*)
2. 分组查询
(1) 语法
select 字段列表 from 表名 [ where 条件 ] group by 分组字段名 [ having 分组后过滤条件 ];
(2) where与having区别(面试)
① 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
② 判断条件不同：where不能对聚合函数进行判断，而having可以。
(3) DQL操作
-
根据性别分组，统计男性和女性员工的数量
-
先查询入职时间在 ‘2015-01-01’(包含) 以前的员工，并对结果根据 职位 分组，获取 员工数量大于等于2 的职位。
-
A. 先查询入职时间在 ‘2015-01-01’(包含) 以前的员工
-
B. 并对结果根据 职位 分组
-
C. 获取 员工数量大于等于2 的职位
-
(4) 注意事项
-
分组之后，查询的字段一般为：聚合函数和分组字段，查询其他字段无任何意义。
-
执行顺序: where > 聚合函数 > having 。
五、DQL-排序查询
1. 语法
select 字段列表 from 表名 [ where 条件列表 ] [ group by 分组字段 ] order by 字段1 排序方式1 , 字段2 排序方式2 …;
2.排序方式
- ASC：升序（默认值）
- DESC：降序
3. DQL操作
-
- 根据 入职时间，对员工进行 升序 排序
-
2.根据 入职时间，对员工进行 降序 排序
-
3.根据 入职时间 对公司的员工进行 升序 排序，入职时间相同，再按照 更新时间 进行降序排序
4. 注意事项
- 如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。
六、DQL-分页查询
1. 应用场景
2. 语法
select 字段列表 from 表名 limit 起始索引, 查询记录数 ;
3. DQL操作
-
1.从 起始索引0 开始查询员工数据，每页展示5条记录
-
2.查询 第1页 员工数据，每页展示5条记录
-
3.查询 第2页 员工数据，每页展示5条记录
-
4.查询 第3页 员工数据，每页展示5条记录
4. 注意事项
① 起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数。
② 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
③ 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10。
七、DQL-案例
1. 案例一
(1) 需求
- 按需求完成员工管理的条件分页查询：
- 输入条件：
- 姓名：张（支持模糊匹配）
- 性别：男（精确查询）
- 入职时间：2000-01-01 ~ 2015-12-31（范围查询）
- 根据输入条件，查询 第1页 数据，每页展示10条记录
- 对查询的结果，根据 最后修改时间 进行倒序排序
- 输入条件：
(2) DQL操作
-- DQL: 案例
-- 案例1: 按需求完成员工管理的条件分页查询：
-- 输入条件：
-- 姓名：张 ; 性别：男 ; 入职时间：2000-01-01 ~ 2015-12-31
-- 根据输入条件，查询 第1页 数据，每页展示10条记录
select *
from tb_emp
where name like '%张%'
and gender = 1 and entrydate between '2000-01-01' and '2015-12-31'
order by update_time desc
limit 10;
2. 案例二
(1) 需求
-
根据需求，完成员工信息的统计：
- 这类的需求也叫图形报表：做数据统计，将统计结果以可视化的形式展示
-
分析：
- 后端开发人员只需要写SQL进行统计，将统计结果返回给前端，前端再借助一些现成的报表组件库可以完成报表的渲染展示
(2) 流程控制函数
- if(表达式, tvalue, fvalue)：当表达式为true时，取值tvalue；当表达式为false时，取值fvalue
- case 表达式 when value1 then result1 [when value2 then value2 …] [else result] end
(3) DQL操作
-
案例2-1: 根据需求，完成员工性别信息的统计
-- 案例2-1: 根据需求，完成员工性别信息的统计 # 函数：if(条件表达式, true取值, false取值) select if(gender = 1, '男性员工', '女性员工') 性别, count(*) 员工数量 from tb_emp group by gender;
-
案例2-2: 根据需求，完成员工职位信息的统计
-- 案例2-2: 根据需求，完成员工职位信息的统计 # 函数：case 表达式 when 值1 then 结果1 when 值2 then 结果2 ... else 结果 end select (case job when 1 then '班主任' when 2 then '讲师' when 3 then '学工主管' when 4 then '教研主管' else '未分配职位' end) 职位, count(*) 员工数量 from tb_emp group by job;
(4) Apache ECharts
- 后端写SQL语句统计好数据后，将统计结果返回给前端，前端再借助这个Apache ECharts现成的报表组件库可以完成报表的渲染展示
① 员工性别统计
② 员工职位统计
总结
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
