菜鸟之路Day32一一多表查询，事物，索引

作者：blue

时间：2025.5.14

文章目录

菜鸟之路Day32一一多表查询，事物，索引
- 1.多表查询
- - 1.1内连接
  - 1.2外连接
  - 1.3子查询
- 2.事物
- 3.索引

1.多表查询

连接查询

内连接：相当于查询A，B交集部分的数据

外连接：

左外连接：查询左表所有数据（包括两张表交集部分的数据）

右外连接：查询右表所有数据（包括两张表交集部分的数据）

子查询

1.1内连接

语法:

隐式内连接：select 字段列表 from 表1，表2 where 条件...;显示内连接：select 字段列表 from 表1 [inner] join 表2 on 连接条件

练习：

-- A. 查询员工姓名，及所属部门名称（隐式内连接）
select tb_emp.name,tb_dept.name from tb_emp,tb_deptwhere tb_emp.dept_id = tb_dept.id;-- B. 查询员工姓名，及所属的部门名称（显示内连接实现）
select tb_emp.name,tb_dept.name from db01.tb_empinner join tb_dept on tb_emp.dept_id = tb_dept.id

1.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_empleft outer join tb_dept on tb_dept.id = tb_emp.dept_id;-- B. 查询部门表 所有 部门名称，和对应的员工名称 （右外连接）
select tb_emp.name,tb_dept.name from tb_empright outer join tb_dept on tb_dept.id = tb_emp.dept_id;

1.3子查询

介绍：SQL语句中嵌套的select语句，称为嵌套查询，又称为子查询

形式:

select * from t1 where column1 = (select column1 from t2 ...);

子查询外部的语句可以是insert/update/delete/select 的任何一个，最常见的是select

标量子查询：子查询返回的结果为单个值

-- 标量子查询
-- A. 查询"教研部"的所有员工信息
-- a. 查询 教研部 的部门 ID - tb_dept
select id from tb_dept where name='教研部';-- b. 再查询该部门ID下的员工信息 - tb_emp
select * from tb_empwhere dept_id = (select id from tb_dept where name='教研部');

列子查询：子查询返回的结果为一列

-- 列子查询
-- A. 查询"教研部"和"咨询部"的所有员工信息
-- a. 查询"教研部"和"咨询部"的部门ID - tb_dept
select id from tb_dept where name='教研部' or name='咨询部';-- b. 根据部门ID，查询该部门下的员工信息 - tb_dept
select * from tb_empwhere tb_emp.dept_idin (select id from tb_dept where name='教研部' or name='咨询部');

行子查询：子查询返回的结果为一行（但可以是多列）

select * from tb_emp where (entrydate,job) = (select entrydate,job from tb_emp where name= 'XXX');

表子查询：子查询返回的结果为多行多列

-- a. 查询入职日期是“2006-01-01”之后的员工信息
select * from tb_emp where entry_time > '2006-01-01';-- b. 查询这部分员工信息及其部门名称 - tb_dept
select e.*,d.name from (select * from tb_emp where entry_time>'2006-01-01') e,tb_dept dwhere e.dept_id = d.id;

2.事物

事物是一组操作的集合，它是一个不可分割的工作单位。事物会把所有操作作为一个整体一起向系统提交或者撤销操作请求，即这些操作要么同时成功，要么同时失败

-- 事物控制-- 开启事物
start transaction;
或
begin;-- 提交事物
commit;-- 回滚事物
rollback;

例子：

-- 开启事物
start transaction;-- 删除部门
delete from tb_dept where id = 3;-- 删除部门下的员工
delete from tb_emp where dept_id = 3;-- 提交事物
commit;-- 回滚事物
rollback;

事物的四大特性

原子性：事物是不可分割的最小单元，要么全部成功，要么全部失败

一致性：事物完成时，必须使所有数据都保持一致的状态

隔离性：数据库系统提供的隔离机制，保证事物在不受外部并发操作影响的独立环境下运行

持久性：事物一旦提交或回滚，它对数据库中的数据的改变就是永久的

3.索引

索引是帮助数据库高效获取数据的数据结构（利用树形结构）

优点：提高数据查询的效率，降低数据库的IO成本

通过索引对数据进行排序，降低数据排序的成本，降低CPU消耗

缺点：索引会占用存储空间

索引大大提高了查询效率，同时也降低了insert，update，delete的效率

索引的结构

MySQL中：B+Tree（多路平衡搜索树）

每一个节点，可以存储多个key（有n个key，就有n个指针）

所有数据都存在叶子节点，非叶子节点仅用于索引数据

叶子节点形成了一颗双向链表，便于数据的排序及区间范围查询

语法：

创建索引：create [unique] index 索引名 on 表名(字段名....);查看索引：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;-- 删除：删除tb_emp表中name字段的索引
drop index index_emp_name on tb_emp;