Mysql基础（三）——SQL完整性、多表查询及事务

---

完整性

• 完整性作用：
  保证数据的有效性和准确性

• 分类：
  实体完整性(行完整性)
  域完整性(列完整性)
  引用完整性(关联表完整性)

• 约束：

约束类型	语句
主键约束	primary key
唯一约束	unique [key]
非空约束	not null
默认约束	default
自动增长	auto_increment
外键约束	foreign key

确保数据的完整性，需要在创建表时给表中添加约束

多个约束条件之间使用空格间隔

建议这些约束应该在创建表的时候设置

示例：

create table student(
studentno int primary key auto_increment,
loginPwd varchar(20) not null default '123456',
studentname varchar(50) not null,
sex char(2) not null,
gradeid int not null,
phone varchar(255) not null,
address varchar(255) default '学生宿舍',
borndate datetime,
email varchar(50)
);

---

一、 实体完整性(行完整性)

• 实体（entity）：
  表中的一行(一条记录)

• 作用：
  标识每一行不重复数据

• 约束类型：
  主键约束（primary key）
  唯一约束(unique)
  自动增长列(auto_increment)

---

1.1 主键约束（primary key）

每个表中至少有一个主键

特点：数据非空且唯一

添加主键约束：

1、建表时直接添加

CREATE TABLE student( id int primary key, name varchar(50) );

2、建表的最后直接添加

CREATE TABLE student( id int, name varchar(50), primary key(id) );

可添加联合主键

CREATE TABLE student( classid int, stuid int, name varchar(50), primary
key(classid,stuid) );

3、使用alter语句

CREATE TABLE student( id int, name varchar(50) );
ALTER TABLE student ADD PRIMARY KEY (id);

---

1.2 唯一约束(unique)

特点：数据不重复

添加唯一约束：

CREATE TABLE student( Id int primary key, Name varchar(50) unique );

---

1.3 自动增长列(auto_increment)

给主键添加自动增长的数值，列只能是整数类型

添加自动增长列：

CREATE TABLE student( Id int primary key auto_increment, Name varchar(50) );
INSERT INTO student(name) values('tom');

---

二、 域完整性(列完整性)

• 域：
  当前单元格

• 作用：
  限制此单元格的数据正确，不对照此列的其它单元格比较

• 约束类型：
  非空约束（not null）
  默认值约束(default)
  check约束【check(sex=‘男’ or sex=‘女’)】（mysql不支持）

---

2.1 非空约束

关键词：not null

添加非空约束：

CREATE TABLE student( Id int primary key, Name varchar(50) not null, Sex varchar(10) ); 
INSERT INTO student values(1,'tom',null);

---

2.2 默认值约束

关键字：default

添加默认值约束：

CREATE TABLE student( Id int primary key, Name varchar(50) not null, Sex varchar(10) default '男' );
insert intostudent1 values(1,'tom','女');
insert intostudent1 values(2,'jerry',default); #此时默认为男

---

三、 引用完整性(关联表完整性)

• 约束类型：
  外键约束（FOREIGN KEY）

3.1 外键约束

外键列的数据类型一定要与主键的类型一致

语法：

constraint 自定义外键名称 foreign key(外键列名) references 主键表名(主键列名)

添加外键约束：

1、直接添加

CREATE TABLE student(id int primary key, name varchar(50) not null, sex varchar(10) default '男' );
create table score(
id int primary key,
score int,
sid int ,
constraint fk_score_sid foreign key(sid) references student(id) );

2、使用alter语句添加

ALTER TABLEscore1 ADD CONSTRAINT fk_stu_score FOREIGN KEY(sid) REFERENCES stu(id);

---

多表关系

四种关系：
1.一对多关系
2.多对一关系
3.多对多关系
4.一对一关系

一对多/多对一关系

• 建表原则：
  在多的一方创建一个字段，字段作为外键指向一的一方的主键

• 例：
  （一位客户和多个订单）
  客户对订单：一对多
  订单对客户：多对一

多对多关系

• 建表原则：
  需要创建第三张表，至少两个字段，这两个字段分别作为外键指向各自一方的主键

