数据库多表查询
一、内连接引入
多表查询：1.交叉连接 cross in ; 2.自然连接：natural join; 3.内连接：--using子句 4.内连接：--on子句（用的最多）。
缺点：当两张表里面的数据不匹配时，就查询不出来。只有两者有共同信息才会查询出来，所以会有信息遗漏。
#当要查询的数据在两张表里面时 #多表查询：cross join 叫做交叉连接 select * from emp cross join dept; #两张表全部都查出来了，笛卡尔乘积 #自然连接:自动匹配所有重名列。同名列只展示一次。natural join。查询的时候没指定字段所属数据库表，效率低。 select * from emp natural join dept; #提高效率，指定所属数据库。方式为：数据库表.查询列标题 select emp.empno,emp.name,dept.loc from emp natrual join dept; select e.empno,e.name,dept.loc from emp as e natrual join dept; #可以给数据表起别名。 #只匹配部分同名列(此时是内连接)，此时两张表的关联字段必须同名 select * from emp e inner join dept d using(deptno) #指定展示括号里面的全部重名列 #当两张表里面的关联字段不重名 on子句。筛选条件和连接条件是分开的 select * from emp e inner join dept d on(e.sid=d.cid) where sal > 3000;
二、外连接
左外连接：left outer join，右连接：right outer join，全外连接：full outer join
#左外连接(左面的那个表信息即使不匹配也可以全部显示） select * from emp e left outer join dept d on(e.sid=d.cid); #右外连接(原理同上) select * from emp e right outer join dept d on(e.sid=d.cid); #全外连接full outer join ，在mysql中不支持，但可在oracle可以，左右全部匹配信息都可展示 select * from emp e full outer join dept d on(e.sid=d.cid); #解决mysql的全外连接，用取并集的方法,将左右两张表并集 select * from emp e left outer join dept d on(e.sid=d.cid) union select * from emp e right outer join dept d on(e.sid=d.cid); #当为union时并集去重但效率低，union all时并集不去重，但效率高
三、三张表及以上的表连接查询
先外连接查询两张表，后面查询依次再加
#查询三张表的数据。查询员工编号，姓名,部门名称，薪水等级 select e.empno,e.name,d.name,s.* from emp e right outer join dept d on e.deptno = d.deptno inner join salagrade s on e.sal between s.losal and s.hisal; #此时先查询两张表，inner join salagrade s为加的第三张表，on e.sal between s.losal and s.hisal;为查询条件，记住条件必须加，否则信息会有错。
#第三表的连接方式既可以内连接也可以外连接，根据自己需求定
自关联：自己和自己相连接
在一张表里面查询相关信息：员工编号、姓名、对应上级编号、上级姓名
#查询不来全部数据 select e1.empno 员工编号,e1.name 员工姓名,e1.mgr 领导编号,e2.name 员工领导姓名 from emp e1 inner join emp e2 on e1.mgr = e2.empno; #左外连接 select e1.empno 员工编号,e1.name 员工姓名,e1.mgr 领导编号,e2.name 员工领导姓名 from emp e1 left outer join emp e2 on e1.mgr = e2.empno;
注意92语法和99语法的区别。92语法不含有cross join 和 natural join, 通过加限制条件才能实现
四、子查询
子查询：一条SQL语句有多个select，子查询可以独立运行
执行顺序：先执行子查询，再执行外查询
结果可分为单行子查询，和多行子查询
单行子查询
#查询所有比clerk员工工资高的员工信息，正常需要两步骤，即先查询clerk的工资，在查询所有比其工资高的员工信息，现在将这两条命令合并 select * from emp where sal > (select sal from emp where name = 'clerk'); #查询工资高于平均工资的员工信息 select name,sal from emp where sal > (select avg(sal) from emp); #查询和clerk同一部门且比他工资低的员工信息 select name,sal from emp where deptno = (select deptno from emp where name = 'clerk') and sal < (select sal from emp where name = 'clerk'); #查询职务和clerk相同，比clerk雇佣时间早的员工信息 select * from emp where job = (select job from emp where name = 'clerk') and hiredate < (select hiredate from emp where name = 'clerk')
多行子查询
#查询部门20中职务同部门10的雇员一样的员工信息 select * from emp where deptno = 20 and job in (select job from emp where deptno = 10) select * from emp where deptno = 20 and job = any (select job from emp where deptno = 10) #因为两部门的结果不一致，又包含关系，所以用in来判断，而不用等号，等号有可能是一对多或者多对一，造成单行子查询结果，无法识别。如果使用等号可以加any. #查询工资比所有的clerk都高的员工编号、名字和工资 select empno,name,sal from emp where sal > all(select max(sal) from emp where job = 'clerk') #查询工资低于任意一个CLERK部门员工的工资的员工信息 select * from emp where sal < any(select sal from emp where job = 'CLERK') and != 'CLERK';
相关子查询：子查询不可以独立运行，并且先运行外查询，再运行子查询。上面两种就是不相关子查询。
#查询最高工资的员工(不相关子查询） select * from emp where sal = (select max(sal) from emp) #查询本部门最高工资的员工（相关子查询） #类似于select * from emp e where deptno = 30 and sal = (select max(sal) from emp where deptno = 30) order by deptno; (这个是不相关子查询，将外面deptno = 30这个条件，与里面的结合) select * from emp e where sal = (select max(sal) from emp where deptno = e.deptno) order by deptno;（相关子查询） #查询工资高于所在岗位的平均工资的员工 select * from emp e where sal >= (select avg(sal) from emp where job = e.job)
五、事务
指的是一个操作序列。用来维护数据库完整性，它能够保证一系列的MySQL操作要么全部执行，要么全部不执行。确保除非事务性序列操作都成功完成。操作序列要成为事务，必须满足事物的原子性(Atomicity),一致性(Consistency),隔离性(Isolation),持久性(Durability)四大特性，称为ACID性。原子性：事务中所有操作可看成一个原子，事务是应用中不可再分的最小的逻辑执行体，要么全执行要么全不执行。一致性：从一个一致性状态，变到另一个一致性状态。隔离性：各个事务执行互不干扰。持久性：事务一旦提交，对数据所做的任何改变，都要记录到永久存储器中。
#丽丽给小刚转账200，丽丽余额少200，小刚增加200 #手动开启事务 start transaction; update account set balance = balance - 200 where id =1; update account set balance = balance + 200 where id =2; #手动回滚，则刚才的操作取消 rollback; #手动提交事务,这一步完成后才真正改变数据库里面的数据，上面的操作都是操作的缓存的数据 commit;
事务多线程问题
1.脏读：当一个事务正在访问数据并且对数据进行了修改，而修改的数据还未提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据，因为数据还未提交，那么另外一个事务读到的数据是个脏数据，可能是不正确的。
2.不可重复读：指一个事务内多次读同一个数据，在这个事务还没结束时，另一个事务也访问了该数据，那么第一个事务中两次读到的数据之间，由于第二个事务的修改数据，可能两次读到的数据不一样，导致一个事务内两次读到数据不一样，称为不可重复读。
3.幻读：幻读与不可重复读类似，一个事务在执行过程中，另一个修改了。不可重复读的重点是修改数据，幻读重点在于新增和删除数据。
解决：不可重复读问题只需要锁住满足条件的行，解决幻读要锁表。
事务隔离级别：
从低到高：READ UNCOMMITTED(都不能解决) < READ COMMITED(只能解决脏读) < REPEATABLE READ(不能解决幻读) < SERIALIZABLE(能解决以上所有问题) 隔离级别越低，越能支持高并发的数据库操作。
#查看默认的事务隔离级别，MySQL默认是repeatable read select @@transaction_isolation; #设置事务隔离级别（设置当前会话的隔离级别） set session transaction isolation level read uncommitted; set session transaction isolation level read committed; set session transaction isolation level repeatable read; set session transaction isolation level serializable;
六、视图（在视图选项里面创建，而不是在查询里面创建）
视图是一个从单张或者多张基础数据表或其它视图中构建出来的虚拟表。包含了一系列带有名称的列和行数据。视图本质上就是一个查询语句，虚拟不存在的表，对机密数据提供安全保护。
单表视图：
#创建单表视图 create or replace view myview as select empno,job from emp where deptno = 20 with check option; #这里的设置代表只能满足where条件的数据才能插入，没有这个则其他数据都可以插入 #查看视图 select * from myview; #在视图里面插入数据（给视图加数据，其实数据库里面的数据也进行了同等操作） insert into myview (empno,name,job,deptno) values (999,'nana','clerk',20); insert into myview (empno,name,job,deptno) values (888,'feifei','clerk',30); #第二条数据无法插入数据表，因为创建表的时候已经申明了with check option;且条件为where deptno = 20。
多表视图：
#创建多表视图 create or replace view myview as select e.empno,e.name,e.sal,d.deptno,d.name from emp e join dept d on e.deptno = d.deptno where sal > 2000; #基于视图的视图 create or replace view myview1 as select * from myview where deptno = 20;
七、存储过程（Stored Procedure）
数据库中保存的一系列SQL命令的集合，也可以看作相互之间有关系的SQL命令组织在一起形成的小程序。能够提高性能，减轻网络负担，将数据库处理黑盒子化。
无返回值的存储过程：
#定义一个没有返回值的存储过程 #实现模糊查询 select * from emp where name like '%A%'; #创建存储过程 create procrdure mypro(name varchar(10)) #name varchar(10)传入参数 begin if name is null or name = '' then select * from emp; else select * from emp where name like concat('%',name,'%'); end if; end; #删除存储过程 drop procedure mypro; #调用存储过程 call mypro(null)
有返回值的存储过程：
#创建有返回值的存储过程 create procrdure mypro1(in name varchar(10), out num int(3)) #name varchar(10)传入参数前面加in区分，out后面为返回值，num int(3)代表返回一个int类型。 begin if name is null or name = '' then select * from emp; else select * from emp where name like concat('%',name,'%'); end if; select found_rows() into num; #此时这就是返回查询结果的条数 end; #调用 call mypro1(null,@num); #此时无法看到num select @num; #到这步才可以看到num
八、MySQL的执行计划
explain select * from 数据库表名 改变浏览方式：explain select * fr\G
需要看的属性：
id：表示sql语句要执行的顺序
type:all(除了all,还有index<range<index_merge<ref<const<system,未列举完) all表示全表扫描,如果是all那一定是需要优化的，必须保证在range以上
key: sql语句执行时，索引列是哪一个
Extra: 额外的信息
九、索引
mysql索引一般是几层：3-4层的B+树足以支撑千万级别的数据量
在选择value存储时，一般情况下用int类型。
尽量保持自增，因为没自增时添加插入数据，在页分裂时，可能影响上层。
索引：加快数据访问，采用的数据结构为B+树，因为
io:读取次数少，读取量少
数据格式：key:value格式数据，hash表，文件太大，需要将文件分块读取
memory存储引擎支持hash索引，innodb支持自适应hash
树：BST / AVL / 红黑树 / B树 / B+树 BST / AVL / 红黑树，这三个成为二叉，有序，多叉排序树
十、锁（作用是事务隔离性）
查看锁的命令：show engine innodb status\G
排他锁、共享锁、意向锁、自增锁、间隙锁、临键锁、悲观锁、乐观锁、行锁、表锁
按照锁的力度：
表锁：
意向锁
自增锁
行锁：
间隙锁
临建锁
记录锁
针对锁的方式：
排他锁：写锁
共享锁：读锁
调优问题：大部分mysql优化都是出现问题之后再根据实际情况开始调整，但是在数据库设计的时候都应该考虑到优化问题，比如表的设计，schema设计，数据库的分库分表，但是在准确过程中难免出现一些纰漏，在这种情况下，需要根据实际情况来进行调优，我之前作mysql调优，基本上是通过以下几个维度开始考虑的：执行计划、索引的使用、SQL语句的调优、性能的监控、SQL语句的调整、参数的调整。
