oracle 基础

-- Create table

create table tableName
(
  id  NUMBER(20),
  name  VARCHAR2(100),
);
-- Add comments to the table 
comment on table  tableName
  is '表名';
-- Add comments to the columns 
comment on column     tableName.id

is '主键ID';

comment on column  tableName.name

 is '姓名';

--  drop table

drop table tableName;

-- add column  
alter table   tableName  add (sex  varchar2(30), age  number(3));
-- Add comments to the columns
comment on column tableName.sex
is '性别';
comment on column tableName.age
is '年龄';
/

-- delete column

alter table tableName   drop column sex;

alter table tableName   drop column age;

-- update column length unit

在表中有数据的情况下

--1、对  tableName  进行备份

    create table tableName_bak as select * from  tt_waybill;

--2、删除 tt_waybill 表内的数据
    delete  from  tableName;

--3、修改tt_waybill 字段
alter   table   tableName  modify   sex  varchar2(1);
alter   table   tableName  modify   age  number(2);

--4、还原表结构
   insert into tableName  select * from tableName _bak;
   commit;

-- update column  name

alter   table  tableName rename column sex  to new_sex ;

-- CRUD

insert into tableName  values ( ' 1 ','翠花','女', '18');

delete * from tableName where name='翠花';

delete from tableName  --删除重复数据

 where id in (select id
                from (select tmn.*,
                             row_number() OVER(PARTITION BY age order by id) ord
                        from  tableName  tmn)
               where ord > 1);

select * from  tableName ;

select *  from tableName  where   create_tm > date'2016-10-19' + 0/24    and tmw.create_tm < date'2016-10-20' + 0/24  ; 查询20号一天的数据

update tableName set name=' 小花' where name='翠花';

update tableName    set  name = '蒋' ||  name    where length(name) = 2; --name的字段长度等于2，就在 前面

--create  sequence 
create sequence sel_name
minvalue 1 
maxvalue 999999999999999999999999999 
start with 1 
increment by 1 
cache 20 order;

--drop sequence 

drop sequence sql_name;