1.3 数据库初识 DQL

• 返回目录 : MySQL 学习笔记
• 上一章 1.2数据库初识 DQL
• 下一章 1.4数据库初识 DCL

---

数据库初识 DQL

DQL 介绍

DLQ 英文全称是 Data Query Language (数据查询语言)，用来查询数据库中表的记录。

查询关键字:SELECT

• DQL 关键字后跟的内容

  1. SELECT 跟 字段列表
  2. FROM 跟 表名列表
  3. WHERE 跟 条件列表
  4. GROUP BY 跟 分组字段列表
  5. HAVING 跟 分组后条件列表
  6. ORDER BY 跟 排序字段列表
  7. LIMIT 跟 分页参数

• 语法 SELECT 字段 FROM 表名 WHERE 条件 GROUP BY 分组字段 ORDER BY 排序字段 LIMIT 分页参数

• 此关键字顺序也是语句的语法顺序.(语法顺序 不等于 执行顺序)

基本查询

• 查询多个字段

-- 查询表中所有字段
SELECT * FROM 表名;

-- 查询表中指定的多个字段
SELECT 字段1,字段2,字段3,... FROM 表名;

• 给查询的字段设置别名

关键字 AS, AS 关键字可省略

SELECT 字段1 [AS 别名1],字段2 [AS 别名2],... FROM 表名;

-- AS 关键字可省略
SELECT 字段1 [别名1],字段2 [别名2],... FROM 表名;

• 去除重复记录 DISTIINCT

SELECT DISTINCT 字段列表 FROM 表名;

条件查询

• 语法 SELECT 字段列表 FROM 表名 WHERE 条件列表;
• 条件 多个比较条件可用逻辑运算符组装

  1. 比较运算符

     序号	比较运算符	功能
     1	大于
     2	>=	大于等于
     3	<	小于
     4	<=	小于等于
     5	=	等于
     6	<> 或 !=	不等于
     7	BETWEEN ... AND ...	在某个范围之内(含最小、最大值)
     8	IN(...)	在IN之后的列表中的值，多选一
     9	LIKE 占位符	模糊匹配（_匹配单个字符,%匹配任意个字符）
     10	IS NULL	是NULL

  2. 逻辑运算符

     逻辑运算符	功能
     AND 或 &&	并且(多个条件同时成立)
     OR 或 ||	或者(多个条件任意一个成立)
     NOT 或 !	非,不是

聚合函数

介绍 : 将一列数据作为一个整体,进行纵向计算.

常见聚合函数 : COUNT,MAX,MIN,AVG,SUM,...;

函数	功能
COUNT	统计数量
MAX	最大值
MIN	最小值
AVG	平均值
SUM	求和

• 语法 SELECT 聚合函数(字段列表) FROM 表名;

• 注 : 所有的NULL值不参与聚合函数的计算;

分组查询

• 语法

SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];

• WHERE 与 HAVING 区别

  1. 执行实际不同 : WHERE是分组之前进行过滤,不满足WHERE条件,不参与分组;而HAVING是分组之后对结果进行过滤;
  2. 判断条件不同 : WHERE不能对聚合函进行判断,而HAVING可以;

• 注意

  • 执行顺序 : WHERE > 聚合函数 > HAVING
  • 分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义

排序查询

• 语法 SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;

• 排序方式

  • 升序 : ASC (默认值)
  • 降序 : DESC

• 注意 : 如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序;

分页查询

• 语法 SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;

• 注意

  • 起始索引从0开始,起始索引 = (查询页码-1) * 每页显示记录数;
  • 分页查询是数据库的方言,不同的数据库有不同的实现,MySQL中是LIMIT.
  • 如果查询的是第一页数据,起始索引可以省略,直接简写为 LIMIT 10(10为每页10条,如每页20,则 LIMIT 20).

执行顺序

1. FROM 表名
2. WHERE 条件列表
3. GROUP BY 分组字段列表
4. HAVING 分组后条件列表
5. SELECT 字段列表
6. ORDER BY 排序字段列表
7. LIMIT 分页参数

• 通过别名方式 验证执行顺序

SELECT e.name ename , e.age eage FROM emp e WHERE e.age > 15 ORDER BY eage ASE;

