MySQL基础之多表操作(多表查询,事务,索引)

已于 2024-04-23 21:18:51 修改
阅读量664 收藏 20
点赞数 15
分类专栏： MySQL 文章标签： mysql
于 2024-04-23 21:16:20 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_57030936/article/details/138122125
版权

目录

一、多表关系

1.1 一对多

  • 一个部门,对应多个员工; 一个员工,只能归属一个部门

  • 在这里插入图片描述

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

1.2 外键约束

  • 1.1 只是在逻辑上知道员工和部门多对一的关系,但是数据库层面并没有进行关联
  • 插入测试数据,假如把部门表ID为1的记录直接删除,员工表不会受到任何影响,但此时员工表已经出现数据不一致不完整的问题了,还有部分员工仍归属于被删掉的部门下
  • 外键约束用于解决多表之间数据的一致性和完整性问题
  • 设置外键关联之后,1部门就删不了了,5部门还是能删,因为没有员工属于5部门
    在这里插入图片描述
alter table tb_emp
	add constraint tb_emp_fk_dept_id
		foreign key (dept_id) references tb_dept (id);

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

删除外键:

alter table tb_emp drop foreign key tb_emp_fk_dept_id;

物理外键与逻辑外键:
在这里插入图片描述

1.3 一对一

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

1.4 多对多

在这里插入图片描述

二、多表查询

2.1 测试数据准备

-- 多表查询: 数据准备
-- 部门管理
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());

2.2 笛卡尔积

select * from tb_emp,tb_dept; -- 这是笛卡尔积 会有5*17条记录 每五条里面只有一条是有效的数据
select * from tb_emp,tb_dept where tb_emp.dept_id = tb_dept.id;

在这里插入图片描述

2.3 内连接

  • 第十七个员工 内连接就查不出来(不属于交集) 要用外连接
    在这里插入图片描述
    在这里插入图片描述

案例:

-- 内连接求的其实就是笛卡尔积 要用where或者on消除无效的数据
-- A. 查询员工的姓名 , 及所属的部门名称 (隐式内连接实现)
-- 这里第十七个员工的所属部门ID是NULL 所以他和部门表 就相当于是没有关系的 不属于交集部分 查不出来
select tb_emp.name as '姓名', tb_dept.name as '所属部门'
from tb_emp,tb_dept
where tb_emp.dept_id = tb_dept.id;

-- 起别名
select e.name, d.name
from tb_emp e,tb_dept d
where e.dept_id = d.id;

-- B. 查询员工的姓名 , 及所属的部门名称 (显式内连接实现)
select tb_emp.name, tb_dept.name
from tb_emp join tb_dept
on tb_emp.dept_id = tb_dept.id;

2.4 外连接

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

案例:

-- =============================== 外连接 ============================
-- A. 查询员工表 所有 员工的姓名, 和对应的部门名称 (左外连接)
-- 能查到17条记录  16(交集部分数据)+1(员工表交集外的一条数据)
select e.name, d.name
from tb_emp e left join tb_dept d
on e.dept_id = d.id;

-- B. 查询部门表 所有 部门的名称, 和对应的员工名称 (右外连接)
-- 能查到18条记录()  16(交集部分数据)+2(部门表交集外的两条数据)
select  d.name '部门名称',e.name '员工名称'
from tb_emp e right join tb_dept d
on e.dept_id = d.id;
-- 换换位置就是左右连接了
select e.name, d.name
from tb_dept d left join tb_emp e
on e.dept_id = d.id;

2.5 子查询

在这里插入图片描述
子查询分类:
在这里插入图片描述

1.标量子查询

在这里插入图片描述

案例:

-- 标量子查询
-- 查询 "教研部" 的所有员工信息
-- a. 查询 教研部 的部门ID - tb_dept
select id from tb_dept where name = '教研部'; -- 结果是2

-- b. 再查询该部门ID下的员工信息 - tb_emp
select * from tb_emp where dept_id = 2; -- 拿到上面的结果2

-- 一条SQL搞定
select * from tb_emp 
where 
dept_id = (select id from tb_dept where name = '教研部');

-- 查询在 "方东白" 入职之后的员工信息
-- a. 查询 方东白 的入职时间
select entrydate from tb_emp where name = '方东白';

-- b. 再查询在 "方东白" 入职之后的员工信息
select * from tb_emp
where 
entrydate > (select entrydate from tb_emp where name = '方东白');

2.列子查询

在这里插入图片描述
案例:

-- 列子查询
-- 查询 "教研部" 和 "咨询部" 的所有员工信息
-- a. 查询 "教研部" 和 "咨询部" 的部门ID - tb_dept
select id from tb_dept where name in ('教研部','咨询部');
select id from tb_dept where name = '教研部' or name = '咨询部';

