第一章

1.数据库系统概述

1.1. 数据（Data）

是数据库中存储的基本对象

1.2.数据库（DataBase，简称DB）

数据库是长期存储在计算机内、有组织的、可共享的数据集合。
数据库中的数据按一定的数据模型描述、组织和储存，具有较小的冗余度、较高的数据独立性和易扩展性，可为用户共享。
数据库中的数据要求具有两个特性：（1）整体性；（2）共享性

1.3.数据库管理系统（DataBase Management System，简称DBMS）

数据库管理系统是专门用于建立和管理数据库的一套软件，介于应用程序和操作系统之间 (位于用户和数据库之间)。

1.4.数据库系统（DataBase System，简称DBS）

数据库系统 = 数据库（DB）+数据库管理系统（DBMS）+应用程序+数据库管理员。

1.5.数据库系统的特点

数据结构化
数据的共享性高、冗余度低、易扩充
数据独立性高：物理独立性+逻辑独立性
数据由数据库管理系统DBMS统一管理和控制

2.数据模型

现实——>(概念模型)——>信息——>(数据模型)——>数据
码(Key):能唯一标识每个实体的属性或属性集
数据模型通常由数据结构、数据操作和完整性约束三要素组成。

2.1.✒️常用的数据模型

层次模型、网状模型、关系模型
层次模型：

仅允许自顶向下的单项查询
适合表示一对多联系
数据依赖性强，上层记录不存在时，下层记录无法储存
语义完整性差，某些数据只有从上下层关系查看时，才能显示全部含义

网状模型：

一个子结点可以有多个父结点；在两个结点之间可以有两种或多种联系
可从网络中任一结点查询；沿着网络中的路径按任意方向查询
结构越复杂，其功能越强，操作也越复杂，操作语言也越复杂

关系模型：

描述的一致性
利用公共属性连接
结构简单直观
有严格的理论基础
语言表达简练

3.✒️数据库系统结构

3.1.三级模式结构

数据库系统的三级模式结构由外模式（用户级）、模式（概念级）、内模式（物理级） 组成。
在这种模式下，形成了二级映像，实现了数据的独立性。
c4.

模式：也称逻辑模式和概念模式，是数据库中全体数据逻辑结构和特征的描述，是所有用户的公共数据视图
外模式：也称子模式或用户模式，它是用以描述用户看到或使用的数据的局部逻辑结构和特性的，描述用户所看到的数据视图，它是用户和数据库的接口
内模式：也称存储模式，它是数据物理结构和存储方式的描述，是数据在数据库内部的表示方式

3.2.数据库系统二级映像

外模式/模式映像：
当模式改变时，外模式/模式映像改变，使外模式和应用程序不变，这保证了数据的逻辑独立性。
模式/内模式映像：
当内模式改变时，模式/内模式映像改变，使模式保持不变，这保证了数据的物理独立性。

第二章

1.关系数据模型

1.1.✒️关系数据模型的构成

✒️（1）关系数据模型由关系数据结构、关系操作集合和关系完整性约束3个部分组成。
域:一组具有相同数据类型的值的集合
笛卡儿积:域上的一种集合运算
✒️关系模型允许定义3类完整性约束，即 实体完整性、参照完整性、用户定义的完整性。
（2）关系的性质

关系中的每一个属性值都是不可分解的
关系中不允许出现重复元组（行）
不考虑行列的顺序
列是同质的，即这一列具有相同的数据类型

✒️（3）关系（数据库）的完整性：实体完整性规则、参照完整性规则、用户定义完整性规则。

实体完整性规则：主码不能为空，且主码整体取值也不为空
参照完整性规则：如果属性集K是R1的外键，同时K也是R2的属性，但不是R2的主键，那么K为R2的外键。在R2中，K的取值只允许有两种可能：①、空值；②、不为空时，等于R1关系中某个主键值。
用户定义的完整性规则：用户针对具体的数据约束，设置完整性规则。

