Mysql 复合查询
多表查询
实际开发中往往数据来自不同的表，所以需要多表查询。本节我们用一个简单的公司管理系统，有三张 表EMP,DEPT,SALGRADE来演示如何进行多表查询。
部门表 dept
员工表 emp
工资等级表 salgrade
案例：
显示雇员名、雇员工资以及所在部门的名字
因为上面的数据来自EMP和DEPT表，因此要联合查询
select emp.ename,emp.sal,dept.dname from emp,dept where emp.deptno=dept.deptno;其实我们只要emp表中的deptno = dept表中的deptno字段的记录,所以后面要有个where限定结果
显示部门号为10的部门名，员工名和工资
select ename, sal,dname from EMP, DEPT where EMP.deptno=DEPT.deptno and DEPT.deptno = 10;显示各个员工的姓名，工资，及工资级别
select ename,sal,grade from emp,salgrade where emp.sal between losal and hisal;自连接
自连接是指在同一张表连接查询
案例：
使用的子查询：
使用多表查询（自查询）
-- 使用到表的别名
--from emp leader, emp worker，给自己的表起别名，因为要先做笛卡尔积，所以别名可以先识 别
子查询
子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询
单行子查询
返回一行记录的子查询
显示SMITH同一部门的员工
select * from EMP WHERE deptno = (select deptno from EMP where ename='smith');多行子查询
返回多行记录的子查询
in关键字；查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包含10自 己的
select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno=10) and deptno<>10;all关键字；显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
select ename, sal, deptno from EMP where sal > all(select sal from EMP where deptno=30);any关键字；显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号（包含自己部门 的员工）
select ename, sal, deptno from EMP where sal > any(select sal from EMP where deptno=30);多列子查询
单行子查询是指子查询只返回单列，单行数据；多行子查询是指返回单列多行数据，都是针对单列而言的，而多列子查询则是指查询返回多个列数据的子查询语句
案例：查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人
mysql> select ename from EMP where (deptno, job)=(select deptno, job from EMP where ename='SMITH') and ename <> 'SMITH';在from子句中使用子查询
子查询语句出现在from子句中。这里要用到数据查询的技巧，把一个子查询当做一个临时表使用。 案例： 显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资
//获取各个部门的平均工资，将其看作临时表 select ename, deptno, sal, format(asal,2) from EMP, (select avg(sal) asal, deptno dt from EMP group by deptno) tmp where EMP.sal > tmp.asal and EMP.deptno=tmp.dt;查找每个部门工资最高的人的姓名、工资、部门、最高工资
select emp.ename, emp.sal, emp.deptno, ms from emp, (select max(sal) ms, deptno from emp group by deptno) tmp where emp.deptno=tmp.deptno and emp.sal=tmp.ms;显示每个部门的信息（部门名，编号，地址）和人员数量
方法1：使用多表
select dept.dname, dept.deptno, dept.loc,count(*) '部门人数' from emp,dept where emp.deptno=dept.deptno group by dept.deptno,dept.dname,dept.loc;方法2：使用子查询
-- 1. 对EMP表进行人员统计 select count(*), deptno from emp group by deptno; -- 2. 将上面的表看作临时表 select dept.deptno, dname, mycnt, loc from dept, (select count(*) mycnt, deptno from emp group by deptno) tmp where dept.deptno=tmp.deptno;合并查询
在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all
union
该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。
案例：将工资大于2500或职位是MANAGER的人找出来
select ename, sal, job from emp where sal>2500 union select ename, sal, job from emp where job='MANAGER';--去掉了重复记录union all
该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。
案例：将工资大于25000或职位是MANAGER的人找出来
select ename, sal, job from emp where sal>2500 union all select ename, sal, job from emp where job='MANAGER';连接查询（重点）
表的连接分为内连和外连
内连接
内连接实际上就是利用where子句对两种表形成的笛卡儿积进行筛选，我们前面学习的查询都是内连 接，也是在开发过程中使用的最多的连接查询。
select 字段 from 表1 inner join 表2 on 连接条件 and 其他条件；
备注：前面学习的都是内连接
案例：显示SMITH的名字和部门名称
-- 用前面的写法 select ename, dname from emp, dept where emp.deptno=dept.deptno and ename='SMITH'; -- 用标准的内连接写法 select ename, dname from emp inner join dept on emp.deptno=dept.deptno and ename='SMITH';外连接
外连接分为左外连接和右外连接
左外连接
如果联合查询，左侧的表完全显示我们就说是左外连接。
select 字段名 from 表名1 left join 表名2 on 连接条件
案例：
-- 建两张表 create table stu (id int, name varchar(30)); -- 学生表 insert into stu values(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono'); create table exam (id int, grade int); -- 成绩表 insert into exam values(1, 56),(2,76),(11, 8);查询所有学生的成绩，如果这个学生没有成绩，也要将学生的个人信息显示出来
-- 当左边表和右边表没有匹配时，也会显示左边表的数据 select * from stu left join exam on stu.id=exam.id;右外连接
如果联合查询，右侧的表完全显示我们就说是右外连接。
select 字段 from 表名1 right join 表名2 on 连接条件；
案例：
对stu表和exam表联合查询，把所有的成绩都显示出来，即使这个成绩没有学生与它对应，也要 显示出来
select * from stu right join exam on stu.id=exam.id;列出部门名称和这些部门的员工信息，同时列出没有员工的部门
方法一：
select d.dname, e.* from dept d left join emp e on d.deptno=e.deptno;方法二：
select d.dname, e.* from emp e right join dept d on d.deptno=e.deptno;