--**************************************************************************************
--字符函数
--大小写转换函数
--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-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
--输入当前的系统日期,格式为 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-dd
select to_char(sysdate,'yyyy-mon-dd') from dual
--输入当前的系统日期,格式为 YYYY-MON-DD
select 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
--*****************************************************************************************************************
--分组的语法结构
SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];
--查询每个部门工资的最大值,最小值
需求:输出结果如下
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子句对分组的结果进行限制
SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];
--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