oracle11g的复杂SQL语句.ppt

《oracle11g的复杂SQL语句.ppt》由会员分享，可在线阅读，更多相关《oracle11g的复杂SQL语句.ppt（56页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、目标q掌握 SQL语句的执行顺序、能分析复杂SQL语句的执行过程 q掌握 Oracle子查询、自连接、行列转换q掌握分析函数、decode函数、 SELECT CASE WHEN的使用q掌握 Oracle 分页、删除重复记录的方法SQL语句的执行顺序 SQL语句的执行顺序 l常见的select、from、where的顺序：l1, from 2, where 3, selectl完整的select、from、where、group by、having、order by的顺序：l1, from 2, where 3, group by 4,having 5, select 6, order byE

2、XISTS 的使用 EXISTS的使用 lEXISTS用来判断查询所得的结果中，是否有l满足条件的纪录存在。l例:select *from student l where exists(select * from addressl where zz=郑州);l 从select 、from、where三者的先后执行顺l序来分析。子查询的使用 子查询的使用 lcreate table student(sno number(6) ,birthday date, sname varchar2(10);linsert into student values(1, 11-1月-81 , 张三);linse

3、rt into student values(2, 10-3月-82 , 李四);linsert into student values(3, 06-1月-83 , 王五);linsert into student values(4, 26-1月-83 , 赵六);lcreate table address(sno number(6) , zz varchar2(10);linsert into address values(1, 郑州);linsert into address values(2, 开封);linsert into address values(3, 洛阳);linsert

4、into address values(4, 郑州); 子查询的使用 l要求：找出zz是郑州的学生中，sno最大的学生的 sname 。 子查询的使用 lselect sname l from student lwhere sno =( lselect max(sno) lfrom addresslwhere zz= 郑州 );group by的练习 group by 练习 l1.创建student表l create table student(xh number,xm varchar2(10),nl int);l insert into student values (1,A,21); l

5、insert into student values (2,B,22);l insert into student values (3,A,23); l insert into student values (4,A,24);l insert into student values (5,A,25); l insert into student values (6,C,26);l insert into student values (7,B,27); group by 练习 l任务：查找xm有相同的纪录，并显示出来，如下：l XH XM NLl- - -l 1 A 21l 3 A 23l 4

6、 A 24l 5 A 25l 2 B 22l 7 B 27 group by 练习l答案：select * from student where xm in (select xm from student group by xm having count(*)1);自连接的使用 自连接的使用 lCREATE TABLE 管理人员 (l 编号 char(10) ,l 姓名 varchar2(10) ,l 管理人员编号 char(10)l);linsert into 管理人员 values(001, 张一, 004);linsert into 管理人员 values(002, 张二, 004);l

7、insert into 管理人员 values(003, 张三, 003);linsert into 管理人员 values(004, 张四, 004); 自连接的使用 l要求：现在想显示：编号，姓名，管理人员姓名 自连接的使用 lselect a.编号,a.姓名,b.姓名 as 管理人员姓名 lfrom 管理人员 a join 管理人员 b on a.管理人员编号=b.编号;lSELECT CASE WHENSELECT CASE WHEN的的的使用 SELECT CASE WHEN的的使用l语法：lCASE W H E N 条 件 1 T H E N a c t i o n 1 W H E

8、 N 条 件 2 T H E N a c t i o n 2 W H E N 条 件 3 T H E N a c t i o n 3 . ELSE actionN END CASE SELECT CASE WHEN的的使用lselect case l when substr(20090310,5,2) = 01 then 一月份l when substr(20090310,5,2) = 02 then 二月份l when substr(20090310,5,2) = 03 then 三月份l when substr(20090310,5,2) = 04 then 四月份l else nulll

9、 end lfrom dual;SELECT CASE WHEN的的使用l语法：lCASE selector W H E N v a l u e 1 T H E N a c t i o n 1 WHEN value2 THEN action2l W H E N v a l u e 3 T H E N a c t i o n 3 . ELSE actionNEND CASE SELECT CASE WHEN的的使用lselect case substr(20090310,5,2) l when 01 then 一月份l when 02 then 二月份l when 03 then 三月份l wh

10、en 04 then 四月份l else nulll end lfrom dual; SELECT CASE WHEN的的使用lcreate table 成绩(sno number, km varchar2(10), score number,grade char(6); linsert into 成绩 values(1, 语文, 65,null);linsert into 成绩 values(2, 数学, 76,null);linsert into 成绩 values(3, 英语, 86,null);linsert into 成绩 values(4, 语文, 94,null);l要求：把每个

11、学生的grade列，用相应的等级来更新。SELECT CASE WHEN的的使用lupdate 成绩 set grade = (l select grade from (l select sno ,l case when score = 90 then 优秀l when score = 80 then 良好l when score = 70 then 中等l when score = 60 then 及格 l else 不及格l end gradel from 成绩l ) al where 成绩.sno = a.sno );复杂更新语句的使用复杂更新语句的使用l表T1里有 a,b,c.N个字段

12、，表T2里有 a,b,c三个字段，l然后想在T1中c与表T2中c相同的情况下从表T2中将a,b覆盖表T1中的a,b怎么做 ？l create table T1(a int ,b int ,c int ,d int ,e int);l create table T2(a int ,b int ,c int );l insert into T1 values(1,2,3,4,5);l insert into T1 values(10,20,3,4,5);l insert into T1 values(10,20,4, 40,50);l insert into T2 values( -1, -1 ,

13、 3);l insert into T2 values( -2, -2, 4);复杂更新语句的使用lupdate t1 set a= (select a from t2 where t1.c= t2.c ) , b =(select b from t2 where t1.c= t2.c) where t1.c in (select c from t2);l分析函数的使用分析函数 分析函数用于计算完成聚集的累计排名、序号等 分析函数为每组记录返回多个行 以下三个分析函数用于计算一个行在一组有序行中的排位，序号从1开始 ROW_NUMBER 返回连续的排序，不论值是否相等 RANK 具有相等值的行

14、排序相同，序数随后跳跃 DENSE_RANK 具有相等值的行排序相同，序号是连续的DECODE 的使用 DECODE 中的if-then-else逻辑 q在逻辑编程中，经常用到If Then Else 进行逻辑判断。在DECODE的语法中，实际上就是这样的逻辑处理过程。它的语法如下：qDECODE(value, if1, then1, if2,then2, if3,then3, . . . else )qValue 代表某个表的任何类型的任意列或一个通过计算所得的任何结果。当每个value值被测试，如果value的值为if1，Decode 函数的结果是then1；如果value等于if2，De

15、code函数结果是then2；等等。事实上，可以给出多个if/then 配对。如果value结果不等于给出的任何配对时，Decode 结果就返回else 。q需要注意的是，这里的if、then及else 都可以是函数或计算表达式。DECODE 的简单例子 lCreate table student(id number,name varchar2(10),sex char(1);lInsert into student values(1, 张, 1);lInsert into student values(2, 王, 2);lInsert into student values(3, 李, 1)

16、;lSelect name ,decode(sex, 1,男生, 2,女生) lfrom student;DECODE 的简单例子 l用case实现：lselect id,name,l case sexl when 1 then 男l when 2 then 女l end 性别lfrom student;l DECODE 取出一行内两列中的较大值 lCreate table sales(month char(2),sales_tv number,sales_computer number);l Insert into sales values(01, 10, 18);l Insert into

17、 sales values(02, 28, 20);l Insert into sales values(03, 36, 33); DECODE 取出一行内两列中的较大值lselect month, decode(sign(sales_tv -sales_computer), 1, sales_tv, sales_computer) as 较大销售量 from sales; Oracle中的行列转换行列转换1lcreate table 销售(商品名称 varchar2(10), 季度 char(2), 销售额 number);linsert into 销售 values(电视机, 01, 10

18、0);linsert into 销售 values(电视机, 02, 200);linsert into 销售 values(电视机, 03, 300);linsert into 销售 values(空调, 01, 50);linsert into 销售 values(空调, 02, 150);linsert into 销售 values(空调, 03, 180);格式1：商品名称 季度 销售额- - -电视机 01 100电视机 02 200电视机 03 300空调 01 50空调 02 150空调 03 180格式2：商品名称 一季度 二季度 三季度 四季度- - - - -电视机 100

19、 200 300 0空调 50 150 180 0从格式1到格式2：select a.商品名称, sum(decode(a.季度,01, a.销售额 ,0 ) 一季度, sum(decode(a.季度,02, a.销售额 ,0 ) 二季度, sum(decode(a.季度,03, a.销售额 ,0 ) 三季度, sum(decode(a.季度,04, a.销售额 ,0 ) 四季度 from 销售 a group by a.商品名称 order by 1;ROWNUM 的使用创建yggz表 create table yggz ( bh number(6) , gz number );insert

20、 into yggz values(1,1000);insert into yggz values(2,1100);insert into yggz values(3,900);insert into yggz values(4,2000);insert into yggz values(5,1500);insert into yggz values(6,3000);insert into yggz values(7,1400);insert into yggz values(8,1200);ROWNUM使用l作用：对查询结果，输出前若干条记录l注意：只能与、=3 and rn = 5;l或者

21、：lselect * from yggz where rownum=5lminus lselect * from yggz where rownum=2;ROWNUM使用l任务：(2)按工资由高到底，查找表中，第3高的到第5高的记录，并显示出来。l 结果如下:l BH GZl - -l 5 1500l 7 1400l 8 1200ROWNUM使用l任务(2)答案：l select bh,gz from (l select a.*,rownum rn from(l select yggz.* from yggz order by gz desc ) a l )l where rn2;l或者：l

22、select * from (select * from yggzl order by gz desc) where rownum=5l minusl select * from (select * from yggzl order by gz desc) where rownum 1)l AND ROWID NOT INl (SELECT MIN(ROWID) FROM student GROUP BY sno HAVING COUNT(*) 1);删除重复记录 方法2lDELETE FROM student WHERE ROWID INl (SELECT A.ROWID FROM stud

23、ent A,student B l WHERE A.sno=B.sno AND A.ROWID B.ROWID);删除重复记录 方法3lDELETE FROM student d WHERE d.rowid l (SELECT MIN(x.rowid) FROM student x WHERE d.sno=x.sno);GROUP BY GROUPING SETS 的使用GROUP BY GROUPING SETS的使用l可以用GROUP BY GROUPING SETS来进行分组l自定义汇总，可以应用它来指定你需要的总数组l合。l其格式为： GROUP BY GROUPING SETS (l

24、ist), (list) . )l这里（list）是圆括号中的一个列序列，这个组合生l成一个总数。要增加一个总和，必须增加一个(NUlL)l分组集。 例如：对于scott.emp表，如果要查询：各部门sal大于2000的员工，进行汇总，得到各部门的sal总和、以及总共的sal总和。SELECT CASE WHEN a.deptno IS NULL THEN 合计 WHEN a.deptno IS NOT NULL AND a.empno IS NULL THEN 小计 ELSE | a.deptno END deptno, a.empno, a.ename, SUM(a.sal) total_sal FROM scott.emp a WHERE a.sal 2000 GROUP BY GROUPING SETS(a.deptno),(a.deptno, a.empno, a.ename),(); 结束结束