Peripateticism

Yuens' blog

View the Project on GitHub

header

单行函数的SQL语法

内容参考《李兴华Oracle数据库》的单行函数部分（链接见文末参考），本节主要包括：[toc]

1.上次课程的主要知识点

1.1 在Oracle数据库中的四个主要用户：

1.2 SQLPlus是Oracle操作的主要工具，其支持的命令：

1.3 给出SQL格式

以上给出的格式执行的顺序：FROM、WHERE、SELECT、ORDER BY（永远都是最后一个执行）；

在编写WHERE子句之中可以设置多个判断条件：关系运算符（<、>、>=、<=、=、!=、<>）、逻辑运算符（AND、OR、NOT，若干个条件的连接只能使用AND或OR）、BETWEEN…AND、IN（使用NOT IN的时候不能出现NULL，否则不会有任何的数据返回）、IS NULL、LIKE（LIKE可以使用于各种数据类型，如果没有设置关键字则表示查询全部“%%”）。

ORDER BY子句是唯一可以使用SELECT子句定义的别名的语句。

2.本次内容

  1. 单行函数（背）
  2. 多表查询；
  3. 分组统计查询。

3.单行函数（重点，背）

单行函数的功能非常简单，就是完成某一个功能，例如：现在希望将字母变为大写，这样的功能就可以通过单行函数完成，在Oracle之中提供的单行函数非常的多，今天主要是看核心的几个。

单行函数分类：字符串函数、数字函数、日期函数、转化函数、通用函数。所有的单行函数在进行记忆的时候要记清楚单行函数的名称、返回值类型、接受的参数数据。

3.1字符串函数

字符串函数的主要功能是进行字符串数据处理，下面按照不同的类型来观察函数的使用。

在Oracle之中为方便用户验证函数，专门提供了一个dual的虚拟表，下面将用这张表讲解。

3.1.1 大小写转化函数

3.1.1.1 范例：验证函数——直接在字符串数据上使用

SELECT UPPER(‘Hello’), LOWER(‘Hello’)
FROM dual;

在Oracle之中为方便用户进行函数验证，专门提供dual的虚拟表

3.1.1.2 范例：验证函数——在列上使用（对所有雇员的姓名按照小写字母的方式显示）

SELECT LOWER(ename)
FROM emp;

通过以上验证，发现UPPER()和LOWER()函数操作的时候是不关心原本的字符串是大写还是小写的，都会按照统一的规则转换，那么就可以实际中利用这两个函数进行一些数据处理，某些系统上某些用户名是不区分大小写的，而这样的功能都是在数据保存的时候将保存数据变为统一的大写或小写。

3.1.1.3 范例：要求用户输入一个雇员的姓名，而后查询出此雇员的详细信息

那么此时首先要解决的问题就是如何实现用户的数据输入，在Oracle数据库里提供了一种输入方法：&。

SELECT *
FROM &tablename; -- 要查询的数据表名称由用户输入

那么下面就是利用替代变量输入雇员姓名

SELECT *
FROM emp
WHERE ename=UPPER(‘&name’);

在工作中若有些系统不区分大小写，那么就要用统一形式保存，使用LOWER()、UPPER()函数进行转换。

3.1.2 首字母大写其余字母小写：字符串INITCAP串 | 列)

3.1.2.1 范例：将每位雇员姓名的首字母大写，其余字母小写

SELECT INITCAP(ename)
FROM emp;

3.1.3 取得字符串长度：数字 LENGTH(字符串 | 列)

3.1.3.1 范例：计算字符串长度

SELECT LENGTH(‘Hello world’)
FROM dual;

3.1.3.2 范例：查询所有雇员姓名及姓名长度

SELECT ename, LENGTH(ename)
FROM dual;

3.1.3.3 范例：查询出所有雇员姓名长度为5的全部雇员信息

此时一定要加入WHERE子句来进行数据行的控制，同时还需要用LENGTH判断：

SELECT ename, LENGTH(ename)
FROM emp
WHERE LENGTH(ename)=5;

3.1.4 替换指定字符串的内容：字符串REPLACE(字符串 | 列，要替换的内容，新的内容)

3.1.4.1 范例：替换字符串数据

SELECT REPLACE(‘hello world’, ‘l’, ‘_’)
FROM dual;

3.1.4.2 范例：替换数据列

SELECT ename, REPLACE(ename, ‘A’, ‘_’)
FROM emp;

