oracle之常用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);
--给两个字段建立唯一属性
alter table W_YJDLFHBASEDATA add constraint YJDLFHBASEDATA_rq_zdm_uKey unique(f_rq,f_zdm); --插入数据 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 新表名 (摘自:https://www.cnblogs.com/dt520/p/5683798.html)