求:::用SQL实现的任意数据库代码,题目不限,(例如:图书馆...)谢谢!

发布网友发布时间：2022-10-10 09:49

共1个回答

热心网友时间：2023-10-07 11:16

create database CaiSale
on primary --主要数据文件
(
name='caisale1',
filename='D:\创建的数据库\ASP项目\foodsale.mdf',
size=5mb,
maxsize=10mb,
filegrowth=10%
)
log on--日志文件
(
name='caisale2',
filename='D:\创建的数据库\ASP项目\foodale.ldf',
size=5mb,
maxsize=10mb,
filegrowth=10%
)
go
use CaiSale --调用数据库

create table UserInfo ---------------------1 用户信息表
(
UserId int identity(1,1) primary key, --用户编号
UserAccounts Varchar(30) not null, --用户帐号
[Password] Varchar(50) Not Null, --用户密码
UserName Nvarchar(10) Not Null, --用户姓名
Sex Char(2) not null, --用户性别
Birthday Datetime, --生日
Email Varchar(50), --邮箱
Roleid Int not null, --角色编号
Stateid Int not null --状态编号
)
go

select * from UserInfo
alter table UserInfo --性别检查约束
add constraint CK_Sex check (sex in('男','女'))
alter table userinfo --帐号唯一约束
add constraint UQ_UserAccounts unique(UserAccounts)
alter table UserInfo --为用户角色创建外键
add constraint FK_Roleid foreign key(Roleid) references UserRole(Roleid)
alter table UserInfo --为用户状态创建外键
add constraint FK_Stateid foreign key(Stateid) references UserState(UserStateId)

create table UserRole ----------------------2 用户角色表
(
Roleid int Identity(1,1) primary key, --角色编号
RoleName Varchar(20) Not Null --角色名称
)
go
insert into UserRole values('普通会员')
insert into UserRole values('VIP会员')
insert into UserRole values('管理员')
insert into UserRole values('待审核人员')

select * from UserRole
alter table UserRole --角色名称唯一约束
add constraint UQ_Rolename unique(rolename)

create table UserState -----------------------3 用户状态表
(
UserStateId Int Identity(1,1) primary key, --状态编号
UserStateName Varchar(20) Not Null --状态名称
)
go
select * from UserState
alter table UserState --状态名称唯一约束
add constraint UQ_UserStateName unique(UserStateName)
go
insert into userstate values('正常')
insert into userstate values('屏蔽')

create table CaiType --------------------------4 菜单类型表
(
CaiTypeId Int Identity(1000,1) primary key, --菜类型编号
CaiTypeName Varchar(20) not null, --菜类型名称
)
go
insert into CaiType values('蔬菜')
insert into CaiType values('水果')
insert into CaiType values('肉食')

select * from CaiType
alter table CaiType --菜类型名称唯一约束
add constraint UQ_CaiTypeName unique(CaiTypeName)

create table Cai ---------------------------5 菜单表
(
CaiId Int Identity(1,1) primary key, --菜的编号
CaiName Varchar(20) not null, --菜的名称
CaiTypeId Int not null, --菜的类型编号
CaiDesc Varchar(50) , --菜的备注
CaiPrice Money not null, --菜的价格
caiimage varchar(40) not null --菜的图片
)
go

select * from Cai
alter table Cai --菜的类型编号创建外键
add constraint FK_CaiTypeId foreign key(CaiTypeId) references CaiType(CaiTypeId)
alter table Cai --菜的名称唯一约束
add constraint UQ_CaiName unique(CaiName)

create table ShoppingCai --------------------------6 菜的销售表
(
ShoppingCaiID Int Identity(1,1) primary key, --销售编号
Userid int not null, --用户编号
Caiid Int not null, --菜的编号
CaiPrice money Not Null, --菜的价格
Num Int not null --菜的数量
)
go
insert into ShoppingCai values(4,27,7.8,6)
insert into ShoppingCai values(4,33,25,4)
insert into ShoppingCai values(4,56,25,3)
insert into ShoppingCai values(4,13,17.9,5)

select * from ShoppingCai
alter table ShoppingCai --用户编号创建外键
add constraint FK_CaiTypeId2 foreign key(Caiid) references Cai(Caiid)
alter table ShoppingCai --菜的编号创建外键
add constraint FK_Userid foreign key(Userid) references Userinfo(Userid)

