MySQL 5-?Chpters（Unfinished）

CH05 多表操作

5.1 外键

--外键用于建立和加强两个表数据之间的连接
create database chapter05;
use chapter05;
create table grade(id int(4) not null primary key,name varchar(36)
);
create table student(sid int(4) not null primary key,--gid是学生所在的班级id 引入了grade表中的主键id 那么gid作为student表的外键--那么grade为主表 student为从表sname varchar(36),gid int(4) not null
);--为表添加外键约束
alter table 表名 add constraint FK_ID foreign key(外键名) references 外表表名(主键字段名);
alter table student add constraint FK_ID foreign key(gid) references grade(id);--删除外键约束
alter table 表名 drop foreign key 外键名;
alter table student drop foreign key FK_ID;

5.2 操作关联表

5.2.1 关联关系

1.多对一：例如员工与部门的关系。一个部门可以有很多员工，而一个员工不能属于多个部门。
2.多对多：例如学生与课程之间的关系。一个学生可以选择很多课程，一门课程也供多个同学选择。
3.一对一：例如人与身份证的关系。

5.2.2 添加数据

--在5.1上我们让student和grade中添加外键约束来建立这两个表的关联关系。
alter table student add constraint FK_ID foreign key(gid) references grade(id);
--此时表student和表grade之间是多对一的关系--因为外键列只能插入参照列存在的值，所以如果要为两个表添加数据 就需要先为主表grade添加数据
insert into grade(id,name) values(1,"class 1");
insert into grade(id,name) values(2,"class 2");--在上述语句中添加的主键id为1,2 由于student表的外键与grade表的主键关联
--所以在为表student添加数据时 gid的值只能为1,2 不能用其他值
insert into student(sid,sname,gid) values(1,"WangHong",1);
insert into student(sid,sname,gid) values(2,"LiQiang",1);
insert into student(sid,sname,gid) values(3,"ZhaoSi",2);
insert into student(sid,sname,gid) values(4,"HaoJuan",2);--如果要查询class 1有哪些人 首先要查询class 1的id 然后根据id在student表中查询该班级有哪些学生
select id from grade where name="class 1";--可以查出id=1 所以
select sname from student where sid=1;--可以查出WangHong和LiQiang

5.2.3 删除数据

--例如class 1取消 则需要删除该班级学生和该班级一起删除 由于grade和student之间有关联关系
--参照列被参照的值是不能被删除的 所以一定要先删除该班级的所有学生 然后在删除该班级 具体如下--将class 1班级同学全部删除
delete from student where sname="WangHong";
delete from student where sname="LiQiang";
--删除class 1
delete from grade where id=1;
--如果直接删除class 2 则会报错

5.3 连接查询

5.3.1 交叉连接

create table department(did int (4)not null primary key,dname varchar(36)
);
create table employee(id int(4)not null primary key,name varchar(36),age int(2),did int(4) not null
);
insert into department(did,dname)values(1,"网络部");
insert into department(did,dname)values(2,"媒体部");
insert into department(did,dname)values(3,"研发部");
insert into department(did,dname)values(5,"人事部");
insert into employee(id,name,age,did)values(1,"王红",20,1);
insert into employee(id,name,age,did)values(2,"李强",22,1);
insert into employee(id,name,age,did)values(3,"赵四",20,2);
insert into employee(id,name,age,did)values(4,"郝娟",20,4);
--交叉连接
select * from department cross join employee;

5.3.2 内连接

select 查询字段 from 表1 [inner] join 表2 on 表1.关系字段=表2.关系字段;
--inner join用于连接两个表，on用来指定连接条件 inner可以省略。
select employee.name,department.dname from department join employee on department.did=employee.did;select employee.name,department.dname from department,employee where department.did=employee.did;

(后面的未显示)

--如果在一个连接查询中 涉及的两个表是同一个表 这种查询被称为自连接查。自连接是一种特殊的内连接。
--例如要查询王红所在部门的有哪些员工就可以使用自连接。
select p1.* from employee p1 join employee p2 on p1.did=p2.did where p2.name="王红";

5.3.3 外连接

Syntax:select 所查字段 from 表1 left|right  [outer] join 表2 on 表1.关系字段=表2.关系字段 where 条件;
--LeftConnetion
select department.did,department.dname,employee.name from department left join employee on department.did=employee.did;+-----+--------+------+
| did | dname  | name |
+-----+--------+------+
|   1 | 网 络 部  | 王 红  |
|   1 | 网 络 部  | 李 强  |
|   2 | 媒 体 部  | 赵 四  |
|   3 | 研 发 部  | NULL |
|   5 | 人 事 部  | NULL |
+-----+--------+------+
5 rows in set (0.07 sec)--RightConnection
select department.did,department.dname,employee.name from department right join employee on department.did=employee.did;
+------+--------+------+
| did  | dname  | name |
+------+--------+------+
|    1 | 网 络 部  | 王 红  |
|    1 | 网 络 部  | 李 强  |
|    2 | 媒 体 部  | 赵 四  |
| NULL | NULL   | 郝 娟  |
+------+--------+------+
4 rows in set (0.00 sec)

