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

<p><span id="_baidu_bookmark_start_0" style="display: none; line-height: 0px;">‍</span>《MySQL数据库设计与应用》
书后习题参考答案
第01章 认识数据库和部署MySQL环境
一、选择题01. C
02. D03. D
04. A05. B
06. C07. D
08. C09. A
10. D第02章创立和管理数据库
一、选择题01. A
02. D03. D
04. A05. B
06. A07. B
08. B09. C
10. B二、操作题
1 .（1）连接数据库
a. win+r输入cmd,回车
b.切换当前目录：cd C： \Program Files\MySQL\MySQL Server 8. 0\bin
c. C：\Program Files\MySQL\MySQL Server 8. 0\bin&gt;mysql -u root -p［密 码］,回车
（2）设置字符集
set character set_server=,gb2312&#39;；—设置服务器字符集
set character_set_database=*gb2312r ；―设置数据库字符集
set names gb2312； 一设置客户端和连接字符集
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET 〔rollback&#39; = 1；
START TRANSACTION；
INSERT INTO borrow(rid,bld,lendDate,wi1IDate) VALUES(1003(SELECT bld from book WHERE bName=，深入.NET 平台和 C# 编程 ^.CURDATEO, &#39;2021-4-1&#39;)；
-图书信息表书的当前数量T
UPDATE book AS B SET B. bCount=B. bCount-1
WHERE B.bId=(SELECT C.bld FROM (SELECT D.bld from book AS D WHERE D.bName=&#39;深入.NET 平台和 C#编程&#39;)AS C)；
-读者信息表读者借书数量+1
UPDATE reader AS R SET R. lendNum=R. lendNum+1
WHERE R.rld=(SELECT t. rid from (SELECT S. rid FROM reader AS S WHERE S. rName=&#39;张无忌&#39;)AS t)；~出错时回滚
IF &#39;.rollback&#39; THENROLLBACK；
ELSECOMMIT；
END IF；END；；
2 . START TRANSACTION；—开启事务 一插入罚款记录INSERTINTOpenalty(rid,bld,pType,amount)
VALUES (,004,,&#39;ISBN0041,1,5.60)；~修改借阅表还书时间
UPDATE borrow SET returnDate=CURDATE() WHERE rid=&#39;004&#39; AND bId=&#39;ISBN004&#39;；~修改读者表借书数量
UPDATE reader SET lendNum=lendNum-l WHERE rid=&#39;004&#39;；~修改图书信息表现存数量
UPDATE book SET bCount=bCount+l WHERE bld=r1SBN004&#39;；~提交
COMMIT；&quot;如果上述代码执行失败，需要手动执行回滚 ROLLBACK；
3 .-为图书信息表中图书名称bName创立索引CREATE INDEX IX_bName ON book(bName)；
~创立管理员查需图书信息视图CREATE VIEW VW_admin AS
SELECT book. bName AS &#39;图书名称&#39;，reader. rName AS &#39;读者姓名 &#39;,borrow. willDate AS &#39;应还时间&#39;，book. bCount AS &#39;现存图书数量 &#39;,book. bTotal AS &#39;馆藏总量&#39;FROM book INNER JOIN borrow ON borrow, bld = book, bld INNER JOIN reader ON borrow. rTd = reader, rid
WHERE borrow. returnDate IS NULL OR borrow. willDate &gt;= CURDATEO ；~创立读者查阅图书视图
CREATE VIEW VW_reader ASSELECTbook. bName AS &#39;图书名称&#39;，book. bCount AS &#39;现存数量’，book. bTotal AS ‘馆藏数量&#39;FROM book ；
第09章 存储过程和触发器
一、选择题01. D (选项D需要修改为：“MySQL数据库中存储过程的参数一般不支持默认值”)
02. C03. B
04. B05. B
06. B07. D
08. D09. C
10. C二、操作题
1. DELIMITER ??
CREATE PROCEDURE proc_getpubComp(TN company varchar (50))BEGIN
SELECT bld, bName,author, pubComp,pubDate FROM book WHERE pubComp LIKEC0NCAT(&#39;%北京%©company)；
END ??
DELIMITER ；DELIMITER ??
CREATE PROCEDURE proc_getBorrowCount(IN b date date,TN o_date,OUT totalNum)BEGIN
DECLARE 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)) BEGIN
DECLARE 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&gt;0 THEN
UPDATE 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 ； BEGIN
DELETE FROM penalty WHERE rId=0LD. rid；
DELETE FROM borrow WHERE rId=OLD. rid； END ?? DELIMITER；第10章 管理和维护数据库
一、选择题D
1. DB
2. AB
二、操作题.一创立用户studOl,密码123
CREATE USER 1 studOr@1 localhost&#39; IDENTIFIED BY &#39;123&#39;；创立用户stud02,密码456
CREATE USER &#39;stud02r@&#39;localhost&#39; IDENTIFIED BY &#39;456&#39;;创立用户test,密码789
CREATE USER &#39;test&#39;@rlocalhostr IDENTIFIED BY &#39;7891；一修改test用户名为stud03
RENAME USER &#39;testlocalhost&#39; TO &#39;stud03&#39;；一删除用户stud03
DROP USER rstud03&#39;；授予studOl用户操作读者表的所有权限
GRANT ALL ON reader TO 1studOllocalhost,；~授予studOl用户对借阅表的查询权限
GRANT SELECT ON borrow TO &#39;studOllocalhost&#39;；授予stud02用户操作数据库的所有权限
GRANT ALL ON * TO &#39;stud02&#39;@&#39;localhost&#39;；.一备份数据library
1. win+r -&gt;cmd.修改当前目录
cd C：\Program Fi1es\MySQL\MySQL Server 8.0\bin.备份数据库为sql文件
mysqldump -u root -p ―databases librarydb &gt; d： \back\bk_library. sql 一备份为sql文件，如有密码回车后输入
备份数据库为txt文件
mysqldump -u root -p -databases librarydb &gt; d： \back\bk_library. txt ~备份为txt文件，如有密码回车后输入.数据恢复
mysql -u root -p 123456 librarydb &lt; d：\back\bk_library. txt第11章数据库设计与规范化
一、选择题01. D
02. C03. A
04. D05. D
06. D07. D
08. B09. A
10. C二、操作题
1 .数据库设计步骤
（1）需求分析阶段
（2）概念设计阶段
（3）逻辑设计阶段
（4）数据库物理设计阶段
（5）数据库实施阶段
（6）数据库运行和维护阶段.第一范式（1NF）是最基本的范式，确保每列保持原子性。如果数据库表中的 所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。
第二范式（2NF）确保表中的每列都和主键相关，在第一范式的基础之上更进一 层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某 一局部相关（主要针对联合主键而言）。
第三范式（3NF）确保每列都和主键列直接相关，而不是间接相关。需要确保数据 表中的每一列数据都和主键直接相关，而不能间接相关。
3. 一对一：身份证和中华人民共和国公民，学籍号和学生，班长和班级…… 一对多：一个班级和同学，一个老板和应多个下属，一个学校和多个教师…… 多对多：一个老师对应多个学生，同时一个学生对应多个老师……E-R图也称实体-联系图（Entity Relationship Diagram）,就是以图形的方 式将数据库的整个逻辑结构表示出来，包括矩形描述实体、椭圆描述属性、菱形 描述联系和直线连接属性和实体集等。
作用：用图形的方式描述数据库的整个逻辑结构，将现实世界关系描述为直观的 概念模型，方便数据库开发人员理解实体间的逻辑关系。
第12章 课程工程银行ATM系统数据库设计与优化
一、选择题C
1. DD
2. AD
3. AD
4. DB
5. D
2. 一创立LibraryDB数据库CREATE DATABASE IF NOT EXISTS LibraryDB
DEFAULT CHARACTER SET gb2312DEFAULT COLLATE gb2312_chinese_ci；
第03章创立和管理数据表
一、选择题01. B
02. C03. C
04. C05. A
06. A07. B
08. I)09. B
10. 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))；— 创立,Book
2 .一创立表ReaderCREATE TABLE Reader(rid VARCHAR(8) PRIMARY KEY.rName VARCHAR(20) NOT
NULL,lendNum INT,rAddress VARCHAR(IOO))；.一创立表Borrow
CREATE 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.pDate
DATE NOT NULL.pType enum(&#39;延期&#39;，‘损坏&#39;，&#39;丧失&#39;),amount DECIMAL(8,2))；.添加约束
-Book 表&quot;创立图书编号约束：图书编号已ISBN开头，不区分大小写
ALTER TABLE Book ADD CONSTRAINT CK.bld CHECK(LEFT(bld,4)=，ISBN1)； ALTER TABLE Book ADD CONSTRAINT ck_bid CHECK(BID LIKE &#39;ISBN%&#39;)；~创立出版日期约束：出版日期在2021T-30之前
ALTER TABLE Book ADD CONSTRAINT CK_pubDateCHECK (pubDate</p><date('2021-1-30'))； 1alter="" table="" book="" add="" constraint="" ck_bcount="" bcount="">=l)；
~创立大家约束：单价大于0ALTER TABLE Book ADD CONSTRAINT CK_price CHECK(price&gt;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 表~已借书数量约束：数量大于或等于0
ALTER 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&gt;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. B
02. C03. A
04. C05. B
06. A07. A
08. B09. C
10. BC
11. AB
12. CD
二、操作题.一插入Book表数据
INSERT INTO Book VALUESCISBN0021, PC语言程序设计&#39;，&#39;张成叔&#39;，&#39;高等教育 出版社&#39;，&#39;2019-9-1&#39;, 200,49. 00,200)；INSERT INTO Book VALUES (&#39;TSBN0011, PSQL Server 数据库设计与应用&#39;，&#39;张成 叔，&#39;中国铁道出版社&#39;，&#39;2020-7-1&#39;，100,56.00,100)；
1 .一插入reader表数据INSERT INTO Reader VALUES (&#39;0011 / zhangYongwei&#39;/1&#39;, NULL)；
INSERT INTO Reader VALUES(&#39;002&#39;,&#39;zhangDawei1,&#39;2r,NULL)；.一 插入Borrow表数据
INSERT INTO Borrow(rID,bld,lendDate,wi1IDate)VALUESC001&#39;, &#39;TSBN001&#39;,，2021-l-30,, ,2021-3-30,)；
INSERT INTO Borrow(rID,bld,lendDate,willDate) VALUES (,002\&#39;ISBN002,, ,2021-1-30&#39;/2021-3-31,)；.一插入Penalty表数据
INSERTINTOPenalty(rid,bld,pType,amount)VALUES(&#39;001&#39;ISBN0011,1,20)；
INSERT INTO Penalty(rid,bld,pType,amount)VALUES(r0021,&#39;TSBN002r, 3,50)；第05单表查询和模糊查询
一、选择题01. C
02. A(A 选项应该修改给为：“SELECT CONCAT(LastName,FirstName) FROM Employee； v)03. B
04. C05. B
06. A07. D
08. B09. B
10. BD
11. AA
12. DB
二、操作题
SELECT rid AS读者编号，bld AS图书编号，pDate AS罚款日期，
CASE pType WHEN 1 THEN &#39;延期&#39;WHEN 2 THEN &#39;损坏&#39;
WHEN 3 THEN &#39;丧失&#39;ELSE NULL encl AS 罚款类型
FROM Penalty；~创立存储过程
CREATE PROCEDURE procO BEGINSELECTCOUNT(*)INTO @bNum from borrow where
willDate=DATE_ADD(CURDATE(),INTERVAL 1 DAY)； IF @bNum=0 THENSELECT &#39;明天没有应归还的图书&#39;；
ELSEIF @bNum&lt;10 THEN
UPDATE borrow SET wi1lDate=DATE_ADD(wi1IDate,INTERVAL 2 DAY) where wi1lDate=DATE_ADD(CURDATE(),INTERVAL 1 DAY)； ELSESELECT bName,rName,1endDate
FROM borrow,book,reader
where borrow. bld=book. bld AND borrow. rld=reader. rid AND willDate=DATE_ADD (CURDATE(),INTERVAL 1 DAY)；SELECT @bNum；
END IF； ENDSELECT CASE
WHEN SUM(bCount)&lt;10000 THEN &#39;现有图书缺乏一万本,还需要继续购置 书籍’WHEN SUM(bCount)&gt;=10000 THEN &#39;现有图书一万本以上，需要加强图书 管理’
ENDFROM book；
1. (1) SELECT * FROM Houseinfo WHERE HouseType LIKE &#39;%一厅％，；SELECT * FROM Houseinfo WHERE Landlord LIKE &#39;于—玲&#39;；
(2) SELECT * FROM HouseTnfo WHERE ExchangeType=&#39;出租&#39;AND Address LIKE &#39;%解放区％’;ECT AVG(Area) AS &#39;平均面积&#39;FROM Houseinfo WHERE HouseType LIKE &#39; 一室一厅&#39;；
2. (1) SELECT AVG(Age) AS &#39;男员工平均年龄&#39;FROM Employee WHERE Sex=&#39; 男’；SELECT * FROM Employee WHERE Education=&#39;本科&#39;；
(2) SELECT AVG(Salary) AS &#39;25 岁以上员工平均工资&#39;FROM Employee WHERE Age&gt;25；SELECT MAX (Salary) AS &#39;最高工资&#39;，MIN(Sala;ry)AS &#39;最低工资&#39;FROM Employee GROUP BY Sex；
6. (1)SELECT ProductNumber*SalesPrice AS &#39;总金额&#39;FROM Sales WHERE ClientName=,美国&#39;；(2)SELECT ClientName,ProductNumber FROM Sales,Product WHERE Product. ProductName=&#39;坦克&#39;AND Product. ProductID=Sales. ProductID；
(3)SELECT ProductNumber*SalesPrice AS &#39;销售总金额’FROM Sales,Product WHERE Product. ProductName=, 轮 船 &#39;ANDProduct. ProductTD=Sales. ProductID；
第06章 分组查询和连接查询
一、选择题01. C
02. A03. A
04. A05. B
06. C07. C
08. D09. C
10. DC
11. BB
12. 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 WHERE
YEAR(pDate)=YEAR(CURDATE())) AS pWHERE book.bld=p. bld AND reader. rld=p. rid；
1 .-使用子查询，查询地址为空的所有为还书读者信息SELECT b.rid,rName,bName,lendDate,willDate
FROM (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 &#39;出差&#39;);
一创立国籍表 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 &#39;国籍&#39;,C0UNT(ID) AS &#39;住店人数&#39;
from customerinfo INNER JOIN nationalityinfo ONcustomerinfo. National!tyID=nationalityInfo. NationalitylD
GROUP BY customerinfo. NationalitylD；~ (2)住店男女人数
SELECT case Sexwhen 1 then &#39;男&quot;
when 0 then &#39;女&#39;
END as &#39;性别 from customcrlnfo ~ (3)不同证件类别人数SELECT Certificate AS &#39;证件类别&#39;，COUNT (ID) AS &#39;人数&#39;FROM customerinfo
GROUP BY Certificate；一 (4)新加坡籍入住证件类型数
SELECT COUNT (Certificate) AS &#39;新加坡证件入住类型数&#39;FROM customerInfo INNER JOIN nationalityinfo ON customerlnfo. Nationali tylD=nationali tylnfo. Nationali tyTD
WHERE Nationality=&#39;新加坡&#39;；~ (5)中国籍住店男女人数
SELECT case Sexwhen 1 then &#39;男&#39;
when 0 then , 女&#39;END as &#39;性别&#39;COUNT (ID) AS &#39;中国籍入住人数&#39;
FROM customerlnfo INNER JOIN nationalityinfo ON customerinfo. NationalityID=nationalitylnfo. NationalitylDWHERE Nationality=&#39;中国&#39;
GROUP BY Sex；5. - (1)每个部门总人数由高到低排序
SELECT DeptName AS &#39;部门&#39;，COUNT(empID) AS &#39;人数&#39;FROM emplnfo INNER JOIN deptTnfo ON emplnfo. DepID=deptInfo. DepID
GROUP BY emplnfo. DepID
ORDER BY COUNT(empID) DESC；
一 (2)每个部门的男女人数SELECT DeptName AS &#39;部门&#39;，case empSex
when 1 then &#39;男&quot;when 0 then &#39;女&#39;
END as &#39;性别&#39;COUNT(empID) AS &#39;人数&#39;FROM emplnfo INNER JOIN deptinfo ON emplnfo. DepID=deptInfo. DepID GROUP BY emplnfo. DepID；
-(3)产品研发部的男女人数SELECT DeptName AS &#39;部门&#39;，case empSex
when 1 then &#39;男, when 0 then &#39;女&#39;END as &#39;性另『，COUNT(empID) AS &#39;人数&#39;
FROM emplnfo INNER JOIN deptinfo ON emplnfo. DepID=deptInfo. DepIDWHERE DeptName=&#39;产品研发部
GROUP BY cmplnfo. DcpID；第07章阶段工程QQ数据库管理
一、选择题B
1. DB
2. BA
3. BC
4. CB
5. C第08章索引、视图和事务
一、选择题01. A
02. C03. C
04. B05. D
06. A07. D
08. D09. A
10. DB
11. DD
12. CD
二、操作题1. CREATE PROCEDURE &#39;NewProc&#39; 0
BEGIN&quot;定义回滚变量
DECLARE &#39; rollback&#39; BOOL DEFAULT 0；</date('2021-1-30'))；>