3.1.5 字符串截取操作

3.1.5.1 范例：字符串截取——由指定位置截取到结尾

取得world：

SELECT SUBSTR(‘hello world’, 7)
FROM dual;

3.1.5.2 范例：字符串截取——截取部分

取得hello：

SELECT SUBSTR(‘hello world’, 0. 5)
FROM dual;
SELECT SUBSTR(‘hello world’, 1, 5)
FROM dual;

实际在Oracle中，字符串索引下标是从1开始的（程序大多是从0开始），但为程序员的使用习惯，即使设置0，也表示从1开始。这点会由Oracle自己处理。

3.1.5.3 范例：截取每一位雇员姓名的前三位字母

SELECT ename, SUBSTR(ename, 1, 3)
FROM emp;

3.1.5.4 范例：截取每一位雇员姓名的后三位字母

SELECT ename, SUBSTR(ename, LENGTH(ename)-2)
FROM emp;

改善思路：在Oracle设计之初，SUBSTR()函数已经考虑到了右后向前的截取。

SELECT ename, SUBSTR(ename, -3)
FROM emp;

面试题：请问Oracle之中的SUBSTR()函数进行截取的时候是从0开始还是从1开始？

3.1.6 去掉左右空格函数：字符串 TRIM(字符串 | 列)

使用TRIM函数可以去掉的只是左右空格，但中间的空格是无法取消的。

SELECT TRIM(‘   hello world   ’), TRIM(‘   hello world   ’)
FROM dual;

这类操作在进行数据接受的时候非常有用处。

3.2 数字函数

数字函数主要是针对数字进行的一些小处理。

3.2.1 四舍五入函数：数字ROUND(数字 | 列 [, ])

3.2.1.1 范例：验证ROUND函数

SELECT ROUND(9615.7652), ROUND(9615.7652, 2), ROUND(9615.7652, -2), ROUND(-15.5) FROM dual; 结果：9616
结果：9615.77
结果：9600
结果：16

如果没有编写保留小数位数，那么就会将小数点之后的数据直接进行进位，不保留小数位。

3.2.2 截取小数函数：数字 TRUNC(数字 | 列 [, 保留小数位])

3.2.2.1 范例：验证TRUNC()函数

SELECT TRUNC(9615.7652), TRUNC(9615.7652, 2), TRUNC(9615.7652, -2), TRUNC(-15.5) FROM dual; è9615
è9615.76
è9600
è-15

3.2.3 取模：数字 MOD(数字1 | 列1, 数字2 | 列2)

3.2.3.1 范例：求余数

SELECT MOD(10,3)
FROM dual;

以后再工作中，只要见到ROUND这个单词，一定要清楚其在四舍五入。

3.3 日期函数

如果要想操作日期函数，实际上有一个首要的问题需要解决。Oracle里提供两个伪列（实不存在表中的列，但可直接使用的列）：SYSDATE、SYSTIMESTAMP。

3.3.0.1 范例：取得当前系统日期和系统时间

SELECT SYSDATE
FROM dual;
SELECT SYSTIMESTAMP
FROM dual;

当有了系统日期之后就可以通过以下的三个公式来进行一些简单的计算：

3.3.0.2 范例：计算若干天之后的日期

SELECT SYSDATE + 3, SYSDATE + 120
FROM dual;

3.3.0.3 范例：计算若干天之前的日期

SELECT SYSDATE - 120
FROM dual;

计算的时候是按照自然日的天数进行计算，是不会考虑那个月由30天或28天等。

3.3.0.4 范例：统计出每位雇员到今天为止的雇佣日期

SELECT ename, SYSDATE – hiredate
FROM emp;

如果在一个很长的时间内，通过天计算年，并不准确（不确定365、366），所以想准确地计算出天数，需要计算相距不要超过30天。

如果想准确地进行日期时间的操作，那么需要提供的日期函数。

3.3.1 计算两个日期之间经历的月数：数字MONTHS_BETWEEN(日期1 | 列1, 日期2 | 列2)

3.3.1.1 范例：计算每位雇员到目前的雇佣年数

年数的计算最准确地一定是通过月数完成的，一年是不会被改变的。

SELECT ename, MONTHS_BETWEEN(SYSDATE, HIREDATE)
MONTHS_BETWEEN(SYSDATE, hiredate)/12
FROM emp;
SELECT ename, MONTHS_BETWEEN(SYSDATE, hiredate),
TRUNC(MONTHS_BETWEEN(SYSDATE, hiredate)/12)
FROM emp;

