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

【数据库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, 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 O