• 先执行FROM生成 emp 别名 e ,所以在它之后的关键字可使用别名e
• SELECT在第5个执行,所以1.FROM,2.WHERE,3.GROUP BY,4.HAVING 使用字段别名时发生报错,不可用.
• 但 6.ORDER BY,7.LIMIT 可使用 字段别名 并且正常运行.

总结

• 基础查询 SELECT 字段列表 FROM 表名;

  1. 字段列表可是用 * 来查询全部,但非常不建议,应明确指定查询的字段名
  2. 字段名 可使用 别名 SELECT 字段1 AS 别名1,关键字 AS 可省略 SELECT 字段1 别名1

• 条件查询 SELECT 字段列表 FROM 表名 WHERE 条件列表;

  1. >,>=,<,<=,=,<>,LIKT,BETWEEN...AND...,IN (...).
  2. 多个条件组装可使用AND或者OR进行连接.
  3. 多条件时阅读不清晰,可使用()圈选条件

• 分组查询 SELECT 字段列表 FROM 表名 WHERE 条件列表 GROUP BY 分组字段 HAVING 分组后条件列表;

  1. WHERE的条件列表,在分组之前执行,
  2. HAVING的条件列表,在分组之后执行
  3. 使用GROUP BY 进行分组
  4. 使用HAVING对分组后的数据进行添加过滤条件列表

• 排序查询 SELECT 字段列表 FROM 表名 WHERE 条件列表 GROUP BY 分组字段 HAVING 分组后条件列表 ORDER BY 排序条件;

  1. 排序可对多个字段进行排序.
  2. 先按照第一个指定字段排序,如果第一个指定字段相同,则使用第二个字段的排序规则.
  3. 排序分为 升序ASC,降序DESC,其中ASC为系统默认值,可省略.

• 分页查询 SELECT 字段列表 FROM 表名 WHERE 条件列表 GROUP BY 分组字段 HAVING 分组后条件列表 ORDER BY 排序条件 LIMIT 分页参数;

  1. LIMIT 为MySQL方言 ,LIMIT无法完全适用其他数据库,其他数据库有别的分页方式.
  2. 分页查询,有2个参数,起始索引,查询数,
     • 起始索引 从0开始
     • 查询数 一般为10 / 20.

---

• 返回目录 : MySQL 学习笔记
• 上一章 1.2数据库初识 DQL
• 下一章 1.4数据库初识 DCL

---

练习

准备数据

-- 删除旧表
DROP TABLE IF EXISTS emp;

-- 创建员工信息表
CREATE TABLE emp
(
    id              INT              COMMENT '编号',
    work_no         VARCHAR(10)      COMMENT '工号',
    name            VARCHAR(10)      COMMENT '姓名',
    gender          CHAR(1)          COMMENT '性别',
    age             TINYINT UNSIGNED COMMENT '年龄',
    id_card         CHAR(18)         COMMENT '身份证号',
    work_address    VARCHAR(50)      COMMENT '工作地址',
    entry_date      DATE             COMMENT '入职时间'
) COMMENT '员工表';

-- 插入数据
INSERT INTO emp (id, work_no, name, gender, age, id_card, work_address, entry_date)
VALUES (1, '1', '张1', '女', 20, '123456789012345678', '北京', '2000-01-01'),
       (2, '2', '张2', '男', 18, '123456789012345670', '北京', '2005-09-01'),
       (3, '3', '张3', '男', 38, '123456789012345670', '上海', '2005-08-01'),
       (4, '4', '张4', '女', 18, '123456789012345670', '北京', '2009-12-01'),
       (5, '5', '张5', '女', 16, '123456789012345678', '上海', '2007-07-01'),
       (6, '6', '张6', '男', 28, '12345678901234567X', '北京', '2006-01-01'),
       (7, '7', '张7', '男', 40, '123456789012345670', '北京', '2005-05-01'),
       (8, '8', '张8', '女', 38, '123456789012345670', '天津', '2015-05-01'),
       (9, '9', '张9', '女', 45, '123456789012345678', '北京', '2010-04-01'),
       (10, '10', '张10', '男', 53, '123456789012345670', '上海', '2011-01-01'),
       (11, '11', '张11', '男', 55, '123456789012345670', '江苏', '2015-05-01'),
       (12, '12', '张12', '男', 32, '123456789012345670', '北京', '2004-02-01'),
       (13, '13', '张13', '男', 88, '123456789012345678', '江苏', '2020-11-01'),
       (14, '14', '张14', '女', 65, '123456789012345670', '西安', '2019-05-01'),
       (15, '15', '张15', '男', 70, '12345678901234567X', '西安', '2018-04-01'),
       (16, '16', '张16', '女', 18, null, '北京', '2012-06-01');

