Use database
Create table borrowing level information (
Borrow grade int primary key,
Maximum borrowing time int,
Maximum borrowed quantity int,
)
Insert into the borrowing level information value
(0, 30, 10),
( 1, 60, 20),
(2, 90, 30),
(3, 120, 40)
Create table reader information (
Name char( 15) is not empty.
Certificate number char( 12) primary key,
The loan level int defaults to 0,
Cumulative borrowing interest defaults to 0,
Foreign key references borrowing level information (borrowing level)
)
Insert the reader information (name, ID number, borrowing level) value.
('Zhang San',' 54 1 607120165',1)?
('Li Si',' 541707010185', 3),
('Wang Wu',' 54 1 707120165',1),
('Liu Zhao',' 54 1505980268', 2),
('Sun Qi',' 541407010169', 0),
('eight weeks',' 54 13070 10489', 1)
Create table publishing house information (
Press varchar(20) primary key,
Address varchar(25),
Telephone Characters (7)
)
Insert into the news information value
('Tsinghua University Publishing House',' Beijing',' 497942 1'),
('Shengwei Publishing House',' Tianjin',' 5564 130'),
(Hainan Nanhai Publishing Company, 49849 10'),
("Shanghai Literature and Art Publishing House", "Shanghai", "6640239")
Create form book information (
Call number char( 15) primary key,
Author char( 15),
Book title char( 15),
Varchar(20)，
Date of publication,
Foreign key (news) refers to news information (news)
)
Insert information values into the book.
('b 12987',' Yan Weimin',' data structure',' Tsinghua University Publishing House',' 20 12-02-06'),
('b97894',' Keigo Higashino',' Magic Night',' Nanhai Publishing Company',' 2004-08-02'),
('b 16546',' Wu Yuhua',' Physical Experiment Course',' Tsinghua University Press',' 20 13-05- 15'),
('b89490',' Zhang Xuefeng',' Guidance for Postgraduate Entrance Examination',' Shengwei Publishing House',' 20 16- 12'),
('b56400',' Yong Zongpei',' Seeking Immortals on Paper',' Shanghai Literature and Art Publishing House',' 201-02-05')
Create form book information (
Bar code number char(7) primary key check (len (bar code number) = 7),
Call number char( 15),
Collection position varchar(40),
Book status varchar(6) check (book status in ('loanable',' borrowed',' non-loanable')),
Historical loan amount int defaults to 0,
Foreign key reference book information (call number)
)
Insert information (barcode number, call number, collection place, book status) values into a single book.
('t987628',' b97894',' A8 on the third floor',' Borrow'),
('t594805',' b97894',' C7 on the second floor',' Borrowable'),
('t9849 10',' b89490',' fifth floor A2',' lending'),
('t940566',' b 12987',' D3 on the first floor',' Borrow')
Create table borrowing information (
Borrowing number char(6) primary key,
Borrowing time and date,
Return time and date,
The book barcode number character (7),
Borrower's ID number char( 12),
The foreign key refers to a single book information (barcode number),
The foreign key (ID number of the borrower) refers to the reader information (ID number).
)
Create table annotation information (
Comment number char(8) primary key,
The score is a number (2, 1).
Content varchar(200),
Comment time and date,
Commentator id char( 12),
Call number char( 15),
Foreign key reference book information (call number),
The foreign key (reviewer id) refers to the reader information (ID number)
)
Insert comment information value
('p 1239 1', 8.9,' very interesting',' 20 15-06-24',' 54 17070 1065438')
('p98523', 7.8,' Benefiting a lot',' 20 16-05-22',' 54 13070 10489',' B89490'),
('p94606', 6.8,' I don't know Orz at all',' 20 17-05-02',' 541607120165',' b/kloc-.
Create table key information (
ID char(8) primary key,
Category character (15),
Call number char( 15),
Foreign key reference book information (call number)
)
Insert keyword information value
('z64400',' computer',' b 12987'),
('z954 10',' physical',' b 16546'),
('z98500',' Postgraduate Entrance Examination',' b89490'),
('z64 165',' reasoning \ suspense',' b97894'),
('z69850',' Xian Xia',' b56400')
Use database
go to
Create trigger trigger 1
On information borrowing
After insertion
be like
Start?
Update reader information? -Change the cumulative number of books borrowed by readers.
Set cumulative loan = cumulative loan+1?
Information from reader, inserted
Reader information. ID number = inserted. Borrower's ID number
Update single information? -Change the status of the book
Set the information of the notebook. Book Status =' Lend'
Information of a single book inserted from
Insertion position. Book barcode number = single book information. Bar code number
Update single information
Set historical borrowing amount = historical borrowing amount+1? -Change the historical borrowing volume of books
Information of a single book inserted from
Insertion position. Book barcode number = single book information. Bar code number
end
Insert into the borrowing information value
(' j 13488 '，' 20 18-05-0 1 '，' 20 18-08-0 1 '，' t987628 '，' 54 14070 10 169 ')
Insert into the borrowing information value
(' j 149 10 '，' 20 16- 12- 1 1 '，' 20 17-0 1-25 '，' t9849 10 '，' 54 1607 120 165 ')
Insert into the borrowing information value
(' j97890 '，' 20 18-05- 14 '，' 20 18-06- 14 '，' t940566 '，' 54 1607 120 165 ')
go to
Create view_ 1 (book barcode, borrower's name, number, borrowing time and return time)
be like
Select the barcode number, name, ID number, borrowing time and return time of the book.
From borrowing information, reader information
Where to borrow information. Borrower ID number = reader information. Id number and return time > acquisition date ()
go to
Create view view_2 (book bar code, historical total borrowing volume)
be like
Select barcode number and historical borrowing amount.
From individual book information, book information
Among them, single information. Call number = book information. Call Number and Title =' Magic Night'
go to
Create view view_3 (category, book barcode, historical total borrowing volume)
be like
Select the category, barcode number and historical loan amount.
From single book information, book information, subject information
Wherein the keyword information. Call number = book information. Call number and book information. Call number = single information. Call Number and Category =' Postgraduate Entrance Examination'
go to
Create view view_4 (reader number, barcode, book name, expiration date)
be like
Select the borrower's ID number, book barcode number, book title and return time.
From single information, book information, borrowing information
Lend information. Book barcode number = single book information. Bar code number and single information. Call number = book information. Call number.
And the repayment time is later than the acquisition date () and the borrower's ID number is' 541607120165'
go to
Create view view_5 (reader number, book barcode, book name, borrowing date, return date)
be like
Select the borrower's ID number, book barcode number, book title, borrowing time and return time.
From single information, book information, borrowing information
Lend information. Book barcode number = single book information. Bar code number and single information. Call number = book information. Call number.
Borrower's ID number =' 541607120165'
go to
Create view view_6 (reader number, book title, comment time, comment content)
be like
Select reviewer id, title, comment time and content.
From borrowing information, single information, review information, book information
Borrow information there. Book barcode number = single book information. Bar code number and single information. Call number = comment information. Call number.
And comment information. Call number = book information. Call number and reviewer ID =' 541607120165'
go to
Create view view_7 (publisher name, book name, publishing time)
be like
Before selecting 100% news information. Publishing house, title, publication time.
From news information, book information
News information. Publishing House = Book Information. press
Sort by release time
-Execute
select * from view_ 1
select * from view_2
select * from view_3
select * from view_4
select * from view_5
select * from view_6
select * from view _ 7 order by publishing time ASC
Extended data:
Database model:
Object model
Hierarchical model (lightweight data access protocol)
Mesh model (big data storage)
relation model
Object-oriented model
Semi-structured model
Plane model (table model, usually a two-dimensional array in form. Such as table model data Excel)
structure
Database architecture can be roughly divided into three levels: inner layer, conceptual layer and outer layer.
Inner layer: closest to the actual storage, that is, the actual storage mode of related data.
Outer layer: the way closest to users, that is, individual users view data.
Conceptual layer: the indirect layer between the two.
Database index
Main entry: database index
The concept of data index has a long history. For example, there is a table of contents in the first few pages of a book, which is also a kind of index, but the classification is very wide, such as license plate, ID number, bar code and so on. , are all index numbers. When you see the numbers, you can see the clues. If we are looking for people, cars or things, we can quickly find the right people and things by providing relevant figures.
In addition, the index has a corresponding relationship with the field, and the index comes from the field. Among them, the field has a so-called key field, which is unique, that is, its value cannot be repeated and cannot be "null".
For example, when merging data, the index plays the role of directionally using the field data to be attached. Therefore, the index is unrepeatable and cannot be empty.
Database operations: transactions
Main entry: database transaction
A transaction is a user-defined sequence of database operations, which is an inseparable unit of work, either all completed or none completed. ?
The concurrency of transactions means that parallel operations of multiple transactions run alternately, and the concurrency of transactions may access and store incorrect data, destroying the isolation of transactions and the consistency of databases.
The data structure of grid data model The grid model is a set of basic hierarchical relationships that satisfy the following two conditions. 1. Multiple nodes without parent nodes are allowed; 2. A node can have multiple parent nodes. ?
Baidu encyclopedia-database