常用函数举例

SQL举例

DECODE(value,if1,then1,if2,then2,if3,then3,...,else)

 

 

 

1、decode[实现switch逻辑]   注:第一个是表达式,最后一个是不满足任何一个条件的值

select ename,deptno,decode(deptno,10,'部门10',20,'部门20',30,'部门30','其他部门') 所在部门 from emp;

 

2、case  when [实现if ..then 逻辑]

(1)

工资<1000        挣得太少

1000<工资<2000   挣得中等

2000<工资<4000   挣得比较高

工资>4000                        挣得太多

 

实现：

select ename,sal,

case

when sal<=1000 then '挣得太少'

when sal>1000 and sal<=2000 then '挣得中等'

when sal>2000 and sal<=4000 then '挣得比较高'

else '挣得太多'

end as "评论工资"

from emp;

 

(2)

工资5000以上的职员，工资减少10%

工资在2000到4600之间的职员，工资增加15%

 

很容易考虑的是选择执行两次UPDATE语句，如下所示

--条件1 

UPDATE test  SET sal = sal * 0.9  WHERE sal >= 5000; 

 

--条件2 

UPDATE test  SET sal = sal * 1.15  WHERE sal >= 2000 AND sal < 4600; 

但是事情没有想象得那么简单，假设有个人工资5000块。首先，按照条件1，工资减少10%，变成工资4500。

接下来运行第二个SQL时候，因为这个人的工资是4500在2000到4600的范围之内， 需增加15%，最后这个人的工资结果是5175,不但没有减少，反而增加了。

暂且不管这个规章是多么荒诞，如果想要一个SQL 语句实现这个功能的话，我们需要用到Case函数。代码如下:

 

create table test as select * from emp;  【创建测试表】

 

UPDATE test   

SET sal =

CASE

WHEN sal >= 5000  THEN sal * 0.9 

WHEN sal >= 2000 AND sal < 4600  THEN sal * 1.15 

ELSE sal

END; 

 

这里要注意一点，最后一行的ELSE sal是必需的，要是没有这行，不符合这两个条件的人的工资将会被写成NUll,那可就大事不妙了。

在Case函数中Else部分的默认值是NULL，这点是需要注意的地方。

 

UPDATE test   

SET sal =

CASE

WHEN sal >= 5000  THEN sal * 0.9 

WHEN sal >= 2000 AND sal < 4600  THEN sal * 1.15 

END; 

 

3、左、右连接、全连接

create table bl(id int,name varchar2(100));

 

insert into bl values(1,'dave');

insert into bl values(2,'bl');

insert into bl values(3,'big bird');

insert into bl values(4,'exc');

insert into bl values(9,'怀宁');

 

create table dave(id int,name varchar2(100));

 

insert into dave values(1,'dave');

insert into dave values(1,'bl');

insert into dave values(2,'bl');

insert into dave values(2,'dave');

insert into dave values(3,'dba');

insert into dave values(4,'sf-express');

insert into dave values(5,'dmm');

insert into dave values(8,'安庆');

commit;

 

(1) 左外连接（Left outer join/ left join）

     left join是以左表的记录为基础的,示例中Dave可以看成左表,BL可以看成右表,它的结果集是Dave表中的数据，在加上Dave表和BL表匹配的数据。

     换句话说,左表(Dave)的记录将会全部表示出来,而右表(BL)只会显示符合搜索条件的记录。BL表记录不足的地方均为NULL.

 

示例：

SQL> select * from dave a left join bl b on a.id = b.id;

 

select a.id,a.name,decode(b.id,null,'null',b.id),decode(b.name,null,'null',b.name) from dave a left join bl b on a.id = b.id;

 

       ID NAME               ID NAME

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

        1 bl                  1 dave

        1 dave                1 dave

        2 dave                2 bl

        2 bl                  2 bl

        3 dba                 3 big bird

        4 sf-express          4 exc

        5 dmm                             -- 此处B表为null，因为没有匹配到

        8 安庆                             -- 此处B表为null，因为没有匹配到

       

       

 

用（+）来实现， 这个+号可以这样来理解： + 表示补充，即哪个表有加号，这个表就是匹配表。所以加号写在右表，左表就是全部显示，故是左连接。

 

SQL> Select * from dave a,bl b where a.id=b.id(+);    -- 注意： 用（+） 就要用关键字where

 

Select  a.id,a.name,decode(b.id,null,'null',b.id),decode(b.name,null,'null',b.name) from dave a,bl b where a.id=b.id(+);

 

        ID NAME               ID NAME

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

         1 bl                  1 dave

         1 dave                1 dave

         2 dave                2 bl

         2 bl                  2 bl

         3 dba                 3 big bird

         4 sf-express          4 exc

         5 dmm

         8 安庆

 

(2) 右外连接（right outer join/ right join）

和left join的结果刚好相反,是以右表(BL)为基础的, 显示BL表的所以记录，在加上Dave和BL 匹配的结果。 Dave表不足的地方用NULL填充.

 

示例：

