zd1269878-Blog

第一章 绪论
1.1 数据库系统概述
四个基本概念
数据（Data）
定义：描述事物的符号记录，与其语义不可分割（如学生成绩、体重等）。
数据库（DB）
定义：长期存储、有组织、可共享的数据集合。
特征：结构化、共享性高、冗余度低、独立性高、易扩展。
数据库管理系统（DBMS）
功能：数据定义、组织存储、操纵、事务管理、维护等。
位于操作系统与应用系统之间，是核心基础软件。
数据库系统（DBS）
构成：数据库 + DBMS + 应用系统 + 数据库管理员（DBA）。
数据管理技术的发展
人工管理阶段（20世纪40-50年代）：数据不保存、不共享。
文件系统阶段（50-60年代）：数据长期保存，但冗余度高、独立性差。
数据库系统阶段（60年代后）：结构化、共享性高、独立性强。
数据库系统的特点
数据结构化（面向全组织，整体结构化）。
数据共享性高，冗余度低，易扩充。
数据独立性高（物理独立性、逻辑独立性）。
数据由DBMS统一管理（安全性、完整性、并发控制、恢复）。

1.2 数据模型
数据模型分类
概念模型（信息模型）：按用户视角建模，如E-R模型。
逻辑模型（机器视角）：层次模型、网状模型、关系模型等。
物理模型：数据在存储介质上的组织方式。
数据模型的组成要素
数据结构：数据的类型、关系（如关系模型中的表结构）。
数据操作：查询、插入、删除、修改。
完整性约束：实体完整性、参照完整性、用户定义完整性。
概念模型（E-R模型）
实体与联系
实体（Entity）：客观存在的事物（如学生、课程）。
属性（Attribute）：实体的特征（如学号、姓名）。
码（Key）：唯一标识实体的属性集。
联系（Relationship）：实体间的关联（1:1、1:N、M:N）。
E-R图表示法
实体：矩形框；属性：椭圆；联系：菱形（标注联系类型）。
常用数据模型
层次模型
结构：树形结构（根节点唯一，子节点有唯一父节点）。
优点：查询效率高；缺点：多对多联系表示复杂。
网状模型
结构：允许节点有多个父节点，支持多对多联系。
优点：直接表示复杂关系；缺点：结构复杂，用户难掌握。
关系模型
结构：二维表（行：元组；列：属性）。
特点：规范化（不可再分的数据项）、操作基于集合。
完整性约束：实体完整性、参照完整性、用户定义完整性。

1.3 数据库系统结构
三级模式结构
外模式（子模式）：用户视角的数据视图（如应用程序接口）。
模式（逻辑模式）：全局数据逻辑结构（如表结构定义）。
内模式（存储模式）：数据的物理存储方式（如索引、存储路径）。
二级映像与数据独立性
外模式/模式映像：保证逻辑独立性（模式变化时外模式可保持不变）。
模式/内模式映像：保证物理独立性（存储结构变化时模式可保持不变）。

1.4 数据库系统的组成
硬件：存储设备（磁盘、磁带）、高速通道。
软件：DBMS、操作系统、开发工具、应用系统。
人员
DBA：设计数据库结构、维护、安全管理。
系统分析员/设计人员：需求分析与数据库设计。
程序员：开发数据库应用。
用户：通过接口访问数据库（偶然用户、简单用户、复杂用户）。

1.5 小结
数据库系统的核心是数据模型与三级模式结构。
关系模型是主流模型，支持高数据独立性与规范化设计。
数据库系统的高效管理依赖DBMS与合理的体系结构。
以下是基于文件内容的第二章 关系数据库知识点归纳整理：
第二章 关系数据库
2.1 关系数据结构及形式化定义
关系模型基础
关系（Relation）：二维表结构，由行（元组）和列（属性）组成，基于集合代数理论构建。
域（Domain）：属性的取值范围（如整数、字符串、性别集合{男, 女}等）。
笛卡尔积：域的集合的笛卡尔积是所有域值组合的集合，每个元素称为一个元组。
关系定义：笛卡尔积的子集，具有实际意义的元组集合。
关系的关键概念
属性（Attribute）：表的列，描述实体的特征。
码（Key）
候选码：能唯一标识元组的最小属性组。
主码：选定的候选码，用于唯一标识元组。
外码（Foreign Key）：引用其他关系主码的属性（组）。
全码（All-Key）：所有属性共同组成候选码的极端情况。
关系的性质
列同质性（每列数据类型相同）。
元组唯一性（无重复行）。
分量原子性（属性值不可再分）。
行列顺序无关性（顺序不影响逻辑结构）。

2.2 关系操作
基本操作
查询操作：选择（σ）、投影（π）、连接（⋈）、除（÷）、并（∪）、差（−）、笛卡尔积（×）。
更新操作：插入、删除、修改。
集合操作：操作对象和结果均为集合（如并、交、差）。
关系语言分类
关系代数语言：基于代数运算（如ISBL）。
关系演算语言：基于谓词逻辑（如元组演算语言ALPHA、域演算语言QBE）。
SQL：结合关系代数与演算的结构化查询语言。

2.3 关系的完整性
实体完整性
规则：主码属性不能为空值（NULL）。
意义：确保每个元组可唯一标识。
参照完整性
规则：外码值必须为空值，或等于被参照关系的某个主码值。
示例：学生表的“专业号”必须引用专业表中存在的专业号。
用户定义的完整性
定义：针对具体应用的约束（如课程表的“学分”只能取1-4的整数）。
实现：通过DBMS提供的约束机制（如非空、唯一、检查约束）。

2.4 关系代数
基本运算
选择（σ）：按条件筛选元组（如σ_年龄>20(学生)）。
投影（π）：选择特定列（如π_姓名,年龄(学生)）。
笛卡尔积（×）：组合两个关系的元组。
并（∪）、差（−）：集合操作（要求属性相同）。
扩展运算
连接（⋈）：基于条件合并元组（如等值连接、自然连接）。
除（÷）：查询满足所有条件的元组（如查询选修所有课程的学生）。

2.5 关系演算
元组关系演算
语法：{ t | P(t) }，其中P为谓词（如t[学号] = ‘001’）。
示例：查询年龄大于20的学生：{ t | 学生(t) ∧ t.年龄 > 20 }。
域关系演算
语法：{ <x1, x2, …> | P(x1, x2, …) }（如QBE语言）。
示例：查询年龄为18的学生姓名：{  | ∃a (学生(学号, n, a) ∧ a = 18) }。

2.6 小结
核心内容：关系模型通过二维表结构统一表示实体与联系，依赖关系代数和完整性约束实现高效数据管理。
关键规则：实体完整性、参照完整性是关系数据库的基石，用户定义完整性增强语义约束。
实践意义：关系操作（如SQL）是数据库应用开发的基础，需熟练掌握查询与更新操作。
以下是基于文件内容的第二章 关系数据库（续）知识点归纳整理：
第二章 关系数据库（续）
2.4 关系代数（续）
2.4.1 传统的集合运算
并（Union）
定义：关系 ( R ) 和 ( S ) 具有相同目数（属性），且属性域相同，结果为属于 ( R ) 或 ( S ) 的元组集合。
符号：( R \cup S )。
差（Difference）
定义：结果为属于 ( R ) 但不属于 ( S ) 的元组集合。
符号：( R – S )。
交（Intersection）
定义：结果为同时属于 ( R ) 和 ( S ) 的元组集合。
符号：( R \cap S )。
笛卡尔积（Cartesian Product）
定义：关系 ( R )（( n ) 目）与 ( S )（( m ) 目）的笛卡尔积为 ( n + m ) 目关系，每个元组由 ( R ) 和 ( S ) 的元组组合而成。
符号：( R \times S )。

2.4.2 专门的关系运算
选择（Selection）
定义：从关系中选取满足条件的元组。
符号：( \sigma{F}(R) )，如 ( \sigma{\text{年龄}<20}(\text{学生}) )。
投影（Projection）
定义：从关系中选取指定属性列，消除重复元组。
符号：( \pi{A}(R) )，如 ( \pi{\text{姓名, 系}}(\text{学生}) )。
连接（Join）
等值连接：从笛卡尔积中选取属性值相等的元组。
符号：( R \bowtie_{A=B} S )。
自然连接：等值连接后去除重复属性列。
符号：( R \bowtie S )。
外连接：保留未匹配的元组，填充空值。
左外连接：保留左表所有元组。
右外连接：保留右表所有元组。
除（Division）
定义：给定关系 ( R(X, Y) ) 和 ( S(Y) )，结果为 ( R ) 中满足 ( Y ) 值包含 ( S ) 的 ( X ) 值。
符号：( R \div S )。
示例：查询选修所有课程的学生，即 ( \pi{\text{学号, 课号}}(\text{选修}) \div \pi{\text{课号}}(\text{课程}) )。

