谢妮娜.txt

关于oracle和sql的书籍和ppt教程,非常好,本人珍藏品

1，创建一个商品表 和 种类表；要求用到五个约束
------创建商品表 
create table goods(id int,name varchar2(10),price number(8,2),kid int,factory varchar2(20));

----------设置主键
 alter table goods add constraint per_id primary key(id);

----------设置唯一性约束
alter table goods add unique (name);

----------设置不为空约束
alter table goods modify price not null;
alter table goods modify kid not null;

----------设置检查约束
alter table goods add constraint check_p check (price>0);

------创建种类表
create table kind (kid int,name varchar2(10));

----------设置主键
alter table kind add constraint per_kid primary key(kid);

----------设置唯一性约束
alter table kind add unique (name);

------设置外键约束
alter table goods add constraint for_kid_kind_kid foreign key(kid) references kind(kid);

=============================================================

或者通过如下方式实现：

------创建种类表
create table kind (  kid      int            primary key,
                     name     varchar2(10),
         constraint  uniq_name unique(name));
         
------创建商品表 
create table goods(   id       int            primary key
                      name     varchar2(10)   
                      price    number(8,2),   not null,  
                      kid      int            not null,                
                      factory  varchar2(20) ,
         constraint   uni_name unique(name)   validate
         constraint   check_p  check (price>0)  validate
         constraint   for_kid_kin_kid foreign key(kid) refrences kind(kid)  validate );



==================================================================


2，对刚才创建的商品表的列进行增，删，改操作，三个操作每个操作都有

---增加stock_price列
alter table goods add stock_price number(8,2);

---删除stock_price列
alter table goods drop column stock_price;

---重命名price列
alter table goods rename column price to stock_price;

---修改factory列的属性值
alter table goods modify factory varchar2(30);