MySQL学习笔记数据库学习【三】

一. SQL的四种连接查询

内连接：
inner join 或者 join
外连接：
a、左连接：left join 或者 left outer join
b、右连接：right join 或者 right outer join
c、完全外连接：full join 或者 full outer join

举例说明：，先创建一个数据库，再创建2个表
person表
id，name，cardid（来自于card表）
card表
id，name

mysql> create table person(
    -> id int,
    -> name varchar(20),
    -> cardid int
    -> );
Query OK, 0 rows affected (0.94 sec)

mysql> create table card(
    -> id int,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.76 sec)

表中添加数据：

mysql> select * from card;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | 饭卡      |
|    2 | 建行卡    |
|    3 | 农行卡    |
|    4 | 工商卡    |
|    5 | 邮政卡    |
+------+-----------+
5 rows in set (0.00 sec)

mysql> select * from person;
+------+--------+--------+
| id   | name   | cardid |
+------+--------+--------+
|    1 | 张三   |      1 |
|    2 | 李四   |      3 |
|    3 | 王五   |      6 |
+------+--------+--------+
3 rows in set (0.00 sec)

我们没有创建外键，如果创建外键了，王五的6是不能插入进去的。
但是这两个表之间是有外键关系的，下面我们进行连接查询：

1、inner join 内连接查询

对person和card表进行连接查询，加上条件

mysql> select * from person inner join card on person.cardid=card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardid | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
+------+--------+--------+------+-----------+
2 rows in set (0.00 sec)

内连接查询就是两张表中的数据，通过某个字段相等，查询出相关记录数据，用 on… 表示条件，其中的inner join 可以用 join代替。

2、left join （左外连接）

mysql> select * from person left join card on person.cardid=card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardid | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
|    3 | 王五   |      6 | NULL | NULL      |
+------+--------+--------+------+-----------+
3 rows in set (0.00 sec)

左外连接，会把左边表里面的所有数据取出来，而右边表数据如果有相等的，就显示出来，如果没有，就补 NULL

这里王五的cardid为6，在card表中没有对应，所有用NULL补上了
这里的语句,也可以用如下语句替代：left join = left outer join

mysql> select * from person left outer join card on person.cardid=card.id;

3、right join（右外连接）

mysql> select * from person right join card on person.cardid=card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardid | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
| NULL | NULL   |   NULL |    2 | 建行卡    |
| NULL | NULL   |   NULL |    4 | 工商卡    |
| NULL | NULL   |   NULL |    5 | 邮政卡    |
+------+--------+--------+------+-----------+
5 rows in set (0.00 sec)

与左外连接类似**，右外连接会把右边表里面的所有数据取出来**，而左边表数据如果有相等的，就显示出来，如果没有，就补 NULL

上面的2、4、5都没有相等，补上NULL，6因为右边card本来就没有就不显示了
这里的语句,也可以用如下语句替代：right join = right outer join

mysql> select * from person right outer join card on person.cardid=card.id;

4、full join（全外连接）

mysql> select * from person full join card on person.cardid=card.id;报错
ERROR 1054 (42S22): Unknown column 'person.cardid' in 'on clause'
这样会报错，原因是mysql 不支持 full join


全连接等于左连接和右连接合在一起的结果

mysql>  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 | 邮政卡    |
+------+--------+--------+------+-----------+
6 rows in set (0.01 sec)

二. 事务

在mysql中，事务其实是一个最小的不可分割的工作单元，事务能够保证一个业务的完整性。

比如我们的银行转账:
　　　a给b转100块钱，相当于
　　update user set monry=money-100 where name='a'
　　update user set monry=money+100 where name='b'
需要两条语句，在实际情况中，如果只有一条语句执行成功，而另一条没有执行成功，就会出现数据前后不一致的情况。

事务用来解决：多条sql语句可能会有同时成功的要求，要么就同时失败。

1、mysql如何控制事务？

在 MySQL 中，事务的自动提交状态默认是开启的。

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.52 sec)

那么，默认事务开启的作用是什么？
当我们去执行一个sql语句的时候，效果会立即体现出来，且不能回滚。

什么是回滚？举个例子：

mysql> create database bank;
Query OK, 1 row affected (0.64 sec)

mysql> use bank;
Database changed

mysql> create table user(
    -> id int primary key,
    -> name varchar(20),
    -> money int
    -> );
