建表语句:
CREATE TABLE DIC_ORG_ADDR(
ID varchar(32) primary key NULL,
ORGAN_ID varchar(32) NULL,
ORGAN_NAME varchar(32) NULL,
ORGAN_ADDR VARCHAR(100)NULL,
TYPE CHAR(1) NULL
)
插入语句:
insert into DIC_ORG_ADDR(ID,ORGAN_ID,ORGAN_NAME,ORGAN_ADDR,TYPE )
values("1","01","公安局","黑空街200号","1")
或是
insert into DIC_ORG_ADDR(ID,ORGAN_ID,ORGAN_NAME,ORGAN_ADDR,TYPE )
select * from OTHERS
删除语句:
delete from DIC_ORG_ADDR where ID='1'
删除表语句:
drop table DIC_ORG_ADDR
修改表结构:
新增字段:
ALTER TABLE DIC_ORG_ADDR
ADD ( "valid" VARCHAR2(25) NULL )
删除字段:
ALTER TABLE DIC_ORG_ADDR
DROP ( "TYPE" ) CASCADE CONSTRAINT
GO
修改数据:
update DIC_ORG_ADDR set type='0' where ID='1'
查询语句:
select * from DIC_ORG_ADDR
创建自增长序列:
创建序列:
create sequence id_seq increment by 1 start with 1;(从1开始每次增1)
创建触发器:(触发器名为trg_seq_pid ,在向DIC_ORG_ADDR表做 插入之前,
使用id_seq.nextval自增长序列,使id字段自增长。这样插入的时候,主键不用输入数据库就会自动添加自增长的主键值了 )
create or replace trigger trg_seq_pid BEFORE
insert ON DIC_ORG_ADDR FOR EACH ROW
begin
select id_seq.nextval into:New.id from dual;
end trg_seq_pid ;
9. 字符串连接符:||
select name||'%' from users;
10. 截取符:trunc
select trunc(num,2) from users; 截取保留小数点后两位,不进行四舍五入
11. case when
update users set name = (case when name='张三' then '李四' else '无名' end) where name is not null;
12. 左连接:左边的表数据内容一定存在,右边连接的如果不存在则为null
select * from INFO a left join RESULT d on a.id = d.resource_guid
全连接:相当与where条件
select * from INFO a join RESULT d on a.id = d.resource_guid
13. 转换为字符串: to_char()
select * from users where to_char('create_time','yyyy-MM-dd')<=sysdate;
14. 时间加天数:时间直接加数字
select * from users where create_time+15 = sysdate;