MySQL

---

一、为什么使用数据库

• 核心价值
  • 数据存储安全性高
  • 数据交互效率高（优于文件存储）
• 应用场景
  • 信息时代数据为王的场景（如金融、电商、社交等）

---

二、数据库类型

类型	存储方式	代表产品
关系型数据库	数据库 → 数据表 → 数据	MySQL, Oracle, SQL Server
非关系型数据库	数据库 → Key-Value 数据	Redis, MongoDB, Hbase

---

三、DB 与 DBMS

• DB (Database)：数据存储的概念，不能直接操作数据
• DBMS (Database Management System)：
  • 实际管理数据的系统（如 MySQL）
  • 注意：程序员常说的“数据库”指 DBMS

---

四、SQL 类型

类型	全称	作用	关键字
DDL	Data Definition Language	定义库/表/约束	CREATE, ALTER, DROP
DML	Data Manipulation Language	增删改数据	INSERT, DELETE, UPDATE
DQL	Data Query Language	查询数据	SELECT
DCL	Data Control Language	权限控制	GRANT, REVOKE
TCL	Transaction Control Language	事务控制	COMMIT, ROLLBACK

---

五、DDL 详解

1. 数据库操作

   -- 创建  
   CREATE DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;  
   -- 查询  
   SHOW DATABASES;  
   SHOW CREATE DATABASE db_name;  
   -- 修改  
   ALTER DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;  
   -- 删除  
   DROP DATABASE IF EXISTS db_name;  
   -- 切换  
   USE db_name;

2. 字符集与排序规则

   • 字符集：
     • utf8mb3（3字节，不推荐） → utf8mb4（4字节，支持表情符号）
   • 排序规则：
     • utf8mb4_0900_ai_ci（不区分大小写，默认）
     • utf8mb4_0900_as_cs（区分大小写）
   • 查看默认配置：

     SHOW VARIABLES LIKE 'character_set_database';  
     SHOW VARIABLES LIKE 'collation_database';

3. 表操作

   -- 创建表  
   CREATE TABLE t_user (  
     id INT PRIMARY KEY,  
     name VARCHAR(100) NOT NULL  
   );  
   -- 查看表  
   SHOW TABLES FROM db_name;  
   DESC t_user;  
   -- 修改表  
   ALTER TABLE t_user ADD age INT;           -- 添加列  
   ALTER TABLE t_user MODIFY age TINYINT;    -- 修改列类型  
   ALTER TABLE t_user DROP age;              -- 删除列  
   ALTER TABLE t_user RENAME TO new_user;    -- 重命名表  
   -- 删除表  
   DROP TABLE t_user;

---

六、MySQL 数据类型

类别	类型	说明
数值型	INT	整型（4字节）
	DECIMAL(M,D)	精确小数（M总位数，D小数位）
	DOUBLE(M,D)	双精度浮点数
字符型	CHAR(N)	定长字符串（0-255字符）
	VARCHAR(N)	变长字符串（0-65535字节）
日期型	DATE	日期（YYYY-MM-DD）
	DATETIME	日期+时间（YYYY-MM-DD HH:MM:SS）
	TIMESTAMP	时间戳（范围：1970-2038年）
其他	ENUM	单选值（如 ENUM('男','女')）
	SET	多选值（如 SET('A','B','C')）

---

七、DML 详解

-- 插入数据  
INSERT INTO t_emp (id, name) VALUES (1, '张三'), (2, '李四');  
-- 修改数据  
UPDATE t_emp SET salary = 50000 WHERE id = 1;  
-- 删除数据  
DELETE FROM t_emp WHERE id = 2;

---

八、DQL 详解（查询）

1. 基础查询

   SELECT id AS 员工编号, name 员工姓名 FROM t_emp;

2. 条件查询

   运算符	示例
   BETWEEN AND	WHERE salary BETWEEN 20000 AND 40000
   IN	WHERE id IN (1002, 1003)
   IS NULL	WHERE gender IS NOT NULL
   LIKE	WHERE name LIKE '张%'
   分支条件 CASE	见下方示例

   分支条件示例：

   SELECT empno, ename,  
     CASE  
       WHEN sal >= 4000 THEN 'A'  
       WHEN sal >= 3000 THEN 'B'  
       ELSE 'C'  
     END AS 工资等级  
   FROM emp;

