db.sql

java编写小型超市管理系统+数据库.用于小型超市简单的货品进存销

create database supermarket
go
use supermarket
go

create table saled_log(-------消费记录表
sale_man varchar(20),
user_id varchar(20),
pay_time datetime,
pay_money varchar(10)
)
create table adminlist   ----管理员表
(
admin_id varchar(20) primary key,
admin_name varchar(20) not null,
admin_pwd varchar(20) not null
)
create table userlist   ---会员表
(
user_id varchar(15) primary key,
user_name varchar(20) not null,
user_date varchar(10) not null,--创建日期
user_buy varchar(10) not null,   --共消费
user_pay float check(user_pay >0 or user_pay <=1) default 0.9
)

create table itemlist  ---物品表
(
item_id varchar(20) primary key,  --物品编号
item_name varchar(20) not null,		--物品名称
item_dw varchar(10) not null,  	--物品单位
item_dj varchar(10) not null,		--物品单价
item_kind varchar(10) check (item_kind in('生活','饮食','书籍','健康','IT','其他'))
)


insert into adminlist values('1','admin','admin')
insert into adminlist values('2','zhang','2')
insert into adminlist values('3','wang','3')

insert into userlist values('1000','临时用户','2006-03-01',0,1.0)
insert into userlist values('1001','老王','1986-03-05',300,0.9)
insert into userlist values('1002','老张','1999-01-05',2540,0.9)
insert into userlist values('1003','老叶','2001-05-26',2345,0.9)
insert into userlist values('1004','小叶','2006-11-12',5324,0.9)


insert into itemlist values('1001','可乐','罐',2.00,'饮食')
insert into itemlist values('1002','饼干','包',1.00,'饮食')
insert into itemlist values('1003','雪碧','罐',2.00,'饮食')
insert into itemlist values('1004','大衣','件',222.00,'生活')
insert into itemlist values('1005','蒙牛纯牛奶','罐',1.20,'饮食')
insert into itemlist values('1006','<Think in Java>','本',60.00,'书籍')
insert into itemlist values('1007','微软IE3.0鼠标','个',300.00,'IT')
insert into itemlist values('1008','微软IO1.0鼠标','个',230.00,'IT')
insert into itemlist values('1009','微软IE3.0键盘','个',222.00,'IT')
insert into itemlist values('1011','罗技光电高手键盘','个',60.00,'IT')
insert into itemlist values('1010','<ASP解读>','本',44.00,'书籍')

select * from userlist