【旧版-废弃-仅用于备份】sql-自用

前言

一、符号表示

下划线代表填空题

斜粗体代表选择题，且无论是否标注 标注什么 都可能考选择题，除非特别标注*

*表示备注 或帮理解非选择题

背景代表上下文关联

||表示or

二、考试规范

填空题

1. 填中文，比如填数据库 而不是DB
2. 考过的会再考
3. 一个字都不能写错，/斜杠不要省略，“的”不要省略，顺序不要错
4. 注意上下文，要对称
5. sql语句关键字大写且要写完整（如修改表结构是ALTER TABLE）

sql语句大题

1. 关键字（如create table）：全部大写（CREATE TABLE）

   表名、列名、值：照抄题目

2. 倒二大题的五小题（5x5=25分）：

   1. 建表——第一题可换行，后面四题都是写在一行上的
   2. 单表查询
   3. 多表查询
   4. 5. 添加删除修改数据中选两个

3. 最后别写分号;

4. 别取别名（除非题目特别说明）

5. 不等于统一用!= 不用<>

6. 字符和字符串 全部用单引号 如 ‘Lisa’

三、tips

学员问有提纲吗？老高说会有一些其它的材料在后面的课程里给大家总结归纳。然后这个课是基础课，主要是打基础完后做做真题巩固提高一下

【我好像没在资料里找到老高的提纲……不过也没事，也没必要（应该

陈志泊这本部分的课后习题会成为真题

讲题·课后习题1~2章：视频【数据库02】后半部分、【数据库03】前半部分

讲书本课后题的部分有很多我没记，可能二刷的时候得重刷一下记一下……因为老师说课上讲了的都是重点

240分和250分相差40名

真题从10年到19年，模拟题5套
老师认为15套题得做了才够
任务：2张a4纸，把数据库要考的内容 写到a4纸上。贴自己床头 每天都看 看完再睡觉

我没弄懂的：关系模型和关系模式的区别？

---

考题类型：

1. 选择题40%
2. 填空题20%
3. 根据题意写出正确的SQL语句25%
4. 计算题15%

参考用书：

《数据库原理与应用教程》陈志泊、李冬梅、王春玲编，人民邮电出版社 2002年版

《关系数据库与SQL语言》黄旭明主编，高等教育出版社，2004.01.

---

第1章 数据库系统概述

客观世界的三大要素：信息、能源、材料

一、基本概念

1. 数据库（DB）

数据库（DB）是有组织，可共享的数据集合。

如果说 数据库是“一个”xxxxx的数据集合，是错的

数据库的发展分为哪三个阶段：人工管理阶段、文件系统阶段、数据库系统阶段

• 人工管理阶段：数据不保存，不共享，无独立性，无软件管理数据
• 文件系统阶段：数据以文件形式长期保存，由文件系统管理数据（有软件，文件系统就是这个软件），程序与数据间有一定独立性
• 数据库系统阶段：数据结构化，共享性高，冗余度低，独立性高，有统一的数据控制功能

数据库的本质是一堆文件

2. 数据库管理员（DBA）

数据库的用户分为哪三种：终端用户、应用程序员、数据库管理员

3. 数据库管理系统（DBMS）

• 数据库管理系统是数据库的核心
• 数据库管理系统负责统一控制和管理各个资源
• sql server是数据库管理系统

1)数据库管理系统的功能

数据库管理系统（DBMS）的四个功能：数据的安全性控制、数据的完整性控制、并发控制、数据恢复

a)数据的安全性控制

控制非法用户的非法操作

通过权限的控制管理来控制

• 通过DCL数据控制语言：grant、revoke

b)数据的完整性控制

c)并发控制：为了保证数据的一致性

保证一致性的方法：封锁技术，张三访问x的时候不允许李四访问x(我的理解是类似java的sychronized)

d)数据恢复：

数据库的文件分为：

• 数据文件：主数据文件只有一个，次数据文件可有多个
• 日志文件：记录保存对数据库的更新操作。数据恢复要用到日志文件

