Oracle的函数与SQL高级查询

1、Oracle函数与高级查询二 Oracle的函数2.1 什么是函数函数：指对一堆逻辑的打包，能够支持传入的参数，根据参数得到一定的结果；那么,在oracle中，都有什么函数，有什么类型的函数，函数有什么分类？2.2 函数的分类2.2.1 单行与多行除了聚合(组)函数，其它全是单行函数Oracle的函数咱们可以简单分成再种：多行函数与单行函数，如下图:函数单行函数多行函数2.2.1.1 什么是单行函数 简单理解：对单行数据进行是操作，得到单行数据 ; 比如concat连接函数concat 连接函数得到的效果; 使用concat 不会影响行数，以前100行，使用concat之后得到的结果也是100

2、行。单行函数 ：在执行获得结果后。可以支持传入参数【列和表达式】，针对每一行单独做运算。最终不会影响结果的行数。单行函数特点:1接收参数返回一个值2对每一行返回行起作用3每一行返回一个结果4可以修改数据类型 5可以使用嵌套 concat(concat(col1,col2),col2) 除了concat 这样的单行函数以外，还有其他的单行函数，比如字符处理函数；比如有时要求用户名全部大写或者小写，或者需要得到一个人的姓，这些都需要字符函数进行处理，字符函数中，也存在分类2.2.1.2 什么是多行函数多行函数：简单理解对多行数据进行操作，最后返回一个数据比如count函数：Count 针对过滤后的

3、结果 支持传入表的列名或者常量，对所有行的记录统一处理。最终只会得到一个结果,会影响结果行数;多行函数 : 在执行获得结果后。可以支持传入参数【列和表达式】，针对每一行整体统计做运算。最终只会产生1行数据咱们所说的单行与多行是指这个函数操作的数据是单行还是多行(不是说的结果)2.2.1.3 单行、多行分别用在哪？扩展理解与练习比如concat 用在哪里? 可以放在select 后，把它的姓和名连接起来；单行函数: select 关键字之后 where 之后的条件上 （where 之后单行函数 如下）姓名中有e或者a的员工?把姓名组合起来查询select * from employeeswher

4、e first_name | last_name like '%e%'or first_name | last_name like '%a%'使用函数Select * from employees Where concat(first_name,last_name) like %e%Or first_name | last_name like %a%;多行函数:针对Select 关键之后 ；比如count函数 统计所有行 ，放在select之后 使用多行函数 得到就一个结果，那我放在where 后面 有意义吗？Where 是针对每一行， 而多行函数 返回结果只有

5、一行，如果放在where 之后，执行一次，是一个结果，执行二次还是同一个结果，所以没有意义.2.2.2 函数功能分类可以分为：字符函数、数字函数，转换函数，日期函数，组函数，其它函数字符(串)函数：拼接字符串，截取字符串，全变大写，全变小写，首字母大写.数字(number)函数：四舍五入，舍掉 转换函数：字符-日期 字符-数字日期函数：拿到年，月，日，时，分，秒组函数：sum,count,max.其它函数：2.3 字符函数字符函数分为大小写转换函数与字符处理函数2.3.1 大小写转换函数大小写转换函数:Lower(列|表达式) 全部转换小写Upper(列|表达式) 全部转换大写Initcap(

6、列|表达式) 首字母大写练习案例：select 'Hello world' from dual;大写：select upper('Hello world') from dual;小写：select lower('Hello world') from dual;练习案例：1,查询first_name为randall的员工工资；分析：我们查询的时候不需要关心名称中的大小写问题select * from employees where lower(first_name) ='randall'练习案例：2,客户在输入名字的时候，可以不会在

7、意名称的大小写，比如，Wang Xiaoer，假设现在在发送邮件，需要按照正规的格式输出姓名；请使用SQL处理分析：我们可以插入一条小写的名字，然后使用首字母大写查询出来，格式更完整insert into employees (employee_id,first_name,last_name,email,phone_number,hire_date,job_id) values (300,'wang','xiaoer','xxx','1895236541',sysdate,'SH_CLERK')查询： select