• 例如：
  学生和课程

一对一关系

• 建表原则：
  唯一外键对应：假设一对一是一个一对多的关系，在多的一方创建一个外键指向一的一方的主键，将外键设置为unique
  主键对应：让一对一的双方的主键进行建立关系

---

多表查询

多个表之间是有关系的，那么多个表之间需要进行多表约束，即通过关键列

一、 合并结果集

• 作用：
  把两个select语句的查询结果合并到一起

• tips：
  被合并的两个结果：列数、列类型必须相同

关键字：UNION 、 UNION ALL

关键字	描述
UNION	去除重复记录
UNION ALL	不去除重复记录

代码示例：

SELECT* FROM t1 UNION SELECT * FROM t2；

SELECT * FROM t1 UNION ALL SELECT * FROM t2

---

二、 连接查询

• 作用：
  求出多个表的乘积，即笛卡尔积
  例如t1连接t2，那么查询出的结果为t1*t2

笛卡尔积：
假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}

2.1 内连接

关键字： [INNER] JOIN ON

tips：
表的顺序可以互换
找两张表表示相同含义的列作为等值关系。
表名.列名
使用as给表名起别名（定义别名之后统一使用别名）

标准内连接：

SELECT *
FROM 表1 e inner join 表2 f
ON e.列名=f.列名;

一般内连接：

select 列名
from 表1 inner join 表2
on 表1.列名=表2.列名 //外键列的关系
where.....

or

select 列名
from 表1,表2
where 表1.列名=表2.列名 and ...(其他条件)

代码示例:
查询学生表中的学生姓名和分数表中的分数

select name,score
from student as s inner join scores as c
on s.studentid=c.stuid

等价于：
select name,score
from student as s,scores as c
where s.studentid=c.stuid

三表联查：

 select 列名 from 表1
 inner join 表2 on 表1.列名=表2.列名
 inner join 表3 on 表1或表2.列名=表3.列
 
 等价于:
 
 select 列名 from 表1,表2,表3
 where 表1.列名=表2.列名 and 表1/表2.列名=表3.列名

---

2.2 外连接

关键字：OUTER JOIN ON

• 特点
  查询出的结果存在不满足条件的可能

• tips
  主表数据全部显示，次表数据匹配显示，匹配成功显示数据，匹配不成功显示null
  主表和次表不能随意调换位置

• 使用场景
  一般会作为子查询的语句使用

全外连接FULL JOIN（MySQL不支持）

2.2.1 左外连接

关键字： LEFT [OUTER] JOIN

先查询出左表（即以左表为主表），然后查询右表，右表中满足条件的显示出来，不满足条件的显示NULL

select 列名 from 主表 left join 次表 on 主表.列名=次表.列名

---

2.2.2 右外连接

关键字：RIGHT [OUTER] JOIN

先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL

select 列名 from 次表 right join 主表 on 主表.列名=次表.列名

tips：

1. 通常连接查询不需要整个笛卡尔积，而只是需要其中一部分，那么这时就需要使用条件来去除不需要的记录
2. 两张表的查询至少有一个主外键条件，三张表连接至少有两个主外键条件

---

2.3 自然连接

关键字：NATURAL INNER JOIN

一种特殊的等值连接，要求两个关系表中进行连接的必须是相同的属性列（名字相同），无须添加连接条件，并且在结果中消除重复的属性列

代码示例：

select * from emp e natural join dept d

---

三、 子查询

子查询即嵌套查询，一个select语句中包含另一个完整的select语句

---

事务Transaction

事务是由一系列对系统中数据进行访问与更新的操作组成的程序执行逻辑单元

事务是用户自定义的数据库操作序列，要么全做，要么全不做，是一个不可分割的工作单位

在关系数据库中，一个事务可以是一条sql语句，一组sql语句或整个程序

事务和程序是两个概念（一般一个程序中包含多个事务）

事务的语法

序号	语法	描述
1	start transaction或者begin	事务开始
2	commit	提交事务，对当前的修改确认
3	rollback	回滚 ，对当前的修改被放弃

