数据库操作练习

一、题目要求

1、显示所有职工的基本信息。
2、查询所有职工所属部门的部门号，不显示重复的部门号。
3、求出所有职工的人数。
4、列出最高工和最低工资。
5、列出职工的平均工资和总工资。
6、创建一个只有职工号、姓名和参加工作的新表，名为工作日期表。
7、显示所有女职工的年龄。
8、列出所有姓刘的职工的职工号、姓名和出生日期。
9、列出1960年以前出生的职工的姓名、参加工作日期。
10、列出工资在1000-2000之间的所有职工姓名。
11、列出所有陈姓和李姓的职工姓名。
12、列出所有部门号为2和3的职工号、姓名、党员否。
13、将职工表worker中的职工按出生的先后顺序排序
14、显示工资最高的前3名职工的职工号和姓名。
15、求出各部门党员的人数。
16、统计各部门的工资和平均工资
17、列出总人数大于2的部门号和总人数。
二、创建表

mysql> create table `worker`(
    -> `部门号` int(11) not null,
    -> `职工号` int(11) not null,
    -> `工作时间` date not null,
    -> `工资` float(8,2) not null,
    -> `政治面貌` varchar(20) not null default '群众',
    -> `姓名` varchar(20) not null,
    -> `出生日期` date not null,
    -> `年龄` int not null,
    -> `性别` varchar(10) not null default '男',
    -> primary key (`职工号`)
    -> )engine=innodb default charset=utf8 row_format=dynamic;

三、插入数据

mysql> insert into `worker` (`部门号`,`职工号`,`工作时间`,`工资`,`政治面貌`,`姓名`,`出生日期`,`年龄`,`性别`) values(101,1001,'2015-5-4',3500.00,'群众','张三','1990-7-1',33,'男');
Query OK, 1 row affected (0.02 sec)
 
mysql> insert into `worker` (`部门号`,`职工号`,`工作时间`,`工资`,`政治面貌`,`姓名`,`出生日期`,`年龄`,`性别`) values(102,1002,'2017-2-6',3200.00,'团员','李四','1997-2-8',26,'女');
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into `worker` (`部门号`,`职工号`,`工作时间`,`工资`,`政治面貌`,`姓名`,`出生日期`,`年龄`,`性别`) values(103,1003,'2011-1-4',8500.00,'党员','王五','1983-6-8',40,'女');
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into `worker` (`部门号`,`职工号`,`工作时间`,`工资`,`政治面貌`,`姓名`,`出生日期`,`年龄`,`性别`) values(101,1004,'2016-10-10',5500.00,'群众','赵六','1994-9-5',29,'男');
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into `worker` (`部门号`,`职工号`,`工作时间`,`工资`,`政治面貌`,`姓名`,`出生日期`,`年龄`,`性别`) values(102,1005,'2014-4-10',4800.00,'党员','钱七','1992-12-30',31,'男');
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into `worker` (`部门号`,`职工号`,`工作时间`,`工资`,`政治面貌`,`姓名`,`出生日期`,`年龄`,`性别`) values(103,1006,'2017-5-10',4500.00,'党员','孙八','1996-9-30',31,'男');
Query OK, 1 row affected (0.01 sec)

四、查询

1、查询基本信息

mysql> select * from worker;
+--------+--------+------------+---------+----------+------+------------+------+------+
| 部门号 | 职工号 | 工作时间   | 工资    | 政治面貌 | 姓名 | 出生日期   | 年龄 | 性别 |
+--------+--------+------------+---------+----------+------+------------+------+------+
|    101 |   1001 | 2015-05-04 | 3500.00 | 群众     | 张三 | 1990-07-01 |   33 | 男   |
|    102 |   1002 | 2017-02-06 | 3200.00 | 团员     | 李四 | 1997-02-08 |   26 | 女   |
|    103 |   1003 | 2011-01-04 | 8500.00 | 党员     | 王五 | 1983-06-08 |   40 | 女   |
|    101 |   1004 | 2016-10-10 | 5500.00 | 群众     | 赵六 | 1994-09-05 |   29 | 男   |
|    102 |   1005 | 2014-04-10 | 4800.00 | 党员     | 钱七 | 1992-12-30 |   31 | 男   |
|    103 |   1006 | 2017-05-10 | 4500.00 | 党员     | 孙八 | 1996-09-30 |   31 | 男   |
+--------+--------+------------+---------+----------+------+------------+------+------+
6 rows in set (0.00 sec)

2、查询部门号

mysql> select distinct `部门号` from worker;
+--------+
| 部门号 |
+--------+
|    101 |
+--------+
1 row in set (0.00 sec)

3、员工人数

mysql> select count(`职工号`) from worker;
+-----------------+
| count(`职工号`) |
+-----------------+
|               6 |
+-----------------+
1 row in set (0.01 sec)

4、最高工资和最低工资

mysql> select max(`工资`) '最高工资',min(`工资`) '最低工资' from worker;
+----------+----------+
| 最高工资 | 最低工资 |
+----------+----------+
|  8500.00 |  3200.00 |
+----------+----------+
1 row in set (0.00 sec)

5、工资总和和平均工资

mysql> select avg(`工资`) '平均工资',sum(`工资`) '总工资' from worker;
+-------------+----------+
| 平均工资    | 总工资   |
+-------------+----------+
| 5000.000000 | 30000.00 |
+-------------+----------+
1 row in set (0.00 sec)

