-- 查看当前系统中有什么数据库
SHOW DATABASES;

-- 创建数据库
CREATE DATABASE MYSQLDATA;

-- 选择数据库
USE MYSQLDATA;

-- 查看当前的表情况
SHOW TABLES;

-- 创建MYTABLE表
CREATE TABLE MYTABLE (name VARCHAR(20), sex CHAR(1));

-- 描述表结构
DESCRIBE MYTABLE;

-- 修改字段数据类型
ALTER TABLE MYTABLE MODIFY sex int;

-- 修改字段名
ALTER TABLE MYTABLE CHANGE sex thissex char(1);

-- 增加字段
ALTER TABLE MYTABLE ADD num INT(8) PRIMARY KEY FIRST;

-- 调整字段排列
ALTER TABLE MYTABLE MODIFY name varchar(20) AFTER thissex;

-- 删除字段
ALTER TABLE MYTABLE DROP thissex;

-- 删除表
drop table mytable;

-- 创建EBASE数据库
CREATE DATABASE EBASE;

-- 使用ebase数据库
USE EBASE;

-- 创建Product表
CREATE TABLE Product (
    maker VARCHAR(40),
    model VARCHAR(40) PRIMARY KEY,
    type VARCHAR(10)
);

-- 创建Laptop表
CREATE TABLE Laptop (
    model VARCHAR(40) PRIMARY KEY,
    speed FLOAT,
    ram INT,
    hd FLOAT,
    screen FLOAT,
    price FLOAT,
    od VARCHAR(10),
    FOREIGN KEY (model) REFERENCES Product(model)
);

-- 填充Product表
INSERT INTO Product VALUES
    ('Apple', 'MacBook Air', 'laptop'),
    ('Apple', 'MacBook Pro', 'laptop'),
    ('Apple', 'iPad Pro', 'laptop'),
    ('Lenovo', 'ThinkPad X1', 'laptop'),
    ('Lenovo', 'Yoga Slim 7', 'laptop'),
    ('Lenovo', 'IdeaPad Flex 5', 'laptop'),
    ('Dell', 'XPS 13', 'laptop'),
    ('Dell', 'Inspiron 15', 'laptop'),
    ('Dell', 'Latitude 14', 'laptop'),
    ('HP', 'Spectre x360', 'laptop'),
    ('HP', 'Pavilion 14', 'laptop'),
    ('HP', 'EliteBook 840', 'laptop'),
    ('HP', 'HP LaserJet Pro', 'printer'),
    ('Canon', 'Canon PIXMA TS8320', 'printer'),
    ('Epson', 'Epson EcoTank ET-4760', 'printer'),
    ('Brother', 'Brother HL-L2350DW', 'printer'),
    ('Samsung', 'Samsung Xpress M2020W', 'printer'),
    ('HP', 'HP OfficeJet Pro 9015', 'printer');

-- 填充Laptop表
INSERT INTO Laptop VALUES
    ('MacBook Air', 1.6, 8, 256, 13.3, 999, 'none'),
    ('MacBook Pro', 2.3, 16, 512, 16, 2399, 'none'),
    ('iPad Pro', 2.5, 6, 128, 12.9, 999, 'none'),
    ('ThinkPad X1', 2.4, 16, 512, 14, 1499, 'none'),
    ('Yoga Slim 7', 2.8, 8, 256, 14, 799, 'none'),
    ('IdeaPad Flex 5', 2.1, 8, 256, 14, 599, 'none'),
    ('XPS 13', 2.6, 8, 256, 13.4, 999, 'none'),
    ('Inspiron 15', 2.1, 8, 512, 15.6, 699, 'DVD'),
    ('Latitude 14', 2.3, 8, 256, 14, 899, 'DVD'),
    ('Spectre x360', 2.4, 8, 256, 13.3, 1099, 'none'),
    ('Pavilion 14', 2.1, 8, 256, 14, 599, 'DVD'),
    ('EliteBook 840', 2.3, 8, 256, 14, 999, 'DVD');

