数据库联系——查询-CSDN博客

本文链接：https://blog.csdn.net/Chen_l916/article/details/140593533

1.显示所有职工的基本信息。

MySQL root@(none):mydb8_worker> select * from t_worker;
+---------------+-----------+-------------+---------+----------+--------+------------+
| department_id | worker_id | worker_date | wages   | politics | name   | borth_date |
+---------------+-----------+-------------+---------+----------+--------+------------+
| 101           | 1001      | 2015-05-04  |  7500.0 | 群众     | 张春燕 | 1990-07-01 |
| 101           | 1002      | 2019-02-06  |  5200.0 | 团员     | 李名博 | 1997-02-08 |
| 102           | 1003      | 2008-01-04  | 10500.0 | 党员     | 王博涵 | 1983-06-08 |
| 102           | 1004      | 2016-10-10  |  5500.0 | 群众     | 赵小军 | 1994-09-05 |
| 102           | 1005      | 2014-04-01  |  8800.0 | 党员     | 钱有财 | 1992-12-30 |
| 103           | 1006      | 2019-05-05  |  5500.0 | 党员     | 孙菲菲 | 1996-09-02 |
+---------------+-----------+-------------+---------+----------+--------+------------+

2.查询所有职工所属部门的部门号，不显示重复的部门号。

MySQL root@(none):mydb8_worker> select distinct department_id from t_worker;
+---------------+
| department_id |
+---------------+
| 101           |
| 102           |
| 103           |
+---------------+

3.求出所有职工的人数。

MySQL root@(none):mydb8_worker> select count(1) from t_worker;
+----------+
| count(1) |
+----------+
| 6        |
+----------+

4.列出最高工和最低工资。

MySQL root@(none):mydb8_worker> select max(wages),min(wages) from t_worker;
+------------+------------+
| max(wages) | min(wages) |
+------------+------------+
| 10500.0    | 5200.0     |
+------------+------------+

5.列出职工的平均工资和总工资。

MySQL root@(none):mydb8_worker> select sum(wages),avg(wages) from t_worker;
+------------+-------------+
| sum(wages) | avg(wages)  |
+------------+-------------+
| 43000.0    | 7166.666667 |
+------------+-------------+

6.创建一个只有职工号、姓名和参加工作的新表，名为工作日期表。

MySQL root@(none):mydb8_worker> CREATE TABLE work_log(worker_id int,name varchar(50),work_date date);
Query OK, 0 rows affected
Time: 0.013s
 
MySQL root@(none):mydb8_worker> desc work_log;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| worker_id | int         | YES  |     | <null>  |       |
| name      | varchar(50) | YES  |     | <null>  |       |
| work_date | date        | YES  |     | <null>  |       |
+-----------+-------------+------+-----+---------+-------+

7.显示所有党员的年龄。

MySQL root@(none):mydb8_worker> select year(now()) - year(borth_date) from t_worker;
+--------------------------------+
| year(now()) - year(borth_date) |
+--------------------------------+
| 34                             |
| 27                             |
| 41                             |
| 30                             |
| 32                             |
| 28                             |
+--------------------------------+

8.列出工资在4000-8000之间的所有职工姓名

select name from t_worker where wages>=4000 and wages<=8000;
+--------+
| name   |
+--------+
| 张春燕 |
| 李名博 |
| 赵小军 |
| 孙菲菲 |
+--------+

9.列出所有孙姓和李姓的职工姓名。

SELECT name FROM t_worker WHERE name LIKE '孙%' or name LIKE '李%';
+--------+
| name   |
+--------+
| 李名博 |
| 孙菲菲 |
+--------+

10.列出所有部门号为102和103日不是党员的职工号、姓名。

select worker_id,name from t_worker 
where department_id in 
(select department_id from t_worker 
where department_id=102 or department_id=103) 
and politics != '党员';
+-----------+--------+
| worker_id | name   |
+-----------+--------+
| 1004      | 赵小军 |
+-----------+--------+

11.将职工表t worker中的职工按出生的先后顺序排序。

select * from t_worker 
order by borth_date 
in (select borth_date from t_worker);
+---------------+-----------+-------------+---------+----------+--------+------------+
| department_id | worker_id | worker_date | wages   | politics | name   | borth_date |
+---------------+-----------+-------------+---------+----------+--------+------------+
| 101           | 1001      | 2015-05-04  |  7500.0 | 群众     | 张春燕 | 1990-07-01 |
| 101           | 1002      | 2019-02-06  |  5200.0 | 团员     | 李名博 | 1997-02-08 |
| 102           | 1003      | 2008-01-04  | 10500.0 | 党员     | 王博涵 | 1983-06-08 |
| 102           | 1004      | 2016-10-10  |  5500.0 | 群众     | 赵小军 | 1994-09-05 |
| 102           | 1005      | 2014-04-01  |  8800.0 | 党员     | 钱有财 | 1992-12-30 |
| 103           | 1006      | 2019-05-05  |  5500.0 | 党员     | 孙菲菲 | 1996-09-02 |
+---------------+-----------+-------------+---------+----------+--------+------------+

12.显示工资最高的前3名职工的职工号和姓名。

select worker_id,name,wages from t_worker order by wages desc limit 3;
+-----------+--------+---------+
| worker_id | name   | wages   |
+-----------+--------+---------+
| 1003      | 王博涵 | 10500.0 |
| 1005      | 钱有财 |  8800.0 |
| 1001      | 张春燕 |  7500.0 |
+-----------+--------+---------+

13.求出各部门党员的人数。

select department_id,count(1) from t_worker where politics ='党员' group 
                             -> by department_id;
+---------------+----------+
| department_id | count(1) |
+---------------+----------+
| 102           | 2        |
| 103           | 1        |
+---------------+----------+

14.统计各部门的工资和平均工资并保留2位小数

select department_id,round(avg(wages),2) 
from t_worker
group by department_id;
+---------------+---------------------+
| department_id | round(avg(wages),2) |
+---------------+---------------------+
| 101           | 6350.0              |
| 102           | 8266.67             |
| 103           | 5500.0              |
+---------------+---------------------+

15.列出总人数大于等于3的部门号和总人数。

select department_id,count(1) 人数 
from t_worker 
group by department_id having count(1)>=3 ;
+---------------+------+
| department_id | 人数 |
+---------------+------+
| 102           | 3    |
+---------------+------+