MySQL语句大全

1. 数据库操作

创建数据库

CREATE DATABASE mydatabase;

使用数据库

USE mydatabase;

删除数据库

DROP DATABASE mydatabase;

显示所有数据库

SHOW DATABASES;

2. 表操作

创建表

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

删除表

DROP TABLE users;

修改表结构

ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users DROP COLUMN age;
ALTER TABLE users MODIFY COLUMN username VARCHAR(100);

重命名表

RENAME TABLE users TO members;

显示表结构

DESCRIBE users;

显示所有表

SHOW TABLES;

3. 数据操作

插入数据

INSERT INTO users (username, email, age) VALUES ('John', 'john@example.com', 30);

批量插入数据

INSERT INTO users (username, email, age) VALUES 
('Alice', 'alice@example.com', 25),
('Bob', 'bob@example.com', 35),
('Charlie', 'charlie@example.com', 40);

更新数据

UPDATE users SET age = 31 WHERE username = 'John';

删除数据

DELETE FROM users WHERE id = 1;

清空表数据

TRUNCATE TABLE users;

4. 查询操作

基本查询

SELECT * FROM users;
SELECT username, email FROM users;

条件查询

SELECT * FROM users WHERE age > 30;
SELECT * FROM users WHERE username LIKE 'J%';
SELECT * FROM users WHERE age BETWEEN 25 AND 35;

排序

SELECT * FROM users ORDER BY age DESC;
SELECT * FROM users ORDER BY username ASC, age DESC;

分页

SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 10 OFFSET 20;

去重

SELECT DISTINCT age FROM users;

分组

SELECT age, COUNT(*) AS count FROM users GROUP BY age;

聚合函数

SELECT AVG(age) AS average_age FROM users;
SELECT MAX(age) AS max_age, MIN(age) AS min_age FROM users;
SELECT SUM(age) AS total_age FROM users;

子查询

SELECT * FROM users WHERE age > (SELECT AVG(age) FROM users);

连接查询

SELECT users.username, orders.order_id
FROM users
INNER JOIN orders ON users.id = orders.user_id;

SELECT users.username, orders.order_id
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

SELECT users.username, orders.order_id
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

SELECT users.username, orders.order_id
FROM users
FULL OUTER JOIN orders ON users.id = orders.user_id;

UNION 查询

SELECT username FROM users
UNION
SELECT customer_name FROM customers;

条件语句

SELECT username, 
    CASE 
        WHEN age < 18 THEN 'Minor'
        WHEN age BETWEEN 18 AND 65 THEN 'Adult'
        ELSE 'Senior'
    END AS age_group
FROM users;

5. 索引操作

创建索引

CREATE INDEX idx_username ON users (username);

创建唯一索引

CREATE UNIQUE INDEX idx_email ON users (email);

删除索引

DROP INDEX idx_username ON users;

显示索引

SHOW INDEX FROM users;

6. 视图操作

创建视图

CREATE VIEW adult_users AS
SELECT * FROM users WHERE age >= 18;

修改视图

ALTER VIEW adult_users AS
SELECT * FROM users WHERE age >= 21;

删除视图

DROP VIEW adult_users;

7. 存储过程

创建存储过程

DELIMITER //
CREATE PROCEDURE get_users_by_age(IN min_age INT)
BEGIN
    SELECT * FROM users WHERE age >= min_age;
END //
DELIMITER ;

调用存储过程

CALL get_users_by_age(30);

删除存储过程

DROP PROCEDURE get_users_by_age;

8. 触发器

创建触发器

DELIMITER //
CREATE TRIGGER before_user_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    SET NEW.username = LOWER(NEW.username);
END //
DELIMITER ;

删除触发器

DROP TRIGGER before_user_insert;

9. 事务处理

开始事务

START TRANSACTION;

提交事务

COMMIT;

回滚事务

ROLLBACK;

设置保存点

SAVEPOINT my_savepoint;

回滚到保存点

ROLLBACK TO SAVEPOINT my_savepoint;

10. 用户管理