5.3.4 复合条件连接查询

--在department和employee之间使用内连接查询，并将查询结果按照年龄从小到大排序
select employee.name,employee.age,department.dname from department join employee on department.did=employee.did order by age;

5.4 子查询

5.4.1 带in关键字的查询

--查询存在年龄为20岁的员工所在部门
select dname from department where did in(select did from employee where age=20);
+--------+
| dname  |
+--------+
| 网 络 部  |
| 媒 体 部  |
+--------+
2 rows in set (0.00 sec)--查询不存在年龄为20岁的员工所在部门
select dname from department where did not in(select did from employee where age=20);
+--------+
| dname  |
+--------+
| 研 发 部  |
| 人 事 部  |
+--------+
2 rows in set (0.00 sec)

5.4.2 带exists关键字的子查询

--查询employee表中是否存在年龄大于21岁的员工 如果存在 则查询 department表中的所有记录
select * from department where exists(select did from employee where age>21);
+-----+--------+
| did | dname  |
+-----+--------+
|   1 | 网 络 部  |
|   2 | 媒 体 部  |
|   3 | 研 发 部  |
|   5 | 人 事 部  |
+-----+--------+
4 rows in set (0.00 sec)

5.4.3 带any关键字的子查询

--any关键字表示满足其中任意一个条件 他允许创建一个表达式对子查询的返回值列表进行比较
--只要满足内层子查询中的任意一个比较条件 就返回一个结果作为外层查询条件
--使用带any的查询满足条件的部门
select * from department where did>any(select did from employee);
+-----+--------+
| did | dname  |
+-----+--------+
|   2 | 媒 体 部  |
|   3 | 研 发 部  |
|   5 | 人 事 部  |
+-----+--------+
3 rows in set (0.00 sec)

5.4.3 带all关键字的子查询

--all:返回的结果需要同时满足所有内层查询条件
--使用带all的查询满足条件的部门
select * from department where did>all(select did from employee);
+-----+--------+
| did | dname  |
+-----+--------+
|   5 | 人 事 部  |
+-----+--------+
1 row in set (0.00 sec)

5.4.4 带比较运算符的子查询

--使用比较运算符查询赵四是哪个部门的员工
select * from department where did=(select did from employee where name="赵四");

CH06 事务与存储过程

6.1 事务管理

--开启事务
start transaction;
--提交事务
commit;
--取消（回滚）事务 只可回滚未提交的事务
rollback;
--INIT
create table account(id int primary key auto_increment,name varchar(40),money float
);
insert into account(name,money) values('a',1000);
insert into account(name,money) values('b',1000);--首先开启一个事务 通过update将a账户的100转给b账户最后提交事务
start transaction;
update account set money=money-100 where name='a';
update account set money=money+100 where name='b';
commit;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+----+------+-------+
2 rows in set (0.00 sec)

6.1.1 事务的特性

1.原子性
事务是数据库的逻辑工作单位，事务中包含的各操作要么都做，要么都不做 。
2.一致性
事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。比如，当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。如果数据库系统在运行中发生故障，有些事务尚未完成就被迫中断，这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态，或者说是不一致的状态。
3.隔离性
一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。
4.持久性
指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。
事务中的所有操作要么全部执行，要么都不执行； 如果事务没有原子性的保证，那么在发生系统 故障的情况下，数据库就有可能处于不一致状态。 因而，事务的原子性与一致性是密切相关的。

6.1.2 事务的提交

--在上个例子中 a账户是900 b账户是1100
--如果我们此时输入
start transaction;
update account set money=money+100 where name='a';
update acount set money=money-100 where name='b';
--查询时会发现 此时a账户为1000 b账户为1000 但当我们退出数据库重新登录时
--查询结果是 a账户为900 b账户为1100 说明没有提交事务 且事务中的语句不能自动提交。
--如果我们输入的是
start transaction;
update account set money=money+100 where name='a';
update acount set money=money-100 where name='b';
commit;
--退出之后出现登录就会发现 a账户为1000 b账户为1000

6.1.3 事务的回滚