-- b. 根据部门ID, 查询该部门下的员工信息 - tb_emp
select * from tb_emp where dept_id in (3,2);
select * from tb_emp where dept_id in (select id from tb_dept where name in ('教研部','咨询部'));

3.行子查询

在这里插入图片描述
案例:

-- 行子查询
-- 查询与 "韦一笑" 的入职日期 及 职位都相同的员工信息 ;
-- a. 查询 "韦一笑" 的入职日期 及 职位
select entrydate,job from tb_emp where name = '韦一笑';


-- b. 查询与其入职日期 及 职位都相同的员工信息 ;
-- 方式一
select * from tb_emp where entrydate = '2007-01-01' and job = 2;
select * from tb_emp where entrydate =  (select entrydate from 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.表子查询

在这里插入图片描述
案例:


-- 表子查询
-- 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门名称
-- a. 查询入职日期是 "2006-01-01" 之后的员工信息
select * from tb_emp where entrydate > '2006-01-01';

-- b. 查询这部分员工信息及其部门名称 - tb_dept
-- e作为临时表 和d表做笛卡尔积 然后筛选出有效数据
select e.*,d.name 
from (select * from tb_emp where entrydate > '2006-01-01') e,tb_dept d
where e.dept_id = d.id;

三、事务

3.1 问题场景引入

有可能是SQL代码写错了或各种原因,导致该部门的员工没有被删除.
模拟这个场景.
在这里插入图片描述

3.2 概念

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

3.3 事务操作

在这里插入图片描述

正常情况:执行完没错,提交事务
在这里插入图片描述

异常情况:执行出错,回滚事务.可以把删除掉的数据恢复,保证了操作前后数据的一致性

3.4 事务的四大特性ACID

在这里插入图片描述

四、索引

4.1 概念

在这里插入图片描述

4.2 优缺点

在这里插入图片描述

4.3 MySQL的索引结构-B+树

在这里插入图片描述

在这里插入图片描述

4.4 索引操作

在这里插入图片描述

案例:

-- ================================ 索引 =============================
select * from tb_sku where sn = '100000003145008'; -- 14s
create index idx_sku_sn on tb_sku (sn);
select * from tb_sku where sn = '100000003145008'; -- 速度贼快

-- 创建 : 为tb_emp表的name字段建立一个索引 .
create index idx_emp_name on tb_emp (name);

-- 查询 : 查询 tb_emp 表的索引信息 .
show index from tb_emp;

-- 删除: 删除 tb_emp 表中name字段的索引 .
drop index idx_emp_name on tb_emp;
朱一头zcy
关注
  • 15
    点赞
  • 踩
  • 20
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL多表联查如何建立索引？
chris3will的博客
08-30 2741
在 MySQL 数据库中，设计索引主要是为了提高查询的效率，降低数据库的压力。当我们进行多表查询时，正确设计索引非常重要。
多表查询添加索引
qq_41953852的博客
05-03 3767
共有t_member（成员表）t_organization（单位表）t_member_t_organization（成员单位映射表） select语句如下 SELECT tm.id,tm.name,birth, CASE sex WHEN '1' THEN '男' WHEN '2' THEN '女' ELSE '其他' END AS sex, is_...
MySQL：多表关联查询添加索引
weixin_43608796的博客
08-12 9876
目标sql: select * from copy666 jee left join ga_bu ogb on jee.ga_bu_code = ogb.bu_code left join ga_coa coa on jee.ga_coa_sub = coa.sub AND jee.ga_coa_obj = coa.obj where jee.year = '2020' select * 比较慢 9w条数据就用了58s。 explain语句 优化： 1、给
SQL - 多表建立索引
qq_41572697的博客
05-09 4292
1.建表 查看class 表的数据 查看 book 表单的数据 card 为外键， 关联2 个表 案例： type 是ALL ， row 20 性能差 加索引， 先给book 表的card 字段加索引 book 是右表， 加了索引。 所以性能比class 表高 将右表book 的index 删除， 将index 加在坐标class上 性能如下 不如将index 在在右表...
MySQL两表关联的连接表该如何创建索引？
热门推荐
YiYing's sharing
05-15 1万+
原文地址：原文可看大图 问题介绍   创建数据库的索引，可以选择单列索引，也可以选择创建组合索引。   遇到如下这种情况，用户表（user）与部门表（dept）通过部门用户关联表（deptuser）连接起来，如下图所示： 表间关系   问题就是，在这个关联表中该如何建立索引呢？ 针对该表，有如下四种选择: 针对于user_uuid建立单列索引idx_user 针对于use...
2024最新MySQL索引事务和多表
最新发布
03-20
2024最新MySQL索引事务和多表
MySQL使用游标批量处理进行表操作
09-10
主要介绍了MySQL使用游标批量进行表操作，包括批量添加索引、批量添加字段等，感兴趣的小伙伴们可以参考一下
Mysql事务及索引
12-15
一、数据库索引概述； 二、数据库索引分类； 三、Mysql数据库管理索引； 四、数据库事务概述； 五、数据库事务特性； 六、Mysqk数据库管理事务；
mysql 事务理解及相关操作
12-14
一、Mysql表引擎 Myisam: Mysql 5.5 版本之前的默认表引擎 ...在 MySQL 中只有使用了 Innodb 数据库引擎 的数据库或表才支持事务。 事务是一个不可再分的工作单元；通常将一组有序的增删改操作看做一个独立的执行单
MySQL在多表上创建视图方法
12-16
MySQL中，在两个或者以上的基本表上创建视图 在student表和stu_info表上，创建stu_class视图，查询出s_id号、姓名和班级 ... 您可能感兴趣的文章:Mysql数据库高级用法之视图、事务、索引、自连接、
sql 中 exists和 in如何选择
znanj的博客
09-09 837
如果外部查询是“大”和内部查询是“小”，IN是一般效率比EXISTS好。 如果外部查询是“小”和内部查询是“大”，那么EXISTS是相当有效的。
SOLR对多个（关联）表创建索引
aaa1117a8w5s6d的专栏
02-19 3654
一、无关联多表创建索引 1.1数据库准备 　　首先在数据库创建一张表，本来有一个tuser表了，我又创建了一个role表，就两三个字段，没什么特殊的，然后在里面随便添加几条数据即可，看一下数据库： 1.2data-config.xml 　　数据库创建完成之后就是修改data-config.xml文件了，因为数据库跟solr之间的关联主要就是建立在这个文件上面的，所以接下来就
多表查询的索引优化
weixin_44408619的博客
08-29 1119
1.建表语句 建立两张表用于索引优化，两张表通过cid进行连接 create table teacher2( tid int(4) primary key, cid int(4) not null ); create table course2(cid int(4) not null,cname varchar(20) not null); insert into teacher2 values (1,2); insert into teacher2 values (2,1);
sql多表联查，索引
mymk的博客
12-27 3887
sql多表联查，索引
单表查询如何建立索引让你的查询语句性能达到最高
唐欢
12-19 9665
在我的上一篇博客Explain 检测SQL语句的性能>>中简单的介绍了explain关键字查询结果字段.这篇博客将介绍如何用正确使用索引提高查询效率.     索引用于快速找出在某个列中有一特定值的行。不使用索引，MySQL必须从第1条记录开始然后读完整个表直到找出相关的行。表越大，花费的时间越多。如果表中查询的列有一个索引，MySQL能快速到达一个位置去搜寻到数据文件的中间，没有必要看所有数
sql多表查询语句优化——建立索引
qq_41035592的博客
09-19 3137
发现问题 今天通过反馈得知上线的项目由于数据量变大查询效率变得很低，页面响应需要几十秒，造成用户等待时间极长。因此对查询语句进行优化 分析问题 因为是多表查询，因此我们首先来看查询方法是否存在问题，然后再检查是否存在主表数据量远远大于子表的情况，之后再检查是否有建立索引查询。 解决问题 首先我们贴出查询语句 然后我们贴出在navicat中此语句的查询时间 我们执行查询语句发现只有7200条数据...
图文详解MySQL中两表关联的连接表如何创建索引
Evan.Zhou的博客
07-25 2807
https://www.jb51.net/article/113942.htm
批量获取多个表的创建索引语句
心似海
06-08 349
批量获取多个表的创建索引语句 一个朋友提供的set echo offset heading offset pages 50000 lines 500set trimspool onset feedback offset long 99999spool c:\gao.sqlselect dbms_metadata.get_ddl('INDEX',index_name)||' parallel ...
mysql查询语句会锁表吗?
05-25
MySQL在执行查询时，会根据不同的隔离级别（Isolation Level）对数据做出不同的锁定。 在默认的隔离级别下（Repeatable Read），MySQL会对读取的数据行进行共享锁定（Shared Lock），这样可以保证其他事务可以读取该行数据，但不能修改该行数据，直到当前事务释放该行的共享锁。 在一些情况下，MySQL会对整张表进行锁定，例如当执行ALTER TABLE、OPTIMIZE TABLE等操作时，需要对整张表进行独占锁定（Exclusive Lock）。 因此，对于查询语句来说，并不是所有情况下都会锁定表。具体是否会锁定表，需要根据查询语句的类型、隔离级别等因素进行判断。一般情况下，对于简单的SELECT语句，不会对表进行锁定。但是如果查询语句中包含JOIN、GROUP BY、ORDER BY等操作，可能会对表进行锁定。 为了避免不必要的表锁定，可以采用合适的索引、优化查询语句、调整隔离级别等措施来减少表锁定的发生。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值