SQL命令大全---超细【保姆级】

前言：以下涵盖了 MySQL 中的常见操作和查询类型。对于更高级的用法和特性，建议查阅 MySQL 的 官方文档。

详细数据库设计教程可也参考：https://blog.csdn.net/weixin_44892179/article/details/141821867

基本连接与退出

• 连接到 MySQL 数据库

mysql -u username -p

这会提示你输入密码。连接到默认的 localhost 和 3306 端口。可以通过 -h 和 -P 参数指定主机和端口。

• 退出 MySQL

EXIT;

查询操作

1. 基本查询

• 选择所有列

SELECT * FROM table_name;

• 选择特定列

SELECT column1, column2 FROM table_name;

2. 条件查询

• 使用 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';

3. 排序与限制

• 排序结果

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行

4. 聚合函数

• 计算总数

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;

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;

6. 子查询

• 作为 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;

数据操作

1. 插入数据

• 插入单行数据

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;

2. 更新数据

• 更新单个字段

UPDATE table_name
SET column_name = new_value
WHERE condition;

• 更新多个字段

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

3. 删除数据

• 删除满足条件的行

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