oracle操作脚本.sql

注释一行

-- select * from test;

注释多行

/*

select * from test;

select * from test1;

*/

创建一个表

create table test (

  NO number(3) primary key NOT NULL ,

  NAME varchar2(30),

  SEX varchar2(3) default '男',

  TEL number(11) default 13123456789

);

向表中添加数据

insert into test ( NO, NAME, SEX, TEL ) values (1, 'wangpandong', '男', 18565674629 );

insert into test ( NO, NAME, SEX, TEL ) values (2, 'zhangsan', '男', 10123456789 );

insert into test ( NO, NAME, SEX, TEL ) values (3, 'lisi', '男', 10123456790 );

insert into test ( NO, NAME, SEX, TEL ) values (4, 'wangwu', '男', 10123456791 );

insert into test ( NO, NAME, SEX, TEL ) values (5, 'liuliu', '女', 10123456792 );

insert into test ( NO, NAME, TEL ) values (6, 'zhaoqi', 10123456793 );

insert into test ( NO, NAME, SEX, TEL ) values (7, 'laoba', '男', 10123456794 );

更新数据

update test set sex = '女' where NO = 6;

查看数据

select NAME from  test where NO = 1;

select NAME from  test where NO <= 5;

select * from  test where ROWNUM <= 5;

select NO ,NAME, TEL from  test where NO = 1 and SEX = '男' ;

select NO "编号" ,NAME "姓名", TEL "电话"from  C##WPD.test  @ORCL where NO = 1 ;

select distinct sex from test;

过滤重复性查看

select distinct sex from test;

select distinct name , sex from test;

更新表头信息

alter table test rename to test1; 

alter table test add ADDRESS varchar2(50) default '广东广州';

alter table test add AGE number(2) ;

alter table test rename column ADDRESS to ADDR; 

alter table test modify (ADDR  varchar2(60)); 

alter table test drop column AGE;

复制表头信息

create table test3 as select * from  test where  1 = 2 ;

复制整个表

create table test2 as select * from  test ;

两张表合并查询（union去重复，union all 不去重复）

select NAME , SEX from test union select NAME , SEX from test2 ;

select NAME , SEX from test union all select NAME , SEX from test2 ;

将表A数据插入表B

insert into test3 (NO, NAME) select NO, NAME from test; 

update test3 set TEL = ( select TEL from test where NO = 1); 

操作查询结果

select rowid, t.* from test3  t for update;

删除表

drop table test;

查询所有表空间

select tablespace_name from dba_tablespaces;

select tablespace_name from user_tablespaces;

循环插入一组数据

declare 

begin 

for i in 10..66 loop 

insert into test (NO) values (i) ;

end loop;

end;

循环删除一组数据

declare 

begin 

for i in 10..66 loop 

delete  from  test where NO = i ;

end loop;

end;

查询结果截取某几个字段

update test set ADDR = '河南汝州' where NO = 1;

update test set ADDR = '新疆乌鲁木齐' where NO = 2;

update test set ADDR = '台湾台北' where NO = 3;

update test set ADDR = '上海上海' where NO = 4;

update test set ADDR = '河北廊坊' where NO = 5;

update test set ADDR = '内蒙古苏荷台' where NO = 6;

update test set ADDR = '广东广州' where NO = 7;

select substr (ADDR, 3,8) from test;

select substr (ADDR, -2) from test;

select substr (ADDR, 3) from test;