oracle第四天：sql学习-多表查询

多表连接基本查询

使用一张以上的表做查询就是多表查询

语法： SELECT {DISTINCT} *|列名… FROM 表名 别名，表名1 别名
{WHERE 限制条件 ORDER BY 排序字段 ASC|DESC…}

• 范例：查询员工表和部门表

select *from emp,dept;

我们发现产生的记录数是56条，我们还会发现emp表是16条，dept表是4条，56正是emp表和dept表的记录数的乘积，我们称其为笛卡尔积。
如果多张表进行一起查询而且每张表的数据很大的话笛卡尔积就会变得非常大，对性能造成影响，想要去掉笛卡尔积我们需要关联查询。
在两张表中我们发现有一个共同的字段是depno，depno就是两张表的关联的字段，我们可以使用这个字段来做限制条件，两张表的关联查询字段一般是其中一张表的主键，另一张表的外键。

select *from emp e,dept d where e.deptno=d.deptno;

范例：查询出雇员的编号，姓名，部门的编号和名称，地址

select e.empno,e.ename,d.deptno,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno;

范例：查询出每个员工的上级领导

分析：emp表中的mgr字段是当前雇员的上级领导的编号，所以该字段对emp表产生了自身关联，可以使用mgr字段和empno来关联

select e.empno,e.ename,d.empno,d.ename from emp e,emp d where e.mgr=d.empno;

在上一个例子的基础上查询该员工的部门名称

析：只要在上一个例子基础上再加一张表的关联，使用deptno来做关联字段即可

SELECT
	e.empno,
	e.ename,
	d.empno,
	d.ename,
	dp.dname 
FROM
	emp e,
	emp d,
	dept dp 
WHERE
	e.mgr = d.empno 
	AND e.deptno = dp.deptno 
ORDER BY
	e.empno ASC;

范例：查询出每个员工编号，姓名，部门名称，工资等级和他的上级领导的姓名，工资等级

• 使用case if

SELECT
	e.empno,
	e.ename,
	d.dname,
CASE
		
		WHEN s.grade = 1 THEN
		'一级' 
		WHEN s.grade = 2 THEN
		'二级' 
		WHEN s.grade = 3 THEN
		'三级' 
		WHEN s.grade = 4 THEN
		'四级' 
		WHEN s.grade = 5 THEN
		'五级' 
	END 'empgrade',
	e1.ename,
CASE
		
		WHEN s1.grade = 1 THEN
		'一级' 
		WHEN s1.grade = 2 THEN
		'二级' 
		WHEN s1.grade = 3 THEN
		'三级' 
		WHEN s1.grade = 4 THEN
		'四级' 
		WHEN s1.grade = 5 THEN
		'五级' 
	END 'mgrgrande' 
FROM
	emp e,
	emp e1,
	dept d,
	salgrade s,
	salgrade s1 
WHERE
	e.mgr = e1.empno 
	AND e.deptno = d.deptno 
	AND e.sal BETWEEN s.losal 
	AND s.hisal 
	AND e1.sal BETWEEN s1.losal 
	AND s1.hisal;

• 使用decode

select e.EMPNO,
       e.ENAME,
       decode(s.GRADE,
              1, '一级',
              2, '二级',
              3, '三级',
              4, '四级',
              5, '五级'
           ) grade,
       d.DEPTNO,d.DNAME,
       e1.EMPNO,
       e1.ENAME,
       decode(s1.GRADE,
              1, '一级',
              2, '二级',
              3, '三级',
              4, '四级',
              5, '五级'
           ) grade1
from EMP e,
     EMP e1,
     DEPT d,
     SALGRADE s,
     SALGRADE s1
where e.MGR=e1.EMPNO and
      e.DEPTNO=d.DEPTNO and
      e.SAL between s.LOSAL and s.HISAL and
      e1.SAL between s1.LOSAL and s1.HISAL
;

外连接

左连接

可以查询出左边所有和右边的公共部分
以下sql可以查询出emp表的全部和dept与emp的公共部分（emp表左连接接了dept表）

select* from emp e left join dept d on d.deptno = e.deptno;

右连接

