Oracle数据库（函数（不包括自定函数，自定义函数会单独在后面博文中说明）、Oracle中select语法讲解）

最新推荐文章于 2024-07-02 00:15:00 发布

原创最新推荐文章于 2024-07-02 00:15:00 发布 · 4.5k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #函数 #数据库

数据库相关技术专栏收录该内容

18 篇文章

订阅专栏

本文详细介绍了Oracle数据库中的函数，包括单行函数（如数学、字符、日期时间、转换和通用函数）和多行函数（如SUM、AVG等组函数）。同时，文章还详细讲解了SELECT查询语句的各个组成部分，如选择列、数据来源、WHERE子句、连接查询、GROUP BY和HAVING子句、ORDER BY以及集合运算。提供了丰富的例子和实践练习，帮助读者深入掌握Oracle SQL的使用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

引用、推荐博客：

http://www.cnblogs.com/qingxinblog/p/4154228.html（函数练手敲代码博客推荐）
http://blog.youkuaiyun.com/jian200801/article/details/7558292（自定义函数实例）
http://www.cnblogs.com/vic_lu/archive/2010/08/28/1811097.html
http://www.cnblogs.com/yuepeng/archive/2011/03/21/1990105.html
http://blog.youkuaiyun.com/rosekin/article/details/39298255（多表链接查询归纳总结）

一、函数

函数再次分为单行函数和多行函数（组函数）来介绍，其中单行函数又分为：（1）与数字相关的函数（2）字符函数（3）日期时间函数（4）转换函数（5）通用函数

1.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）上述函数的代码演示
（代码演示来源：http://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] 回收权限

1.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;

二、select查询语句（Oracle）

select查询语句完整语法以及执行顺序：

这里写图片描述

2.1、选择列

select后面出现的内容可以是from后面的数据来源中的列，可以是*，可以是常量或者T-SQL函数
如果列名很长，或者多个表的列名有冲突，可以使用as来定义别名

2.2、数据来源

数据来源可以是表，视图，还可以是其他的select语句(即行集)
from子句中可以包含连接说明，即inner join， outer join这样的内容。这个内容参见下面的内容
可以在from子句中为表，视图，或者其他select语句的结果指定别名，但是不要用as

2.3、where子句

多个条件之间可以用and 或者or连接
null值查询要使用is null，或者is not null，而不是=null或者<>null
like是进行模式匹配的一种方式，列的数据类型可以是任何字符或者日期数据。它的否定形式是not like。%和是通配符，%表示0或多个任意字符，表示一个任意字符。但是这两个字符如果不出现在like后面的模式中，就是两个普通字符
text列的where条件只能是like，is null，patindex
如果要在like中匹配普通字符%和_，可以使用escape定义一个转义字符，这个转义字符可以随意指定。然后将这个转义字符放在一个通配符或者单引号之前，就表示这个通配符或者单引号是一个普通的字符
in ，not in， between and， not between and.“between a and b”将会包括a和b在内。in可以转换为一个连接，但是not in不能
where exists R.当且仅当R非空时，条件exists R为真。其否定形式是 where not exists R.
where s <| >| = | <>| >=|<= all/any R.否定形式是在s前对整个表达式加not.

2.4、连接查询

（oracle 多表联合查询总结归纳推荐博客阅读http://blog.youkuaiyun.com/rosekin/article/details/39298255）
两张表的连接可以用from子句的ansi连接语法或者where子句中的sql连接语法实现。ansi连接语法格式为from table1 join_type table2 on(conditions) join_type table3 on(conditions)。连接有等值连接，笛卡尔积（交叉连接），自然连接，theta连接，外链接五种。

假设有三个表，其结构如下：

T_Student表：id, name； 3条数据

T_Course表：id，name； 4条数据

T_SC表：sid， cid，score；10条数据

等值连接

select t_student., t_sc.
from t_student inner join t_sc
on (t_student.id = t_sc.sid)
–10条记录，t_sc的记录数目

交叉连接（笛卡尔积）

select t_student., t_sc.
from t_student cross join t_Sc
–30条记录，t_student的数目乘以 t_sc的数目

自然连接（等值连接的简化）

select t_student.*, t_sc.cid, t_sc.score
from t_student inner join t_sc
on (t_student.id = t_sc.sid)
–10条记录，t_sc的记录数目。自然连接是一种特殊的等值连接，其特殊之处在于要将结果列中的重复列去掉。

theta连接

select t_student.*, t_sc.cid, t_sc.score
from t_student inner join t_sc
on (t_student.id = t_sc.sid)
and t_student.id = ‘aaa’

多表连接,同时也是自然连接

select t_student.name as sname, t_course.name as cname, t_sc.score
from t_student inner join t_sc on (t_student.id = t_sc.sid)
inner join t_course on(t_sc.cid = t_course.id)
–10条记录，t_sc的记录数目
外连接

select t_student.*, t_sc.cid, t_sc.score
from t_student left outer join t_sc
on(t_student.id = t_sc.sid)

或者

select t_student.*, t_sc.cid, t_sc.score
from t_student, t_sc
where t_student.id = t_sc.sid(+)

上面的例子是左外连接的示例，对应的还有右外连接。以左外连接为例，实际的查询过程大概是这样的：以t_student为主表，遍历t_student。对于 t_student中的每一条记录(每一个学生)，如果t_sc中的记录的sid字段值和该学生id相同，就形成新的记录。如果t_sc中找不到任何一条记录的sid字段值和该学生的id相同，也生成一条新纪录，只不过t_sc表的字段都为null。

连接和子查询的比较

如果需要频繁计算聚集数据并将其用在外查询中进行比较，一般用子查询。如果select中的列，来自多个不同的表，一般用连接。

2.5、group by和having
select语句中出现的列，都必须出现在group by子句中，除非那一列使用了聚集函数
having子句是从分组后的结果中筛选行，having的搜索条件在进行分组操作之后应用，所以它必须在group by之后使用
having子句和where子句的另一个区别在于，它可以出现聚合函数
where和having中都可以出现比较运算符，between，in，like，all，any

2.6、order by
order by columns用来对结果集进行排序，按照列顺序，嵌套排序。默认是asc升序，desc是降序。

2.7、查询集合运算
intersect：∩ 交 select1 intersect select2 取出两个结果集共有的部分
union：∪ 并取出两个结果集的所有结果，并自动去除重复行
union all：并取出两个结果集的所有结果，不会去除重复行
minus：差 select1 minus select2 从结果集1中去掉与结果集2中相同的部分
原则：
在两个select列表中的表达式必须在数目上和数据类型上相匹配
order by子句只能出现在语句的最后，从第一个select语句接受列名、别名或者位置记号
集合运算可以用在子查询中。

2.8、练习

题目为：表1和表2的相互转换
这里写图片描述
（1）将表1转换为表2

 select 
      name,
      sum(decode(course,'english',score)) english,
      sum(decode(course,'math',score)) math, 
      sum(decode(course,'chinese',score)) chinese
   from t_student group by name;

关于decode函数的使用推荐博客：http://www.cnblogs.com/juddhu/archive/2012/03/07/2383101.html

（2）将表2转换为表1

 select name, 'english' course, english score from t_stu
   union
   select name, 'math' course, math score from t_stu
   union
   select name, 'chinese' course, chinese score from t_stu;