DQL(数据查询语言)
- DQL英文全称是Data Query Language(数据查询语言),数据查询语言,用来查询数据库中表的记录。
- 查询关键字: SELECT
- 在一个正常的业务系统中,查询操作的频次是要远高于增删改的,当我们去访问企业官网、电商网站,在这些网站中我们所看到的数据,实际都是需要从数据库中查询并展示的。而且在查询的过程中,可能还会涉及到条件、排序、分页等操作。
- 数据准备:
CREATE TABLE emp(id INT COMMENT '编号',workno VARCHAR(10) COMMENT '工号',NAME VARCHAR(10) COMMENT '姓名',gender CHAR(1) COMMENT '性别',age TINYINT UNSIGNED COMMENT '年龄',idcard CHAR(18) COMMENT '身份证号',workaddress VARCHAR(50) COMMENT '工作地址',entrydate DATE COMMENT '入职时间')COMMENT '员工表';
INSERT INTO emp (id, workno, NAME, gender, age, idcard, workaddress, entrydate) VALUES (1, '00001', '柳岩666', '女', 20, '123456789012345678', '北京', '2000-01-01');
INSERT INTO emp (id, workno, NAME, gender, age, idcard, workaddress, entrydate) VALUES (2, '00002', '张无忌', '男', 18, '123456789012345670', '北京', '2005-09-01');
INSERT INTO emp (id, workno, NAME, gender, age, idcard, workaddress, entrydate) VALUES (3, '00003', '韦一笑', '男', 38, '123456789712345670', '上海', '2005-08-01');
INSERT INTO emp (id, workno, NAME, gender, age, idcard, workaddress, entrydate) VALUES (4, '00004', '赵敏', '女', 18, '123456757123845670', '北京', '2009-12-01');
INSERT INTO emp (id, workno, NAME, gender, age, idcard, workaddress, entrydate) VALUES (5, '00005', '小昭', '女', 16, '123456769012345678', '上海', '2007-07-01');
INSERT INTO emp (id, workno, NAME, gender, age, idcard, workaddress, entrydate) VALUES (6, '00006', '杨逍', '男', 28, '12345678931234567X', '北京', '2006-01-01');
INSERT INTO emp (id, workno, NAME, gender, age, idcard, workaddress, entrydate) VALUES (7, '00007', '范瑶', '男', 40, '123456789212345670', '北京', '2005-05-01');
INSERT INTO emp (id, workno, NAME, gender, age, idcard, workaddress, entrydate) VALUES (8, '00008', '黛绮丝', '女', 38, '123456157123645670', '天津', '2015-05-01');
INSERT INTO emp (id, workno, NAME, gender, age, idcard, workaddress, entrydate) VALUES (9, '00009', '范凉凉', '女', 45, '123156789012345678', '北京', '2010-04-01');
INSERT INTO emp (id, workno, NAME, gender, age, idcard, workaddress, entrydate) VALUES (10, '00010', '陈友谅', '男', 53, '123456789012345670', '上海', '2011-01-01');
INSERT INTO emp (id, workno, NAME, gender, age, idcard, workaddress, entrydate) VALUES (11, '00011', '张士诚', '男', 55, '123567897123465670', '江苏', '2015-05-01');
INSERT INTO emp (id, workno, NAME, gender, age, idcard, workaddress, entrydate) VALUES (12, '00012', '常遇春', '男', 32, '123446757152345670', '北京', '2004-02-01');
INSERT INTO emp (id, workno, NAME, gender, age, idcard, workaddress, entrydate) VALUES (13, '00013', '张三丰', '男', 88, '123656789012345678', '江苏', '2020-11-01');
INSERT INTO emp (id, workno, NAME, gender, age, idcard, workaddress, entrydate) VALUES (14, '00014', '灭绝', '女', 65, '123456719012345670', '西安', '2019-05-01');
INSERT INTO emp (id, workno, NAME, gender, age, idcard, workaddress, entrydate) VALUES (15, '00015', '胡青牛', '男', 70, '12345674971234567X', '西安', '2018-04-01');
INSERT INTO emp (id, workno, NAME, gender, age, idcard, workaddress, entrydate) VALUES (16, '00016', '周芷若', '女', 18, NULL, '北京', '2012-06-01');
一、基本语法
SELECT字段列表
FROM表名列表
WHERE条件列表
GROUP BY分组字段列表
HAVING分组后条件列表
ORDER BY排序字段列表
LIMIT分页参数
1、基础查询(不带任何条件)—查询多个字段
-- 查询指定字段SELECT 字段1, 字段2, 字段3 ... FROM 表名 ;-- 查询所有字段SELECT * FROM 表名 ;
注意 : * 号代表查询所有字段,在实际开发中尽量少用(不直观、影响效率)
-- 查询emp表的id字段
select id from emp;
-- 查询emp表的所有字段
select * from emp;
2、基础查询(不带任何条件)—设置字段别名
-- 设置别名可以使用as关键字也可以省略as
SELECT 字段1 [ AS 别名1 ] , 字段2 [ AS 别名2 ] ... FROM 表名; -- 省略asSELECT 字段1 [ 别名1 ] , 字段2 [ 别名2 ] ... FROM 表名;
as可以省略
select id as eid from emp;-- 这个和上面的作用一样的,省略了as
select id eid from emp;
3、基础查询(不带任何条件)—去除重复记录
DISTINCT关键字:用于从查询结果中删除重复的行,确保返回的每一行都是唯一的。它通常用于 SELECT 语句中,以获取不同(唯一)的记录集合
- DISTINCT 可以单列使用
- DISTINCT 也可以与多个列一起使用,以返回基于这些列组合的唯一行
SELECT DISTINCT 字段列表 FROM 表名;
- distinct单列
-- 查询公司员工的上班地址有哪些(不要重复),'工作地址'是别名
select distinct workaddress '工作地址' from emp;
- distinct多列
-- 查询公司男员工的上班地址有哪些(不要重复),'工作地址'是别名
select distinct gender '性别', workaddress '工作地址' from emp;
- DISTINCT 是一个有用的关键字,用于从查询结果中去除重复的行。然而,它可能会增加查询的复杂性,并可能影响性能,因此在使用时需要谨慎
4、条件查询
- 语法:
SELECT 字段列表 FROM 表名 WHERE 条件列表 ;
4.1、常用的比较运算符
- 判断字段是不是null的时候,就可以使用is null或者是is not null来进行条件判断,而不是用=null或!=null。
-- 查询身份证号码为null的数据
select * from emp where idcard is null;
-
=
运算符不能用于检查 NULL 值,因为 NULL 表示未知,而未知与任何值(包括它自己)都不相等。因此,column = NULL 总是返回 FALSE。 -
同样地,
<>
运算符也不能用于检查 NULL 值,因为 NULL 不等于任何值,包括它自己。因此,column <> NULL 也总是返回 FALSE。 -
is null和 is not null的影响——索引和性能:
- 在某些情况下,使用 IS NULL 或 IS NOT NULL 条件可能会影响查询性能,特别是当涉及到大表或索引时。因此,在设计数据库和编写查询时,应考虑到这一点。
- 默认值:在创建表时,可以为列指定默认值。如果未为列提供值,则MySQL将使用默认值(如果已指定)。如果未指定默认值且列允许 NULL,则MySQL将插入 NULL 值。
- 在某些情况下,使用 IS NULL 或 IS NOT NULL 条件可能会影响查询性能,特别是当涉及到大表或索引时。因此,在设计数据库和编写查询时,应考虑到这一点。
4.2、常用的的逻辑运算符
-- 查询没有身份证号的员工信息select * from emp where idcard is null;-- 查询有身份证号的员工信息
select * from emp where idcard is not null;-- 查询年龄不等于 28 的员工信息select * from emp where age != 28;select * from emp where age <> 28;-- 查询年龄在15岁(包含) 到 20岁(包含)之间的员工信息
select * from emp where age >= 15 && age <= 20;select * from emp where age >= 15 and age <= 20;select * from emp where age between 15 and 20;-- 查询年龄等于18 或 20 或 40 的员工信息select * from emp where age = 18 or age = 20 or age =40;select * from emp where age in(18,20,40);-- 查询姓名为两个字的员工信息(_表示一个占位符,%表示匹配任意个字符)
select * from emp where name like '__';-- 查询身份证号最后一位是X的员工信息(%表示前面可以有n个字符)select * from emp where idcard like '%X';
5、聚合函数
- 聚合函数:将一列数据作为一个整体,进行纵向计算 。
SELECT 聚合函数(字段列表) FROM 表名 ;
注意 : NULL值是不参与所有聚合函数运算的。
-- 统计的是总记录数(常用)
select count(*) from emp; -- 统计的是idcard字段不为null的记录数
select count(idcard) from emp; -- 统计该企业员工的平均年龄
select avg(age) from emp;-- 统计该企业员工的最大年龄
select max(age) from emp;-- 统计该企业员工的最小年龄select min(age) from emp;-- 统计西安地区员工的年龄之和select sum(age) from emp where workaddress = '西安';
6、分组查询
SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组后过滤条件 ];
- where与having区别:
- 执行时机不同:
where是分组之前进行过滤
,不满足where条件,不参与分组;而having是分组之后对结果进行过滤
。 - 判断条件不同:
where不能对聚合函数进行判断
,而having可以。
- 执行时机不同:
分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义。
执行顺序: where > 聚合函数 > having 。
- 支持多字段分组, 具体语法为 : group by columnA,columnB,…
-- 根据性别分组 , 统计男性员工 和 女性员工的数量
select gender, count(*) from emp group by gender ;
-- 查询年龄小于45的员工 , 并根据工作地址分组 , 获取员工数量大于等于3的工作地址
select workaddress, count(*) address_count from emp where age < 45 group by
workaddress having address_count >= 3;
7、排序查询
- 排序在日常开发中是非常常见的一个操作,有升序排序(ASC,默认升序),也有降序排序(DESC)。
- 语法:
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2 ;
-
排序方式:
- ASC(ascend) : 升序(默认值)
- DESC(descend): 降序
-
注意事项:
如果是升序, 可以不指定排序方式ASC ;
如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序 ;
-- 根据年龄对公司的员工进行升序排序select * from emp order by age asc;select * from emp order by age;-- 根据入职时间, 对员工进行降序排序
select * from emp order by entrydate desc;-- 根据年龄对公司的员工进行升序排序 , 年龄相同 , 再按照入职时间进行降序排序select * from emp order by age asc , entrydate desc;
8、分页查询
- 分页操作在业务系统开发时,也是非常常见的一个功能,我们在网站中看到的各种各样的分页条,后台都需要借助于数据库的分页操作。
- 语法:
SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数 ;
- 注意事项:
起始索引从0开始,起始索引 = (查询页码 - 1)* 每页显示记录数。
分页查询是数据库的方言,不同的数据库有不同的实现,MySQL中是LIMIT。
如果查询的是第一页数据,起始索引可以省略,直接简写为 limit 10。
-- 查询第1页员工数据, 每页展示10条记录select * from emp limit 0,10;select * from emp limit 10; -- 如果查询的是第一页数据,起始索引可以省略,直接简写为 limit 10-- 查询第2页员工数据, 每页展示10条记录 --------> (页码-1)*页展示记录数select * from emp limit 10,10;
9、DQL语句在执行时的执行顺序
- 1、证明from比where和select先执行
select e.age from emp e where e.id = 1; -- 这里使用表的别名可以说明是from先执行的,而where和select是后执行的,因为如果from不限执行,表别名e就无法使用了
- 2、证明where比select先执行
select e.age a from emp e where e.id = 1 and a = 1;
-
3、证明select比order by 先执行
-
4、因为group by是对where条件过滤后的数据进行分组,而select是从分组的数据中选择需要的列,所以group by先执行
-
5、limit是对最后的结果限制返回的行数,所以limit是最后执行的。
-
总结:
select的执行顺序为
:- 执行第一:FROM:首先确定数据源,即表或视图。
- 执行第二:WHERE:对数据源进行过滤,只保留符合条件的行。
- 执行第三:GROUP BY:对过滤后的数据进行分组。
- 执行第四:HAVING:对分组后的数据进行进一步过滤(如果有 HAVING 子句)。
- 执行第五:SELECT:选择需要返回的列,并计算表达式(包括聚合函数)。
- 执行第六:ORDER BY:对结果进行排序(如果有 ORDER BY 子句)。
- 执行第七:LIMIT:限制返回的行数(如果有 LIMIT 子句)。