1、基本查询
特殊运算符
|
运 算
|
功 能
|
实 例
|
|
[NOT] BETWEEN…AND…
|
用于测试是否在范围内
|
Select * from emp Where sal between 1000 and 2000
|
|
[NOT] IN (…)
|
用于测试是否在列表中
|
Select*from emp Where job in('CLERK', 'SALESMAN','ANYLYST')
|
|
[NOT] LIKE
|
用于进行模式匹配
|
Select * from emp Where ename like '%A%'
|
|
IS [NOT] NULL
|
用于测试是否为空值
|
Select * from emp Where comm is not null
|
|
ANY SOME
|
同列表或查询中的每一个值进行比较，测试是否有一个满足，前面必须使用的运算符包括=、!=、>=、<=、>、<等
|
Select * from emp Where sal<any(select sal from emp where deptno=10)
|
|
ALL
|
同列表或查询中的每一个值进行比较，测试是否所有的值都满足，前面必须使用的运算符包括=、!=、>=、<=、>、<等
|
Select*from emp Where sal<all(1000,1500,
2000)
|
|
[NOT] EXISTS
|
测试是否子查询至少返回一行
|
Select '存在雇员SCOTT' from dual where exists(select*from emp where ename='SCOTT');
|
运算的优先顺序是NOT，AND，OR。如果要改变优先顺序，可以使用括号。
缺省中文日期格式为DD-MM月-YY，如2003年1月10日应该表示为“10-1月-03”。
字符串和日期型数据的值是包含在单引号中的，如SALESMAN，需要用单引号引起。字符的值对大小写敏感。
比 较 运 算 符
|
运算符
|
功 能
|
实 例
|
|
>,<
|
大于，小于
|
Select * from emp where sal>2000
|
|
>=.<=
|
大于等于，小于等于
|
Select * from emp where sal>=2000
|
|
=
|
等于
|
Select * from emp where deptno=10
|
|
!=,<>,^=
|
不等于
|
Select * from emp where deptno!=10
|
如果要对计算列排序，可以为计算列指定别名，然后按别名排序。
别名如果含有空格或特殊字符或大小写敏感，需要使用双引号将它引起来。
表头的显示默认为全部大写。对于日期和数值型数据，右对齐显示，如deptno列。对于字符型数据，左对齐显示，如dname列。
%：代表0个或多个任意字符。_ ：代表一个任意字符。
2、函数
数值型函数
|
函 数
|
功 能
|
实 例
|
结 果
|
|
abs
|
求绝对值函数
|
abs(−5)
|
5
|
|
sqrt
|
求平方根函数
|
sqrt(2)
|
1.41421356
|
|
power
|
求幂函数
|
power(2,3)
|
8
|
|
cos
|
求余弦三角函数
|
cos(3.14159)
|
−1
|
|
mod
|
求除法余数
|
mod(1600, 300)
|
100
|
|
ceil
|
求大于等于某数的最小整数
|
ceil(2.35)
|
3
|
|
floor
|
求小于等于某数的最大整数
|
floor(2.35)
|
2
|
|
round
|
按指定精度对十进制数四舍五入
|
round(45.923, 1)
round(45.923, 0)
round(45.923,−1)
|
45.9
46
50
|
|
trunc
|
按指定精度截断十进制数
|
trunc(45.923, 1)
trunc(45.923)
trunc(45.923,−1)
|
45.9
45
40
|
字符型函数
|
函数名称
|
功 能
|
实 例
|
结 果
|
|
ascii
|
获得字符的ASCII码
|
Ascii('A')
|
65
|
|
chr
|
返回与ASCII码相应的字符
|
Chr(65)
|
A
|
|
lower
|
将字符串转换成小写
|
lower ('SQL Course')
|
sql course
|
|
upper
|
将字符串转换成大写
|
upper('SQL Course')
|
SQL COURSE
|
|
initcap
|
将字符串转换成每个单词以大写开头
|
initcap('SQL course')
|
Sql Course
|
|
concat
|
连接两个字符串
|
concat('SQL', ' Course')
|
SQL Course
|
|
substr
|
给出起始位置和长度，返回子字符串
|
substr('String',1,3)
|
Str
|
|
length
|
求字符串的长度
|
length('Wellcom')
|
7
|
|
instr
|
给出起始位置和出现的次数，求子字符串在字符串中出现的位置
|
instr('String', 'r',1,1)
|
3
|
|
lpad
|
用字符填充字符串左侧到指定长度
|
lpad('Hi',10,'-')
|
--------Hi
|
|
rpad
|
用字符填充字符串右侧到指定长度
|
rpad('Hi',10,'-')
|
Hi--------
|
|
trim
|
在一个字符串中去除另一个字符串
|
trim('S' FROM 'SSMITH')
|
MITH
|
|
replace
|
用一个字符串替换另一个字符串中的子字符串
|
replace('ABC', 'B', 'D')
|
ADC
|
SYSDATE是返回系统日期和时间的虚列函数。
使用日期的加减运算，可以实现如下功能：
* 对日期的值加减一个天数，得到新的日期。
* 对两个日期相减，得到相隔天数。
* 通过加小时来增加天数，24小时为一天，如12小时可以写成12/24(或0.5)。
日期函数
|
函 数
|
功 能
|
实 例
|
结 果
|
|
months_between
|
返回两个日期间的月数
|
months_between ('04-11月-05','11-1月-01')
|
57.7741935
|
|
add_months
|
返回把月份数加到日期上的新日期
|
add_months('06-2月-03',1)
add_months('06-2月-03',-1)
|
06-3月-03
06-1月-03
|
|
next_day
|
返回指定日期后的星期对应的新日期
|
next_day('06-2月-03','星期一')
|
10-2月-03
|
|
last_day
|
返回指定日期所在的月的最后一天
|
last_day('06-2月-03')
|
28-2月-03
|
|
round
|
按指定格式对日期进行四舍五入
|
round(to_date('13-2月-03'),'YEAR')
round(to_date('13-2月-03'),'MONTH')
round(to_date('13-2月-03'),'DAY')
|
01-1月-03
01-2月-03
16-2月-03
(按周四舍五入)
|
|
trunc
|
对日期按指定方式进行截断
|
trunc(to_date('06-2月-03'),'YEAR')
trunc(to_date('06-2月-03'),'MONTH')
trunc(to_date('06-2月-03'),'DAY')
|
01-1月-03
01-2月-03
02-2月-03
(按周截断)
|
类型转换函数
|
函 数
|
功 能
|
实 例
|
结 果
|
|
To_char
|
转换成字符串类型
|
To_char(1234.5, '$9999.9')
|
$1234.5
|
|
To_date
|
转换成日期类型
|
To_date('1980-01-01', 'yyyy-mm-dd')
|
01-1月-80
|
|
To_number
|
转换成数值类型
|
To_number('1234.5')
|
1234.5
|
日期转换格式字符
|
代 码
|
代表的格式
|
例 子
|
|
AM、PM
|
上午、下午
|
08 AM
|
|
D
|
数字表示的星期(1～7)
|
1,2,3,4,5,6,7
|
|
DD
|
数字表示月中的日期(1～31)
|
1,2,3,…,31
|
|
MM
|
两位数的月份
|
01,02,…,12
|
|
Y、YY、YYY、YYYY
|
年份的后几位
|
3,03,003,2003
|
|
RR
|
解决Y2K问题的年度转换
|
|
|
DY
|
简写的星期名
|
MON,TUE,FRI,…
|
|
DAY
|
全拼的星期名
|
MONDAY,TUESDAY,…
|
|
MON
|
简写的月份名
|
JAN,FEB,MAR,…
|
|
MONTH
|
全拼的月份名
|
JANUARY,FEBRUARY,…
|
|
HH、HH12
|
12小时制的小时(1～12)
|
1,2,3,…,12
|
|
HH24
|
24小时制的小时(0～23)
|
0,1,2,…,23
|
|
MI
|
分(0～59)
|
0,1,2,…,59
|
|
SS
|
秒(0～59)
|
0,1,2,…,59
|
|
,./-;:
|
原样显示的标点符号
|
|
|
'TEXT'
|
引号中的文本原样显示
|
TEXT
|
查询中插入中文的年月日，其中原样显示部分区别于外层的单引号，需要用双引号引起。
SELECT TO_CHAR(sysdate,'YYYY"年"MM"月"DD"日"') FROM dual;
执行结果为：
TO_CHAR(SYSDAT
-------------------------
2003年11月18日
说明：双引号中的中文字“年”、“月”、“日”原样显示，单引号为字符串的界定标记，区别于双引号，不能混淆。
对于数字型的日期格式，可以用数字或全拼格式显示，即在格式字符后面添加TH或SP。TH代表序列，SP代表全拼。
SELECT SYSDATE,to_char(SYSDATE,'yyyysp'),to_char(SYSDATE,'mmspth'),
to_char(SYSDATE,'ddth') FROM dual;
执行结果为：
SYSDATE TO_CHAR(SYSDATE,'YYYYSP') TO_CHAR( TO_C
------------- -------------------------------------------------------------- --------------- --------
07-2月 -04 two thousand four second 07th
说明：“two thousand four”为全拼表示的2004年；“second”为全拼序列表示的2月；“07th”为用序列表示的7号。
数值转换符
|
代 码
|
代表的格式
|
例 子
|
|
9
|
代表一位数字，如果是正数，前面是空格，如果是负数，前面是-号
|
9999
|
|
0
|
代表一位数字，在相应的位置上如果没有数字则出现0
|
0000
|
|
,
|
逗号，用作组分隔符
|
99,999
|
|
.
|
小数点，分隔整数和小数
|
999.9
|
|
$
|
$货币符号
|
$999.9
|
|
L
|
本地货币符号
|
L999.99
|
|
FM
|
去掉前后的空格
|
FM999.99
|
|
EEEE
|
科学计数法
|
9.9EEEE
|
|
S
|
负数符号−放在开头
|
S999.9
|
如果实际位数超过5位，则会填充为#号。
SQL> select to_char(1212121.2121,'99.99') from dual;
TO_CHA
------
######
其他常用函数
|
函 数
|
功 能
|
实 例
|
结 果
|
|
nvl
|
空值转换函数
|
nvl(null, '空')
|
空
|
|
decode
|
实现分支功能
|
decode(1,1, '男', 2, '女')
|
男
|
|
userenv
|
返回环境信息
|
userenv('LANGUAGE')
|
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
|
|
greatest
|
返回参数的最大值
|
greatest(20,35,18,9)
|
35
|
|
least
|
返回参数的最小值
|
least(20,35,18,9)
|
9
|
在ASCII码表中，排在后边的字符大，小写字母排在大写字母之后。字符串的比较原则是，先比较第一位，如果相同，则继续比较第二位，依此类推，直到出现大小关系。
常用的组函数
|
函 数
|
说 明
|
|
AVG
|
求平均值
|
|
COUNT
|
求计数值，返回非空行数，*表示返回所有行
|
|
MAX
|
求最大值
|
|
MIN
|
求最小值
|
|
SUM
|
求和
|
|
STDDEV
|
求标准偏差，是根据差的平方根得到的
|
|
VARIANCE
|
求统计方差
|
分组函数中SUM和AVG只应用于数值型的列，MAX、MIN和COUNT可以应用于字符、数值和日期类型的列。组函数忽略列的空值。
使用GROUP BY 从句可以对数据进行分组。所谓分组，就是按照列的相同内容，将记录划分成组，对组可以应用组函数。
如果不使用分组，将对整个表或满足条件的记录应用组函数。
在组函数中可使用DISTINCT或ALL关键字。ALL表示对所有非NULL值(可重复)进行运算(COUNT除外)。DISTINCT 表示对每一个非NULL值，如果存在重复值，则组函数只运算一次。如果不指明上述关键字，默认为ALL。
在查询列中，不能使用分组列以外的其他列，否则会产生错误信息。
HAVING从句过滤分组后的结果，它只能出现在GROUP BY从句之后，而WHERE从句要出现在GROUP BY从句之前。
HAVING从句的限定条件中要出现组函数。如果同时使用WHERE条件，则WHERE条件在分组之前执行，HAVING条件在分组后执行。
子查询一般出现在SELECT语句的WHERE子句中，Oracle也支持在FROM或HAVING子句中出现子查询。子查询比主查询先执行，结果作为主查询的条件，在书写上要用圆括号扩起来，并放在比较运算符的右侧。子查询可以嵌套使用，最里层的查询最先执行。子查询可以在SELECT、INSERT、UPDATE、DELETE等语句中使用。
多列子查询
如果子查询返回多列，则对应的比较条件中也应该出现多列，这种查询称为多列子查询。以下是多列子查询的训练实例。
查询职务和部门与SCOTT相同的雇员的信息。
执行以下查询：
SELECT empno, ename, sal FROM emp
WHERE (job,deptno) =(SELECT job,deptno FROM emp WHERE empno=7788);
集合运算操作
|
操 作
|
描 述
|
|
UNION
|
并集，合并两个操作的结果，去掉重复的部分
|
|
UNION ALL
|
并集，合并两个操作的结果，保留重复的部分
|
|
MINUS
|
差集，从前面的操作结果中去掉与后面操作结果相同的部分
|
|
INTERSECT
|
交集，取两个操作结果中相同的部分
|
查询部门10和部门20的所有职务。
执行以下查询：
SELECT job FROM emp WHERE deptno=10
UNION（UNION ALL" MINUS" MINUS" INTERSECT）
SELECT job FROM emp WHERE deptno=20;