mysql设计概念及多表查询和事务操作

2.显示内连接

显示内连接:使用INNER JOIN...ON语句,可以省略INNER关键字

-- 语法核心select * from 表名1 inner join 表名2 on 条件;-- 或者select * from 表名1 join 表名2 on 条件;

3.隐式内连接

看不到JOIN关键字,条件使用WHERE指定

select 列名,列名,... from 表名1,表名2 where 表名1.列名=表名2.列名;

4.示例

查询水果的价格

-- 隐式内连接select * from fruit,price where fruit.price_id=price.id;-- 显式内连接select * from fruit inner join price on fruit.price_id=price.id;

在这里插入图片描述
查询苹果的信息,显示苹果的id,名字,价格

-- 方式1select fruit.id, fruit.name, price.pricefrom fruit,      pricewhere fruit.price_id = price.id  and fruit.name = '苹果';-- 方式2select fruit.id, fruit.name, price.pricefrom fruit         inner join      price     on fruit.price_id = price.id         and fruit.name = '苹果';

在这里插入图片描述
5.总结

1.内连接作用?

  • 过滤笛卡尔积

  • 获取两表的交集部分(都满足条件的部分)

2.什么是隐式内连接和显示内连接?

  • 隐式内连接:看不到JOIN:select 列名,列名....from 表名1,表名2 where 表名1.列名=表名2.列名;
  • 显示内连接:看得到JOIN:select * from 表名1 inner join 表名2 on 条件;

3.内连接查询步骤?

  • 1)确定查询几张表
  • 2)确定表连接条件
  • 3)根据需要在操作

外连接查询

1.左外连接

  • 左表的记录全部显示出来
  • 外表只会显示符合搜索条件的记录

语法格式:

select * from 表1 left [outer] join 表2 on 条件;

说明:

  • left关键字左边的表定义为左表left关键字右边的表定义为右表,查询的内容以左表为主
  • 如果左表有数据,而右表没有数据对应的数据,仍然会把左表数据进行显示
  • outer关键字可以省略

练习:

不管能否查到水果对应价格,都要把水果显示出来

-- 左外连接查询select * from fruit left outer join price on fruit.price_id=price.id;

在这里插入图片描述
2.右外连接

  • 右表的记录全部表示出来
  • 左表只会显示符合搜索条件的记录

语法格式:

select * from 表名1 right [outer] join 表名2 on 条件;

说明:

  • right关键字左边的表定义为左表,right关键字右边的表定义为右表,查询的内容以右表为主
  • 如果右表没有数据,而左表没有对应的数据,仍然会把右表数据进行显示
  • outer关键字可以省略

练习:

不管能否查到价格对应的水果,都要把价格显示出来

select * from fruit right outer join price on fruit.price_id=price.id;

在这里插入图片描述
总结:

1.掌握左外连接查询格式?

  • select * from 表1 left outer join 表2 on 条件;
  • 表1看作为左表,表2看做为右表

2.左外连接查询特点?

  • 在满足要求的基础上保证左表的数据全部显示

3.掌握右外连接查询格式?

  • select * from 表1 right outer join 表2 on 条件;

4.右外连接查询特点?

  • 在满足要求的基础上,保证右表的数据全部显示

嵌套查询(子查询)

1.什么是子查询

一条查询语句结果作为另一条查询语法一部分。

SELECT 查询字段 FROM 表 WHERE 条件;举例:SELECT * FROM employee WHERE salary=(SELECT MAX(salary) FROM employee);

说明:子查询需要放在()中

三种子查询情况:单行单列多行单列多行多列

2.单行单列

子查询结果是单列,在WHERE后面作为条件

SELECT 查询字段 FROM 表 WHERE 字段=(子查询);

通常使用比较运算符: >>=<<==

3.多行单列

子查询结果是多行单列,结果集类似于一个数组,在WHERE后面作为条件,父查询使用IN运算符

-- IN表示在数值中SELECT 查询字段 FROM 表 WHERE 字段 IN (子查询);

4.多行多列

子查询结果是多列,在FROM后面作为

SELECT 查询字段 FROM (子查询) 表别名 WHERE 条件;

注意:子查询作为表需要取别名,使用as,可以省略,否则这张表没用名称无法访问表中的字段

事务操作

事务的概念

什么是事务

在实际的业务开发中,有些业务操作要多次访问数据库。一个业务要发送多条SQL语句给数据库执行。需要将多次访问数据库的操作视为一个整体来执行,要么所有的SQL语句全部执行成功。如果其中有一条SQL语句失败,就进行事务的回滚,所有的SQL语句全部执行失败。

