個(gè)人博客：haichenyi.com。感謝關(guān)注

合并結(jié)果集 union all(包含相同行)、union(去除相同行)

??把查詢的結(jié)果合并到一起。要求： 查詢的結(jié)果列數(shù)相同，列類型相同

create table emp(
    eid int,
    ename varchar(50),
    eage int
);

create table stu(
    sid int,
    sname varchar(50)
);

//合并結(jié)果集（加上all，包含相同的行）
select eid,ename from emp
union all
select * from stu;

//合并結(jié)果集（去掉all，去除相同行）
select eid,ename from emp
union
select * from stu;

連接查詢

內(nèi)連接

方言： select * from 表1,表2 where 條件

表a有三條數(shù)據(jù)：q，w，e
表b有兩條數(shù)據(jù)：h，j
select * from a,b;
得出的結(jié)果是表a與表b的笛卡爾集，也就是會(huì)得到6條數(shù)據(jù)，
分別：qh,qj,wh,wj,eh,ej

去除無用的笛卡爾集，加上where后面的條件
連表查詢會(huì)有附表的外鍵=主表的主鍵這個(gè)條件

select * from student,teacher where student.tid=teacher.tid;

查詢學(xué)生的全部信息，需要查詢學(xué)生表和老師表，
條件是學(xué)生表里面的老師id等于老師表里面的老師id，
這樣查出來的結(jié)果就是我們想要的結(jié)果

例子：要求打印員工姓名，工資以及部分名稱
     員工姓名，工資在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;

標(biāo)準(zhǔn)： select * from 表1 別名1 inner join 表2 別名2 on 條件

別名都是可選的，條件跟上面寫的一樣。兩者的區(qū)別就只有二個(gè)：
1、方言連接兩個(gè)表是用逗號(hào)，標(biāo)準(zhǔn)連接兩個(gè)表是用 inner join
2、條件：方言用where，標(biāo)準(zhǔn)用on

select e.ename,e.sal,d.dname
from emp e inner join dept d
where e.did=d.did;

外連接 一主一次

左外連接 left outer join ,左表為主表，右表為次表,左表中不論滿足不滿足條件都查詢出來，右表中對應(yīng)的位置用null補(bǔ)位

查詢所有員工名稱，工資以及部門名稱，部門名稱為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 與左外對應(yīng)

子查詢 一條sql語句中有多個(gè)select關(guān)鍵字,ALL,ANY,IN

//查詢員工中最高工資員工的詳細(xì)信息
select * from emp where sal=MAX(sal);
//上面這個(gè)寫法是錯(cuò)誤的，條件中是不能出現(xiàn)集合函數(shù)
//上面這個(gè)寫法是錯(cuò)誤的，條件中是不能出現(xiàn)集合函數(shù)
//上面這個(gè)寫法是錯(cuò)誤的，條件中是不能出現(xiàn)集合函數(shù)


//1、先查最高工資
select MAX(sal) from emp;
//2、最高工資的員工信息
select * from emp where sal=(select MAX(sal) from emp);
//上面這個(gè)寫法才是正確的
//上面這個(gè)寫法才是正確的
//上面這個(gè)寫法才是正確的

出現(xiàn)的位置： 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='張三');

練習(xí)題

前提條件已知四張表

員工表emp(eid,ename,job,emid,etime,sal,edid)
部門表dept(did,dname,dloc)
薪資等級(jí)表salgrade(grade,losal,hisal)
領(lǐng)導(dǎo)表mgr(mid,mname,mtime)

1、查詢至少有一個(gè)員工的部門，顯示部門編號(hào)，部門名稱，部門位置，部門人數(shù)

分析：
1、列：d.did，d.dname，d.dloc，部門人數(shù)
2、表：dept d，emp e
條件：e.did=d.did

//查詢部門編號(hào)，部門名稱，部門位置
select * from dept;

//查詢部門人數(shù)
select e.did,count(*) from emp group by e.did;

//把上面查詢的兩張表內(nèi)連接
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、查詢所有員工名稱及其直接上級(jí)領(lǐng)導(dǎo)名稱

分析：
1、列：e.ename，m.mname
2、表：emp e，mgr m
條件：e.emid=m.mid
//查詢員工名稱
select ename from emp;
//查詢領(lǐng)導(dǎo)名稱
select mname from mgr;

select ename from emp e left outer join (select mname from mgr ) m on e.emid = m.mid; 

3、列出受雇傭日期早于直接上級(jí)的所有員工編號(hào)，姓名，以及部門名稱

分析：
1、列：e.eid,e.ename,d.dname
2、表：emp，mgr，dept
//查詢受雇傭日期早于直接上級(jí)領(lǐng)導(dǎo)的員工信息
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的各種工作以及從事此工作的人數(shù)

分析：
1、列：工作類型，人數(shù)
2、表：emp
條件：min(sal)>15000

select job,count(*)
from emp
group by job
having min(sal)>15000

6、列出在銷售部工作的員工姓名，假定不知道銷售部的部門編號(hào)

分析：
1、列：員工姓名
2、表：emp,dept
條件：e.edid=d.did

select ename
from emp e
where e.edid=(select did from dept where dname='銷售部');

7、列出薪資高于公司平均工資的所有員工信息，所在部門名稱，上級(jí)領(lǐng)導(dǎo)，工資等級(jí)

分析：
1、列：員工信息，部門名稱，上級(jí)領(lǐng)導(dǎo)名稱，工資等級(jí)
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);

//把后面的表一個(gè)一個(gè)的加上去
//加部門名稱
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)

//加上級(jí)領(lǐng)導(dǎo)名稱
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)

//最后加工資等級(jí)
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;