数据库原理与应用

✨

课程亮点

🗄️从零到精通
数据库原理 + MySQL实操 + SQL实战全覆盖

🔍查询实战
单表查询到多表连接，循序渐进掌握SQL

⚡性能优化
索引原理与优化技巧，提升查询效率

🗺️

学习路径

阶段 1：数据库基础概念

关系模型、ER图、三大范式、键的概念

阶段 2：MySQL环境搭建

安装配置、命令行操作、图形化工具

阶段 3：SQL核心操作

DDL/DML/DQL、聚合函数、分组查询

阶段 4：高级查询与优化

多表连接、子查询、视图、索引优化

👥

适合人群

✅ 适合学习：
· 零基础想学数据库的同学
· 数据分析专业需要SQL技能的同学
· 想从事后端开发或数据分析的同学

⚠️ 建议先补基础：
· 基本的电脑操作能力
· 了解表格数据的基本概念

🎓

学完能做什么

学完本课程，你将能够：

独立设计关系型数据库表结构
熟练编写SQL进行数据查询和分析
使用多表连接解决复杂业务查询
通过索引优化提升数据库查询性能

01

数据库基础概念

1.1 什么是数据库

数据库（Database，简称 DB）是按照一定的数据模型组织、存储和管理数据的仓库。数据库管理系统（Database Management System，简称 DBMS）则是用于管理数据库的软件系统，常见的有 MySQL、Oracle、SQL Server、PostgreSQL 等。

数据库系统（Database System，简称 DBS）通常由数据库、数据库管理系统、应用程序和数据库管理员（DBA）共同组成。

💡 提示：数据库的核心优势在于数据的持久化存储、高效检索、并发访问控制以及数据的一致性和完整性保障。

1.2 数据模型

数据模型是对现实世界数据特征的抽象，主要分为以下三种：

层次模型（Hierarchical Model）：用树形结构表示数据之间的联系，每个节点只有一个父节点。典型代表是 IBM 的 IMS 系统。
网状模型（Network Model）：允许一个节点有多个父节点，用有向图结构表示数据联系。典型代表是 DBTG 系统。
关系模型（Relational Model）：用二维表格结构表示数据及数据之间的联系，是目前最主流的数据模型。典型代表是 MySQL、Oracle 等关系型数据库。

模型类型	数据结构	典型代表	优缺点
层次模型	树形结构	IMS	结构简单，但难以表达多对多关系
网状模型	有向图	DBTG	能表达复杂关系，但结构复杂
关系模型	二维表格	MySQL / Oracle	理论基础坚实，使用 SQL 统一操作

1.3 关系数据库基础

关系数据库是基于关系模型的数据库，数据以表格（Table）的形式存储。每个表由行（Row/Record）和列（Column/Field）组成。

关系（Relation）：一张二维表就是一个关系。
元组（Tuple）：表中的一行即为一个元组。
属性（Attribute）：表中的一列即为一个属性。
域（Domain）：属性的取值范围。
模式（Schema）：数据库的结构定义，即"表的结构"。

下面是一个学生表的示例：

SQL
-- 创建学生表
CREATE TABLE student (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    name        VARCHAR(50) NOT NULL,
    gender      ENUM('男', '女') DEFAULT '男',
    age         TINYINT UNSIGNED,
    major       VARCHAR(100),
    enroll_date DATE
);

1.4 键的类型

在关系数据库中，键（Key）用于唯一标识记录和建立表与表之间的联系：

主键（Primary Key, PK）：唯一标识表中的每一行记录，不允许为 NULL 且不允许重复。每张表只能有一个主键。
外键（Foreign Key, FK）：引用另一张表的主键，用于建立表之间的关联关系，保证参照完整性。
唯一键（Unique Key, UK）：保证列值不重复，但允许为 NULL。一张表可以有多个唯一键。

SQL
-- 主键示例
CREATE TABLE department (
    dept_id   INT PRIMARY KEY AUTO_INCREMENT,
    dept_name VARCHAR(50) NOT NULL UNIQUE
);

