本文共 3585 字，大约阅读时间需要 11 分钟。

MySQL多表查询 查询多张表才能得到我们想要的数据

查询多张表才能得到我们想要的数据

找孙悟空在哪个部门

孙悟空 跟 开发部 市场部 财务部 组合 （左表的每条数据和右表的每个数据连接）但实际上 悟空只是开发部 所以 A.外键值=B.主键值 员工表.部门id=部门表.id； 这种现象称为笛卡尔积 去掉笛卡尔积的条件称为：表连接条件

字段取别名 表也能取别名

表连接查询 同时查询多张表

去掉笛卡尔积就是表连接查询

上面的是隐式内连接 on后面加表连接条件，去掉笛卡尔积。 建议都用显示内连接，将where改成on on + 表连接条件 where + 查询条件 select * from e inner join d on e.dept_id = d.id;

显示内连接和隐式内连接效果一样，但常用显示内连接。

左外连接 满足(表连接条件的)员工表.外键=部门表.主键的条件的内容显示，员工表不满足要求的也显示

满足表连接条件的内容显示，右表不满足表连接条件的内容也显示。

多表查询

列名不写的话 id的值为null 写全部列名不写id列的话，null可以不写

子查询 先查一张表 儿子和爸爸 子查询的三种情况 值（where xxx < 单行单列），多行单列（in），多行多列（一张表）

一个查询语句的结果作为另一个查询语句的一部分

先执行子查询 有可能作为条件(作为判断的值)，表，多行单列（查询满足条件的一个字段）和多行多列 （满足where条件的所有字段和部分字段） 作为判断的值 select * from emp where salary = (select MAX(salary) from emp );

员工信息，也包括所在部门，新表和部门表的表连接查询（inner join，没部门的员工就用left outer join） 换行 子查询是表的时候表不能别名 可能作为一个条件，可能作为一张表

多表查询案例联系 4张表 步骤：1.明确查询那些表（select * from a,b 先这样写）2.明确表连接条件（去掉笛卡尔积 select * from a,b）3.后续查询 部分字段 条件

员工是多的，job和部门都是其他表，需要两个外键，工资等级自己计算，不是用salary_id 来标注员工等级 你是无法计算出他是哪张表

可以只写salary因为只有一个，但为了严谨

在范围内不能用表连接 不能用等号

4张表

dept_id 就是所有员工所在的部门 财务部是null没有员工 count(dept_id) 员工表和领导表 领导的名字要创建一个领导表 emp表的复制 select * from emp pt, emp ld; pt.mgr = ld.id 消除笛卡尔积 自己的名字 pt.ename 罗贯中在pt表显示，没有领导也要显示。

自连接

练习6 查询所有普通员工 排除领导

子查询 多行单列 IN

薪水在不同等级是有范围的。

索引 是排好序，查询快速的数据结构

全盘扫描就恐怖了。 主键会自动生成主键索引

练习5 自连接

插入百万数据

好多人一起来，延时，超过0.771s，要等待别人处理完。 建索引也要花时间 查主键也很快，因为默认给主键加索引。 有索引和没索引的差距

索引要重新建，耗时。 某个字删除了，要重新建索引。 十万之前，建索引要时间。 密码不会建索引，不会通过查密码得到账户名，密码可能重复。 频繁改动，不建议使用索引。 京东查询才用索引。

Explain 执行计划，模拟索引过程。 导致索引失效

Or前后一样会走索引，不一样不走索引

这里本来是走索引的，前后都是一样，但是数据太少，直接全盘扫描。

？？？如果给地址索引，name没有索引，按照 where name=‘’；根据name增删改数据会不会更新索引，不会，增删改索引列的数据才会更新索引。如果在索引列中间的数字删除，后面的数据就要往前，或者像堆的，中间有个数删除之前，先和最后的数交换，然后就heapify，往下走。最大堆的话。然后heapSize–

后面%会走索引。

最好用执行计划去看，有没有建索引。explain+sql

事务

一个事务，一件事，不可分割，多个sql语句

如果只执行了一半就崩溃，就回滚。 一致性，总数是一样，数据一样。