2.4.3 综合示例
查询选修1号和3号课程的学生：
构建临时关系 ( K = {1, 3} )。
计算 ( \pi_{\text{学号, 课号}}(\text{选修}) \div K )。
查询选修全部课程的学生：
( \pi{\text{学号, 课号}}(\text{选修}) \div \pi{\text{课号}}(\text{课程}) )。

2.5 关系演算
元组关系演算
语法：( { t | P(t) } )，其中 ( P ) 为谓词。
示例：查询年龄大于20的学生：
( { t | \text{学生}(t) \land t.\text{年龄} > 20 } )。
域关系演算
语法：( { <x_1, x_2, …> | P(x_1, x_2, …) } )。
示例：查询年龄为18的学生姓名：
( {  | \exists a (\text{学生}(\text{学号}, n, a) \land a = 18) } )。

2.6 小结
核心内容：
关系代数是关系数据库的理论基础，包括集合运算和专门运算。
除运算是复杂查询（如“全部”条件）的关键工具。
自然连接与外连接处理多表关联，需注意属性匹配和空值处理。
实践意义：
关系代数是SQL语言的理论基础，掌握其运算逻辑有助于优化查询语句。
综合示例展示了如何将实际问题转化为关系代数表达式。
以下是基于文件内容的第三章 SQL语言知识点归纳整理：
第三章 SQL语言
3.1 SQL概述
SQL的定义
Structured Query Language：结构化查询语言，关系数据库的标准语言。
特点：
一体化（集数据定义、操纵、控制于一体）。
高度非过程化（只需说明“做什么”，无需“怎么做”）。
面向集合操作（一次处理多行数据）。
SQL的组成
数据定义语言（DDL）：创建、修改、删除数据库对象（如表、索引）。
数据操纵语言（DML）：查询与更新数据（SELECT、INSERT、UPDATE、DELETE）。
数据控制语言（DCL）：权限管理（GRANT、REVOKE）。
嵌入式SQL：与宿主语言（如C、Java）结合使用。

3.2 数据定义（DDL）
创建表（CREATE TABLE）
CREATE TABLE Student (
  Sno CHAR(9) PRIMARY KEY,
  Sname VARCHAR(20) UNIQUE,
  Sage INT CHECK (Sage >= 18),
  Sdept VARCHAR(20) DEFAULT ‘计算机系’
);
修改表（ALTER TABLE）
添加列：ALTER TABLE Student ADD COLUMN Saddress VARCHAR(50);
删除列：ALTER TABLE Student DROP COLUMN Saddress;
修改约束：ALTER TABLE Student ALTER COLUMN Sage SET DEFAULT 18;
删除表（DROP TABLE）
DROP TABLE Student CASCADE;（级联删除依赖对象）。

3.3 数据查询（SELECT）
基本查询
投影：SELECT Sname, Sage FROM Student;
选择：SELECT * FROM Student WHERE Sage > 20;
排序：SELECT * FROM Student ORDER BY Sage DESC;
聚合函数
COUNT(), SUM(), AVG(), MAX(), MIN()
示例：SELECT AVG(Sage) FROM Student;
分组与过滤（GROUP BY + HAVING）
SELECT Sdept, AVG(Sage)
FROM Student
GROUP BY Sdept
HAVING AVG(Sage) > 20;
连接查询
内连接：SELECT * FROM Student JOIN Course ON Student.Cno = Course.Cno;
外连接：
左外连接：LEFT JOIN（保留左表所有行）。
右外连接：RIGHT JOIN（保留右表所有行）。
自然连接：NATURAL JOIN（自动匹配同名属性）。
子查询
标量子查询：返回单值（如WHERE Sage > (SELECT AVG(Sage) FROM Student)）。
行子查询：返回多列（如WHERE (Sno, Cno) IN (SELECT …)）。
表子查询：返回多行多列（如EXISTS子查询）。
集合查询
UNION（并集）、INTERSECT（交集）、EXCEPT（差集）。

3.4 数据更新（DML）
插入数据（INSERT）
单行插入：INSERT INTO Student VALUES (‘001’, ‘张三’, 20, ‘计算机系’);
多行插入：INSERT INTO Student SELECT …（从其他表导入）。
修改数据（UPDATE）
UPDATE Student
SET Sage = Sage + 1
WHERE Sdept = ‘计算机系’;
删除数据（DELETE）
删除特定记录：DELETE FROM Student WHERE Sno = ‘001’;
删除全部记录：DELETE FROM Student;（慎用！）

3.5 视图（View）
定义视图
CREATE VIEW CS_Student AS
SELECT * FROM Student
WHERE Sdept = ‘计算机系’;
视图的作用
简化复杂查询。
提供逻辑独立性（基表结构变化时视图可保持稳定）。
实现安全性（限制用户访问敏感数据）。
更新限制
可更新视图：不含聚合、分组、DISTINCT的单表视图。
不可更新视图：涉及多表连接、子查询的视图。

3.6 索引（Index）
创建索引
CREATE [UNIQUE] INDEX Stu_Index
ON Student(Sname DESC, Sage ASC);
索引的优缺点
优点：加速查询（尤其WHERE、JOIN、ORDER BY操作）。
缺点：占用存储空间，降低更新速度（需维护索引）。

3.7 数据控制（DCL）
权限授予（GRANT）
GRANT SELECT, INSERT ON Student
TO User1 WITH GRANT OPTION;
权限回收（REVOKE）
REVOKE UPDATE ON Student
FROM User1 CASCADE;

3.8 事务处理
事务特性（ACID）
原子性（Atomicity）：事务全做或全不做。
一致性（Consistency）：事务保持数据完整性。
隔离性（Isolation）：并发事务互不干扰。
持久性（Durability）：提交后修改永久保存。
事务控制语句
COMMIT：提交事务。
ROLLBACK：回滚事务。
SAVEPOINT：设置保存点（部分回滚）。

3.9 小结
核心内容：
SQL是关系数据库的核心语言，涵盖数据定义、查询、更新与控制。
复杂查询（连接、子查询、集合操作）是SQL的重点与难点。
事务与索引是数据库性能与安全的关键技术。
实践意义：
熟练编写高效SQL语句是数据库应用开发的基础。
合理使用视图、索引与事务可优化系统性能与安全性。
以下是基于文件内容的第三章 SQL语言（续）知识点归纳整理：
第三章 SQL语言（续）
3.10 嵌入式SQL
基本概念
宿主语言：如C、Java等，与SQL结合使用。
预编译：将嵌入式SQL转换为宿主语言函数调用。
关键语法
声明部分：EXEC SQL BEGIN DECLARE SECTION; 和 EXEC SQL END DECLARE SECTION;
游标（Cursor）：处理多行查询结果。
声明游标：EXEC SQL DECLARE cur_student CURSOR FOR SELECT * FROM Student;
打开游标：EXEC SQL OPEN cur_student;
提取数据：EXEC SQL FETCH cur_student INTO :sno, :sname;
关闭游标：EXEC SQL CLOSE cur_student;
动态SQL
运行时构建SQL语句（如EXEC SQL EXECUTE IMMEDIATE ‘DELETE FROM Student WHERE Sage > 20’;）。

3.11 存储过程与函数
存储过程（Stored Procedure）
创建：
CREATE PROCEDURE UpdateScore(
  IN stu_no CHAR(9),
  IN new_score INT
)
BEGIN
  UPDATE SC SET Score = new_score WHERE Sno = stu_no;
END;
调用：CALL UpdateScore(‘001’, 90);
函数（Function）
创建：
CREATE FUNCTION GetAvgScore(sdept VARCHAR(20))
RETURNS FLOAT
BEGIN
  DECLARE avg_score FLOAT;
  SELECT AVG(Score) INTO avg_score FROM Student WHERE Sdept = sdept;
  RETURN avg_score;
END;
调用：SELECT GetAvgScore(‘计算机系’);

3.12 触发器（Trigger）
定义与作用
触发器：当特定事件（INSERT/UPDATE/DELETE）发生时自动执行的存储过程。
用途：实现复杂业务规则、数据审计、完整性约束。
语法示例
CREATE TRIGGER before_insert_student
BEFORE INSERT ON Student
FOR EACH ROW
BEGIN
  IF NEW.Sage < 18 THEN
    SIGNAL SQLSTATE ‘45000’ SET MESSAGE_TEXT = ‘年龄不能小于18岁’;
  END IF;
END;

