数据库概述

数据库基本特点：数据结构化，数据独立性，数据冗余小，易扩充，统一管理和控制，永久存储，有组织，可共享

三级模式

• 模式：一个数据库只有一个模式，是对数据逻辑结构的定义（属性名，类型，取值范围）

• 外模式：是模式的一部份，不同用户看到不同的外模式

• 内模式：靠近数据库的底层，对数据库存储的描述

两级映射

• 外模式/模式映射：当模式改变时，外模式不需要改变，逻辑的独立性

• 内模式/模式映射：当内模式改变时，模式不用改变，物理的独立性

视图属于外模式（编译好的SQL语句），表属于内模式

关系数据库

关系模式

关系也是一个二维表，表的每行对应于关系一个元组，表的每列与一个域对应，表列的值集为对应域的子集

关系模式的形式化表示：R(U, D, DOM, F)，U属性集，D域集，DOM属性与域之间来源关系集，F属性间的依赖关系

关系模式定义至少应包括：模式名、属性名、值域名和完整性约束，其实表头那一行就是关系模式

关系模式是一个类型，任何属性不可再分

不同的咧也可以属于同一个域

行列的顺序都无所谓

码

关系中不同的列可以对应相同的域，为了区分取不同的名字即属性

候选码：可以唯一标识一个元组，可以一个，也可以多个；候选码都是主属性，主属性不一定是候选码

主码：从候选码中筛选出一个，也叫主键

外键：外部关系中的主码

全码：所有属性构成的主码

关系代数的运算

运算对象：关系 -> 运算结果：关系

合并U 、相交∩ 、求差-（相减）、笛卡尔积×、选择σ 、投影π （选择某些列组成新的关系）、联接（取满足关系的拼接，自然连接（重复字段只保留一个），等值连接，左外连接（左无null），右外连接（右无null）） 、求商÷

关系代数表达式：用F表示每个条件，T指代每个表，getfield就是要获取的字段

$$\prod\ _{get field} (\delta _{F_{1} \wedge F_{2} \wedge ... \wedge F_{n}} (T_{1} \times T_{2} \times ... \times T_{n}))$$

数据库系统

数据库完整性

实体完整性：基本关系R的主属性不能取空值

参照完整性：定义主--外码之间的引用规则，不引用不存在的实体，外键是否为空要根据情况而定

用户定义完整性：针对某一具体应用的数据必须满足的语义要求

数据库范式

X,Y是关系R的两个属性集合，X’是X的真子集，存在X→Y，但对每一个X’都有X’!→Y，则称Y完全函数依赖X

X,Y是关系R的两个属性集合，存在X→Y，若X’是X的真子集，存在X’→Y，则称Y部分函数依赖X

X,Y,Z是关系R中互不相同的属性集合，存在X→Y(Y !→X),Y→Z，则称Z传递函数依赖X

• 1NF：列不能再分

• 2NF：消除部分依赖

• 3NF：消除传递依赖

• BCNF：非主属性对每一个码都是完全函数依赖；主属性对每一个不包含它的码,也是完全函数依赖；没有任何属性完全函数依赖于非码的任何一组属性

DBMS设计

七个阶段：规划 -> 需求分析（数据流图和数据字典） -> 概念设计（E-R图） -> 逻辑设计（关系模式） -> 物理设计 -> 数据库实施 -> 运维

E-R图：方框表示实体，属性使用圆圈，联系用菱形

通过把实体和联系都转成表就是关系模型（1:1和1:N的关系一般和实体集转换的关系合并，M：N单独出一个关系）

数据库设计&系统分析人员，自始至终参与，决定平台质量的核心成员

子系统

原子性：事务是一组不可分割的操作单元，这组单元要么同时成功要么同时失败；DBMS的事务管理子系统

一致性：事务前后的数据完整性要保持一致；DBMS的完整性子系统

隔离性：多个用户的事务之间不要相互影响，要相互隔离；DBMS的并发控制子系统

持久性：一个事务一旦提交，那么它对数据库产生的影响就是永久的不可逆的，如果后面再回滚或者出异常，都不会影响已提交的事务；DBMS的恢复管理子系统

数据库高级能力

索引

表的主键索引必须是唯一索引，平衡B+树适合做为唯一索引

表上建立索引会影响数据插入和修改的效率，建立索引不一定会提高查询效率

聚簇索引：一个表只能有一个，将数据存储与索引放到了一块，找到索引也就找到了数据；表中记录的顺序和物理顺序一致，适合按照列值范围查询的操作

非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，key buffer把索引先缓存到内存中，当需要访问数据时），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，索引不在key buffer命中时，速度慢的原因

查询优化

查询树的优化：提前根据条件从表选出字段

硬件：增加内存，增加CPU核心，不设置数据库自增；软件：索引，优化语句，不使用游标

存储过程和触发器

都是SQL语句集

触发器：一种的特殊的存储过程，(INSERT, UPDATE, DELETE)*(BEFORE,AFTER)，总共六种组合

存储过程：编译执行，创建好的会存储在数据库服务中

