-- 1. 创建数据库（推荐指定字符集，utf8mb4支持emoji和全量中文）
CREATE DATABASE [IF NOT EXISTS] 数据库名
DEFAULT CHARSET = utf8mb4
DEFAULT COLLATE = utf8mb4_general_ci;

-- 2. 查看所有数据库
SHOW DATABASES;
-- 查看数据库的创建语句
SHOW CREATE DATABASE 数据库名;

-- 3. 切换/使用数据库
USE 数据库名;

-- 4. 修改数据库字符集
ALTER DATABASE 数据库名 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci;

-- 5. 删除数据库
DROP DATABASE [IF EXISTS] 数据库名;

-- 1. 添加字段
ALTER TABLE 表名 ADD [COLUMN] 字段名 数据类型 [约束] [FIRST|AFTER 已有字段];

-- 2. 修改字段类型/约束
ALTER TABLE 表名 MODIFY [COLUMN] 字段名 新数据类型 [新约束];

-- 3. 修改字段名+类型
ALTER TABLE 表名 CHANGE [COLUMN] 旧字段名 新字段名 数据类型 [约束];

-- 4. 删除字段
ALTER TABLE 表名 DROP [COLUMN] 字段名;

-- 5. 修改表名
ALTER TABLE 旧表名 RENAME [TO] 新表名;

-- 6. 修改表字符集
ALTER TABLE 表名 CONVERT TO CHARSET utf8mb4;

-- 7. 添加/删除主键
ALTER TABLE 表名 ADD PRIMARY KEY (字段名);
ALTER TABLE 表名 DROP PRIMARY KEY;

-- 8. 添加/删除外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (外键字段) REFERENCES 主表(主键字段);
ALTER TABLE 表名 DROP FOREIGN KEY 外键名;

-- 1. 指定字段插入（推荐，兼容性强，不受表字段顺序变更影响）
INSERT INTO 表名 (字段1, 字段2, 字段3)
VALUES (值1, 值2, 值3);

-- 2. 全字段插入（必须和表字段顺序完全一致，不推荐生产使用）
INSERT INTO 表名 VALUES (值1, 值2, 值3, ...);

-- 3. 批量插入（性能远高于单条循环插入，减少IO与事务开销）
INSERT INTO 表名 (字段1, 字段2)
VALUES (v1, v2), (v3, v4), (v5, v6);

-- 4. 插入或更新（唯一键冲突时执行更新，避免重复插入报错）
INSERT INTO 表名 (唯一键字段, 字段2)
VALUES (v1, v2)
ON DUPLICATE KEY UPDATE 字段2 = v2;

-- 5. 插入查询结果（将一张表的数据写入另一张表）
INSERT INTO 目标表 (字段1, 字段2)
SELECT 字段a, 字段b FROM 源表 WHERE 筛选条件;

-- 基础语法
UPDATE 表名
SET 字段1 = 新值1, 字段2 = 新值2
[WHERE 筛选条件]
[ORDER BY 排序字段]
[LIMIT 行数限制];

-- 多表关联更新
UPDATE 表1 t1
JOIN 表2 t2 ON t1.id = t2.t1_id
SET t1.字段 = 新值
WHERE 筛选条件;

-- 1. 指定字段查询（生产推荐，禁止用SELECT *）
SELECT 字段1, 字段2 FROM 表名;

-- 2. 字段别名（简化SQL，解决重名问题）
SELECT 字段1 AS 别名1, 字段2 别名2 FROM 表名;

-- 3. 去重查询（多字段为组合去重）
SELECT DISTINCT 字段1, 字段2 FROM 表名;

-- 4. 常量与表达式查询
SELECT 100, '测试文本', 字段1 * 10 AS 计算结果 FROM 表名;

-- 基础分组：统计每个部门的员工人数、平均薪资
SELECT dept_id,
       COUNT(*) AS emp_num,
       AVG(salary) AS avg_salary
FROM emp
GROUP BY dept_id;

-- 分组后筛选：只保留员工人数大于5的部门
SELECT dept_id, COUNT(*) AS emp_num
FROM emp
GROUP BY dept_id
HAVING emp_num > 5;

-- 1. 内连接（INNER JOIN，默认JOIN）：只返回两张表匹配条件的记录
SELECT t1.*, t2.dept_name
FROM emp t1
INNER JOIN dept t2 ON t1.dept_id = t2.id;