6、创建一个表

mysql> create table work_date select `职工号`,`姓名`,`工作时间` from worker;
Query OK, 6 rows affected (0.02 sec)
Records: 6  Duplicates: 0  Warnings: 0
 
mysql> select * from work_date;
+--------+------+------------+
| 职工号 | 姓名 | 工作时间   |
+--------+------+------------+
|   1001 | 张三 | 2015-05-04 |
|   1002 | 李四 | 2017-02-06 |
|   1003 | 王五 | 2011-01-04 |
|   1004 | 赵六 | 2016-10-10 |
|   1005 | 钱七 | 2014-04-10 |
|   1006 | 孙八 | 2017-05-10 |
+--------+------+------------+
6 rows in set (0.00 sec)

7、显示女职工年龄

mysql> select `姓名`,`性别`,`年龄` from worker where `性别` like '女';
+------+------+------+
| 姓名 | 性别 | 年龄 |
+------+------+------+
| 李四 | 女   |   26 |
| 王五 | 女   |   40 |
+------+------+------+
2 rows in set (0.00 sec)

8、显示所有姓刘的员工

mysql> select `职工号`,`姓名`,`出生日期` from worker where `姓名` like '刘%';
Empty set (0.00 sec)

9、1960年以前的员工

mysql> select `姓名`,`工作时间` from worker where `出生日期`<19600000;
Empty set, 1 warning (0.00 sec)

10、工资在1000-2000之间的

mysql> select `姓名`,`工作时间` from worker where `出生日期`<19600000;
Empty set, 1 warning (0.00 sec)

11、姓陈或者姓李的的员工

mysql> select `姓名` from worker where `姓名` regexp '^[陈 or 李]';
+------+
| 姓名 |
+------+
| 李四 |
+------+
1 row in set (0.00 sec)

12、列出所有部门号为2和3的职工号、姓名、并判断政治面貌

mysql> select `职工号`,`姓名`,`政治面貌` from worker where `部门号`=2 or 3;
+--------+------+----------+
| 职工号 | 姓名 | 政治面貌 |
+--------+------+----------+
|   1001 | 张三 | 群众     |
|   1002 | 李四 | 团员     |
|   1003 | 王五 | 党员     |
|   1004 | 赵六 | 群众     |
|   1005 | 钱七 | 党员     |
|   1006 | 孙八 | 党员     |
+--------+------+----------+
6 rows in set (0.00 sec)

13、按照出生日期排序

mysql> select * from worker order by `出生日期`;
+--------+--------+------------+---------+----------+------+------------+------+------+
| 部门号 | 职工号 | 工作时间   | 工资    | 政治面貌 | 姓名 | 出生日期   | 年龄 | 性别 |
+--------+--------+------------+---------+----------+------+------------+------+------+
|    103 |   1003 | 2011-01-04 | 8500.00 | 党员     | 王五 | 1983-06-08 |   40 | 女   |
|    101 |   1001 | 2015-05-04 | 3500.00 | 群众     | 张三 | 1990-07-01 |   33 | 男   |
|    102 |   1005 | 2014-04-10 | 4800.00 | 党员     | 钱七 | 1992-12-30 |   31 | 男   |
|    101 |   1004 | 2016-10-10 | 5500.00 | 群众     | 赵六 | 1994-09-05 |   29 | 男   |
|    103 |   1006 | 2017-05-10 | 4500.00 | 党员     | 孙八 | 1996-09-30 |   31 | 男   |
|    102 |   1002 | 2017-02-06 | 3200.00 | 团员     | 李四 | 1997-02-08 |   26 | 女   |
+--------+--------+------------+---------+----------+------+------------+------+------+
6 rows in set (0.00 sec)

14、工资前三排名

mysql> select `职工号`,`姓名` from worker order by `工资` desc limit 3;
+--------+------+
| 职工号 | 姓名 |
+--------+------+
|   1003 | 王五 |
|   1004 | 赵六 |
|   1005 | 钱七 |
+--------+------+
3 rows in set (0.00 sec)

15、求出各个部门党员人数

mysql> select `部门号`,count(`职工号`) from worker
    ->     where `政治面貌`='党员'
    ->     group by `部门号`;
+--------+-----------------+
| 部门号 | count(`职工号`) |
+--------+-----------------+
|    102 |               1 |
+--------+-----------------+
|    103 |               2 |
+--------+-----------------+
1 row in set (0.00 sec)

16、统计各个部门工资总和和平均工资

mysql> select `部门号`,sum(`工资`) '工资总和',avg(`工资`) '平均工资'
    ->     from worker
    ->     group by `部门号`;
+--------+----------+-------------+
| 部门号 | 工资总和 | 平均工资    |
+--------+----------+-------------+
|    101 | 9000.00  | 4500.000000 |
+--------+----------+-------------+
|    102 | 8000.00  | 4000.000000 |
+--------+----------+-------------+
|    103 | 13000.00 | 6500.000000 |
+--------+----------+-------------+
1 row in set (0.00 sec)

17、列出总人数大于2的部门号和总人数

mysql> select `部门号`,count(`职工号`) `总人数` from worker
    ->     group by `部门号`
    ->     having count(`职工号`)>=2;
+--------+--------+
| 部门号 | 总人数 |
+--------+--------+
|    101 |      2 |
+--------+--------+
|    102 |      2 |
+--------+--------+
|    103 |      2 |
+--------+--------+
1 row in set (0.00 sec)