龙空技术网

亲密接触:oracle sql 复杂查询(上)

浪花小子啊 925

前言:

如今大家对“oracle 查询 不存在的记录”大概比较注意,朋友们都想要学习一些“oracle 查询 不存在的记录”的相关知识。那么小编也在网上汇集了一些关于“oracle 查询 不存在的记录””的相关文章,希望姐妹们能喜欢,看官们快快来学习一下吧!

【七】复杂查询(上):多表连接技术

7.1 简单查询的解析方法:

全表扫描:指针从第一条记录开始,依次逐行处理,直到最后一条记录结束;

横向选择+纵向投影=结果集

7.2 多表连接

7.2.1多表连接的优缺点

优点:

1)减少冗余的数据,意味着优化了存储空间,降低了IO负担。

2)根据查询需要决定是否需要表连接。

3)灵活地增加字段,各表中字段相对独立(非主外键约束),增减灵活。

缺点:

1)多表连接语句可能冗长复杂,易读性差。

2)可能需要更多的CPU资源,一些复杂的连接算法消耗CPU和Memory。

3)只能在一个数据库中完成多表连接查询。

7.2.2多表连接中表的对应关系

1)一对一关系

将表一分为二,最简单的对应关系

2)一对多关系

两表通过定义主外键约束,符合第三范式标准的对应关系。

3)多对多关系

多对多关系

非标准的对应关系:当两表为多对多关系的时候,通常需要建立一个中间表,中间表至少要有两表的主键,这样,可使中间表分别与每个表为一对多关系。

7.2.3 多表连接的种类和语法

交叉连接(笛卡尔积)

非等值连接

等值连接(内连)

外连接(内连的扩展,左外,右外,全连接)

自连接

自然连接(内连,隐含连接条件,自动匹配连接字段)

复合连接(多个结果集进行并、交、差)

7.2.1 交叉连接(笛卡尔积)

连接条件无效或被省略,两个表的所有行都发生连接,所有行的组合都会返回(n*m)

SQL99写法:

SCOTT@ prod>select * from emp e cross join dept d;

Oracle写法:

SCOTT@ prod>select * from emp e, dept d;

7.2.2非等值连接:(连接条件没有“=”号)

SQL99写法:

SCOTT@ prod>select empno, ename, sal, grade, losal,hisal from emp join salgrade on sal between losal and hisal;

Oracle写法:

SCOTT@ prod>select empno, ename, sal, grade, losal, hisal from emp, salgrade where sal between losal and hisal;

7.2.3 等值连接,典型的内连接

SQL99写法:

SCOTT@ prod>select e.ename, d.loc from emp e inner join dept d on e.deptno=d.deptno;

Oracle写法:

SCOTT@ prod>select e.ename, d.loc from emp e,dept d where e.deptno=d.deptno;

7.2.4 等值连接的using字句(常用)

等值连接的连接字段可以相同,

比如 on e.deptno=d.deptno;

也可以不同

比如 on e.empno=e.mgr

如果连接字段相同,可以使用using字句简化书写

如 on e.deptno=d.detpno.; 换成using(deptno)

例:

SCOTT@ prod>select deptno, e.ename, d.loc from emp e inner join dept d using(deptno); using里也可以多列,使用using关键字注意事项:

1、如果select的结果列表项中包含了using关键字所指明的那个关键字,那么,不要指明该关键字属于哪个表。

2、using中可以指定多个列名。

3、on和using关键字是互斥的,也就是说不能同时出现。

7.2.5 外连接(包括左外连接,右外连接,全外连接)

1)左外连接语法

SQL99语法:

SCOTT@ prod>select * from emp e left outer join dept d on e.deptno=d.deptno;

Oracle语法:

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

2)左连接要理解两个关键点

1、如何确定左表和右表

SQL99写法:通过from后面表的先后顺序确定,第一个表为左表

SCOTT@ prod>select e.ename, d.loc from emp e left join dept d on e.deptno=d.deptno;

from后第一个表是emp表,为左表,“=”左右位置无所谓

Oracle写法:通过where 后面的“=”的位置确定,“=”号左边的为左表

SCOTT@ prod>select e.ename, d.loc from emp e, dept d where e.deptno=d.deptno(+);

“=”左边是emp表,为左表,from后面表位置无所谓

2、左连是左表为主

①左连是以左表为驱动,每行都参与匹配右表的行,匹配上就连成一行,如果匹配不上,左表行也不缺失该连接行,这时右表内容填空就是了。

②左连后,左表的行是不缺失的,即左连后的结果集的行数>=左表行数,存在>的可能是因为左表的一行可能匹配了右表的多行。

③也可以左表、右表都是同一个表,即“自左连”。

3、到底哪个表当做主表好

无一定之规,根据业务需求来决定。

两表之间一般以主外键确定一对多关系,外键表是明细表,比如emp和dept的关系,以deptno确定父子关系,emp是外键表

你要查每个员工的工作地点,这时以外键表(emp明细表)做左表理所当然

SCOTT@ prod>select e.ename, d.loc from emp e left outer join dept d on e.deptno=d.deptno;

