Oracle多表查询，子查询，集合运算

select * from emp;
select * from salgrade;
select * from bonus;

--直接写一个常量要比写*更高效
select count(*) from emp;
select count(1) from emp;


/*

(1)、    别名查询：使用as关键字，可以省略
         别名中不可以有特殊字符或关键字，如果有就加双引号       
*/

select ename 姓名,sal 工资 from emp;
select ename "姓        名" from emp;


/*

(2)、   去除重复的列：distinct
        多列去除重复：每一列都一样才能算做重复

*/

--单列去除重复的列
select distinct job from emp;
--多行去除重复的列
select distinct job,deptno from emp;

--四则运算
select 1+1 from dual;
--查询员工年薪
select sal*12 from emp;
--查询员工年薪加奖金
select sal*12+nvl(comm,0) from emp;

--nvl(参数1，参数2)：如果参数为null,则返回参数2


/*

(3)    字符串拼接
            java中：    +号拼接
            Oracle中：  ||拼接
            在oracle中，双引号主要是别名时使用，单引号是使用的值，是字符
            concat(str1,str2)函数：在mysql和oracle中都有
*/

--查询员工姓名
select ename from emp;
--使用拼接符
select '姓名:'||ename "姓名" from emp;
--使用函数拼接
select concat('姓名:',ename) from emp;


/*

(4)    条件查询：【where后面的写法】
               1、关系运算符：>    <    >=   <=    !=,><
               2、逻辑运算符：and    or   not
               3、其他运算符：like          模糊查询
                              in(set)       在某个集合里
                              between..and  介于两者之间
                              is null       判断为空
                              is not null   判断不为空
*/

--查询每月能获得奖金的员工信息
select * from emp where comm is not null;
--查询工资在1500-3000之间的信息
select * from emp where sal between 1500 and 3000;
select * from emp where sal>=1500 and sal<=3000;
--查询名字在某个范围的员工信息(7521,7654,7844)
select * from emp where empno in('7521','7654','7844');



/*

(5)    模糊查询：like
             1、 %:匹配多个字符
             2、 _:匹配单个字符
             3、如果有特殊字符，需要转义escape
*/

--查询员工姓名第三个是A的学员信息
select * from emp where ename like '__A%';
--查询员工姓名中包含%的学员
select * from emp where ename like '%\%%' escape '\';

update emp set ename='jk%sd' where ename='jksd';



/*

(6)    1、排序：order by
       2、升序：asc ascend
       3、降序：desc desced
       4、排序注意null问题：nulls first/last
       5、同时排列多列，用逗号隔开
*/

--查询员工信息,按照奖金由高到低排序
select * from emp  order by  comm desc;
--查询部门编号和薪水，按照部门升序排列，工资降序排列
select * from emp order by deptno asc,sal desc;



/*

(7)    函数：必须要有返回值
       1、单行函数：对某一行的某个值进行处理
          (数值函数、字符函数、日期函数、转换函数、通用函数)
          
       2、多行函数(聚合函数)：对某一列的所有行进行处理
          (max  min  count  sum   avg)
*/

--统计员工工资总和
select sum(sal) from emp;
--统计员工奖金总和
select sum(comm) from emp;
--统计员工人数
select count(empno) from emp;
select count(1)from emp;
--统计员工的平均奖金
select avg(comm) from emp;
@@@select ceil(sum(comm)/count(1)) from emp;


1、数值函数
select ceil(45.926) from dual;   --向上取整 46
select floor(45.926) from dual;  --向下取整 45

2、四舍五入
select round(45.926,2) from dual;  --45.93
select round(45.926,1) from dual;  --45.9
select round(45.926,0) from dual;  --46
select round(45.926,-1) from dual; --50
select round(45.926,-2) from dual;   --0
select round(65.926,-2) from dual;    --100

3、截取，截断  
select trunc(45.926,2) from dual;   --45.92
select trunc(45.926,1) from dual;   --45.9
select trunc(45.926,0) from dual;   --45
select trunc(45.926,-2) from dual;  --0
select trunc(65.926,-2) from dual;  --0

4、求余
select mod(9,3) from dual;  --0
select mod(9,4) from dual;  --1

5、字符函数
--substr(str1,起始索引,长度)
--注意，起始索引不管写0还是1，都是从第1个字符串开始截取
select substr('abcdefg',0,3) from dual;  --abc  从1开始截取
select substr('abcdefg',1,3) from dual;  --abc
select substr('abcdefg',2,3) from dual;  --bcd

6、获取字符串长度
select length('asdfgh') from dual;   --6

