【MySQL课后题】第三章——MySQL数据库的基本操作

🕗 发布于 2024-10-14 10:53 数据库 mysql

【MySQL课后题】第三章——MySQL数据库的基本操作

一、假设某商业集团中有若干公司，人事数据库中有3个基本表。
- 1. 准备工作
- 2. 题目
二、完成以下MySQL题目的要求。
- 1. 准备工作
- 2. 题目
三、某工厂的信息管理数据库中有如下两个关系模式。
- 1. 题目

一、假设某商业集团中有若干公司，人事数据库中有3个基本表。

职工表：EMP(E#, ENAME, AGE, SEX, ECITY)。
其属性分别表示职工工号、姓名、年龄、性别和居住城市。
工作表：WORKS(E#, C#, SALARY)。
其属性分别表示职工工号、所在公司的编号和工资。
公司表：COMP(C#, CNAME, CITY, MGR_E#)。
其属性分别表示公司编号、公司名称、公司所在城市和公司经理的工号。
在3个基本表中，字段AGE和SALARY为数值型，其他字段均为字符型。

1. 准备工作

新建一个数据库
```
CREATE DATABASE db_first;
```

建立表结构

use db_first;

CREATE TABLE EMP (
  `E#` varchar(4) PRIMARY KEY,
  ENAME varchar(50),
  AGE int(11),
  SEX varchar(2),
  ECITY varchar(50)
);

CREATE TABLE WORKS  (
  `E#` varchar(4),
  `C#` varchar(4),
  SALARY decimal(10, 2),
  PRIMARY KEY (`E#`, `C#`)
);

CREATE TABLE COMP (
  `C#` varchar(4) PRIMARY KEY,
  CNAME varchar(50),
  CITY varchar(50),
  `MEG_E#` varchar(4)
);

插入表数据

INSERT INTO EMP
VALUES
  ( '0001', '张三', 55, '男', '北京' ),
  ( '0002', '李四', 45, '女', '上海' ),
  ( '0003', '王五', 23, '男', '北京' ),
  ( '0004', '赵六', 80, '女', '上海' ),
  ( '0005', '孙七', 52, '女', '广州' );

INSERT INTO WORKS
VALUES
  ( '0001', '0001', 8848.00 ),
  ( '0001', '0002', 1.00 ),
  ( '0001', '0003', 9527.00 ),
  ( '0002', '0002', 18888.88 ),
  ( '0002', '0003', 1999.00 ),
  ( '0003', '0001', 211.22 ),
  ( '0003', '0002', 1573.99 ),
  ( '0004', '0002', 233.23 ),
  ( '0005', '0003', 14332.23 );

INSERT INTO COMP
VALUES
  ( '0001', '联华公司', '北京', '0001' ),
  ( '0002', '第二分公司', '上海', '0002' ),
  ( '0003', '第三分公司', '广州', '0001' );

2. 题目

检索超过50岁的男职工的工号和姓名

--  1. 检索超过50岁的男职工的工号和姓名
SELECT
  `E#`,
  ENAME 
FROM
  emp 
WHERE
  SEX = "男" 
  AND age > 50;

在这里插入图片描述

假设每个职工可在多个公司工作，检索每个职工的兼职公司数目和总工资。显示为（E#, NUM, SUM_SALARY），其属性分别表示工号、公司的数目和总工资。

/**
* 2. 假设每个职工可在多个公司工作，检索每个职工的兼职公司数目和总工资。
* 显示为 （E#, NUM, SUM_SALARY），其属性分别表示工号、公司的数目和总工资。
*/
SELECT
  `E#`,
  count(*) NUM,
  sum( salary ) SUM_SALARY 
FROM
  works 
GROUP BY
  `E#`;

在这里插入图片描述

检索联华公司低于本公司职工平均工资的所有职工的工号和姓名。

-- 3. 检索联华公司低于本公司职工平均工资的所有职工的工号和姓名。
SELECT
  emp.`E#`,
  emp.ENAME 
FROM
  emp
  JOIN works ON works.`E#` = emp.`E#`
  JOIN comp ON comp.`C#` = works.`C#` 
WHERE
  comp.CNAME = "联华公司" 
  AND works.SALARY <(
  SELECT
    AVG( SALARY ) 
  FROM
    works
    JOIN comp ON works.`C#` = comp.`C#` 
  WHERE
  comp.CNAME = "联华公司" 
  );

在这里插入图片描述

检索职工人数最多的公司的编号和名称。

-- 4. 检索职工人数最多的公司的编号和名称。
SELECT
  comp.`C#`,
  comp.CNAME 
FROM
  comp
  JOIN works ON comp.`C#` = works.`C#` 
GROUP BY
  `C#` 
HAVING
  count(*)>= ALL ( SELECT count(*) FROM works GROUP BY works.`C#` );

在这里插入图片描述

检索平均工资高于联华公司平均工资的公司编号和名称。

-- 5. 检索平均工资高于联华公司平均工资的公司编号和名称。
SELECT
  comp.`C#`,
  comp.CNAME 
FROM
  comp
  JOIN works ON comp.`C#` = works.`C#` 
GROUP BY
  `C#` 
HAVING
  AVG( works.SALARY )>(
  SELECT
    AVG( works.SALARY ) 
  FROM
    works
    JOIN comp ON works.`C#` = comp.`C#` 
  WHERE
    comp.CNAME = "联华公司" 
  );

在这里插入图片描述

为联华公司的职工加薪5%。

-- 6. 为联华公司的职工加薪5%。
UPDATE works 
SET works.SALARY = works.SALARY * 1.05 
WHERE
  works.`C#` IN ( SELECT comp.`C#` FROM comp WHERE comp.CNAME = "联华公司" );

在这里插入图片描述

在表WORKS中删除年龄大于60岁的职工记录。

-- 7. 在表WORKS中删除年龄大于60岁的职工记录。
DELETE 
FROM
  works 
WHERE
  works.`E#` IN ( SELECT emp.`E#` FROM emp WHERE emp.AGE > 60 );

在这里插入图片描述

建立一个有关女职工的视图 emp_woman, 属性包括(E#, ENAME, C#, CNAME, SALARY)。然后对视图 emp_woman 进行操作，检索每一个女职工的总工资（假设每个职工可在多个公司兼职）。

-- 8. 建立一个有关女职工的视图 emp_woman, 属性包括(E#, ENAME, C#, CNAME, SALARY)。
CREATE VIEW emp_woman AS SELECT
emp.`E#`,
emp.ENAME,
comp.`C#`,
comp.CNAME,
works.SALARY 
FROM
  emp
  JOIN works ON emp.`E#` = works.`E#`
  JOIN comp ON comp.`C#` = works.`C#` 
WHERE
  emp.SEX = "女";

-- 然后对视图 emp_woman 进行操作，检索每一个女职工的总工资（假设每个职工可在多个公司兼职）。
SELECT
  `E#`,
  ENAME,
  sum( salary ) 
FROM
  emp_woman 
GROUP BY
  `E#`;

在这里插入图片描述

二、完成以下MySQL题目的要求。

1. 准备工作

新建一个数据库

CREATE DATABASE db_second;
use db_second;

2. 题目

创建表 workinfo，要求创建表的同时在id字段上创建名为index_id的唯一索引，且降序排列。workinfo的表结构如下标所示。

字段名	字段描述	数据类型	非空	主键	唯一	自增
id	编号	INT	是	是	是	是
name	职位名称	VARCHAR(20)	是	否	否	否
type	职位类型	VARCHAR(10)	否	否	否	否
address	工作地址	VARCHAR(50)	否	否	否	否
wages	工资	INT	否	否	否	否
contents	工作内容	TINYTEXT	否	否	否	否
extra	附加信息	TEXT	否	否	否	否

CREATE TABLE workinfo (
  id int NOT NULL PRIMARY KEY UNIQUE AUTO_INCREMENT,
  name varchar(20) NOT NULL,
  type varchar(10),
  address varchar(50),
  wages int,
  contents tinytext,
  extra text,
  UNIQUE INDEX index_id (id DESC)
);

在这里插入图片描述

使用CREATE INDEX语句为name字段创建长度为10的索引index_name。

-- 2. 使用CREATE INDEX语句为name字段创建长度为10的索引index_name。
CREATE INDEX index_name ON workinfo(name(10));

在这里插入图片描述

使用ALTER TABLE语句在type和address字段上创建名为index_t的索引。

-- 3. 使用ALTER TABLE语句在type和address字段上创建名为index_t的索引。
ALTER TABLE workinfo ADD INDEX index_t ( type, address );

在这里插入图片描述

使用ALTER TABLE语句在extra字段上创建名为index_ext的全文索引。

-- 4. 使用ALTER TABLE语句在extra字段上创建名为index_ext的全文索引。
ALTER TABLE workinfo ADD FULLTEXT INDEX index_ext ( extra );

在这里插入图片描述

删除表workinfo的唯一性索引index_id。

-- 5. 删除表workinfo的唯一性索引index_id。
DROP INDEX index_id ON workinfo;

在这里插入图片描述

三、某工厂的信息管理数据库中有如下两个关系模式。

职工（职工号，姓名，年龄，月工资，部门号，电话，办公室）
部门（部门号，部门名，负责人代码，任职时间）

1. 题目

(1) 查询每个部门月工资最高的“职工号”的SQL语句如下。

SELECT 职工号 FROM 职工 E
WHERE 月工资=(SELECT MAX(月工资)
FROM 职工 M
WHERE M.部门号=E.部门号);

① 请用30字以内的文字简要说明该查询语句对查询效率的影响。
② 对该查询语句进行修改，使它既能实现相同功能，又能提高查询效率。

答：
① 该查询使用了子查询，导致每个部门的最高工资都需多次计算，效率较低。
② 使用临时表存储每个部门的最高工资，避免重复查询
SELECT E.职工号 
FROM 职工 E
JOIN (
   SELECT 部门号, MAX(月工资) AS 最高工资
   FROM 职工
   GROUP BY 部门号
) AS M ON E.部门号 = M.部门号 AND E.月工资 = M.最高工资;

（2）假定分别在“职工”关系中的“年龄”和“月工资”字段上创建索引，如下的SELECT查询语句可能不会促使查询优化器使用索引，从而降低查询效率，请写出既能实现相同功能又能提高查询效率的SQL语句。

SELECT 姓名,年龄,月工资 FROM 职工
WHERE 年龄>35 OR 月工资<1000;

答：

(SELECT 姓名, 年龄, 月工资 FROM 职工 WHERE 年龄 > 35)
UNION
(SELECT 姓名, 年龄, 月工资 FROM 职工 WHERE 月工资 < 1000);

原文地址：https://blog.csdn.net/realoser/article/details/142899099

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

上一篇：【华为】配置BGP协议
下一篇：软件工程：需求规格说明书（图书管理系统）

五、UI弹窗提示
协程调用方法；制作弹窗文本
阅读更多2024-10-14
使用verilog设计实现的数字滤波器（低通、高通、带通）及其仿真
对于低通FIR滤波器，可以使用窗函数法（如汉宁窗）来设计滤波器系数。例如，设计一个截止频率为。高通滤波器系数的设计方法与低通类似，但频率特性不同。例如，设计一个截止频率为。带通滤波器需要确定下限截止频
阅读更多2024-10-14
树莓派应用--AI项目实战篇来啦-14.基于OpenCV二维码识别
二维码又称二维条码，常见的二维码为QR Code,QR全称Quick Response，是一个近几年来移动设备上超流行的一种编码方式，它比传统的Bar Code条形码能存更多的信息，也能表示更多的数据
阅读更多2024-10-14
【无标题】
欢迎大家转发，一起传播知识和正能量，帮助到更多人。期待大家提出宝贵改进建议，互相交流，收获更大。辛苦大家转发时注明出处。【小白从小学Python+C+Java】也是咱们公益编程交流群的入口网址。【40
阅读更多2024-10-14
UniApp入门教程
UniApp 是一种用于构建跨平台应用程序的框架，它基于 Vue.js 并通过 UniApp 技术栈支持多种平台，如微信小程序、支付宝小程序、H5、Android 和 iOS。
阅读更多2024-10-14
突破一个强大算法模型，Transformer ！！
今儿和大家再来聊聊 Transformer ，以及给一个简单的代码案例让大家更好的理解~Transformer 是一种深度学习模型架构，最初由 Vaswani 等人在 2017 年提出，它的设计用于解
阅读更多2024-10-14
初识git · 基本操作
Git 是一个开源的分布式版本控制系统，最初由林纳斯·托瓦兹（Linus Torvalds）于2005年开发，目的是为了更好地管理Linux内核的开发过程。与传统的集中式版本控制系统（如SVN）不同，
阅读更多2024-10-14
五大检索模式，精确定位所需专利
2.高级检索：提供多种条件组合，如专利申请人、专利分类、申请日期等，帮助用户深入挖掘特定的专利数据。4.语义检索：通过语义分析技术，用户可以使用自然语言进行查询，平台会智能理解并匹配相关的专利内容。1
阅读更多2024-10-14
C语言指针（test_1_29）
C语言指针（test_1_29）
阅读更多2024-10-14
PyTorch中的with torch.no_grad：节省计算资源与加速推理的关键
with torch.no_grad是一个上下文管理器，它能够在其作用域内禁止计算图的构建。这意味着在推理过程中，PyTorch不会为前向传播操作生成计算图，从而节省显存和计算资源。这对于大型模型或在
阅读更多2024-10-14

【MySQL课后题】第三章——MySQL数据库的基本操作

【MySQL课后题】第三章——MySQL数据库的基本操作

一、假设某商业集团中有若干公司，人事数据库中有3个基本表。

1. 准备工作

2. 题目

二、完成以下MySQL题目的要求。

1. 准备工作

2. 题目

三、某工厂的信息管理数据库中有如下两个关系模式。

1. 题目

相关文章