ETL复习--2020年3月26日--视图、函数

-- 2020年3月26日 AM

0、知识回顾

select * from emp;

insert into emp(empno, ename, job) values (22, kk, pp);

insert into emp(empno, ename, job) select * from dept;--配合查询语句插入值

update emp set sal=sal*5;--整表修改

update emp1

delete from emp where rownum=1;--单击一次即执行一次，待提交即做出修改

DQL语句

变量命名

select empno "select", job "#@#@" from emp;
--使用双引号强制将关键字、特殊符号转换为字段名

1、视图

封装视图 机密保护

create view v_1 as select empno, ename from emp;
create or replace view v_2 as select empno, ename from emp where rownum<6;

select * from v_1;
select * from v_2;

drop view v_1;

cmd中给Scott授权：
调用系统管理员sys，默认密码 sys as sysdba
grant dba to scott; --cmd环境下严格语法，必须加分号

rename emp1 to emp2; --突然想到的，表的重命名

create or replace view vv1 as
select * from (
select * from stu order by dbms_random.value ) where rownum<6

select * from stu
minus
select * from vv1;--差集，取被筛选出来的5名同学之外的同学

练习题：

1.求工资最高的员工的信息。（两种办法实现）

select * from emp where sal=(select max(sal) from emp);
--避免查询结果重复时输出不全

select * from (select * from emp order by sal desc) where rownum=1;
--只能输出一个结果，有输出不全的风险

2.求工资最高的前五名员工信息。

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

3.按工资排序，求第5到10名的信息。
select * from (
select e.*, rownum r from (select * from emp order by sal) e
) where r between 5 and 10;--比较复杂的子查询

select * from (
select e.*, rownum r from (select * from emp order by sal) e
where rownum < 11
minus
select e.*, rownum r from (select * from emp order by sal) e
where rownum < 5 ) order by sal;
--使用差集也能比较好理解,但minus后需重新排序

--PM

2、函数

2.1 数值函数
mod()求余 abs()绝对值 power()幂指函数

2.2 组函数（多行函数、聚合函数）
max() min() avg() count() sum()

select sysdate from dual;
get_date()--sqlserver中的当前系统时间

select max(ename) from emp;--对首字母进行排序

select count(1) from emp;
--相当于select 1, e.* from emp e;
select count(*) from emp;
select count(comm) from emp;
--组函数不可出现在where之后，语句解析顺序可以辅助理解

2.3 单行函数

2.3.1 数值

--round() 四舍五入

select round(2525.369,2) from dual;
select round(2525.369,-2) from dual;

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

--trunc() 截断函数，不进行四舍五入

select trunc(2525.369,2) from dual;
select sal, trunc(sal,-2) from emp;

--nvl() 有值不执行，空值则转换为0，Oracle独有函数

select nvl(comm,0) from emp;

--nvl2() 有值转换为第一位值，空值则转换为第二位值，Oracle独有函数

select nvl2(comm,1,2) from emp;

2.3.2 日期

select 1, sysdate from emp;

--to_char to_date

select to_char(sysdate,'yyyy-mm-dd day hh24miss') from emp;

select sal , to_char(sal) from emp

select to_date('21053306/25/2020','hh24missmm/dd/yyyy') from emp;

--add_months，月份天数不固定，增加月份

select add_months(to_date('20200129','yyyymmdd'),1) from dual;
select add_months(to_date('20200130','yyyymmdd'),1) from dual;
select add_months(to_date('20200131','yyyymmdd'),1) from dual;
--以上结果相同，注意理解Oracle对日期的处理逻辑

--months_between

select months_between(sysdate,hiredate) from emp;

--last_day
--本月最后一天

select last_day(sysdate) from dual;

--下月第一天

select last_day(sysdate)+1 from dual;

--本月第一天

select add_months(last_day(sysdate)+1,-1) from dual;

--日期截断

select trunc(sysdate,'dd') from dual;
select trunc(sysdate,'day') from dual;

2.3.3 字符串
严格区分大小写

--length() 长度函数

select ename, length(ename) from emp;

--concat() 拼接函数，只能连接2个字段，但可嵌套使用

select concat(ename,job) from emp;
select concat(concat(ename,job),mgr) from emp;
select ename||job from emp;--"||" Oracle中独有写法

--首字母大写 initcap()

select initcap(ename) from emp;

--大、小写转换 upper() lower()

select upper(ename) from emp;
select lower(ename) from emp;

--字符串替换函数 replace()

select replace(ename,'A','%') from emp;--将所有A替换为%

--截断函数 substr(), Oracle中首位从1开始，编程中首位几乎以0开始

select ename, substr(ename,2,3) from emp;--从第2位开始截取，连续截取3位
select ename, substr(ename,-1) from emp;--截取最后1位

--查询某字符所在位数 instr()