-- 查询
-- 查询所有的产品型号和类型
SELECT model, type FROM Product;

-- 查询所有的笔记本电脑的型号、制造商和价格
SELECT L.model, P.maker, L.price 
FROM Laptop L, Product P 
WHERE L.model = P.model;

-- 查询所有没有光驱的笔记本电脑的型号和屏幕尺寸
SELECT model, screen 
FROM Laptop 
WHERE od = 'none';

-- 查询所有价格高于1000的笔记本电脑的型号、速度和内存大小
SELECT model, speed, ram 
FROM Laptop WHERE price > 1000;

-- 创建Printer表
CREATE TABLE Printer (
    model VARCHAR(40) PRIMARY KEY,
    color BOOLEAN,
    type VARCHAR(10),
    price FLOAT,
    FOREIGN KEY (model) REFERENCES Product(model)
);

-- 填充Printer表
-- 用文本方式将数据装入数据库表中
-- 先给出文本文件的内容，文件名为printer.txt，每行代表一条记录，字段之间用逗号分隔
-- 文件内容如下：
-- HP LaserJet Pro,TRUE,laser,199
-- 然后使用LOAD DATA命令将文本文件的数据装入Printer表中
LOAD DATA INFILE 'D:/printer.txt' INTO TABLE Printer FIELDS TERMINATED BY ',';

-- 使用所有字段增加记录
INSERT INTO Printer VALUES
    ('Canon PIXMA TS8320', TRUE, 'ink-jet', 179),
    ('Epson EcoTank ET-4760', TRUE, 'ink-jet', 499),
    ('Brother HL-L2350DW', FALSE, 'laser', 119),
    ('Samsung Xpress M2020W', FALSE, 'laser', 99),
    ('HP OfficeJet Pro 9015', TRUE, 'ink-jet', 229);

-- 使用同时增加多条数据
INSERT INTO Printer (model, color, type, price)
SELECT model, TRUE, 'ink-jet', 299 FROM Product WHERE maker = 'Apple';

-- 使用特定字段增加记录
INSERT INTO Printer (model, color, type)
VALUES
    ('Dell Color Laser Printer', TRUE, 'laser'),
    ('Dell Inkjet Printer', TRUE, 'ink-jet');

-- 使用查询结果插入到表中
INSERT INTO Printer (model, color, type, price)
SELECT model, FALSE, 'laser', 149 FROM Product WHERE maker = 'Lenovo';

-- 修改表结构
-- 修改字段数据类型，将price字段的类型从FLOAT改为DECIMAL(10,2)
ALTER TABLE Printer MODIFY price DECIMAL(10,2);

-- 修改字段名，将type字段改为print_type
ALTER TABLE Printer RENAME COLUMN type TO print_type;

-- 增加字段，增加一个名为resolution的字段，类型为VARCHAR(20)，放在print_type字段后面
ALTER TABLE Printer ADD resolution VARCHAR(20) AFTER print_type;

-- 删除字段，删除color字段
ALTER TABLE Printer DROP color;

-- 修改字段排列位置，将resolution字段移到model字段后面
ALTER TABLE Printer MODIFY resolution VARCHAR(20) AFTER model;

-- 删除表，删除Printer表
DROP TABLE Printer;

-- 导入.sql命令，假设有一个名为ebase.sql的文件，包含了创建数据库、填入数据等操作的代码
SOURCE D:/ebase.sql;

-- 创建EPBASE数据库
CREATE DATABASE EPBASE;

-- 选择数据库
USE EPBASE;

-- 创建Product表
CREATE TABLE Product (
    maker VARCHAR(1) NOT NULL,
    model VARCHAR(4) NOT NULL,
    type VARCHAR(20) NOT NULL,
    PRIMARY KEY (model)
);

