oracle二操作符及SQL函数：单行函数，分析函数，聚合函数，SQL支持的操作符

最新推荐文章于 2024-01-10 20:22:16 发布

原创最新推荐文章于 2024-01-10 20:22:16 发布 · 602 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#Oracle单行函数 #分析函数 #聚合函数 #SQL操作符

Oracle数据库专栏收录该内容

9 篇文章

订阅专栏

摘要：算术、比较、逻辑、集合和连接操作符及操作符优先级，字符函数，数字函数，日期函数，转换函数，分析函数，其他函数。

SQL操作符

SQL操作符主要分为：算数操作符，比较操作符，逻辑操作符，集合操作符，连接操作符。用法较为简单。
算数操作符：
- 用于执行数值计算
- 可以在SQL语句中使用算术表达式，算术表达式由数值数据类型的列名、数值常量和连接它们的算术操作符组成
- 算术操作符包括加(+)、减(-)、乘(*)、除(/)
  如：检索出课程号是2的成绩+10分后的结果

SELECT sid,cid, score+10 as lastscore from t_score Where cid = 2;

比较操作符
- 比较操作符用于比较两个表达式的值
- 比较操作符包括 =、!=、<、>、<=、>=、BETWEEN…AND、IN、LIKE 和 IS NULL等
用法示例
检索1980年前出生的学生信息

SELECT * FROM t_student
WHERE sbirthday < '01-1月-1980';

检索1986年出生的学生信息

SELECT * FROM t_student
WHERE sbirthday between ‘01-1月-1986‘ and ’31-12月-1986’;

检索班级是1班或2班的学生信息

SELECT * FROM t_student WHERE sclass in (1,2);

逻辑操作符
- 用于组合多个比较运算的结果以生成一个或真或假的结果。
- 逻辑操作符包括与(AND)、或(OR)和非(NOT)，NOT通常和AND,OR联合使用。
查询 1班的 80后的学生信息

SELECT * FROM t_studenWHERE sbirthday >= '01-1AND sclass = 2;

连接操作符
- 使用连接操作符可以将表中的多个列合并成逻辑上的一行列
  如：

select ('学号为' || sid|| '的同学姓名是' || sname) as 学生信息 from t_student;

集合操作符
有union，union all，intersect，minus。
union 操作符返回两个查询的不重复的所有行。=(AUB)-(A∩B)
intersect 操作符只返回两个查询的公共行。=AUB
minus 操作符返回从第一个查询结果中排除第二个查询中出现的行。=A-B
如统计学习操作系统(1)或数据结构(2)的同学学号

select sid from t_score where score>=70 and cid = 1
UNION
select sid from t_score where score>=70 and cid = 2;

如，统计操作系统(1) 和数据结构(2)都为及格(60分以上)的同学学号

select sid from t_score where score>=60 and cid = 1
INTERSECT
select sid from t_score where score>=60 and cid = 2;

如统计操作系统(1)70分但数据结构(2)未达到65的同学学号

select sid from t_score where score>=70 and cid = 1
MINUS
select sid from t_score where score>=65 and cid = 2;

操作符的优先级
SQL 操作符的优先级从高到低的顺序是：
算术操作符 --------最高优先级
连接操作符
比较操作符
NOT 逻辑操作符
AND 逻辑操作符
OR 逻辑操作符 --------最低优先级

SQL 函数

Oracle 提供一系列用于执行特定操作的函数,SQL 函数带有一个或多个参数并返回一个值

SQL函数分类：单行函数，分组函数，分析函数。
单行函数
- 单行函数对于从表中查询的每一行只返回一个值
- 可以出现在 SELECT 子句中和 WHERE 子句中
- 单行函数可以大致划分为：字符函数，数字函数，日期函数，转换函数，其他函数。
字符函数接受字符输入并返回字符或数值
一些其它的字符函数
CHR ：根据ASCII码返回对应的字符
LPAD和RPAD ：填充
TRIM ：去除左右两边的空格符
LENGTH ：字符串长度
DECODE :逐个值替换（类似与case end/switch结构）
示例：

SQL> SELECT LPAD(‘function’,15,’=’) FROM dual;

SQL> SELECT TRIM(9 from 9999876789999) FROM dual;

SELECT LENGTH(‘student') FROM dual;

SELECT sname , 
DECODE(ssex,’m’,‘男’,‘f‘,’女’) as ssex
FROM t_student;

数字函数
数字函数接受数字输入并返回数值结果
日期函数
日期函数对日期值进行运算，并生成日期数据类型或数值型的结果
日期函数包括：
 ADD_MONTHS：日期加月数
 MONTHS_BETWEEN：求两个日期间相差的月数
 LAST_DAY：求日期所在月的最后一天
 ROUND：日期的四舍五入
 TRUNC：截取本年、本月、本季度的第一天等
 NEXT_DAY：求下一个星期几对应的日期
 EXTRACT：获得日期某个部分，如年月日
转换函数
转换函数将值从一种数据类型转换为另一种数据类型
常用的转换函数有：
 TO_CHAR :按照指定的格式转化字符串
 TO_DATE :将字符串转化成日期
 TO_NUMBER :将数字字符串转化成数字
示例：

Select TO_CHAR(0.123,‘$0.9999’) FROM DUAL;
SELECT TO_CHAR(sysdate,'YYYY"年"fmMM"月"fmDD"日" HH24:MI:SS')FROM dual;
SELECT TO_DATE(‘2005-12-06’ , ‘yyyy-mm-dd’)FROM dual;
SELECT TO_NUMBER('100') FROM dual;

其它函数
以下是几个用来转换空值的函数（scott.emp）
- NVL（ string1 , replace_with ）如果string1为NULL，则NVL函数返回replace_with的值，否则返回string1的值
- NVL2 （ E1, E2, E3）如果E1为NULL，则函数返回E3，若E1不为null，则返回E2
- NULLIF NULLIF（（表达式表达式11，表达式，表达式22））如果表达式如果表达式11和表达式和表达式22相等则返回空相等则返回空值，如果表达式1和表达式2不相等则返回表达式1的结果
  示例：

Select empno ,sal + nvl(comm,0) from emp;
SELECT empno,sal + nvl2(comm,comm,0) from emp;
SELECT NULLIF(100,200) FROM DUAL;
Select empno ,sal +comm from emp;

分组函数
- 分组函数基于一组行来返回结果
- 为每一组行返回一个值
  有 max,min,sum,count,avg,上述说过，这里不再介绍。
分析函数
- 分析函数根据一组行来计算聚合值
- 用于计算完成聚集的累计排名、移动平均数等
- 分析函数为每组记录返回多个行
  有row_number，rank，dense_rank三类。其均用于计算一个行在一组有序行中的排位，序号从1开始。
- ROW_NUMBER 返回连续的序号，不论值是否相等
- RANK 具有相等值的行排位相同，序号随后跳跃
- DENSE_RANK 具有相等值的行排位相同，序号是连续的