【数据库MySQL】数据库网上书店管理系统

网上书店管理系统

1.创建数据库和数据表

USE [MASTER]
GO

IF EXISTS(SELECT * FROM SYSDATABASES WHERE NAME=BOOKSTORE)
DROP DATABASE BOOKDTORE;
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 MANAGER(
MANAGERID INT PRIMARY KEY,
PHONE VARCHAR(11) NOT NULL,
PASSWORD VARCHAR(50) NOT NULL
);
CREATE TABLE WARE(
BOOKID VARCHAR(50) NOT NULL,
WARENUM INT NOT NULL,
WAREAD VARCHAR(50) NOT  NULL,
WAREID VARCHAR(10) PRIMARY KEY NOT NULL
);
CREATE TABLE EXPRESS(
ORDERID INT NOT NULL,
EXPRESSID VARCHAR(20) PRIMARY KEY NOT NULL,
EXPRESSCP VARCHAR(10) NOT NULL
);
CREATE TABLE USERINFO(
USERID VARCHAR(10) PRIMARY KEY,
PASSWORD VARCHAR(50) NOT NULL,
USERNAME VARCHAR(4) NOT NULL,
ADDRESS VARCHAR(100) NOT NULL,
PHONE VARCHAR(11) NOT NULL
);
CREATE TABLE CATE(
CATEID INT IDENTITY(1,1) NOT NULL,
CATEINFO VARCHAR(50) NOT NULL,
CATENAME VARCHAR(50) NOT NULL,
PRIMARY KEY(CATEID)
);
CREATE TABLE BOOK(
BOOKID VARCHAR(50) PRIMARY KEY NOT NULL,
WRITER VARCHAR(50) NOT NULL,
PRESS VARCHAR(50) NOT NULL,
PUBTIME DATETIME NOT NULL,
PUBNUM INT NOT NULL,
VERSION VARCHAR(50) NOT NULL,
PAGES INT NOT NULL,
BOOKINTRO VARCHAR(100),
PROINTRO VARCHAR(100),
CATEID INT NOT NULL,
PRICE FLOAT NOT NULL,
BOOKNAME VARCHAR(10) NOT NULL,
FOREIGN KEY(CATEID) REFERENCES CATE(CATEID) ON DELETE NO ACTION ON UPDATE CASCADE,
);
CREATE TABLE ORDERINFO(
ORDERID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
ORDERTIME DATETIME NOT NULL,
USERID VARCHAR(10) NOT NULL,
BOOKID VARCHAR(50) NOT NULL,
QUANTITY INT DEFAULT 1,
SENDTIME DATETIME NOT NULL,
STATE VARCHAR(10) CHECK(STATE IN(等待,执行,完成)),
FOREIGN KEY(USERID) REFERENCES USERINFO(USERID) ON DELETE NO ACTION ON UPDATE CASCADE,
FOREIGN KEY(BOOKID) REFERENCES BOOK(BOOKID) ON DELETE NO ACTION ON UPDATE CASCADE,
);

2.视图创建

CREATE VIEW ORDERSITEMPRICE
AS
SELECT USERINFO.USERID AS 用户名,BOOK.BOOKNAME AS 图书名称,BOOK.PRICE AS 单价,ORDERINFO.QUANTITY AS 数量,(BOOK.PRICE*ORDERINFO.QUANTITY) AS 合计, ORDERINFO.ORDERTIME AS 时间
FROM USERINFO,ORDERINFO,BOOK
WHERE USERINFO.USERID=ORDERINFO.USERID AND ORDERINFO.BOOKID=BOOK.BOOKID;

3.触发器创建

CREATE TRIGGER TRIG1
ON WARE
FOR UPDATE
AS
IF(UPDATE(WARENUM))
  BEGIN
  DECLARE @BOOKWARE INT
  SET @BOOKWARE=(SELECT WARENUM FROM DELETED)
  IF(@BOOKWARE<0)
  BEGIN
    PRINT 库存不足，库存为空
    ROLLBACK
END
END

4.存储过程创建

create procedure sp2
@STARTDATE DATE
AS
BEGIN
IF(@STARTDATE IS NULL)
RAISERROR(时间为空,5,5)
ELSE 
SELECT BOOKNAME,COUNT(*) FROM BOOK
WHERE DATEDIFF(DAY,PUBTIME,@STARTDATE)<0
GROUP BY BOOKNAME
END

5.加密过程

CREATE MASTER KEY ENCRYPTION BY PASSWORD =passW@ord
GO
CREATE CERTIFICATE TestCert with SUBJECT = Test Certificate
GO
CREATE SYMMETRIC KEY TestSymmetric WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE TestCert 
GO