Mysql作业2

最新推荐文章于 2025-12-15 13:56:53 发布

原创最新推荐文章于 2025-12-15 13:56:53 发布 · 197 阅读

CC 4.0 BY-SA版权

文章标签：

mysql> create database mydb8_worker;
Query OK, 1 row affected (0.01 sec)
mysql> use mydb8_worker;
Database changed
mysql> create table t_worker(
    -> department_id int(11) not null comment '部门号',
    -> worker_id int(11) primary key not null comment '职工号',
    -> worker_dare date not null comment '工作时间',
    -> wages float(8,2) not null comment '工资',
    -> politics varchar(10) not null default '群众' comment '政治面貌',
    -> name varchar(20) not null comment '姓名',
    -> borth_date date not null comment '出生日期');
Query OK, 0 rows affected, 3 warnings (0.03 sec)

mysql>
mysql> insert into t_worker values (101, 1001, '2015-5-4', 7500.00, '群众', '张春燕', '1990-7-1');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_worker values (101, 1002, '2019-2-6', 5200.00, '团员', '李名博', '1997-2-8');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_worker values (102, 1003, '2008-1-4', 10500.00, '党员', '王博涵', '1983-6-8');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_worker values (102, 1004, '2016-10-10', 5500.00, '群众', '赵小军', '1994-9-5');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_worker values (102, 1005, '2014-4-1', 8800.00, '党员', '钱有财', '1992-12-30');
Query OK, 1 row affected (0.00 sec)
       insert into t_worker values (103, 1006, '2019-5-5', 5500.00, '党员', '孙菲菲', '1996-9-2');

完成查询

显示所有职工的基本信息
全表查询

mysql> select * from t_worker;
+---------------+-----------+-------------+----------+----------+--------+------------+
| department_id | worker_id | worker_dare | wages    | politics | name   | borth_date |
+---------------+-----------+-------------+----------+----------+--------+------------+
|           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)

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

mysql> select distinct department_id from t_worker;
+---------------+
| department_id |
+---------------+
|           101 |
|           102 |
|           103 |
+---------------+
3 rows in set (0.00 sec)

求出所有职工的人数
聚合函数--count统计数量

mysql> select count(*) from t_worker;
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

列出最高工和最低工资
max、min

mysql> select max(wages) '最高工资',min(wages) '最低工资' from t_worker;
+----------+----------+
| 最高工资 | 最低工资 |
+----------+----------+
| 10500.00 |  5200.00 |
+----------+----------+
1 row in set (0.00 sec)

列出职工的平均工资和总工资
avg、sum

mysql> select avg(wages) '平均工资', sum(wages) '工资总和' from t_worker;
+-------------+----------+
| 平均工资    | 工资总和 |
+-------------+----------+
| 7166.666667 | 43000.00 |
+-------------+----------+
1 row in set (0.00 sec)

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

mysql> create table 工作日期表 (
    ->     worker_id int,
    ->     name varchar(20),
    ->     worker_date date
    -> );
Query OK, 0 rows affected (0.02 sec)

显示所有党员的年龄

方法一
mysql> select name, year(curdate()) - year(borth_date) as age from t_worker where politics='党员';
+--------+------+
| name   | age  |
+--------+------+
| 王博涵 |   42 |
| 钱有财 |   33 |
| 孙菲菲 |   29 |
+--------+------+
3 rows in set (0.00 sec)

方法二
mysql> select name, timestampdiff(year, borth_date, curdate()) as age from t_worker where politics = '党员';
+--------+------+
| name   | age  |
+--------+------+
| 王博涵 |   42 |
| 钱有财 |   32 |
| 孙菲菲 |   29 |
+--------+------+
3 rows in set (0.00 sec)

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

mysql> select name from t_worker where wages between 4000 and 8000;

mysql> select name from t_worker where wages >= 4000 and wages <= 8000;
+--------+
| name   |
+--------+
| 张春燕 |
| 李名博 |
| 赵小军 |
| 孙菲菲 |
+--------+
4 rows in set (0.00 sec)

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

mysql> select name from t_worker where name like '孙%' or name like '李%';
+--------+
| name   |
+--------+
| 李名博 |
| 孙菲菲 |
+--------+
2 rows in set (0.00 sec)

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

mysql> select worker_id, name from t_worker where department_id in (102, 103) and politics != '党员';
+-----------+--------+
| worker_id | name   |
+-----------+--------+
|      1004 | 赵小军 |
+-----------+--------+
1 row in set (0.00 sec)

将职工表t_worker中的职工按出生的先后顺序排序

mysql> select * from t_worker order by borth_date;
+---------------+-----------+-------------+----------+----------+--------+------------+
| department_id | worker_id | worker_dare | wages    | politics | name   | borth_date |
+---------------+-----------+-------------+----------+----------+--------+------------+
|           102 |      1003 | 2008-01-04  | 10500.00 | 党员     | 王博涵 | 1983-06-08 |
|           101 |      1001 | 2015-05-04  |  7500.00 | 群众     | 张春燕 | 1990-07-01 |
|           102 |      1005 | 2014-04-01  |  8800.00 | 党员     | 钱有财 | 1992-12-30 |
|           102 |      1004 | 2016-10-10  |  5500.00 | 群众     | 赵小军 | 1994-09-05 |
|           103 |      1006 | 2019-05-05  |  5500.00 | 党员     | 孙菲菲 | 1996-09-02 |
|           101 |      1002 | 2019-02-06  |  5200.00 | 团员     | 李名博 | 1997-02-08 |
+---------------+-----------+-------------+----------+----------+--------+------------+
6 rows in set (0.00 sec)

显示工资最高的前3名职工的职工号和姓名
升序：asc(ascend)，默认 / 降序：desc (descend)

mysql> select worker_id, name from t_worker order by wages desc limit 3;
+-----------+--------+
| worker_id | name   |
+-----------+--------+
|      1003 | 王博涵 |
|      1005 | 钱有财 |
|      1001 | 张春燕 |
+-----------+--------+
3 rows in set (0.00 sec)

求出各部门党员的人数

mysql> select department_id, count(*) as party_member_count from t_worker where politics = '党员' group by department_id;
+---------------+--------------------+
| department_id | party_member_count |
+---------------+--------------------+
|           102 |                  2 |
|           103 |                  1 |
+---------------+--------------------+
2 rows in set (0.00 sec)

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

mysql> select department_id, round(sum(wages), 2) as total_salary, round(avg(wages), 2) as avg_salary from t_worker group by department_id;
+---------------+--------------+------------+
| department_id | total_salary | avg_salary |
+---------------+--------------+------------+
|           101 |        12700 |       6350 |
|           102 |        24800 |    8266.67 |
|           103 |         5500 |       5500 |
+---------------+--------------+------------+
3 rows in set (0.00 sec)

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

mysql> select department_id, count(*) as total_workers from t_worker group by department_id  having count(*) >= 3;
+---------------+---------------+
| department_id | total_workers |
+---------------+---------------+
|           102 |             3 |
+---------------+---------------+
1 row in set (0.00 sec)