SQL 的四种连接查询
1.内连接
2.外连接
-左连接
-右连接
-完全外连接
创建两个表格
peson表格
–person表
id,name,cardID
create table person(
id int,
name varchar(20),
cardID int);
card表格
–card表
id ,name
create table card(
id int,
name varchar(20));
插入数据
card数据
insert into card values(1,’饭卡’);
insert into card values(2,’建行卡’);
insert into card values(3,’农行卡’);
insert into card values(4,’工商卡’);
insert into card values(5,’邮政卡’);
person数据
insert into person values(1,’张三’,1);
insert into person values(2,’李四’,3);
insert into person values(3,’王五’,6);
“连接”实战
select * from person;
+——+——–+——–+
| id | name | cardID |
+——+——–+——–+
| 1 | 张三 | 1 |
| 2 | 李四 | 3 |
| 3 | 王五 | 6 |
+——+——–+——–+
–并没有创建外键，
内连接
inner join
内联查询，就是两张表中的数据通过某个字段相对，查询出相关记录数据
– inner join 查询
select * from person inner join card on person.cardId=card.id;
+——+——–+——–+——+———–+
| id | name | cardID | id | name |
+——+——–+——–+——+———–+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 李四 | 3 | 3 | 农行卡 |
+——+——–+——–+——+———–+
外连接
1.左外连接
left join或者left outer join
select * from person left join card on person.cardId=card.id;
左外连接，会把左边表里面的所有数据取出来，而右边表中的数据，如果有相等的，就会显示出来
如果没有，就会补NULL
+——+——–+——–+——+———–+
| id | name | cardID | id | name |
+——+——–+——–+——+———–+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 李四 | 3 | 3 | 农行卡 |
| 3 | 王五 | 6 | NULL | NULL |
+——+——–+——–+——+———–+
2.右外连接
right或者right outer join
select * from person right join card on person.cardId=card.id;
右外连接，会把右边表里面的所有数据取出来，而左边表中的数据，如果有相等的，就显示出来
如果没有，就会补NULL
select * from person right outer join card on person.cardId=card.id;
+——+——–+——–+——+———–+
| id | name | cardID | id | name |
+——+——–+——–+——+———–+
| 1 | 张三 | 1 | 1 | 饭卡 |
| NULL | NULL | NULL | 2 | 建行卡 |
| 2 | 李四 | 3 | 3 | 农行卡 |
| NULL | NULL | NULL | 4 | 工商卡 |
| NULL | NULL | NULL | 5 | 邮政卡 |
+——+——–+——–+——+———–+
2.完全外连接
full join或者full outer join
select * from person full join card on person.cardId=card.id;
mysql> select * from person full join card on person.cardId=card.id;
ERROR 1054 (42S22): Unknown column ‘person.cardId’ in ‘on clause’
mysql不支持全外连接，原因不知，下节课再议
集合考虑：
全外连接=左连接union右连接
select * from person left join card on person.cardId=card.id
union
select * from person right join card on person.cardId=card.id;
+——+——–+——–+——+———–+
| id | name | cardID | id | name |
+——+——–+——–+——+———–+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 李四 | 3 | 3 | 农行卡 |
| 3 | 王五 | 6 | NULL | NULL |
| NULL | NULL | NULL | 2 | 建行卡 |
| NULL | NULL | NULL | 4 | 工商卡 |
| NULL | NULL | NULL | 5 | 邮政卡 |
+——+——–+——–+——+———–+
mysql事务
mysql中，事务其实是一个最小的不可分割的工作单元。事务能够保证一个业务的完整性
比如我们的银行转账：
a->100
update user set money =money-100 where name=’a’
b->
update user set money=money+100 where name=’b’
–实际的程序中，如果只有一条语句执行成功了，而另外一条没有执行成功？
–出现数据前后不一致
update user set money =money-100 where name=’a’
update user set money=money+100 where name=’b’
–多条sql语句，可能会有同时成功的要求，要么就同时失败
–mysql如何控制事务
mysql默认是开启事务的(自动提交)
mysql> select @@autocommit;
+————–+
| @@autocommit |
+————–+
| 1 |
+————–+
–默认事务开启的作用是什么？
–当我们区执行sql语句的时候，效果就会立即体现出来，且不能回滚
create database bank;
create table user(
id int primary key,
name varchar(20),
money int);
insert into user values(1,’a’,1000);
–事务回滚：撤销sql语句
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user;
+—-+——+——-+
| id | name | money |
+—-+——+——-+
| 1 | a | 1000 |
+—-+——+——-+
1 row in set (0.00 sec)
–设置自动提交为false
set @@autocommit=0
mysql> select @@autocommit;
+————–+
| @@autocommit |
+————–+
| 0 |
+————–+
–上面的操作关闭了mysql的自动提交（commit)
insert into user values(2,’b’,1000);
mysql> insert into user values(2,’b’,1000);
Query OK, 1 row affected (0.00 sec)
mysql> select * from user;
+—-+——+——-+
| id | name | money |
+—-+——+——-+
| 1 | a | 1000 |
| 2 | b | 1000 |
+—-+——+——-+
2 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user;
+—-+——+——-+
| id | name | money |
+—-+——+——-+
| 1 | a | 1000 |
+—-+——+——-+
1 row in set (0.00 sec)
–再一次插入数据
mysql> insert into user values(2,’b’,1000);
Query OK, 1 row affected (0.00 sec)
–手动提交数据
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from user;
+—-+——+——-+
| id | name | money |
+—-+——+——-+
| 1 | a | 1000 |
| 2 | b | 1000 |
+—-+——+——-+
2 rows in set (0.01 sec)
–再撤销，是不可以撤销的（持久性）
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user;
+—-+——+——-+
| id | name | money |
+—-+——+——-+
| 1 | a | 1000 |
| 2 | b | 1000 |
+—-+——+——-+
2 rows in set (0.00 sec)
–自动提交？
@@autocommit=1
–手动提交？
commit;
–事务回滚？
rollback;
–如果说这个时候转账
转账实际操作
如果要执行成功撤销操作 需要@@autocommit=0
mysql> set @@autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@autocommit;
+————–+
| @@autocommit |
+————–+
| 0 |
+————–+
update user set money =money+100 where name=’a’;
update user set money=money-100 where name=’b’;
mysql> select * from user;
+—-+——+——-+
| id | name | money |
+—-+——+——-+
| 1 | a | 1100 |
| 2 | b | 900 |
+—-+——+——-+
2 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from user;
+—-+——+——-+
| id | name | money |
+—-+——+——-+
| 1 | a | 1000 |
| 2 | b | 1000 |
+—-+——+——-+
–事务给我们提供了一个反悔的机会
begin;
–或者
start transaction;
–都可以帮我们手动开启一个事务
手动开启事务1
begin;
update user set money =money+100 where name=’a’;
update user set money=money-100 where name=’b’;
mysql> update user set money=money-100 where name=’b’;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update user set money =money+100 where name=’a’;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from user;
+—-+——+——-+
| id | name | money |
+—-+——+——-+
| 1 | a | 1200 |
| 2 | b | 800 |
+—-+——+——-+
2 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from user;
+—-+——+——-+
| id | name | money |
+—-+——+——-+
| 1 | a | 1100 |
| 2 | b | 900 |
+—-+——+——-+
####手动开启事务2
start transaction
update user set money =money+100 where name=’a’;
update user set money=money-100 where name=’b’;
与上述同理
begin
start transaction
这两条命令相当于小开关手动开启事务
@@autocommit属于总开关
事务的四大特性
A 原子性：事务是最小的单位，不可以再分割
B 一致性：事务要求，同一事务中的sql语句u，必须保证同时成功或者同时失败
C 隔离性：事务1和事务2 之间具有隔离性
D 持久性：事务一旦结束（commit，rollback），就不可以返回
事务开启：
1.修改默认提交 set @@autocommit=0;
2.begin
3.start transaction
事务手动提交：
commit;
事务手动回滚：
rollback;
事务的隔离性
1.read uncommitted;—读未提交的
2.read committed;—读已经提交的
3.repeatable read;—可以重复读
4.serializable;—-串行化
1-read uncommitted
如果存在事务a和事务b
a 事务对数据进行操作，在操作过程中，事务没有被提交，但b可以看见a
bank数据库user表
insert into user values(3,’小明’,1000);
insert into user values(4,’淘宝店’,1000);
mysql> select * from user;
+—-+———–+——-+
| id | name | money |
+—-+———–+——-+
| 1 | a | 1100 |
| 2 | b | 900 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
+—-+———–+——-+
–如何查看数据库的隔离级别
–系统级别
select @@global.transaction_isolation;
–会话级别
select @@transaction_isolation;
//查看数据库隔离级别需要退回到最初的databases
mysql> select @@global.transaction_isolation;
+——————————–+
| @@global.transaction_isolation |
+——————————–+
| REPEATABLE-READ |
+——————————–+
–如何修改隔离级别
set global transaction isolation level read uncommitted;
mysql> select @@global.transaction_isolation;
+——————————–+
| @@global.transaction_isolation |
+——————————–+
| READ-UNCOMMITTED |
+——————————–+
–转账： 小明在淘宝店买鞋子：800块
小明-》成都
淘宝点-》广州
start transaction;
update user set money=money-800 where name=’小明’;
update user set money=money+800 where name=’淘宝店’;
mysql> select * from user;
+—-+———–+——-+
| id | name | money |
+—-+———–+——-+
| 1 | a | 1100 |
| 2 | b | 900 |
| 3 | 小明 | 200 |
| 4 | 淘宝店 | 1800 |
+—-+———–+——-+
–给淘宝店打电话，说你去查一下是不是到账了
–淘宝店在广州查账
mysql> select * from user;
+—-+———–+——-+
| id | name | money |
+—-+———–+——-+
| 1 | a | 1100 |
| 2 | b | 900 |
| 3 | 小明 | 200 |
| 4 | 淘宝店 | 1800 |
+—-+———–+——-+
–发货
–晚上请女朋友吃好吃的（呸！）
–1800
–小明-》成都
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user;
+—-+———–+——-+
| id | name | money |
+—-+———–+——-+
| 1 | a | 1100 |
| 2 | b | 900 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
+—-+———–+——-+
–结帐的时候发现钱不够
mysql> select * from user;
+—-+———–+——-+
| id | name | money |
+—-+———–+——-+
| 1 | a | 1100 |
| 2 | b | 900 |
| 3 | 小明 | 200 |
| 4 | 淘宝店 | 1800 |
+—-+———–+——-+
–如果两个不同的地方都在进行操作，如果事务a开启以后，他的数据可以被其他事务读取到
–这样就会出现“脏读”
–脏读：一个事务读到另外一个事务没有提交的数据，就叫做脏读
–实际开发是不允许脏读出现的
2.read committed; 读已经提交的
set global transaction isolation level read committed;
bank 数据库 user表
小张：银行的会计
start transaction;
select * from user;
+—-+———–+——-+
| id | name | money |
+—-+———–+——-+
| 1 | a | 1100 |
| 2 | b | 900 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
+—-+———–+——-+
小张出去了。
小王：
start transaction;
insert into user values(5,’c’,100);
–小张回来了
select avg(money) from user;
mysql> select avg(money) from user;
+————+
| avg(money) |
+————+
| 820.0000 |
+————+
–money的平均值不是1000，变少了？
–虽然我只能读到另一个事务提交的数据，但还是会出现问题，就是
–读取同一个表中的数据，发现前后不一致
–不可重复读现象：read committed
3.repeatable read
可以重复读，数据库默认也是这个
set global transaction isolation level repeatable read;
–在repeatable-read隔离级别下会出现什么问题
select * from user;
mysql> select * from user;
+—-+———–+——-+
| id | name | money |
+—-+———–+——-+
| 1 | a | 1100 |
| 2 | b | 900 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
| 5 | c | 100 |
+—-+———–+——-+
–张全蛋-成都
start transaction;
–王尼玛-北京
start transaction;
–张全蛋-成都
insert into user values(6,’d’,1000);
mysql> select * from user;
+—-+———–+——-+
| id | name | money |
+—-+———–+——-+
| 1 | a | 1100 |
| 2 | b | 900 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
| 5 | c | 100 |
| 6 | d | 1000 |
+—-+———–+——-+
–王尼玛-北京
insert into user values(6,’d’,1000);
mysql> select * from user;
+—-+———–+——-+
| id | name | money |
+—-+———–+——-+
| 1 | a | 1100 |
| 2 | b | 900 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
| 5 | c | 100 |
+—-+———–+——-+
mysql> insert into user values(6,’d’,1000);
ERROR 1062 (23000): Duplicate entry ‘6’ for key ‘user.PRIMARY’
–这种现象叫做幻读
–事务a和事务b同时操作一张表，事务a提交的数据，也不能被事务b读到
意思就是两人操作同一个表格的时候，操作的数据并不会实时更新，所以王尼玛当时看不到d的数据，
如果重新查看可以查看到。在不知道d的数据已经被插入的情况下再次插入d的数据就会出现错误!
4.serializable read
//
这个示例如果想实际操作演示的话，需要同时开启两个终端窗口
然后进行视频中的操作
//
串行化
set global transaction isolation level serializable;
修改成功
mysql> select @@global.transaction_isolation;
+——————————–+
| @@global.transaction_isolation |
+——————————–+
| SERIALIZABLE |
+——————————–+
mysql> select * from user;
+—-+———–+——-+
| id | name | money |
+—-+———–+——-+
| 1 | a | 1100 |
| 2 | b | 900 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
| 5 | c | 100 |
| 6 | d | 1000 |
+—-+———–+——-+
–小张-成都
start transaction;
insert into user values(7,’小赵’,1000);
mysql> select * from user;
+—-+———–+——-+
| id | name | money |
+—-+———–+——-+
| 1 | a | 1100 |
| 2 | b | 900 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
| 5 | c | 100 |
| 6 | d | 1000 |
| 7 | 小赵 | 1000 |
+—-+———–+——-+
–小王-北京
–小王查询信息发现小赵的信息已经存在
mysql> select * from user;
+—-+———–+——-+
| id | name | money |
+—-+———–+——-+
| 1 | a | 1100 |
| 2 | b | 900 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
| 5 | c | 100 |
| 6 | d | 1000 |
| 7 | 小赵 | 1000 |
+—-+———–+——-+
–小张
start transaction;
insert into user values(8,’小王’,1000);
–sql语句卡住？
这个时候小张进行操作想要输入新的数据发现卡住
–当user表被另外一个事务操作的时候，其他事务写操作，是不可以被进行的
（银行效率低的原因发现！！）
–进入排队状态（串行化），指导小王那边的事务结束之后（commit）之后
小张的写入操作才会执行
–在没有等待超时的情况下。
小王：
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
小张：
Query OK, 1 row affected (25.70 sec)
mysql>
mysql>
mysql>
mysql>
mysql>
–串行化问题是，性能特差
read—committed>read committed>repeatable read>serializable;
–隔离级别越高，性能越查
mysql语句默认隔离级别是repeatable
完结散花！！！
小希同学加油！
正好一周
2021.2.7
