MySQL数据库操作(五)——多表查询
2018-09-21 本文已影响76人
海晨忆
个人博客:haichenyi.com。感谢关注
合并结果集 union all(包含相同行)、union(去除相同行)
把查询的结果合并到一起。要求: 查询的结果列数相同,列类型相同
create table emp(
eid int,
ename varchar(50),
eage int
);
create table stu(
sid int,
sname varchar(50)
);
//合并结果集(加上all,包含相同的行)
select eid,ename from emp
union all
select * from stu;
//合并结果集(去掉all,去除相同行)
select eid,ename from emp
union
select * from stu;
连接查询
内连接
方言: select * from 表1,表2 where 条件
表a有三条数据:q,w,e
表b有两条数据:h,j
select * from a,b;
得出的结果是表a与表b的笛卡尔集,也就是会得到6条数据,
分别:qh,qj,wh,wj,eh,ej
去除无用的笛卡尔集,加上where后面的条件
连表查询会有附表的外键=主表的主键这个条件
select * from student,teacher where student.tid=teacher.tid;
查询学生的全部信息,需要查询学生表和老师表,
条件是学生表里面的老师id等于老师表里面的老师id,
这样查出来的结果就是我们想要的结果
例子:要求打印员工姓名,工资以及部分名称
员工姓名,工资在emp表,部门名称在dept表
select emp.ename,emp.sal,dept.dname
from emp,dept
where emp.did=dept.did;
如果嫌弃表名字太长了可以这样:
select e.ename,e.sal,d.dname
from emp e,dept d
where e.did=d.did;
标准: select * from 表1 别名1 inner join 表2 别名2 on 条件
别名都是可选的,条件跟上面写的一样。两者的区别就只有二个:
1、方言连接两个表是用逗号,标准连接两个表是用 inner join
2、条件:方言用where,标准用on
select e.ename,e.sal,d.dname
from emp e inner join dept d
where e.did=d.did;
外连接 一主一次
左外连接 left outer join ,左表为主表,右表为次表,左表中不论满足不满足条件都查询出来,右表中对应的位置用null补位
查询所有员工名称,工资以及部门名称,部门名称为null的填无部分
select e.ename as 姓名,e.sal as 工资,d.ifnull(dname,'无部门') as 部门名称
from emp e left outer join dept d
where e.did=d.did;
右外连接 right outer join 与左外对应
子查询 一条sql语句中有多个select关键字,ALL,ANY,IN
//查询员工中最高工资员工的详细信息
select * from emp where sal=MAX(sal);
//上面这个写法是错误的,条件中是不能出现集合函数
//上面这个写法是错误的,条件中是不能出现集合函数
//上面这个写法是错误的,条件中是不能出现集合函数
//1、先查最高工资
select MAX(sal) from emp;
//2、最高工资的员工信息
select * from emp where sal=(select MAX(sal) from emp);
//上面这个写法才是正确的
//上面这个写法才是正确的
//上面这个写法才是正确的
出现的位置: 1、from后作为表 2、where后作为条件
上面那条sql语句就是作为条件
//查询部门等于30的员工的姓名和年龄
select e.name,e.age from (select * from emp where did=30) e;
//上面这条就是from后面作为表
1、打印高于平均工资的所有人的信息(单行单列作为条件)
select * from emp where (sal>select AVG(sal) from emp);
2、打印大于30部门的所有员工工资的员工信息(多行单列作为条件)
select * from emp where sal > ALL (select sal from emp where did=30);
3、打印大于30部门任意一人员工工资的员工信息
select * from emp where sal> ANY (select sal from emp where did = 30);
4、打印工作和部门与张三相同的员工信息
select * from emp where (job,did) IN (select job,did from emp where ename='张三');
练习题
前提条件已知四张表
员工表emp(eid,ename,job,emid,etime,sal,edid)
部门表dept(did,dname,dloc)
薪资等级表salgrade(grade,losal,hisal)
领导表mgr(mid,mname,mtime)
1、查询至少有一个员工的部门,显示部门编号,部门名称,部门位置,部门人数
分析:
1、列:d.did,d.dname,d.dloc,部门人数
2、表:dept d,emp e
条件:e.did=d.did
//查询部门编号,部门名称,部门位置
select * from dept;
//查询部门人数
select e.did,count(*) from emp group by e.did;
//把上面查询的两张表内连接
select * from dept inner join select e.did,count(*) from emp group by e.did;
//加上条件,给出别名
select d.*,z1.cnt from dept d inner join (select did,count(*) cnt from emp group by e.did) z1 where d.did=z1.did;
2、查询所有员工名称及其直接上级领导名称
分析:
1、列:e.ename,m.mname
2、表:emp e,mgr m
条件:e.emid=m.mid
//查询员工名称
select ename from emp;
//查询领导名称
select mname from mgr;
select ename from emp e left outer join (select mname from mgr ) m on e.emid = m.mid;
3、列出受雇佣日期早于直接上级的所有员工编号,姓名,以及部门名称
分析:
1、列:e.eid,e.ename,d.dname
2、表:emp,mgr,dept
//查询受雇佣日期早于直接上级领导的员工信息
select etime from emp;
select mtime from mgr;
select * from emp e
inner join
select mtime from mgr m
on e.emid=m.mid and e.etime<m.mtime
//再把部门名称加上
(select eid,ename,edid from emp e
inner join
select mtime from mgr m
on e.emid=m.mid and e.etime<m.mtime) z1
left outer join
select dname from dept d
on z1.edid = d.did;
或者
select e.eid,e.ename,d.dname
from emp e,mgr m,dept d
where e.emid=m.mid and e.etime<m.mtime and e.edid=d.did;
4、列出部门名称和这些部门的员工信息,同事列出没有员工的部门
分析:
1、列:员工信息,和部门名称,没有员工的部门也要列出来
2、表:emp,dept
select e.*,d.dname from emp e
right outer join
dept d
on e.edid=d.did;
5、列出最低薪资大于15000的各种工作以及从事此工作的人数
分析:
1、列:工作类型,人数
2、表:emp
条件:min(sal)>15000
select job,count(*)
from emp
group by job
having min(sal)>15000
6、列出在销售部工作的员工姓名,假定不知道销售部的部门编号
分析:
1、列:员工姓名
2、表:emp,dept
条件:e.edid=d.did
select ename
from emp e
where e.edid=(select did from dept where dname='销售部');
7、列出薪资高于公司平均工资的所有员工信息,所在部门名称,上级领导,工资等级
分析:
1、列:员工信息,部门名称,上级领导名称,工资等级
2、表:emp,dept,mgr
条件:sal>avg(sal),e.edid=d.did,e.emid=m.mid
//先查询高出平均工资的员工信息
select * from emp where sal>(select avg(sal) from emp);
//把后面的表一个一个的加上去
//加部门名称
select e.*,d.dname
from
emp e left outer join dept d on e.edid=d.did
where e.sal>(select avg(sal) from emp)
//加上级领导名称
select e.*,d.dname,m.mname
from
emp e left outer join dept d on e.edid=d.did
left outer join mgr m on e.emid = m.mid
where e.sal>(select avg(sal) from emp)
//最后加工资等级
select e.*,d.dname,m.mname,s.grade
from
emp e left outer join dept d on e.edid=d.did
left outer join mgr m on e.emid = m.mid
left outer join salgrade s on e.sal between s.losal and hisal
where e.sal>(select avg(sal) from emp)
8、列出与张三从事相同工作的所有员工以及部门名称
分析:
1、列:所有员工,部门名称
2、表:emp,dept
条件:张三的工作相同,e.edid=d.did
select e.*,d.dname from emp e
left outer join dept d
on e.edid=d.did
where e.job=(select job from emp where ename='张三');
或者
select e.*,d.dname from emp e,dept d where e.edid=did and e.job=(select job from emp where ename='张三');
9、列出薪资高于30部门的所有员工薪资的员工的姓名和薪资,部门名称
select e.ename,e.sal,d.dname from emp e left outer join dept d
where e.sal > all (select sal from emp where edid=30) and e.edid = d.did;