8、 initcap(first_name)|' '|initcap(last_name) from employees;2.3.2 字符处理函数1、CONCAT(strexp, strexp): 连接两个字符串 2、Substr(str,start_index,length) :从指定的位置截取指定长度的字符串 3、LENGTH(strexp):返回字符串的长度 LENGTHB(strexp):返回字节的长度4、LPAD( string1, padded_length, pad_string ) 在列的左边粘贴字符 其中string1是需要粘贴字符的字符串 * padded_le

9、ngth是返回的字符串的数量，如果这个数量比原字符串的长度要短，lpad函数将会把字符串截取成padded_length; * pad_string是个可选参数，这个字符串是要粘贴到string1的左边，如果这个参数未写，lpad函数将会在string1的左边粘贴空格。 5、RPAD(粘贴字符) RPAD  在列的右边粘贴字符6、Trim():截取字符串两端特殊字符 Trim('s' from 'string');可以指定从字符串两边要截取的特殊字符2.3.2.1 CONCAT(strexp, strexp):连接两个字符串注意：在Oracle中只能连

10、接两个字符串(MySQL中可以任何加字符串) 这里可以使用函数嵌套的方式错误方式：select concat('123','456',789) from dualORA-00909: 参数个数无效正确方式(嵌套)：select concat(concat('123','456'),'789') from dual;2.3.2.2 Substr(str,start_index,length) 从指定的位置截取指定长度的字符串Str:的字符串start_index：从第几个开始(第1开始计算)如果是0，和1效果是一样的

11、 如果是负数，从倒数第几个字符开始Length：截取几个字母(长度)案例一：select substr('123456789',0,4) from dual;结果：1234案例二：select substr('123456789',1,4) from dual;结果：1234上面两个例子大家看到，两个结果都是1234，在SQL中很多参数是应该从1开始计算的，而且从1开始计算也更加符合我们的计算，因此建议大家从1开始计算。比如，我们拿到从4到7的数：案例三：select substr('123456789',4,4) from dual;结果：45

12、672.3.2.3 LENGTH(strexp)返回字符串的长度案例一：select length('admin') from dual; ->结果：5select length('中国') from dual; ->结果2还有一个针对字节的LENGTHB(strexp):返回字节的长度案例二：select lengthb('admin') from dual; ->结果：5select lengthb('中国') from dual; ->结果4练习案例：隐藏部分员工姓名，例如Julia Dellinge

13、r取前三位Jul加最后三位ger，中间补5位'*'，得到Jul*ger分析：拿到前三位，拿到后三位，中间补齐select subStr(first_name,1,3)|'*'|subStr(last_name,-3) from employees注意：起始位置是可以写负数的(从负几开始)思考： select subStr('123456789',-5,3) from dual; 值是多少现在问大家一个问题，如果我们要在中间加50个*，那怎么做？如果自己去写的话，实在是太麻烦了，而且容易写错。因此，真有这个需求的话，可以用到下一个函数：LPAD2.

14、3.2.4 LPAD( string1, padded_length, pad_string ) 在列的左边粘贴字符 其中string1是需要粘贴字符的字符串 * padded_length是返回的字符串的数量，如果这个数量比原字符串的长度要短，lpad函数将会把字符串截取成padded_length; * pad_string是个可选参数，这个字符串是要粘贴到string1的左边，如果这个参数未写，lpad函数将会在string1的左边粘贴空格。 案例一：结果：加上Lap的结果在前面多了一些空格，而且是加上hello刚好10个字符案例二：select Lpad('hello'

15、,10,'*') from dual;结果：*hello案例三：完成前面提出的50个*select subStr(first_name,1,3)|Lpad('*',50,'*')|subStr(last_name,-3) from employees注意：第一个参数不能为空2.3.2.5 RPAD(粘贴字符) RPAD  在列的右边粘贴字符 这个效果和LPAD一样，只是字符加在右边2.3.3.6 Trim():截取字符串两端特殊字符(默认截取空格)Trim('s' from 'string');可以指定从