4. 数据库系统（DBS）

数据库系统（DBS）的组成：数据库、数据库用户、计算机硬件系统、计算机软件系统

数据库系统范围最大

数据库系统的发展有三代，我们当前处于第二代

操作系统（OS）是位于用户和数据库之间沟通的一个桥梁

OS：操作系统

5. 数据模型

数据模型由三个部分组成：数据结构、数据操作、数据的约束条件

数据模型||逻辑模型有哪三种：层次模型、网状模型、关系模型、面向对象模型

【上下文】数据结构||逻辑结构 有哪三种：层次结构、网状结构、关系结构、面向对象结构

*只有三个空就只填前三个

层次模型：一对多。树
网状模型：多对多。有向图
关系模型：二维表

二、数据库系统的模式结构

1. 三级模式

数据库的三级模式分为：外模式、模式、内模式

• 外模式（子模式）：用户级。视图。多个。是模式的子集
• 模式（概念模式）：概念级。全部数据。一个。
• 内模式：物理级。文件。一个。

用于存放三级结构的数据库称为数据字典

2. 两层映射

数据库的两层映射分为：外模式/模式映射、模式/内模式映射

*视【上下文】选填：映射||映象||映像

数据库的独立性分为：逻辑独立性、物理独立性

*【上下文】数据库的独立性分为：物理数据独立性和逻辑数据独立性

• 外模式/模式映射对应的是：逻辑独立性
• 模式/内模式映射对应的是：物理独立性

物理独立性：如db修改了文件存储，web软件不变

逻辑独立性：如db修改了表，web软件不变

三、E-R图

1. 三个世界

数据加工经历了哪三个世界：现实世界、信息世界、计算机世界

对现实世界进行第一层抽象的模型，称为概念模型；对现实世界进行第二层抽象的模型，称为结构模型

*

信息世界中：“概念模型”=画E-R图

计算机世界：把E-R图转换为关系模式来建表

2. E-R图

两个实体型之间的联系类型有：一对一、一对多、多对多

【上下文】两个实体型之间的联系类型有1：1联系、1：n联系、m：n联系

E-R图怎么画？见【数据库01.mp4的01:00:30~01:32:39——详解考试最后一答题】

• 竖着画

• 实体用矩形，联系用菱形，属性用椭圆形

• 1：1、1：n、m：n写在菱形上下两边

• 只有联系产生的属性画右边

e.g.

64. 设有实体“运动员”，其属性为运动员号、姓名、性别、年龄；有实体“竞赛项目”，其属性为项目号、项目名、比赛时间、比赛场地；运动员和竞赛项目之间有一个联系“参赛”，一个运动员可参加多个竞赛项目，一个竞赛项目允许多名运动员参赛，运动员参赛某一项目后有成绩。
(1)根据上述语义设计E-R模型，画出E-R图。(4分)
(2)将E-R图转换成关系模型，并指出每个关系的主码。(3分)

(1)

(2)

// 教材上有下划线，考试时候别写

运动员(运动员号,姓名,性别,年龄)
	主码为运动员号
竞赛项目(项目号,项目名,比赛时间,比赛场地)
	主码为项目号
// 这个参数是多对多，多对多一定要单独转成关系模式
参赛(运动员号,项目号,成绩)
    主码为(运动员号,项目号)
// 题目没要求写外键，千万不要写，否则会错
// 如果题目让写 写成 外码为运动员号,项目号

• 1：1 直接合并。或两边任意一边的主键拿出来做出新关系模式的主键

• 1：n 与多的那边合并。或把多的那边的主键拿出来做出新关系模式的主键

• m：n 无法合并。一定要单独转成关系模式，将两边的主键合在一起设为新关系模式的主键，联系产生的新属性要记得加入

e.g. 有3个实体，2个m：n联系，要转成几个关系模式？

6个

第2章 关系模型

* 第2章 不重要 分值不大

