青软实训2 建表与插入数据

grant connect,resource to yihan;

-------------tb_user表(用户信息表)
 create sequence s1 start with 1000 increment by 1;
create table tb_user(
   userId number(10) not null primary key,
   name varchar2(10) unique not null,
   password varchar2(30) not null,
   email varchar2(20),
   provinceId number(10),
   cityId number(10),
   telephone char(11),
   sex char(3) default '男',
   age number(10),
   birthday date,
   money number(10) default 0,
   photo varchar2(500),
   dsp varchar2(200),
   qq varchar2(15),
   msn varchar2(100),
   loveBook varchar2(100),
   loveMusic varchar2(100),
   loveMovie varchar2(100),
   loveSport varchar2(100),
   loveGame varchar2(100),
   constraint fk_cityId foreign key(cityId) references tb_city(cityId)
   )
   
   insert into tb_user values(s1.nextval,'yiyi','123456','123456789',1001,1003,15455556666,
     '男',20,to_date('2014-02-14','yyyy-mm-dd'),10000,'1005','5656','1566','44545','5555','5666','5555','5555','55666');
     insert into tb_user values(s1.nextval,'yi','123456','123456789',1002,1004,15455556666,
     '男',20,to_date('2014-02-14','yyyy-mm-dd'),10000,'1005','5656','1566','44545','5555','5666','5555','5555','55666');
  
   select * from tb_user;
   -----------tb_product表(商品信息表)---
   create sequence s2 start with 1000 increment by 1; 
   create table tb_product(
      productId number(10) not null primary key,
      userId number(10) not null,
      productName varchar2(50) not null,
      typeId number(10) not null,
      price number(10) not null,
      photo varchar2(500),
      information varchar2(500),
      constraint fk_userId foreign key(userId) references tb_user(userId),
      constraint fk_typeId foreign key(typeId) references tb_productType(productTypeId)
      )
      
      ----为tb_product表插入数据----
      
      insert into tb_product values(s2.nextval,1002,'小苹果',1001,1000,'s55s5','zixi');
       insert into tb_product values(s2.nextval,1004,'小苹果',1001,1000,'s55s5','zixi');
       
       select * from tb_product;
      -------tb_productType表（商品类型表）--
      create sequence s3 start with 1000 increment by 1;
      create table tb_productType(
          productTypeId number(10) not null primary key,
          productTypeName varchar2(100)
          );
      -----------为此表插入数据-----
      
      insert into tb_productType values(s3.nextval,'饼干');
      insert into tb_productType values(s3.nextval,'雪糕'); 
      
      select * from tb_productType;    
          
          
          
          
          
          ----tb_order表（订单表）----
       create sequence s4 start with 1000 increment by 1;
       create table tb_order(
          orderId number(10) not null primary key,
          toId number(10) not null,
          colorId number(10),
          productId number(10),
          address varchar2(100),
          telephone char(13),
          orderState number(10),
          count number(10),
          price number(10),
          orderDate date,
          consignmentDate date,
          constraint fk_toId foreign key(toId) references tb_user(userId),
          constraint fk_colorId foreign key(colorId) references tb_productColor(productColorId),
          constraint fk_productId11 foreign key(productId) references tb_product(productId)
          );
          
          
          -------创建tb_userAccount---
          create sequence s5 start with 1000 increment by 1;
          drop table tb_userAccount;
          create table tb_userAccount(
              userAccountId number(10) primary key,
              userId number(10),
              orderId number(10),
              type number(10),
              time date,
              constraint fk_userId1 foreign key(userId) references tb_user(userId),
              constraint fk_orderId foreign key(orderId) references tb_order(orderId)
          );
          ---------创建tb_review
          
          create sequence s6 start with 1000 increment by 1;
        
          create table tb_review (
             reviewId number(10) primary key,
             review varchar2(200),
             isReply number(1) default 0,
             isDel number(1) default 0,
             senderId number(10),
             productId number(10),
             addTime date,
             replyId  number(10),
             constraint fk_senderId foreign key(senderId) references tb_user(userId),
             constraint fk_productId1 foreign key(productId) references tb_product(productId)
             
           );
           ----------- tb_province表（省份数据字典）
            create sequence s7 start with 1000 increment by 1;
           create table tb_province(
              provinceId number(10) not null primary key,
              provinceName varchar2(30)
           );
           
           ---------为tb_province插入数据-----
           
           insert into tb_province values(s7.nextval,'山东省');
           insert into tb_province values(s7.nextval,'浙江省');
           
           select * from tb_province;
           
           ----------tb_city表（城市数据字典
            create sequence s8 start with 1000 increment by 1;
           create table tb_city(
              cityId number(10) not null primary key,
              provinceId number(10),
              cityName varchar2(30),
              constraint fk_provinceId foreign key(provinceId) references tb_province(provinceId)
             );
           -----为tb_city添加数据----
           insert into tb_city values(s8.nextval,1001,'青岛市');
           insert into tb_city values(s8.nextval,1002,'烟台市');
           select * from tb_city;
             
             ------------tb_productSize表（商品型号表
             create sequence s9 start with 1000 increment by 1;
             
             create table tb_productSize(
                 productSizeId number(10) not null primary key,
                 productSize varchar2(20) not null,
                 productId number(10),
                 constraint fk_productId foreign key(productId) references tb_product(productId)
                 );
            ----------为tb_productSize添加数据-----
            insert into tb_productSize values(s9.nextval,'XXX',1001);
            insert into tb_productSize values(s9.nextval,'XXL',1002);
            
            select * from tb_productSize;
                 
               ----------tb_productColor表（商品颜色表）
               create sequence s10 start with 1000 increment by 1; 
              create table tb_productColor(
                 productColorId number(10) primary key,
                 productColor varchar2(20),
                 stockPile number(10),
                 productSizeId number(10),
                 constraint fk_productSizeId foreign key(productSizeId) references tb_productSize(productSizeId)
                 )
             
               insert into tb_productColor values(s10.nextval,'red',1000,1001);
               insert into tb_productColor values(s10.nextval,'yellow',1000,1002);
               
               select * from tb_productColor;