Oracle 操作表总结

/*============创建Customer表==========*/   
create table Customer   
(   
  Customer_id number(6) not null,   
  Customer_name varchar2(50) not null,   
  Password varchar2(20) not null,   
  True_name varchar2(20),   
  Email_address varchar2(50) not null,       --唯一   
  Password_question varchar2(50) not null,   
  Password_anwser varchar2(50) not null,   
  Status char(1),                            --默认是1，取值0或1   
  Customer_level char(1),                    --默认是1，取值1，2，3   
  Score number(6),   
  Register_date date,                        --默认为系统时间   
  Login_time timestamp,   
  Login_count number(6),   
  Login_ip char(6)   
);   
/*===========创建Orders表==========*/   
create table Orders   
(   
   Order_id varchar2(10) not null,   
   Order_Customer_id number(6) not null,   
   Order_date date not null,   
   Order_price number not null  
);   
/*==========查询Customer表===========*/   
select * from Customer   
  
/*==========向表中添加数据===========*/   
insert into Customer values  
(220077,'wantingqiang','wtq','万廷强','[e-mail]','你是哪个?','wtq','1','3',150,sysdate,sysdate,15789,'172.26')   
  
/*==========修改表Customer===========*/   
alter table Customer   
    modify Login_ip char(16);   
       
 /*=========修改第一条记录中的ip=====*/   
 update Customer set Login_ip='172.26.3.145' where Customer_id='220077';--ip地址修改成功   
    
 /*=========添加一个列===============*/   
 alter table Customer   
    add LoginOut_time date;                                             --新列增加成功   
       
 /*=========删除一个列LoginOut_time========*/   
 alter table Customer   
    drop column LoginOut_time;                                          --列删除成功   
       
 /*========给列添加注释===========*/   
 comment on column  
     Customer.Customer_Name is '客户姓名';   
    
 /*========给表添加注释==========*/   
 comment on table Customer is '客户表，为了保持与客户的联系';   
    
 /*========重新命名表============*/   
 alter table Customer rename to Customer_Change;   
    
 select * from Customer_Change   
    
 alter table Customer_Change rename to Customer;   
    
 /*==========添加非空约束========*/   
 alter table Customer    
    modify status not null;                                            --非空约束添加成功   
       
 /*==========添加主键约束========*/   
 alter table Customer   
    add constraint customer_id_pk primary key(Customer_id);            --主键添加成功   
    
 /*==========添加外键约束========*/   
 --向Orders表中添加外键，与Customer表关联   
 --在下面的列子末尾加上：   
 --on delete 表示允许级联删除   
 --on update 表示允许级联更新   
 alter table Orders                                                  
    add constraint Orders_Customer_fk  foreign key(Order_Customer_id) references Customer(Customer_id);   
                                                                       --外键添加成功   
 /*==========删除外键约束=========*/   
 alter table Orders   
    drop constraint "ORDERS_CUSTOMER_FK";                              --外键删除成功，这个要注意大小写哈     
    
 /*=========添加唯一约束========*/   
 alter table Customer    
    add constraint un_email unique(Email_address);                     --添加唯一约束成功   
    
    
 /*=========修改默认约束========*/   
 alter table Customer   
    modify Status default('1');   
       
 /*=========添加检查约束========*/   
 alter table Customer   
    add constraint ck_status check(Status in ('1','0'));               --检查约束添加成功   
    
    
 /*==========禁止检查约束=======*/   
 alter table Customer    
    disable constraint ck_status;                                      --禁止成功   
       
 /*==========激活检查约束=======*/   
 alter table Customer   
    enable constraint ck_status;                                       --激活成功   
    
 /*==========删除检查约束========*/   
 alter table Customer   
    drop constraint ck_status;                                         --删除检查约束成功   
    
/*==========最后事删除表========*/   
 drop table Customer;