整理一份 MySQL 常用 SQL 语句大全，从基础操作到进阶查询，都涵盖。方便日常开发和学习参考。

---

1. 数据库操作

-- 查看所有数据库
SHOW DATABASES;-- 创建数据库
CREATE DATABASE db_name;-- 删除数据库
DROP DATABASE db_name;-- 使用数据库
USE db_name;-- 查看当前数据库
SELECT DATABASE();

---

2. 数据表操作

-- 查看所有表
SHOW TABLES;-- 查看表结构
DESCRIBE table_name;
-- 或
SHOW COLUMNS FROM table_name;-- 创建表
CREATE TABLE user (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(50) NOT NULL,age INT,create_time DATETIME DEFAULT NOW()
);-- 删除表
DROP TABLE table_name;-- 修改表
ALTER TABLE user ADD email VARCHAR(100);
ALTER TABLE user DROP COLUMN age;
ALTER TABLE user MODIFY COLUMN name VARCHAR(100) NOT NULL;
ALTER TABLE user RENAME TO new_user;

---

3. 数据操作（增删改查）

-- 插入数据
INSERT INTO user (name, age) VALUES ('Alice', 25);
INSERT INTO user SET name='Bob', age=30;-- 更新数据
UPDATE user SET age=26 WHERE name='Alice';-- 删除数据
DELETE FROM user WHERE age < 20;-- 查询数据
SELECT * FROM user;
SELECT name, age FROM user WHERE age > 20;
SELECT name AS username, age FROM user;

---

4. 条件查询与排序

-- 条件查询
SELECT * FROM user WHERE age BETWEEN 20 AND 30;
SELECT * FROM user WHERE name LIKE 'A%';
SELECT * FROM user WHERE age IN (20, 25, 30);-- 排序
SELECT * FROM user ORDER BY age DESC, name ASC;

---

5. 聚合函数与分组

-- 聚合函数
SELECT COUNT(*) FROM user;
SELECT AVG(age) FROM user;
SELECT SUM(age) FROM user;
SELECT MAX(age), MIN(age) FROM user;-- 分组
SELECT age, COUNT(*) FROM user GROUP BY age;
SELECT age, AVG(salary) FROM user GROUP BY age HAVING AVG(salary) > 3000;

---

6. 多表查询（JOIN）

-- 内连接
SELECT u.name, d.department_name
FROM user u
JOIN department d ON u.department_id = d.id;-- 左连接
SELECT u.name, d.department_name
FROM user u
LEFT JOIN department d ON u.department_id = d.id;-- 右连接
SELECT u.name, d.department_name
FROM user u
RIGHT JOIN department d ON u.department_id = d.id;-- 自连接
SELECT a.name AS employee, b.name AS manager
FROM user a
JOIN user b ON a.manager_id = b.id;

---

7. 子查询

-- 单行子查询
SELECT name FROM user WHERE age = (SELECT MAX(age) FROM user);-- 多行子查询
SELECT name FROM user WHERE age IN (SELECT age FROM user WHERE age > 25);-- EXISTS 判断
SELECT name FROM user u WHERE EXISTS (SELECT 1 FROM department d WHERE d.id = u.department_id);

---

8. 排序与分页

-- 排序
SELECT * FROM user ORDER BY age DESC;-- 分页
SELECT * FROM user LIMIT 0, 10;  -- 从第1条开始取10条
SELECT * FROM user LIMIT 10 OFFSET 10; -- 跳过前10条取后10条

---

9. 索引操作

-- 创建索引
CREATE INDEX idx_name ON user(name);-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON user(email);-- 删除索引
DROP INDEX idx_name ON user;

---

10. 事务操作

-- 开启事务
START TRANSACTION;-- 提交事务
COMMIT;-- 回滚事务
ROLLBACK;-- 自动提交设置
SET autocommit=0; -- 关闭自动提交

---

11. 数据库函数（常用）

-- 字符串函数
SELECT CONCAT(name,'_',age) FROM user;
SELECT LENGTH(name) FROM user;
SELECT SUBSTRING(name,1,3) FROM user;
SELECT REPLACE(name,'A','B') FROM user;-- 日期函数
SELECT NOW();  -- 当前时间
SELECT CURDATE();  -- 当前日期
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');-- 数学函数
SELECT ROUND(12.345,2);
SELECT CEIL(12.3);
SELECT FLOOR(12.9);
SELECT POW(2,3);

---

12. 权限管理

-- 创建用户
CREATE USER 'test'@'localhost' IDENTIFIED BY '123456';-- 授权
GRANT ALL PRIVILEGES ON db_name.* TO 'test'@'localhost';-- 查看权限
SHOW GRANTS FOR 'test'@'localhost';-- 删除用户
DROP USER 'test'@'localhost';

---