MySQL 入门笔记

MySQL 入门笔记

数据库基础操作与实践

文档结构导航

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 插入操作模板

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 分页方案对比

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. 实战演练建议

1. 在线沙盒练习
   推荐使用 DB Fiddle 进行SQL实验

2. 可视化工具推荐

   • MySQL Workbench (官方工具)
   • DBeaver (开源多平台)

3. 学习路线推荐

/www.db-fiddle.com/) 进行SQL实验

2. 可视化工具推荐

   • MySQL Workbench (官方工具)
   • DBeaver (开源多平台)

3. 学习路线推荐