SM-SQL建表语句

–1:创建表空间–

CREATE TABLESPACE sm_spaceDATAFILE 'e:\jsp\db\sm_space.dbf'SIZE 10m;

–2:创建用户

CREATE USER sm IDENTIFIED BY 123456DEFAULT TABLESPACE sm_space;

–3:授予权限

GRANT CONNECT,RESOURCE TO sm;

–用户表

create table USERS(  userid   INTEGER not null,  username VARCHAR2(20) not null,  userpwd VARCHAR2(20) not null,  sex VARCHAR2(10) not null,  tel VARCHAR2(20) not null,  adr VARCHAR2(500) NOT NULL,  usertype VARCHAR2(20) not null,  state NUMBER(10) not null)

–用户表主键、用户名唯一约束

alter table USERS  add constraint PRI_userid primary key (userid);alter table USERS  add constraint UNI_username unique (username);

–供应商表

create table PROVIDER(  pid   INTEGER not null,  pname VARCHAR2(20) not null,  intro VARCHAR2(500)NOT NULL,  pman VARCHAR2(20) not null,  tel VARCHAR2(20) not null,  city VARCHAR2(20) not null,  adr VARCHAR2(500) not null,  area VARCHAR2(20) NOT NULL)

–供应商主键、供应商名唯一约束

alter table PROVIDER  add constraint PRI_pid primary key (pid);  alter table PROVIDER  add constraint UNI_pname unique (pname);

–商品类别表

create table GOODSTYPE(  gtid   INTEGER not null,gtname varchar2(20) not null)

–商品类别表主键、类别名唯一约束

alter table GOODSTYPEadd constraint PRI_GTID primary key (gtid);  alter table GOODSTYPE  add constraint UNI_gtname unique (gtname);

–商品表

create table GOODS(  gid   INTEGER not null,  gtid   INTEGER not null,  sid   INTEGER not null,  gname VARCHAR2(20) not null, price NUMBER(10,2) not null, unit VARCHAR2(20) not null)

–商品表主键、商品名唯一约束

alter table GOODS  add constraint PRI_GID primary key (gid);    alter table GOODS  add constraint UNI_gname unique (gname);

–商品表外键（商品类别主键、供应商主键）

alter table GOODS  add constraint FOR_gtid foreign key (gtID)  references GOODSTYPE (gtID) on delete cascade;alter table GOODS  add constraint FOR_pID foreign key (pID)  references PROVIDER (pID) on delete cascade;

–账单表

create table BILL(  bid   INTEGER not null,  pid   INTEGER not null,btime VARCHAR2(20) NOT NULL,ispaid NUMBER(10) NOT NULL,amount number(38) not null,paydate varchar2(20))

–账单表主键

alter table BILL  add constraint PRI_BID primary key (bid);

–账单表外键

alter table BILLadd constraint FOR_bpID foreign key(pid)references provider (pID) on delete cascade;

–账单明细表

create table billitem(biid integer not null,bid integer not null,gsid integer not null,gcount number(20) not null,amount number(20,2) not null)

–账单明细表主键

alter table billitemadd constraint PRI_BIID primary key(biid);

–账单明细表外键

alter table billitemadd constraint FOR_BID foreign key(bid)references bill(bid) on delete cascade;alter table billitemadd constraint FOR_gid foreign key(gid)references goods(gid) on delete cascade;

–区域表

create table AREA(  aid   INTEGER not null,  aname   VARCHAR2(20) not null)

–区域表主键、区域名唯一约束

alter table AREAadd constraint PRI_aId primary key(aid);    alter table AREA  add constraint UNI_Aname unique (aname);

–创建序列
–用户表序列

create sequence seq_useridincrement by 1start with 1minvalue 1 nomaxvaluenocycle;

–供应商表序列

create sequence seq_pidincrement by 1start with 1minvalue 1 nomaxvaluenocycle;

–商品类别表序列

create sequence seq_gtidincrement by 1start with 1minvalue 1 nomaxvaluenocycle;

–商品表序列

create sequence seq_gidincrement by 1start with 1minvalue 1 nomaxvaluenocycle;

–账单表序列

create sequence seq_bidincrement by 1start with 1minvalue 1 nomaxvaluenocycle;

–账单明细表序列

create sequence seq_biidincrement by 1start with 1minvalue 1 nomaxvaluenocycle;

–区域表序列

create sequence seq_aidincrement by 1start with 1minvalue 1 nomaxvaluenocycle;

–管理员用户

insert into users(userid,username,userpwd,sex,tel,adr,usertype,state)values(seq_userid.nextval,'admin','admin','男','15090275153','硅谷吴先森','admin','1');commit;

–添加测试数据

insert into provider(pid,pname,intro,pman,tel,adr,fax)values(seq_pid.nextval,'台湾统一集团','as范德萨快乐分手费','郭台铭','110','台湾第一大道','110');commit;insert into goodstype(gtid,gtname)values(seq_gtid.nextval,'饮料');commit;insert into goods(gid,gtid,pid,gname,price,unit)values(seq_gid.nextval,1,1,'统一冰红茶',3.5,'瓶');commit;

–获取商品数据（需要连接查询：供应商名字、品类名字）

select g.gid,g.gtid,g.pid,g.gname,g.price,g.unit,gt.gtname,p.pnamefrom goods g,goodstype gt,provider pwhere g.gtid=gt.gtid and g.pid=p.pid;alter table providermodify(set fax=city)