1.2.基本术语

关系：规范的二维表
属性：二维表中的列称为属性
值域：二维表中属性的取值范围
元组：二维表中的行称为元组
分量：元组中的每一个属性值称为元组的一个分量
候选码：如果一个属性集的值能唯一标识一个关系的元组且不含多余的属性，称该属性集为候选码。
主键：一个关系中可有多个候选码，可以选其中一个作为主键。每一个关系都有且只有一个主键
主属性：包含在任一候选码中的属性
非主属性：不包含在任一候选码中的属性
外键：如果关系模式R中的属性K是其他关系模式的主键，则K在关系模式R中称为外键。
全码：若关系中只有一个候选码，且这个候选码中包含全部属性，则该候选码为全码。

2.关系代数

第三章、结构化查询语句SQL

SQL语言的特点：

综合统一
高度非过程化
面向集合的操作方式
用同一种语法结构提供两种使用方式
语言简洁，易学易用

SELECT 查询、CREATE 创建、DROP 删除、ALTER 修改、ADD增加
INSERT 插入数据、UPDATE 修改数据、DELETE 删除数据

1.数据定义语句

1.1.基本表的创建

CREATE TABLE 表名(
    prj_num char(8)  PRIMARY KEY,     /**列名 数据类型 列级完整性约束条件**/
    prj_name varchar(50),
    start_date datetime,
    end_date datetime,
    prj_status bit,
);

1.2.基本表的修改

1
2
3

ALTER TABLE 表名 ADD 新列名 数据类型；  -- 添加列
ALTER TABLE 表名 DROP COLUMN 列名;   -- 删除列
ALTER TABLE 表名 ALTER COLUMN 列名 数据类型;   -- 将表的某一列的数据类型修改;

1.3.基本表的删除

1	DROP TABLE 表名;

2.查询

SELECT 目标列表达式     ---列   所查内容
FROM 表名或视图名       ---来自那些表
[WHERE<条件表达式>]     ---条件
[GROUP BY 列名1 [HAVING <条件表达式>]]
[ORDER BY 列名2 [ASC|DESC]];

如果有GROUP BY子句，则将结果按<列名1>的值进行分组，该属性列值相等的元组为一个组；
如果GROUP BY子句带有HAVING短语，则只有满足HAVING后面指定条件的组才给予输出。
如果有ORDER BY子句，则结果表要按<列名2>的值升序或降序排序。

2.1.单表查询

1 2	SELECT mat_num,mat_name,speci -- 查询三个列 FROM Stock;

1 2	SELECT * -- 查询所有列 FROM Stock;

(1)datediff 日期函数

<目标列表达式>可以是表中存在的属性列,也可以是表达式、字符串常量或函数
函数 datediff(datepart,date1,date2)   返回date1和date2的时间间隔,其单位由datepart决定

datediff(datepart,date1,date2)  -- 格式
例子
SELECT prj_name, start_date, end_date, datediff(day,start_date,end_date)
FROM Salvaging;

✒️ (2)去掉表中的重复行 (DISTINCT)

1 2	SELECT DISTINCT 名称 FROM 名称;

1 2	SELECT DISTINCT warehouse FROM Stock;

(3)WHERE子句常用的查询条件
1.比较运算符:=、>、<、>=、<=、NOT

1.查询供电局1中存放的所有物资编号、名称、规格、数量
SELECT mat_num,mat_name,speci,amount
FROM Stock
WHERE warehouse = '供电局1';
2.查询所有单价小于80的物资名称、数量、单价
SELECT mat_name,amount,unit
FROM Stock
WHERE unit < 80;  -- 或  WHERE NOT unit >= 80

2.确定范围的查询:BETWEEN…AND、NOT BETWEEN…AND

查询单价在50~100的物资名称、数量及其单价
SELECT mat_name,amount,unit
FROM Stock
WHERE unit BETWEEN 50 AND 100;

3.确定集合的查询:IN、NOT IN

