数据库作业

LIl_

已于 2024-11-15 18:49:01 修改

阅读量448

点赞数 3

文章标签：数据库

于 2024-10-27 00:13:50 首次发布

本文链接：https://blog.csdn.net/syzzz0/article/details/143260129

版权

第一次作业

1.编写一篇博客记录mysql任意版本的安装和卸载过程，注意编写的逻辑性

我选择的是以解压方式来安装 5.7.28版本的MySQL

1.1 解压——注意路径不可以包含中文
1.2 在安装文件夹里创建data文件夹和my.ini文件
1.3 配置my.ini文件

[mysqld]
# skip_grant_tables
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=D:\\MySQL\\mysql-5.7.28-winx64
# 设置mysql数据库的数据的存放目录
datadir=D:\\MySQL\\mysql-5.7.28-winx64\\data
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password

[client]
#password = your_password
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8mb4

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4

下面为一定要修改的配置内容

#设置mysql的安装目录
basedir=D:\MySQL\mysql-5.7.28-winx64
#设置mysql数据库的数据的存放目录
datadir=D:\MySQL\mysql-5.7.28-winx64\data

1.4 配置环境变量

1.4.1.win搜索环境变量，打开编辑系统环境变量
1.4.2.点击点环境变量
1.4.3.点击Path
1.4.4.点击新建，粘贴MySQL安装目录下到bin文件夹的路径

2.使用任意绘图软件自学并结合上课所学内容完成数据库原理图绘制

在这里插入图片描述

3.根据以下需求完成图书管理系统数据库及表设计，并建库建表，并截图创建表的详细信息(desc 表名),不用添加数据

用户表: 字段: 姓名，用户名，密码，电话，住址，专业及年级
图书表: 字段: 图书名，分类，介绍，出版社，入库时间
借阅登记表: 借阅人、图书名、借阅时间、是否归还

1. 用户表: 字段: 姓名，用户名，密码，电话，住址，专业及年级

mysql> show databases;
+---------------------------+
| Database                  |
+---------------------------+
| information_schema        |
| li_test_lls               |
| library_management_system |
| mysql                     |
| performance_schema        |
| sys                       |
+---------------------------+
6 rows in set (0.00 sec)

mysql> use library_management_system
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> create table `client`(
    -> `name` varchar(255) not null primary key,
    -> `username` varchar(255) not null,
    -> `password` varchar(255) not null,
    -> `telephone` int(20) not null unique,
    -> `address` varchar(255) not null,
    -> `major` varchar(255) not null,
    -> `grade` int(10) not null);
Query OK, 0 rows affected (0.03 sec)

mysql> desc client;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| name      | varchar(255) | NO   | PRI | NULL    |       |
| username  | varchar(255) | NO   |     | NULL    |       |
| password  | varchar(255) | NO   |     | NULL    |       |
| telephone | int(20)      | NO   | UNI | NULL    |       |
| address   | varchar(255) | NO   |     | NULL    |       |
| major     | varchar(255) | NO   |     | NULL    |       |
| grade     | int(10)      | NO   |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

2. 图书表: 字段: 图书名，分类，介绍，出版社，入库时间

mysql> create table `book`(
    -> `book_name` varchar(255) not null primary key unique,
    -> `type` varchar(255) not null,
    -> `presentaion` varchar(255) not null,
    -> `publisher` varchar(255) not null,
    -> `storage_time` varchar(255) not null);
Query OK, 0 rows affected (0.03 sec)

mysql> desc book;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| book_name    | varchar(255) | NO   | PRI | NULL    |       |
| type         | varchar(255) | NO   |     | NULL    |       |
| presentaion  | varchar(255) | NO   |     | NULL    |       |
| publisher    | varchar(255) | NO   |     | NULL    |       |
| storage_time | varchar(255) | NO   |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

3. 借阅登记表: 借阅人、图书名、借阅时间、是否归还

mysql> create table `checkout_counter`(
    -> borrower_id int(10) primary key auto_increment,
    -> borrower varchar(255) not null,
    -> book_name varchar(255),
    -> borrowing_time varchar(255),
    -> IsRevert varchar(255));