-- 插入Product表的数据
INSERT INTO Product (maker, model, type) VALUES
('A', '1001', 'pc'),
('A', '1002', 'pc'),
('A', '1003', 'pc'),
('A', '2004', 'laptop'),
('A', '2005', 'laptop'),
('A', '2006', 'laptop'),
('B', '1004', 'pc'),
('B', '1005', 'pc'),
('B', '1006', 'pc'),
('B', '2007', 'laptop'),
('C', '1007', 'pc'),
('D', '1008', 'pc'),
('D', '1009', 'pc'),
('D', '1010', 'pc'),
('D', '3004', 'printer'),
('D', '3005', 'printer'),
('E', '1011', 'pc'),
('E', '1012', 'pc'),
('E', '1013', 'pc'),
('E', '2001', 'laptop'),
('E', '2002', 'laptop'),
('E', '2003', 'laptop'),
('E', '3001', 'printer'),
('E', '3002', 'printer'),
('E', '3003', 'printer'),
('F', '2008', 'laptop'),
('F', '2009', 'laptop'),
('G', '2010', 'laptop'),
('H', '3006', 'printer'),
('H', '3007', 'printer');

-- 创建PC表
CREATE TABLE PC (
    model VARCHAR(4) NOT NULL,
    speed DECIMAL(5,2) NOT NULL,
    ram INT NOT NULL,
    hd DECIMAL(5,1) NOT NULL,
    price DECIMAL(12,2) NOT NULL,
    PRIMARY KEY (model),
    FOREIGN KEY (model) REFERENCES Product(model)
);

-- 插入PC表的数据
INSERT INTO PC (model, speed, ram, hd, price) VALUES
('1001', 2.66, 1024, 250, 2114),
('1002', 2.1, 512, 250, 995),
('1003', 1.42, 512, 80, 478),
('1004', 2.8, 1024, 250, 649),
('1005', 3.2, 512, 250, 630),
('1006', 3.2, 1024, 320, 1049),
('1007', 2.2, 1024, 200, 510),
('1008', 2.2, 2048, 250, 770),
('1009', 2, 1024, 250, 650),
('1010', 2.8, 2048, 300, 770),
('1011', 1.86, 2048, 160, 959),
('1012', 2.8, 1024, 160, 649),
('1013', 3.06, 512, 80, 529);

-- 创建Laptop表
CREATE TABLE Laptop (
    model VARCHAR(4) NOT NULL,
    speed DECIMAL(5,2) NOT NULL,
    ram INT NOT NULL,
    hd DECIMAL(5,1) NOT NULL,
    screen DECIMAL(4,1) NOT NULL,
    price DECIMAL(12,2) NOT NULL,
    PRIMARY KEY (model),
    FOREIGN KEY (model) REFERENCES Product(model)
);

-- 插入Laptop表的数据
INSERT INTO Laptop (model, speed, ram, hd, screen, price) VALUES
('2001', 2, 2048, 240, 20.1, 3673),
('2002', 1.73, 1024, 80, 17, 949),
('2003', 1.8, 512, 60, 15.4, 549),
('2004', 2, 512, 60, 13.3, 1150),
('2005', 2.16, 1024, 120, 17, 2500),
('2006', 2, 2048, 80, 15.4, 1700),
('2007', 1.83, 1024, 120, 13.3, 1429),
('2008', 1.6, 1024, 0, 15.4, 900),
('2009', 1.6, 512, 80, 14.1, 680),
('2010', 2, 2048, 160, 15.4, 2300);

-- 创建Printer表
CREATE TABLE Printer (
    model VARCHAR(4) NOT NULL,
    color CHAR(1) NOT NULL,
    type VARCHAR(10) NOT NULL,
    price DECIMAL(12,2) NOT NULL,
    PRIMARY KEY (model),
    FOREIGN KEY (model) REFERENCES Product(model)
);

-- 插入Printer表的数据
INSERT INTO Printer (model, color, type, price) VALUES
('3001', TRUE, 'ink-jet', 99),
('3002', FALSE, 'laser', 239),
('3003', TRUE, 'laser', 899),
('3004', TRUE, 'ink-jet', 120),
('3005', FALSE, 'laser', 120),
('3006', TRUE, 'ink-jet', 100),
('3007', TRUE, 'laser', 200);

