基础查询

一、SELECT基础

1.1 基本语法

1.1.1 SELECT语句

语法格式：

SELECT 列名1, 列名2, ...
FROM 表名;

示例：

-- 创建学生表
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age TINYINT UNSIGNED,
    gender ENUM('男', '女'),
    score DECIMAL(5, 2),
    city VARCHAR(50)
);

-- 插入测试数据
INSERT INTO students (name, age, gender, score, city) VALUES
('张三', 20, '男', 85.5, '北京'),
('李四', 22, '女', 92.0, '上海'),
('王五', 21, '男', 78.5, '广州'),
('赵六', 19, '女', 88.0, '深圳'),
('孙七', 23, '男', 95.5, '北京'),
('周八', 20, '女', 82.0, '上海');

-- 查询所有列
SELECT * FROM students;

-- 查询指定列
SELECT name, age, score FROM students;

1.1.2 列别名

语法格式：

SELECT 列名 AS 别名 FROM 表名;

或

SELECT 列名 别名 FROM 表名;

示例：

-- 使用AS关键字
SELECT name AS 姓名, age AS 年龄 FROM students;

-- 省略AS关键字
SELECT name 姓名, score 成绩 FROM students;

-- 使用引号（包含特殊字符）
SELECT name AS '学生姓名', score AS '成绩(分)' FROM students;

1.2 DISTINCT去重

1.2.1 去除重复值

语法格式：

SELECT DISTINCT 列名 FROM 表名;

示例：

-- 查询所有城市（包含重复）
SELECT city FROM students;

-- 查询不重复的城市
SELECT DISTINCT city FROM students;

输出对比：

-- 不使用DISTINCT
+--------+
| city   |
+--------+
| 北京   |
| 上海   |
| 广州   |
| 深圳   |
| 北京   |
| 上海   |
+--------+

-- 使用DISTINCT
+--------+
| city   |
+--------+
| 北京   |
| 上海   |
| 广州   |
| 深圳   |
+--------+

1.2.2 多列去重

-- 查询不重复的城市和性别组合
SELECT DISTINCT city, gender FROM students;

1.3 LIMIT限制

1.3.1 限制返回行数

语法格式：

SELECT 列名 FROM 表名 LIMIT 数量;

示例：

-- 查询前3条记录
SELECT * FROM students LIMIT 3;

1.3.2 分页查询

语法格式：

SELECT 列名 FROM 表名 LIMIT 偏移量, 数量;

或

SELECT 列名 FROM 表名 LIMIT 数量 OFFSET 偏移量;

示例：

-- 第1页：从第0条开始，取3条
SELECT * FROM students LIMIT 0, 3;

-- 第2页：从第3条开始，取3条
SELECT * FROM students LIMIT 3, 3;

-- 第3页：从第6条开始，取3条
SELECT * FROM students LIMIT 6, 3;

-- 使用OFFSET语法
SELECT * FROM students LIMIT 3 OFFSET 0;
SELECT * FROM students LIMIT 3 OFFSET 3;

二、WHERE条件

2.1 基本条件

2.1.1 比较运算符

示例：

-- 等于
SELECT * FROM students WHERE age = 20;

-- 不等于
SELECT * FROM students WHERE age != 20;

-- 大于
SELECT * FROM students WHERE score > 85;

-- 小于等于
SELECT * FROM students WHERE age <= 20;

2.1.2 逻辑运算符

示例：

-- AND：多个条件同时满足
SELECT * FROM students WHERE age > 20 AND score > 80;

-- OR：满足任一条件
SELECT * FROM students WHERE city = '北京' OR city = '上海';

-- NOT：取反
SELECT * FROM students WHERE NOT gender = '男';

-- 组合使用
SELECT * FROM students 
WHERE (city = '北京' OR city = '上海') AND score > 80;

2.2 范围条件

2.2.1 BETWEEN...AND

语法格式：

WHERE 列名 BETWEEN 值1 AND 值2

示例：

-- 查询年龄在20到22岁之间的学生
SELECT * FROM students WHERE age BETWEEN 20 AND 22;

-- 等价于
SELECT * FROM students WHERE age >= 20 AND age <= 22;

2.2.2 IN运算符

语法格式：

WHERE 列名 IN (值1, 值2, ...)

示例：

-- 查询北京、上海、广州的学生
SELECT * FROM students WHERE city IN ('北京', '上海', '广州');

-- 等价于
SELECT * FROM students 
WHERE city = '北京' OR city = '上海' OR city = '广州';

2.3 NULL值判断

2.3.1 IS NULL和IS NOT NULL

语法格式：

WHERE 列名 IS NULL
WHERE 列名 IS NOT NULL

示例：

-- 插入包含NULL的数据
INSERT INTO students (name, age, gender) VALUES ('测试', 20, NULL);

-- 查询city为NULL的记录
SELECT * FROM students WHERE city IS NULL;

