Oracle数据库（函数（不包括自定函数，自定义函数会单独在后面博文中说明）、Oracle中select语法讲解）
知识点一、函数
函数再次分为单行函数和多行函数（组函数）来介绍，其中单行函数又分为：（1）与数字相关的函数（2）字符函数（3）日期时间函数（4）转换函数（5）通用函数
1、单行函数：即输入一行的某个 列的值，输出一个结果，单行函数存在于sql语句、select子句、where条件中。第一部分先介绍一些常用的单行函数，第二部分在对这些单行函数具体用法给出代码演示。
（1）数字相关的函数
输入和输出都是数字类型，下面列举几个常用的与数字相关的函数：
abs(n) 返回数字n的绝对值
exp(n) e的n次方
power(m,n) m的n次方
mod(m,n) m除以n的余数
ceil(n) 大于等于n的最小整数
floor(n) 小于等于n的最大整数
round(m[，n]) 四舍五入，n四舍五入的位数：n正数，小数后第n位的数据要四舍五入;n为负数，小数点前第n位开始四舍五入，例如：round(789.346,2) 结果为789.35
trunc(m[,n]) 截断数据，n截断到第几位：，负数表示截断到小数点之前第n位
sign(n) 检测数字的正负，整数返回1，负数返回-1，0返回0
aqrt(n) n的平方根
（2）字符函数
大部分字符函数的输入参数都为字符类型，返回值是字符类型或数字类型，下面列举一些常用的字符函数：
ascii(c) 返回字符串首字符的ascii编码值
chr(n) 将ascii码值转换成字符
concat(c1,c2) 连接两个字符串 ||字符串的连接运算符
initcap(c) 用于将字符串中每个单词的首字母大写，其余字母小写
instr(c1,c2) c2第一次在c1中出现的位置，字符串位置从1开始
lenght(c) 返回字符串字符的个数，如果为”、null，则length函数返回null;
lower(c) 转小写
uper(c) 转大写
replace(c,seraching_c,replace_c)
substr(c,m,n)获取子串，m取的第一个字符的位置，n取的字符的个数;
m为负数时，从字符串末尾开始数m个位置，从该位置开始取，从前往后取n个
trim(c from str) 删除首尾的所有子串c
（3）日期时间函数
日期时间函数大多用来处理date和timestamp类型的数据。下面列举一些常用的日期时间函数：
add_months(d,n) 用于获取指定日期d之前或之后n个月的日期
mouths_between (d1,d2) 返回两个日期之间的月份差
current_date
current_timestamp
last day(d) 返回指定日期的月份的最后一天的日期时间
注意：
日期算数运算
+：不允许日期+日期，只能加整数，在天数上加上对应的值
-：允许日期-日期，得到两个日期之间的天数差
（4）转换函数
转换函数有很多，在这里值列举几个：
to_char
to_date
to_number(c,format)
将一个数字格式的字符串转换成数字
format:
9,代表一个数字
$,美元符号
L,本地货币符号
.,用于匹配一个小数点
,,用于匹配一个逗号，不能用于匹配小数位
x,表示16进制数的1位
（5）通用函数（参数类型没有限制）
null与任何数据进行算术运算时，结果都是null
nvl(expr1,expr2),如果expr1为null，返回expr2;
如果expr1不为null，返回expr2
nullif(expr1,expr2) expr1 == expr2 ? null : expr1
nvl2(expr1,expr2,expr3) expr1 == null ? Expr3 : expr2
decode(expr,search1,result1,search2,result2,…defaultValue)
根据表达式与值的匹配情况，返回对应的结果，且一旦找到匹配的值，则直接返回对应的result
（6）上述函数的代码演示（代码演示来源：https://www.cnblogs.com/vic_lu/archive/2010/08/28/1811097.html，里面代码本人都测试过，都可以运行，可以手动敲一下代码，提高熟练度）：
1、set linesize 100; 设置长度
2、set pagesize 30; 设置每页显示数目
3、em a.sql 打开记事本
4、@ a 执行文件a中的代码，可指定文件的路径 @d:a.txt
5、conn 用户名/密码 根据用户名和密码连接数据库 如果连接超级管理员(sys) 则应加上as sysdba;
6、show user; 显示当前连接的用户
7、select * from tab; 得到当前用户下的所有表
8、desc temp; 查看表结构
9、/ 继续执行上一个查询语句
clear scr; 清屏
字符函数
10、select upper('coolszy') from dual; 将小写字母转换成大写，dual 为一虚表
11、select lower('KUKA') from dual; 将大写字母转换成小写
12、select initcap('kuka') from dual; 将首字母大写
13、select concat('Hello',' world') from dual; 连接字符串，但没有||好用select concat('Hello','world') from dual;
14、select substr('hello',1,3) from dual; 截取字符串
15、select length('hello') from dual; 求字符串长度
16、select replace('hello','l','x') from dual; 替换字符串
17、select substr('hello',-3,3) from dual; 截取后三位
数值函数
18、select round(789.536) from dual; 四舍五入，舍去小数
19、select round(789.536,2) from dual; 保留两位小数
20、select round(789.536,-1) from dual; 对整数进行四舍五入
21、select trunc(789.536) from dual; 舍去小数，但不进位
22、select trunc(789.536,2) from dual;
23、select trunc(789.536,-2) from dual;
24、select mod(10,3) from dual; 返回10%3的结果
日期函数
25、select sysdate from dual; 返回当前日期
26、select months_between(sysdate,'16-6月 -08') from dual; 返回之间的月数
27、select add_months(sysdate,4) from dual; 在日期上加上月数
28、select next_day(sysdate,'星期一') from dual; 求下一个星期一
29、select last_day(sysdate) from dual; 求本月的最后一天
转换函数
30、select to_char(sysdate,'yyyy') year,to_char(sysdate,'mm'),to_char(sysdate,'dd') from dual;
31、select to_char(sysdate,'yyyy-mm-dd') from dual;
32、select to_char(sysdate,'fmyyyy-mm-dd') from dual; 取消月 日 前面的0
33、select to_char('20394','99,999') from dual; 分割钱 9表示格式
34、select to_char('2034','L99,999') from dual; 加上钱币符号
35、select to_number('123')*to_number('2') from dual;
36、select to_date('1988-07-04','yyyy-mm-dd') from dual;
通用函数
37、select nvl(null,0) from dual; 如果为null，则用0代替
38、select decode(1,1,'内容是1',2,'内容是2',3,'内容是3') from dual; 类似于 switch...case...
事务处理
39、commit; 提交事务
40、rollback; 回滚事务
41、select rownum from table; 在没一列前面显示行号
42、drop table 表名 cascade constraint
on delete casecade 当父表中的内容被删除后，子表中的内容也被删除
43、desc表名 显示表的结构
44、create user [username] identified by [password] 创建新的用户
45、grant 权限1、权限2...to 用户 给创建用户权限
ex:grant create session to [username] 此时只能连接到数据库
grant connect,resource to [username] 此时权限能满足要求
46、alter user [username] identified by [password] 修改用户密码
47、alter user [username] password expired 下次登录时提示修改密码
48、alter user [username] account lock 锁住用户
49、alter user [username] account unlock 解锁锁用户
50、grant select,delete on scott.emp to [username] 把scott下emp表的两个权限给用户
51、revoke select ,delete on scott.emo from [username] 回收权限
2、多行函数（组函数）
常见的组函数有：sum，avg，count，max，min等等。组函数会忽略值为null的数据，输入多行某列的值，输出一个结果在mysql中如果查询列，不是分组条件，这些列的值对于一组来说可能各不相同，默认显示查到的第一行数据的这些列的值。但是在Oracle中如果查询列，不是分组条件，直接报错；但是group by所指定的列并不是必须出现在select列表中。在分组查询中，select只能指定为分组条件列、组函数；Oracle分组时，select 可以指定为 分组条件列、组函数。并且在Oracle中规定，组函数嵌套只能嵌两层。其实多层嵌套并没有实际的用途，因此Oracle没有提供组函数的多层嵌套，但是，单行函数是可以多层嵌套的。下面列举一些常见组函数的具体语法：
注意点：
（1）、在where语句中不允许使用组函数
（2）、SELECT ename,MAX(sal),MIN(sal),SUM(sal),AVG(sal) FROM emp;此条语句会报错的，错误原因：ename不是单组分组函数。
1、Oracle包含以下常见组函数
-AVG([DISTINCT|ALL]n) 返回平均值，忽略空值
-COUNT({*|[DISTINCT|ALL]}expr) 返回记录的数量，用*包含空值，否则不包含空值
-MAX([DISTINCT|ALL]expr) 返回最大值，忽略空值
-MIN([DISTINCT|ALL]expr) 返回最小值，忽略空值
-SUM([DISTINCT|ALL]n) 返回总值，忽略空值
-STDDEV([DISTINCT|ALL]x) 返回标准差，忽略空值
-VARIANCE([DISTINCT|ALL]x) 返回统计方差，忽略空值
2、AVG和SUM的用法
-可以对数值型数据使用AVG和SUM
例：
SELECT AVG(sal),SUM(sal)
FROM EMP
WHERE job LIKE 'SALES%';
3、MIN和MAX的用法
-MIN和MAX可以用于任何数据类型
例：
SELECT MIN(hiredate),MAX(hiredate)
FROM emp;
结果：
MIN(HIREDATE) MAX(HIREDATE)
17-12月-80 23-5月 -87
4、COUNT的用法
1)COUNT(*)返回表中行的总数
例：
SELECT COUNT(*)
FROM emp
WHERE deptno=30;
结果：
COUNT(*)
6
2)COUNT(expr)返回非空行的数量
例:
SELECT COUNT(comm)
FROM emp
WHERE deptno=30;
结果：
COUNT(*)
4
5、组函数和空值
1)组函数会忽略列中的空值
例：
SELECT AVG(comm)
FROM emp;
结果：
AVG(COMM)
550
2)NVL函数可以使组函数强制包含含有空值的记录
例：
SELECT AVG(NVL(comm,0))
FROM emp;
结果：
AVG(NVL(COMM,0))
157.142857
6、创建数据组
SELECT column,group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[OEDER BY column];
-通过GROUP BY子句将表中的记录划分成若干个小组
-GROUP BY子句中必须包含指定的列
-GROUP BY子句中不能使用列的别名
-当使用GROUP BY子句时Orale服务器会自动对结果集合默认按GROUP BY子句所指定的列升序排列
-在SELECT列表中除了组函数外，所有列都必须包含在GROUP BY子句中
例：
SELECT deptno,AVG(sal)
FROM emp
GROUP BY deptno;
结果：
DEPTNO AVG(SAL)
10 2916.66667
20 2175
30 1566.66667
-GROUP BY所指定的列并不是必须出现在SELECT列表中
例：
SELECT AVG(sal)
FROM emp
GROUP BY deptno;
结果：
AVG(SAL)
2916.66667
2175
1566.66667
7.按多个列分组
例：
SELECT deptno,job,sum(sal)
FROM emp
GROUP BY deptno,job;
结果：
DEPTNO JOB SUM(SAL)
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
8.使用组函数的非法查询
例：
SELECT dept,COUNT(ename)
FROM emp;
结果：
ERROR 位于第 1 行:
ORA-00937: 非单组分组函数
纠正：
SELECT dept,COUNT(ename)
FROM emp
GROUP BY deptno;
结果：
DEPTNO COUNT(ENAME)
10 3
20 5
30 6
9.限制组结果
使用HAVING子句限制组
-对记录分组
-在分组的基础上应用组函数
-与HAVING子句匹配的结果才输出
SELECT column,group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];
例1：
SELECT deptno,max(sal)
FROM emp
WHERE max(sal)>2900
GROUP BY deptno;
结果：
ERROR 位于第 3 行:
ORA-00934: 此处不允许使用分组函数
纠正：
SELECT deptno,max(sal)
FROM emp
GROUP BY deptno
HAVING max(sal)>2900;
结果：
DEPTNO MAX(SAL)
10 5000
20 3000
例2：
SELECT job,SUM(sal) PAYROLL
FROM emp
WHERE job NOT LIKE 'SALES%'
GROUP BY job
HAVING SUM(sal)>5000
ORDER BY SUM(sal);
结果：
JOB PAYROLL
ANALYST 6000
MANAGER 8275
10.组函数嵌套
-与单行函数不同，组函数只能嵌套两层
例：显示平均薪水的最大值
SELECT max(avg(sal))
FROM emp
GROUP BY deptno;
结果：
MAX(AVG(SAL))
2916.66667
练习（利用Oracle中scott用户下系统自带表进行联系）
1.使用emp表显示所有雇员的最多、最少、总和、平均薪水
SELECT MAX(sal),MIN(sal),SUM(sal),AVG(sal)
FROM emp;
2.显示emp表中不同部门编号的数量
SELECT COUNT(DISTINCT deptno)
FROM emp;
3.在emp表中根据job列分组显示雇员的最多、最少、总和、平均薪水
SELECT job,MAX(sal),MIN(sal),SUM(sal),AVG(sal)
FROM emp
GROUP BY job;
4.使用emp表显示job名、每组最多、最少、总和、平均薪水
要求：按job列分组，ename列的名字不是以A开头，且任何组的最少薪水大于1600
SELECT job,MAX(sal),MIN(sal),SUM(sal),AVG(sal)
FROM emp
WHERE ename NOT LIKE 'A%'
GROUP BY job
HAVING MIN(sal)>1600
5.显示部门名和每个部门的累计薪水，要求每个部门的累计薪水大于3000
SELECT dname,SUM(sal)
FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY dname
HAVING SUM(sal)>3000;
6.显示每个部门、每个岗位的最高及最低薪水
SELECT deptno,job,MAX(sal),MIN(sal)
FROM emp
GROUP BY deptno,job; 上一篇： mysql创建日历表的方法
下一篇： shrinkspace