一、关系性质

常用的关系数据库：（会考选择题“以下哪些不是关系数据库”）Foxpro、Access、Oracle、SQL Server、Informix、Sybase、DB2、mysql、ingres

关系的有关概念：

关系=关系模式=表
字段=属性=列
记录=元组=行
关键字=关系键=主码=主键
分量=列里的每一个值
域=取值范围（列的域相同=列同质）

* 关系中填属性，表中填列

关系的特征：

• 每一列中的分量必须来自同一个域，必须为同一类型的数据

• 不同的列可来自同一个域，属性名不可重复

• 列序任意，行序任意

• 每一分量必须是不可分的数据项

表中必有主键

R(U,D,DOM,F)

R：关系名

U：属性名集合

D：属性所来自的域

DOM：属性向域的映像集合

F：属性间数据的依赖关系集合

二、关系的键与关系的完整性

1. 关系的键

候选键：能唯一标识关系中元组的一个属性或属性集，称为候选键

主键（主关系键）：从多个候选键里选出一个来作为主键，每个表仅有一个主键

主属性：包含在主关系键中的各个属性称为主属性

外部关系键。

2. 关系的完整性

数据库的 完整性||约束||完整性约束 是指数据的：正确性、有效性、相容性

数据库的完整性是指防止合法用户使用数据库时向数据库中加入不符合语义的数据。

数据的完整性包括三个方面：实体完整性、参照完整性、用户自定义完整性

• 实体完整性：约束主键
• 参照完整性：约束外部关系键
• 用户自定义完整性：如字段长度或取值、如前四位必须为年份 等

三、关系代数

关系运算分为哪两种：关系代数、关系演算

关系运算的结果是关系

1. 关系代数运算符

在关系代数运算中，基本的运算是：并、差、笛卡尔积、选择、投影。

* 差和交可转换、单表不需要连接。所以去掉这俩剩下填进去

1)集合运算符：并、差、交、笛卡尔积

• ∪ 并（Union）

  R和S的并运算是什么？属于R或属于S

• - 差（Difference）

  R和S的差运算是什么？属于R不属于S

  *
  R和S有相同的关系模式，目数是n，则R和S的差运算结果的目数是？是n

• ∩ 交（Intersection）

  R和S的交运算是什么？属于R且属于S

• × 笛卡尔积（Extended Cartesian Product）

  行数相乘，列数相加

  运算符中 最花时间、运算量最大、结果最大

两张表相容才能进行并、差、交运算

相容：具有相同的度、对应列同质

集合运算中，交和差可以相互转换：R∩S = R-(R-S)

2)关系运算符：选择、投影、连接、除法

* 只有三个空就填前三个

• 选择：行筛选

• π 投影：列筛选

  π两条腿要写弯一点

• ∞ 连接

• * 自然连接：等值连接后去掉重复列

* 除法不考

3)比较运算符

4)逻辑运算符

• ∧ 与
• ∨ 或
• ┐ 非

2. 关系代数表达式

关系代数运算是以集合运算为基础的运算

* 关系代数表达式近几年都不考，但是之前有考过大题

* 考试专用教材上：S学生表，T教师表，C课程表，SC选课表，TC教师授课表

本门课中，字符或字符串都是单引号’xxx’

e.g.

e.g.

e.g.

e.g.【标准模板】

第3章 SQL

1. 基本概念

sql是结构化查询语言

编写应用程序的语言（如c语言等高级语言）是宿主语言

sql包含哪几种操作：数据查询、数据定义、数据操纵、数据控制

• DDL 数据定义语言

  CREATE、ALTER（修改结构）、DROP（删除结构）

• DML 数据操纵语言

  SELECT、DELETE、INSERT、UPDATE

• DCL 数据控制语言

  GRANT（授权）、REVOKE（回收权限）

sql语言的特点：非过程语言、面向集合、自含式又嵌入式、支持面向对象的扩展

2. 表的创建与使用

数据类型