-- 2. 左连接（LEFT JOIN）：返回左表全部记录，右表匹配不到的字段为NULL
SELECT t1.*, t2.dept_name
FROM emp t1
LEFT JOIN dept t2 ON t1.dept_id = t2.id;

-- 3. 右连接（RIGHT JOIN）：返回右表全部记录，左表匹配不到的字段为NULL
SELECT t1.*, t2.dept_name
FROM emp t1
RIGHT JOIN dept t2 ON t1.dept_id = t2.id;

-- 4. 自连接：表自身与自身关联，常用于树形结构、上下级关系
SELECT a.emp_name AS 员工名, b.emp_name AS 上级名
FROM emp a
LEFT JOIN emp b ON a.manager_id = b.id;

-- 5. 交叉连接（CROSS JOIN）：返回两张表的笛卡尔积，慎用！
SELECT * FROM emp CROSS JOIN dept;

-- 1. 标量子查询：返回单个值，配合比较运算符使用
SELECT * FROM emp
WHERE salary > (SELECT AVG(salary) FROM emp);

-- 2. 列子查询：返回一列值，配合IN/ANY/ALL使用
SELECT * FROM emp
WHERE dept_id IN (SELECT id FROM dept WHERE dept_name LIKE '销售%');

-- 3. 行子查询：返回一行多列
SELECT * FROM emp
WHERE (dept_id, salary) = (SELECT dept_id, MAX(salary) FROM emp WHERE dept_id=1);

-- 4. 表子查询：返回多行多列，放在FROM中必须加别名
SELECT t.dept_id, AVG(t.salary)
FROM (SELECT dept_id, salary FROM emp WHERE salary > 5000) t
GROUP BY t.dept_id;

-- 5. EXISTS子查询：判断子查询是否有结果，大数据量下性能优于IN
SELECT * FROM dept d
WHERE EXISTS (SELECT 1 FROM emp e WHERE e.dept_id = d.id);

-- 1. 创建索引
-- 普通索引
CREATE INDEX 索引名 ON 表名 (字段1, 字段2);
-- 唯一索引
CREATE UNIQUE INDEX 索引名 ON 表名 (字段);
-- 建表时创建索引
CREATE TABLE 表名 (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    INDEX idx_name (name),
    UNIQUE INDEX idx_phone (phone)
) ENGINE=InnoDB;

-- 2. 查看索引
SHOW INDEX FROM 表名;

-- 3. 删除索引
DROP INDEX 索引名 ON 表名;

-- 1. 排名函数
-- ROW_NUMBER()：连续排名，相同值排名不同，1,2,3,4
-- RANK()：跳跃排名，相同值排名相同，1,1,3,4
-- DENSE_RANK()：连续排名，相同值排名相同，1,1,2,3
SELECT emp_name, dept_id, salary,
       ROW_NUMBER() OVER(PARTITION BY dept_id ORDER BY salary DESC) AS rank_num
FROM emp;

