ETL复习--2020年3月30日--表连接（上）、SQL全练习

--2020年3月30日 AM

1、表连接

1.1 笛卡尔积（交叉连接） cross join

select * from emp，dept;
select * from emp cross join dept;
--行级执行，从emp中取每条数据与dept的每条数据进行连接

--PM

select * from emp，dept;

select emp.deptno from emp，dept;--关联列需要指定所属

--字段的别名可加as，表的别名不可加as，保险起见，as均省略

1.2 内连接
有效关联，两个表有关系才会关联
关键词：inner join，inner可省略，不区分左、右内连接

select * from emp e, dept d where e.deptno = d.deptno;
--join on 写法更换
select * from emp e join dept d on e.deptno = d.deptno;

1.3 外连接
关键词：outer join，outer可省略，区分左、右外连接
left join / right join

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

select * from emp e right join dept d on e.deptno = d.deptno;
--left join / right join指向主表

full join 全外连接，避免相连接的表互相丢失数据

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

--SQL全练习

组1：简单查询

1、查询部分列
--查询当前员工入职时间信息。

select ename, hiredate from emp;

--查询员工的编号、姓名、工资

select EMPNO, ENAME, SAL from emp;

2、查询所有列
--查询员工的所有信息

select * from emp;

3、对查询出的列进行算术运算
--查询员工的编号、姓、年薪

select EMPNO, ENAME, SAL*12 + nvl(comm,0) from emp;

4、给列起别名
--查询所有员工信息，列名全部按照中文显示。

select EMPNO 员工编号,
ENAME 员工姓名,
JOB 工作,
MGR 上级领导编号,
HIREDATE 入职日期,
SAL 工资,
COMM 奖金,
DEPTNO 部门编号
from emp;

5、字符串拼接
--查询员工的编号、姓名、工资，在一列显示。

select empno||ename||sal from emp;

select concat(empno,concat(ename,sal)) from emp;

组2：排序

1、单列排序
--查询所有员工信息，并按工资从高到低显示

select * from emp order by sal desc;

--查询所有员工信息，并按工资从低到高显示

select * from emp order by sal;

2、多列排序
--查询所有员工信息，并按工资从高到低显示，如果工资相同，再按部门编号从大到小排

select * from emp order by sal desc, deptno desc;

--先按工资升序，如果工资相同，再按部门降序

select * from emp order by sal, deptno desc;

组3：条件查询

select 列1，列2 from 表名 where 过滤条件表达式 order by 排序列 asc/desc

1、等值查询
--查询工资是2400的员工编号、姓名、工资

select empno, ename, sal from emp where sal in (2400);

--查询员工姓是king的员工编号、姓名、工资

select empno, ename, sal from emp where ename='KING';

2、
--查询工资是2400同时姓King的员工编号、姓名、工资

select empno, ename, sal from emp where sal in (2400) and ename='KING';

--查询工资高于2000的员工编号、姓名、工资

select empno, ename, sal from emp where sal>2000;

--查询部门90和100下的所有员工

select * from emp where deptno in (10,20);

3、
--查询没有奖金的员工信息

select * from emp where nvl(comm,0)>0;

--查询有奖金的员工信息

select * from emp where nvl(comm,0)=0;

4、
--查询工资介于1000到2000之间的员工信息

select * from emp where sal between 1000 and 2000;

5、
--查询部门10、20、30下的员工

select * from emp where deptno in (10,20,30);

6、模糊查询
--查询姓中包含L的员工

select * from emp where instr(ename,'L')>0;

--查询姓中第2个字母是L的员工

select * from emp where ename like '_L%';

--查询姓的长度是5，并且第2个字母是L的员工

select * from emp where length(ename)=5 and ename like '_L%';

--查询名字中不包含L的员工

select * from emp where ename not like '%L%';

select * from emp where instr(ename,'L')=0;

组4：去掉查询结果中的重复数据行

--查询公司的经理

select * from emp where job='MANAGER';

--查看员工的工资情况：>20000 ***** ; 15000-20000 ****;10000-15000 ***;其它 *
--使用case when 语句
select * from emp where sal between 15000 and 20000;
select * from emp where sal between 10000 and 15000;
select * from emp where sal > 20000;

select e.*,
case
when sal between 1000 and 1500 then '***'
when sal between 1500 and 2000 then '****'
when sal > 2000 then '*****'
else '*'
end
from emp e;

select e.*,
case deptno
when 10 then '*'
when 20 then '**'
else '***'
end
from emp e;
--简单写法，适合离散型数据

组5：单行函数：运行在单条数据上，有一条数据函数就会被执行1次。

1、字符串相关
--查询员工的姓的长度

select ename, length(ename) from emp;

--查询长度是5位，并且第3位是a的员工

