数据库的索引

create DATABASE LittleLibrary;
use LittleLibrary;

-- 创建读者表
CREATE TABLE Readers (
  reader_id INT PRIMARY KEY,
  reader_name VARCHAR(50),
  gender VARCHAR(10),
  department VARCHAR(50),
  email VARCHAR(100),
	tel VARCHAR(11)
);

-- 插入读者数据
INSERT INTO Readers (reader_id, reader_name, gender, department, email,tel)
VALUES 
  (1, '张三', '男', '计算机科学与技术', 'zhangsan@example.com','13594427396'),
  (2, '李四', '女', '信息管理与信息系统', 'lisi@example.com','15552369452'),
  (3, '王五', '男', '软件工程', 'wangwu@example.com','12345678998'),
  (4, '赵六', '女', '电子工程', 'zhaoliu@example.com','98765432112'),
  (5, '钱七', '男', '网络工程', 'qianqi@example.com','14725836912');

-- 创建图书表
CREATE TABLE BookInfo (
  Book_id INT PRIMARY KEY,
  ISBN VARCHAR(20),
  Book_name VARCHAR(100),
  author VARCHAR(50),
  publisher VARCHAR(50),
  Book_type VARCHAR(50),
  price DECIMAL(8, 2),
  order_date DATE,
  Book_status VARCHAR(10)
);

-- 插入图书数据
INSERT INTO BookInfo (Book_id, ISBN, Book_name, author, publisher, Book_type, price, order_date, Book_status)
VALUES 
  (1, '978-7-302-33007-0', '数据库系统原理及应用', '袁丽娜', '人民邮电出版社', '专业基础', 59.00, '2021-01-01', '在库'),
  (2, '978-7-115-41235-5', '网站设计与Web前端应用开发技术', '张锦祥', '清华大学出版社', '编程语言', 69.00, '2021-01-01', '借出'),
  (3, '978-7-302-33007-1', '计算机科学导论', '罗琼', '北京邮电大学出版社', '专业基础', 89.00, '2021-01-02', '在库'),
  (4, '978-7-03-032711-6', '大数据技术实战教程', '袁丽娜', '大连理工大学出版社', '实践类', 79.00, '2021-01-02', '借出'),
  (5, '978-7-04-047815-2', '数据结构', '严蔚敏', '人民邮电出版社', '编程语言', 99.00, '2021-01-03', '在库'),
	(6, '978-7-04-047915-2', '数据仓库与数据挖掘实践', '李春葆', '电子工业出版社', '实践类', 99.00, '2021-01-03', '在库'),
  (7, '978-7-110-41235-5', 'Java程序设计', '李晶晶', '北京理工大学出版社', '编程语言', 99.00, '2021-01-01', '借出');

-- 创建借阅记录表
CREATE TABLE Borrow_Records (
  Record_id INT PRIMARY KEY,
  Reader_id INT,
  Book_id INT,
  Borrow_date DATE,
  return_date DATE,
  FOREIGN KEY (Reader_id) REFERENCES Readers(Reader_id),
  FOREIGN KEY (Book_id) REFERENCES BookInfo(Book_id)
);

-- 插入借阅记录数据
INSERT INTO borrow_records (record_id, reader_id, book_id, borrow_date, return_date)
VALUES 
  (1, 1, 2, '2021-01-05', '2021-01-10'),
  (2, 1, 3, '2021-02-15', '2021-02-20'),
  (3, 2, 1, '2021-03-10', '2021-03-15'),
  (4, 3, 4, '2021-04-20', NULL),
  (5, 4, 2, '2021-05-01', '2021-05-05'),
  (6, 5, 3, '2021-06-10', NULL),
  (7, 1, 4, '2021-07-15', '2021-07-20'),
  (8, 3, 2, '2021-08-01', NULL),
  (9, 4, 3, '2021-09-10', NULL),
  (10, 5, 1, '2021-10-05', NULL);



-- 为 Publisher 字段创建一个普通索引
CREATE INDEX idx_publisher ON BookInfo (Publisher);



-- 查看查询语句 select Bookname, author, price from BookInfo where Publisher='大连理工大学出版社' 的查询计划
EXPLAIN select Book_name, author, price from BookInfo where Publisher='大连理工大学出版社';


-- 删除第一题中创建的普通索引
DROP INDEX idx_publisher ON BookInfo;