1.基本查询

题1.1

• 问 : 查询指定字段 name , work_no , age 返回;

• 答 SELECT name,work_no,age FROM emp;

---

题1.2

• 问 : 查询所有字段返回;

• 答1 SELECT * FROM emp;

• 答2 SELECT id, work_no, name, gender, age, id_card, work_address, entry_date FROM emp;

---

题1.3

• 问 : 查询所有员工的工作地址 , 查询结果使用别名;

• 答1 SELECT work_address '工作地址' FROM emp;

• 答2 SELECT work_address AS '工作地址' FROM emp;

---

题1.4

• 问 : 查询所有员工的工作地址 , 结果需要去重;

• 答 SELECT DISTINCT work_address AS '工作地址' FROM emp;

---

2.条件查询

题2.1

• 问 : 查询年龄等于88的员工信息

• 答 SELECT * FROM emp WHERE age = 88;

---

题2.2

• 问 : 查询年龄小于20的员工信息

• 答 SELECT * FROM emp WHERE age < 20

---

题2.3

• 问 : 查询年龄小于等于20的员工信息

• 答 SELECT * FROM emp WHERE age <= 20;

---

题2.4

• 问 : 查询 没有身份证号 的员工信息

• 答 SELECT * FROM emp WHERE id_card IS NULL;

---

题2.5

• 问 : 查询 有身份证号 的员工信息

• 答 SELECT * FROM emp WHERE id_card IS NOT NULL;

---

题2.6

• 问 : 查询年龄不等于88的员工信息

• 答

SELECT * FROM emp WHERE age != 88;
-- or
SELECT * FROM emp WHERE age <> 88;

---

题2.7

• 问 : 查询年龄在15(包含)到20(包含)之间的员工信息

• 答

SELECT * FROM emp WHERE age >= 15 && age <= 20;
-- or
SELECT * FROM emp WHERE age >= 15 AND age <= 20;
-- or
SELECT * FROM emp WHERE age BETWEEN 15 AND 20;

---

题2.8

• 问 : 查询性别为女且年龄小于25的员工信息

• 答

SELECT * FROM emp WHERE gender = '女' && age < 25;
-- or
SELECT * FROM emp WHERE gender = '女' AND age < 25;

---

题2.9

• 问 : 查询年龄等于18或20或40的员工信息

• 答

SELECT * FROM emp WHERE age = 18 || age = 20 || age = 40;
-- or
SELECT * FROM emp WHERE age = 18 OR age = 20 OR age = 40;
-- or
SELECT * FROM emp WHERE age IN (18, 20, 40);

---

题2.10

• 问 : 查询姓名为2个字的员工信息

• 答 SELECT * FROM emp WHERE name LIKE '__';

---

题2.11

• 问 : 查询身份证号最后一位是X的员工信息

• 答

SELECT * FROM emp WHERE id_card LIKE '%X';
-- or 因为身份证号为18位,所以前面添加 17个 _ 即可
SELECT * FROM emp WHERE id_card LIKE '_________________X';

---

3.聚合函数

题3.1

• 问 : 统计该企业员工数量

• 答 : 此处注意 如果指定字段,只处理内容不为null.

• 例 : 16条数据,15条有身份证号码,1条没有身份证号码. 如COUNT(id_card) 查询数量为15;

-- 统计这个表的 总数 16
SELECT COUNT(*) FROM emp;
-- 统计这个表的id字段不为null 总数 16
SELECT COUNT(id) FROM emp;
-- 统计这个表的id字段不为null 总数 15
SELECT COUNT(id_card) FROM emp;

---

题3.2

• 问 : 统计该企业员工的平均年龄

• 答 : SELECT AVG(age) FROM emp;

---

题3.3

• 问 : 统计该企业员工的最大年龄

• 答 : SELECT MAX(age) FROM emp;

---

题3.4

• 问 : 统计该企业员工的最小年龄

