网上书店管理系统数据库 sql sever

use [master]
go

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

create database bookStore

ON PRIMARY
(
name='bookStore_data', 
 -- 主数据文件的物理名称	
filename='D:\bookStore_data.mdf',
--主数据文件的初始大小
size=5mb,
-- 主数据文件增长的最大值
maxsize=100mb, 
--主数据文件的增长率
filegrowth=20%
)
LOG ON
(
--日志文件的具体描述
--日志文件的逻辑名称
name='bookStore_log',
--日志文件的物理名称
filename='D:\bookStore_log.ldf',
--日志文件的初始大小
size=3mb,
--日志文件的大小
filegrowth=1mb
)
go

use bookStore;

create table catalog(
	catalogid int identity(1,1) not null,
	catalogname varchar(20) not null,
	primary key(catalogid)
);

create table book(
	bookid int identity(1,1) primary key not null,
	catalogid int not null,
	bookname varchar(20) not null,
	price int not null,
	picture varchar(30) not null,
	foreign key(catalogid) references catalog(catalogid) on delete no action on update cascade
);

create table users(
	userid int identity(1,1) primary key not null,
	username varchar(20) not null,
	password varchar(20) not null,
	sex varchar(4),
	age int,
);

create table orders(
	orderid int identity(1,1) primary key not null,
	userid int not null,
	orderdate datetime not null,
	foreign key (userid) references users(userid) on delete no action on update cascade
);

create table orderitem(
	orderitemid int identity(1,1) primary key not null,
	bookid int not null,
	orderid int not null,
	quantity int not null,
	foreign key (bookid)references book(bookid) on delete no action on update cascade,
	foreign key (orderid)references orders(orderid) on delete no action on update cascade,);

create view ordersItemPrice
as
select users.username as 用户名,bookname as 图书名称,price as 单价,orderitem.quantity as 数量,(book.price*orderitem.quantity) as 合计, orders.orderdate as 时间
from users,orders,orderitem,book
where users.userid=orders.userid and orderitem.orderid=orders.orderid and orderitem.bookid=book.bookid;

create view ordersTotalPrice
as
select users.username as 用户名 ,orders.orderid as 订单编号 ,sum(book.price*orderitem.quantity) as 应付金额, orders.orderdate as 时间
from users,orders,orderitem,book
where users.userid=orders.userid and orderitem.orderid=orders.orderid and orderitem.bookid=book.bookid
group by users.username,users.userId,orders.orderid,orders.orderdate;