个人博客：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;
网友评论