-- 表的数据更新
-- 将PC表中model为1001的price更新为1999
UPDATE PC SET price = 1999 WHERE model = '1001';

-- 表的数据插入
-- 在Product表中插入一条新的记录，model为2011，maker为G，type为laptop
INSERT INTO Product (model, maker, type) VALUES
('2011', 'G', 'laptop');

-- 在Laptop表中插入一条新的记录，model为2011，speed为2.5，ram为4096，hd为500，screen为15.6，price为1299
INSERT INTO Laptop (model, speed, ram, hd, screen, price) VALUES
('2011', 2.5, 4096, 500, 15.6, 1299);

-- 表的数据删除
-- 从Laptop表中删除model为2011的记录
DELETE FROM Laptop WHERE model = '2011';

-- 列出表的所有字段
-- 列出Product表的所有字段
SELECT * FROM Product;

-- 列出表的某些字段
-- 列出PC表的model，speed和price字段
SELECT model, speed, price FROM PC;

-- 查询指定记录
-- 查询Product表中maker为A的记录
SELECT * FROM Product WHERE maker = 'A';

-- 带BETWEEN AND的范围查询
-- 查询pc表中speed在2.5和3之间的记录
SELECT * FROM pc WHERE speed BETWEEN 2.5 AND 3;

-- 带LIKE的字符匹配查询
-- 查询Product表中type以p开头的记录
SELECT * FROM Product WHERE type LIKE 'p%';

-- 查询空值
-- 查询Printer表中price为空的记录
SELECT * FROM Printer WHERE price IS NULL;

-- 带AND的多条件查询
-- 查询PC表中speed大于2.5且ram小于1024的记录
SELECT * FROM PC WHERE speed > 2.5 AND ram < 1024;

-- 带OR的多条件查询
-- 查询PC表中speed等于2.00或2.20的记录
SELECT * FROM PC WHERE speed = 2.00 OR speed = 2.20;

-- 查询结果不重复
-- 查询Product表中不重复的maker
SELECT DISTINCT maker FROM Product;

-- 排序
-- 查询PC表中的所有记录，并按price降序排序
SELECT * FROM PC ORDER BY price DESC;

-- GROUP BY with 聚集函数
-- 查询Product表中每种type的数量
SELECT type, COUNT(*) AS count FROM Product GROUP BY type;

-- HAVING
-- 查询Product表中每种type的数量，并只显示数量大于5的记录
SELECT type, COUNT(*) AS count FROM Product GROUP BY type HAVING count > 5;

-- 使用到五个聚集函数的查询
-- 查询PC表中speed的最大值，最小值，平均值，总和和数量
SELECT MAX(speed) AS max_speed, 
    MIN(speed) AS min_speed, 
    AVG(speed) AS avg_speed, 
    SUM(speed) AS sum_speed, 
    COUNT(speed) AS count_speed 
FROM 
    PC;

-- 基于内连接的多表查询
-- 查询Product表和PC表的内连接，显示maker，model，speed和price
SELECT Product.maker, 
    Product.model, 
    PC.speed, 
    PC.price 
FROM Product 
    INNER JOIN PC ON Product.model = PC.model;

-- 基于左外连接的多表查询
-- 查询Product表和Laptop表的左外连接，显示maker，model，speed和price
SELECT 
    Product.maker, 
    Product.model, 
    Laptop.speed, 
    Laptop.price 
FROM Product 
    LEFT OUTER JOIN Laptop ON Product.model = Laptop.model;

-- 基于右外连接的多表查询
-- 查询Product表和pc表的右外连接，显示maker，model，ram和price
SELECT Product.maker, 
    Product.model, Pc.ram, Pc.price 
FROM Product
    RIGHT OUTER JOIN Pc ON Product.model = Pc.model;

-- 子查询
-- 查询PC表中price高于平均price的记录
SELECT * FROM PC WHERE price > (SELECT AVG(price) FROM PC);

-- a) 找出所有价格低于$1000的个人计算机的型号、速度和硬盘大小
SELECT model, speed, hd FROM PC WHERE price < 1000;