Query OK, 0 rows affected (0.93 sec)
mysql> insert into user values(1,'a',1000);
Query OK, 1 row affected (0.51 sec)

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+
1 row in set (0.00 sec)

可以看到，在执行插入语句后数据立刻生效，原因是 MySQL 中的事务自动将它提交到了数据库中。那么所谓回滚的意思就是，撤销执行过的所有 SQL 语句，使其回滚到最后一次提交数据时的状态。

事务回滚：撤销sql的执行效果 （rollback;）
下面调用rollback

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)

可以看到，即使调用了rollback，数据依然存在，说明是不能够回滚的，添加数据的语句执行完后默认就提交了。

我们可以通过设置默认事务为0的方式来进行改变，也就是设置mysql默认提交为false set autocommit=0;
(1为开启，0为关闭)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.54 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

上面的操作，关闭了mysql的自动提交（commit）
这里在操作一遍来验证：（这时候自动提交已经关闭了）

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.07 sec)

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+
1 row in set (0.00 sec)

刚才插入的数据没有了。我们在执行完语句时（关闭默认提交），看到的效果只是一个临时效果，并没有真实的发生在我们数据库里面，是在一张虚拟的表中。
这里可以用commit提交

#再插入一次数据
mysql> insert into user values(2,'b',1000);
Query OK, 1 row affected (0.00 sec)

#手动提交数据
mysql> commit;
Query OK, 0 rows affected (0.15 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)

只要commit了，rollback也没有用了.

自动提交： @@autocommit=1
手动提交： commit
回滚： rollback（在没有提交的情况下，是可以提交的）
也就是说，事务给我们提供了一个返回的机会！！！

2. 事务的其他打开方式？

手动开启事务：
begin；或者 start transaction；都可以帮我们手动开启一个事务

