day02_mysql-DDLDMLDQL_课后练习 - 参考答案

2024-03-22 14:20
文章标签 mysql 参考答案 database day02 课后练习 ddldmldql

本文主要是介绍day02_mysql-DDLDMLDQL_课后练习 - 参考答案，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

文章目录

  • day02_mysql_课后练习
    • 第1题
    • 第2题

day02_mysql_课后练习

第1题

案例：

1、创建数据库test02_library

2、创建表格books

字段名字段说明数据类型
b_id书编号int(11)
b_name书名varchar（50）
authors作者varchar(100)
price价格float
pubdate出版日期year
note说明varchar(100)
num库存int(11)

3、使用alter语句给books按如下要求增加相应的约束

字段名字段说明数据类型主键外键非空唯一自增
b_id书编号int(11)是否是是是
b_name书名varchar（50）否否是否否
authors作者varchar(100)否否是否否
price价格float否否是否否
pubdate出版日期year否否是否否
note说明varchar(100)否否否否否
num库存int(11)否否是否否

4、向books表中插入记录

1） 指定所有字段名称插入第一条记录

2）不指定字段名称插入第二记录

3）同时插入多条记录（剩下的所有记录）

b_idb_nameauthorspricepubdatenotenum
1Tal of AAADickes231995novel11
2EmmaTJane lura351993joke22
3Story of JaneJane Tim402001novel0
4Lovey DayGeorge Byron202005novel30
5Old landHonore Blade302010law0
6The BattleUpton Sara301999medicine40
7Rose HoodRichard haggard282008cartoon28

5、统计书名中包含a字母的书

6、统计书名中包含a字母的书的数量和库存总量

7、找出“novel”类型的书，按照价格降序排列

8、查询图书信息，按照库存量降序排列，如果库存量相同的按照note升序排列

9、按照note分类统计书的数量

10、按照note分类统计书的库存量，显示库存量超过30本的

11、查询所有图书，每页显示5本，显示第二页

12、按照note分类统计书的库存量，现在库存量最多的

13、查询书名达到10个字符的书，不包括里面的空格

14、查询书名和类型，其中

​ note值为novel显示小说，law显示法律，medicine显示医药，cartoon显示卡通，joke显示笑话

15、查询书名、库存，其中

​ num值超过30本的，显示滞销，大于0并低于10的，显示畅销，为0的显示需要无货

16、统计每一种note的库存量，并合计总量

17、统计每一种note的数量，并合计总量

18、统计库存量前三名的图书

19、找出最早出版的一本书

20、找出novel中最高的一本书

21、找出书名中字数最多的一本书，不含空格

