MySQL中有哪些语句收集

# MySQL中有哪些语句收集

## 引言

MySQL作为最流行的开源关系型数据库之一，其语句体系是数据库操作的核心。本文将全面梳理MySQL中的各类语句，包括数据操作语句(DML)、数据定义语句(DDL)、数据控制语句(DCL)和事务控制语句(TCL)，并深入探讨实用技巧和性能优化相关语句。

## 一、基础语句分类

### 1. 数据定义语言(DDL)

#### 数据库操作
```sql
-- 创建数据库
CREATE DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 修改数据库
ALTER DATABASE db_name CHARACTER SET = utf8mb4;

-- 删除数据库
DROP DATABASE IF EXISTS db_name;

表操作

-- 创建表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- 修改表结构
ALTER TABLE users ADD COLUMN email VARCHAR(100) AFTER username;

-- 重命名表
RENAME TABLE old_name TO new_name;

-- 截断表(清空数据)
TRUNCATE TABLE temp_data;

索引操作

-- 创建索引
CREATE INDEX idx_name ON users(username);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_unique_email ON users(email);

-- 删除索引
DROP INDEX idx_name ON users;

2. 数据操作语言(DML)

基本CRUD操作

-- 插入数据
INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com');

-- 批量插入
INSERT INTO users (username, email) VALUES 
('user1', 'user1@example.com'),
('user2', 'user2@example.com');

-- 更新数据
UPDATE users SET email = 'new_email@example.com' WHERE id = 1;

-- 删除数据
DELETE FROM users WHERE id = 5;

查询语句

-- 基础查询
SELECT id, username FROM users WHERE status = 'active';

-- 分页查询
SELECT * FROM products LIMIT 10 OFFSET 20;  -- 等价于 LIMIT 20, 10

-- 聚合查询
SELECT department_id, AVG(salary) as avg_salary 
FROM employees 
GROUP BY department_id 
HAVING AVG(salary) > 5000;

3. 数据控制语言(DCL)

-- 创建用户
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'secure_password';

-- 授权
GRANT SELECT, INSERT ON db_name.* TO 'app_user'@'localhost';

-- 撤销权限
REVOKE INSERT ON db_name.* FROM 'app_user'@'localhost';

-- 刷新权限
FLUSH PRIVILEGES;

4. 事务控制语言(TCL)

-- 开始事务
START TRANSACTION;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

-- 设置保存点
SAVEPOINT savepoint_name;

-- 回滚到保存点
ROLLBACK TO savepoint_name;

二、高级查询语句

1. 连接查询

-- 内连接
SELECT o.order_id, c.customer_name 
FROM orders o 
JOIN customers c ON o.customer_id = c.id;

-- 左外连接
SELECT u.username, p.post_title 
FROM users u 
LEFT JOIN posts p ON u.id = p.author_id;

-- 自连接
SELECT e1.name AS employee, e2.name AS manager
FROM employees e1 
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

2. 子查询

-- WHERE子句中的子查询
SELECT product_name FROM products 
WHERE category_id IN (SELECT id FROM categories WHERE is_active = 1);

-- FROM子句中的子查询
SELECT avg_sales.category_id, categories.name
FROM (
    SELECT category_id, AVG(price) as avg_price 
    FROM products 
    GROUP BY category_id
) AS avg_sales
JOIN categories ON avg_sales.category_id = categories.id;

3. 集合操作

-- 并集(UNION会自动去重)
SELECT product_id FROM current_products
UNION
SELECT product_id FROM discontinued_products;

-- 交集(MySQL 8.0+)
SELECT product_id FROM inventory 
INTERSECT 
SELECT product_id FROM discounted_products;

-- 差集
SELECT customer_id FROM all_customers
EXCEPT 
SELECT customer_id FROM active_customers;

三、实用语句收集

1. 数据库维护语句

-- 查看表状态
SHOW TABLE STATUS LIKE 'users';

-- 分析表(更新统计信息)
ANALYZE TABLE orders;

-- 检查表错误
CHECK TABLE important_data;

-- 修复表
REPR TABLE corrupted_table;

2. 元数据查询

-- 查看所有数据库
SHOW DATABASES;

-- 查看表结构
DESCRIBE products;
-- 或
SHOW COLUMNS FROM products;

-- 查看建表语句
SHOW CREATE TABLE customers;

-- 查看进程列表
SHOW PROCESSLIST;

3. 性能相关语句

-- 解释执行计划
EXPLN SELECT * FROM users WHERE username LIKE 'j%';

-- 优化表(整理碎片)
OPTIMIZE TABLE large_table;

-- 设置变量
SET SESSION sort_buffer_size = 1000000;

-- 查看索引使用情况
SHOW INDEX FROM orders;

4. 导入导出语句

-- 导出数据到文件
SELECT * INTO OUTFILE '/tmp/products.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM products;

-- 从文件导入数据
LOAD DATA INFILE '/tmp/users.csv' 
INTO TABLE users 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

四、MySQL 8.0新增语句

1. 窗口函数

-- 排名函数
SELECT 
    product_id,
    product_name,
    price,
    RANK() OVER (PARTITION BY category_id ORDER BY price DESC) as price_rank
FROM products;

-- 累计求和
SELECT 
    month,
    revenue,
    SUM(revenue) OVER (ORDER BY month) as cumulative_revenue
FROM monthly_sales;

2. CTE (公共表表达式)

-- 简单CTE
WITH regional_sales AS (
    SELECT region, SUM(amount) as total_sales
    FROM orders
    GROUP BY region
)
SELECT region, total_sales
FROM regional_sales
WHERE total_sales > 100000;

-- 递归CTE(生成序列)
WITH RECURSIVE number_sequence AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM number_sequence WHERE n < 100
)
SELECT n FROM number_sequence;

3. JSON相关语句

-- 创建JSON字段
CREATE TABLE product_catalog (
    id INT PRIMARY KEY,
    details JSON
);

-- 查询JSON字段
SELECT id, details->>'$.name' AS product_name
FROM product_catalog
WHERE details->>'$.price' > 100;

-- 修改JSON字段
UPDATE product_catalog
SET details = JSON_SET(details, '$.stock', 50)
WHERE id = 101;

五、存储过程和函数

1. 存储过程

DELIMITER //
CREATE PROCEDURE update_product_price(
    IN product_id INT,
    IN increase_percent DECIMAL(5,2)
)
BEGIN
    UPDATE products 
    SET price = price * (1 + increase_percent / 100)
    WHERE id = product_id;
    
    SELECT ROW_COUNT() AS affected_rows;
END //
DELIMITER ;

-- 调用存储过程
CALL update_product_price(123, 10.0);

2. 自定义函数

DELIMITER //
CREATE FUNCTION calculate_discount(
    original_price DECIMAL(10,2),
    discount_rate DECIMAL(5,2)
) 
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    DECLARE final_price DECIMAL(10,2);
    SET final_price = original_price * (1 - discount_rate / 100);
    RETURN final_price;
END //
DELIMITER ;

-- 使用函数
SELECT product_name, calculate_discount(price, 15) AS discounted_price
FROM products;

六、安全相关语句

1. 密码管理

-- 修改用户密码
ALTER USER 'app_user'@'localhost' IDENTIFIED BY 'new_secure_password';

-- 密码过期策略
ALTER USER 'admin'@'%' PASSWORD EXPIRE INTERVAL 90 DAY;

-- 查看密码策略
SHOW VARIABLES LIKE 'validate_password%';

2. 审计相关

-- 启用通用查询日志(谨慎使用)
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/mysql-query.log';

-- 查看慢查询
SHOW VARIABLES LIKE 'slow_query_log%';

七、实用技巧语句

1. 批量操作优化

-- 批量插入忽略重复
INSERT IGNORE INTO users (username, email) VALUES 
('user1', 'user1@example.com'),
('user2', 'user2@example.com');

-- 批量更新不同值
INSERT INTO products (id, stock) VALUES 
(1, 10), 
(2, 20)
ON DUPLICATE KEY UPDATE stock = VALUES(stock);

2. 动态SQL准备

-- 准备语句
PREPARE stmt FROM 'SELECT * FROM products WHERE price > ?';

-- 设置参数并执行
SET @min_price = 100;
EXECUTE stmt USING @min_price;

-- 释放语句
DEALLOCATE PREPARE stmt;

结语

MySQL的语句体系丰富而强大，从基础CRUD到高级分析功能应有尽有。本文整理了300+条实用语句，覆盖了日常开发和管理中的大多数场景。掌握这些语句不仅能提高工作效率，还能帮助开发者更好地优化数据库性能。随着MySQL版本的更新，更多强大的语句和功能不断加入，建议持续关注官方文档以获取最新信息。

注意：实际执行语句前，请确保在生产环境有完整备份，并在测试环境验证语句效果。某些语句可能需要特定权限才能执行。 “`