文理学院数据库技术应用实验报告8
| 实验名称 | 数据聚合查询和分组查询 | 实验日期 | 2024年11月1日 |
|---|---|---|---|
| 课程名称 | 数据库技术应用 | 实验项目 | 数据聚合查询和分组查询 |
一、实验目的
- 聚合函数（
max、min、avg、sum、count） - 分组查询（
group by子句、having子句）（重点）
二、实验原理
- 聚合函数：
max 最大值
min 最小值
avg 平均值
sum 求和
count 数目
- 分组查询语句：
（1） group by子句
select 列名1, 列名2, ……, 聚合函数 from 表名group by 列名1, 列名2, ……
强调：凡是在查询时遇到普通列与聚合函数同时一起查询时，必须用group by子句对普通列进行分组汇总，否则就会数据不正确。
（2） having的用法
select 列名1, 列名2, ……, 聚合函数 from 表名
where 普通条件
group by 列名1, 列名2, ……
having 分组后条件
强调：having与where两种条件的区别是：where限定普通的条件，而having限定那些必须分组后才能看到的条件。
三、实验设备、材料
安装了MySQL和navicat的主机
四、 实验步骤
请完成以下表数据查询练习：
（1）找到“stumanagement”（学生成绩管理）数据库：
- 查询学生信息表中的学生总人数。（提示：要用到聚合函数count）
SELECT COUNT(*) AS 学生总人数
FROM 学生信息;
-
查询选修课表中学生的最高分和最低分。（提示：要用到聚合函数max、min）
SELECT MAX(成绩) AS 最高分,MIN(成绩) AS 最低分 FROM 选修课; -
查询选修课表中选修了101课程的学生平均成绩。（提示：要用到聚合函数avg）
SELECT AVG(成绩) as 平均成绩 FROM 选修课 WHERE 课程号='101' -
查询选修课表中选了课程的学生总人数。（提示：要用到聚合函数count）
SELECT COUNT(学号) AS 学生总人数 FROM 选修课; -
查询学生信息表中各个专业的学生人数。（提示：要用到聚合函数count以及group by子句进行分组）
SELECT 专业,COUNT(*) as 学生人数 FROM 学生信息 GROUP BY 专业; -
查询选修课表中各门课程的平均成绩和选修了该课程的人数。（提示：要用到聚合函数avg、count以及group by子句进行分组）
SELECT 课程号,ROUND(AVG(成绩),2) AS 平均成绩,ROUND(COUNT(*),2) AS 人数
FROM 选修课
GROUP BY 课程号;
（2）找到“staff”（职工管理）数据库：
-
在工资表（字段有：职工编号，基本工资，奖金，实发工资）中计算出实发工资，并输出工资单。
-- 更新实发工资 UPDATE 工资 SET 实发工资 = 基本工资 + 奖金; SELECT 职工编号, 基本工资, 奖金, 实发工资 FROM 工资; -
查询职工信息表中哪些人是主管，并输出员工信息。
SELECT * FROM 职工信息
WHERE 职务 = '主管';
- 查询部门信息表中部门名称带“务”字的部门信息。
WHERE 部门名称 LIKE '%务%';
-
查询职工信息表中1997年出生的职工信息。
SELECT * FROM 职工信息 WHERE YEAR(出生日期) = 1997; -
在部门信息表中按照部门名称排序。
SELECT * FROM 部门信息 ORDER BY 部门名称 DESC;
-
查询职工信息表中今年超过20岁的员工，输出姓名、年龄。
SELECT 姓名,TIMESTAMPDIFF(YEAR, 出生日期, CURDATE()) AS '年龄' FROM 职工信息 WHERE TIMESTAMPDIFF(YEAR, 出生日期, CURDATE()) > 20; -
在工资表中查询最高工资、最低工资和平均工资（按照实发工资计算），字段名有职工编号，基本工资，奖金，实发工资。
SELECT MAX(实发工资) AS 最高工资,MIN(实发工资) AS 最低工资,AVG(实发工资) AS 平均工资
FROM 工资;
- 通过职工信息表查询每个部门有多少人。
SELECT 部门编号,COUNT(*) AS 人数 FROM 职工信息 GROUP BY 部门编号;
-
将职工信息表中的性别是1的替换为男，是0的替换为女，并输出员工信息。
SELECT 职工编号,姓名, CASE WHEN 性别 = 1 THEN '男' ELSE '女'END as 性别,出生日期,职务,部门编号 FROM 职工信息; -
查询职工信息表中男女分别有多少人。
SELECT SUM(CASE WHEN 性别 = 1 THEN 1 ELSE 0 END) AS '男性人数',SUM(CASE WHEN 性别 = 0 THEN 1 ELSE 0 END) AS '女性人数' FROM 职工信息;
（3）附stumanagement和staff数据库源码
stumanagement学生成绩管理数据库源码
CREATE DATABASE IF NOT EXISTS `stumanagement` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `stumanagement`;CREATE TABLE IF NOT EXISTS `学生信息` (`学号` char(6) NOT NULL DEFAULT '',`姓名` varchar(8) NOT NULL,`生日` date NOT NULL,`专业` varchar(10) NOT NULL,`性别` tinyint(4) NOT NULL,`总学分` int(11) NOT NULL,`备注` text,PRIMARY KEY (`学号`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `学生信息` (`学号`, `姓名`, `生日`, `专业`, `性别`, `总学分`, `备注`) VALUES
('001101', '王林', '1999-02-10', '软件技术', 1, 50, ''),
('001102', '程明', '1998-02-01', '软件技术', 1, 50, ''),
('001103', '王燕', '1997-10-06', '软件技术', 0, 50, ''),
('001104', '韦延平', '1999-08-26', '软件技术', 1, 50, ''),
('001106', '李方方', '1999-11-20', '软件技术', 1, 50, ''),
('001107', '李明', '1999-05-01', '网络技术', 1, 54, '提前修完《计算机基础》，获得学分'),
('001108', '林一凡', '1997-08-05', '网络技术', 1, 52, '已提前修完一门课'),
('001109', '张强民', '1996-08-11', '网络技术', 1, 50, ''),
('001110', '张微', '1998-07-22', '网络技术', 0, 50, '三好生'),
('001111', '赵琳', '1998-03-18', '网络技术', 0, 50, ''),
('001113', '严红', '1996-12-25', '网络技术', 0, 48, '有一门功课不及格，待补考'),
('001201', '王敏', '1995-06-10', '电子商务', 1, 42, ''),
('001202', '王林', '1996-01-29', '电子商务', 1, 40, '有一门功课不及格，待补考'),
('001203', '王玉民', '1998-03-26', '电子商务', 1, 42, ''),
('001204', '马琳琳', '1995-02-10', '电子商务', 0, 42, ''),
('001206', '李纪', '1996-09-20', '电子商务', 1, 42, ''),
('001210', '李宏庆', '1996-05-01', '电子商务', 1, 44, '已提前修完一门课'),
('001216', '孙祥新', '1995-03-09', '信息管理', 1, 42, ''),
('001218', '孙雁', '1996-11-09', '信息管理', 1, 42, ''),
('001220', '吴微花', '1998-03-18', '信息管理', 0, 42, ''),
('001221', '刘艳敏', '1999-01-30', '信息管理', 0, 50, '转专业学习');CREATE TABLE IF NOT EXISTS `课程信息` (`课程号` char(3) NOT NULL DEFAULT '',`课程名` varchar(16) NOT NULL,`学期` int(11) NOT NULL,`学时` int(11) NOT NULL,PRIMARY KEY (`课程号`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `课程信息` (`课程号`, `课程名`, `学期`, `学时`) VALUES
('101', '计算机基础', 1, 80),
('102', '程序设计与语言', 2, 68),
('206', '可视化编程技术', 3, 68),
('208', 'JAVA面向对象编程', 4, 68),
('209', '操作系统', 3, 68),
('210', '计算机原理', 5, 85),
('212', 'MySQL数据库', 5, 68),
('301', '计算机网络', 3, 51),
('302', '软件工程', 4, 51);CREATE TABLE IF NOT EXISTS `选修课` (`编号` int(11) NOT NULL AUTO_INCREMENT,`学号` char(6) DEFAULT NULL,`课程号` char(3) DEFAULT NULL,`成绩` decimal(5,2) DEFAULT NULL,PRIMARY KEY (`编号`),KEY `fk_学生选修` (`学号`),KEY `fk_课程选修` (`课程号`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=43 ;INSERT INTO `选修课` (`编号`, `学号`, `课程号`, `成绩`) VALUES
(1, '001101', '101', '80.00'),
(2, '001101', '102', '78.00'),
(3, '001101', '206', '76.00'),
(4, '001102', '102', '78.00'),
(5, '001102', '206', '78.00'),
(6, '001103', '101', '62.00'),
(7, '001103', '102', '70.00'),
(8, '001103', '206', '81.00'),
(9, '001104', '101', '90.00'),
(10, '001104', '102', '84.00'),
(11, '001104', '206', '65.00'),
(12, '001106', '101', '65.00'),
(13, '001106', '102', '71.00'),
(14, '001106', '206', '80.00'),
(15, '001107', '101', '78.00'),
(16, '001107', '102', '80.00'),
(17, '001107', '206', '68.00'),
(18, '001108', '101', '85.00'),
(19, '001108', '102', '80.00'),
(20, '001108', '206', '87.00'),
(21, '001109', '101', '66.00'),
(22, '001109', '102', '83.00'),
(23, '001109', '206', '70.00'),
(24, '001110', '101', '95.00'),
(25, '001110', '102', '90.00'),
(26, '001110', '206', '89.00'),
(27, '001111', '101', '91.00'),
(28, '001111', '102', '70.00'),
(29, '001111', '206', '76.00'),
(30, '001113', '101', '63.00'),
(31, '001113', '102', '79.00'),
(32, '001113', '206', '60.00'),
(33, '001201', '101', '80.00'),
(34, '001202', '101', '65.00'),
(35, '001203', '101', '87.00'),
(36, '001204', '101', '91.00'),
(37, '001210', '101', '76.00'),
(38, '001216', '101', '81.00'),
(39, '001218', '101', '70.00'),
(40, '001220', '101', '82.00'),
(41, '001221', '101', '76.00'),
(42, '001221', '101', '90.00');ALTER TABLE `选修课`ADD CONSTRAINT `fk_课程选修` FOREIGN KEY (`课程号`) REFERENCES `课程信息` (`课程号`),ADD CONSTRAINT `fk_学生选修` FOREIGN KEY (`学号`) REFERENCES `学生信息` (`学号`);
- staff 职工管理数据库源码
CREATE DATABASE IF NOT EXISTS `staff` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `staff`;CREATE TABLE IF NOT EXISTS `工资` (`职工编号` char(5) NOT NULL,`基本工资` decimal(8,2) NOT NULL,`奖金` decimal(8,2) NOT NULL,`实发工资` decimal(8,2) DEFAULT NULL,PRIMARY KEY (`职工编号`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `工资` (`职工编号`, `基本工资`, `奖金`, `实发工资`) VALUES
('10001', '3000.00', '2500.00', NULL),
('10002', '3500.00', '2500.00', NULL),
('10003', '4000.00', '1500.00', NULL),
('20001', '2800.00', '500.00', NULL),
('20002', '2850.00', '700.00', NULL),
('20003', '3100.00', '500.00', NULL),
('30001', '2500.00', '900.00', NULL),
('30002', '3000.00', '450.00', NULL),
('40001', '2500.00', '560.00', NULL),
('40002', '2500.00', '980.00', NULL),
('50001', '4000.00', '1050.00', NULL),
('50002', '4000.00', '750.00', NULL);CREATE TABLE IF NOT EXISTS `职工信息` (`职工编号` char(5) NOT NULL DEFAULT '',`姓名` varchar(10) NOT NULL,`性别` tinyint(11) NOT NULL,`出生日期` date NOT NULL,`职务` varchar(10) NOT NULL,`部门编号` char(3) NOT NULL,PRIMARY KEY (`职工编号`),KEY `fk_职工部门` (`部门编号`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `职工信息` (`职工编号`, `姓名`, `性别`, `出生日期`, `职务`, `部门编号`) VALUES
('10001', '郭靖', 1, '1996-04-26', '主管', '001'),
('10002', '张无忌', 1, '1997-03-04', '技术员', '001'),
('10003', '萧峰', 1, '1995-10-31', '工程师', '001'),
('20001', '黄蓉', 0, '1997-06-20', '主管', '002'),
('20002', '任盈盈', 0, '1997-12-09', '专员', '002'),
('20003', '东方不败', 1, '1996-05-12', '专员', '002'),
('30001', '李莫愁', 0, '1993-09-22', '主管', '003'),
('30002', '杨康', 1, '1996-01-28', '助理', '003'),
('40001', '小龙女', 0, '1997-11-06', '主管', '004'),
('40002', '杨过', 1, '1998-02-11', '会计', '004'),
('50001', '韦小宝', 1, '1997-10-12', '主管', '005'),
('50002', '段誉', 1, '1996-05-30', '业务员', '005');CREATE TABLE IF NOT EXISTS `部门信息` (`部门编号` char(3) NOT NULL,`部门名称` varchar(10) NOT NULL,`部门简介` text,PRIMARY KEY (`部门编号`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `部门信息` (`部门编号`, `部门名称`, `部门简介`) VALUES
('001', '技术部', '专门维护整个公司的网站、设备和网络，提供相关技术支持'),
('002', '商务部', '市场营销和商务谈判'),
('003', '人事部', '人事管理，职工培训，考勤核算，招聘'),
('004', '财务部', '工资核算，公司账务管理'),
('005', '广告部', '对外宣传公司');ALTER TABLE `工资`ADD CONSTRAINT `fk_职工工资` FOREIGN KEY (`职工编号`) REFERENCES `职工信息` (`职工编号`);ALTER TABLE `职工信息`ADD CONSTRAINT `fk_职工部门` FOREIGN KEY (`部门编号`) REFERENCES `部门信息` (`部门编号`);