MySQL數(shù)據(jù)庫基礎(chǔ)實例教程(微課版)-習題答案

1、MySQL數(shù)據(jù)庫基礎(chǔ)實例教程(微課版)-習題答案.docx第一章 數(shù)據(jù)庫基礎(chǔ)知識思考與練習一1.B 2.B 3.B 4.C 5,D二.1.root 2.QUIT 3.my.ini三.略第二章數(shù)據(jù)庫設(shè)計1.略2.略3.(1)B(2) A方案一張表：6*10*5000=30，0000 個數(shù)據(jù)B方案三張表：學生表：3*5000=1500 0課程表：2*10=20成績表：3*10*5000=150000三張表合計：15000+20+150000=16，5020A與B方案數(shù)據(jù)差:30,0000-16,5020 =13,4980(3)B更節(jié)省空間,因為B方案滿足第二范式,而A方案不滿足。4E-R圖關(guān)系模型

2、：商店（店號，店名，店址，店經(jīng)理）職工（工號，姓名，性別，工資，店號，入職時間）商品（商品號，品名，單價，數(shù)量，產(chǎn)地）銷售（店號，商品號，月銷售數(shù)量）第三章 數(shù)據(jù)定義一1.A 2.D 3.A 4.B 5,D二.1.CREATE， ALTER ，DROP 2.網(wǎng)絡(luò)模型，關(guān)系模型 3.數(shù)據(jù)庫管理系統(tǒng)，操作系統(tǒng)4數(shù)據(jù)庫，數(shù)據(jù)庫管理系統(tǒng)三.1create database XSCJ;2. use XSCJ;CREATE TABLE XS( 學號 char(6) NOT NULL PRIMARY KEY,姓名 char(8) NOT NULL ,專業(yè)名 varchar(20) NULL ,性別 tiny

3、int(1) NOT NULL ,出生時間 date NOT NULL ,總學分 tinyint(1) NULL , 照片 blob NULL,備注 text NULL );3. USE XSCJ;CREATE TABLE KC( 課程號 char(3) NOT NULL PRIMARY KEY,課程名 char(16) NOT NULL ,開課學期 tinyint(1) NOT NULL ,學時 tinyint(1)NOT NULL ,學分 tinyint(1) NULL);4. USE XSCJ;CREATE TABLE XS_ KC( 學號 char(6) NOT NULL,課程號 ch

4、ar(3) NOT NUL,成績tinyint NULL ,學分 tinyint(1)NULL ,PRIMARY KEY(學號,課程號);5.USE XSCJ;ALTER TABLE XSADD 獎學金等級tinyint NULL ,DROP COLUMN 姓名;6. USE XSCJALTER TABLE XS RENAME TO student;7.CREATE TABLE kc_copy1 LIKE KC;8.CREATE TABLE cj_copy2 AS(SELECT * FROM XS_KC);9. DROP TABLE IF EXISTS kc_copy1;10.USE XSCJ

5、SHOW TABLES ;11.DESCRIBE XS;12.DESC XS 學號;13.CREATE TABLE XS1(學號varchar(6) NOT NULL,姓名varchar(8) NOT NULL,成績int(3) NULL,PRIMARY KEY (學號),FOREIGN KEY (學號)REFERENCES XS (學號)ON DELETE RESTRICTON UPDATE RESTRICT) ENGINE=INNODB;14.CREATE TABLE XS2( 學號varchar(6) NOT NULL,姓名varchar(8) NOT NULL,出生日期date NUL

6、L,PRIMARY KEY (學號),FOREIGN KEY (學號)REFERENCES XS (學號)ON UPDATE CASCADE) ENGINE=INNODB;第四章數(shù)據(jù)更新(1)INSERT INTO XS VALUES(081101, 王林 , 計算機, 1, 1990-02-10, 50, NULL,NULL);(2)INSERT INTO XS SET 學號=081101, 姓名=王林, 專業(yè)=DEFAULT, 性別=1,出生日期=1990-02-10, 總學分=50;(3)REPLACE INTO XS VALUES(081101, 王小林 , 通信工程, 1, 1991

7、-03-08, 48, NULL,NULL);(4)UPDATE XSSET 總學分= 總學分+10;UPDATE XSSET 學號=081251 , 備注=轉(zhuǎn)專業(yè)學習 WHERE 姓名= 羅林琳;(5)DELETE FROM XSWHERE 總學分50; 第五章 數(shù)據(jù)查詢1.SELECT 姓名,專業(yè)名,總學分 FROM XS;2.SELECT 學號 AS number, 姓名 AS name, 總學分 AS markFROM XS WHERE 專業(yè)名= 計算機;3.SELECT 學號, 姓名,CASE WHEN 總學分 IS NULL THEN 尚未選課WHEN 總學分 =50 and 總學

8、分50;9.SELECT MAX(成績), MIN(成績)FROM XS_KCWHERE 課程號 = 101; 10.SELECT SUM(成績) AS 課程總成績FROM XS_KC WHERE 學號 = 081101;11.SELECT AVG(成績) AS 課程101平均成績FROM XS_KC WHERE 課程號 = 101;12.SELECT * FROM XS WHERE 學號 = 081101;13. SELECT * FROM XS WHERE總學分50;14.SELECT * FROM XS WHERE 備注 IS NULL;15.SELECT * FROM XSWHERE

9、專業(yè)名=計算機 AND 性別=0;16.SELECT * FROM XS_KCWHERE (課程號=102 OR 課程號=206 ) AND 成績=80或：SELECT * FROM XS_KCWHERE (課程號=102 and 成績=80) or (課程號=206 AND 成績=80)17.SELECT 學號,姓名,性別 FROM XSWHERE 姓名 LIKE 王%;18.SELECT 學號,姓名,專業(yè)名 FROM XSWHERE 學號 LIKE %0_;19.SELECT 學號,姓名 FROM XSWHERE 學號 LIKE %#_% ESCAPE #;20.SELECT * FROM

10、 XSWHERE 出生時間 NOT BETWEEN 1989-01-01 and 1989-12-31;21.SELECT * FROM XSWHERE 專業(yè)名 IN (計算機, 通信工程, 無線電);或：SELECT * FROM XSWHERE 專業(yè)名 =計算機 OR 專業(yè)名 = 通信工程 OR 專業(yè)名 = 無線電;22.SELECT * FROM XS WHERE 總學分 IS NULL;23.SELECT DISTINCT KC.課程名, XS_KC.課程號 FROM KC, XS_KC WHERE KC.課程號=XS_KC.課程號;或：SELECT DISTINCT 課程名, XS_

11、KC.課程號 FROM KC INNER JOIN XS_KC ON (KC.課程號=XS_KC.課程號);24.SELECT 姓名,成績FROM XS JOIN XS_KC ON XS.學號 = XS_KC.學號 WHERE 課程號 = 206 AND 成績=80;25.SELECT XS.學號, 姓名, 課程名, 成績FROM XS JOIN XS_KC ON XS.學號 = XS_KC.學號 JOIN KC ON XS_KC.課程號 = KC.課程號WHERE 課程名 = 計算機基礎(chǔ) AND 成績=80 ;26.SELECT a.學號,a.課程號,a.成績FROM XS_KC AS a

12、JOIN XS_KC AS b ON a.成績=b.成績 AND a.學號=b.學號 AND a.課程號!=b.課程號;27. SELECT DISTINCT 課程名FROM KC INNER JOIN XS_KC USING (課程號)或：SELECT DISTINCT 課程名FROM KC INNER JOIN XS_KC ON KC.課程號=XS_KC .課程號28.SELECT XS.* , 課程號FROM XS LEFT OUTER JOIN XS_KC ON XS.學號 = XS_KC.學號;29.SELECT XS_KC.* , 課程名FROM XS_KC RIGHT JOIN

13、KC ON XS_KC.課程號= KC.課程號;30.SELECT 姓名,學號 FROM XSWHERE 學號 IN ( SELECT 學號 FROM XS_KC WHERE 課程號 = 206 );或：SELECT 姓名,xs.學號FROM XS JOIN XS_KC ON XS.學號 = XS_KC.學號 WHERE 課程號 = 206 ;31.SELECT 姓名,學號,專業(yè)名 FROM XSWHERE 學號 NOT IN (SELECT 學號 FROM XS_KCWHERE 課程號 IN( SELECT 課程號 FROM KC WHERE 課程名 =離散數(shù)學);32.SELECT 學號

14、FROM XS_KCWHERE 課程號 =(SELECT 課程號FROM KC WHERE 課程名 =離散數(shù)學);33.SELECT 學號, 姓名, 專業(yè)名, 出生日期 FROM XSWHERE 出生日期 =ANY ( SELECT 成績 FROM XS_KC WHERE 課程號 =101 );35.SELECT 姓名 FROM XSWHERE EXISTS( SELECT * FROM XS_KC WHERE 學號 = XS.學號 AND 課程號 = 206 );36.SELECT 專業(yè)名 FROM XS GROUP BY 專業(yè)名;37.SELECT 專業(yè)名,COUNT(*) AS 學生數(shù)

15、FROM XS GROUP BY 專業(yè)名;38.SELECT 課程號, AVG(成績) AS 平均成績 ,COUNT(學號) AS 選修人數(shù)FROM XS_KC GROUP BY 課程號;39.SELECT 專業(yè)名, 性別, COUNT(*) AS 人數(shù) FROM XSGROUP BY 專業(yè)名,性別 WITH ROLLUP;40.SELECT 學號, AVG(成績) AS 平均成績 FROM XS_KCGROUP BY 學號HAVING AVG(成績) =85;41.SELECT 學號 FROM XS_KC WHERE 成績 = 80GROUP BY 學號HAVING COUNT(*) 2;4

16、2.SELECT 學號,姓名,專業(yè)名,出生日期 FROM XS WHERE 專業(yè)名 = 通信工程ORDER BY 出生日期;43.SELECT 姓名,課程名,成績 FROM XS,KC,XS_KCWHERE XS.學號= XS_KC.學號 AND XS_KC.課程號= KC.課程號 AND 課程名= 計算機基礎(chǔ) AND 專業(yè)名= 計算機ORDER BY 成績 DESC;44.SELECT 學號, 姓名, 專業(yè)名 FROM XS WHERE 專業(yè)名= 計算機 ORDER BY ( SELECT AVG(成績) FROM XS_KC GROUP BY XS_KC.學號HAVING XS.學號=XS

17、_KC.學號);45.SELECT * FROM XS ORDER BY 學號 LIMIT 5;46.SELECT * FROM XS ORDER BY 學號 LIMIT 3, 5;第六章 數(shù)據(jù)視圖一略二1.CREATE OR REPLACE VIEW XSCJ.CS_KC ASSELECT XS.學號,課程號,成績 FROM XSCJ.XS, XSCJ.XS_KCWHERE XS.學號 = XS_KC.學號 AND XS.專業(yè)名 = 計算機WITH CHECK OPTION;2.CREATE VIEW CS_KC_AVG(num, score_avg)ASSELECT 學號,AVG(成績)

18、FROM CS_KC GROUP BY 學號;3.SELECT 學號, 課程號 FROM CS_KC4.創(chuàng)建學生平均成績視圖XS_KC_AVG：CREATE VIEW XS_KC_AVG ( num,score_avg )AS SELECT 學號, AVG(成績) FROM XS_KC GROUP BY 學號;再對XS_KC_AVG視圖進行查詢。SELECT * FROM XS_KC_AVG WHERE score_avg=80;5.首先創(chuàng)建視圖CS_XS：CREATE OR REPLACE VIEW CS_XSAS SELECT * FROM XS WHERE 專業(yè)名 = 計算機WITH C

19、HECK OPTION;接下來插入記錄：INSERT INTO CS_XSVALUES(081255, 李牧, 計算機, 1, 1990-10-14, 50, NULL, NULL);6.UPDATE CS_XS SET 總學分 = 總學分+ 8;7.UPDATE CS_KC SET 成績=90WHERE 學號=081101 AND 課程號=101;8.DELETE FROM CS_XS WHERE 性別 = 0;9.ALTER VIEW CS_XSASSELECT 學號,姓名,總學分 FROM XS WHERE 專業(yè)名 = 計算機;第七章索引一略二1.CREATE INDEX XH_XS O

20、N XS(學號(5) ASC);2.CREATE INDEX XSKC_IN ON XS_KC(學號,課程號);3.ALTER TABLE XSADD INDEX XS_XM (姓名);4.ALTER TABLE XSADD PRIMARY KEY(學號);5.CREATE TABLE XS_KC(學號 CHAR(6) NOT NULL,課程號 CHAR(3) NOT NULL,成績 TINYINT(1),學分 TINYINT(1),PRIMARY KEY(學號,課程號),INDEX CJ(成績);6.ALTER TABLE XS DROP PRIMARY KEY;第八章 數(shù)據(jù)庫編程一略二1.

21、SET var1=1, user2=2, user3=3;2.SELECT LEFT(課程名, 3) FROM KC;3.SELECT SUBSTRING(姓名, 1,1) AS 姓, SUBSTRING(姓名, 2, LENGTH(姓名)1) AS 名 FROM XS;4.SELECT 學號,姓名, YEAR(NOW()YEAR(出生日期) AS 年齡 FROM XS WHERE 性別=0;5.SELECT 姓名, IF(性別=0, 女, 男) AS 性別, 專業(yè)名 FROM XS WHERE 姓名 LIKE _;6.DELIMITER $CREATE PROCEDURE DELETE_ST

22、UDENT(IN XH CHAR(6)BEGINDELETE FROM XS WHERE 學號=XH;END $DELIMITER ;7.DELIMITER $CREATE PROCEDURE XSCJ.DO_UPDATE(IN XH CHAR(6), IN KCM CHAR(16)BEGINDECLARE KCH CHAR(3);DECLARE XF TINYINT;DECLARE CJ TINYINT;SELECT 課程號, 學分 INTO KCH, XF FROM KC WHERE 課程名=KCM;SELECT 成績 INTO CJ FROM XS_KC WHERE 學號=XH AND

23、課程號=KCH;IF CJ60 THENUPDATE XS_KC SET 學分=0 WHERE 學號=XH AND 課程號=KCH;ELSE UPDATE XS_KC SET 學分=XF WHERE 學號=XH AND 課程號=KCH;END IF;END$DELIMITER ;8.DELIMITER $CREATE FUNCTION NUM_OF_XS()RETURNS INTEGERBEGINRETURN (SELECT COUNT(*) FROM XS);END$DELIMITER ;9.DELIMITER $CREATE TRIGGER XS_DELETE AFTER DELETEON

24、 XS FOR EACH ROWBEGINDELETE FROM XS_KC WHERE 學號=OLD.學號;END$DELIMITER ;10.DELIMITER $CREATE TRIGGER XS_KC_UPDATE BEFORE UPDATEON XS_KC FOR EACH ROWBEGINDECLARE XF INT(1);SELECT 學分 INTO XF FROM KC WHERE 課程號=NEW.課程號;IF NEW.成績=60 THENUPDATE XS SET 總學分=總學分+XF WHERE 學號=NEW.學號;END IF;END$DELIMITER ;12.首先，定義存儲過程：DELIMITER $CREATE PROCEDURE CHANGES()BEGINREPLACE INTO STUDENT SELECT * FROM XS