数据库操作

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;

上一篇下一篇

猜你喜欢

热点阅读