扫码加入数据分析学习群

多表连接查询

create table t1(key1 char,v1 int);

create table t2(key2 char,v2 int);

insert into t1 values('a',1),('a',2),('b',3),('c',4),('a',13);

insert into t2 values('b',10),('b',11),('a',12),('a',13),('e',14);

select * from t1;

select * from t2;

1. 内连接(满足条件就连接显示)（其他数据库还有全连接：full join）

-- select 字段1[,…] from 表1 [inner] join 表2 on 表1.key=表2.key;

select *

from t1

join t2

on t1.key1=t2.key2;

select *

from emp

join dept

on emp.deptno=dept.deptno;

2. 左连接(以左边表为主去连接，左边的都会显示，右边没有对应的不显示)

-- select 字段1[,…] from 表1(主表) left join 表2 on 表1.key=表2.key;

select *

from t1

left join t2

on t1.key1=t2.key2;

select *

from emp

left join dept

on emp.deptno=dept.deptno;

3.右连接(以右边表为主去连接，右边的都会显示，左边没有对应的不显示)

-- select 字段1[,…] from 表1 right join 表2(主表) on 表1.key=表2.key;

select *

from t1

right join t2

on t1.key1=t2.key2;

select *

from emp

right join dept

on emp.deptno=dept.deptno;

4. 合并查询（联合查询）:被合并的结果集的列数、顺序和数据类型必须完全一致

-- union去重:select 字段1[,字段2,…] from 表名 union select 字段1[,字段2,…] from 表名;

select * from t1 union select * from t2;

5. union all不去重:select 字段1[,字段2,…] from 表名 union all select 字段1[,字段2,…] from 表名;

select * from t1 union all select * from t2;

多表查询练习

-- 创建salgrade表

create table salgrade(grade int,losal int,hisal int);

insert into salgrade values(1,700,1200),(2,1201,1400),(3,1401,2000),(4,2001,3000),(5,3001,9999);

select * from emp;

select * from dept;

select * from salgrade;

6. 查询每位员工的ename,dname,sal

select e.ename,e.sal,d.dname

from emp e,dept d

where e.deptno=d.deptno;

select ename,sal,dname

from emp

left join dept

on emp.deptno=dept.deptno;

7. 查询各地区的员工数（统计每个地区，没有员工计为0）

select loc,count(empno)

from dept

left join emp

on emp.deptno=dept.deptno

group by loc;

8. 查询manager的姓名、所属部门名称和入职日期：ename,dname,job,hiredate(内连接/笛卡尔积连接)

select ename,job,hiredate,dname

from dept join emp on emp.deptno=dept.deptno

where job='manager'; -- 内连接查询(好)

select ename,job,hiredate,dname,emp.deptno

from emp,dept

where emp.deptno=dept.deptno and job='manager'; -- -- 笛卡尔积连接查询

9.查询每位员工的工资等级；empno,ename,sal,grade(不等值连接)

select empno,ename,sal,grade

from emp,salgrade

where sal between losal and hisal

order by grade asc; -- 笛卡尔积连接查询

select empno,ename,sal,grade

from emp left join salgrade

on sal between losal and hisal

order by grade asc; -- 左连接查询

10.查询所有员工姓名及其直属领导姓名（自连接：通过别名，将同一张表视为多张表）(e1：员工表，e2：领导表)

select e1.ename 员工姓名,e2.ename 领导姓名

from emp e1 left join emp e2 on e1.mgr=e2.empno;

-- select e1.ename 员工姓名,e2.ename 领导姓名 from emp e1,emp e2 where e1.mgr=e2.empno;(笛卡尔积会忽略mgr为空的员工)

11. 查询入职日期早于其直属领导的员工：empno,ename,dname (e1：员工表，e2：领导表)

select e1.empno,e1.ename,dname

from emp e1 left join emp e2 on e1.mgr=e2.empno

left join dept on e1.deptno=dept.deptno

where e1.hiredate<e2.hiredate;