oracle常用的SQL語句，oracle常用SQL語句

oracle常用的SQL語句，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;