MySQL单表查询语句结构详解
基础查询语法
在MySQL中，数据存储于具有特定结构的表内，用户可通过SQL语句实现增删改查操作。最基本的查询方式如下：
SELECT * FROM 表名;：检索指定表中所有字段的全部记录。SELECT 字段1, 字段2 FROM 表名;：仅获取指定字段的数据。
查询子句及其执行顺序
完整的单表查询可包含多个子句，各关键字按以下逻辑顺序排列（但并非全部必须）：
- WHERE：对原始数据进行条件过滤（分组前筛选）。
- GROUP BY：根据某一列或多列将数据分组。
- HAVING：对已分组的结果再次筛选（分组后条件判断）。
- DISTINCT：去除重复的字段组合。
- ORDER BY：对最终结果集排序。
- LIMIT：限制返回的行数，常用于分页。
此外，还支持正则匹配和字符串拼接函数增强查询表达能力。
示例数据准备
为演示各项功能，创建并填充角色信息表：
CREATE TABLE role_info (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
gender ENUM('male', 'female') DEFAULT 'male',
age TINYINT UNSIGNED DEFAULT 28,
birth DATE NOT NULL,
game_title VARCHAR(50),
price DECIMAL(15,2),
studio_room INT,
team_id INT
);
INSERT INTO role_info (name, gender, age, birth, game_title, price, studio_room, team_id) VALUES
('蔚', 'female', 18, '2017-03-01', 'LOL', 7300.33, 401, 1),
('金克斯', 'female', 16, '2015-03-02', 'LOL', 1000000.31, 401, 1),
('杰斯', 'male', 27, '2013-03-05', 'LOL', 8300.00, 401, 1),
('维克多', 'male', 22, '2014-07-01', 'LOL', 3500.00, 401, 1),
('凯特琳', 'female', 17, '2012-11-01', 'LOL', 2100.00, 401, 1),
('希尔克', 'male', 50, '2011-02-11', 'LOL', 9000.00, 401, 1),
('范德尔', 'male', 60, '1900-03-01', 'LOL', 30000.00, 401, 1),
('亚瑟', 'male', 48, '2010-11-11', 'HOK', 10000.00, 402, 1),
('艾琳', 'female', 48, '2015-03-11', 'HOK', 3000.13, 402, 2),
('云缨', 'female', 38, '2010-11-01', 'HOK', 2000.35, 402, 2),
('东方曜', 'male', 18, '2011-03-12', 'HOK', 1000.37, 402, 2),
('宫本武藏', 'male', 18, '2016-05-13', 'HOK', 3000.29, 402, 2),
('司空震', 'male', 28, '2017-01-27', 'HOK', 4000.33, 402, 2),
('巴巴托斯', 'male', 28, '2016-03-11', 'GI', 10000.13, 403, 3),
('摩拉克斯', 'male', 18, '1997-03-12', 'GI', 20000.00, 403, 3),
('巴尔泽布', 'female', 18, '2013-03-11', 'GI', 19000.00, 403, 3),
('布耶尔', 'male', 18, '2015-04-11', 'GI', 18000.00, 403, 3),
('天理', 'female', 18, '2014-05-12', 'GI', 17000.00, 403, 3);
WHERE 条件过滤
用于在分组或排序前对原始记录施加筛选条件。
范围匹配：BETWEEN ... AND
-- 查询ID介于3到6之间的角色
SELECT * FROM role_info WHERE id BETWEEN 3 AND 6;
集合匹配：IN 与 NOT IN
-- 查找价格为17000、18000或20000的角色
SELECT * FROM role_info WHERE price IN (17000, 18000, 20000);
-- 排除某些值
SELECT * FROM role_info WHERE price NOT IN (30000, 1000);
模糊匹配：LIKE
使用通配符进行模式匹配：
%：匹配任意长度字符（包括零个）。_：匹配单个字符。
-- 名称以"巴"开头
SELECT name, price FROM role_info WHERE name LIKE '巴%';
-- 名称为四个汉字
SELECT name, price FROM role_info WHERE name LIKE '____';
存在性判断：EXISTS
仅当子查询返回结果时，主查询才执行。
SELECT name, age FROM role_info r1
WHERE EXISTS (SELECT 1 FROM role_info r2 WHERE r2.name = '东方曜');
GROUP BY 数据分组
将数据按某一字段归类，便于分析群体特征。
-- 按游戏名称分组
SELECT game_title, COUNT(*) AS total_roles FROM role_info GROUP BY game_title;
ONLY_FULL_GROUP_BY 模式说明
启用该SQL模式后，SELECT列表中只能出现分组字段或聚合函数计算值，防止非确定性输出。可在配置文件中设置：
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,ONLY_FULL_GROUP_BY'
常用聚合函数
| 函数 | 用途 |
|---|---|
| MAX() | 最大值 |
| MIN() | 最小值 |
| AVG() | 平均值 |
| SUM() | 总和 |
| COUNT() | 计数 |
| GROUP_CONCAT() | 合并组内字段为字符串 |
-- 各游戏中最贵角色的价格
SELECT game_title, MAX(price) AS max_price FROM role_info GROUP BY game_title;
-- 列出每款游戏的所有角色名
SELECT game_title, GROUP_CONCAT(name SEPARATOR ', ') AS characters FROM role_info GROUP BY game_title;
HAVING 分组后筛选
针对聚合后的结果设定条件，不能使用WHERE替代。
-- 统计各游戏中年龄大于20的角色平均价格，并保留高于1万元的组
SELECT game_title, AVG(price) AS avg_price
FROM role_info
WHERE age > 20
GROUP BY game_title
HAVING AVG(price) > 10000
ORDER BY avg_price DESC;
DISTINCT 去重处理
消除重复的字段组合记录。
-- 查询不同的性别与工作室组合
SELECT DISTINCT gender, studio_room FROM role_info;
-- 统计不同价格数量
SELECT COUNT(DISTINCT price) FROM role_info;
ORDER BY 结果排序
控制最终输出顺序，支持多级排序。
-- 年龄升序，相同年龄下价格降序
SELECT * FROM role_info ORDER BY age ASC, price DESC;
-- 对分组结果排序
SELECT game_title, AVG(age) AS avg_age
FROM role_info
GROUP BY game_title
ORDER BY avg_age DESC;
LIMIT 实现分页
控制返回行数，适用于大数据量场景。
-- 获取前5条记录
SELECT * FROM role_info LIMIT 5;
-- 跳过前5条，取接下来的6条（分页第二页）
SELECT * FROM role_info LIMIT 5, 6;
REGEXP 正则表达式匹配
提供比LIKE更强大的文本匹配能力。
-- 名字以"巴"或"金"开头，且以"斯"或"布"结尾
SELECT * FROM role_info WHERE name REGEXP '^[巴金].*[斯布]$';
字符串拼接：CONCAT 与 CONCAT_WS
构造复合字段输出。
-- 使用自定义连接符拼接字段
SELECT CONCAT(game_title, ' - ', name) AS full_desc FROM role_info ORDER BY age DESC LIMIT 5;
-- 使用统一分隔符连接多个字段
SELECT CONCAT_WS(' | ', game_title, name, price) AS detail_line FROM role_info ORDER BY price DESC LIMIT 5;
综合练习题
- 统计男女角色各自的平均售价：
SELECT gender, AVG(price) AS avg_price FROM role_info GROUP BY gender; - 计算年龄在18至20岁之间（含边界）的角色总价，按游戏分类：
SELECT game_title, SUM(price) AS total_price FROM role_info WHERE age BETWEEN 18 AND 20 GROUP BY game_title;
