oracle复杂查询语句(oraclesql复杂查询)

多表连接技术

7.1解析简单查询:

全表扫描:指针以第1条记录为起点,按顺序逐行加工,直至第1条记录的末尾;

横向的选取+纵向的投影=结果集

7.2多表连接

7.2、1多表连接利弊

优势:

1)减少冗余数据即优化存储空间和减轻IO负担。

2)实现了不同类型的表连接;

3)字段的灵活添加,每个表中的字段都是相对独立的(不受主外键的限制),添加和删除都具有灵活性。

不足之处:

1)多表连接语句会冗长繁杂且易读性较差。

3)多个表的连接查询仅能在单个数据库上实现。

7.2 .2多表连接中各表之间的一一对应

1)一对一的关系

把表分成两半是最简单对应关系

2)一对多关系

这两个表以界定主外键约束的方式满足第三范式标准中的相应要求。

图片[1]-oracle复杂查询语句(oraclesql复杂查询)-【聚禄鼎】一站式企业服务平台

图片[2]-oracle复杂查询语句(oraclesql复杂查询)-【聚禄鼎】一站式企业服务平台

3)多对多的关系

图片[3]-oracle复杂查询语句(oraclesql复杂查询)-【聚禄鼎】一站式企业服务平台

多对多的关系

非标准对应关系:两个表之间是多对多关系时,一般需设置中间表,中间表最少应有两个表主键,如此一来,就可以使得中间表和各个表各自都是一对多。

7.2 .3多表连接类型及语法

交叉连接,即笛卡尔积

非等值连接

等值连接(内连)

外连接(延伸内连、左连接、右连接、全连接)

自连接

自然联系(内连、隐含联系条件、联系字段的自动搭配)

复合连接(若干结果集的并接,交接,差接)

7.2 .1交叉连接,笛卡尔积

连接条件不成立或者略去,2个表中的全部行出现连接且全部行合并后返回(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关键字指出的那一个关键字时,则不指出这个关键字是哪一个表格。

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)左连接需要了解2个要点

1.怎样判断左,右表的位置

SQL99书写方法:由from后的表格顺序决定,第1个表格是左侧表格

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.左连,主要指左表

1左连由左表带动,每一行参与到与右表相匹配的行列中,在相匹配时连接成一行,若不能相匹配,则左表行列中不会缺少这个相连的行列,此时右表中内容填空即成。

②左连和左表同时存在时,则左连的结果集只与左表行数有关系,而不是与左表或右表没有关系;

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个部门没雇员也是统计的),此时用主键表的形式来进行表格的制作就比较合理了

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;

上述3点,都是个人认识,右连法则也是如此。

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 @ pro >

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

Unionist

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自然连接,属内连的等值连接

用关键字naturaljoin是自然连接。

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

若存在多列组合匹配的情况下,进行自动多列匹配。

7.3)对于每个复合查询,定义一个集合运算符?

Union,在2个select结果集上执行并集操作并在默认规则排序的情况下重复行只执行1次;

Union All中,两个select结果集的并集操作包含了全部重复的行且没有排序;

在Intersect中,执行2个select结果集的交集操作和只执行1次重复行,而执行默认规则;

在Minus中,在默认规则排序的前提下,差操作两个select结果集而不取重复行。

复合查询操作具有并、交、差三个算子。

例:

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 COL

———- ————– ————-

10 ACCOUNTING NEW YORK.

20 LOOKING FOR DALLAS

Thirty SALES CHICAGO

Forty OPERATIONS BOSTON

SQL> select * from dept1;

DEPTNO DNAME COL

———- ————– ————-

10 ACCOUNTING NEW YORK.

80 MARKTING BEIJING

1) Trade unions

SQL>

Select * from the department.

unionization

select * from dept1;

DEPTNO DNAME COL

———- ————– ————-

10 ACCOUNTING NEW YORK.

20 LOOKING FOR DALLAS

Thirty SALES CHICAGO

Forty OPERATIONS BOSTON

80 MARKTING BEIJING

3) Unions all

SQL>

Select * from the department.

unionism all

select * from dept1;

DEPTNO DNAME COL

———- ————– ————-

10 ACCOUNTING NEW YORK.

20 LOOKING FOR DALLAS

Thirty SALES CHICAGO

Forty OPERATIONS BOSTON

10 ACCOUNTING NEW YORK.

80 MARKTING BEIJING

特别说明:可见仅union all结果集没有排序。

3) Intersect.

SQL>

Select * from the department.

intersecting

select * from dept1;

DEPTNO DNAME COL

———- ————– ————-

10 ACCOUNTING NEW YORK.

4)minus,注:minus是什么人

SQL>

Select * from the department.

minus

select * from dept1;

DEPTNO DNAME COL

———- ————– ————-

20 LOOKING FOR DALLAS

Thirty SALES CHICAGO

Forty OPERATIONS BOSTON

SQL>

select * from dept1.

minus

select * from dept;

DEPTNO DNAME COL

———- ————– ————-

80 MARKTING BEIJING

7.4复合查询的一些注意事项

1)列名不一定是一样的,但是应该是类型匹配和顺序对应的,大型的类型配对即可,如char到varchar2、date到timestamp均可,字段数应该相等,不要等待需补充。

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

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

insert into a values (1, ‘sohu’);

inserted into a values(2, ‘sina’);

insert into b values (1, ‘sohu’, 1000);

insert b values (2, ‘yahoo’, 2000); e!

commit;

SQL> select * from a;

ID_A NAME_A

———- ———-

hu 1

2nd sina

SQL> select * from b;

ID_B NAME_B SAL

———- ———- ———-

hu 1,000

2 yahoo 2000

SQL>

select id_a,name_a from a list.

unionization

choose id_b,name_b out of b;

2)4个集合运算符的优先级是按照依次发生的次序进行的,如果有特殊需要可采用()。

3)对于复合查询,orderby采用别名排序:。

1.缺省在复合查询之后,结果集按照全部字段(union all除外)合并隐式进行排序

不想缺省排序的话还可以用orderby显式排序

SQL> select id_a, name_a name from a.

unionization

select id_b, name_b from the b.

order by name;

SQL>

select id_a, name_a from a list.

unionization

choose id_b, name_b from b.

Order by 2 persons;

2.显式order by指参考首条select语句列元。因此orderby后面的列只能作为第一个select中用到的列,别名和列号。③在查询中,trie值越大,所需查找到的数据也就越多;而对某个对象而言,该对象所包含的属性或参数越多时,其查询处理效率越低。若为补全null值要求orderby,需采用别名。

SQL>

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

unionization

select id_b, name_b name,sal from b.

order by sal;

报错误:ORA-00904:“SAL”:标识符失效

下列3种写法均对:

SQL>

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

unionization

select id_b, name_b name,sal from b.

ordered by three;

SQL>

select id_b, name_b name,sal from b.

unionization

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.

unionization

choose id_b, name_b name,sal aa of b.

order by aa;

3.排序为复合查询结果集,单个表不可以单独进行,orderby只可以进行1次并显示在末行中;

SQL>

select id_a, name_a from a order by id_a.

unionization

select id_b, name_b from b order by id_b;

报错误:ORA-00933:SQL命令没有被适当终止

原文链接:http://www.sfdkj.com/15687.html

 

© 版权声明
THE END
喜欢就支持一下吧
点赞10 分享
评论 抢沙发
头像
欢迎您留下宝贵的见解!
提交
头像

昵称

取消
昵称表情代码图片