SQL> select * from dave a right join bl b on a.id = b.id;

 

        ID NAME               ID NAME

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

         1 dave                1 dave

         2 bl                  2 bl

         1 bl                  1 dave

         2 dave                2 bl

         3 dba                 3 big bird

         4 sf-express          4 exc

                               9 怀宁    --此处左表不足用Null 填充

已选择7行。

 

 

用（+）来实现， 这个+号可以这样来理解： + 表示补充，即哪个表有加号，这个表就是匹配表。所以加号写在左表，右表就是全部显示，故是右连接。

 

SQL> Select * from dave a,bl b where a.id(+)=b.id;

 

        ID NAME               ID NAME

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

         1 dave                1 dave

         2 bl                  2 bl

         1 bl                  1 dave

         2 dave                2 bl

         3 dba                 3 big bird

         4 sf-express          4 exc

                               9 怀宁

 

(3)全连接（full join）

     左表和右表都不做限制，所有的记录都显示，两表不足的地方用null 填充。 全外连接不支持（+）这种写法。

 

 

示例：

 

SQL> select * from dave a full join bl b on a.id = b.id;

        ID NAME               ID NAME

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

         1 bl                  1 dave      

         1 dave                1 dave      

         2 dave                2 bl        

         2 bl                  2 bl        

         3 dba                 3 big bird  

         4 sf-express          4 exc       

         5 dmm                             

         8 安庆                            

                               9 怀宁      

已选择9行。

 

查询员工姓名和所在部门的名称 

 

--等值查询

select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;

 

--内联接查询   inner join  on

select a.ename,b.dname  from emp a inner join dept b on a.deptno=b.deptno

 

--自连接查询

   查询员工姓名和其直接上级的姓名

 

emp员工表 字段内容如下:

                empno  员工号

                ename  员工姓名

                job  工作

                mgr  上级的员工号

                hiredate 受雇日期

                sal  薪金

                comm  佣金

                deptno  部门编号

 

例如："SMITH"的上级是"FORD" 

empno：7369    ename：SMITH    mgr：7902

empno：7902    ename：FORD      

 

select a.ename as 员工姓名,b.ename as 领导姓名 from emp a left join emp b on a.mgr=b.empno;

 

SELECT worker.ename as 员工姓名,manager.ename as 领导姓名 FROM emp worker, emp manager 

WHERE worker.mgr = manager.empno; 

 

--子查询

如果子查询未返回任何行，则主查询也不会返回任何结果

(空值)select * from emp where sal > (select sal from emp where empno = 8888);

 

如果子查询返回单行结果，则为单行子查询，可以在主查询中对其使用相应的单行记录比较运算符

 

(正常)select * from emp where sal > (select sal from emp where empno = 7369);

 

如果子查询返回多行结果，则为多行子查询，此时不允许对其使用单行记录比较运算符

 

(多值)select * from emp where sal > (select avg(sal) from emp group by deptno);//非法

 

子查询中常用方法

 

(1)any即任何一个。如果在where条件中加入>any，意思是大于任何一个，也就是大于最小的

 

select * from emp t where t.sal > any (select sal from emp where deptno=30);

 

(2)all即所有。如果在where条件中加入>all，意思是大于每一个，也就是大于最大的。

select * from emp t where t.sal> all(select sal from emp where deptno=30);

create table test(id int);

 

insert into test values(1);

insert into test values(2);

insert into test values(3);

insert into test values(4);

commit;

 

--All：对所有数据都满足条件，整个条件才成立，例如：5大于所有返回的id

select *

from test where 5>All(select id from test)

 

--Any：只要有一条数据满足条件，整个条件成立，例如：3大于1，2

select *

from test where 3>any(select id from test)

 

--Some和Any一样

备注：Any的用法中，在作数字比对时，也可以改用先select subquery的min/max value的方法，某些情况下效率更高

 

这两个都是用于子查询的

 

any 是任意一个

all 是所有

 

比如

select * from student where 班级='01' and age > all (select age from student where 班级='02');

就是说，查询出01班中，年龄大于 02班所有人的同学

相当于select * from student where 班级='01' and age > (select max(age) from student where 班级='02');

而select * from student where 班级='01' and age > any (select age from student where 班级='02');就是说，查询出01班中，年龄大于 02班任意一个的同学相当于select * from student where 班级='01' and age > (select min(age) from student where 班级='02');

 

(3)In 在某集合内select * from emp t where t.deptno in(30,10);

select * from emp t where t.deptno =30 or t.deptno =10;

 

(4)exists 是否存在

查找部门号是10的所有员工信息

 

 select * from emp t where t.deptno in (10);

 select * from emp t where t.deptno =10;

 

 select * from emp e where exists (select 1 from dept d where e.deptno=d.deptno and d.deptno=10);

 

   查找部门号不是10的所有员工信息

      select * from emp t where t.deptno not in(10);

                             select * from emp t where t.deptno !=10;

                                                         select * from emp t where t.deptno <>10;

  select * from emp e where not exists(select 1 from dept d where e.deptno=d.deptno and d.deptno=10);