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

于 2024-11-01 21:40:05 发布
阅读量757 收藏 14
点赞数 29
分类专栏： MySQL 文章标签： mysql 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/m0_63451989/article/details/143441327
版权

文理学院数据库技术应用实验报告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 `部门信息` (`部门编号`);
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

@杨星辰

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值