【后端】MySQL 常用 SQL 语句大全

整理一份 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';