欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

oracle中复杂SQL语句——提升版

程序员文章站 2022-06-01 15:32:59
...

一、SQL语句的执行顺序

1、select 、 from 、where(group by、 having、 order by) 语句的执行顺序是什么呢?
select ——从纵向上进行过滤
from —— 一个表或多张表的连接
where —— 从横向上进行过滤

举个栗子:
“ select ID,Sname from student where score>=60;
– 从学生表中选择成绩大于等于60的学生的学号和姓名。
– 从某个表中在某个条件下选择某些列

2、完整的select语句呢?
同理 完整的是 from ->where-> group by -> having-> select ->order by

二、EXISTS的使用

exists用于判断查询所得的结果中是否有满足条件的记录存在。
“exists(xxx)”就表示括号里的语句能不能查出记录,它要查的记录是否存在。

举个栗子(巩固SQL执行顺序):

select * from student 
    where exists(select * from address where zz="兰州");
-- 如果exists中条件为真,那么就返回student表中所有的结果
-- 如果为假就什么也不返回

扩展:
①exist、 in 和 not exist的区别:

EXISTS:后面可以是整句的查询语句 如:SELECT * FROM titles
IN:后面只能是对单列 如: SELECT pub_id FROM titles
NOT EXISTS: 后面可以是整句的查询语句对EXISTS的否定。即就是不存在的返回真,存在的返回假。
  
② 有两个简单例子,以说明 “exists”和“in”的效率问题

1) select * from T1 where exists(select 1 from T2 where T1.a=T2.a) ;

 T1数据量小而T2数据量非常大时,T1<<T2 时,1) 的查询效率高。

2) select * from T1 where T1.a in (select T2.a from T2) ;

 T1数据量非常大而T2数据量小时,T1>>T2 时,2) 的查询效率高。

      通过使用EXISTS,Oracle会首先检查主查询,然后运行子查询直到它找到第一个匹配项,这就节省了时间。Oracle在执行IN子查询时,首先执行子查询,并将获得的结果列表存放在一个加了索引的临时表中。在执行子查询之前,系统先将主查询挂起,待子查询执行完毕,存放在临时表中以后再执行主查询。这也就是使用EXISTS比使用IN通常查询速度快的原因。

三、复杂sql语句思维培养

1、如下两张表:
oracle中复杂SQL语句——提升版
question:将住址在郑州的学生中学号最大的那一个学生的姓名取出来。 
step 1: 主:在student表中选取一个姓名。从:家住郑州且学号最大
step 2: 确定两张表的连接条件 sno

select sname from student
  where sno=(select max(sno) from address where zz='郑州');
  • 聚合函数只能放在select后面
    2、如下一张表:
    oracle中复杂SQL语句——提升版
    question :找出姓名不唯一的学生的所有记录。
    step1: select XM,count(*) from student group by XM;
    step 2 : select XM from student group by XM having (count(*)>1);
    step3 : select * from student where XM in (select XM from student group by XM having (count(*)>1) );

四、自连接的使用:

1、有如下一张表
oracle中复杂SQL语句——提升版
question: 将这张表对应的管人人员编号替换成相应的管理人员姓名
step 1:select * from 管理人员 a ,管理人员 b;
                – 即就是两张表的笛卡尔积
oracle中复杂SQL语句——提升版
step 2:select * from 管理人员 a ,管理人员 b where a.管理人员编号=b.编号;
step 3:select a.编号 ,a. 姓名,b.姓名 as 管理人员姓名 from 管理人员 a ,管理人员 b where a.管理人员编号=b.编号;

select  a.编号 ,a. 姓名,b.姓名 as 管理人员姓名 
from 管理人员 a ,管理人员 b  where a.管理人员编号=b.编号;
  • as 可以用于取别名,也可省略不写 如:b.姓名 管理人员姓名

五、 select case when的用法

1、 判断条件在when中
oracle中复杂SQL语句——提升版

select  case 
      when  substr('20180625',5,2) = '04'  then  '四月份'
      when  substr('20180625',5,2) = '05'  then  '五月份'
      when  substr('20180625',5,2) = '06'  then  '六月份'
      when  substr('20180625',5,2) = '07'  then  '七月份'
      else null
    end 
from dual;

2、 case后有计算式
oracle中复杂SQL语句——提升版

select  case substr('20180625',5,2)
      when  '04'  then  '四月份'
      when  '05'  then  '五月份'
      when  '06'  then  '六月份'
      when '07'  then  '七月份'
      else null
    end 
from dual;

五、复杂更新语句的使用

1、有如下两张表:
oracle中复杂SQL语句——提升版
question : 想在T1中”c”与表T2中”c”相同的情况下从表T2中将a,b覆盖表T1中的a,b怎么做 ?
step 1 :update t1 set a ,b
step 2 :update t1 set a ,b where t1.c in (select c from t2);
step 3 :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);

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

六、分析函数的使用

分析函数用于计算完成聚集的累计排名、序号等, 分析函数为每组记录返回多个行。

ROW_NUMBER ()over(条件)返回连续的排序,不论值是否相等
RANK()over(条件) 具有相等值的行排序相同,序数随后跳跃
DENSE_RANK ()over(条件)具有相等值的行排序相同,序号是连续的

