一、数据定义语言(DDL)
(一)CREATE 语句
1. 功能与应用场景
用于创建数据库、表、索引等对象,是构建数据库结构的基础。比如创建存储用户信息的表,搭建数据库初始框架。
2. 使用规范与示例(创建表)
-- 创建 student 表
CREATE TABLE student (
id INT AUTO_INCREMENT PRIMARY KEY, -- 自增主键
name VARCHAR(50) NOT NULL, -- 非空姓名字段
age INT CHECK (age > 0 AND age < 100), -- 年龄约束
gender ENUM('男','女') -- 性别枚举
);
规范:字段定义需明确数据类型、约束;主键、外键合理设置保障数据完整性。
注意事项:创建表时,若数据库中已存在同名表,会报错;合理规划字段约束,避免过度约束或约束不足影响业务。
常见错误:数据类型不匹配(如将应存字符串的字段设为 INT );约束语法错误(如 CHECK 条件书写不规范 )。
3. 拓展(创建数据库)
CREATE DATABASE school_db CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
指定字符集和校对规则,避免中文乱码等问题。
(二)ALTER 语句
1. 功能与应用场景
修改现有表结构,如添加字段、修改字段类型、删除约束等,适应业务变化对表结构的调整需求。
2. 使用规范与示例(添加字段)
-- 向 student 表添加 email 字段
ALTER TABLE student
ADD email VARCHAR(100) AFTER name; -- AFTER 指定字段位置
规范:明确修改操作(ADD、MODIFY、DROP 等 );调整字段类型时,需考虑数据兼容性。
注意事项:修改字段类型可能导致数据丢失或转换错误(如 VARCHAR 转 INT ,非数字内容会报错 );删除字段前,确认无业务依赖。
常见错误:忘记指定修改的表名;添加字段时语法顺序错误(如关键字位置不对 )。
3. 其他操作示例(修改字段类型、删除约束)
-- 修改 age 字段类型为 TINYINT
ALTER TABLE student
MODIFY age TINYINT;
-- 删除 age 字段的 CHECK 约束(MySQL 需先查询约束名再删除,较复杂,此处简化演示逻辑删除 )
ALTER TABLE student
DROP CHECK age_check;
(三)DROP 语句
1. 功能与应用场景
删除数据库、表、索引等对象,用于清理不再使用的数据库资源。
2. 使用规范与示例(删除表)
-- 删除 student 表
DROP TABLE IF EXISTS student;
规范:谨慎使用,删除后数据无法恢复;可结合 IF EXISTS
避免因对象不存在报错。
注意事项:删除表前,确保关联业务已停用;删除数据库会移除其下所有表等对象,需特别小心。
常见错误:误删重要表或数据库;未处理关联对象(如外键关联表,直接删除主表可能失败 )。
二、数据操作语言(DML)
(一)INSERT 语句
1. 功能与应用场景
向表中插入数据,是新增记录的核心语句,用于初始化数据或实时添加业务数据。
2. 使用规范与示例(插入单条数据)
-- 向 student 表插入数据
INSERT INTO student (name, age, gender)
VALUES ('张三', 20, '男');
规范:字段列表与值列表数量、顺序匹配;遵循表的约束(如非空字段必须赋值 )。
注意事项:批量插入时,合理使用 INSERT INTO...VALUES...,(...),(...)
语法,提升效率;避免插入重复主键数据。
常见错误:字段与值数量不匹配;插入违反约束的数据(如向非空字段插 NULL )。
3. 批量插入示例
INSERT INTO student (name, age, gender)
VALUES
('李四', 21, '女'),
('王五', 22, '男');
(二)UPDATE 语句
1. 功能与应用场景
修改表中现有记录的数据,用于更新业务数据状态(如用户信息修改、订单状态变更 )。
2. 使用规范与示例(修改单条数据)
-- 修改 student 表中 id=1 的记录
UPDATE student
SET age = 22, gender = '女'
WHERE id = 1;
规范:必须搭配 WHERE
子句(除非故意更新全表 ),否则会修改表中所有记录;修改字段值需符合约束。
注意事项:WHERE
条件要精准,避免误改大量数据;更新关联表数据时,注意外键关联影响。
常见错误:遗漏 WHERE
子句导致全表更新;修改后的数据违反约束(如年龄字段改负值 )。
3. 复杂条件更新示例
UPDATE student
SET age = age + 1
WHERE gender = '男' AND age < 30;
(三)DELETE 语句
1. 功能与应用场景
删除表中记录,用于清理无效、过期数据(如删除过期订单、注销用户 )。
2. 使用规范与示例(删除单条数据)
-- 删除 student 表中 id=1 的记录
DELETE FROM student
WHERE id = 1;
规范:搭配 WHERE
子句精准删除;删除前确认数据是否可删(如关联数据处理 )。
注意事项:删除操作不可逆,重要数据删除前建议备份;大规模删除可考虑分批处理,减少锁表影响。
常见错误:遗漏 WHERE
子句删除全表数据;未处理外键关联导致删除失败。
3. 批量删除示例
DELETE FROM student
WHERE age > 30 AND gender = '女';
三、数据查询语言(DQL)
(一)SELECT 语句基础
1. 功能与应用场景
查询表中数据,是最常用的 SQL 语句,用于获取业务所需数据(如查询用户列表、订单详情 )。
2. 使用规范与示例(简单查询)
-- 查询 student 表所有字段
SELECT * FROM student;
-- 查询指定字段
SELECT name, age FROM student;
规范:*
代表所有字段,实际业务中建议明确字段名,避免多余数据传输;合理使用别名(AS )提升可读性。
注意事项:查询大数据量表时,避免全表扫描,合理加条件;注意字段名大小写(MySQL 不区分,但规范书写可提升可读性 )。
常见错误:字段名拼写错误;查询结果集过大导致性能问题。
3. 带别名查询示例
SELECT name AS 姓名, age AS 年龄
FROM student;
(二)WHERE 子句
1. 功能与应用场景
过滤查询结果,只返回符合条件的记录,精准获取目标数据。
2. 使用规范与示例(条件查询)
-- 查询年龄大于 20 的学生
SELECT * FROM student
WHERE age > 20;
-- 多条件查询(且关系)
SELECT * FROM student
WHERE age > 20 AND gender = '男';
规范:条件运算符(>
、<
、=
、AND
、OR
等 )使用正确;复杂条件合理用括号分组。
注意事项:NULL 值判断需用 IS NULL
或 IS NOT NULL
,不能用 =
;避免在 WHERE
子句中对字段做函数运算(影响索引使用 )。
常见错误:逻辑运算符使用错误(如 AND
和 OR
混淆 );条件判断语法错误(如漏写运算符 )。
(三)GROUP BY 与聚合函数
1. 功能与应用场景
分组统计数据,结合聚合函数(COUNT
、SUM
、AVG
等 )实现业务统计需求(如统计各班级人数、各商品销售额 )。
2. 使用规范与示例(统计班级人数)
-- 假设 student 表有 class_id 字段,统计每个班级人数
SELECT class_id, COUNT(*) AS 人数
FROM student
GROUP BY class_id;
规范:GROUP BY
后字段需是查询字段(除聚合函数外 );聚合函数正确使用,明确统计逻辑。
注意事项:GROUP BY
会排序分组结果(可结合 ORDER BY
调整 );避免分组字段为 NULL 导致统计异常。
常见错误:GROUP BY
字段与查询字段不匹配;聚合函数使用错误(如统计数量用 SUM
而非 COUNT
)。
(四)JOIN 关联查询
1. 功能与应用场景
关联多张表查询数据,获取跨表关联业务信息(如查询学生及所属班级信息、订单及商品详情 )。
2. 使用规范与示例(INNER JOIN 内连接)
-- 假设 student 表有 class_id,class 表有 id(班级主键)、class_name(班级名)
SELECT student.name, class.class_name
FROM student
INNER JOIN class ON student.class_id = class.id;
规范:明确关联类型(INNER JOIN、LEFT JOIN 等 );ON
后写关联条件,清晰准确。
注意事项:关联字段数据类型需兼容;多表关联时,注意表别名使用,避免字段名冲突。
常见错误:关联条件错误导致结果集异常;遗漏关联表导致查询错误。
3. LEFT JOIN 左连接示例
SELECT student.name, class.class_name
FROM student
LEFT JOIN class ON student.class_id = class.id;
左连接会返回左表(student )所有记录,即使右表(class )无匹配。
四、数据控制语言(DCL)
(一)GRANT 语句
1. 功能与应用场景
授予用户数据库操作权限,用于管理数据库访问权限,保障数据安全(如给开发人员赋予查询权限、给管理员赋予所有权限 )。
2. 使用规范与示例(授予查询权限)
-- 给用户 user1 授予 student 表查询权限
GRANT SELECT ON school_db.student TO 'user1'@'localhost';
规范:明确权限(SELECT、INSERT、UPDATE 等 )、操作对象(库。表 )、用户及主机;按需授权,遵循最小权限原则。
注意事项:授权后需刷新权限(FLUSH PRIVILEGES
);注意用户主机限制(如 localhost
、%
区别 )。
常见错误:权限类型写错(如 SELET
而非 SELECT
);用户或主机指定错误。
(二)REVOKE 语句
1. 功能与应用场景
收回用户已授予的权限,当业务需求变更或权限分配错误时使用。
2. 使用规范与示例(收回查询权限)
-- 收回 user1 对 student 表的查询权限
REVOKE SELECT ON school_db.student FROM 'user1'@'localhost';
规范:与 GRANT
对应,明确权限、对象、用户及主机;收回后及时验证权限是否生效。
注意事项:超级管理员(root )权限无法被收回;操作需谨慎,避免影响业务。
常见错误:权限、对象、用户等信息与授予时不匹配导致收回失败。
五、事务控制语句
(一)START TRANSACTION、COMMIT、ROLLBACK
1. 功能与应用场景
用于管理事务,保证一组 SQL 操作要么全部成功(COMMIT ),要么全部回滚(ROLLBACK ),保障数据一致性(如转账业务,扣钱和加钱操作需在同一事务 )。
2. 使用规范与示例(转账模拟)
START TRANSACTION;
-- 假设从账户 A 转 100 到账户 B,accounts 表有 id、balance 字段
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
-- 若执行无误,提交事务
COMMIT;
-- 若中间出错,回滚事务
-- ROLLBACK;
规范:事务操作需包含在 START TRANSACTION
和 COMMIT
/ROLLBACK
之间;关键业务逻辑建议使用事务。
注意事项:长事务会占用锁资源,影响并发性能;事务中操作需是同一会话,不同会话不共享事务。
常见错误:忘记提交或回滚事务,导致数据不一致;事务中包含不必要的耗时操作。
六、学习总结与反思
(一)掌握情况
对基础 DDL、DML、DQL 语句应用较熟练,能完成常见增删改查及简单关联查询。但对于复杂的多表关联(如三张及以上表 JOIN )、高级事务应用(嵌套事务等 ),还需加强练习。
(二)经验教训
- 写 SQL 语句前,先梳理业务逻辑和表结构,避免因逻辑不清写出错误语句。
- 操作数据(UPDATE、DELETE 等 )时,一定先加
WHERE
子句并验证条件,多次踩过误操作全表数据的坑 。 - 复杂查询尽量分步调试,逐步添加条件和关联,减少排查错误难度。
(三)疑惑与待解决问题
- 对于超大规模数据(千万级以上 ),如何优化
GROUP BY
和JOIN
操作的性能? - 事务在高并发场景下,除了加锁,还有哪些更高效的处理方式保障数据一致性?
后续计划通过阅读 MySQL 高性能相关书籍、实战优化案例,以及向老师和行业人士请教,解决这些疑惑,进一步提升 SQL 应用水平 。
以上是对 MySQL 常用 SQL 语句的整理总结,涵盖课程学习的核心内容,后续也会持续更新完善,欢迎大家交流探讨,共同进步!