sql

drop table t_user;
create table t_user(
	username nvarchar(10) not null default N'默认用户名',	unique(username),           --用户名，需要不重名
	userpass nvarchar(10) not null default N'默认密码',                           --密码
	a bit not null default 0,                     --访问某页的权限开关
	b bit not null default 0,
	c bit not null default 0,
	d bit not null default 0,
	e bit not null default 0,
	f bit not null default 0,
	g bit not null default 0, 
	h bit not null default 0,
	i bit not null default 0,
	j bit not null default 0,
	k bit not null default 0,
	l bit not null default 0,
	m bit not null default 0,
	n bit not null default 0,
	o bit not null default 0,
	p bit not null default 0,
	q bit not null default 0,
	r bit not null default 0,
	s bit not null default 0,
	t bit not null default 0,
	u bit not null default 0,
	v bit not null default 0,
	w bit not null default 0,
	x bit not null default 0,
	y bit not null default 0,
	z bit not null default 0,
	--userdate date not null default getdate(),      --最后编辑时间
	p_user_id uniqueidentifier not null default newid(),	primary key(p_user_id)      --主键
);
insert into t_user (username, userpass,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z) values (N'0', N'0',1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1);
insert into t_user (username, userpass,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z) values (N'镇二', N'2',1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1);
insert into t_user (username, userpass) values (N'a', N'a');
insert into t_user (username, userpass) values (N'user', N'user');
insert into t_user (username, userpass) values (N'张三', N'333');
insert into t_user (username, userpass) values (N'李四', N'444');
insert into t_user (username, userpass) values (N'王五',N'555');
insert into t_user (username, userpass) values (N'刘六',N'666');
insert into t_user (username, userpass) values (N'按时地方',N'6');
insert into t_user (username, userpass) values (N'asdf',N'6');
insert into t_user (username, userpass) values (N'asdfgb',N'6');
insert into t_user (username, userpass) values (N'34',N'6');
insert into t_user (username, userpass) values (N'fg',N'6');
insert into t_user (username, userpass) values (N'cb',N'6');
insert into t_user (username, userpass) values (N'fgh34',N'6');
insert into t_user (username, userpass) values (N'dfh',N'6');
insert into t_user (username, userpass) values (N'sdfg',N'6');
insert into t_user (username, userpass) values (N'345dfg',N'6');
insert into t_user (username, userpass) values (N'34534dfg',N'6');
insert into t_user (username, userpass) values (N'dfg',N'6');
insert into t_user (username, userpass) values (N'56',N'6');
insert into t_user (username, userpass) values (N'456',N'6');
insert into t_user (userpass) values (N'666');
select * from t_user order by username asc

