数据库速成

关系模型及关系代数操作

　　下面我用一段话解释一些基本概念：

　　　　每一个关系模型对应一个关系表。一个关系表有一个唯一的名字。关系表是一个二维的数据结构，表中的行被称为元组；列被称为属性。关系实例表示一个关系中一组特定的行。域表示一个属性允许取值的集合。分量表示元组中的一个属性。关系数据库由关系表的集合构成。

　　　　不存在任何两个元组，它的属性取值是完全相同的。

　　通过这段话，应该可以清楚的了解关系模型的定义，作用等等。

 

　　接下来再谈一谈关系代数

　　1.关系代数是什么？

　　　　它是一种查询语言，用对关系的运算来表达查询。

　　2.关系代数有哪些？

　　　　一般有 并，差，交（简单） 和 笛卡尔积，选择，投影，连接，除（复杂）

　　　　1）简单part——只需要知道一点：关系与关系通过关系代数运算依然为关系。 easy part省略。

　　　　2）复杂part

　　　　　　x笛卡尔积——两个关系中所有的元组两两组合。

　　　　　　σ选择——在关系中对一个或多个属性进行限制得到的新的关系。限制手段包括＞，≥，＜，≤，＝，<>（不等于），非，与，或。

　　　　　　Π投影——从一个关系中选择几个属性，构成一个新的关系

　　　　　　连接（符号比较多，不记录了）包括等值连接，自然连接，外连接，左外连接，右外连接。

　　　　　　　　等值连接——先做笛卡尔积，然后做选择，选择原则是筛选那些被选属性值相等的元组。

　　　　　　　　自然连接——特殊的等值连接，要求比较的分量必须是相同的属性组，并且在结果中把重复的属性列去掉。

　　　　　　　　悬浮元组——在自然连接中，在两个关系中都可能出现没有被利用到的元组，它们被称为悬浮元组。

　　　　　　　　外连接——把悬浮元组也保存在结果关系中，其他属性上填NULL。

　　　　　　　　左/右外连接——只保留左/右关系中的悬浮元组的外连接。

　　　　　　除——比如R÷S，保留R六面满足S的元组，并且去掉S的属性

 

SQL语言

　　　SQL语言的特点：综合统一；高度非过程化；面向集合操作；可以做脚本语言，也可以包含在高级语言中；简洁。

　　　SQL分类：

　　　　1）DDL：数据定义语言——定义数据库对象

　　　　2）DML：数据操作语言——数据操作

　　　　3）DCL：数据控制语言——控制访问权限和安全级别

　　　　4）DQL：数据查询语言——用于数据查询

　　　SQL基本语法：

　　　　数据类型：  CHAR(n)——定长字符串    VARCHAR(n)——变长字符串

　　　　　　　　　  INT——长整数（4字节）   SMALLINT——短整数（2字节） BIGINT——大整数（8字节）

　　　　　　　　　  DEC（p,d）：一共三位，d位小数的数

　　　　　　　　　  FLOAT（n），BOOLEAN，DATE（YYYY-MM-DD），TIME（HH：MM：SS），TIMESTAMP（时间戳，毫秒，从1970年开始），INTERVAL（时间间隔）

　　　　基本操作：

　　　　　　CREATE user 'Jack'@localhost IDENTIFIED BY '123';   - - 创建用户 （DCL）

　　　　　　CREATE DATABASE mydb1;

　　　　　　DROP DATABASE mydb1;  - - 创建/删除数据库

 

　　　　操作表

　　　　创建表   CREATE TABLE stu( sid CHAR(6) , sname VARCHAR(20), age INT, gender VARCHAR(10),score INT);

　　　　修改表

　　　　　　1）添加列  ALTER TABLE stu ADD(classname varchar(10)) ;

　　　　　　2)  修改列的数据类型 ALTER TABLE stu MODIFY gender CHAR(2) ;　　　　　　　　　　

　　　　　   3） 修改列的名称  ALTER TABLE stu CHANGE gender sex CHAR(2);

　　　　　　4)  删除列 ALTER TABLE stu DROP classname;

                     5)  改表名 ALTER TABLE stu RENAME TO studdents;             （ all above DDL）

　　　　插入数据

　　　　　　       INSERT INTO stu VALUES('s_001','ZhangSan',18,'male');