3.3.1.2 范例：查询出所有已经被雇佣了33年以上的雇员

SELECT *
FROM emp
WHERE MONTHS_BETWEEN(SYSDATE, hiredate)/12>33;

3.3.2 加上指定月之后的日期：日期 ADD_MONTHS(日期 | 列, 月数)

3.3.2.1 范例：计算若干月后/前（正/负）的日期

SELECT SYSDATE + 120, ADD_MONTHS(SYSDATE, 4), ADD_MONTHS(SYSDATE, 500)
FROM dual;

在进行计算的时候，数字的大小是不受限制的。

思考题：要求计算出每一位雇员到今天为止雇佣的年限（理解）

第一步：计算出每一位雇员到今天为止的雇佣年份，直接利用月数除以12

SELECT ename, hiredate,
TRUNC(MONTHS_BETWEEN(SYSDATE, hriedate)/12) year
FROM emp;

第二步：计算月，在进行年计算的时候发现存在有小数，这些小数就包含了月

SELECT ename, hiredate,
TRUNC(MONTHS_BETWEEN(SYSDATE, hriedate)/12) year,
TRUNC(MOD(MONTHS_BETWEEN(SYSDATE, hiredate), 12)) months
FROM emp;

第三步：计算天数，对于天数的计算实际上只学会一个公式：“日期1 – 日期2 = 天数”，于是分析日期：

SELECT ename, hiredate,
TRUNC(MONTHS_BETWEEN(SYSDATE, hriedate)/12) year,
TRUNC(MOD(MONTHS_BETWEEN(SYSDATE, hiredate), 12)) months，
TRUNC(SYSDATE – ADD_MONTHS(hiredate, MONTHS_BETWEEN(SYSDATE, hriedate))) day
FROM emp;

【为何ADD_MONTHS的结果不TRUNC？】

只有日期函数可准确计算出日期，其它直接操作无法进行。

3.3.3 求出指定的下一个一周时间数的日期：日期 NEXT_DAY(日期 | 列, 一周时间数)

一周时间数：周一，周二等等

3.3.3.1 范例：计算下一个星期二

SELECT NEXT_DAY(SYSDATE, ‘星期二’)
FROM dual;

3.3.4 求出指定日期所在月的最后一天日期：日期 LAST_DAY(日期 | 列)

3.3.4.1 范例：求出本月的最后一天

SELECT LAST_DAY(SYSDATE)
FROM dual;

3.3.4.2 范例：查询出每个雇员雇佣所在月倒数第二天雇佣的全部雇员信息

SELECT ename, hiredate, LAST_DAY(hiredate)-2
FROM emp
WHERE LAST_DAY(hiredate)-2=hiredate;

所有的日期函数实际上在进行一些统计操作的时候还是有用处的。

3.4 转换函数

目前为止，我们已经接触到Oracle提供的三类数据：字符串、数字、日期，那么这三种数据可通过转换函数实现类型转换。

3.4.1 转字符串数据：字符串 TO_CHAR(数字 | 日期 | 列, 转换格式)

在进行转换格式设置的时候，要根据不同的数据类型进行格式标记的定义：

3.4.1.1 范例：将日期格式化为字符串

SELECT TO_CHAR(SYSDATE, ‘yyyy-mm-dd’) FROM dual;
 2014-08-12 
SELECT TO_CHAR(SYSDATE, ‘fmyyyy-mm-dd’) FROM dual;
 2014-8-12

3.4.1.2 范例：在Oracle的SYSDATE里，包含有时间数据，也可继续显示时间

SELECT TO_CHAR(SYSDATE, ‘yyyy-mm-dd hh24:mi:ss’) FROM dual;

那么，实际上用户也可利用TO_CHAR()函数进行年、月、日数据的拆分。

3.4.1.3 范例：拆分日期数据

SELECT TO_CHAR(SYSDATE, ‘yyyy’) year,
TO_CHAR(SYSDATE, ‘mm’) months,
TO_CHAR(SYSDATE, ‘dd’) day
FROM dual;

3.4.1.4 范例：查询出所有在2月份雇佣的雇员

SELECT *
FROM emp
WHERE TO_CHAR(hiredate, ‘mm’)=’02’;

使用TO_CHAR()还可以格式化数字显示。

3.4.1.5 范例：格式化数字显示