create table Shophistory ---------------------------7 菜的销售记录表
(
ShopHistoryId Int Identity(1,1) primary key, --销售历史编号
Userid int Not Null, --用户编号
CaiId Int Not Null, --菜的编号
ShopTime Datetime Not Null, --销售时间
Num int not null--菜的数量
)
go
insert into Shophistory values(3,11,'2011-5-1 12:20',9)
insert into Shophistory values(3,53,'2011-3-15 17:35',5)
insert into Shophistory values(9,73,'2011-5-10 10:00',4)
insert into Shophistory values(4,73,'2011-5-11 10:11',4)

select * from Shophistory
alter table Shophistory --用户编号创建外键
add constraint FK_userid2 foreign key(userid) references UserInfo (userid)
alter table Shophistory --菜的编号创建外键
add constraint FK_CaiId foreign key(CaiId) references Cai(CaiId)

create table SysFun -------------------------------8 用户功能表
(
NodeId Int Identity(1,1) primary key, --编号
Displayname Varchar(50) Not Null, --所用的区域
Nodeurl Varchar(50) Not Null, --定向的字符串
Displayorder int Not Null, --一级节点
Parentnodeid int --节点的子节点，二级节点
)
go
select * from SysFun
insert into SysFun values('个人管理','',1,0)
insert into SysFun values('系统管理','',2,0)
insert into SysFun values('会员管理','',3,0)
insert into SysFun values('综合信息','',4,0)
insert into Sysfun values('资料修改','SysManage/UserUpdate.aspx',1,1)
insert into Sysfun values('审核操作','SysManage/Auditing.aspx',2,1)
insert into Sysfun values('用户管理','SysManage/UserManage.aspx',3,2)
insert into Sysfun values('角色管理','SysManage/RoleManage.aspx',4,2)
insert into Sysfun values('菜的类别管理','SysManage/CaiTypeManage.aspx',5,2)
insert into Sysfun values('菜的管理','SysManage/CaiManage.aspx',6,2)
insert into Sysfun values('汇款审核','SysManage/PostAccess.aspx',7,2)
insert into Sysfun values('销售统计','CaiManage/SaleStatistic.aspx',8,4)
insert into Sysfun values('浏览投诉','CaiManage/ScanAdvice.aspx',9,4)
insert into Sysfun values('联系我们','MemberOperate/ConnectUs.aspx',10,4)
insert into Sysfun values('购物记录','MemberOperate/ShopHistory.aspx',11,4)
insert into Sysfun values('存款记录','MemberOperate/PostHistory.aspx',12,4)
insert into Sysfun values('汇款通知','MemberOperate/PostNotice.aspx',13,3)
insert into Sysfun values('投诉建议','MemberOperate/Advice.aspx',14,3)

create table Roleright ------------------------------9 功能和角色的连接表
(
rolerightid Int Identity(1,1) primary key, --连接编号
roleid int Not Null, --角色编号
nodeid Int Not Null, --功能编号
)
go
select * from Roleright
alter table Roleright --角色编号创建外键
add constraint FK_Roleright_Roleid Foreign key(roleid) references UserRole(Roleid)
alter table Roleright --功能编号创建外键
add constraint FK_Roleright_nodeid foreign key(nodeid) references Sysfun(nodeid)

create table CaiNews -------------------------------10 新闻表
(
NewsId Int Identity(1,1) primary key, --新闻编号
Title Varchar(20) Not Null, --新闻标题
Content Varchar(500), --新闻内容
Newstime Datetime Not Null, --新闻时间
NewsState Int Not Null, --新闻发布状态
)
go
select * from Cainews
alter table CaiNews --新闻标题唯一约束
add constraint UQ_title unique(title)

create table Advice -------------------------------11 客户建议表
(
AdviceId Int Identity(1,1) primary key, --建议编号
UserId Int Not Null, --用户编号
Content Varchar(500) --建议内容
)
go
select * from Advice
alter table advice --用户编号创建外键
add constraint FK_advice_userid foreign key(userid) references userinfo(userid)

--创建视图
create view viewuserright
as
SELECT TOP (100) PERCENT a.NodeId, a.RoleId AS Expr1, b.UserId, b.UserName, c.DisplayName,
c.NodeURL, d.RoleName AS Expr2, c.ParentNodeId
FROM dbo.RoleRight AS a INNER JOIN
dbo.UserInfo AS b ON a.RoleId = b.roleid INNER JOIN
dbo.SysFun AS c ON a.NodeId = c.NodeId INNER JOIN
dbo.userrole AS d ON a.RoleId = d.RoleId
ORDER BY c.NodeId追问您好，有没有相对应的指导书，报告呢？谢谢！

追答你说的指导书指的是什么？