《数据库》第九章 操作符及sql函数

统计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';
16
Inspur Education
数字函数
数字函数接受数字输入并返回数值结果
函数
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
以下三个分析函数用于计算一个行在一组有序行中的排位，序号从1开始
ROW_NUMBER 返回连续的排位，不论值是否相等 RANK 具有相等值的行排位相同，序数随后跳跃 DENSE_RANK 具有相等值的行排位相同，序号是连续的
select f_stuid,f_courseid,row_number() over (partition by f_courseid order by f_grade desc) as rank
比较操作符
集合操作符
5
Inspur Education
比较操作符
比较操作符用于比较两个表达式的值 比较操作符包括 =、!=、<、>、<=、>=、BETWEEN…AND、
IN、LIKE 和 IS NULL等
SQL> SELECT f_id, f_name, f_birth FROM t_student WHERE f_birth < '01-1月-86';
统计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';
SQL 函数
单行函数
12
分组函数
分析函数
Inspur Education
单行函数分类
单行函数对于从表中查询的每一行只返回一个值 可以出现在 SELECT 子句中和 WHERE 子句中 单行函数可以大致划分为：
日期函数 数字函数 字符函数 转换函数 其他函数
13
Inspur Education
函数
Initcap(char) Lower(char) Upper(char) Ltrim(char,set) Rtrim(char,set) Translate(char, from, to) Replace(char, searchstring,[rep string]) Instr (char, m) Substr (char, m, n) Concat (expr1, expr2)
SELECT NULLIF(100,200) FROM DUAL;
19
Inspur Education
分析函数 2-1
分析函数根据一组行来计算聚合值 用于计算完成聚集的累计排名、移动平均数等 分析函数为每组记录返回多个行
分析函数
ROW_NUMBER
RANK
DENSE_RANK
20
Inspur Education
输出
Hello fun SUN adams xyzad back black and blue 5 cd Hello world
15
Inspur Education
字符函数 2-2
以下是一些其它的字符函数：
CHR和ASCII LPAD和RPAD TRIM LENGTH DECODE
SQELLE>CSTELPEACNDTG(TC‘fuHHn(R‘cst(ti6uo7dn)e’,1Fn5tR'),O’=FM’R) OFdRMuaOdl;Muadl;ual; SELECT f_id,f_name, DSQECL>ODSEL(fE_cCaTlsTsR,1I,M‘c(la9sfsroomne9',929,‘9c8la7s6s78tw9o9‘9,’9o)thFeRr OclMassd’u)acll;ass FROM t_student;
Inspur Education
第9章 操作符及SQL函数
Inspur Education
知识点回顾
表连接原理 多表连接查询
• 等值连接 • 非等值连接 • 交叉连接 • 内连接 • 外连接 • 特殊连接
子查询的概念及应用
• 单行子查询 • 多行子查询
2
Inspur Education
本章目标
掌握 SQL 操作符 掌握并熟练应用SQL 函数
8
Inspur Education
集合操作符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';
日期函数
日期函数对日期值进行运算，并生成日期数据类型或数值类型的结果 日期函数包括：
ADD_MONTHS MONTHS_BETWEEN LAST_DAY ROUND NEXT_DAY TRUNC EXTRACT
14
Inspur Education
字符函数 2-1
字符函数接受字符输入并返回字符或数值
from t_grade
21
总结
Inspur Education
SQL 支持的操作符包括算术、比较、逻辑、集合和连接操作符
SQL 函数可大致分为：
单行函数：日期、字符、数字、转换、其他 聚合函数 分析函数
22
Inspur Education
谢谢！
23
17
输出
15 45 -.5984601 100 16 1 100.26 100.25 2 -1
Inspur Education
转换函数
转换函数将值从一种数据类型转换为另一种数据类型 常用的转换函数有：
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;
输入
select initcap('hello') from dual; select lower('FUN') from dual; select upper('sun')from dual; select ltrim( 'xyzadams','xyz') from dual; select rtrim('xyzadams','ams') from dual; select translate('jack','j' ,'b') from dual; select replace('jack and jue' ,'j','bl') from dual; select instr ('worldwide','d') from dual; select substr('abcdefg',3,2) from dual; select concat ('Hello',' world') from dual;
18
Inspur Education
其它函数
以下是几个用来转换空值的函数：
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; SELECT f_id, f_name, NVL2(f_department,'existed dep','other dep' as dep) from t_student;
操作符的优先级
SQL 操作符的优先级从高到低的顺序是：
算术操作符
--------最高优先级
连接操作符
比较操作符
NOT 逻辑操作符
AND 逻辑操作符
OR 逻辑操作符 --------最低优先级
11
Inspur Education
SQL 函数
Oracle 提供一系列用于执行特定操作的函数 SQL 函数带有一个或多个参数并返回一个值 以下是SQL函数的分类：
SELECT f_stuid, f_grade * 1.1 as new_grade FROM t_grade WHERE f_courseid='01';
4
Inspur Education
SQL 操作符 Oracle 支持的 SQL 操作符分类如下：
SQL 操作符
算术操作符
逻辑操作符
连接操作符
显示 80-85年之间出生的学生信息
7
Inspur Education
集合操作符2-1
集合操作符将两个查询的结果组合成一个结果
集合操作符
UNION
UNION ALL INTERSECT
MINUS
INTERSECT 操作符只返回两个查询的公共行。
MINUS 操作符返回从第一个查询结果中排除第二个查 询中出现的行。
9
Inspur Education
连接操作符 连接操作符用于将多个字符串或数据值合并成一个字符串
SELECT ('学号为' || f_id|| '的同学姓名是' ||f_name) as ddress FROM t_student;
通过使用连接操作符可以将表中 的多个列合并成逻辑上的一行列
10
ห้องสมุดไป่ตู้
Inspur Education
6
Inspur Education
逻辑操作符
逻辑操作符用于组合多个比较运算的结果以生成一个或真或假的结果。 逻辑操作符包括与(AND)、或(OR)和非(NOT)。
SELECT * FROM t_student WHERE f_birth >= '01-1月-80' AND f_birth <= '31-12月-85';
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 '_富%';
单行函数：日期、字符、数字、转换、其他 分组函数 分析函数
3
Inspur Education
算术操作符
算术操作符用于执行数值计算 可以在SQL语句中使用算术表达式，算术表达式由数值数据类型的列名、
数值常量和连接它们的算术操作符组成 算术操作符包括加(+)、减(-)、乘(*)、除(/)
SELECT f_stuid, f_grade + 1 as new_grade FROM t_grade WHERE f_courseid='01';