--举个例子 a=1000 b=1000
start transaction;
update account set money=money-100 where name='a';
update acount set money=money+100 where name='b';
--此时a=900 b=1100 由于事务没有提交 所以我们可以输入
rollback;
--此时 a=1000 b=1000

6.1.4事务的隔离级别

数据库事务的隔离级别有4种，由低到高分别为Read uncommitted 、Read committed 、Repeatable read 、Serializable 。而且，在事务的并发操作中可能会出现脏读，不可重复读，幻读。下面通过事例一一阐述它们的概念与联系。

1.Read uncommitted
读未提交，顾名思义，就是一个事务可以读取另一个未提交事务的数据。
事例：老板要给程序员发工资，程序员的工资是3.6万/月。但是发工资时老板不小心按错了数字，按成3.9万/月，该钱已经打到程序员的户口，但是事务还没有提交，就在这时，程序员去查看自己这个月的工资，发现比往常多了3千元，以为涨工资了非常高兴。但是老板及时发现了不对，马上回滚差点就提交了的事务，将数字改成3.6万再提交。
分析：实际程序员这个月的工资还是3.6万，但是程序员看到的是3.9万。他看到的是老板还没提交事务时的数据。这就是脏读。
那怎么解决脏读呢？Read committed！读提交，能解决脏读问题。

2.Read committed
读提交，顾名思义，就是一个事务要等另一个事务提交后才能读取数据。
事例：程序员拿着信用卡去享受生活（卡里当然是只有3.6万），当他埋单时（程序员事务开启），收费系统事先检测到他的卡里有3.6万，就在这个时候！！程序员的妻子要把钱全部转出充当家用，并提交。当收费系统准备扣款时，再检测卡里的金额，发现已经没钱了（第二次检测金额当然要等待妻子转出金额事务提交完）。程序员就会很郁闷，明明卡里是有钱的…
分析：这就是读提交，若有事务对数据进行更新（UPDATE）操作时，读操作事务要等待这个更新操作事务提交后才能读取数据，可以解决脏读问题。但在这个事例中，出现了一个事务范围内两个相同的查询却返回了不同数据，这就是不可重复读。
那怎么解决可能的不可重复读问题？Repeatable read ！

3.Repeatable read
重复读，就是在开始读取数据（事务开启）时，不再允许修改操作
事例：程序员拿着信用卡去享受生活（卡里当然是只有3.6万），当他埋单时（事务开启，不允许其他事务的UPDATE修改操作），收费系统事先检测到他的卡里有3.6万。这个时候他的妻子不能转出金额了。接下来收费系统就可以扣款了。
分析：重复读可以解决不可重复读问题。写到这里，应该明白的一点就是，不可重复读对应的是修改，即UPDATE操作。但是可能还会有幻读问题。因为幻读问题对应的是插入INSERT操作，而不是UPDATE操作。
什么时候会出现幻读？
事例：程序员某一天去消费，花了2千元，然后他的妻子去查看他今天的消费记录（全表扫描FTS，妻子事务开启），看到确实是花了2千元，就在这个时候，程序员花了1万买了一部电脑，即新增INSERT了一条消费记录，并提交。当妻子打印程序员的消费记录清单时（妻子事务提交），发现花了1.2万元，似乎出现了幻觉，这就是幻读。
那怎么解决幻读问题？Serializable！

4.Serializable 序列化
Serializable 是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。
值得一提的是：大多数数据库默认的事务隔离级别是Read committed，比如Sql Server , Oracle。Mysql的默认隔离级别是Repeatable read。

值得一提的是：大多数数据库默认的事务隔离级别是Read committed，比如Sql Server , Oracle。Mysql的默认隔离级别是Repeatable read。

set session transaction isolation level read uncommitted;
select @@tx_isolation;

1.脏读：
脏读又称无效数据读出。一个事务读取另外一个事务还没有提交的数据叫脏读。
例如：事务T1修改了一行数据，但是还没有提交，这时候事务T2读取了被事务T1修改后的数据，之后事务T1因为某种原因Rollback了，那么事务T2读取的数据就是脏的。
解决办法：把数据库的事务隔离级别调整到READ_COMMITTED

2.不可重复读：
不可重复读是指在同一个事务内，两个相同的查询返回了不同的结果。
例如：事务T1读取某一数据，事务T2读取并修改了该数据，T1为了对读取值进行检验而再次读取该数据，便得到了不同的结果。
解决办法：把数据库的事务隔离级别调整到REPEATABLE_READ
3.幻读：
例如：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样。这就叫幻读。

快速理解脏读，不可重复读，幻读https://blog.csdn.net/Vincent2014Linux/article/details/89669762