商务智能方法与应用综合实验2

2020-06-15
4分钟阅读时长

«返回课程汇总页面

【实验手册版本】

当前版本号v20200615

版本修改说明
v20200615初始化版本

综合练习2

【实验名称】

综合练习2

【实验目的】

针对教育应用场景,练习使用关系数据库分析解决问题

【实验数据说明】

  1. 请从下面链接下载练习数据:
https://pan.baidu.com/s/1Q3kheymJorKGTxU4pChd3w#提取码kwvi
  1. stu.sql 中包含了3个表,分别是学生信息表stu家长信息表parents,学生成绩表score
  • 学生信息表stu
备注
id主键
stuid学号
name姓名
gender性别
clazz班级
  • 家长信息表parents
备注
id主键
stuid学号
fname父亲姓名
fphone父亲手机号
fname母亲姓名
fphone母亲手机号
  • 学生成绩表score
备注
id主键
testid考试ID
stuid学号
chn语文成绩
math数学成绩
eng英语成绩
tot总分

【实验环境】

  • 操作系统:Windows
  • MySQL
  • MySQL Workbench/Navicat/HeidiSQL

【实验原理】

  • SQL 常用语法模板
select 表名1.列名1 as 别名1,表名2.列名2 as 别名2, ...
from 表名1 表别名1,表名2 表别名2
where 条件语句
group by 列名
order by 列名 asc|desc

例子:

SELECT s.stuid AS 学号,s.name AS '姓名',sc.chn AS 语文成绩  FROM stu s,score sc WHERE s.stuid=sc.stuid
  • SQL 连接语法模板

(1)连接可以分为内连接(inner join),左外连接(left join ),右外连接(right join)

select * from TableA a join TableB b on a.id=b.id
  • 语法说明

(1)`符号(Esc键下面)通常可以括列名和表名,如果列名或表名和 SQL 语句里面的关键字有冲突可以使用。

(2)单引号 ' 用来包围字符串。

【实验前准备】

  1. 运行 MySQL 数据库,并打开MySQL客户端(MySQL Workbench/Navicat/HeidiSQL)使用 root 用户连接。

  2. 创建一个stu数据库。

create database stu;
use stu;
  1. 运行stu.sql 脚本,创建三个表格stu、score和parents。

【实验步骤】

请按照以下要求完成SQL语句。

  1. 按班级统计男女人数
  • 关键知识点:count,group by,order by

  • 展示要求如下表所示:

班级性别人数
三1班
三1班
三2班
三2班
  • 期望结果:


  1. 统计三1班和三2班语文成绩90分或以上的人数
  • 关键知识点:join,count,group by

  • 展示要求如下表所示:

班级人数
三1班
三2班
  • 期望结果:


  1. 查找家长姓名为李迁迁的学生姓名和班级
  • 关键知识点:表连接,where 条件

  • 展示要求如下表所示:

姓名班级父亲姓名母亲姓名
  • 期望结果:


  1. 查找三1班和三2班学生该次考试(testid=1011)的总分的平均分
  • 关键知识点:表连接,group by,avg函数,format函数

  • 展示要求如下表所示:

班级平均分
三1班
三2班
  • 期望结果:


  1. 查找三2班该次考试(testid=1011)总分前5名的学生
  • 关键知识点:表连接,order by,limit

  • 展示要求如下表所示:

姓名班级总分
三2班
三2班
三2班
三2班
三2班
  • 期望结果:


  1. 按表格格式呈现三1班语文科成绩各个分数层人数,包括90(含)以上,,75(含)-90、60(含)-75。
  • 关键知识点:join,count,union,case语句

  • 展示要求如下表所示:

分数层人数
90以上
75以下
75-90
  • 期望结果:


  1. 为了方便给学生家长发送成绩短信,需要构造以下内容进行个性化发送,短信模板如下:

尊敬的[学生姓名]家长,你好。[学生姓名]期末成绩为语文[语文成绩],数学[数学成绩],英语[英语成绩],总分[总分成绩]。

  • 关键知识点:join,字符连接concat,union,子查询

  • 展示要求如下表所示:

注:这里注意学生如果有2位家长留有手机号,则2位都需要发送。

学生姓名班级家长姓名家长手机号短信内容
柏倩利三1班柏松山13763410385尊敬的柏倩利家长,你好。柏倩利期末成绩为语文69,数学64,英语73,总分206。
遇济深三2班遇金龙13827221156尊敬的遇济深家长,你好。遇济深期末成绩为语文78,数学71,英语96,总分245。
  • 期望结果:

【常见错误和解答】

  • 错误1:SQL 语法结构不清晰,基本的select、from、where等关键字顺序和位置不清楚。

答:参考上面“SQL 常用语法模板”。

  • 错误2:使用双引号"来包围字符串。

答:应该使用单引号'。

  • 错误3:使用2个等号“==”来进行等值判断条件。

答:等值判断应该使用1个=,不等于可以使用!=<>

  • 错误4:使用聚合函数如count,sum等没有配合使用group by进行分组。

答:应该要根据分组的列来使用group by。