一个菜鸟的oracle之路----------三

经琴音

今天学习的知识点有

1，非关联子查询  学习非关联子查询的数据环境和语法

2，关联子查询   对比非关联子查询，掌握语法

3，集合操作    多个结果集之间的操作

4，表间操作    多表之间的关联关系

5，内连接和外连接 多表之间的关联查询

6，左/右/全外连接 多表之间的关联查询


1,知识点小结。

1.1 查询语句的基本格式。
   select 字段1，字段2，字段3，表达式，函数,...
from 表名
where 条件
group by 列名
having 带组函数的条件
order by 列名



1.2 函数

1.21 组函数，count avg sum max min

//注意：组函数忽略空值

1.22 单行函数

字符函数，upper/lower/initcap/length/lpad/rpad/replace/trim

upper 转化为大写

lower 转化为小写

initcap 转化为首字母大写

length  取长度

lpad   左补丁

rpad   右补丁

replace 字符转化

trim   去除前面的空格

案例1 将ename字段设置为10个长度，如果不够左边用'*'补齐
select lpad(ename,10,'*')from emp_xxx;



案例2
  select rpad(ename,10,'#')from emp_xxx;



案例3 数字函数，round /trunc/mod

求salary对5000取模
  select salary,mod(salary,5000)from emp_xxx;



案例4 日期函数 months_between、add_months、last_day

months_between 两个日期之间的月份数

add_months   给定一个时间，为该日期增加指定月份

last_day    找出参数时间点所在月份的最后一天

将amy 的入职时间提前2个月
select ename,hiredate from emp_xxx where ename='郭靖';
select ename,add_months(hiredate,-2) from emp_xxx where ename='郭靖';



//使用update语句进行更改
update emp_xxx set hiredate=add_months(hiredate,-2) where ename='郭靖';



//再次进行查询
select ename,hiredate from emp_xxx where ename='郭靖';



案例5，这个月最后一天是多少
select last_day(sysdate) from dual;



转换函数 to_char/to_date和to_number

to_char      to_number

日期  ---------->字符   ------------>数字

<----------     <------------

to_date       to_char

案例6 将“$7912345.67”乘以10，输出结果
  select to_number('$7912345.67','$9999999.99')*10 from dual;



案例7 将79123456.79按照指定格式$9999999.99输出
  select to_char('7912345.67','$9999999.99')from dual;



通用函数 nvl/coalesce/decode

单行函数出去四大类字符函数，数字函数，日期函数，转换函数，还有一些其他函数

nvl/coalesce/decode

使用频率比较高的函数

单行函数 upper round to_char/to_date/nvl

组函数  count/avg/sum/max/min

2 子查询

2.1 单行函数比较运算 > < >= <= = <>

案例8 ，谁的薪水比黄蓉的高
select ename,salary
from emp_xxx
where salary>(
select salary from emp_xxx where ename='黄蓉');



//保留此问题，如果存在2个黄蓉该怎么办

案例9  研发部有哪些职位
  select distinct job
from emp_xxx
where deptno=(select empno from emp_xxx
where ename='Analyst');



//感觉该问题还没有解决

案例10 谁的薪水比张无忌的高
insert into emp_xxx(empno,ename,salary)
values(1012,'张无忌',8000);
select ename,salary from emp_xxx;



//如果有多个叫张无忌的则会出错

//报错为  单行子查询返回多个行
  select ename,salary
from emp_xxx
where salary>(select salary from emp_xxx where ename='张无忌');




2.2 ALL

案例11 查询谁的薪水比所有叫张无忌都高

//大于最大值
  select ename,salary
from emp_xxx
where salary>All(select salary from emp_xxx where ename='黄蓉');



//大于最大值，本表中存在2位叫黄蓉的同学


2.3 Any

案例12 //大于最小值
select ename,salary
from emp_xxx
where salary>any(select salary from emp_xxx where ename='黄蓉');



2.4 In

案例13 谁和刘苍松同部门？
select ename
from emp_xxx
where deptno=(select deptno from emp_xxx where ename='刘苍松');



谁和刘苍松同部门？ 列出除刘苍松之外的员工名字     

用子查询实现同一功能
  select ename
from emp_xxx
where deptno=(select deptno from emp_xxx where ename='刘苍松')
and ename<>'刘苍松';



2.5 单行比较运算符和All Any in

案例14 谁和刘苍松同部门？列出除刘苍松之外的员工的名字

（如果子查询得到的结果十多个）
insert into emp_xxx(empno,ename,deptno)values(1015,'刘苍松',20);
values(1015,'刘苍松',20)



//如果子查询得到的结果是多个，不能使用单行比较运算符，应该改为in

//否则会报错，ORA-01427 单行子查询返回多个行
select ename,salary,job,deptno
from emp_xxx
where deptno in(select deptno from emp_xxx
where ename='刘苍松')
and ename<>'刘苍松';



案例15 谁是张无忌的下属，如果只有一个叫张无忌的员工，则无问题

如果有多个，需要用in
   select empno from emp_xxx
where ename='张无忌'
//select ename from emp_xxx
//  where mgr in(1001,1014);
select ename
from emp_xxx
where mgr in(select empno from emp_xxx
where ename='张无忌');



小结：单行比较运算符和All Any in

根据子查询返回的结果的行数选择使用

返回一行 > < <= = <>

返回多行 >ALL >Any <any in

2.6 子查询结果返回多列的情况

案例16 每个部门拿最高薪水的是谁？

//首先求出每个部门拿最高薪水的薪水是多少
  select deptno,max(salary)
from emp_xxx
where deptno is not null
group by deptno;



//然后求出该部门拿最高薪水的人是谁？
      select ename, salary,deptno
from emp_xxx
where (deptno,salary) in(select deptno,max(salary)
from emp_xxx
where deptno is not null
group by deptno);



//感觉该题还没有完全掌握，

//明白了，不明白请看案例14

2.7 子查询出现在having短语中

案例17 哪个部门的人数比部门30人多？
select count(*)
from emp_xxx
where deptno=10;



//我的错误解法
  select deptno,count(*)
from emp_xxx
group by deptno
where count(*)>(select count(*)
from emp_xxx
where deptno=30);



//正确答案
  select deptno,count(*)
from emp_xxx
group by deptno
having count(*)>=(select count(*)
from emp_xxx
where deptno=10)
and deptno<>10;



//这个案例题很经典，学习到了group by having <>

案例18 哪个部门的平均薪水比部门20的平均薪水高？
select avg(nvl(salary,0))
from emp_xxx
where deptno=20;
select deptno
from emp_xxx
group by deptno
having avg(nvl(salary,0))>(select avg(nvl(salary,0))
from emp_xxx
where deptno=20);



//该题目也很经典，是我自己做出来的O(∩_∩)O哈哈~


案例19 列出员工的名字和职位，这些员工所在的部门的平均薪水大于5000元
   select ename,job
from emp_xxx;



//首先查询出部门平均薪水大于5000元的部门
  select deptno
from emp_xxx
group by deptno
having avg(nvl(salary,0))>5000;



//汇总以后的结果为
  select ename,job
from emp_xxx
where deptno in (select deptno
from emp_xxx
group by deptno
having avg(nvl(salary,0))>2000);



//该示例也非常经典，综合考察了 group by having avg nvl 子查询等知识