SQL函数
SQL是用于访问和处理数据库的标准的计算机语言,我们所使用的的是Oracle SQL
一个数据库通常包含一个或多个表,每个表有一个名字表示,下图即为一个名为“emp”的表,接下来的操作都将基于以下的表进行
## 字符函数
字符函数:主要指参数类型是字符型,不同函数返回值可能是字符型或数值型
【大小写转换】
函数 | 效果 | 结果 |
LOWER('SQL Course') | 将大写或大小写混合的字符转换成小写 | sql course |
UPPER('SQL Course') | 将小写写或大小写混合的字符转换大写 | SQL COURSE |
INITCAP('SQL Course') | 将每个单词的第一个字母转换成大写,其余的字母都转换成小写 | Sql course |
## 字符处理函数
## 数值函数
- DUAL:是一张虚表,不能保存任何数据,只有一个字段,一行记录。当我们不希望从任何表中读取数据,同时又想利用SQL引擎中的计算表达式的能力帮我们运算时,就可以使用DUAL表。
select ROUND(100.456,2),Round(100.456,0),Round(100.456,-1) from DUAL
## Date函数
默认的日期形式是: DD-MON-RR
我们可以在系统变量-环境变量中,添加 key = NLS_DATE_FORMAT value = YYYY:MM:DD HH24:MI:SS 就可以正常按照‘年与日’的格式查询了!
SELECT ename, sal, MONTHS_BETWEEN(SYSDATE,hiredate) months FROM emp ORDER BY months; SELECT ename, sal, hiredate, ADD_MONTHS(hiredate,3) new_date FROM emp WHERE hiredate>'01-1月-82'; SELECT NEXT_DAY('2006-02-06','星期一') NEXT_DAY FROM DUAL; SELECT LAST_DAY('02-2月-06') "LAST DAY" FROM DUAL;
SELECT empno, hiredate, ROUND(hiredate, 'MONTH') FROM emp WHERE SUBSTR(hiredate,-2,2)='81'; SELECT empno, hiredate, TRUNC(hiredate, 'MONTH') FROM emp WHERE SUBSTR(hiredate,-2,2)='81';
## 转换函数
【隐式数据类型转换规则】
- 不同的数据类型之间关联,如果不使用显式转换函数,则它会根据以下规则对数据进行隐式转换:
- 对于 INSERT 和 UPDATE 操作,oracle会把插入值或者更新值隐式转换为字段的数据类型
- 对于SELECT语句,oracle会把字段的数据类型隐式转换为变量的数据类型
- 当比较一个字符型和数值型的值时,oracle会把字符型的值隐式转换为数值型
- 当比较字符型和日期型的数据时,oracle会把字符型转换为日期型
- 用连接操作符 (||)时,oracle会把非字符类型的数据转换为字符类型
- 如果字符类型的数据和非字符类型的数据(如number、date、rowid等)作算术运算,则oracle会将字符类型的数据转换为合适的数据类型,这些数据类型可能是number、date、rowid等
【显式数据类型转换规则】
- 通常是在字符类型、日期类型、数值类型之间进行显式转换。
- 主要的三种显式转换函数:TO_char TO_NUMBER TO_DATE
- To_char(日期格式模型)
- 必须用单引号引起来并且是大小写敏感的
- 可以包含任何有效的日期元素
- 使用逗号将日期型数据与日期型格式模型分隔开
- 日期格式模型的元素
- To_char用于日期型,用时间元素 格式化日期的事件部分
- To_char语句中也可以通过双引号添加字符串
- To_char作用于数值型,可以按照以下形式将数值型数据转化为变长的字符串:
- 栗子:
SELECT ename, TO_CHAR(hiredate, 'DD Month YYYY') HIREDATE FROM emp; Select To_char(sal,'$99,999') Salary from emp where ename = 'Scott'
- To_number:可以将一个字符串转换成数值型数据
select to_number(1999) year from dual;
- To_Date: 将一个字符串转换成日期型数据
select to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mi:ss') from dual;
## 通用函数
- 通用函数即与空值(NULL)相关的一些函数,完成对空值(NULL)的一些操作。
- 主要包括以下函数:
- NVL:如果expr1不是null,返回expr1,否则返回expr2
SELECT ename, sal, comm, (sal*12)+NVL(comm,0) from emp
- NVL2:如果expr1不是null,返回expr2,否则返回expr3
- NULLIF:比较两个表达式,如果相等,返回null,否则,返回第一个表达式
- COALESCE: COALESCE (表达式1, 表达式2, ...,表达式n)函数是对NVL函数的扩展。COALESCE函数的功能是返回第一个不为空的参数,参数个数不受限制。
SELECT ename, COALESCE(comm, 0) comm , deptno FROM emp;
- NVL:如果expr1不是null,返回expr1,否则返回expr2
- 条件处理函数:
- CASE表达式:
SELECT ename, deptno, (CASE deptno WHEN 10 THEN '销售部' WHEN 20 THEN '技术部' WHEN 30 THEN '管理部' ELSE '无' END) deptname FROM emp;
- DECODE
SELECT ename, deptno, decode(deptno, 10,'销售部', 20,'技术部', 30 , '管理部', '无') deptname FROM emp;
- CASE表达式: