一、数据定义语言 (DDL)

数据定义语言用于定义和管理数据库对象，如表、索引、视图等。

1. 数据库操作

-- 创建数据库
CREATE DATABASE database_name;

-- 创建数据库并指定字符集
CREATE DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

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

-- 使用数据库
USE database_name;

-- 删除数据库
DROP DATABASE database_name;

-- 查看当前使用的数据库
SELECT DATABASE();

2. 表操作

-- 创建表
CREATE TABLE table_name (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT DEFAULT 0,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 查看所有表
SHOW TABLES;

-- 查看表结构
DESCRIBE table_name;

-- 查看表详细信息
SHOW CREATE TABLE table_name;

-- 修改表名
ALTER TABLE table_name RENAME TO new_table_name;

-- 添加列
ALTER TABLE table_name ADD COLUMN column_name VARCHAR(50);

-- 添加列并指定位置
ALTER TABLE table_name ADD COLUMN column_name INT AFTER existing_column;

-- 修改列类型
ALTER TABLE table_name MODIFY COLUMN column_name VARCHAR(100);

-- 修改列名
ALTER TABLE table_name CHANGE old_column_name new_column_name VARCHAR(100);

-- 删除列
ALTER TABLE table_name DROP COLUMN column_name;

-- 删除表
DROP TABLE table_name;

-- 清空表数据（保留表结构）
TRUNCATE TABLE table_name;

3. 索引操作

-- 创建普通索引
CREATE INDEX index_name ON table_name(column_name);

-- 创建唯一索引
CREATE UNIQUE INDEX index_name ON table_name(column_name);

-- 创建联合索引
CREATE INDEX index_name ON table_name(column1, column2);

-- 创建全文索引
CREATE FULLTEXT INDEX index_name ON table_name(column_name);

-- 删除索引
DROP INDEX index_name ON table_name;

-- 查看表索引
SHOW INDEX FROM table_name;

二、数据操作语言 (DML)

数据操作语言用于操作数据库中的数据，如插入、更新、删除等。

1. 插入数据

-- 插入单条数据
INSERT INTO table_name (name, age, email) VALUES ('张三', 25, 'zhangsan@example.com');

-- 插入多条数据
INSERT INTO table_name (name, age, email) VALUES 
    ('李四', 26, 'lisi@example.com'),
    ('王五', 27, 'wangwu@example.com');

-- 插入所有列数据
INSERT INTO table_name VALUES (NULL, '赵六', 28, 'zhaoliu@example.com', CURRENT_TIMESTAMP);

-- 从另一个表插入数据
INSERT INTO table_name (name, age) SELECT name, age FROM another_table;

2. 更新数据

-- 更新所有数据
UPDATE table_name SET age = 30;

-- 条件更新
UPDATE table_name SET age = 30 WHERE id = 1;

-- 更新多列
UPDATE table_name SET age = 30, email = 'new_email@example.com' WHERE id = 1;

-- 使用子查询更新
UPDATE table_name SET age = (SELECT age FROM another_table WHERE id = 1) WHERE id = 2;

3. 删除数据

-- 删除所有数据
DELETE FROM table_name;

-- 条件删除
DELETE FROM table_name WHERE id = 1;

-- 多条件删除
DELETE FROM table_name WHERE age > 30 AND gender = '男';

-- 使用子查询删除
DELETE FROM table_name WHERE id IN (SELECT id FROM another_table WHERE age < 20);

三、数据查询语言 (DQL)

数据查询语言用于查询数据库中的数据，是 SQL 中最常用的部分。

1. 基本查询

-- 查询所有列
SELECT * FROM table_name;

-- 查询指定列
SELECT name, age FROM table_name;

-- 别名查询
SELECT name AS 姓名, age AS 年龄 FROM table_name;

-- 去重查询
SELECT DISTINCT name FROM table_name;

-- 条件查询
SELECT * FROM table_name WHERE age > 25;

-- 多条件查询
SELECT * FROM table_name WHERE age > 25 AND gender = '男';

-- 范围查询
SELECT * FROM table_name WHERE age BETWEEN 20 AND 30;

-- IN 查询
SELECT * FROM table_name WHERE name IN ('张三', '李四', '王五');

-- LIKE 查询（%匹配任意字符，_匹配单个字符）
SELECT * FROM table_name WHERE name LIKE '张%';
SELECT * FROM table_name WHERE email LIKE '%@example.com';
SELECT * FROM table_name WHERE name LIKE '张_';

-- NULL 查询
SELECT * FROM table_name WHERE email IS NULL;
SELECT * FROM table_name WHERE email IS NOT NULL;

2. 排序查询

-- 升序排序
SELECT * FROM table_name ORDER BY age ASC;

-- 降序排序
SELECT * FROM table_name ORDER BY age DESC;

-- 多列排序
SELECT * FROM table_name ORDER BY age DESC, name ASC;

3. 限制查询

-- 限制返回行数
SELECT * FROM table_name LIMIT 10;

-- 分页查询（跳过前10行，返回10行）
SELECT * FROM table_name LIMIT 10 OFFSET 10;

-- 分页查询（简化写法）
SELECT * FROM table_name LIMIT 10, 10;

4. 聚合查询

-- 计数
SELECT COUNT(*) FROM table_name;
SELECT COUNT(DISTINCT name) FROM table_name;

-- 求和
SELECT SUM(age) FROM table_name;

-- 平均值
SELECT AVG(age) FROM table_name;

-- 最大值
SELECT MAX(age) FROM table_name;

-- 最小值
SELECT MIN(age) FROM table_name;

5. 分组查询

-- 分组查询
SELECT gender, COUNT(*) FROM table_name GROUP BY gender;

-- 分组查询并过滤
SELECT gender, AVG(age) FROM table_name GROUP BY gender HAVING AVG(age) > 25;

-- 分组查询多列
SELECT gender, department, COUNT(*) FROM table_name GROUP BY gender, department;

6. 连接查询

-- 内连接
SELECT t1.name, t2.department_name 
FROM employees t1 
INNER JOIN departments t2 ON t1.department_id = t2.id;

-- 左连接
SELECT t1.name, t2.department_name 
FROM employees t1 
LEFT JOIN departments t2 ON t1.department_id = t2.id;

-- 右连接
SELECT t1.name, t2.department_name 
FROM employees t1 
RIGHT JOIN departments t2 ON t1.department_id = t2.id;

-- 全连接（MySQL不直接支持，可用UNION实现）
SELECT t1.name, t2.department_name 
FROM employees t1 
LEFT JOIN departments t2 ON t1.department_id = t2.id
UNION
SELECT t1.name, t2.department_name 
FROM employees t1 
RIGHT JOIN departments t2 ON t1.department_id = t2.id;

-- 交叉连接
SELECT t1.name, t2.department_name 
FROM employees t1 
CROSS JOIN departments t2;

7. 子查询

-- 子查询作为条件
SELECT * FROM table_name WHERE age > (SELECT AVG(age) FROM table_name);

-- 子查询作为表
SELECT t1.name, t1.age, t2.avg_age 
FROM table_name t1, (SELECT AVG(age) AS avg_age FROM table_name) t2;

-- IN 子查询
SELECT * FROM table_name WHERE department_id IN (SELECT id FROM departments WHERE name LIKE '销售%');

-- EXISTS 子查询
SELECT * FROM table_name WHERE EXISTS (SELECT 1 FROM orders WHERE user_id = table_name.id);

四、数据控制语言 (DCL)

数据控制语言用于管理用户权限和安全。

-- 创建用户
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

-- 创建允许远程访问的用户
CREATE USER 'username'@'%' IDENTIFIED BY 'password';

-- 授权所有权限
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost';

-- 授权特定数据库权限
GRANT SELECT, INSERT, UPDATE ON database_name.* TO 'username'@'localhost';

-- 授权特定表权限
GRANT SELECT, INSERT ON database_name.table_name TO 'username'@'localhost';

-- 刷新权限
FLUSH PRIVILEGES;

-- 查看用户权限
SHOW GRANTS FOR 'username'@'localhost';

-- 撤销权限
REVOKE UPDATE ON database_name.table_name FROM 'username'@'localhost';

-- 删除用户
DROP USER 'username'@'localhost';

五、事务控制语言 (TCL)

事务控制语言用于管理数据库事务。

-- 开始事务
START TRANSACTION;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

-- 保存点
SAVEPOINT savepoint_name;

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

-- 删除保存点
RELEASE SAVEPOINT savepoint_name;

-- 设置事务隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

六、高级查询

1. 窗口函数

-- 排名函数
SELECT 
    name, 
    age, 
    RANK() OVER (ORDER BY age DESC) AS rank1,
    DENSE_RANK() OVER (ORDER BY age DESC) AS rank2,
    ROW_NUMBER() OVER (ORDER BY age DESC) AS rank3
FROM table_name;

-- 分区排名
SELECT 
    department, 
    name, 
    age, 
    RANK() OVER (PARTITION BY department ORDER BY age DESC) AS dept_rank
FROM employees;

-- 聚合窗口函数
SELECT 
    name, 
    age, 
    AVG(age) OVER () AS avg_age,
    SUM(age) OVER (ORDER BY id) AS cumulative_sum
FROM table_name;

2. 公用表表达式 (CTE)

-- 基本CTE
WITH cte_name AS (
    SELECT * FROM table_name WHERE age > 25
)
SELECT * FROM cte_name;

-- 递归CTE（查询部门层级）
WITH RECURSIVE dept_cte AS (
    SELECT id, name, parent_id, 1 AS level
    FROM departments
    WHERE parent_id IS NULL
    UNION ALL
    SELECT d.id, d.name, d.parent_id, dc.level + 1
    FROM departments d
    INNER JOIN dept_cte dc ON d.parent_id = dc.id
)
SELECT * FROM dept_cte;

七、视图操作

-- 创建视图
CREATE VIEW view_name AS 
SELECT id, name, age FROM table_name WHERE age > 25;

-- 创建可更新视图
CREATE OR REPLACE VIEW view_name AS 
SELECT id, name, age FROM table_name WHERE age > 25;

-- 查询视图
SELECT * FROM view_name;

-- 删除视图
DROP VIEW view_name;

八、存储过程

-- 创建存储过程
DELIMITER //
CREATE PROCEDURE get_employee(IN emp_id INT)
BEGIN
    SELECT * FROM employees WHERE id = emp_id;
END //
DELIMITER ;

-- 创建带输出参数的存储过程
DELIMITER //
CREATE PROCEDURE get_employee_count(OUT total INT)
BEGIN
    SELECT COUNT(*) INTO total FROM employees;
END //
DELIMITER ;

-- 调用存储过程
CALL get_employee(1);

-- 调用带输出参数的存储过程
SET @total = 0;
CALL get_employee_count(@total);
SELECT @total;

-- 删除存储过程
DROP PROCEDURE get_employee;

九、函数

-- 创建自定义函数
DELIMITER //
CREATE FUNCTION calculate_age(birth_date DATE) RETURNS INT
DETERMINISTIC
BEGIN
    RETURN TIMESTAMPDIFF(YEAR, birth_date, CURDATE());
END //
DELIMITER ;

-- 调用函数
SELECT calculate_age('1990-01-01');

-- 删除函数
DROP FUNCTION calculate_age;

-- 常用内置函数
SELECT NOW(); -- 当前日期时间
SELECT CURDATE(); -- 当前日期
SELECT CURTIME(); -- 当前时间
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d'); -- 日期格式化
SELECT CONCAT('Hello', ' ', 'World'); -- 字符串拼接
SELECT SUBSTRING('Hello World', 1, 5); -- 字符串截取
SELECT LENGTH('Hello World'); -- 字符串长度
SELECT UPPER('hello'); -- 转大写
SELECT LOWER('HELLO'); -- 转小写
SELECT ROUND(3.14159, 2); -- 四舍五入
SELECT RAND(); -- 随机数

十、其他常用语句

-- 查看SQL执行历史
SHOW FULL PROCESSLIST;

-- 终止SQL进程
KILL process_id;

-- 查看数据库连接数
SHOW STATUS LIKE 'Threads_connected';

-- 查看慢查询日志配置
SHOW VARIABLES LIKE 'slow_query%';

-- 查看当前会话变量
SHOW VARIABLES;

-- 设置会话变量
SET SESSION sort_buffer_size = 1024000;