文章目录
前言
MySQL 是一个流行的关系型数据库管理系统，广泛应用于网站开发、数据存储和企业级应用。下面我们将详细介绍 MySQL 常用的 SQL 语句，包括数据库和表的操作、数据查询、数据更新、索引管理等内容，并结合实际示例进行说明。
0. SQL 语言分类
一般情况下我们将SQL语言分为下面几类：
| 分类 | 功能描述 | 常用 SQL 语句 |
|---|---|---|
| 数据查询语言 (DQL) | 用于查询数据库中的数据。 | SELECT |
| 数据操作语言 (DML) | 用于操作数据库中的数据，包括插入、更新、删除。 | INSERT、UPDATE、DELETE |
| 数据定义语言 (DDL) | 用于定义和管理数据库对象的结构，如创建、修改、删除数据库和表。 | CREATE、ALTER、DROP |
| 数据控制语言 (DCL) | 用于控制数据库访问权限，授予或撤销用户的权限。 | GRANT、REVOKE |
| 事务控制语言 (TCL) | 用于管理事务的执行，如提交、回滚事务等。 | COMMIT、ROLLBACK、SAVEPOINT、SET TRANSACTION |
通过英文缩写就能很方便的记住，比如对于数据查询语言（DQL），即 Data Query Language
1. 数据库操作
1.1 创建数据库
使用 CREATE DATABASE 语句可以创建新的数据库。
CREATE DATABASE my_database;
如果数据库已经存在，可以使用 IF NOT EXISTS 来避免报错：
CREATE DATABASE IF NOT EXISTS my_database;
1.2 查看数据库
要查看当前服务器上的所有数据库，可以使用 SHOW DATABASES：
SHOW DATABASES;
1.3 选择数据库
使用 USE 语句来选择当前操作的数据库：
USE my_database;
1.4 删除数据库
要删除一个数据库，使用 DROP DATABASE 语句。请注意，这会删除数据库及其所有内容。
DROP DATABASE my_database;
2. 表操作
2.1 创建表
在 MySQL 中创建表使用 CREATE TABLE 语句。可以指定表的列、数据类型、约束条件等。
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE,
age INT
);
2.2 查看表结构
使用 DESCRIBE 或 SHOW COLUMNS 查看表结构：
DESCRIBE users;
或
SHOW COLUMNS FROM users;
比如对于上文创建的用户表，执行查看语句后，有输出：
2.3 查看表列表
可以查看当前数据库中的所有表：
SHOW TABLES;
2.4 删除表
要删除一个表，使用 DROP TABLE 语句。删除表时，表中的所有数据将被永久删除。
DROP TABLE users;
2.5 修改表
添加列：使用
ALTER TABLE添加新列：ALTER TABLE users ADD COLUMN phone VARCHAR(20);删除列：删除指定列：
ALTER TABLE users DROP COLUMN phone;修改列数据类型：
ALTER TABLE users MODIFY COLUMN name newname VARCHAR(200);重命名表：
ALTER TABLE users RENAME TO customers;
3. 数据操作
3.1 插入数据
使用 INSERT INTO 插入数据到表中。可以插入单条或多条记录。
插入单条记录：
INSERT INTO users (name, email, age) VALUES ('Alice', 'alice@example.com', 25);插入多条记录：
INSERT INTO users (name, email, age) VALUES ('Bob', 'bob@example.com', 30), ('Charlie', 'charlie@example.com', 35);
3.2 查询数据
使用 SELECT 查询数据。可以指定查询条件、排序、分组等。
查询所有数据：
SELECT * FROM users;查询特定列：
SELECT name, age FROM users;使用
WHERE过滤数据：SELECT * FROM users WHERE age > 30;排序查询结果：
使用
ORDER BY进行排序，默认是升序（ASC）升序：
SELECT * FROM users ORDER BY age ASC;降序：
SELECT * FROM users ORDER BY age DESC;限制查询结果：
SELECT * FROM users LIMIT 10; -- 获取前 10 条记录
3.3 更新数据
使用 UPDATE 语句更新表中的数据。必须指定 WHERE 子句，否则会更新表中所有记录。
UPDATE users
SET age = 28
WHERE name = 'Alice';
3.4 删除数据
使用 DELETE 语句删除表中的数据。删除时也应使用 WHERE 子句来避免删除所有记录。
DELETE FROM users WHERE name = 'Bob';
4. 查询优化与高级操作
4.1 使用索引
为了加速查询，可以使用索引。创建索引的基本语法如下：
CREATE INDEX idx_name ON users(name);
删除索引：
DROP INDEX idx_name ON users;
关于索引，具体全面的解释在下文：
4.2 使用 JOIN 连接查询
JOIN 用于将两个或多个表的数据进行组合。常见的 JOIN 类型有：INNER JOIN、LEFT JOIN、RIGHT JOIN。
内连接（
INNER JOIN）：返回两个表中匹配的行。SELECT users.name, orders.amount FROM users INNER JOIN orders ON users.id = orders.user_id;左连接（
LEFT JOIN）：返回左表的所有行和右表中匹配的行。SELECT users.name, orders.amount FROM users LEFT JOIN orders ON users.id = orders.user_id;右连接（
RIGHT JOIN）：返回右表的所有行，以及左表中匹配的行（如果没有匹配，左表列为 NULL）。
4.3 分组与聚合函数
分组：使用
GROUP BY将查询结果分组。SELECT age, COUNT(*) AS count FROM users GROUP BY age;上面的语句即统计users表中每个age的出现次数，将次数放到count列中
聚合函数：如
COUNT(),SUM(),AVG(),MAX(),MIN()。SELECT AVG(age) AS average_age FROM users;
下面对这些聚合函数进行总结：
| 聚合函数 | 功能描述 | 示例用法 |
|---|---|---|
COUNT() |
计算指定列的非 NULL 值的数量，常用于统计行数。 |
SELECT COUNT(*) FROM users; |
SUM() |
计算指定列的数值总和。 | SELECT SUM(salary) FROM employees; |
AVG() |
计算指定列的数值平均值。 | SELECT AVG(age) FROM users; |
MAX() |
返回指定列的最大值。 | SELECT MAX(age) FROM users; |
MIN() |
返回指定列的最小值。 | SELECT MIN(age) FROM users; |
4.4 使用 HAVING 进行分组后过滤
HAVING 是对 GROUP BY 结果的过滤条件，类似于 WHERE，但是它适用于聚合结果。
SELECT age, COUNT(*) AS count
FROM users
GROUP BY age
HAVING COUNT(*) > 1;
4.5 子查询
子查询是在主查询中嵌套的查询，可以用来作为条件、返回值等。
单行子查询：
SELECT * FROM users WHERE age = (SELECT MAX(age) FROM users);多行子查询：
SELECT * FROM users WHERE age IN (SELECT age FROM users WHERE age > 30);
5. 事务管理
5.1 启动事务
事务是数据库操作的基本单位，多个 SQL 语句可以作为一个事务执行。
START TRANSACTION;
5.2 提交事务
当事务中的所有操作成功时，使用 COMMIT 提交事务，保存更改。
COMMIT;
5.3 回滚事务
如果事务中的某个操作失败，可以使用 ROLLBACK 来回滚事务，撤销之前的操作。
ROLLBACK;
5.4 设置事务隔离级别
MySQL 支持以下事务隔离级别：READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;