数据库创建、数据表创建和数据操作任务 - 教材选录系统

创建数据库db_10:\nCREATE DATABASE db_10;\n\n创建表T_book:\nCREATE TABLE T_book (\n Book_id INT NOT NULL PRIMARY KEY,\n Book_name VARCHAR(20) NOT NULL,\n Book_isbn VARCHAR(20) NOT NULL,\n Book_author VARCHAR(10) NOT NULL,\n Book_publisher VARCHAR(20) NOT NULL,\n Book_price FLOAT NOT NULL\n);\n\n创建表T_Order:\nCREATE TABLE T_Order (\n Order_id INT NOT NULL PRIMARY KEY,\n Book_id INT NOT NULL,\n OrderDet_num INT NOT NULL,\n Order_time DATETIME DEFAULT CURRENT_TIMESTAMP,\n FOREIGN KEY (Book_id) REFERENCES T_book(Book_id)\n);\n\n插入数据到表T_book:\nINSERT INTO T_book (Book_id, Book_name, Book_isbn, Book_author, Book_publisher, Book_price)\nVALUES (101, '《Hadoop大数据分析》', '9787113259198', '高永彬', '中国铁道出版社', 108);\n\nINSERT INTO T_book (Book_id, Book_name, Book_isbn, Book_author, Book_publisher, Book_price)\nVALUES (102, '《Python程序设计》', '9787302515326', '夏敏捷', '清华大学出版社', 56);\n\nINSERT INTO T_book (Book_id, Book_name, Book_isbn, Book_author, Book_publisher, Book_price)\nVALUES (103, '《Html5+CSS3从入门到精通》', '9787517054160', '未来科技', '水利水电出版社', 70);\n\nINSERT INTO T_book (Book_id, Book_name, Book_isbn, Book_author, Book_publisher, Book_price)\nVALUES (104, '《Linux管理与服务》', '9787040395853', '钱峰', '水利水电出版社', 48);\n\n插入数据到表T_Order:\nINSERT INTO T_Order (Order_id, Book_id, OrderDet_num, Order_time)\nVALUES (1, 102, 127, '2021-06-24');\n\nINSERT INTO T_Order (Order_id, Book_id, OrderDet_num, Order_time)\nVALUES (2, 101, 127, '2021-06-24');\n\nINSERT INTO T_Order (Order_id, Book_id, OrderDet_num, Order_time)\nVALUES (3, 104, 127, '2021-06-24');\n\nINSERT INTO T_Order (Order_id, Book_id, OrderDet_num, Order_time)\nVALUES (4, 102, 55, CURRENT_TIMESTAMP);\n\n查询T_book表中作者是‘高永彬’的教材名称、ISBN号:\nSELECT Book_name, Book_isbn FROM T_book WHERE Book_author = '高永彬';\n\n查询‘水利水电出版社’所有的教材信息:\nSELECT * FROM T_book WHERE Book_publisher = '水利水电出版社';\n\n查询价格在50到80之间的教材名称、价格:\nSELECT Book_name, Book_price FROM T_book WHERE Book_price >= 50 AND Book_price <= 80;\n\n查询T_Order表中订单时间为‘2021-06-24’的订单信息:\nSELECT * FROM T_Order WHERE Order_time = '2021-06-24';\n\n修改T_Order表第4个订单的订单数量为126:\nUPDATE T_Order SET OrderDet_num = 126 WHERE Order_id = 4;\n\n查询T_Order表中id为‘102’的教材被征订的总数量:\nSELECT SUM(OrderDet_num) FROM T_Order WHERE Book_id = 102;\n\n查询ISBN号为‘9787040395853’的教材信息:\nSELECT * FROM T_book WHERE Book_isbn = '9787040395853';\n\n统计订单表中订单id，教材名称，订购总数:\nSELECT T_Order.Order_id, T_book.Book_name, SUM(T_Order.OrderDet_num) AS TotalNum\nFROM T_Order\nJOIN T_book ON T_Order.Book_id = T_book.Book_id\nGROUP BY T_Order.Order_id, T_book.Book_name;\n\n将以上SQL语句保存为.sql格式的脚本文件。