#创建数据库test02_library
CREATE DATABASE test02_library;#使用test02_library
USE test02_library;#创建表格books
CREATE TABLE books(b_id INT,b_name VARCHAR(50),`authors` VARCHAR(100),price FLOAT,pubdate YEAR,note VARCHAR(100),num INT
);#给b_id增加主键约束
ALTER TABLE books ADD PRIMARY KEY(b_id);#给b_id字段增加自增约束
ALTER TABLE books MODIFY b_id INT AUTO_INCREMENT;#给b_name等字段增加非空约束
ALTER TABLE books b_name VARCHAR(50) NOT NULL;
ALTER TABLE books `authors` VARCHAR(100) NOT NULL;
ALTER TABLE books price FLOAT NOT NULL;
ALTER TABLE books pubdate DATE NOT NULL;
ALTER TABLE books num INT NOT NULL;#指定所有字段名称插入第一条记录
INSERT INTO books (b_id,b_name,`authors`,price,pubdate,note,num)
VALUES(1,'Tal of AAA','Dickes',23,1995,'novel',11);#不指定字段名称插入第二记录
INSERT INTO books 
VALUE(2,'EmmaT','Jane lura',35,1993,'Joke',22);#同时插入多条记录（剩下的所有记录）。
INSERT INTO books VALUES
(3,'Story of Jane','Jane Tim',40,2001,'novel',0),
(4,'Lovey Day','George Byron',20,2005,'novel',30),
(5,'Old land','Honore Blade',30,2010,'Law',0),
(6,'The Battle','Upton Sara',30,1999,'medicine',40),
(7,'Rose Hood','Richard haggard',28,2008,'cartoon',28);#查询书名中包含a字母的书
SELECT * FROM books WHERE b_name LIKE '%a%';#统计书名中包含a字母的书的数量和库存总量
SELECT COUNT(*),SUM(num) FROM books WHERE b_name LIKE '%a%';#找出“novel”类型的书，按照价格降序排列
SELECT * FROM books WHERE note = 'novel' ORDER BY price DESC;#查询图书信息，按照库存量降序排列，如果库存量相同的按照note升序排列
SELECT * FROM books ORDER BY num DESC,note ASC;#按照note分类统计书的数量
SELECT note,COUNT(*) FROM books GROUP BY note;#按照note分类统计书的库存量，显示库存量超过30本的
SELECT note,SUM(num) FROM books GROUP BY note HAVING SUM(num)>30;#查询所有图书，每页显示5本，显示第二页
SELECT * FROM books LIMIT 5,5;#按照note分类统计书的库存量，现在库存量最多的
SELECT note,SUM(num) FROM books GROUP BY note ORDER BY SUM(num) DESC LIMIT 0,1;#查询书名达到10个字符的书，不包括里面的空格
SELECT * FROM books WHERE CHAR_LENGTH(REPLACE(b_name,' ',''))>=10;/*
查询书名和类型，其中
​note值为novel显示小说，law显示法律，medicine显示医药，cartoon显示卡通，joke显示笑话
*/
SELECT b_name AS "书名" ,note, CASE note WHEN 'novel' THEN '小说'WHEN 'law' THEN '法律'WHEN 'medicine' THEN '医药'WHEN 'cartoon' THEN '卡通'WHEN 'joke' THEN '笑话'END AS "类型"
FROM books;/*
查询书名、库存，其中
num值超过30本的，显示滞销，大于0并低于10的，显示畅销，为0的显示需要无货，其他的显示正常
*/
SELECT b_name,num,CASE WHEN num>30 THEN '滞销'WHEN num>0 AND num<10 THEN '畅销'WHEN num=0 THEN '无货'ELSE '正常'END AS "库存状态"
FROM books;#统计每一种note的库存量，并合计总量
SELECT IFNULL(note,'合计总库存量') AS note,SUM(num) FROM books GROUP BY note WITH ROLLUP;#统计每一种note的数量，并合计总量
SELECT IFNULL(note,'合计总数') AS note,COUNT(*) FROM books GROUP BY note WITH ROLLUP;#统计库存量前三名的图书
SELECT * FROM books ORDER BY num DESC LIMIT 0,3;#找出最早出版的一本书
SELECT * FROM books ORDER BY pubdate ASC LIMIT 0,1;#找出novel中最高的一本书
SELECT * FROM books WHERE note = 'novel' ORDER BY price DESC LIMIT 0,1;#找出书名中字数最多的一本书
SELECT * FROM books ORDER BY CHAR_LENGTH(REPLACE(b_name,' ','')) DESC LIMIT 0,1;

第2题

案例：

1、创建数据库：day02_test02_company

2、在此数据库下创建如下3表，数据类型，宽度，是否为空根据实际情况自己定义。

A． 部门表（department）：部门编号（depid），部门名称（depname），部门简介（deinfo）；其中部门编号为主键。

B． 雇员表（emoloyee）：雇员编号（empid），姓名（name），性别（sex），职称（title），出生日期（birthday），所在部门编号（depid）；其中

  • ​ 雇员编号为主键；
  • ​ 部门编号为外键，外键约束等级为（on update cascade 和on delete set null）；
  • ​ 性别默认为男；

C． 工资表（salary）：雇员编号（empid），基本工资（basesalary），职务工资（titlesalary），扣除（deduction）。其中雇员编号为主键。

3、给工资表（salary）的雇员编号（empid）增加外键约束，外键约束等级为（on update cascade 和on delete cascade）

4、添加数据如下：

部门表：

