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或

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;