　　　　　　　　INSERT INTO stu(sid,age) VALUES('s_002',20);

　　　　修改数据

 

　　　　　　　   UPDATE stu SET sname='Lisi' WHERE sid="s_001";

　　　　　　　　UPDATE stu SET age=age+1 WHERE sname=’zhaoLiu’;

　　　　删除数据

　　　　　　　　DELETE FROM stu WHERE sid="S_002";                         (all above DML) 

 

　　　　数据查询语言

　　　　1)基础查询

　　　　　　SELECT * FROM stu;  - - 查询所有列

　　　　　　SELECT sid,age FROM stu; - - 查询指定列

　　　　2）条件查询

　　　　　　SELECT * FROM stu WHERE gender='female' AND age < 50;　　

　　　　　　SELECT * FROM stu WHERE gender='female' AND age < 50;

　　　　　　SELECT * FROM stu WHERE sid NOT IN('s_001','s_002');

　　　　　　SELECT * FROM stu WHERE gender!='female' ;

　　　　　　SELECT * FROM stu WHERE sname LIKE '____s';

　　　　　　SELECT * FROM stu WHERE sname LIKE '%a%';

　　　　3)排序

　　　　　　SELECT * FROM stu ORDER BY age ASC,score DESC;  - - 查询所有学生，按年龄升序排序，如果年龄相同时，按分数降序排序

　　　　4)分组查询

　　　　　　SELECT  gender,AVG(score) FROM stu GROUP BY gender;

　　　　5)连接查询

　　　　　　内连接：SELECT class1.sname,class2.sname FROM class1,class2 WHERE class1.score=class2.score;　

　　　　　　外连接：SELECT class1.xname,class2.sname FROM class1 LEFT JOIN class2 class1.score=class2.score;　

　　　　

数据库的安全性，完整性

　　安全性

　　数据库不安全的主要因素有：非授权用户对数据库的恶意存取破坏、重要数据泄露等等

　　数据库的安全性控制主要取决于用户身份鉴别和存取权限控制（主要）。

　　存取权限控制按照字面意思就是用户可以自定义和分配其他用户的操作权限，主要通过grant 和 revoke来操作。

　　　　GRANT SELECT UPDATE  ON stu TO user1 WITH GRANT OPTION  - - 给user1权限，并且他能够进一步下放权限

　　　　REVOKE SELECT UPDATE  ON stu FROM user1

　　审计机制  把对数据库的所有操作记录到审计日志中，通过审查日志来看有无非法行为

　　　　AUDIT UPDATE on stu；

　　

　　完整性

　　　　数据库的完整性是指数据的正确性和相容性

　　　　　　1）符合现实语义，反映当前情况。

　　　　　　2）数据库的同一对象在不同的关系中的数据符合逻辑

　　　　目的：防止数据库中存在不合法的数据。因此数据库系统需要满足以下功能：1）提供定义完整性约束条件的机制 2）提供完整性检查的方法 3）进行违约处理

　　　　重点一：三大实体完整性，即实体完整性，参照完整性，用户定义完整性

　　　　　　1.实体完整性——主码唯一且非空

　　　　　　　　create table Student(id char(9) primary key , sname char(20) not null )； - -主码用primary标识即可

       　　　　2.参照完整性——外码中的取值要么取主码中存在的值，要么取空值（外码：某关系中的非候选码是另一个关系中的主码）

　　　　　　　   create table SC(

　　　　　　　　　　id char(9) ,

　　　　　　　　　　cno char(4) ,

　　　　　　　　　　grade smallint ,

　　　　　　　　　　primary key (id,cno) ,

　　　　　　　　　　foreign key(id) references Student(id),

　　　　　　　　　　foreign key(Cno) references Course(Cno)

　　　　　　　　)；

　　　　　　3.用户定义完整性

　　　　　　　这个比较特殊，用户可以在属性或者元组定义上添加约束条件（非空，列值唯一和check）　　　 //这里示例是前者型　　　　

　　　　　　　　create table SC(
　　　　　　　　　　Sno char(6) not null ,
　　　　　　　　　　Cno char(4) not null ,
　　　　　　　　　　Grade smallint check (Grade >= 0 and Grade <=100), 
　　　　　　　　　　primary key (Sno,Cno)

　　　　　　　　);

