mysql连接查询

2022-10-23 13:01:06

SQL的四种连接方式

内连接
inner join 或者join

外连接

  1. 左连接 left join 或者 left outer join
  2. 右连接 right join 或者right outer join
  3. 完全外连接 full join 或者 full outer join 
-- 创建两个表：
create database testJoin;

-- person 表
-- id、name、cardId
create table person(
	id int,
	name varchar(20),
	cardId int
);
insert into person values(1,'张三',1);
insert into person values(2,'李四',3);
insert into person values(3,'王五',6);

-- card表
-- id、name
create table card(
	id int,
	name varchar(20)
);
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,'邮政卡');
-- 并没有创建外键

mysql连接查询

mysql连接查询

-- 1.inner join 查询（内连接）

-- 内联查询，其实就是两张表中的数据，通过某个相同字段，查询出相关记录数据

mysql> select * from person inner join card on person.cardId=card.id;

mysql连接查询

-- 2.left join(左外连接)

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

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

mysql连接查询

-- 3.right join(右外连接)

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

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

mysql连接查询

-- 4.full join(全外连接)

mysql> select * from person full join card on person.cardId=card.id;
-- mysql不支持full join,使用左连接union右连接实现
mysql> select * from person left join card on person.cardId=card.id
    -> union
    -> select * from person right join card on person.cardId=card.id;

mysql连接查询

MySQL事务

-- mysql事务
-- 在mysql中，事务其实是一个最小的不可分割的工作单元，事务能够保证一个业务的完整性。
-- 比如在银行转账：
 	a-> -100
 	update user set money=money-100 where name='a';
 	b-> +100
 	update user set money=money+100 where name='b';

-- 实际程序中，如果只有一条语句执行成功，而另一条没有执行成功，就会出现数据前后不一致
-- 多条sql语句，可能会有要么同时成功，要么就同时失败的要求

-- 事务的四大特征(ACID)
A 原子性:事务是最小的单位，不可再分割
C 一致性:事务执行之前和执行之后都必须处于一致性状态，要么同时成功要么同时失败
I 隔离性:事务1 和 事务2 之间是具有隔离性的
D 持久性:事务一旦结束(commit，rollback)，就不可以返回

持久性

-- 持久性是指一个事务一旦被提交，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

-- mysql默认是开启事务的(默认自动提交)。

mysql> select @@autocommit;

mysql连接查询

-- 默认事务开启的作用：当我们去执行一个sql语句的时候，效果会立即体现出来，且不能回滚
-- 事务回滚(rollback)：撤销sql语句执行效果

mysql> create database bank;
mysql> create table user(
    -> id int primary key,
    -> name varchar(20),
    -> money int
    -> );
mysql> insert into user values(1,'a',1000)
mysql> rollback;	-- 默认无法回滚,数据会自动提交

mysql连接查询

-- 关闭mysql的自动提交(commit)

mysql> set autocommit=0;
mysql> select @@autocommit;

mysql连接查询

mysql> insert into user values(2,'b',1000);
mysql> select * from user;

mysql连接查询

mysql> rollback;	-- 回滚
mysql> select * from user;

mysql连接查询

mysql> insert into user values(2,'b',1000);	-- 再次添加数据
mysql> commit;	-- 手动提交数据(持久性)
mysql> rollback;	-- 不可回滚(持久性)
mysql> select * from user;

mysql连接查询

自动提交 @@autocommit=1;

手动提交 commit;

事务回滚 rollback;

事务给我们提供了一个返回的机会。

使用 begin; 或 start transaction; 都可以帮我们手动开启一个事务

mysql> set autocommit=1;	-- 打开自动提交
mysql> select @@autocommit;

mysql连接查询

手动开启事务（1） begin;

mysql> begin;
mysql> update user set money=money-100 where name='a';
mysql> update user set money=money+100 where name='b';
mysql> select * from user;

mysql连接查询

mysql> rollback;
mysql> select * from user;

mysql连接查询

手动开启事务（2） start transaction;

mysql> start transaction;
mysql> update user set money=money-100 where name='a';
mysql> update user set money=money+100 where name='b';
mysql> select * from user;

mysql连接查询

mysql> rollback;
mysql> select * from user;

mysql连接查询

事务开启之后，一旦commit提交，就不能回滚，事务在提交的时候就结束了。

隔离性

-- 隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离
-- 数据库为事务提供了多种隔离级别：
1.read uncommitted;		读未提交的（脏读）
2.read committed;		读已经提交的（不可重复读）
3.repeatable read;		可以重复读（幻读）
4.serializable;			串行化

数据库的隔离级别：

-- 查看mysql默认隔离级别
mysql> select @@global.transaction_isolation;	-- 系统级别
mysql> select @@transaction_isolation;			-- 会话级别
-- 修改mysql隔离级别
mysql> set [ global.transaction_isolation | transaction_isolation ]='隔离级别';
mysql> set  [glogal | session]  transaction isolation level [隔离级别]