3.13 高级查询优化
窗口函数（Window Functions）
ROW_NUMBER()：为结果集分区内的行分配唯一序号。
RANK() / DENSE_RANK()：基于排序的排名。
示例：
SELECT Sname, Sage,
       RANK() OVER (ORDER BY Sage DESC) AS age_rank
FROM Student;
递归查询（WITH RECURSIVE）
场景：查询树形结构（如组织层级、物料清单）。
示例：
WITH RECURSIVE org_tree AS (
  SELECT * FROM Employee WHERE ManagerID IS NULL
  UNION ALL
  SELECT e.* FROM Employee e INNER JOIN org_tree o ON e.ManagerID = o.EmployeeID
)
SELECT * FROM org_tree;

3.14 事务隔离级别
隔离级别类型
READ UNCOMMITTED：最低级别，允许脏读。
READ COMMITTED：仅读取已提交数据（避免脏读）。
REPEATABLE READ：保证同一事务中多次读取结果一致（避免不可重复读）。
SERIALIZABLE：最高级别，完全隔离（避免幻读）。
设置隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

3.15 数据库安全性
用户认证与授权
创建用户：CREATE USER ‘user1’@’localhost’ IDENTIFIED BY ‘password’;
角色管理：
CREATE ROLE ‘developer’;
GRANT SELECT, INSERT ON db.* TO ‘developer’;
GRANT ‘developer’ TO ‘user1’;
行级安全（RLS）
策略定义：限制用户只能访问特定行。
示例：
CREATE POLICY student_policy ON Student
FOR SELECT
USING (Sdept = CURRENT_USER);

3.16 小结
核心内容：
嵌入式SQL与存储过程增强数据库编程能力。
触发器实现自动化业务逻辑，窗口函数解决复杂分析需求。
事务隔离级别与安全性机制保障数据一致性与访问控制。
实践意义：
掌握高级SQL特性可提升复杂业务场景的开发效率。
合理使用触发器与隔离级别可避免数据异常与并发问题。
以下是基于文件内容的第三章 SQL语言（续）知识点归纳整理：
第三章 SQL语言（续）
3.17 数据库完整性约束
实体完整性
主键约束：
ALTER TABLE Student ADD CONSTRAINT PK_Sno PRIMARY KEY (Sno);
唯一约束：
ALTER TABLE Student ADD CONSTRAINT UQ_Sname UNIQUE (Sname);
参照完整性
外键约束：
ALTER TABLE SC
ADD CONSTRAINT FK_Sno FOREIGN KEY (Sno) REFERENCES Student(Sno)
ON DELETE CASCADE ON UPDATE CASCADE;
用户定义的完整性
检查约束：
ALTER TABLE Student ADD CONSTRAINT CK_Sage CHECK (Sage >= 18);
默认值约束：
ALTER TABLE Student ALTER COLUMN Sdept SET DEFAULT ‘计算机系’;

3.18 规范化理论
范式（Normal Forms）
第一范式（1NF）：属性不可再分（如地址字段需拆分为省、市、街道）。
第二范式（2NF）：消除非主属性对候选码的部分依赖。
第三范式（3NF）：消除非主属性对候选码的传递依赖。
BC范式（BCNF）：消除主属性对候选码的部分和传递依赖。
反规范化
适用场景：以牺牲部分范式为代价提升查询性能（如冗余字段、预计算字段）。
示例：在订单表中冗余存储商品价格以避免频繁JOIN。

3.19 查询优化
优化策略
选择运算优化：优先执行选择运算以减少中间结果集。
连接顺序优化：小表驱动大表（如Student JOIN SC而非SC JOIN Student）。
索引优化：为WHERE、JOIN、ORDER BY字段创建索引。
执行计划分析
EXPLAIN命令：查看SQL执行计划（如MySQL的EXPLAIN SELECT …）。
关键指标：扫描行数、索引使用情况、临时表创建。

3.20 数据库设计案例
需求分析
确定实体（学生、课程、教师）、属性（学号、课程名、职称）及联系（选课、授课）。
E-R图设计
实体：学生（Sno, Sname, Sage）、课程（Cno, Cname）。
联系：选课（SC: Sno, Cno, Score），授课（Teaching: Tno, Cno）。
逻辑模型转换
实体表：Student, Course, Teacher。
联系表：SC, Teaching，外键约束确保参照完整性。

3.21 分布式数据库基础
分布式数据库特点
数据分片：水平分片（按行）、垂直分片（按列）。
复制与同步：主从复制、多主复制。
事务管理：两阶段提交（2PC）保证分布式事务一致性。
SQL分布式查询
数据定位：SELECT * FROM Student WHERE Sdept = ‘计算机系’ AT SITE1;
跨节点JOIN：通过全局名称或链接服务器实现。

3.22 小结
核心内容：
完整性约束是数据库可靠性的基石，需结合业务需求设计。
规范化理论指导数据库设计，避免冗余与更新异常。
查询优化与分布式技术是应对大数据与高并发的关键。
实践意义：
合理设计数据库结构可显著提升系统性能与可维护性。
掌握分布式SQL技术是应对现代分布式系统的基础能力。
以下是基于文件内容的第四章 数据库安全性知识点归纳整理：
第四章 数据库安全性
4.1 数据库安全性概述
安全性定义
核心目标：防止未授权访问、篡改或破坏数据，确保数据的机密性、完整性和可用性。
应用场景：军事机密、金融数据、医疗档案等敏感信息保护。
安全标准
TCSEC/TDI标准（橘皮书）：
安全级别划分（从低到高）：D（最低）、C1/C2、B1/B2/B3、A1（最高）。
关键指标：安全策略、责任、保证、文档。
CC标准（Common Criteria）：国际通用的安全评估标准。

4.2 数据库安全性控制
用户标识与鉴别
用户标识：通过用户名、用户ID等唯一标识用户。
鉴别方法：口令验证、生物特征识别、动态令牌等。
存取控制
自主存取控制（DAC）
授权与回收：
GRANT SELECT, INSERT ON Student TO User1;  — 授权
REVOKE UPDATE ON Student FROM User1;       — 回收权限
缺点：可能因权限传递导致数据泄露。
强制存取控制（MAC）
敏感度标记：
许可证级别（主体，如用户）：Top Secret > Secret > Confidential > Public。
密级（客体，如数据）：同上。
规则：
读操作：主体许可证级别 ≥ 客体密级。
写操作：主体许可证级别 = 客体密级。
数据库角色
角色定义：将权限绑定到角色，简化授权管理。
示例：
CREATE ROLE Manager;                — 创建角色
GRANT SELECT ON SC TO Manager;      — 角色授权
GRANT Manager TO User1, User2;      — 分配角色

4.3 视图机制
安全作用：通过视图限制用户访问特定数据。
示例：
CREATE VIEW CS_Student AS
SELECT * FROM Student WHERE Sdept = ‘计算机系’;  — 创建视图
GRANT SELECT ON CS_Student TO WangPing;          — 授权视图

4.4 审计（Audit）
功能：记录用户操作日志，追踪非法行为。
分类：
用户级审计：针对特定表或视图的操作记录。
系统级审计：监控登录、权限变更等全局事件。
SQL语法：
AUDIT ALTER, UPDATE ON SC;       — 开启审计
NOAUDIT ALTER, UPDATE ON SC;     — 关闭审计

4.5 数据加密
加密方法：
替换加密：替换数据中的字符（如凯撒密码）。
置换加密：重新排列数据位置（如列置换）。
混合加密：结合对称与非对称加密（如AES+RSA）。
应用场景：敏感字段加密（如密码、身份证号）。

4.6 统计数据库安全性
问题：通过多次合法查询推导出敏感信息（如某疾病患者数量）。
防护策略：
查询限制：单次查询需覆盖足够多记录（如N≥100）。
交叉保护：限制查询结果的重叠比例（如M≤10%）。

4.7 小结
核心内容：
存取控制（DAC与MAC）是数据库安全的核心机制。
审计与加密提供事后追踪与主动防护。
统计数据库需防范推导攻击，确保数据匿名化。
实践意义：
合理设计权限体系与加密策略可有效防范数据泄露。
审计日志是合规性检查（如GDPR）的重要依据。
以下是基于文件内容的第五章 数据库完整性知识点归纳整理：
第五章 数据库完整性
5.1 完整性约束概述
完整性定义
目标：确保数据库中数据的正确性、有效性和一致性。
分类：
实体完整性：主键唯一且非空。
参照完整性：外键必须引用已存在的主键或为空。
用户定义完整性：业务规则约束（如年龄范围、性别枚举）。
约束实施机制
静态约束：数据本身需满足的条件（如字段非空）。
动态约束：数据操作时需满足的条件（如余额不能为负）。

