聲明:本博文如存在問題，歡迎各位dalao指正！！！

實驗目的與任務：

掌握對數據庫和表對象的操作及管理。

一.創建數據庫library

CREATE DATABASE libary;

二.進入數據庫

USE library;

三.開始建庫建表

1.借閱人員分類信息表：Clas_User

DROP TABLE IF EXISTS Class_User; CREATE TABLE Class_User( classNo TINYINT NOT NULL PRIMARY KEY, cname VARCHAR(20) NOT NULL UNIQUE, term TINYINT NOT NULL default 30, ceilingNum TINYINT NOT NULL );

2.圖書信息表：BookInfo

DROP TABLE IF EXISTS BookInfo; CREATE TABLE BookInfo( ISBN CHAR(13) NOT NULL PRIMARY KEY, bname VARCHAR(60) NOT NULL, author VARCHAR(30) NOT NULL, press VARCHAR(40) NOT NULL, price FLOAT NOT NULL, language VARCHAR(20) DEFAULT '中文', pages int );

3.圖書目錄表：Books

DROP TABLE IF EXISTS Books; CREATE TABLE Books( bookNo CHAR(9) PRIMARY KEY, ISBN CHAR(13), location VARCHAR(40) , bstatus CHAR(1), ctr_no TINYINT, CHECK(bstatus='0' OR bstatus='1' OR bstatus='2' OR bstatus='3'), FOREIGN KEY (ISBN) REFERENCES BookInfo(ISBN) );

4.借閱人員的信息表：Users

DROP TABLE IF EXISTS Users; CREATE TABLE Users( loanNo VARCHAR(16) NOT NULL PRIMARY KEY, lname VARCHAR(30) NOT NULL, pwd VARCHAR(16) NOT NULL, unitName VARCHAR(50) NOT NULL, registerDate DATE NOT NULL, classNo TINYINT NOT NULL, email VARCHAR(40) );

5.借閱信息表：Loan

DROP TABLE IF EXISTS Loan; CREATE TABLE Loan( bookNo CHAR(9) NOT NULL PRIMARY KEY, loanNo VARCHAR(16)NOT NULL, borrowDate DATETIME NOT NULL, FOREIGN KEY(loanNo) REFERENCES Users(loanNo), FOREIGN KEY(bookNo) REFERENCES Books(bookNo) );

6.借閱歷史表:LoanHist

DROP TABLE IF EXISTS LoanHist; CREATE TABLE LoanHist( loanNo VARCHAR(16) NOT NULL, bookNo CHAR(9) NOT NULL, borrowDate DATETIME NOT NULL, returnDate DATETIME NOT NULL, primary key(bookNo,loanNo,borrowDate), foreign key(bookNo) references Books(bookNo), foreign key(loanNo) references Users(loanNo) );

7.預約表：Reservation

DROP TABLE IF EXISTS Reservation; CREATE TABLE Reservation( ISBN CHAR(13) NOT NULL, loanNo VARCHAR(16) NOT NULL, reservation_Date DATETIME NOT NULL, rsratus CHAR(1) NOT NULL, primary key(ISBN,loanNo,reservation_Date), foreign key(ISBN) references BookInfo(ISBN), foreign key(loanNo) references Users(loanNo) );

8.收費表：Money

DROP TABLE IF EXISTS Money; CREATE TABLE Money( ID int NOT NULL AUTO_INCREMENT PRIMARY KEY, loanNo VARCHAR(16) NOT NULL, bookNo CHAR(9), amount FLOAT , reason ENUM('過期罰款','損壞賠償','丟失賠償','辦證費','辦證押金'), billdate DATETIME NOT NULL );

總結

以上是生活随笔為你收集整理的mysql实验一：图书管理系统之建库建表的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。