前言：以下涵盖了 MySQL 中的常见操作和查询类型。对于更高级的用法和特性，建议查阅 MySQL 的 官方文档。
详细数据库设计教程可也参考：https://blog.csdn.net/weixin_44892179/article/details/141821867
基本连接与退出
- 连接到 MySQL 数据库
mysql -u username -p
这会提示你输入密码。连接到默认的 localhost 和 3306 端口。可以通过 -h 和 -P 参数指定主机和端口。
- 退出 MySQL
EXIT;
查询操作
- 基本查询
- 选择所有列
SELECT * FROM table_name;
- 选择特定列
SELECT column1, column2 FROM table_name;
- 条件查询
- 使用 WHERE 子句
SELECT * FROM table_name WHERE condition;
例：
SELECT * FROM employees WHERE department = 'Sales';
- 使用 AND 和 OR
SELECT * FROM table_name WHERE condition1 AND condition2;
SELECT * FROM table_name WHERE condition1 OR condition2;
例：
SELECT * FROM employees WHERE department = 'Sales' AND hire_date > '2024-01-01';
- 使用 IN 和 NOT IN
SELECT * FROM table_name WHERE column_name IN (value1, value2, ...);
SELECT * FROM table_name WHERE column_name NOT IN (value1, value2, ...);
例：
SELECT * FROM employees WHERE department IN ('Sales', 'Marketing');
- 使用 LIKE 和 NOT LIKE
SELECT * FROM table_name WHERE column_name LIKE 'pattern';
SELECT * FROM table_name WHERE column_name NOT LIKE 'pattern';
例：
SELECT * FROM employees WHERE name LIKE 'J%'; -- 以 J 开头的所有名字
- 使用 BETWEEN 和 NOT BETWEEN
SELECT * FROM table_name WHERE column_name BETWEEN value1 AND value2;
SELECT * FROM table_name WHERE column_name NOT BETWEEN value1 AND value2;
例：
SELECT * FROM employees WHERE hire_date BETWEEN '2024-01-01' AND '2024-12-31';
- 排序与限制
- 排序结果
SELECT * FROM table_name ORDER BY column_name [ASC|DESC];
例：
SELECT * FROM employees ORDER BY hire_date DESC;
- 限制返回行数
SELECT * FROM table_name LIMIT number;
例：
SELECT * FROM employees LIMIT 5;
- 限制返回行数并指定偏移量
SELECT * FROM table_name LIMIT offset, number;
例：
SELECT * FROM employees LIMIT 5, 10; -- 从第6行开始，返回10行
- 聚合函数
- 计算总数
SELECT COUNT(column_name) FROM table_name;
- 计算总和
SELECT SUM(column_name) FROM table_name;
- 计算平均值
SELECT AVG(column_name) FROM table_name;
- 计算最大值
SELECT MAX(column_name) FROM table_name;
- 计算最小值
SELECT MIN(column_name) FROM table_name;
- 分组
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;
- 分组并筛选
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING COUNT(*) > value;
例：
SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 5;
- 连接查询
- 内连接（INNER JOIN）
SELECT columns
FROM table1
INNER JOIN table2 ON table1.common_column = table2.common_column;
例：
SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;
- 左连接（LEFT JOIN）
SELECT columns
FROM table1
LEFT JOIN table2 ON table1.common_column = table2.common_column;
例：
SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id;
- 右连接（RIGHT JOIN）
SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.common_column = table2.common_column;
例：
SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.id;
- 全连接（FULL JOIN） MySQL 不直接支持 FULL JOIN，但可以通过 UNION 实现类似效果：
SELECT columns
FROM table1
LEFT JOIN table2 ON table1.common_column = table2.common_column
UNION
SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.common_column = table2.common_column;
- 子查询
- 作为 SELECT 的一部分
SELECT column_name
FROM table_name
WHERE column_name = (SELECT column_name FROM other_table WHERE condition);
例：
SELECT name
FROM employees
WHERE department_id = (SELECT id FROM departments WHERE department_name = 'Sales');
- 作为 FROM 的一部分
SELECT *
FROM (SELECT column_name FROM table_name WHERE condition) AS alias;
例：
SELECT *
FROM (SELECT name FROM employees WHERE hire_date > '2024-01-01') AS recent_employees;
数据操作
- 插入数据
- 插入单行数据
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
- 插入多行数据
INSERT INTO table_name (column1, column2, ...)
VALUES (value1a, value2a, ...), (value1b, value2b, ...), ...;
- 从另一个表插入数据
INSERT INTO table_name (column1, column2, ...)
SELECT column1, column2, ...
FROM other_table
WHERE condition;
- 更新数据
- 更新单个字段
UPDATE table_name
SET column_name = new_value
WHERE condition;
- 更新多个字段
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
- 删除数据
- 删除满足条件的行
DELETE FROM table_name
WHERE condition;
- 删除所有行
DELETE FROM table_name;
事务管理
- 开始事务
START TRANSACTION;
- 提交事务
COMMIT;
- 回滚事务
ROLLBACK;
索引操作
- 创建索引
CREATE INDEX index_name
ON table_name (column_name);
- 删除索引
DROP INDEX index_name
ON table_name;
用户管理
- 创建用户
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
- 授予权限
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host';
- 撤销权限
REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'host';
- 删除用户
DROP USER 'username'@'host';
备份和恢复
- 备份数据库
mysqldump -u username -p database_name > backup.sql
- 恢复数据库
mysql -u username -p database_name < backup.sql