03、SQL--单行函数（1）（含习题）

一、学习目标与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