章彬.txt

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

1，创建一个商品表 和 种类表；要求用到五个约束

   create table shanpin
(
 spid number(9) null,
 sname varchar2(10),
 made varchar2(10),
 genre varchar2(10),
 constraint pk_spid primary key(spid) validate,
 constraint uniq_sname unique (sname) validate,
 constraint chk_made check
 ( made in 
  ('china','usa','england','grade')) validate
  );
  
  create table genre 
  (
  genre_id number(9) not null,
  gname varchar2(10) not null,
  made varchar2(10),
  spid number(9),
  constraint pk_genre_id primary key (genre_id) validate,
  constraint chk_gname check (gname in ('农产品','副食','杂货','电器')) validate,
  constraint fk_made foreign key (spid)
             references shanpin (spid)  validate
             );


2，对刚才创建的商品表的列进行增，删，改操作，三个操作每个操作都有
   
2.1 增加列
    
SQL> alter table shanpin add (chunhuo number(9),updateDate date default sysdate);

Table altered

SQL> desc shanpin;
Name       Type         Nullable Default Comments 
---------- ------------ -------- ------- -------- 
SPID       NUMBER(9)                              
SNAME      VARCHAR2(10) Y                         
MADE       VARCHAR2(10) Y                         
GENRE      VARCHAR2(10) Y                         
CHUNHUO    NUMBER(9)    Y                         
UPDATEDATE DATE         Y        sysdate          

2.2  更改列表
  SQL> alter table shanpin modify (chunhuo char(10),made varchar2(30));

Table altered

2.3   删除列表
   SQL> alter table shanpin drop (chunhuo,updatedate) cascade constraints;

Table altered

SQL> select * from shanpin;

      SPID SNAME      MADE                           GENRE
---------- ---------- ------------------------------ ----------