MySQL 数据库 SQL 语句全面解析与实战总结

sfmyf

于 2025-04-24 12:13:33 发布

阅读量1k

点赞数 12

文章标签：数据库 oracle

本文链接：https://blog.csdn.net/2501_91809383/article/details/147475518

版权

前言

在《MySQL 数据库技术》课程中，SQL 语句是核心知识体系。本文从语法规范、应用场景、易错点及实战案例四个维度，对 MySQL 常用 SQL 语句进行系统性整理，结合官方文档（参考MySQL 8.0 官方文档）和实战经验，帮助读者构建完整的 SQL 知识框架。

应用场景
数据库初始化：CREATE DATABASE用于创建空数据库
表结构设计：定义字段类型（如INT UNSIGNED、VARCHAR(50)）、约束（主键、外键、唯一键等）
注意事项
字段类型需匹配业务场景：字符串长度不足会导致插入失败（如VARCHAR(10)存储 11 个字符）
外键约束需确保引用表存在，且数据类型一致

2. ALTER 语句

语法规范

-- 添加字段
ALTER TABLE table_name ADD COLUMN new_col data_type [AFTER col_name];

-- 修改字段
ALTER TABLE table_name MODIFY COLUMN col_name new_data_type;

-- 删除字段
ALTER TABLE table_name DROP COLUMN col_name;

易错点
修改字段时可能导致数据丢失：如将VARCHAR(20)改为VARCHAR(10)，超过长度的数据会被截断
生产环境慎用ALTER TABLE：会锁表影响性能，建议使用pt-osc工具

二、数据操作语言（DML）

1.INSERT 语句

语法规范

-- 单条插入
INSERT INTO table_name (col1, col2) VALUES (val1, val2);

-- 批量插入
INSERT INTO table_name VALUES (1,'A'), (2,'B'), (3,'C');

最佳实践

显式指定字段列表：避免因表结构变更导致插入错误
批量插入提升性能：单次插入 100-500 条为宜，减少网络 IO

错误示例

-- 错误：字段数量不匹配
INSERT INTO student (stu_name) VALUES ('张三', 'M'); -- 多出一个值

2.UPDATE 语句

语法规范

UPDATE table_name
SET col1 = val1, col2 = val2
[WHERE condition]; -- 不加WHERE会更新全表！

致命错误

忘记添加WHERE条件：曾有案例误操作导致百万级数据被篡改

事务保护：重要更新需在事务中执行（START TRANSACTION; ... ROLLBACK;）

优化案例

-- 安全更新：给成绩低于60分的学生加10分
UPDATE score_table
SET score = score + 10
WHERE score < 60 AND semester = '2024';

三、数据查询语言（DQL）

1. SELECT 语句

完整语法

SELECT [DISTINCT] col1, col2 
FROM table1 [JOIN table2 ON condition]
WHERE filter_condition
GROUP BY col_name [HAVING group_condition]
ORDER BY col_name [ASC/DESC]
LIMIT offset, count;

核心组件解析

子句	执行顺序	作用场景	典型错误
FROM	1	确定数据源	表名拼写错误（大小写敏感）
WHERE	2	行级过滤	误用聚合函数（如WHERE AVG(score)）
GROUP BY	3	分组统计	非聚合列未包含在 GROUP BY 中
HAVING	4	分组后过滤	混淆 HAVING 与 WHERE 的使用时机
ORDER BY	5	结果排序	排序字段不存在
LIMIT	6	分页控制	偏移量计算错误（如LIMIT 10,5实际取第 11-15 条）

实战：多表关联查询

-- 查询成绩大于80分的学生姓名和课程名（三表JOIN）
SELECT s.stu_name, c.course_name, sc.score
FROM student s
INNER JOIN score sc ON s.stu_id = sc.stu_id
INNER JOIN course c ON sc.course_id = c.course_id
WHERE sc.score > 80
ORDER BY sc.score DESC
LIMIT 0, 10;

2. 聚合函数

函数	作用	注意事项
*COUNT()**	统计行数	比COUNT(col)效率更高（不检查 NULL）
SUM()	求和	对非数值型字段使用会返回 NULL
AVG()	求平均值	忽略 NULL 值
MAX/MIN()	求最值	对字符串类型按字典序比较

反模式案例

-- 错误：HAVING子句不能直接使用SELECT别名
SELECT dept_id, AVG(salary) AS avg_sal
FROM employee
GROUP BY dept_id
HAVING avg_sal > 50000; -- 正确写法，MySQL支持别名引用

四、数据控制语言（DCL）

1. 权限管理

语法规范

-- 授予权限
GRANT privilege ON db_name.table_name TO 'user'@'host' [IDENTIFIED BY 'password'];

-- 回收权限
REVOKE privilege ON db_name.table_name FROM 'user'@'host';

最佳实践

最小权限原则：给应用账号只授予必要的权限（如SELECT, INSERT, UPDATE）
定期审计：使用SHOW GRANTS FOR 'user'@'host';检查权限配置

案例：创建只读用户

-- 创建用户并授予查询权限
CREATE USER 'read_only'@'%' IDENTIFIED BY 'SecurePass123!';
GRANT SELECT ON school_db.* TO 'read_only'@'%';

五、事务控制语言（TCL）

1. 事务操作

核心语句

START TRANSACTION; -- 开启事务
SAVEPOINT sp1;      -- 设置保存点
UPDATE account SET balance = balance - 100 WHERE id = 1;
ROLLBACK TO sp1;    -- 回滚到保存点
COMMIT;             -- 提交事务

关键特性（ACID）

原子性：事务中的操作要么全做，要么全不做

隔离级别：默认REPEATABLE READ，可通过SET TRANSACTION ISOLATION LEVEL调整

常见问题

长事务风险：可能导致锁等待，建议控制事务范围

自动提交：默认开启，可通过SET AUTOCOMMIT=0;关闭

六、进阶 SQL 技巧

1. 窗口函数

语法

SELECT col1, col2,
       RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rank
FROM employee;

应用场景

排名计算（RANK()/DENSE_RANK()）

分组聚合（SUM() OVER (ORDER BY date)）

版本支持

MySQL 8.0 + 开始支持窗口函数，旧版本需用子查询实现

2. 索引优化

创建索引

CREATE INDEX idx_name ON table_name (col1, col2); -- 组合索引

失效场景

左前缀匹配原则：查询条件未包含索引最左列

函数运算：对索引列使用SUBSTRING(col, 1, 3)会导致索引失效

OR条件：当左右字段无索引时，索引失效

七、学习经验总结

1. 易错点汇总

问题分类	典型错误示例	解决方案
语法错误	*SELECT FROM table WHERE id = null**	空值判断用IS NULL
逻辑错误	关联表未加条件导致笛卡尔积	养成先写 JOIN 条件的习惯
性能问题	全表扫描（Missing Index）	使用EXPLAIN分析执行计划
事务问题	忘记提交导致数据未保存	重要操作使用事务 + 确认机制