精品，架构师总结，MySQL 5.7 查询入门详解

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 优化建议

1. 优先使用覆盖索引
2. 避免在索引列上使用函数
3. 控制单表数据量（建议<500万行）
4. 定期执行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;

---

总结

1. 基础操作：掌握CRUD基本语法
2. 查询核心：灵活运用WHERE、JOIN、聚合函数
3. 性能关键：合理使用索引和EXPLAIN分析
4. 实战重点：理解事务、锁机制、字符集设置

建议通过实际项目练习，逐步掌握复杂查询、存储过程、视图等高级功能。可通过MySQL官方文档（5.7版本）深入学习特定语法细节。