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
