单表查询练习

目录

题目：

制定约束：

添加表格信息：

所需查询的信息：

实验步骤：

第一步：制作表格

创建新的数据库

创建表格约束：

为表格加入数据：

第二步：查询信息

---

题目：

制定约束：

素材： 表名：worker-- 表中字段均为中文，比如 部门号 工资 职工号 参加工作 等
CREATE TABLE `worker` (
?`部门号` int(11) NOT NULL,
?`职工号` int(11) NOT NULL,
?`工作时间` date NOT NULL,
?`工资` float(8,2) NOT NULL,
?`政治面貌` varchar(10) NOT NULL DEFAULT '群众',
?`姓名` varchar(20) NOT NULL,
?`出生日期` date NOT NULL,
?PRIMARY KEY (`职工号`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

添加表格信息：

INSERT INTO worker (部门号, 职工号, 工作时间, 工资, 政治面貌, 姓名, 出生日期) VALUES (101, 1001, '2015-5-4', 3500.00, '群众', '张三', '1990-7-1');

INSERT INTO worker (部门号, 职工号, 工作时间, 工资, 政治面貌, 姓名, 出生日期) VALUES (101, 1002, '2017-2-6', 3200.00, '团员', '李四', '1997-2-8');

INSERT INTO worker (部门号, 职工号, 工作时间, 工资, 政治面貌, 姓名, 出生日期) VALUES (102, 1003, '2011-1-4', 8500.00, '党员', '王亮', '1983-6-8');

INSERT INTO worker (部门号, 职工号, 工作时间, 工资, 政治面貌, 姓名, 出生日期) VALUES (102, 1004, '2016-10-10', 5500.00, '群众', '赵六', '1994-9-5');

INSERT INTO worker (部门号, 职工号, 工作时间, 工资, 政治面貌, 姓名, 出生日期) VALUES (102, 1005, '2014-4-1', 4800.00, '党员', '孙八', '1996-9-2');

所需查询的信息：

1、显示所有职工的基本信息。
2、查询所有职工所属部门的部门号，不显示重复的部门号。
3、求出所有职工的人数。
4、列出最高工资和最低工资。
5、列出职工的平均工资和总工资。
6、创建一个只有职工号、姓名和参加工作的新表，名为工作日期表。
7、显示所有女职工的年龄。
8、列出所有姓刘的职工的职工号、姓名和出生日期。
9、列出1960年以前出生的职工的姓名、参加工作日期。
10、列出工资在1000－2000之间的所有职工姓名。
11、列出所有陈姓和李姓的职工姓名。
12、列出所有部门号为2和3的职工号、姓名、党员否

实验步骤：

第一步：制作表格

创建新的数据库

mysql> CREATE DATABASE db_worker;
Query OK, 1 row affected (0.07 sec)

mysql> SHOW DATABASES;
?

+--------------------+
| Database ? ? ? ? ? |
+--------------------+
| db_hero ? ? ? ? ? ?|
| db_user ? ? ? ? ? ?|
| db_worker ? ? ? ? ?|
| information_schema |
| mysql ? ? ? ? ? ? ?|
| performance_schema |
| sys ? ? ? ? ? ? ? ?|
+--------------------+
7 rows in set (0.04 sec)

创建表格约束：

mysql> use db_worker;
Database changed

mysql> CREATE TABLE `worker` (
? ? -> ?`部门号` int(11) NOT NULL,
? ? -> ?`职工号` int(11) NOT NULL,
? ? -> ?`工作时间` date NOT NULL,
? ? -> ?`工资` float(8,2) NOT NULL,
? ? -> ?`政治面貌` varchar(10) NOT NULL DEFAULT '群众',
? ? -> ?`姓名` varchar(20) NOT NULL,
? ? -> ?`出生日期` date NOT NULL,
? ? -> ?PRIMARY KEY (`职工号`)
? ? -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected, 4 warnings (0.08 sec)

检测表格是否创建：

mysql> SHOW TABLES;
+---------------------+
| Tables_in_db_worker |
+---------------------+
| worker              |
+---------------------+
1 row in set (0.01 sec)

mysql> 

检测表格约束是否制定成功：

mysql> DESC worker;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| 部门号       | int         | NO   |     | NULL    |       |
| 职工号       | int         | NO   | PRI | NULL    |       |
| 工作时间     | date        | NO   |     | NULL    |       |
| 工资         | float(8,2)  | NO   |     | NULL    |       |
| 政治面貌     | varchar(10) | NO   |     | 群众    |       |
| 姓名         | varchar(20) | NO   |     | NULL    |       |
| 出生日期     | date        | NO   |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
7 rows in set (0.02 sec)

为表格加入数据：

INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生日期`) VALUES (101, 1001, '2015-5-4', 3500.00, '群众', '张三', '1990-7-1');
INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生日期`) VALUES (101, 1002, '2017-2-6', 3200.00, '团员', '李四', '1997-2-8');
INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生日期`) VALUES (102, 1003, '2011-1-4', 8500.00, '党员', '王亮', '1983-6-8');
INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生日期`) VALUES (102, 1004, '2016-10-10', 5500.00, '群众', '赵六', '1994-9-5');
INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生日期`) VALUES (102, 1005, '2014-4-1', 4800.00, '党员', '孙八', '1996-9-2');

查看表格：

第二步：查询信息

1、显示所有职工的基本信息。

mysql> select * from worker;
+-----------+-----------+--------------+---------+--------------+--------+--------------+
| 部门号    | 职工号    | 工作时间     | 工资    | 政治面貌     | 姓名   | 出生日期     |
+-----------+-----------+--------------+---------+--------------+--------+--------------+
|       101 |      1001 | 2015-05-04   | 3500.00 | 群众         | 张三   | 1990-07-01   |
|       101 |      1002 | 2017-02-06   | 3200.00 | 团员         | 李四   | 1997-02-08   |
|       102 |      1003 | 2011-01-04   | 8500.00 | 党员         | 王亮   | 1983-06-08   |
|       102 |      1004 | 2016-10-10   | 5500.00 | 群众         | 赵六   | 1994-09-05   |
|       102 |      1005 | 2014-04-01   | 4800.00 | 党员         | 孙八   | 1996-09-02   |
+-----------+-----------+--------------+---------+--------------+--------+--------------+
5 rows in set (0.00 sec)

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

mysql> select distinct `部门号` from worker;
+-----------+
| 部门号    |
+-----------+
|       101 |
|       102 |
+-----------+

3、求出所有职工的人数。

mysql> select count(*) from worker;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.01 sec)

mysql> select count()
    -> ^C
mysql> select count(*) `人数` from worker;
+--------+
| 人数   |
+--------+
|      5 |
+--------+
1 row in set (0.01 sec)

4、列出最高工资和最低工资。

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

5、列出职工的平均工资和总工资。

mysql> select sum(工资),avg(工资) from worker;
+-------------+-------------+
| sum(工资)   | avg(工资)   |
+-------------+-------------+
|    25500.00 | 5100.000000 |
+-------------+-------------+
1 row in set (0.00 sec)

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

mysql> create table worktime  select 职工号,姓名,工作时间 from worker;
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from worktime;
+-----------+--------+--------------+
| 职工号    | 姓名   | 工作时间     |
+-----------+--------+--------------+
|      1001 | 张三   | 2015-05-04   |
|      1002 | 李四   | 2017-02-06   |
|      1003 | 王亮   | 2011-01-04   |
|      1004 | 赵六   | 2016-10-10   |
|      1005 | 孙八   | 2014-04-01   |
+-----------+--------+--------------+
5 rows in set (0.00 sec)

7、显示所有女职工的年龄。

mysql> select 2019 - year(出生日期) as 年龄 from worker;
+--------+
| 年龄   |
+--------+
|     29 |
|     22 |
|     36 |
|     25 |
|     23 |
+--------+
5 rows in set (0.00 sec)

8、列出所有姓刘的职工的职工号、姓名和出生日期。若没有则列出所有姓王的

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

mysql> select 职工号,姓名,出生日期 from worker where 姓名 like '王%';
+-----------+--------+--------------+
| 职工号    | 姓名   | 出生日期     |
+-----------+--------+--------------+
|      1003 | 王亮   | 1983-06-08   |
+-----------+--------+--------------+
1 row in set (0.00 sec)

9、列出1960年以前出生的职工的姓名、参加工作日期。

mysql> select 姓名,工作时间 from worker where 出生日期 < '1990-01-01';
+--------+--------------+
| 姓名   | 工作时间     |
+--------+--------------+
| 王亮   | 2011-01-04   |
+--------+--------------+
1 row in set (0.00 sec)

10、列出工资在3000－4000之间的所有职工姓名。

mysql> select 姓名 from worker where 工资 between 3000 and 4000;
+--------+
| 姓名   |
+--------+
| 张三   |
| 李四   |
+--------+
2 rows in set (0.00 sec)

11、列出所有陈姓和李姓的职工姓名。

mysql> select 姓名 from worker where 姓名 like '张%' or 姓名 like '李%';
+--------+
| 姓名   |
+--------+
| 张三   |
| 李四   |
+--------+
2 rows in set (0.00 sec)

12、列出所有部门号为2和3的职工号、姓名、党员否。

mysql> select 职工号,姓名,政治面貌 from worker where 部门号 like '%2' and 政治面貌 like '党员';
+-----------+--------+--------------+
| 职工号    | 姓名   | 政治面貌     |
+-----------+--------+--------------+
|      1003 | 王亮   | 党员         |
|      1005 | 孙八   | 党员         |
+-----------+--------+--------------+
2 rows in set (0.00 sec)

13.将职工表worker中的职工按出生的先后顺序排序

mysql> select 姓名 from worker order by 出生日期;
+--------+
| 姓名   |
+--------+
| 王亮   |
| 张三   |
| 赵六   |
| 孙八   |
| 李四   |
+--------+
5 rows in set (0.00 sec)

14.显示工资最高的前3名职工的职工号和姓名

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 |        2 |
+-----------+----------+
1 row in set (0.00 sec)

16.统计各部门的工资和平均工资

mysql> select 部门号,sum(工资),avg(工资) from worker group by 部门号;
+-----------+-------------+-------------+
| 部门号    | sum(工资)   | avg(工资)   |
+-----------+-------------+-------------+
|       101 |     6700.00 | 3350.000000 |
|       102 |    18800.00 | 6266.666667 |
+-----------+-------------+-------------+
2 rows in set (0.00 sec)