16、字符串两边要截取的特殊字符案例一：select trim(' hello world ') from dual;结果：hello world案例二：select trim('-' from '-hello world-') from dual;结果：hello world2.4 数字函数Round：传回一个数值,该数值是按照指定的小数位数进行四舍五入运算的结果。TRUNC函数返回处理后的数值，其工作机制与ROUND函数极为类似，只是该函数不对指定小数前或后的部分做相应舍入选择处理，而统统截去mod（number1，number2）两个数值相除并返

17、回其余数。运算符执行 number1 除以 number2 操作。2.4.1 Round传回一个数值,该数值是按照指定的小数位数进行四舍五入运算的结果。语法：ROUND( number, decimal_places )number : 需四舍五入处理的数值，decimal_places : 四舍五入 , 小数取几位 ( 预设为 0 )案例：ROUND(45.926, 2) 45.93案例一：select round(445.3434,2) from dual; -> 445.34案例二：select round(445.3434,-2) from dual; -> 4002.4.

18、2 TRUNC函数返回处理后的数值，其工作机制与ROUND函数极为类似，只是该函数不对指定小数前或后的部分做相应舍入选择处理，而统统截去。语法：TRUNC( number, decimal_places )number : 要截取的数值，decimal_places : 小数取几位 ( 预设为 0 )案例：TRUNC(45.926, 2) 45.92案例：select TRUNC(445.3484,2) from dual; -> 445.342.4.3 mod（number1，number2）两个数值相除并返回其余数。运算符执行 number1 除以 number2 操作。案例一：se

19、lect mod(100,5) from dual; -> 0案例二：select mod(100,6) from dual; -> 42.4.4 数字函数练习题作业题：在POS系统中，都会有这样一个功能，在系统中可以设置找零的方式，比如常见的有下面几种：1，角以下四舍五入2，元以下四舍五入3，不管分4，不管角5，不管元请思考实现这些功能的方法2.5 转换函数转换函数分两种：隐式与显示转换函数：数字与字符之间的转换/日期与字符之间的转换2.5.1 隐式转换看下面的案例理解：select '555' | 6 from dual; 5556select '555

20、'+6 from dual; 561必需知道内部的机制才能知道最后的结果。咱们并不推荐使用这种方式。注：虽然可以使用隐式数据类型转换，但是还是建议执行显式的数据类型转换，以确保SQL语句的可靠性。2.5.2 显示函数转换从下图咱们可以看到 NUMBER(数字)，CHARACTER(字符串)，DATE(日期)是可以相互转换的TO_NUMBER : 字符串转成数字TO_CHAR:数字转换成字符串/日期转换成字符串TO_DATE:字符串转换成日期2.5.1 数字与字符的转换2.5.1.1 to_charA 数字转换成字符串语法：to_char(number,fmt) 大家应该可以看得出来，前