部门编号部门名称部门简介
111生产部Null
222销售部Null
333人事部人力资源管理

雇员表：

雇员编号姓名性别职称出生日期所在部门编号
1001张三男高级工程师1975-1-1111
1002李四女助工1985-1-1111
1003王五男工程师1978-11-11222
1004张六男工程师1999-1-1222

工资表：

雇员编号基本工资职务工资扣除
100122001100200
10021200200NULL
10032900700200
10041950700150

5、查询出每个雇员的雇员编号，姓名，职称，所在部门名称，应发工资（基本工资+职务工资），实发工资（基本工资+职务工资-扣除）。

6、查询销售部门的雇员姓名及其基本工资

7、查询姓“张”且年龄小于40的员工的全部信息和年龄

8、查询所有男员工的基本工资和职务工资

9、查询基本工资低于2000的员工姓名和职称、所在部门名称

10、查询员工总数

11、查询部门总数

12、查询应发工资的平均工资和最高工资、最低工资

13、按照部门统计应发工资的平均工资

14、找出部门基本工资的平均工资低于2000的

15、按照员工编号、姓名、基本工资、职务工资、扣除，并按照职务升序排列，如果职务工资相同，再按照基本工资升序排列

16、查询员工编号、姓名，出生日期，及年龄段，其中

​ 如果80年之前出生的，定为”老年“；80后定为”中年“，90后定为”青壮年“

17、查询所有的员工信息，和他所在的部门名称

18、查询所有部门信息，和该部门的员工信息

19、查询所有职位中含“工程师”的男员工的人数

20、查询每个部门的男生和女生的人数和平均基本工资

