【Oracle11g】03_复杂的SQL语句

目录

1.SQL语句的执行顺序

  • 常见的select、from、where的顺序

  • 完整的select、from、where、group by、having、order by的顺序

1.1 案例分析：分析exists语句的SQL执行顺序

create table student(sno varchar2(10),sname varchar2(20));
insert into student values('1','张三');
insert into student values('2','李四');
insert into student values('3','王五');
insert into student values('4','赵云');
insert into student values('5','关羽');

create table address(sno varchar2(10),zz varchar(20));
insert into address values('1','昆明');
insert into address values('2','曲靖');
insert into address values('3','曲靖');
insert into address values('4','昭通');
insert into address values('5','昆明');
commit;

exists语句

select * from student s where exists (select 1 from address a where s.sno=a.sno and zz='昆明');

执行结果为：
SNO SNAME
---------- --------------------
1 张三
5 关羽

语句执行分析：
首先，执行from，找到需要查询的表student,然后逐行与where条件的子集进行匹配，如果匹配上，则返回true或者false，例如：当指针指向第一条记录sno=1,那么这条记录在子集address中也是存在的，所以返回true，此时与SQL语句：
select * from student where sno=1 and 1=1等效,接着逐行遍历完毕之后，则执行select子句，展示结果集。

2.子查询

查询出zz是昆明的学生中,sno最大的学生的名字

select * from student s where sno = (select max(sno) from address where zz='昆明');

输出结果：
SNO SNAME
----------\ --------------------
5 关羽

3.自连接

-- 建立测试数据
create table manager(id int,name varchar2(20),mgr int);
insert into manager values(1,'关羽',3);
insert into manager values(2,'张飞',3);
insert into manager values(3,'刘备',3);
insert into manager values(4,'曹操',4);
commit;

找出关羽,张飞的上级领导的名字

select a.id,a.name,b.name from manager a join manager b on a.mgr= b.id;

输出结果：

  • --------------------\ -------------
    3 刘备 刘备
    2 张飞 刘备
    1 关羽 刘备
    4 曹操 曹操

4.CASE WHEN 子句

语法：
CASE
WHEN 条件1 THEN action1
WHEN 条件1 THEN action2
WHEN 条件1 THEN action3
...
ELSE actionN
END

实战案例：

select case when substr('20190330',5,2)='01' then '一月'
			when substr('20190330',5,2)='02' then '二月'
			when substr('20190330',5,2)='03' then '三月'
			else '其他月份' 
       END
from dual;

5.复杂的更新语句

需求：
表T1里有 a,b,c...N个字段，表T2里有 a,b,c三个字段，
然后想在T1中"c"与表T2中"c"相同的情况下从表T2中将a,b覆盖表T1中的a,b怎么做 ？

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);

实现代码

update t1 set a=(select a from t2 where t2.c=t1.c) ,b=(select b from t2 where t2.c=t1.c)  where t1.c in (select c from t2);

update语句是逐行执行的，首先从第一行开始，先判断where条件是否成立，成立的话则根据 a=(select a from t2 where t2.c=t1.c) 将该行对应的t2表中的a取出赋值给t1表的a更新,b的更新类似。

6.分析函数

  • ROW_NUMBER:返回连续的排序，不论值是否相等
  • RANK:具有相等值的行排序相同，序数随后跳跃
  • DENSE_RANK:具有相等值的行排序相同，序号是连续的

建立测试表：

create table eInfo(id number(10),name varchar2(10),score int);
insert into eInfo values(1,'曹操',90);
insert into eInfo values(2,'刘备',85);
insert into eInfo values(3,'孙权',85);
insert into eInfo values(4,'赵云',80);
insert into eInfo values(5,'张飞',80);
commit;

6.1 ROW_NUMBER函数

SQL> select id,name,score,row_number() over(order by score desc) as seq from eInfo;
         ID NAME                                         SCORE        SEQ
----------- \---------- \--------------------------------------- \----------
          1 曹操                                            90          1
          2 刘备                                            85          2
          3 孙权                                            85          3
          4 赵云                                            80          4
          5 张飞                                            80          5

6.2 RANK函数

SQL> select id,name,score,rank() over(order by score desc) as seq from eInfo;
         ID NAME                                         SCORE        SEQ
-----------\ ---------- \---------------------------------------\ ----------
          1 曹操                                            90          1
          2 刘备                                            85          2
          3 孙权                                            85          2
          4 赵云                                            80          4
          5 张飞                                            80          4