肯定是建表后，表都是已经建成的

回滚到开启的状态，中间相当于没有执行起来。 事务都在DOS命令行演示。 模糊查询 依赖临时日志文件 起码讲两遍

查询不需要提交事务；不需要修改东西；

事务的隔离级别

尚未提交的数据是1000和1000，修改后是500和1500，还未提交，但是一个事务读了两次数据，读了1000和1000的垃圾数据。（本来是不能读到1000和1000的垃圾数据，只能读到500和1500，A提交了才能读） set global transaction isolation level read committed; （B提交了数据才能读到新的修改啊） commit前读，和commit后读，一次事务读了两个数据，不可重复读。行数相同 一个事务中读取两次不一样的数据。 保证一个事务提交之前读到的数据都是一样的。 两边都要开启事务。 银行发余额给QQ 微信 邮件 短信 ，这次事务中，余额变了也要显示一样的信息。 （本来应该只能读到1000和1000的，不能读到500和1500，可重复读）

3. 幻读，修改前读和修改后读，行数发生变化，就是幻读。

第三个隔离级别 改隔离级别后，要两边重新启动mysql才能看到。

串行话，其他事务被阻塞，一个个来，但无法高并发。其他人要等待太久。 效率太低了。

使用默认的隔离级别即可。

总结

– 能够使用内连接进行多表查询

– 隐式内连接: SELECT * FROM 表1, 表2 WHERE 条件;

– 显式内连接:

SELECT * FROM 表1 INNER JOIN 表2 ON 表连接条件 WHERE 查询条件;

– 能够使用左外连接和右外连接进行多表查询

– 左外连接, 满足要求的数据显示,左表不满足要求的数据也显示 SELECT * FROM 表1 LEFT OUTER JOIN 表2 ON 表连接条件 WHERE 查询条件;

– 右外连接, 满足要求的数据显示,右表不满足要求的数据也显示

SELECT * FROM 表1 RIGHT OUTER JOIN 表2 ON 表连接条件 WHERE 查询条件;

– 能够使用子查询

– 子查询: 一条查询语句的结果作为另一个查询语句的一部分 SELECT * FROM 表 WHERE 字段名=(SELECT MAX(字段名) FROM 表名);

– 能够理解多表查询的规律

– 1.明确查询哪些表 – 2.明确表连接条件 – 3.后续查询

– 了解MySQL查询优化

– 通过索引可以极大的提升查询效率

– 理解索引的作用及使用策略

– 索引的作用: 提高查询效率 – 使用策略: – 1.数据量大于10万以上考虑使用索引 – 2.频繁使用的字段才添加索引 – 3.表中数据更新频繁,不建议使用索引

– 了解索引失效的问题

– 1.查询条件中使用OR导致索引失效(OR左右变量相同不失效) – 2.模糊查询 '%xxx’失效, 'xxx%'不失效 – 3.条件中使用< > != 导致索引失效 – 4.表中数据太少MySQL认为全表扫描比索引还快,索引失效

– 能够理解事务的概念

– 有多条SQL语句组成一个功能,这多条SQL语句形成一个事务, – 事务中的多条SQL语句要么一起执行,要么不执行

– 能够说出事务的原理

– 1.客户端登录MySQL,创建一个临时事务日志文件 – 2.开启事务后 – 3.执行的SQL语句会保存到临时日志文件中 – 4.如果执行commit提交事务,临时日志文件中的SQL语句就作用到数据库 – 5.如果执行rollback回滚事务,清空临时日志文件中的SQL语句

– 能够在MySQL中使用事务

– 开启事务: START TRANSACTION; – 提交事务: COMMIT; – 回滚事务: ROLLBACK;

– 能够理解脏读,不可重复读,幻读的概念及解决办法

– 脏读: 一个事务读取到另一个事务没有提交的数据, 解决方案设置隔离级别为 read committed; – 不可重复读: 一个事务读取多次,读取到的内容不同, 解决方案设置隔离级别为 repeatable read; – 幻读: 一个事务读取多次,读取到的数量不同, 解决方案设置隔离级别为 serializable; – 使用默认的隔离级别即可