在本学期的《MySQL 数据库技术》课程中，我们系统学习了各类 SQL 语句，它们是操作数据库的核心工具。为了加深理解、总结经验，本文将对所有 SQL 语句进行全面的知识性整理，涵盖使用规范、应用规则、易错点、应用实战等内容。
一、数据定义语言（DDL）
1. CREATE 语句
使用规范
- 创建数据库：CREATE DATABASE database_name [CHARACTER SET charset_name] [COLLATE collate_name];
- 创建表：CREATE TABLE table_name (column1 data_type [约束条件], column2 data_type [约束条件], ...);
常见约束条件包括PRIMARY KEY（主键）、NOT NULL（非空）、UNIQUE（唯一）、DEFAULT（默认值）、FOREIGN KEY（外键）等。
应用规则
- 创建数据库时，若数据库已存在会报错，可使用CREATE DATABASE IF NOT EXISTS database_name;避免。
- 创建表时，列名不能与 MySQL 关键字重复，数据类型需根据存储数据的类型和范围合理选择，如整数用INT，字符串用VARCHAR等。
易错点
- 外键约束创建时，主表和从表的关联列数据类型必须一致，且主表需存在对应的主键或唯一键。
- 忽略字符集和校对规则设置，可能导致数据存储和比较出现问题，建议根据需求设置合适的字符集，如UTF8。
应用实战
-- 创建学生数据库，字符集为UTF8
CREATE DATABASE student_db CHARACTER SET utf8;
-- 在学生数据库中创建学生表
USE student_db;
CREATE TABLE students (
student_id INT PRIMARY KEY AUTO_INCREMENT,
student_name VARCHAR(50) NOT NULL,
age INT,
gender VARCHAR(10) DEFAULT '男',
class_id INT,
FOREIGN KEY (class_id) REFERENCES classes(class_id)
);
-- 创建班级表
CREATE TABLE classes (
class_id INT PRIMARY KEY,
class_name VARCHAR(50) UNIQUE NOT NULL
);
2. ALTER 语句
使用规范
- 添加列：ALTER TABLE table_name ADD column_name data_type [约束条件];
- 修改列：ALTER TABLE table_name MODIFY column_name new_data_type [约束条件]; 或 ALTER TABLE table_name CHANGE old_column_name new_column_name data_type [约束条件];
- 删除列：ALTER TABLE table_name DROP column_name;
- 重命名表：ALTER TABLE table_name RENAME TO new_table_name;
应用规则
- 添加列时，新列默认添加到表的最后，若要指定位置，可使用FIRST或AFTER column_name。
- 修改列时，MODIFY只能修改列的类型和约束，CHANGE可同时修改列名和类型。
易错点
- 修改列的数据类型时，需确保现有数据与新数据类型兼容，否则可能导致数据丢失或错误。
- 对大表进行ALTER操作时，可能会锁定表，影响数据库性能，建议在低峰期进行。
应用实战
-- 在学生表中添加入学时间列，位于年龄列之后
ALTER TABLE students ADD entry_time DATE AFTER age;
-- 修改学生表中性别列的默认值为'女'
ALTER TABLE students MODIFY gender VARCHAR(10) DEFAULT '女';
-- 删除学生表中无用的列（假设存在无用列unused_col）
ALTER TABLE students DROP unused_col;
-- 重命名班级表为class_info
ALTER TABLE classes RENAME TO class_info;
3. DROP 语句
使用规范
- 删除数据库：DROP DATABASE [IF EXISTS] database_name;
- 删除表：DROP TABLE [IF EXISTS] table_name [, table_name2, ...];
应用规则
- 使用IF EXISTS可以避免删除不存在的数据库或表时报错。
- 删除数据库会删除其中的所有表和数据，操作需谨慎。
易错点
- 误删重要数据库或表，且没有备份，会导致数据永久丢失，建议在操作前确认无误，并做好数据备份。
应用实战
-- 删除测试数据库（若存在）
DROP DATABASE IF EXISTS test_db;
-- 删除临时表（假设存在临时表temp_table）
DROP TABLE IF EXISTS temp_table;
二、数据操作语言（DML）
1. INSERT 语句
使用规范
- 插入单行数据：INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
- 插入多行数据：INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...), (value3, value4, ...), ...;
- 从其他表插入数据：INSERT INTO table_name (column1, column2, ...) SELECT column1, column2, ... FROM another_table;
应用规则
- 列名列表可选，若省略，则需按照表的列顺序插入所有列的数据，且每个值的类型和顺序必须与列匹配。
- 插入的数据要符合列的约束条件，如非空列不能插入NULL，唯一列不能插入重复值。
易错点
- 插入数据时，值的类型与列的数据类型不匹配，会导致插入失败，例如向整数列插入字符串。
- 忽略自增主键列，直接插入值可能导致主键冲突，除非明确需要设置特定的主键值。
应用实战
-- 向班级表插入单行数据
INSERT INTO class_info (class_id, class_name) VALUES (1, '高三一班');
-- 向班级表插入多行数据
INSERT INTO class_info (class_id, class_name) VALUES (2, '高三二班'), (3, '高三三班');
-- 从旧学生表（假设存在old_students表）向新学生表插入数据
INSERT INTO students (student_name, age, gender, class_id, entry_time)
SELECT student_name, age, gender, class_id, entry_time FROM old_students;
2. UPDATE 语句
使用规范
- 更新单表数据：UPDATE table_name SET column1 = value1, column2 = value2, ... [WHERE condition];
- 多表更新（MySQL 支持）：UPDATE table1 JOIN table2 ON condition SET column1 = value1, ...;
应用规则
- WHERE子句用于指定更新的条件，若省略WHERE子句，会更新表中的所有行，操作需谨慎。
- 可以使用表达式或函数作为更新的值，如SET age = age + 1;
易错点
- 忘记添加WHERE子句，导致全表数据被更新，造成严重后果，建议在执行前先使用SELECT语句确认要更新的数据。
- 在多表更新时，关联条件不正确，导致更新错误的数据。
应用实战
-- 将学生表中年龄为18岁的学生性别改为'男'
UPDATE students SET gender = '男' WHERE age = 18;
-- 将高三一班学生的入学时间更新为2023-09-01
UPDATE students s
JOIN class_info c ON s.class_id = c.class_id
SET s.entry_time = '2023-09-01'
WHERE c.class_name = '高三一班';
3. DELETE 语句
使用规范
- 删除单表数据：DELETE FROM table_name [WHERE condition];
- 多表删除（MySQL 支持）：DELETE table1, table2 FROM table1 JOIN table2 ON condition [WHERE condition];
应用规则
- WHERE子句用于指定删除的条件，若省略WHERE子句，会删除表中的所有行，但表结构仍保留。
- DELETE语句删除的数据可以通过事务回滚（如果开启了事务）或备份恢复，而TRUNCATE语句会直接删除表中的所有数据且不可回滚。
易错点
- 同样，忘记添加WHERE子句会导致全表数据被删除，一定要仔细确认条件。
- 在有外键约束的情况下，删除主表数据可能会导致从表数据无法删除，需先删除从表相关数据或取消外键约束。
应用实战
-- 删除学生表中没有班级信息的学生（class_id为NULL）
DELETE FROM students WHERE class_id IS NULL;
-- 删除毕业班级（假设班级表中class_name为'毕业班级'）及其对应的学生
DELETE s, c
FROM students s
JOIN class_info c ON s.class_id = c.class_id
WHERE c.class_name = '毕业班级';
三、数据查询语言（DQL）
1. SELECT 语句
使用规范
- 基本查询：SELECT column1, column2, ... FROM table_name;
- 去重查询：SELECT DISTINCT column1, column2, ... FROM table_name;
- 条件查询：SELECT ... FROM table_name WHERE condition;
- 排序查询：SELECT ... FROM table_name ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;
- 聚合查询：使用聚合函数（如COUNT()、SUM()、AVG()、MAX()、MIN()）结合GROUP BY和HAVING子句。
应用规则
- WHERE子句用于过滤行，HAVING子句用于过滤分组后的结果，GROUP BY用于按指定列分组。
- 列名可以使用别名，如SELECT column1 AS '列1', column2 AS '列2' FROM table_name;
- 支持使用通配符%（匹配任意字符）和_（匹配单个字符）在LIKE条件中。
易错点
- 在使用GROUP BY时，SELECT后面的列要么是聚合函数中的列，要么是GROUP BY后的列，否则会报错（取决于 MySQL 的 SQL 模式）。
- ORDER BY子句默认按升序（ASC）排序，降序需明确指定DESC，多个排序字段按顺序依次排序。
应用实战
-- 查询学生表中所有学生的姓名和年龄，按年龄降序排列
SELECT student_name, age FROM students ORDER BY age DESC;
-- 查询每个班级的学生人数，过滤掉人数少于5人的班级
SELECT class_id, COUNT(student_id) AS student_count
FROM students
GROUP BY class_id
HAVING student_count >= 5;
-- 查询姓名以'张'开头的学生
SELECT student_name FROM students WHERE student_name LIKE '张%';
2. JOIN 操作
使用规范
- 内连接（INNER JOIN）：SELECT ... FROM table1 INNER JOIN table2 ON table1.column = table2.column;
- 左连接（LEFT JOIN）：SELECT ... FROM table1 LEFT JOIN table2 ON table1.column = table2.column;
- 右连接（RIGHT JOIN）：SELECT ... FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;
- 全连接（FULL OUTER JOIN）：MySQL 不直接支持，可通过UNION组合左连接和右连接的结果。
应用规则
- 内连接只返回两个表中连接条件匹配的行，左连接返回左表的所有行和右表匹配的行，右连接反之。
- 连接条件要明确，避免出现笛卡尔积（即没有连接条件或条件错误导致结果行数过多）。
易错点
- 忘记添加连接条件，导致结果出现大量无用数据，甚至数据库性能下降。
- 混淆左连接和右连接的结果，需明确主表和从表的关系。
应用实战
-- 内连接学生表和班级表，查询学生姓名和对应的班级名称
SELECT s.student_name, c.class_name
FROM students s
INNER JOIN class_info c ON s.class_id = c.class_id;
-- 左连接学生表和班级表，查询所有学生及其所在班级（包括没有班级的学生）
SELECT s.student_name, c.class_name
FROM students s
LEFT JOIN class_info c ON s.class_id = c.class_id;
四、数据控制语言（DCL）
1. GRANT 语句
使用规范
- 授予权限：GRANT privilege1, privilege2, ... ON database_name.table_name TO user@host [IDENTIFIED BY 'password'];
- 授予所有权限：GRANT ALL PRIVILEGES ON database_name.* TO user@host;
应用规则
- privilege可以是SELECT、INSERT、UPDATE、DELETE、CREATE、DROP等权限。
- user@host指定用户和允许连接的主机，%表示允许从任何主机连接。
易错点
- 授予过高的权限（如ALL PRIVILEGES）给普通用户，可能导致安全风险，应按需授予最小权限。
- 忘记使用IDENTIFIED BY设置密码，或密码过于简单，增加数据库被攻击的风险。
应用实战
-- 授予用户user1对学生数据库的SELECT和INSERT权限，允许从本地连接，密码为123456
GRANT SELECT, INSERT ON student_db.* TO user1@localhost IDENTIFIED BY '123456';
-- 授予用户user2对学生表的所有权限，允许从任何主机连接
GRANT ALL PRIVILEGES ON student_db.students TO user2@'%';
2. REVOKE 语句
使用规范
- 收回权限：REVOKE privilege1, privilege2, ... ON database_name.table_name FROM user@host;
应用规则
- 收回权限后，用户将不再拥有相应的操作权限。
- 只能收回通过GRANT语句授予的权限，不能收回用户本身的角色权限（如果使用了角色的话）。
易错点
- 错误地收回关键用户的必要权限，导致用户无法正常操作数据库，收回前需确认权限列表。
应用实战
-- 收回用户user1对学生数据库的INSERT权限
REVOKE INSERT ON student_db.* FROM user1@localhost;
-- 收回用户user2对学生表的所有权限
REVOKE ALL PRIVILEGES ON student_db.students FROM user2@'%';
五、学习经验教训与自我提升
1. 学习经验
- 通过实际案例练习，能够更快地掌握 SQL 语句的用法和技巧，比如在课堂上的应用场景实践让我对 JOIN 操作有了更深刻的理解。
- 查阅官方文档和权威资料是解决问题的重要途径，当遇到不确定的语法或规则时，官方文档能提供最准确的信息。
2. 教训总结
- 曾经在使用UPDATE和DELETE语句时忘记添加WHERE子句，导致数据错误修改和删除，后来养成了先使用SELECT语句确认数据的习惯。
- 对一些复杂的 SQL 语句，如带有多个子查询和连接的语句，一开始理解困难，通过分步拆解和逐步调试，逐渐掌握了编写技巧。
3. 自身需要加强的部分
- 对索引的优化和查询性能调优方面还需要进一步学习，虽然掌握了基本的索引创建方法，但在复杂查询场景下如何选择合适的索引还不够熟练。
- 事务处理和存储过程的使用也需要加强实践，目前只是了解了基本概念，还没有在实际项目中灵活运用。
4. 问题与解决
- 问题：在使用外键约束时，有时会出现无法删除主表数据的情况。
- 解决：通过查阅资料和实践，发现是因为从表中存在关联数据，需要先删除从表中的相关数据，或者在删除主表数据时使用级联删除（在创建外键时设置ON DELETE CASCADE）。
通过本次对 SQL 语句的全面整理和总结，我不仅加深了对所学知识的理解，也发现了自己的不足。在今后的学习和实践中，我将继续加强对数据库技术的学习，不断提高自己的应用能力。同时，也希望这篇博文能为其他学习者提供参考，共同进步。
扫一扫
22万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
