廣青mysql實訓1

《資料庫應用技術》期末實訓方案
適用班級 19級軟體技術2班 期末實訓 開卷 時長 90分鐘

注意事項：
①　以“姓名”命名新建一個檔案夾，將實訓結果檔案保存到該檔案夾；
②　將有自己姓名的檔案夾”壓縮后提交發到教師機，
第一部分 資料管理部分
一、實訓環境
MySQL資料庫系統+Navicat工具

二、實訓目標
1、掌握用戶管理方法
2、掌握資料操縱方法
3、掌握資料高級查詢
4、掌握資料庫檔案生成方法

三、實訓任務
（一）資料定義
1、使用Navcat工具，連接MySQL資料庫，連接名“myconn”
注意：用“root”登錄，密碼：123456，

2、創建新的資料庫“studb，字符集“UTF-8 Unicode”，

3、給“studb”資料庫創建下列兩張資料表：
欄位名稱 資料型別（欄位長度） 是否允許為空 屬性 說明
StudentID char(12) 否 主鍵 學號
StudentName varchar(20) 否 姓名
StudentGender char(4) 是 性別
Birthday date 是 出生日期
Major Varchar(30) 是 專業
StudentPhone char(11) 是 電話
Student表

欄位名稱 資料型別（欄位長度） 是否允許為空 屬性 說明
StudentID char(12) 否 學號
CourseID char(4) 否???? 課程編號
Score int 是 默認是0 分數
Grade表

CREATE TABLE IF NOT EXISTS `Student` (
   `StudentID` CHAR(12) NOT NULL  COMMENT '學號',
   `StudentName` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
   `StudentGender` CHAR(4)  DEFAULT '女' COMMENT '性別',
   `Birthday` DATE DEFAULT NULL COMMENT '出生日期',
   `Major` VARCHAR(30) DEFAULT NULL COMMENT '專業',
   `StudentPhone` CHAR(11) DEFAULT NULL COMMENT '電話',
   PRIMARY KEY(`StudentID`)   
)ENGINE=INNODB DEFAULT CHARSET=utf8


SELECT * FROM Student;


CREATE TABLE IF NOT EXISTS `Grade`(
   `StudentID` CHAR(12) NOT NULL  COMMENT '學號',
   `CourseID` CHAR(4) NOT NULL COMMENT '課程編號',
   `Score` INT DEFAULT 0 COMMENT '分數'

)ENGINE=INNODB DEFAULT CHARSET=utf8

DROP TABLE Grade;
SELECT * FROM Grade;

4、用下圖的視圖設計面板，新建一個“查看學生平均成績”的視圖，??????
要求：（1）包括專業、學號、姓名、平均分三列，其中平均分需要統計；
（2）按照專業排序，

CREATE VIEW `查看學生平均成績`
AS
   SELECT s.Major , s.StudentID, s.StudentName , AVG(Score) AS 平均分
 FROM Student s,Grade c
 WHERE s.StudentID=c.StudentID
 GROUP BY s.Major
 -- order by 平均分 desc

（二）資料操縱

5、在學生資訊表STUDENT中，用insert陳述句添加入下列三位學生資料，寫出添加資料SQL陳述句，
(‘201422010003’,‘呂新’,‘男’,‘1997-07-22’,‘軟體工程’,‘88888’);
(‘201422010004’,‘周強’,‘男’,‘1997-09-16’,‘軟體工程’,‘88888’);
(‘201422010005’,‘王亞’,‘女’,‘1997-04-26’,‘軟體工程’,NULL);

INSERT INTO `Student` VALUES('201422010003','呂新','男','1997-07-22','軟體工程','88888');
INSERT INTO `Student` VALUES('201422010004','周強','男','1997-09-16','軟體工程','88888');
INSERT INTO `Student` VALUES('201422010005','王亞','女','1997-04-26','軟體工程',NULL);
INSERT INTO `Student` VALUES('201422010006','王非','女','1997-04-26','軟體工程',NULL);
INSERT INTO `Student` VALUES('201422010007','王非','女','1997-04-26','外語系',NULL);

6、在學生資訊表STUDENT中，學生“趙剛”的原有StudentPhone列資料為空，現需要修改為“156923”，

UPDATE `Student` SET `StudentPhone` = '156923' WHERE `StudentName` = '趙剛' ;

7、在STUDENT學生資訊表中，要洗掉姓名為 “肖玲”的學生資料，

DELETE FROM `Student` WHERE `StudentName` = '肖玲';

8、若要從STUDENT表中獲取專業為“軟體工程”、性別為“男”的學生資料，撰寫其資料查詢SQL陳述句，

SELECT * FROM `Student` WHERE `Major` = '軟體工程' AND `StudentGender` = '男';

9、若要從STUDENT表中查詢出生日期在“1997年”的學生資料，撰寫查詢SQL陳述句，

SELECT * FROM `Student` WHERE `Birthday` LIKE '1997%';

10、若要從STUDENT表中查詢“王”姓的學生資料，撰寫其資料查詢SQL陳述句，

SELECT * FROM `Student` WHERE `StudentName` LIKE '王%';

11、若要從STUDENT表中查詢手機號為“88888”的學生資料，撰寫其資料查詢SQL陳述句，

SELECT * FROM `Student` WHERE `StudentPhone` = '88888';

12、若要從STUDENT表中獲取專業為“軟體工程”，性別為“男”的學生部分列（學號、姓名、性別、專業）資料，撰寫其資料查詢SQL陳述句，

SELECT `StudentID`,`StudentName` ,`StudentGender`,`Major` FROM `Student` WHERE `Major` = '軟體工程' AND `StudentGender` = '男';

13、若要將STUDENT表查詢資料按學生出生日期升序輸出，撰寫其資料查詢SQL陳述句，

SELECT * FROM `Student` ORDER BY  `Birthday`  ASC;

14、若要從STUDENT表中查詢學生資料，首先按出專業升序排列，然后按出生日期降序排列，撰寫其資料查詢SQL陳述句，

SELECT * FROM `Student` ORDER BY `Major` ASC,`Birthday` DESC;

15、若要分專業統計STUDENT表中的學生人數，則在SELECT陳述句中需使用GROUP BY分組子句完成統計，撰寫查詢SQL陳述句，

SELECT  `Major`,COUNT(*) AS COUNT FROM `Student`
GROUP BY  `Major`;

16、在統計各專業人數時，限定只顯示人數多于4的專業人數資訊，這時需要在分組統計SQL陳述句中加入HAVING限定條件，撰寫查詢SQL陳述句可重新撰寫，

SELECT  `Major`,COUNT(*) AS COUNT FROM `Student`
GROUP BY  `Major`
HAVING COUNT>=4;

17、若要分專業統計STUDENT表中男生人數，但限定只顯示人數大于2的人數，撰寫查詢SQL陳述句，

SELECT  `Major`,COUNT(*) AS COUNT FROM `Student`
WHERE `StudentGender` = '男'
GROUP BY  `Major`
HAVING COUNT>=2;

（三）資料維護
1）新建用戶，用戶名是“user1”，密碼是“1234”，服務器權限是“show database”，資料庫權限是“studb”資料庫的“select”權限，
2）給studb資料庫生成SQL檔案，其名稱為：studb.sql;
3）將studb資料表匯出到名為studb.xlss的Execl檔案，

注意：將上述兩個檔案（studb.sql與studb.xlss）復制到姓名檔案夾并且壓縮后提交教師機，