mysql连接查询

1、脏读

如果有事务a和事务b

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

-- 情形模拟
mysql> insert into user values(3,'小明',1000);
mysql> insert into user values(4,'淘宝店',1000);
mysql> select * from user;

mysql连接查询

-- 打开两个命令行窗口
-- 修改隔离级别
mysql> set session transaction isolation level read uncommitted;
mysql> select @@transaction_isolation;

mysql连接查询

-- 转账：小明在淘宝店买鞋子：800块钱
-- 小明->成都ATM
-- 淘宝店->广州ATM

mysql> start transaction;
mysql> update user set money=money-800 where name='小明';
mysql> update user set money=money+800 where name='淘宝店';
mysql> select * from user;

mysql连接查询

-- 给淘宝店打电话，说你去查一下，是不是到账了
-- 淘宝店在广州查账
mysql> select * from user;
-- 发货,晚上消费1800

mysql连接查询

-- 小明->成都
mysql> rollback;
mysql> select * from user;

mysql连接查询

-- 淘宝店->结账发现钱不够
mysql> select * from user;

mysql连接查询

-- 脏读：一个事务读到了另外一个事务没有提交的数据
-- 如果两个不同的对方都在进行操作,如果事务a开启之后,他的数据可以被其他事务读取到,这样就会出现脏读,只要该事务不提交，则所有操作都将回滚
-- 实际开发中是不允许脏读出现的

2、不可重复读

-- 不可重复读(read committed)是指在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交

-- 打开两个命令行窗口
-- 修改隔离级别为 READ-COMMITTED
mysql> set session transaction isolation level read committed;	-- 会话级别
mysql> select @@transaction_isolation;

mysql连接查询

-- 小张开启事务：
mysql> start transaction;
mysql> select * from user;
-- 出门上厕所

mysql连接查询

-- 小王开启事务：
mysql> start transaction;
mysql> insert into user values(5,'c',100);
mysql> commit;
mysql> select * from user;

mysql连接查询

-- 小张上完厕所回来
mysql> select avg(money) from user;

mysql连接查询

-- money的平均不是1000，变少了？
-- 不可重复读和脏读的区别，脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据

-- 小张再次核对数据
mysql> select * from user;

mysql连接查询

3、幻读

-- 打开两个命令行界面
-- 修改隔离级别为 repeatable read

mysql> set global transaction isolation level repeatable read;	-- 系统级别
mysql> select @@global.transaction_isolation;
mysql> select @@transaction_isolation;

mysql连接查询

-- 小明开启事务：
mysql> start transaction;
mysql> select * from user;

mysql连接查询

-- 小红开启事务：
mysql> start transaction;
mysql> insert into user values(6,'d',1000);
mysql> commit;
mysql> select * from user;

mysql连接查询

-- 小明添加数据
mysql> insert into user values(6,'d',1000);
1062 - Duplicate entry '6' for key 'user.PRIMARY'	-- 失败
-- 查看列表
mysql> select * from user;

mysql连接查询

这种现象就叫幻读

事务a和事务b同时操作同一张表，事务a提交数据，无法被事务b读到，也就造成了幻读。

-- 小明结束事务
mysql> commit;
-- 再次查看列表
mysql> select * from user;

mysql连接查询

4、串行化

-- 打开两个命令行窗口
-- 修改隔离级别为串行化
mysql> set session transaction isolation level serializable;
mysql> select @@transaction_isolation;
mysql> select * from user;

mysql连接查询

-- 小明开启事务
mysql> start transaction;
-- 小红开启事务
mysql> start transaction;
-- 小明插入数据
mysql> insert into user values(7,'张三',1000);
mysql> commit;
mysql> select * from user;

mysql连接查询

-- 小红查看列表
mysql> select * from user;

mysql连接查询

-- 小明再次开启事务
mysql> start transaction;
mysql> insert into user values(8,'李四',1000);
1205 - Lock wait timeout exceeded; try restarting transaction
-- sql语句被卡住了?
-- 当user表被另外一个事务操作的时候，其他事务里面的写操作，是不可以进行的
-- 进入排队状态(串行化),直到小红那边的事务结束之后，小明的这个写操作才会执行。

mysql连接查询

-- 小红结束事务
mysql> commit;
-- 小明继续插入数据
mysql> insert into user values(8,'李四',1000);
mysql> commit;
mysql> select * from user;

mysql连接查询

-- 总结：
-- 串行化的问题是,性能特差！！
-- 性能排行：
READ-UNCOMMITTED > READ-COMMITTED > REPEATABLE-READ > SERIALIZABLE
-- 隔离级别越高,性能越差
-- mysql 默认隔离级别为 REPEATABLE-READ
上一篇：代理模式实例


下一篇：死磕Spring之AOP篇 - Spring 事务详解