《数据库应用技术》期末实训方案
适用班级 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)复制到姓名文件夹并且压缩后提交教师机。