3. 聚合函数与分组

   函数	作用
   SUM()	求和
   AVG()	平均值
   MAX()	最大值
   MIN()	最小值
   COUNT()	计数

   分组查询：

   -- 各部门平均工资（HAVING过滤分组）  
   SELECT deptno, AVG(sal)  
   FROM emp  
   GROUP BY deptno  
   HAVING AVG(sal) > 9000;

4. 排序与分页

   -- 按薪资降序（ASC升序，DESC降序）  
   SELECT * FROM emp ORDER BY sal DESC;  
   -- 分页（LIMIT 起始下标,每页条数）  
   SELECT * FROM emp LIMIT 0, 5;  -- 第1页  
   SELECT * FROM emp LIMIT 5, 5;  -- 第2页

5. 执行顺序

   FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

---

九、约束

1. 实体完整性

   • 主键约束：PRIMARY KEY（唯一+非空）
   • 唯一约束：UNIQUE（唯一，可为NULL）
   • 自增列：AUTO_INCREMENT（仅整型）

2. 域完整性

   • 非空约束：NOT NULL
   • 默认值：DEFAULT（如 DEFAULT 18）
   • 检查约束：CHECK（如 CHECK(gender IN ('男','女'))）

3. 引用完整性

   • 外键约束：

     -- 创建时添加  
     CONSTRAINT fk_dept FOREIGN KEY(dept_id) REFERENCES dept(id)  
     -- 后期添加  
     ALTER TABLE emp ADD CONSTRAINT fk_dept FOREIGN KEY(dept_id) REFERENCES dept(id);

---

十、表关系与多表查询

1. 表关系

   关系	示例	实现方式
   一对一	用户↔身份证	共享主键或唯一外键
   一对多	部门↔员工	在多的一方添加外键
   多对多	学生↔课程	通过中间表关联

2. 多表查询

   • 内连接（交集）:

     -- 方式1（显式）  
     SELECT * FROM emp e INNER JOIN dept d ON e.dept_id = d.id;  
     -- 方式2（隐式）  
     SELECT * FROM emp e, dept d WHERE e.dept_id = d.id;

   • 外连接：

     -- 左外（左表全显示）  
     SELECT * FROM emp e LEFT JOIN dept d ON e.dept_id = d.id;  
     -- 右外（右表全显示）  
     SELECT * FROM emp e RIGHT JOIN dept d ON e.dept_id = d.id;

   • 自连接（同一表多次引用）:

     -- 查询员工及其经理  
     SELECT e.name 员工, m.name 经理  
     FROM emp e, emp m  
     WHERE e.manager_id = m.id;

   • 子查询：

     类型	示例
     标量子查询	SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE name='张三');
     列子查询	SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE name='IT');
     行子查询	UPDATE emp SET (sal, bonus) = (SELECT avg_sal, avg_bonus FROM tmp) WHERE id=1;

---

十一、事务

1. ACID 特性

   • 原子性（Atomicity）：全成功或全失败
   • 一致性（Consistency）：数据状态一致
   • 隔离性（Isolation）：并发事务互不干扰
   • 持久性（Durability）：提交后永久生效

2. 操作命令

   START TRANSACTION;  -- 开启事务  
   COMMIT;             -- 提交事务  
   ROLLBACK;           -- 回滚事务

3. 隔离级别（解决并发问题）

   级别	脏读	不可重复读	幻读	效率
   READ UNCOMMITTED (1)	✓	✓	✓	最高
   READ COMMITTED (2)	✗	✓	✓	高
   REPEATABLE READ (4)	✗	✗	✓	中（MySQL默认）
   SERIALIZABLE (8)	✗	✗	✗	最低

---

十二、权限管理（DCL）

-- 创建用户  
CREATE USER 'user1'@'%' IDENTIFIED BY 'password';  
-- 授权  
GRANT SELECT, INSERT ON db1.* TO 'user1'@'%';  
-- 撤销权限  
REVOKE ALL ON db1.* FROM 'user1'@'%';  
-- 删除用户  
DROP USER 'user1'@'%';

---

十三、备份与还原

# 备份数据库  
mysqldump -u root -p db_name > /backup.sql  
# 还原数据库  
mysql -u root -p db_name < /backup.sql

---

重点总结：

1. DDL/DML/DQL 是核心操作，尤其 SELECT 复杂查询（分组、连接、子查询）。
2. 约束保障数据完整性，主外键约束是关联查询的基础。
3. 事务的 ACID 特性及隔离级别解决并发问题。
4. 多表查询时注意 内连接 vs 外连接 的数据差异。
5. 生产环境务必用 utf8mb4 字符集支持全字符（如表情符号）。