MySQL 核心概念与高级应用实践
数据库基础概述
数据库（Database）是用于持久化存储结构化数据的系统，而数据库管理系统（DBMS）则是管理与操作这些数据的核心工具。在关系型数据库中，数据以表格形式组织，每张表由行和列构成。
- 表（Table）：数据存储的基本单元。
- 字段（Column）：表示某一类属性，如姓名、年龄等。
- 元组（Tuple）：即表中的一行记录。
- 主键（Primary Key）：唯一标识一条记录的字段，不可为空且必须唯一。
- 数据冗余：相同数据在多处重复出现，应尽量避免。
- 数据完整性：确保数据准确性和一致性，例如唯一约束、外键约束等。
SQL 分类说明
结构化查询语言（SQL）按功能可分为三类：
- DDL（数据定义语言）：用于定义或修改数据库结构。
CREATE TABLE：创建表ALTER TABLE：修改表结构DROP TABLE：删除表
- DML（数据操作语言）：处理数据本身。
INSERT：插入新数据UPDATE：更新已有数据DELETE：删除数据SELECT：查询数据
- DCL（数据控制语言）：管理访问权限。
GRANT：授予权限REVOKE：撤销权限
MySQL 常用操作命令
切换数据库使用：
USE database_name;
创建数据库：
CREATE DATABASE db_example;
删除数据库：
DROP DATABASE db_example;
创建与管理数据表
CREATE TABLE employee (
id INT PRIMARY KEY,
name VARCHAR(100),
gender CHAR(2),
age INT,
salary DECIMAL(10,2),
hire_date DATE
);
添加主键约束（若未在建表时指定）：
ALTER TABLE employee ADD CONSTRAINT PK_EMP_ID PRIMARY KEY (id);
查看表结构：
DESC employee;
查看建表语句（格式化输出）：
SHOW CREATE TABLE employee \G
数据增删改查操作
插入数据：
INSERT INTO employee (name, gender, age, salary, hire_date) VALUES ('Alice', '女', 28, 8500.00, '2023-04-15');
更新数据：
UPDATE employee SET salary = 9000.00 WHERE name = 'Alice';
删除数据：
DELETE FROM employee WHERE age < 18;
复杂查询示例
查询所有员工信息：
SELECT * FROM employee;
投影特定字段：
SELECT name, salary FROM employee;
设置别名显示：
SELECT name AS 姓名, salary AS 薪资 FROM employee;
去重查询：
SELECT DISTINCT age FROM employee;
条件筛选：
SELECT * FROM employee WHERE id < 3;
分页查询（跳过前4条取2条）：
SELECT * FROM employee LIMIT 4, 2;
计算字段：
SELECT name, salary, salary * 1.2 AS bonus FROM employee;
逻辑组合查询：
SELECT * FROM employee WHERE gender = '男' AND salary > 5000 AND department = '人事部';
范围查询（两种写法等价）：
SELECT * FROM employee WHERE age BETWEEN 20 AND 22;
SELECT * FROM employee WHERE age >= 20 AND age <= 22;
枚举匹配：
SELECT * FROM employee WHERE age IN (19, 20, 25, 30);
模糊匹配：
SELECT * FROM employee WHERE name LIKE '%L%'; — 包含L
SELECT * FROM employee WHERE name LIKE '张%'; — 姓张
SELECT * FROM employee WHERE name LIKE '李_'; — 李开头且两个字
SELECT * FROM employee WHERE name NOT LIKE '李%'; — 不以李开头
空值判断：
SELECT * FROM employee WHERE department IS NULL;
排序规则（先按薪资降序，再按年龄升序）：
SELECT * FROM employee ORDER BY salary DESC, age ASC;
聚合函数与分组统计
统计销售部门整体情况：
SELECT
COUNT(id) AS 总人数,
SUM(salary) * 12 AS 年总支出,
AVG(salary) AS 平均月薪,
MAX(salary) AS 最高薪,
MIN(salary) AS 最低薪,
AVG(age) AS 平均年龄
FROM employee
WHERE department = '销售部';
各部门平均薪资并过滤结果：
SELECT department, AVG(salary) AS avg_salary
FROM employee
GROUP BY department
HAVING avg_salary > 5000;
事务处理机制
事务具备四大特性（ACID）：
- 原子性：操作要么全部成功，要么全部回滚。
- 一致性：事务前后数据状态保持一致。
- 隔离性：并发执行互不干扰。
- 持久性：提交后数据永久保存。
转账示例（张三给李四转500元）：
SET autocommit = 0;
START TRANSACTION;
UPDATE bank SET balance = balance - 500 WHERE name = '张三';
UPDATE bank SET balance = balance + 500 WHERE name = '李四';
-- 若出错可回滚
ROLLBACK;
-- 正常则提交
COMMIT;
常用内置函数
字符串函数
SELECT LENGTH('hello'); -- 长度
SELECT SUBSTR('hello', 2, 3); -- 截取子串
SELECT CONCAT('A', 'B', 'C'); -- 拼接
SELECT TRIM(' abc '); -- 去除空格
SELECT LOWER('ABC'), UPPER('abc');-- 大小写转换
SELECT REPLACE('张 三', ' ', ''); -- 替换字符
SELECT LOCATE('b', 'abcde'); -- 查找位置
数学函数
SELECT RAND(); -- 随机数
SELECT POW(3, 4); -- 幂运算
SELECT FLOOR(5.6); -- 向下取整
SELECT CEILING(5.6); -- 向上取整
SELECT ROUND(5.456, 2); -- 四舍五入保留两位
日期函数
SELECT NOW(), SYSDATE(); -- 当前时间
SELECT DATEDIFF('2017-08-10', '2017-08-07'); -- 天数差
SELECT DATE_ADD('2017-08-07', INTERVAL 7 DAY); -- 加减日期
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d'); -- 格式化输出
类型转换函数
SELECT CAST('123' AS UNSIGNED); -- 转为无符号整型
SELECT CONVERT('456', SIGNED); -- 转为有符号整型
自定义函数示例
创建一个计算某学员平均成绩的函数：
DELIMITER //
CREATE FUNCTION get_avg_score(student_id INT)
RETURNS DECIMAL(5,2)
READS SQL DATA
BEGIN
DECLARE avg_result DECIMAL(5,2);
SELECT AVG(score) INTO avg_result FROM j_score WHERE sno = student_id;
RETURN IFNULL(avg_result, 0);
END //
DELIMITER ;
-- 使用方式
SELECT get_avg_score(1);
子查询优化建议
使用 EXISTS 替代 IN 提升性能，尤其在子查询数据量较大时：
-- 推荐写法（高效）
SELECT * FROM j_student s1
WHERE NOT EXISTS (
SELECT 1 FROM j_score WHERE sno = s1.sno
);
-- 不推荐（大数据量下效率低）
SELECT * FROM j_student
WHERE sno NOT IN (SELECT sno FROM j_score);
一般建议：子查询返回记录不超过一万条时才考虑使用 IN。
存储过程编写
存储过程可包含变量、流程控制和循环结构。
DELIMITER //
CREATE PROCEDURE proc_find_student()
BEGIN
DECLARE var_name VARCHAR(50) DEFAULT '张三';
-- 查询赋值
SELECT sname INTO var_name FROM j_student WHERE sno = 1;
-- 条件判断
IF var_name = '张三' THEN
SELECT '找到张三' AS result;
ELSEIF var_name = '李四' THEN
SELECT '找到李四' AS result;
ELSE
SELECT '未知用户' AS result;
END IF;
END //
DELIMITER ;
循环结构示例
使用 LOOP 实现删除编号从600到699的数据：
DELIMITER //
CREATE PROCEDURE delete_batch_students()
BEGIN
DECLARE idx INT DEFAULT 600;
del_loop: LOOP
DELETE FROM j_student WHERE sno = idx;
SET idx = idx + 1;
IF idx >= 700 THEN
LEAVE del_loop;
END IF;
END LOOP;
END //
DELIMITER ;
CALL delete_batch_students();
数据库设计范式
良好的表结构设计需遵循规范化原则：
- 第一范式（1NF）：确保每一列都是不可分割的原子值。例如地址字段不应包含"国家+省+市"混合内容。
- 第二范式（2NF）：在满足1NF基础上，非主键字段完全依赖于整个主键（适用于联合主键场景）。
- 第三范式（3NF）：消除传递依赖，所有非主键字段直接依赖主键，而非通过其他非主键字段间接依赖。
实际开发中，通常满足前两范式即可，过度规范化可能影响查询性能。
ER 图与模型设计
实体关系图（ER Diagram）是数据库建模的重要工具，帮助可视化表之间的关联关系，如一对多、多对多等。