1.查询存放在供电局1和供电局2的物资名称、规格、数量
SELECT mat_name,speci,amount
FROM Stock
WHERE warehouse IN ('供电局1','供电局2');
2.查询没有存放在供电局1和供电局2的物资名称、规格、数量
SELECT mat_name,speci,amount
FROM Stock
WHERE warehouse NOT IN ('供电局1','供电局2');

✒️4.字符匹配的查询:LIKE、NOT LIKE。(用于查找与指定列名相匹配的元组)
[NOT] LINK ‘匹配符’
—- % (百分号)代表任意长度的字符串。例 a%b:表示以a开头,以b结尾的任意长度的字符串
—- _ (下横线)代表任意单个字符。例 a_b:表示以a开头,以b结尾的长度为3的任意字符串

查询所有绝缘电线的物资编号、名称、规格
SELECT mat_num,mat_name,speci
FROM Stock
WHERE mat_name LIKE '%绝缘电线';

5.空值:IS NULL、IS NOT NULL。(判断取值是否为空)

查询无库存单价的物资编号、名称
SELECT mat_num,mat_name
FROM Stock
WHERE unit IS NULL;

6.多重条件查询：AND(和)、OR(或)
AND两个条件都要满足；OR满足一个即可

查询规格为120的护套绝缘电线的物资编号、数量、地点
SELECT mat_num,amount,warehouse
FROM Stock
WHERE mat_name = '护套绝缘电线' AND speci = '120';

(4)排序 ORDER BY

1	ORDER BY 列名 ASC\|DESC -- ASC升序;DESC降序

1.查询“护套绝缘电线”的物资名称、单价，查询结果按单价降序排序
SELECT mat_name,unit
FROM Stock
WHERE mat_name = '护套绝缘电线' ORDER BY unit DESC;
2.查询所有物资的信息，查询结果按所在仓库名降序排序，同一仓库的物资按库存量升序排序。
SELECT *
FROM Stock
ORDER BY warehouse DESC,amount (ASC);  -- 默认为升序，ASC可省略

✒️ (5)TOP 子句的用法
放在 SELECT 后
TOP n 子句:在查询结果中输出前面的n条记录
TOP n PERCENT 子句:在查询结果中输出前面占结果记录总数的n% 条记录

显示Stock表中库存量最大的两条记录
SELECT TOP 2 *
FROM Stock
ORDER BY amount DESC;

✒️ (6)聚集函数

放在 SELECT 后
COUNT (DISTINCT|ALL * )     统计表中元组个数
COUNT (列名)                  统计一列中值的个数
SUM (列名)                    求和
AVG (列名)                    求平均值
MAX (列名)                    求最大值
MIN (列名)                    求最小值
DISTINCT 短语表示在计算时要取消指定列中的重复值
WHERE后面不能使用聚集函数

1.统计领取了物资的抢修工程项目数
SELECT COUNT (DISTINCT prj_num)
FROM Stock;
2.查询使用001号物资的抢修工程的最高领取数量、最低领取数量、平均领取数量
SELECT MAX(amount),MIN(amount),AVG(amount)
FROM Stock
WHERE mat_num = '001';

✒️ (7)分组 GROUP BY
GROUP BY子句将查询结果按某一列或多列的值分组，值相等的为一组。

1
2
3

GROUP BY 列名
[HAVING 组提取条件]
HAVING可以使用聚集函数，WHERE不能用

1.查询每个抢修工程项目号和使用的物资种类
SELECT prj_num 项目号,COUNT (*)物资种类
FROM Stock
GROUP BY prj_num;
2.查询使用了两种及两种以上物资的抢修工程项目号
SELECT prj_num
FROM Stock
GROUP BY prj_num
HAVING COUNT(*)>=2;

✒️2.2.连接查询(多表查询)

(1).等值与非等值连接查询

