【Oracle】个人收集整理的Oracle常用SQL及命令
【建表】
create table emp(
id number(12),
name nvarchar2(20),
primary key(id)
);
【充值一】
insert into emp
select rownum,dbms_random.string('*',dbms_random.value(6,20))
from dual
connect by level<101;
【充值二】
begin
for i in 1..100 loop
insert into emp values(i,'No.'||i);
end loop;
commit;
end;
/
【充值三】
（要充值的表）
create table heishehui(
id number(12),
name nvarchar2(20),
groupname nvarchar2(20),
primary key(id)
);
(PLSQL程序)
declare
v integer;
begin
for i in 1..100 loop
v:=dbms_random.value(1,5);
insert into heishehui values(i,'No.'||i,decode(mod(v,2),0,'洪兴',1,'东星'));
end loop;
commit;
end;
/
【更新字段内容】
update emp set age=dbms_random.value(18,65) where 1=1;
【加字段】
alter table emp add(dept number(2));
【删字段】
alter table emp drop column dept;
【更改字段类型】
alter table emp modify(dept number(5));
【字段改名】
alter table emp rename column dept to deptid;
【表改名】
alter table emp rename to emp1;
【看表下有什么字段及其类型】
select column_name,data_type
from all_tab_columns
where table_name=upper('emp');
(简化版)
desc emp;
【增删无名unique约束】
alter table emp add unique(name);
alter table emp drop unique(name);
【增删有名唯一性限制】
alter table ar_variable add constraint AR_VARIABLE_UID_NAME_UNIQUE unique(uid,name);
alter table ar_variable drop constraint AR_VARIABLE_UID_NAME_UNIQUE;
【查看某表上的约束】
select constraint_name,constraint_type,search_condition
from user_constraints
where table_name=upper('emp');
【查看单个约束】
select saerch_condition
from user_constraints
where table_name=upper('emp') and
constraint_name='SYS_C0011586';
【删除约束】
alter table ar_variable drop constraint SYS_C0011586;
【添加约束】
alter table emp add primary key(id);
alter table emp add unique(name);
alter table emp add check(status=1 or status=0);
alter table emp add check(dept='dev' or dept='mng' or dept='sales');
【建表时即加约束】
create table XXX(
prj varchar2(10) check(prj='oper' or prj='sql' or prj='api),
status number(1) check(status=0 or status=1),
...
);
【Sqlplus中设置列宽】
column XXX format a30;
简短模式:col XXX for a30;
逆操作：column XXX clear;
【Sql plus连远程DB】
模式：conn username/password@ip:port/servicename
实例：conn datamng/123456@170.0.35.86:1521/db19c
【查看解释计划】
1.explain plan for select f1,f2 from tb where ...
2.select * from table(dbms_xplan.display);
【用hint强制走索引】
create index idx_age on emp(age);
select /*+ index(emp,idx_age)*/ name from emp where age=42;
【查看服务名】
show parameter service;
【查看实例名】
select * from v$instance;
【查看数据库名】
select name from v$database;
【查看用到几个表空间】
select distinct tablespace_name from tabs;
【看当前用户能用的表】
1.select * from tab;
2.select table_name from user_tables;
【不让直接改字段类型之曲线救国做法】
1.加 alter table emp add(f2 varchar2(100));
2.拷 update emp set f2=f1;
3.删 alter table emp drop column f1;
4.改 alter table emp rename column f2 to f1;
【设字段为主键前清除重复】
1.确认有无重复
select id from test group by id having count(id)>1;
2.删除多余之N条
delete from test where rowid<>(select max(rowid) from test where id=2);
3.删除完设上主键
alter table test add constraint pk_test primary key(id);
【创建序列】
CREATE SEQUENCE emp_sqs
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
NOCACHE;
【创建注释】
给表加注释
comment on table ta is '表A';
给视图加注释
comment on table vb is '视图B';
给字段加注释
comment on column ta.f1 is '表A的字段1';
【日期时间与字符串互转】
时间转字符串
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
字符串转时间
select to_date('2022-06-03','yyyy-mm-dd') from dual;