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

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

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

一、实验目的

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

二、实验原理

  1. 聚合函数：

max 最大值

min 最小值

avg 平均值

sum 求和

count 数目

  1. 分组查询语句：

（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”（学生成绩管理）数据库：

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/906290.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

flex 布局比较容易犯的错误 出现边界超出的预想的情况

flex 布局比较容易犯的错误 出现边界超出的预想的情况

flex 布局比较容易犯的错误 出现边界超出的预想的情况 如图 当使用flex布局时，设置flex:1 或者是flex:x 时 如果没有多层嵌套的flex布局，内容超出flex：1规定的后，仍然会撑大融器 在flex:1 处设置 overflow:hidden 即可超出后不显…
阅读更多...
【vue项目中添加告警音频提示音】

【vue项目中添加告警音频提示音】

一、前提： 由于浏览器限制不能自动触发音频文件播放，所以实现此类功能时，需要添加触发事件，举例如下： 1、页面添加打开告警声音开关按钮 2、首次进入页面时添加交互弹窗提示：是否允许播放音频 以上两种方…
阅读更多...
Java 用户随机选择导入ZIP文件，解压内部word模板并入库，Windows/可视化Linux系统某麒麟国防系统...均可适配

Java 用户随机选择导入ZIP文件，解压内部word模板并入库，Windows/可视化Linux系统某麒麟国防系统...均可适配

1.效果 压缩包内部文件 2.依赖 <!--支持Zip--><dependency><groupId>net.lingala.zip4j</groupId><artifactId>zip4j</artifactId><version>2.11.5</version></dependency>总之是要File类变MultipartFile类型的 好像是…
阅读更多...
反悔贪心

反悔贪心

Problem - C - Codeforces&#xff08;初识反悔贪心&#xff09; 题目&#xff1a; 思路&#xff1a; 代码&#xff1a; #include <bits/stdc.h> #define fi first #define se secondusing namespace std; typedef pair<int,int> PII;string a, b, ans; bool vis…
阅读更多...
Cisco Packet Tracer 8.0 路由器静态路由配置

Cisco Packet Tracer 8.0 路由器静态路由配置

文章目录 静态路由简介一、定义与特点二、配置与命令三、优点与缺点四、应用场景 一&#xff0c;搭建拓扑图二&#xff0c;配置pc IP地址三&#xff0c;pc0 ping pc1 timeout四&#xff0c;配置路由器Router0五&#xff0c;配置路由器Router1六&#xff0c;测试 静态路由简介 …
阅读更多...
burp靶场-Remote code execution via web shell upload

burp靶场-Remote code execution via web shell upload

Lab: 通过 Web shell 上传远程执行代码 This lab contains a vulnerable image upload function. It doesn’t perform any validation on the files users upload before storing them on the server’s filesystem. 此实验室包含易受攻击的映像上传功能。在将用户上传的文件…
阅读更多...
极简实现酷炫动效：Flutter隐式动画指南第二篇之一些酷炫的隐式动画效果

极简实现酷炫动效：Flutter隐式动画指南第二篇之一些酷炫的隐式动画效果

目录 前言 1.弹性放大按钮效果 2.旋转和缩放组合动画 3.颜色渐变背景动画 4.缩放进出效果 前言 在上一篇文章中&#xff0c;我们介绍了Flutter中的隐式动画的一些相关知识&#xff0c;在这篇文章中,我们可以结合多个隐式动画 Widget 在 Flutter 中创建一些酷炫的视觉效果&…
阅读更多...
后端：Spring-1

后端：Spring-1

文章目录 1. 了解 spring(Spring Framework)2. 基于maven搭建Spring框架2.1 纯xml配置方式来实现Spring2.2 注解方式来实现Spring3. Java Config类来实现Spring 2.4 总结 1. 了解 spring(Spring Framework) 传统方式构建spring(指的是Spring Framework)项目&#xff0c;导入依…
阅读更多...
qt QStackedLayout详解

qt QStackedLayout详解

QStackedLayout类提供了一种布局方式&#xff0c;使得在同一时间内只有一个子部件&#xff08;或称为页面&#xff09;是可见的。这些子部件被维护在一个堆栈中&#xff0c;用户可以通过切换来显示不同的子部件&#xff0c;适合用在需要动态显示不同界面的场景&#xff0c;如向…
阅读更多...
C++进阶：C++11的新特性

C++进阶：C++11的新特性

✨✨所属专栏&#xff1a;C✨✨ ✨✨作者主页&#xff1a;嶔某✨✨ C11的发展历史 2011年&#xff0c;C标准委员会发布了C11标准&#xff0c;这是C的一次巨大飞跃&#xff0c;引入了许多重要的新特性&#xff0c;如智能指针、lambda表达式、并发编程支持等。这一版本的发布对C社…
阅读更多...
GA/T1400视图库平台EasyCVR视频分析设备平台微信H5小程序：智能视频监控的新篇章

