打好数据库基础，玩转数据库查询（进阶篇上）

于 2020-06-01 22:20:17 发布
阅读量299 收藏 1
点赞数
分类专栏： mysql 文章标签： 数据库 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_45037429/article/details/106441313
版权

MySQL数据库进阶篇

一、多表关联查询

多表查询操作，是在项目开发时使用较多的查询操作。操作多表的时候我们需要掌握的重点有三个，一是多表关联关系和查询操作、二是什么是事务以及事务的特性、三是索引操作。掌握了这三个知识点，数据库查询基本就没什么问题啦！

（一）多表关联关系

多表关联关系一共有三种：一对一；一对多；多对多。

1、一对一关系

  • 通过主键关联主键可以实现
  • 通过外键关联主键也可以实现，外键需要添加唯一约束。
  • 举例：游戏用户和账号
# 第一种方式，通过主键关联主键实现，建完表后添加外键。
# 创建一个用户表
create table user(
id int primary key auto_increment,
name varchar(50),
age int
);
# 创建一个账户表
create table account(
id int primary key auto_increment,
nickname varchar(50),
level int
);

在这里插入图片描述

# 添加外键约束
alter table account
add foreign key(id) references user(id);

在这里插入图片描述
当一个表添加外键之后，身为外键的那张表在该表被删除之前不可以被删除，因为存在引用关系，只有在该表被删除后，外键表就没有了外键约束，此时才可以被删除。
在这里插入图片描述

# 方式二：创建表时就创建外键
# 用户表user
create table user(
	id int primary key auto_increment,
	name varchar(50),
	age int);
# 账号表game_num
create table game_num(
	id int primary key auto_increment,
	nickname varchar(50),
	level int,
	foreign key(id) references user(id)
	);

2、一对多关系表

  • 通过外键关联可以实现，外键添加在多的一方
  • 中间表进行关联，多的一方设置唯一约束
  • 案例：店铺（shop），商品（goods）
# 创建店铺表
create table shop(
id int primary key,
shop_name varchar(50)
);
# 创建商品表
create table goods(
id int primary key,
goods_name varchar(50),
price float,
sid int
);

在这里插入图片描述

# 第一种方式创建外键约束,将goods表中的sid和shop表中的id对应起来
alter table goods add foreign key(sid) references shop(id);

在这里插入图片描述
在实现第二种设置外键的方法时，先要删除原来的表格，注意要先删goods表才能删shop表。。
在这里插入图片描述

# 在创建表的时候设置外键，方法和创建一对一关系的时候类似，但是关联的不是主键，而是一个普通的列。
# 创建店铺表
create table shop(
id int primary key,
shop_name varchar(50)
);
# 创建商品表
create table goods(
id int primary key,
goods_name varchar(50),
price float,
sid int,
foreign key(sid) references shop(id)
);

还有一种是创建关系表，使用中间表进行关联，然后多的一方设置唯一约束。在进行第三种方式创建外键时需要先将表格删除，注意要先删goods表才能删shop表。

# 使用中间表建立外键关系
# 创建店铺表
create table shop(
id int primary key,
shop_name varchar(50)
);
# 创建商品表
create table goods(
id int primary key,
goods_name varchar(50),
price float,
sid int,
);
# 创建中间表,并将对应列与原表格对应的属性对应上。
create table shop_goods(
id int primary key auto_increment,
sid int,
foreign key(sid) references shop(id),
gid int unique,
foreign key(gid) references goods(id)
);

在这里插入图片描述

3、多对多关联关系

  • 使用中间表关联，不设置唯一约束即可
  • 案例：学生（stu）和课程（course）
# 创建学生表
create table stu(
id int primary key,
s_name varchar(20)
);
# 创建课程表
create table course(
id int primary key,
c_name varchar(50)
);
# 创建中间表完成多对多关联关系
create table stu_course(
id int primary key,
stu_id int,
foreign key(stu_id) references stu(id),
course_id int,
foreign key(course_id) references course(id)
);

在这里插入图片描述

（二）多表链接查询

多表链接查询顾名思义就是通过多张表之间的关系查找多张表之间的数据。多表查询会比单表查询耗费更多的系统资源。

1、首先创建两个测试表来简单认识一下多表链接查询。

# 创建测试表a和b
create table a(
id int primary key,
name varchar(10)
);
create table b(
id int primary key,
name varchar(10)
);

在这里插入图片描述
往表中添加数据

insert into a values(1,"a")
insert into a values(2,"b")
insert into a values(4,"d")

insert into b values(1,"x")
insert into b values(2,"y")

在这里插入图片描述

