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来显示的