oracle系列教程三oracle复杂SQL语句

《oracle系列教程三oracle复杂SQL语句》由会员分享，可在线阅读，更多相关《oracle系列教程三oracle复杂SQL语句（6页珍藏版）》请在装配图网上搜索。

1、文档供参考，可复制、编制，期待您的好评与关注！ oracle复杂SQL语句一、sql语句的执行顺序1)、常见的select、from、where的顺序：1, from 2, where 3, select2)、完整的select、from、where、group by、having、order by的顺序：1, from 2, where 3, group by 4,having 5, select 6, order by二、select case when的的使用1)、语法：case when 条件1 then action1 when 条件2 then action2 when 条件3 t

2、hen action3 . else actionnend caseeg、select case when substr(to_char(SYSDATE, yyyymmdd), 5 ,2) = 01 then 一月份 when substr(to_char(SYSDATE, yyyymmdd), 5 ,2) = 02 then 二月份 when substr(to_char(SYSDATE, yyyymmdd), 5 ,2) = 03 then 三月份 when substr(to_char(SYSDATE, yyyymmdd), 5 ,2) = 04 then 四月份 else other

3、end casefrom dual;2)、语法：语法：case selector when value1 then action1 when value2 then action2 when value3 then action3 . else action nend caseeg、SELECT CASE substr(to_char(SYSDATE, yyyymmdd), 5 ,2) WHEN 01 THEN 一月份 WHEN 02 THEN 二月份 WHEN 03 THEN 三月份 WHEN 04 THEN 四月份 ELSE other END CASEFROM DUAL;3)、案例cre

4、ate table 成绩(sno number, km varchar2(10),score number,grade char(6); insert into 成绩 values(1, 语文, 65,null);insert into 成绩 values(2, 数学, 76,null);insert into 成绩 values(3, 英语, 86,null);insert into 成绩 values(4, 语文, 94,null);i、SELECT SNO, CASE WHEN SCORE = 90 THEN 优秀 WHEN SCORE = 80 THEN 良好 WHEN SCORE =

5、 70 THEN 中等 WHEN SCORE = 60 THEN 及格 ELSE 不及格 END GRADE GRADE别名，END后面不能包含CASE关键字，否则报错 FROM 成绩查询结果如下：ii、把每个学生的grade列，用相应的等级来更新。- UPDATE 成绩 set grade = (SELECT grade FROM(xxxxxx) a where 成绩.sno = a.sno);update 成绩 set grade = ( select grade FROM( SELECT SNO, CASE WHEN SCORE = 90 THEN 优秀 WHEN SCORE = 80

6、THEN 良好 WHEN SCORE = 70 THEN 中等 WHEN SCORE = 60 THEN 及格 ELSE 不及格 END GRADE FROM 成绩 ) awhere 成绩.sno = a.sno);三、复杂更新语句的使用表T1里有a,b,c.N个字段，表T2里有a,b,c三个字段，然后想在T1中c与表T2中c相同的情况下从表T2中将a,b覆盖表T1中的a,b怎么做？i、create table T1(a int ,b int ,c int ,d int ,e int);create table T2(a int ,b int ,c int );insert into T1 v

7、alues(1,2,3,4,5);insert into T1 values(10,20,3,4,5);insert into T1 values(10,20,4, 40,50);insert into T2 values( -1, -1 , 3);insert into T2 values( -2, -2, 4);ii、UPDATE 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);四、DECODE函

8、数的使用i、select decode(x, 1, x is 1, 2, x is 2, others) from dual 当x等于1时，则返回x is 1。 当x等于2时，则返回x is 2。 否则，返回others。eg、create table student(id number,name varchar2(10),sex char(1);Insert into student values(1, 张, 1);Insert into student values(2, 王, 2);Insert into student values(3, 李, 1);-方法一select name ,

9、decode(sex, 1,男生, 2,女生) 性别FROM student;-方法二 用case实现SELECT NAME, CASE SEX WHEN 1 THEN 男 WHEN 2 THEN 女 END 性别 FROM STUDENT;ii、DECODE取出一行内两列中的较大值create table sales(month char(2),sales_tv number,sales_computer number);Insert into sales values(01, 10, 18);Insert into sales values(02, 28, 20);Insert into

10、sales values(03, 36, 33);-sign(number): 取数字n的符号,大于0返回1,小于0返回-1,等于0返回0 select month, decode(sign(sales_tv-sales_computer), 1, sales_tv, sales_computer) as 较大销售量 from sales;五、Oracle中的行列转换create table 销售(商品名称 varchar2(10), 季度 char(2), 销售额 number);insert into 销售 values(电视机, 01, 100);insert into 销售 value

11、s(电视机, 02, 200);insert into 销售 values(电视机, 03, 300);insert into 销售 values(空调, 01, 50);insert into 销售 values(空调, 02, 150);insert into 销售 values(空调, 03, 180);从格式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

12、.季度,04, a.销售额 ,0 ) 四季度 from 销售 a group by a.商品名称 order by 1;六、ROWNUM 的使用-创建yggz表create table yggz (bh number(6),gz number);insert 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 v

13、alues(6,3000);insert into yggz values(7,1400);insert into yggz values(8,1200);作用：对查询结果，输出前若干条记录注意：只能与、= 3 AND RN = 5;-方法二select * from yggz where rownum=5minus select * from yggz where rownum=2;ii、按工资由高到底，查找表中，第3高的到第5高的记录，并显示出来。显示结果如下：-方法一SELECT BH, GZ FROM (SELECT A.*, ROWNUM RN FROM (SELECT YGGZ.*

14、 FROM YGGZ ORDER BY GZ DESC) A)WHERE RN 2;-方法二select * from (select * from yggz order by gz desc) where rownum=5minusselect * from (select * from yggz order by gz desc) where rownum 1) AND ROWID NOT IN-挑选学号不是最小的记录 (SELECT MIN(ROWID) FROM STUDENT GROUP BY SNO HAVING COUNT(*) 1);-方法二（不推荐，如果记录超过999条要慎重，in用法）DELETE FROM STUDENT WHERE ROWID IN (SELECT A.ROWID FROM STUDENT A, STUDENT B WHERE A.SNO = B.SNO AND A.ROWID B.ROWID);-方法三（推荐）DELETE FROM STUDENT D WHERE D.ROWID (SELECT MIN(X.ROWID) FROM STUDENT X WHERE D.SNO = X.SNO);备注：rowid的信息包含有数据库对象号、数据文件号、数据块号、行号，唯一的、查询速度最快滴。6 / 6