四.DQL
DQL英文全称是Data Query Language(数据查询语言),数据查询语言,用来查询数据库中表的记 录。
4.1 测试数据的准备
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' ),
( 3, '00003', '韦一笑', '男', 38, '123456789712345670', '上海', '2005-08-01' ),
( 4, '00004', '赵敏', '女', 18, '123456757123845670', '北京', '2009-12-01' ),
( 5, '00005', '小昭', '女', 16, '123456769012345678', '上海', '2007-07-01' ),
( 6, '00006', '杨逍', '男', 28, '12345678931234567X', '北京', '2006-01-01' ),
( 7, '00007', '范瑶', '男', 40, '123456789212345670', '北京', '2005-05-01' ),
( 8, '00008', '黛绮丝', '女', 38, '123456157123645670', '天津', '2015-05-01' ),
( 9, '00009', '范凉凉', '女', 45, '123156789012345678', '北京', '2010-04-01' ),
( 10, '00010', '陈友谅', '男', 53, '123456789012345670', '上海', '2011-01-01' ),
( 11, '00011', '张士诚', '男', 55, '123567897123465670', '江苏', '2015-05-01' ),
( 12, '00012', '常遇春', '男', 32, '123446757152345670', '北京', '2004-02-01' ),
( 13, '00013', '张三丰', '男', 88, '123656789012345678', '江苏', '2020-11-01' ),
( 14, '00014', '灭绝', '女', 65, '123456719012345670', '西安', '2019-05-01' ),
( 15, '00015', '胡青牛', '男', 70, '12345674971234567X', '西安', '2018-04-01' ),
( 16, '00016', '周芷若', '女', 18, NULL, '北京', '2012-06-01' );
4.2 基本语法
DQL 查询语句,语法结构如下:
SELECT
字段列表
FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段列表
HAVING
分组后条件列表
ORDER BY
排序字段列表
LIMIT
分页参数
4.3 基础查询
在基本查询的DQL语句中,不带任何的查询条件,查询的语法如下:
- 查询多个字段
SELECT 字段1, 字段2, 字段3 ... FROM 表名 ;
SELECT * FROM 表名 ;
注意 : * 号代表查询所有字段,在实际开发中尽量少用(不直观、影响效率)。
- 字段设置别名
SELECT 字段1 [ AS 别名1 ] , 字段2 [ AS 别名2 ] ... FROM 表名;
- 去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;
-- 查询指定字段 name,workno,age
SELECT NAME,workno,age FROM emp;
-- 查询返回所有字段
SELECT * FROM emp;
-- 查询所有员工的工作地址(去重)
SELECT DISTINCT workaddress AS '工作地址' FROM emp;
4.4 条件查询
-
语法
SELECT 字段列表 FROM 表名 WHERE 条件列表 ;
-
条件
常用的比较运算符如下:
比较运算符 功能 > 大于 >= 大于等于 < 小于 <= 小于等于 = 等于 <> 或 != 不等于 BETWEEN … AND … 在某一个范围之内,包含最小和最大值 IN(…) 在IN之后的列表中的值多选一,相当于OR操作 LIKE 占位符 模糊匹配(**_**匹配单个字符, **%**匹配任意个字符) IS NULL 是NULL 常用的逻辑运算符如下:
逻辑运算符 功能 AND 或 && 并且 (多个条件同时成立) OR 或 || 或者 (多个条件任意一个成立) NOT 或 ! 非 , 不是
案例:
-- 查询年龄等于88的员工信息
SELECT * FROM emp WHERE age = 88;
-- 查询年龄小于20的员工信息
SELECT * FROM emp WHERE age<20;
-- 查询没有身份证号的员工信息
SELECT * FROM emp WHERE idcard IS NULL;
-- 查询年龄不等于88的员工信息
SELECT * FROM emp WHERE age <> 88;
-- 查询年龄在15-20岁之间的信息(包含)
SELECT * FROM emp WHERE age BETWEEN 15 AND 20;
SELECT * FROM emp WHERE age >= 15 AND age <= 20;
-- 查询性别为女且年龄小于25的员工信息
SELECT * FROM emp WHERE gender = '女' AND age < 25;
-- 查询年龄等于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的员工信息
SELECT * FROM emp WHERE idcard LIKE '%X';
4.5 聚合函数
-
常见的聚合函数
函数 功能 count 统计数量 max 最大值 min 最小值 avg 平均值 sum 求和 -
语法
SELECT 聚合函数(字段列表) FROM 表名 ;
注意:NULL值是不参与所有聚合函数的运算的。
案例:
-- 统计员工数量
SELECT COUNT(*) FROM emp;
SELECT COUNT(1) FROM emp;
-- 统计员工的平均年龄
SELECT AVG(age) FROM emp;
-- 统计员工的最大年龄
SELECT MAX(age) FROM emp;
-- 统计员工的最小年龄
SELECT MIN(age) FROM emp;
-- 统计西安地区的员工年龄之和
SELECT SUM(age) FROM emp WHERE workaddress = '西安';
4.6 分组查询
-
语法
SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组后过滤条件 ];
-
where
与having
区别- 执行时机不同:where是分组之前进行过滤,不满足where条件,不参与分组;而having是分组 之后对结果进行过滤。
- 判断条件不同:where不能对聚合函数进行判断,而having可以。
注意事项:
- 执行顺序: where > 聚合函数 > having 。
- 支持多字段分组, 具体语法为 : group by columnA,columnB
案例:
-- 根据性别进行分组,统计男性员工和女性员工的数量
SELECT gender,COUNT(*) AS '人员数量' FROM emp GROUP BY gender;
-- 根据性别进行分组,统计男性员工和女性员工的平均年龄
SELECT gender,AVG(age) AS '平均年龄' FROM emp GROUP BY gender;
-- 查询年龄小于45的员工 , 并根据工作地址分组 , 获取员工数量大于等于3的工作地址
SELECT
workaddress,
COUNT(*) AS address_count
FROM
emp
WHERE
age < 45 GROUP BY workaddress HAVING address_count >= 3;
-- 统计各个工作地址上班的男性及女性员工的数量
SELECT workaddress,gender,COUNT(*) AS '数量' FROM emp GROUP BY workaddress,gender;
4.7 排序查询
-
语法
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2 ;
-
排序方式
- ASC : 升序(默认)
- DESC : 降序
注意事项:
- 如果是升序, 可以不指定排序方式ASC ;
- 如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序 ;
案例:
-- 根据年龄对公司的员工进行升序排序
SELECT * FROM emp ORDER BY age ASC;
-- 根据入职时间, 对员工进行降序排序
SELECT * FROM emp ORDER BY entrydate DESC;
-- 根据年龄对公司的员工进行升序排序 , 年龄相同 , 再按照入职时间进行降序排序
SELECT * FROM emp ORDER BY age ASC,entrydate DESC;
4.8 分页查询
-
语法
SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数 ;
注意事项:
- 起始索引从0开始,起始索引 = (查询页码 - 1)* 每页显示记录数。
案例:
-- 查询第1页员工数据, 每页展示10条记录
SELECT * FROM emp LIMIT 0,10;
-- 查询第2页员工数据, 每页展示10条记录
SELECT * FROM emp LIMIT 10,10;
4.9 案例
-- 查询年龄为20,21,22,23岁的女员工信息
SELECT * FROM emp WHERE gender = '女' AND age IN(20,21,22,23);
-- 查询性别为 男 ,并且年龄在 20-40 岁(含)以内的姓名为三个字的员工
SELECT
*
FROM
emp
WHERE
gender = '男'
AND NAME LIKE '___'
AND (age BETWEEN 20 AND 40 );
-- 统计员工表中, 年龄小于60岁的 , 男性员工和女性员工的人数。
SELECT gender,COUNT(*) AS '员工人数' FROM emp WHERE age<60 GROUP BY gender;
-- 查询所有年龄小于等于35岁员工的姓名和年龄,并对查询结果按年龄升序排序,如果年龄相同按入职时间降序排序。
SELECT
e.`NAME`,
e.age
FROM
emp e
WHERE
e.age <= 35
ORDER BY
age ASC,
entrydate DESC;
-- 查询性别为男,且年龄在20-40 岁(含)以内的前5个员工信息,对查询的结果按年龄升序排序,年龄相同按入职时间升序排序。
SELECT
*
FROM
emp e
WHERE
e.gender = '男'
AND ( e.age BETWEEN 20 AND 40 )
ORDER BY
e.age ASC,
e.entrydate ASC
LIMIT 5;
4.10 执行顺序(重要)
注:如果要验证其执行顺序采用别名方式进行验证
例如:
select e.name , e.age from emp e where e.age > 15 order by age asc;
执行上述SQL后可以得到正确的结果,说明了from先执行,那么要判断where
还是select
先执行的话在select
中设置别名而where
中不设置,运行成功就代表select
先执行,反之。
DQL语句的执行顺序为: from … where … group by … having … select … order by … limit …