Oracle操作符和SQL函数
一、操作符
1、算术操作符
- 算术操作符用于执行数值计算
- 可以在SQL语句中使用算术表达式，算术表达式由数值数据类型的列名、数值常量和连接它们的算术操作符组成
- 算术操作符包括加(+)、减(-)、乘(*)、除(/)
实例：
检索出课程号是’02’的成绩+10分后的结果
SELECT sid,cid, score+10 as lastscore Where cid = ‘02’;
2、比较操作符
- 比较操作符用于比较两个表达式的值
- 比较操作符包括 =、!=、<、>、<=、>=、BETWEEN…AND、IN、LIKE 和 IS NULL等
实例：
①检索1991年前出生的学生信息
SELECT * FROM t_student WHERE sbirthday < '01-1月-91';
②检索1990年出生的学生信息
SELECT * FROM t_student WHERE sbirthday between ‘01-1月-90‘ and ’31-12月-90’;
③检索班级是1班或2班的学生信息
SELECT * FROM t_student WHERE sclass in (1,2);
三、逻辑操作符
- 逻辑操作符用于组合多个比较运算的结果以生成一个或真或假的结果。
- 逻辑操作符包括与(AND)、或(OR)和非(NOT)，NOT通常和AND,OR联合使用。
实例：
查询 二班的 90后的学生信息
select * from student where sbirthday >='01-1月-90' and sclass = 2;
四、集合操作符
集合操作符：
将两个查询的结果组合成一个结果
1、UNION 操作符：返回两个查询的不重复的所有行。（类似or 联集）
注意：UNION的前提是两个表的查询结果条数要一致
2、INTERSECT 操作符：只返回两个查询的公共行。（类似于and 交集）
3、MINUS 操作符返回从第一个查询结果中排除第二个查询中出现的行。（类似于差集）
实例：
①统计Oracle(01) 和 Jsp(07)都为良好(70分以上)的同学学号
select sid from t_score where score>=70 and cid = '01' INTERSECT select sid from t_score where score>=70 and cid = '07';
②统计学习Oracle(01) 或 Jsp(07)的同学学号
select sid from t_score where score>=70 and cid = '01' UNION select sid from t_score where score>=70 and cid = '07';
③统计Oracle(01)良好但Jsp(07)未达到良好的同学学号
select sid from t_score where score>=70 and cid = '01' MINUS select sid from t_score where score>=70 and cid = '07'
五、连接操作符
- 连接操作符用于将多个字符串或数据值合并成一个字符串
- 通过使用连接操作符可以将表中的多个列合并成逻辑上的一行列
SELECT ('学号为' || SID|| '的同学姓名是' || SNAME) as SINFO FROM t_student;
操作符的优先级
SQL 操作符的优先级从高到低的顺序是：
- 算术操作符 --------最高优先级
- 连接操作符
- 比较操作符 NOT
- 逻辑操作符 AND
- 逻辑操作符 OR
- 逻辑操作符 --------最低优先级
二、SQL函数
Oracle 提供一系列用于执行特定操作的函数
SQL 函数带有一个或多个参数并返回一个值
以下是SQL函数的分类：
①单行函数②分组函数③分析函数
单行函数对于从表中查询的每一行只返回一个值
可以出现在 SELECT子句中和 WHERE 子句中
单行函数可以大致划分为：
- 字符函数
- 数字函数
- 日期函数
- 转换函数
- 其他函数
1、字符函数：
字符函数接受字符输入并返回字符或数值
以下是一些其它的字符函数
- CHR ：根据ASCII码返回对应的字符
- LPAD和RPAD ：填充
- TRIM ：去除左右两边的空格符
- LENGTH ：字符串长度
- DECODE :逐个值替换
SELECT LENGTH(‘student') FROM dual;
SELECT sname , DECODE(ssex,’m’,‘男’,‘f‘,’女’) as ssex FROM t_student;
2、数字函数：
数字函数接受数字输入并返回数值结果
日期函数对日期值进行运算，并生成日期数据类型或数值类型的结果
日期函数包括：
- ADD_MONTHS
- MONTHS_BETWEEN
- LAST_DAY
- ROUND
- TRUNC
- NEXT_DAY
- EXTRACT 计算年份差
3、转换函数：
转换函数将值从一种数据类型转换为另一种数据类型
常用的转换函数有：
- 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;
4、其它函数
以下是几个用来转换空值的函数 （scott.emp）
- NVL
- NVL2
- NULLIF
查询员工工资和（工资sal+奖金comm）
Select empno ,sal +comm from emp;
Select empno ,sal + nvl(comm,0) from emp;
SELECT empno,sal + nvl2(comm,comm,0) from emp;
SELECT NULLIF(100,200) FROM DUAL;
5、分组函数
分组函数基于一组行来返回结果
为每一组行返回一个值
- AVG
- MIN
- MAX
- SUM
- COUNT
6、分析函数
分析函数根据一组行来计算聚合值
用于计算完成聚集的累计排名、移动平均数等
分析函数为每组记录返回多个行
- ROW_NUMBER
- RANK
- DENSE_RANK
ROW_NUMBER 返回连续的排位，不论值是否相等
select ename,job,deptno,sal,row_number() over(order by sal desc) as sal_rank from scott.emp;
RANK 具有相等值的行排位相同，序数随后跳跃
DENSE_RANK 具有相等值的行排位相同，序号是连续的