6.3 DENSE_RANK函数

SQL> select id,name,score,dense_rank() over(order by score desc) as seq from eInfo;
         ID NAME                                         SCORE        SEQ
----------- \----------\ ---------------------------------------\ ----------
          1 曹操                                            90          1
          2 刘备                                            85          2
          3 孙权                                            85          2
          4 赵云                                            80          3
          5 张飞                                            80          3

7.DECODE函数

语法：
DECODE(value, if1, then1, if2,then2, if3,then3, . . . else )
Value 代表某个表的任何类型的任意列或一个通过计算所得的任何结果。当每个value值被测试，如果value的值为if1，Decode 函数的结果是then1；如果value等于if2，Decode函数结果是then2；等等。事实上，可以给出多个if/then 配对。如果value结果不等于给出的任何配对时，Decode 结果就返回else 。
需要注意的是，这里的if、then及else 都可以是函数或计算表达式。

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);

求较大销售数

SQL> select month, decode(sign(sales_tv -sales_computer), 1, sales_tv, sales_computer) as  较大销售量 from sales; 
MONTH      较大销售量
----- \----------
01            18
02            28
03            36

8.行列转换

create table salesInfo(pName varchar2(10), quarter char(2), amt number);
insert into salesInfo values('电视机', '01', 100);
insert into salesInfo values('电视机', '02', 200);
insert into salesInfo values('电视机', '03', 300);
insert into salesInfo values('空调', '01', 50);
insert into salesInfo values('空调', '02', 150);
insert into salesInfo values('空调', '03', 180);

-- 行转列
select a.pName,
     sum(decode(a.quarter,'01', a.amt ,0  ))  Q1,
     sum(decode(a.quarter,'02', a.amt ,0  ))  Q2,
     sum(decode(a.quarter,'03', a.amt ,0  ))  Q3,
     sum(decode(a.quarter,'04', a.amt ,0  ))  Q4
     from salesInfo a 
     group by a.pName 
     order by 1;

PNAME Q1 Q2 Q3 Q4
---------- ----------\ ---------- ---------- ----------
电视机 100 200 300 0
空调 50 150 180 0

使用create table salesInfo2 as select ... 命令将上述结果集转储到表salesInfo2 中。

-- 列转行

select pName,quarter,amt from salesInfo2 unpivot(amt for quarter in (q1,q2,q3,q4));

运行结果：
PNAME QUARTER AMT
----------\ -------\ ----------
电视机 Q1 100
电视机 Q2 200
电视机 Q3 300
电视机 Q4 0
空调 Q1 50
空调 Q2 150
空调 Q3 180
空调 Q4 0

9.ROWNUM函数

作用：对查询结果，输出前若干条记录
注意：只能与<、<=、between and连用(使用between and的时候需要从1开始，否则不生效，一般不推荐使用)

SQL> select * from dept;
DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON

SQL> select * from dept where rownum < 2;
DEPTNO DNAME          LOC
------ \--------------\ -------------
    10 ACCOUNTING     NEW YORK

SQL> select * from dept where rownum between 1 and 3;
DEPTNO DNAME          LOC
------\ --------------\ -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO

9.1 综合案例

显示emp表的第3行到第5行
第一种写法
``sql
select * from emp where rownum <= 5
minus
select * from emp where rownum < 3;


**第二种写法**
```sql
select * from (select e.*,rownum rn from emp e ) where rn>=3 and rn<=5;

10.删除重复记录

rowid包括的信息：数据对象号、数据文件号、数据块号、行号。根据rowid查询，查询速度最快。

-- 创建测试数据
create table student(sno number(6)  , sname varchar2(10), sage  int );  
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);
commit;

---- 第一种方法
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);

---- 第二种方法：
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);

11.GROUP BY GROUPING SETS

例如：对于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),());

输出结果：
DEPTNO EMPNO ENAME TOTAL_SAL
----------------------------------------\ -----\ ----------\ ----------
10 7782 CLARK 2450
10 7839 KING 5000
小计 7450
20 7566 JONES 2975
20 7788 SCOTT 3000
20 7902 FORD 3000
小计 8975
30 7698 BLAKE 2850
小计 2850
合计 19275

posted @ 2020-04-01 00:01  OLIVER_QIN  阅读(676)  评论(0)  编辑  收藏  举报