CREATE DATABASE dometest;
USE dometest;

CREATE TABLE dometest.t_user( 
	Id Int Not Null Auto_increment , 
	name Varchar(512) ,  
	age Varchar(512) ,     
	Primary Key (id)  
);

?

?

唯一约束：

CREATE TABLE TBL_BLACKLIST
(
 mobile varchar2(100) not null ,
 mobileDesc  varchar2(128),
 constraint mobile_uk unique(mobile)
);

?

?

CREATE TABLE TBL_ORDER_TIME
(   
 ORDER_NO NUMBER(10),   
 PROM_ID NUMBER(8),   
 PROM_TYPE CHAR(1),
 AMOUNT NUMBER(8,2) default 0
); 

?

中文注释

?

comment on table TBL_ORDER_TIME is '工作表';
comment on column TBL_ORDER_TIME.ORDER_NO is '订单号';
comment on column TBL_ORDER_TIME.PROM_ID is '促销ID';
comment on column TBL_ORDER_TIME.PROM_TYPE is '促销类型';
comment on column TBL_ORDER_TIME.AMOUNT is '金额';

?

修改字段：

ALTER TABLE tb_book   CHANGE id id INT(11) NOT NULL AUTO_INCREMENT COMMENT 'id值';

?

?

?

create database bookshop;
use bookshop;

CREATE TABLE bookshop.tb_book( 
   id INT NOT NULL AUTO_INCREMENT COMMENT 'id主键', 
   bookName VARCHAR(215) COMMENT '书名', 
   bookContent VARCHAR(1025) COMMENT '内容描述', 
   bookPrice DOUBLE(10,2) DEFAULT '0' COMMENT '价格', 
   bookAuthor VARCHAR(215) COMMENT '作者', 
   PRIMARY KEY (id)
 )  

?

修改字段长度：

alter table tbl_invoice_log  modify ( log_content varchar2(200));

??

?

?增加字段：

?

  ALTER table tb_order add payCash varchar2(5);

ALTER TABLE shell_command ADD c_type INT(1) DEFAULT 0 COMMENT '1为手动执行，2为程序功能辅助';

?删除字段：

?

alter table tbl_refund drop column lostState

?

?

?

?删除重复记录

delete from tbl_member_report a
where (a.memberNo,a.opendate) in   
(select memberNo,opendate from tbl_member_report group by memberNo,opendate having count(*) > 1)
and 
rowid not in 
(select min(rowid) from tbl_member_report group by memberNo,opendate having count(*)>1)

?

修改字段名称

?

?

?alter table tbl_feifei rename column no to no_bak;

?

?

Oracle分页：

?

select * from
(select e.*,ROWNUM rn
from (select * from emp order by empno) e)
where rn between 1 and 10;


select * from
(select e.*,ROWNUM rn
from (select * from emp order by empno) e
where ROWNUM<=10)
where rn>=1;

?

?oracle 主键自增长：

?

create table simon_example
(
  id number(4) not null primary key,
  name varchar2(25)
)

-- 建立序列：

-- Create sequence 
create sequence SIMON_SEQUENCE                       
minvalue 1               
maxvalue 999999999999999999999999999  
start with 1 
increment by 1
cache 20; 

-- 建立触发器
create trigger "simon_trigger" before
insert on simon_example for each row when(new.id is null)
begin
 select simon_sequence.nextval into:new.id from dual;
 end;

?

CREATE SEQUENCE emp_sequence
　　INCREMENT BY 1 -- 每次加几个
　　START WITH 1 -- 从1开始计数
　　NOMAXVALUE -- 不设置最大值
　　NOCYCLE -- 一直累加，不循环
　　NOCACHE -- 不建缓冲区

?

?

INSERT INTO project_routine_user(routineId,userId)  SELECT id routineId , userId  FROM project_routine_error; 


SELECT * INTO application_bak FROM appli