教材订单管理系统数据库设计及查询练习

一、数据库表设计

表（一）T_book

表（二）T_Order

二、数据示例

表（一）T_book

表（二）T_Order

三、SQL 查询练习

A.查询T_book表中作者是‘高永彬’的教材名称、ISBN号

SELECT Book_name, Book_isbn
FROM T_book
WHERE Book_author = '高永彬';

B.查询‘水利水电出版社’所有的教材信息

SELECT *
FROM T_book
WHERE Book_publisher = '水利水电出版社';

C.查询价格在50到80之间的教材名称、价格

SELECT Book_name, Book_price
FROM T_book
WHERE Book_price >= 50 AND Book_price <= 80;

D.查询T_Order表中订单时间为‘2021-06-24’的订单信息

SELECT *
FROM T_Order
WHERE Order_time = '2021-06-24';

E.修改T_Order表第4个订单的订单数量为126

UPDATE T_Order
SET OrderDet_num = 126
WHERE Order_id = 4;

F.查询T_Order表中id为‘102’的教材被征订的总数量

SELECT SUM(OrderDet_num)
FROM T_Order
WHERE Book_id = 102;

G.查询ISBN号为‘9787040395853’的教材信息

SELECT *
FROM T_book
WHERE Book_isbn = '9787040395853';

H.统计订单表中订单id，教材名称，订购总数

SELECT Order_id, T_book.Book_name, SUM(OrderDet_num) AS total_quantity
FROM T_Order
INNER JOIN T_book ON T_Order.Book_id = T_book.Book_id
GROUP BY Order_id, T_book.Book_name;