【安博培训技术】Oracle3 操作符及SQL函数20130909

操作符及SQL函数


回顾
SQL 是通用的数据库语言
SQL 命令可分为数据定义语言、数据操纵语言、事务控制语言和数据控制语言
Oracle 支持的数据类型包括字符、数值、日期时间、RAW 和 LOB 等
伪列：ROWID、ROWNUM
数据完整性：实体完整性、域完整性、引用完整性、自定义完整性


目标
掌握 SQL 操作符
掌握并熟练应用SQL 函数
单行函数：日期、字符、数字、转换、其他
分组函数
分析函数


SQL 操作符
算术操作符 比较操作符 逻辑操作符 集合操作符 连接操作符


算术操作符
算术操作符用于执行数值计算
可以在SQL语句中使用算术表达式，算术表达式由数值数据类型的列名、数值常量和连接它们的算术操作符组成
算术操作符包括加(+)、减(-)、乘(*)、除(/)
SQL> SELECT empno, ename,sal+100 new_sal from emp
SQL > SELECT empno, ename,comm*1.5 new_comm from emp


比较操作符 
比较操作符用于比较两个表达式的值
比较操作符包括 =、!=、<、>、<=、>=、BETWEEN…AND、IN、LIKE 和 IS NULL等
SQL> SELECT f_id, f_name, f_birth
     FROM  t_student
     WHERE f_birth < '01-1月-86';
