数据库

数据库管理系统类型

关系型数据库~

使用关系模型来存储数据，使用二维表来关联数据。
常见有：MySQL、ORACLE、PostgreSQL

非关系型~

对前者的补充和发展。
常见：Redis、mongoDB、neo4j、cassandra

SQL

用来操作关系型数据库的语言，用于操作关系型数据库中表中的数据。
一般不区分大小写，但是对于关键字，一般全大写。
分为有以下几种：

DDL 数据定义语言

定义数据库对象，如数据库、表、列等
相关关键字：CREATE、DROP、ALTER、TRUNCATE等。

DML 数据操作语言

用于对数据库中的记录进行新增、删除或修改操作
相关关键字有：INSERT、UPDATE、DELETE、CALL

DQL 数据查询语言

查询数据库中记录。
SELECT、WHERE等

DCL 数据控制语言

定义数据库访问权限和安全级别。
GRANT、REVOKE等。

基本表与导出表

基本表

实际存在，每个表在存储中可用一个存储文件表示

导出表

由基本表导出的，有视图和快照

视图

虚表，即视图对应的数据不实际存储在数据库中，只在数据库的数据字典中存储视图的定义，其一经定义即可和基本表一样进行查询等操作，也可用来定义新的视图。

快照

某时刻数据库中数据的静态副本，例：

CREATE MATERIALIZED VIEW employee_snapshot AS
SELECT employee_id, first_name, last_name, department
FROM employees;

数据定义功能

1. 定义、删除模式
2. ~、修改基本表
3. ~视图
4. ~、修改索引

定义模式和基本表

        CREATE SCHEMA 
        -- <模式名> 
        AUTHORIZATION 
        -- <用户名>;
        -- [<表定义子句> | <视图定义子句> | <授权定义子句>]

        DROP SCHEMA  
        -- <模式名> 
        <CASCADE|RESTRICT>
        
        -- 创建基本表
        Create Table 
        -- <表名>
-- (<列名><数据类型>[<列级完整性约束>]
-- [{,<列名><数据类型>[<列级完整性约束>]}]
-- [{, [<表级完整性约束>]}]);
        DROP TABLE <表名> [<CASCADE|RESTRICT>]

        -- 修改基本表
        ALTER TABLE <表名>
-- [ADD <新列名><数据类型>[<完整性约束>]]
-- [DROP <完整性约束名>]
-- [ALTER/MODIFY <列名><数据类型>]

操作索引

