西部数码网站站点,江苏省城乡建设厅网站,网站制作设计报价,wordpress 腾讯云存储提示#xff1a;文章写完后#xff0c;目录可以自动生成#xff0c;如何生成可参考右边的帮助文档 文章目录 前言一、多表设计1 多表设计-概述2 三种多表关系一对多#xff08;多对一#xff09;#xff08;1#xff09;无外键约束#xff08;逻辑外键#xff09;文章写完后目录可以自动生成如何生成可参考右边的帮助文档 文章目录 前言一、多表设计1 多表设计-概述2 三种多表关系一对多多对一1无外键约束逻辑外键建立独立的部门表和员工表一个部门下面可以有很多员工这就是典型的一对多2外键约束建立依赖的部门表和员工表一个部门下面可以有很多员工这就是典型的一对多也学一下虽然互联网大厂数据库层面都禁止使用外键约束3逻辑外键无外键约束的创建多表与物理外键采用外键约束的方式创建多表 一对一多用于表的拆分一张大表拆成多张小表多对多 3 案例苍穹外卖多表关系分析设计4 数据库中表设计的范式原则5 小结 二、多表查询0 数据准备1 多表查询概述1引入多表查询中的笛卡尔现象与解决方案2多表查询的分类概念连接查询内连接、外连接和子查询 2 连接查询1内连接查询select 字段列表 from 表1 [inner] join 表2 on 连接条件...2外连接查询左外连接select 字段列表 from 表1 left [outer] join 表2 on 连接条件...右外连接select 字段列表 from 表1 right [outer] join 表2 on 连接条件... 3内外连接语法进阶重要重要---- 在on连接后可以继续加上 where条件查询、group by分组查询等 --- 多对多怎么三张表连接前面都是两张表连接查询 3 子查询也称为嵌套查询1标量子查询: 子查询返回的结果为单个值( 标量用 ,等 )2列子查询: 子查询返回的结果为一列列向量用 in,not in等3行子查询: 子查询返回的结果为一行行向量用 !in, not in等4表子查询: 子查询返回的结果为多行多列矩阵其实就是一张表一般跟在from后当做一张查询表使用 4 案例苍穹外卖多表查询练习重要重要多练习 三、事务1 事务的概念2 事务的操作语法3 事务的自动提交与手动提交两种方式4 事务的四大特性ACID5 事务的隔离级别了解即可通常也不需要我们去设置事务的隔离级别 四、索引数据库的优化索引部分1 索引的介绍2 MySQL中索引采用的结构3 索引的语法 五、多学两招1 UNION ALL语法和UNION语法两表纵向合并在一起2 SQL中的with查询语法MySQL8.0版本后才支持 极大的简化多层子查询的逻辑 前言 一、多表设计
1 多表设计-概述
项目开发中在进行数据库表结构设计时会根据业务需求及业务模块之间的关系分析并设计表结构由于业务之间相互关联所以各个表结构之间也存在着各种联系基本上分为三种:
一对多(多对一)多对多一对一
下面我们也从这三个方面开始学习。
2 三种多表关系
一对多多对一
1无外键约束逻辑外键建立独立的部门表和员工表一个部门下面可以有很多员工这就是典型的一对多
参考视频
step1创建一个一对多的结构部门表 — 员工表 一个部门里面有很多员工
create table tb_emp(id int primary key auto_increment comment 主键ID,唯一标识,username varchar(20) not null unique comment 用户名,password varchar(32) default 123456 comment 密码,name varchar(10) not null comment 姓名,gender int not null comment 性别, 1:男 2:女, -- 1:男 2:女image varchar(300) comment 头像的URL, -- http://www.baidu.com/a.jpgjob int comment 职位, 1:班主任 2:讲师 3:学工主管 4教研主管,entry_date date comment 入职日期, -- 2020-01-01 年月日dept_id int comment 归属的部门ID, -- 其实就是部门表的主键 id 字段create_time datetime not null comment 创建时间, -- 2020-01-01 10:10:10 年月日时分秒update_time datetime not null comment 更新时间 -- 2020-01-01 10:10:10 年月日时分秒
) comment 员工表;create table tb_dept(id int primary key auto_increment comment 主键ID,唯一标识,name varchar(10) not null unique comment 部门名称,create_time datetime not null comment 创建时间, -- 2020-01-01 10:10:10 年月日时分秒update_time datetime not null comment 更新时间 -- 2020-01-01 10:10:10 年月日时分秒
) comment 部门表;step2往两张表里面插入数据
insert into tb_dept (id, name, create_time, update_time)
values (1, 学工部, now(), now()),(2, 教研部, now(), now()),(3, 咨询部, now(), now()),(4, 就业部, now(), now()),(5, 人事部, now(), now());INSERT INTO tb_emp(id, username, password, name, gender, image, job, entry_date, dept_id, create_time, update_time)
VALUES (1, jinyong, 123456, 金庸, 1, 1.jpg, 4, 2000-01-01, 2, now(), now()),(2, zhangwuji, 123456, 张无忌, 1, 2.jpg, 2, 2015-01-01, 2, now(), now()),(3, yangxiao, 123456, 杨逍, 1, 3.jpg, 2, 2008-05-01, 2, now(), now()),(4, weiyixiao, 123456, 韦一笑, 1, 4.jpg, 2, 2007-01-01, 2, now(), now()),(5, changyuchun, 123456, 常遇春, 1, 5.jpg, 2, 2012-12-05, 2, now(), now()),(6, xiaozhao, 123456, 小昭, 2, 6.jpg, 3, 2013-09-05, 1, now(), now()),(7, jixiaofu, 123456, 纪晓芙, 2, 7.jpg, 1, 2005-08-01, 1, now(), now()),(8, zhouzhiruo, 123456, 周芷若, 2, 8.jpg, 1, 2014-11-09, 1, now(), now()),(9, dingminjun, 123456, 丁敏君, 2, 9.jpg, 1, 2011-03-11, 1, now(), now()),(10, zhaomin, 123456, 赵敏, 2, 10.jpg, 1, 2013-09-05, 1, now(), now()),(11, luzhangke, 123456, 鹿杖客, 1, 11.jpg, 5, 2007-02-01, 3, now(), now()),(12, hebiweng, 123456, 鹤笔翁, 1, 12.jpg, 5, 2008-08-18, 3, now(), now()),(13, fangdongbai, 123456, 方东白, 1, 13.jpg, 5, 2012-11-01, 3, now(), now()),(14, zhangsanfeng, 123456, 张三丰, 1, 14.jpg, 2, 2002-08-01, 2, now(), now()),(15, yulianzhou, 123456, 俞莲舟, 1, 15.jpg, 2, 2011-05-01, 2, now(), now()),(16, songyuanqiao, 123456, 宋远桥, 1, 16.jpg, 2, 2007-01-01, 2, now(), now()),(17, chenyouliang, 123456, 陈友谅, 1, 17.jpg, NULL, 2015-03-21, NULL, now(), now());问题分析这样我们建立了部门表和员工表但是有一个问题 部门数据可以直接删除然而还有部分员工归属于该部门下此时就出现了数据的不完整、不一致问题。 e.g. 例如我们删除了部门表中的一个部门按道理来说对应部门下的员工数据都要删除但是我们上面那种创建表的方式并不会从员工表中删除对应部门员工数据。 也就是说我们上面那种方式创建的表其实还是相互独立的 解决方案 1逻辑外键不在数据库层进行映射在其他业务或者应用层进行映射不考虑修改一张表另一张表也要对应变化互联网大厂都是采用这种做法其实就是我们这里的这种非外键约束的方式 2外键约束物理外键采用外键约束将表联系起来外键约束互联网大厂都禁止使用因为和优点比起来缺点更难处理
2外键约束建立依赖的部门表和员工表一个部门下面可以有很多员工这就是典型的一对多也学一下虽然互联网大厂数据库层面都禁止使用外键约束
参考视频 外键约束的弊端 改、删时要考虑外键每次做DELETE 或者UPDATE都必须考虑外键约束不方便。表级锁导致并发差并发问题外键约束会启用行级锁主表写入时会进入阻塞级联删除问题删除主表的一条记录该记录外键关联的从表记录也会随之删除导致数据不可控。例如删除“订单表”的一条订单关联的“订单详情表”的一条记录也会随之删除。耦合高、迁移麻烦主表从表之间互相耦合主表数据量过大要分表并迁移数据时就必须先删除外键不然你刚删完主表的一条记录从表关联记录也级联删除了导致数据丢失。 语法解释 外键约束添加是添加在子表的语法中的一定要注意其中【外键名称】就是你给这个外键约束自己取一个名字自己随便写就是了主表就是对应一对多中对应“一”的表子表就是对应一对多中对应“多”的表 step1创建一个一对多的结构部门表 — 员工表 一个部门里面有很多员工 创建子表里面写外键约束语法父表要在子表前创建出来不然子表中外键约束会报错【注】也可以先用常规方法创建好两张表后再使用 alter table tb_emp add constraint tb_emp_fk_dept_id foreign key (dept_id) references tb_dept (id);
create table tb_dept(id int primary key auto_increment comment 主键ID,唯一标识,name varchar(10) not null unique comment 部门名称,create_time datetime not null comment 创建时间, -- 2020-01-01 10:10:10 年月日时分秒update_time datetime not null comment 更新时间 -- 2020-01-01 10:10:10 年月日时分秒
) comment 部门表;create table tb_emp(id int primary key auto_increment comment 主键ID,唯一标识,username varchar(20) not null unique comment 用户名,password varchar(32) default 123456 comment 密码,name varchar(10) not null comment 姓名,gender int not null comment 性别, 1:男 2:女, -- 1:男 2:女image varchar(300) comment 头像的URL, -- http://www.baidu.com/a.jpgjob int comment 职位, 1:班主任 2:讲师 3:学工主管 4教研主管,entry_date date comment 入职日期, -- 2020-01-01 年月日dept_id int comment 归属的部门ID, -- 其实就是部门表的主键 id 字段create_time datetime not null comment 创建时间, -- 2020-01-01 10:10:10 年月日时分秒update_time datetime not null comment 更新时间, -- 2020-01-01 10:10:10 年月日时分秒constraint fk_dept_id foreign key (dept_id) references tb_dept(id) -- 外键约束,将子表的dept_id字段与父表的id字段进行关联) comment 员工表;step2往两张表里面插入数据
insert into tb_dept (id, name, create_time, update_time)
values (1, 学工部, now(), now()),(2, 教研部, now(), now()),(3, 咨询部, now(), now()),(4, 就业部, now(), now()),(5, 人事部, now(), now());INSERT INTO tb_emp(id, username, password, name, gender, image, job, entry_date, dept_id, create_time, update_time)
VALUES (1, jinyong, 123456, 金庸, 1, 1.jpg, 4, 2000-01-01, 2, now(), now()),(2, zhangwuji, 123456, 张无忌, 1, 2.jpg, 2, 2015-01-01, 2, now(), now()),(3, yangxiao, 123456, 杨逍, 1, 3.jpg, 2, 2008-05-01, 2, now(), now()),(4, weiyixiao, 123456, 韦一笑, 1, 4.jpg, 2, 2007-01-01, 2, now(), now()),(5, changyuchun, 123456, 常遇春, 1, 5.jpg, 2, 2012-12-05, 2, now(), now()),(6, xiaozhao, 123456, 小昭, 2, 6.jpg, 3, 2013-09-05, 1, now(), now()),(7, jixiaofu, 123456, 纪晓芙, 2, 7.jpg, 1, 2005-08-01, 1, now(), now()),(8, zhouzhiruo, 123456, 周芷若, 2, 8.jpg, 1, 2014-11-09, 1, now(), now()),(9, dingminjun, 123456, 丁敏君, 2, 9.jpg, 1, 2011-03-11, 1, now(), now()),(10, zhaomin, 123456, 赵敏, 2, 10.jpg, 1, 2013-09-05, 1, now(), now()),(11, luzhangke, 123456, 鹿杖客, 1, 11.jpg, 5, 2007-02-01, 3, now(), now()),(12, hebiweng, 123456, 鹤笔翁, 1, 12.jpg, 5, 2008-08-18, 3, now(), now()),(13, fangdongbai, 123456, 方东白, 1, 13.jpg, 5, 2012-11-01, 3, now(), now()),(14, zhangsanfeng, 123456, 张三丰, 1, 14.jpg, 2, 2002-08-01, 2, now(), now()),(15, yulianzhou, 123456, 俞莲舟, 1, 15.jpg, 2, 2011-05-01, 2, now(), now()),(16, songyuanqiao, 123456, 宋远桥, 1, 16.jpg, 2, 2007-01-01, 2, now(), now()),(17, chenyouliang, 123456, 陈友谅, 1, 17.jpg, NULL, 2015-03-21, NULL, now(), now());step3删除部门表中一个部门数据验证一下外键约束
-- 删除部门表中id 1的记录,其实就是删除学工部
delete from tb_dept where id 1; -- 会发现删除失败,因为员工表中dept_id 1学工部员工外键约束你不能删除-- 删除部门表中id 5的记录,其实就是删除人事部
delete from tb_dept where id 5; -- 会发现删除成功,因为员工表中没有dept_id 5人事部员工此时就可以删除成功这就是外键约束
3逻辑外键无外键约束的创建多表与物理外键采用外键约束的方式创建多表 我们下面的继续学习也是采用逻辑外键也就是不采用外键约束的那种方式来学习。
一对一多用于表的拆分一张大表拆成多张小表 拆分成下面两张表本质上一对一之间看做一对多的一种特例也就能实现了
-- 一对一: 用户 与 身份证
create table tb_user
(id int unsigned primary key auto_increment comment ID,name varchar(10) not null comment 姓名,gender tinyint unsigned not null comment 性别, 1 男 2 女,phone char(11) comment 手机号,degree varchar(10) comment 学历
) comment 用户信息表;insert into tb_user
values (1, 白眉鹰王, 1, 18812340001, 初中),(2, 青翼蝠王, 1, 18812340002, 大专),(3, 金毛狮王, 1, 18812340003, 初中),(4, 紫衫龙王, 2, 18812340004, 硕士);create table tb_user_card
(id int unsigned primary key auto_increment comment ID,nationality varchar(10) not null comment 民族,birthday date not null comment 生日,idcard char(18) not null comment 身份证号,issued varchar(20) not null comment 签发机关,expire_begin date not null comment 有效期限-开始,expire_end date comment 有效期限-结束,user_id int unsigned not null unique comment 用户ID,constraint fk_user_id foreign key (user_id) references tb_user (id)
) comment 用户信息表;insert into tb_user_card
values (1, 汉, 1960-11-06, 100000100000100001, 朝阳区公安局, 2000-06-10, null, 1),(2, 汉, 1971-11-06, 100000100000100002, 静安区公安局, 2005-06-10, 2025-06-10, 2),(3, 汉, 1963-11-06, 100000100000100003, 昌平区公安局, 2006-06-10, null, 3),(4, 回, 1980-11-06, 100000100000100004, 海淀区公安局, 2008-06-10, 2028-06-10, 4);多对多 -- 多对多: 学生 与 课程
create table tb_student
(id int auto_increment primary key comment 主键ID,name varchar(10) comment 姓名,no varchar(10) comment 学号
) comment 学生表;
insert into tb_student(name, no)
values (黛绮丝, 2000100101),(谢逊, 2000100102),(殷天正, 2000100103),(韦一笑, 2000100104);create table tb_course
(id int auto_increment primary key comment 主键ID,name varchar(10) comment 课程名称
) comment 课程表;
insert into tb_course (name)
values (Java),(PHP),(MySQL),(Hadoop);create table tb_student_course
(id int auto_increment comment 主键 primary key,student_id int not null comment 学生ID,course_id int not null comment 课程ID,constraint fk_courseid foreign key (course_id) references tb_course (id),constraint fk_studentid foreign key (student_id) references tb_student (id)
) comment 学生课程中间表;insert into tb_student_course(student_id, course_id)
values (1, 1),(1, 2),(1, 3),(2, 2),(2, 3),(3, 4);3 案例苍穹外卖多表关系分析设计
参考视频1 参考视频2 这是一个外卖平台的案例里面三个功能分类管理、菜品管理、套餐管理模块都对应着一张表 分类管理分类表包含菜品的类别、套餐的类别 菜品管理菜品表菜品表有哪些菜品 套餐管理套餐表有哪些套餐
简单分析一下就有下面的结构设计图
分析 每个菜品分类下面有多种菜品所以分类表和菜品表是一对多关系每个套餐只能属于一个菜品分类一个菜品分类下面有多种套餐需求上面规定是所以分类表和套餐表间也是一对多关系每个套餐下面有多种菜品每个菜品可以属于不同套餐所以套餐表和菜品表间是属于多对多关心所以需要一张中间表
采用物理外键的方式将创建表的SQL语句写了下
# 1 分类表
create table category
(id int auto_increment primary key comment 主键ID,name varchar(20) not null unique comment 分类名称,type int not null comment 分类类型, 1.菜品分类 2.套餐分类 ,sort int not null comment 排序字段,status int not null default 0 comment 状态字段, 1.启用 0.禁用,create_time datetime not null comment 创建时间,update_time datetime not null comment 修改时间
) comment 分类表;# 2 菜品表
create table dish
(id int auto_increment primary key comment 主键ID,name varchar(20) not null unique comment 菜品名称,category_id int not null comment 所属分类ID(菜品分类)外键关联category表,price decimal(8, 2) not null comment 菜品价格,image varchar(300) not null comment 菜品图片,description varchar(200) comment 菜品描述,status int not null default 0 comment 状态字段, 1.启售 0.停售,create_time datetime not null comment 创建时间,update_time datetime not null comment 修改时间
) comment 菜品表;# 3 套餐表
create table setmeal
(id int auto_increment primary key comment 主键ID,name varchar(20) not null unique comment 套餐名称,category_id int not null comment 所属分类ID(套餐分类)外键关联category表,price decimal(8, 2) not null comment 套餐价格,image varchar(300) not null comment 套餐图片,description varchar(200) comment 套餐描述,status int not null default 0 comment 状态字段, 1.启售 0.停售,create_time datetime not null comment 创建时间,update_time datetime not null comment 修改时间
) comment 套餐表;# 4 套餐菜品关系中间表
create table setmeal_dish
(id int auto_increment primary key comment 主键ID,setmeal_id int not null comment 套餐ID, 外键关联setmeal表主键,dish_id int not null comment 菜品ID, 外键关联dish表主键,copys int not null comment 表示该套餐中该菜品的数量
) comment 套餐菜品关系中间表;
4 数据库中表设计的范式原则
参考视频 概念: 设计数据库时需要遵循的一些规范。 设计关系数据库时遵从不同的规范要求设计出合理的关系型数据库这些不同的规范要求被称为不同的范式各种范式呈递次规范越高的范式数据库冗余越小。 也就是说满足第二范次就一定满足第一范次依次下去目前关系数据库有六种范式: 第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF) 和 第五范式(5NF又称完美范式)。一般来说如果我们数据库的设计能够满足前三个范式就已经设计的很完美了我们也只学习这前三大范式。 分类 第一范式(1NF):每一列都是不可分割的原子数据项第二范式(2NF)在1NF的基础上非码属性必须完全依赖于候选码(在1NF基础上消除非主属性对主码的部分函数依赖)第三范式(3NF)在2NF基础上任何非主属性不依赖于其它非主属性(在2NF基础上消除传递依赖)
下面用一张表来依次说明这个表的设计有什么问题 第一范式很显然上面这张表的设计中 系 这个字段 分成了 系名、系主任这两个子字段这就违反了第一范式。应该修改 这么一修改就符合了第一范式但是还是有一些问题。 这些问题可以通过第二、第三范式来解决。
先来理解一下第二范式
第二范式(2NF)在1NF的基础上非码属性必须完全依赖于候选码(在1NF基础上消除非主属性对主码的部分函数依赖) 几个概念: 函数依赖: A–B, 如果通过A属性(或属性组)的值可以确定唯一B属性的值。则称B依赖于A 例如: 学号 -- 姓名 (学号课程名称) -- 分数 完全函数依赖: A–B 如果A是一个属性组则B属性值得确定需要依赖于A属性组中所有的属性值。 例如: (学号课程名称)–分数 部分函数依赖: A–B如果A是一个属性组则B属性值得确定只需要依赖于A属性组中某一些值即可。 例如: (学号课程名称) -- 姓名 传递函数依赖:A–B8 --C 。如果通过A属性(属性组)的值可以确定唯一B属性的值在通过B属性(属性组)的值可以确定唯一C 属性的值则称C 传递函数依赖于A 例如: 学号–系名系名–系主任 码: 如果在一张表中一个属性或属性组被其他所有属性所完全依赖则称这个属性(属性组)为该表的码 就是如果一个属性或属性组可以确定剩下的属性那这个属性或属性组就称为这张表的码。 例如: 该表中码为: (学号课程名称) 只要知道了学号和课程名称就能确定姓名、系名、系主任、分数 1主属性: 码属性组中的所有属性 2非主属性: 除过码属性组的属性 很显然分数完全依赖于码(学号课程名称)但是姓名、系名、系主任只是部分依赖于码(学号课程名称) 这就不符合第二范式了第二范式要求非主属性必须完全依赖于码。 解决方案进行表的拆分将姓名、系名、系主任这几个不是完全依赖的独立出去成一张表继续分析学号就可以作为主键当逻辑外键 第二范式将数据冗余的问题解决了但是任然存在 下面就要使用第三范式来解决这个问题
第三范式(3NF)在2NF基础上任何非主属性不依赖于其它非主属性(在2NF基础上消除传递依赖) 上面的 学号可以确定系名系名又可以确定系主任很显然这里构成了传递 解决方案继续拆 这样就完全解决了上面的三个弊端。
5 小结
一对多 在多的一方添加外键关联另外一方的主键一对一 任意一方添加外键关联另外一方的主键多对多 通过中间表来维护中间表的两个外键分别关联另外两张表的主键
二、多表查询
多表查询:指从多张表中查询数据
0 数据准备
先讲我们要进行的查询数据的表创建好数据也insert进去
还是从我们熟悉的部门表与员工表
-- 多表查询: 数据准备
-- 部门管理
create table tb_dept
(id int unsigned primary key auto_increment comment 主键ID,name varchar(10) not null unique comment 部门名称,create_time datetime not null comment 创建时间,update_time datetime not null comment 修改时间
) comment 部门表;
insert into tb_dept (id, name, create_time, update_time)
values (1, 学工部, now(), now()),(2, 教研部, now(), now()),(3, 咨询部, now(), now()),(4, 就业部, now(), now()),(5, 人事部, now(), now());-- 员工管理
create table tb_emp
(id int unsigned primary key auto_increment comment ID,username varchar(20) not null unique comment 用户名,password varchar(32) default 123456 comment 密码,name varchar(10) not null comment 姓名,gender tinyint unsigned not null comment 性别, 说明: 1 男, 2 女,image varchar(300) comment 图像,job tinyint unsigned comment 职位, 说明: 1 班主任,2 讲师, 3 学工主管, 4 教研主管, 5 咨询师,entrydate date comment 入职时间,dept_id int unsigned comment 部门ID,create_time datetime not null comment 创建时间,update_time datetime not null comment 修改时间
) comment 员工表;INSERT INTO tb_emp(id, username, password, name, gender, image, job, entrydate, dept_id, create_time, update_time)
VALUES (1, jinyong, 123456, 金庸, 1, 1.jpg, 4, 2000-01-01, 2, now(), now()),(2, zhangwuji, 123456, 张无忌, 1, 2.jpg, 2, 2015-01-01, 2, now(), now()),(3, yangxiao, 123456, 杨逍, 1, 3.jpg, 2, 2008-05-01, 2, now(), now()),(4, weiyixiao, 123456, 韦一笑, 1, 4.jpg, 2, 2007-01-01, 2, now(), now()),(5, changyuchun, 123456, 常遇春, 1, 5.jpg, 2, 2012-12-05, 2, now(), now()),(6, xiaozhao, 123456, 小昭, 2, 6.jpg, 3, 2013-09-05, 1, now(), now()),(7, jixiaofu, 123456, 纪晓芙, 2, 7.jpg, 1, 2005-08-01, 1, now(), now()),(8, zhouzhiruo, 123456, 周芷若, 2, 8.jpg, 1, 2014-11-09, 1, now(), now()),(9, dingminjun, 123456, 丁敏君, 2, 9.jpg, 1, 2011-03-11, 1, now(), now()),(10, zhaomin, 123456, 赵敏, 2, 10.jpg, 1, 2013-09-05, 1, now(), now()),(11, luzhangke, 123456, 鹿杖客, 1, 11.jpg, 5, 2007-02-01, 3, now(), now()),(12, hebiweng, 123456, 鹤笔翁, 1, 12.jpg, 5, 2008-08-18, 3, now(), now()),(13, fangdongbai, 123456, 方东白, 1, 13.jpg, 5, 2012-11-01, 3, now(), now()),(14, zhangsanfeng, 123456, 张三丰, 1, 14.jpg, 2, 2002-08-01, 2, now(), now()),(15, yulianzhou, 123456, 俞莲舟, 1, 15.jpg, 2, 2011-05-01, 2, now(), now()),(16, songyuanqiao, 123456, 宋远桥, 1, 16.jpg, 2, 2007-01-01, 2, now(), now()),(17, chenyouliang, 123456, 陈友谅, 1, 17.jpg, NULL, 2015-03-21, NULL, now(), now());1 多表查询概述
参考视频
1引入多表查询中的笛卡尔现象与解决方案
单表查询基本语法: select * from 表名 我们自然猜测多表查询基本语法select * from 表名1表名2…
下面就来试一试
select * from tb_emp, tb_dept;很显然这个结果将两张表安装横轴拼接并且按照笛卡尔积产生了很多条无效数据。
笛卡尔积:笛卡尔乘积是指在数学中两个集合(A集合 和 B集合)的所有组合情况。 怎么解决这种现象很简单利用我们的where和我们表中的逻辑外键就可以了。在多表查询时需要消除无效的笛卡尔积
select * from tb_emp, tb_dept where tb_emp.dept_id tb_dept.id;这样就正确了这样就知道每一个员工属于哪一个部门了。
2多表查询的分类概念连接查询内连接、外连接和子查询
上面的引入是最基础的多表查询在实际的多表查询中主要分为两大类
2 连接查询
1内连接查询select 字段列表 from 表1 [inner] join 表2 on 连接条件…
参考博客
内连接查询就是查询两张表交集部分的数据 很显然显示的连接语法更加明显后面加上了进阶用法十分灵活 在【4 案例苍穹外卖多表查询练习】里面就会发现
【注】这里的交关系不要用我们传统的集合观念来理解这里要使用我们表中的逻辑外键来理解看完下面案例就明白了
-- A.查询员工的姓名 及所属的部门名称(隐式内连接实现)
select tb_emp.name,tb_dept.name from tb_emp, tb_dept where tb_emp.dept_id tb_dept.id;-- select e.name,d.name from tb_emp as e, tb_dept as d where e.dept_id d.id; -- 别名时的写法-- B.查询员工的姓名 及所属的部门名称(显示内连接实现)
select tb_emp.name,tb_dept.name from tb_emp inner join tb_dept on tb_emp.dept_id tb_dept.id;用这个例子来解释这里内连接中的交集什么意思。其实员工表中有17个员工但是上面只查出来了16条还有一个呢这是因为有一个员工的dept_id是null没有部门这意味着该员工没有与部门表联系上所以这条数据就应该是在交集外面的。
从上面的例子也可以看出内连接也是多表间横向连接起来。
2外连接查询
左外连接select 字段列表 from 表1 left [outer] join 表2 on 连接条件…
右外连接select 字段列表 from 表1 right [outer] join 表2 on 连接条件…
外连接分为左外连接和右外连接两种
左外连接完全包含左表的数据和左右表交集部分的数据右外连接完全包含右表的数据和左右表交集部分的数据 【注】很显然左右外连接的语法是可以相互转换的因此我们开发中多使用左外连接。 【注】本质上外连接和内连接实现的效果差不多外连接多一个想要完全保存其中哪一张表的数据
-- A.查询员工表 所有 员工的姓名和对应的部门名称(左外连接)
select tb_emp.name,tb_dept.name from tb_emp left outer join tb_dept on tb_emp.dept_id tb_dept.id;可以看到这个外连接将左表中的这条null数据也可以查询出来这就是外连接相较于内连接多出来的功能
-- B.查询部门表 所有 部门的名称和对应的员工名称(右外连接)
select tb_emp.name,tb_dept.name from tb_emp right outer join tb_dept on tb_emp.dept_id tb_dept.id;select * from tb_emp right outer join tb_dept on tb_emp.dept_id tb_dept.id;可以看到这里外连接要求保存完整的是部门表所以里面就业部和人事部在员工表中没有员工数据查询出来的结果横向拼接就将其余字段全部用null填充了。
【注】所以这里的交集也不能用我们传统的那种交集思想去理解或许将交集改成联系这个词来解释可能会更加恰当。
3内外连接语法进阶重要重要
---- 在on连接后可以继续加上 where条件查询、group by分组查询等
显示内外连接进阶语法重要 如果要在连接后的表后边继续查询可以继续在后面加上 where条件查询、group by分组查询这些。
语法类似 select tb_emp.name,tb_dept.name from tb_emp inner join tb_dept on tb_emp.dept_id tb_dept.id where 条件… group by …;
具体的使用见下面的综合案例 具体案例使用见 【4 案例苍穹外卖多表查询练习】
— 多对多怎么三张表连接前面都是两张表连接查询
具体案例使用见 【4 案例苍穹外卖多表查询练习】 【注中间表放中间要】
语法demo select s.name,s.price,d.name,d.price,sd.copies from setmeal s inner join setmeal_dish sd on s.id sd.setmeal_id inner join dish d on sd.dish_id d.id where s.name ‘商务套餐A’;
3 子查询也称为嵌套查询 1标量子查询: 子查询返回的结果为单个值( 标量用 ,等 )
语法 select * from t1 where column1 (select column1 from t2 …); 【技巧】将问题先拆解先想里面的子查询语句要写什么
-- 标量子查询
-- A.査询教研部”的所有员工信息-- a.查询教研部的部门IDselect id from tb_dept where name 教研部;-- b.再查询教研部的所有员工信息select * from tb_emp where dept_id (select id from tb_dept where name 教研部);-- B.查询在方东白”入职之后的员工信息-- a.查询方东白的入职时间select entrydate from tb_emp where name 方东白;-- b.再查询在方东白入职之后的员工信息select * from tb_emp where entrydate (select entrydate from tb_emp where name 方东白);2列子查询: 子查询返回的结果为一列列向量用 in,not in等
语法 select * from t1 where column1 in (select column1 from t2 …);
-- 列子查询
-- A.查询教研部”和咨询部”的所有员工信息-- a. 查询教研部”和咨询部的部门 ID- tb_deptselect id from tb_dept where name in (教研部, 咨询部);-- b.再根据部门ID查询教研部”和咨询部的所有员工信息select * from tb_emp where tb_emp.dept_id in (select id from tb_dept where name in (教研部, 咨询部));3行子查询: 子查询返回的结果为一行行向量用 !in, not in等 -- 行子查询
-- A.査询与 韦一笑”的 入职日期 及 职位都相同的员工信息 ;-- a.查询韦一笑”的入职日期 及 职位select entrydate, job from tb_emp where name 韦一笑;-- b.再查询与韦一笑”的入职日期 及 职位都相同的员工信息-- select * from tb_emp where entrydate (select entrydatefrom tb_emp where name 韦一笑) and job (select job from tb_emp where name 韦一笑);-- select * from tb_emp where (entrydate, job) (2007-01-01, 2);select * from tb_emp where (entrydate, job) (select entrydate, job from tb_emp where name 韦一笑);4表子查询: 子查询返回的结果为多行多列矩阵其实就是一张表一般跟在from后当做一张查询表使用
参考视频 【注】前面几种查询都是放在where后面但是这个表子查询子语句返回的是一个表因此我们通常放在from后面使用
-- 表子查询
-- A.査询入职日期是 2006-01-01”之后的员工信息及其部门名称-- a.查询入职日期是 2006-01-01”之后的员工信息select * from tb_emp where entrydate 2006-01-01;-- b.再查询入职日期是 2006-01-01”之后的员工信息及其部门名称-- select e.*, d.name from (select * from tb_emp where entrydate 2006-01-01) as e , tb_dept as d where e.dept_id d.id;select e.*, d.name from (select * from tb_emp where entrydate 2006-01-01) as e inner join tb_dept as d on e.dept_id d.id;-- e.*: 表示查询所有员工表的字段4 案例苍穹外卖多表查询练习重要重要多练习
先导入我们需要的四张表
-- 数据准备 :
-- 分类表
create table category
(id int unsigned primary key auto_increment comment 主键ID,name varchar(20) not null unique comment 分类名称,type tinyint unsigned not null comment 类型 1 菜品分类 2 套餐分类,sort tinyint unsigned not null comment 顺序,status tinyint unsigned not null default 0 comment 状态 0 禁用1 启用,create_time datetime not null comment 创建时间,update_time datetime not null comment 更新时间
) comment 分类;-- 菜品表
create table dish
(id int unsigned primary key auto_increment comment 主键ID,name varchar(20) not null unique comment 菜品名称,category_id int unsigned not null comment 菜品分类ID,price decimal(8, 2) not null comment 菜品价格,image varchar(300) not null comment 菜品图片,description varchar(200) comment 描述信息,status tinyint unsigned not null default 0 comment 状态, 0 停售 1 起售,create_time datetime not null comment 创建时间,update_time datetime not null comment 更新时间
) comment 菜品;-- 套餐表
create table setmeal
(id int unsigned primary key auto_increment comment 主键ID,name varchar(20) not null unique comment 套餐名称,category_id int unsigned not null comment 分类id,price decimal(8, 2) not null comment 套餐价格,image varchar(300) not null comment 图片,description varchar(200) comment 描述信息,status tinyint unsigned not null default 0 comment 状态 0:停用 1:启用,create_time datetime not null comment 创建时间,update_time datetime not null comment 更新时间
) comment 套餐;-- 套餐菜品关联表
create table setmeal_dish
(id int unsigned primary key auto_increment comment 主键ID,setmeal_id int unsigned not null comment 套餐id ,dish_id int unsigned not null comment 菜品id,copies tinyint unsigned not null comment 份数
) comment 套餐菜品中间表;-- 导入测试数据
-- category
insert into category (id, type, name, sort, status, create_time, update_time)
values (1, 1, 酒水饮料, 10, 1, 2022-08-09 22:09:18, 2022-08-09 22:09:18);
insert into category (id, type, name, sort, status, create_time, update_time)
values (2, 1, 传统主食, 9, 1, 2022-08-09 22:09:32, 2022-08-09 22:18:53);
insert into category (id, type, name, sort, status, create_time, update_time)
values (3, 2, 人气套餐, 12, 1, 2022-08-09 22:11:38, 2022-08-10 11:04:40);
insert into category (id, type, name, sort, status, create_time, update_time)
values (4, 2, 商务套餐, 13, 1, 2022-08-09 22:14:10, 2022-08-10 11:04:48);
insert into category (id, type, name, sort, status, create_time, update_time)
values (5, 1, 经典川菜, 6, 1, 2022-08-09 22:17:42, 2022-08-09 22:17:42);
insert into category (id, type, name, sort, status, create_time, update_time)
values (6, 1, 新鲜时蔬, 7, 1, 2022-08-09 22:18:12, 2022-08-09 22:18:28);
insert into category (id, type, name, sort, status, create_time, update_time)
values (7, 1, 汤类, 11, 1, 2022-08-10 10:51:47, 2022-08-10 10:51:47);-- dish
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (1, 王老吉, 1, 6.00,https://reggie-itcast.oss-cn-beijing.aliyuncs.com/41bfcacf-7ad4-4927-8b26-df366553a94c.png, , 1,2022-06-09 22:40:47, 2022-06-09 22:40:47);
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (2, 北冰洋, 1, 4.00,https://reggie-itcast.oss-cn-beijing.aliyuncs.com/4451d4be-89a2-4939-9c69-3a87151cb979.png,还是小时候的味道, 1, 2022-06-10 09:18:49, 2022-06-10 09:18:49);
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (3, 雪花啤酒, 1, 4.00,https://reggie-itcast.oss-cn-beijing.aliyuncs.com/bf8cbfc1-04d2-40e8-9826-061ee41ab87c.png, , 1,2022-06-10 09:22:54, 2022-06-10 09:22:54);
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (4, 米饭, 2, 2.00,https://reggie-itcast.oss-cn-beijing.aliyuncs.com/76752350-2121-44d2-b477-10791c23a8ec.png, 精选五常大米, 1,2022-06-10 09:30:17, 2022-06-10 09:30:17);
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (5, 馒头, 2, 1.00,https://reggie-itcast.oss-cn-beijing.aliyuncs.com/475cc599-8661-4899-8f9e-121dd8ef7d02.png, 优质面粉, 1,2022-06-10 09:34:28, 2022-06-10 09:34:28);
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (6, 老坛酸菜鱼, 5, 56.00,https://reggie-itcast.oss-cn-beijing.aliyuncs.com/4a9cefba-6a74-467e-9fde-6e687ea725d7.png,原料汤草鱼酸菜, 1, 2022-06-10 09:40:51, 2022-06-10 09:40:51);
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (7, 经典酸菜鮰鱼, 5, 66.00,https://reggie-itcast.oss-cn-beijing.aliyuncs.com/5260ff39-986c-4a97-8850-2ec8c7583efc.png,原料酸菜江团鮰鱼, 1, 2022-06-10 09:46:02, 2022-06-10 09:46:02);
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (8, 蜀味水煮草鱼, 5, 38.00,https://reggie-itcast.oss-cn-beijing.aliyuncs.com/a6953d5a-4c18-4b30-9319-4926ee77261f.png, 原料草鱼汤, 1,2022-06-10 09:48:37, 2022-06-10 09:48:37);
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (9, 清炒小油菜, 6, 18.00,https://reggie-itcast.oss-cn-beijing.aliyuncs.com/3613d38e-5614-41c2-90ed-ff175bf50716.png, 原料小油菜, 1,2022-06-10 09:51:46, 2022-06-10 09:51:46);
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (10, 蒜蓉娃娃菜, 6, 18.00,https://reggie-itcast.oss-cn-beijing.aliyuncs.com/4879ed66-3860-4b28-ba14-306ac025fdec.png, 原料蒜娃娃菜,1, 2022-06-10 09:53:37, 2022-06-10 09:53:37);
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (11, 清炒西兰花, 6, 18.00,https://reggie-itcast.oss-cn-beijing.aliyuncs.com/e9ec4ba4-4b22-4fc8-9be0-4946e6aeb937.png, 原料西兰花, 1,2022-06-10 09:55:44, 2022-06-10 09:55:44);
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (12, 炝炒圆白菜, 6, 18.00,https://reggie-itcast.oss-cn-beijing.aliyuncs.com/22f59feb-0d44-430e-a6cd-6a49f27453ca.png, 原料圆白菜, 1,2022-06-10 09:58:35, 2022-06-10 09:58:35);
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (13, 清蒸鲈鱼, 5, 98.00,https://reggie-itcast.oss-cn-beijing.aliyuncs.com/c18b5c67-3b71-466c-a75a-e63c6449f21c.png, 原料鲈鱼, 1,2022-06-10 10:12:28, 2022-06-10 10:12:28);
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (14, 东坡肘子, 5, 138.00,https://reggie-itcast.oss-cn-beijing.aliyuncs.com/a80a4b8c-c93e-4f43-ac8a-856b0d5cc451.png, 原料猪肘棒, 1,2022-06-10 10:24:03, 2022-06-10 10:24:03);
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (15, 梅菜扣肉, 5, 58.00,https://reggie-itcast.oss-cn-beijing.aliyuncs.com/6080b118-e30a-4577-aab4-45042e3f88be.png, 原料猪肉梅菜,1, 2022-06-10 10:26:03, 2022-06-10 10:26:03);
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (16, 剁椒鱼头, 5, 66.00,https://reggie-itcast.oss-cn-beijing.aliyuncs.com/13da832f-ef2c-484d-8370-5934a1045a06.png, 原料鲢鱼剁椒,1, 2022-06-10 10:28:54, 2022-06-10 10:28:54);
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (17, 馋嘴牛蛙, 5, 98.00,https://reggie-itcast.oss-cn-beijing.aliyuncs.com/7a55b845-1f2b-41fa-9486-76d187ee9ee1.png,配料鲜活牛蛙丝瓜黄豆芽, 1, 2022-06-10 10:37:52, 2022-06-10 10:37:52);
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (18, 鸡蛋汤, 7, 4.00,https://reggie-itcast.oss-cn-beijing.aliyuncs.com/c09a0ee8-9d19-428d-81b9-746221824113.png, 配料鸡蛋紫菜,1, 2022-06-10 10:54:25, 2022-06-10 10:54:25);
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (19, 平菇豆腐汤, 7, 6.00,https://reggie-itcast.oss-cn-beijing.aliyuncs.com/16d0a3d6-2253-4cfc-9b49-bf7bd9eb2ad2.png, 配料豆腐平菇,1, 2022-06-10 10:55:02, 2022-06-10 10:55:02);-- setmeal
insert into setmeal (id, category_id, name, price, status, description, image, create_time, update_time)
values (1, 4, 商务套餐A, 20.00, 1, ,https://reggie-itcast.oss-cn-beijing.aliyuncs.com/21a5ed3a-97f6-447a-af9d-53deabfb5661.png,2022-06-10 10:58:09, 2022-06-10 10:58:09);
insert into setmeal (id, category_id, name, price, status, description, image, create_time, update_time)
values (2, 4, 商务套餐B, 22.00, 1, ,https://reggie-itcast.oss-cn-beijing.aliyuncs.com/8d0075f8-9008-4390-94ca-2ca631440304.png,2022-06-10 11:00:13, 2022-06-10 11:11:37);
insert into setmeal (id, category_id, name, price, status, description, image, create_time, update_time)
values (3, 3, 人气套餐A, 49.00, 1, ,https://reggie-itcast.oss-cn-beijing.aliyuncs.com/8979566b-0e17-462b-81d8-8dbace4138f4.png,2022-06-10 11:11:23, 2022-06-10 11:11:23);-- setmeal_dish
insert into setmeal_dish (id, setmeal_id, dish_id, copies)
values (1, 1, 1, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies)
values (2, 1, 4, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies)
values (3, 1, 11, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies)
values (4, 2, 2, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies)
values (5, 2, 4, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies)
values (6, 2, 9, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies)
values (7, 3, 2, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies)
values (8, 3, 6, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies)
values (9, 3, 5, 1);参考视频1 参考视频2
1查询价格低于 10元 的菜品的名称、价格 及其 菜品的分类名称
-- 1查询价格低于 10元 的菜品的名称、价格 及其 菜品的分类名称-- 表 dish category-- 需求中没有特别强调需要完全保留哪一张表的数据一般直接使用内连接查询-- 子查询select * from dish where price 10;select a.name,a.price,c.name from (select * from dish where price 10) as a inner join category as c on a.category_id c.id;-- 隐式内连接select d.name,d.price,c.name from dish as d,category as c where d.price 10 and d.category_id c.id;-- 显式内连接进阶这个最好用select d.name,d.price,c.name from dish as d inner join category as c on d.category_id c.id where d.price 10;2查询所有价格在 10元(含)到50元(含)之间 且 状态为起售的菜品名称、价格及其分类名称
-- 2查询所有价格在 10元(含)到50元(含)之间 且 状态为起售的菜品名称、价格及其分类名称(即使菜品没有分类也要查询出来)-- 表 dish category-- 很明显需求中要求查询所有的菜品所以使用左连接查询select d.name,d.price,c.name from dish as d left outer join category as c on d.category_id c.id where d.price 10 and d.price 50 and d.status 1;3查询每个分类下最贵的菜品,展示出分类的名称、最贵的菜品的价格
-- 3查询每个分类下最贵的菜品, 展示出分类的名称、最贵的菜品的价格-- 表 dish categoryselect c.name, max(d.price) from dish as d inner join category as c on d.category_id c.id group by c.name;4查询各个分类下 菜品状态为起售,并且 该分类下菜品总数量大于等于3 的 分类名称
-- 4查询各个分类下菜品状态为起售,并且 该分类下菜品总数量大于等于 3 的分类名称-- 表 dish categoryselect c.name,count(*) from dish d inner join category c on d.category_id c.id where d.status 1 group by c.name having count(*) 3;5查询出商务套餐A 中包含了哪些菜品 (展示出套餐名称、价格,包含的菜品名称、价格、份数 这是一个多对多有中间表的三张表内连接查询案例
-- 5查询出商务套餐A 中包含了哪些菜品 (展示出套餐名称、价格,包含的菜品名称、价格、份数)-- 表 setmeal dish setmeal_dish 多对多三张表连接中间表放在中间select s.name,s.price,d.name,d.price,sd.copies from setmeal s inner join setmeal_dish sd on s.id sd.setmeal_id inner join dish d on sd.dish_id d.id where s.name 商务套餐A;-- 隐式内连接语法简单点select s.name,s.price,d.name,d.price,sd.copies from setmeal s, setmeal_dish sd, dish d where s.id sd.setmeal_id and sd.dish_id d.id and s.name 商务套餐A;6查询出低于菜品平均价格的菜品信息(展示出菜品名称、菜品价格)
-- 6查询出低于菜品平均价格的菜品信息(展示出菜品名称、菜品价格)-- 表 dishselect d.name,d.price from dish d where price (select avg(price) from dish);三、事务
事务的应用场景 【注】很显然这是使用逻辑外键的一个缺陷但是可以使用事务来解决这个缺陷。
1 事务的概念 事务是一组操作的集合它是一个不可分割的工作单位。 事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求即这些操作 要么同时成功功要么同时失败。 通俗理解如果一个包含多个步骤的业务操作被事务管理那么这些操作要么同时成功要么同时失败。 e.g1. 以下面的SQL语句为例 在SQL执行中默认上面两行SQL语句是两个事务每一行都是一个事务。所以有可能会出现第一行成功执行但是第二行失败的特殊情况。 即默认MySOL的事务是自动提交的也就是说当执行一条DML语句MySQL会立即隐式的提交事务。
我们要做的就是将这两行SQL语句封装成一个事务而不是自动的看做两个事务提交要么都成功要么有一个失败大家都一起失败这样就能保证完整性。
e.g2张三给李四转账的例子 很显然这个转账的流程分三步如果不使用一个事务管理就有可能出现在第三步出现异常导致张三账户 -500但是李四账户没有500这会造成很严重的后果。
2 事务的操作语法
参考视频 语法 开启事务: start transaction; / begin;提交事务: commit; 当我们这组操作中所有操作都成功我们就可以执行提交事务。 如果开启了事务如果没有提交事务只是执行了SQL语句那么数据库中的数据只是临时性的被修改。 什么意思意思是如果你关闭窗口重新打开数据会自动回滚。也就是说数据恢复原样回滚事务: rollback; 当我们这组操作中只要有一个操作失败就要将数据库中的数据回滚恢复到操作前的样子此时就必须使用回滚事务。不能使用提交事务 变成if…else…理解 if step1,…stepn 全部执行成功执行commit; else 执行rollback
还是以上面那个解散部门的SQL为案例进行演示
有一条SQL执行失败后我们回滚事务
start transaction;-- step2执行删除操作 直接执行下面两个SQL语句
-- 删除部门
delete from tb_dept where id 2;
-- 删除部门下的员工
delete from tb_emp where dept_id 2; -- 很显然这行有语法错误必定执行失败我们可以通过回滚事务来撤销这次操作-- 需要注意的是这里的两行SQL执行后数据库中的数据已经发生了变化可以使用select * from tb_dept;来查看但是在gui中是看不到的因为这是一个事务只有commit之后才会真正的提交到数据库中再显示在gui上-- step3step2中有SQL语句执行失败我们需要回滚事务来撤销这次操作保证数据的完整性
-- 回滚事务
rollback;-- 回滚后再次执行select * from tb_dept;就会发现数据恢复回来了在这里插入代码片所以SQL语句均能正常执行
-- step1开启事务 先执行这一行
start transaction;-- step2执行删除操作 直接执行下面两个SQL语句
-- 删除部门
delete from tb_dept where id 2;
-- 删除部门下的员工
delete from tb_emp where dept_id 2;-- 需要注意的是这里的两行SQL执行后数据库中的数据已经发生了变化可以使用select * from tb_dept;来查看但是在gui中是看不到的因为这是一个事务只有commit之后才会真正的提交到数据库中再显示在gui上-- step3提交事务 step2中的SQL语句都能正常执行因此我们需要提交事务
commit;-- 这时候在gui中就能看到数据发生了变化部门2和部门2下的员工都被删除了问题我们怎么知道所有的SQL是不是都能正常执行无法确定最后要使用的是提交事务还是回滚事务啊 答业务层可以捕获异常就可以知道了
3 事务的自动提交与手动提交两种方式
参考视频 MySQL数据库中事务默认自动提交 一条DML(增删改)语句会自动提交一次事务。知道了MySQL的这个特性提交事务是怎么回事就可以完全理解了。事务提交的两种方式: 自动提交: mysql就是自动提交的,一条DML(增删改)语句会自动提交一次事务。 MySQL默认是自动提交手动提交: 需要先开启事务再提交 oracle默认是手动提交 查看事务的默认提交方式: SELECT autocommit; --1 代表自动提交 0 代表手动提交 修改默认提交方式:set autocommit 0; 手动提交 另外这个修改默认事务提交方式MySQL如果关闭界面重新打开就又会重新设置成自动提交了 如果我们修改成手动提交后再执行任意DML(增删改)语句就只会临时修改数据了如果关闭界面重新打开数据就会恢复回来
下面演示一下这个修改事务的默认提交方式就能对事务有一个更加充分的认识了
step1查询mysql默认的事务提交方式
-- 查询默认的事务提交方式
select autocommit; -- 1 代表自动提交 0 代表手动提交step2修改成默认事务手动提交并且将 修改员工表中的name全部为 “张三” 部手动执行commit;提交事务会发现员工表中数据只会临时被修改如果关闭界面重新打开员工表中的数据会恢复到修改前
-- 设置事务提交方式为手动提交
set autocommit 0;-- 修改员工表中的name全部为 张三
update tb_emp set name 王五;4 事务的四大特性ACID
参考视频
原子性: 事务是不可分割的最小单元要么全部成功要么全部失败持久性: 事务一旦提交或回滚它对数据库中的数据的改变就是永久的隔离性: 数据库系统提供的隔离机制保证事务在不受外部并发操作影响的独立环境下运行 但真实情况是事务见是有相互影响的所以我们下面需要学习事务的隔离级别一致性 : 事务完成时必须使所有的数据都保持一致状态 5 事务的隔离级别了解即可通常也不需要我们去设置事务的隔离级别
参考视频1 参考视频2 参考视频3 概念: 多个事务之间隔离的相互独立的。但是如果多个事务操作同一批数据则会引发一些问题设置不同的隔离级别就可以解决这些问题。 存在问题 : 脏读: 一个事务读取到另一个事务中没有提交的数据。 不可重复读(虚读):在同一个事务中两次读取到的数据不一样 幻读: 一个事务操作(DML)数据表中所有记录另一个事务添加了一条数据则第一个事务查询不到自己的修改。 隔离级别 : 1.read uncommitted:读未提交 产生的问题:脏读、不可重复读、幻读2.read committed:读已提交 (Oracle) 产生的问题: 不可重复读、幻读3.repeatable read:可重复读 (MySQL默认) 产生的问题:幻读4.serializable:串行化 可以解决所有的问题 其实这个原理可以类比多线程的锁这里一个事务在操作共享的那批数据另一个事务要进行操作只能在外面等着等上一个事务提交或者回滚后才能进行 注意: 隔离级别从小到大安全性越来越高但是效率越来越低 数据库查询隔离级别: select tx_isolation; 数据库设置隔离级别: set global transaction isolation level 级别字符串;
【注】很显然数据库自己有默认隔离级别我们没必要自己改人家这么选择有人家的原因。这里之所以还点出这个知识点是为了防止有面试会问 【注】其实上面这些都是并发产生的一系列问题而已
具体演示可以参考上面视频由于并不常用本笔记这里不讲解。
四、索引数据库的优化索引部分
1 索引的介绍
参考视频 概念索引(index)是帮助数据库 高效获取数据 的 数据结构 简单理解就是一旦给表中的某些字段设置了索引那么查询这些字段的查询速度会提高指数倍当然牺牲的是空间换时间并且进行insert、update、delete的效率会降低因为需要维护更加复杂的数据结构。不过现在空间不值钱在大数据量的情况下查询效率才是最重要的。 无索引就是全表扫描一行一行遍历查找索引优化就是将这种数组或者链表的遍历数据结构变成二叉搜索树这些更加高效的数据结构进行处理。但注意这里使用二叉搜索树只是一个演示MySQL里面的索引优化不一定是二叉搜索树。 索引的优缺点 优点 提高数据查询的效率降低数据库的IO成本通过索引列对数据进行排序降低数据排序的成本降低CPU消耗。 缺点 索引会占用存储空间。索引大大提高了查询效率同时却也降低了insert、update、delete的效率。
2 MySQL中索引采用的结构
参考视频
MySQL数据库支持的索引结构有很多如: Hash索引、BTree索引、Full-Text索引等。我们平常所说的索引如果没有特别指明都是指默认的 BTree 结构组织的索引。 BTree(多路平衡搜索树) 【注】这里要详细理解就要去看数据结构了
3 索引的语法
语法 创建索引 create [unique] index 索引名 on 表名(字段名, …); [unique] 可选是否要创建唯一索引 查看索引 show index from 表名;删除索引 drop index 索引名 on 表名
-- 创建 :为tb_emp表的name字段建立一个索引
create index idx_emp_name on tb_emp(name);-- 査询 :査询 tb_emp 表的索引信息
show index from tb_emp;可以看到我们查询出来了三条索引其中idx_emp_name索引是我们为name这个字段创建的但是另外两个索引怎么回事呢
注意 主键约束的字段会自动创建一个索引上面的id这个主键就自动创建了PRIMARY这个索引 主键字段在建表时会自动创建主键索引。唯一约束的字段会自动创建一个唯一索引上面的username就是一个唯一约束的字段所以自动给我们创建了一个username的索引 添加唯一约束时数据库实际上会添加唯一索引。
-- 删除:删除 tb_emp 表中name字段的索引
drop index idx_emp_name on tb_emp;-- 査询 :査询 tb_emp 表的索引信息
show index from tb_emp;五、多学两招
1 UNION ALL语法和UNION语法两表纵向合并在一起
UNION ALL 是 SQL 中用于合并多个 SELECT 查询结果集的操作符。它将两个或多个查询的结果集简单拼接在一起并返回一个包含所有行的结果集。
基本语法
SELECT column1, column2, ...
FROM table1
UNION ALL
SELECT column1, column2, ...
FROM table2;示例 假设有两个表employees 和 contractors你想将这两个表中的所有人员信息合并到一个结果集中
SELECT employee_id AS id, first_name, last_name, Employee AS type
FROM employees
UNION ALL
SELECT contractor_id AS id, first_name, last_name, Contractor AS type
FROM contractors;注意事项 不删除重复行与 UNION 不同UNION ALL 不会去重它会保留所有行包括重复的行。性能更高因为不需要去重UNION ALL 通常比 UNION 性能更好。结果集顺序UNION ALL 的结果集顺序是不确定的除非显式使用 ORDER BY。 与 UNION 的区别 UNION 会去重即删除结果集中的重复行。UNION ALL 不会去重保留所有行。 特别注意由于是纵向拼接所以 所有 SELECT 语句的列数和数据类型必须一致。
2 SQL中的with查询语法MySQL8.0版本后才支持 极大的简化多层子查询的逻辑
WITH 语法用于定义 公用表表达式CTE, Common Table Expression它是一种在查询中定义临时结果集的方式。 CTE 可以提高查询的可读性和重用性并且可以在同一个查询中多次引用。 WITH语法允许你定义临时结果集这些结果集可以在后续的查询中被引用。 它通常用于简化复杂查询尤其是涉及多层嵌套子查询的情况。
基本语法简单来说就是将一张查出来的临时取个名字临时变量存起来后面的查询就可以基于这个临时表进一步操作 cte_name 是定义的公用表表达式的名字通常是一个临时表或视图。CTE 查询部分就是你定义的查询语句它可以像一个普通的表一样被引用。
WITH cte_name AS (-- CTE 查询SELECT ...
)
SELECT ...
FROM cte_name;特点 可读性WITH语法可以将复杂的查询拆分为多个逻辑部分提高代码的可读性。可重用性在同一个查询中可以多次引用同一个CTE。递归查询WITH语法还支持递归CTE用于处理层次结构数据如树形结构。
基本的 CTE 使用 假设有一个表employees你想查询每个部门的平均工资然后再筛选出高于平均工资的员工
WITH DeptAvgSalary AS (SELECT department_id, AVG(salary) AS avg_salaryFROM employeesGROUP BY department_id
)
SELECT e.employee_id, e.first_name, e.last_name, e.salary, e.department_id
FROM employees e
JOIN DeptAvgSalary d ON e.department_id d.department_id
WHERE e.salary d.avg_salary;递归CTE示例 查询一个组织结构中的所有下属
WITH RECURSIVE Subordinates AS (-- 非递归部分选择顶层员工例如CEOSELECT employee_id, first_name, last_name, manager_idFROM employeesWHERE employee_id 1 -- 假设1是顶级经理UNION ALL-- 递归部分选择下属SELECT e.employee_id, e.first_name, e.last_name, e.manager_idFROM employees eINNER JOIN Subordinates s ON e.manager_id s.employee_id
)
SELECT * FROM Subordinates;
非递归部分我们选择一个顶级经理的员工作为递归的起点。 递归部分通过 JOIN 操作将上级的 id 和下属的 manager_id 进行匹配不断递归查询直到找到所有下属。
注意事项 WITH语法仅在MySQL 8.0及以上版本中可用。CTE的作用范围仅限于当前查询查询结束后CTE会自动销毁。 如果你使用的是MySQL 8.0或更高版本可以充分利用WITH语法来优化和简化查询