-- 外键示例：student 表引用 department 表
CREATE TABLE student (
    id      INT PRIMARY KEY AUTO_INCREMENT,
    name    VARCHAR(50) NOT NULL,
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES department(dept_id)
);

✅ 小技巧：在设计表时，建议使用无业务含义的自增整数作为主键（代理键），而不是使用有业务含义的字段（如学号、身份证号）作为主键，这样可以避免业务变更导致主键修改的问题。

1.5 数据库三大范式

数据库范式（Normal Form）是设计关系数据库时需要遵循的规范，目的是减少数据冗余、消除插入/更新/删除异常。最常用的三大范式如下：

第一范式（1NF）

要求每一列都是不可再分的原子值。即表的每个字段只能存储单一值，不能存储集合或重复组。

SQL
-- 不满足1NF的设计（联系方式列存储了多个值）
CREATE TABLE student_bad (
    id   INT PRIMARY KEY,
    name VARCHAR(50),
    contacts VARCHAR(200)  -- 存了电话、邮箱等多个值
);

-- 满足1NF的设计（拆分为独立列）
CREATE TABLE student_1nf (
    id      INT PRIMARY KEY,
    name    VARCHAR(50),
    phone   VARCHAR(20),
    email   VARCHAR(100)
);

第二范式（2NF）

在满足1NF的基础上，要求非主属性完全依赖于主键，不能存在部分依赖（仅适用于复合主键的情况）。

第三范式（3NF）

在满足2NF的基础上，要求非主属性直接依赖于主键，不能存在传递依赖。

SQL
-- 不满足3NF的设计（存在传递依赖：系名依赖于系ID，系ID依赖于学生）
CREATE TABLE student_bad3nf (
    id      INT PRIMARY KEY,
    name    VARCHAR(50),
    dept_id INT,
    dept_name VARCHAR(50)  -- 传递依赖！
);

-- 满足3NF的设计（拆分成两张表）
CREATE TABLE department (
    dept_id   INT PRIMARY KEY,
    dept_name VARCHAR(50)
);

CREATE TABLE student_3nf (
    id      INT PRIMARY KEY,
    name    VARCHAR(50),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES department(dept_id)
);

💡 提示：在实际项目中，并不一定要严格遵守所有范式。有时为了查询性能，会适当进行反范式设计（如冗余存储某些字段），这需要在冗余与性能之间做权衡。

02

MySQL环境搭建

2.1 MySQL安装与配置

MySQL 是目前最流行的开源关系型数据库管理系统之一。以下是常见的安装方式：

Windows：从 MySQL 官网下载安装包（MSI 安装器），安装时选择 Server Only 或 Developer Default，设置 root 密码。
macOS：使用 Homebrew 安装：brew install mysql，然后 brew services start mysql 启动服务。
Linux（Ubuntu/Debian）：使用 APT 包管理器安装。

Bash
# Ubuntu / Debian 安装 MySQL
sudo apt update
sudo apt install mysql-server -y

# 安装完成后查看版本
mysql --version

# 运行安全初始化脚本
sudo mysql_secure_installation

💡 提示：在 Ubuntu 20.04+ 中，MySQL 安装后默认使用 auth_socket 认证插件，root 用户无需密码即可通过 sudo mysql 登录。如果需要用密码登录，需要修改认证方式。

2.2 MySQL服务管理

安装完成后，需要掌握 MySQL 服务的启动、停止和重启操作：

Bash
# Linux 系统服务管理
sudo systemctl start mysql      # 启动 MySQL 服务
sudo systemctl stop mysql       # 停止 MySQL 服务
sudo systemctl restart mysql    # 重启 MySQL 服务
sudo systemctl status mysql     # 查看 MySQL 服务状态
sudo systemctl enable mysql     # 设置开机自启

# Windows 系统服务管理（以管理员身份运行 CMD）
net start mysql
net stop mysql

✅ 小技巧：如果 MySQL 服务无法启动，可以查看错误日志定位问题。Linux 下日志通常在 /var/log/mysql/error.log，Windows 下在 MySQL 安装目录的 data 文件夹中。

2.3 命令行客户端使用

MySQL 安装后会自带命令行客户端工具 mysql，它是学习 SQL 最直接的方式。

