MySQL 入门笔记
MySQL 入门笔记
数据库基础操作与实践
文档结构导航
文章目录
- MySQL 入门笔记
- 文档结构导航
- 1. 数据库核心操作
- 1.1 库操作模板
- 1.2 表操作规范
- 1.3 表结构维护
- 2. 数据操作语言 (DML)
- 2.1 插入操作模板
- 2.2 更新删除规范
- 3. 数据查询语言 (DQL)
- 3.1 查询模板示例
- 3.2 分页方案对比
- 4. 常用函数速查
- 4.1 日期处理示例
- 4.2 JSON处理函数
- 5. 高级查询模式
- 5.1 CTE递归查询
- 5.2 窗口函数应用
- 6. 事务控制与优化
- 6.1 事务保存点机制
- 6.2 锁机制对比
- 7. 知识体系扩展
- 7.1 版本差异注意
- 7.2 性能优化技巧
- 8. 实战演练建议
1. 数据库核心操作
1.1 库操作模板
-- 安全创建数据库
CREATE DATABASE IF NOT EXISTS school_db
DEFAULT CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
-- 删除数据库（危险操作！）
DROP DATABASE IF EXISTS temp_db;
1.2 表操作规范
-- 标准建表语句
CREATE TABLE courses (
course_id INT UNSIGNED AUTO_INCREMENT COMMENT '课程ID',
course_name VARCHAR(30) NOT NULL DEFAULT '未命名课程' COMMENT '课程名称',
credit DECIMAL(3,1) UNSIGNED NOT NULL COMMENT '学分',
duration TINYINT UNSIGNED NOT NULL COMMENT '学时',
PRIMARY KEY (course_id),
UNIQUE INDEX idx_course_name (course_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课程信息表';
最佳实践
- 始终指定存储引擎和字符集
- 使用
COMMENT添加字段描述- 主键推荐使用无符号整型
1.3 表结构维护
-- 增加字段示例
ALTER TABLE students
ADD COLUMN wechat VARCHAR(30) COMMENT '微信账号' AFTER email;
-- 修改字段示例
ALTER TABLE teachers
MODIFY COLUMN phone VARCHAR(15) NOT NULL COMMENT '联系电话';
2. 数据操作语言 (DML)
2.1 插入操作模板
| 方式 | 语法示例 | 适用场景 |
|---|---|---|
| 全字段插入 | INSERT INTO table VALUES (...) | 需要插入所有字段时 |
| 定向插入 | INSERT INTO table (col1, col2) VALUES (...) | 字段可选时推荐方式 |
| 批量插入 | INSERT INTO table VALUES (...), (...) | 多数据插入效率优化 |
2.2 更新删除规范
-- 严格WHERE条件更新
UPDATE course_schedule
SET status = '已取消'
WHERE course_id = 1024
AND start_time > NOW();
-- 安全删除操作
DELETE FROM temp_logs
WHERE create_time < DATE_SUB(NOW(), INTERVAL 30 DAY);
危险操作警示
-- 禁用无条件的删除/更新 UPDATE users SET status = 0; -- ❌ 错误示范 DELETE FROM logs; -- ❌ 错误示范
3. 数据查询语言 (DQL)
3.1 查询模板示例
-- 多条件复合查询
SELECT
stu.name AS '姓名',
cls.class_name AS '班级',
cou.course_name AS '课程'
FROM students stu
JOIN classes cls ON stu.class_id = cls.id
JOIN course_selection cou ON stu.id = cou.stu_id
WHERE cls.grade = '2023级'
AND cou.score >= 60
ORDER BY cou.score DESC;
3.2 分页方案对比
| 分页方式 | 语法 | 优劣分析 |
|---|---|---|
| LIMIT传统分页 | LIMIT 0, 10 | 简单易用，大数据量性能差 |
| Keyset分页 | WHERE id > 100 LIMIT 10 | 高效性能，不支持跳页 |
| 窗口函数分页 | ROW_NUMBER() OVER() | SQL2018+ 支持，功能最强大 |
4. 常用函数速查
4.1 日期处理示例
-- 计算学生年龄
SELECT
name,
birth_date,
TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age
FROM students;
-- 下学期开始日期计算
SELECT DATE_ADD(CURDATE(), INTERVAL 3 MONTH);
4.2 JSON处理函数
-- 解析学生扩展信息
SELECT
stu_id,
JSON_EXTRACT(ext_info, '$.emergency_contact') AS emergency
FROM student_info
WHERE JSON_CONTAINS(ext_info, '{"blood_type": "A"}');
5. 高级查询模式
5.1 CTE递归查询
WITH RECURSIVE org_tree AS (
SELECT id, name, parent_id
FROM departments
WHERE id = 1 -- 顶级部门
UNION ALL
SELECT d.id, d.name, d.parent_id
FROM departments d
INNER JOIN org_tree ot ON d.parent_id = ot.id
)
SELECT * FROM org_tree;
5.2 窗口函数应用
SELECT
department,
name,
salary,
RANK() OVER(PARTITION BY department ORDER BY salary DESC) as dept_rank
FROM employees;
6. 事务控制与优化
6.1 事务保存点机制
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SAVEPOINT transfer_start;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
IF @@ERROR_COUNT <> 0 THEN
ROLLBACK TO transfer_start;
END IF;
COMMIT;
6.2 锁机制对比
| 锁类型 | 粒度 | 冲突概率 | 性能影响 |
|---|---|---|---|
| 行级锁 | 细 | 低 | 内存占用高 |
| 表级锁 | 粗 | 高 | 影响吞吐量 |
7. 知识体系扩展
7.1 版本差异注意
-- MySQL 8.0+ 新特性示例
CREATE TABLE temporal_table (
id INT PRIMARY KEY,
sys_period_start DATETIME(6) GENERATED ALWAYS AS ROW START,
sys_period_end DATETIME(6) GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME(sys_period_start, sys_period_end)
) WITH SYSTEM VERSIONING;
7.2 性能优化技巧
-- 查询缓存查看
SHOW VARIABLES LIKE 'query_cache%';
-- 执行计划分析
EXPLAIN ANALYZE SELECT * FROM large_table WHERE status = 'active';
8. 实战演练建议
-
在线沙盒练习
推荐使用 DB Fiddle 进行SQL实验 -
可视化工具推荐
- MySQL Workbench (官方工具)
- DBeaver (开源多平台)
-
学习路线推荐
/www.db-fiddle.com/) 进行SQL实验
-
可视化工具推荐
- MySQL Workbench (官方工具)
- DBeaver (开源多平台)
-
学习路线推荐