Mysql 复合查询

多表查询实际开发中往往数据来自不同的表所以需要多表查询。本节我们用一个简单的公司管理系统有三张 表EMP,DEPT,SALGRADE来演示如何进行多表查询。部门表 dept员工表 emp工资等级表 salgrade案例显示雇员名、雇员工资以及所在部门的名字因为上面的数据来自EMP和DEPT表因此要联合查询select emp.ename,emp.sal,dept.dname from emp,dept where emp.deptnodept.deptno;其实我们只要emp表中的deptno dept表中的deptno字段的记录,所以后面要有个where限定结果显示部门号为10的部门名员工名和工资select ename, sal,dname from EMP, DEPT where EMP.deptnoDEPT.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 enamesmith);多行子查询返回多行记录的子查询in关键字查询和10号部门的工作岗位相同的雇员的名字岗位工资部门号但是不包含10自 己的select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno10) and deptno10;all关键字显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号select ename, sal, deptno from EMP where sal all(select sal from EMP where deptno30);any关键字显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号包含自己部门 的员工select ename, sal, deptno from EMP where sal any(select sal from EMP where deptno30);多列子查询单行子查询是指子查询只返回单列单行数据多行子查询是指返回单列多行数据都是针对单列而言的而多列子查询则是指查询返回多个列数据的子查询语句案例查询和SMITH的部门和岗位完全相同的所有雇员不含SMITH本人mysql select ename from EMP where (deptno, job)(select deptno, job from EMP where enameSMITH) 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.deptnotmp.dt;查找每个部门工资最高的人的姓名、工资、部门、最高工资select emp.ename, emp.sal, emp.deptno, ms from emp, (select max(sal) ms, deptno from emp group by deptno) tmp where emp.deptnotmp.deptno and emp.saltmp.ms;显示每个部门的信息部门名编号地址和人员数量方法1使用多表select dept.dname, dept.deptno, dept.loc,count(*) 部门人数 from emp,dept where emp.deptnodept.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.deptnotmp.deptno;合并查询在实际应用中为了合并多个select的执行结果可以使用集合操作符 unionunion allunion该操作符用于取得两个结果集的并集。当使用该操作符时会自动去掉结果集中的重复行。案例将工资大于2500或职位是MANAGER的人找出来select ename, sal, job from emp where sal2500 union select ename, sal, job from emp where jobMANAGER;--去掉了重复记录union all该操作符用于取得两个结果集的并集。当使用该操作符时不会去掉结果集中的重复行。案例将工资大于25000或职位是MANAGER的人找出来select ename, sal, job from emp where sal2500 union all select ename, sal, job from emp where jobMANAGER;连接查询重点表的连接分为内连和外连内连接内连接实际上就是利用where子句对两种表形成的笛卡儿积进行筛选我们前面学习的查询都是内连 接也是在开发过程中使用的最多的连接查询。select 字段 from 表1 inner join 表2 on 连接条件 and 其他条件备注前面学习的都是内连接案例显示SMITH的名字和部门名称-- 用前面的写法 select ename, dname from emp, dept where emp.deptnodept.deptno and enameSMITH; -- 用标准的内连接写法 select ename, dname from emp inner join dept on emp.deptnodept.deptno and enameSMITH;外连接外连接分为左外连接和右外连接左外连接如果联合查询左侧的表完全显示我们就说是左外连接。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.idexam.id;右外连接如果联合查询右侧的表完全显示我们就说是右外连接。select 字段 from 表名1 right join 表名2 on 连接条件案例对stu表和exam表联合查询把所有的成绩都显示出来即使这个成绩没有学生与它对应也要 显示出来select * from stu right join exam on stu.idexam.id;列出部门名称和这些部门的员工信息同时列出没有员工的部门方法一select d.dname, e.* from dept d left join emp e on d.deptnoe.deptno;方法二select d.dname, e.* from emp e right join dept d on d.deptnoe.deptno;