一、学习目标与SQL中函数的概念:
1、大小写控制函数:
(1)、lower(全小写)、upper(全大写)、initcap(单词中首字母大写)三个函数:
SQL> select lower('ATGUIGU.COM'),upper('helloworld.java'),initcap('AtGuiGu.Hello')
2 from dual;
结果:
LOWER('ATGUIGU.COM') UPPER('HELLOWORLD.JAVA') INITCAP('ATGUIGU.HELLO')
-------------------- ------------------------ -----------------------
atguigu.com HELLOWORLD.JAVA Atguigu.Hello
lower upper initcap
(2)、当数据表中不知道名字是大写还是小写时,可借助lower统一转为小写:
select *
from employees
where lower(last_name)='king' //还可以借助于upper
2、字符控制函数:
(1)、concat(连接)、substr(从第一个代表下标处,第二个代表之后几个)、length函数:
select concat('hello','world'),substr('helloworld',2,4),length('helloworld')
from dual
结果:
CONCAT('HELLO','WORLD') SUBSTR('HELLOWORLD',2,4) LENGTH('HELLOWORLD')
----------------------- ------------------------ --------------------
helloworld ello 10
(2)、instr(字符首次再字符串中出现的位置):
select instr('hello','o')
from dual
结果:
INSTR('HELLO','O')
------------------
5
(3)、lpad(一共几位,不足的位数在前面补字符…),rpad(不足的位数在后面补字符…):-1、
select employee_id,last_name,lpad(salary,10,'*')
from employees
结果:
EMPLOYEE_ID LAST_NAME LPAD(SALARY,10,'*')
----------- ------------------------- --------------------
204 Baer *****10000
205 Higgins *****12000
206 Gietz ******8300
-2、
select employee_id,last_name,rpad(salary,10,'*')
from employees
(4)、trim( ‘a’ from ‘b’):从字符串b中将首尾的字符串a去掉,而中间的保留:
select trim('h' from 'hellohhh worldhhh')
from dual
结果:
TRIM('H'FROM'HELLOHHHWORLDHHH')
-------------------------------
ellohhh world
(5)、replace(’a’,’b’,’c’):将a字符串中的b字符换为c字符
select replace('hello','o','a')
from dual
3、数字函数:
(1)、round( , )--四舍五入、trunc( , )--截断、mod( , )--求余
Round(435.45,2):435.45 round(435.45):435 round(435.45,-2):400
trunc(435.45,-1):430;
select round(435.3232,2),trunc(3.323,2),mod(1600,300)
from dual
结果:
ROUND(435.3232,2) TRUNC(3.323,2) MOD(1600,300)
----------------- -------------- -------------
435.32 3.32 100
4、日期函数(一):
sysdate:SQL中的获取系统当前日期的函数。
select sysdate,sysdate+1,sysdate-4
from dual
(1)、计算所有员工截止到日前为止,工作了多少天。
select employee_id,last_name, sysdate-hire_date worked_days //起别名
from employees
(2)、将天数取整(截断—使用trunc)。
select employee_id,last_name,trunc(sysdate-hire_date) worked_days
from employees
5、日期函数(二):
(1)、MONTHS_BETWEEN函数(两个日期相差的月数):
select employee_id,last_name,trunc(months_between(sysdate,hire_date))
from employees
(2)、ADD_MONTHS函数(向指定日期中加上若干个月):
select add_months(sysdate,5),add_months(sysdate,-4)
from dual
(3)、NEXT_DAY函数(指定日期的下一个星期几对应的日期):
select next_day(sysdate,'星期日') 离得最近的星期日对应的日期
from dual
(4)、LAST_DAY函数(本月的最后一天):
-1:当前系统日期所在月份的最后一天
select last_day(sysdate)
from dual
-2:查找在每个月倒数第二天进公司的有哪些员工
select last_name,hire_date
from employees
where hire_date = last_day(hire_date)-1
(5)、ROUND函数(日期四舍五入)与TRUNC函数(日期截断):
select round(sysdate,'month'),round(sysdate,'mm'),trunc(sysdate,'hh')
from dual
结果:
ROUND(SYSDATE,'MONTH') ROUND(SYSDATE,'MM') TRUNC(SYSDATE,'HH')
---------------------- ------------------- -------------------
2019/8/1 2019/8/1 2019/7/29 9:00:00
二、转换函数(重点):
1、隐式数据类型转换:可以自动进行。
(1)、加法运算符“+“与连接运算符” | | “:
select '12'+2
from dual
讲解:在其他语言中,“+“符号表示连接运算,而在SQL中”+“符号仍表示加法,连接运算用的是符号”||“。
2、显式数据类型转换。
char类型到date类型的转化:
(1)、查找在1994年6月7日入职的员工信息(以前必须严格按照下面形式):
select employee_id,hire_date
from employees
where hire_date='7-6月-94' 必须严格的按照此日期格式,写错一点结果就运行不出来。
改进1(使用函数):
select employee_id,hire_date
from employees
where to_char(hire_date,'yyyy-mm-dd')='1994-06-07' //也可将日期间的短横线改为斜线'1994/06/07',这样结果就会用斜线显示。
改进2(使用to_date函数):
select employee_id,hire_date
from employees
where to_date('1994-06-07','yyyy-mm-dd')=hire_date
(2)、换个格式显示日期(‘yyyy年mm月dd日’):
select employee_id,hire_date,to_char(hire_date,'yyyy"年"mm"月"dd"日"')
from employees
where to_char(hire_date,'yyyy"年"mm"月"dd"日"')='1994年06月07日'
结果:
EMPLOYEE_ID HIRE_DATE TO_CHAR(HIRE_DATE,'YYYY"年"MM"月"DD"日"')
----------- ----------- --------------------------------------
203 1994/6/7 1994年06月07日
204 1994/6/7 1994年06月07日
205 1994/6/7 1994年06月07日
206 1994/6/7 1994年06月07日
3、显式类型转换之- - - 数字类型到字符串类型的转化(to_char函数):
(1)、转化成的数字格式全部用数字9999表示,如果三个数字有不满的,希望用数字0充上,这时可以数字格式内的数字全部用00000:
select to_char(123456789.12,'999,999,999,999.99')
from dual
结果:
TO_CHAR(1234567.12,'999,999,999,999.99')
------------------------------------------
1,234,567.12
(2)、数字格式内的数字全部用00000:
select to_char(1234567.12,'000,000,000,000.00')
from dual
结果:
TO_CHAR(1234567.12,'000,000,000,000.00')
----------------------------------------
000,001,234,567.12
(3)、可以在格式中使用货币符号:
--1:货币符号“ $“
select to_char(1234567.12,'$000,000,000,000.00')
from dual
结果:$000,001,234,567.12
--2:本地货币符号“ L“
select to_char(1234567.12,'L000,000,000,000.00')
from dual
结果:¥000,001,234,567.12
4、显式类型转化之- - -字符串类型到数字类型的转化(to_number函数):
(1)、字符串类型转到数字类型:
select to_number('¥001,234,567.12','L000,000,999.99') //$ 对应 $
from dual
结果:
TO_NUMBER('¥001,234,567.12','L000,000,999.99'
----------------------------------------------
1234567.12