Oracle中的虚拟表
虚拟表:dual
查看当前用户所有的数据表:
SELECT * FROM tab ;
以当前用户身份访问其他用户表
SELECT * FROM表所属用户.表名 ;
查询表结构
DESC表名 ;
简单查询语句
SELECT [DISTINCT] *或字段 [别名] [,字段 [别名]]
FROM表名称 [别名] ;
连接查询字段与显示原样输出(||和'')
限定查询子句
SELECT [DISTINCT] *或字段 [别名] [,字段 [别名]]
FROM 表名称 [别名]
[WHERE(S)];
限定查询中可以使用的操作符:
关系运算:+ 、- 、* 、/ 、!= 、<> 、AND 、OR ,NOT
范围判断:BETWEEN最小值 AND最大值
判断空:IS[NOT] NULL
指定范围判断:IN (指定匹配)
模糊查询:LIKE ( '_(匹配单个字符)或%(匹配多个字符)' )
数据的排序子句
SELECT [DISTINCT] *或字段 [别名] [,字段 [别名]]
FROM 表名称 [别名]
[WHERE(S)]
[ORDER BY字段 [ASC|DESC][,字段[ASC|DESC],…]];
排序子句的操作符:
升序(默认):ASC
降序(用户指定):DESC
字符函数
UPPER(字符串|列):将输入的字符串变为大写返回
LOWER(字符串|列):将输入的字符串变为小写返回
INITCAP(字符串|列):开头首字母大写
LENGTH(字符串|列):求出字符串的长度
REPLACE(字符串|列,’需要替换掉的’,’要替换成的’):进行替换
SUBSTR(字符串|列,开始点[,结束点]):字符串截取
Oracle数据库中,SUBSTR()函数中从0或1开始截取效果是一样的,
并且SUBSTR()支持设置负值,表示由后面指定截取开始点
数字函数
ROUND(数字|列[,保留小数的位数]):四舍五入截取的操作
TRUNC(数字|列[,保留小数的位数]):舍弃指定位数操作
MOD(数字1,数字2):取模,求余数
其中保留小数的位数可以使用负值,表示截取整数部分
日期函数
SYSDATE:获取当前日期
日期 +数字 = 日期:表示若干天后的日期
日期 -数字 = 日期:表示若干天前的日期
日期 -日期 = 数字:表示两个日期间的天数,要用大日期-小日期
LAST_DAY(日期):求出指定日期当月的最后一天
NEXT_DAY(日期,星期数):求出距离指定日期最近的指定星期
ADD_MONTHS(日期,数字):求出若干月之后的日期
MONTHS_BETWEEN(日期 1,日期 2):求出两个日期间隔多少月
转换函数
TO_CHAR(字符串|列,格式字符串):将日期或者是数字变为字符串显示
TO_DATE(字符串,格式字符串):将字符串变为DATE数据显示
TO_NUMBER(字符串):将字符串变为数字显示
转换函数相关格式字符:
年(yyyy)、月(mm)、日(dd),消除前导0(fm)
小时(hh)、分(mi)、秒(ss),24小时显示制(hh24)
数字按位格式化标志(9),当前所在语言环境下的货币符号(L)
通用函数
NVL(列,该列中null转换成的值):列中NULL值的处理函数
DECODE(数值|列,判断值1,显示值1,判断值2,显示值2……):类似if…else的多数值判断函数
DECODE()中,如果只判断替换部分列,没替换的部分则为空显示
多表查询
SELECT [DISTINCT] *或字段 [别名] [,字段 [别名]]
FROM 表1名称 [别名] ,表2名称 [别名]……
[WHERE(S)]
[ORDER BY 字段[ASC|DESC][, 字段 [ASC|DESC],…]];
笛卡尔积与左右连接
1、 Oracle语法:(+)=、=(+)
2、 SQL1999语法:
l 交叉连接(CROSS JOIN):用于产生笛卡尔积
l 自然连接(NATURAL JOIN):
自动找到匹配的关联字段,消除笛卡尔积,设置关联字段需要通过约束指定
l JOIN…USING子句: 用户自己指定一个消除笛卡尔积的关联字段
l JOIN…ON子句:用户自己指定一个可以消除笛卡尔积的关联条件
l 连接方向的改变:
Ø 左(外)连接:LEFT OUTER JOIN … ON ;à作用同Oracle中的(+)
Ø 右(外)连接:RIGHT OUTER JOIN … ON ;à 作用同Oracle中的(+)
Ø 全(外)连接:FULL OUTER JOIN … ON ;à把两张表中没有的数据都显示出来
统计函数
Ø COUNT():查询表中的数据记录
Ø AVG():求出平均值
Ø SUM():求各
Ø MAX():求最大值
Ø MIN():求最小值
分组查询子句:
FROM 表名称 [别名]
[WHERE(S)]
[GROUP BY分组字段1[,分组字段2…]]
[ORDER BY 字段 [ASC|DESC][, 字段 [ASC|DESC],…]];
HAVING子句
FROM 表名称 [别名]
[WHERE(S)]
[GROUP BY 分组字段1[,分组字段2…]]
[HAVING分组后的过滤条件(可以使用统计函数)]
[ORDER BY 字段 [ASC|DESC][, 字段 [ASC|DESC],…]];
子查询
SELECT [DISTINCT] * | 分组字段1 [别名][, 分组字段2 [别名]…] | 统计函数 [,(
SELECT [DISTINCT] * |分组字段1 [别名][, 分组字段2 [别名]…] | 统计函数
FROM表名称 [别名][,表名称 [别名]...]
[WHERE条件(S)]
[GROUP BY分组字段1[,分组字段2…]]
[HAVING分组后的过滤条件(可以使用统计函数)]
[ORDER BY字段[ASC|DESC][,字段 [ASC|DESC],…]]
)]
FROM 表名称 [别名] [,表名称 [别名]...] [,(
SELECT [DISTINCT] * |分组字段1 [别名][, 分组字段2 [别名]…] | 统计函数
FROM表名称 [别名][,表名称 [别名]...]
[WHERE条件(S)]
[GROUP BY分组字段1[,分组字段2…]]
[HAVING分组后的过滤条件(可以使用统计函数)]
[ORDER BY字段[ASC|DESC][,字段 [ASC|DESC],…]]
)]
[WHERE 条件(S) (
SELECT [DISTINCT] * |分组字段1 [别名][, 分组字段2 [别名]…] | 统计函数
FROM表名称 [别名][,表名称 [别名]...]
[WHERE条件(S)]
[GROUP BY分组字段1[,分组字段2…]]
[HAVING分组后的过滤条件(可以使用统计函数)]
[ORDER BY字段[ASC|DESC][,字段 [ASC|DESC],…]]
)]
[GROUP BY 分组字段1[,分组字段2…]]
[HAVING 分组后的过滤条件(可以使用统计函数)]
[ORDER BY 字段[ASC|DESC][, 字段 [ASC|DESC],…]];
Ø WHERE子句中的子查询:一般只返回单行单列、多行多列、单行多列的数据
n IN用于指定一个子查询的判断范围
n ANY与每一个内容相匹配,有三种匹配形式
² =ANY:与IN操作符完全一样
² >ANY:比子查询中返回记录最小值还要大的数据
²
n ALL与每一个内容相匹配,有两种匹配形式:
² >ALL:比子查询中返回记录最大值还要大的数据
²
Ø FROM子句中的子查询:一般返回多行多列的数据,当作一张临时表
数据表复制(Oracle语法):
CREATE TABLE新表名称 AS SELECT * FROM复制的表 ;
数据的增加
INSERT INTO表名称 [(字段1,字段2 ...)]
VALUES (值1,值2 ...) ;
数据的修改
UPDATE表名称
SET更新字段1=更新值1,更新字段2=更新值2 ...
[ WHERE更新条件(S)] ;
数据的删除
DELETE FROM表名称
[ WHERE删除条件(S)] ;
事务处理
Ø 事务的回滚(ROLLBACK):更新操作回到原点即最近一次事务提交后
Ø 事务的提交(COMMIT):真正的发出更新操作一但提交之后无法回滚
数据伪列
Ø ROWNUM:为每一个显示的记录自动随着查询生成行号
Ø ROWID:表示的是每一行数据保存的物理地址的编号(AAAMfNAAEAAAAAQAAA)
n 数据对象号:AAAMfN
n 相对文件号:AAE
n 数据块号:AAAAAQ
n 数据行号:AAA