Three, complex SQL statements

Three, complex SQL statements

  • Master the execution order of SQL statements, and be able to analyze the execution process of complex SQL statements
  • Master Oracle sub-query, self-join, and rank conversion
  • Master the use of analysis functions, decode functions, and SELECT CASE WHEN
  • Master the method of Oracle paging and deleting duplicate records

SQL statement execution order

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

1, from  2, where  3, select


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

1, from  2, where  3, group by  4,having  5, select  6, order by

Use of EXISTS

EXISTS is used to determine whether there are records that meet the conditions in the results of the query.

select * from student 
   where    exists(select * from address
                    where zz='郑州');

​ Analyze the execution order of select, from, and where.

Insert picture description here

Use of subqueries

create table student(sno number(6) ,birthday date, sname varchar2(10));
insert into student values(1, '11-1月-81' , '张三');
insert into student values(2, '10-3月-82' , '李四');
insert into student values(3, '06-1月-83' , '王五');
insert into student values(4, '26-1月-83' , '赵六');


create table address(sno number(6) , zz varchar2(10));
insert into address values(1,   '郑州');
insert into address values(2,   '开封');
insert into address values(3,   '洛阳');
insert into address values(4,   '郑州');

Requirements: Find out zz is the sname of the student with the largest sno among Zhengzhou students

select sname      
  from student     
 where sno =(        
 select max(sno)              
 from address                
where zz= '郑州' );  

Use of group by

1.创建student表
  create table student(xh number,xm varchar2(10),nl int);
   insert into student values (1,'A',21);  
  insert into student values (2,'B',22);
  insert into student values (3,'A',23);  
  insert into student values (4,'A',24);
  insert into student values (5,'A',25);  
  insert into student values (6,'C',26);
  insert into student values (7,'B',27);

Task: Find the same records in xm and display them, as follows

Insert picture description here
答案:select * from student where xm in (select xm from student group by xm having count(*)>1);

Use of self-connection

CREATE TABLE  管理人员  (
  编号  char(10) ,
  姓名  varchar2(10) ,
  管理人员编号  char(10)
);

insert into 管理人员 values('001', '张一', '004');
insert into 管理人员 values('002', '张二', '004');
insert into 管理人员 values('003', '张三', '003');
insert into 管理人员 values('004', '张四', '004');

Requirement: Now I want to display: number, name, manager's name

select a.编号,a.姓名,b.姓名 as 管理人员姓名    
from 管理人员 a   join 管理人员 b on a.管理人员编号=b.编号;

SELECT CASE WHEN

语法:
CASE    WHEN   条件1   THEN   action1   
		WHEN   条件2   THEN   action2
        WHEN   条件3   THEN   action3
        …..   
        ELSE actionN 
        
        END CASE   

select  case 
      when  substr('20090310',5,2) = '01'  then  '一月份'
      when  substr('20090310',5,2) = '02'  then  '二月份'
      when  substr('20090310',5,2) = '03'  then  '三月份'
      when  substr('20090310',5,2) = '04'  then  '四月份'
      else null
    end 
from dual;
语法:
CASE selector   
		WHEN value1 THEN action1
        WHEN value2 THEN action2
      	WHEN value3 THEN action3
        …..
        
        ELSE actionN
        END [CASE]

select  case substr('20090310',5,2) 
      when   '01'  then  '一月份'
      when    '02'  then  '二月份'
      when   '03'  then  '三月份'
      when    '04'  then  '四月份'
      else null
    end 
from dual;

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

要求:把每个学生的grade列,用相应的等级来更新。


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


Use of complex update statements

表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 t1.c= t2.c ) ,  
			  b =(select b from t2 where t1.c= t2.c)  
where  t1.c  in (select c from t2);

Use of analysis functions

The analysis function is used to calculate the cumulative ranking, serial number, etc. of the completed aggregation

Analysis function returns multiple rows for each set of records

The following three analysis functions are used to calculate the rank of a row in a group of ordered rows, and the sequence number starts from 1.

  • ROW_NUMBER returns a sequential order, regardless of whether the values ​​are equal
Insert picture description here
Insert picture description here
  • RANK The rows with equal values ​​are sorted the same, and the ordinal number then jumps
Insert picture description here
  • DENSE_RANK rows with equal values ​​are sorted the same, and the sequence numbers are consecutive
Insert picture description here

Use of DECODE

If-then-else logic in DECODE

  • In logic programming, If – Then – Else is often used to make logic judgments. In the grammar of DECODE, this is actually such a logical process. Its syntax is as follows:
  • DECODE(value, if1, then1, if2,then2, if3,then3,... Else)
  • Value represents any type of any column of a table or any result obtained by calculation. When each value is tested, if the value is if1, the result of the Decode function is then1; if the value is equal to if2, the result of the Decode function is then2; and so on. In fact, multiple if/then pairs can be given. If the value result is not equal to any given pairing, the Decode result will return else.
  • It should be noted that if, then and else here can be functions or calculation expressions.
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 id,name,
     case sex
      when '1' then '男'
      when '2' then '女'
     end 性别
from student;



DECODE takes the larger value of two columns in a row
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);



select month, decode(sign(sales_tv -sales_computer), 1, sales_tv, sales_computer) as  较大销售量 from sales; 


Row and Column Conversion in 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);

Insert picture description here
从格式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;

Insert picture description here
Insert picture description here

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

Insert picture description here

Function: For the query result, output the first several records
Note: Only with ==<,<===,between andUsed in conjunction

Insert picture description here
任务(1)答案:
    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;

Insert picture description here
任务(2)答案:
     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;

Insert picture description here

Delete duplicate records

Delete duplicate records method 1

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



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 duplicate records method 2

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 duplicate records method 3

DELETE FROM student d WHERE d.rowid > 
    (SELECT MIN(x.rowid) FROM student x WHERE d.sno=x.sno);

GROUP BY GROUPING SETS

You can use GROUP BY GROUPING SETS to group custom summaries, **you can use it to specify the total number combination you need.

The format is:
GROUP BY GROUPING SETS ((list), (list)…)

Here (list) is a sequence of columns in parentheses, and this combination generates a total. To add a total, you must add a (NUlL) grouping set.

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

Insert picture description here
Insert picture description here