7、去除字符左右两边空格
select '  hello  ' from dual;
select trim('            hello') from dual;

8、面试中可能会遇到
select floor(-12.5) from dual;  --  -13 向下取整 负数
select floor(12.5) from dual;   --   12
select ceil(-12.5) from dual;   --   -12   向上取整  正数
select floor(12.5) from dual;   --   12

9、替换字符串
select replace('hello world','l','a') from dual;

10、日期函数
--查询今天的信息
select sysdate from dual;
--查询3个月之后的今天日期
select add_months(sysdate,3)from dual;
--查询3天后的信息
select sysdate+3 from dual;

--查询员工入职的天数
select sysdate-hiredate from emp;
select ceil(sysdate-hiredate) from emp;
--查询员工入职的周数
select (sysdate-hiredate)/7 from emp;
--查询员工入职的月数
select months_between(sysdate,hiredate) from emp;
--查询员工入职的年数
select months_between(sysdate,hiredate)/12 from emp;

11、转换函数 (字符转数值，数值转字符，日期转字符,字符转日期)
--1)字符转数值 to_number(str)
select 100+'10'  from dual;   --110
select 100+to_number('10') from dual;  --110

--2)数值转字符
select to_char(sal,'$9,999.99') from emp;  --1300
select to_char(sal,'$9,999,99') from emp;  --13  注意.和,的区别
select to_char(sal,'L9,999.99') from emp;  --1300

--3)日期转字符
select sysdate from dual;
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;  --当前时间，12小时制
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; --当前时间，24小时制

--只想要年
select to_char(sysdate,'yyyy')from dual;
--只想要天数
select to_char(sysdate,'d') from dual;  --代表一周中的第几天  6
select to_char(sysdate,'dd') from dual; --代表一月中的第几天  6
select to_Char(sysdate,'ddd') from dual;--代表一年中的第几天  187

select to_char(sysdate,'day') from dual;  --monday 获取当前星期  星期五
select to_char(sysdate,'dy') from dual;   --星期的简写

--4)字符转日期
select to_date('2018-7-6','yyyy-mm-dd') from dual;  --2018/7/6
--查询1981-1987入职的学员信息
select * from emp where hiredate>to_date('1981','yyyy') and hiredate<to_date('1987','yyyy');
select * from emp where hiredate between to_date('1981','yyyy') and to_date('1987','yyyy');


/*

8)     通用参数
         nvl(参数1,参数2)         如果参数1=null,就返回参数2
         nvl2(参数1,参数2,参数3)  如果参数1=null,就返回参数3,否则返回参数2
         nullif(参数1,参数2)      如果参数1=参数2,则返回空,否则返回参数1
         coalesce                 返回第一个不为null的值
         
      注意：null值，代表不确定的，不可预知的内容，不可以做四则运算

*/

--查询员工年薪加奖金
select sal*12+nvl(comm,0) from emp;  --nvl

select nvl2(1,2,3) from dual;     --2  nvl2
select nvl2(0,2,3) from dual;     --2  0也算参数
select nvl2(null,2,3) from dual;  --3

select nullif(6,6) from dual;    --null
select nullif(7,6) from dual;    --7

select coalesce(null,null,7,8,9) from dual;   --7
select coalesce(null,7,8,9) from dual;        --7


/*

9)     条件表达式
            1、case字段
               when1值   then值
               when2值   then值
               else
                  默认值
             end "别名"
             
      case..when通用的写法在mysql和oracle中都可以使用
      
        2、oracle中特有的写法
             decode(字段,if1,then1,if2,then2,else1..);
        

*/

--给studentinfo表中取一个别名
select * from studentinfo;

select
   case stuname
        when  '孙悟空'  then '曹操'
        when  '大黄蜂'  then '刘备'
        else
            '苏陌'
   end "姓名"
from studentinfo;

select decode(stuname,'孙悟空','曹操','大黄蜂','刘备', '苏陌') from studentinfo;


/*

10)    分组表达式： group by
       select 分组的条件,分组之后的操作 from 表名 group by 分组的条件 having条件过滤
       
       SQL的编写顺序
              select..from ..where ..group by ..having ..order by
       SQL的执行顺序
              from .. where .. group by ..having.. select ..order by ..
             
       where和having的区别：
              where后面不能接聚合函数，可以接单行函数
              having是在group by 后面执行，可以接聚合函数
*/

--分组统计所有部门的平均工资，找出平均工资大于2500的部门
select deptno,avg(sal) from emp group by deptno having avg(sal)>2500;
--出错，执行顺序
select deptno,avg(sal) dd from emp group by deptno having dd>=2500;