-- b) 要求同(a)，但要将列speed重命名为gigahertz，并将列hd重命名为gigabytes
SELECT model, speed AS gigahertz, hd AS gigabytes FROM PC WHERE price < 1000;

-- c) 找出所有打印机制造厂商
SELECT DISTINCT maker FROM Product WHERE type = 'printer';

-- d) 找出价格高于$1500的笔记本电脑的型号、内存大小和屏幕尺寸
SELECT model, ram, screen FROM Laptop WHERE price > 1500;

-- e)找出关系Printer中所有彩色打印机元组。注意属性color是一个布尔类型。
SELECT * FROM Printer WHERE color = TRUE;

-- f) 找出速度为3.2且价格低于$2000的个人计算机的型号和硬盘大小。
SELECT model, hd FROM PC WHERE speed = 3.2 AND price < 2000;

-- a) 查询硬盘容量至少30G的笔记本电脑制造商及该电脑的速度
SELECT maker, speed FROM Product INNER JOIN Laptop ON Product.model = Laptop.model WHERE hd >= 30;

-- b) 查询制造商B生产的任意类型的所有产品的型号和价格。
SELECT model, price
from
    (
        select model, price 
            from pc
        union 
        select model, price 
            from laptop
    union
    select model, price 
        from printer
    ) a
where model in (
    select model from product where maker='B')

-- c)查询只卖笔记本电脑不卖PC的厂商。
SELECT maker 
FROM Product 
WHERE type = 'laptop' 
    AND maker NOT IN (SELECT maker FROM Product WHERE type = 'pc');

-- d)查询出现在两种或两种以 上PC中的硬盘的大小。
SELECT hd FROM PC GROUP BY hd HAVING COUNT(*) >= 2;

-- e)查询每对具有相同速度和RAM的PC的型号。每一对只能列出一次，例如，若 (i,j) 已被列出则 (i.i) 就不能再被列出。
SELECT 
    p1.model, 
    p2.model 
FROM 
    PC p1, 
    PC p2 
    WHERE p1.speed = p2.speed 
        AND p1.ram = p2.ram 
        AND p1.model < p2.model;

-- f)查询生产至少两种速度至少为3.0的电脑 (PC或笔记本电脑) 的厂商。
SELECT maker 
FROM
    (
        SELECT 
            a.maker,
            b.speed 
        FROM product a 
            JOIN pc b ON a.model=b.model
        GROUP BY b.speed,a.maker
    )a
GROUP BY 
    maker 
HAVING 
    COUNT(*)>=2

-- a)找出速度在3.0以上的PC制造商。
-- 方法一：使用IN运算符
SELECT DISTINCT maker 
FROM Product 
WHERE model IN (SELECT model FROM PC WHERE speed > 3);

-- 方法二：使用EXISTS运算符
SELECT DISTINCT maker 
FROM Product p 
WHERE EXISTS (SELECT * FROM PC WHERE speed > 3 AND model = p.model);

-- b) 找出价格最高的打印机。
-- 方法一：使用MAX函数
SELECT * FROM 
Printer 
WHERE price = (SELECT MAX(price) FROM Printer);

-- 方法二：使用ALL运算符
SELECT * 
FROM Printer 
WHERE price >= ALL (SELECT price FROM Printer);

-- c) 找出速度比任何一台PC都慢的笔记本电脑。
-- 方法一：使用MIN函数
SELECT * 
FROM Laptop 
WHERE speed < (SELECT MIN(speed) FROM PC);

-- 方法二：使用ANY运算符
SELECT * FROM Laptop WHERE speed < ANY (SELECT speed FROM PC);

-- d)找出价格最高的产品 (PC、笔记本电脑或打印机)的型号
-- 方法一：使用UNION和MAX函数
-- 使用UNION和MAX函数
SELECT
    model
FROM
    (
        SELECT model, price
        FROM PC
        UNION
        SELECT model, price
        FROM Laptop
        UNION
        SELECT model, price
        FROM Printer
    ) AS t