-- 操作索引
Create [Unique][Cluster] Index <索引名> 
On <表名> (<列名>[次序][, <列名>[次序]] …);
-- 例：
Create Unique Index Scno
On SC(S# ASC, C# DESC)

Drop Index <索引名>

Alter Index <旧索引名> Rename to <新索引名>

操作视图

        -- 操作视图
        -- 定义视图
        Create View <视图名>
 --             [(<列名>[,<列名>] …)]
--      As <子查询>
--      [With Check Option]

        -- 例：
        CREATE VIEW CS_Student
        AS
        SELECT S#, SN, SA FROM S
        WHERE SD = ‘CS’ ；

        -- 删除一样用DROP

        -- 视图本质是一个存储的查询，DBMS执行查询视图时，将视图的定义替换回原始的基础表查询的过程为试图消解
        -- 从数据字典中取出视图定义；
        -- 将定义中子查询与用户查询结合，转换成等价的对基本表的查询
        -- 再执行修正的查询
        -- 例：

        -- 创建视图
        CREATE VIEW CS_Student
        AS
        SELECT S#, SN, SA FROM S
        WHERE SD = ‘CS’ ； 

        -- 在视图上查询
        SELECT S#, SA FROM CS_Student
        WHERE SA < 20;

        -- 转换后为
        SELECT S#, SA FROM S
        WHERE SD=‘CS’ And SA <20;

视图可以简化用户操作，使用户能以多种角度看待统一数据，提供逻辑独立性，对数据提供安全保护。

数据更新

插入数据

插入单个元组或者子查询结果。

单个

Insert Into <表名>[(<属性列>[{,<属性列>}])] Values(<值>[{,<值>}]){, ...}

子查询

Insert Into Dept_Age (Sdept, Avgage) Select SD, AVG(SA) From S Group By SD;

修改数据

Update <表名>
Set <列名>=<表达式>[{, <列名>=<表达式>}]
[Where <条件>]

删除数据

Delete From <表名> [Where <条件>]

数据安全性控制

权限有：ALL PRIVILEGES、SELECT、INSERT、UPDATE等

自主存取控制(DAC)

用户对不同数据对象有不同的存取权限，不同用户对同一对象也有不同的权限，且用户可以将权限转授给其他用户。
角色是一组权限的集合，可授予用户或其他角色。当把某个角色授予用户(角色)或从用户(或角色)处收回时，同时授予或收回了该角色代表的全部权限。
用户组是一组有相同特性用户的集合，授权与收回可以此为单位。
根据预先定义的用户权限(用户对数据对象允许执行的操作类型，前者包含了模式、外模式和内模式；以及表和属性列，对于模式操作类型只有建立哦、修改和检索；而对于后者则有检索、插入、删除和修改)进行存取控制。

授权

GRANT <权限>|<角色>[{,...}] [ON <对象类型> <对象名>] TO <用户>|<角色>[{,...}] [With Grant Option](最后的with是用来允许被授权的用户将指定的用户级权限或角色授予其他用户)

撤权

REVOKE <权限>|<角色>[{,...}] [ON <对象类型> <对象名>] FROM <用户>|<角色>[{,...}] [CASCADE|RESTRICT]

举例

-- 将Student表上的所有权限授予全体用户
Grant ALL ON Student TO PUBLIC; 

-- 收回xxx对Student表的全部权限
REVOKE ALL ON Student FROM xxx;

强制存取(MAC)

每个数据对象被标以一定的密级，每个用户也被授予某一级别的许可证，对任意对象，只有有合法许可证的用户可存取。
全部实体分为主客体，前者是系统中的活动实体，既有实际用户，也有代表用户的各进程；后者是系统中的被动实体，受前者控制。
每个实例被指定了一个敏感度标记，分为若干级别：绝密、机密、公开等，主体的称为许可证级别，而客体的为密级。
通过对比主客体的Label，确定主体是否能存区客体，当主体许可证级别大于等于客体密级时，才可读取；当前者小于等于后者时才可写入。

其他方法

视图机制

为不同用户定义不同视图，可将用户对数据的访问权限限制在一定的范围内。

审计

将用户对数据库的所有操作都自动记录下来放入审计日志中。

数据加密

防止数据库中数据在存储和传输中失密。

数据完整性控制

**数据的正确性(合法类型并在有效取值范围内)和相容性(同一事实两个数据应该相同)**。
为了防止数据库中存在不符合语义数据，防止错误信息的输入输出。

完整性约束条件

施加在数据库数据上的语义约束条件，作用对象可以是列、元组、关系三种。

关系模型中的

实体完整性

关系约束：一个关系元组间，主键形式。

参照完整性

关系约束：关系间联系的约束，外键形式

用户自定义完整性

列约束和元组约束，UNIQUE、NOT NULL、CHECK等

动静之分

静态约束

每一确定状态数据对象应满足约束条件，包括了对数据类型、数据格式、取值范围等约束。

动态约束

数据库由一种状态转变为另一种，新旧值间应满足约束条件。

完整性规则

由一个五元组(D(约束数据对象),O(触发完整性检查数据库操作),A(数据对象需满足断言或语义约束),C(选择A作用的数据对象值的谓词),P(违反完整性规则时触发的过程))描述，

CREATE ASSERTION <断言名> <CHECK 子句> 
-- 例
CREATE ASSERTION ASSE-SC-SNUM1
CHECK ( 60>= ALL( SELECT COUNT(*)
FROM SC
GROUP BY C#) );
-- 触发器
-- 用户定义在关系上的一类由事件驱动的特殊过程，
-- 类似约束，但更灵活，可实施更复杂的检查和违约操作，一经定义，自动触发
-- 定义
CREATE TRIGGER <触发器名称>
{BEFORE|AFTER}<触发器事件> ON <表名>
REFERENCING NEW | OLD ROW AS <变量>
FOR EACH {ROW|STATEMENT}
[WHEN <触发条件>]
<触发动作体>

-- 例希望能在员工信息更新时将旧信息保存在历史记录中
CREATE TABLE Employees (
 EmployeeID INT PRIMARY KEY,
 Name VARCHAR(100),
 Position VARCHAR(100),
 Salary DECIMAL(10, 2)
);
CREATE TABLE EmployeeHistory (
 HistoryID INT PRIMARY KEY,
 EmployeeID INT,
 Name VARCHAR(100),
 Position VARCHAR(100),
 Salary DECIMAL(10, 2),
 ChangeDate TIMESTAMP DEFAULT 
CURRENT_TIMESTAMP
);

CREATE TRIGGER UpdateEmployeeHistory
BEFORE UPDATE ON Employees
FOR EACH ROW
BEGIN
 INSERT INTO EmployeeHistory (EmployeeID, Name, 
Position, Salary)
 VALUES (OLD.EmployeeID, OLD.Name, OLD.Position, 
OLD.Salary);
END;

空值运算

空值与另一个值算术运算结果为NULL，比较运算结果为UNKNOWN

常用语句：

SELECT * FROM player WHERE level > 1 AND level < 5;
SELECT * FROM player WHERE level > 1 OR level < 5;
SELECT * FROM player WHERE level NOT IN (1， 3， 5)；
SELECT * FROM player WHERE level BETWEEN 1 AND 10；
-- LIKE是模糊查询，其中%表示匹配任意个字符，而_代表匹配任意一个字符
SELECT * FROM player WHERE name LIKE '王%';
SELECT * FROM player WHERE name LIKE '王_';
-- REGEXP表示正则表达式，语法规则与此前接触的一致。
SELECT * FROM player WHERE name REGEXP '...';
-- 由于在sql中null与任何值都不等，包括null，因此不能使用= null来判断其是否与null相等，应该使用is null来判断,但是对于空字符串，只能使用=来判断
SELECT * FROM player WHERE name is null;
SELECT * FROM player WHERE name = '';   
-- order by 用于排序,默认升序排列，后加上DESC使其降序排列
SELECT * FROM player WHERE ORDER BY level DESC;
-- 直接跟数字表示根据第几列的数据来排序
SELECT * FROM player WHERE ORDER BY 5 DESC;
-- 还可以在此后再多加一列来表示在第一种排序方式的基础上，以第二种方式作为后续的排列方式，例：(其中ASC表示按顺序即先排level后exp，不填写默认ASC)
SELECT * FROM player WHERE ORDER BY level DESC, exp ASC;

-- 还有一些常用的聚合函数:COUNT()、MAX() 、MIN() 、SUM() 、AVG()
SELECT COUNT(*) FROM player;
SELECT AVG(level) FROM player;

-- group by 来分组查询
SELECT sex, count(*) FROM player group by sex; 
-- SELECT 后的level代表查询level列，count(level)表示不同level中的个数，group by后的level表示按level来分组
SELECT level, count(level) FROM player group by level;
-- HAVING：筛选分组后数据,只能和GROUP BY一起使用 
-- GROUP BY (HAVING ...)是对于分组内容进行的筛选，主要是针对列，用于去掉不符合条件的若干分组；而WHERE则是对于行进行的筛选，主要是针对行。
SELECT level, count(level) FROM player group by level having count(level) > 4;
SELECT level, count(level) FROM player group by level order by count(level) DEC;
-- LIMIT可以限制显示的数量
-- 例如下语句只会返回前三名
LIMIT 3;
-- DISTINCT可用来去重
SELECT DISTINCT sex from player;

-- UNION来合并查询结果集(并集)
-- 例如下语句将会把上下语句的结果合并起来，UNION默认去重，若不想去重，可使用UNION ALL
SELECT * FROM player WHERE level BETWEEN 1 AND 3;
UNION;
SELECT * FROM player WHERE exp BETWEEN 1 AND 3;
-- INTERSECT 用来查找两个结果的交集
-- 用法与UNION类似
-- EXCEPT 用于查找两个结果的差集
-- 这三个操作的对象必须是相容的，同类关系，即必须有相同数量的属性列且相应属性列的域也相同

-- 子查询的使用
-- where中包含另一个查询块，称为子查询或嵌套查询。
-- 外部查询利用子查询获取检索条件条件值，检索条件根据子查询结果确定外部查询的结果数据。    
-- 若子查询返回单值可以直接使用比较运算符等连接子查询；反之若是一组值，则需要使用ANY或者ALL插入到比较符号与表达式之间
-- 或者也可以使用IN或NOT IN来进行与一组值关系的判断，前者等价于`=ANY`，而后者等价于`!=ALL`.
SELECT AVG(level) from player;
select * from player where level > (SELECT AVG(level) from player);
select level, ROUND(SELECT AVG(level) from player) as average, level - ROUND(SELECT AVG(level) from player) as diff from player;  

-- 子查询返回的是一组值，那么要判断查询值与子查询返回值之间的关系，
-- 需要使用ANY或者ALL插入到比较符号与表达式之间
SELECT SN FROM S WHERE SA = ANY(SELECT SA FROM SC WHERE C# = 'C1');
-- 使用子查询创建新表
create table new_player select * from player where level < 5;
insert into `new_player` select * from player where level between 6 and 10;
-- EXISTS判断一个查询是否有结果,只返回0或1，NOT EXISTS同理
select exists (select * from player where level > 10);

-- 基于派生表的查询     
-- as关键字可省略，但必须有派生表表名，若子查询中无函数体，派生表可以不指定列。    

-- 表连接
-- 内连接，返回两个表中都有的数据
INNER JOIN
-- 左外连接，返回左表所有数据和右表中匹配数据,右表中没有的数据用NULL填充
LEFT JOIN;

SELECT * FROM S 
LEFT JOIN SC ON S.A = SC.A;

-- 右外连接，同理
RIGHT JOIN;
-- 全外连接，左右两个表中没有匹配数据都用NULL填充
FULL OUTER JOIN;
-- 外连接可以包括上述三种，OUTER可省略


-- 交叉连接，作笛卡儿积
CROSS JOIN;


-- 自然连接，自动将两张表中列名相同的连接在一起，
-- 不常用，因为假若其中某几列的数据并不是我们想要关联相等的情况下，
-- 该连接仍会纳入考虑，导致出现结果缺少
NATURAL JOIN;

-- 例如:下表示将玩家同对应装备通过对应的玩家id关联起来
select * from player
inner join equip
on player.id = equip.player_id

-- 索引的创建,INDEX前可加上索引类型，UNIQUE表示唯一索引，FULLTEXT表示全文索引，SPATIAL表示空间索引，index_name为索引名称
-- UNIQUE强制列值唯一性，适用所有标量类型(INT、VARCHAR等)、允许出现NULL，常用于查询身份证号、用户名等唯一值
-- FULLTEXT是进行文本内容的语义搜索，适用于CHAR、VARCHAR、TEXT，允许空值和重复值，适用于文章内容搜索、商品描述搜索
-- SPATIAL是地理空间数据检索，适用于GEOMETRY、POINT、POLYGON等，允许重复值，适用于地图应用、位置服务
-- on tbl_name 表示要在tbl_name这张表上创建索引，(index_col_name, ...)是用一个或多个名称括起来的字段名，是我们对哪些字段创建索引，一般对于表的主键字段或常用于查询的字段创建索引，即where语句后的条件字段，来提高查询的效率。
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
on tbl_name (index_col_name, ...);

-- 查看索引
show index from tbl_name;  

-- 删除索引
drop index index_name on tbl_name; 

-- 可在修改表结构时创建索引
alter table tbl_name add index on index_name (name);  

-- 视图作为一种虚拟存在的表，本身不包含数据，而作为一个查询语句保存在数据字典中，查询视图时会根据查询语句的定义动态生成数据
-- 例如：下表示查询player表中玩家level在前三名的数据
-- 视图的使用方式与表类似，也可使用select等语句来操作
create view view_name 
as 
select * from player order by level desc limit 3;
-- 视图中的数据是动态的，会随着原表中数据的改变而改变

理论

数据模型

用于描述数据、数据之间的关系、数据的语义以及数据的约束等。
由数据结构、数据操作、数据的完备性约束组成。

概念模型

实体：现实世界中可区分的事物，如学生、课程等。
属性：实体所具有的某种特性，如学生的学号、姓名等。
码：能够唯一标识实体的属性或属性组，如学生的学号，在图中用下划线去区别表示。(主键)
实体集：同类实体的集合，如所有学生的集合。
联系：实体之间或内部的联系，如学生选课。
实体-联系模型：用实体、属性和联系来描述现实世界的一种数据模型。(ER模型)

ER模型

实体内部联系指组成实体各属性联系，实体间联系指不同实体型的实体集之间的联系。
实体用矩形表示，属性用椭圆表示，联系用菱形表示。
参与联系的实体型数目称为联系的度，常见的有一对一、一对多和多对多。

ISA联系(继承)

由三角形表示，表示一种特殊的实体与一般实体之间的联系。
不相交约束与可重叠约束，前者指一个父类中实体最多属于一个子类实体集，用一个X表示，后者指一个父类中实体可以属于多个子类实体集，不用X表示。
完备性约束描述父类中一个实体是否为某一子类的实体，是为完全特化用双线连接表示，否为部分特化用单线连接表示。

基数约束

描述实体集参与联系的数量约束，常用(最小值,最大值)表示。

part-of联系

聚合和组合的表示，不过这里是非独占联系和独占联系。
前者可以使用基数约束来表示。而后者中对于依赖其他实体型存在的实体型是弱实体型，用双矩形表示，相应的识别联系用双菱形表示。

逻辑模型和物理模型

层次模型

“树”结构，是一个有向树，优缺点同树。

网状模型

“图”结构，允许多对多关系，允许一个以上结点无双亲，可以有多个双亲，优缺点同图。

关系模型

优点：简单、灵活、易于理解和使用、与应用程序独立。
缺点：对复杂应用支持不够好、对大规模数据处理效率不高。

三级模式结构

模式：数据库的逻辑结构和特征的描述，所有用户的公共数据视图。
外模式：子模式或用户模式，描述用户可见和使用的局部数据逻辑结构和特征的描述。
内模式：数据库的物理存储结构，描述数据在存储介质上的实际存储方式。例如：记录存储方式、索引方式等。

两级映像与数据独立性

外模式/模式映像

粗略的比喻，模式可以理解成多个表组成的逻辑结构，而外模式就可以理解成对于不同业务来说用到的模式中的一部分，子集的关系。

模式/内模式映像

同样的比喻，模式可以理解成一个班级的课程表，而内模式可以理解成课程表的具体实现，如课程的存储方式、索引等，映像则是将课程表的逻辑结构与物理存储结构进行对应起来。

关系的数学定义

域是一组有相同数据类型的值的集合。
笛卡尔积是是域上的一种集合运算，将n个域中有序形成n元组的集合，记为D1×D2×…×Dn。
关系是笛卡尔积的子集。
关系表每行对应一个元组，每列对应一个域，每个域对应一个属性。
若关系中某一属性组的值能唯一标识一个元组，而其子集不能，则该属性组为候选码。

关系代数

关系的并、交、差和笛卡尔积(将两个关系间的每一行有序拼接成新的行形成新的表，相关的属性应该表明是哪种关系下的(例如：S.A，S.B，…))
此外还有较为高级的运算方式：选择、投影、连接和除运算等。
选择：从某个关系表中select出满足某些条件的数据，使用σF(R)来表示。
投影：从关系中选择出若干属性列组成新的关系，需要删除取消其中完全相同的行。
连接：从两个关系的笛卡尔积中选取属性间满足一定条件的元组，较为常用的是等值连接和自然连接，前者是寻找笛卡尔积中两个关系中相同属性的值相同的元组；而后者则是在前者的基础上取消$$掉重复列后的结果，即做一个新的投影,有外连接、左外连接和右外连接。
除运算：定义关系R除以关系S的结果为关系T，则T包含了所有在R但不在S中的属性及其值，且T与S的笛卡尔积，即两者所有组合都存在于R中，一般适用于题目中出现全部或者所有的字眼的问题中。

关系演算

元组关系演算语言ALPHA

以元组变量作为谓词变元的基本对象，主要有GET、PUT、HOLD、UPDATE、DELETE、DROP6条。
基本格式：操作语句 工作空间名 (number) (表达式): 操作条件，其中工作空间名：工作空间是内存中用户与数据库之间的数据通讯区，一般用字母W表示。表达式用于指定语句的操作对象，可是关系名或(和)属性名，可以同时操作多个关系或属性。操作条件是一个逻辑表达式，用于将操作结果限定在满足条件的元组中，可为空。此外，还可以在基本格式基础上加上排序要求(DOWN ATTNAME(属性名))并指定返回元组的条数number。还可以使用RANGE来为关系设置一个元组变量(1.简化关系名；2.操作条件中使用量词(全称和存在)时必须用元组变量)，此外还可以使用诸如COUNT、TOTAL、MAX、MIN和AVG这类的聚集函数。

1. 检索操作，使用GET语句：

   RANGE Course CX
           SC SCX
           SC SCY
   GET W (Student.Sno): ∀ CX(∃ SCX (SCX.Sno ='202'∩ SCX.Cno = CX.Cno)) => ...

2. 更新操作：
   1. 修改操作(UPDATE)：1.先用HOLD语句将要修改元组从数据库中读到工作空间中(修改数据而读元组时必须使用HOLD语句，其是带上并发控制的GET语句)；2.然后用宿主语言修改相应元组的属性值(如:MOVE)；3.最后用UPDATE将修改后元组送回数据库中。
   2. 插入操作(PUT)：1.先用宿主语言在空间中建立新元组。2.然后用put(只对一个关系操作，即表达式只能是单个关系名)将该元组存入指定关系中。
   3. 删除操作(DELETE):1.用HOLD语句将要删除元组从库中读到工作空间中；2.用DELETE语句删除该元组。

        SELECT * FROM S WHERE A = 10;
        SELECT A, B FROM S;
        SELECT * FROM S NATURAL JOIN T;
        SELECT * FROM S INNER JOIN T ON S.C = T.C;
        SELECT * FROM S INNER JOIN T ON S.A < T.E;
        SELECT * FROM T CROSS JOIN (SELECT C, D FROM S) AS subS;


        CREATE TABLE `try_db23371355`.`S` (
	`SNO` VARCHAR(32) NOT NULL COMMENT '供应商代码',
	`SNAME` VARCHAR(32) NOT NULL COMMENT '供应商姓名',
	`STATUS` TINYINT UNSIGNED NOT NULL COMMENT '供应商状态',
	`CITY` VARCHAR(32) NOT NULL COMMENT '供应商所在城市',
	PRIMARY KEY (`SNO`)
)	ENGINE = InnoDB
	DEFAULT CHARACTER SET = utf8mb4
	COLLATE = utf8mb4_general_ci
	COMMENT = '供应商表';


        INSERT INTO s (SNO, SNAME, STAT, CITY ) VALUES ("S1", "精益", 20, "天津") , ("S2", "盛锡", 10, "北京"), ("S3", "东方红", 30, "北京"), ("S4", "丰泰盛", 20, "天津"), ("S5", "为民", 30, "上海");

        CREATE TABLE `try_db23371355`.`P` (
	`PNO` VARCHAR(32) NOT NULL COMMENT '零件代码',
	`PNAME` VARCHAR(32) NOT NULL COMMENT '零件名',
	`COLOR` CHAR(4) NOT NULL COMMENT '颜色',
	`WEIGHT` TINYINT UNSIGNED NOT NULL COMMENT '重量',
	PRIMARY KEY (`PNO`)
)	ENGINE = InnoDB
	DEFAULT CHARACTER SET = utf8mb4
	COLLATE = utf8mb4_general_ci
	COMMENT = '工程项目表';

        CREATE TABLE `try_db23371355`.`J` (
	`JNO` VARCHAR(32) NOT NULL COMMENT '工程项目代码',
	`JNAME` VARCHAR(32) NOT NULL COMMENT '工程项目名',
	`CITY` VARCHAR(32) NOT NULL COMMENT '工程项目所在城市',
	PRIMARY KEY (`JNO`)
)	ENGINE = InnoDB
	DEFAULT CHARACTER SET = utf8mb4
	COLLATE = utf8mb4_general_ci
	COMMENT = '工程项目表';

        CREATE TABLE `try_db23371355`.`SPJ` (
	`SNO` VARCHAR(32) NOT NULL COMMENT '供应商代码',
	`PNO` VARCHAR(32) NOT NULL COMMENT '零件代码',
	`JNO` VARCHAR(32) NOT NULL COMMENT '工程项目代码',
	`QTY` SMALLINT UNSIGNED NOT NULL COMMENT '供应数量'
)	ENGINE = InnoDB
	DEFAULT CHARACTER SET = utf8mb4
	COLLATE = utf8mb4_general_ci
	COMMENT = '供应情况表';


        INSERT INTO p (PNO, PNAME, COLOR, WEIGHT ) VALUES ("p1", "螺母", "红", 12), ("P2", "螺栓", "绿", 17), ("P3", "螺丝刀", "蓝", 14), 
("P4", "螺丝刀", "红", 14), ("P5", "凸轮", "蓝", 40), ("P6", "齿轮", "红", 30);

INSERT INTO j (JNO, JNAME, CITY ) VALUES ("J1", "三建", "北京"), ("J2", "一汽", "长春"), ("J3", "弹簧厂", "天津"), 
("J4", "造船厂", "天津"), ("J5", "机车厂", "唐山"), ("J6", "无线电厂", "常州"), ("J7", "半导体厂", "南京");

INSERT INTO spj (SNO, PNO, JNO, QTY ) VALUES ("S1", "P1", "J1", 200), ("S1", "P1", "J3", 100), ("S1", "P1", "J4", 700), ("S1", "P2", "J2", 100), 
("S2", "P3", "J1", 400), ("S2", "P3", "J2", 200), ("S2", "P3", "J4", 500), ("S2", "P3", "J5", 400), ("S2", "P5", "J1", 400), ("S2", "P5", "J2", 100), 
("S3", "P1", "J1", 200), ("S3", "P3", "J1", 200), 
("S4", "P5", "J1", 100), ("S4", "P6", "J3", 300), ("S4", "P6", "J4", 200), 
("S5", "P2", "J4", 100), ("S5", "P3", "J1", 200), ("S5", "P6", "J2", 200), ("S5", "P6", "J4", 500);

SELECT DISTINCT SNO FROM spj WHERE JNO = "J1";

SELECT DISTINCT  SNO FROM spj WHERE JNO = "J1" AND PNO = "P1";

SELECT DISTINCT  SNO FROM spj WHERE JNO = "J1" AND PNO IN (SELECT PNO FROM p WHERE COLOR = "红");


SELECT DISTINCT SNAME, CITY FROM s;

SELECT DISTINCT PNAME, COLOR, WEIGHT FROM p;

SELECT DISTINCT JNO FROM spj WHERE SNO = "S1";

SELECT p.PNAME, SUM(spj.QTY) FROM p, spj WHERE spj.PNO = p.PNO GROUP BY spj.JNO, spj.PNO HAVING spj.JNO = "J2";

SELECT DISTINCT  spj.PNO FROM spj, s WHERE s.CITY = "上海" AND s.SNO = spj.SNO;

SELECT DISTINCT j.JNAME FROM j, s, spj WHERE s.CITY = "上海" AND spj.SNO = s.SNO AND spj.JNO = j.JNO;

SELECT DISTINCT j.JNO FROM j WHERE j.JNO NOT IN (SELECT DISTINCT spj.JNO FROM s, spj WHERE s.CITY = "天津" AND s.SNO  = spj.SNO);


-- 4.8 把全部红色零件的颜色改成蓝色; 
UPDATE p SET COLOR = "蓝" WHERE COLOR = "红";

-- 4.9 由 S5 供给 J4 的零件 P6 改为由 S3 供应，请作必要的修改; 
UPDATE spj SET SNO  = "S3" WHERE SNO = "S5" AND PNO = "P6" AND JNO = "J4";

-- 4.10 从供应商关系中删除 S2 的记录，并从供应情况关系中删除相应的记录; 
DELETE FROM s WHERE s.SNO = "S2";
DELETE FROM spj WHERE spj.SNO = "S2";


-- 4.11 请将(S2.J6.P4,200)插入供应情况关系。
INSERT INTO spj (SNO, JNO, PNO, QTY) VALUES ("S2", "J6", "P4", 200);


CREATE VIEW SANJIAN_VIEW AS SELECT spj.SNO, spj.PNO, spj.QTY FROM spj, j WHERE j.JNAME = "三建" AND j.JNO = spj.JNO;

SELECT * FROM SANJIAN_VIEW;

-- 6.1 找出三建工程项目使用的各种零件代码及其数量
SELECT PNO, SUM(QTY) FROM SANJIAN_VIEW GROUP BY PNO ;

-- 6.2 找出供应商 S1 的供应情况。
SELECT * FROM SANJIAN_VIEW WHERE SNO = "S1";

数据依赖

一个关系内属性值间相互依赖又相互制约的关系。

函数依赖

R为属性集U上关系模式，X、Y为U子集，r为R的任意一个具体关系，t、s为r中任意两个元组，若有t[X]=s[X],则t[Y]=s[Y]，则称X函数确定Y或Y函数依赖于X，记：X->Y：即对X的每个具体值，Y均有唯一值与之对应，X为决定因素。
对X->Y，若Y包含于X，则称X->Y为平凡的函数依赖，反之不平凡。
函数依赖是语义范畴概念，且不随时间改变。
对于两个属性之间不同联系与函数依赖关系：

• 一一对应，则两者互为函数依赖。
• 一对多对应，则只有多的确定1的
• 多多对应，则不存在函数依赖

在R中，若X->Y且对任意X的真子集X’有X’不确定Y，则称Y对X完全依赖，记X-F->Y，反之，Y对X部分依赖,记X-P->Y，显然属性集完全依赖于候选码，部分依赖于超码。
若X->Y(Y不包含于X)，Y不确定X，且Y确定Z(Z不包含与Y)，则Z对X传递函数依赖，X-T->Z，

Armstrong公理系统

若在R(U,F)中，X,Y为R属性集合，若从F(U上的一组函数依赖)可以推出X->Y，则称F逻辑蕴涵X->Y

1. A1自反律：若Y包含于X,X包含于U，则X->Y为F所蕴含，
2. A2增广律：若X->Y为F所蕴含，且Z包含于U则XZ->YZ为F所蕴含。
3. A3传递律：若X->Y,Y->Z为F所蕴含，则X->Z为F所蕴含。
   由上述公理系统可以得到三条推理规则：

• 合并规则：由X->Y, X->Z，有X->YZ
• 伪传递规则:由X->Y,WY->Z，有WX->Z
• 分解规则：由X->Y,Z包含于Y,有X->Z
  有效性：自查ppt
  完备性：自查ppt

闭包

函数依赖集F的闭包：关系模式R<U,F>中为F所逻辑蕴含的所有函数依赖为F的闭包:F+
属性集X关于函数依赖集F的闭包为能由F根据公理系统导出的函数依赖中由X所确定的所有属性集合。

Input：𝑋, 𝐹
Output：XF
+
初始化𝑋𝐹
+ = 𝑋
DO
FOR any 𝐴 ⊆ 𝑋𝐹
+ DO
IF 在𝐹中存在函数依赖 𝐴 → 𝐵
THEN 𝑋𝐹
+ = 𝑋𝐹
+ ∪ 𝐵
WHILE (𝑋𝐹
+发生变化且 𝑋𝐹
+ ≠ 𝑈）

凡不能用公理推出的函数依赖都不被F逻辑蕴含，即凡是F逻辑蕴含的函数依赖都能用公理系统从F导出
函数依赖集F,G若两者的闭包相等，则这两者等价；若两者等价则这两者相互覆盖。

最小依赖集

若函数依赖集F有如下条件，则为极小函数依赖集或称为最小依赖集或最小覆盖：

• F中任一函数依赖X->A，A为单属性(右部单属性化)
• F中不存在函数依赖使得F与F减去该依赖后等价，没有多余的函数依赖
• F中的每个函数依赖的左部都没有多余的属性，即不存在F中的函数依赖X->A有在X中有真子集Z使得F与F-{X->A,Z->A}等价。
  函数依赖集的极小化处理，即通过处理使每个函数依赖集等价于一个最小函数依赖集Fm
  相应算法：
• 逐个检查各个函数依赖X->Y,若Y=A1A2A3…Ak，则用X->Ai(i=1..k)k个函数依赖代替
• 逐个检查各函数依赖X->A，设X=B1B2B3…Bk,检查Bi，若A∈(X-Bi)F+，则用X-Bi取代X，直到F不发生改变
• 逐个检查各函数依赖X->A，令G=F-{X->A}，若A∈XG+，则从F中去掉该函数依赖，直到F不再改变。

多值依赖

范式

若一个关系满足某个指定的约束集，则称其属于某种特定的范式，相应的约束条件为范式

1NF

最低要求，一个关系只包含原子值(即不能有”表中表”)这一约束时，即满足，满足这个的关系为规范化关系
规范化：一个低一级范式关系模式通过模式分解转换为若干高级范式关系模式集合。

2NF

满足1NF，且每个非主属性完全依赖于码，则称其满足2NF。
若关系R全体属性都是R主属性，那么其也满足；2NF中允许主属性部分函数依赖于码；从1NF中消除非主属性对码的部分函数依赖可获得2NF。
对于1NF提升至2NF，可以采用投影分解法，消除UN中非主属性对码的部分函数依赖：
仍有不足

3NF

在关系模式满足1NF范式的R<U,F>中，若不存在这样的码X，属性组Y以及非主属性Z(Y不属于Z)，使得X->Y,Y->Z成立，且X不依赖于Y，则称满足3NF。
3NF规范化：在满足2NF的基础上，使得每个非主属性都不传递依赖于R的任何码，都是直接依赖的。
同样采用投影分解的方法
不过并没有限制主属性对码的部分与传递函数依赖，若发生上述依赖，仍有可能存在插入、删除、修改的异常。

BCNF

若关系模式满足1NF，若对R的每个函数依赖X->Y且Y不包含于X，X必含有码，则其满足BCNF
概念详见ppt
规范化：

模式分解

定义见ppt

分解的无损连接性

若对R中任何一个关系r有r=mp(r)，则称分解p为无损分解，mp(r)详见ppt
相应的判定算法详见ppt
无损连接性的充要条件分解中任意两个关系模式的共同属性至少构成R1、R2二者之一的候选码。

分解的保持函数依赖性

定义和判定方法见ppt，也就是R中每个函数依赖都能从分解的不同关系模式的函数依赖并集中逻辑导出。

遵循原则

投影分解或其他方法进行规范化时，应具有无损连接性和保持函数依赖

关系模式的分解算法(详见ppt)

达到3NF保持函数依赖

达到3NF且保持无损连接与函数依赖

达到BCNF无损连接

候选码的求解理论和算法

快速求解候选码充分条件

对关系R属性可分为L(仅出现在函数依赖集左部的)、R(…)、N(两边都未出现过)和LR(…)

定理1

对R，若X为L类，则其必为任一候选码成员；若且X在F上的闭包为U，则其必为R的唯一候选码。

定理2

R类属性不在R任何候选码中

定理3

N类属性必包含在R的任一候选码中；若X为L和N类组成属性集，其该属性集在F上的闭包为U，则X为R唯一候选码。

可以通过图论的方式获得

易于理解的，将属性当作节点，F的函数依赖代表边。
参考ppt中的图论术语。

定理4

R的函数依赖图中原始点(L)和孤立点(N)对应属性在R任何候选码中，终结点(R)不在。

定理5

属性集X为R的唯一候选码的充要条件是其能达到G中任意节点。单属性情况下，R有唯一候选码的充要条件是G中不存在独立的回路。

定理6

Y为途中点(LR)，则Y必在某个候选码中的充要条件是Y为某一独立回路中的节点。

定理7

详见ppt

单属性依赖集和多属性依赖集图论判定方法(详见ppt)

数据库设计

概念结构设计

使用E-R图来描述现实世界的信息，其由实体、联系和属性三者组成。
实体之间可以存在以下的语义扩充：1.存在依赖，即某一个实体依赖另一实体的存在；2.标识依赖，实体不能由自己的属性唯一标识，必须通过与其相联系的另一实体一起来标识，则称该实体标识依赖于另一实体；3.子类关系，子类可以继承父类属性，同时也可以附加属性。
设计方式一般有四种：1.自顶向下；2.自底向上；3.逐步扩张；4.混合策略。
其中最常用的就是自底向上方法：1.自顶向下进行需求分析；2.自底向上设计概念结构，分为局部E-R图设计和综合局部E-R图形成总E-R图两个阶段。

局部E-R图设计

• 选择局部应用，以需求分析中得到的数据元素表为基础，利用数据抽象机制，建立实体模型，关键在于确定实体及其属性，建立方法如下：
  • 先对数据字典进行初步抽象，得到实体和属性，再按原则进行必要调整，数据抽象机制有分类：定义某一概念作为现实世界中一组对象的类型、聚集：定义某一类型的组成成分和概括：定义类型间的一种子集联系；调整原则：1.属性不能再有需要描述的性质，即不能再是另一些属性的聚集。2.属性不能与其他实体有联系。3.实体和描述其属性之间保持1:1或者n:1的联系，此外的联系需要进行调整，一般可将其上升至实体 。
• 确定实体间联系类型，用E-R图表示实体间联系，形成分E-R图。

综合分E-R图形成总E-R图

1. 多个分E-R图一次集成
2. 逐步集成，累加方式一次集成两个分E-R图。
   集成方式：1.合并：消除冲突(属性(属性类型、取值范围或取值集合不同导致)、命名(属性名、实体名和联系名间同名异义，异名同义)和结构(同一对象不同应用中有不同抽象，同一实体不同分E-R图中属性个数、次序不同，实体间联系在不同分E-R图中呈现不同类型)冲突)，生成初步E-R图。2.修改和重构：消除不必要的冗余，生成基本E-R图。
   修改初步E-R图设计基本E-R图：1.消除不必要的冗余，设计基本E-R图，由于初步图中可能存在**冗余的数据(可由基本数据导出的数据)和联系(可由其他联系导出的联系)**，可以使用分析法或者规范化方法来消除，前者通过分析两个实体之间是否有冗余的联系(可通过传递得到的联系与直接的联系即为潜在的冗余)；后者将图中所有实体用符号表示，将联系用依赖的方式表示，利用函数依赖集的最小覆盖算法进行极小化处理，并最终考虑每个函数依赖是否存在冗余联系，去掉后即可形成基本E-R图。

逻辑结构设计

E-R图转换为关系模型：
一个实体型转换为一个关系模式，实体的属性和码就是关系的。
一个联系->一个关系模式，1:1则联系的码为关系的码；1:n则关系的码是n端实体的码;n:m，关系的码是诸实体码的组合。
三个及以上实体间的多元联系，转换为一个关系模式：该多元联系相连的各实体的码以及联系的属性转换为关系的属性，关系的码为各实体码的组合。有相同码的关系可以合并。
弱实体类型的转换：每个弱实体类型，创建一个新的包含所有弱实体类型的属性的关系，将标识关系(被依赖关系)的主码添加到新关系中作为外码，新关系的主码为标识关系的主码和弱实体类型的部分标识的组合。

关系模型的规范化与优化

规范化

按照函数依赖的理论，逐一分析转换得到关系模式，判断是否存在部分、传递函数依赖和多值依赖等，确定范式。

优化

为提高存取效率和存储空间利用率，对关系模式进行必要分解：

• 水平分解：将关系元组分为若干子集和，定义子关系，提高系统效率，80/20原则和数据分片。
• 垂直分解：将关系模式R属性分解为若干子集合，形成若干子关系模式，原则是常在一起使用的属性从R中分解出来形成一个子关系模式，必须确保无损连接性合保持函数依赖。

物理结构设计

数据在物理设备上的存储结构与方法，尽可能平衡存区效率和空间

定存放位置

常存取部分与和存取频率较低部分分开存取
常使用索引法或聚集法

索引法

B+树索引

聚集法

关系中某个属性/组(聚集键)值相同记录集中存放在连续物理块中。

HASH法

使用HASH函数将记录关键字转换成地址

定系统配置

MongoDB

基于分布式文件存储的开源数据库。
文档型数据库，NoSQL，数据以文档存储，类似MySQL中的行，数据库由一个集合代表，含有多个文档，使用类似json的格式，或者称为bson(binary json).