　　　　　　重点二：断言和触发器

　　　　　　　　1.断言

　　　　　　　　什么是断言？——在 SQL 中可以使用数据定义语言中的 create assertion 语句，通过声明性断言来指定更具一般性的约束，可以定义涉及多个表或聚集操作的比较复杂的完整性约束。断言创建以后，任何对断言中所涉及关系的操作都会触发关系数据库管理系统对断言的检查，任何使断言不为真值的操作都会被拒绝执行。

　　　　　　　　格式：create assertion <断言名> <check 子句>

　　　　　　　　例如：- -限制每一门课程最多 60 名学生选修

　　　　　　　　　　create assertion ASS_SC_NUM

　　　　　　　　　　check (60>= all(select count(*) from SC group by Cno));

 　　　　　　　　　　drop assertion ASS_SC_NUM； - - 删除断言

　　　　　　　　2.触发器

　　　　　　　　什么是触发器？——简单理解就是  事件->条件->动作 的规则，当一个特定的事件（如对一个表的增，删，改操作）发生时，对条件进行检查，如果成立则执行动作。

　　　　　　　　格式：

　　　　　　　　　　create trigger <触发器名>/*每当触发事件发生时，该触发器被激活*/

　　　　　　　　　　{before|after}<触发事件> on <表名>/*指明触发器激活的时间是在执行触发事件前或后*/

　　　　　　　　　　referencing new|old row as <变量>/* referencing 指出引用的变量 */

　　　　　　　　　　for each{row|statement}/*定义触发器的类型，指明动作体执行的频率*/

 

　　　　　　　　　　[when <触发条件>] <触发动作>/*仅当触发条件为真时才执行触发动作体*/

　　　tips： 1)、只有表的拥有者，即创建表的用户才可以在表上创建触发器，并且一个表上只能创建一定数量的触发器。触发器的具体数量由具体的关系数据库管理系统在设计时确定。

　　　　　　2)、触发器名：触发器名可以包括模式名，也可以不包含模式名。同一模式下，触发器名必须是唯一的，并且触发器名和表名必须在同一模式下。

　　　　　　3)、表名：触发器只能定义在基本表上，不能定义在视图上。当基本表的数据发生变化时，将激活定义在该表上相应触发事件的触发器，因此该表也被称为触发器的目标表。

　　　　　　4)、触发事件：触发事件可以是 insert 、delete 或 update ，也可以是这几个事件的组合，如 insert or delete 等，还可以是 update of <触发列，···> ，即进一步指明修改哪些列时激活触发器。after/before 是触发的时机。after 表示在触发事件的操作执行后激活触发器；before 表示在触发事件的操作执行之前激活触发器。

　　　　　　5)、触发器类型：触发器按照所触发动作的间隔尺寸可以分为行级触发器和语句级触发器。假设在 teacher 表上创建了一个 after update 触发器，触发事件是 update 语句：update teacher set Deptno=5 ；假设表 teacher 有 100 行，如果定义的触发器为语句级触发器，那么执行完 update 语句后触发动作体执行一次；如果是行级触发器，触发动作体将执行 100 次。

　　　　　　6)、触发条件：触发器激活时，只有当触发器条件为真时触发动作体才执行，否则触发动作体不执行。如果省略 when 触发条件，则触发动作体在触发器激活后立即执行。

　　　　　　7)、触发动作体：触发动作体既可以是一个匿名 PL/SQL 过程块，也可以是对已创建存储过程的调用。如果是行级触发器，用户可以在过程中使用 new 和 OLD 引用 update/insert 事件之后的新值和 uipdate/delete 事件之前的旧值；如果是语句级触发器，则不能在触发动作体中使用 new 或 OLD 进行引用

 

关系数据库理论

　　首先是定性的理解。当面对一个现实问题，例如要求设计一个教学管理数据库，如何使关系模型设计一个合理且合适的关系数据库，如何选择一个比较好的关系模式的集合，每个关系由哪些属性组成，这些属于数据库逻辑设计的问题。数据库规范化理论是数据库逻辑设计的理论依据。在关系数据库中，关系模型包括一组关系模式，并且关系之间并不是一座座孤岛。如何设计一个合适的关系数据库系统，其关键是设计关系型数据库的模式。具体来说就是：　

