欧鹏数据库第二次作业

2024-07-23 02:44:04
开发
12

文章目录

第二次作业题目

第二次作业题目

1,建库建表

create database db_mysql_5; # 名字自拟
use db_mysql_5;
create table t_worker ( department_id int(11) not null comment ‘部门号’ , worker_id int(11) primary key not null comment ‘职工号’ , worker_date 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 ‘出生日期’ );

2,插入数据

insert into t_worker (department_id , worker_id , worker_date , wages , politics , name , borth_date) values (101 , 1001 , ‘2015-5-4’ , 7500.00 , ‘群众’ , ‘张春燕’ , ‘1990-07-01’) , (101 , 1002 , ‘2019-2-6’ ,5200.00 , ‘团员’ , ‘李名博’ , ‘1997-2-8’);
insert into t_worker (department_id , worker_id , worker_date , wages , politics , name , borth_date) values (102 , 1003 , ‘2008-1-4’ , 10500.00 , ‘党员’ , ‘王博涵’ , ‘1983-9-8’), (102 ,1004 , ‘2016-10-10’ , 5500.00 , ‘群众’ , ‘赵小军’ , ‘1994-9-5’), (102 , 1005 , ‘2014-4-1’ , 8800.00 , ‘党员’ , ‘钱有才’ , ’ 1992-12-30’);
insert into t_worker (department_id , worker_id , worker_date , wages , politics , name , borth_date) values (103 , 1006 , ‘2019-5-5’ , 5500.00 , ‘党员’ , ‘孙菲菲’ , ‘1996-9-2’);

3,完成查询

(1),显示所有职工的基本信息
(2),查询所有职工所属部门的部门号，不显示重复的部门号
(3),求出所有职工的人数
(4),列出最高工资和最低工资
(5),列出职工的平均工资和总工资
(6),创建一个只有职工号,姓名和参加工作的新表,名为工作日期表
(7),显示所有党员的年龄
(8),列出工资在4000-8000之间的所有职工姓名
(9),列出所有孙姓和李姓的职工姓名
(10),列出所有部门号为102和103日不是党员的职工号,姓名
(11),将职工表t worker中的职工按出生的先后顺序排序
(12),显示工资最高的前3名职工的职工号和姓名
(13),求出各部门党员的人数
(14),统计各部门的工资和平均工资并保留2位小数
(15),列出总人数大于等于3的部门号和总人数

(1),显示所有职工的基本信息

mysql> select * from t_worker;
+---------------+-----------+-------------+----------+----------+--------+------------+
| department_id | worker_id | worker_date | 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-09-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;
+---------------+
| department_id |
+---------------+
|           101 |
|           102 |
|           103 |
+---------------+
3 rows in set (0.01 sec)

(3),求出所有职工的人数

mysql> select count(*) as '职工人数' from t_worker;
+----------+
| 职工人数 |
+----------+
|        6 |
+----------+
1 row in set (0.01 sec)

(4),列出最高工资和最低工资

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

(5),列出职工的平均工资和总工资

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

(6),创建一个只有职工号,姓名和参加工作的新表,名为工作日期表

mysql> create table work_date_table ( worker_id int primary key , name varchar(20) not null, worker_date date not null );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into work_date_table ( worker_id , name , worker_date ) select worker_id , name , worker_date from t_worker;
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

(7),显示所有党员的年龄

(now函数获取日期和时间,curdate函数获取日期,timestampdiff(返回单位[有second,minute,hour,day,week,moth,quarter{季节},year],时间1,时间2)函数计算两时间戳的不同)

# 写法1:
mysql> select name , timestampdiff(year , borth_date , curdate()) as '年龄' from t_worker;
+--------+------+
| name   | 年龄 |
+--------+------+
| 张春燕 |   34 |
| 李名博 |   27 |
| 王博涵 |   40 |
| 赵小军 |   29 |
| 钱有才 |   31 |
| 孙菲菲 |   27 |
+--------+------+
6 rows in set (0.00 sec)

# 写法2:
mysql> select name , year(now())-year(borth_date) as '年龄' from t_worker;
+--------+------+
| name   | 年龄 |
+--------+------+
| 张春燕 |   34 |
| 李名博 |   27 |
| 王博涵 |   41 |
| 赵小军 |   30 |
| 钱有才 |   32 |
| 孙菲菲 |   28 |
+--------+------+
6 rows in set (0.00 sec)

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

# 写法1
mysql> select name from t_worker where wages between 4000.00 and 8000.00;
+--------+
| name   |
+--------+
| 张春燕 |
| 李名博 |
| 赵小军 |
| 孙菲菲 |
+--------+
4 rows in set (0.00 sec)

# 写法2
mysql> select name from t_worker where wages >4000.00 and wages <8000.00;
+--------+
| name   |
+--------+
| 张春燕 |
| 李名博 |
| 赵小军 |
| 孙菲菲 |
+--------+
4 rows in set (0.00 sec)

(9),列出所有孙姓和李姓的职工姓名

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

(10),列出所有部门号为102和103日不是党员的职工号,姓名(不等于{<>[数和字符]\not like[字符]})

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)

(11),将职工表t worker中的职工按出生的先后顺序排序

mysql> select * from t_worker order by borth_date;
+---------------+-----------+-------------+----------+----------+--------+------------+
| department_id | worker_id | worker_date | wages    | politics | name   | borth_date |
+---------------+-----------+-------------+----------+----------+--------+------------+
|           102 |      1003 | 2008-01-04  | 10500.00 | 党员     | 王博涵 | 1983-09-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)

(12),显示工资最高的前3名职工的职工号和姓名

mysql> select worker_id name from t_worker order by wages desc limit 3;
+------+
| name |
+------+
| 1003 |
| 1005 |
| 1001 |
+------+
3 rows in set (0.00 sec)

(13),求出各部门党员的人数

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

(14),统计各部门的工资总数和平均工资并保留2位小数

mysql> select department_id , sum(wages) as '各部门的工资总数' , round(avg(wages),2) as '各部门的平均工资' from t_worker group by department_id;
+---------------+------------------+------------------+
| department_id | 各部门的工资总数 | 各部门的平均工资 |
+---------------+------------------+------------------+
|           101 |         12700.00 |             6350 |
|           102 |         24800.00 |          8266.67 |
|           103 |          5500.00 |             5500 |
+---------------+------------------+------------------+
3 rows in set (0.00 sec)

(15),列出总人数大于等于3的部门号和总人数

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

原文地址:https://blog.csdn.net/weixin_73298423/article/details/140591417 本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：https://www.suanlizi.com/kf/1815457800386646016.html 如若内容造成侵权/违法违规/事实不符，请联系《酸梨子》网邮箱：1419361763@qq.com进行投诉反馈，一经查实，立即删除！

阅读全部