5.2 实体完整性
主键约束
定义方式：
CREATE TABLE Student (
  Sno CHAR(9) PRIMARY KEY,
  Sname VARCHAR(20)
);
验证规则：
主键值唯一且不允许为空。
更新主键时需保证新值不冲突。
唯一约束
示例：确保姓名唯一：
ALTER TABLE Student ADD CONSTRAINT UQ_Sname UNIQUE (Sname);

5.3 参照完整性
外键约束
定义方式：
CREATE TABLE SC (
  Sno CHAR(9),
  Cno CHAR(4),
  Score INT,
  FOREIGN KEY (Sno) REFERENCES Student(Sno)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);
外键操作规则：
ON DELETE CASCADE：删除主表记录时级联删除子表记录。
ON UPDATE CASCADE：更新主表主键时同步更新子表外键。
SET NULL：删除或更新时将外键设为空值。
违反参照完整性的处理
拒绝操作（默认行为）。
级联操作（CASCADE）。
置空（SET NULL）。

5.4 用户定义的完整性
属性约束
非空约束（NOT NULL）：
Sname VARCHAR(20) NOT NULL
检查约束（CHECK）：
Sage INT CHECK (Sage >= 18 AND Sage <= 60)
默认值约束（DEFAULT）：
Sdept VARCHAR(20) DEFAULT ‘计算机系’
域约束
自定义数据类型：
CREATE DOMAIN GenderType CHAR(2) CHECK (VALUE IN (‘男’, ‘女’));

5.5 触发器（Trigger）
触发器的作用
主动完整性检查：在INSERT、UPDATE、DELETE时自动执行预定义逻辑。
复杂业务规则：如更新库存时自动计算总价。
触发器语法
CREATE TRIGGER before_update_score
BEFORE UPDATE ON SC
FOR EACH ROW
BEGIN
  IF NEW.Score < 0 OR NEW.Score > 100 THEN
    SIGNAL SQLSTATE ‘45000’ SET MESSAGE_TEXT = ‘成绩必须在0-100之间’;
  END IF;
END;

5.6 事务与完整性
事务的ACID特性
原子性（Atomicity）：事务全做或全不做。
一致性（Consistency）：事务保持数据完整。
隔离性（Isolation）：并发事务互不干扰。
持久性（Durability）：提交后修改永久保存。
事务与约束的协同
事务回滚时撤销违反约束的操作。
事务提交前需通过所有完整性检查。

5.7 完整性约束的冲突与解决
常见冲突场景
插入外键值时主表无对应主键。
更新主键导致子表外键失效。
检查约束与业务逻辑冲突。
解决策略
优先级规则：系统约束 > 用户定义约束。
延迟检查：事务提交时再验证约束。

5.8 小结
核心内容：
实体完整性与参照完整性是关系模型的基础，用户定义完整性扩展业务规则。
触发器提供灵活的主动约束机制，事务保证操作的原子性与一致性。
实践意义：
合理设计约束可避免数据异常（如脏数据、孤儿记录）。
触发器需谨慎使用，避免过度耦合导致维护困难。
以下是基于文件内容的第六章 数据库恢复技术知识点归纳整理：
第六章 数据库恢复技术
6.1 事务与恢复概述
事务的ACID特性
原子性（Atomicity）：事务全做或全不做。
一致性（Consistency）：事务保持数据完整。
隔离性（Isolation）：并发事务互不干扰。
持久性（Durability）：提交后修改永久保存。
故障类型
事务故障：逻辑错误（如违反约束、死锁）。
系统故障：断电、OS崩溃导致数据丢失。
介质故障：磁盘损坏导致数据不可访问。

6.2 日志文件
日志的作用
记录事务对数据库的更新操作（如INSERT、UPDATE、DELETE）。
支持UNDO（撤销未提交事务）和REDO（重做已提交事务）。
日志记录内容
事务标识（TID）、操作类型、数据对象、旧值、新值。
示例：<T1, start>, <T1, A, 100, 200>, <T1, commit>
日志管理原则
先写日志原则（WAL）：日志记录必须在数据修改前写入磁盘。

6.3 恢复技术
UNDO与REDO
UNDO：撤销未提交事务（如回滚或系统故障）。
REDO：重做已提交事务（如系统故障后恢复）。
检查点（Checkpoint）
作用：减少恢复时需处理的日志量。
步骤：
将当前内存中的脏页（已修改但未写入磁盘的数据）写入磁盘。
记录检查点日志（如<Checkpoint, T1, T2>）。

6.4 基于日志的恢复策略
事务故障恢复
UNDO失败事务：反向扫描日志，撤销未提交操作。
系统故障恢复
REDO已提交事务：正向扫描日志，重做已提交但未写入磁盘的操作。
UNDO未提交事务：反向扫描日志，撤销未提交操作。
介质故障恢复
备份恢复：从备份中恢复数据，结合日志重做后续操作。

6.5 ARIES恢复算法
三阶段恢复
分析阶段：确定故障时未提交的事务及受影响的数据页。
重做阶段：从检查点开始重做所有已提交事务。
撤销阶段：撤销未提交事务。
关键数据结构
事务表（Transaction Table）：记录活跃事务状态。
脏页表（Dirty Page Table）：记录内存中已修改但未写入磁盘的数据页。

6.6 备份与恢复策略
备份类型
完全备份：备份全部数据库数据。
增量备份：仅备份自上次备份以来修改的数据。
归档日志模式
启用归档日志后，日志文件在检查点后保留，支持时间点恢复（PITR）。

6.7 小结
核心内容：
日志与检查点是恢复技术的核心，确保数据的原子性与持久性。
ARIES算法通过分析、重做、撤销三阶段实现高效恢复。
备份策略需结合业务需求选择完全或增量备份。
实践意义：
理解恢复机制可设计高可用性数据库系统。
合理配置日志与检查点可显著减少故障恢复时间。
以下是基于文件内容的第七章 数据库设计知识点归纳整理：
第七章 数据库设计
7.1 数据库设计概述
设计目标
构造优化的数据库逻辑模式和物理结构，建立高效、可靠的数据库应用系统。
满足用户的信息管理需求和数据操作需求，支持未来扩展。
设计特点
三分技术，七分管理：需结合技术实现与业务管理需求。
迭代性：设计过程需反复调整以适应需求变化。
设计步骤
需求分析 → 概念设计 → 逻辑设计 → 物理设计 → 实施与维护。

7.2 需求分析
任务
调查用户需求，明确数据与处理要求（信息、功能、安全等）。
确定系统边界与功能范围。
方法
用户访谈：通过问卷、会议等方式收集需求。
数据流分析：绘制数据流图（DFD），明确数据输入/输出与处理流程。
建立数据字典：定义数据结构、关系及约束。
输出成果
数据字典：包含数据项、结构、流、存储等的详细描述。
需求说明书：明确功能需求与非功能需求（性能、安全）。

7.3 概念结构设计
核心任务
构建概念模型（如E-R图），抽象描述现实世界的实体与联系。
设计步骤
实体识别：确定核心实体（如学生、课程）。
属性定义：为实体分配属性（如学号、姓名）。
联系建立：定义实体间关系（1:1、1:N、M:N）及属性（如选课成绩）。
优化模型：消除冗余，确保模型简洁清晰。
E-R图表示
矩形：实体；椭圆：属性；菱形：联系；线段：连接实体与联系。

7.4 逻辑结构设计
核心任务
将概念模型（E-R图）转换为关系模式，并进行规范化处理。
转换规则
实体转换：每个实体转化为一个关系表（如学生表）。
联系转换：
1:1联系：合并到任一实体表或独立成表。
1:N联系：外键添加到“多”方表。
M:N联系：独立成表（含双方主键及联系属性）。
规范化处理
范式应用：确保关系模式满足1NF（原子性）、2NF（消除部分依赖）、3NF（消除传递依赖）、BCNF（消除主属性依赖）。
反规范化：在性能需求下适当冗余（如预计算字段）。

7.5 物理结构设计
设计内容
存储结构：确定数据文件组织方式（堆、索引、聚簇）。
存取方法：选择索引类型（B+树、哈希）以优化查询。
存储分配：规划磁盘空间，考虑数据增长与访问频率。
性能优化
索引策略：为高频查询字段（如主键、外键）创建索引。
分区技术：水平/垂直分表提升大数据量处理效率。

7.6 数据库实施与维护
实施阶段
建库建表：使用DDL语句创建数据库对象。
数据入库：通过ETL工具或脚本导入初始数据。
功能开发：编写应用程序接口（如SQL、ORM）。
系统测试：验证功能正确性与性能指标。
维护阶段
性能监控：定期分析慢查询日志，优化索引与SQL语句。
备份恢复：制定备份策略（全量、增量），确保数据安全。
版本升级：根据需求扩展字段或调整结构。