• 数据库中应该包含哪些关系模式
• 每一个关系模式应该包括哪些属性
• 如何将这些相互之间有关联的关系模式组建成一个完整的关系型数据库

        关系数据库的规范化理论主要包括三个方面的内容：函数依赖、范式Normal Form以及模式设计。其中函数依赖起着核心的作用，是模式分解和模式设计的基础，范式则是模式分解的标准。

一些概念：

为什么要引入范式？——因为可能存在数据冗余，更新、插入、删除异常等情况

函数依赖，候选码及其求法，超码可参照b站【拯救者】数据库系统概论速成，这里不再展开了解了。

ok，接下来我们具体重点学习：范式（背背背）

　　1NF：所有字段都是不可再分的原子值

　　2NF：一个关系表中只能保存一种有实际价值的数据

　　3NF：确保数据表中的每一列属性值都和主码直接相关，而不是间接相关。

　　BCNF：每个属性对于候选码不能有传递依赖，是对3NF的补充。

公理系统存在三律：自反律，增广律和传递律，通过它们可以得到三个规则：合并规则，伪传递规则和分解规则

　　1）合并规则：X->Y,X->Z,则X->YZ

　　2）伪传递规则：X->Y，WY->Z，则XW->Z

　　3）分解规则：X->Y，Z属于Y，则X->Z

 

然后，求最小函数的依赖集，直接上步骤：

　　1）拆右边为多个元素的，比如A->BC，拆分为A->B和A->C

　　2）除去当前的元素，求闭包，把集合中所有元素都弄完

　　3）左边最小化，比如BCD 遮住B能推出C吗？ 遮住C能推出BD吗？  遮住D能推出BC吗？

 

最后，模式分解：

　　（记住）准则：无损链接和保持函数依赖

 

数据库设计（重点：概念结构设计和逻辑结构设计）

基本步骤：

　　需求分析------->概念结构分析（ER图）------->逻辑结构设计（ER图转化为逻辑模型）------->物理结构设计------->数据库实施（写代码）------->数据库运行维护        孰为重点自不必多言

　　1）ER图的设计，

　　　　　实体用长方形表示，属性用椭圆表示，关系用菱形表示；关系两端的实体要标明其关系：1对1，1对多或者多对多。根据需求设计即可。

　　2）逻辑模型的转化

　　　　　每一个实体都是一个表，把所有的属性添加到本表中。对于关系上的属性：

　　　　　     （1）1对1，则把关系合并到任意一个表中，同时把另一个表中的主码也放到该表中

　　　　　　 （2）1对多，则把关系合并到n关系表中，同时把1关系表中的主码放到n关系表中

　　　　　　 （3）多对多，单独作为一个关系表，需要自身的所有属性和两边实体的主码。

 

 

查询处理和优化

　　1）查询过程是什么

　　　　查询语句的语法分析（查询分析）；语义分析，安全性完整性初步检查（查询检查）；代数优化，物理优化等（查询优化）；执行，生成代码。

　　2）查询优化是什么？

　　　　查询优化包括代数优化和物理优化，重点是代数优化

　　3）代数优化过程：

　　　　选择运算尽量先做；头因运算和选择运算同时执行；把投影同它前后的双目运算符连接起来。

 

 

PL/SQL数据库编程

1）嵌入式SQL

　　　什么是嵌入式SQL？  把SQL嵌入到其他编程语言中

　　　嵌入式SQL的处理过程？   预编译转换为函数调用；主语言编译；SQL语句变成主语言所编译的类型

　　　SQL与主语言通信的过程？  SQL给主语言传递状态；主语言给SQL传递参数；SQL把执行结果交给主语言处理

 

2）事务

　　　什么是事务？数据库事务是构成单一逻辑工作单元的操作集合

　　　　       关于事务的定义有几点需要解释下：

　　　　　　1.数据库事务可以包含一个或多个数据库操作,但这些操作构成一个逻辑上的整体。
　　　　　　2.构成逻辑整体的这些数据库操作,要么全部执行成功,要么全部不执行。
　　　　　　3.构成事务的所有操作,要么全都对数据库产生影响,要么全都不产生影响,即不管事务是否执行成功,数据库总能保持一致性状态。