21、面是number,后面是格式。Number很好理解，那么格式到底是怎么回事呢？请看下图：数字格式控制符描述9代表一位数字，如果当前位有数字，显示数字，否则不显示(小数部分仍然会强制显示0）0强制显示该位，如果当前位有数字，显示数字，否则显示0$增加美元符号显示L增加本地货币符号显示.小数点符号,千分位符号  3,000,000,000.00咱们例出了下面几个案例来帮助理解：案例一：把一个110.110 变成一个字符串select to_char(110.110) from dual; 这个得到的结果不正确的，因为默认转换直接把最后一位省去了select to_char(11

22、0.110,'999.999') from dual; 9在这里代表的就是占位，并且小数点强制显示案例二：把把一个110.110 变成一000110.110select to_char(110.110,'000999.999') from dual; 0也代表的就是占位，并且所有数字强制显示从上面两个案例中咱们可以想到，9肯定比0的情况要用得多一点。那么0可能会用到什么情况下呢？ 这就需要我们把0与下面的千分位符号结合进来使用：案例二：财务看千分位的工资等(财务看这个就很习惯了)select to_char(110.110,'000,000.000

23、9;) from dual;结果： 000,110.110下面再来研究$与L：使用美元打印用户工资select first_name,to_char(salary,'$999999.99') from employees;使用本地货币符号打印工资select first_name,to_char(salary,'L999999.99') from employees;使用财务习惯打印工资select first_name,to_char(salary,'L000,000.00') from employees;2.5.1.2 to_number使

24、用to_number函数将字符转换为数字语法：to_number(char , 'fmt')案例一：把￥3100.00 转回成数字select to_number('￥3100.00','L9999.99') from dual;这个的转换正好是和to_char是相应的。格式设定一样。2.5.2 日期与字符的转换下面是转换的格式：格式控制符描述YYYY  YYY YY以数字表示全年(分别代表4位、三位、两位)的数字年YEAR年的拼写MM(mm)两位数字月MONTH月的全拼MON月名称的缩写DD数字日DAY星期的全拼D

25、 星期中的第几天 DY表示三位缩写的星期格式控制符描述HH，HH12一天中的第几个小时，12进制表示法 HH24一天中的第几个小时，取值为0023 MI一小时中的分钟SS一分钟中的秒AM显示上午或下午2.5.2.1 TO_CHAR 函数操作日期TO_CHAR(date, fmt) ：是字符类型的函数，转化日期为字符格式(fmt)1、必须用单引号括起来,并且是大小写敏感2、可包含任何有效的日期格式3、fmt值的宽度正好能容纳所有的有效数字 案例一：拿到日期的年Select to_char(sysdate,'YYYY') from dual;

26、Select to_char(sysdate,'Year') from dual;其它：咱们可以把上述表中的效果都显示查看一下：1999年入职的员工.案例二：查询出1999/10/15入职的员工select * from employees where to_char(HIRE_DATE,'YYYY-MM-dd')='1999-10-15'案例三：打印出"xxxx年xx月xx日 xx:xx:xx"格式的日期和时间 Select to_char(sysdate,'yyyy"年"mm"月&qu

27、ot;dd"日"') from dual注：如果添加中文，需要在中文上使用双引号包裹案例四：查询出1995年3月至2000年8月入职的员工select * from employees where to_char(hire_date,'YYYY-MM') between '1995-03' and '2000-06'刚才咱们讲的都是将日期转换成字符串，大家想一下，怎么把字符串转成日期呢？select to_date('2005-05-12 18','yyyy-MM-dd hh24') f

28、rom dual;自己反推一下即可。2.6 日期函数add_months(date_value,number_of_months):用于从一个日期值增加或减少一些月份select add_months(sysdate,12) "明年今日" from dual;select add_months(sysdate,-12) "去年今日" from dual;current_date:返回当前会话时区中的当前日期select sessiontimezone,current_date from dual;alter session set time_zone=&

29、#39;-11:00'/修改当前会话时区extract(date_field from datetime_value):找出日期或间隔值的字段值select extract(month from sysdate) "当前月份" from dual;last_day(date_value):返回指定日期中的月份的最后一天的日期select last_day('2000-02-01') "润月" from dual;next_day( date, weekday ):返回指定时间的下一个星期的指定星期对应的日期select next_

30、day(sysdate,'星期一') from dual; 如果今天是星期三，下一个星期四就是明天months_between(f,s) 日期f和s间相差月数select months_between(sysdate,'04-5月-09')from dual;2.7 其它函数1 NVL函数NVL(expr1,expr2)将空值转换为替换的值 支持多种数据格式可以是日期,字符,数字 NVL的两个参数数据类型必须匹配 ,否则出错。NVL(comm,0)NVL(hiredate,'01-JAN-97') NVL(job,'No Job Yet&

31、#39;)select NVL(null,'01-JAN-97') from dual案例：查询员工所有工资2 NVL2函数NVL2(expr0,expr1,expr2)如果expr0不为Null,返回expr1, 为Null,返回expr2. expr0可以为任何数据类型研究下面代码的结果：SELECT last_name, salary, commission_pct, NVL2(commission_pct, 'SAL+COMM', 'SAL') incomeFROM employees WHERE department_id IN (50

32、, 80);结果是： 如果这个人有奖金点，那就他的工资就是工资+奖金 如果这个人没有奖金点，那就他的工资就没有奖金3 NULLIFNULLIF(expr1,expr2)比较两个表达式，如果相等返回空值，如果不等返回第一个表达式。研究下面代码的结果：SELECT first_name, LENGTH(first_name) "expr1", last_name, LENGTH(last_name) "expr2", NULLIF(LENGTH(first_name), LENGTH(last_name) resultFROM employees;两个名称长

33、度相等，返回空，两个名称不相等 ，返回第一个名称的长度4 COALESCECOALESCE(expr1,expr2,.,exprn)返回表达式列表里的第一个非空表达式的值，表达式列表可以包含很多表达式,当第一个满足值非空时，返回这个表达式的值。 expr1,expr2,.,exprn的数据类型必须一致研究下面代码的结果：SELECT last_name, COALESCE(commission_pct, salary, 10) commFROM employeesORDER BY commission_pct;如果奖金点存在，返回奖金点如果奖金点不存在，返回工资如果工资都不存在，给10块钱以后

34、做开发有这么一个要求：用户有几个电话号码字段，咋们要拿到其中一个有值的公司电话，手机，家里座机，手机2.Select coalesce(workphone,mobile,homephone) from employee5 CASE表达式实现逻辑的IF-THEN-ELSECASE expr WHEN comparison_expr1 THEN return_expr1 WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_exprEND案例分析：SELECT last_n

35、ame, case job_id when '总经理' then 1.15*salary when '副总经理' then 1.20*salary else salary end 工资 FROM employees; 练习题：打印公司员工工资等级：5000以下D；5000-10000C;10000-15000B;15000以上Aselect last_name,salary, case trunc(nvl(salary,0)/5000) when 0 then 'D' when 1 then 'C' when 2 then 

36、9;B' else 'A' end from employees6 DECODE函数 DECODE函数类似于一系列CASE 或 IF-THEN-ELSE 语句 DECODE(col/expression, search1, result1 , search2, result2,., , default)案例分析： SELECT last_name,salary, decode(job_id, '总经理',1.15*salary, '副总经理',1.10*salary,salary) 工资 FROM employees; 练习题：打印公司员

37、工工资等级：5000以下D；5000-10000C;10000-15000B;15000以上A select last_name,salary, decode(trunc(salary/5000), 0,'D', 1,'C', 2,'B','A') from employees2.8 分组函数分组函数 就我们所说的多行函数 我们看一下 max . 求员工表中工资的最高值，就相当于我们把整个公司的员工分了一个组 求最大值查询公司员工工资的总和，工资的最大值，最小值，平均值2.8.1 常用分组函数(通用)AVG (DISTINCT|A

38、LLn)COUNT ( *|DISTINCT|ALLexpr)MAX (DISTINCT|ALLexpr)MIN (DISTINCT|ALLexpr)SUM (DISTINCT|ALLn)1,查询出公司所有员工的平均工资2,计算公司平均奖金点(即提成比)注意：空值不参与运算这里咱们计算平均提成，可以看到差距是很大的 select avg(commission_pct),sum(commission_pct)/count(employee_id) from employees;正确的统计方式：select avg(nvl(commission_pct,0) from employees;2.8.

39、2 分组分析先分组再统计：查询出每一个部门的最高工资select max(salary),department_id from employees group by department_id查询出每一个部门的平均工资select avg(salary),department_id from employees group by department_id查询平均工资高于8000的部门和其平均工资select avg(salary),department_id from employees group by department_idhaving avg(salary)>8000分析练习题：查询在95，96，97，98年各进公司多少人select count(employee_id),to_char(hire_date,'yy') from employees group by to_char(hire_date,'yy'