Oracle 常用语句

一.查询

• 拼接查询

查询 ABC 表中 a 字段值与 – 字符与 b 字段值拼接的结果

select i.a||'--'||i.b from ABC i where i.id = '123456'

• 分组查询

先根据字段 A 进行分组,再根据 B 进行排序,取每一组的前 3 条数据，双竖线是拼接

select t.* from
(select row_number() over(partition by 分组字段 order by 排序字段) rn,b.* 
 from 表名 b) t
where t.rn <= 3 ;

• 分页查询

排序并查询出第 11 到第 20 条数据

select t.* from
(select row_number() over(order by 排序字段 desc) rn,b.* 
 from 表名 b) t
where t.rn <= 20 and t.rn>10;

• 查询重复

查询 people 表中,peopleId重复的数据

select * from people where peopleId in 
(select peopleId from people group by peopleId having count(peopleId)>1 )

• 截取查询

查询并截取字段 B 的前五位

select i.字段名A,substr(i.字段名B,0,5) from 表名 i where i.字段名C like 'ABC%' 

查询并截取字段 B 的后四位

select i.字段名A,substr(i.字段名B,-4) from 表名 i where i.字段名C like 'ABC%' 

查询并去掉字段B 的第一位和最后一位

select v.字段A,substr(v.字段B,1,LENGTH(v.字段B)-1) as kkk from 表名 v

• 查询约束

根据约束名称来查询外键约束 FK932E5474D9434E8 为约束名

SELECT TABLE_NAME
	FROM DBA_CONSTRAINTS
	WHERE CONSTRAINT_NAME = 'FK932E5474D9434E8' AND CONSTRAINT_TYPE = 'R'

二.插入

• 插入数据

如果字段值为字符型，就用单引号引起来

insert into 表名字 (字段1,字段2,字段3) values (值1,值2,值3)

三.删除

• 删除数据

delete from 表名 where 字段名 = '字段值'

四.修改

• 匹配修改

用表2 的数据插入到表1,前提是表2 的 id 等于表1 的parent_id

merge into 表名1 a
using 表名2 b
on (a.parent_id = b.id)
when matched then
update set a.字段 = b.字段
where b.字段 is not null

merge into 表名1 a
using 表名2 b
on (a.parent_id = b.id)
when matched then
update set a.field1 = (b.field1||'/'||a.field2) --双竖线 || 是拼接
where b.field1 is not null 
and a.field1 is not null 
and a.field2 is null 

五.表结构

新增字段（加列）

alter table 表名 add (字段名 字段类型)
alter table 表名 add (AA_ID varchar2(64))

删除字段

alter table 表名 drop column 字段名
alter table 表名 drop column stock

改表字段长度

alter table 表名 modify (AA_ID varchar(64),BB_ID varchar(64))

更改表的字段名

alter table 表名 rename column 现列名 to 新列名

六.数据库

查询表空间的名称和大小(单位MB)

select t.tablespace_name,round(SUM(bytes / (1024 *1024 )),0) ts_size FROM dba_tablespaces t, dba_data_files d
	WHERE t.tablespace_name = d.tablespace_name GROUP BY t.tablespace_name

查询表空间的名称和空闲空间大小(单位MB)

select tablespace_name,SUM (bytes) / (1024 *1024 ) AS free_space
	FROM dba_free_space 
	GROUP BY tablespace_name

查询表空间的物理存储位置和大小(单位MB)

select tablespace_name,file_id,file_name,round(bytes / (1024 *1024 ),0) totle_space
	FROM dba_data_files
	order BY tablespace_name

创建临时表空间

create temporary tablespace space_name
	tempfile '/tmp/oradata/escc_tmp.dbf'
	size 1G
	extent management local

创建表空间

create tablespace space_name
	datafile '/tmp/oradata/escc_db.dbf'
	size 1024m
	autoextend on --自动扩容
	next 50m maxsize 20480m  --每次扩容50m，最大到20480m
	extent management local

删除表空间包含物理文件

drop tablespace space_name including contents and datafiles

创建用户

create user 用户名 identified by 密码 --用户密码
	default tablespace space_db --默认表空间 space_db
	temporary tablespace temp_db  --临时表空间 temp_db
	profile default

查询数据库每个用户的连接

select username,count(username) from v$session where username is not null group by username

查询数据库的时间

select sysdate from dual

回滚数据库中某个表到某时间

alter table 表名 enable row movement;
flashback table 表名 to timestamp to_timestamp('2020-06-06 06:06:06','yyyy-mm-dd hh24:mi:ss');

(完)