oracle的常用sql

 

 

区别1

• oralce :  select e.ename, (e.sal + nvl(e.comm, 0)) * 12 from emp e;
• mysql :  select e.ename, (e.sal + ifnull(e.comm, 0)) * 12 from emp e;

 

• 模糊查询 like '_A%' 查询第二个字符是A
•  

 

其他

• 全集 union all
• 并集 union 
• 交集 intersect
• 差集 minus

 

函数

• sql函数可以分为分组函数和单组函数

 

连表查询

•  -- 等值连接
   select *  from emp e,dept d where e.deptno = d.deptno;
   select * from emp e inner join dept d on e.deptno = d.deptno
   
   -- 左外连接(+)
    select *  from emp e,dept d where e.deptno = d.deptno(+);
    select * from emp e left join dept d on e.deptno = d.deptno
    
   -- 右外连接
   
   select *  from emp e,dept d where e.deptno(+) = d.deptno;
   select * from emp e right join dept d on e.deptno = d.deptno
   
   --全查询
    select * from emp e full join dept d on e.deptno = d.deptno

 

分页/限制输出

• rownum，不能直接使用，需要嵌套使用
• 取工资前五名人员信息

  // 1. 先排序
  // 2. 取前五、需要嵌套两层
  select *
    from (select * from emp e where e.sal is not null order by e.sal desc) t
   where rownum <= 5

• 取工资第六名到第十名人员信息

  // 1. 先排序
     2. 嵌套、需要查询出rownum
     3. 在取rownum 的排序 
  
  select *
     from (select t.*, rownum rn
             from (select * from emp e order by e.sal desc) t
            where rownum <= 10) t
    where t.rn between 5 and 10
  
  
   select *
     from (select t.*, rownum rn
             from (select * from emp e order by e.sal desc) t
            ) m
    where m.rn between 5 and 10

   

行转列

• 如下图，将图一转化为图二
  
  

• 答案

  行转列
  
  1.先将胜负拆出来
  2.对日期进行分组
  3.统计分组
  
  select t.rq, sum(t.sheng) as 胜, sum(t.fu) as 负
    from (select rq,
                 decode(shengfu, '胜', 1) sheng,
                 decode(shengfu, '负', 1) fu
            from tmp) t
   group by t.rq
  

• 将图一转为图二
  
  

• 答案
   

  -- 创建表
  create table STUDENT_SCORE
  (
    name    VARCHAR2(20),
    subject VARCHAR2(20),
    score   NUMBER(4,1)
  );
  insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '语文', 78.0);
  insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '数学', 88.0);
  insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '英语', 98.0);
  insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '语文', 89.0);
  insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '数学', 76.0);
  insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '英语', 90.0);
  insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '语文', 99.0);
  insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '数学', 66.0);
  insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '英语', 91.0);
  
  
  
  -- decode
  select name, max(decode(SUBJECT, '语文', score)) 语文, max(decode(SUBJECT, '数学', score)) 数学, max(decode(SUBJECT, '英语', score)) 英语 from STUDENT_SCORE
  group by name
  
  
  -- case when
  select s.name,
         max(case s.SUBJECT
               when '语文' then
                s.score
             end) 语文,
         max(case s.SUBJECT
               when '数学' then
                s.score
             end) 数学,
         max(case s.SUBJECT
               when '英语' then
                s.score
             end) 英语
  
    from STUDENT_SCORE s
   group by s.name
  
  
  
  -- 连表
  select a.name, a.score 语文, b.score 数学, c.score 英语 from
  (select s.name, s.score from STUDENT_SCORE s where s.SUBJECT = '语文')  a inner join  
  (select s.name, s.score from STUDENT_SCORE s where s.SUBJECT = '数学')  b on a.name = b.name inner join
  (select s.name, s.score from STUDENT_SCORE s where s.SUBJECT = '英语')  c on b.name = c.name 
  
  
  
  
  -- uniom all
  select t.name, max(t.a) 语文, max(t.b) 数学, max(t.c) 英语 from (
  select a.name name, a.score a, 0 b, 0 c from STUDENT_SCORE a where a.SUBJECT = '语文' union all
  select b.name name, 0 a, b.score b, 0 c from STUDENT_SCORE b where b.SUBJECT = '数学' union all
  select c.name name, 0 a, 0 b, c.score c from STUDENT_SCORE c where c.SUBJECT = '英语' ) t
  group by t.name
  
  
  

   

• 创建表

  • create table emp2 as select * from emp; 创建表同时复制表数据

  • create table emp3 as select * from emp where 1 = 2;创建表结构。不需要数据