【Oracle】常用命令汇总

本文基于黑马程序员文档做的二次总结，如有侵权，请联系本人删除。

字段定义

创建表空间

create tablespace waterboss
datafile 'c:\waterboss.dbf'
size 100m
autoextend on
next 10m;

waterboss 为表空间名称
datafile 用于设置物理文件名称
size 用于设置表空间的初始大小
autoextend on 用于设置自动增长，如果存储量超过初始大小，则开始自动扩容
next 用于设置扩容的空间大小

创建用户

create user wateruser
identified by itcast
default tablespace waterboss;

wateruser 为创建的用户名
identified by 用于设置用户的密码
default tablesapce 用于指定默认表空间名称

用户赋权

grant dba to wateruser

DBA: 拥有全部特权，是系统最高权限，只有 DBA 才可以创建数据库结构。
RESOURCE:拥有 Resource 权限的用户只可以创建实体，不可以创建数据库结构。
CONNECT:拥有 Connect 权限的用户只可以登录 Oracle，不可以创建实体，不可以创建数据库结构。
对于普通用户：授予 connect, resource 权限。
对于 DBA 管理用户：授予 connect，resource, dba 权限。

创建表

create table t_owners
(
id number primary key,
name varchar2(30),
addressid number,
housenumber varchar2(30),
watermeter varchar2(30),
adddate date,
ownertypeid number
);

数据类型：

字符型
（1）CHAR : 固定长度的字符类型，最多存储 2000 个字节
（2）VARCHAR2 :可变长度的字符类型，最多存储 4000 个字节（3）LONG : 大文本类型。最大可以存储 2 个 G
2.数值型
NUMBER : 数值类型
例如：NUMBER(5) 最大可以存的数为 99999
NUMBER(5,2) 最大可以存的数为 999.99
3.日期型
（1）DATE：日期时间型，精确到秒
（2）TIMESTAMP：精确到秒的小数点后 9 位
4.二进制型（大数据类型）
（1）CLOB : 存储字符,最大可以存 4 个 G
（2）BLOB：存储图像、声音、视频等二进制数据,最多可以存 4 个 G

表增加字段

--追加字段
ALTER TABLE T_OWNERS ADD
(
 REMARK VARCHAR2(20),
 OUTDATE DATE
);

表更改字段类型

ALTER TABLE T_OWNERS MODIFY
(
REMARK CHAR(20) ,
OUTDATE TIMESTAMP
);

表字段重命名

ALTER TABLE T_OWNERS RENAME COLUMN OUTDATE TO EXITDATE;

表删除字段

# ALTER TABLE T_OWNERS DROP COLUMN REMARK;
ALTER TABLE T_OWNERS DROP (REMARK,EXITDATE);

删除表

# DROP TABLE T_OWNERS;

记录的增删改

表插入记录

insert into T_OWNERS VALUES (1,' 张三丰',1,'2-2','5678',sysdate,1);
insert into T_OWNERS (id ,name,addressid,housenumber,watermeter ,adddate,ownertypeid) VALUES (2,'赵大侃',1,'2-3','9876',sysdate,1);
commit;

表修改记录

update T_OWNERS set adddate=adddate-3 where id=1;
commit;

表删除记录

delete from T_OWNERS where id=2;
commit;

# TRUNCATE TABLE T_OWNERS

比较 truncat 与 delete 实现数据删除？

delete 删除的数据可以 rollback
delete 删除可能产生碎片，并且不释放空间
truncate 是先摧毁表结构，再重构表结构

导入导出数据库

导出数据库全部数据

exp system/itcast full=y;
# exp system/itcast file=文件名 full=y

不指定 file 参数，则默认用导出 EXPDAT.DMP

导入数据库全部数据

imp system/itcast full=y;
# imp system/itcast full=y file=water.dmp;

按用户导入数据库

exp system/itcast owner=wateruser file=wateruser.dmp;

按用户导入数据库

imp system/itcast file=wateruser.dmp fromuser=wateruser;

按表导出数据库

exp wateruser/itcast file=a.dmp tables=t_account,a_area;

按表导入数据库

imp wateruser/itcast file=a.dmp tables=t_account,a_area;

数据查询