Query OK, 0 rows affected (0.03 s

mysql> desc checkout_counter;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| borrower_id    | int(10)      | NO   | PRI | NULL    | auto_increment |
| borrower       | varchar(255) | NO   |     | NULL    |                |
| book_name      | varchar(255) | YES  |     | NULL    |                |
| borrowing_time | varchar(255) | YES  |     | NULL    |                |
| IsRevert       | varchar(255) | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

第二次作业

单表查询

查询出部门编号为D2019060011的所有员工
所有财务总监的姓名、编号和部门编号。
找出奖金高于工资的员工。
找出奖金高于工资40%的员工。
找出部门编号为D2019090011中所有财务总监，和部门编号为D2019060011中所有财务专员的详细资料。
找出部门编号为D2019090001中所有总经理，部门编号为D2019090011中所有财务总监，还有即不是总经理又不是销售总监但其工资大或等于4000的所有员工详细资料。
有奖金的工种。
无奖金或奖金低于1000的员工。
查询名字由两个字组成的员工。
查询2020年入职的员工。
查询所有员工详细信息，用编号升序排序。
查询所有员工详细信息，用工资降序排序，如果工资相同使用入职日期升序排序。
查询每个部门的平均工资。
求出每个部门的雇员数量。
查询每种工作的最高工资、最低工资、人数.
列出最低薪金大于4000的各种工作及从事此工作的员工人数。
统计各部门工资总和，显示部门编号和该部门雇员的月工资的总和，并且要满足该部门雇员的月工资合计大于6000，输出结果按月工资的合计升序排列。

多表联查

列出所有员工的姓名及其直接上级的姓名。
列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。
列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
列出在财务部工作的员工的姓名，假定不知道财务部的部门编号。
列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导。
列出与陈超从事相同工作的所有员工及部门名称。
查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。
列出薪金高于在财务部工作员工平均薪金的员工姓名和薪金、部门名称。

单表查询

1. 查询出部门编号为D2019060011的所有员工

mysql> select * from employee where department_NO = "D2019060011";
+----+-------------+-----------+--------------+-------------+------------+---------+--------+-------+---------------+
| id | number      | name      | job          | leader_NO   | hire_date  | salary  | bonus  | level | department_NO |
+----+-------------+-----------+--------------+-------------+------------+---------+--------+-------+---------------+
|  5 | E2019050001 | 马金花    | 财务总监     | E2018010001 | 2019-01-01 | 3800.00 | 500.00 |     6 | D2019060011   |
|  6 | E2019050018 | 李昌贵    | 财务专员     | E2019050001 | 2019-04-21 | 2800.00 | 800.00 |     4 | D2019060011   |
+----+-------------+-----------+--------------+-------------+------------+---------+--------+-------+---------------+
2 rows in set (0.00 sec)

2. 所有财务总监的姓名、编号和部门编号。

mysql> select name,number,department_NO from employee where job = "财务总监";
+-----------+-------------+---------------+
| name      | number      | department_NO |
+-----------+-------------+---------------+
| 马金花    | E2019050001 | D2019060011   |
| 刘六一    | E2019020017 | D2019090011   |
+-----------+-------------+---------------+
2 rows in set (0.00 sec)

3. 找出奖金高于工资的员工。

mysql> select name from employee where bonus > salary;
+-----------+
| name      |
+-----------+
| 吴所为    |
| 韩金龙    |
| 王黎明    |
| 龚爱国    |
+-----------+
4 rows in set (0.00 sec)

4. 找出奖金高于工资40%的员工。

mysql> select name from employee where bonus > salary * 1.4;
+-----------+
| name      |
+-----------+
| 吴所为    |
| 韩金龙    |
| 王黎明    |
| 龚爱国    |
+-----------+
4 rows in set (0.01 sec)

5. 找出部门编号为D2019090011中所有财务总监，和部门编号为D2019060011中所有财务专员的详细资料。

mysql> select * from employee
    -> where
    -> (department_NO = "D2019090011" and job = "财务总监")
    -> or
    -> (department_NO = "D2019060011" and job = "财务专员");
+----+-------------+-----------+--------------+-------------+------------+---------+---------+-------+---------------+
| id | number      | name      | job          | leader_NO   | hire_date  | salary  | bonus   | level | department_NO |
+----+-------------+-----------+--------------+-------------+------------+---------+---------+-------+---------------+
|  6 | E2019050018 | 李昌贵    | 财务专员     | E2019050001 | 2019-04-21 | 2800.00 |  800.00 |     4 | D2019060011   |
| 15 | E2019020017 | 刘六一    | 财务总监     | E2018070003 | 2019-02-16 | 3800.00 | 1000.00 |     6 | D2019090011   |
+----+-------------+-----------+--------------+-------------+------------+---------+---------+-------+---------------+
2 rows in set (0.03 sec)

6. 找出部门编号为D2019090001中所有总经理，部门编号为D2019090011中所有财务总监，还有即不是总经理又不是销售总监但其工资大或等于4000的所有员工详细资料。

mysql> select * from employee
    -> where ( department_NO = "D2019090001" and job = "总经理" ) or ( department_NO = "D2019090011" and job = "财务总监" ) or ( job not in ("总经理","销售总监") and salary >= 4000 );
+----+-------------+-----------+-----------------+-------------+------------+---------+---------+-------+---------------+
| id | number      | name      | job             | leader_NO   | hire_date  | salary  | bonus   | level | department_NO |
+----+-------------+-----------+-----------------+-------------+------------+---------+---------+-------+---------------+
|  2 | E2018070003 | 韩金龙    | 总经理          | NULL        | 2018-07-01 | 2800.00 | 4000.00 |     8 | D2019090001   |
| 11 | E2019060009 | 廖云龙    | 技术总监        | E2018010001 | 2019-06-01 | 4800.00 | 2000.00 |     7 | D2019060012   |
| 12 | E2019120021 | 刘盛会    | 研发工程师      | E2019060009 | 2019-12-11 | 4800.00 |  500.00 |     5 | D2019060012   |
| 13 | E2019020001 | 马明全    | 高级工程师      | E2019060009 | 2019-02-01 | 4800.00 | 1000.00 |     6 | D2019060012   |
| 15 | E2019020017 | 刘六一    | 财务总监        | E2018070003 | 2019-02-16 | 3800.00 | 1000.00 |     6 | D2019090011   |
| 16 | E2020020012 | 陈超      | 研发工程师      | E2019060009 | 2020-02-18 | 4200.00 |  500.00 |     5 | D2019060012   |
+----+-------------+-----------+-----------------+-------------+------------+---------+---------+-------+---------------+
6 rows in set (0.00 sec)

7. 有奖金的工种。

mysql> select * from employee
    -> where bonus is not null;
+----+-------------+-----------+-----------------+-------------+------------+---------+---------+-------+---------------+
| id | number      | name      | job             | leader_NO   | hire_date  | salary  | bonus   | level | department_NO |
+----+-------------+-----------+-----------------+-------------+------------+---------+---------+-------+---------------+
|  1 | E2018010001 | 吴所为    | 总经理          | NULL        | 2018-01-01 | 2800.00 | 4000.00 |     9 | D2019060001   |
|  2 | E2018070003 | 韩金龙    | 总经理          | NULL        | 2018-07-01 | 2800.00 | 4000.00 |     8 | D2019090001   |
|  3 | E2018060002 | 王黎明    | 总经理          | NULL        | 2018-06-01 | 2800.00 | 4000.00 |     8 | D2019060002   |
|  4 | E2018020002 | 龚爱国    | 总经理          | NULL        | 2018-02-01 | 2800.00 | 4000.00 |     8 | D2020010001   |
|  5 | E2019050001 | 马金花    | 财务总监        | E2018010001 | 2019-01-01 | 3800.00 |  500.00 |     6 | D2019060011   |
|  6 | E2019050018 | 李昌贵    | 财务专员        | E2019050001 | 2019-04-21 | 2800.00 |  800.00 |     4 | D2019060011   |
|  9 | E2020020023 | 繁茂森    | 销售专员        | E2019060005 | 2020-02-01 | 2800.00 |    0.00 |     4 | D2019060014   |
| 10 | E2019060005 | 张善民    | 销售经理        | E2018010001 | 2019-06-01 | 2800.00 |  500.00 |     6 | D2019060014   |
| 11 | E2019060009 | 廖云龙    | 技术总监        | E2018010001 | 2019-06-01 | 4800.00 | 2000.00 |     7 | D2019060012   |
| 12 | E2019120021 | 刘盛会    | 研发工程师      | E2019060009 | 2019-12-11 | 4800.00 |  500.00 |     5 | D2019060012   |
| 13 | E2019020001 | 马明全    | 高级工程师      | E2019060009 | 2019-02-01 | 4800.00 | 1000.00 |     6 | D2019060012   |
| 14 | E2019120015 | 李意      | 行政专员        | E2018070003 | 2019-12-20 | 2800.00 |  500.00 |     4 | D2019090001   |
| 15 | E2019020017 | 刘六一    | 财务总监        | E2018070003 | 2019-02-16 | 3800.00 | 1000.00 |     6 | D2019090011   |
| 16 | E2020020012 | 陈超      | 研发工程师      | E2019060009 | 2020-02-18 | 4200.00 |  500.00 |     5 | D2019060012   |
+----+-------------+-----------+-----------------+-------------+------------+---------+---------+-------+---------------+
14 rows in set (0.00 sec)

** 8. 无奖金或奖金低于1000的员工。**

mysql> select * from employee
    -> where bonus is null or bonus < 1000;
+----+-------------+-----------+-----------------+-------------+------------+---------+--------+-------+---------------+
| id | number      | name      | job             | leader_NO   | hire_date  | salary  | bonus  | level | department_NO |
+----+-------------+-----------+-----------------+-------------+------------+---------+--------+-------+---------------+
|  5 | E2019050001 | 马金花    | 财务总监        | E2018010001 | 2019-01-01 | 3800.00 | 500.00 |     6 | D2019060011   |
|  6 | E2019050018 | 李昌贵    | 财务专员        | E2019050001 | 2019-04-21 | 2800.00 | 800.00 |     4 | D2019060011   |
|  7 | E2019100011 | 王建国    | 网络管理员      | E2018010001 | 2019-10-01 | 3200.00 |   NULL |     5 | D2019060013   |
|  8 | E2019110004 | 黎锦熙    | 网络管理员      | E2019100011 | 2019-11-01 | 3200.00 |   NULL |     5 | D2019060013   |
|  9 | E2020020023 | 繁茂森    | 销售专员        | E2019060005 | 2020-02-01 | 2800.00 |   0.00 |     4 | D2019060014   |
| 10 | E2019060005 | 张善民    | 销售经理        | E2018010001 | 2019-06-01 | 2800.00 | 500.00 |     6 | D2019060014   |
| 12 | E2019120021 | 刘盛会    | 研发工程师      | E2019060009 | 2019-12-11 | 4800.00 | 500.00 |     5 | D2019060012   |
| 14 | E2019120015 | 李意      | 行政专员        | E2018070003 | 2019-12-20 | 2800.00 | 500.00 |     4 | D2019090001   |
| 16 | E2020020012 | 陈超      | 研发工程师      | E2019060009 | 2020-02-18 | 4200.00 | 500.00 |     5 | D2019060012   |
+----+-------------+-----------+-----------------+-------------+------------+---------+--------+-------+---------------+
9 rows in set (0.00 sec)

9. 查询名字由两个字组成的员工。

mysql> select name from employee
    -> where name like "__";
+--------+
| name   |
+--------+
| 李意   |
| 陈超   |
+--------+
2 rows in set (0.02 sec)

** 10. 查询2020年入职的员工。**

mysql> select name from employee
    -> where hire_date between "2020-01-01" and "2020-12-31";
+-----------+
| name      |
+-----------+
| 繁茂森    |
| 陈超      |
+-----------+
2 rows in set (0.00 sec)

** 11. 查询所有员工详细信息，用编号升序排序。**

mysql> select * from employee order by number asc;
+----+-------------+-----------+-----------------+-------------+------------+---------+---------+-------+---------------+
| id | number      | name      | job             | leader_NO   | hire_date  | salary  | bonus   | level | department_NO |
+----+-------------+-----------+-----------------+-------------+------------+---------+---------+-------+---------------+
|  1 | E2018010001 | 吴所为    | 总经理          | NULL        | 2018-01-01 | 2800.00 | 4000.00 |     9 | D2019060001   |
|  4 | E2018020002 | 龚爱国    | 总经理          | NULL        | 2018-02-01 | 2800.00 | 4000.00 |     8 | D2020010001   |
|  3 | E2018060002 | 王黎明    | 总经理          | NULL        | 2018-06-01 | 2800.00 | 4000.00 |     8 | D2019060002   |
|  2 | E2018070003 | 韩金龙    | 总经理          | NULL        | 2018-07-01 | 2800.00 | 4000.00 |     8 | D2019090001   |
| 13 | E2019020001 | 马明全    | 高级工程师      | E2019060009 | 2019-02-01 | 4800.00 | 1000.00 |     6 | D2019060012   |
| 15 | E2019020017 | 刘六一    | 财务总监        | E2018070003 | 2019-02-16 | 3800.00 | 1000.00 |     6 | D2019090011   |
|  5 | E2019050001 | 马金花    | 财务总监        | E2018010001 | 2019-01-01 | 3800.00 |  500.00 |     6 | D2019060011   |
|  6 | E2019050018 | 李昌贵    | 财务专员        | E2019050001 | 2019-04-21 | 2800.00 |  800.00 |     4 | D2019060011   |
| 10 | E2019060005 | 张善民    | 销售经理        | E2018010001 | 2019-06-01 | 2800.00 |  500.00 |     6 | D2019060014   |
| 11 | E2019060009 | 廖云龙    | 技术总监        | E2018010001 | 2019-06-01 | 4800.00 | 2000.00 |     7 | D2019060012   |
|  7 | E2019100011 | 王建国    | 网络管理员      | E2018010001 | 2019-10-01 | 3200.00 |    NULL |     5 | D2019060013   |
|  8 | E2019110004 | 黎锦熙    | 网络管理员      | E2019100011 | 2019-11-01 | 3200.00 |    NULL |     5 | D2019060013   |
| 14 | E2019120015 | 李意      | 行政专员        | E2018070003 | 2019-12-20 | 2800.00 |  500.00 |     4 | D2019090001   |
| 12 | E2019120021 | 刘盛会    | 研发工程师      | E2019060009 | 2019-12-11 | 4800.00 |  500.00 |     5 | D2019060012   |
| 16 | E2020020012 | 陈超      | 研发工程师      | E2019060009 | 2020-02-18 | 4200.00 |  500.00 |     5 | D2019060012   |
|  9 | E2020020023 | 繁茂森    | 销售专员        | E2019060005 | 2020-02-01 | 2800.00 |    0.00 |     4 | D2019060014   |
+----+-------------+-----------+-----------------+-------------+------------+---------+---------+-------+---------------+
16 rows in set (0.01 sec)

** 12. 查询所有员工详细信息，用工资降序排序，如果工资相同使用入职日期升序排序。**

mysql> select * from employee order by salary desc,hire_date asc;
+----+-------------+-----------+-----------------+-------------+------------+---------+---------+-------+---------------+
| id | number      | name      | job             | leader_NO   | hire_date  | salary  | bonus   | level | department_NO |
+----+-------------+-----------+-----------------+-------------+------------+---------+---------+-------+---------------+
| 13 | E2019020001 | 马明全    | 高级工程师      | E2019060009 | 2019-02-01 | 4800.00 | 1000.00 |     6 | D2019060012   |
| 11 | E2019060009 | 廖云龙    | 技术总监        | E2018010001 | 2019-06-01 | 4800.00 | 2000.00 |     7 | D2019060012   |
| 12 | E2019120021 | 刘盛会    | 研发工程师      | E2019060009 | 2019-12-11 | 4800.00 |  500.00 |     5 | D2019060012   |
| 16 | E2020020012 | 陈超      | 研发工程师      | E2019060009 | 2020-02-18 | 4200.00 |  500.00 |     5 | D2019060012   |
|  5 | E2019050001 | 马金花    | 财务总监        | E2018010001 | 2019-01-01 | 3800.00 |  500.00 |     6 | D2019060011   |
| 15 | E2019020017 | 刘六一    | 财务总监        | E2018070003 | 2019-02-16 | 3800.00 | 1000.00 |     6 | D2019090011   |
|  7 | E2019100011 | 王建国    | 网络管理员      | E2018010001 | 2019-10-01 | 3200.00 |    NULL |     5 | D2019060013   |
|  8 | E2019110004 | 黎锦熙    | 网络管理员      | E2019100011 | 2019-11-01 | 3200.00 |    NULL |     5 | D2019060013   |
|  1 | E2018010001 | 吴所为    | 总经理          | NULL        | 2018-01-01 | 2800.00 | 4000.00 |     9 | D2019060001   |
|  4 | E2018020002 | 龚爱国    | 总经理          | NULL        | 2018-02-01 | 2800.00 | 4000.00 |     8 | D2020010001   |
|  3 | E2018060002 | 王黎明    | 总经理          | NULL        | 2018-06-01 | 2800.00 | 4000.00 |     8 | D2019060002   |
|  2 | E2018070003 | 韩金龙    | 总经理          | NULL        | 2018-07-01 | 2800.00 | 4000.00 |     8 | D2019090001   |
|  6 | E2019050018 | 李昌贵    | 财务专员        | E2019050001 | 2019-04-21 | 2800.00 |  800.00 |     4 | D2019060011   |
| 10 | E2019060005 | 张善民    | 销售经理        | E2018010001 | 2019-06-01 | 2800.00 |  500.00 |     6 | D2019060014   |
| 14 | E2019120015 | 李意      | 行政专员        | E2018070003 | 2019-12-20 | 2800.00 |  500.00 |     4 | D2019090001   |
|  9 | E2020020023 | 繁茂森    | 销售专员        | E2019060005 | 2020-02-01 | 2800.00 |    0.00 |     4 | D2019060014   |
+----+-------------+-----------+-----------------+-------------+------------+---------+---------+-------+---------------+
16 rows in set (0.01 sec)

13. 查询每个部门的平均工资。

mysql> select department_NO,avg(salary) from employee group by department_NO;
+---------------+-------------+
| department_NO | avg(salary) |
+---------------+-------------+
| D2019060001   | 2800.000000 |
| D2019060002   | 2800.000000 |
| D2019060011   | 3300.000000 |
| D2019060012   | 4650.000000 |
| D2019060013   | 3200.000000 |
| D2019060014   | 2800.000000 |
| D2019090001   | 2800.000000 |
| D2019090011   | 3800.000000 |
| D2020010001   | 2800.000000 |
+---------------+-------------+
9 rows in set (0.01 sec)

** 14. 求出每个部门的雇员数量。**

mysql> select department_NO,count(*) from employee group by department_NO;
+---------------+----------+
| department_NO | count(*) |
+---------------+----------+
| D2019060001   |        1 |
| D2019060002   |        1 |
| D2019060011   |        2 |
| D2019060012   |        4 |
| D2019060013   |        2 |
| D2019060014   |        2 |
| D2019090001   |        2 |
| D2019090011   |        1 |
| D2020010001   |        1 |
+---------------+----------+
9 rows in set (0.00 sec)

** 15. 查询每种工作的最高工资、最低工资、人数.**

mysql> select job,count(*),max(salary),min(salary) from employee group by job;
+-----------------+----------+-------------+-------------+
| job             | count(*) | max(salary) | min(salary) |
+-----------------+----------+-------------+-------------+
| 总经理          |        4 |     2800.00 |     2800.00 |
| 技术总监        |        1 |     4800.00 |     4800.00 |
| 研发工程师      |        2 |     4800.00 |     4200.00 |
| 网络管理员      |        2 |     3200.00 |     3200.00 |
| 行政专员        |        1 |     2800.00 |     2800.00 |
| 财务专员        |        1 |     2800.00 |     2800.00 |
| 财务总监        |        2 |     3800.00 |     3800.00 |
| 销售专员        |        1 |     2800.00 |     2800.00 |
| 销售经理        |        1 |     2800.00 |     2800.00 |
| 高级工程师      |        1 |     4800.00 |     4800.00 |
+-----------------+----------+-------------+-------------+
10 rows in set (0.01 sec)

** 16. 列出最低薪金大于4000的各种工作及从事此工作的员工人数。**

mysql> select job,min(salary),count(1) from employee group by job having min(salary) > 4000;
+-----------------+-------------+----------+
| job             | min(salary) | count(1) |
+-----------------+-------------+----------+
| 技术总监        |     4800.00 |        1 |
| 研发工程师      |     4200.00 |        2 |
| 高级工程师      |     4800.00 |        1 |
+-----------------+-------------+----------+
3 rows in set (0.00 sec)

** 17. 统计各部门工资总和，显示部门编号和该部门雇员的月工资的总和，并且要满足该部门雇员的月工资合计大于6000，输出结果按月工资的合计升序排列。**

mysql> select department_NO,sum(salary) from employee group by department_NO having sum(salary
) > 6000 order by sum(salary) asc;
+---------------+-------------+
| department_NO | sum(salary) |
+---------------+-------------+
| D2019060013   |     6400.00 |
| D2019060011   |     6600.00 |
| D2019060012   |    18600.00 |
+---------------+-------------+
3 rows in set (0.00 sec)

多表联查

** 1. 列出所有员工的姓名及其直接上级的姓名。**

mysql> select s.name 员工name,e.name 领导name from employee e,(select name,leader_NO from employee) s
    -> where e.number = s.leader_NO;
+------------+------------+
| 员工name   | 领导name   |
+------------+------------+
| 马金花     | 吴所为     |
| 李昌贵     | 马金花     |
| 王建国     | 吴所为     |
| 黎锦熙     | 王建国     |
| 繁茂森     | 张善民     |
| 张善民     | 吴所为     |
| 廖云龙     | 吴所为     |
| 刘盛会     | 廖云龙     |
| 马明全     | 廖云龙     |
| 李意       | 韩金龙     |
| 刘六一     | 韩金龙     |
| 陈超       | 廖云龙     |
+------------+------------+
12 rows in set (0.00 sec)

** 2. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。**

mysql> select e2.number,e2.name,d.name from employee e1
    -> join employee e2
    -> on e1.number = e2.leader_NO
    -> join department d
    -> on e2.department_NO = d.number
    -> where e1.hire_date > e2.hire_date;
+-------------+-----------+-----------+
| number      | name      | name      |
+-------------+-----------+-----------+
| E2019020001 | 马明全    | 技术部    |
+-------------+-----------+-----------+
1 row in set (0.00 sec)

** 3. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。**

mysql> select * from employee e
    -> right join department d
    -> on d.number = e.department_NO;
+------+-------------+-----------+-----------------+-------------+------------+---------+---------+-------+---------------+----+-------------+--------------+----------+-------------+
| id   | number      | name      | job             | leader_NO   | hire_date  | salary  | bonus   | level | department_NO | id | number      | name         | location | super_NO    |
+------+-------------+-----------+-----------------+-------------+------------+---------+---------+-------+---------------+----+-------------+--------------+----------+-------------+
|    1 | E2018010001 | 吴所为    | 总经理          | NULL        | 2018-01-01 | 2800.00 | 4000.00 |     9 | D2019060001   |  3 | D2019060001 | 成都中心     | 成都     | D2019050001 |
|    2 | E2018070003 | 韩金龙    | 总经理          | NULL        | 2018-07-01 | 2800.00 | 4000.00 |     8 | D2019090001   |  5 | D2019090001 | 上海中心     | 上海     | D2019050001 |
|    3 | E2018060002 | 王黎明    | 总经理          | NULL        | 2018-06-01 | 2800.00 | 4000.00 |     8 | D2019060002   |  4 | D2019060002 | 武汉中心     | 武汉     | D2019050001 |
|    4 | E2018020002 | 龚爱国    | 总经理          | NULL        | 2018-02-01 | 2800.00 | 4000.00 |     8 | D2020010001   |  6 | D2020010001 | 广州中心     | 广州     | D2019050001 |
|    5 | E2019050001 | 马金花    | 财务总监        | E2018010001 | 2019-01-01 | 3800.00 |  500.00 |     6 | D2019060011   |  9 | D2019060011 | 财务部       | 成都     | D2019060001 |
|    6 | E2019050018 | 李昌贵    | 财务专员        | E2019050001 | 2019-04-21 | 2800.00 |  800.00 |     4 | D2019060011   |  9 | D2019060011 | 财务部       | 成都     | D2019060001 |
|    7 | E2019100011 | 王建国    | 网络管理员      | E2018010001 | 2019-10-01 | 3200.00 |    NULL |     5 | D2019060013   | 11 | D2019060013 | 网络部       | 成都     | D2019060001 |
|    8 | E2019110004 | 黎锦熙    | 网络管理员      | E2019100011 | 2019-11-01 | 3200.00 |    NULL |     5 | D2019060013   | 11 | D2019060013 | 网络部       | 成都     | D2019060001 |
|    9 | E2020020023 | 繁茂森    | 销售专员        | E2019060005 | 2020-02-01 | 2800.00 |    0.00 |     4 | D2019060014   | 12 | D2019060014 | 市场部       | 成都     | D2019060001 |
|   10 | E2019060005 | 张善民    | 销售经理        | E2018010001 | 2019-06-01 | 2800.00 |  500.00 |     6 | D2019060014   | 12 | D2019060014 | 市场部       | 成都     | D2019060001 |
|   11 | E2019060009 | 廖云龙    | 技术总监        | E2018010001 | 2019-06-01 | 4800.00 | 2000.00 |     7 | D2019060012   | 10 | D2019060012 | 技术部       | 成都     | D2019060001 |
|   12 | E2019120021 | 刘盛会    | 研发工程师      | E2019060009 | 2019-12-11 | 4800.00 |  500.00 |     5 | D2019060012   | 10 | D2019060012 | 技术部       | 成都     | D2019060001 |
|   13 | E2019020001 | 马明全    | 高级工程师      | E2019060009 | 2019-02-01 | 4800.00 | 1000.00 |     6 | D2019060012   | 10 | D2019060012 | 技术部       | 成都     | D2019060001 |
|   14 | E2019120015 | 李意      | 行政专员        | E2018070003 | 2019-12-20 | 2800.00 |  500.00 |     4 | D2019090001   |  5 | D2019090001 | 上海中心     | 上海     | D2019050001 |
|   15 | E2019020017 | 刘六一    | 财务总监        | E2018070003 | 2019-02-16 | 3800.00 | 1000.00 |     6 | D2019090011   |  7 | D2019090011 | 财务部       | 上海     | D2019090001 |
|   16 | E2020020012 | 陈超      | 研发工程师      | E2019060009 | 2020-02-18 | 4200.00 |  500.00 |     5 | D2019060012   | 10 | D2019060012 | 技术部       | 成都     | D2019060001 |
| NULL | NULL        | NULL      | NULL            | NULL        | NULL       |    NULL |    NULL |  NULL | NULL          |  1 | D2019050001 | 清华集团     | 北京     | NULL        |
| NULL | NULL        | NULL      | NULL            | NULL        | NULL       |    NULL |    NULL |  NULL | NULL          |  2 | D2019050002 | 集团总部     | 北京     | D2019050001 |
| NULL | NULL        | NULL      | NULL            | NULL        | NULL       |    NULL |    NULL |  NULL | NULL          |  8 | D2020020012 | 行政部       | 上海     | D2019090001 |
+------+-------------+-----------+-----------------+-------------+------------+---------+---------+-------+---------------+----+-------------+--------------+----------+-------------+
19 rows in set (0.00 sec)

** 4. 列出在财务部工作的员工的姓名，假定不知道财务部的部门编号。**

mysql> select name from employee e
    -> where department_NO in (select number from department where name = "财务部");
+-----------+
| name      |
+-----------+
| 马金花    |
| 李昌贵    |
| 刘六一    |
+-----------+
3 rows in set (0.01 sec)

mysql> select e.name from employee e
    -> join department d
    -> on e.department_NO = d.number
    -> where d.name = "财务部";
+-----------+
| name      |
+-----------+
| 马金花    |
| 李昌贵    |
| 刘六一    |
+-----------+
3 rows in set (0.00 sec)

** 5. 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导。**

mysql> select e2.*,d.name,e1.name from employee e1
    -> join employee e2
    -> on e1.number = e2.leader_NO
    -> join department d
    -> on d.number = e2.department_NO
    -> where e2.salary > (select avg(salary) from employee);
+----+-------------+-----------+-----------------+-------------+------------+---------+---------+-------+---------------+-----------+-----------+
| id | number      | name      | job             | leader_NO   | hire_date  | salary  | bonus   | level | department_NO | name      | name      |
+----+-------------+-----------+-----------------+-------------+------------+---------+---------+-------+---------------+-----------+-----------+
|  5 | E2019050001 | 马金花    | 财务总监        | E2018010001 | 2019-01-01 | 3800.00 |  500.00 |     6 | D2019060011   | 财务部    | 吴所为    |
| 11 | E2019060009 | 廖云龙    | 技术总监        | E2018010001 | 2019-06-01 | 4800.00 | 2000.00 |     7 | D2019060012   | 技术部    | 吴所为    |
| 15 | E2019020017 | 刘六一    | 财务总监        | E2018070003 | 2019-02-16 | 3800.00 | 1000.00 |     6 | D2019090011   | 财务部    | 韩金龙    |
| 12 | E2019120021 | 刘盛会    | 研发工程师      | E2019060009 | 2019-12-11 | 4800.00 |  500.00 |     5 | D2019060012   | 技术部    | 廖云龙    |
| 13 | E2019020001 | 马明全    | 高级工程师      | E2019060009 | 2019-02-01 | 4800.00 | 1000.00 |     6 | D2019060012   | 技术部    | 廖云龙    |
| 16 | E2020020012 | 陈超      | 研发工程师      | E2019060009 | 2020-02-18 | 4200.00 |  500.00 |     5 | D2019060012   | 技术部    | 廖云龙    |
+----+-------------+-----------+-----------------+-------------+------------+---------+---------+-------+---------------+-----------+-----------+
6 rows in set (0.00 sec)

** 6. 列出与陈超从事相同工作的所有员工及部门名称。**

mysql> select e.*,d.name from employee e
    -> join department d
    -> on e.department_NO = d.number
    -> where e.job = (select job from employee where name = "陈超");
+----+-------------+-----------+-----------------+-------------+------------+---------+--------+-------+---------------+-----------+
| id | number      | name      | job             | leader_NO   | hire_date  | salary  | bonus  | level | department_NO | name      |
+----+-------------+-----------+-----------------+-------------+------------+---------+--------+-------+---------------+-----------+
| 12 | E2019120021 | 刘盛会    | 研发工程师      | E2019060009 | 2019-12-11 | 4800.00 | 500.00 |     5 | D2019060012   | 技术部    |
| 16 | E2020020012 | 陈超      | 研发工程师      | E2019060009 | 2020-02-18 | 4200.00 | 500.00 |     5 | D2019060012   | 技术部    |
+----+-------------+-----------+-----------------+-------------+------------+---------+--------+-------+---------------+-----------+
2 rows in set (0.02 sec)

** 7. 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。**

mysql> select res.department_NO,d.name,d.location,res.count
    -> from (select department_NO,count(*) count from employee group by
    -> department_NO) res
    -> join department d
    -> on res.department_NO = d.number;
+---------------+--------------+----------+-------+
| department_NO | name         | location | count |
+---------------+--------------+----------+-------+
| D2019060001   | 成都中心     | 成都     |     1 |
| D2019060002   | 武汉中心     | 武汉     |     1 |
| D2019090001   | 上海中心     | 上海     |     2 |
| D2020010001   | 广州中心     | 广州     |     1 |
| D2019090011   | 财务部       | 上海     |     1 |
| D2019060011   | 财务部       | 成都     |     2 |
| D2019060012   | 技术部       | 成都     |     4 |
| D2019060013   | 网络部       | 成都     |     2 |
| D2019060014   | 市场部       | 成都     |     2 |
+---------------+--------------+----------+-------+
9 rows in set (0.00 sec)

** 8. 列出薪金高于在财务部工作员工平均薪金的员工姓名和薪金、部门名称。**

mysql> select e.name,e.salary,d.name from employee e
    -> join department d
    -> on e.department_NO = d.number
    -> where e.salary > (select avg(salary) from employee where department_NO in (select number from department where name = "财务部"));
+-----------+---------+-----------+
| name      | salary  | name      |
+-----------+---------+-----------+
| 刘六一    | 3800.00 | 财务部    |
| 马金花    | 3800.00 | 财务部    |
| 廖云龙    | 4800.00 | 技术部    |
| 刘盛会    | 4800.00 | 技术部    |
| 马明全    | 4800.00 | 技术部    |
| 陈超      | 4200.00 | 技术部    |
+-----------+---------+-----------+
6 rows in set (0.01 sec)