文理学院数据库技术应用实验报告8

server/2024/11/28 7:29:34/

文理学院数据库技术应用实验报告8

实验名称数据聚合查询和分组查询实验日期2024年11月1日
课程名称数据库技术应用实验项目数据聚合查询和分组查询

一、实验目的

  1. 聚合函数（max、min、avg、sum、count）
  2. 分组查询（group by子句、having子句）（重点）

二、实验原理

  1. 聚合函数：

max 最大值

min 最小值

avg 平均值

sum 求和

count 数目

  1. 分组查询语句：

（1） group by子句

sql">select 列名1, 列名2, ……, 聚合函数 from 表名group by 列名1, 列名2, ……

强调：凡是在查询时遇到普通列与聚合函数同时一起查询时，必须用group by子句对普通列进行分组汇总，否则就会数据不正确。

（2） having的用法

sql">select 列名1, 列名2, ……, 聚合函数 from 表名
where 普通条件
group by 列名1, 列名2, ……
having 分组后条件

强调：having与where两种条件的区别是：where限定普通的条件，而having限定那些必须分组后才能看到的条件。

三、实验设备、材料

安装了MySQL和navicat的主机

四、 实验步骤

请完成以下表数据查询练习：

（1）找到“stumanagement”（学生成绩管理）数据库：

  1. 查询学生信息表中的学生总人数。（提示：要用到聚合函数count）
sql">SELECT COUNT(*) AS 学生总人数
FROM 学生信息;

  1. 查询选修课表中学生的最高分和最低分。（提示：要用到聚合函数max、min）

    sql">SELECT MAX(成绩) AS 最高分,MIN(成绩) AS 最低分
FROM 选修课;

  2. 查询选修课表中选修了101课程的学生平均成绩。（提示：要用到聚合函数avg）

    sql">SELECT  AVG(成绩) as 平均成绩 FROM  选修课 WHERE 课程号='101'

  3. 查询选修课表中选了课程的学生总人数。（提示：要用到聚合函数count）

    sql">SELECT COUNT(学号) AS 学生总人数
FROM 选修课;

  4. 查询学生信息表中各个专业的学生人数。（提示：要用到聚合函数count以及group by子句进行分组）

    sql">SELECT 专业,COUNT(*) as 学生人数
FROM 学生信息
GROUP BY 专业;

  5. 查询选修课表中各门课程的平均成绩和选修了该课程的人数。（提示：要用到聚合函数avg、count以及group by子句进行分组）

sql">SELECT 课程号,ROUND(AVG(成绩),2) AS 平均成绩,ROUND(COUNT(*),2) AS 人数
FROM 选修课
GROUP BY 课程号;

（2）找到“staff”（职工管理）数据库：

  1. 在工资表（字段有：职工编号，基本工资，奖金，实发工资）中计算出实发工资，并输出工资单。

    sql">-- 更新实发工资
UPDATE 工资
SET 实发工资 = 基本工资 + 奖金;
SELECT 职工编号, 基本工资, 奖金, 实发工资 FROM 工资;

  2. 查询职工信息表中哪些人是主管，并输出员工信息。

sql">SELECT * FROM 职工信息
WHERE 职务 = '主管';
  1. 查询部门信息表中部门名称带“务”字的部门信息。
sql">WHERE 部门名称 LIKE '%务%';

  1. 查询职工信息表中1997年出生的职工信息。

    sql">SELECT * FROM 职工信息 
WHERE YEAR(出生日期) = 1997;

  2. 在部门信息表中按照部门名称排序。

sql">SELECT * FROM 部门信息 ORDER BY  部门名称 DESC;

  1. 查询职工信息表中今年超过20岁的员工，输出姓名、年龄。

    sql">SELECT 姓名,TIMESTAMPDIFF(YEAR, 出生日期, CURDATE()) AS '年龄'
FROM 职工信息
WHERE TIMESTAMPDIFF(YEAR, 出生日期, CURDATE()) > 20;

  2. 在工资表中查询最高工资、最低工资和平均工资（按照实发工资计算），字段名有职工编号，基本工资，奖金，实发工资。

sql">SELECT MAX(实发工资) AS 最高工资,MIN(实发工资) AS 最低工资,AVG(实发工资) AS 平均工资
FROM 工资;
  1. 通过职工信息表查询每个部门有多少人。