-- 查询city不为NULL的记录
SELECT * FROM students WHERE city IS NOT NULL;

⚠️ 注意：不能使用= NULL或!= NULL判断NULL值。

2.4 模糊查询

2.4.1 LIKE运算符

语法格式：

WHERE 列名 LIKE '模式'

通配符：

示例：

-- 以'张'开头
SELECT * FROM students WHERE name LIKE '张%';

-- 以'三'结尾
SELECT * FROM students WHERE name LIKE '%三';

-- 包含'小'
SELECT * FROM students WHERE name LIKE '%小%';

-- 第二个字是'三'
SELECT * FROM students WHERE name LIKE '_三%';

-- 查询两个字的名字
SELECT * FROM students WHERE name LIKE '__';

2.4.2 转义字符

使用ESCAPE：

-- 查询包含'%'的字符串
SELECT * FROM students WHERE name LIKE '%\%%';

-- 使用ESCAPE指定转义字符
SELECT * FROM students WHERE name LIKE '%|%%' ESCAPE '|';

三、ORDER BY排序

3.1 基本排序

3.1.1 升序排序

语法格式：

ORDER BY 列名 ASC

或

ORDER BY 列名

示例：

-- 按年龄升序排序（默认）
SELECT * FROM students ORDER BY age;

-- 明确指定ASC
SELECT * FROM students ORDER BY age ASC;

3.1.2 降序排序

语法格式：

ORDER BY 列名 DESC

示例：

-- 按成绩降序排序
SELECT * FROM students ORDER BY score DESC;

3.2 多列排序

3.2.1 多个排序字段

语法格式：

ORDER BY 列名1 [ASC|DESC], 列名2 [ASC|DESC], ...

示例：

-- 先按城市升序，再按成绩降序
SELECT * FROM students 
ORDER BY city ASC, score DESC;

-- 先按性别升序，再按年龄降序
SELECT * FROM students 
ORDER BY gender, age DESC;

3.3 按表达式排序

3.3.1 使用表达式

-- 按成绩的整数部分排序
SELECT name, score, FLOOR(score) 
FROM students 
ORDER BY FLOOR(score);

-- 按名字长度排序
SELECT * FROM students ORDER BY LENGTH(name);

3.3.2 按别名排序

-- 使用列别名排序
SELECT name, score * 10 AS total_score
FROM students
ORDER BY total_score DESC;

3.4 按位置排序

3.4.1 使用列位置

-- 按第2列排序
SELECT name, age, score FROM students ORDER BY 2;

-- 按第3列降序
SELECT name, age, score FROM students ORDER BY 3 DESC;

⚠️ 注意：不推荐使用位置排序，可读性差。

四、聚合函数

4.1 常用聚合函数

4.1.1 函数列表

4.1.2 COUNT函数

统计行数：

-- 统计总行数
SELECT COUNT(*) FROM students;

-- 统计非NULL值数量
SELECT COUNT(city) FROM students;

-- 统计不重复值数量
SELECT COUNT(DISTINCT city) FROM students;

4.1.3 SUM函数

求和：

-- 计算总成绩
SELECT SUM(score) AS total_score FROM students;

-- 计算总年龄
SELECT SUM(age) AS total_age FROM students;

4.1.4 AVG函数

平均值：

-- 计算平均成绩
SELECT AVG(score) AS avg_score FROM students;

-- 保留小数位
SELECT ROUND(AVG(score), 2) AS avg_score FROM students;

4.1.5 MAX和MIN函数

最大值和最小值：

-- 查询最高分
SELECT MAX(score) AS max_score FROM students;

-- 查询最低分
SELECT MIN(score) AS min_score FROM students;

-- 查询最大年龄
SELECT MAX(age) AS max_age FROM students;

-- 组合使用
SELECT 
    MAX(score) AS max_score,
    MIN(score) AS min_score,
    MAX(score) - MIN(score) AS score_range
FROM students;

4.2 GROUP BY分组

4.2.1 基本分组

语法格式：

SELECT 列名, 聚合函数()
FROM 表名
GROUP BY 列名;

示例：

-- 按城市分组统计人数
SELECT city, COUNT(*) AS count
FROM students
GROUP BY city;

-- 按性别分组统计平均成绩
SELECT gender, AVG(score) AS avg_score
FROM students
GROUP BY gender;

-- 按城市和性别分组
SELECT city, gender, COUNT(*) AS count
FROM students
GROUP BY city, gender;

4.2.2 GROUP BY结合ORDER BY

-- 按城市分组，按人数降序排序
SELECT city, COUNT(*) AS count
FROM students
GROUP BY city
ORDER BY count DESC;

4.3 HAVING子句

4.3.1 过滤分组结果

语法格式：

SELECT 列名, 聚合函数()
FROM 表名
GROUP BY 列名
HAVING 条件;

示例：

