数据库基础重要概念

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、当上述得不一致发生时，系统可以采用一下策略加以处理：

1. 拒绝操作（该策略一般设置为默认策略）
2. 级联（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 sex

MODIFY:重新修改字段属性，数据类型...

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_name

  5.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 不是自动提交