可以查询出右边表的所有和左边表的公共部分
以下sql可以查询出dept表的全部和emp与dept表的公共部分

select* from emp e right join dept d on d.deptno = e.deptno;

unionall的用法

nion all 相当于在查询出来的表的下侧在拼接了一个表

select *from emp
union all
select
       count(empno),
       count(ename),
       count(job),
       count(mgr),
       count(hiredate),
       count(sal),
       count(comm),
       count(deptno)
from emp;

分组函数

1.统计记录数count()

范例：查询出所有员工的记录数
不建议使用count(*)，可以使用一个具体的列以免影响性能。

select count(empno) from emp;

2.最小值查询min()

范例：查询出来员工最低工资

select min(sal) from emp;

3.最大值查询max()

范例：查询出员工的最高工资

select max(sal) from emp;

4.查询平均值avg()

范例：查询出员工的平均工资

select avg(sal) from emp;

5.求和函数sum()

范例：查询出20部门的员工的工资总和

select sum(sal) from emp where emp.deptno=20;

分组统计

分组统计需要使用GROUP BY来分组

语法：语法：SELECT * |列名 FROM 表名 {WEHRE 查询条件} {GROUP BY 分组字段} ORDER BY 列名1 ASC|DESC，列名2…ASC|DESC

• 范例：查询每个部门的人数

SELECT
	count( empno ) 人数,
	d.deptno 编号,
	d.dname 部门 
FROM
	emp e,
	dept d 
WHERE
	e.deptno = d.deptno 
GROUP BY
	d.deptno;

• 范例：查询出每个部门的平均工资

SELECT
	avg( sal ) '平均工资',
	d.dname,
	d.deptno 
FROM
	emp e,
	dept d 
WHERE
	e.deptno = d.deptno 
GROUP BY
	d.deptno;

注意：1. 如果使用分组函数，SQL只可以把GOURP BY分组条件字段和分组函数查询出来，不能有其他字段。
2. 如果使用分组函数，不使用GROUP BY 只可以查询出来分组函数的值

• 范例：按部门分组，查询出部门名称和部门的员工数量

SELECT
	d.dname,
	d.deptno,
	count( e.ename ) 
FROM
	emp e,
	dept d 
WHERE
	e.deptno = d.deptno 
GROUP BY
	d.deptno;

• 查询出部门人数大于5人的部门
  第一种方法 使用having

SELECT
	d.deptno,
	d.dname,
	count( e.ename ) 
FROM
	emp e,
	dept d 
WHERE
	e.deptno = d.deptno 
GROUP BY
	d.deptno,
	d.dname 
HAVING
	count( ename )> 5;

第二种方法 使用子查询

SELECT
	s.deptno 
FROM
	(
	SELECT
		count( e.ename ) a,
		e.empno,
		d.dname,
		d.deptno 
	FROM
		emp e,
		dept d 
	WHERE
		e.deptno = d.deptno 
	GROUP BY
		d.deptno 
	) s 
WHERE
	s.a > 5;

• 范例：查询出部门平均工资大于2000的部门

SELECT
	avg( sal ),
	e.sal,
	d.dname,
	d.deptno 
FROM
	emp e,
	dept d 
WHERE
	e.deptno = d.deptno 
GROUP BY
	d.deptno 
HAVING
	avg( sal )> 2000;

显示非销售人员工作名称以及从事同一工作的员工的月工资的总和，并且要满足从事同一工作月工资总和大于5000，结果按月工资总和的升序排列。

• 使用子查询 with as

with su as (
    select *FROM emp e where e.JOB!='SALESMAN'
),
su1 as (
    select su.JOB job,sum(su.SAL) sal from su group by su.JOB
)
select * from su1 where su1.sal>5000 order by su1.sal asc ;

• 不使用子查询

select e.JOB,sum(SAL) from emp e where e.JOB!='SALESMAN'
GROUP BY E.JOB having sum(sal)>5000 order by sum(SAL) asc ;

• 嵌套子查询

select *
from (
      (select *
       from (
                select su.JOB, sum(su.SAL) sal
                from (select *From EMP e where e.JOB != 'SALESMAN') su
                group by su.JOB
            )) su1
         )
where su1.sal > 5000
order by su1.sal asc;