ASP.NET网站开发

数据库

use master

go

if exists(select * from sysdatabases where name = 'db_user')
    drop database db_user
go

create database db_user
go

use db_user
go

---用户表
if exists(select * from sysobjects where name = 'UserInfo')
    drop table UserInfo
go

create table UserInfo
(
    ID int identity(1,1) not null primary key, --id
    Username varchar(20) not null,             --姓名
    Password varchar(20) not null,             --密码
    Email varchar(50) not null                 --邮箱
)
go

--角色表
if exists(select * from sysobjects where name = 'Role')
    drop table [Role]
go

create table [Role]
(
    ID int identity(1,1) not null primary key,     --id
    RoleName varchar(10) not null                  --角色名
)
go


--用户角色信息表
if exists(select * from sysobjects where name = 'UserRole')
    drop table UserRole
go

create table UserRole
(
    id INT PRIMARY KEY IDENTITY(1,1),             --id
    UserID int references UserInfo(ID),           --用户id
    RoleID int references [Role](ID)              --角色id
)
go


--用户详细表
if exists(select * from sysobjects where name = 'Product')
    drop table Product
go

create table Product
(
    ID int identity(1,1) not null primary key,        --id
    Name varchar(200) not null,                       --作品名
    Remark varchar(50) not null,                      --说明
    UserId int references UserInfo(ID),               --用户id
    Price int default(0) not null,                    --价格
    CreateDate datetime default(getdate()) not null,  --创建时间
    LasterDate datetime default(getdate()) not null,  --结束时间
    [Status] int not null check([Status]=0 or [Status]=1 or [Status]=2)   --状态
)
go

--购物订单表
if exists(select * from sysobjects where name = 'Order')
    drop table [Order]
go
create table [Order]
(
    ID int identity(1,1) not null primary key,             --id
    OrderNo varchar(30) not null,                          --订单号
    UserId int references UserInfo(ID),                    --用户id
    CreateDate datetime default(getdate()) not null,       --创建日期
    [Status] int not null check([Status]=0 or [Status]=1)  --状态
)


insert into UserInfo
select 'admin','admin','admin@xiecan.cc'
union
select '张三','123456','zhangsan@xiecan.cc'
union
select '李四','111111','lisi@xiecan.cc'
union
select '王五','666666','wangwu@xiecan.cc'
union
select '赵六','888888','zhaoliu@xiecan.cc'
union
select '燕七','456789','yanqi@xiecan.cc'
union
select '娄艺潇','222222','louyixiao@xiecan.cc'
go

insert into [Role]
select '超级管理员'
union
select '管理员'
union
select '协管'
union
select '版主'
union
select '普通游客'
go


insert into UserRole
select 1,2
union
select 2,4
union
select 3,1
union
select 4,4
union
select 5,5
union
select 6,3
union
select 7,4
go

--向Product添加数据
insert into Product values('Java编程技术基础','Java入门级教科书',6,100,default,default,1)
insert into Product values('C#编程技术基础','C#入门级教科书',2,50,default,default,1)
insert into Product values('RFID射频识别技术','物联网专业教材',3,2,default,default,1)
go

--向[Order] 添加数据
insert into [Order] values('201404080786AC639',6,default,0)
insert into [Order] values('201404080786AC640',6,default,0)
insert into [Order] values('2014008010786AC888',4,default,0)
go


select * from UserInfo
select * from UserRole
select * from [Role]

select * from Product
select * from [Order]

select M.*,B.RoleName from UserInfo M inner join UserRole A on M.ID = A.UserID
inner join [Role] B on A.RoleID = B.ID
order by B.RoleName

--update UserInfo set Username='张小三' where ID=6

--delete Product where ID=3

ASP.NET网站开发