Bash
# 登录 MySQL（本地）
mysql -u root -p

# 登录 MySQL（远程）
mysql -h 192.168.1.100 -u root -p

# 登录后常用命令
SHOW DATABASES;              -- 查看所有数据库
USE school;                  -- 切换到 school 数据库
SHOW TABLES;                 -- 查看当前数据库的所有表
DESC student;                -- 查看表结构
EXIT;                        -- 退出

2.4 图形化管理工具

虽然命令行功能强大，但图形化工具能大幅提升开发效率。以下是常用的 MySQL 图形化管理工具：

工具名称	特点	适用平台	是否免费
Navicat	功能全面，界面友好，支持数据同步、备份	Windows / macOS / Linux	付费（有教育版）
DBeaver	开源免费，支持多种数据库	Windows / macOS / Linux	免费（社区版）
MySQL Workbench	MySQL 官方工具，支持建模和SQL开发	Windows / macOS / Linux	免费
DataGrip	JetBrains 出品，智能SQL补全	Windows / macOS / Linux	付费

💡 提示：推荐初学者使用 DBeaver Community Edition，它完全免费、开源，并且支持几乎所有主流数据库，非常适合学习和日常开发使用。

2.5 数据库与表的创建

下面通过一个完整的示例演示如何创建数据库和表：

SQL
-- 创建数据库（如果不存在则创建）
CREATE DATABASE IF NOT EXISTS school
    DEFAULT CHARACTER SET utf8mb4
    DEFAULT COLLATE utf8mb4_unicode_ci;

-- 使用该数据库
USE school;