2、连接查询分类

（1）交叉连接查询
不指定条件链接，结果就是笛卡尔积，记录数就是a表记录m乘以b表记录n；通过关键字[cross] join建立链接，cross可以不写

# 交叉连接查询
select * from a cross join b;
select * from a join b;

在这里插入图片描述
在这里插入图片描述
（2）内连接查询
指定条件连接，结果集合包含符合连接的记录数；
使用关键字：表1 [inner] join 表2 on 指定的连接条件;inner可以不写

# 内连接查询
select * from a inner join b on a.id=b.id;
select * from a join b on a.id=b.id;

在这里插入图片描述
在这里插入图片描述
（3）左外连接查询
指定条件连接，结果包含左表全部记录，右表中符合连接条件的记录，右表中不符合连接条件的记录使用null来填充。
实现关键字：左表 left [outer] join 右表 on 指定的连接条件; outer可以不写。

# 左外连接查询
select * from a left outer join b on a.id=b.id;
select * from a left join b on a.id=b.id;

在这里插入图片描述
在这里插入图片描述
（4）右外连接查询
指定条件连接，结果集包含右表的全部记录，左表中符合连接条件的记录，左表不符合的记录用null填充。
使用关键字：左表 right [outer] join 右表 on 指定的连接条件;outer可以不写

# 右外连接查询
select * from a right outer join b on a.id=b.id;
select * from a right join b on a.id=b.id;

在这里插入图片描述
在这里插入图片描述

3、复合查询

采用集合运算符对多个查询结果进行运算，产生新的查询结果集。
（1）对两个结果进行并集操作
并集：union, union all
union/union all两边分别是一条查询语句。

# 查看两个列表的并集
select * from a union select * from b;

在这里插入图片描述
（2）差集和交集
mysql中，差集和交集没有固定的引用关键字，只能通过多种条件限制使得结果产生交集和差集。

4、子查询

子查询也可以称作嵌套查询。是通过一条查询语句来实现更加复杂的查询，嵌套的selet往往被称为子查询，需要使用()括起来。
子查询存在两种：

  • 非关联子查询：子查询可以单独用于主查询执行，仅执行一次，效率非常高
    在这里插入图片描述
  • 关联子查询：子查询不可以单独用于主查询执行，如果主查询有n行，子查询将执行n次，效率相对较低，但是灵活度高。关联查询有个特点就是子查询中需要用到主查询的数据。
    在这里插入图片描述

二、数据库事务

（一）什么是事务？

事务是一种机制，一个操作序列，包含了一组数据库操作命令。事务是一个不可分割的工作逻辑单元，在数据库执行并发操作时，事务是最小的控制单元。

举个例子：
A用户和B用户是银行的客户，现在A要给B转500元，此时需要执行以下步骤
第一步：检查账户余额是否大于500元
第二步：A账户扣除500元
第三步：B账户增加500元
正常流程走下来，A账户扣除500元，B账户增加500元
如果A扣了钱之后，系统出故障了，A损失了500元，B却没有收到500元，这样的结果是不正确的。即表示事务没有完成。

所谓事务，就是一个操作序列要么全部都执行，要么都不执行，是一个不可以分割的工作单位

（二）事务需要数据库引擎

1、什么是数据库引擎？

  • 数据库存储引擎是数据库底层构建的核心，负责底层数据持久化和软件交互的序列化操作、校验过程和交互过程，通过数据库存储引擎完成创建、查询、更新和删除数据的功能。
  • 不同的的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。
  • 现在许多不同的数据库管理系统都支持多种不同的数据引擎。
  • InnoDB数据库引擎：
    InnDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，是MySQL默认引擎。
  • MyISAM数据库引擎：
    MyISAM是基于ISAM存储引擎，并对其进行扩展是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事务。

2、事务的特性（ACID）

（1）原子性（atomicity）
一个事务必须视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部成功执行，要么全部失败回滚，对于一个事务而言不可能只执行其中的一部分操作。
（2）一致性（consistency）
从一个一致性的状态转换到另一个一致性的状态。当一方的数据发生了变化，另一方的数据必定也会发生等量的数据变化。
（3）隔离性（isolation）
同一时间，只允许一个事务请求同一数据，不同事务之间彼此互不影响，互不干扰。
（4）持久性（durability）
数据一旦发生改变就是永久性的变化；一旦失误提交，其所作的修改会永久的保存到数据库中。

3、事务的隔离级别

