ETL复习--2020年3月25日--结果集、逻辑运算符、集合、子查询、exists
--2020年3月25日 AM
昨日回顾
0.1
insert into emp1(empno,job) select deptno, dname from dept;
--保证目标字段和源字段类型相同、长度满足即可插入，也不绝对
0.2 大对象模式
调出建表语句
select dbms_metadata.get_ddl('TABLE','EMP') from dual;
/* CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
*/
字段格式为 大对象格式 clob 时，可插入超长字符串，即超长文本
select * from user_tables;--数据字典
select * from emp order by dbms_random.value;
1、限制返回结果集
mysql: select * from emp limit 3;--查询前三行
sqlserver: select top 3 * from emp;--查询前三行
1.1 rownum
select rownum, EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO from emp;
select * from emp where rownum<=3;
--注意rownum的运行原理，行级执行方式，从第一行开始，若不符合条件，则剔除，原来第二行变成第一行，继续执行
--rownum=1或<x或<=x
1.2 rowid
号称全球唯一，但不绝对
select rowid, EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO from emp;
2、模糊查询
like % _
select * from emp where ename like '%A%';--不确定A的所在位置
select * from emp where ename like '_A%';--确定A在第二位，但不确定A后面内容
select * from emp where ename like '%A__';--确定A在倒数第三位，但不确定A前面内容
select * from emp where ename like '_M%T_';--倒数第二位为T，第二位是M
3、逻辑运算符
3.1 and, or, not
优先级: not > and > or
例：查询emp表工资大于2000，并且所在部门是10号的员工信息
select * from emp where sal>2000 and deptno=10;
3.2 in --离散型
select * from emp where deptno in (10,20,30,40);
3.3 between and -- 连续型,闭区间
select * from emp where sal between 1000 and 3000;
3.4 any, all
例：查询工资大于20号部门所有人工资的员工的信息
select * from emp where sal > all(select sal from emp where deptno=20);
select * from emp where sal > any(select sal from emp where deptno=20);
= any 等价于 in /// = all all后的子集返回值只有一个时可以用
--PM
4、集合操作
4.1 并集操作
union all 不去重并集，常用
select ename from emp --主列名以ename为主
union all
select job from emp;
union 去重并集，不常用
select ename from emp
union
select ename from emp;
保持数据类型相同才可并集
select ename from emp
union all
select job from emp
union all
select to_char(hiredate,'yyyymmdd') from emp
union all
select to_char(empno) from emp;
4.2 交集操作 intersect
select deptno from emp
intersect
select deptno from dept;
4.3 差集操作
select deptno from dept
minus
select deptno from emp;
--注意前后顺序
select deptno from emp
minus
select deptno from dept;
5、子查询
例：查询最高工资的员工的名字
/*select ename from emp where sal = max(sal)*/ --语法错误
例：查询最高工资、最低工资的员工的名字
select max(sal) from emp
union all
select min(sal) from emp;
/*select ename from emp where sal = any(select max(sal),min(sal) from emp)*/
--语法错误，any后查询结果为多个字段，并集操作可修改
select ename from emp
where sal = any(
select max(sal) from emp
union all
select min(sal) from emp);
5.1 子查询返回结果
单行单列
select * from emp where sal=all(select min(sal) from emp);
-- =all 的应用
单行多列
例：查询与scott从事同一工作且工资相同的员工信息
select *
from emp
where (job, sal) = (select job, sal from emp where ename = 'SCOTT')
and ename <> 'SCOTT';
多行单列
常用any、all、in
例：查询员工工资等于30号部门任意员工工资的员工信息
select * from emp where sal in (select sal from emp where deptno = 30);
多行多列
例：查询员工工资等于30号部门任意员工工资的员工信息
select *
from emp
where (job, sal) in (select job, sal from emp where deptno = 30);
6、exists 空数据判断关键词
select * from emp where exists(select 1 from dual where 1=2);
select * from emp where exists(select 1 from dual where 1=1);
exists关注子查询中是否有值，有值则执行，无值则不执行，不关心值的大小多少，如此一来，执行效率较高
练习题
集合类：
1.对emp表和dept表的empno与deptno取并集。
select empno from emp
union all
select deptno from dept;
2.对emp表和dept表deptno与deptno取交集。
select deptno from emp
intersect
select deptno from dept;
子查询类：
1.查询最高薪水的员工信息。
select * from emp where sal = (select max(sal) from emp);
2.查询薪水最低的员工信息。
select * from emp where sal = (select min(sal) from emp);
3.查询各部门最高薪水的员工信息。
select * from emp where (deptno, sal) in (select deptno, max(sal) from emp group by deptno);
4.查询‘SMITH’的领导姓名。
select ename from emp where empno = (select mgr from emp where ename='SMITH');
5.查询部门名称是‘SALES’的员工信息。
select * from emp where deptno = (select deptno from dept where dname = 'SALES');
6.查询公司中薪水最高的员工信息。
select * from emp where sal = (select max(sal) from emp);
7.查询薪水等级为4的员工信息。
select * from emp where sal between (select losal from salgrade where grade = 4)
and (select hisal from salgrade where grade = 4);
8.查询领导者是‘BLAKE’的员工信息。
select * from emp where mgr = (select empno from emp where ename = 'BLAKE');
9.查询最高领导者的薪水等级。
select grade from salgrade where (select sal from emp where mgr is null) between losal and hisal;
或
select grade from salgrade where (select sal from emp where mgr is null) >= losal and
(select sal from emp where mgr is null) <= hisal;
