day03_sql举例
1、decode[实现if ..then 逻辑] 注:第一个是表达式,最后一个是不满足任何一个条件的值
select ename,decode(deptno,10,'部门10',20,'部门20',30,'部门30','其他部门') "所在部门" from emp;
select ename,decode(deptno,10,'部门10',20,'部门20',30,'部门30','其他部门') aaa from emp;
(所在部门要改名字)
2、case when [实现if ..then 逻辑]
(1)
工资<1000 挣得太少
1000<工资<2000 挣得中等
2000<工资<4000 挣得比较高
工资>4000 挣得太多
实现：
select ename,
case
when sal<1000 then '挣得太少'
when sal>1001 and sal<2000 then '挣得中等'
when sal>2001 and sal<4000 then '挣得高'
else '挣得太多'
end as "评论工资"
from emp;
select ename,
case
when sal<1000 then '挣得太少'
when sal>1001 and sal<2000 then '挣的中等'
when sal>2001 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，这点是需要注意的地方。
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(8,'安庆');
insert into dave values(1,'dave');
insert into dave values(2,'bl');
insert into dave values(1,'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');
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
---------- ---------- ---------- ----------
8 安庆
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
5 dmm
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||' 上级是：'||b.ename from emp a left join emp b on a.mgr=b.empno;
SELECT worker.ename||' 的经理是 '||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);
(4)exists 是否存在
查找部门号是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 e where not exists(select 1 from dept d where e.deptno=d.deptno and d.deptno=10);