mysql> update user set money=money-100 where name='a';
Query OK, 1 row affected (0.95 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update user set money=money+100 where name='b';
Query OK, 1 row affected (0.14 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+----+------+-------+
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    |   900 |
|  2 | b    |  1100 |
+----+------+-------+
2 rows in set (0.00 sec)

这里发现现在rollback是没有任何效果的，因为现在autocommit=1，为自动提交模式，每当执行一句话就立即生效了。
现在在输入这两个语句之前，输入begin；或者 start transaction；

#加上begin；或者 start transaction 开启事务
mysql> begin;
Query OK, 0 rows affected (0.01 sec)

#对a、b进行转账操作
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> 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> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   800 |
|  2 | b    |  1200 |
+----+------+-------+
2 rows in set (0.00 sec)

#事务回滚
mysql> rollback;
Query OK, 0 rows affected (0.57 sec)

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+----+------+-------+
2 rows in set (0.00 sec)

事务开启后，一旦 commit 提交，就不可回滚（也就是这个事务在提交的时候结束了），此时再rollback也没有用了

小结：
一. 事务的四大特征：
ACID
A 、原子性：事务是最小的单位，不可以再分割。
C、一致性：事务要求，同一事务sql语句，必须保证同时成功或者同时失败
I、隔离性：事务1和事务2之间是具有隔离性的
只有隔离性没有讲，下面会有
D、持久性：事务一旦结束，就不可以返回
二、事务开启
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; #读未提交的
2、read committed; #读已经提交的
3、repeatable read；#可以重复读
4、serializable；# 串行化

1. read uncommitted; #读未提交的

如果有事务a和事务b，
a 事务对数据进行操作，在操作过程中事务没有被提交，但是b可以看见a操作的结果。

下面举个例子：
小明去淘宝店买800块钱的鞋子，要转账给淘宝店

mysql> select * from user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   800 |
|  2 | b         |  1200 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
+----+-----------+-------+
4 rows in set (0.00 sec)

如何查看数据库的隔离级别？
mysql8.0查看方法：
select @@global.transaction_isolation; #系统级别的
select @@transaction_isolation; #会话级别的

如何修改隔离级别？
set global transaction isolation level read uncommitted;

#修改到可以读未提交的
mysql> set global transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| READ-UNCOMMITTED               |
+--------------------------------+
1 row in set (0.00 sec)

小明开始进行转账：

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update user set money=money-800 where name='小明';
Query OK, 1 row affected (0.40 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update user set money=money+800 where name='淘宝店';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   800 |
|  2 | b         |  1200 |
|  3 | 小明      |   200 |
|  4 | 淘宝店    |  1800 |
+----+-----------+-------+
4 rows in set (0.00 sec)

然后小明给淘宝店打电话，让他们去看一下钱是否到账了，这时候打开另外一个终端去查user表(相当于淘宝店在另一个ATM机子上查询)

mysql> use bank;
Database changed
mysql> select * from user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   800 |
|  2 | b         |  1200 |
|  3 | 小明      |   200 |
|  4 | 淘宝店    |  1800 |
+----+-----------+-------+
4 rows in set (0.00 sec)

发现钱确实到账了，然后发货。
淘宝店主晚上请女朋友吃饭，花了1800，结账的时候钱不够。为什么呢？
因为小明在他的ATM上进行了 rollback（相当于在第一个终端上操作）

mysql> rollback;
Query OK, 0 rows affected (0.53 sec)

mysql> select * from user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   800 |
|  2 | b         |  1200 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
+----+-----------+-------+
4 rows in set (0.00 sec)

这样一下钱就回来了。
淘宝店主在他的ATM机子上又查了一下（相当于第二个终端）

mysql> select * from user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   800 |
|  2 | b         |  1200 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
+----+-----------+-------+
4 rows in set (0.00 sec)

如果两个不同的地方，都在进行操作，如果事务a开启后，他的数据可以被其他事务读取到，这样就会出现 （脏读 ）

脏读：一个事务读到了另一个事务没有提交的数据，就叫做脏读。
在实际开发中是不允许出现的

2、read committed; #读已经提交的

我们首先修改隔离级别为read committed
set global transaction isolation level read committed;
再查看：

mysql> select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| READ-COMMITTED                 |
+--------------------------------+
1 row in set (0.00 sec)

我们还是举个例子：
小张是银行的会计，他查看资产：

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   800 |
|  2 | b         |  1200 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
+----+-----------+-------+
4 rows in set (0.00 sec)

然后去上厕所了，此时小王干了一件事：(再打开一个终端)

mysql> use bank;
Database changed
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into user values(5,'c',100);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.15 sec)

mysql> select * from user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   800 |
|  2 | b         |  1200 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
|  5 | c         |   100 |
+----+-----------+-------+
5 rows in set (0.00 sec)

此时，小张上完厕所抽完烟，开始计算存款的平均数：

mysql> select avg(money) from user;
+------------+
| avg(money) |
+------------+
|   820.0000 |
+------------+
1 row in set (0.00 sec)

小张就会觉着这怎么可能是820呢？？明明是1000啊

虽然我只能读到已经提交的数据，但还是会出现问题就是读取一个表的数据，发现前后不一致。

在read committed情况下会出现不可重复读现象
不可重复读(虚读): 指一个线程中的事务读取到了另外一个线程中提交的update的数据，导致了产生不同于刚才的结果。

3、repeatable read；#可以重复读

老规矩，修改隔离级别

mysql> set global transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| REPEATABLE-READ                |
+--------------------------------+
1 row in set (0.00 sec)

有张全蛋和王尼玛两个人，在两个地方分别进行操作（两个终端）
在张全蛋的终端上开始事务：

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

王尼玛在另一个终端上开始事务：

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

然后张全蛋插入了一条数据：

mysql> insert into user values(6,'d',1000);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.47 sec)
# 都已经commit了，但是另一方仍然没有查看到这个信息。

但是王尼玛并没有查到这条记录,所以他就添加6的信息，就会报错，说6号已经存在，但是王尼玛又查不到，这种现象叫做幻读。

事务a和事务b同时操作一张表，事务a提交的数据也不能被事务b看到，就会造成幻读！！

4、serializable；# 串行化

老规矩，修改隔离级别：

mysql> set global transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| SERIALIZABLE                   |
+--------------------------------+
1 row in set (0.00 sec)

当表被另一个事务操作的时候，其他事务里的写操作时不可进行的。进入排队状态，直到另一个事务结束之后，也就是被提交以后才能让其他的事务进行操作，其他事务的写入操作才会执行（在没有超时的情况下）。
串行口的问题是，性能太差！

隔离级别越高，性能越差:
read uncommitted > read committed > repeatable read > serializable
mysql默认隔离级别是repeatable read