oracle常用的SQL语句

一些常用的SQL语句：

--建表

create table adolph             

(id number(10,0),              name varchar2(20),              salary number(10,0));         

 --建约束

alter table adolph add constraint adolph_id_pk primary key(id);

 

--插入数据 insert into adolph       

　　values(1,'adolph',1000);                      insert into adolph        values(2,'adolph',1000);       

--修改数据 update adolph set salary =2000 where id = 1;

--删除数据 delete from adolph where id = 2; --查找 select * from adolph ;

--多表查询 select * from emp_info_adolph e,dept d where e.empid = d.deptno;

--分组 select avg(e.deptno),deptno from dept e group by e.deptno;

--建视图 create view viewtest as select * from dept;

--建索引 create index indextest on adolph(salary);

--建同义词 create synonym sys1 for adolph ; SELECT * FROM sys1;

--建序列 create sequence s1        increment by 1        start with 1        maxvalue 99        nocache        nocycle;       

--INSERT INTO adolph        values(s1.nextval,'aaa' ,998);            

truncate table adolph;   

--添加列 alter table adolph add(loc varchar2(10));  

--修改列 alter table adolph modify(loc varchar2(20));

 

--字符串交换： 

SELECT replace (replace('a is b','a','b'),' b',' a') FROM dual;

--重命名栏位名

 ALTER TABLE 表名 rename   column  列名 to 新列名  例如：alter table WMSVMI940X rename column "ZONE" to "AREAZONE";

--重命名表名

ALTER TABLE 表名 rename to  新表名