select单表查询语句--使用函数

使用函数可以大大提高SELECT语句操作数据库的能力;它给数据的转换和处理提供了方便。函数只是将取出的数据进行处理，不会改变数据库中的值。

Oracle函数分为单行函数和多行函数两大类

【单行函数】

单行函数分类：字符函数 数值函数 日期函数 转换函数 通用函数

--特点1：不改变真实数据，只对数据做了进一步的修饰显示

--特点2：可以和字段混合使用

--字符函数：select 字段名,函数名(字段名),字段名... from 表名;

多行函数：sum() avg() 仅适用数值型   count() max() min() 适用任何类型数据

伪表：[不是真实存在的表，是为了方便进行数据的验证临时存在的表，表名：dual]

【常用字符函数】：

字符函数举栗子：

1 --举栗子【字符函数】 2 --1.首字母大写：查询emp表中姓名列首字符大写 3 select ename,initcap(ename) as "首字母大写" from emp; 4 --2.转为小写：查询emp表中的职位全部小写显示 5 select job,lower(job) as "职位小写显示" from emp; 6 --3.转为大写：查询emp表中的姓名全部大写显示 7 select ename,upper(ename) as "姓名大写" from emp; 8 --4.左裁剪：ltrim('ltrimHello world','ltrim')=>Hello world 9 select ltrim('ltrimHello world','ltrim') from dual;10 --5.右裁剪：rtrim('Oracle1234','1234')=>Oracle11 select rtrim('Oracle1234','1234') from dual;12 --6.按字符翻译:abcd按字符翻译成boom=>boom40413 select translate('abcd404','abcd','boom') from dual;14 --7.字符串替换:把java替换成oracle=>hello oracle15 select replace('hello java','java','oracle') from dual;16 --8.查找子串位置:查找下标从1开始，并不是从017 select instr('caoyinboom404','y') from dual;18 --9.取子字符串：从第7个开始截取，取出后面7个字符19 select substr('caoyinboom404',7,7) from dual;20 --10.连接字符串21 select concat('hello',' oracle') from dual;

【常用数值函数】：

数值函数举栗子：

1 --举栗子【数值函数】 2 --1.绝对值 3 select abs(-9) from dual; 4 --2.向上取整:不管小数点第一位是几，都进以为数值 5 select ceil(55.12) from dual; 6 --3.正弦 7 select sin(45) from dual; 8 --4.余弦 9 select cos(45) from dual;10 --5.取符号：正数 1、负数-111 select sign(5) from dual;12 select sign(-5) from dual;13 --6.向下取整：相当于只取出整数部分.小数部分忽略不计14 select floor(99.99) from dual;15 --7.m的n次幂16 select power(5,2) from dual;17 --8.取余数：是否带符号取决于左边的数值，[不存在- - 得 +]18 select mod(10,8) from dual;-- 219 select mod(-10,8) from dual;-- -220 select mod(10,-8) from dual;-- 221 select mod(-10,-8) from dual;-- -2【不存在负负得正】22 --9.四舍五入23 select round(10.49) from dual;24 --10.保留小数位25 select trunc(10.98170,2) from dual;26 --11.平方根27 select sqrt(16) from dual;

【常用日期函数】：

日期函数举栗子：

1 --举栗子【日期函数】 2 --1.返回两个日期间的月份：月份在前，否则就负值 3 select months_between('01-8月-2018','01-6月-2018') from dual; 4 --2.返回月份数添加到日期对应的新日期:参数2：相当于加减月份 5 select add_months('01-8月-2018',1) from dual;--2018/9/1 星期六 6 select add_months('01-8月-2018',-2) from dual;--2018/6/1 星期五 7 --3.返回指定日期后的星期对应的新日期 8 select next_day('02-10月-2018','星期一') from dual;--查询最近星期的日期。2018/10/8 星期一 9 --4.返回指定日期所在月份的最后一天10 select last_day('02-2月-2018') from dual;--2018/2/28 星期三11 12 --5.按指定格式对日期进行四舍五入【挣扎了好久，外国的日期跟中国的习惯不一样，不必要纠结日期这段，跳过去】13 14 --指定年份：年的四舍五入按月份算15 select round(to_date('30-6月-03'),'YEAR') from dual;--2003/1/1 星期三16 select round(to_date('21-7月-03'),'YEAR') from dual;--2004/1/1 星期四17 --指定月份：月的四舍五入按日份算18 select round(to_date('15-8月-03'),'MONTH') from dual;--2003/8/1 星期五19 select round(to_date('16-9月-03'),'MONTH') from dual;--2003/10/1 星期三20 --指定日:按星期的中间天数21 select round(to_date('01-10月-2018'),'DAY') from dual;22 select round(to_date('01-4月-2018'),'DAY') from dual;

【多行函数】

【多行函数】：对一组数据进行运算，针对一组数据（多行记录）只返回一个结果，也称分组函数。

--多行函数不能和普通字段值及单行函数混合使用，除非分组