WHERE
    price = (
        SELECT
            MAX(price)
        FROM
            (
                SELECT price
                FROM PC
                UNION
                SELECT price
                FROM Laptop
                UNION
                SELECT price
                FROM Printer
            ) AS p
    );



-- 方法二：使用UNION和ALL运算符
SELECT model 
FROM 
    (
        SELECT model, price 
        FROM PC 
        UNION SELECT model, price 
        FROM Laptop 
        UNION SELECT model, price 
        FROM Printer
    ) AS t 
WHERE price >= ALL (SELECT price FROM t);

-- e) 找出价格最低的彩色打印机的制造商。
-- 方法一：使用JOIN和MIN函数
SELECT maker 
FROM Product JOIN Printer ON Product.model = Printer.model 
WHERE color = TRUE 
    AND price = (
        SELECT MIN(price) 
        FROM Printer 
        WHERE color = TRUE
    );

-- 方法二：使用IN运算符
SELECT maker 
FROM product,printer
WHERE product.model=printer.model 
AND color='1' 
AND price in (
    select min(price)
    from product,printer
    where product.model=printer.model
    and color='1'
);

-- f)找出RAM容量最小而PC中速度最快者的制造商。
-- 方法一：使用JOIN和MIN/MAX函数
SELECT maker 
FROM Product JOIN PC ON Product.model = PC.model 
WHERE ram = (SELECT MIN(ram) FROM PC) 
    AND speed = (SELECT MAX(speed) FROM PC);

-- 方法二：使用IN运算符
SELECT DISTINCT maker
FROM product
WHERE model IN 
		(
		SELECT model 
		FROM pc 
		WHERE speed>=ALL(
			SELECT speed
			 FROM pc
			WHERE ram <=ALL (
			SELECT ram 
			FROM pc
			)
		)
		AND
		ram <= ALL(
			SELECT ram 
			FROM pc
		)
);

-- a) 查询PC的平均速度。
SELECT AVG(speed) AS avg_speed FROM PC;

-- b) 查询价格高于$1000的笔记本电脑的平均速度。
SELECT AVG(speed) AS avg_speed FROM Laptop WHERE price > 1000;

-- c) 查询厂商“A”生产的PC的平均价格。
SELECT AVG(price) AS avg_price FROM Product JOIN PC ON Product.model = PC.model WHERE maker = 'A';

-- d)查询厂商“D”生产的PC和笔记本电脑的平均价格。
SELECT round(AVG(price)) AS avg_price
    FROM (
    SELECT price
        FROM product,pc
        WHERE product.model = pc.model
        AND maker = 'D'
    UNION ALL
    SELECT price
        FROM product, laptop
        WHERE product.model = laptop.model   
        AND maker = 'D'
    ) temp;

-- e) 查询每种不同速度的PC的平均价格。
SELECT speed, AVG(price) AS avg_price 
FROM PC 
GROUP BY speed;

-- f) 查询每家厂商生产的笔记本电脑的屏幕尺寸的平均值。
SELECT maker, AVG(screen) AS avg_screen 
FROM Product JOIN Laptop ON Product.model = Laptop.model 
GROUP BY maker;

-- g) 查询至少生产三种不同型号PC的制造商。
SELECT maker 
FROM Product 
WHERE type = 'pc' 
GROUP BY maker 
    HAVING COUNT(DISTINCT model) >= 3;

-- h) 查询每个销售PC的广商的PC的最高价格。
SELECT maker, MAX(price) AS max_price 
FROM Product JOIN PC ON Product.model = PC.model 
GROUP BY maker;

-- i) 查询每种高于2.0速度的PC的平均价格。
SELECT speed, AVG(price) AS avg_price 
FROM PC 
WHERE speed > 2 
GROUP BY speed;

-- j) 查询所有生产打印机的厂商生产的PC的硬盘容量的平均大小。
SELECT maker, AVG(hd) AS avg_hd 
FROM Product JOIN PC ON Product.model = PC.model 
    WHERE maker IN (SELECT maker FROM Product WHERE type = 'printer') 
    GROUP BY maker;

