【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;

END