-- 查询人数大于1的城市
SELECT city, COUNT(*) AS count
FROM students
GROUP BY city
HAVING count > 1;

-- 查询平均成绩大于85的性别
SELECT gender, AVG(score) AS avg_score
FROM students
GROUP BY gender
HAVING avg_score > 85;

4.3.2 HAVING与WHERE区别

示例对比：

-- WHERE：过滤行
SELECT city, AVG(score) AS avg_score
FROM students
WHERE score > 80
GROUP BY city;

-- HAVING：过滤分组
SELECT city, AVG(score) AS avg_score
FROM students
GROUP BY city
HAVING avg_score > 85;

五、常用函数

5.1 字符串函数

5.1.1 字符串处理

示例：

-- 连接字符串
SELECT CONCAT(name, '(', city, ')') AS info FROM students;

-- 字符串长度
SELECT name, LENGTH(name), CHAR_LENGTH(name) FROM students;

-- 大小写转换
SELECT UPPER(name), LOWER(name) FROM students;

-- 截取字符串
SELECT LEFT(name, 1) AS 姓氏 FROM students;
SELECT SUBSTRING(name, 2, 1) AS 名字 FROM students;

-- 替换
SELECT REPLACE(name, '张', '王') FROM students;

5.2 数值函数

5.2.1 数学函数

示例：

-- 绝对值
SELECT ABS(score - 90) FROM students;

-- 四舍五入
SELECT ROUND(score, 1) FROM students;

-- 向上取整
SELECT CEIL(score) FROM students;

-- 随机数
SELECT RAND() FROM students;

-- 随机排序
SELECT * FROM students ORDER BY RAND();

5.3 日期时间函数

5.3.1 日期函数

示例：

-- 当前日期时间
SELECT NOW(), CURDATE(), CURTIME();

-- 提取日期部分
SELECT YEAR(NOW()), MONTH(NOW()), DAY(NOW());

-- 格式化日期
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日 %H:%i:%s');

-- 日期计算
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);

-- 日期差值
SELECT DATEDIFF('2024-12-31', NOW());

5.4 控制流函数

5.4.1 IF函数

语法格式：

IF(条件, 真值, 假值)

示例：

-- 成绩等级判断
SELECT name, score, 
    IF(score >= 60, '及格', '不及格') AS grade
FROM students;

5.4.2 CASE WHEN

语法格式1：

CASE 
    WHEN 条件1 THEN 结果1
    WHEN 条件2 THEN 结果2
    ...
    ELSE 默认结果
END

语法格式2：

CASE 表达式
    WHEN 值1 THEN 结果1
    WHEN 值2 THEN 结果2
    ...
    ELSE 默认结果
END

示例：

-- 成绩等级划分
SELECT name, score,
    CASE 
        WHEN score >= 90 THEN '优秀'
        WHEN score >= 80 THEN '良好'
        WHEN score >= 70 THEN '中等'
        WHEN score >= 60 THEN '及格'
        ELSE '不及格'
    END AS grade
FROM students;

-- 城市区域划分
SELECT name, city,
    CASE city
        WHEN '北京' THEN '华北'
        WHEN '上海' THEN '华东'
        WHEN '广州' THEN '华南'
        WHEN '深圳' THEN '华南'
        ELSE '其他'
    END AS region
FROM students;

六、SELECT执行顺序

6.1 完整语法

SELECT DISTINCT 列名
FROM 表名
JOIN 表名 ON 条件
WHERE 条件
GROUP BY 列名
HAVING 条件
ORDER BY 列名
LIMIT 数量;

6.2 执行顺序

执行顺序：

1. FROM：选择表
2. JOIN：连接表
3. ON：连接条件
4. WHERE：过滤行
5. GROUP BY：分组
6. HAVING：过滤分组
7. SELECT：选择列
8. DISTINCT：去重
9. ORDER BY：排序
10. LIMIT：限制结果

示例：

SELECT city, COUNT(*) AS count
FROM students
WHERE score > 80
GROUP BY city
HAVING count > 1
ORDER BY count DESC
LIMIT 3;

执行过程：

1. FROM students：选择students表
2. WHERE score > 80：过滤成绩大于80的学生
3. GROUP BY city：按城市分组
4. HAVING count > 1：过滤分组后人数大于1的城市
5. SELECT city, COUNT(*)：选择列
6. ORDER BY count DESC：按人数降序排序
7. LIMIT 3：取前3条

七、本章小结

7.1 核心要点

✅ SELECT查询数据，支持列别名 ✅ WHERE过滤数据，支持多种条件 ✅ ORDER BY排序，支持多列排序 ✅ LIMIT限制结果，支持分页 ✅ 聚合函数结合GROUP BY使用

7.2 验证清单

完成本章学习后，请确认您能够：

• 使用SELECT查询数据
• 使用WHERE过滤数据
• 使用ORDER BY排序
• 使用LIMIT分页
• 使用聚合函数和GROUP BY