分享
 
 
 

复杂查询语句的使用

王朝other·作者佚名  2008-05-31
窄屏简体版  字體: |||超大  

.查询语句的使用

使用 select语句和子查询(subquery)可以从一个或多个表,视图,实体试图中返回数据.

1.1相关子查询

可以将子查询(as subquery)或in或exists当成where的一个条件的一部分,这样的查询称为子查询

.where中可以包含一个select语句的子查询

.where中可以包含in,exists语句

.最多可以嵌套16层

.层次过多会影响性能

[例]简单子查询实例

查询是否有的专家既以研究所的名义来申请基金项目,又以大学系为单位申请项目

(按规定只能以一个单位来申请)

SQL> create table univ_subject

2 (

3 name varchar2(12) not null,

4 per_id number not null,

5 dept_name varchar2(20)

6 );

SQL> insert into univ_subject values('gaoqianjing',1001,'信息工程系');

SQL> insert into univ_subject values('wangbing',1002,'物理系');

SQL> insert into univ_subject values('liming',1003,'化学系');

===============

SQL> create table colle_subject

2 (

3 colle_name varchar2(20),

4 per_id number

5 );

SQL> insert into colle_subject values('电子研究所',1001);

SQL> insert into colle_subject values('物理研究所',1005);

================

SQL> select name,per_id,dept_name from univ_subject where per_id in

2 (select per_id from colle_subject);

NAME PER_ID DEPT_NAME

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

gaoqianjing 1001 信息工程系

############################复杂查询语句的使用#####################################

1.查询语句的使用

使用 select语句和子查询(subquery)可以从一个或多个表,视图,实体试图中返回数据.

1.1相关子查询

可以将子查询(as subquery)或in或exists当成where的一个条件的一部分,这样的查询称为子查询

.where中可以包含一个select语句的子查询

.where中可以包含in,exists语句

.最多可以嵌套16层

.层次过多会影响性能

[例]简单子查询实例

查询是否有的专家既以研究所的名义来申请基金项目,又以大学系为单位申请项目

(按规定只能以一个单位来申请)

SQL> create table univ_subject

2 (

3 name varchar2(12) not null,

4 per_id number not null,

5 dept_name varchar2(20)

6 );

SQL> insert into univ_subject values('gaoqianjing',1001,'信息工程系');

SQL> insert into univ_subject values('wangbing',1002,'物理系');

SQL> insert into univ_subject values('liming',1003,'化学系');

===============

SQL> create table colle_subject

2 (

3 colle_name varchar2(20),

4 per_id number

5 );

SQL> insert into colle_subject values('电子研究所',1001);

SQL> insert into colle_subject values('物理研究所',1005);

================

SQL> select name,per_id,dept_name from univ_subject where per_id in

2 (select per_id from colle_subject);

NAME PER_ID DEPT_NAME

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

gaoqianjing 1001 信息工程系

1.2外连接

[例]外连接实例

招生中所有学生的信息放在students表中,而部分有特长的学生在另一个表中stuent_skill中同样有该学生

的信息。现在要全部列出所有学生,假如某个学生在表student_skill中就有其特长信息,并显示特长信息,假如

某个学生没有特长就显示特长问空.

SQL> create table students

2 (

3 st_id varchar2(20),

4 name varchar2(10),

5 age number(2),

6 tol_score number(3)

7 ) ;

SQL> insert into students values('973231','wangbindu',22,501);

SQL> insert into students values('973232','zhuzhijing',21,538);

SQL> insert into students values('973233','gaojing',21,576);

===================

SQL> create table student_skill

2 (

3 st_id varchar2(20),

4 skill varchar2(20)

5 );

SQL> insert into student_skill values('973231','篮球');

SQL> insert into student_skill(st_id) values('973232');

SQL> insert into student_skill values('973233','足球');

===================

SQL> select a.* , b.skill from students a,student_skill b where a.st_id=b.st_id(+)

order by a.st_id;

ST_ID NAME AGE TOL_SCORE SKILL

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

973231 wangbindu 22 501 篮球

973232 zhuzhijing 21 538

973233 gaojing 21 576 足球

1.3自我连接

自我连接是在同一个表或视图内进行条件连接.

[例]自我连接实例

查询每个雇员的名字和该雇员的经理的名字:

SQL> select e1.ename' work for 'e2.ename "Employees and their Managers"

2 from scott.emp e1,scott.emp e2 where e1.mgr=e2.empno;

Employees and their Managers

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

SMITH work for FORD

