根据《图书信息管理系统》需求完成以下数据库创建、数据表创建和数据操作任务。①创建数据库db_085分②建表如下总分25分表一T_book属性名数据类型可否为空含义备注book_novarchar20否书号主键book_namevarchar100否书名authorvarchar50否作者pricefloat否单价qtyint否库存册数默认为‘0’loan_qtyint否借阅次数默认为‘0’表二T_

①创建数据库db_08

CREATE DATABASE db_08;

②建表如下

--表（一）T_book 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 );

--表（二）T_card 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) );

--表（三）T_borrow 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 );

③添加数据如下

--表（一）T_book INSERT INTO T_book (book_no, book_name, author, price, qty, loan_qty) VALUES ('8010001', '安徒生童话', '安徒生', 102.23, 109, 1), ('10002', '稻草人', '安徒生', 45.85, 1, 78), ('10003', '丑小鸭', '格林', 88.28, 7, 250);

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

--表（三）T_borrow INSERT INTO T_borrow (borrow_id, book_no, card_no, borrow_date, return_date) VALUES (110001, '205', '8010001', '2018-09-01', '2018-10-25'), (210002, '206', '10062', '2018-09-05', '2018-09-17'), (310003, '205', '10032', '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 = '8010001';

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 = '丑小鸭'