Oracle_SQL_day02_SQL函数

1、单行函数

• 字符类型（character）
• 数值类型（number)
• 通用函数

---

2、多行函数
- 又称为组函数或聚和函数
- LOWER 小写函数
- UPPER 大写函数
- CONCAT
- 拼接函数
- SUBSTR 求子串函数
- LENGTH 字符串的长度
- LPAD 右对齐函数，位数不够的，左边补指定字符
- RPAD 左对齐函数，位数不够的，右边补指定字符
字符串的大小写转换

LOWER(‘SQL Course’)
结果： sql course
UPPER(‘SQL Course’)
结果： SQL COURSE
INITCAP(‘SQL Course’)
结果： Sql Course

字符串的常规操作

CONCAT(‘Good’,‘String’)
结果： GoodString
SUBSTR(‘String’,1,3)
结果：Str
LENGTH(‘String’)
结果：6
LPAD(‘String’,10,’*’)
结果：****String

3、数值函数
ROUND 四舍五入函数
TRUNC 截取函数

 ROUND(45.923,2) --》45.92
 ROUND(45.923,0) --》46
ROUND(45.923,-1) --》50
 TRUNC(45.923,2) --》45.92
 TRUNC(45.923)  --》45
 TRUNC(45.923,-1)  --》40

4.日期类型的运算

对日期加减一个数字，返回值为一个日期
两个日期相减表示两个日期相差多少天
months_between(’01-SEP-95’,’11-JAN-94’)
19.774194
add_months(’11-JAN-94’,6)
11-JUL-94
next_day(’01-SEP-95’,’FRIDAY’)
08-SEP-95
last_day(’01-SEP-95’)
30-SEP-95
yyyy：用数字表达的四位年（2009年）
mm：用数字表达的二位月（01月）
dd：用数字表达的二位日（01日）
hh24：用数字表达的24进制的小时（20点）
h12：用数字表达的12进制的小时（8点）
mi：用数字表达的分钟（30分）
ss：用数字表达的小时（30秒）
d：用数字表达的一周内的第几天（周日：1）
day：用全拼表达的星期几（sunday）
month:用全拼表达的月（march）
sysdate=’25-MAY-09’
round(sysdate,’MONTH’)
01-JUN-09
round(sysdate,’YEAR’)
01-JAN-09
trunc(sysdate,’MONTH’)
01-MAY-09
trunc(sysdate,’YEAR’)
01-JAN-09

5、转换函数

TO_NUMBER将字符串转换成数值
TO_CHAR将数值或日期转化成字符串
转换函数中包含格式说明
TO_NUMBER TO_DATE
数值 <———— 字符 ————> 日期
————-> <————
因此我们说字符是日期和数字的桥梁
to_number语法
例子
SELECT TO_NUMBER(’12’)*12
FROM dual;
TO_NUMBER(char)
SELECT TO_NUMBER(’ab’,’xx’)
FROM dual;

TO_CHAR 函数( ( 数值类型) )

语法
格式说明
9 代表数字位
0 定义宽度大于实际值宽度时，0会被强制显示在前面，以补齐位数

$ 美元的货币符号
L 本地货币符号
. 小数点
， 每千位显示一个逗号

如果显示位数不足（定义宽度小于实际值宽度），用#代替
用法：9按实际位数显示，0按定义位数显示，不足的补0
SELECT ename,TO_CHAR(sal,’$99,999.99’) FROM emp; 用法：9按实际位数显示，0按定义位数显示，不足的补0 SELECT ename,TO_CHAR(sal,’$00,000.00’)
FROM emp;
SELECT ename,TO_CHAR(sal,’L99,999.99’)
FROM emp;

TO_DATE 函数

语法：TO_DATE(char[, 'fmt'])
例子：INSERT INTO emp (empno, ename, sal, hiredate)
VALUES (26, 'Donna', 3000,TO_DATE('01-10-10 08:00', 'DD-MM-YY HH:MI'));

用TO_DATE函数将字符串转换成一个日期值
TO_DATE（’10 September 2009’,’dd Month yyyy’）
TO_CHAR(date, ‘fmt’)
格式说明
第一个参数为要处理的日期，第二个参数为格式
格式必须用单引号括起来，并且大小写敏感
必须是有效的日期格式
NVL 空值处理函数
COALESCE 空值处理函数
DECODE 分支函数
NVL(comm,0)
NVL(hiredate,‘01-9月-10’)
NVL(job,’No Job Yet’)
COALESCE(comm, salary, 100)
DECODE(job, ‘SALESMAN’, 1.10*sal,
‘ANALYST’, 1.15*sal,
‘MANAGER’, 1.20*sal,
sal)

组函数操作在一组行（记录）上，每组返回一个结果
组函数可以出现在SELECT子句和HAVING子句中
GROUP BY子句将行分成若干个组
HAVING子句对分组后的结果进行过滤

组函数 ：

AVG和SUM只能针对NUMBER类型
COUNT和MAX和MIN可以针对任何类型
SELECT AVG(sal), MAX(sal), MIN(sal), SUM(sal)
FROM emp
WHERE UPPER(job) LIKE ‘SALES%’;
SELECT MIN(ename), MAX(ename) FROM emp;

缺省情况组函数处理所有的非空值

SELECT COUNT(comm)
FROM emp
WHERE deptno = 20;

COUNT(*)统计记录总数

SELECT COUNT(*)
FROM emp
WHERE deptno = 30;

2 GROUP BY 及 HAVING
GROUP BY 子句 ：

SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BYcolumn];

SELECT deptno, COUNT(*)  ”Number ”
FROM emp
GROUP BY deptno;

有 GROUP BY 子句的 SELECT语句执行过程

根据GROUP BY子句指定的表达式，将要处理的数据（若有
WHERE子句即为通过条件过滤后的数据）分成若干组，每组
有唯一的组标识，组内有若干条记录，根据SELECT后面的
组函数对每组的记录进行计算，每组对应一个返回值

若有GROUP BY子句，SELECT后面可跟GROUP BY后面跟的
表达式以及组函数，其它会报错

SELECT ename, AVG(sal)
FROM emp
GROUP BY deptno;
SELECT ename, AVG(sal)
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression

GROUP BY子句中包含多列，分组的粒度更细，即每组的记
录少了，但组多了
各个部门不同职位的人数

SELECT deptno, job, COUNT(*)
FROM emp
GROUP BY deptno, job;

HAVING 子句 ：

SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];

有 HAVING 子句的 SELECT
行被分组
将HAVING子句的条件应用到每个分组上
只有符合HAVING条件的组被保留，再应用SELECT后面的组
函数对每组的数据进行处理

SELECT job, SUM(sal) PAYROLL
FROM emp
WHERE job NOT LIKE 'P%'
GROUP BY job
HAVING SUM(sal) > 5000
ORDER BY SUM(sal);

WHERE 子句和 HAVING

• WHERE子句过滤的是行（记录）
• HAVING子句过滤的是分组（组标识、每组数据的聚合结果）
• WHERE子句包含单行函数
• HAVING子句只能包含GROUP BY后面的表达式和组函数
• WHERE子句执行在前，HAVING子句执行在后