简而言之,事务指的是逻辑上的一组操作,组成这组操作的各个单元要么全都成功,要么全都失败。

事务作用:保证在一个事务中多次操作数据库表中数据时,要么全都成功,要么全都失败。

事务的应用场景声明

关于事务在实际中的应用场景:

假设我在淘宝买了一部手机,然后当我付完款,钱已经从我的账户中扣除。正当此时,淘宝转账系统宕机了,那么此时淘宝还没有收到钱,而我的账户的钱已经减少了,这样就会导致我作为买家钱已经付过,而卖家还没有收到钱,他们不会发货物给我。这样做显然是不合理。实际生活中是如果淘宝出问题,作为用户的账户中钱是不应该减少的。这样用户就不会损失钱。

还有种情况,就是当我付完款之后,卖家看到我付款成功,然后直接发货了,我如果有权限操作,我可以撤销,这样就会导致我的钱没有减少,但是卖家已经发货,同样这种问题在实际生活中也是不允许出现的。

MySQL中可以有两种方式进行事务的操作:

  • 手动提交事物:先开启,在提交
  • 自动提交事物(默认的):即执行一条sql语句提交一次事物

数据准备

# 创建账号表create table account(  id int primary key auto_increment,  name varchar(20),  money double);# 初始化数据insert into account values (null,'a',1000);insert into account values (null,'b',1000);

手动提交事务

手动提交事务有关的sql语句

SQL语句 描述
start transaction 开启手动控制事物
commit 提交事物
rollback 回滚事物

手动提交事务使用步骤

  • 开启事务–>执行SQL语句–>成功–>提交事务
  • 开启事务–>执行SQL语句–>失败–>回滚事务

在这里插入图片描述
演示案例:演示提交事务,a给b转账100元

-- 1.开启事务start transaction;-- 2.执行sql语句update account set money=money-100 where name='a';update account set money=money+100 where name='b';-- 3.提交事务commit;

在这里插入图片描述
案例演示:演示回滚事务,a给b转账100元

-- 1.开启事务start transaction;-- 2.执行sql语句update account set money=money-100 where name='a';update account set money=money+100 where name='b';-- 3.回滚事务rollback;

在这里插入图片描述
注意:

  • 提交事务(commit) :事务提交之后,sql语句对数据库产生的操作才会被永久的保存
  • 事务的回滚(rollback):撤销已经成功执行的sql语句,回到开启事务之前的状态
  • 只要提交事务,那么数据就会长久保存了,就不能回滚事务了。即提交或者回滚事务都是代表结束当前事务的操作

自动提交事务

MySQL的每一条DML(增删改)语句都是一个单独的事务,每条语句都会自动开启一个事务,执行完毕自动提交事务,MySQL默认开始自动提交事务。自动提交,通过修改mysql全局变量autocommit进行控制。

通过以下命令可以查看当前autocommit模式

show variables like '%commit%';

在这里插入图片描述
设置自动提交的参数为OFF

set autocommit = 0;  -- 0:OFF  1:ON

在这里插入图片描述
案例演示

-- 自动提交事务:每条sql语句就是一个事务,那么执行一条sql语句就会提交一次事务-- mysql数据库就是自动提交事务-- a给b转账100元update account set money=money-100 where name='a';update account set money=money+100 where name='b';-- 查看mysql是否自动提交事务-- autocommit的值是on表示自动提交事务,值是off表示关闭自动提交事务show variables like '%commit%';-- 我们可以使用命令临时设置mysql变为手动提交事务,即将自动提交事务关闭-- 下次重新连接mysql依然是自动提交事务set autocommit = 0; -- 0 表示关闭自动提交事务 1表示开启自动事务update account set money=money-100 where name='a'

注意:

1)MySql默认自动提交。即执行一条sql语句提交一次事务。

2)设置autocommit为off状态,只是临时性的,下次重新连接mysql,autocommit依然变为on状态。

3)如果设置autocommit为off状态,那么当我们执行一条sql语句,就不会自动提交事务,重新启动可视化工具,数据并没有改变。

4)如果设置autocommit为on状态,如果我们先执行start transaction 然后在执行修改数据库的语句:

update account set money = money-100 where name='a';  update account set money = money+100 where name='b';

那么此时就表示上述修改数据库的sql语句都在同一个事务中,此时必须手动提交事务,即commit

