数据库实战

要素

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

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

• 字段（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。

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：修改表结构（增删列、修改类型）

ALTER TABLE Students ADD Email VARCHAR(100);

DROP：删除数据库或表。

DROP TABLE Students;

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

TRUNCATE TABLE Students;

数据操作语言（DML）

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

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

UPDATE：更新数据。

UPDATE Students SET Age = 21 WHERE StudentID = 1;

DELETE：删除数据

DELETE FROM Students WHERE StudentID = 1;

数据查询语言（DQL）

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

SELECT Name, Age FROM Students WHERE Age > 18;

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

JOIN：多表关联查询。

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

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

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（内连接）

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

LEFT JOIN（左外连接）

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

子查询

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

数据控制语言DCL

GRANT：授予权限

GRANT SELECT, INSERT ON Students TO User1;

REVOKE：撤销权限

REVOKE INSERT ON Students FROM User1;

事务控制

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

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

索引管理

CREATE INDEX idx_name ON Students(Name);  

唯一索引（UNIQUE INDEX）

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

删除索引：

DROP INDEX idx_name ON Students;  

出错点

忽略事务导致数据不一致

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

滥用 SELECT *

-- 错误示例
SELECT * FROM Students;

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

SELECT Name, Age FROM Students;

未使用索引导致慢查询

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

CREATE INDEX idx_age ON Students(Age);

SQL 注入漏洞

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

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

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

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

隐式类型转换

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

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

解决：确保类型一致。

SELECT * FROM Students WHERE Age = 20;

过度使用子查询

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

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

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

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