drop table t_goods
create table t_goods(
	fenlei nvarchar(10) not null default N'默认分类',
	bianma nvarchar(10) not null default N'默认编码', unique(bianma),
	goodsname nvarchar(10) not null default N'默认名称',unique(goodsname),
	danwei nvarchar(10) not null default N'默认单位',
	danjia money not null default 0,
	p_goods_id uniqueidentifier not null default newid(),	primary key(p_goods_id)
);
insert into t_goods (fenlei,bianma,goodsname,danwei,danjia) values ('肉类','r001','猪肉', '斤', 8);
insert into t_goods (fenlei,bianma,goodsname,danwei,danjia) values ('肉类','r002','羊肉', '斤', 32);
insert into t_goods (fenlei,bianma,goodsname,danwei,danjia) values ('肉类','r003','牛肉', '斤', 30);
insert into t_goods (fenlei,bianma,goodsname,danwei,danjia) values ('肉类','r004','驴肉', '斤', 30);
insert into t_goods (fenlei,bianma,goodsname,danwei,danjia) values ('肉类','r005','狗肉', '斤', 40);
insert into t_goods (fenlei,bianma,goodsname,danwei,danjia) values ('肉类','r006','猫肉', '斤', 20);
insert into t_goods (fenlei,bianma,goodsname,danwei,danjia) values ('肉类','r007','兔肉', '斤', 18);
insert into t_goods (fenlei,bianma,goodsname,danwei,danjia) values ('肉类','r008','鸡肉', '斤', 5);
insert into t_goods (fenlei,bianma,goodsname,danwei,danjia) values ('肉类','r009','鸭肉', '斤', 6);
insert into t_goods (fenlei,bianma,goodsname,danwei,danjia) values ('肉类','r010','鹅肉', '斤', 10);
insert into t_goods (fenlei,bianma,goodsname,danwei,danjia) values ('蔬菜类','c001','菠菜', '斤', 5.5);
insert into t_goods (fenlei,bianma,goodsname,danwei,danjia) values ('蔬菜类','c002','白菜', '斤', 1.2);
insert into t_goods (fenlei,bianma,goodsname,danwei,danjia) values ('蔬菜类','c003','芹菜', '斤', 1.5);
insert into t_goods (fenlei,bianma,goodsname,danwei,danjia) values ('蔬菜类','c004','油菜', '斤', 2.2);
insert into t_goods (fenlei,bianma,goodsname,danwei,danjia) values ('蔬菜类','c005','韭菜', '斤', 9.6);
insert into t_goods (fenlei,bianma,goodsname,danwei,danjia) values ('蔬菜类','c006','包菜', '斤', 2.2);
insert into t_goods (fenlei,bianma,goodsname,danwei,danjia) values ('蔬菜类','c007','生菜', '斤', 5.5);
insert into t_goods (fenlei,bianma,goodsname,danwei,danjia) values ('蔬菜类','c008','萝卜', '斤', 1.5);
insert into t_goods (fenlei,bianma,goodsname,danwei,danjia) values ('蔬菜类','c009','茭瓜', '斤', 2.5);
insert into t_goods (fenlei,bianma,goodsname,danwei,danjia) values ('蔬菜类','c010','冬瓜', '斤', 1.5);
select * from t_goods order by fenlei asc, bianma asc

drop table t_shop
create table t_shop(
	fenlei nvarchar(10) not null default N'默认分类',
	bianma nvarchar(10) not null default N'默认编码', 
	goodsname nvarchar(10) not null default N'默认名称',
	danwei nvarchar(10) not null default N'默认单位',
	danjia money not null default 0,	
	shuliang int not null default 0,
	username nvarchar(10) not null default N'默认用户名',
	p_shop_id uniqueidentifier not null default newid(), primary key(p_shop_id)
);
select * from t_shop
insert into t_shop (fenlei, bianma, goodsname, danwei, danjia, username) select fenlei, bianma, goodsname, danwei, danjia, '0' from t_goods

select count(*) from t_shop where username = '0' and shuliang = 0
insert into t_shop (fenlei) select fenlei from t_goods
insert into t_shop (username) values ('aa')
--drop table t_dingdanlist
--create table t_dingdanlist(
--	fenlei nvarchar(10) not null default N'默认分类',
--	bianma nvarchar(10) not null default N'默认编码', 
--	goodsname nvarchar(10) not null default N'默认名称',
--	danwei nvarchar(10) not null default N'默认单位',
--	danjia money not null default 0,	
--	shuliang money not null default 0,
--	dingdanliebiaodate date not null default getdate(),
--	username nvarchar(10) not null default N'默认用户名',
--	p_dingdanlist_id uniqueidentifier not null default newid(), primary key(p_dingdanlist_id)
--);
drop table t_dingdanlist
create table t_dingdanlist
(
	username nvarchar(10) not null default N'默认用户名',
	shijian datetime not null default getdate(),
	tiaoshu int not null default 0,
	jine money not null default 0,
	zhuangtai bit not null default 0,
	p_dingdanlist_id uniqueidentifier not null default newid(), primary key(p_dingdanlist_id)
);

insert into t_dingdanlist (username,zhuangtai) values ('0',1)

select * from t_dingdanlist

drop table t_dingdandetail
create table t_dingdandetail(
	f_dingdanlist_id uniqueidentifier not null FOREIGN KEY REFERENCES t_dingdanlist(p_dingdanlist_id),
	fenlei nvarchar(10) not null default N'默认分类',
	bianma nvarchar(10) not null default N'默认编码', 
	goodsname nvarchar(10) not null default N'默认名称',
	danwei nvarchar(10) not null default N'默认单位',
	danjia money not null default 0,	
	shuliang int not null default 0,
	username nvarchar(10) not null default N'默认用户名',
	p_dingdandetail_id uniqueidentifier not null default newid(), primary key(p_dingdandetail_id)
)