数据库-练习

题目要求：按照要求建立数据库与表，并完成相应的查询操作

解题步骤如下代码所示：

//建立相关的数据库mydb8_worker
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb10_city        |
| mydb1_test         |
| mydb2_stuinfe      |
| mydb3_employee     |
| mydb4_product      |
| mydb5_sales        |
| mydb6_product      |
| mydb7_openlab      |
| mydb8_worker       |
| mydb9_stusys       |
| mydbx_temp2        |
| mydbx_temp3        |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
16 rows in set (0.01 sec)
//使用该数据库
mysql> use mydb8_worker
Database changed
//建立相关的表
mysql> show tables;
+------------------------+
| Tables_in_mydb8_worker |
+------------------------+
| t_worker               |
+------------------------+
//1
mysql> select * from t_worker;
+---------------+-----------+-------------+----------+----------+-----------+------------+
| department_id | worker_id | worker_date | wages    | politics | name      | borth_data |
+---------------+-----------+-------------+----------+----------+-----------+------------+
|           101 |      1001 | 2015-05-04  |  7500.00 | 群众     | 张春燕    | 1990-07-01 |
|           101 |      1002 | 2019-02-06  |  5200.00 | 团员     | 李名博    | 1997-02-08 |
|           102 |      1003 | 2008-01-04  | 10500.00 | 党员     | 王博涵    | 1983-06-08 |
|           102 |      1004 | 2016-10-10  |  5500.00 | 群众     | 赵小军    | 1994-09-05 |
|           102 |      1005 | 2014-04-01  |  8800.00 | 党员     | 钱有财    | 1992-12-30 |
|           103 |      1006 | 2019-05-05  |  5500.00 | 党员     | 孙菲菲    | 1996-09-02 |
+---------------+-----------+-------------+----------+----------+-----------+------------+
6 rows in set (0.00 sec)