23567823651 = 23,567,823,651，格式化数字任意一位用9表示（多了也没事），L代表本地货币。

SELECT TO_CHAR(23567823651, ‘L999,999,999,999,999’)
FROM mep;

3.4.2 转日期数据：日期TO_DATE(字符串, 转换格式)

3.4.2.1 范例：将字符串变为日期

SELECT ‘1981-09-15’, TO_DATE(‘1981-09-15’, ‘yyyy-mm-dd’)
FROM dual;

但是在Oracle之中提供有自动的转换方式，如果字符串按照日期的格式编写，那么可以自动由字符串变为日期。

3.4.3 转数字数据：数字 TO_NUMBER(字符串, 转换格式)

SELECT TO_NUMBER(‘1’) + TO_NUMBER(‘2’)
FROM dual;

或

SELECT ‘1’ + ‘2’
FROM dual;

在Oracle中这些自动的数据类型转换功能是非常方便的，包括日期和字符串、字符串和数字。

3.5 通用函数

通用函数主要是Oracle的特色函数，有两个：NVL()、DECODE()。

3.5.1 处理NULL数据：数字 NVL(列 | 数据, 默认值)

3.5.1.1 范例：遇到NULL

SELECT ename, sal, comm, (sal+comm)*12
FROM emp;

此时，发现有一部分人没有年薪，是因为佣金为NULL。

在数据库中，NULL与任何数据计算都是NULL。那么如果遇到了NULL应按照数字0来处理，那么我们可用NULL来处理。

SELECT ename, sal, comm, (sal+NVL(comm, 0))*12
FROM emp;

3.5.2 多数据判断：数据 DECODE(字段 | 数据, 判断数据1, 显示数据1, 判断数据2, 显示数据2, …[默认数据])

3.5.2.1 范例：将所有雇员的职位数据变为中文

SELECT ename, job,
DECODE(job, ‘CLERK’, ‘办事员’, ‘SALESMAN’, ‘销售’, ‘MANAGER’, ‘经理’, ‘ANALYST’, ‘分析员’, ‘PRESIDENTIAL’, ‘总裁’, ‘未处理’)
FROM emp;

非常类似IF…ELSE…IF语句（或者是SWICTH…CASE）。

若没有给出对应的职位信息的解码信息，那么遇到这条数据时候会给默认输出，若没有给出默认输出，则为NULL。

4. 单行函数总结

No. 方法名称 类型
1 字符串 UPPER(字符串 | 数据列) 将特定的字符串或者指定的列数据变为大写
2 字符串 LOWER(字符串 | 数据列) 将特定的字符串或者指定的列数据变为小写
3 字符串 INITCAP(字符串 | 列) 将特定字符串或者指定列的数据的首字母大写，其余字母小写
4 数字 LENGTH(字符串 | 列) 计算出指定字符串或者数据列的数据长度
5 字符串 REPLACE(字符串 | 列，要替换的内容，新的内容) 将指定字符串或者数据列中的数据按照指定的新内容
6 字符串 SUBSTR(字符串 | 数据列，截取开始索引) 由指定位置截取到结尾
7 字符串 SUBSTR(字符串 | 数据列，截取开始索引，截取结束索引) 指定截取的开始和结束位置
8 字符串 TRIM(字符串 | 列) 去掉左右空格函数
9 数字 ROUND(数字 | 列 [，保留小数位]) 四舍五入操作
10 数字 TRUNC(数字 | 列 [，保留小数位]) 截取小数
11 数字 MOD(数字1 | 列1，数字2 | 列2) 求模（余数）
12 数字MONTHS_BETWEEN(日期1 | 列1，日期2 | 列2) 计算两个日期之间所经历的月数
13 ADD_MONTHS(日期 | 列，月数) 在指定日期上增加若干月之后的日期
14 日期 NEXT_DAY(日期 | 列，一周时间数) 求出指定的下一个一周时间数的日期
15 日期 LAST_DAY(日期 | 列) 求出指定日期所在月的最后一天日期
16 字符串 TO_CHAR(数字 | 日期 | 列，转换格式) 转字符串数据
17 日期 TO_DATE(字符串，转换格式) 转日期数据
18 数字 TO_NUMER(字符串，转换格式) 转数字数据
19 数字NVL(列 | 数据，默认值) 处理NULL数据
20 数据DECODE(字段 | 数据，判断数据1，显示数据1，判断数据2，显示数据2，…[默认显示]) 多数据判断

5. 参考