创建用户

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

授予权限

GRANT ALL PRIVILEGES ON mydatabase.* TO 'newuser'@'localhost';

撤销权限

REVOKE ALL PRIVILEGES ON mydatabase.* FROM 'newuser'@'localhost';

删除用户

DROP USER 'newuser'@'localhost';

修改用户密码

ALTER USER 'newuser'@'localhost' IDENTIFIED BY 'newpassword';

11. 数据导入导出

导出数据

SELECT * FROM users
INTO OUTFILE '/tmp/users.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

导入数据

LOAD DATA INFILE '/tmp/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

12. 性能优化

使用 EXPLAIN 分析查询

EXPLAIN SELECT * FROM users WHERE username = 'John';

优化表

OPTIMIZE TABLE users;

分析表

ANALYZE TABLE users;

检查表

CHECK TABLE users;

13. 高级查询技巧

使用 WITH 子句（公共表表达式）

WITH cte AS (
    SELECT id, username, age
    FROM users
    WHERE age > 30
)
SELECT * FROM cte WHERE username LIKE 'A%';

窗口函数

SELECT 
    username, 
    age, 
    ROW_NUMBER() OVER (ORDER BY age) AS row_num,
    RANK() OVER (ORDER BY age) AS rank,
    DENSE_RANK() OVER (ORDER BY age) AS dense_rank
FROM users;

递归查询

WITH RECURSIVE cte AS (
    SELECT id, parent_id, name, 1 AS level
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.parent_id, c.name, cte.level + 1
    FROM categories c
    JOIN cte ON c.parent_id = cte.id
)
SELECT * FROM cte;

全文搜索

CREATE FULLTEXT INDEX idx_fulltext ON articles (title, content);

SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('MySQL tutorial' IN BOOLEAN MODE);

14. JSON 操作

创建 JSON 列

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    attributes JSON
);

插入 JSON 数据

INSERT INTO products (id, name, attributes) VALUES
(1, 'Laptop', '{"brand": "Dell", "cpu": "Intel i7", "ram": 16}');

查询 JSON 数据

SELECT id, name, attributes->>'$.brand' AS brand
FROM products
WHERE attributes->>'$.ram' > 8;

更新 JSON 数据

UPDATE products
SET attributes = JSON_SET(attributes, '$.ram', 32)
WHERE id = 1;

15. 临时表

创建临时表

CREATE TEMPORARY TABLE temp_users AS
SELECT * FROM users WHERE age > 30;

使用临时表

SELECT * FROM temp_users;

16. 分区表

创建分区表

CREATE TABLE sales (
    id INT,
    amount DECIMAL(10,2),
    sale_date DATE
)
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p0 VALUES LESS THAN (2020),
    PARTITION p1 VALUES LESS THAN (2021),
    PARTITION p2 VALUES LESS THAN (2022),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

添加分区

ALTER TABLE sales ADD PARTITION (PARTITION p4 VALUES LESS THAN (2023));

删除分区

ALTER TABLE sales DROP PARTITION p0;

17. 存储引擎

查看支持的存储引擎

SHOW ENGINES;

创建表时指定存储引擎

CREATE TABLE mytable (
    id INT PRIMARY KEY,
    name VARCHAR(50)
) ENGINE = InnoDB;

修改表的存储引擎

ALTER TABLE mytable ENGINE = MyISAM;

18. 字符集和排序规则

查看支持的字符集

SHOW CHARACTER SET;

查看支持的排序规则

SHOW COLLATION;

创建数据库时指定字符集和排序规则

CREATE DATABASE mydatabase
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

修改表的字符集和排序规则

ALTER TABLE users
CONVERT TO CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

19. 备份和恢复

使用 mysqldump 备份数据库

mysqldump -u username -p database_name > backup.sql

恢复数据库

mysql -u username -p database_name < backup.sql

20. 性能监控

查看当前连接

SHOW PROCESSLIST;

查看系统状态变量

SHOW STATUS;

查看表的统计信息

SHOW TABLE STATUS;