-- 创建数据库
CREATE DATABASE LIBRARY;

-- 创建图书表
CREATE TABLE Book (
    Bno VARCHAR(10) PRIMARY KEY, -- 图书编号
    Bname VARCHAR(50) NOT NULL, -- 书名
    Author VARCHAR(20) NOT NULL, -- 作者
    Publisher VARCHAR(50) NOT NULL, -- 出版社
    PubDate DATE NOT NULL, -- 出版日期
    Price DECIMAL(10, 2) NOT NULL, -- 价格
    Stock INT NOT NULL -- 库存量
);

-- 创建读者表
CREATE TABLE Reader (
    Rno VARCHAR(10) PRIMARY KEY, -- 读者编号
    Rname VARCHAR(20) NOT NULL, -- 姓名
    Gender VARCHAR(10) NOT NULL, -- 性别
    Age INT NOT NULL, -- 年龄
    Phone VARCHAR(20) NOT NULL, -- 电话
    BorrowTimes INT NOT NULL -- 借阅次数
);

-- 创建借阅表
CREATE TABLE Borrow (
    Lno VARCHAR(10) PRIMARY KEY, -- 借阅编号
    Bno VARCHAR(10) NOT NULL, -- 图书编号
    Rno VARCHAR(10) NOT NULL, -- 读者编号
    BorrowDate DATE NOT NULL, -- 借阅日期
    ReturnDate DATE NOT NULL, -- 归还日期
    Fine DECIMAL(10, 2) NOT NULL, -- 逾期罚款
    FOREIGN KEY (Bno) REFERENCES Book (Bno), -- 外键约束
    FOREIGN KEY (Rno) REFERENCES Reader (Rno) -- 外键约束
);

-- 插入图书数据
INSERT INTO Book VALUES
('B001', '数据库系统概念', 'Abraham Silberschatz', '机械工业出版社', '2019-01-01', 79.00, 10),
('B002', '计算机网络', 'Andrew S. Tanenbaum', '机械工业出版社', '2018-01-01', 89.00, 8),
('B003', '数据结构与算法分析', 'Mark Allen Weiss', '机械工业出版社', '2017-01-01', 69.00, 6),
('B004', '编译原理', 'Alfred V. Aho', '机械工业出版社', '2016-01-01', 99.00, 4);

-- 插入读者数据
INSERT INTO Reader VALUES
('R001', '张三', '男', 20, '13800000001', 0),
('R002', '李四', '女', 21, '13800000002', 0),
('R003', '王五', '男', 22, '13800000003', 0),
('R004', '赵六', '女', 23, '13800000004', 0);

-- 插入借阅数据
INSERT INTO Borrow VALUES
('L001', 'B001', 'R001', '2023-11-01', '2023-11-15', 0.00),
('L002', 'B002', 'R002', '2023-11-02', '2023-11-16', 0.00),
('L003', 'B003', 'R003', '2023-11-03', '2023-11-17', 0.00),
('L004', 'B004', 'R004', '2023-11-04', '2023-11-18', 0.00),
('L005', 'B001', 'R002', '2023-11-05', '2023-11-19', 0.00);

-- 查询图书表中所有图书的信息
SELECT * FROM Book;

-- 查询读者表中年龄大于等于22的读者的姓名和电话
SELECT Rname, Phone FROM Reader WHERE Age >= 22;

-- 查询借阅表中逾期罚款大于0的借阅记录的编号和罚款金额
SELECT Lno, Fine FROM Borrow WHERE Fine > 0;

-- 查询图书表和借阅表连接后，借阅日期在2023年11月2日之后的图书的书名和借阅日期
SELECT Bname, BorrowDate FROM Book JOIN Borrow ON Book.Bno = Borrow.Bno WHERE BorrowDate > '2023-11-2';

-- 查询图书表、读者表和借阅表连接后，读者姓名为李四的图书的书名和作者
SELECT Bname, Author FROM Book JOIN Borrow ON Book.Bno = Borrow.Bno JOIN Reader ON Borrow.Rno = Reader.Rno WHERE Rname = '李四