文章目录
MySQL 5.7 查询入门详解
一、数据库与表基础操作
1.1 连接数据库
mysql -u 用户名 -p
-- 输入密码后进入MySQL命令行
1.2 创建数据库
CREATE DATABASE IF NOT EXISTS test_db
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
1.3 使用数据库
USE test_db;
1.4 创建数据表
CREATE TABLE students (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
age TINYINT UNSIGNED,
gender ENUM('男','女','其他'),
create_time DATETIME DEFAULT CURRENT_TIMESTAMP
);
1.5 表结构查看
DESCRIBE students; -- 查看表结构
SHOW CREATE TABLE students; -- 查看完整建表语句
二、SELECT基础查询
2.1 全列查询
SELECT * FROM students;
2.2 指定列查询
SELECT name, age FROM students;
2.3 别名使用
SELECT
name AS 学生姓名,
age + 5 AS 预测年龄
FROM students;
2.4 去重查询
SELECT DISTINCT gender FROM students;
2.5 表达式计算
SELECT
name,
age,
age * 0.8 AS discount_age
FROM students;
三、WHERE条件查询
3.1 比较运算符
-- 查询年龄大于18岁的学生
SELECT * FROM students
WHERE age > 18;
-- 查询性别为女的记录
SELECT * FROM students
WHERE gender = '女';
3.2 逻辑运算符
-- AND运算（同时满足）
SELECT * FROM students
WHERE age > 18 AND gender = '男';
-- OR运算（满足其一）
SELECT * FROM students
WHERE age < 18 OR gender = '女';
-- NOT运算（取反）
SELECT * FROM students
WHERE NOT (age > 20);
3.3 范围查询
-- BETWEEN...AND（闭区间）
SELECT * FROM students
WHERE age BETWEEN 18 AND 25;
-- IN运算符
SELECT * FROM students
WHERE gender IN ('男','女');
3.4 模糊查询
-- LIKE通配符
SELECT * FROM students
WHERE name LIKE '张%'; -- 以张开头
SELECT * FROM students
WHERE name LIKE '%小'; -- 以小结尾
SELECT * FROM students
WHERE name LIKE '_小%'; -- 第二个字是小
四、JOIN关联查询
4.1 内连接
-- 学生表与成绩表关联
SELECT s.name, sc.score
FROM students s
INNER JOIN scores sc
ON s.id = sc.student_id;
4.2 左连接
-- 包含没有成绩的学生
SELECT s.name, sc.score
FROM students s
LEFT JOIN scores sc
ON s.id = sc.student_id;
4.3 自连接
-- 查询学生及其导师信息
SELECT
s.name AS 学生,
t.name AS 导师
FROM students s
JOIN students t
ON s.teacher_id = t.id;
五、聚合函数与分组
5.1 聚合函数
-- 统计总人数
SELECT COUNT(*) AS total FROM students;
-- 计算平均年龄
SELECT AVG(age) AS avg_age FROM students;
-- 最大最小值
SELECT MAX(age), MIN(age) FROM students;
-- 求和
SELECT SUM(age) FROM students;
5.2 GROUP BY分组
-- 按性别分组统计
SELECT
gender,
COUNT(*) AS count,
AVG(age) AS avg_age
FROM students
GROUP BY gender;
5.3 HAVING过滤分组
-- 查询平均年龄大于20的分组
SELECT
gender,
AVG(age) AS avg_age
FROM students
GROUP BY gender
HAVING avg_age > 20;
六、子查询与高级用法
6.1 标量子查询
-- 查询比平均年龄大的学生
SELECT * FROM students
WHERE age > (SELECT AVG(age) FROM students);
6.2 IN子查询
-- 查询有成绩的学生
SELECT * FROM students
WHERE id IN (SELECT DISTINCT student_id FROM scores);
6.3 EXISTS子查询
-- 查询存在成绩的学生
SELECT * FROM students s
WHERE EXISTS (
SELECT 1 FROM scores sc
WHERE sc.student_id = s.id
);
七、索引与性能优化
7.1 创建索引
-- 普通索引
CREATE INDEX idx_name ON students(name);
-- 唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);
-- 组合索引
CREATE INDEX idx_age_gender ON students(age, gender);
7.2 EXPLAIN分析
EXPLAIN SELECT * FROM students WHERE age > 18;
7.3 优化建议
- 优先使用覆盖索引
- 避免在索引列上使用函数
- 控制单表数据量（建议<500万行）
- 定期执行
OPTIMIZE TABLE整理碎片
八、实用技巧与注意事项
8.1 分页查询
-- 每页10条，查询第3页
SELECT * FROM students
LIMIT 20, 10; -- (页码-1)*每页数量
8.2 排序规则
-- 多列排序
SELECT * FROM students
ORDER BY age DESC, name ASC;
-- 修改默认排序规则
ALTER TABLE students MODIFY name VARCHAR(50) COLLATE utf8mb4_unicode_520_ci;
8.3 时间区间查询
-- 查询最近一个月的数据
SELECT * FROM orders
WHERE create_time >= DATE_SUB(NOW(), INTERVAL 1 MONTH);
九、常见问题解决
9.1 中文乱码处理
-- 修改连接字符集
SET NAMES utf8mb4;
-- 修改配置文件（my.cnf）
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
9.2 密码重置
# 5.7版本密码重置
ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码';
FLUSH PRIVILEGES;
十、实战案例：订单管理系统
-- 创建订单表
CREATE TABLE orders (
order_id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
total_amount DECIMAL(10,2),
order_status ENUM('待支付','已支付','已发货','已完成'),
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id)
);
-- 统计月度销售额
SELECT
DATE_FORMAT(created_at, '%Y-%m') AS month,
SUM(total_amount) AS total_sales
FROM orders
GROUP BY month
ORDER BY month DESC;
总结
- 基础操作：掌握CRUD基本语法
- 查询核心：灵活运用WHERE、JOIN、聚合函数
- 性能关键：合理使用索引和EXPLAIN分析
- 实战重点：理解事务、锁机制、字符集设置
建议通过实际项目练习，逐步掌握复杂查询、存储过程、视图等高级功能。可通过MySQL官方文档（5.7版本）深入学习特定语法细节。