1、什么是参照完整性
设F是基本关系R的一个或者一组属性，
是基本关系S的主码，如果F与
相对应，则称F是R的外码（foreign key)，并称基本关系R称为参照关系，基本关系S为被参照关系
2、参照完整性规则
2.1、参考完整性定义
参照完整性规则就是定义外码与主码之间的引用关系
若属性（或者属性组）F是基本关系R的外码，它与基本关系S的主码
，则对于R中的每一个元组在F上的值必须：
- 取空值
- 或者等于S中某个元组的主码值
定义SC中的参考完整性：
create table SC
(
Sno char(9) not null,
Cno char(8) not null,
grade int,
primary key(Sno,Cno),/*在表级定义实体完整性*/
foreign key(Sno) references Student(Sno),/*在表级定义参考完整性*/
foreign key(Cno) references Course(Cno)/*在表级定义参考完整性*/
);
create table Student
(
Sno char(9) primary key,
Sname char(20) unique,
Ssex char(2),
Sage int,
Sdept char(10)
);
create table Course
(
Cno char(9) primary key,
Cname char(20) not null,
Cpno char(4),
credit int,
foreign key(Cpno) references Course(Cno)
);2.2、参考完整性检查和违约处理
参考完整性把两个表中相应的元组联系起来，对参照表和被参照表进行增删改有可能破坏参看完整性，这时必须检查以保证两个表的相容性。
对于SC和Student表有4种情况可能破坏参考完整性的情况：
（1）SC表种增加一个元组，该元组的Sno属性在student中找不到一个元组其Sno属性值与之相等。
（2）修改SC表中的一个元组，修改后该元组的Sno属性值在Student表中找不到一个元组其Sno属性值与之相等。
（3）在Student表中删除一个元组，造成SC表中某一些元组的Sno属性值在Student中找不到一个元组其Sno与之相等。
（4）修改Student表中一个元组得Sno属性，造成SC表中某些元组得Sno属性值在Student表中找不到一个元组与之Sno属性值相等。
2.3、当上述得不一致发生时，系统可以采用一下策略加以处理：
- 拒绝操作（该策略一般设置为默认策略）
- 级联（CASCADE）操作
当删除或者修改被参照表得一个元组导致参照表的不一致，删除或者修改参照表中所有导致不一致的元组。
例如：删除Student表中的Sno值为'20125'的元组，则要从SC表中级联删除SC.Sno='20125'所有元组。
3. 设置为空值
当删除或者修改被参照表的一个元组时造成不一致，则将参照表中所有造成不一致的元组对应属性设置为空值。
一般地，当参照表和被参照表的操作违反了参照完整性时，系统选用默认策略，即拒绝执行，如果想让系统采用其他策略则必须在创建参照表时显式地加以说明
显式说明参照完整性的违约处理示例：
create table SC
(
Sno char(9) not null,
Cno char(8) not null,
grade int,
primary key(Sno,Cno),
/* on delete cascade 是当删除student表中Sno元组时，级联删除SC表中相应的元组 */
foreign key(Sno) references Student(Sno) on delete cascade on update cascade,
/* on delete cascade 是当删除Course表中Cno元组时，级联删除SC表中相应的元组 */
foreign key(Cno) references Course(Cno) on delete cascade on update cascade
);3、SQL的基本概念
外模式包括若干视图和部分基本表；模式包括若干个基本表，内模式包括若干存储文件
4、数据定义
操作对象 | 操作方式 | ||
创建 | 删除 | 修改 | |
模式 | CREATE SCHEMA | DROP SCHEMA | |
表 | CREATE TABLE | DROP TABLE | ALTER TABLE |
视图 | CREATE VIEW | DROP VIEW | |
索引 | CREATE INDEX | DROP INDEX | ALTER INDEX |
SQL标准不提供修改模式定义和修改视图定义的操作，用户想修改视图就得删除然后在重新创建。
4.1、模式得定义与删除
定义模式：
CREATE SCHEMA < 模式名 >AUTHORIZATION <用户名>
例如：为lisi创建一个模式TEST，并在其中定义一个表TAB1
create SCHEMA TEST AUTHORIZATION lisi
create table TAB1(
col1 int,
col2 int,
col3 varchar(20)
);删除模式：
DROP SCHEMA <模式名> <SASCADE | RESTRICT>;
其中SASCADE | RESTRICT任选一
比如上面得删除：
drop schema lisi SASCADE;4.2、表的定义、删除、修改
表的创建：
CREATE TABLE <表名>（
<列名> <数据类型> [ 列级完整性约束条件 ]，
<列名> <数据类型> [ 列级完整性约束条件 ]，
....
<列名> <数据类型> [ 列级完整性约束条件 ]
）
例如：
create table student（
Sno char(9) primary key,
Sname varchar(20) UNIQUE,
Ssex char(2),
Sage int,
Sdept varchar(20)
）修改基本表：
ALTER TABLE <表名>
[ADD <新列名><数据类型>[完整性约束]]
[ADD constraint <完整性约束名> <完整性约束>]
[ DROP column 列名]
[DROP constraint <完整性约束名>]
[MODIFY<列名> <数据类型>[完整性约束]]
其中<表名>指定需要修改的基本表
add:
添加多列时，用括号围住，并以逗号分隔
ALTER TABLE tbl_dept ADD(Sbithday DATE,Shobby VARCHAR(20));DROP：
可以一列一列删除，也可以如下一次性删除
ALTER TABLE tbl_dept DROP age,DROP sexMODIFY:重新修改字段属性，数据类型...
ALTER TABLE tbl_dept MODIFY sex CHAR(8) DEFAULT('女');修改多个字段：
ALTER TABLE tbl_dept MODIFY age INT(2), MODIFY sex CHAR(8)表的删除：
Drop table <表名>[RESTRICT | CASCADE]
RESTRICT：只是删除表
CASCADE：把视图等与表一起删除
4.3、视图 （用的少）
创建视图：
CREATE VIEW <视图名>
AS <子查询>
[ WITH CHECK OPTION ]
其中WITH CHECK OPTION 表示对视图进行UPDATE、INSERT、DELETE操作时要保证更新、插入或者删除的行满足视图定义中的微词条件
5、数据更新
5.1、插入数据
通常插入语句有两种，一种是插入一个元组，另一个是插入子查询结果，后者可以一次性插入多个元组。
插入元组：
insert into <表名> [<属性列1>，<属性列2>,<属性列3>......] values(值1，值2，值3......);
当指定属性列插入时，其他没有指定的就默认为null，或者属性列是自增的就会自行增加，例如id为int类型自增，不指定该属性列插入，就会自动增加1.
insert into student(Sno,Sname,Ssex,Sage)values('2018782','李四','男',20)如果属性列全部都不指定，则默认是插入的元组是所有字段值都要加入，即使是null值也要在value中写上。
比如学生表字段就Sno，Sname，Ssex，Sage，插入所有字段对应值：
insert into student values('201555','张三','男',21)也可以插入多个元组
INSERT INTO tbl_dept VALUES(10,'开发部','深圳'),(11,'开发部','深圳'),(12,'人事部','北京')插入子查询:
子查询不仅可以嵌套在Select语句中还可以嵌套在insert语句中，进行批量插入数据。
INSERT INTO <表名> [<属性列1>,<属性列2>,<属性列3>,.....] 子查询;
子查询出来的数据需要和表的属性列对应，不可以出现表属性列就三列，而子查询的列数有4列。
例如按照部门名分组插入表中：
insert into tbl_dept(dept_name,locAdd) select dept_name,locAdd form tbl_dept Group by dept_name5.2、修改数据
修改操作又称为更新操作，其语句一般格式：
update <表名> set <列名> = <表达式> , [<列名> = <表达式> ] ........ [ where <条件>];
修改指定where条件的元组，其中set子句的 =<表达式>的值用来取代相应的属性列值。
修改某一个元组的值：
例如：
update student set Sage=10 where Sno='266100';修改多个元组的值：
update student set Sage=Sage+1把表中的所有Sage都加1操作。
在开发中删除用户其实是修改数据，通过一个字段控制，从激活状态修改成未激活状态。
5.3、删除数据
删除语句的一般格式：
delete from <表名> [ where <条件>]
delete 删除满足where条件的所有元组，删除的是表中的数据。
删除某个元组：
delete from student where Sno=‘1820400'删除多个元组：
delete from SC;删除表中所有的数据，成为空表。
6、数据库完整性
6.1、实体完整性
定义：实体完整性这项规则中要求每一个数据表都必须有主键，而作为主键的所有字段，其属性必须独一非空值。
列级约束：是对于某一个特定列的约束
表级约束：用于对多个列一起进行约束
表级约束与列级约束的区别：
（1）列级约束：只能应用在一列上
表级约束：可以应用在一列上，也可以应用在一个表中的多列上。
（2）列级约束：在列的定义上，直接跟在该列的定义后面，用空格分开；不必指定列名。
表级约束：与列定义相互独立，不在列的定义中，与列定义分开用逗号分开，必须指定要约束的列名
例如：表级约束定义主码
create table SC
(
Sno char(8) not null,
Cno char(4) not null,
grade int,
primary key (Sno,Cno)
)例如：列级约束定义主码
create table SC
(
Sno char(8) primary key,
Cno char(4),
grade int
)6.2、实体完整性检查和违约处理
拿primary key 短语做说明，定义主码后每当用户插入一条记录，实体完整性规则就会自动进行检查：
（1）检查主码值是否唯一，不唯一就拒绝插入或修改
（2）检查主码的各个属性是否为空，只要有一个为空就拒绝插入或修改
检查记录中的主码值是否唯一的一种方法：扫描全表，这个过程是非常耗时的，为了避免扫描全表，关系型数据库管理系统一般都会在主码中自动建立一个索引（B+树索引）
通过索引查找大大提高效率，如果插入记录主码值是25，通过主码索引，从B+树的根节点开始查找，只要读取三个节点就知道主码值已经存在。
7、用户定义的完整性
用户定义完整性：就是针对某一个具体应用的数据必须满足的语义要求
7.1、属性上的约束条件
- 列值非空 （not null )
Sno char(8) not null
- 列值唯一 （unique）
Sname char(9) unique not null
- 检查列值是否满足一个条件表达式 （check短语）
比如：student表的Ssex只允许取 "男" 或者"女"
Ssex char(8) check(Ssex in ('男','女')
分数grade的值在0-100之间：
grade int check(grade>=0 and grade<=100)
当往表里插入数据或者修改属性的值时，会检查属性的约束条件是否满足。如果不满足就会拒绝操作。
7.2、完整性约束命名子句
当使用完整性约束命名，从而可以灵活地增加、删除一个完整性约束条件。
格式：
constraint <完整性约束条件名> <完整性约束条件>
<完整性约束条件>：not null 、unique、primary key、foreign key、check
例如：创建学生登记表student，要求学号在90000-99999之间，姓名不能为空，年龄小于30，性别只能是男或者女
create table student
(
Sno int constraint C1 check(sno between 90000 and 99999),
Sname char(8) constraint C2 not null,
Sage int constraint C3 check(Sage<30),
Ssex char(2) constraint C4 check(Ssex in('男','女'),
constraint studentKey primary key(Sno)
);修改表中的完整性限制：
可以使用alter table 语句修改表中的完整性限制
例如：
alter table student drop constraint C4;可以先删除然后在添加：
alter table student drop constraint C1;
alter table student add constraint C1 check(Sno between 100000 and 999999);8、触发器
触发器是用户定义在关系表傻瓜的一类由事件驱动的特殊过程。一旦定义，触发器将会保存在数据库服务器中，任何用户对表的增、删、改操作均由服务器自动激活相应的触发器。
触发器又叫做：事件-条件-动作规则。
8.1、触发器三要素
事件类型：增、删、改； insert、delete、update
触发时间：前后before 和after After：是表示触发事件的操作在执行之后才激活触发器 ； Before：是在触发事件操作执行之前激活触发器
触发对象：表中的每一条记录（行）
一张表最后有6个触发器
8.2、触发器的作用
触发器类似于约束，但是比约束更加灵活，可以实施更加复杂的检查和操作（强大的数据控制能力）保证数据的完整性，起到约束的作用
8.3、触发器的格式
create trigger <触发器名>
{before |after } <触发器事件> on <表名> // 指明触发器激活的事件是在执行之前还是执行之后
referencing NewRow | OldRow as <变量> //referencing指出引用的变量
for Each {Row | STATEMENT}
WHEN <触发条件> <触发动作体>
9、 三大范式（重点）
设计表的依据，按照这个三个范式设计的表不会出现数据冗余。
第一范式：每一列属性都是不可以再分，确保每一列的原子性。
学号 | 姓名 | 性别 | 家庭信息 | 学校信息 |
200015 | 李四 | 男 | 上海 | 硕士，研二 |
200016 | 张三 | 男 | 武汉 | 本科，大三 |
学校信息这列不满足原子性的要求，可以再分为学历和所在年级
第二范式：属性完全依赖于主键，不能产生部分依赖
是在第一范式的基础上建立的，要求数据库表中的每一行必须可以被唯一区分，通常就是加上一列来唯一标识（主键）
学生编号(PK) | 教师编号(PK) | 学生姓名 | 教师姓名 |
10001 | 1001 | 李四 | 王老师 |
10002 | 1002 | 张三 | 李老师 |
在这个数据库中，多对多的关系，多个学生对应多位老师，但是这个符合第一范式（1NF）但是不符合第二范式（2NF）属性不完全依赖主键，学生姓名只是依赖于学生编号。不能产生部分依赖。
对于多对多表的结构设计：三张表，关系表两个外键+主键
对上面的表重新设计：
t_student：
sno（pk） | Sname |
1 | 张三 |
2 | 李四 |
3 | 小王 |
t_teacher：
tno（pk） | Tname |
1 | 王老师 |
2 | 李老师 |
3 | 张老师 |
relation_table:
id（pk） | sno(FK) | tno(FK) |
11 | 1 | 3 |
2 | 1 | 1 |
3 | 2 | 1 |
这样非主属性完全依赖主键，所以在设计表的结构时，我们不要使用复合主键。
第三范式：属性完全依赖于主键，不能产生传递依赖
建立在第二范式基础上，所有非主键字段直接依赖主键，并且不能产生依赖传递
学生编号（PK） | 学生姓名 | 班级编号 | 班级名称 |
1001 | aaaa | 01 | 一年级一班 |
1002 | bbbb | 02 | 一年级二班 |
1003 | cccc | 03 | 一年级三班 |
在这里班级编号依赖学生编号，班级名称依赖于班级编号，就会产生传递依赖了，这里是一个班级对应多个学生。
一对多表的设计：两张表，多的表加外键
班级表t_class
cno(PK) | cname |
1 | 班级1 |
2 | 班级2 |
学生表t_student:
sno(PK) | sname | classNO(fk) |
1 | aaaa | 1 |
2 | bbbb | 1 |
3 | cccc | 2 |
4 | dddd | 2 |
一对一
方式一：主键共享
CREATE TABLE user_login
(
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(20),
`password` VARCHAR(20)
);
CREATE TABLE user_user_detail
(
id INT PRIMARY KEY,
realname VARCHAR(20),
tel VARCHAR(20),
FOREIGN KEY(id) REFERENCES user_login(id)
);方式二：外键唯一
10、事务（重要）
事务是用户定义的一个数据库操作序列，这些操作要么全做，要么全不做，是一个不可分割的工作单位。
和事务相关得语句只有：DML语句。（insert、delete、update）为什么？因为事务得存在时为了保证数据得完整性，安全性。
通常事务，需要多条DML语句共同联合完成，就比如转账操作：需要修改两个用户的信息。
10.1、事务的原理
就比如一个事务：需要先插入一条数据，在执行更新一条数据；最后在执行一条删除语句
第一步：开启事务机制（开始）
执行insert语句（执行这行成功之后，把这个执行记录保存在数据库操作历史当中（类似缓存）；并不是向文件中保存一条数据，不会真正的插入道硬盘上去。
执行update语句：这个执行也是把记录保存在操作数据库历史中，不会真正修改到硬盘上
执行delete语句：这个执行也是不会真正修改到硬盘中。
第二步：提交事务或者回滚事务（结束）
在结束时，哪些历史记录就会被删除。
10.2、事务四大特性
事务包括四大特性：ACID
A：原子性：事务是最小的工作单元，不可再分。
C：一致性：事务必须保证多条DML语句同时成功或者同时失败。
I：隔离性：事务A和事务B之间有隔离，互不干扰，并发执行的事务不会互相影响。
D：持续性：最终数据必须持久化到硬盘文件中，事务才算成功的结束。
10.3、并发事务处理带来的问题
更新丢失（Lost Update）：
脏读（Dirty Reads）：
不可重复读（Non-Repeatable Reads）：
幻读（Phantom Reads）：
10.4、事务的隔离性
事务隔离性存在级别，理论上隔离级别包括4个：
第一级别：读未提交 （read uncommitted）
对方事务还没有提交，我们当前事务就可以读取到对方未提交的数据；读未提交存在 脏读 （dirty read）现象 ：表示读到了脏的数据。
第二级别：读已提交 （read committed ）
对方事务提交后的数据我方可以读取到，这种隔离解决了脏读现象 ；存在的问题就是不可重复读。
不可重复读 指事务T1读取数据后，事务T2对其进行更新，使得T1无法再现前一次读到的结果
具体包括三种情况：
1）事务T1读取某一数据后，事务T2对其进行修改，事务T1再次读取该数据时，得到与之前不一样的值
2）事务T1按照一定条件读取数据后，事务T2删除了其中部分记录，当T1再次按相同条件读取数据时，发现某些记录不见
3）事务T1按照一定条件读取数据后，事务T2插入了一些数据，当T1再次按相同的条件再次读取数据时，发现多了某些数据
第三级别：可重复读（repeatable read）
这种隔离级别解决：不可重复读问题；这种隔离级别存在的问题：读取到的数据是幻像。
可重复读：事务T1对某个数据修改并提交了，事务T2读取到的还是之前（事务T1没有提交前的）没修改之前的数据。（"重",和之前读是相同）
第四级别：序列化读 / 串行化读 （serializable）
解决所有问题，效率低。需要排队，当某个事务在操作某一个时，下一个事务就不可以在操作，需要等待处理完。
mysql数据库默认的隔离级别：可重复读
oracle数据库默认的级别：读取已提交
查看隔离级别：select @@global.tx_isolation;
设置隔离级别：set global transaction isolation level 隔离级别；read uncommitted 、read committed、repeatable read、serializable
查看事务自动提交：SHOW VARIABLES LIKE 'autocommit'
设置事务是否自动提交：set autocommit=1 -- 1是自动提交 -- 0 不是自动提交