sql">SELECT 部门编号,COUNT(*) AS 人数 FROM 职工信息 GROUP BY 部门编号;

  1. 将职工信息表中的性别是1的替换为男，是0的替换为女，并输出员工信息。

    sql">SELECT 职工编号,姓名,  CASE WHEN 性别 = 1 THEN '男'  ELSE '女'END as 性别,出生日期,职务,部门编号
FROM 职工信息;

  2. 查询职工信息表中男女分别有多少人。

    sql">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数据库源码

  1. stumanagement 学生成绩管理数据库源码
sql">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 `学生信息` (`学号`);
  1. staff 职工管理数据库源码
sql">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 `部门信息` (`部门编号`);

http://www.ppmy.cn/server/137717.html

相关文章

Java 多线程（八）—— 锁策略，synchronized 的优化，JVM 与编译器的锁优化，ReentrantLock，CAS

Java 多线程（八）—— 锁策略，synchronized 的优化，JVM 与编译器的锁优化，ReentrantLock，CAS

前言 本文为 Java 面试小八股，一句话，理解性记忆，不能理解就死背吧。 锁策略 悲观锁与乐观锁 悲观锁和乐观锁是锁的特性，并不是特指某个具体的锁。 我们知道在多线程中，锁是会被竞争的，悲观锁就是指锁…
阅读更多...
FPGA与ARM处理器在嵌入式系统中的融合设计！！！

FPGA与ARM处理器在嵌入式系统中的融合设计！！！

FPGA（现场可编程门阵列）与ARM处理器在嵌入式系统中的融合设计是一种常见的做法，这种设计结合了FPGA的并行处理能力和高度可编程性与ARM处理器的通用计算能力。以下是FPGA与ARM处理器融合设计的概述： 1. 设计动机 并行处理&#…
阅读更多...
.net core 接口,动态接收各类型请求的参数

.net core 接口,动态接收各类型请求的参数

[HttpPost] public async Task<IActionResult> testpost([FromForm] object info) { //Postman工具测试结果&#xff1a; //FromBody,Postman的body只有rawjson时才进的来 //参数为空时&#xff0c;Body(form-data、x-www-form-urlencoded)解析到的数据也有所…
阅读更多...
高考相关 APP 案例分享

高考相关 APP 案例分享

文章首发于https://qdgithub.com/article/2032 一、核心内容 &#xff08;一&#xff09;高考相关 APP 案例 圈友朱康分享高考相关的 APP。提到猿题库&#xff0c;其主要功能有练习册和猿辅导&#xff0c;都是收费的。猿题库出题给学生练习&#xff0c;将易错的总结起来出练习…
阅读更多...
【传知代码】基于标签相关性的多标签学习

【传知代码】基于标签相关性的多标签学习

&#x1f351;个人主页&#xff1a;Jupiter. &#x1f680; 所属专栏&#xff1a;传知代码 欢迎大家点赞收藏评论&#x1f60a; 目录 论文概述论文贡献算法流程实验结果核心代码复现main.py文件multi_label_learn.py文件 使用方法导入本地数据集构建多标签学习分类模型运行模型…
阅读更多...
Excel-多表数据查找匹配(VLOOKUP)

Excel-多表数据查找匹配(VLOOKUP)

&#x1f496;简介 Excel的VLOOKUP函数同样可以用来查找表格中的数据。VLOOKUP&#xff08;垂直查找&#xff09;是一个非常有用的函数&#xff0c;它可以在一个表格或数据表的一列中搜索特定的值&#xff0c;并返回与之在同一行上的另一列中的值。 &#x1f4d6;环境 WPS …
阅读更多...
造纸粉体分散机、改性包覆机、改性打散机

造纸粉体分散机、改性包覆机、改性打散机

包覆改性机在造纸填料中的应用是近年来造纸行业技术创新的一个重要方向。通过包覆改性&#xff0c;可以改善填料的表面性质&#xff0c;提升其在纸张中的留着率和分布均匀性&#xff0c;进而增强纸张的性能&#xff0c;降低生产成本。以下是包覆改性机在造纸填料中的具体应用及…
阅读更多...
java开发如何在单例模式下通过锁机制防止并发？

java开发如何在单例模式下通过锁机制防止并发？

在Java开发中，单例模式是一种常用的设计模式，它确保一个类只有一个实例，并提供全局访问点。 单例模式在多线程环境下的实现尤为重要，因为不当的实现可能导致多个实例的创建，从而破坏单例的特性。 本文将深入探讨Java中单例模式的实现方式，并结合各种锁机制，分析如何有…
阅读更多...
最新文章

Copyright @ 2022~2023