数据库操作会严格按照四大特性进行管理，这样操作数据库的时候会比较缓慢。事务之间的隔离的基础是有两个事务同时操作。

  • 级别一：读未提交（read uncommitted）：一个事务可以读取到另一个事务中未提交的操作。该隔离级别是操作效率最高的，但是主句稳定性是最差的。
  • 级别二：读已提交（read committed）：一个事务可以读取到另一个事务已提交的更新结果。该隔离级别操作效率较高，数据稳定性差。
  • 级别三：可重复读（repeat read）：整个事务过程中，对同一个数据的读取结果是相同的，不管其他事务是否同时在对同一笔数据进行更新、提交。该隔离级别操作效率较低数据稳定性较差。
  • 级别四：可串行化/序列化（serializable）：所有的操作全部排队，任何事情必须等待前一件事情全部完成才能开始。该隔离级别操作效率最低，数据稳定性最高。

4、事务的并发问题。

由于事务隔离级别的存在，事务在并发执行的过程中可能会出现这三个问题：脏读、不可重复读、幻读。
（1）脏读
A事务读取了B事务尚未提交的更改数据，并在这个数据基础上操作，如果B事务回滚了，那么A事务读取的数据就不是合法的，此称之为脏读。
（2）不可重复读
A事务和B事务并发执行，然后A事务第一次读取数据，获取到了一个数据结果，接着B事务对数据进行修改（或者删除）数据的操作，此时A再次读取原来的数据就会发现两次数据内容不一样，此称之为不可重复读。
（3）幻读
A事务与B事务并发执行，A事务发现或者意识到B事务已经提交的新增数据。与不可重复读的区别就是幻读在于数据数量（条数）的变化，不可重复读体现在数据内容的变化。

5、事务的操作

# 设置隔离级别
set session transaction isolation level 事务名称(read committed);
# 开启事务
begin/start transaction;
# 进行事务操作
(select/insert/delete/update)......
# 提交事务
commit();
# 回滚事务：将所有发生的数据改变全部恢复
rollback()

小提示：在命令行窗口中执行数据库操作命令的时候，每句命令都是一次完整的事务，因此在命令行完成的操作是事务隔离级别是读已提交，这样才不会出现脏读、幻读、不可重复读的情况。
案例操作：开启两个窗口进行数据库操作，模拟事务A和事务B。
在这里插入图片描述
（1）读未提交
①两个命令行读设置读未提交的事务隔离级别。
在这里插入图片描述
②左边窗口添加数据但是未提交，右边窗口查看数据。
在这里插入图片描述
可以发现在右边窗口中可以读取到左边事务只是添加了，但是没有提交的数据。这样读取到的数据就是脏读数据；
在这里插入图片描述
左边窗口操作修改了数据并且提交了事务，右边再读取一次相同的数据，这时候就会出现数据不一致的情况，该情况就是不可重复读；
在这里插入图片描述
左边窗口这时候进行添加数据操作且提交了事务，此时右边读取到的数据就又会增加，这就是幻读的情况。
（2）读已提交
两边都设置读已提交的事务隔离级别。
set session transaction isolation level read committed;
在这里插入图片描述
在这里插入图片描述
当我们对左边的事务进行数据修改且没有提交事务的情况下，右边是读取不到左边未提交的数据的。左边一提交了事务，右边就可以马上查看到。但是与此同时带来的问题就是，两次查询相同数据的结果不一致，出现了不可重复读的情况。假如左边的操作改成添加数据或者删除数据的擦欧洲哦，那么读已提交的事务级别就出现了幻读的情况。
（3）可重复读
先将两边的事务隔离级别设置成可重复读的情况：
set session transaction isolation level repeatable read;
在这里插入图片描述
在这里插入图片描述
在左边的事务中进行添加数据操作并且已经提交，但是在右边的事务中是不可以查询到其自己事务开启之后的数据变化，他能查到的只能是本事务开启之前的数据状态，这样查询到的数据是不会发生变化的，因此不会出现不可重复读的情况；但是左边事务添加或者删除数据已经引起原数据的变化，在可重复读的隔离级别下也是没有办法读取的，这样就会造成事务中的数据和真实数据的数量发生变化，从而引发幻读的问题。
（4）可串行化（可序列化）
在这里插入图片描述
由于两边都开启了可序列化事务隔离级别，因此左边想要操作数据的时候就不允许操作，只能够等待右边的事务结束之后才可以执行。
在这里插入图片描述
当右边事务提交之后，左边的操作就可以执行了。
事务隔离级别并发问题汇总表格：

事务隔离级别脏读不可重复读幻读
读未提交存在存在存在
读已提交不存在存在存在
可重复读不存在不存在存在
可串行化不存在不存在不存在
B+Tree
关注
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值