select distinct name from t_owners where (name like '%刘%' or usenum<=20000
') and addressid=3 and maxnum is not null order by usenum desc

精确查询

select * from T_OWNERS where watermeter='30408'

模糊查询

select * from t_owners where name like '%刘%'

and or 混用

select * from t_owners where (name like '%刘%' or housenumber like '%5%') and addressid=3

范围查询

select * from T_ACCOUNT where usenum>=10000 and usenum<=20000;
--  select * from T_ACCOUNT where usenum between 10000 and 20000

不建议使用between,再别的sql中有不含等于号的情况。

空值查询

select * from T_PRICETABLE t where maxnum is not null
-- select * from T_PRICETABLE t where maxnum is null

去重复查询

select distinct addressid from T_OWNERS

升序查询

select * from T_ACCOUNT order by usenum

降序查询

select * from T_ACCOUNT order by usenum desc

伪列ROWID查询

-- select rowID,t.* from T_AREA t
select rowID,t.* from T_AREA t where ROWID='AAAM1uAAGAAAAD8AAC';

伪列ROWNUM查询

-- select rowID,rownum,t.* from T_AREA t
select rownum,t.* from T_OWNERTYPE t

聚合查询求和

select sum(usenum) from t_account where year='2012'

聚合查询求平均

select avg(usenum) from T_ACCOUNT where year='2012'

聚合查询求最大值

select max(usenum) from T_ACCOUNT where year='2012'

聚合查询求最小值

select min(usenum) from T_ACCOUNT

聚合查询统计个数

select areaid,sum(money) from t_account group by areaid

分组后条件查询

select areaid,sum(money) from t_account group by areaid having sum(money)>169000

多表内连接查询

select o.id 业主编号,o.name 业主名称,ot.name 业主类型
from T_OWNERS o,T_OWNERTYPE ot
where o.ownertypeid=ot.id

select o.id 业主编号,o.name 业主名称,ad.name 地址,
ot.name 业主类型
from T_OWNERS o,T_OWNERTYPE ot,T_ADDRESS ad
where o.ownertypeid=ot.id and o.addressid=ad.id

多表左外连接查询

SELECT ow.id,ow.name,ac.year ,ac.month,ac.money
FROM T_OWNERS ow left join T_ACCOUNT ac
on  ow.id=ac.owneruuid

ORACLE语法优化，在右表所在的条件一端填上（+）

SELECT ow.id,ow.name,ac.year ,ac.month,ac.money FROM
T_OWNERS ow,T_ACCOUNT ac
WHERE ow.id=ac.owneruuid(+)

多表右外连接查询

select ow.id,ow.name,ac.year,ac.month,ac.money from
T_OWNERS ow right join T_ACCOUNT ac on  ow.id=ac.owneruuid

ORACLE 的语法优化，在左表所在的条件一端填上（+）

select ow.id,ow.name,ac.year,ac.month,ac.money from
T_OWNERS ow , T_ACCOUNT ac where ow.id(+) =ac.owneruuid

子查询

where 子句中的子查询

select * from T_ACCOUNT 
where year='2012' and month='01' and usenum >
( select avg(usenum) from T_ACCOUNT where year='2012' and month='01' )

多行子查询 in

select * from T_OWNERS where addressid in ( 1,3,4 )

select * from T_OWNERS where addressid not in
( select id from t_address where name like '%花园%' )

多行子查询 any

多行子查询 all

from 子句中的子查询

select * from (select o.id 业主编号,o.name 业主名称,ot.name 业主类型
from T_OWNERS o,T_OWNERTYPE ot
where o.ownertypeid=ot.id)
where 业主类型='居民'

select 子句中的子查询

select id,name, (select name from t_address where id=addressid) addressname
from t_owners

分页查询

rownum分页查询

select * from
(select rownum r,t.* from T_ACCOUNT t where rownum<=20)
where r>10

这是因为 rownum 是在查询语句扫描每条记录时产生的，所以不能使用“大于”
符号，只能使用“小于”或“小于等于” ，只用“等于”也不行。

rownum分页查询且排序

select * from
(select rownum r,t.* from
(select * from T_ACCOUNT order by usenum desc) t
where rownum<=20 )
where r>10

对结果先排序，再根据查询结果表进行分页。原因分析 ROWNUM 伪列的产生是在表记录扫描
是产生的，而排序是后进行的，排序时 R 已经产生了，所以排序后 R 是乱的。

row_number() over分页查询且排序

select * from
(select row_number() over(order by usenum desc )
rownumber,usenum from T_ACCOUNT)
where rownumber>10

分页查询minus

select rownum,t.* from T_ACCOUNT t where rownum<=20
minus
select rownum,t.* from T_ACCOUNT t where rownum<=10

字符函数

ASCII 返回对应字符的十进制值
CHR 给出十进制返回字符
CONCAT 拼接两个字符串，与 || 相同
INITCAT 将字符串的第一个字母变为大写
INSTR 找出某个字符串的位置
INSTRB 找出某个字符串的位置和字节数
LENGTH 以字符给出字符串的长度
LENGTHB 以字节给出字符串的长度
LOWER 将字符串转换成小写
LPAD 使用指定的字符在字符的左边填充
LTRIM 在左边裁剪掉指定的字符
RPAD 使用指定的字符在字符的右边填充
RTRIM 在右边裁剪掉指定的字符
REPLACE 执行字符串搜索和替换
SUBSTR 取字符串的子串
SUBSTRB 取字符串的子串（以字节）
SOUNDEX 返回一个同音字符串

字符串长度length

select length('ABCD') from dual;

字符串截取substr

select substr('ABCD',2,2) from dual; -- BC

从第几位开始截取，截取多少位

字符串拼接concat ||

select concat('ABC','D') from dual;

select 'ABC'||'D' from dual;

数值函数

ABS(value) 绝对值
CEIL(value) 大于或等于 value 的最小整数
COS(value) 余弦
COSH(value) 反余弦
EXP(value) e 的 value 次幂
FLOOR(value) 小于或等于 value 的最大整数
LN(value) value 的自然对数
LOG(value) value 的以 10 为底的对数
MOD(value,divisor) 求模
POWER(value,exponent) value 的 exponent 次幂
ROUND(value,precision) 按 precision 精度 4 舍 5 入
SIGN(value) value 为正返回 1;为负返回-1;为 0 返回 0. SIN(value) 余弦
SINH(value) 反余弦
SQRT(value) value 的平方根
TAN(value) 正切
TANH(value) 反正切
TRUNC(value,按 precision) 按照 precision 截取 value
VSIZE(value) 返回 value 在 ORACLE 的存储空间大小

四舍五入

select round(100.567) from d

select round(100.567,2) from dual

截取函数

select trunc(100.567) from dual

select trunc(100.567,2) from dual

求余函数（取模）

select mod(10,3) from dual

日期函数

ADD_MONTHS 在日期 date 上增加 count 个月
GREATEST(date1,date2,. . .) 从日期列表中选出最晚的日期
LAST_DAY( date ) 返回日期 date 所在月的最后一天
LEAST( date1, date2, . . .) 从日期列表中选出最早的日期
MONTHS_BETWEEN(date2, date1) 给出 Date2 - date1 的月数(可以是小数)
NEXT_DAY( date,’day’) 给出日期 date 之后下一天的日期，这里的 day 为星期，
如： MONDAY,Tuesday 等。
NEW_TIME(date,’this’,’other’) 给出在 this 时区=Other 时区的日期和时间
ROUND(date,’format’) 未指定 format 时，如果日期中的时间在中午之前，则
将日期中的时间截断为 12 A.M.(午夜，一天的开始),否则进到第二天。时间截断为 12 A.M.(午夜，一天的开始), 否则进到第二天。
TRUNC(date,’format’) 未指定 format 时，将日期截为 12 A.M.( 午夜，一天的开始

获取当前时间

select sysdate from dual

增加月份

select add_months(sysdate,2) from dual

所在月最后一天

select last_day(sysdate) from dual

日期截取到零时

select TRUNC(sysdate) from dual

日期截取为1月

select TRUNC(sysdate,'yyyy') from dual

日期截取到零秒

select TRUNC(sysdate,'mm') from dual

类型转换函数

CHARTOROWID 将字符转换到 rowid 类型
CONVERT 转换一个字符节到另外一个字符节
HEXTORAW 转换十六进制到 raw 类
RAWTOHEX 转换 raw 到十六进制
ROWIDTOCHAR 转换 ROWID 到字符
TO_CHAR 转换日期格式到字符串
TO_DATE 按照指定的格式将字符串转换到日期型
TO_MULTIBYTE 把单字节字符转换到多字节
TO_NUMBER 将数字字串转换到数字
TO_SINGLE_BYTE 转换多字节到单字节

数字转字符串 to_char

select TO_CHAR(1024) from dual

日期转字符串 to_char

select TO_CHAR(sysdate,'yyyy-mm-dd') from dual

select TO_CHAR(sysdate,'yyyy-mm-dd hh:mi:ss') from dual

字符串转日期

select TO_DATE('2017-01-01','yyyy-mm-dd') from dual

指定值转换函数nvl

select NVL(NULL,0) from dual

select PRICE,MINNUM,NVL(MAXNUM,9999999)
from T_PRICETABLE where OWNERTYPEID=1

null值判断转换函数

select PRICE,MINNUM,NVL2(MAXNUM,to_char(MAXNUM) , '不限')
from T_PRICETABLE where OWNERTYPEID=1

条件取值 decode

select name,decode( ownertypeid,1,' 居 民 ',2,' 行 政 事 业 单 位',3,'商业') as 类型 from T_OWNERS

条件取值 case when then

select name ,(case ownertypeid
when 1 then '居民'
when 2 then '行政事业单位'
when 3 then '商业'
else '其它'
end
) from T_OWNERS

select name,(case
when ownertypeid= 1 then '居民'
when ownertypeid= 2 then '行政事业'
when ownertypeid= 3 then '商业'
end )

行列转换

select (select name from T_AREA where id= areaid ) 区域,
sum( case when month='01' then money else 0 end) 一月,
sum( case when month='02' then money else 0 end) 二月,
sum( case when month='03' then money else 0 end) 三月,
sum( case when month='04' then money else 0 end) 四月,
sum( case when month='05' then money else 0 end) 五月,
sum( case when month='06' then money else 0 end) 六月,
sum( case when month='07' then money else 0 end) 七月,
sum( case when month='08' then money else 0 end) 八月,
sum( case when month='09' then money else 0 end) 九月,
sum( case when month='10' then money else 0 end) 十月,
sum( case when month='11' then money else 0 end) 十一月,
sum( case when month='12' then money else 0 end) 十二月
from T_ACCOUNT where year='2012' group by areaid

select (select name from T_AREA where id= areaid ) 区域,
sum( case when month>='01' and month<='03' then money else
0 end) 第一季度,
sum( case when month>='04' and month<='06' then money else
0 end) 第二季度,
sum( case when month>='07' and month<='09' then money else
0 end) 第三季度,
sum( case when month>='10' and month<='12' then money else
0 end) 第四季度
from T_ACCOUNT where year='2012' group by areaid

分析函数

相同的值排名相同，排名跳跃 rank() over

select rank() over(order by usenum desc ),usenum from T_ACCOUNT

相同的值排名相同，排名连续 DENSE_RANK

select dense_rank() over(order by usenum desc ),usenum from T_ACCOUNT

ROW_NUMBER 返回连续的排名，无论值是否相等

select row_number() over(order by usenum desc ),usenum from T_ACCOUNT

row_number() over实现分页查询

select * from
(select row_number() over(order by usenum desc )
rownumber,usenum from T_ACCOUNT)
where rownumber>10

集合运算

允许元素重复的集合相加 union all

select * from t_owners where id<=7
union all
select * from t_owners where id>=5

不允许元素重复的合集相加 union

select * from t_owners where id<=7
union
select * from t_owners where id>=5

求集合相交的元素intersect

select * from t_owners where id<=7
intersect
select * from t_owners where id>=5

求一个集合减去相交部分 minus

select * from t_owners where id<=7
minus
select * from t_owners where id>=5

分页查询minus

select rownum,t.* from T_ACCOUNT t where rownum<=20
minus
select rownum,t.* from T_ACCOUNT t where rownum<=10

视图

OR REPLACE ：若所创建的试图已经存在，ORACLE 自动重建该视图；
FORCE ：不管基表是否存在 ORACLE 都会自动创建该视图；
subquery ：一条完整的 SELECT 语句，可以在该语句中定义别名；
WITH CHECK OPTION ：插入或修改的数据行必须满足视图定义的约束；
WITH READ ONLY ：该视图上不能进行任何 DML 操作。

创建或修改视图

create or replace view view_address2 as
select * from T_ADDRESS where areaid=2

带约束的视图with check option

只能修改视图中显示的数值

create or replace view view_address2 as
select * from T_ADDRESS where areaid=2
with check option