一个菜鸟的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 子查询等知识