举个栗子: 有一组成绩 90 80 70 70 60 60
使用row_number() 1 2 3 4 5 6
使用rank() 1 2 3 3 5 5
使用dense_rank() 1 2 3 3 4 4

七、decode中的if-then-else逻辑

在逻辑编程中,经常用到If – Then –Else 进行逻辑判断。在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 都可以是函数或计算表达式。
1、有下面一张表:
question : 将student表中的sex列的1 2 1 转化为 男 女 男 的显示方式。
oracle中复杂SQL语句——提升版
除此之外还可以使用:

Select name ,decode(sex, '1','男', '女') from student;
-- 此时的女相当于在else条件下的女
-- 用case实现:

select id,name,
     case sex
      when '1' then '男'
      when '2' then '女'
     end 性别
from student;

2、有如下一张表:
oracle中复杂SQL语句——提升版

question : 返回当月销售数量最大的那个数
step 1 : sign()函数介绍,如果()中结果为正,返回1 ,如果()中结果为负,返回-1,如果()中结果为0,返回0
step 2 : select * from sales;

select month ,
decode(sign(SALES_TV- SALES_COMPUTER),
            1,SALES_TV,
            -1,SALES_COMPUTER,
            0,SALES_TV) 
from sales;

结果:
oracle中复杂SQL语句——提升版

八、oracle中的行列转化

1、将表格式一显示成格式二:
oracle中复杂SQL语句——提升版

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;
  • 说明 : 在a表中,先按商品名称进行分组,故sum函数中取到的01季度的只能是第一组电视机中的一季度的销售额。
     

九、rownum()的使用:

1、有如下一张表
oracle中复杂SQL语句——提升版

  • rownum() 只能与 < <= 以及特殊情况下的between and 连用。
  • between 必须从1 开始!!
select * from yggz where rownum()<=3
select * from yggz where between 1 and 3;

question : 查找表中第3~5的记录并显示出来

-- 集合相减法
select * from yggz where rownum<=5
minus 
select * from yggz where rownum<=2;

另一种方式: step 1:
oracle中复杂SQL语句——提升版


--嵌套法
select bh,gz 
from ( select yggz.*, rownum rn from yggz     )
where rn >=3 and rn <= 5;
  • 将rownum 作为一列 传入到表yggz中,然后在编辑条件rn >=3 and rn <= 5

十 、 删除重复记录

1、有如下一张表:
oracle中复杂SQL语句——提升版
question:将其中的sno重复的行删除。

  • rowid()rownum()均为伪列,实际上是存在在表中的,可以使用
  • select student . *,rowid from student;查看到
    oracle中复杂SQL语句——提升版
  • rowid() 里面的信息有 数据库对象号 数据文件号 数据块号 行号。
-- 删除学号有重复的 rowid不是最小的。
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);

自连接删除法

--  A、B两表先进行内连接。
--  取出内连接后两表中学号相等 且A.ROWID > B.ROWID的那些行的ROWID
--  将rowid等于取出的这些ROWID中的行进行删除操作
DELETE FROM student WHERE ROWID IN
    (SELECT A.ROWID FROM student A,student B 
     WHERE A.sno=B.sno AND A.ROWID > B.ROWID);

嵌套查询删除法

--  我要删除一些记录
--  删除条件是 rowid > 取出学号相等的那些行的rowid中的最小rowid
--  换句话说就是在学号相等的条件下,rowid最小的是被保留的 
DELETE FROM student d WHERE d.rowid > 
    (SELECT MIN(x.rowid) FROM student x WHERE d.sno=x.sno);

十一、group by grouping sets的使用

      可以用GROUP BY GROUPING SETS来进行分组自定义汇总,可以应用它来指定你需要的总数组合。
      其格式为: GROUP BY GROUPING SETS ((list), (list) … )
      这里(list)是圆括号中的一个列序列,这个组合生成一个总数。要增加一个总和,必须增加一个(NULL)分组集。
GROUPING SETS 子句是 SELECT 语句的 GROUP BY 子句的扩展。通过 GROUPING SETS 子句,您可采用多种方式对结果分组,而不必使用多个 SELECT 语句来实现这一目的。这就意味着,能够减少响应时间并提高性能。

使用select语句进行多个分组:

SELECT NULL, NULL, NULL, COUNT( * ) AS Cnt
FROM Customers
WHERE State IN ( 'MB' , 'KS' )
   UNION ALL
SELECT City, State, NULL, COUNT( * ) AS Cnt
FROM Customers
WHERE State IN ( 'MB' , 'KS' )
GROUP BY City, State
   UNION ALL
SELECT NULL, NULL, CompanyName, COUNT( * ) AS Cnt
FROM Customers
WHERE State IN ( 'MB' , 'KS' )
GROUP BY CompanyName;

使用 GROUPING SETS 的多个分组:

SELECT City, State, CompanyName, COUNT( * ) AS Cnt
FROM Customers
WHERE State IN ( 'MB' , 'KS' )
GROUP BY GROUPING SETS( ( City, State ), ( CompanyName ) , ( ) );

                                                                        oracle中复杂SQL语句——提升版

相关标签: 复杂SQL