• 答 SELECT MIN(age) FROM emp;

---

题3.5

• 问 : 统计西安地区员工年龄之和;

• 答 SELECT SUM(age) FROM emp WHERE work_address = '西安';

---

4.分组查询

题4.1

• 问 : 根据性别分组,统计男性员工和女性员工的数量

• 答 : SELECT gender,COUNT(*) FROM emp GROUP BY gender;

---

题4.2

• 问 : 根据性别分组,统计男性员工和女性员工的平均年龄

• 答 : SELECT gender,COUNT(*) FROM emp GROUP BY age;

---

题4.3

• 问 : 查询年龄小于45的员工,并根据工作地址分组,获取员工数量大于等于3的工作地址;

• 答 :

-- 1.工作地址 分组 年龄小于45 员工数量大于等于3
SELECT work_address,COUNT(*) FROM emp WHERE age < 45 GROUP BY work_address HAVING COUNT(*)>=3;
-- 2.对语句中 COUNT(*) 使用别名 address_count
SELECT work_address,COUNT(*) address_count FROM emp WHERE age < 45 GROUP BY work_address HAVING address_count >= 3;

---

5.排序查询

题5.1

• 问 : 根据年龄对公司的员工进行升序排序

• 答 :

SELECT * FROM emp ORDER BY age ASC;
-- 默认值 是升序 ASC 可省略
SELECT * FROM emp ORDER BY age ;

---

题5.2

• 问 : 根据入职时间,对员工进行降序排序

• 答 : SELECT * FROM emp ORDER BY entry_date DESC;

---

题5.3

• 问 : 根据年龄对公司的员工进行升序排序,年龄相同,再按照入职时间进行降序排序

• 答 :

SELECT * FROM emp ORDER BY age ASC , entry_date DESC;
-- 默认值 是升序 ASC 可省略
SELECT * FROM emp ORDER BY age , entry_date DESC;

---

6.分页查询

题6.1

• 问 : 查询第1页员工数据,每页展示10条.

• 答 :

SELECT * FROM emp LIMIT 0,10;
-- or 简写(仅适用于 第一页)
SELECT * FROM emp LIMIT 10;

---

题6.2

• 问 : 查询第2页员工数据,每页展示10条.

• 答 :

SELECT * FROM emp LIMIT 10,20;

---

7.综合练习

题7.1

• 问 : 查询年龄为20,21,22,23岁的员工信息

• 答 :

SELECT * FROM emp WHERE age IN (20,21,22,23);
-- or
SELECT * FROM emp WHERE age BETWEEN 20 AND 23;

---

题7.2

• 问 : 查询性别为男,并且年龄在20(含)-40(含)以内的姓名为3个字的员工.

• 答 :

SELECT * FROM emp WHERE gender = '男' AND age >= 20 AND age <= 40 AND name LIKE '___';
-- or
SELECT * FROM emp WHERE gender = '男' AND age BETWEEN 20 AND 40 AND name LIKE '___';
-- 20 AND 40 AND NAME 多条件阅读不清晰可加()
SELECT * FROM emp WHERE ( gender = '男' ) AND ( age BETWEEN 20 AND 40 ) AND ( name LIKE '___' );

---

题7.3

• 问 : 统计员工表中,年龄小于60岁的男性员工和女性员工各有多少人.

• 答 :

SELECT gender, COUNT(*) FROM emp WHERE age < 60 GROUP BY gender;

---

题7.4

• 问 : 查询所有年龄小于等于35岁员工的姓名和年龄和入职时间,并对查询结果按年龄升序排序,如果年龄相同按入职时间降序排序

• 答 :

SELECT name, age, entry_date FROM emp WHERE age <= 35 ORDER BY age ASC , entry_date DESC ;

---

题7.5

• 问 : 查询性别为男,且年龄在20(含)-40(含)以内的前5个员工信息,对查询结果按年龄升序排序,年龄相同按入职时间升序排序

• 答 :

SELECT * FROM emp WHERE gender = '男' AND age BETWEEN 20 AND 40 ORDER BY age ASC ,entry_date DESC LIMIT 0,5;

---

• 返回目录 : MySQL 学习笔记
• 上一章 1.2数据库初识 DQL
• 下一章 1.4数据库初识 DCL