ALLEN work for BLAKE

WARD work for BLAKE

JONES work for KING

MARTIN work for BLAKE

BLAKE work for KING

CLARK work for KING

SCOTT work for JONES

TURNER work for BLAKE

ADAMS work for SCOTT

JAMES work for BLAKE

FORD work for JONES

MILLER work for CLARK

1.4UNION , INTERSECT及 MINUS

UNION: 可以将两个以上的表的相类似的查询结果放在一起 (union all则表示返回所有的行)

具体语法:

select ...

union[all]

select...

==========

INTERSECT: 返回两个表中相同的信息

具体语法:

select ...

intersect

select...

==========

MINUS : 返回一个表中出现的信息

具体语法:

select ...

minus

select...

[例1]UNION操作实例

SQL> select st_id from students

2 union

3 select st_id from student_skill;

ST_ID

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

973231

973232

973233

[例2]INTERSECT操作实例

列出有特长的学生的学号

SQL> select st_id from students

2 intersect

3 select st_id from student_skill;

ST_ID

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

973231

973233

[例3]MINUS操作实例

列出没有特长学生的学号

select st_id from students

minus

select st_id from student_skill;

ST_ID

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

973232

2.创建复杂的视图

许多应用系统有统计等功能,建议最好把这些复杂语句写成视图.下面是几个常用的视图.

2.1分组视图

[例1]简单的分组视图

SQL> create or replace view dept_tot as

2 select a.dname dept,sum(b.sal) total_sal from scott.dept a,scott.emp b

3 where a.deptno=b.deptno group by a.dname;

查看已建立。

SQL> select * from dept_tot;

DEPT TOTAL_SAL

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

ACCOUNTING 8750

RESEARCH 10875

SALES 9400

[例2]带复杂函数视图

SQL> create or replace view itemtot as

2 select persion,sum(amount) itemtot from ledger

3 where actiondate between

4 to_date('01-MAR-1901','dd-mon-yyyy') and

5 to_date('31-MAR-1901','dd-mon-yyyy')

6 and action in('bought','raid') group by persion;

2.2合计视图

[例]合计函数视图实例

SQL> create or replace view emp_no1 as

2 select deptno,sum(sal) 工资和,sum(comm) 总和

3 from scott.emp group by deptno;

SQL> select * from emp_no1;

DEPTNO 工资和 总和

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

10 8750

20 10875

30 9400 2200

2.3组合视图

[例]带组合函数的视图

SQL> create or replace view byitem as

2 select l.persion persion.item, amount, 100*amount/item bypersion,100*amount/total bytotal

3 from ledgent l,itemtotal i,total where l.persion=i.persion where l.persion=i.persion

4 and actiondate between

5 to_date('01-MAR-1901','dd-mon-yyyy') and

6 to_date('31-MAR-1901','dd-mon-yyyy')

7 and action in('bought','raid') ;

3.家族树

语法:

select column from table_name start with column=value

connect by prior 父主键=子主键

3.1排除单一性和分枝

以Oracle中的EMP表为例

[例]从顶到底列出各雇员的信息

SQL> select lpad(' ',4*(level-1))ename name,empno,mgr from emp start with mgr is null

2 connect by prior empno=mgr;

NAME EMPNO MGR

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

KING 7839

JONES 7566 7839

SCOTT 7788 7566

ADAMS 7876 7788

3.2遍历至根

[例1]现在要从某个雇员开始向他的上级列出该雇员的层次结构

SQL> col ename for a30;

SQL> select lpad(' ',4*(level-1))ename ename,mgr,empno from scott.emp

2 start with mgr=7788 connect by prior mgr=empno;

ENAME MGR EMPNO

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

ADAMS 7788 7876

SCOTT 7566 7788

JONES 7839 7566

KING 7839

[例2]列出所有雇员的层次结构

SQL> select lpad(' ',4*(level-1))ename ename,empno,mgr from scott.emp

2 start with mgr is not null

3 connect by empno=prior mgr;

ENAME EMPNO MGR

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

SMITH 7369 7902

FORD 7902 7566

JONES 7566 7839

KING 7839

ALLEN 7499 7698

BLAKE 7698 7839

KING 7839

WARD 7521 7698

BLAKE 7698 7839

KING 7839

JONES 7566 7839

KING 7839

MARTIN 7654 7698

BLAKE 7698 7839

KING 7839

BLAKE 7698 7839

KING 7839

CLARK 7782 7839

KING 7839

SCOTT 7788 7566

JONES 7566 7839

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有