day2-MySQL多表查询、事务、索引

一、多表查询

1、笛卡尔积：select * from a,b。如果a有10条数据，b有3条数据，那么这个查询会有30条数据。就是穷尽a与b的全部情况下的组合，有点像数量关系中的概率问题。

2、解决笛卡尔积：select * from a,b where a.ID = b.ID 。a的非主键ID与b 的主键ID。

3、多表查询分类：①连接查询：内连接，查询A、B表的交集。外连接（左）：查询A整个A表（假设A表在左）。外连接（右）：查询整个B表。②子查询。

4、内连接（交集）
①隐式内连接
格式：select * from A,B where A.ID = B.ID
例子：SELECT tb_emp.name as ‘员工姓名’,tb_dept.name as '所属部门 ’ FROM tb_emp,tb_dept where tb_emp.dept_id = tb_dept.id
注意：查询内容为交集，当A表的某行不和B表匹配时，此行会被筛选出去。
②显示内连接
格式：select * from A inner join b on A.ID = B.ID
例子：SELECT tb_emp.name as ‘员工姓名’,tb_dept.name as '所属部门 ’ FROM tb_emp INNER JOIN tb_dept on tb_emp.dept_id = tb_dept.id
③给表起别名
例子：SELECT a.name as ‘员工姓名’,b.name as '所属部门 ’ FROM tb_emp a INNER JOIN tb_dept b on a.dept_id = b.id
注意：一旦给表起别名后，就不能再用表名了，只能用别名

5、外来接
①左外连接
格式：select * from a left join b on…
注意：不是简单的返回A表的所有结果，应该这样理解，先返回A表与B表的交集，再返回A表与B表没有交集的行
②右外连接
格式：select * from a right join b on…
注意：先返回交集，假如B表是部门表，A表是员工表，B表的一个行（部门）会匹配多个A表的行，此时先把交集都返回了，再返回B表无交集的行，所以因为交集的存在，会返回比B表本身更多的行

6、子查询（在select中嵌套select，有种襁褓中的婴儿的感觉，犬子查询）
格式：select * from table where cols1 = (select * from table2…)
注意：子查询的外部条件可以是insert、update、delete、select
①标量子查询（返回一个值）
例子：select * from tb_emp where tb_emp.dept_id = (select id from tb_dept where NAME = ‘教研部’);
②列子查询（返回一个列，可以是多行，常见于in和not in）
例子：select * from tb_emp where tb_emp.dept_id in (select id from tb_dept where NAME in (‘教研部’,‘咨询部’));
③行子查询（返回一个行，可以是多列）
例子：select * from tb_emp where (entrydate,dept_id) = (select entrydate,dept_id from tb_emp where name = ‘韦一笑’)
注意：这样的写法能提高性能，避免多次重复子查询的出现
④表子查询（返回一张表，常用于in）
例子：select e.*,d.name from (SELECT * from tb_emp where entrydate < ‘2026-01-01’) e,tb_dept d where e.dept_id = d.id

二、事务

1、概念：事务是一组操作的集合，是一个不可分割的工作单位。事务会把所有的操作作为一个整体向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。（MySQL中的事务是自动提交的，也就是说执行完一条SQL语句，就会自动隐式提交事务）。当尚未执行提交操作时，执行的delete操作也不会影响原表，因为事务和原表本身也是两个事务，相互隔离。
2、操作：开启事务（start transaction; / begin;）;提交事务（commit；）；回滚事务（rollback；）
3、四大特性：原子性、一致性、隔离性、持久性
原子性：事务是不可分割的最小执行单元，要么全成功，要么全失败。
一致性：事务完成时，必须使所有数据都保持一致状态。
隔离性：数据库系统提供隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
持久性：事务一旦提交或回滚，那么对数据库的改变就是永久的。

三、索引

1、索引是提升数据库查询的最有效的方式，是数据库优化的一种。
2、索引：帮助数据库高效获取数据的数据结构。
4、优缺点：优点（提高查询效率，降低IO成本和CPU消耗）。缺点（占用磁盘空间，降低增删改的效率。但目前95%的业务都是检索）
5、select * from user where age = 50。如果没有索引，那么就会扫描全部的表，如果不是唯一的行，则会在遇到第一个50时继续查找，直至全部扫描完，这叫全表扫描。

6、索引的结构：MySQL支持的索引有很多，比如Hash索引、B+Tree索引、Full-Text索引。我们平常所说的索引，如果没有特殊说明，则为MySQL默认的B+Tree索引。B+Tree多路平衡搜索树。

降低了树的高度，扩宽树的面积，提高查询速度

7、操作语法
创建索引：create [unique] index 索引名 on 表名 (字段名，…);、
create index suoyin on tb_emp(username);

查看索引：show index from 表名；
show index from tb_emp

删除索引：drop index 索引名 on 表名
drop index suoyin on tb_emp