广青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）复制到姓名文件夹并且压缩后提交教师机。