sql cookbook实验记录（Third）

环境介绍：

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

目前可以下载到的最新版本啦！

关于多表集合操作:

如集合操作

select ename,deptno from emp
union all
select dname,deptno from dept

列和列对应类型需要相同

scott@ORCL>select ename,deptno from emp
union all
select dname,deptno from dept
union all
select 123,000 from dual;
  2    3    4    5  select dname,deptno from dept
       *
ERROR at line 3:
ORA-01790: expression must have same datatype as corresponding expression

而union all是不排除重复值的，想要排除重复值那得上手union。

如这里对deptno的排除。只输出了四个列。

scott@ORCL>select deptno from dept
union 
select deptno from emp;  2    3  

    DEPTNO
----------
	10
	20
	30
	40

当然union也是要求类型一致的

scott@ORCL>select deptno from dept union select ename from emp;
select deptno from dept union select ename from emp
       *
ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression

union大致的效果就是在union all基础上加distinct

select distinct deptno from (select deptno from dept
union all 
select deptno from emp) v 

其执行计划是这样的。

直接用

scott@ORCL>select deptno from dept
union 
select deptno from emp  2    3  
  4  ;

    DEPTNO
----------
	10
	20
	30
	40

所以说union和union all+distinct输出结果一样，但是走的执行计划并不一样，cost也不相同。二者并不真的等价哦。

笛卡尔积

让我们来复习下高中数学

笛卡尔乘积是指在数学中，两个集合X和Y的笛卡尔积（Cartesian
product），又称直积，表示为X×Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员 [3] 。
假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0),
(b, 1), (b, 2)}。

确定下每张表的行数

select * from emp; --14 col

select * from dept; -- 4 col

按笛卡尔的可能性，应该出来的14*4种结果咯。

说明默认的连接就是笛卡尔积啊

加个限制条件看看

scott@ORCL>select count(*) from emp e, dept d  where e.deptno=d.deptno;

  COUNT(*)
----------
	14

如下三个语句其实都是一致的，其区别就在于你呢，想把逻辑放在from子句呢，还是where子句

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

select  e.ename,d.loc from emp e inner join dept d on  (e.deptno=d.deptno);

select  e.ename,d.loc from emp e  join dept d on  (e.deptno=d.deptno);

于是乎，我想说明的是

1 inner关键字可以省略
2 每个语句走的执行计划是一样的，真的等价的。

怎么写都是符合ANSI标准，普及下高中英语
ANSI：美国国家标准学会-——AMERICAN NATIONAL STANDARDS INSTITUTE

说个题外话，我自己也存在的毛病，就是以前思考问题，习惯性的文学好，这是与生具来的习气，如果要搞现实世界的技术工作，逻辑思维还是要更注重点，要两种思维方式都具备。

创造一个视图：

create view v as select ename,job,sal from emp where job='CLERK';

输出一下咯

我想去除emp表里面和视图V的交集。

SELECT ename, e.job, e.sal
  FROM emp e
INTERSECT
SELECT ename, job, sal FROM V;

SELECT ename, e.job, e.sal
  FROM emp e
INTERSECT
SELECT *  FROM V;

既然想取交集的话，当然要保持列值相同了。for example:

SELECT * 
  FROM emp e
INTERSECT
SELECT *  FROM V;  -- ORA-01789: query block has incorrect number of result columns

ORA-01789: query block has incorrect number of result columns

其实Intersect就等价于

SELECT e.ename, e.job, e.sal
  FROM emp e,V
where( e.ename=v.ename and e.job=v.job and e.sal=v.sal)

或者

SELECT e.ename, e.job, e.sal
  FROM emp e join V v 
on ( e.ename=v.ename and e.job=v.job and e.sal=v.sal)

但是intersect明显简化了工作。

求差函数

问题1我想取出emp表中不存在的dept表

 select deptno from dept where deptno not in(
select deptno from emp)

还好oracle给我们开发了求差函数，所以我们还要更简单的方法（mysql，sqlserver不知道有没有这样的功能）

select deptno from dept
  minus
   select deptno from emp;