GA/T1400视图库平台EasyCVR视频分析设备平台微信H5小程序：智能视频监控的新篇章

GA/T1400视图库平台EasyCVR是一款综合性的视频管理工具&#xff0c;它兼容Windows、Linux&#xff08;包括CentOS和Ubuntu&#xff09;以及国产操作系统。这个平台不仅能够接入多种协议&#xff0c;还能将不同格式的视频数据统一转换为标准化的视频流&#xff0c;通过无需插件的…
阅读更多...
OpenAI推出搜索GPT，进军搜索引擎领域

OpenAI推出搜索GPT，进军搜索引擎领域

OpenAI 推出了一项新功能——Search GPT&#xff0c;为 ChatGPT 引入实时网络搜索功能&#xff0c;使其站上与 Google 和 Bing 等搜索巨头竞争的舞台。 OpenAI 产品的重大变化&#xff0c;Search GPT 承诺提供快捷、实时的答案&#xff0c;并附上可靠来源的链接。 ChatGPT 一直…
阅读更多...
Unity XR Interaction Toolkit 开发教程（3）快速配置交互：移动、抓取、UI交互【3.0以上版本】

Unity XR Interaction Toolkit 开发教程（3）快速配置交互：移动、抓取、UI交互【3.0以上版本】

获取完整课程以及答疑&#xff0c;工程文件下载&#xff1a; https://www.spatialxr.tech/ 视频试看链接&#xff1a; 3.快速配置交互&#xff1a;移动、抓取、UI交互【Unity XR Interaction Toolkit 跨平台开发教程】&#xff08;3.0以上版本&#xff09; 系列教程专栏&…
阅读更多...
SE-Net模型实现猴痘病识别

SE-Net模型实现猴痘病识别

项目源码获取方式见文章末尾&#xff01; 600多个深度学习项目资料&#xff0c;快来加入社群一起学习吧。 《------往期经典推荐------》 项目名称 1.【DeepLabV3模型实现人体部位分割CIHP数据】 2.【卫星图像道路检测DeepLabV3Plus模型】 3.【GAN模型实现二次元头像生成】 4.…
阅读更多...
深度学习之权重、偏差

深度学习之权重、偏差

1 权重偏差初始化 1.1 全都初始化为 0 偏差初始化陷阱&#xff1a; 都初始化为 0。 产生陷阱原因&#xff1a;因为并不知道在训练神经网络中每一个权重最后的值&#xff0c;但是如果进行了恰当的数据归一化后&#xff0c;我们可以有理由认为有一半的权重是正的&#xff0c;另…
阅读更多...
企业物流管理数据仓库建设的全面指南

企业物流管理数据仓库建设的全面指南

文章目录 一、物流管理目标二、总体要求三、数据分层和数据构成&#xff08;1&#xff09;数据分层&#xff08;2&#xff09;数据构成 四、数据存储五、数据建模和数据模型&#xff08;1&#xff09;数据建模&#xff08;2&#xff09;数据模型 六、总结 在企业物流管理中&…
阅读更多...
Spring Boot 与 EasyExcel 携手：复杂 Excel 表格高效导入导出实战

Spring Boot 与 EasyExcel 携手：复杂 Excel 表格高效导入导出实战

数据的并行导出与压缩下载&#xff1a;EasyExcel&#xff1a;实现大规模数据的并行导出与压缩下载 构建高效排队导出&#xff1a;解决多人同时导出Excel导致的服务器崩溃 SpringBoot集成EasyExcel 3.x&#xff1a; 前言 在企业级应用开发中&#xff0c;常常需要处理复杂的 …
阅读更多...
【网络篇】计算机网络——链路层详述（笔记）

【网络篇】计算机网络——链路层详述（笔记）

目录 一、链路层 1. 概述 2. 链路层提供的服务 &#xff08;1&#xff09;成帧&#xff08;framing&#xff09; &#xff08;2&#xff09;链路接入 &#xff08;3&#xff09;可靠交付 &#xff08;4&#xff09;差错检测和纠正 3. 链路层的实现 二、多路访问链路和…
阅读更多...
Android——显式/隐式Intent

Android——显式/隐式Intent

概述 在Android中&#xff0c;Intent是各个组件之间信息通信的桥梁&#xff0c;它用于Android各组件的通信。 Intent 的组成部分 一、显式 Intent 第一种方式 Intent intent new Intent(this, ActFinishActivity.class);startActivity(intent);第二种方式 Intent intent …
阅读更多...
__init__.py __all__和 __name__的作用及其用法

__init__.py __all__和 __name__的作用及其用法

__ init__.py 的作用及其用法&#xff1a; 包下的__init__.py 所在目录是一个模块包,本身也是一个模块,可用于定义模糊导入时要导入的内容。当我们导入一个包的时候&#xff0c;包下的__init__.py中的代码会自动执行&#xff0c;因此在某些大的项目被使用频率较高的模块&#x…
阅读更多...
最新文章

Copyright @ 2022~2023