ch4-操作符及SQL函数

Select rtrim(‘xyzadams’,’ams’) from dual; xyzad
Translate(char, from, to) Select translate(‘jack’,’j’ ,’b’) from dual;
back
Replace(char, searchstring,[rep string])
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 '_富%';
部分 last_day：当月最后一天 [例] select last_day(sysdate) from dual; round(date,[fmt])：四舍五入（最靠近） ‘day’：week，[fmt]请参
考Complete Reference next_day(d,day)：下周周X的日期 next_day(sysdate,’星期
SQL 函数
单行函数
分组函数
分析函数
HandsOn
单行函数分类
单行函数对于从表中查询的每一行只返回一个值 可以出现在 SELECT 子句中和 WHERE 子句中
单行函数可以大致划分为： ➢ 日期函数 ➢ 数字函数 ➢ 字符函数 ➢ 转换函数 ➢ 其他函数
HandsOn
日期函数
日期函数对日期值进行运算，并生成日期数据类 型或数值类型的结果
day [例] select extract(year from sysdate) from dual;
HandsOn
字符函数 2-1
字符函函数数接受字符输入并返输回入 字符或数值 输出
Initcap(char)
Select initcap(‘hello’) from dual;
Hello
Lower(char)
Select substr(‘abcdefg’,3,2) from dual; Select concat (‘Hello’,’ world’) from dual;
cd Hello world
字符函数
HandsOn
字符函数 2-2
以下是一些其它的字符函数：
CHR和ASCII LPAD和RPAD TRIM LENGTH DECODE
分组函数handson21分析函数根据一组行来计算聚合值用于计算完成聚集的累计排名移动平均数等分析函数为每组记录返回多个行分析函数denserankrankrownumberhandson22以下三个分析函数用于计算一个行在一组有序行中的排位序号从1开始rownumber返回连续的排位不论值是否相等rank具有相等值的行排位相同序数随后跳跃denserank具有相等值的行排位相同序号是连selectfstuidfcourseidrownumberoverpartitionfcourseidorderfgradedescrankfromtgradehandsonsql支持的操作符包括算术比较逻辑集合和连接操作符sql函数可大致分为
SSQQLL>> SSEELLEECCTT CLLPEHANRDG(6(T‘7fHu)n(F‘csRttiuoOdnMe’,n1dt5'u),’a=Fl’R;) OFRMOdMuadl;ual;
SQL> SELECT fT_RidI,Mf_(n9afmroem, 9999876789999) FROM dual; DECODE(f_calss,1,‘class one',2,‘class two‘,’other class’) class FROM t_student;
统计C语言或Java为良好的同学学号
select f_stuid from t_grade where f_grade>=70 and f_courseid = '01' UNION select f_stuid from t_grade where f_grade>=70 and f_courseid = '02';
操作符的优先级
SQL 操作符的优先级从高到低的顺序是：
算术操作符
--------最高优先级
连接操作符
比较操作符
NOT 逻辑操作符
AND 逻辑操作符
OR 逻辑操作符 --------最低优先级
HandsOn
SQL 函数
Oracle 提供一系列用于执行特定操作的函数 SQL 函数带有一个或多个参数并返回一个值 以下是SQL函数的分类：
分组函数
分组函数
AVG
MIN
MAX
SUM
SSEELLEECCTTCAOVGUN(reT_(*le) vFeRl)OFMROiteMmfiitleem; file WHERE p_category='accessories';
第四章
操作符及SQL函数
HandsOn
回顾
SQL 是通用的数据库语言 SQL 命令可分为数据定义语言、数据操纵语言、
事务控制语言和数据控制语言 Oracle 支持的数据类型包括字符、数值、日期时
间、RAW 和 LOB 等 伪列：ROWID、ROWNUM 数据完整性：实体完整性、域完整性、引用完整
日期函数包括：
➢ ADD_MONTHS ➢ MONTHS_BETWEEN ➢ LAST_DAY ➢ ROUND ➢ NEXT_DAY ➢ TRUNC ➢ EXTRACT
HandsOn
add_months(date,count)：日期的count月后的日期 [例] select add_months(f_birth,3) from t_student where f_id=‘001’; months_between：相差月数（d1,d2），d1-d2，以31天计算小数
SSEeLleEcCt TOT_OC_HNAURM(B0.E1R23(',1'00.09'9) 9F'R) FORMOdMuaDlU; AL;
HandsOn
其它函数
以下是几个用来转换空值的函数：
NVL NVL2 NULLIF
SELECT f_id, f_name, NVL(avg(f_grade),0) FROM t_student left join t_grade on f_id = f_stuid GROUP BY f_id, f_name;
HandsOn
数字函数
数字函数接受数字输入并返回数值结果
函数
Abs(n) Ceil(n) Cos(n) Floor(n) Power(m,n) Mod(m,n) Round(m,n) Trunc(m,n) Sqrt(n) Sign(n)
输入
Select abs(-15) from dual; Select ceil(44.778) from dual; Select cos(180) from dual; Select floor(100.2) from dual; Select power(4,2) from dual; Select mod(10,3) from dual; Select round(100.256,2) from dual; Select trunc(100.256,2) from dual; Select sqrt(4) from dual; Select sign(-30) from dual;
集合操作符2-2
统计C语言(01)和Java(02)都为良好(70分以上)的同学学号
select f_stuid from t_grade where f_grade>=70 and f_courseid = '01' INTERSECT select f_stuid from t_grade where f_grade>=70 and f_courseid = '02';
HandsOn
算术操作符
算术操作符用于执行数值计算 可以在SQL语句中使用算术表达式，算术表达式
由数值数据类型的列名、数值常量和连接它们的 算术操作符组成 算术操作符包括加(+)、减(-)、乘(*)、除(/)
SQL> SELECT f_stuid, f_grade + 1 as new_grade FROM t_grade WHERE f_courseid='01';
SELECT f_id, f_name, NVL2(f_department,'existed dep','other dep' as dep) from t_student;
SELECT NULLIF(100,200) FROM DUAL;
HandsOn
分组函数基于一组行来返回结果 为每一组行返回一个值
二’)=next_day(sysdate,3) trunc(d, [fmt])：vs round，只舍不入 [例] select round(sysdate,'day'),trunc(sysdate,'day') from dual; extract(fmt from d)：抽取日期部分的格式，fmt为year、month、
HandsOn
逻辑操作符
逻辑操作符用于组合多个计较运算的结果以生成 一个或真或假的结果。
逻辑操作符包括与(AND)、或(OR)和非(NOT)。
SQL> SELECT * FROM t_student WHERE f_birth >= '01-1月-80' AND f_birth <= '31-12月-85';
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)
性、自定义完整性
HandsOn
目标
掌握 SQL 操作符 掌握并熟练应用SQL 函数
➢ 单行函数：日期、字符、数字、转换、其他 ➢ 分组函数 ➢ 分析函数
HandsOn
SQL 操作符
Oracle 支持的 SQL 操作符分类如下：
SQL 操作符
算术操作符
逻辑操作符
连接操作符
比较操作符
集合操作符
显示 80-85年之间出生的学生信息
HandsOn
集合操作符2-1
集合操作符将两个查询的结果组合成一个结果
集合操作符
UNION
UNION ALL INTERSECT
MINUS
INTERSECT 操作符只返回两个查询的公共行。
MINUS 操作符返回从第一个查询结果中排除第二个查 询中出现的行。
HandsOn
输出
15 45 -.5984601 100 16 1 100.26 100.25 2 -1
数字函数
HandsOn
转换函数
转换函数将值从一种数据类型转换为另一种数据 类型
常用的转换函数有：
TO_CHAR TO_DATE TO_NUMBER
SELECT TO_CDHATAER((‘s2y0s0d5-a1t2e-,'0Y6Y’ Y, ‘Yy"y年yy"-fmmmM-Md"d月’) "fmDD"日" HH24:MI:SS') FROM dual;
HandsOn
连接操作符
连接操作符用于将多个字符串或数据值合并成一 个字符串
SQL> SELECT ('学号为' || f_id|| '的同学姓名是' ||f_name) as ddress FROM t_student;
通过使用连接操作符可以将表中 的多个列合并成逻辑上的一行列
HandsOn
SQL > SELECT f_stuid, f_grade * 1.1 as new_grade FROM t_grade WHERE f_courseid='01';
HandsOn
比较操作符
比较操作符用于比较两个表达式的值
比较操作符包括 =、!=、<、>、<=、>=、 BETWEEN…AND、IN、LIKE 和 IS NULL等
统计C语言良好但Java未达到良好的同学学号
select f_stuid from t_grade where f_grade>=70 and f_courseid = '01' MINUS select f_stuid from t_grade where f_grade>=70 and f_courseid = '02';
Instr (char, m, n)
Select replace(‘jack and jue’ ,’j’,’bl’) from dual;
Select instr (‘worldwide’,’d’) from dual;
black and blue
5
Substr (char, m, n) Concat (expr1, expr2)