ORACLE_高级查询（4）_单行函数_转换函数_通用函数_Unit_6;

 Topic 1 : 单行查询；

 

--单行函数
--字符函数  数值  日期   转换   通用
--  操作一行返回一行
-- 字符函数 
select lower('Hello World') 小写，upper('Hello World') 大写,initcap ('hello world') 首字母大写 from dual;   
--dual 是一个伪表

select substr ('Hello World',3) 字串 from dual;
--我们的显示结果是 llo World  这再次验证了一个问题就是oracle  的计数是从1开始计数的
--而mySql是从0开始的
--我们看一个他的重载函数
select substr('Hello World',3,3) 字符串 from dual;

select length('Hello ') 字符数,lengthb('Hello ') 字节数 from dual;

select length('西安') 字符数,lengthb('西安') 字节数 from dual;
--这个操作系统（UTF-8）的一个汉字是占3个字节的  而gbk两个字节；
select * from nls_database_parameters;
-- 可以看到数据字典  我们的编码为  AL32UTF8  所以我们的汉字是3个字节的；

--unicode 是字符集  utf-8，utf-16是编码规则  unicode传输的时候就会用  utf-18这样的编码规则  
-- utf-8和GBK（涵盖了GB2312     unicode的汉字）是编码方式

--instr  oracle是从一开始  找不到的时候我们返回0  而java中是从0开始找不到是返回-1；
select instr('Hello','ll') 位置 from dual;

--lpad 左填充  rpad 右填充
select lpad('abc',10,'*'),rpad('sas',10,'*') from dual;

--trim 在java中是去空格的  但是在这里的功能更强大  他是默认去空格（是去首尾两端的中间的空格是不去的  和java一样）
select trim('  hello  world   ') from dual;
--他还可以去掉任意字符
select trim('X' from 'XXX aaaX aaXX') from dual;

--repalce
select replace('He**o','*','l') from dual;

--  数值函数 round（数字，n） 四舍五入  trunc  截取   mod（）求余数；
select round(78.23,1) 一, round(78.23,0) 零,round(78.23,-1) 负一,round(78.23,-2) 负二 from dual;
-- 结果为 78.2   78  80  100；
 
--日期函数  关键字SYSDATE(求出当前时间)
select SYSDATE from dual;

select (SYSDATE -1) 昨天 from dual;

--对于我们的日期我们可以加行加减几天（数值）  但是对于日期类型与日期类型的操作我们只能减不能加
select ename,hiredate,(SYSDATE - hiredate) 天,(SYSDATE - hiredate)/7 星期 from emp; 

--精确的算出 两者之间的月份 months—_between
select (SYSDATE - hiredate)/30 入职的大致月数,months_between(SYSDATE,hiredate) 入职精确月份 from emp;

--add_months()  增加多少月之后是哪一天  89个月之后今天是哪一天;  两个月之前是你哪一天？
select add_months(SYSDATE,89) from dual;
select add_months(SYSDATE,-2) from dual;
--last_day()   当月的最后一天
select last_day(SYSDATE) from dual;


--next_day(time，星期n) 求最近的下一个星期n是哪一天？  
select next_day(SYSDATE,'星期五') from dual; 

--round 和  trunc  --四舍五入的时候  舍掉的都是用1来填充
select round(SYSDATE,'month'),round(SYSDATE,'year') from dual;

select trunc(SYSDATE,'month'),trunc(SYSDATE,'year') from dual;

--相同就返回  null   不相同就返回第一个参数
select nullif('abc','aaa'),nullif('aaa','aaa') from dual;

select comm,sal,coalesce(comm,sal) 返回第一个不为空的值 from emp;
--注意comm不只两个参数

--decode  与switch类似   如果是'SALESMAN'  就sal+1000  如果MANAGER  就sal+500  default   sal+200
select ename,job,sal 涨工资前,decode(job,'SALESMAN',sal+1000,'MANAGER',sal+500,sal+200) 涨工资后 from emp;

--case when  then  else  end 表达式  与decode的用法基本相似的 
select ename,job,sal 涨工资前,case job when 'SALESMAN' then sal+100
                                        when 'MANAGER'then sal+500
                                          else sal + 400
                                            end 涨工资后
                                       from emp;

--转换函数

--字符与数字之间的转换是隐士转换
select * from emp where empno = 7369;
--然后看empno发生的隐士转换
select * from emp where empno = '7369';
select length(123) from dual;
--length()是一个字符函数但是  123隐式转化为字符类型了；

--字符与日期的自动转换（隐式转化）；
select * from emp where hiredate = '17-12月-80';  --字符自动转化为日期

--instr 为字符函数
select SYSDATE,instr(SYSDATE,'10') from dual;
--所以字符与数字  字符与数字类型可以自动的隐式转化；



--显示转化：
--to_number  to_char
--to_date   to_char
--这也是讲的  字符与数字   字符与日期   ，也就是说不是所有的他们都可以隐式转化的  有些是需要显示转化的
--toB的类型（A的数据，'格式'）

--数字到字符
select to_char(123456.7,'L999,999.9') from dual;
--显示为        ￥123,456.7


-- 字符到数字
select to_number('￥123,456.7','L999,999.9') from dual;
select to_number('￥123,456.7','L999999.9') from dual;

 --字符边日期
 
 select to_date('1997-09-29','YYYY-MM-dd') from dual;


--日期到字符  --这个是没有MM和mm  区分但是java中有 因为在sql中只有真实数据采取分大小写  在这里是不区分大小写的
--所以我们的分是  --mi  
select to_char(SYSDATE,'yyyy-mm-dd hh:mi:ss') from  dual;
--变为24进制
select to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss') from  dual;




--单行函数的练习题

-- 1： 查询员工总人数 和 各个年份的入职人数
select count(*) 总人数,
       sum(decode(to_char(hiredate,'yyyy'),1980,1,0))  as "1980",
       sum(decode(to_char(hiredate,'yyyy'),1981,1,0))  as "1981",
       sum(decode(to_char(hiredate,'yyyy'),1982,1,0))  as "1982",
       sum(decode(to_char(hiredate,'yyyy'),1987,1,0))  as "1987"
       from emp;

-- 第二种算法
select to_char(hiredate,'yyyy'),count(*) from emp group by to_char(hiredate,'yyyy')
union
select null,count(*) from emp; --注意这个地方null一定要有的  不然的话 会报错  并集必须列数和类型都一样的；

--这样先分组在不分组的题 我们就会想到增强的分组；
select to_char(hiredate,'yyyy'),count(*) from emp group by rollup (to_char(hiredate,'yyyy'));
--第二次我们不根据
--to_char(hiredate,'yyyy') 分组  所以他就没有显示出来按照null来显示的