表名1.列名1 比较运算符 表名2.列名2;
或 表名1.列名1 BETWEEN 表名2.列名2 AND 表名2.列名3;
比较运算符 ＝ 、＞ 、＜ 、＞＝ 、＜＝ 、！＝(或＜＞)
＝ 是 等值连接,其他为非等值连接
在等值连接中把目标列中重复的属性去掉,则为自然连接

(2).外连接查询

1
2
3

左外连接:LEFT OUTER JOIN
右外连接:RIGHT OUTER JOIN
全外连接:FULL OUTER JOIN

(3).复合条件连接查询
WHERE 子句中可以有多个连接条件,称为复合条件查询

查询项目号为“20100015”的抢修项目所使用的物资编号、物资名称、规格和使用数量
SELECT Out_stock.mat_num,mat_name,speci,Out_stock.amount
FROM Stock,Out_stock
WHERE Stock.mat_num = Out_stock.mat_num AND prj_num = '20100015'

(4).自身连接查询

1 2	同一个表与自己进行连接必须给表取别名加以区分,必须加前缀

查询同时使用了物资编号为m001和m002的抢修工程的工程号
SELECT A.prj_num
FROM Out_stock A, Out_stock B
WHERE A.prj_num = B.prj_num AND A.mat_num = 'm001' AND B.mat_num = 'm002';

✒️2.3.嵌套查询

子查询的 SELECT 语句不能使用 ORDER BY 子句,ORDER BY 子句只能对最终查询结果排序
(1).带谓词 IN 的嵌套查询

查询工程项目“观澜站光缆抢修”所使用的物资编号和物资名称
SELECT mat_num,mat_name
FROM Stock
WHERE mat_num IN
      (SELECT mat_num
       FROM Out_stock
       WHERE prj_num IN
          (SELECT prj_num
           FROM Salvaging
           WHERE prj_name = '观澜站光缆抢修'));

(2).带有 ANY 或 ALL 谓词的子查询

1 2	> ANY 大于子查询结果中的某个值 > ALL 大于子查询结果中的所有值...

查询其他仓库中比供电局 1# 仓库的某一物资库存量少的物资名称、规格和数量
SELECT mat_name,speci,amount
FROM Stock
WHERE warehouse <> '供电局 1# 仓库'
        AND amount < ANY
          (SELECT amount
           FROM Stock
           WHERE warehouse = '供电局 1# 仓库');

2.4.集合查询

并操作 UNION
交操作 INTERSECT
差操作 EXCEPT
集合操作会自动去除重复元组,保留时要用 ALL 关键词

3.数据更新

3.1.插入数据

(1)插入单/多行元组

INSERT
INTO 表名 [属性列1,属性列2...]
VALUES (常量1,常量2...)
       (常量1,常量2...);

(2)插入子查询结果

1
2
3

INSERT
INTO 表名 [属性列1,属性列2...]
子查询;

✒️3.2.修改数据

(1)修改单个元组的值

UPDATE
SET 列名 = 表达式  -- SET复制
[WHERE 条件] ;
---WHERE省略表示要修改表中所有元组

将编号为 “m020” 的物资单价改为 44.5
UPDATE
SET unit = 44.5
WHERE mat_num = m020;

(2)修改多个元组的值

1
2
3

将所有物资的单价增加 1
UPDATE Stock
SET unit = unit + 1;

(3)带子查询的修改

将供电局1中的所有物资的领取数量置零
UPDATE Stock1
SET amount = 0
WHERE mat_num IN(
    SELECT mat_num
    FROM Stock2
    WHERE warehouse = '供电局1'）; 
)

3.3.删除数据

DELETE
FROM 表名
WHERE 条件 ;
---WHERE 省略表示删除表中所有元组

4.视图

视图是从一个或几个基本表（或视图）导出的表，它与基本表不同，是一个虚表。
数据库中只存放视图的定义，不存放视图对应的数据，数据存放在原来的基本表中。

4.1.创建视图

