拓扑园

  • O&M
    • Universal部署
    • PHP+VUE+Laravel相关
  • Oracle性能优化
  • Oracle项目案例
    • Oracle近期项目案例(目录)
    • Oracle实战问题解析(目录)
    • Oracle数据库名变更流程(2种方式)
    • Oracle数据库目录更换流程(使用Oracle的clone工具)
    • Oracle数据库迁移方案(目录)
    • 标准化文档系列
  • Oracle基础知识
    • LLL的Oracle培训(分类)
    • LLL的docker培训(分类)
    • 标准化文档系列--(分类)
    • Oracle核心经典分析(分类)
    • 图灵小队----(分类并包含以下文章)
    • --MySQL8.0/Oracle/Memcached/Redis等安装配置于RHEL/OL6/7/8.X系列-运行环境最优配置
    • --PG安装配置于RHEL/9X系列-运行环境最优配置
    • --自动维护任务详解-开启、关闭信息统计收集(统计信息)
    • --图灵小队—Oracle/PostgreSQL下创建一个用户测试表(自行定义数据行)
    • --图灵小队-Oracle存储过程导出表的明细_UTL_FILE(文章)
    • --图灵小队-Oracle数据库删除/卸载操作指南(文章)
    • --图灵小队-Oracle常用性能查询SQL语句(文章)
    • --图灵小队-Oracle数据库上线前检查(文章)
    • --图灵小队-Oracle常用SQL语句(文章)
    • --图灵小队—Linux/Oracle脚本/MySQL合集(持续更新)
    • --图灵小队-Oracle技巧记录(文章)
    • ADG
    • RAC
    • ASM
    • OGG
    • RMAN
    • EXPDP/IMPDP
    • 工厂数据导入导出系列
  • MySQL
    • MySQL数据库规范
    • MySQL项目案例
    • MySQL安装配置
    • MYSQL集群项目
    • MySQL常见处理
    • MySQL-Sysbench专题
    • MySQL-Percona Toolkit专题
  • Linux
    • Shell编程
    • kubernetes
    • docker
    • Linux
    • PHP
    • Nginx
    • haproxy
    • mail
    • 网站
    • 域名
    • 网址收藏
  • 数据中心
    • 新框架系统集合
    • 工作文档
    • EBS数据文件扩容
    • VMware虚拟化
    • EBS系列
    • 大数据
    • SVN
    • zabbix
    • SAP
    • 备份相关
    • FC交换机
    • SVN
  • K-Studing
    • D8-Python学习
    • Oracle/MySQl等面试题
    • LG-MySQL
    • LG-Docker/K8S
    • LG-PostgreSQL
    • LG-ORACLE_BBED
    • LG-ORACLE
    • LG-Elasticsearch(ES)+ELK
    • Oracle-19C-OCP
    • WERN_ORACLE培训
    • redis数据库
    • Nginx培训学习系列
  • 其他
    • 外研英语4年级下册-听力
    • 影视系列
    • 如何使用iTunes软件通过抓包下载旧版本的ios的app
天高任鸟飞
Oracle/MySQL数据库恢复/数据迁移/生产规范报告技术交流:TEL:18562510581(微信同号);加微信入群
  1. 首页
  2. 技术学习系列
  3. WERN_ORACLE培训
  4. 正文

第02-2部分 oracle11g的复杂SQL语句

2020年2月26日 640点热度 0人点赞 0条评论

一、sql语句的执行顺序

       from——where——group by——having——select——order by

          group by的使用方法:select 组名,聚合函数(count、max、min、sum、avg等) from tables group by 组名:

image.png

二、EXISTS的使用(返回true或false)

    SQL> select * from student where exists(select * from address where zz='zhengzhou');    

    1、先执行from

    2、再执行where条件

        a、exists中的内容是否有值

        b、如果有值,返回student所有值,如果没有值,则返回空

image.png

image.png

三、新表做测试

 1、创建新表(select结果作为条件)

        (1)

        create table student(sno number(6) ,birthday date, sname varchar2(12));

        insert into student values(1,'11-JAN-81','zhangsan');

        insert into student values(2,'10-MAR-82','lisi');

        insert into student values(3,'06-JAN-83','wangwu');

        insert into student values(4,'26-JAN-83','zhaoliu');

        (2)

        create table address(sno number(6) , zz varchar2(12));

        insert into address values(1,   'zhengzhou');

        insert into address values(2,   'kaifeng');

        insert into address values(3,   'luoyang');

        insert into address values(4,   'zhengzhou');

        (3)题目:找出zz是郑州的学生中,sno最大的学生的sname。

        SQL> select sname from student where sno=(select max(sno) from address where zz='zhengzhou');

image.png

