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

oracle复杂SQL语句 一、sql语句的执行顺序 1)、常见的select、from、where的顺序：1, from 2, where 3, select 2)、完整的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 then action3 ... else actionn end [case] eg、 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' end case from dual; 2)、语法： 语法： case selector when value1 then action1 when value2 then action2 when value3 then action3 ... else action n end [case] eg、 SELECT CASE substr(to_char(SYSDATE, 'yyyymmdd'), 5 ,2) WHEN '01' THEN '一月份' WHEN '02' THEN '二月份' WHEN '03' THEN '三月份' WHEN '04' THEN '四月份' ELSE 'other' END CASE FROM DUAL; 3)、案例 create 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 >= 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 THEN '良好' WHEN SCORE >= 70 THEN '中等' WHEN SCORE >= 60 THEN '及格' ELSE '不及格' END GRADE FROM 成绩 ) a where 成绩.sno = a.sno); 三、复杂更新语句的使用 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 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 values(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函数的使用 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 ,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 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 销售 values('电视机', '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.季度,'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 values(6,3000); insert into yggz values(7,1400); insert into yggz values(8,1200); 作用：对查询结果，输出前若干条 记录 混凝土 养护记录下载土方回填监理旁站记录免费下载集备记录下载集备记录下载集备记录下载 注意：只能与<、<=、between and连用。 i、 查找表中，第3条到第5条记录，并显示出来。 结果如下: --方法一 SELECT BH, GZ FROM (SELECT YGGZ.*, ROWNUM RN FROM YGGZ) WHERE RN >= 3 AND RN <= 5; --方法二 select * from yggz where rownum<=5 minus select * from yggz where rownum<=2; ii、按工资由高到底，查找表中，第3高的到第5高的记录，并显示出来。 显示结果如下： --方法一 SELECT BH, GZ FROM (SELECT A.*, ROWNUM RN FROM (SELECT YGGZ.* FROM YGGZ ORDER BY GZ DESC) A) WHERE RN <= 5 AND RN > 2; --方法二 select * from (select * from yggz order by gz desc) where rownum<=5 minus select * from (select * from yggz order by gz desc) where rownum<=2; 七、删除重复记录 CREATE table student(sno number(6), sname varchar2(10), sage number(6)); insert into student values(1, 'AA', 21); insert into student values(2, 'BB', 22); insert into student values(3, 'CC', 23); insert into student values(3, 'CC', 34); insert into student values(3, 'CC', 35); insert into student values(3, 'CC', 36); --方法一(不推荐，如果记录超过999条要慎重，in用法) DELETE FROM STUDENT --挑选学号重复的记录 WHERE SNO IN (SELECT SNO FROM STUDENT GROUP BY SNO HAVING COUNT(*) > 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的信息包含有数据库对象号、数据文件号、数据块号、行号，唯一的、查询速度最快滴。