--作用：对查询数据进行统计

--使用：select 多行函数名(字段名),多行函数名(字段名)...from 表名;

常用多行函数：sum() avg() 仅适用数值型   count() max() min() 适用任何类型数据

--max（字段名）返回该字段的最大值

--min（字段名）返回该字段的最小值

--sum（字段名）返回该字段的和

--avg（字段名）返回该字段的平均值

--count

　--count（*）返回表的记录数

　--count（字段名）返回非空值的数量

　--count（distinct 字段名）去除重复后的字段值的数量

多行函数举栗子：

1 --查询员工的最高工资 2 select max(sal) from emp; 3 --查询员工的最底工资 4 select min(sal) from emp; 5 --查询员工的平均工资 6 select avg(sal) from emp; 7 --查询所有员工的平均工资之和 8 select sum(sal) from emp; 9 --查询公司有多少员工10 select count(*) from emp;--查询表的记录数11 --查询有奖金的员工人数12 select count(comm) from emp;--查询字段的值的数量，null自动过滤13 --查询公司有多少工作种类14 select distinct job from emp;15 select count(distinct job)from emp;

【转换函数】

转换函数：Oracle的类型转换分为自动类型转换和强制类型转换。数据类型之间可以进行自动转换，仍建议使用显示转换函数，以保持良好的设计风格。

常用类型转换函数有：

--to_number(数值类型的字符)：将字符转换为数值

--to_char(数值或者是日期)：将数值或者日期转换为字符

--to_date(日期格式的字符)：将字符转换为日期

1 ---------------------数值和字符的互转--------------------- 2 --字符转换数字char --> number 3 select to_number('1999')+1 from dual; 4 --数字转换字符number --> cahr 5   --指定显示格式： 6     --9表示位置的占位：eg:999,999,999=>三位一组使用逗号隔开 7     --L表示人民币符号 8     --$表示美元符号 9     --0也可以占位分组，但若真实数据位数不足，会使用0进行补位10 select to_char(987654321,'￥999,999,999')from dual;11 select to_char(123456789,'$999,999,999')from dual;12 select to_char(123456789,'000,000,000,000.000')from dual;13 --查询工资大于2000的员工信息14   --数值和字符之间的转换可以隐式转换.to_number可缺省15 select * from emp where sal>'2000'--存在隐式自动转换 to_number('2000')16 select '154'+2 from dual;

1 ---------------------日期和字符的互转--------------------- 2 --字符转换日期 cahr-->date 3  --查询员工入职日期在82年之后的信息 4  --使用to_date('要转的字符','日期格式')函数将字符转换为日期 5    --注意1：字符必须符合日期格式 6    --注意2：Oracle默认的转换格式为日月年,'01-1月-2018' 7    --常用日期格式：yyyy-mm-dd     yyyy/mm/dd 8 select * from emp where to_char(hiredate,'yyyy-mm-dd')>'1982-01-01'; 9 select * from emp where hiredate>to_date('1982-01-01','yyyy-mm-dd');10 select * from emp where hiredate>to_date('1982-01-01','yyyy/mm/dd');11 --日期转换字符 date-->char12   --使用to_char('要转的日期','转换格式')13   --注意1：不指定转换格式，使用默认格式，日月年：'01-01月-18'14 select to_char(hiredate) from emp;--默认格式15 --指定格式16 select to_char(hiredate,'yyyy-mm-dd') from emp;17 select to_char(hiredate,' yyyy"年" mm"月" dd"日" ') from emp;

【其他函数】

1 --查询员工的薪水信息：如果奖金等于null，就返回0；不等于0，sal+comm 在进行计算相加2 select ename,sal,comm,sal+nvl(comm,0) as "工资+奖金" from emp;3 select ename, sal, comm, nvl2(comm, sal+comm, sal) as "工资+奖金" from emp;

--decode (value, key0,value0,key1,value 1,..... ,valuen)

--参数的意思，如果 value 的值 是 key0？ 整个函数返回 value0 ，如果值是key1就返回value1，以此类推，如果都没有找到合适，最后返回 valuen。

--decode(字段名,值1,处理1,值2,处理2,值3,处理3,...公共处理)

--如果字段的值和decode中的条件相同则执行对象的处理，都不是就则执行公共处理

1 --将emp 表中所有的员工的名字 工作 以及工作中文显示2 select ename, job, decode(job,'CLERK','职员','SALESMAN','销售','PRESIDENT','主席','MANAGER','经理','ANALYST','分析师') 中文职业 from emp;3 --职位除了：(job,'SALESMAN','销售','PRESIDENT','主席','MANAGER','经理','ANALYST','分析师')其他都是职员4 --处理大量的其他使用：比如：几万数据，除了职称为管理员和老师之外的其他都是学生5 select ename, job, decode(job,'SALESMAN','销售','PRESIDENT','主席','MANAGER','经理','ANALYST','分析师','职员') 中文职业 from emp;