存储过程中只能定义一个触发器

数据转储

事务开始BEGIN TRANSATION提交事务COMMIT

故障：事务故障，系统故障（日志检查点前面的都保存，后面的已提交的redo,没有提交的undo），介质故障

动态（转储和用户事物并发）静态（转储前没有不一致，转储中不能操作）

日志：先写日志再写数据库

数据库并发

读脏数据：在一个事务读取了另一个事务修改但还未提交的数据后，如果这个事务被回滚，那么之前读取的数据就会变得无效

不可重复读：需要读两次，前后间隔时间大，期间数据被修改，回来读时候发现不一致

数据幻读：需要两次统计数据总量，第一次到第二次统计期间发生了提交，前后统计数据不一致

数据库隔离的级别：

• 读未提交（造成脏读，不可重复读，幻读）

• 读已提交（不可重复读，大多数数据库默认级别）

• 可重复读（读操作不允许update，但可以add，幻读）

• 可串行化（所有事物顺序执行）

x锁：独占锁，写之前必须申请；S锁：共享锁；读之前要申请（二级：读完释放，三级：操作完释放）

两段锁协议：必须先申请完所有，再解锁，不能解锁后又申请；可串行的设计，遵循两段锁协议，调度是串行化的，不遵循，也可以是串行化的

数据库产生死锁的原因：

• 事务之间对资源访问顺序的交替：程序BUG

• 并发修改同一个记录：使用乐观锁和悲观锁

• 事务封锁范围大且相互等待

• 索引不当导致全表扫描：全表扫描的语句做索引优化

SQL语句

SQL是一种结构化的语言，用于描述要做什么而不考虑怎么做

数据库权限

#授权A更新TB的G属性
GRANT UPDATE(G) ON TB TO A
#给予全部权限，授权不能循环授权，可以重复授权
GRANT ALL PRIVILIGES ON TABLE <TABLE_NAME> TO <USER_NAME>
#取消授权
REVOKE <PRIVILIGE> ON TABLE ... TO ...
#使用角色
CREATE ROLE <ROLE_NAME>
GRANT ... TO <ROLE_NAME>
GRANT <ROLE_NAME> TO <USER_NAME>/PUBLIC
#审计功能，记录SC表的修改
AUDIT ALTER, UPDATE ON SC

建表

CREATE TABLE + 表名 + （字段 + 类型）

PRIMARY KEY 主键

FOREIGN KEY（字段）REFERENCES 表（字段）

CHECK（条件）

CREATE TABLE RESULT(
  PNO VARCHAR,
  GNO VARCHAR,
  RANK INT,
  BONUS NUMBER(10,5),
  PRIMARY KEY(PNO,GON),
  FOREIGN KEY(PNO) REFERENCES PLAYER(PNO),
  FOREIGN KEY(GNO) REFERENCES GAME(GNO),
  CHECK((RANK IS NULL)OR(RANK > 0)),
  CHECK(BONUS>=1)
);

视图

CREATE VIEW BORROW_VIEW(CATEGORY,BORROW_NUM) AS
SELECT B.CATEGORY,BR.COUNT(DATE_BORROW) FROM BOOK B BORROW BR
WHERE B.BOOK_ID == BR.BOOK_ID AND B.CATEGORY='' AND TO_CHAR(BR.DATE_BORROW,'YYYY')=''
GROUP BY CATEGORY;  

查询

SELSCT + 字段 + FROM + 表名 + WHERE + 条件

求和 SUM 求平均 AVG 计算数量 COUNT 不在其中 NO IN 拥有条件 HAVING

SELECT P.PNAME FROM PLAYER P
WHERE P.PNO NOT IN (
  SELECT R.PNO FROM GAME G, RESULT R
  WHERE G.GNO=R.GNO AND G.GAME=''
) AND P.SEX='';

SELSECT P.NAME, Q.BONUS FROM PLAYER P (
  SELECT PNO, SUM(BONUS) AS BONUS FROM RESULT
  GROUP BY PNO HAVING SUM(BONUS)>10
) Q WHERE P.PNO = Q.PNO;

SELECT LEVEL, COUNT(LEVEL) FROM READER
GROUP BY LEVEL ORDER BY LEVEL DESC;

更新

UPADATE + 表名 + SET + 字段 + WHERE + 条件

UPDATE RESULT SET BONUS = BONUS+1
WHERE PNO IN (
  SELECT R.PNO FROM PLAYER P, RESULT R
  WHERE P.PNO=R.PNO AND PSEX='' AND REANK=1
);

存储过程

开头必须DELIMITER //

CREATE PROCEDURE + 过程名 + （IN + 变量名 + 类型）

BEGIN开始 + 语句多行 + END结束

DELIMITER //
CREATE PROCEDURE DELPLAYER(IN PNAME VARCHAR)
BEGIN
DECALRE V1 VARCHAR;
SET V1 = PNAME;
DELETE FROM RESULT WHERE PNO=(SELECT PNO FROM PLAYER WHERE PNAME=V1);
DELETE FROM PLAYER WHERE PNAME=V1;
END