select ename, instr(ename,'T') from emp;--从第1位开始找T的位数,返回找到的第1个T的位数
select ename, instr(ename,'T',2) from emp;--从第2位开始往后找,返回找到的第1个T的位数
select ename, instr(ename,'T',-1) from emp;--从最后1位往前找,返回找到的第1个T的位数
select ename, instr(ename,'T',-1,2) from emp;--从最后1位往前找,返回找到的第2个T的位数

2.3.4 case when 函数

select e.*,
case
when deptno = 10 then '一组'
when deptno = 20 then '二组'
when deptno = 30 then '三组'
end 组别
from emp e;

--扩展 decode，仅Oracle中适用

select e.*, decode(deptno,10,'一组',20,'二组',30,'三组') 组别 from emp e;

练习题：

组1：

1.查询公司所有员工的个数。

select count(1) from emp;

2.查询公司中最高薪水是多少。

select max(sal) from emp;

3.查询公司中平均奖金是多少。

select avg(nvl(comm,0)) from emp;

4.查询公司中最晚入职的时间。

select max(hiredate) from emp;

5.查询公司中有奖金的人数。

select count(1) from (select nvl(comm,0) n from emp) where n != 0;

6.查询20部门的最高薪水是多少。

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

7.查询各部门的平均薪水及部门编号，部门名称。

select e.*, dname from (select deptno, avg(sal) from emp group by deptno) e, dept d where e.deptno = d.deptno;

8.查询各部门中最高薪水的员工编号，姓名。

select deptno, sal, empno, ename from emp where (deptno,sal) in
(select deptno, max(sal) from emp group by deptno);--注意理解多字段同时in到一个结果集的情况

9.查询所有员工姓名中包含‘A’的最高薪水。

select max(sal) from emp where ename like '%A%';
select max(sal) from emp where instr(ename,'A') > 0;

10.查询员工ENAME的第三个字母是A的员工的信息(使用2个函数)。

select * from emp where ename like '__A%';
select * from emp where instr(ename,'A') = 3;

11.将员工的参加工作日期按如下格式显示：月份/年份。

select to_char(hiredate,'mm/yyyy') from emp;

12.计算2000年1月1日到现在有多少月，多少周（四舍五入）。

select months_between(sysdate,to_date('20000101','yyyymmdd')) from dual;

select round((sysdate-to_date('20000101','yyyymmdd'))/7) from dual;

13.显示姓名、入职日期和雇员开始工作日是星期几(提示：使用to_char函数)。

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

组2：

1.将姓名和工作作为一列，并查询新列第三个字母是A的员工的信息。

select * from emp where substr(concat(ename,job),3,1) = 'A';
select * from emp where concat(ename,job) like '__A%';
select * from emp where instr(concat(ename,job),'A',3,1) = 3 ;--避免第一位是A就停止查找，直接从第三位开始查找

2.将员工的参加工作日期按如下格式显示：日/月/年

select to_char(hiredate,'dd/mm/yyyy') from emp;

3.计算2000年1月1日到现在有多少年，多少月，多少天。

select months_between(sysdate,to_date('20000101','yyyymmdd'))
,trunc((sysdate-to_date('20000101','yyyymmdd'))/365)
,round((sysdate-to_date('20000101','yyyymmdd'))/7) from dual;

4.选择所有有奖金的员工的首字母和末尾字母。

select substr(ename, 1, 1), substr(ename, -1, 1)
from (select * from emp where nvl(comm, 0) <> 0);

5.写一个查询,用首字母小写，其它字母大写显示雇员的 全名以及显示名字的长度。

select concat(lower(substr(ename,1,1)),substr(ename,2)), length(ename) from emp;

6.查询员工姓名中中包含大写或小写字母A的员工姓名。

select ename from emp where ename like '%A%' or ename like '%a%';
select ename from emp where instr(ename,'a')>0 or instr(ename,'A')>0;
 
7.查询部门编号为10或20，入职日期在81年5月1日之后，并且姓名中包含大写字母A的员工姓名

select ename from emp where deptno in (10,20) and to_date('19810501','yyyymmdd')<hiredate and instr(ename,'A')>0;
--deptno处若适用deptno=10 or deptno=20，则影响执行顺序，and优先级大于or，但可以用括号避免

8.查询员工一共入职多少天（显示为整数）

select round(sysdate-hiredate) from emp;
select trunc(sysdate-hiredate) from emp;

9.查询部门10、20的员工截止到2000年1月1日，工作了多少个月，入职的月份。

select months_between(to_date('20000101','yyyymmdd'),hiredate), to_char(hiredate,'mm') from emp;

10.查询职位不是MANAGER的员工姓名，入职日期,入职当月的最后一天日期。

select ename,hiredate,last_day(hiredate) from emp where job <> 'MANAGER';
 
11.计算2000年1月1日到现在有多少月，多少周（四舍五入）。

select months_between(sysdate,to_date('20000101','yyyymmdd')),round((sysdate-to_date('20000101','yyyymmdd'))/7) from dual;

12.把字符串‘20200226132045’转换成日期格式。

select to_date('20200226132045','yyyymmddhh24miss') from dual;