Obsidian-Knowledge-Base/X-Knowledge-Base/raw/Joplin/计算机专业基础/数据库/3.数据库——SQL与数据库设计.md

计算机考研复试--SQL与数据库设计核心知识点

一、SQL语言核心

1. SQL特点与功能

• 四大功能：
  • DDL（数据定义）：CREATE/DROP/ALTER
  • DML（数据操作）：INSERT/UPDATE/DELETE/SELECT
  • DCL（数据控制）：GRANT/REVOKE
  • DQL（数据查询）：复杂查询语句
• 核心特点：
  • 综合统一：集数据定义、操作、控制于一体
  • 高度非过程化：只需声明"做什么"，无需关心"怎么做"
  • 面向集合操作：操作对象和结果均为元组集合
  • 两种使用方式：独立SQL与嵌入式SQL

2. SQL高频题型

(1) 复杂查询

-- 嵌套查询示例：查询选修"数据库"课程的学生
SELECT Sname FROM Student 
WHERE Sno IN (
    SELECT Sno FROM SC 
    WHERE Cno = 'C01'
) [16](@ref)[50](@ref)

-- 连接查询示例：查询学生姓名及选修课程名
SELECT Sname,Cname FROM Student 
NATURAL JOIN SC JOIN Course USING(Cno)

(2) 视图操作

-- 创建视图：隐藏敏感字段
CREATE VIEW Student_View AS 
SELECT Sno,Sname FROM Student WHERE Dept='CS' [1](@ref)[48](@ref)

-- 视图作用：
1. 简化多表连接查询（如跨表统计）
2. 实现逻辑独立性（如屏蔽表结构修改）
3. 数据安全控制（如限制字段访问）

---

二、数据库设计方法论

1. 设计六大步骤

1. 需求分析：建立数据字典（含数据项、数据结构、数据流）
2. 概念设计：绘制E-R图（实体矩形/属性椭圆/联系菱形）
3. 逻辑设计：E-R图转关系模式（1:1合并/1:n加外键/m:n建新表）
4. 物理设计：选择存储结构（如B+树索引）
5. 实施阶段：编写应用程序与数据入库
6. 运行维护：性能监控与结构优化

2. E-R图设计要点

• 合并冲突：
  • 属性冲突：同一属性类型/单位不一致（如日期格式）
  • 命名冲突：同名异义/异名同义（如"课程"与"科目"）
  • 结构冲突：同一实体在不同图中抽象级别不同

3. 范式应用实例

范式	消除依赖	典型案例
1NF	消除多值属性	拆分"联系电话"字段为多行
2NF	消除部分函数依赖	订单表拆分（订单ID→客户ID+商品ID）
3NF	消除传递函数依赖	员工表拆分（员工→部门→部门地址）
BCNF	消除主属性部分依赖	课程-教师关系优化（教师决定课程）

---

三、高级特性与优化

1. 存储过程 vs 触发器

维度	存储过程	触发器
执行方式	显式调用（CALL proc_name）	事件驱动（INSERT/UPDATE等）
返回值	可返回多个OUT参数	无返回值
典型应用场景	批量数据处理、复杂业务逻辑	级联更新、审计日志
优点	预编译提升性能	自动维护数据完整性

2. 索引优化策略

• B+树索引：适合范围查询（WHERE age>20），有序存储特性
• Hash索引：适合等值查询（WHERE id=1001），O(1)时间复杂度
• 创建原则：
  • 频繁作为WHERE/JOIN条件的字段
  • ORDER BY/GROUP BY涉及的字段
  • 避免在更新频繁的字段上建索引

---

四、高频SQL题型速查

1. 复杂查询模板

-- 查询每个学生的平均分（GROUP BY+聚合函数）
SELECT Sno, AVG(Grade) 
FROM SC GROUP BY Sno HAVING AVG(Grade)>80

-- 全称查询：选修全部课程的学生
SELECT Sname FROM Student
WHERE NOT EXISTS (
    SELECT * FROM Course 
    WHERE NOT EXISTS (
        SELECT * FROM SC 
        WHERE Sno=Student.Sno AND Cno=Course.Cno
    )
) [16](@ref)[50](@ref)

2. 权限管理语句

-- 授权示例：允许用户李平修改学生表
GRANT UPDATE ON Student TO LiPing WITH GRANT OPTION;

-- 回收权限示例：
REVOKE DELETE ON Course FROM WangHong [1](@ref)[31](@ref)