目录
一、数据库基础：为什么需要数据库？
1.1 数据存储的演进
-
简单存储：变量、列表、字典等仅适合小规模数据，缺乏持久化和高效查询。
-
文件存储：JSON/XML/YML文件解决了结构化存储，但面临读写效率低、并发控制难等问题。
-
数据库优势：
-
结构化存储（表、行、列）。
-
高效查询（SQL语言）。
-
事务支持（ACID特性）。
-
数据安全（权限控制、备份恢复）。
-
1.2 数据库分类
-
关系型数据库（RDBMS）：
-
核心模型：表格结构，支持SQL，强一致性（如MySQL、Oracle）。
-
适用场景：金融系统、ERP、CRM等需复杂事务和关联查询的场景。
-
-
非关系型数据库（NoSQL）：
-
核心模型：键值对（Redis）、文档（MongoDB）、图（Neo4j）。
-
适用场景：实时数据处理（IoT）、内容管理、社交网络关系分析。
-
二、MySQL深度解析：安装、配置与核心功能
2.1 安装与卸载
-
安装注意事项：
-
路径避免中文，设置UTF-8编码。
-
配置环境变量，确保命令行全局访问。
-
-
卸载彻底方案：
1. 控制面板卸载MySQL程序。
2. 删除C:\ProgramData\MySQL隐藏文件夹。
3. 清理注册表残留项。 2.2 MySQL vs Oracle：关键区别
| 特性 | MySQL | Oracle |
|---|---|---|
| 字符串类型 | VARCHAR | VARCHAR2 |
| 自增字段 | AUTO_INCREMENT | 序列（Sequence） |
| 检查约束 | 8.0.16+版本生效 | 全版本支持 |
2.3 基础SQL命令速查
-- 创建数据库
CREATE DATABASE shop;
-- 切换数据库
USE shop;
-- 创建用户表（含主键自增）
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE
);
-- 插入数据
INSERT INTO users (name, email) VALUES ('张三', 'zhangsan@example.com');
-- 查询数据
SELECT * FROM users WHERE name LIKE '张%'; 三、数据库设计核心：约束与关系
3.1 五大建表约束
-
主键约束（PK）：唯一且非空，加速查询。
适用场景：表的核心标识字段，如用户ID、订单号等。
通过 PRIMARY KEY 定义，是数据唯一性的核心保障，建议搭配自增属性使用。
优先使用自增整数（如 AUTO_INCREMENT），避免业务字段（如身份证号）作为主键。
-- 创建商品表，主键自增（AUTO_INCREMENT）
CREATE TABLE products (
product_id INT PRIMARY KEY AUTO_INCREMENT, -- 主键约束
name VARCHAR(100) NOT NULL
);
-- 插入数据（主键自动生成）
INSERT INTO products (name) VALUES ('手机');
INSERT INTO products (name) VALUES ('电脑');
-- 查询结果：product_id 依次为 1, 2
-- 测试插入重复主键
INSERT INTO products (product_id, name) VALUES (1, '平板');
-- 报错：Duplicate entry '1' for key 'PRIMARY'
-- 测试插入空主键
INSERT INTO products (product_id, name) VALUES (NULL, '耳机');
-- 成功（AUTO_INCREMENT 自动填充） 2.外键约束（FK）：实现表间关联，支持级联操作。
作用：建立表间关联关系，支持级联操作（如删除或更新时自动处理关联数据）。
适用场景：一对多关系（如订单与用户）、多对多中间表。
通过 FOREIGN KEY 实现表间关联，需谨慎选择级联策略，避免数据意外丢失。
-- 创建用户表（主表）
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL
);
-- 创建订单表（从表），外键关联用户表
CREATE TABLE orders (
order_id INT PRIMARY KEY,
user_id INT,
amount DECIMAL(10,2),
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE -- 外键约束（级联删除）
);
-- 插入主表数据
INSERT INTO users (name) VALUES ('张三'), ('李四');
-- 插入从表数据（有效外键）
INSERT INTO orders (order_id, user_id, amount) VALUES (1001, 1, 500.00);
-- 成功
-- 测试插入无效外键
INSERT INTO orders (order_id, user_id, amount) VALUES (1002, 99, 300.00);
-- 报错：Cannot add or update a child row: a foreign key constraint fails
-- 测试级联删除
DELETE FROM users WHERE id = 1;
-- 主表用户ID=1被删除，从表关联订单自动删除 级联操作类型：
-
ON DELETE CASCADE：主表记录删除时，从表关联记录自动删除。 -
ON DELETE SET NULL：主表记录删除时，从表外键字段设为NULL（需字段允许空值）。
3.唯一约束（UK）：字段值唯一，允许空值。
通过 UNIQUE 实现，允许空值但禁止重复值。
适用场景：用户注册邮箱、手机号等需唯一但允许未填写的字段。
-- 创建用户表，邮箱字段添加唯一约束
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE -- 唯一约束
);
-- 测试插入重复邮箱
INSERT INTO users (username, email) VALUES ('张三', 'zhangsan@example.com');
-- 成功
INSERT INTO users (username, email) VALUES ('李四', 'zhangsan@example.com');
-- 报错：Duplicate entry 'zhangsan@example.com' for key 'email'
-- 允许插入空值
INSERT INTO users (username, email) VALUES ('王五', NULL);
-- 成功
INSERT INTO users (username, email) VALUES ('赵六', NULL);
-- 成功（NULL不触发唯一约束） 4.非空约束（NK）：字段禁止为空。
通过 NOT NULL 强制字段必须赋值。
适用场景：必填字段，如用户姓名、订单ID等。
-- 创建订单表，订单号字段禁止为空
CREATE TABLE orders (
order_id INT PRIMARY KEY,
user_id INT NOT NULL, -- 非空约束
total_amount DECIMAL(10,2) NOT NULL
);
-- 测试插入空值
INSERT INTO orders (order_id, user_id, total_amount) VALUES (1, NULL, 100.00);
-- 报错：Column 'user_id' cannot be null
-- 正确插入
INSERT INTO orders (order_id, user_id, total_amount) VALUES (1, 101, 100.00);
-- 成功 5.检查约束（CK）：MySQL 8.0.16+生效，如限制年龄范围。
通过 CHECK 定义业务规则（MySQL 8.0.16+支持），确保数据逻辑正确性。
适用场景：年龄限制、价格范围、状态枚举等业务规则验证。
-- 创建员工表，限制年龄在18~65岁之间
CREATE TABLE employees (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
age INT,
salary DECIMAL(10,2),
CONSTRAINT age_check CHECK (age >= 18 AND age <= 65) -- 检查约束
);
-- 测试插入非法年龄
INSERT INTO employees (name, age, salary) VALUES ('张三', 17, 5000.00);
-- 报错：Check constraint 'age_check' is violated
-- 正确插入
INSERT INTO employees (name, age, salary) VALUES ('李四', 25, 8000.00);
-- 成功
-- 支持复杂规则（如薪资必须为正数）
ALTER TABLE employees
ADD CONSTRAINT salary_check CHECK (salary > 0);
-- 测试插入负数薪资
INSERT INTO employees (name, age, salary) VALUES ('王五', 30, -1000.00);
-- 报错：Check constraint 'salary_check' is violated 3.2 联合主键与实战应用
-
场景：多对多关系中间表（如学生选课）。
CREATE TABLE student_course (
student_id INT,
course_id INT,
PRIMARY KEY (student_id, course_id)
); -
优势：确保组合唯一性，避免冗余。
3.3 级联操作：数据关联的生命周期
-
级联删除：删除主表记录时，自动删除从表关联数据（慎用！）。
-
级联置空：主表记录删除后，从表外键字段设为NULL。
四、SQL高级查询：从简单到复杂
4.1 限定查询与排序
-- 查询薪资>3000且入职在1981年的员工
SELECT * FROM emp
WHERE sal > 3000 AND hiredate BETWEEN '1981-01-01' AND '1981-12-31'
ORDER BY hiredate DESC; 4.2 分组与聚合
-- 统计各部门平均薪资（仅显示>5000的部门）
SELECT deptno, AVG(sal) AS avg_salary
FROM emp
GROUP BY deptno
HAVING avg_salary > 5000; 4.3 连接查询：多表关联
-
内连接：获取用户订单详情。
SELECT u.name, o.order_date
FROM users u
INNER JOIN orders o ON u.id = o.user_id; -
左连接：显示所有用户，包括无订单的用户。
4.4 子查询与分页优化
-- 查询薪资高于平均值的员工（子查询）
SELECT name, sal
FROM emp
WHERE sal > (SELECT AVG(sal) FROM emp);
-- 分页查询（LIMIT + OFFSET）
SELECT * FROM products
LIMIT 10 OFFSET 20; -- 第3页，每页10条
-- 性能优化：避免大偏移量，改用游标分页（基于ID）
SELECT * FROM products
WHERE id > 100
ORDER BY id
LIMIT 10; 五、实战项目：电商系统数据库设计
5.1 表结构设计
用户表（users）：
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) UNIQUE,
password VARCHAR(100)
); 商品表（products）：
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(200),
price DECIMAL(10,2)
); 订单表（orders）：联合主键 + 外键
CREATE TABLE orders (
order_id INT,
user_id INT,
product_id INT,
quantity INT,
PRIMARY KEY (order_id, product_id),
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (product_id) REFERENCES products(id)
); 5.2 高频查询示例
统计热销商品TOP5：
SELECT p.name, COUNT(*) AS sales
FROM orders o
JOIN products p ON o.product_id = p.id
GROUP BY p.id
ORDER BY sales DESC
LIMIT 5; 六、进阶技巧：索引与性能调优
6.1 索引设计原则
-
高频查询字段：如
users.email、orders.user_id。 -
前缀索引：对长文本字段（如
VARCHAR(255)）取前N字符。
CREATE INDEX idx_name_prefix ON products(name(20)); 6.2 避免全表扫描
-
使用EXPLAIN分析查询：
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com'; -
优化WHERE条件：避免对索引字段使用函数或计算。
七、自我总结与学习资源推荐
-
核心要点回顾：
-
关系型数据库适合强事务场景，NoSQL适合高扩展需求。
-
约束与关系设计是数据库健壮性的基石。
-
复杂查询需结合索引优化提升性能。
-
-
推荐学习：
-
书籍：《高性能MySQL》《SQL必知必会》。
-
在线课程：Coursera“Database Systems”专项课。
-
扫一扫
2323
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