7.7 小结
核心内容：
数据库设计需经历需求分析、概念建模、逻辑转换、物理优化及实施维护的完整流程。
规范化是逻辑设计的核心，E-R模型是概念设计的基础工具。
物理设计需权衡存储效率与查询性能，维护阶段需持续优化与监控。
实践意义：
合理的设计流程可减少冗余与异常，提升系统扩展性与稳定性。
规范化与反规范化的平衡是实际项目中的关键决策点。
以下是基于文件内容的第七章 数据库设计（续）知识点归纳整理：
第七章 数据库设计（续）
7.8 高级逻辑设计
反规范化技术
适用场景：以冗余换取查询性能提升（如OLAP系统）。
常见方法：
增加冗余列：预计算常用连接字段（如订单表冗余商品价格）。
合并表：将频繁JOIN的表合并为宽表。
代价：增加存储开销，需额外维护一致性（如触发器或批量更新）。
视图设计
逻辑数据独立性：通过视图隔离基表结构变化。
安全性：限制用户仅能访问视图定义的数据子集。
示例：
CREATE VIEW Student_Course AS
SELECT S.Sno, Sname, Cno, Score
FROM Student S JOIN SC ON S.Sno = SC.Sno;

7.9 物理设计优化
索引策略
聚簇索引：数据按索引顺序存储（每表仅一个聚簇索引）。
覆盖索引：索引包含查询所需全部字段，避免回表（如CREATE INDEX idx ON SC(Sno, Score)）。
组合索引：按查询条件顺序创建（如WHERE Sno = ‘001’ AND Cno = ‘C1’）。
分区技术
水平分区：按行划分（如按时间分区订单表）。
垂直分区：按列划分（如将大字段单独存储）。
分区键选择：需与查询条件匹配（如范围查询常用时间字段）。
存储参数调优
填充因子：控制索引页填充比例，减少页分裂（如高更新表设为70%）。
缓冲池配置：分配更多内存给热点数据页。

7.10 分布式数据库设计
设计挑战
数据分片：水平分片（按行划分）、垂直分片（按列划分）。
副本管理：主从复制、多主复制，需解决冲突（如时间戳或仲裁机制）。
分布式事务：两阶段提交（2PC）保证ACID特性。
分片策略
哈希分片：根据键值哈希分布（如用户ID % 分片数）。
范围分片：按键值范围划分（如订单按日期分片）。
混合分片：结合哈希与范围（如先按地区哈希，再按时间范围）。

7.11 数据库安全设计
权限控制
最小权限原则：仅授予用户必要的权限。
角色分级：如管理员、审计员、普通用户。
加密设计
透明数据加密（TDE）：加密整个数据文件（如Oracle TDE）。
字段级加密：敏感字段单独加密（如AES加密身份证号）。
审计设计
细粒度审计：记录敏感操作（如修改工资表的SQL语句）。
日志分离：审计日志存储于独立服务器，防篡改。

7.12 数据库性能调优
查询优化
执行计划分析：使用EXPLAIN查看索引使用情况。
慢查询日志：定位执行时间超过阈值的SQL。
参数调优
内存分配：调整缓冲池、日志缓冲区大小。
并发控制：设置最大连接数、锁超时时间。
批量操作优化
批量插入：使用LOAD DATA INFILE或多值INSERT。
批量更新：结合CASE WHEN实现一次语句更新多行。

7.13 设计案例：电商系统
需求分析
实体：用户、商品、订单、库存。
关键操作：下单、支付、库存扣减、物流跟踪。
E-R模型
用户表：用户ID、姓名、地址。
订单表：订单ID、用户ID、商品ID、数量、金额。
库存表：商品ID、库存量、版本号（乐观锁）。
反规范化设计
订单快照：冗余商品名称与价格，避免历史数据变更影响查询。

7.14 小结
核心内容：
反规范化与索引优化是提升性能的关键手段。
分布式设计需权衡分片策略与事务一致性。
安全设计需结合权限、加密与审计形成纵深防御。
实践意义：
物理设计需根据业务场景选择存储与索引策略（如OLTP与OLAP差异）。
持续的性能监控与调优是数据库稳定运行的保障。
以下是基于文件内容的第七章 数据库设计（续2）知识点归纳整理：
第七章 数据库设计（续2）
7.4 逻辑结构设计（续）
7.4.1 E-R图向关系模型的转换
转换规则
实体转换：每个实体转化为一个关系表，主键为实体的主码。
示例：学生（学号，姓名，年龄，系）
联系转换：
1:1联系：可合并到任一实体表或独立成表。
1:N联系：外键添加到“多”方表。
M:N联系：独立成表，包含双方主键及联系属性。
示例：选修（学号，课程号，成绩）
属性处理
复合属性：分解为简单属性（如“地址”拆分为省、市）。
多值属性：单独建表（如电话（学号，电话号码））。

7.4.2 数据模型的优化
规范化处理
范式应用：确保关系模式满足3NF或BCNF，消除冗余。
反规范化：在查询性能需求下适当冗余（如预计算字段）。
优化策略
分解冗余表：拆分频繁更新的大表。
合并关联表：减少JOIN操作（如订单表冗余商品名称）。

7.4.3 用户子模式设计
设计目标
简化用户视图，提高安全性。
支持不同用户需求（如管理层与操作层）。
实现方法
视图机制：为不同角色创建定制视图。
字段别名：使用业务术语替代技术字段名。

7.5 数据库物理设计
存储结构设计
行存储 vs 列存储：OLTP适用行存储，OLAP适用列存储。
分区策略：
水平分区：按时间或范围划分（如订单按年分区）。
垂直分区：分离冷热数据（如将大字段单独存储）。
索引设计
B+树索引：适合范围查询与排序。
哈希索引：适合等值查询（如用户登录验证）。
聚簇索引：按主键物理排序，提升查询效率。
硬件资源分配
内存配置：增大缓冲池以提升高频数据访问速度。
磁盘规划：日志文件与数据文件分离存储。

7.6 数据库实施与维护
数据加载
ETL工具：使用Kettle或Informatica迁移数据。
批量导入：通过LOAD DATA INFILE加速初始数据入库。
试运行与调试
压力测试：模拟高并发场景验证系统稳定性。
SQL优化：通过EXPLAIN分析执行计划，添加缺失索引。
运行维护
性能监控：定期分析慢查询日志，优化Top SQL。
备份策略：
全量备份：每日凌晨执行。
增量备份：每小时备份事务日志。
版本升级：通过灰度发布逐步迁移新旧数据。

7.7 小结
核心内容：
逻辑设计需平衡规范化与性能，E-R图转换是关键。
物理设计需结合业务场景选择存储与索引策略。
维护阶段需持续监控性能，定期优化与备份。
实践意义：
合理设计用户子模式可提升系统易用性与安全性。
物理设计直接影响系统吞吐量与响应时间，需反复调优。
以下是基于文件内容的第八章 数据库编程知识点归纳整理：
第八章 数据库编程
8.1 嵌入式SQL
基本概念
嵌入式SQL：将SQL语句嵌入到宿主语言（如C、Java）中，通过预编译器处理生成可执行代码。
宿主语言：负责流程控制与数据处理，SQL负责数据库操作。
处理流程
预编译：将嵌入式SQL转换为宿主语言函数调用。
绑定参数：通过主变量（Host Variable）传递数据。
执行与通信：SQL执行结果通过主变量返回宿主语言。
游标（Cursor）
作用：处理多行查询结果，逐条读取数据。
操作步骤：
声明游标：DECLARE CURSOR。
打开游标：OPEN。
提取数据：FETCH。
关闭游标：CLOSE。
示例：
EXEC SQL DECLARE emp_cursor CURSOR FOR
SELECT ename, salary FROM Employee WHERE dept = ‘Sales’;
EXEC SQL OPEN emp_cursor;
EXEC SQL FETCH emp_cursor INTO :name, :salary;
动态SQL
适用场景：运行时动态生成SQL语句。
语法：
EXEC SQL PREPARE stmt FROM :sql_string;
EXEC SQL EXECUTE stmt;

8.2 存储过程（Stored Procedure）
定义与优势
存储过程：预编译并存储在数据库中的SQL代码块，可通过名称调用。
优点：
减少网络传输开销。
提高代码复用性与执行效率。
增强安全性（通过权限控制）。
PL/SQL块结构
CREATE PROCEDURE procedure_name (参数列表)
BEGIN
  — SQL语句与逻辑控制
  DECLARE变量;
  IF条件 THEN
    — 操作
  END IF;
  FOR循环…
