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

«返回课程汇总页面

【实验手册版本】

当前版本号v20200615

综合练习2

【实验名称】

综合练习2

【实验目的】

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

【实验数据说明】

1. 请从下面链接下载练习数据：

https://pan.baidu.com/s/1Q3kheymJorKGTxU4pChd3w#提取码kwvi

2. stu.sql 中包含了3个表，分别是学生信息表stu，家长信息表parents,学生成绩表score。

• 学生信息表stu

• 家长信息表parents

• 学生成绩表score

【实验环境】

• 操作系统：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;

3. 运行stu.sql 脚本，创建三个表格stu、score和parents。

【实验步骤】

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

1. 按班级统计男女人数

• 关键知识点：count，group by，order by

• 展示要求如下表所示：

• 期望结果：

2. 统计三1班和三2班语文成绩90分或以上的人数

• 关键知识点：join，count，group by

• 展示要求如下表所示：

• 期望结果：

3. 查找家长姓名为李迁迁的学生姓名和班级

• 关键知识点：表连接，where 条件

• 展示要求如下表所示：

• 期望结果：

4. 查找三1班和三2班学生该次考试（testid=1011)的总分的平均分

• 关键知识点：表连接，group by，avg函数，format函数

• 展示要求如下表所示：

• 期望结果：

5. 查找三2班该次考试（testid=1011)总分前5名的学生

• 关键知识点：表连接，order by，limit

• 展示要求如下表所示：

• 期望结果：

6. 按表格格式呈现三1班语文科成绩各个分数层人数，包括90(含)以上，,75(含)-90、60(含)-75。

• 关键知识点：join，count，union，case语句

• 展示要求如下表所示：

• 期望结果：

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

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

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

• 展示要求如下表所示：

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

• 期望结果：

【常见错误和解答】

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

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

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

答：应该使用单引号'。

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

答：等值判断应该使用1个=，不等于可以使用!=或<>。

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

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