#创建数据库：day02_test02_company
CREATE DATABASE day02_test02_company;#使用数据库day02_test02_company
USE day02_test02_company;#创建部门表（department）
CREATE TABLE department(depid INT PRIMARY KEY,depname VARCHAR(20) NOT NULL,deinfo VARCHAR(50)
);#创建雇员表（emoloyee）
CREATE TABLE employee(empid INT PRIMARY KEY,`name` VARCHAR(20) NOT NULL,sex CHAR NOT NULL DEFAULT '男',title VARCHAR(20) NOT NULL,birthday DATE,depid INT,FOREIGN KEY(depid) REFERENCES department(depid) ON UPDATE CASCADE ON DELETE SET NULL
);#创建工资表（salary）
CREATE TABLE salary(empid INT PRIMARY KEY,basesalary DOUBLE,titlesalary DOUBLE,deduction DOUBLE
);#给工资表（salary）的雇员编号（empid）增加外键约束，外键约束等级为（on update cascade 和on delete cascade）
ALTER TABLE salary ADD FOREIGN KEY empid REFERENCES employee(empid) ON UPDATE CASCADE ON DELETE CASCADE;#添加部门表数据
INSERT INTO department VALUES
(111,'生产部',NULL),
(222,'销售部',NULL),
(333,'人事部','人力资源管理');#添加雇员表
INSERT INTO employee VALUES
(1001,'张三',DEFAULT,'高级工程师','1975-1-1',111),
(1002,'李四','女','助工','1985-1-1',111),
(1003,'王五','男','工程师','1978-11-11',222),
(1004,'张六',DEFAULT,'工程师','1999-1-1',222);#添加工资表
INSERT INTO salary VALUES
(1001,2200,1100,200),
(1002,1200,200,NULL),
(1003,2900,700,200),
(1004,1950,700,150);/*
查询出每个雇员的雇员编号，姓名，职称，所在部门名称，
应发工资（基本工资+职务工资），
实发工资（基本工资+职务工资-扣除）。
*/
SELECT employee.empid,`name`,title,depname,
basesalary+titlesalary AS "应发工资",
basesalary+titlesalary-IFNULL(deduction,0) AS "实发工资"
FROM department INNER JOIN employee INNER JOIN salary
ON department.depid = employee.depid AND employee.empid = salary.empid;#查询销售部门的雇员姓名及其基本工资
SELECT `name`,basesalary
FROM department INNER JOIN employee INNER JOIN salary
ON department.depid = employee.depid AND employee.empid = salary.empid
WHERE department.depname = '销售部';#查询姓“张”且年龄小于40的员工的全部信息和年龄
SELECT *,YEAR(CURRENT_DATE())-YEAR(birthday) AS "年龄" 
FROM employee 
WHERE `name` LIKE '张%' AND YEAR(CURRENT_DATE())-YEAR(birthday)<40;#查询所有男员工的基本工资和职务工资
SELECT basesalary,titlesalary 
FROM employee INNER JOIN salary
ON employee.empid = salary.empid
WHERE employee.sex = '男';#查询基本工资低于2000的员工姓名和职称、所在部门名称
SELECT `name`,title,depname
FROM department INNER JOIN employee INNER JOIN salary
ON department.depid = employee.depid AND employee.empid = salary.empid
WHERE basesalary < 2000;#查询员工总数
SELECT COUNT(*) FROM employee;#查询部门总数
SELECT COUNT(*) FROM department;#查询应发工资的平均工资和最高应发工资、最低应发工资
SELECT AVG(basesalary+titlesalary) AS "平均应发工资",MAX(basesalary+titlesalary) AS "最高应发工资",MIN(basesalary+titlesalary) AS "最低应发工资"
FROM salary;#按照部门统计应发工资的平均工资
SELECT depid,AVG(basesalary+titlesalary)
FROM employee INNER JOIN salary
ON employee.`empid` = salary.`empid`
GROUP BY employee.`depid`;#找出部门基本工资的平均工资低于2000的
SELECT depid,AVG(basesalary)
FROM employee INNER JOIN salary
ON employee.`empid` = salary.`empid`
GROUP BY employee.`depid`
HAVING AVG(basesalary)<2000;#按照员工编号、姓名、基本工资、职务工资、扣除，
#并按照职务升序排列，如果职务工资相同，再按照基本工资升序排列
SELECT emp.empid,`name`,basesalary,titlesalary,deduction
FROM employee emp INNER JOIN salary
ON emp.`empid` = salary.`empid`
ORDER BY emp.`title` ASC , basesalary ASC;#查询员工编号、姓名，出生日期，及年龄段，其中
​#如果80年之前出生的，定为”老年“；80后定为”中年“，90后定为”青壮年“
SELECT empid,`name`,birthday,CASE WHEN YEAR(birthday)<1980 THEN '老年'WHEN YEAR(birthday)<1990 THEN '中年'ELSE '青壮年' END "年龄段"
FROM employee;#查询所有的员工信息，和他所在的部门名称
SELECT emp.*,depname
FROM employee emp LEFT JOIN department dep
ON emp.`depid` = dep.`depid`;#查询所有部门信息，和该部门的员工信息
SELECT dep.*,emp.*
FROM employee emp RIGHT JOIN department dep
ON emp.`depid` = dep.`depid`;#查询所有职位中含“工程师”的男员工的人数
SELECT COUNT(*) FROM employee WHERE sex='男' AND title LIKE '%工程师%';#查询每个部门的男生和女生的人数和平均基本工资
SELECT dep.depid,sex,COUNT(*),AVG(basesalary)
FROM department dep INNER JOIN employee INNER JOIN salary
ON dep.depid = employee.depid AND employee.empid = salary.empid
GROUP BY dep.depid,sex;

在这里插入图片描述

这篇关于day02_mysql-DDLDMLDQL_课后练习 - 参考答案的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/835464。

相关文章

SQL Server 中的表进行行转列场景示例

SQL Server 中的表进行行转列场景示例

《SQLServer中的表进行行转列场景示例》本文详细介绍了SQLServer行转列（Pivot）的三种常用写法,包括固定列名、条件聚合和动态列名,文章还提供了实际示例、动态列数处理、性能优化建议... 目录一、常见场景示例二、写法 1：PIVOT（固定列名）三、写法 2：条件聚合（CASE WHEN）四、
阅读更多...
Mybatis对MySQL if 函数的不支持问题解读

Mybatis对MySQL if 函数的不支持问题解读