-- 创建学生表
CREATE TABLE student (
    id          INT PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
    name        VARCHAR(50) NOT NULL COMMENT '姓名',
    gender      ENUM('男', '女') DEFAULT '男' COMMENT '性别',
    age         TINYINT UNSIGNED COMMENT '年龄',
    major       VARCHAR(100) COMMENT '专业',
    enroll_date DATE COMMENT '入学日期'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生信息表';

-- 创建课程表
CREATE TABLE course (
    course_id   INT PRIMARY KEY AUTO_INCREMENT COMMENT '课程编号',
    course_name VARCHAR(100) NOT NULL COMMENT '课程名称',
    credit      DECIMAL(2,1) COMMENT '学分',
    teacher     VARCHAR(50) COMMENT '授课教师'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课程信息表';

-- 创建成绩表（含外键）
CREATE TABLE score (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL COMMENT '学号',
    course_id  INT NOT NULL COMMENT '课程编号',
    score      DECIMAL(5,2) COMMENT '成绩',
    exam_date  DATE COMMENT '考试日期',
    FOREIGN KEY (student_id) REFERENCES student(id),
    FOREIGN KEY (course_id) REFERENCES course(course_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='成绩表';

-- 查看已创建的表
SHOW TABLES;

-- 查看表结构
DESC student;

✅ 小技巧：创建表时建议加上 COMMENT 注释，这样在 DBeaver 或 Navicat 中查看表结构时，注释会直接显示在字段说明中，方便团队协作和后期维护。

03

SQL数据操作语言

3.1 DDL - 数据定义语言

DDL（Data Definition Language）用于定义和修改数据库对象（数据库、表、索引等）的结构。核心语句包括 CREATE、ALTER 和 DROP。

SQL
-- 创建表
CREATE TABLE teacher (
    id       INT PRIMARY KEY AUTO_INCREMENT,
    name     VARCHAR(50) NOT NULL,
    title    VARCHAR(30) DEFAULT '讲师',
    salary   DECIMAL(10,2)
);

-- 修改表：添加新列
ALTER TABLE teacher ADD COLUMN phone VARCHAR(20) AFTER name;

-- 修改表：修改列的数据类型
ALTER TABLE teacher MODIFY COLUMN salary DECIMAL(12,2);

-- 修改表：修改列名
ALTER TABLE teacher CHANGE COLUMN title position VARCHAR(30);

-- 修改表：删除列
ALTER TABLE teacher DROP COLUMN phone;

-- 删除表
DROP TABLE IF EXISTS teacher;

💡 提示：使用 DROP TABLE 会永久删除表及其所有数据，操作不可逆！执行前请务必确认，生产环境中建议先备份再操作。

3.2 DML - 数据操纵语言

DML（Data Manipulation Language）用于对表中的数据进行增、删、改操作，核心语句包括 INSERT、UPDATE 和 DELETE。

SQL
-- 插入数据（指定列）
INSERT INTO student (name, gender, age, major, enroll_date)
VALUES ('张三', '男', 20, '计算机科学', '2024-09-01');

-- 插入数据（省略列名，需提供所有列的值）
INSERT INTO student VALUES (NULL, '李四', '女', 19, '软件工程', '2024-09-01');

-- 批量插入
INSERT INTO student (name, gender, age, major, enroll_date) VALUES
('王五', '男', 21, '数据科学', '2024-09-01'),
('赵六', '女', 20, '人工智能', '2024-09-01'),
('孙七', '男', 22, '计算机科学', '2024-09-01');

-- 更新数据
UPDATE student SET age = 21 WHERE name = '张三';

-- 删除数据
DELETE FROM student WHERE id = 5;

-- 清空整张表（效率更高，重置自增ID）
TRUNCATE TABLE student;

✅ 小技巧：DELETE 和 TRUNCATE 都能清空表数据，但 DELETE 是逐行删除并记录日志（可回滚），而 TRUNCATE 是直接删除整张表再重建（不可回滚，速度更快）。需要根据场景选择。

3.3 DQL - 数据查询语言

DQL（Data Query Language）用于从数据库中查询数据，最核心的语句是 SELECT。查询语句可以包含多种子句来筛选、排序和限制结果。

SQL
-- 基本查询
SELECT name, age, major FROM student;

-- 条件查询（WHERE）
SELECT * FROM student WHERE age > 20 AND major = '计算机科学';

-- 模糊查询（LIKE）
SELECT * FROM student WHERE name LIKE '张%';

-- 范围查询（BETWEEN / IN）
SELECT * FROM student WHERE age BETWEEN 19 AND 22;
SELECT * FROM student WHERE major IN ('计算机科学', '软件工程', '数据科学');

-- 排序（ORDER BY）
SELECT * FROM student ORDER BY age ASC, name DESC;

-- 分页查询（LIMIT）
SELECT * FROM student ORDER BY id LIMIT 5;           -- 前5条
SELECT * FROM student ORDER BY id LIMIT 5 OFFSET 5;  -- 第6-10条
SELECT * FROM student ORDER BY id LIMIT 10, 5;       -- 等价写法

💡 提示：LIKE 中 % 匹配任意多个字符，_ 匹配单个字符。如果需要匹配 % 或 _ 本身，使用 ESCAPE 关键字指定转义字符。

3.4 聚合函数

聚合函数对一组值进行计算，返回一个单一的值。MySQL 提供了五个常用的聚合函数：

函数	功能	说明
COUNT()	计数	COUNT(*) 统计所有行，COUNT(列名) 统计非 NULL 值
SUM()	求和	对数值列求和，忽略 NULL 值
AVG()	平均值	对数值列求平均，忽略 NULL 值
MAX()	最大值	返回列中的最大值
MIN()	最小值	返回列中的最小值

SQL
-- 查询学生总数
SELECT COUNT(*) AS total_students FROM student;

-- 查询平均年龄、最大年龄、最小年龄
SELECT
    AVG(age) AS avg_age,
    MAX(age) AS max_age,
    MIN(age) AS min_age
FROM student;

-- 查询各专业的学生人数
SELECT major, COUNT(*) AS student_count
FROM student
GROUP BY major;

-- 查询各专业的平均年龄（只显示人数大于1的专业）
SELECT major, COUNT(*) AS cnt, AVG(age) AS avg_age
FROM student
GROUP BY major
HAVING cnt > 1;

3.5 GROUP BY 与 HAVING

GROUP BY 用于将查询结果按指定列分组，通常与聚合函数配合使用。HAVING 用于对分组后的结果进行过滤，类似于 WHERE，但 HAVING 只能用于分组后的筛选。

SQL
-- 查询每门课程的选课人数和平均分
SELECT
    c.course_name,
    COUNT(*) AS student_count,
    ROUND(AVG(s.score), 2) AS avg_score
FROM score s
JOIN course c ON s.course_id = c.course_id
GROUP BY c.course_id, c.course_name;

-- 只显示平均分大于80的课程
SELECT
    c.course_name,
    COUNT(*) AS student_count,
    ROUND(AVG(s.score), 2) AS avg_score
FROM score s
JOIN course c ON s.course_id = c.course_id
GROUP BY c.course_id, c.course_name
HAVING avg_score > 80
ORDER BY avg_score DESC;

✅ 小技巧：WHERE 在分组前过滤行（不能使用聚合函数），HAVING 在分组后过滤组（可以使用聚合函数）。执行顺序为：WHERE -> GROUP BY -> HAVING。

04

SQL高级查询

4.1 多表连接查询

在实际应用中，数据通常分散在多张表中，需要通过连接（JOIN）查询将相关数据组合在一起。

连接类型	说明	特点
INNER JOIN	内连接	只返回两表中匹配的行
LEFT JOIN	左外连接	返回左表所有行，右表无匹配则填 NULL
RIGHT JOIN	右外连接	返回右表所有行，左表无匹配则填 NULL
CROSS JOIN	交叉连接	返回两表的笛卡尔积

SQL
-- 内连接：查询学生姓名和对应的专业
SELECT s.name, s.major, d.dept_name
FROM student s
INNER JOIN department d ON s.dept_id = d.dept_id;

-- 左连接：查询所有学生及其成绩（含未参加考试的学生）
SELECT s.name, c.course_name, s.score
FROM student s
LEFT JOIN score sc ON s.id = sc.student_id
LEFT JOIN course c ON sc.course_id = c.course_id;

-- 右连接：查询所有课程及选课学生
SELECT c.course_name, s.name
FROM student s
RIGHT JOIN score sc ON s.id = sc.student_id
RIGHT JOIN course c ON sc.course_id = c.course_id;

-- 三表连接：查询学生姓名、课程名称和成绩
SELECT s.name AS '姓名', c.course_name AS '课程', sc.score AS '成绩'
FROM student s
INNER JOIN score sc ON s.id = sc.student_id
INNER JOIN course c ON sc.course_id = c.course_id
ORDER BY sc.score DESC;

✅ 小技巧：使用表别名（如 student s）可以简化 SQL 语句，特别是在多表连接时。建议始终为每个列加上表别名前缀，避免歧义。

4.2 子查询

子查询（Subquery）是嵌套在其他查询中的 SELECT 语句，可以出现在 SELECT、FROM、WHERE 和 HAVING 子句中。

SQL
-- WHERE 子查询：查询成绩高于平均分的记录
SELECT s.name, c.course_name, sc.score
FROM student s
JOIN score sc ON s.id = sc.student_id
JOIN course c ON sc.course_id = c.course_id
WHERE sc.score > (SELECT AVG(score) FROM score);

-- IN 子查询：查询选修了"数据库原理"课程的学生
SELECT name FROM student
WHERE id IN (
    SELECT student_id FROM score
    WHERE course_id = (SELECT course_id FROM course WHERE course_name = '数据库原理')
);

-- EXISTS 子查询：查询有成绩记录的学生
SELECT name FROM student s
WHERE EXISTS (
    SELECT 1 FROM score sc WHERE sc.student_id = s.id
);

-- FROM 子查询（派生表）：查询每个专业的学生人数
SELECT major, cnt FROM (
    SELECT major, COUNT(*) AS cnt
    FROM student
    GROUP BY major
) AS major_stats
WHERE cnt > 1;

💡 提示：子查询虽然灵活，但在数据量大时性能可能较差。如果子查询可以用 JOIN 改写，通常 JOIN 的执行效率更高。

4.3 UNION 合并查询

UNION 用于将多个 SELECT 语句的结果合并为一个结果集。要求各 SELECT 语句的列数和数据类型一致。

SQL
-- UNION：合并两个查询结果（自动去重）
SELECT name, '学生' AS role FROM student
UNION
SELECT name, '教师' AS role FROM teacher;

-- UNION ALL：合并结果（不去重，性能更好）
SELECT name, '学生' AS role FROM student
UNION ALL
SELECT name, '教师' AS role FROM teacher;

-- 实际应用：查询成绩在90分以上或60分以下的记录
SELECT name, course_name, score, '优秀' AS level
FROM student s
JOIN score sc ON s.id = sc.student_id
JOIN course c ON sc.course_id = c.course_id
WHERE sc.score >= 90

UNION ALL

SELECT name, course_name, score, '不及格' AS level
FROM student s
JOIN score sc ON s.id = sc.student_id
JOIN course c ON sc.course_id = c.course_id
WHERE sc.score < 60;

✅ 小技巧：如果确定合并的结果不会有重复行，优先使用 UNION ALL，因为它不需要执行去重操作，查询速度更快。

4.4 视图（VIEW）

视图是基于 SQL 查询结果的虚拟表。视图本身不存储数据，每次查询视图时都会执行其定义的 SQL 语句。视图可以简化复杂查询、提高安全性（隐藏敏感列）。

SQL
-- 创建视图：学生成绩概览
CREATE VIEW v_student_score AS
SELECT
    s.id,
    s.name,
    s.major,
    c.course_name,
    sc.score,
    CASE
        WHEN sc.score >= 90 THEN '优秀'
        WHEN sc.score >= 80 THEN '良好'
        WHEN sc.score >= 70 THEN '中等'
        WHEN sc.score >= 60 THEN '及格'
        ELSE '不及格'
    END AS grade
FROM student s
JOIN score sc ON s.id = sc.student_id
JOIN course c ON sc.course_id = c.course_id;

-- 使用视图（像普通表一样查询）
SELECT * FROM v_student_score WHERE grade = '优秀';

-- 查看视图定义
SHOW CREATE VIEW v_student_score;

-- 修改视图
CREATE OR REPLACE VIEW v_student_score AS
SELECT s.name, c.course_name, sc.score
FROM student s
JOIN score sc ON s.id = sc.student_id
JOIN course c ON sc.course_id = c.course_id;

-- 删除视图
DROP VIEW IF EXISTS v_student_score;

💡 提示：视图虽然方便，但不宜过度使用。包含复杂计算和多表连接的视图可能严重影响查询性能。在性能敏感的场景下，建议将复杂视图改为物化视图或直接优化底层查询。

4.5 索引基础

索引（Index）是数据库中用于加速数据检索的数据结构，类似于书籍的目录。合理使用索引可以大幅提升查询性能，但过多的索引会降低写入速度并占用存储空间。

SQL
-- 创建普通索引
CREATE INDEX idx_student_name ON student(name);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_student_email ON student(email);

-- 创建复合索引
CREATE INDEX idx_score_composite ON score(student_id, course_id);

-- 查看表的索引
SHOW INDEX FROM student;

-- 使用 ALTER TABLE 添加索引
ALTER TABLE student ADD INDEX idx_major(major);
ALTER TABLE student ADD UNIQUE INDEX idx_name(name);

-- 删除索引
DROP INDEX idx_student_name ON student;
ALTER TABLE student DROP INDEX idx_major;

-- 使用 EXPLAIN 分析查询是否使用了索引
EXPLAIN SELECT * FROM student WHERE name = '张三';

索引类型	说明	适用场景
普通索引（INDEX）	最基本的索引，无约束	加速查询
唯一索引（UNIQUE）	索引列值必须唯一	保证数据唯一性
主键索引（PRIMARY KEY）	特殊的唯一索引，不允许 NULL	唯一标识记录
复合索引（COMPOSITE）	多个列组合的索引	多条件联合查询
全文索引（FULLTEXT）	用于全文搜索	文本内容检索

✅ 小技巧：使用 EXPLAIN 关键字可以查看 SQL 语句的执行计划，帮助判断索引是否被正确使用。关注 type 列（访问类型）和 key 列（使用的索引），理想情况是 type 为 ref 或 range，避免出现 ALL（全表扫描）。

🗄️ 数据库原理与应用