图书管理数据库 数据表创建和数据操作的sql文件
时间: 2024-10-10 14:17:12 浏览: 33
图书管理系统(Library Management System)的数据库通常包含几个关键的数据表,例如:
1. **图书表(Books Table)**: 可能包括字段如ID、书名(Title)、作者(Author)、出版年份(Publication Year)、ISBN号(ISBN)、类别(Genre),甚至还有库存信息(Stock Quantity)。
```sql
CREATE TABLE Books (
ID INT PRIMARY KEY,
Title VARCHAR(255) NOT NULL,
Author VARCHAR(100),
PublicationYear INT,
ISBN VARCHAR(13) UNIQUE,
Genre VARCHAR(50),
StockQuantity INT
);
```
2. **借阅者表(Borrowers Table)**: 包含用户ID、姓名(Name)、联系方式(Contact Information)等信息。
```sql
CREATE TABLE Borrowers (
UserID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Contact VARCHAR(50)
);
```
3. **借阅记录表(Loans Table)**: 记录书籍被借出的情况,可能有借书日期(LoanDate)、归还日期(ReturnDate)、以及借阅者的ID。
```sql
CREATE TABLE Loans (
LoanID INT AUTO_INCREMENT PRIMARY KEY,
BookID INT,
UserID INT,
LoanDate DATE,
ReturnDate DATE,
FOREIGN KEY (BookID) REFERENCES Books(ID),
FOREIGN KEY (UserID) REFERENCES Borrowers(UserID)
);
```
数据操作的SQL语句示例:
- 插入数据:
```sql
INSERT INTO Books (Title, Author, PublicationYear, ISBN, Genre, StockQuantity)
VALUES ('Python编程入门', '张三', 2021, '978-7-121-46605-6', '编程', 10);
INSERT INTO Borrowers (Name, Contact)
VALUES ('李四', '13912345678');
```
- 查询数据:
```sql
SELECT * FROM Books WHERE Genre = '编程';
SELECT * FROM Loans WHERE ReturnDate IS NULL;
```
- 更新数据:
```sql
UPDATE Books SET StockQuantity = StockQuantity - 1 WHERE ISBN = '978-7-121-46605-6';
UPDATE Loans SET ReturnDate = CURDATE() WHERE LoanID = 1;
```