跳到主要内容

单表查询练习

1 准备表数据​

-- 创建库
CREATE DATABASE IF NOT EXISTS test_dql;
USE test_dql;


-- 创建员工表
DROP TABLE IF EXISTS `t_employee`;

CREATE TABLE `t_employee` (
  `eid` INT NOT NULL COMMENT '员工编号',
  `ename` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '员工姓名',
  `salary` DOUBLE NOT NULL COMMENT '薪资',
  `commission_pct` DECIMAL(3,2) DEFAULT NULL COMMENT '奖金比例',
  `birthday` DATE NOT NULL COMMENT '出生日期',
  `gender` ENUM('男','女') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '男' COMMENT '性别',
  `tel` CHAR(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '手机号码',
  `email` VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '邮箱',
  `address` VARCHAR(150) DEFAULT NULL COMMENT '地址',
  `work_place` SET('北京','深圳','上海','武汉') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '北京' COMMENT '工作地点'
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


INSERT  INTO `t_employee`(`eid`,`ename`,`salary`,`commission_pct`,`birthday`,`gender`,`tel`,`email`,`address`,`work_place`) 
VALUES (1,'孙洪亮',28000,'0.65','1980-10-08','男','13789098765','shl@example.com','白庙村西街','北京,深圳'),
(2,'何进',7001,'0.10','1984-08-03','男','13456732145','hj@example.com','半截塔存','深圳,上海'),
(3,'邓超远',8000,NULL,'1985-04-09','男','18678973456','dcy666@example.com','宏福苑','北京,深圳,上海,武汉'),
(4,'黄熙萌',9456,NULL,'1986-09-07','女','13609876789','hxm@example.com','白庙村东街','深圳,上海,武汉'),
(5,'陈浩',8567,NULL,'1978-08-02','男','13409876545','ch888@example.com','回龙观','北京,深圳,上海'),
(6,'韩庚年',12000,NULL,'1985-04-03','男','18945678986','hgn@example.com','龙泽','深圳,上海'),
(7,'贾宝玉',15700,'0.24','1982-08-02','男','15490876789','jby@example.com','霍营','北京,武汉'),
(8,'李晨熙',9000,'0.40','1983-03-02','女','13587689098','lc@example.com','东三旗','深圳,上海,武汉'),
(9,'李易峰',7897,NULL,'1984-09-01','男','13467676789','lyf@example.com','西山旗','武汉'),
(10,'陆风',8789,NULL,'1989-04-02','男','13689876789','lf@example.com','天通苑一区','北京'),
(11,'黄冰茹',15678,NULL,'1983-05-07','女','13787876565','hbr@example.com','立水桥','深圳'),
(12,'孙红梅',9000,NULL,'1986-04-02','女','13576234554','shm@example.com','立城苑','上海'),
(13,'李冰冰',18760,NULL,'1987-04-09','女','13790909887','lbb@example.com','王府温馨公寓','北京'),
(14,'谢吉娜',18978,'0.25','1990-01-01','女','13234543245','xjn@example.com','园中园','上海,武汉'),
(15,'董吉祥',8978,NULL,'1987-05-05','男','13876544333','djx@example.com','小辛庄','北京,上海'),
(16,'彭超越',9878,NULL,'1988-03-06','男','18264578930','pcy@example.com','西二旗','深圳,武汉'),
(17,'李诗雨',9000,NULL,'1990-08-09','女','18567899098','lsy@example.com','清河','北京,深圳,武汉'),
(18,'舒淇格',16788,'0.10','1978-09-04','女','18654565634','sqg@example.com','名流花园','北京,深圳,武汉'),
(19,'周旭飞',7876,NULL,'1988-06-13','女','13589893434','sxf@example.com','小汤山','北京,深圳'),
(20,'章嘉怡',15099,'0.10','1989-12-11','女','15634238979','zjy@example.com','望都家园','北京'),
(21,'白露',9787,NULL,'1989-09-04','女','18909876789','bl@example.com','西湖新村','上海'),
(22,'刘烨',13099,'0.32','1990-11-09','男','18890980989','ly@example.com','多彩公寓','北京,上海'),
(23,'陈纲',13090,NULL,'1990-02-04','男','18712345632','cg@example.com','天通苑二区','深圳'),
(24,'吉日格勒',10289,NULL,'1990-04-01','男','17290876543','jrgl@163.com','北苑','北京'),
(25,'额日古那',9087,NULL,'1989-08-01','女','18709675645','ergn@example.com','望京','北京,上海'),
(26,'李红',5000,NULL,'1995-02-15','女','15985759663','lihong@example.com','冠雅苑','北京'),
(27,'周洲',8000,NULL,'1990-01-01','男','13574528569','zhouzhou@example.com','冠华苑','北京,深圳');

1. 基础查询语法​

1.1 基本查询结构​

-- 查询所有列
SELECT * FROM 表名;

-- 查询指定列
SELECT 列1, 列2 FROM 表名;

-- 起别名
SELECT 列名 AS 别名, 列名 别名 FROM 表名;

1.2 特殊查询场景​

-- 非表查询（计算、函数）
SELECT NOW(), 1+1;

-- 常数列
SELECT 列名, '固定值' AS 新列 FROM 表名;

-- 去重
SELECT DISTINCT 列名 FROM 表名;

2. WHERE 条件查询​

2.1 比较运算符​

=, >, <, >=, <=, <>, !=
-- 注意：字符串和数字比较时会自动转换

2.2 空值判断​

IS NULL, IS NOT NULL
-- 注意：不能使用 = NULL 或 <> NULL

2.3 区间和范围​

-- 区间
BETWEEN 最小值 AND 最大值

-- 范围
IN (值1, 值2, 值3)

2.4 模糊匹配​

LIKE '模式'
-- % 匹配任意多个字符
-- _ 匹配单个字符

2.5 SET类型字段查询​

-- 使用 FIND_IN_SET 函数
FIND_IN_SET('值', 列名) = 1  -- 存在
FIND_IN_SET('值', 列名) = 0  -- 不存在

3. 空值处理技巧​

3.1 IFNULL 函数​

-- 处理 NULL 值参与运算
SELECT salary + salary * IFNULL(commission_pct, 0) FROM t_employee;

3.2 NULL 运算特性​

  • NULL 与任何值运算结果都是 NULL
  • 聚合函数忽略 NULL 值

4. 常用函数​

4.1 数值函数​

ABS(), CEIL(), FLOOR(), ROUND(数值, 小数位数), TRUNCATE(数值, 小数位数)
RAND()  -- 随机数

4.2 字符串函数​

CHAR_LENGTH(), CONCAT(), FIND_IN_SET()
REPLACE(字符串, '目标', '替换值')

4.3 时间函数​

-- 获取时间
NOW(), CURDATE(), CURTIME()

-- 时间提取
YEAR(), MONTH(), DAY(), WEEKDAY(), DAYOFWEEK()

-- 时间运算
DATE_ADD(时间, INTERVAL 值 单位)
DATEDIFF(日期1, 日期2)

-- 时间格式化
DATE_FORMAT(时间, '格式')
STR_TO_DATE('字符串', '格式')

4.4 流程控制函数​

-- IF 函数
IF(表达式, true值, false值)

-- CASE 语句（两种形式）
-- 形式1：条件判断
CASE 
    WHEN 条件1 THEN 结果1
    WHEN 条件2 THEN 结果2
    ELSE 默认值
END

-- 形式2：值匹配
CASE 列名
    WHEN 值1 THEN 结果1
    WHEN 值2 THEN 结果2
    ELSE 默认值
END

5. 聚合函数与分组​

5.1 常用聚合函数​

AVG(), SUM(), MIN(), MAX(), COUNT()
-- COUNT(*) 统计所有行，COUNT(列) 统计非空值

5.2 分组查询​

SELECT 分组字段, 聚合函数 
FROM 表名 
GROUP BY 分组字段
HAVING 分组后条件;

WHERE vs HAVING：

  • WHERE：分组前过滤，不能使用聚合函数
  • HAVING：分组后过滤，可以使用聚合函数和SELECT中的别名

6. 排序与分页​

6.1 排序​

ORDER BY 列1 ASC|DESC, 列2 ASC|DESC
-- ASC 升序（默认），DESC 降序

6.2 分页​

LIMIT 偏移量, 数量
LIMIT 数量  -- 简写，偏移量为0

-- 分页公式
LIMIT (页码-1)*每页大小, 每页大小

7. 查询执行顺序​

书写顺序：

SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT

执行顺序：

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

8. 实用技巧总结​

8.1 日期相关查询​

-- 今天生日
WHERE DATE_FORMAT(birthday, '%m-%d') = DATE_FORMAT(NOW(), '%m-%d')

-- 本月生日
WHERE MONTH(birthday) = MONTH(NOW())

-- 计算年龄
ROUND(DATEDIFF(NOW(), birthday) / 365, 1)

8.2 数据统计技巧​

-- 使用 WITH ROLLUP 生成汇总行
SELECT 字段, COUNT(*) FROM 表 GROUP BY 字段 WITH ROLLUP;

-- 使用 IFNULL 处理汇总行的NULL值
SELECT IFNULL(字段, '总计'), COUNT(*) FROM 表 GROUP BY 字段 WITH ROLLUP;

8.3 字符串处理​

-- 统计字符数（忽略空格）
CHAR_LENGTH(REPLACE(列名, ' ', ''))

9. 注意事项​

  1. NULL 处理：始终注意 NULL 值对运算和比较的影响
  2. SET 类型：使用 FIND_IN_SET 而不是 LIKE 进行精确匹配
  3. 分组查询：SELECT 中只能包含分组字段和聚合函数
  4. 别名使用：HAVING 和 ORDER BY 可以使用 SELECT 中的别名
  5. 性能考虑：LIKE 以 % 开头会导致全表扫描