CREATE VIEW 视图名 [列名,列名...]
AS 子查询
[WITN CHECK OPTION] ;
---子查询可以是任意的 SELECT 语句,但不允许含有 ORDER BY 子句
---组成视图的属性列名要么全部省略,要么全部指定

例题
CREATE VIEW s1_stock
AS
 SELECT mat_num,mat_name,speci,amount,unit
 FROM Stock
 WHERE warehouse = '供电局 1# 仓库';

在创建视图时还可以用带有聚集函数和GROUP BY子句的查询来定义视图，称为分组视图。

4.2.删除视图

1	DROP VIEW 视图名;

4.3.查询视图(和查询表一样)

✒️视图消解法，转换成等价的对基本表的查询
对视图的查询实际上就是对基本表的查询

4.4.更新视图

插入 INSERT
删除 DELETE
修改 UPDATE

第四章、

✒️1.存储过程

(1).创建存储过程

1
2
3

CREATE PROCEDURE 存储过程名
AS
 SQL 语句

(2).修改存储过程

1
2
3

ALTER PROCEDURE 存储过程名
AS
 SQL 语句

(3).删除存储过程

1	DROP PROCEDURE 存储过程名

✒️2.触发器

AFTER 触发器
INSTEAD OF 触发器
(1).创建触发器

CREATE TRIGGER 触发器名
ON 表名|视图名
AS
 SQL 语句

第五章、范式

1.第一范式

简称1NF，记作R∈1NF，关系模式R中的所有属性都是简单结构，不可再分的。
满足第一范式的关系R中既存在非主属性对码的完全函数依赖，又存在非主属性对码的部分函数依赖和传递函数依赖。
为了去掉过于复杂的函数依赖，向高一级的范式转化，范式越高，冗余越小。

2.第二范式

简称2NF,记作R∈2NF，如果关系模式R∈1NF，且每个非主属性都完全函数依赖于R的码，则称R属于第二范式。(在1NF基础上消除非主属性对主码的部分函数依赖)

3.第三范式

简称3NF,记作R∈3NF,如果关系模式R∈2NF，且每个非主属性都不传递函数依赖于R的候选码，则称R属于第三范式。(在2NF基础上消除非主属性对码的传递依赖)

第六章、索引

1.索引的概念

（1）聚集索引
聚集索引的特点是数据文件中的记录按照索引键指定的顺序排序，使具有相同索引键值的记录在物理上聚集在一起。
一个数据表只能有一种实际存储顺序，所以一个数据表只能建立一个聚集索引。
（2）非聚集索引
非聚集索引不会影响数据表中记录的实际存储顺序，与存储顺序无关
有利于单行查询，不利于范围查询
一个表可以有多个非聚集索引
（3）唯一索引
唯一索引不允许表中任何两行具有相同索引值的索引
主键索引是唯一索引的特定类型

2.索引操作

创建索引

CREATE 索引类型
INDEX 索引名称 ON 用于创建索引的表或视图 (用于创建索引的列 指定索引列的排列方式,ASC升序、DESC降序)
--- UNIQUE :创建一个唯一索引
--- CLUSTERED :聚集索引
--- NONCLUSTERED :非聚集索引

查看索引

1 2	USE 数据库名 Exec sp_helpindex 所查表

删除索引

1	DROP INDEX 索引名

第七章 E-R图

1.基本概念

实体：长方形表示
属性：椭圆表示
联系：菱形表示

2.画E-R图

3.转化为关系数据模型

（1）.E-R图的实体转换为关系（表名）
（2）.E-R图的属性转换为关系的属性
（3）.联系的转换
①联系种类：1:1、1:n(n:1)、n:m
②联系转换方法

第一种方法，将联系转换成一个关系（表），这个关系的属性是相连的两个关系的主键和联系的属性。
第二种方法将一个关系的主键和联系的属性写到另一个关系中。
1:1两种方法都可
1:n两种方法都可，写到n那一边的属性里
n:m只能第一种方法