SQL> SELECT f_id FROM t_student
     WHERE f_birth IN (‘01-8月-84’,‘30-8月-85');
SQL> SELECT f_id,f_name,f_sex,f_birth
     FROM t_student
     WHERE f_name LIKE '_富%';


逻辑操作符 
逻辑操作符用于组合多个计较运算的结果以生成一个或真或假的结果。
逻辑操作符包括与(AND)、或(OR)和非(NOT)。 
SQL> SELECT * FROM t_student 
     WHERE f_birth >= '01-1月-80' 
     AND f_birth <= '31-12月-85';


集合操作符 
集合操作符将两个查询的结果组合成一个结果
UNION UNION ALLINTERSECTINTERSECT
INTERSECT 操作符只返回两个查询的公共行。
MINUS 操作符返回从第一个查询结果中排除第二个查询中出现的行。
SELECT employee_id, job_id FROM employees
UNION
SELECT employee_id, job_id FROM job_history;
SELECT employee_id, job_id, department_id FROM employees
UNION ALL
SELECT employee_id, job_id, department_id FROM job_history
ORDER BY employee_id;
SELECT employee_id, job_id FROM employees
INTERSECT
SELECT employee_id, job_id FROM job_history;
SELECT employee_id,job_id FROM employees
MINUS
SELECT employee_id,job_id FROM job_history;


连接操作符
连接操作符用于将多个字符串或数据值合并成一个字符串
SQL> SELECT ('学号为' || f_id|| '的同学姓名是' ||f_name) as ddress
     FROM t_student;
通过使用连接操作符可以将表中的多个列合并成逻辑上的一行列


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


SQL 函数
Oracle 提供一系列用于执行特定操作的函数
SQL 函数带有一个或多个参数并返回一个值
以下是SQL函数的分类：
Oracle 提供一系列用于执行特定操作的函数
SQL 函数带有一个或多个参数并返回一个值
以下是SQL函数的分类：
SQL 函数
单行函数 分组函数 分析函数


单行函数分类
单行函数对于从表中查询的每一行只返回一个值
可以出现在 SELECT 子句中和 WHERE 子句中 
单行函数可以大致划分为：
日期函数
数字函数
字符函数
转换函数
其他函数


日期函数对日期值进行运算，并生成日期数据类型或数值类型的结果
日期函数包括：
ADD_MONTHS
MONTHS_BETWEEN
LAST_DAY
ROUND
NEXT_DAY
TRUNC
EXTRACT


字符函数
字符函数接受字符输入并返回字符或数值
   函数   输入  输出
Initcap(char) Select initcap(‘hello’) from dual;Hello 
Lower(char) Select lower(‘FUN’) from dual;fun 
Upper(char) Select upper(‘sun’) from dual;SUN 
Ltrim(char,set) Select ltrim( ‘xyzadams’,’xyz’) from dual;adams
Rtrim(char,set) Select rtrim(‘xyzadams’,’ams’) from dual;xyzad 
Translate(char, from, to) Select translate(‘jack’,’j’ ,’b’) from dual;back 
Replace(char, searchstring,[rep string]) Select replace(‘jack and jue’ ,’j’,’bl’) from dual;black and blue 
Instr (char, m, n) Select instr (‘worldwide’,’d’) from dual;5 
Substr (char, m, n) Select substr(‘abcdefg’,3,2) from dual;cd 
Concat (expr1, expr2) Select concat (‘Hello’,’ world’) from dual;Hello world


以下是一些其它的字符函数：
CHR和ASCII
LPAD和RPAD
TRIM
LENGTH
DECODE


SQL> SELECT LENGTH(‘student') FROM dual;
SQL> SELECT f_id,f_name, 
DECODE(f_calss,1,‘class one',2,‘class two‘,’other class’) class 
FROM t_student;


数字函数
数字函数接受数字输入并返回数值结果
   函数   输入  输出
Abs(n) Select abs(-15) from dual; 15
Ceil(n) Select ceil(44.778) from dual; 45
Cos(n) Select cos(180) from dual; -.5984601 
Floor(n) Select floor(100.2) from dual; 100
Power(m,n) Select power(4,2) from dual; 16 
Mod(m,n) Select mod(10,3) from dual; 1
Round(m,n) Select round(100.256,2) from dual;100.26 
Trunc(m,n) Select trunc(100.256,2) from dual;100.25 
Sqrt(n) Select sqrt(4) from dual; 2 
Sign(n) Select sign(-30) from dual;-1


日期格式元素


转换函数
转换函数将值从一种数据类型转换为另一种数据类型
常用的转换函数有：
TO_CHAR
TO_DATE
TO_NUMBER
SELECT TO_DATE(‘2005-12-06’ , ‘yyyy-mm-dd’)
FROM dual;
SELECT TO_CHAR(sysdate,'YYYY"年"fmMM"月"fmDD"日" HH24:MI:SS')
FROM dual;　　　　
SELECT TO_NUMBER('100') FROM dual;
Select TO_CHAR(0.123,'0.999') FROM DUAL;　


其它函数
以下是几个用来转换空值的函数：
NVL
NVL2
NULLIF    如果相等返回空
select employee_id, last_name ,nvl(commission_pct,0) from employees;
select employee_id, last_name ,nvl2(commission_pct,commission_pct,0) from employees;
select nullif(100,100) from dual;


分组函数
分组函数基于一组行来返回结果
为每一组行返回一个值
分组函数
AVG MIN MAX SUM COUNT
SELECT COUNT(*) FROM itemfile;
SELECT AVG(re_level) FROM  itemfile 
WHERE p_category='accessories';
SELECT COUNT(itemrate) FROM itemfile; 
SELECT MAX(max_level) FROM  itemfile;
SELECT COUNT(DISTINCT qty_hand) FROM itemfile;
SELECT SUM(itemrate*max_level) FROM itemfile;


分析函数 
分析函数根据一组行来计算聚合值
用于计算完成聚集的累计排名、移动平均数等
分析函数为每组记录返回多个行
分析函数
ROW_NUMBER RANK DENSE_RANK


以下三个分析函数用于计算一个行在一组有序行中的排位，序号从1开始
ROW_NUMBER 返回连续的排位，不论值是否相等
RANK 具有相等值的行排位相同，序数随后跳跃
DENSE_RANK 具有相等值的行排位相同，序号是连续的
select employee_id , last_name,salary  , row_number() over(order by salary desc  ) as rank from employees;


找出工资前三名的雇员：
select employee_id , last_name , salary  from employees
  where rownum<=3
order by salary desc
找出工资排名在5-10名的雇员：
select employee_id, last_name, salary from(select employee_id , last_name , salary ,rownum rn from employees order by salary desc) emp where emp.rn>5 and emp.rn<10


总结
SQL 支持的操作符包括算术、比较、逻辑、集合和连接操作符
SQL 函数可大致分为：
单行函数：日期、字符、数字、转换、其他
聚合函数
分析函数