2、创建新表2——group by 练习

     create table student2(xh number,xm varchar2(10),nl int);

     insert into student2 values (1,'A',21);  

      insert into student2 values (2,'B',22);

      insert into student2 values (3,'A',23);  

      insert into student2 values (4,'A',24);

      insert into student2 values (5,'A',25);  

      insert into student2 values (6,'C',26);

      insert into student2 values (7,'B',27);

任务:查找xm有相同的纪录,并显示出来,如下:

        XH XM                 NL

---------- ---------- ----------

         1 A                  21

         3 A                  23

         4 A                  24

         5 A                  25

         2 B                  22

         7 B                  27

    (1)SQL>select count(*) from student2;

image.png

    (2)SQL>select xm,count(*) from student2 group by xm;

image.png

    (3)SQL>select xm,count(*) from student2 group by xm having(count(*));

image.png

    (4)SQL> select * from student2 where xm in(select xm  from student2 group by xm having(count(*)>1));

image.png

3、创建新表3——自连接的使用

        CREATE TABLE  Manager  (

          ID  char(10) ,

          name  varchar2(10) ,

          managerID  char(10)

        );

        insert into Manager values('001', 'zhangyi', '004');

        insert into Manager values('002', 'zhanger', '004');

        insert into Manager values('003', 'zhangsan', '003');

        insert into Manager values('004', 'zhangsi', '004');

image.png

    要求:显示ID、name、manager的name.

        ID         NAME       MANAGER的name

        ---------- ---------- ----------

        001        zhangyi    zhangsi 

        002        zhanger    zhangsi 

        003        zhangsan  zhangsan 

        004        zhangsi    zhangsi 

    (1)查看表的自连接:SQL> select * from manager a,manager b;

image.png

    (2)查看a.managerID的编号和b.ID一致的那一行,即为满足条件

SQL> select * from manager a,manager b where a.managerID=b.ID;

image.png

    (3)过滤上述的表 并按照a.ID排序

    SQL> select a.ID,a.NAME,b.NAME mangagerName from manager a,manager b where a.managerID=b.ID;

    SQL> select a.ID,a.NAME,b.NAME mangagerName from manager a,manager b where a.managerID=b.ID order by a.ID asc;

1582870844289757.png

4、select case when使用

       方法一:算式在条件中

       SELECT  CASE 

           WHEN   条件1   THEN   action1

           WHEN   条件2   THEN   action2

           WHEN   条件3   THEN   action3

           …..

           ELSE actionN 

        END CASE   

image.png

        方法二:算式在case 后,结果在条件中

           SELECT CASE selector

               WHEN value1 THEN action1

               WHEN value2 THEN action2

                  WHEN value3 THEN action3

               …..

               ELSE actionN

            END [CASE]

image.png

    (1)创建表: 

create table transcript3(sno number, km varchar2(10), score number,grade char(6));     
insert into  transcript3 values(1, 'Chinese', 65,null);
insert into  transcript3 values(2, 'Math', 76,null);
insert into  transcript3 values(3, 'English', 86,null);
insert into  transcript3 values(4, 'Chinese', 94,null);

    (2)实现grade更新为优秀、良好、中等、及格、不及格等方式

update transcript3 set grade=(
    select grade from
	(select 
	sno,
	CASE
 	    WHEN SCORE>=90 THEN 'youxiu'
 	    WHEN SCORE>=80 and SCORE<90THEN 'lianghao'
 	    WHEN SCORE>=70 and SCORE<80 THEN 'zhongdeng'
 	    WHEN SCORE>=60 THEN 'jige'
 	    ELSE 'bujige'
	END  grade 
	From transcript3
	) a 
	where transcript3.sno=a.sno);

   image.png


四、复杂更新语句的使用

    1、创建表

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

2、实现语句:

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、RANK、DENSE_RANK)

    以下三个分析函数用于计算一个行在一组有序行中的排位,序号从1开始

    ROW_NUMBER 返回连续的排序,不论值是否相等

    RANK 具有相等值的行排序相同,序数随后跳跃

    DENSE_RANK 具有相等值的行排序相同,序号是连续的

1、创建表 transcrpit;

create table transcript(sno number,km varchar2(15),score number);
insert into transcript valudes(1,'Chinese',60);
insert into transcript valudes(1,'Math',60);
insert into transcript valudes(2,'English',60);
insert into transcript valudes(2,'Math',70);
insert into transcript valudes(3,'Chinese',80);

2、用法:

(1)row_number

SQL> select sno,km,score,row_number() over(order by score desc) row_number from transcript;

image.png

(2)rank

SQL> select sno,km,score,rank() over(order by score desc) row_number from transcript;

image.png

(3)dense_rank

SQL> select sno,km,score,dense_rank() over(order by score desc) row_number from transcript;

image.png

六、DECODE函数

1、在逻辑编程中,经常用到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 都可以是函数或计算表达式。

2、创建表

