seafood.sql

海鲜超市管理系统是不个错的超市管理系统

--数据库用户表。
create table shop_user(
	username     varchar2(10),
	password     varchar2(10),
	flag         number(1)
);

insert into  shop_user values('admin','admin',1);
insert into  shop_user values('rjl','rjl',0);
insert into  shop_user values('1','1',1);
select * from shop_user

--创建部门表
DROP TABLE TABLE_DEPT;

CREATE SEQUENCE DEPT_SEQ
MINVALUE 1
MAXVALUE 99999
START WITH 10001
INCREMENT BY 1
CACHE 20

CREATE TABLE TABLE_DEPT(
	DEPT_ID           NUMBER(5) PRIMARY KEY ,
	DEPT_NAME         VARCHAR2(20),
	DEPT_DESCREPTION  VARCHAR2(200)
);


INSERT INTO TABLE_DEPT VALUES(DEPT_SEQ.NEXTVAL,'A','ABC');
INSERT INTO TABLE_DEPT VALUES(DEPT_SEQ.NEXTVAL,'B','BCD');
INSERT INTO TABLE_DEPT VALUES(DEPT_SEQ.NEXTVAL,'C','CDE');
INSERT INTO TABLE_DEPT VALUES(DEPT_SEQ.NEXTVAL,'D','DEF');
INSERT INTO TABLE_DEPT VALUES(DEPT_SEQ.NEXTVAL,'E','EFG');
INSERT INTO TABLE_DEPT VALUES(DEPT_SEQ.NEXTVAL,'F','FGH');
INSERT INTO TABLE_DEPT VALUES(DEPT_SEQ.NEXTVAL,'G','GHI');
INSERT INTO TABLE_DEPT VALUES(DEPT_SEQ.NEXTVAL,'H','HIJ');

SELECT * FROM TABLE_DEPT;

--创建收入表

DROP TABLE TABLE_INCOME;

CREATE SEQUENCE INCOME_SEQ
MINVALUE 1 
MAXVALUE 999999999999
START WITH 1000000001
INCREMENT BY 1
CACHE 20

CREATE TABLE TABLE_INCOME(
	INCOME_ID     NUMBER(10) PRIMARY KEY,
	DEPT_ID       NUMBER(5),
	DAILY_INCOME  NUMBER(5),
  BUSINESS_DATE DATE DEFAULT SYSDATE,
  LST_MOD_TIMESTEMP DATE DEFAULT SYSDATE,
  CONSTRAINT DEPT_ID_FK FOREIGN KEY (DEPT_ID)
  REFERENCES TABLE_DEPT(DEPT_ID)
);

INSERT INTO TABLE_INCOME VALUES(INCOME_SEQ.NEXTVAL,10000,500,TO_DATE('20060601','YYYY-MM-DD'),TO_DATE('20060602','YYYY-MM-DD'));
INSERT INTO TABLE_INCOME VALUES(INCOME_SEQ.NEXTVAL,10007,500,TO_DATE('20060603','YYYY-MM-DD'),TO_DATE('20060611','YYYY-MM-DD'));
INSERT INTO TABLE_INCOME VALUES(INCOME_SEQ.NEXTVAL,10006,500,TO_DATE('20060604','YYYY-MM-DD'),TO_DATE('20060612','YYYY-MM-DD'));
INSERT INTO TABLE_INCOME VALUES(INCOME_SEQ.NEXTVAL,10005,500,TO_DATE('20060605','YYYY-MM-DD'),TO_DATE('20060622','YYYY-MM-DD'));
INSERT INTO TABLE_INCOME VALUES(INCOME_SEQ.NEXTVAL,10004,500,TO_DATE('20060606','YYYY-MM-DD'),TO_DATE('20060623','YYYY-MM-DD'));
INSERT INTO TABLE_INCOME VALUES(INCOME_SEQ.NEXTVAL,10003,500,TO_DATE('20060607','YYYY-MM-DD'),TO_DATE('20060625','YYYY-MM-DD'));
INSERT INTO TABLE_INCOME VALUES(INCOME_SEQ.NEXTVAL,10002,500,TO_DATE('20060608','YYYY-MM-DD'),TO_DATE('20060626','YYYY-MM-DD'));
INSERT INTO TABLE_INCOME VALUES(INCOME_SEQ.NEXTVAL,10001,500,TO_DATE('20060609','YYYY-MM-DD'),TO_DATE('20060627','YYYY-MM-DD'));

SELECT * FROM TABLE_INCOME;

--创建供货商信息表。
drop table table_vendor;

create sequence vendor_seq
minvalue 1
maxvalue 99999
start with 10001
increment by 1
cache 20



create table table_vendor(
	vendor_id       number(5) primary key,
	vendor_name     varchar2(50) unique,
	vendor_address  varchar2(200),
	vendor_phone    number(8),
	vendor_fas      number(8),
	vendor_contact_person  varchar2(10)
);

insert into table_vendor values(vendor_seq.nextval,'abc','aaaaaaaaa',89898989,89898989,'jim');
insert into table_vendor values(vendor_seq.nextval,'bcd','bbbbbbbbb',86868686,86868686,'tom');
insert into table_vendor values(vendor_seq.nextval,'cde','ccccccccc',65656565,65656565,'harry');
insert into table_vendor values(vendor_seq.nextval,'def','ddddddddd',46464646,46464646,'harry1');
insert into table_vendor values(vendor_seq.nextval,'efg','eeeeeeeee',38383838,38383838,'harry2');
insert into table_vendor values(vendor_seq.nextval,'fgh','fffffffff',12345678,12345678,'harry3');
insert into table_vendor values(vendor_seq.nextval,'ghi','ggggggggg',98765432,98765432,'harry4');
insert into table_vendor values(vendor_seq.nextval,'hij','hhhhhhhhh',65432198,65432198,'harry5');

select * from table_vendor;

--创建bill表
drop table table_bill;

create sequence bill_seq
minvalue 1
maxvalue 9999999999
start with 1000000001
increment by 1
cache 20


create table table_bill(
	bill_id           number(10) primary key,
	vendor_id         number(10),
	bill_date         date,
	bill_due_date     date,
	bill_paid_flag    number(1) default 0,
	bill_amount       number(5),

  constraint vendor_id_fk foreign key(vendor_id)
  references table_vendor(vendor_id)
);

select * from table_bill;


--创建bill_item表。
drop table table_bill_item;

create sequence bill_item_seq
minvalue 1
maxvalue 9999999999
start with 1000000001
increment by 1
cache 20

create table table_bill_item(
	bill_id		number(10),
	dept_id         number(5),
	bill_item_id    number(10) primary key,
	bill_item_expense number(5),
  constraint bill_id_fk foreign key(bill_id) references table_bill(bill_id),
  constraint dept_id_id_fk foreign key(dept_id) references table_dept(dept_id)
);

select * from table_bill_item;

select bill_seq.nextval as id from table_bill;

--创建payment表
drop table table_payment;
create table table_payment(
	bill_id     number(10),
	check_id    number(10),
  constraint bill_fk foreign key(bill_id) references table_bill(bill_id),
  constraint check_id_fk foreign key(check_id) references table_check(check_id)
);



select * from table_payment;

--创建table_check

create sequence check_seq
minvalue 1
maxvalue 9999999999
start with 1000000001
increment by 1
cache 20


drop table table_check;
create table table_check(
	check_id  number(10) primary key,
	check_sum number(5)
);

select * from table_check;