--**************************************************************************************--字符函数--大小写转换函数--LOWER (strexp) 返回字符串,并将所有的字符小写. --'SQL Course'转化为小写 select lower('SQL Course') from dual --UPPER (strexp) 返回字符串,并将所有的字符大写. --'SQL Course'转化为大写 select UPPER('SQL Course') from dual --函数在数据库中的应用 select empno,lower(ename) from emp--INITCAP(strexp) 将字符串的(每个单词的)第一个字母变为大写,后面的小写; --处理'SQL Course'字符串 select initcap('sal course') from dual --CONCAT(strexp, strexp): 连接两个字符串 --连接'Good', 'String'两个字符串 select concat('Good','String') from dual --利用CONCAT函数输出员工的姓名(employees 表的first_name和last_name) select concat(first_name,last_name) from employees--substr(str,start_index,length) :从指定的位置截取指定长度的字符串 --截取 string 从1 开始 截取 3 个 select substr('string',1,3) from dual;--LENGTH(strexp):返回字符串的长度 --测试'String'的长度 select length('String') from dual --LPAD( string1, padded_length, [ pad_string ] ) 在列的左边粘贴字符 * string1是需要粘贴字符的字符串 . * padded_length是返回的字符串的数量，如果这个数量比原字符串的长度要短， lpad函数将会把字符串截取成padded_length; .\ * pad_string是个可选参数，这个字符串是要粘贴到string1的左边，如果这个参 数未写，lpad函数将会在string1的左边粘贴空格。 --LPAD(sal,10,'*') select lpad('aaa',6,'*') from dual select lpad('abcde',6,'*') from dual select lpad('abcdeefghjk',6,'*') from dual --RPAD(粘贴字符) RPAD? 在列的右边粘贴字符 --RPAD(sal,10,'*') select rpad('aaa',6,'*') from dual select rpad('abcde',6,'*') from dual select rpad('abcdeefghjk',6,'*') from dual --trim():截取字符串两端特殊字符 --去掉‘stirng '两端的空格 select trim(' string ') from dual--replace(str,search_str[,replace_str]):将每次在str中出现的search_str用replace_str替换 --把'HELOVEYOU'中'HE'替换为'I select replace('HELOVEYOU','HE','I') from dual--**************************************************************************************--数学函数--round：传回一个数值，该数值是按照指定的小数位数进行四舍五入运算的结果。--ROUND( number, decimal_places ) --* number : 需四舍五入处理的数值， --* decimal_places : 四舍五入 , 小数取几位 ( 预设为 0 ) --例如数字 45.926 select round(45.926) from dual select round(45.926,0) from dual select round(45.926,2) from dual--TRUNC函数返回处理后的数值，其工作机制与ROUND函数极为类似，--只是该函数不对指定小数前或后的部分做相应舍入选择处理，而统统截去。 select trunc(45.926) from dual select trunc(45.926,0) from dual select trunc(45.926,2) from dual--mod（number1，number2）两个数值相除并返回其余数。运算符执行 number1 除以 number2 操作 --例如1600除以300 select mod(1600,300) from dual--**************************************************************************************--转化函数 --TO_CHAR(date, ‘fmt’) ：是字符类型的函数，转化日期为字符--格式(‘fmt’)--1、必须用单引号括起来,并且是大小写敏感(对英文有效)--2、可包含任何有效的日期格式--3、fmt值的宽度正好能容纳所有的有效数字 --修改当前的语言环境为中文(只对当前的窗口有效) alter session set nls_language='SIMPLIFIED CHINESE'--输入当前的系统日期,格式为 yyyy-mm-dd select sysdate from dual select to_char(sysdate,'yyyy-mm-dd') from dual --输入当前的系统日期,格式为 yyyy-mon-dd select to_char(sysdate,'yyyy-mon-dd') from dual --2010-10月-18 --输入当前的系统日期,格式为 yyyy-month-ddselect to_char(sysdate,'yyyy-month-dd') from dual select to_char(sysdate,'YYYY-MONTH-DD') from dual --输入当前的系统日期,格式为 yyyy-mon-dd-dayselect to_char(sysdate,'yyyy-mon-dd-day') from dual --输入当前的系统日期,格式为 yyyy-mon-dd-dy select to_char(sysdate,'yyyy-mon-dd-dy') from dual --输入当前的系统日期,格式为 yyyy-mon-dd-dy-d select to_char(sysdate,'yyyy-mon-dd-dy-d') from dual --**********************************************************************************************************--修改为英文环境(此语句只对当前的窗口有效,当窗口关闭需重新设置)alter session set nls_language=AMERICAN;--输入当前的系统日期,格式为 yyyy-mm-dd select to_char(sysdate,'yyyy-mm-dd') from dual select to_char(sysdate,'YYYY-MM-DD') from dual --输入当前的系统日期,格式为 yyyy-mon-ddselect to_char(sysdate,'yyyy-mon-dd') from dual --输入当前的系统日期,格式为 YYYY-MON-DDselect to_char(sysdate,'yyyy-MON-dd') from dual --输入当前的系统日期,格式为 YYYY-MONTH-DD select to_char(sysdate,'yyyy-MONTH-dd') from dual select to_char(sysdate,'yyyy-month-dd') from dual --输入当前的系统日期,格式为 yyyy-mon-dd-day select to_char(sysdate,'yyyy-mon-dd-day') from dual select to_char(sysdate,'yyyy-mon-dd-DAY') from dual --输入当前的系统日期,格式为 yyyy-mon-dd-dy select to_char(sysdate,'yyyy-mon-dd-dy') from dual select to_char(sysdate,'yyyy-mon-dd-DY') from dual --********************************************************************************--ddspth 表示日期的英文显示(不区分语言环境)--输入当前的系统日期,格式为 yyyy-mon-ddspth select to_char(sysdate,'yyyy-mon-ddspth') from dual select to_char(sysdate,'yyyy-mon-DDSPTH') from dual --小时 分 秒--输入当前的系统日期,格式为 yyyy-mon-dd HH24:MI:SS select to_char(sysdate,'yyyy-mon-dd HH24:MI:SS') from dual ----输入当前的系统日期,格式为 dd-mon-yyyy SS:HH24:MI(格式没有顺序) select to_char(sysdate,'dd-mon-yyyy SS:HH24:MI') from dual --输入当前的系统日期,格式为 yyyy-mon-dd HH24:MI:SS AM(PM) AM和PM没有区别 alter session set nls_language='SIMPLIFIED CHINESE' select to_char(sysdate,'yyyy-mon-dd HH24:MI:SS AM') from dual select to_char(sysdate,'yyyy-mon-dd HH24:MI:SS PM') from dual --修改为英文环境(此语句只对当前的窗口有效,当窗口关闭需重新设置)alter session set nls_language=AMERICAN;--输出当前的系统日期,格式为 dd of month (19 of 1月)--可以在格式中增加字符串,字符串用""引起来 select to_char(sysdate,'dd "of" month') from dual--to_date(char,'fmt') 转化字符为日期，前面的char和后面的格式必须对应 --字符串 2010-4月-08 select to_date('2010-4月-08','yyyy-month-dd') from dual select to_date('2010-4月-08 星期四','yyyy-month-dd day') from dual --**********************************************************************************************************--TO_CHAR(number,'fmt')：是字符类型的函数，转化数字为字符--问题 转化3000.45这个数字为字符 --9 代表一位数字，如果当前位有数字，显示数字，否则不显示(小数部分仍然会强制显示） --使用9 输出如下格式 3000.4500 select to_char(3000.45,'99999999.9999') from dual --0 强制显示该位，如果当前位有数字，显示数字，否则显示0 --使用0 输出如下格式 0003000.4500 select to_char(3000.45,'0000000.0000') from dual select to_char(3000.45,'0009999.9999') from dual select to_char(3000.45,'0009999.0099') from dual --$ 增加美元符号显示 --增加美元符号 输出如下格式 $0003000.4500 select to_char(3000.45,'$0009999.0000') from dual --L 增加本地货币符号显示(RMB ) --增加本地货币符号 输出如下格式 ￥0003000.4500 select to_char(3000.45,'L0009999.0000') from dual --千分位符号 3,000,000,000.00 --增加千分位符号 1234567.45 --显示结果 1,234,567.4500 select to_char(1234567.45,'9,999,999.0000') from dual--使用to_number('字符'，格式) 使用TO_NUMBER函数将字符转换为数字 -- 0003000.4500 字符转化为数组 3000.45 select to_number('0003000.4500','0009999.9999') from dual -- $0003000.4500 字符转化为数字3000.45 select to_number('$0003000.4500','$0009999.9999') from dual --￥1,234,567.4500 转化为数字 select to_number('￥1,234,567.4500','L9,999,999.9999') from dual--*************************************************************************************************************--日期函数: --MONTHS_BETWEEN(date2,date1) --计算2008-4月-1日和2008-8月-10日相差多少个月 select months_between(to_date('2008-4月-01','yyyy-month-dd'),to_date('2008-8月-10','yyyy-month-dd')) from dual --Add_months(date1,number) --给出日期2008-4月-01 计算六个月后的日期 select add_months(to_date('2008-4月-01','yyyy-month-dd'),6) from dual --NEXT_DAY(date,'day') 给出日期date和星期x之后计算下一个星期的日期 -- 计算2008-4月-1日 星期二 select next_day(to_date('2008-4月-01','yyyy-month-dd'),'星期一') from dual --计算指定日期所在月份的最后一天的日期 --Last_day(date1) select last_day(to_date('2008-5月-01','yyyy-month-dd')) from dual--**************************************************************************************************--其他函数--NVL(expr1,expr2) 如果expr1为空,则输出expr2的值,如果expr1的值不为空 输出expr1的值 select nvl(null,'aaa') from dual select nvl('gggg','aaa') from dual --使用nvl函数实现:输出员工的姓名 月薪 提成 月收入 select first_name||last_name,salary,commission_pct,salary*(1+commission_pct) from employees select first_name||last_name,salary,commission_pct,salary*(1+nvl(commission_pct,0)) from employees--NVL2(expr0,expr1,expr2) --如果expr0不为Null,返回expr1, 为Null,返回expr2. --expr0可以为任何数据类型 select nvl2(null,'a','b') from dual select nvl2('c','a','b') from dual --使用nvl2函数实现:输出员工的姓名 月薪 提成 月收入 select first_name||last_name,salary,commission_pct, nvl2(commission_pct,salary*(1+commission_pct),salary) from employees--NULLIF(expr1,expr2)--比较两个表达式，如果相等返回空值，如果不等返回第一个表达式。--使用NULLIF函数实现:比较first_name last_name的长度 select first_name,last_name,length(first_name),length(last_name), nullif(length(first_name),length(last_name)) from employees-- 使用COALESCE函数 返回表达式列表里的第一个非空表达式的值，表达式列表可以包含很多表达式, --当第一个满足值非空时，返回这个表达式的值。 --expr1,expr2,...,exprn的数据类型必须一致 --输出first_name,last_name,job_id和三个字段中非空表达式的值 select first_name,last_name,job_id, coalesce(first_name,last_name,job_id) from employees --CASE表达式--语法结构: 实现逻辑的IF-THEN-ELSE CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr] END--需求:输出first_name,job_id,salary, 涨后的工资-- 如果是'人力经理' 工资涨1%-- 如果是'计划部经理' 工资涨2%-- 如果是'秘书' 工资涨3%-- 如果是'保安部经理' 工资涨4%-- 其他的涨 5%select first_name,salary,job_id,case job_id when '人力经理' then salary*1.01 when '计划部经理' then salary*1.02 when '秘书' then salary*1.03 when '保安部经理' then salary*1.04 else salary*1.05 end "涨后的工资" from employees --DECODE函数类似于一系列CASE 或 IF-THEN-ELSE 语句--语法: DECODE函数类似于一系列CASE 或 IF-THEN-ELSE 语句 DECODE(col/expression, search1, result1 [, search2, result2,...,] [, default]--需求:输出first_name,job_id,salary, 涨后的工资-- 如果是'人力经理' 工资涨1%-- 如果是'计划部经理' 工资涨2%-- 如果是'秘书' 工资涨3%-- 如果是'保安部经理' 工资涨4%-- 其他的涨 5%select first_name,salary,job_id,decode(job_id, '人力经理',salary*1.01, '计划部经理',salary*1.02, '秘书',salary*1.03, '保安部经理' ,salary*1.04, salary*1.05 ) "涨后的工资" from employees--**********************************************************************************************************--聚合函数 --在数字类型数据使用AVG and SUM 函数--AVG：计算平均值--SUM：计算总和--输出员工工资的总和,工资的平均值 select sum(salary),avg(salary) from employees--输出员工表10号部门员工工资的总和，工资的平均值 select sum(salary),avg(salary) from employees where department_id=10--MIN and MAX适用于任何数据类型 --MIN： 计算最小值--MAX：计算最大值--输出员工表中工资的最大值和最小值 select max(salary),min(salary) from employees--输出20号部门中工资的最大值和最小值select max(salary),min(salary) from employees where department_id=20--COUNT(*)返回表中所有符合条件的记录数--查询有多少员工 select count(*) from employees --COUNT(字段) 返回所有符合条件并且字段值非空的记录--带条件查询--查询10号部门有多少员工 select count(employee_id) from employees select count(employee_id) from employees where department_id=10 --count(distinct(expr))返回不重复的，非空值的数量--查询10号部门的工种数量 select count(distinct(job_id)) from employees where department_id=10 --分组函数在计算时省略列中的空值 --计算30号部门员工工资的平均工资 select avg(salary) from employees where department_id=30 --NVL函数迫使分组函数包括空值 --计算30号部门员工工资的平均工资 select avg(nvl(salary,0)) from employees where department_id=30--*****************************************************************************************************************--分组的语法结构 SELECTcolumn, group_function FROMtable [WHEREcondition] [GROUP BYgroup_by_expression] [ORDER BYcolumn]; --查询每个部门工资的最大值,最小值 需求:输出结果如下 department_id max(salary),min(salary) 10 5000 1000.00 20 6000 1000.00 30..... select department_id,max(salary),min(salary) from employees group by department_id order by department_id asc --使用groupby子句的注意事项 --1、出现在SELECT列表中的字段，如果出现的位置不是在组函数中，那么必须出现在GROUP BY子句中 select department_id,max(salary),min(salary) from employees group by department_id --2、在GROUP BY 子句中出现的字段，可以不出现在SELECT列表中 select max(salary),min(salary) from employees group by department_id --对多列分组 --一查询部门编号和工种并按 部门编号 和 工种分组 select department_id,job_id from employees group by department_id,job_id --不能在 WHERE 子句中限制组. --限制组必须使用 HAVING 子句. --不能在 WHERE 子句中使用组函数 语法结构: --使用HAVING子句对分组的结果进行限制 SELECTcolumn, group_function FROMtable [WHEREcondition] [GROUP BYgroup_by_expression] [HAVINGgroup_condition] [ORDER BYcolumn]; --select语句的执行流程 * 先执行where子句，对数据进行过滤 * 过滤后的数据再用group by子句分组 * 分组后的数据再用 HAVING子句进行组函数过滤 * 最后,对查询的数据分组 --按部门进行分组,输出部门id,部门的平均工资,要求平均工资>2000 select department_id,avg(salary) from employees group by department_id having avg(salary)>2000 order by department_id --按部门进行分组,输出部门id,部门的平均工资,要求平均工资>2000，并且部门不为null,并且不是10号部门 select department_id,avg(salary) from employees where department_id is not null and department_id<>10 group by department_id having avg(salary)>2000 order by department_id
