/ * Libeasyman) SQL script * /
Create Database Libeasyman;
Use Libeasyman;
/ * Manager information table * / create Table Sysuser (ID Numeric (5) Not null, / * Manager number * / name varchar (32) Not null, / * Manage Name * / Password Varchar (12), / * Manager password * / / * 0: Ordinary users, 1: System administrator * / privilege numeric (1) default 0 not null, / * Managers Permissions * / Primary Key (ID));
/ * Create a system administrator * / Insert INTO SYSUSER VALUES (10000, 'DBA', 'SA', 1); / * Create System User Personnel * / Insert Into Sysuser Values (10001, 'Test', '123', 0 );
/ * Book Type Information Table * / Create Table BookType (ID Numeric (3) Not Null, / * Type No. * / Name Varchar (32) Not Null, / * Type Name * / Primary Key (ID));
Insert Into Booktype Values (001, 'Literature "; Insert Into BookType Values (002,' Science '); Insert Into BookType Values (003,' Medicine); Insert Into BookType Values (004, 'Computer');
/ * All book information table * / create table books (isbn varchar (18) Not null, / * isbn number * / name varchar (64) Not null, / * Book name * / author varchar (32), / * Author name * / BookType Numeric (3) Not null, / * Book type NULL, / * Publish Date * / HOWMUCH NUMERIC (6, 2) Not Null, / * Book Pricing * / Remark Varchar (128), / * Note * / / * YY-mm-DD * / AddTime Varchar (8) Not NULL, / * Add Time * / Primary Key (ISBN));
INSERT INTO BOOKS VALUES ('100000000000', 'Windows95 System Programming ",' No Mames ', 004,' Huaxia Publishing House ',' 2004-07-15 ', 85.50,' No ', '04 -07-25';
/ * Lost Book Information Table * / Create Table Bookloss (ISBN VARCHAR (18) Not Null, / * ISBN Number * / Name Varchar (64) Not Null, / * Book Name * / Author VARCHAR (32), / * Author name * / BookType Numeric (3) Not null, / * Book type NULL, / * Publish Date * / HOWMUCH NUMERIC (6, 2) Not Null, / * Book Pricing * / Remark Varchar (128), / * Note * / / * YY-mm-DD * / AddTime Varchar (8) Not NULL, / * Add Time * / Losstime Varchar (8) Not Null, / * Lost Time * / Primary Key (ISBN, Losstime)); / * Reader Type Information Table * / Create Table Readertype (ID Numeric (3) Not Null , / * Type number * / name varchar (32) Not null, / * Type name * / days numeric (3) Default 30 Not null, / * Borrowing days * / maxnum numeric (2) Default 1 NOT NULL, / * Maximum number * / connum numeric (2) Default 0 Not null, / * Renewal Number * / Primary Key (ID));
Insert Into Readertype Values (001, 'Undergraduate', 30, 2, 0); Insert Into Readertype Values (002, 'Graduate', 40, 4, 10); Insert Into Readertype Values (003, 'Doctoral ", 50 , 6, 20); Insert Into Readertype Values (004, 'Teacher / Employee ", 60, 6, 30);
/ * Reader information table * / create table reader (ID Numeric (8) Not null, / * reader number * / name varchar (32) Not null, / * reader name * / / / * 0: Male, 1: female * / SEX NUMERIC (1) DEFAULT 0 NOT NULL, / * Gender * / Age Numeric (2) Not null, / * Age * / Address VARCHAR (128), / * Address * / Readertype Numeric (3) Not null, / * Reader Type, associated with readertype table * / Identify varchar (18) Not null, / * valid ID number * / / * 0: Normal, 1: Loss, 2: Seaver * / status numeric (1) default 0 Not null, / * Reader status * / / * YYYY-mm-dd * / regtime varchar (10) Not null, / * Registration Date * / Primary Key (ID));
INSERT INTO Reader Values (00000001, 'Test ", 0, 50,' Abcdefghijklmnopqrstuvwxyz ', 2,' 000000000000000000 ', 0,' 2004-07-25 ');
/ * Borrow information table * / create Table Borrow (Readerid Numeric (8) Not null, / * reader number * / bookisbn varchar (18) Not null, / * isbn number * / conborrow numeric (1) Default 0 Not Null, / * Solid borrowing flag * / / * YYYY-mm-dd * / borrowdate varchar (10) Not null, / * borrow date * / / * YYYY-MM-DD * / / * 0000-00-00: Not returned 1111-11-11: Lost xxxx-xx-xx: returned * / returndate varchar (10), / * Return Date * / Primary Key (Readerid, Bookisbn, BorrowDate); / * Publishing House Name Management Information Table * / CREATE Table Publisher (Name Varchar (64) Not Null, Primary Key (Name);
INSERT INTO PUBLISHER VALUES ('Electronics Press'); Insert Into Publisher Values (' Tsinghua University Press'); Insert Into Publisher Values ('Peking University Press'); Insert Into Publisher Values (' Fudan University Press " ); INSERT INTO PUBLISHER VALUES ('Nanjing University Press'); Insert Into Publisher Values (' Nankai University Press'); Insert Into Publisher Values ('Southeast University Press'); Insert Into Publisher Values (' China Science and Technology University Press');
/ * Book number temporary table * / create table isbntemp (isbn varchar (18) Not Null, Primary Key (ISBN));