END;
示例：转账存储过程
CREATE PROCEDURE transfer(
  IN from_account CHAR(10),
  IN to_account CHAR(10),
  IN amount DECIMAL
)
BEGIN
  START TRANSACTION;
  UPDATE Accounts SET balance = balance – amount WHERE account_id = from_account;
  UPDATE Accounts SET balance = balance + amount WHERE account_id = to_account;
  COMMIT;
END;
调用与管理
调用：CALL transfer(‘A001’, ‘A002’, 1000);
删除：DROP PROCEDURE transfer;

8.3 ODBC编程
ODBC架构
驱动管理器：加载数据库驱动，管理连接。
驱动程序：实现具体数据库的API调用。
数据源（DSN）：配置数据库连接信息（如MySQL、Oracle）。
编程步骤
分配环境句柄：SQLAllocHandle(SQL_HANDLE_ENV, …)。
连接数据库：SQLConnect()或SQLDriverConnect()。
执行SQL：SQLExecDirect()或预编译语句。
处理结果集：通过绑定变量获取数据。
断开连接：SQLDisconnect()。
示例代码（伪代码）
SQLHENV env;
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
SQLConnect(env, “DSN=MyDB;UID=user;PWD=pass”);
SQLExecDirect(“SELECT * FROM Employees”, …);
SQLFetch(…); // 获取数据
SQLDisconnect(env);

8.4 小结
核心内容：
嵌入式SQL通过游标与主语言交互，适合复杂事务处理。
存储过程提升代码复用与安全性，支持事务管理。
ODBC提供跨数据库访问的统一接口，依赖驱动实现。
实践意义：
嵌入式SQL与存储过程是传统企业级应用的核心技术。
ODBC/JDBC是现代应用连接数据库的标准方式，需掌握基本编程模式。
 

20250328

主键约束 不可为空 唯一

唯一性约束 可为空 唯一

ADD 数据列名 数据类型 #添加新的列

DROP COLUMN 列名 #删除列

ALTER COLUMN 列名 新的数据类型 #改该列的数据类型，只能加长不能缩短

 

以下是关于 SQL 中 UPDATE、DELETE 和 TRUNCATE 语句的使用方法及注意事项的详细说明：

1. UPDATE 语句

用途：修改表中已有的数据。

基本语法：

UPDATE 表名
SET 列1 = 值1, 列2 = 值2, …
WHERE 条件;

示例：

— 将用户ID为1的用户的年龄更新为25
UPDATE users
SET age = 25
WHERE user_id = 1;

— 同时更新多个字段
UPDATE employees
SET salary = 50000, department = ‘HR’
WHERE employee_id = 101;

注意事项：

• 务必使用 WHERE 子句：如果省略 WHERE，表中所有行的指定列都会被更新！
• 事务安全：在生产环境中，建议在更新前开启事务（如 BEGIN TRANSACTION），以便在出错时回滚。
• 备份数据：对关键数据操作前，建议先备份（如 CREATE TABLE users_backup AS SELECT * FROM users）。

2. DELETE 语句

用途：删除表中的数据行。

基本语法：

DELETE FROM 表名
WHERE 条件;

示例：

— 删除用户ID为1的记录
DELETE FROM users
WHERE user_id = 1;

— 删除所有年龄大于60的记录
DELETE FROM employees
WHERE age > 60;

注意事项：

• WHERE 子句同样关键：省略 WHERE 会删除表中所有数据！
• 性能问题：删除大量数据时，DELETE 会逐行操作并记录日志，可能较慢。
• 触发器：DELETE 会触发 DELETE 类型的触发器（如果有）。
• 事务与备份：同 UPDATE，建议操作前备份并使用事务。

3. TRUNCATE 语句

用途：快速删除表中所有数据（不可逆操作）。

基本语法：

TRUNCATE TABLE 表名;

示例：

TRUNCATE TABLE logs;  — 清空 logs 表的所有数据

注意事项：

• 不可逆：TRUNCATE 会直接删除所有数据，且无法通过事务回滚（除非在支持事务的数据库中显式使用 ROLLBACK）。
• 效率高：不逐行删除，而是释放数据页，日志记录少，速度远快于 DELETE。
• 重置自增列：会将自增列（如 AUTO_INCREMENT 或 IDENTITY）重置为初始值。
• 权限要求：通常需要更高权限（如 DROP 权限）。
• 不触发触发器：不会触发 DELETE 触发器。

DELETE vs TRUNCATE 对比

特性	DELETE	TRUNCATE
删除范围	可以有条件删除部分数据	只能删除全部数据
日志记录	逐行记录日志，较慢	整体释放数据页，速度快
事务回滚	可回滚（需在事务中）	通常不可回滚
自增列重置	不重置自增列	重置自增列到初始值
触发器	触发 DELETE 触发器	不触发
语法复杂度	支持复杂条件（如子查询）	仅能清空整个表

使用建议

1. 谨慎操作：在生产环境执行前，先用 SELECT * FROM 表名 WHERE 条件 确认影响范围。
2. 优先使用 TRUNCATE：如果需要清空表且无需条件，优先选择 TRUNCATE 以提高效率。
3. 避免全表操作：对大表执行 DELETE 或 TRUNCATE 时，注意锁表和性能问题。
4. 权限控制：严格限制对 TRUNCATE 的权限，防止误操作。

 

 

DISTINCT查询时去重

SELECT DISTINCT FROM WHERE GROUP BY HAVING ORDER BY

在 SQL 中，DISTINCT 关键字用于在查询结果中去除重复的行。当与其他子句（如 WHERE、GROUP BY、HAVING、ORDER BY）结合时，需注意它们的执行顺序和逻辑关系。以下是具体用法及示例：

1. 基本语法

SELECT DISTINCT column1, column2, …
FROM table_name
WHERE condition
GROUP BY column1, column2, …
HAVING group_condition
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];

2. 各子句与 DISTINCT 的结合使用

(1) SELECT DISTINCT 基础去重

• 作用：返回指定列的唯一组合值。
• 示例：

• — 查询不重复的部门名称
  SELECT DISTINCT department
  FROM employees;– 查询部门和职位的唯一组合
  SELECT DISTINCT department, position
  FROM employees;

(2) WHERE 子句过滤后去重

• 执行顺序：先执行 WHERE 过滤数据，再对结果去重。
• 示例：

• — 查询工资高于 5000 的员工所在城市（去重）
  SELECT DISTINCT city
  FROM employees
  WHERE salary > 5000;

(3) GROUP BY 与 DISTINCT 的异同

• 区别：
  • DISTINCT 直接去重，返回所有列的唯一值。
  • GROUP BY 用于分组聚合（如 COUNT, SUM）。
• 示例：

• — 使用 GROUP BY 统计每个部门的人数（无需 DISTINCT）
  SELECT department, COUNT(*) AS employee_count
  FROM employees
  GROUP BY department;– 若同时需要去重和分组，需明确逻辑（如统计不同职位的数量）
  SELECT department, COUNT(DISTINCT position) AS unique_positions
  FROM employees
  GROUP BY department;

(4) HAVING 过滤分组后的结果

• 作用：对 GROUP BY 分组后的结果进行条件过滤。
• 示例：

• — 查询员工数超过 5 人的部门（需 GROUP BY）
  SELECT department
  FROM employees
  GROUP BY department
  HAVING COUNT(*) > 5;– 结合 DISTINCT 和 HAVING（较少见）
  SELECT department, COUNT(DISTINCT city) AS cities
  FROM employees
  GROUP BY department
  HAVING cities > 2;

(5) ORDER BY 排序去重结果

• 作用：对最终结果按指定列排序。
• 示例：

• — 按注册时间降序排列不重复的用户名
  SELECT DISTINCT username
  FROM users
  ORDER BY registration_date DESC;

3. 注意事项

1. 多列去重：DISTINCT 作用于所有列的组合，而非单列。例如：

• SELECTDISTINCT department, position FROM employees;
  — 结果中 department 和 position 的组合是唯一的。

1. 性能问题：DISTINCT 可能导致全表扫描和排序，大数据量时需谨慎使用。
2. 避免混淆：

• • DISTINCT 不适用于聚合函数内部（但 COUNT(DISTINCT column) 是合法的）。
  • 不能同时使用 DISTINCT 和 GROUP BY 对同一列去重（逻辑冲突）。

1. 替代方案：若仅需去重，优先用 DISTINCT；若需分组统计，使用 GROUP BY。

4. 完整示例

— 查询工资高于 5000 的员工所在城市（去重），并按城市名排序
SELECT DISTINCT city
FROM employees
WHERE salary > 5000
ORDER BY city;

