数据库创造

2012-09-01

数据库创建CREATE DATABASE dometestUSE dometestCREATE TABLE dometest.t_user( Id Int Not Null Auto_

数据库创建

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 awhere (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 rnfrom (select * from emp order by empno) e)where rn between 1 and 10;select * from(select e.*,ROWNUM rnfrom (select * from emp order by empno) ewhere 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 1cache 20; -- 建立触发器create trigger "simon_trigger" beforeinsert 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 application  CREATE TABLE application_bak (SELECT * FROM application);

1 楼 a_bin 2011-07-03
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)
) 2 楼 a_bin 2012-05-29   ALTER TABLE project_workteam ADD COLUMN id INT NOT NULL AUTO_INCREMENT AFTER project_id, ADD PRIMARY KEY(id);

热点排行

其他数据库

数据库创造