MySQL练习

创建数据库（数据库名为Library）及上述四个表（TB_ReaderType、TB_Reader、TB_Book和TB_Borrow）。

CREATE DATABASE IF NOT EXISTS Library;
USE Library;
CREATE TABLE IF NOT EXISTS TB_ReaderType (
    rdType INT PRIMARY KEY,
    rdTypeName VARCHAR(20) UNIQUE NOT NULL,
    CanLendQty INT,
    CanLendDay INT,
    CanContinueTimes INT,
    PunishRate DECIMAL(3, 2),
    DateValid INT NOT NULL
);

CREATE TABLE TB_Reader (
    rdID INT PRIMARY KEY,
    rdName VARCHAR(20),
    rdSex CHAR(1),
    rdType INT NOT NULL,
    rdDept VARCHAR(20),
    rdPhone CHAR(11),
    rdEmail VARCHAR(30),
    rdDateReg DATETIME,
    rdPhoto BLOB,
    rdStatus CHAR(2),
    rdBorrowQty INT DEFAULT 0,
    rdPwd VARCHAR(20),
    rdAdminRoles INT
);

CREATE TABLE IF NOT EXISTS TB_Book (
    bkID INT AUTO_INCREMENT PRIMARY KEY,
    bkCode VARCHAR(20),
    bkName VARCHAR(50),
    bkAuthor VARCHAR(30),
    bkPress VARCHAR(50),
    bkDatePress DATETIME,
    bkISBN VARCHAR(15),
    bkCatalog VARCHAR(30),
    bkLanguage INT,
    bkPages INT,
    bkPrice DECIMAL(4, 1),
    bkDateIn DATETIME,
    bkBrief TEXT,
    bkCover BLOB,
    bkStatus CHAR(2)
);

 

CREATE TABLE IF NOT EXISTS TB_Borrow (
    BorrowID INT AUTO_INCREMENT PRIMARY KEY,
    rdID INT REFERENCES TB_Reader(rdID),
    bkID INT REFERENCES TB_Book(bkID),
    ldContinueTimes INT DEFAULT 0,
    ldDateOut DATETIME,
    ldDateRetPlan DATETIME,
    ldDateRetAct DATETIME,
    ldOverDay INT DEFAULT 0,
    ldOverMoney DECIMAL(6, 2) DEFAULT 0,
    ldPunishMoney DECIMAL(6, 2) DEFAULT 0,
    lsHasReturn BIT DEFAULT 0,
    OperatorBorrow VARCHAR(20),
    OperatorReturn VARCHAR(20)
);

运行结果

 后面还要插入数据呀。