sql语句t_use,t_goods,t_shop

大家好，欢迎来到IT知识分享网。

--create database mydata; --use mydata; 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) 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 money not null default 0, p_shopid uniqueidentifier not null default newid(), primary key(p_shopid) );