你要查每个部门有多少员工,要求根据每个部门号做统计(40号部门没有员工也统计),这时以主键表(dept)做做表更合理

SCOTT@ prod>select d.deptno, count(e.ename) from emp e, dept d where d.deptno=e.deptno(+) group by d.deptno;

推导一下:

SCOTT@ prod>select d.deptno, e.ename from dept d left outer join emp e on e.deptno=d.deptno;

以上三点,属于个人理解,这套法则同样适用于右连。

2)右外连接

SQL99语法:

SCOTT@ prod>select * from emp e right join dept d on e.deptno=d.deptno;

Oracle语法:

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

3)全外连接

SQL99语法:

SCOTT@ prod> select * from emp e full join dept d on e.deptno=d.deptno;

Oracle语法:(无,等同于union连接)

SCOTT@ prod>

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

Union

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

7.2.6 自连接

SQL99语法:

SCOTT@ prod>select e1.empno,e2.mgr from emp e1 cross join emp e2;

Oracle语法:

SCOTT@ prod> select e1.empno,e2.mgr from emp e1,emp e2;

注意:必须使用别名区别不同的表

7.2.6 自然连接(属于内连中等值连接)

使用关键字natural join,就是自然连接。

SCOTT@ prod>select e.ename, d.loc from emp e natural join dept d;

如果有多列复合匹配条件,则自动多列匹配。

7.3 复合查询(使用集合运算符)

Union,对两个select结果集进行并集操作,重复行只取一次,同时进行默认规则的排序;

Union All,对两个select结果集进行并集操作,包括所有重复行,不进行排序;

Intersect,对两个select结果集进行交集操作,重复行只取一次,同时进行默认规则的排序;

Minus,对两个select结果集进行差操作,不取重复行,同时进行默认规则的排序。

复合查询操作有并,交,差3种运算符。

示例:

SQL> create table dept1 as select * from dept where rownum <=1;

SQL> insert into dept1 values (80, 'MARKTING', 'BEIJING');

SQL> select * from dept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL> select * from dept1;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

80 MARKTING BEIJING

1)union

SQL>

select * from dept

union

select * from dept1;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

80 MARKTING BEIJING

2)union all

SQL>

select * from dept

union all

select * from dept1;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

10 ACCOUNTING NEW YORK

80 MARKTING BEIJING

特别注意:可以看出只有union all的结果集是不排序的。

3)intersect

SQL>

select * from dept

intersect

select * from dept1;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

4)minus(注意谁minus谁)

SQL>

select * from dept

minus

select * from dept1;

DEPTNO DNAME LOC

---------- -------------- -------------

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL>

select * from dept1

minus

select * from dept;

DEPTNO DNAME LOC

---------- -------------- -------------

80 MARKTING BEIJING

7.4 复合查询几点注意事项

1)列名不必相同,但要类型匹配且顺序要对应,大类型对上就行了,比如char对varchar2,date对timestamp都可以,字段数要等同,不等需要补全。

create table a (id_a int,name_a char(10));

create table b (id_b int,name_b char(10),sal number(10,2));

insert into a values (1, 'sohu');

insert into a values (2, 'sina');

insert into b values (1, 'sohu', 1000);

insert into b values (2, 'yahoo', 2000);

commit;

SQL> select * from a;

ID_A NAME_A

---------- ----------

1 sohu

2 sina

SQL> select * from b;

ID_B NAME_B SAL

---------- ---------- ----------

1 sohu 1000

2 yahoo 2000

SQL>

select id_a,name_a from a

union

select id_b,name_b from b;

2)四种集合运算符优先级按先后出现的顺序执行,如有特殊要求可以使用()。

3)关于复合查询中order by 使用别名排序的问题:

①缺省情况下,复合查询后的结果集是按所有字段的组合隐式排序的(除union all 外)

如果不希望缺省的排序,也可以使用order by显式排序

SQL> select id_a, name_a name from a

union

select id_b, name_b name from b

order by name;

SQL>

select id_a, name_a from a

union

select id_b, name_b from b

order by 2;

②显式order by是参照第一个select语句的列元素。所以,order by后的列名只能是第一个select使用的列名、别名、列号。如果是补全的null值需要order by,则需要使用别名。

SQL>

select id_a, name_a name,to_number(null) from a

union

select id_b, name_b name,sal from b

order by sal;

报错:ORA-00904: "SAL": 标识符无效

以下三种写法都是正确的:

SQL>

select id_a, name_a name,to_number(null) from a

union

select id_b, name_b name,sal from b

order by 3;

SQL>

select id_b, name_b name,sal from b

union

select id_a, name_a name,to_number(null) from a

order by sal;

SQL>

select id_a, name_a name,to_number(null) aa from a

union

select id_b, name_b name,sal aa from b

order by aa;

③排序是对复合查询结果集的排序,不能分别对个别表排序,order by 只能一次且出现在最后一行;

SQL>

select id_a, name_a from a order by id_a

union

select id_b, name_b from b order by id_b;

报错:ORA-00933: SQL 命令未正确结束

the end !!!

@jackman 共筑美好!

标签: #oracle 查询 不存在的记录