select * from emp where length(ename)=5 and instr(ename,'A',3,1)>0;

--显示字符串abcdefg的长度

select length('abcdefg') from dual;

--查询姓中包含L的员工

select * from emp where instr(ename,'L')>0;

--查询员工的全名和员工号，一列显示。

select concat(ename,empno) from emp;

2、数学相关

--查询编号是奇数的员工

select * from emp where mod(empno,2)>0;

--查询员工的日平均工资，保留2位小数

select round(avg(sal),2) from emp;

3、日期相关
--获取系统的当前日期时间

select sysdate from dual;

--计算1-2月-88 是星期几

select to_char(to_date('19880201','yyyymmdd'),'day') from dual;

select to_char(sysdate,'day') from dual;

--按年月日时分秒显示当前日期

select to_char(sysdate,'yyyymmddhh24miss') from dual;

--查看员工的入职时间

select ename, hiredate from emp;

--查看员工的入职月份

select ename, to_char(hiredate,'mm') from emp;

--查看7月份入职的员工

select ename from emp where to_char(hiredate,'mm')=7;

--查看本月入职的员工

select ename from emp where to_char(hiredate,'mm')=to_char(sysdate,'mm');

--查看上个月入职的员工

select ename from emp where to_char(hiredate,'mm')=to_char(sysdate,'mm')-1;

--查看昨天入职的员工

select ename from emp where to_char(hiredate,'yyyymmdd')=to_char(sysdate-1,'yyyymmdd');
select ename from emp where trunc(hiredate,'dd')=trunc(sysdate-1,'dd');

--查看员工的年薪（月薪*12+奖金）

select e.*, sal*12+nvl(comm,0) 年薪 from emp e ;

组6：组函数：作用在已经分好的一组数据上，每组数据产生1个结果。
1、sum(列名) --求和
2、avg(列名) --均值
3、max(列名) --求最大值
4、min(列名) --最小值

--查询公司的最高工资

select max(sal) from emp;

--查询公司的员工总数

select count(ename) from emp;

--查询10号部门下的员工人数

select count(ename) from emp where deptno in (10);

--查询没有奖金的人数

select count(ename) from emp where nvl(comm,0)=0;

--查询有奖金的人数

select count(ename) from emp where nvl(comm,0)>0;

--查询每个部门的人数

select deptno, count(deptno) from emp group by deptno;

--查询各个岗位的平均工资

select job, avg(sal) from emp group by job;

--查询每个部门每个岗位的平均工资

select deptno, job, avg(sal) from emp group by deptno, job;

--查询各个岗位的员工总数

select job, count(ename) from emp group by job;

--查询1981年各个月份入职的人数，并按月份升序显示

select to_char(hiredate, 'yyyy-mm'), count(1)
from emp
where to_char(hiredate, 'yyyy') = 1981
group by to_char(hiredate, 'yyyy-mm')
order by to_char(hiredate, 'yyyy-mm');

select count(ename) from emp where to_char(hiredate,'yyyy')=1981

--查询1997年各个月份入职人数超过5人的月份及人数（显示月份和人数），并按月份升序显示

--查询表中的前5个员工

select * from emp where rownum<6;

--查询表中第6-10个员工

select e.*, rownum r from emp e where rownum<11
minus
select e.*, rownum r from emp e where rownum<6;

--select语句各子句的执行顺序

--1）查询最高工资的员工姓名

select ename from emp where sal in (select max(sal) from emp);

--2)查询工资是maxSalary的员工编号、姓名

select empno, ename from emp where sal in (select max(sal) from emp);

--查询工资高于平均工资的员工

select empno, ename, sal from emp where sal > (select avg(sal) from emp);

--查询各部门具有本部门最高工资的员工

select *
from emp
where (deptno, sal) in (select deptno, max(sal) from emp group by deptno);

--查询某个部门的最高工资

select max(sal) from emp where deptno = 20;

--查询各部门工资高于本部门平均工资的员工

select *
from (select e.*, avg(sal) over(partition by deptno) av from emp e)
where sal > av;

--查询和King同部门的员工

select * from emp where deptno in (select deptno from emp where ename = 'KING');

--查询工资最高的前5个员工

select * from (select * from emp order by sal desc) where rownum<6;

--查询工资排名6-10的员工（子查询+rownum）

select * from (select * from emp order by sal desc) where rownum<11
minus
select * from (select * from emp order by sal desc) where rownum<6;

select * from (select e.*, row_number() over(order by sal desc) r from emp e ) where r between 6 and 10;

--查询入职最早的前3-5名员工

select * from (select e.*, row_number() over( order by hiredate) r from emp e ) where r between 3 and 5;

--查看1999-10-11是星期几

select to_char(to_date('19991011','yyyymmdd'),'day') from dual;