长春火车站最新消息,上海千途网站建设,网网站基础建设优化知识,公司网站建设怎么做目录 多表查询简介内连接查询 join on外连接查询 left join、right join子连接查询标量子查询列子查询 (in、not in)行子查询表子查询 多表查询案例 事务事务介绍操作 start transaction、commit、rollback事务四大特性(面试题) 索引索引介绍索引原理索引语法 index 上次学习了… 目录 多表查询简介内连接查询 join on外连接查询 left join、right join子连接查询标量子查询列子查询 (in、not in)行子查询表子查询 多表查询案例 事务事务介绍操作 start transaction、commit、rollback事务四大特性(面试题) 索引索引介绍索引原理索引语法 index 上次学习了MySQL的单表查询和多表设计。 这次学习MySQL的多表查询、事务和索引。
多表查询
简介
多表查询查询时从多张表中获取所需数据
单表查询的SQL语句select 字段列表 from 表名;
那么要执行多表查询只需要使用逗号分隔多张表即可如 select 字段列表 from 表1, 表2;
先准备一些数据
#建议创建新的数据库
create database db04;
use db04;-- 部门表
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());查询用户表和部门表中的数据
select * from tb_emp , tb_dept;此时,我们看到查询结果中包含了大量的结果集总共85条记录而这其实就是员工表所有的记录(17行)与部门表所有记录(5行)的所有组合情况这种现象称之为笛卡尔积。
如何去除无效的笛卡尔积呢只需要给多表查询加上连接查询的条件即可:
select * from tb_emp , tb_dept where tb_emp.dept_id tb_dept.id ;由于id为17的员工没有dept_id字段值所以在多表查询时根据连接查询的条件并没有查询到。 多表查询可以分为 连接查询 内连接相当于查询A、B交集部分数据 外连接 左外连接查询左表所有数据(包括两张表交集部分数据) 右外连接查询右表所有数据(包括两张表交集部分数据) 子查询又叫嵌套查询SQL语句中嵌套select语句 内连接查询 join on
内连接查询查询两表或多表中交集部分数据。
内连接从语法上可以分为隐式内连接和显式内连接
隐式内连接语法
select 字段列表 from 表1 , 表2 where 条件 ... ;显式内连接语法
select 字段列表 from 表1 [ inner ] join 表2 on 连接条件 ... ;案例查询员工的姓名及所属的部门名称
隐式内连接实现
select tb_emp.name , tb_dept.name -- 分别查询两张表中的数据
from tb_emp , tb_dept -- 关联两张表
where tb_emp.dept_id tb_dept.id; -- 消除笛卡尔积显式内连接实现
select tb_emp.name , tb_dept.name
from tb_emp inner join tb_dept
on tb_emp.dept_id tb_dept.id;起别名的必要: 起别名语法
tableA as 别名1 , tableB as 别名2 ;
tableA 别名1 , tableB 别名2 ; # 空格后跟别名使用了别名的多表查询
select t1.name , t2.name
from tb_emp t1 join tb_dept t2
on t1.dept_id t2.id;想要给字段加上别名也行:
select t1.name as 姓名 , t2.name as 部门
from tb_emp t1 join tb_dept t2 # inner可省略不写
on t1.dept_id t2.id;注意事项: 一旦为表起了别名就不能再使用表名来指定对应的字段了此时只能够使用别名来指定字段。 外连接查询 left join、right join
外连接分为两种左外连接 和 右外连接。
左外连接语法结构
select 字段列表 from 表1 left [ outer ] join 表2 on 连接条件 ... ;左外连接相当于查询表1(左表)的所有数据当然也包含表1和表2交集部分的数据。 右外连接语法结构
select 字段列表 from 表1 right [ outer ] join 表2 on 连接条件 ... ;右外连接相当于查询表2(右表)的所有数据当然也包含表1和表2交集部分的数据。 案例查询员工表中所有员工的姓名, 和对应的部门名称
-- 左外连接以left join关键字左边的表为主表查询主表中所有数据以及和主表匹配的右边表中的数据
select emp.name , dept.name
from tb_emp AS emp left join tb_dept AS dept on emp.dept_id dept.id;案例查询部门表中所有部门的名称, 和对应的员工名称
-- 右外连接
select dept.name , emp.name
from tb_emp AS emp right join tb_dept AS depton emp.dept_id dept.id;注意事项 左外连接和右外连接是可以相互替换的只需要调整连接查询时SQL语句中表的先后顺序就可以了。而我们在日常开发使用时更偏向于左外连接。 子连接查询
SQL语句中嵌套select语句称为嵌套查询又称子查询。
SELECT * FROM t1 WHERE column1 ( SELECT column1 FROM t2 ... );子查询外部的语句可以是insert / update / delete / select 的任何一个最常见的是 select。 子查询可以书写的位置
where之后from之后select之后 根据子查询结果的不同分为
标量子查询子查询结果为单个值[一行一列]列子查询子查询结果为一列但可以是多行行子查询子查询结果为一行但可以是多列表子查询子查询结果为多行多列[相当于子查询结果是一张表] 标量子查询
子查询返回的结果是单个值(数字、字符串、日期等)最简单的形式这种子查询称为标量子查询。
案例1查询教研部的所有员工信息
-- 1.查询教研部部门ID
select id from tb_dept where name 教研部; #查询结果2
-- 2.根据教研部部门ID, 查询员工信息
select * from tb_emp where dept_id 2;-- 合并出上两条SQL语句
select * from tb_emp where dept_id (select id from tb_dept where name 教研部);案例2查询在 “方东白” 入职之后的员工信息
-- 1.查询方东白的入职日期
select entrydate from tb_emp where name 方东白; #查询结果2012-11-01
-- 2.查询指定入职日期之后入职的员工信息
select * from tb_emp where entrydate 2012-11-01;-- 合并以上两条SQL语句
select * from tb_emp where entrydate (select entrydate from tb_emp where name 方东白);列子查询 (in、not in)
子查询返回的结果是一列(可以是多行)这种子查询称为列子查询。
常用的操作符
操作符描述IN在指定的集合范围之内多选一NOT IN不在指定的集合范围之内
案例查询教研部和咨询部的所有员工信息
-- 1.查询销售部和市场部的部门ID
select id from tb_dept where name 教研部 or name 咨询部; #查询结果3,2 查询结果是一列多行
-- 2.根据部门ID, 查询员工信息
select * from tb_emp where dept_id in (3,2);-- 合并以上两条SQL语句
select * from tb_emp where dept_id in (select id from tb_dept where name 教研部 or name 咨询部);行子查询
子查询返回的结果是一行(可以是多列)这种子查询称为行子查询。
常用的操作符 、 、IN 、NOT IN
案例查询与韦一笑的入职日期及职位都相同的员工信息
-- 查询韦一笑的入职日期 及 职位
select entrydate , job from tb_emp where name 韦一笑; #查询结果 2007-01-01 , 2 查询结果是一行多列
-- 查询与韦一笑的入职日期及职位相同的员工信息
select * from tb_emp where (entrydate,job) (2007-01-01,2);-- 合并以上两条SQL语句
select * from tb_emp where (entrydate,job) (select entrydate , job from tb_emp where name 韦一笑);表子查询
子查询返回的结果是多行多列常作为临时表这种子查询称为表子查询。
案例查询入职日期是 “2006-01-01” 之后的员工信息 , 及其部门信息
# 1. 查询入职日期是 2006-01-01 之后的员工信息
select * from tb_emp where entrydate 2006-01-01;# 2. 基于查询到的员工信息在查询对应的部门信息
select e.*, d.* from (select * from tb_emp where entrydate 2006-01-01) e left join tb_dept d on e.dept_id d.id ;多表查询案例 1.查询价格低于 10元 的菜品的名称 、价格 及其 菜品的分类名称
/*查询技巧明确1查询需要用到哪些字段菜品名称、菜品价格 、 菜品分类名明确2查询的字段分别归属于哪张表菜品表[菜品名称、菜品价格]分类表[分类名]明确3如查多表建立表与表之间的关联菜品表.caategory_id 分类表.id其他其他条件、其他要求价格 10
*/查询所有价格在 10元(含)到50元(含)之间 且 状态为起售的菜品名称、价格及其分类名称(即使菜品没有分类 , 也要将菜品查询出来) 查询每个分类下最贵的菜品, 展示出分类的名称、最贵的菜品的价格 查询各个分类下 菜品状态为 “起售” , 并且 该分类下菜品总数量大于等于3 的 分类名称
/*查询技巧明确1查询需要用到哪些字段分类名称、菜品总数量明确2查询用到的字段分别归属于哪张表分类表[分类名]菜品表[菜品状态]明确3如查多表建立表与表之间的关联菜品表.caategory_id 分类表.id其他其他条件、其他要求条件菜品状态 1 (1表示起售)分组分类名分组后条件 总数量 3
*/查询出 “商务套餐A” 中包含了哪些菜品 展示出套餐名称、价格, 包含的菜品名称、价格、份数 查询出低于菜品平均价格的菜品信息 (展示出菜品名称、菜品价格)
-- 1.计算菜品平均价格
select avg(price) from dish; -- 查询结果37.736842
-- 2.查询出低于菜品平均价格的菜品信息
select * from dish where price 37.736842;-- 合并以上两条SQL语句
select * from dish where price (select avg(price) from dish);事务
事务介绍
在实际的业务开发中有些业务操作要多次访问数据库。一个业务要发送多条SQL语句给数据库执行。
需要将多次访问数据库的操作视为一个整体来执行要么所有的SQL语句全部执行成功。如果其中有一条SQL语句失败就进行事务的回滚所有的SQL语句全部执行失败。
简而言之事务是一组操作的集合它是一个不可分割的工作单位。事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求即这些操作要么同时成功要么同时失败。
事务作用保证在一个事务中多次操作数据库表中数据时要么全都成功,要么全都失败。
举例 场景学工部整个部门解散了该部门及部门下的员工都需要删除了。 -- 删除学工部delete from dept where id 1; -- 删除成功-- 删除学工部的员工delete from emp where dept_id 1; -- 删除失败操作过程中出现错误造成删除没有成功 或者sql语句写错了导致删除失败问题如果删除部门成功了而删除该部门的员工时失败了此时就造成了数据的不一致。 要解决上述的问题就需要通过数据库中的事务来解决。 操作 start transaction、commit、rollback
MYSQL中有两种方式进行事务的操作
1. 自动提交事务即执行一条sql语句提交一次事务。默认MySQL的事务是自动提交
2. 手动提交事务先开启再提交手动提交事务使用步骤
第1种情况开启事务 执行SQL语句 成功 提交事务
第2种情况开启事务 执行SQL语句 失败 回滚事务事务操作有关的SQL语句
SQL语句描述start transaction; 或者 begin ;开启手动控制事务commit;提交事务rollback;回滚事务 使用事务控制删除部门和删除该部门下的员工的操作
-- 开启事务
start transaction ;-- 删除学工部
delete from tb_dept where id 1;-- 删除学工部的员工
delete from tb_emp where dept_id 1;上述的这组SQL语句如果如果执行成功则提交事务
-- 提交事务 (成功时执行)
commit ;-- 注意这条commit语句还没执行时如果上面的删除语句已经执行了
-- 此时在命令行里查找学工部和员工可以发现id为1的部门和员工已经删除了
-- 但是如果手动打开数据库里的员工表和部门表查看可以发现还没被删除
-- 因为事务还没有被commit所以数据库里的数据还没被删除
-- 如果不执行commit执行rollback然后在命令行里再查询
-- 就可以发现id为1的部门和员工又恢复了上述的这组SQL语句如果如果执行失败则回滚事务
-- 回滚事务 (出错时执行)
rollback ;事务四大特性(面试题)
面试题事务有哪些特性
原子性Atomicity事务是不可分割的最小单元要么全部成功要么全部失败。解释原子性是指事务包装的一组sql是一个不可分割的工作单元事务中的 操作要么全部成功要么全部失败。一致性Consistency事务完成时必须使所有的数据都保持一致状态。解释一个事务完成之后数据都必须处于一致性状态。如果事务成功的完成那么数据库的所有变化将生效。如果事务执行出现错误那么数据库的所有变化将会被回滚(撤销)返回到原始状态。隔离性Isolation数据库系统提供的隔离机制保证事务在不受外部并发操作影响的独立环境下运行。解释多个用户并发的访问数据库时一个用户的事务不能被其他用户的事务 干扰多个并发的事务之间要相互隔离。一个事务的成功或者失败对于其他的事务是没有影响。持久性Durability事务一旦提交或回滚它对数据库中的数据的改变就是永久的。解释一个事务一旦被提交或回滚它对数据库的改变将是永久性的哪怕数 据库发生异常重启之后数据亦然存在。 事务的四大特性简称为ACID 索引
索引介绍
索引(index)是帮助数据库高效获取数据的数据结构 。 简单来讲就是使用索引可以提高查询的效率。
没有使用索引的查询 添加索引后查询
-- 添加索引
create index idx_sku_sn on tb_sku (sn); #在添加索引时也需要消耗时间-- 查询数据使用了索引
select * from tb_sku where sn 100000003145008;优点
提高数据查询的效率降低数据库的IO成本。通过索引列对数据进行排序降低数据排序的成本降低CPU消耗。
缺点
索引会占用存储空间。索引大大提高了查询效率同时却也降低了insert、update、delete的效率。 索引原理
MySQL数据库支持的索引结构有很多如Hash索引、BTree索引、Full-Text索引等。 我们平常所说的索引如果没有特别指明都是指默认的 BTree 结构组织的索引。
之前所学习的树结构
当我们向二叉查找树保存数据时是按照从大到小(或从小到大)的顺序保存的此时就会形成一
个单向链表搜索性能会打折扣。选择平衡二叉树或者是红黑树来解决上述问题。红黑树也是一棵平衡的二叉树但是在Mysql数据库中并没有使用二叉搜索数或二叉平衡数或红黑树来作为索引的结构。思考采用二叉搜索树或者是红黑树来作为索引的结构有什么问题答案
说明如果数据结构是红黑树那么查询1000万条数据根据计算树的高度大概是23左右这样
确实比之前的方式快了很多但是如果高并发访问那么一个用户有可能需要23次磁盘IO那么
100万用户那么会造成效率极其低下。所以为了减少红黑树的高度那么就得增加树的宽度就
是不再像红黑树一样每个节点只能保存一个数据可以引入另外一种数据结构一个节点可以保存
多个数据这样宽度就会增加从而降低树的高度。这种数据结构例如BTree就满足。下面我们来看看BTree(多路平衡搜索树)结构中如何避免这个问题 BTree结构
每一个节点可以存储多个key有n个key就有n个指针
节点分为叶子节点、非叶子节点叶子节点就是最后一层子节点所有的数据都存储在叶子节点上非叶子节点不是树结构最下面的节点用于索引数据存储的的是key指针
为了提高范围查询效率叶子节点形成了一个双向链表便于数据的排序及区间范围查询拓展
非叶子节点都是由key指针域组成的一个key占8字节一个指针占6字节而一个节点总共容
量是16KB那么可以计算出一个节点可以存储的元素个数16*1024字节 / (86)1170个元
素。查看mysql索引节点大小show global status like innodb_page_size;-- 节点大小16384(16*1024)当根节点中可以存储1170个元素那么根据每个元素的地址值又会找到下面的子节点每个子节
点也会存储1170个元素那么第二层即第二次IO的时候就会找到数据大概是
1170*1170135W。也就是说BTree数据结构中只需要经历两次磁盘IO就可以找到135W条数
据。对于第二层每个元素有指针那么会找到第三层第三层由key数据组成假设key数据总大小
是1KB而每个节点一共能存储16KB所以一个第三层一个节点大概可以存储16个元素(即16条
记录)。那么结合第二层每个元素通过指针域找到第三层的节点第二层一共是135W个元素那么
第三层总元素大小就是135W*16结果就是2000W的元素个数。结合上述分析BTree有如下优点
1.千万条数据BTree可以控制在小于等于3的高度
2.所有的数据都存储在叶子节点上并且底层已经实现了按照索引进行排序还可以支持范围
查询叶子节点是一个双向链表支持从小到大或者从大到小查找 索引语法 index
创建索引
create [ unique ] index 索引名 on 表名 (字段名,... ) ;为tb_emp表的name字段建立一个索引:
create index idx_emp_name on tb_emp(name);在创建表时如果添加了主键和唯一约束就会默认创建主键索引(性能最好)、唯一索引: 查看索引:
show index from 表名;案例查询 tb_emp 表的索引信息
show index from tb_emp;删除索引
drop index 索引名 on 表名;案例删除 tb_emp 表中name字段的索引
drop index idx_emp_name on tb_emp;