//2
mysql> select distinct department_id'部门号' from t_worker;
+-----------+
| 部门号    |
+-----------+
|       101 |
|       102 |
|       103 |
+-----------+
3 rows in set (0.00 sec)
//3
mysql> select count(1)'职工人数' from t_worker;
+--------------+
| 职工人数     |
+--------------+
|            6 |
+--------------+
1 row in set (0.00 sec)
//4
mysql> select max(wages)'最高工资',min(wages)'最低工资' from t_worker;
+--------------+--------------+
| 最高工资     | 最低工资     |
+--------------+--------------+
|     10500.00 |      5200.00 |
+--------------+--------------+
1 row in set (0.00 sec)
//5
mysql> select avg(wages)'平均工资',sum(wages)'总工资' from t_worker;
+--------------+-----------+
| 平均工资     | 总工资    |
+--------------+-----------+
|  7166.666667 |  43000.00 |
+--------------+-----------+
1 row in set (0.00 sec)
//6
mysql> create table work_date (worker_id int(11) primary key not null,name varchar(20) not null,work varchar(20) not null);
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> show tables;
+------------------------+
| Tables_in_mydb8_worker |
+------------------------+
| t_worker               |
| work_date              |
+------------------------+
2 rows in set (0.00 sec)
mysql> desc work_date;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| worker_id | int         | NO   | PRI | NULL    |       |
| name      | varchar(20) | NO   |     | NULL    |       |
| work      | varchar(20) | NO   |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> select * from t_worker;
+---------------+-----------+-------------+----------+----------+-----------+------------+
| department_id | worker_id | worker_date | wages    | politics | name      | borth_data |
+---------------+-----------+-------------+----------+----------+-----------+------------+
|           101 |      1001 | 2015-05-04  |  7500.00 | 群众     | 张春燕    | 1990-07-01 |
|           101 |      1002 | 2019-02-06  |  5200.00 | 团员     | 李名博    | 1997-02-08 |
|           102 |      1003 | 2008-01-04  | 10500.00 | 党员     | 王博涵    | 1983-06-08 |
|           102 |      1004 | 2016-10-10  |  5500.00 | 群众     | 赵小军    | 1994-09-05 |
|           102 |      1005 | 2014-04-01  |  8800.00 | 党员     | 钱有财    | 1992-12-30 |
|           103 |      1006 | 2019-05-05  |  5500.00 | 党员     | 孙菲菲    | 1996-09-02 |
+---------------+-----------+-------------+----------+----------+-----------+------------+
6 rows in set (0.00 sec)
//7
mysql> select name '姓名',year(now())-year(borth_data) as '党员的年龄' from t_worker where politics='党员' ;
+-----------+-----------------+
| 姓名      | 党员的年龄      |
+-----------+-----------------+
| 王博涵    |              41 |
| 钱有财    |              32 |
| 孙菲菲    |              28 |
+-----------+-----------------+
3 rows in set (0.00 sec)
//8
mysql> select name'姓名' from t_worker where wages between 4000 and 8000;
+-----------+
| 姓名      |
+-----------+
| 张春燕    |
| 李名博    |
| 赵小军    |
| 孙菲菲    |
+-----------+
4 rows in set (0.00 sec)
//9
mysql> select name '姓名' from t_worker where name like'孙%' or name like'李%';
+-----------+
| 姓名      |
+-----------+
| 李名博    |
| 孙菲菲    |
+-----------+
2 rows in set (0.00 sec)
//10
mysql> select worker_id '职工号',name'姓名' from t_worker where (department_id=102 or department_id=103) and politics!='党员';
+-----------+-----------+
| 职工号    | 姓名      |
+-----------+-----------+
|      1004 | 赵小军    |
+-----------+-----------+
1 row in set (0.00 sec)
//11
mysql> select * from t_worker order by borth_data desc;
+---------------+-----------+-------------+----------+----------+-----------+------------+
| department_id | worker_id | worker_date | wages    | politics | name      | borth_data |
+---------------+-----------+-------------+----------+----------+-----------+------------+
|           101 |      1002 | 2019-02-06  |  5200.00 | 团员     | 李名博    | 1997-02-08 |
|           103 |      1006 | 2019-05-05  |  5500.00 | 党员     | 孙菲菲    | 1996-09-02 |
|           102 |      1004 | 2016-10-10  |  5500.00 | 群众     | 赵小军    | 1994-09-05 |
|           102 |      1005 | 2014-04-01  |  8800.00 | 党员     | 钱有财    | 1992-12-30 |
|           101 |      1001 | 2015-05-04  |  7500.00 | 群众     | 张春燕    | 1990-07-01 |
|           102 |      1003 | 2008-01-04  | 10500.00 | 党员     | 王博涵    | 1983-06-08 |
+---------------+-----------+-------------+----------+----------+-----------+------------+
6 rows in set (0.00 sec)
//12
mysql> select department_id'职工号',name'姓名' from t_worker order by wages desc limit 3;
+-----------+-----------+
| 职工号    | 姓名      |
+-----------+-----------+
|       102 | 王博涵    |
|       102 | 钱有财    |
|       101 | 张春燕    |
+-----------+-----------+
3 rows in set (0.00 sec)
//13
mysql> select  distinct department_id'部门号', count(1)'人数' from t_worker where politics=' 党员' group by department_id;
+-----------+--------+
| 部门号    | 人数   |
+-----------+--------+
|       102 |      2 |
|       103 |      1 |
+-----------+--------+
2 rows in set (0.00 sec)
//14
mysql> select department_id'部门号', sum(wages)'工资',round(avg(wages),2)'平均工资' from t_worker
    ->  group by department_id;
+-----------+----------+--------------+
| 部门号    | 工资     | 平均工资     |
+-----------+----------+--------------+
|       101 | 12700.00 |         6350 |
|       102 | 24800.00 |      8266.67 |
|       103 |  5500.00 |         5500 |
+-----------+----------+--------------+
3 rows in set (0.00 sec)
//15
mysql> select department_id'部门号',count(1)'总人数' from t_worker group by department_id having count(1)>=3;
+-----------+-----------+
| 部门号    | 总人数    |
+-----------+-----------+
|       102 |         3 |
+-----------+-----------+
1 row in set (0.00 sec)