数据库实战

要素

数据：文本、数字、图像、音视频等任何形式的信息

表：数据以二维表的形式组织，每个表包含：

字段（Field）：列名，表示数据的属性（如“姓名”“年龄”）。
记录（Record）：每一行的具体数据（如“张三，25岁”）。

数据库管理系统（DBMS）：负责数据的存储、查询、更新和安全控制

数据操作（Data Manipulation）：

对数据进行增删改查（CRUD），DML（Data Manipulation Language）实现SELECT, INSERT, UPDATE, DELETE

数据定义（Data Definition）：定义数据的结构和约束，创建、修改、删除数据库对象（如表、视图、索引等）。

DDL（Data Definition Language）实现，如 CREATE TABLE, ALTER TABLE, DROP TABLE

数据存储与管理（Storage Management）：

管理数据的物理存储（如磁盘空间分配、文件组织）。

优化存储结构以提高读写效率（如数据压缩、分区存储）。

自动处理数据在内存与磁盘间的交换（缓存机制）。

B+树索引、哈希表、缓冲区管理

数据安全与权限控制（Security & Access Control）

- 用户认证：验证用户身份（如账号密码、多因素认证）。
- 权限管理：控制用户对数据的访问权限（如只读、读写、禁止删除）。
- 数据加密：保护敏感数据（如AES加密、SSL传输加密）。
工具：通过 DCL（Data Control Language）实现，如 GRANT, REVOKE。

1	GRANT SELECT, INSERT ON Users TO user_developer;

并发控制（Concurrency Control）:

功能

：管理多用户同时访问数据库时的冲突。
- 锁机制：行级锁、表级锁、共享锁（读锁）、排他锁（写锁）。
- 事务隔离级别：通过不同级别（如读未提交、可重复读、串行化）平衡一致性与性能。
目标：防止脏读、不可重复读、幻读等问题，确保数据一致性。

DDL

CREATE：创建数据库或表。

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT
);

ALTER：修改表结构（增删列、修改类型）

1	ALTER TABLE Students ADD Email VARCHAR(100);

DROP：删除数据库或表。

1	DROP TABLE Students;

TRUNCATE：清空表中所有数据（比 DELETE 更快，不可回滚）。

1	TRUNCATE TABLE Students;

数据操作语言（DML）

用于对表中数据进行增删改。

1 2	INSERT INTO Students (StudentID, Name, Age) VALUES (1, '张三', 20);

UPDATE：更新数据。

1	UPDATE Students SET Age = 21 WHERE StudentID = 1;

DELETE：删除数据

1	DELETE FROM Students WHERE StudentID = 1;

数据查询语言（DQL）

仅用于查询数据，不修改数据。

1	SELECT Name, Age FROM Students WHERE Age > 18;

SELECT 列1, 列2, ...
FROM 表1
EXCEPT
SELECT 列1, 列2, ...
FROM 表2;

JOIN：多表关联查询。

1
2
3

SELECT s.Name, d.DepartmentName 
FROM Students s
JOIN Departments d ON s.DepartmentID = d.DepartmentID;

聚合函数：SUM, AVG, COUNT, MAX, MIN。

1	SELECT AVG(Age) FROM Students;

分组与过滤

HAVING 是对 GROUP BY 分组后的结果进行条件过滤。
WHERE 在分组前过滤单行数据，HAVING 在分组后过滤聚合后的组数据。

HAVING 聚合条件

适用场景：筛选聚合值（如平均工资、总销售额等）满足条件的组。

子句	执行阶段	用途	是否支持聚合函数
`WHERE`	数据分组前	过滤单行数据	否
`HAVING`	数据分组后	过滤分组后的聚合结果	是

SELECT DepartmentID, AVG(Salary) 
FROM Employees 
GROUP BY DepartmentID 
HAVING AVG(Salary) > 5000;

多表连接

JOIN（内连接）

1
2
3

SELECT s.Name, d.DepartmentName 
FROM Students s 
JOIN Departments d ON s.DepartmentID = d.DepartmentID;

LEFT JOIN（左外连接）

1
2
3

SELECT s.Name, d.DepartmentName 
FROM Students s 
LEFT JOIN Departments d ON s.DepartmentID = d.DepartmentID;

子查询

1
2
3

SELECT Name 
FROM Students 
WHERE Age > (SELECT AVG(Age) FROM Students);

数据控制语言DCL

GRANT：授予权限

1	GRANT SELECT, INSERT ON Students TO User1;

REVOKE：撤销权限

1	REVOKE INSERT ON Students FROM User1;

事务控制

BEGIN TRANSACTION：开始事务。
COMMIT：提交事务（永久保存）。
ROLLBACK：回滚事务（撤销未提交的操作）

BEGIN TRANSACTION;
UPDATE Account SET Balance = Balance - 100 WHERE UserID = 1;
UPDATE Account SET Balance = Balance + 100 WHERE UserID = 2;
COMMIT; -- 或 ROLLBACK;

索引管理

1	CREATE INDEX idx_name ON Students(Name);

唯一索引（UNIQUE INDEX）

确保列值唯一，可为 NULL（但只能有一个 NUL）

删除索引：

1	DROP INDEX idx_name ON Students;

出错点

忽略事务导致数据不一致

1
2
3

-- 错误示例：未使用事务，若第二步失败，第一步无法回滚
UPDATE Account SET Balance = Balance - 100 WHERE UserID = 1;
UPDATE Account SET Balance = Balance + 100 WHERE UserID = 2;

**滥用 `SELECT *`**

1 2	-- 错误示例 SELECT * FROM Students;

解决：明确指定所需字段。

1	SELECT Name, Age FROM Students;

未使用索引导致慢查询

问题：频繁查询的字段未建索引，导致全表扫描。
解决：为高频查询字段添加索引。

1	CREATE INDEX idx_age ON Students(Age);

SQL 注入漏洞

问题：拼接字符串导致恶意代码执行。

1 2	-- 错误示例（Python） query = "SELECT * FROM Users WHERE Name = '" + user_input + "'";

解决：使用参数化查询（Prepared Statements）。

1	cursor.execute("SELECT * FROM Users WHERE Name = %s", (user_input,));

隐式类型转换

问题：字段类型与查询条件类型不匹配，导致索引失效。

1 2	-- 错误示例：Age 是 INT 类型，但用字符串比较 SELECT * FROM Students WHERE Age = '20';

解决：确保类型一致。

1	SELECT * FROM Students WHERE Age = 20;

过度使用子查询

问题：嵌套子查询导致性能低下。

1
2
3

-- 错误示例
SELECT Name FROM Students 
WHERE Age > (SELECT AVG(Age) FROM Students);

解决：改用 JOIN 或临时表优化。

1 2	WITH AvgAge AS (SELECT AVG(Age) AS avg_age FROM Students) SELECT Name FROM Students, AvgAge WHERE Age > avg_age;

要素

DDL