文章目录
- 第二次作业题目
-
- 1,建库建表
- 2,插入数据
- 3,完成查询
-
- (1),显示所有职工的基本信息
- (2),查询所有职工所属部门的部门号,不显示重复的部门号
- (3),求出所有职工的人数
- (4),列出最高工资和最低工资
- (5),列出职工的平均工资和总工资
- (6),创建一个只有职工号,姓名和参加工作的新表,名为工作日期表
- (7),显示所有党员的年龄
- (8),列出工资在4000-8000之间的所有职工姓名
- (9),列出所有孙姓和李姓的职工姓名
- (10),列出所有部门号为102和103日不是党员的职工号,姓名(不等于{<>[数和字符]\not like[字符]})
- (11),将职工表t worker中的职工按出生的先后顺序排序
- (12),显示工资最高的前3名职工的职工号和姓名
- (13),求出各部门党员的人数
- (14),统计各部门的工资总数和平均工资并保留2位小数
- (15),列出总人数大于等于3的部门号和总人数
第二次作业题目
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)