• 事务开始于
  1.连接到数据库上，并执行一条DML语句insert、update或delete
  2.前一个事务结束后，又输入了另一条DML语句
• 事务结束于
  1.执行commit（正常操作完提交）或rollback（遇到错误回滚）语句
  2.执行一条DDL语句（例如create table语句、grant语句）在这种情况下，会自动执行commit语句
  3.断开与数据库的连接
  4.执行了一条DML语句，该语句却失败了，在这种情况中，会为这个无效的DML语句执行rollback语句

---

事务的ACID特性

1、 Atomicity原子性

事务是一个整体，事务中的操作要么全部执行成功，要么全部执行失败

2、 Consistency一致性

事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态，即在执行一个事务前后数据库的完整性约束没有没有被破坏

数据库都必须处以⼀致性状态，事务内有一个操作失败时，所有更改过的数据都必须回滚到修改前状态

3、 Isolation隔离性

在并发环境中并发的事务互相隔离，一个事务的执行时不被其他的事务干扰，不同的事务访问相同的数据时，每个事务都有各自的完整的数据空间

事务并发会引起脏读、不可重复读、幻读

事务隔离级别有读未提交、不可重复读、可重复读、串行化

4、 Duration持久性

完成事务后，对数据的修改是永久的，即便系统故障也不会丢失

---

事务并发引起的问题

类别	描述
脏读	读取未提交数据，即事务A读取了事务B的更新的数据但是事务B回滚，A读取的为脏数据
不可重复读	同⼀条命令返回不同的结果集（更新），即事务A读取同一数据两次，但是在两次之间事务B对该数据进行了修改并提交，导致事务A读取两次读取不一致（数据的update问题）
幻读	重复查询的过程中，数据发生了量的变化，即同一个事务连续做两次当前读 (例如：select * from t1 where id = 10 for update;)，这两次当前读返回的是不相同的记录（数据的insert和delete问题）

事务隔离级别

⼀般数据默认级别是 读已提交或可重复读

级别	描述
读未提交(read_uncommitted)	不做隔离控制，可以读到“脏数据”，这个隔离级别没有太大意义
读已提交（不可重复读）(read_committed)	不允许读取事务没有提交的数据，这种级别可以避免脏读问题，是大多数数据库（除了mysql）的默认隔离级别
可重复读(repeatable_read)	与提交读（不可重复读）相对应，避免了提交读级别不可重复读问题，此为Mysql默认的隔离级别
序列化（Serializable）	要求所有事务排队执行，避免了脏读、不可重复读、幻读，是最安全的隔离级别

查询当前的隔离级别：

select @@transaction_isolation;

设置当前回话的隔离级别为read uncommitted：

 set session transaction isolation level read uncommitted;

---

4种事务隔离级别从上往下，级别越高，安全性就越来越高，但是并发性越差

隔离级别\并发	脏读	不可重复读	幻读
读未提交	√	√	√
不可重复读	×	√	√
可重复读	×	×	可能会
串行化	×	×	×

---

不同的隔离级别的锁的情况

隔离级别	锁
读未提交（RU）	有行级的锁，没有间隙锁（与RC的区别是能够查询到未提交的数据）
读已提交（RC）	有行级的锁，没有间隙锁，读不到没有提交的数据
可重复读（RR）	有行级的锁，也有间隙锁，每次读取的数据都是一样的，可能出现幻读
序列化（S）	有行级锁，也有间隙锁，读表的时候，就已经上锁了

---

隐式提交

隐式提交：执行这种语句句相当于执行commit

• DDL 定义语句
  数据定义语言，用来定义数据库对象：库、表、列等

• DML 操作语句
  数据操作语言，用来定义数据库记录（数据）增删改

• DCL 控制语句
  数据控制语言，用来定义访问权限和安全级别

• DQL 查询语句
  数据查询语言，用来查询记录（数据）查询

DDL（Data Define Language）语句都是隐式提交
【当执行到DDL语句时，会隐式的将当前回话的事务进行一次“COMMIT”操作】