SQL函数
单行函数 对一行进行操作
多行函数 对很多行同时操作
Function_name（参数1，参数2，参数3，…），函数可以嵌套
1、字符串函数
大小写变换的函数，lower，upper，initcap
1、lower 把所有字母都变成小写
select t.employeeid,t.empfirstname,t.emplastname from employees t
select t.employeeid,lower(t.empfirstname),t.emplastname from employees t
select t.employeeid,t.empfirstname,t.emplastname from employees t where t.empfirstname = 'ann'
为什么查询不到
2、upper 把所有字母都转化为大写字母
select t.employeeid,lower(t.empfirstname),upper(t.emplastname) from employees t
3、initcap 把所有单词的第一个字母大写
update employees t set t.empfirstname = 'mary ann john' where t.employeeid = 702
select t.employeeid,initcap(t.empfirstname) from employees t
字符串操作函数
4、拼接函数 concat
Concat（str1,str2）把两个字符串连接起来
select t.employeeid,concat(t.empfirstname,t.emplastname) as full_name from employees t
5、substr 函数
substr(STR1，2，5) 从字符串str1中的第二个字符开始，数5个
select substr('abc def hig',3,5) from dual;
select t.empstreetaddress,substr(t.empstreetaddress,5,5) from employees t where t.employeeid = 701
函数可以嵌套
select t.employeeid,substr(concat(t.empfirstname,t.emplastname),3,5) as sub_name from employees t
6、length 函数，返回字符串的长度
select t.empfirstname,length(t.empfirstname) from employees t
7、instr 函数，在一个字符串里定位子字符串的位置
select e.empfirstname,instr(e.empfirstname,'o') from employees e where e.employeeid =703
Instr（’HELLOWORLD’,W） 返回 6，w首次在字符串中的位置为6
select instr('helloworld','y') from dual;
4、查询出雇员中first名字中不含有’n’的所有员工
select * from employees e where instr(e.empfirstname,'n')=0;
select * from employees e where e.employeeid
not in(select e.employeeid from employees e where e.empfirstname like '%n%');
8、LPAD 函数，左填充，用于输出做整齐
Lpad(‘abcde’,10,’*’) 输出*****abcde
select lpad(t.empfirstname,6,'*'),length(t.empfirstname) from employees t
select e.empfirstname,rpad(e.empfirstname,10,'*') from employees e
9、RPAD 函数，右填充，用于输出做整齐
10、REPLACE函数,替换函数
select Replace('ABCDER','D','WWW') from dual;
Replace(‘ABCDER’，’D’,’WWW’) 输出 ABCWWWER
update customers c set c.phone =replace(c.phone,'800','400');
select t.empfirstname,replace(t.empfirstname,'m','www') from employees t where t.employeeid =703
select e.emplastname,replace(e.emplastname,'t','AAAAA') from employees e where e.employeeid =701
11、TRIM函数,切掉字符串的一个字符，消减的意思
--ltrim(str,trim_str)从str中的左边截去一些字符，直到str中有字符不出现在trim_str中为止
--rtrim(str,trim_str)从str中的右边截去一些字符，直到str中有字符不出现在trim_str中为止
--trim(leading|trailing|both y from x)从x中截去单个字符y
select trim(both 'h' from 'hhhelloworlh') from dual;
select trim('e' from trim('h' from 'helloworld')) from dual;
select ltrim('ellhworl', 'elloworl') from dual;
练习
用一条sql语句查询。
满足要求如下：
1、从雇员表employees中查询雇员ID号为703的员工。
表中显示的字段有：
雇员ID号，
合并first与lastname后取别名为full_name，
显示lastname的长度，
显示字母’a’在firstname中的位置
显示雇员所在的城市empcity（去掉城市前2个字母）
2、查询出雇员表中，empfirstname的长度为4的所有员工
3、查询出雇员表中empfirstname的长度为4，或者emplastname的程度为6的所有员工信息。
4、查询出雇员中first名字中不含有’n’的所有员工
5、显示所有雇员的姓名，并且用’A’代替’a’
1、
select t.employeeid,concat(t.empfirstname,t.emplastname) full_name,instr(t.empfirstname,'a'),length(t.emplastname),
substr(e.empcity,3,length(e.empcity))
from employees t where t.employeeid = 703
3、
select * from employees e where length(e.empfirstname)=4 or length(e.emplastname)=6
4
select * from employees e where instr(e.empfirstname,'n')=0
5、
select replace(e.empfirstname,'a','A') from employees e
2、数字函数
12、round 函数，四舍五入
select round(45.926,2) from dual;
select round(45.9269,0) from dual;
select round(45.926,-1) from dual;
SELECT round(5.75), round(5.75, 1), round(15.75, -1) FROM dual;
13、trunc 函数，参数是保留几位小数，直接砍掉，不四舍五入
SELECT trunc(5.75), trunc(5.76, 1), trunc(15.75, -1) FROM dual;
14、mod 函数，取余数
select mod(1600,300) from dual;
15、abs 函数，返回绝对值
select t.quotedprice -100 from order_details t where t.ordernumber =354 and t.productnumber = 13
16、sign(x)返回x的符号。若x<0，返回-1;若x=0，返回0；若x>0，返回1
select sign(5),sign(-1),sign(0) from dual;
17、floor(x)返回小于或等于x的最大整数
select floor(5.8),floor(-5.6) from dual;
18、ceil(x)返回大于或等于x的最小整数
select ceil(5.8),ceil(-5.6) from dual;
19、power(x,y)返回x的y次幂
select power(2,3) from dual;
3、日期函数
20、sysdate 返回系统当前时间
select sysdate from dual;
21、add_months(x,y)
返回x加上y个月后的结果，若y为负值，则从x中减去y个月
select * from customers;
---如果顾客表的出生年月为空，就给默认值 1970-10-10
update customers c set c.dob =date '1970-10-10' where c.dob is null;
---修改顾客表中的出生年月，让出生年月都加10年。
update customers c set c.dob = add_months(c.dob,120);
22、last_day(x)返回包含x月的最后一天
select t.orderdate,last_day(t.orderdate) from orders t where t.ordernumber =884
23、next_day(x,day)返回从x开始，下一个day的时间值
SELECT next_day(sysdate,'星期一') FROM dual;
SELECT next_day(sysdate,2) FROM dual;
24、months_between(x,y) x-y
返回x和y之间有几个月，如果x比y晚，就返回正数，否则为负数
select months_between(sysdate +1000,sysdate) from dual;
select months_between(sysdate,sysdate+1000) from dual;
25、round（date，month/year）
Month 1-15日算上一个月，15日后算下一个月
Year 1-6月算上一年，7-12月算下一年
select round(date'2014-7-16','month') from dual;
select round(date'2014-7-16','year') from dual;
26、trunc 函数，截掉
select trunc(date'2014-7-16','month') from dual;
select trunc(date'2014-7-16','year') from dual;
4、转换函数
批量替换时间
select * from orders;
update orders od
set od.orderdate = od.orderdate + interval'18'year,
od.shipdate = od.shipdate + interval'18'year;
-----------------------------------------------------------------------
update orders o set o.orderdate =to_date((replace(to_char(o.orderdate,'yyyy/mm/dd'),'1999','2016')),'yyyy-mm-dd')
where o.ordernumber=1;
27、 to_char(date/number,format) 函数
把date或者number按照一定的格式转换为字符串
/*常用format参数
YYYY四位年份
MM二位月份
MONTH月份的全拼，全部大写
MON月份的前3个字母
WW本年中的第几周
W本月中的第几周
DD本月中的第几天
D本周中的第几天
DAY周几的全名，全部大写
HH24 24小时格式小时数
HH 12小时格式小时数
MI分钟
SS秒数
SELECT to_char(sysdate, 'yy/mm/dd') FROM dual;
SELECT to_char(sysdate, 'yy/mm/dd hh:mi:ss') FROM dual;
SELECT to_char(123.456) FROM dual;
SELECT to_char(12345.67, '99,999.99') FROM dual;
SELECT to_char(12345.67, '$99,999.99') FROM dual;
转换不了
SELECT to_char(123451.67, '$99,999.99') FROM dual;
28、to_number 函数
to_number(x,format)用于将x转换为一个数字。可以用format指定格式
SELECT to_number('123.456') FROM dual;
SELECT to_number('-$12,345.67', '$99,999.99') FROM dual;
29、to_date 将字符串转换为时间值
SELECT to_date('5月 2,2006', 'month dd,yyyy') FROM dual;
SELECT to_date('20120808','yyyy-mm-dd') FROM dual;
select date'2014-7-12' from dual;
select add_months((to_date('2014-7-12','yyyy-mm-dd')), 3) FROM dual;
30、translate()函数
translate(x,from_str,to_str)函数在x中查找from_str中的字符，并将其转换成to_str中对应的字符
SELECT translate('I am Chinese, I love China', 'China', '中国') "Translate example"
FROM dual;
SELECT translate('我是中国人,我爱中国', '中国', 'China') "Translate example"
FROM dual;
5、通用函数
If –then–else 逻辑
31、 nvl(value,str)函数
如果第一个参数不是null，返回的就是第一个参数，如果第一个为null，就返回第二个参数
update employees t set t.empcity ='' where t.employeeid = 708
select nvl(t.empcity,'empcity is null') from employees t
32、 nvl2(value,X,Y)函数
如果value非空，则返回X,为空就返回Y
select nvl2(t.empcity,'beijing','empcity is null') from employees t
33、 nullif(X,Y)
如果X与Y相等，则返回null，不等则返回X值
select nullif(10,20) from dual;
比较复杂的sql语句，可以使用case 与decode
34、 case表达式
Case国际sql通用支持的，使用case可移植更好。
相当于在SQL中执行if语句
CASE 可用于允许使用有效表达式的任意语句或子句。
例如，可以在 SELECT、UPDATE、DELETE 和 SET 等语句以及 select_list、IN、WHERE、ORDER BY 和 HAVING 等子句中使用 CASE。
--case简单表达式:
CASE input_expression
WHEN when_expression1 THEN result_expression1
WHEN when_expression2 THEN result_expression2
...
[ ELSE else_result_expression ]
END
select t.product_type_id,
case t.product_type_id
when 1 then t.product_type_id * 10
when 2 then t.product_type_id * 10
when 3 then t.product_type_id * 10
else
t.product_type_id * 100
end
as new_id
from product_types t
store
select t.categoryid,
case t.categoryid
when 1 then （select 'aaaa' from dual）
when 2 then 'bbb'
when 3 then 'ccc'
else 'ddd'
end
as new_id
from categories t;
---产品id，产品类型名，产品名，产品价格
select p.product_id,
case p.product_type_id
when 1 then 'Book'
when 2 then 'Video'
when 3 then 'DVD'
when 4 then 'CD'
else 'evd'
end
as "产品类型名",p.name,p.price
from products p;
---产品id，产品类型名，产品名，产品价格
select p.product_id,
case p.product_type_id
when 1 then 'Book'
when 2 then 'Video'
when 3 then 'DVD'
when 4 then 'CD'
else 'Magazine'
end as type_name,
p.name,p.price from products p;
select p.product_id,
case p.product_type_id
when 1 then (select pt.name from product_types pt where pt.product_type_id=1)
when 2 then (select pt.name from product_types pt where pt.product_type_id=2)
when 3 then (select pt.name from product_types pt where pt.product_type_id=3)
when 4 then (select pt.name from product_types pt where pt.product_type_id=4)
else (select pt.name from product_types pt where pt.product_type_id=5)
end as type_name,
p.name,p.price from products p;
select p.product_id,
case p.product_type_id
when p.product_type_id then (select pt.name from product_types pt where pt.product_type_id=p.product_type_id)
else
'没有类型名'
end as type_name,
p.name,p.price from products p;
select * from employees;
update employees e set e.first_name =
case e.employee_id
when 1 then 'aaa'
when 2 then 'bbb'
when 3 then 'ccc'
else 'ddd'
end;
35、decode 函数 ，oracle特有的，比较简洁
decode(value,search_value,result,default_value)对value与search_value进行比较。如果这两个值相等
则返回result，否则返回default_value
SELECT DECODE(1,1,2,3) FROM dual;
select t.product_type_id,
decode(t.product_type_id,
1,t.product_type_id * 10,
2,t.product_type_id * 10,
3,t.product_type_id * 10,
t.product_type_id*100) as new_id
from product_types t;
查询雇员表的雇员ID与雇员薪水
雇员薪水的显示规则如下
如果雇员ID 为1的时候，薪资增加2倍
如果雇员ID 为2的时候，薪资增加3倍
如果雇员ID 为3的时候，薪资增加4倍
其他增加5倍
select e.employee_id,
case e.employee_id
when 1 then e.salary /100
when 2 then e.salary /100
when 3 then e.salary /100
else
e.salary /1000
end
as new_id
from employees e
select pt.product_type_id,
decode(pt.product_type_id,1,pt.product_type_id*10,pt.product_type_id*100) a1,
decode(pt.product_type_id,2,pt.product_type_id*10,pt.product_type_id*100) a2,
decode(pt.product_type_id,3,pt.product_type_id*10,pt.product_type_id*100) a3,
decode(pt.product_type_id,4,pt.product_type_id*10,pt.product_type_id*100) a4
from product_types pt;