前言
DQL英文全称是Data Query Language(数据查询语言),用来查询数据库表中的记录。
查询关键字:SELECT
查询操作是所有SQL语句当中最为常见,也是最为重要的操作。在一个正常的业务系统中,查询操作的使用频次是要远高于增删改操作的。当我们打开某个网站或APP所看到的展示信息,都是通过从数据库中查询得到的,而在这个查询过程中,还会涉及到条件、排序、分页等操作。
一、语法
DQL查询语句,语法结构如下:
SELECT
字段列表
FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段列表
HAVING
分组后条件列表
ORDER BY
排序字段列表
LIMIT
分页参数
我们将分为以下几个类型:
1.基本查询(不带任何条件)
2.条件查询(where)
3.分组查询(group by)
4.排序查询(order by)
5.分页查询(limit)
准备一些用于测试数据用于查询操作:
-- 员工管理(带约束)
create database db02;-- 创建数据库
use db02; -- 切换操作数据库
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 '员工表';
-- 准备测试数据
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');
二、基本查询
语法如下:
1.查询多个字段
select 字段1,字段2,字段3 from 表名;
2.查询所有字段(通配符)
select * from 表名;
3.设置别名
select 字段1 【as 别名1】 , 字段2 【as 别名2】 from 表名;
4.去除重复记录
select distinct 字段列表 from 表名;
案例1:查询指定字段name,entrydate并返回
select name,entrydate from tb_emp;
运行结果
案例2:查询返回所有字段
select * from tb_emp;
*号代表查询所有字段,在实际开发中尽量少用(不直观,影响效率)
运行结果
案例3:查询所有员工的name,entrydate并起名(姓名,入职时期)
方式1
select name as 姓名, entrydate as 入职日期
from tb_emp;
方式2
select name 姓名, entrydate 入职日期
from tb_emp;
方式3
select name '姓 名', entrydate 入职日期
from tb_emp;
运行结果
案例4:查询已有的员工关联了哪几种职位(不要重复)
select distinct job from tb_emp;
运行结果
三、条件查询
语法:
selsct 字段名称 from 表名 where 条件列表 ;
-- 条件列表,意味着可以有多个列表
学习条件查询的关键就是学习条件的构造方式,而在SQL语句中构造条件的运算符分为两类:
1.比较运算符
2.逻辑运算符
常用的比较运算符如下
比较运算符 | 功能 |
---|---|
> | 大于 |
>= | 大于等于 |
< | 小于 |
<= | 小于等于 |
= | 等于 |
<> 或 != | 不等于 |
between … and … | 在某个范围之内(含最小、最大值) |
in(…) | 在in之后的列表中的值,多选一 |
like 占位符 | 模糊匹配(_匹配单个字符, %匹配任意个字符) |
is null | 是null |
常用的逻辑运算符如下:
逻辑运算符 | 功能 |
---|---|
and 或 && | 并且 (多个条件同时成立) |
or 或 || | 或者 (多个条件任意一个成立) |
not 或 ! | 非 , 不是 |
案例1:查询 姓名 为 杨逍 的员工
select *
from tb_emp
where name = '杨逍';
运行结果
案例2:查询 id小于等于5 的员工信息
select *
from tb_emp
where id <= 5;
运行结果
案例3:查询 没有分配职位 的员工信息
select *
from tb_emp
where job is null;
运行结果
注意:查询为NULL的数据时,不能使用 = null
案例4:查询 入职时间在‘2000-01-01’(包含)到‘2010-01-01’(包含)之间的员工信息
-- 方法1
select *
from tb_emp
where entrydate >= '2000-01-01'
and entrydate <= '2010-01-01';
-- 方法2
select *
from tb_emp
where entrydate between '2000-01-01' and '2010-01-01';
运行结果
案例5:查询 职位是 2(讲师),3(学工主管),4(教研主管)的员工信息
-- 方式1:or
select *
from tb_emp
where job = 3
or job = 4
or job = 2;
-- 方式2:in
select *
from tb_emp
where job in (2, 3, 4);
运行结果
案例6:查询 姓名 为两个字的员工信息
select *
from tb_emp
where name like '__';
运行结果
案例7:查询 姓 ‘张’ 的员工信息
select *
from tb_emp
where name like '张%';
运行结果
四、聚合函数
之前我们做的查询都是横向查询,就是根据条件一条一条的进行判断,而使用聚合函数查询就是纵向查询,它是对一列的值进行计算,然后返回一个结果。(将一列数据作为一个整体,进行纵向计算)
语法:
select 聚合函数(字段列表) from 表名;
注意:聚合函数会忽略空值,对NULL值不做统计
常见的聚合函数:
函数 | 功能 |
---|---|
count | 统计数量 |
max | 最大值 |
min | 最小值 |
avg | 平均值 |
sum | 求和 |
count :按照列去统计有多少行数据。
- 在根据指定的列统计的时候,如果这一列中有null的行,该行不会被统计在其中。
sum :计算指定列的数值和,如果不是数值类型,那么计算结果为0
max :计算指定列的最大值
min :计算指定列的最小值
avg :计算指定列的平均值
案例1:统计该企业员工的数量
# count(字段)
select count(id)
from tb_emp;
select count(job)
from tb_emp;
# count(常量)
select count(0)
from tb_emp;
# count(*) 推荐此写法(MySQL底层进行了优化)
select count(*)
from tb_emp;
案例2:统计该企业最早入职的员工
select min(entrydate) from tb_emp;
运行结果
五、分组查询
分组:按照某一列或者某几列,把相同的数据进行合并输出,
分组其实就是按列进行分类(指定列下相同的数据归为一类),然后可以对分类完的数据进行合并计算。
分组查询通常会使用聚合函数进行计算。
语法:
select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];
案例1:根据性别分组,统计男性和女性员工的数量
select gender, count(*)
from tb_emp
group by gender;
运行结果
案例2:查询入职时间在 ‘2015-01-01’ (包含) 以前的员工 , 并对结果根据职位分组 , 获取员工数量大于等于2的职位
select job, count(*)
from tb_emp
where entrydate <= '2015-01-01'
group by job
having count(*) >= 2;
运行结果
注意事项:
分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义
执行顺序:where > 聚合函数 > having
六、排序查询
排序在日常生活中是非常常见的一个操作,有升序排序,也有降序排序。
语法:
select 字段列表
from 表名
[where 条件列表]
[group by 分组字段]
order by 字段1 排序方式1,字段2 排序方式2...;
排序方式:
ASC:升序
DESC:降序
案例1:根据入职时间,对员工进行升序排列
select *
from tb_emp
order by entrydate asc;
运行结果
案例2:根据入职时间对公司进行升序排序,入职时间相同,再按照更新时间进行降序排列
select *
from tb_emp
order by entrydate, update_time desc;
运行结果
注意事项:如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序。
七、分页查询
分页查询语法:
select 字段列表 from 表名 limit 启示索引,查询记录数;
案例1:从起始索引0开始查询员工数据,每页展示5条记录
select *
from tb_emp
limit 0,5;
运行结果
案例3:查询第3页的员工数据,每页展示5条数据
select *
from tb_emp
limit 10,5;
运行结果
注意事项:
- 起始索引从0开始。 计算公式 : 起始索引 = (查询页码 - 1)* 每页显示记录数
- 分页查询是数据库的方言,不同的数据库有不同的实现,MySQL中是LIMIT
- 如果查询的是第一页数据,起始索引可以省略,直接简写为 limit 条数
八、综合案例
分析:根据输入的条件,查询第1页数据
在员工管理的列表上方有一些查询条件:员工姓名、员工性别,员工入职时间(开始时间~结束时间)
- 姓名:张
- 性别:男
- 入职时间:2000-01-01 ~ 2015-12-31
除了查询条件外,在列表的下面还有一个分页条,这就涉及到了分页查询
- 查询第1页数据(每页显示10条数据)
基于查询的结果,按照修改时间进行降序排序
结论:条件查询 + 分页查询 + 排序查询
SQL语句代码:
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 0,10;
运行结果:
总结
在本文中,我们深入探讨了数据库操作的核心之一——DQL(Data Query Language)或称为数据查询语言,专注于单表查询的各种技巧和策略。通过详细解析SELECT语句的不同用法,包括基本的列选择、条件筛选、聚合函数、分组以及排序等,我们得以理解如何从单一数据表中检索出精确且有用的信息。