《MySQL数据库设计与应用》-习题参考答案及操作题源代码.docx

MySQL数据库设计与应用书后习题参考答案第01章 认识数据库和部署MySQL环境一、选择题01. C02. D03. D04. A05. B06. C07. D08. C09. A10. D第02章创立和管理数据库一、选择题01. A02. D03. D04. A05. B06. A07. B08. B09. C10. B二、操作题1 .（1）连接数据库a. win+r输入cmd,回车b.切换当前目录：cd C： Program FilesMySQLMySQL Server 8. 0binc. C：Program FilesMySQLMySQL Server 8. 0bin>mysql -u root -p密 码,回车（2）设置字符集set character set_server=,gb2312'；设置服务器字符集set character_set_database=*gb2312r ；设置数据库字符集set names gb2312； 一设置客户端和连接字符集DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET rollback' = 1；START TRANSACTION；INSERT INTO borrow(rid,bld,lendDate,wi1IDate) VALUES(1003(SELECT bld from book WHERE bName=，深入.NET 平台和 C# 编程 .CURDATEO, '2021-4-1')；-图书信息表书的当前数量TUPDATE book AS B SET B. bCount=B. bCount-1WHERE B.bId=(SELECT C.bld FROM (SELECT D.bld from book AS D WHERE D.bName='深入.NET 平台和 C#编程')AS C)；-读者信息表读者借书数量+1UPDATE reader AS R SET R. lendNum=R. lendNum+1WHERE R.rld=(SELECT t. rid from (SELECT S. rid FROM reader AS S WHERE S. rName='张无忌')AS t)；出错时回滚IF '.rollback' THENROLLBACK；ELSECOMMIT；END IF；END；2 . START TRANSACTION；开启事务 一插入罚款记录INSERTINTOpenalty(rid,bld,pType,amount)VALUES (,004,'ISBN0041,1,5.60)；修改借阅表还书时间UPDATE borrow SET returnDate=CURDATE() WHERE rid='004' AND bId='ISBN004'；修改读者表借书数量UPDATE reader SET lendNum=lendNum-l WHERE rid='004'；修改图书信息表现存数量UPDATE book SET bCount=bCount+l WHERE bld=r1SBN004'；提交COMMIT；"如果上述代码执行失败，需要手动执行回滚 ROLLBACK；3 .-为图书信息表中图书名称bName创立索引CREATE INDEX IX_bName ON book(bName)；创立管理员查需图书信息视图CREATE VIEW VW_admin ASSELECT book. bName AS '图书名称'，reader. rName AS '读者姓名 ',borrow. willDate AS '应还时间'，book. bCount AS '现存图书数量 ',book. bTotal AS '馆藏总量'FROM book INNER JOIN borrow ON borrow, bld = book, bld INNER JOIN reader ON borrow. rTd = reader, ridWHERE borrow. returnDate IS NULL OR borrow. willDate >= CURDATEO ；创立读者查阅图书视图CREATE VIEW VW_reader ASSELECTbook. bName AS '图书名称'，book. bCount AS '现存数量，book. bTotal AS 馆藏数量'FROM book ；第09章 存储过程和触发器一、选择题01. D (选项D需要修改为：“MySQL数据库中存储过程的参数一般不支持默认值”)02. C03. B04. B05. B06. B07. D08. D09. C10. C二、操作题1. DELIMITER ?CREATE PROCEDURE proc_getpubComp(TN company varchar (50)BEGINSELECT bld, bName,author, pubComp,pubDate FROM book WHERE pubComp LIKEC0NCAT('%北京%company)；END ?DELIMITER ；DELIMITER ?CREATE PROCEDURE proc_getBorrowCount(IN b date date,TN o_date,OUT totalNum)BEGINDECLARE s_date VARCHAR(lO)；IF b_date IS NULL THEN 假设未指定起始日期，起始时间为上月当日SET b_date=DATE_SUB(CURDATE(),INTERVAL 1 MONTH)； END IF；IF o_date IS NULL THEN 假设未指定结束日期，起始时间为上月当日SET o_date=CURDATE()；END IF；-统计借阅人次SELECT COUNT(*) INTO totalNum FROM borrow ； END ? DELIMITER；DELIMITER ?CREATE PROCEDURE proc_readerBorrow(TN readerTD varchar(8), TN bookID varchar(8),IN bookName varchar(50) BEGINDECLARE readerCount INT DEFAULT 0； 统计名字为 XXX 的数量UPDATE book SET bCount=bCount-l WHERE bld= (SELECT a. bld FROM (SELECT b. bld FROM book AS b WHERE b. bName=bookName) as a)；SELECT COUNT(*) INTO readerCount FROM reader WHERE rId=readerID； IF readerCount>0 THENUPDATE reader SET 1endNum=1endNum+1 WHERE rName=readcrName； ELSEINSERT reader VALUES(readerID,readerName,1,NULL)；END IF；INSERT INTO borrow VALUES(readerlD,(SELECT bld FROM book WHERE bName=bookName LIMIT1),CURDATE (),DATE_ADD(CURDATE(),INTERVAL1MONTH),NULL)； END DELIMITER；2. DELIMITER ?CREATE TRIGGER TR_delReader BEFORE DELETE ON reader FOR EACH ROW delete ； BEGINDELETE FROM penalty WHERE rId=0LD. rid；DELETE FROM borrow WHERE rId=OLD. rid； END ? DELIMITER；第10章 管理和维护数据库一、选择题D1. DB2. AB二、操作题.一创立用户studOl,密码123CREATE USER 1 studOr1 localhost' IDENTIFIED BY &#39;123'；创立用户stud02,密码456CREATE USER 'stud02r'localhost' IDENTIFIED BY '456'创立用户test,密码789CREATE USER 'test'rlocalhostr IDENTIFIED BY '7891；一修改test用户名为stud03RENAME USER 'testlocalhost' TO 'stud03'；一删除用户stud03DROP USER rstud03'；授予studOl用户操作读者表的所有权限GRANT ALL ON reader TO 1studOllocalhost,；授予studOl用户对借阅表的查询权限GRANT SELECT ON borrow TO 'studOllocalhost'；授予stud02用户操作数据库的所有权限GRANT ALL ON * TO 'stud02''localhost'；.一备份数据library1. win+r ->cmd.修改当前目录cd C：Program Fi1esMySQLMySQL Server 8.0bin.备份数据库为sql文件mysqldump -u root -p databases librarydb > d： backbk_library. sql 一备份为sql文件，如有密码回车后输入备份数据库为txt文件mysqldump -u root -p -databases librarydb > d： backbk_library. txt 备份为txt文件，如有密码回车后输入.数据恢复mysql -u root -p 123456 librarydb < d：backbk_library. txt第11章数据库设计与规范化一、选择题01. D02. C03. A04. D05. D06. D07. D08. B09. A10. C二、操作题1 .数据库设计步骤（1）需求分析阶段（2）概念设计阶段（3）逻辑设计阶段（4）数据库物理设计阶段（5）数据库实施阶段（6）数据库运行和维护阶段.第一范式（1NF）是最基本的范式，确保每列保持原子性。如果数据库表中的 所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。第二范式（2NF）确保表中的每列都和主键相关，在第一范式的基础之上更进一 层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某 一局部相关（主要针对联合主键而言）。第三范式（3NF）确保每列都和主键列直接相关，而不是间接相关。需要确保数据 表中的每一列数据都和主键直接相关，而不能间接相关。3. 一对一：身份证和中华人民共和国公民，学籍号和学生，班长和班级 一对多：一个班级和同学，一个老板和应多个下属，一个学校和多个教师 多对多：一个老师对应多个学生，同时一个学生对应多个老师E-R图也称实体-联系图（Entity Relationship Diagram）,就是以图形的方 式将数据库的整个逻辑结构表示出来，包括矩形描述实体、椭圆描述属性、菱形 描述联系和直线连接属性和实体集等。作用：用图形的方式描述数据库的整个逻辑结构，将现实世界关系描述为直观的 概念模型，方便数据库开发人员理解实体间的逻辑关系。第12章 课程工程银行ATM系统数据库设计与优化一、选择题C1. DD2. AD3. AD4. DB5. D2. 一创立LibraryDB数据库CREATE DATABASE IF NOT EXISTS LibraryDBDEFAULT CHARACTER SET gb2312DEFAULT COLLATE gb2312_chinese_ci；第03章创立和管理数据表一、选择题01. B02. C03. C04. C05. A06. A07. B08. I)09. B10. C二、操作题1 . USE LibraryDB；选择数据库 LibraryDBSET NAMES gb2312；设置字符集CREATE TABLE Book(bld CHAR(17) PRIMARY KEY.bName VARCHAR(50) NOTNULL,author VARCHAR(20),pubComp VARCHAR(50),pubDate DATE,bCount INT,price DECIMAL(8,2)； 创立,Book2 .一创立表ReaderCREATE TABLE Reader(rid VARCHAR(8) PRIMARY KEY.rName VARCHAR(20) NOTNULL,lendNum INT,rAddress VARCHAR(IOO)；.一创立表BorrowCREATE TABLE Borrow(rid VARCHAR (8),bid CHAR (17),lendDate DATE NOTNULL.willDate DATE NOT NULL,returnDate DATE,PRIMARY KEY(rid,bld)；3 .一 创立表 PenaltyCREATE TABLE Penalty (rid VARCHAR (8) NOT NULL, bld CHAR (17) NOT NULL.pDateDATE NOT NULL.pType enum('延期'，损坏'，'丧失'),amount DECIMAL(8,2)；.添加约束-Book 表"创立图书编号约束：图书编号已ISBN开头，不区分大小写ALTER TABLE Book ADD CONSTRAINT CK.bld CHECK(LEFT(bld,4)=，ISBN1)； ALTER TABLE Book ADD CONSTRAINT ck_bid CHECK(BID LIKE 'ISBN%')；创立出版日期约束：出版日期在2021T-30之前ALTER TABLE Book ADD CONSTRAINT CK_pubDateCHECK (pubDate=l)；创立大家约束：单价大于0ALTER TABLE Book ADD CONSTRAINT CK_price CHECK(price>0)； -Borrow 表一创立Borrow表中rid字段外键约束ALTER TABLE Borrow ADD CONSTRAINT FK_Borrowrld FOReign KEY(rid) REFERENCES Reader (rid)；一创立Borrow表中bld字段外键约束ALTER TABLE Borrow ADD CONSTRAINT FK_BorrowbTd FOReign KEY(BID) REFERENCES Book (bld)；一 Reader 表已借书数量约束：数量大于或等于0ALTER TABLE Reader ADD CONSTRAINT CK_lendNum CHECK(lendNum&gt;=0)；-Penalty 表创立复合主键ALTER TABLE Penalty ADD CONSTRAINT PRIMARY KEY(rid,bld,pDate)；罚款金额约束：罚金大于0ALTER TABLE Penalty ADD CONSTRAINT CHECK(amount>0)； 创立Penalty表rid外键约束ALTER TABLE Penalty ADD CONSTRAINT FK_PenaltyrId FOReign KEY(rid) REFERENCES Reader(rid)；一 创立Penalty表bld外键约束ALTER TABLE Penalty ADD CONSTRAINT FK_penaltybId FOReign KEY (bld) REFERENCES Book(bld)；一创立罚款日期默认值为当前日期ALTER TABLE Penalty ALTER pDate SET DEFAULT(CURDATEO)；4 .为Book表天剑bTotal字段ALTER TABLE Book ADD COLUMN bTotal INT；第04章用SQL语句操作数据一、选择题01. B02. C03. A04. C05. B06. A07. A08. B09. C10. BC11. AB12. CD二、操作题.一插入Book表数据INSERT INTO Book VALUESCISBN0021, PC语言程序设计'，'张成叔'，'高等教育 出版社'，'2019-9-1', 200,49. 00,200)；INSERT INTO Book VALUES ('TSBN0011, PSQL Server 数据库设计与应用'，'张成 叔，'中国铁道出版社'，'2020-7-1'，100,56.00,100)；1 .一插入reader表数据INSERT INTO Reader VALUES ('0011 / zhangYongwei'/1', NULL)；INSERT INTO Reader VALUES('002','zhangDawei1,'2r,NULL)；.一 插入Borrow表数据INSERT INTO Borrow(rID,bld,lendDate,wi1IDate)VALUESC001', 'TSBN001',，2021-l-30, ,2021-3-30,)；INSERT INTO Borrow(rID,bld,lendDate,willDate) VALUES (,002'ISBN002, ,2021-1-30'/2021-3-31,)；.一插入Penalty表数据INSERTINTOPenalty(rid,bld,pType,amount)VALUES('001'ISBN0011,1,20)；INSERT INTO Penalty(rid,bld,pType,amount)VALUES(r0021,'TSBN002r, 3,50)；第05单表查询和模糊查询一、选择题01. C02. A(A 选项应该修改给为：“SELECT CONCAT(LastName,FirstName) FROM Employee； v)03. B04. C05. B06. A07. D08. B09. B10. BD11. AA12. DB二、操作题SELECT rid AS读者编号，bld AS图书编号，pDate AS罚款日期，CASE pType WHEN 1 THEN '延期'WHEN 2 THEN '损坏'WHEN 3 THEN '丧失'ELSE NULL encl AS 罚款类型FROM Penalty；创立存储过程CREATE PROCEDURE procO BEGINSELECTCOUNT(*)INTO bNum from borrow wherewillDate=DATE_ADD(CURDATE(),INTERVAL 1 DAY)； IF bNum=0 THENSELECT '明天没有应归还的图书'；ELSEIF bNum<10 THENUPDATE borrow SET wi1lDate=DATE_ADD(wi1IDate,INTERVAL 2 DAY) where wi1lDate=DATE_ADD(CURDATE(),INTERVAL 1 DAY)； ELSESELECT bName,rName,1endDateFROM borrow,book,readerwhere borrow. bld=book. bld AND borrow. rld=reader. rid AND willDate=DATE_ADD (CURDATE(),INTERVAL 1 DAY)；SELECT bNum；END IF； ENDSELECT CASEWHEN SUM(bCount)<10000 THEN '现有图书缺乏一万本,还需要继续购置 书籍WHEN SUM(bCount)>=10000 THEN '现有图书一万本以上，需要加强图书 管理ENDFROM book；1. (1) SELECT * FROM Houseinfo WHERE HouseType LIKE '%一厅，；SELECT * FROM Houseinfo WHERE Landlord LIKE '于玲'；(2) SELECT * FROM HouseTnfo WHERE ExchangeType='出租'AND Address LIKE '%解放区;ECT AVG(Area) AS '平均面积'FROM Houseinfo WHERE HouseType LIKE ' 一室一厅'；2. (1) SELECT AVG(Age) AS '男员工平均年龄'FROM Employee WHERE Sex=' 男；SELECT * FROM Employee WHERE Education=&#39;本科'；(2) SELECT AVG(Salary) AS '25 岁以上员工平均工资'FROM Employee WHERE Age>25；SELECT MAX (Salary) AS '最高工资'，MIN(Sala;ry)AS '最低工资'FROM Employee GROUP BY Sex；6. (1)SELECT ProductNumber*SalesPrice AS '总金额'FROM Sales WHERE ClientName=,美国'；(2)SELECT ClientName,ProductNumber FROM Sales,Product WHERE Product. ProductName='坦克'AND Product. ProductID=Sales. ProductID；(3)SELECT ProductNumber*SalesPrice AS '销售总金额FROM Sales,Product WHERE Product. ProductName=, 轮 船 'ANDProduct. ProductTD=Sales. ProductID；第06章 分组查询和连接查询一、选择题01. C02. A03. A04. A05. B06. C07. C08. D09. C10. DC11. BB12. DA二、操作题.-查询当前没有被读者借阅的图书信息SELECT bName, bld,author, pubComp, pubDate,price from book WHERE bld NOT IN (SELECT bld from borrow)；.-使用子查询，查询今年所有缴纳罚款的读者清单SELECT rName,bName,pDate,pType,amountFROM book,reader,(SELECT * from penalty WHEREYEAR(pDate)=YEAR(CURDATE() AS pWHERE book.bld=p. bld AND reader. rld=p. rid；1 .-使用子查询，查询地址为空的所有为还书读者信息SELECT b.rid,rName,bName,lendDate,willDateFROM (SELECT * FROM borrow WHERE returnDate IS NULL) AS b,reader,bookWHERE b. rid=reader, rid AND rAddress IS NULL AND b. bld=book, bld；2 .一创立客户表customer InfoCREATE TABLE customerInfo(ID I NT AUT0_INCREMENT PRIMARY KEY,Name VARCHAR(50) NOT NULL,Sex TINYINT NOT NULL DEFAULT 1 CONSTRAINT CHECK (Sex=l OR sex=0), NationalitylD INT NOT NULL, Certificate VARCIIAR(50) NOT NULL,Cert ificateNum VARCHAR(50) NOT NULL,Address VARCHAR(50) NOT NULL,Company VARCIIAR(50) NOT NULL, matter VARCHAR(200) DEFAULT '出差');一创立国籍表 nationalityinfoCREATE TABLE nationalityinfo(NationalitylD INT PRIMARY KEY,Nationality varchar(50) NOT NULL)；一 创立 customerlnfo 表的 Nationality 外键ALTER TABLE customerinfo ADD CONSTRAINT FK_NationalityID FOREIGN KEY (NationalitylD) REFERENCES nationalitylnfo(NationalitylD)； (1)统计每个国家住店总人数SELECT Nationality AS '国籍',C0UNT(ID) AS '住店人数'from customerinfo INNER JOIN nationalityinfo ONcustomerinfo. National!tyID=nationalityInfo. NationalitylDGROUP BY customerinfo. NationalitylD； (2)住店男女人数SELECT case Sexwhen 1 then '男"when 0 then '女'END as '性别 from customcrlnfo (3)不同证件类别人数SELECT Certificate AS '证件类别'，COUNT (ID) AS '人数'FROM customerinfoGROUP BY Certificate；一 (4)新加坡籍入住证件类型数SELECT COUNT (Certificate) AS '新加坡证件入住类型数'FROM customerInfo INNER JOIN nationalityinfo ON customerlnfo. Nationali tylD=nationali tylnfo. Nationali tyTDWHERE Nationality='新加坡'； (5)中国籍住店男女人数SELECT case Sexwhen 1 then '男'when 0 then , 女'END as '性别'COUNT (ID) AS '中国籍入住人数'FROM customerlnfo INNER JOIN nationalityinfo ON customerinfo. NationalityID=nationalitylnfo. NationalitylDWHERE Nationality='中国'GROUP BY Sex；5. - (1)每个部门总人数由高到低排序SELECT DeptName AS '部门'，COUNT(empID) AS '人数'FROM emplnfo INNER JOIN deptTnfo ON emplnfo. DepID=deptInfo. DepIDGROUP BY emplnfo. DepIDORDER BY COUNT(empID) DESC；一 (2)每个部门的男女人数SELECT DeptName AS '部门'，case empSexwhen 1 then '男"when 0 then &#39;女'END as '性别'COUNT(empID) AS '人数'FROM emplnfo INNER JOIN deptinfo ON emplnfo. DepID=deptInfo. DepID GROUP BY emplnfo. DepID；-(3)产品研发部的男女人数SELECT DeptName AS '部门'，case empSexwhen 1 then '男, when 0 then '女'END as '性另，COUNT(empID) AS '人数'FROM emplnfo INNER JOIN deptinfo ON emplnfo. DepID=deptInfo. DepIDWHERE DeptName='产品研发部GROUP BY cmplnfo. DcpID；第07章阶段工程QQ数据库管理一、选择题B1. DB2. BA3. BC4. CB5. C第08章索引、视图和事务一、选择题01. A02. C03. C04. B05. D06. A07. D08. D09. A10. DB11. DD12. CD二、操作题1. CREATE PROCEDURE 'NewProc' 0BEGIN"定义回滚变量DECLARE ' rollback' BOOL DEFAULT 0；