换句话说,如果我们手动开启事务start transaction那么此时mysql就不会自动提交事务,必须手动提交事务。

5)如果设置autocommit为on状态,如果我们不执行start transaction 直接执行修改数据库的语句:

update account set money = money-100 where name='a';update account set money = money+100 where name='b';

那么此时mysql就会自动提交事务,即上述每条sql语句就是一个事务

事务原理和四大特征

事务原理

在这里插入图片描述
原理说明

  1. 一个用户登录成功以后,服务器会创建一个临时日志文件。日志文件用来保存用户事务状态。
  2. 如果没有使用事务,则所有的操作直接写到数据库中,不会使用日志文件。
  3. 如果开启事务,将所有的写操作写到日志文件中。
  4. 如果这时用户提交了事务,则将日志文件中所有的操作写到数据库中。
  5. 如果用户回滚事务,则日志文件会被清空,不会影响到数据库的操作。

事务的四大特征

事务的四大特征(ACID)

数据库的事务必须具备ACID特征,ACID是指Atomicity(原子性)、Consistensy(一致性)、Isolation(隔离性)和Durabiliyt(持久性)

隔离性(Isolation)

多个用户并发的访问数据库时,一个用户的事务不能被其他用户的事物干扰,多个并发的事务之间相互隔离
在这里插入图片描述
持久性(Durability)

指一个事务一旦被提交,它对数据库的改变是永久性的,哪怕数据库发生异常,重启之后数据依然会存在
在这里插入图片描述
原子性(Atomicity)

指事务包装的一组sql语句(一组业务逻辑)是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生

一致性(Consistency)

一致性是指数据处于一种语义上有意义且正确的状态;

事务一致性是指事务执行的结果必须是使数据从一个一致性状态变到另一个一致性状态。

事务的成功与失败,最终数据库的数据都是符合实际生活的业务逻辑。一致性绝大多数依赖业务逻辑和原子性
在这里插入图片描述

事务的并发访问引发的三个问题(面试)

事务在操作时的理想状态:多个事务之间互不影响,如果隔离级别设置不当就可能引发并发访问问题

并发访问的问题 含义
脏读 一个事务读取到了另一个事务中尚未提交的数据。最严重,杜绝发生。
不可重复读 一个事务中两次读取的数据内容不一致,要求的是一个事务中多次读取时数据是不一致的,这是事务update时引发的问题
幻读(虚读) 一个事务内读取到了别的事务插入或者删除的数据,导致前后读取记录行数不同。这是insert或delete时引发的问题

1.脏读:指一个事务读取了另外一个事务未提交的数据。(非常危险)
在这里插入图片描述
2.不可重复读:在一个事务内多次读取表中的数据,多次读取的结果不同。
在这里插入图片描述
3.幻读(虚读):一个事务内读取到了别的事务插入或者删除的数据,导致前后读取记录行数不同
在这里插入图片描述
4.总结

  • 赃读:一个事务读取另一个事务还没有提交的数据,一定避免。
    不可重复读:一个事务读取多次数据内容不一样,主要是update语句。事务已经提交了。 可以发生的。

  • 幻读(虚读):一个事务读取多次数量不一样,主要是delete或者insert语句。事务已经提交了。可以发生的。

事务的隔离级别

通过以上问题演示,我们发现如果不考虑事务的隔离性,会遇到脏读、不可重复读和虚读等问题。所以在数据库中我们要对上述三种问题进行解决。MySQL数据库规范规定了4种隔离级别,分别用于描述两个事务并发的所有情况。

事物隔离级别

上面的级别最低,下面的级别最高。表示会出现这种问题,表示不会出现这种问题。

级别 名字 隔离级别 脏读 不可重复读 幻读 数据库默认隔离级别
1 读未提交 read uncommitted
2 读已提交 read committed Oracle和SQL Server
3 可重复读 repeatable read MySQL
4 串行化 serializable

安全和性能对比

  • 安全: 串行化>可重复读>读已提交>读未提交

  • 性能: 串行化<可重复读<读已提交<读未提交

其实三个问题中,最严重的就是脏读(读取了错误数据),这个问题一定要避免;

关于不可重复读和虚读其实并不是逻辑上的错误,而是数据的时效性问题,所以这种问题并不属于很严重的错误;

如果对于数据的时效性要求不是很高的情况下,我们是可以接受不可重复读和虚读的情况发生的

推荐学习:mysql视频教程

商匡云商
Logo
注册新帐户
对比商品
  • 合计 (0)
对比
0
购物车