— 统计每个部门的不同职位数量，并筛选职位数超过 2 的部门
SELECT department, COUNT(DISTINCT position) AS unique_positions
FROM employees
GROUP BY department
HAVING unique_positions > 2;

通过合理组合这些子句，可以高效实现复杂的去重查询需求。

SELECT * from STUDENT t where ROWNUM<0; #最近六条记录

SELECT * FROM STUDENT WHERE SNAME LIKE '%张%';
SELECT * FROM STUDENT WHERE SNAME LIKE '%张__'; #下划线代表一个字符 百分号代表任意字符

    select* from STU1 t
    oDBCImporter Command Window
    where t.birthday between to_date(2000-01-01',yyyy-mm-d') and to_date(2003/12/31','yyy/mm/dd')

SELECT count(*) from STUDENT;
ASC为升序，DESC为降序
‘=’是精确匹配，不可用过量通配符
‘LIKE’是模糊匹配
11-12题
11
(1)ALTER TABLE STUDENT ADD NATION VARCHAR2(20);
(2)ALTER TABLE STUDENT DROP COLUMN NATION;
(3)INSERT INTO GRADE VALUES (‘2021110′,’3′,’80’);
(4)UPDATE GRADE SET GMARK=’70’ WHERE SNO=’2021110′;
(5)DELETE FROM GRADE WHERE SNO=’2021110′;
(6)CREATE INDEX IX_CLASS ON GRADE(CNO ASC);
(7)DROP INDEX IX_CLASS;/*这是ORACLE的写法*/
12
(1)SELECT DISTINCT CNO FROM GRADE;
(2)SELECT * FROM STUDENT T WHERE T.SsEX=’女’ AND T.CLNO=’20311′;
(3)SELECT T.SNAME,T.SSEX,T.SBIRTH FROM STUDENT T WHERE T.CLNO=’20311′ OR T.CLNO=’20312′;
(4)SELECT * FROM STUDENT T WHERE T.SNAME LIKE ‘李%’;
(5)SELECT *
FROM STUDENT
WHERE CLNO = (SELECT CLNO FROM STUDENT WHERE SNAME = ‘李勇’);
(6)SELECT
MAX(GMARK) AS 最高分,
MIN(GMARK) AS 最低分,
AVG(GMARK) AS 平均分
FROM GRADE
WHERE CNO = (SELECT CNO FROM COURSE WHERE CNAME = ‘操作系统’);
(7)SELECT COUNT(DISTINCT SNO)
FROM GRADE;
(8)SELECT COUNT(DISTINCT SNO)
FROM GRADE WHERE CNO=(SELECT CNO FROM COURSE WHERE CNAME=’操作系统’);
(9)SELECT SNAME
FROM STUDENT
WHERE CLNO IN (
SELECT CLNO
FROM CLASS
WHERE SPECIAL = ‘软件工程’
AND TRIM(INYEAR) = ‘2020’ — 处理 INYEAR 是 CHAR(8) 的空格填充问题
)
AND SUBSTR(SNO, 1, 7) NOT IN ( — 匹配 GRADE.SNO 的 CHAR(7) 长度
SELECT SNO FROM GRADE
);
/*步入迷惑*/
(10)
SELECT
C.CNO,
C.CNAME,
C.CPNO AS 直接先修课号,
(SELECT CPNO FROM COURSE WHERE CNO = C.CPNO) AS 间接先修课号,
(SELECT CNAME FROM COURSE
WHERE CNO = (SELECT CPNO FROM COURSE WHERE CNO = C.CPNO)) AS 间接先修课名
FROM COURSE C;

SQL语言初步知识点与考点归纳

一、SQL概述

1. SQL简介

• • 定义：结构化查询语言（Structured Query Language），用于管理关系型数据库的标准语言。
  • 历史与标准化：
    • 1970年，E.F.Codd提出关系模型；1974年由Boyce和Chamberlin设计初版（SEQUEL）。
    • 1986年ANSI发布首个标准（SQL-86），后续版本包括SQL-92 正在使用的标准（SQL2）、SQL:1999（支持面向对象）、SQL:2003（支持XML）等。
  • 特点：
    • 一体化：集DDL、DML、DCL于一体。
    • 非过程化：用户只需说明“做什么”，无需指定“怎么做”。
    • 面向集合：操作对象是数据集合而非单条记录。
    • 两种使用方式：自含式语言（独立使用）和嵌入式语言（嵌入其他编程语言）。

1. SQL语言分类

类别	功能	关键语句
DDL	数据定义	CREATE, ALTER, DROP
DML	数据操作	SELECT, INSERT, UPDATE, DELETE
DCL	数据控制	GRANT, REVOKE

二、数据定义语言（DDL）

1. 基本表操作

• • 创建表：

• CREATETABLE表名 (
  列名1 数据类型 [约束],
  列名2 数据类型 [约束],
  …
  [PRIMARY KEY (列名),]
  [FOREIGN KEY (列名) REFERENCES 父表名(列名),]
  [CHECK (条件),]
  );

• • • 示例：

• CREATETABLE Student (
  sno CHAR(8) NOT NULL,
  sname VARCHAR(20) NOT NULL,
  sage NUMERIC(3,0),
  ssex CHAR(2),
  clno CHAR(6),
  PRIMARY KEY (sno)
  );

• • 修改表：
    • 添加列：ALTER TABLE 表名 ADD 列名 数据类型 [约束];
    • 删除列：ALTER TABLE 表名 DROP COLUMN 列名;
    • 修改列类型：ALTER TABLE 表名 ALTER COLUMN 列名 新数据类型;
  • 删除表：DROP TABLE 表名;（删除表结构及数据，不可逆）。

1. 索引操作

• • 创建索引：

• CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED] INDEX索引名
  ON 表名 (列名 [ASC|DESC], …);

• • • 聚集索引：数据行物理存储顺序与索引顺序一致（每表仅一个）。
    • 非聚集索引：独立于数据存储的B树结构（默认类型）。
  • 删除索引：DROP INDEX 表名.索引名;

三、数据操作语言（DML）

1. 插入数据

• INSERTINTO表名 [(列名1, 列名2, …)]
  VALUES (值1, 值2, …);

• • 批量插入：INSERT INTO 表名 SELECT …（从其他表导入数据）。

1. 更新数据

• UPDATE表名
  SET 列名1 = 表达式1, 列名2 = 表达式2, …
  [WHERE 条件];

• • 示例：UPDATE Employee SET salary = salary * 1.1 WHERE dept = ‘IT’;

1. 删除数据

• DELETEFROM表名 [WHERE条件];

• • 清空表：TRUNCATE TABLE 表名;（删除所有数据，保留结构，效率高于DELETE）。

四、数据查询（SELECT）

1. 基础语法

• SELECT [DISTINCT] 列名1, 列名2, …
  FROM 表名
  [WHERE 条件]
  [GROUP BY 分组列]
  [HAVING 分组条件]
  [ORDER BY 排序列];

• • 示例：SELECT sno, sname FROM Student WHERE ssex = ‘男’;

1. 子查询

• • 嵌套查询：在WHERE或HAVING子句中使用另一个SELECT语句。
  • 示例：查找年龄大于平均年龄的学生：

• SELECT*FROM Student
  WHERE sage > (SELECT AVG(sage) FROM Student);

五、视图（View）

1. 定义与特点

• • 视图：虚拟表，基于SELECT查询结果生成，不存储数据。
  • 作用：简化复杂查询、增强安全性（限制数据访问）、逻辑数据独立性。

1. 创建与删除

• CREATEVIEW视图名 AS SELECT语句;
  DROP VIEW 视图名;

• • 示例：创建女生视图：

• CREATEVIEW Girl_Student AS
  SELECT * FROM Student WHERE ssex = ‘女’;

六、考点总结

1. 高频考点：

• • SQL语句分类：区分DDL、DML、DCL的语句。
  • 索引类型：聚集索引与非聚集索引的区别。
  • 约束类型：主键、外键、唯一性约束、检查约束的定义。
  • 子查询与连接：IN、EXISTS、JOIN的使用场景。
  • 数据类型选择：CHAR与VARCHAR、NUMERIC与FLOAT的适用场景。

1. 易错点：

• • DROP TABLE与DELETE的区别（结构 vs 数据）。
  • 修改表结构时，新增列必须允许NULL或设置默认值。
  • 索引的维护成本（过多索引影响写性能）。

 

—

一、四个基本概念
1. 数据（Data）
– 定义：描述事物的符号记录，与其语义不可分割。
– 种类：数字、文字、图形、图像、声音等。
– 示例：学生记录（李明，男，1972，江苏，计算机系，1990）需结合语义解释。