INT、FLOAT、CHAR、VARCHAR

* NCHAR(2)是两个中文的长度，CHAR(2)是两个英文的长度

建表

CREATE TABLE s
(
    Sno INT PRIMARY KEY,
    Sname VARCHAR(10) NOT NULL,
    Age INT DEFAULT 18,
    Tel CHAR(11) UNIQUE,
    Sex CHAR(2),
    CHECK(Sex='男' OR Sex='女')
)

CREATE TABLE sc
(
    Sno INT FOREIGN KEY REFERENCES s(Sno),
    Cno INT FOREIGN KEY REFERENCES c(Cno),
    Score INT,
    PRIMARY KEY(Sno,Cno)
)

改表：ALTER TABLE

• 只能改大（如长度20改30，NOT NULL改NULL）不能改小
• 不能改列名

删表：DROP TABLE

3. 索引

创建索引：CREATE INDEX

对指定列做排序，目的是提高查询效率

索引越多更新速度越慢，所以对于数据频繁更新的表应当少建索引

• 聚集索引（CREATE CLUSTERED INDEX）：只能有一个，在表上直接排序

• 非聚集索引（默认）：可以有多个，将表复制后排序

唯一索引（CREATE UNIQUE INDEX）：指定列中的值不重复

删除索引：DROP INDEX

4. 查询

1)基本使用

SELECT SNo,SUM(Score) FROM SC WHERE Score>=60 GROUP BY SNO HAVING COUNT(*)>=3 ORDER BY SUM(Score) DESC

执行顺序：WHERE的条件筛选、GROUP BY分组，库函数统计、HAVING的条件分组（分组后再进行条件筛选）、ORDER BY排序

2)比较运算符

运算符	含义
=, >, <, >=, <=, !=, <>	比较大小
AND, OR, NOT	多重条件
[NOT] BETWEEN AND	确定范围
[NOT] IN	确定集合
[NOT] LIKE	字符匹配
IS [NOT] NULL	空值

注意：等于是= 不是==

部分匹配查询LIKE：

• 匹配单个字 _

• 匹配任意多个字 %

SELECT TNo,TN FROM T WHERE TN LIKE '_力%'
// 查询姓名中第二个汉字是“力”的教师号和姓名

空值查询NULL：

空值不同于零和空格，它不占任何存储空间

3)常用库函数

函数名称	功能
AVG	按列计算平均值
SUM	按列计算值的总和
MAX	求一列中的最大值
MIN	求一列中的最小值
COUNT	按列计算个数

SELECT SUM(Score), AVG(Score) FROM SC WHERE SNo='S1'
// 求学号为S1学生的总分和平均分

SELECT MAX(Score)-MIN(Score) FROM SC WHERE CNo='C1'
// 求选修C1号课程的最高分与最低分之间相差的分数

// *
SELECT COUNT(DISTINCT Dept) FROM S
// 求学校中共有多少个系
// * DISTINCT：消去重复行。另 COUNT(*)不去重且不允许使用DISTINCT

SELECT COUNT(Score) FROM SC
// 成绩为零的同学它计算在内，没有成绩（空值）的不计算
// 本人觉得这个不行 要 SELECT COUNT(DISTINCT SNo) FROM SC WHERE Score IS NOT NULL 好一点

4)分组查询：GROUP BY

3~5年考一次

很容易因为想不到要用到分组查询而失分
注意：题目出现“每个” “各个”要考虑分组查询

SELECT TNo,COUNT(*) FROM TC GROUP BY TNo
// 查询各个教师的教师号及其任课的门数
// SELECT 后面一定要带 GROUP BY后面那个做分组依据的属性

5)排序ORDER BY

• 升序ASC（默认）
• 降序DESC

SELECT SNo,Score FROM SC ORDER BY Score DESC,SNo
// 查询所有学生学号和成绩，并按成绩降序排列，成绩相同则按学号排列

6)连接查询（多表查询）

考试用=来连接 不用JOIN

