菜鸟之路Day32一一多表查询，事物，索引
作者：blue
时间：2025.5.14
[TOC]
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_dept
where tb_emp.dept_id = tb_dept.id;
-- B. 查询员工姓名，及所属的部门名称（显示内连接实现）
select tb_emp.name,tb_dept.name from db01.tb_emp
inner 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_emp
left outer join tb_dept on tb_dept.id = tb_emp.dept_id;
-- B. 查询部门表 所有 部门名称，和对应的员工名称 （右外连接）
select tb_emp.name,tb_dept.name from tb_emp
right 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_emp
where 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_emp
where tb_emp.dept_id
in (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 d
where 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;
注意事项：
主键字段，在建表时，会自动创建主键索引
添加唯一约束时，数据库实际上会添加唯一索引
