创建数据库db_085分②建表如下总分25分 表一T_book属性名 数据类型 可否为空 含 义 备注book_no varchar20 否 书号 主键book_name varchar100 否 书名 author varchar50 否 作者 price float 否 单价 qty int 否 库存册数 默认为‘0’loan_qty int 否 借阅次数 默认为‘0’ 表二T_card属性名

①创建数据库：

CREATE DATABASE db_08;

②创建表：

CREATE TABLE T_book ( book_no VARCHAR(20) NOT NULL PRIMARY KEY, book_name VARCHAR(100) NOT NULL, author VARCHAR(50) NOT NULL, price FLOAT NOT NULL, qty INT NOT NULL DEFAULT 0, loan_qty INT NOT NULL DEFAULT 0 );

CREATE TABLE T_card ( card_no VARCHAR(20) NOT NULL PRIMARY KEY, card_name VARCHAR(30) NOT NULL, adress VARCHAR(200) NOT NULL, mobile VARCHAR(11) );

CREATE TABLE T_borrow ( borrow_id INT NOT NULL PRIMARY KEY, book_no VARCHAR(20) NOT NULL, card_no VARCHAR(20) NOT NULL, borrow_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, return_date DATETIME, FOREIGN KEY (book_no) REFERENCES T_book (book_no), FOREIGN KEY (card_no) REFERENCES T_card (card_no) );

③添加数据：

INSERT INTO T_book (book_no, book_name, author, price, qty, loan_qty) VALUES ('10001', '安徒生童话', '安徒生', 102, 23, 109), ('10002', '稻草人', '安徒生', 45.8, 501, 78), ('10003', '丑小鸭', '格林', 88.2, 87, 250);

INSERT INTO T_card (card_no, card_name, adress, mobile) VALUES ('205', '刘大海', '湖南长沙', '13309097878'), ('206', '派大星', '湖南长沙', '15067237172');

INSERT INTO T_borrow (borrow_id, book_no, card_no, borrow_date, return_date) VALUES (1, '10001', '205', '2018-09-01', '2018-10-25'), (2, '10001', '206', '2018-09-05', '2018-09-17'), (3, '10003', '205', '2018-11-01', '2018-11-10');

④实现以下SQL语句：

a.查询T_book表中作者是‘安徒生’的所有图书

SELECT * FROM T_book WHERE author = '安徒生';

b.查询T_card表中手机号为‘13309097878’的借书人姓名，借书人地址

SELECT card_name, adress FROM T_card WHERE mobile = '13309097878';

c.查询T_borrow表中的书号为‘10001’的所有借书记录

SELECT * FROM T_borrow WHERE book_no = '10001';

d.查询T_book表中价格大于‘丑小鸭’价格的所有书籍名称、作者、单价

SELECT book_name, author, price FROM T_book WHERE price > (SELECT price FROM T_book WHERE book_name = '丑小鸭');

e.修改‘派大星’的借书人名字为‘派小星’

UPDATE T_card SET card_name = '派小星' WHERE card_name = '派大星';

f.查询T_borrow表中书名、借书人姓名、借书日期

SELECT T_book.book_name, T_card.card_name, T_borrow.borrow_date FROM T_borrow JOIN T_book ON T_borrow.book_no = T_book.book_no JOIN T_card ON T_borrow.card_no = T_card.card_no;

g.查询库存数量大于100的书名、价格、库存

SELECT book_name, price, qty FROM T_book WHERE qty > 100;

h.查询‘丑小鸭’的作者、借阅次数

SELECT author, loan_qty FROM T_book WHERE book_name = '丑小鸭'