SELECT T.TNo,TN,CNo FROM T,TC WHERE T.TNo=TC.TNo AND TN='刘伟'
// 查询“刘伟”老师所教授的课程，要求列出教师号、教师姓名和课程号
// 两表中重要的所查信息多的作为主表 写前面
// 注意要写T.TNo不要写TNo。主表.两表共同属性名

// *
SELECT S.SNo,SN,CN,Score FROM S,C,SC WHERE S,SNo=SC.SNo AND SC.CNo=C.CNo
// 查询所有选课学生的学号、姓名、选课名称及成绩

7)子查询（嵌套查询）

仅选择题

* e.g.
SELECT * FROM T
WHERE Prof=(SELECT Prof FROM T WHERE TN=‘刘伟’)

=、!=、>、<后面只能跟一个值，当有多个值时用ANY或ALL

ANY：某一个。
ALL：全部。

选择题会考↓：

	相当于
=ANY(…)	IN(…)
>ANY(…)	>MIN(…)
>ALL(…)	>MAX(…)
!=ALL(…) <>ALL(…)	NOT IN(…)

8)合并查询：UNION

仅选择题

4行 UNION 3行
有1行重复，结果是6行

4行 UNION ALL 3行
有n行重复，结果是7行

5. 数据更新

1)添加

INSERT INTO S(...) VALUES(...)

* 插入多行数据：INSERT INTO S(…) SELECT子查询

2)修改

UPDATE S SET Age=Age+1 WHERE SN='Lisa'

3)删除

DELETE FROM S WHERE SN='Lisa'

6. 视图

• 视图是虚表，其数据不进行存储
• 简化了查询操作，没有提高查询速度
• 保证了数据的逻辑独立性

创建视图

近5年没考过，但是之前有考过大题

CREATE VIEW V[(视图列表)] AS SELECT子查询

考试没说视图的列名叫什么，就别写[]及其里面的内容

CREATE VIEW SNo_SN AS SELECT S.SNo,SN FROM S
// 创建一学生情况视图SNo_SN（包括学号、姓名）

CREATE VIEW S_Avg AS SELECT SNo,AVG(Score) FROM SC GROUP BY SNo
// 创建一学生平均成绩视图S_Avg

修改视图：ALTER VIEW

修改视图后可能会修改原表里数据

删除视图：DROP VIEW

删除视图后对原表毫无影响

7. 数据控制

WITH GRANT OPTION 允许转授权限

第4章 关系数据库理论

一、函数依赖

• 假设在关系R中，X、Y为R的两个属性，如果每个X值只有一个Y值与之对应，则称属性Y函数依赖于属性X；或称属性X唯一确定属性Y，记作X→Y

  理解：例如 Sno→Sname（01→张三）

  • 如果X→Y，同时X不包含Y，则称X→Y是非平凡函数依赖

  • 如果X→Y，同时X包含Y，则称X→Y是平凡函数依赖

    理解：AB→A是平凡函数依赖

• 否则X-/->Y

函数依赖的类型分为哪三种：完全函数依赖、部分函数依赖、传递函数依赖

理解：

1. 已知A→C。那么AB→C就是部分函数依赖，A→C是完全函数依赖
2. 传递函数依赖：主键X→Y，Y→Z。则X→Z
3. 完全函数依赖是好的，部分函数依赖和传递函数依赖是不好的

二、范式

1NF ⊇ 2NF ⊇ 3NF ⊇ BCNF

默认都是第一范式(1NF)

第二范式(2NF)

每一个非主属性完全函数依赖于主键

* 单个列设为主键时，那一定是完全依赖

第三范式(3NF)

属于2NF，且任何非主属性不传递函数依赖于任何主键

BCNF

定义：若X→Y，Y不在X内，且X包含主键。每一个关系模式都满足上述要求，那就是BCNF。

BCNF是最高的范式

三、关系模式的规范化

关系模式的规范化能解决什么问题：数据冗余、插入异常、删除异常、更新异常