《Mybatis对MySQLif函数的不支持问题解读》接手项目后,为了实现多租户功能,引入了Mybatis-plus,发现之前运行正常的SQL语句报错,原因是Mybatis不支持MySQL的if函... 目录MyBATis对mysql if 函数的不支持问题描述经过查询网上搜索资料找到原因解决方案总结Myb
阅读更多...
MySQL 筛选条件放 ON后 vs 放 WHERE 后的区别解析

MySQL 筛选条件放 ON后 vs 放 WHERE 后的区别解析

《MySQL筛选条件放ON后vs放WHERE后的区别解析》文章解释了在MySQL中,将筛选条件放在ON和WHERE中的区别,文章通过几个场景说明了ON和WHERE的区别,并总结了ON用于关... 今天我们来讲讲数据库筛选条件放 ON 后和放 WHERE 后的区别。ON 决定如何 "连接" 表，WHERE
阅读更多...
mysql_mcp_server部署及应用实践案例

mysql_mcp_server部署及应用实践案例

《mysql_mcp_server部署及应用实践案例》文章介绍了在CentOS7.5环境下部署MySQL_mcp_server的步骤,包括服务安装、配置和启动,还提供了一个基于Dify工作流的应用案例... 目录mysql_mcp_server部署及应用案例1. 服务安装1.1. 下载源码1.2. 创建独立
阅读更多...
Mysql中RelayLog中继日志的使用

Mysql中RelayLog中继日志的使用

《Mysql中RelayLog中继日志的使用》MySQLRelayLog中继日志是主从复制架构中的核心组件,负责将从主库获取的Binlog事件暂存并应用到从库,本文就来详细的介绍一下RelayLog中... 目录一、什么是 Relay Log（中继日志）二、Relay Log 的工作流程三、Relay Lo
阅读更多...
MySQL日志UndoLog的作用

MySQL日志UndoLog的作用

《MySQL日志UndoLog的作用》UndoLog是InnoDB用于事务回滚和MVCC的重要机制,本文主要介绍了MySQL日志UndoLog的作用,文中介绍的非常详细,对大家的学习或者工作具有一定的... 目录一、Undo Log 的作用二、Undo Log 的分类三、Undo Log 的存储四、Undo
阅读更多...
MySQL游标和触发器的操作流程

MySQL游标和触发器的操作流程

《MySQL游标和触发器的操作流程》本文介绍了MySQL中的游标和触发器的使用方法,游标可以对查询结果集进行逐行处理,而触发器则可以在数据表发生更改时自动执行预定义的操作,感兴趣的朋友跟随小编一起看看... 目录游标游标的操作流程1. 定义游标2.打开游标3.利用游标检索数据4.关闭游标例题触发器触发器的基
阅读更多...
MySQL查看表的历史SQL的几种实现方法

MySQL查看表的历史SQL的几种实现方法

《MySQL查看表的历史SQL的几种实现方法》：本文主要介绍多种查看MySQL表历史SQL的方法,包括通用查询日志、慢查询日志、performance_schema、binlog、第三方工具等,并... 目录mysql 查看某张表的历史SQL1.查看MySQL通用查询日志（需提前开启）2.查看慢查询日志3.
阅读更多...
MySQL底层文件的查看和修改方法

MySQL底层文件的查看和修改方法

《MySQL底层文件的查看和修改方法》MySQL底层文件分为文本类（可安全查看/修改）和二进制类（禁止手动操作）,以下按「查看方法、修改方法、风险管控三部分详细说明,所有操作均以Linux环境为例,需... 目录引言一、mysql 底层文件的查看方法1. 先定位核心文件路径（基础前提）2. 文本类文件（可直
阅读更多...
MySQL数据目录迁移的完整过程

MySQL数据目录迁移的完整过程

《MySQL数据目录迁移的完整过程》文章详细介绍了将MySQL数据目录迁移到新硬盘的整个过程,包括新硬盘挂载、创建新的数据目录、迁移数据（推荐使用两遍rsync方案）、修改MySQL配置文件和重启验证... 目录1，新硬盘挂载（如果有的话）2，创建新的 mysql 数据目录3，迁移 MySQL 数据（推荐两
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2