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。

相关文章

MySQL的JDBC编程详解

MySQL的JDBC编程详解

《MySQL的JDBC编程详解》：本文主要介绍MySQL的JDBC编程,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录前言一、前置知识1. 引入依赖2. 认识 url二、JDBC 操作流程1. JDBC 的写操作2. JDBC 的读操作总结前言本文介绍了mysq
阅读更多...
java.sql.SQLTransientConnectionException连接超时异常原因及解决方案

java.sql.SQLTransientConnectionException连接超时异常原因及解决方案

《java.sql.SQLTransientConnectionException连接超时异常原因及解决方案》：本文主要介绍java.sql.SQLTransientConnectionExcep... 目录一、引言二、异常信息分析三、可能的原因3.1 连接池配置不合理3.2 数据库负载过高3.3 连接泄漏
阅读更多...
Linux下MySQL数据库定时备份脚本与Crontab配置教学

Linux下MySQL数据库定时备份脚本与Crontab配置教学

《Linux下MySQL数据库定时备份脚本与Crontab配置教学》在生产环境中,数据库是核心资产之一,定期备份数据库可以有效防止意外数据丢失,本文将分享一份MySQL定时备份脚本,并讲解如何通过cr... 目录备份脚本详解脚本功能说明授权与可执行权限使用 Crontab 定时执行编辑 Crontab添加定
阅读更多...
MySQL中On duplicate key update的实现示例

MySQL中On duplicate key update的实现示例

《MySQL中Onduplicatekeyupdate的实现示例》ONDUPLICATEKEYUPDATE是一种MySQL的语法,它在插入新数据时,如果遇到唯一键冲突,则会执行更新操作,而不是抛... 目录1/ ON DUPLICATE KEY UPDATE的简介2/ ON DUPLICATE KEY UP
阅读更多...
MySQL分库分表的实践示例

MySQL分库分表的实践示例

《MySQL分库分表的实践示例》MySQL分库分表适用于数据量大或并发压力高的场景,核心技术包括水平/垂直分片和分库,需应对分布式事务、跨库查询等挑战,通过中间件和解决方案实现,最佳实践为合理策略、备... 目录一、分库分表的触发条件1.1 数据量阈值1.2 并发压力二、分库分表的核心技术模块2.1 水平分
阅读更多...
Python与MySQL实现数据库实时同步的详细步骤

Python与MySQL实现数据库实时同步的详细步骤

《Python与MySQL实现数据库实时同步的详细步骤》在日常开发中,数据同步是一项常见的需求,本篇文章将使用Python和MySQL来实现数据库实时同步,我们将围绕数据变更捕获、数据处理和数据写入这... 目录前言摘要概述：数据同步方案1. 基本思路2. mysql Binlog 简介实现步骤与代码示例1
阅读更多...
使用shardingsphere实现mysql数据库分片方式

使用shardingsphere实现mysql数据库分片方式

《使用shardingsphere实现mysql数据库分片方式》本文介绍如何使用ShardingSphere-JDBC在SpringBoot中实现MySQL水平分库,涵盖分片策略、路由算法及零侵入配置... 目录一、ShardingSphere 简介1.1 对比1.2 核心概念1.3 Sharding-Sp
阅读更多...
MySQL 表空却 ibd 文件过大的问题及解决方法

MySQL 表空却 ibd 文件过大的问题及解决方法

《MySQL表空却ibd文件过大的问题及解决方法》本文给大家介绍MySQL表空却ibd文件过大的问题及解决方法,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考... 目录一、问题背景：表空却 “吃满” 磁盘的怪事二、问题复现：一步步编程还原异常场景1. 准备测试源表与数据
阅读更多...
Mac电脑如何通过 IntelliJ IDEA 远程连接 MySQL

Mac电脑如何通过 IntelliJ IDEA 远程连接 MySQL

《Mac电脑如何通过IntelliJIDEA远程连接MySQL》本文详解Mac通过IntelliJIDEA远程连接MySQL的步骤,本文通过图文并茂的形式给大家介绍的非常详细,感兴趣的朋友跟... 目录MAC电脑通过 IntelliJ IDEA 远程连接 mysql 的详细教程一、前缀条件确认二、打开 ID
阅读更多...
MySQL的配置文件详解及实例代码

MySQL的配置文件详解及实例代码

《MySQL的配置文件详解及实例代码》MySQL的配置文件是服务器运行的重要组成部分,用于设置服务器操作的各种参数,下面：本文主要介绍MySQL配置文件的相关资料,文中通过代码介绍的非常详细,需要... 目录前言一、配置文件结构1.[mysqld]2.[client]3.[mysql]4.[mysqldum
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2