2. 数据库（DB, Database）
– 定义：长期存储在计算机内、有组织、可共享的大量数据集合。
– 特征：按数据模型组织、冗余度小、独立性高、易扩展。

3. 数据库管理系统（DBMS）
– 定义：位于用户与操作系统之间的数据管理软件。
– 功能：数据定义（DDL）、数据操纵（DML）、完整性控制、并发控制、数据库恢复等。

4. 数据库系统（DBS）
– 定义：引入数据库后的计算机系统，由数据库、DBMS、应用系统、DBA和用户构成。
– 核心：DBMS。

—

二、数据管理技术的发展
1. 人工管理阶段（20世纪50年代前）
– 特点：数据不保存、无共享、完全依赖程序、无结构。
– 应用场景：科学计算，数据量小。

2. 文件系统阶段（50年代末-60年代中）
– 特点：数据长期保存，但共享性差、冗余度大、独立性差。
– 问题：数据不一致、查询困难、维护成本高。

3. 数据库系统阶段（60年代后期）
– 特点：数据整体结构化、共享性高、独立性强、由DBMS统一管理。
– 数据模型：网状模型（第一代）、关系模型（第二代，主流）、面向对象模型（第三代）。

—

三、数据库系统结构
1. 三级模式结构
– 外模式（子模式）：用户视角的局部数据逻辑结构（如视图）。
– 模式（逻辑模式）：全局数据逻辑结构，描述数据整体框架（如关系表）。
– 内模式（存储模式）：数据的物理存储结构（如索引、存储方式）。

2. 两级映像与数据独立性
– 外模式/模式映像：保证逻辑独立性（修改模式时不影响外模式）。
– 模式/内模式映像：保证物理独立性（修改存储结构时不影响模式）。

—

四、数据库管理系统（DBMS）
1. 主要功能
– 数据定义（DDL）、数据操纵（DML）、事务管理、安全性与完整性控制、并发控制、恢复机制。

2. 组成模块
– 查询处理器、存储管理器、事务管理器、缓冲区管理器、数据字典。

—

五、数据库管理员（DBA）
– 职责：
1. 数据库设计与维护。
2. 定义完整性约束与安全策略。
3. 监控性能并优化（如重组织、重构造）。
4. 故障恢复与备份。

—

六、数据库系统特点
1. 数据共享性高：减少冗余，避免不一致性。
2. 结构化存储：整体数据按模型组织（如关系表）。
3. 独立性：物理独立性（存储变化不影响应用）、逻辑独立性（结构变化不影响应用）。
4. 可控冗余：通过设计降低冗余，提升一致性。

—

七、对比：文件系统 vs 数据库系统
| 特性 | 文件系统| 数据库系统|
|—————-|—————————|—————————–|
| 数据共享 | 共享性差，冗余度大 | 共享性高，冗余度小 |
| 数据独立性 | 依赖程序，独立性差 | 高度独立（两级映像） |
| 数据结构 | 记录内有结构，整体无结构 | 整体结构化（如关系模型） |
| 完整性控制 | 由程序自行控制 | 由DBMS统一管理 |

—

八、关键示例
– 数据独立性应用：修改存储结构（如从顺序存储改为B树索引）时，通过模式/内模式映像调整，应用程序无需改动。
– 三级模式实例：
– 模式：学生表（学号，姓名，系别）。
– 外模式：教务处视图（学号，姓名）、财务处视图（学号，奖学金）。
– 内模式：数据按学号聚簇存储，使用B+树索引。

—

SQL分类
·数据定义语言 DDL （Data Definition Language）大幅度
·用于创建、更改或删除数据库对象，如表、视图、索引及其
它对象。
·语句包括：***CREATE、ALTER、DROP***·
数据操纵语言DML（Data Manipulation Language）小幅度
■用于操纵数据库对象。
·语句包括：SELECT、INSERT、UPDATE、DELETE
·数据控制语言DCL （Data Control Language）
·用于定义控制访问对象。
·语句包括：GRANT、REVOKE
数据类型

字符型
·Char（n）：固定长度的字符串 **空的自动置空**
·Varchar（n）：可变长字符串 **来几个占几个**
·数值型
·整数型
·bigint：8字节、int：4字节、smallint：2字节、tinyint：1字节
·小数型
numeric（p,s）：定点数，整数位和小数位共p位，小数位s位， **10，2整数八位**
0<=s<=p<=38
· decimal，用法同numeric
·**货币型**
· money、smallmoney
日期型
·datetime：精确度1/300秒，8字节 ***计算方法:当前时间-(1899-1-1-0:0）***
·Smalldatetime：范围缩小，精确度1秒，4字节

创建表
·设计表的结构：
·字段的名称
·每个字段的数据类型和宽度
·字段是否允许为空
·是否有缺省值
·是否为标识列
·是否为主键、外键

语法1：
CREATE TABLE
（[NULLI NOT NULL],
[PRIMARY KEY，]
[FOREIGN KEY REFERENCES <外来关键字所
[CHECK，]）;
例1.建立student关系
CREATE TABLE student（
sno char(8) NOT NULL,
sname varchar(2o) NoT NULL,
age numeric(3,0),
sex char(2),
deptno char(6));

建表过程

create table student
(
sno CHAR(8) not null Unique(代表唯一),
sname VARCHAR2(20) not null default('缺省值'),
age NUMBER(3),
sex CHAR(2),
deptno CHAR(6)
)

tables下查看
表上右键
View是查看表结构
Qurrey data查询数据

UNIQUE代表唯一列
DEFAULT(‘代表缺省值’)
列名中最后不要有空格
CREATE TABLE stu_girl AS
SELECT * FROM STUDENT t WHERE t.SEX='02';t是别名
DROP删除表结构和数据
DELETE只删除数据行

实践课考察代码
create table student
(
Sno CHAR(7) not null ,
Sname VARCHAR2(20) not null ,
Ssex CHAR(2) not null,
Sbirth DATE,
Clno CHAR(5) not null
);
CREATE TABLE Course(
Cno CHAR(3) NOT NULL,
Cname VARCHAR2(20) NOT NULL,
Ccredit SMALLINT,
Cpno CHAR(3)
);
CREATE TABLE Class(
Clno CHAR(5) NOT NULL,
Special VARCHAR2(20) NOT NULL,
Inyear CHAR(4) NOT NULL,
"NUMBER" INTEGER,
Monitor CHAR(7)
);
CREATE TABLE Grade(
Sno CHAR(7) NOT NULL,
Cno CHAR(1) NOT NULL,
Gmark NUMERIC(4,1)
);

ADD ***数据列名*** 数据类型 #添加新的列
DROP ***COLUMN*** 列名 #删除列
ALTER ***COLUMN*** 列名 新的数据类型 #改该列的数据类型，只能加长不能缩短
ADD CONSTRAINT 约束名 具体约束 #添加约束 UNIQUE可以为空不能相同 PRIMARY不能为空不能相同

sql
alter table STUDENT
add constraint stu_pk #添加键
primary key (SNO) #主键，应用于SNO列
/
sql
INSERT #增加
UPDATE #修改
DELETE #删除

sql
INSERT INTO STUDENT (sno, sname, ssex, sbirth, clno) VALUES ('2020101', '李勇', '01', to_date('2002-08-09', 'YYYY-MM-DD'), '20311');/*时间需要使用to_date()函数*/
INSERT INTO STUDENT (sno, sname, ssex, sbirth, clno) VALUES ('2020102', '刘诗晨', '02', to_date('2003-04-01', 'YYYY-MM-DD'), '20311');
INSERT INTO STUDENT (sno, sname, ssex, sbirth, clno) VALUES ('2020103', '王一鸣', '01', to_date('2002-12-25', 'YYYY-MM-DD'), '20312');
INSERT INTO STUDENT (sno, sname, ssex, sbirth, clno) VALUES ('2020104', '张婷婷', '02', to_date('2002-10-01', 'YYYY-MM-DD'), '20312');
INSERT INTO STUDENT (sno, sname, ssex, sbirth, clno) VALUES ('2020105', '李勇敏', '02', to_date('2003-11-11', 'YYYY-MM-DD'), '21311');
INSERT INTO STUDENT (sno, sname, ssex, sbirth, clno) VALUES ('2020106', '贾向东', '02', to_date('2003-12-12', 'YYYY-MM-DD'), '21311');
INSERT INTO STUDENT VALUES ('2020107', '陈宝玉', '01', to_date('2004-05-01', 'YYYY-MM-DD'), '21311');/*当插入的数据全部按照顺序时VALUES可以省略*/
INSERT INTO STUDENT VALUES ('2020108', '张逸凡', '01', to_date('2005-01-01', 'YYYY-MM-DD'), '21311');