关系模式的规范化中要遵循的原则：无损连接性、函数依赖保持性

做题

e.g.1

设有关系模式R(A,B,C,D)，其函数依赖集合F={(A,B)→C,C→D}。
（1）指出此关系模式的主码。（4分）
（2）判断此关系模式最高属于第几范式。（4分）

AB→C
C→D
解题方法：看右边。右边没有AB，所以AB就是主键（但是最后自己要验证一下AB要可以推出其它所有，可能出现特殊情况）
答题卡上写：主码为(A,B)

第二题答题卡上写：2NF

e.g.2

设有关系模式R(A,B,C,D,E)，R中属性均不可再分解，其函数依赖集F={AB→C,AB→D,AB→E}。
（1）指出此关系模式的主码。（4分）
（2）判断此关系模式最高属于什么范式。（4分）

AB→C
AB→D
AB→E

主码为(A,B)

判断得出是3NF了，现在判断是不是BCNF：

第一行：AB包含主键AB、且C不属于AB。
第二行：AB包含主键AB、且D不属于AB。
第三行：AB包含主键AB、且E不属于AB。

所以是BCNF

e.g.3 特殊情况（考的概率不高 近十年没考了）

AB→C
B→D
D→B

虽然只有A不在右边，但是可以看出A-/->C，所以A不是主键。
这个是特殊情况，可以看出：B→D，D→B。所以B和D是等价的。

所以看右边只要看AB→C，B→D就行了（当然AB→C，D→B也行）
此时可以看出 主码是(A,B)或(A,D)

问候选键：多个候选键分隔用 逗号

问主键：只有一个主键。所以分隔用“或”

推第几范式：

注意：A、B、D都是主属性。推范式是考虑非主属性

推出是3NF（C完全依赖于AB，AB→C后C不能推出其它。看出不是BCNF）

e.g.4

姓名→年龄
姓名→系名
系名→系地址

主码是姓名 2NF

第5章 数据库安全保护

SQL Server的身份验证模式分为哪两种：Windows身份验证模式、混合身份验证模式

事务是数据库系统中执行的一个工作单位

BEGIN TRANSACTION 事务的开始
COMMIT 事务的提交
ROLLBACK 事务的回滚

事务的特征分为：原子性、一致性、隔离性、持久性

第6章 数据库设计

一、概述

数据库设计包含哪两部分：结构设计、行为设计

• 结构设计主要 包含哪些东西||体现在哪些方面：概念设计、逻辑设计、物理设计

• 行为设计体现在应用程序的设计

  数据库行为通过应用程序实现

二、数据库设计的六个阶段

1. 系统需求分析阶段
2. 概念[结构]设计阶段
3. 逻辑[结构]设计阶段
4. 物理[结构]设计阶段
5. 数据库实施阶段
6. 数据库运行与维护阶段

上下文：[]内看情况填

1. 系统需求分析阶段

任务：调查分析用户活动

是数据库设计过程中最重要、最花时间的阶段

系统需求分析阶段有两个工具：数据流图（DFD）、数据字典（DD）

• 数据字典是由哪几部分组成的：数据项、数据结构、数据流、数据存储、处理过程

2. 概念结构设计阶段

任务：画E-R图

是数据库设计过程中与计算机无关、对用户的需求进行分析和整理总结的阶段

冲突分为哪三种：属性冲突、命名冲突、结构冲突

*
E-R图有两种：局部E-R模型，全局E-R模型
将局部E-R图合并成全局E-R图的时候可能会产生冲突

3. 逻辑结构设计阶段

任务：关系模型规范化

如将1NF转换为3NF

关系模式的改进主要体现在哪两个方面：合并、分解

4. 物理结构设计

任务：确定存储方案

会考虑索引

5. 数据库实施阶段

任务：创建数据库、数据库测试

6. 数据库运行和维护

维护：重新组织和构造数据库

变化太大时，与其维护，不如重新做一个