-- 2. 聚合窗口函数：累计求和、移动平均
SELECT date, amount,
       SUM(amount) OVER(ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum
FROM `order`;

-- 3. 偏移函数：跨行引用
-- LAG(字段,偏移量,默认值)：取当前行之前的行数据
-- LEAD(字段,偏移量,默认值)：取当前行之后的行数据
SELECT date, amount,
       LAG(amount,1,0) OVER(ORDER BY date) AS last_month_amount,
       LEAD(amount,1,0) OVER(ORDER BY date) AS next_month_amount
FROM `order`;

-- 1. 开启事务
START TRANSACTION;
-- 或简写
BEGIN;

-- 2. 业务SQL操作
INSERT INTO 表名 VALUES (...);
UPDATE 表名 SET 字段=值 WHERE ...;

-- 3. 提交事务（成功后执行，永久生效）
COMMIT;

-- 4. 回滚事务（出错时执行，撤销所有操作）
ROLLBACK;

-- 5. 保存点：实现部分回滚
SAVEPOINT sp1;
-- 执行SQL
ROLLBACK TO SAVEPOINT sp1;

-- 6. 关闭自动提交（MySQL默认autocommit=1，每条SQL自动提交）
SET autocommit = 0;

-- 1. 创建视图
CREATE VIEW 视图名 AS
SELECT t1.emp_name, t1.salary, t2.dept_name
FROM emp t1
JOIN dept t2 ON t1.dept_id = t2.id
WITH CHECK OPTION; -- 保证插入/更新的数据符合视图的WHERE条件

-- 2. 查询视图（和查询表语法一致）
SELECT * FROM 视图名;

-- 3. 修改视图
ALTER VIEW 视图名 AS SELECT 语句;

-- 4. 删除视图
DROP VIEW [IF EXISTS] 视图名;

-- 1. 创建存储过程（临时修改结束符，避免与SQL内的;冲突）
DELIMITER //
CREATE PROCEDURE 存储过程名(
    IN 入参名 数据类型,  -- 入参
    OUT 出参名 数据类型  -- 出参
)
BEGIN
    -- 业务逻辑
    SELECT * FROM emp WHERE dept_id = 入参名;
    SET 出参名 = (SELECT COUNT(*) FROM emp WHERE dept_id = 入参名);
END //
DELIMITER ;

-- 2. 调用存储过程
CALL 存储过程名(1, @total_num);
-- 查看出参结果
SELECT @total_num;

-- 3. 删除存储过程
DROP PROCEDURE [IF EXISTS] 存储过程名;

-- 1. 创建函数
DELIMITER //
CREATE FUNCTION 函数名(参数名 数据类型)
RETURNS 返回数据类型
DETERMINISTIC -- 确定性：相同输入返回相同输出
BEGIN
    DECLARE 变量名 数据类型 DEFAULT 0;
    -- 业务逻辑
    SET 变量名 = 参数名 * 1.1;
    RETURN 变量名;
END //
DELIMITER ;

-- 2. 调用函数
SELECT 函数名(100);
SELECT emp_name, 函数名(salary) AS after_tax FROM emp;

-- 3. 删除函数
DROP FUNCTION [IF EXISTS] 函数名;

-- 1. 创建触发器
DELIMITER //
CREATE TRIGGER 触发器名
AFTER INSERT ON emp -- 插入后触发，可选BEFORE/AFTER，INSERT/UPDATE/DELETE
FOR EACH ROW -- 行级触发器，每一行数据变更都触发
BEGIN
    -- 逻辑：NEW代表新行数据，OLD代表修改前的行数据
    INSERT INTO emp_log(operate_type, emp_id, operate_time)
    VALUES ('INSERT', NEW.id, NOW());
END //
DELIMITER ;

-- 2. 查看触发器
SHOW TRIGGERS;

-- 3. 删除触发器
DROP TRIGGER [IF EXISTS] 触发器名;

-- 1. 创建用户
CREATE USER '用户名'@'访问主机' IDENTIFIED BY '复杂密码';
-- 示例：创建允许所有地址访问的用户
CREATE USER 'test_user'@'%' IDENTIFIED BY 'Test@123456';
-- 访问主机：localhost仅本地访问，192.168.1.%允许指定网段访问，%允许所有地址访问

-- 2. 授权
GRANT 权限1,权限2 ON 库名.表名 TO '用户名'@'访问主机';
-- 示例：授予test库所有表的查询、写入权限
GRANT SELECT,INSERT,UPDATE,DELETE ON test_db.* TO 'test_user'@'%';
-- 授予所有权限（慎用，仅管理员账号）
GRANT ALL PRIVILEGES ON *.* TO 'admin_user'@'%' WITH GRANT OPTION;

-- 3. 刷新权限（授权后必须执行，使权限生效）
FLUSH PRIVILEGES;

-- 4. 查看用户权限
SHOW GRANTS FOR 'test_user'@'%';

-- 5. 回收权限
REVOKE DELETE ON test_db.* FROM 'test_user'@'%';

-- 6. 修改用户密码
ALTER USER 'test_user'@'%' IDENTIFIED BY 'NewPass@123456';

-- 7. 删除用户
DROP USER 'test_user'@'%';

-- 当前日期时间
SELECT NOW(), CURDATE(), CURTIME();
-- 日期格式化
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');
-- 字符串转日期
SELECT STR_TO_DATE('2024-01-01', '%Y-%m-%d');
-- 日期加减
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);
-- 日期差值
SELECT DATEDIFF('2024-01-10', '2024-01-01');
SELECT TIMESTAMPDIFF(YEAR, '2000-01-01', NOW()); -- 计算年龄