Create table student5(id number,name varchar2(10),sex char(1));
Insert into student5 values(1, 'zhang', '1');
Insert into student5 values(2,  'wang', '2');
Insert into student5 values(3, 'li', '1');

image.png

3、SQL语句

SQL> select id,name,decode(sex,1,'male',2,'female')from student5;

image.png

4、创建表

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

    

image.png

得到每个行中,两列值中,TV和computer中的较大值,通过decode配合两数相减sign()的值来判定,谁的值大,谁的值小

SQL> select month,decode(sign(sales_tv-sales_computer),1,sales_tv,0,sales_tv,-1,sales_computer) as maxiser from sales;

1582961239931902.png

七、oracle的行列转换

1、创建表

create table sales2(product varchar2(10), jidu char(2), sale number);
insert into sales2 values('TV', '01', 100);
insert into sales2 values('TV', '02', 200);
insert into sales2 values('TV', '03', 300);
insert into sales2 values('kongtiao', '01', 50);
insert into sales2 values('kongtiao', '02', 150);
insert into sales2 values('kongtiao', '03', 180);

2、sql语句

select a.product,
sum(decode(a.jidu,'01',a.sale,0)) jidu1,
sum(decode(a.jidu,'02',a.sale,0)) jidu2,
sum(decode(a.jidu,'03',a.sale,0)) jidu3,
sum(decode(a.jidu,'04',a.sale,0)) jidu4
  from sales2 a
  group by a.product;

行列转换:

image.png

八、ROWNUM使用

1、创建表

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

2、rownum 作用与注意事项(与sqlserver中的top类似,oracle中没有top使用)

作用:对查询结果,输出前若干条记录

注意:只能与<、<=、between and(且between中开始必须是1)连用

image.png

image.png

输出:第3-5行

(1)用minus

select * from yggz where rownum<=5 
minus 
select * from yggz where rownum<=2;

 (2)用rownum直接取出后,嵌套,获取第3行到第5行数据

SQL> select *  from (select yggz.*,rownum m from yggz) where m<=5 and m>=3;

image.png

image.png

嵌套子查询:select * from (select * from yggz); 或select a.* from (select * from yggz) a; 后面这句在SqlServer中也这样用,但第一句在SqlServer中不能使用

按照工资从高到低排序后,输出第3到第5行

(1)用minus

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;

image.png

(2)select * from (select a.*,rownum rn from (select *  from yggz order by gz desc) a) where rn<=5 and rn >=3;

image.png

九、删除重复记录(非完全一致,或完全一致),ROWID

1、创建表

create table student6(sno number(6)  , sname varchar2(10), sage  int );  
insert into student6 values(1, 'AA', 21);
insert into student6 values(2, 'BB', 22);
insert into student6 values(3, 'CC', 23);
insert into student6 values(3, 'CC', 34);
insert into student6 values(3, 'CC', 35);
insert into student6 values(3, 'CC', 36);

2、rowid

rowid的信息有:数据库对象号、数据文件号、数据块号、行号

image.png

通过rowid查询

image.png

第一种方式:删除sno相同的行,即重复的行,通过rowID,group分组

SQL>select * from student6 where 
sno in(select sno from student6 group by sno having(count(*)>1)) 
and 
rowid not in(select min(rowid) from student6 group by sno having(count(*)>1));

image.png

image.png

第二种方式:自连接方式,笛卡尔

DELETE FROM student6 WHERE ROWID IN
    (SELECT A.ROWID FROM student6 A,student6 B 
     WHERE A.sno=B.sno AND A.ROWID > B.ROWID);

image.png

第三种方式:嵌套查询,

image.png

image.png

十、GROUP BY GROUPING SETS的使用

可以用GROUP BY GROUPING SETS来进行分组自定义汇总,可以应用它来指定你需要的总数组合。

其格式为: GROUP BY GROUPING SETS ((list), (list) ... )

这里(list)是圆括号中的一个列序列,这个组合生成一个总数。要增加一个总和,必须增加一个(NUlL)分组集。 

对于scott.emp表,如果要查询:各部门sal大于2000的员工,进行汇总,得到各部门的sal总和、以及总共的sal总和。

SELECT CASE       
 WHEN a.deptno IS NULL THEN
    'heji'
 WHEN a.deptno IS NOT NULL AND a.empno IS NULL THEN
    'xiaoji'
 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),());

image.png

本作品采用 知识共享署名 4.0 国际许可协议 进行许可
标签: 暂无
最后更新:2020年3月1日

admin

这个人很懒,什么都没留下

打赏 点赞
< 上一篇
下一篇 >

文章评论

您需要 登录 之后才可以评论

COPYRIGHT © 2022 拓扑园. ALL RIGHTS RESERVED.

Theme Kratos Made By Seaton Jiang

鲁ICP备2021020523号

鲁ICP备2021020523号