--建表
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 新表名