mysql 单表查询-CSDN博客

本文链接：https://blog.csdn.net/2201_75540599/article/details/132253818

1.建worker表
mysql> create table worker(
-> dept_id int comment '部门号',
-> worker_no int primary key auto_increment comment '员工编号',
-> hire_date datetime not null comment '入职时间',
-> salary int not null comment '薪资',
-> worker_face varchar(30) not null comment '政治面貌',
-> worker_name varchar(30) not null comment '姓名',
-> worker_age tinyint not null check (worker_age >= 18),
-> brith_date datetime not null comment '出生日期'
-> )ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected, 1 warning (0.03 sec)

查看结果

2.建department表
mysql> create table department(
-> dept_id int primary key auto_increment comment '部门编号',
-> dept_name varchar(50) not null comment '部门名称'
-> )auto_increment=1001;
Query OK, 0 rows affected (0.04 sec)
3.添加外键
mysql> alter table worker add constraint worker_dept_fk foreign key (dept_id) references department(dept_id);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
4.在department表中，添加数据：
mysql> insert into department values(null,'科技部'),(null,'后勤部'),(null,'财务部'),(null,'法务部');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
添加完成后，我们就可以查看表的结构

mysql> select *from department;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 1001 | 科技部 |
| 1002 | 后勤部 |
| 1003 | 财务部 |
| 1004 | 法务部 |
+---------+-----------+
4 rows in set (0.00 sec)

5.在worker表中添加数据
mysql> insert into worker values(1001,null,'2008-08-15',5000,'党员','樊富田','20','1991-01-11','工程师');
Query OK, 1 row affected (0.01 sec)

mysql> insert into worker values(1001,null,'2008-06-14',6000,'党员','哈思瑗','19','1992-03-15','工程师');
Query OK, 1 row affected (0.01 sec)

mysql> insert into worker values(1001,null,'2009-05-24',5200,'群众','李言','21','1991-02-15','工程师');
Query OK, 1 row affected (0.01 sec)

mysql> insert into worker values(1002,null,'2008-05-14',4200,'群众','张三','21','1992-07-15','保洁');
Query OK, 1 row affected (0.01 sec)

mysql> insert into worker values(1002,null,'2010-05-14',4200,'群众','杨幂','31','1982-07-15','保洁');
Query OK, 1 row affected (0.01 sec)

mysql> insert into worker values(1002,null,'2011-09-14',4000,'群众','张韶涵','25','1987-04-25','保洁');
Query OK, 1 row affected (0.01 sec)

mysql> insert into worker values(1002,null,'2013-01-14',5000,'群众','杨洋','26','1986-10-25','保安');
Query OK, 1 row affected (0.00 sec)

mysql> insert into worker values(1003,null,'2014-02-18',9000,'党员','赵丽颖','26','1989-11-25','秘书长');
Query OK, 1 row affected (0.01 sec)

mysql> insert into worker values(1003,null,'2014-05-17',8000,'群众','刘诗诗','27','1990-12-23','副秘书长');
Query OK, 1 row affected (0.01 sec)

mysql> insert into worker values(1003,null,'2015-04-19',8500,'群众','迪丽热巴','30','1992-04-13','司机');
Query OK, 1 row affected (0.00 sec)

mysql> insert into worker values(1003,null,'2016-05-20',8200,'群众','古力娜扎','31','1982-04-13','小蜜');
Query OK, 1 row affected (0.01 sec)

mysql> insert into worker values(1004,null,'2012-06-10',11200,'群众','肖战','30','1992-07-17','法务助理');
Query OK, 1 row affected (0.01 sec)

mysql> insert into worker values(1004,null,'2013-04-10',10200,'群众','郑爽','32','1994-04-14','法务专员');
Query OK, 1 row affected (0.00 sec)

mysql> insert into worker values(1004,null,'2013-09-19',10500,'群众','岳云鹏','29','1987-05-14','法务主管');
Query OK, 1 row affected (0.01 sec)

mysql> insert into worker values(1004,null,'2014-04-19',10000,'党员','郭德纲','45','1977-07-14','法务总监');
Query OK, 1 row affected (0.01 sec)
这时候，我们就可以查看worker表中的数据了：

mysql> select *from worker;
+---------+-----------+---------------------+--------+-------------+-------------+------------+---------------------+------------+
| dept_id | worker_no | hire_date | salary | worker_face | worker_name | worker_age | brith_date | worker_job |
+---------+-----------+---------------------+--------+-------------+-------------+------------+---------------------+------------+
| 1001 | 1 | 2008-08-15 00:00:00 | 5000 | 党员 | 樊富田 | 20 | 1991-01-11 00:00:00 | 工程师 |
| 1001 | 2 | 2008-06-14 00:00:00 | 6000 | 党员 | 哈思瑗 | 19 | 1992-03-15 00:00:00 | 工程师 |
| 1001 | 3 | 2009-05-24 00:00:00 | 5200 | 群众 | 李言 | 21 | 1991-02-15 00:00:00 | 工程师 |
| 1002 | 4 | 2008-05-14 00:00:00 | 4200 | 群众 | 张三 | 21 | 1992-07-15 00:00:00 | 保洁 |
| 1002 | 5 | 2010-05-14 00:00:00 | 4200 | 群众 | 杨幂 | 31 | 1982-07-15 00:00:00 | 保洁 |
| 1002 | 6 | 2011-09-14 00:00:00 | 4000 | 群众 | 张韶涵 | 25 | 1987-04-25 00:00:00 | 保洁 |
| 1002 | 7 | 2013-01-14 00:00:00 | 5000 | 群众 | 杨洋 | 26 | 1986-10-25 00:00:00 | 保安 |
| 1003 | 8 | 2014-02-18 00:00:00 | 9000 | 党员 | 赵丽颖 | 26 | 1989-11-25 00:00:00 | 秘书长 |
| 1003 | 9 | 2014-05-17 00:00:00 | 8000 | 群众 | 刘诗诗 | 27 | 1990-12-23 00:00:00 | 副秘书长 |
| 1003 | 10 | 2015-04-19 00:00:00 | 8500 | 群众 | 迪丽热巴 | 30 | 1992-04-13 00:00:00 | 司机 |
| 1003 | 11 | 2016-05-20 00:00:00 | 8200 | 群众 | 古力娜扎 | 31 | 1982-04-13 00:00:00 | 小蜜 |
| 1004 | 12 | 2012-06-10 00:00:00 | 11200 | 群众 | 肖战 | 30 | 1992-07-17 00:00:00 | 法务助理 |
| 1004 | 13 | 2013-04-10 00:00:00 | 10200 | 群众 | 郑爽 | 32 | 1994-04-14 00:00:00 | 法务专员 |
| 1004 | 14 | 2013-09-19 00:00:00 | 10500 | 群众 | 岳云鹏 | 29 | 1987-05-14 00:00:00 | 法务主管 |
| 1004 | 15 | 2014-04-19 00:00:00 | 10000 | 党员 | 郭德纲 | 45 | 1977-07-14 00:00:00 | 法务总监 |
+---------+-----------+---------------------+--------+-------------+-------------+------------+---------------------+------------+
15 rows in set (0.00 sec)

1.显示所有职工的基本信息
mysql> select *from worker;
+---------+-----------+---------------------+--------+-------------+-------------+------------+---------------------+------------+
| dept_id | worker_no | hire_date | salary | worker_face | worker_name | worker_age | brith_date | worker_job |
+---------+-----------+---------------------+--------+-------------+-------------+------------+---------------------+------------+
| 1001 | 1 | 2008-08-15 00:00:00 | 5000 | 党员 | 樊富田 | 20 | 1991-01-11 00:00:00 | 工程师 |
| 1001 | 2 | 2008-06-14 00:00:00 | 6000 | 党员 | 哈思瑗 | 19 | 1992-03-15 00:00:00 | 工程师 |
| 1001 | 3 | 2009-05-24 00:00:00 | 5200 | 群众 | 李言 | 21 | 1991-02-15 00:00:00 | 工程师 |
| 1002 | 4 | 2008-05-14 00:00:00 | 4200 | 群众 | 张三 | 21 | 1992-07-15 00:00:00 | 保洁 |
| 1002 | 5 | 2010-05-14 00:00:00 | 4200 | 群众 | 杨幂 | 31 | 1982-07-15 00:00:00 | 保洁 |
| 1002 | 6 | 2011-09-14 00:00:00 | 4000 | 群众 | 张韶涵 | 25 | 1987-04-25 00:00:00 | 保洁 |
| 1002 | 7 | 2013-01-14 00:00:00 | 5000 | 群众 | 杨洋 | 26 | 1986-10-25 00:00:00 | 保安 |
| 1003 | 8 | 2014-02-18 00:00:00 | 9000 | 党员 | 赵丽颖 | 26 | 1989-11-25 00:00:00 | 秘书长 |
| 1003 | 9 | 2014-05-17 00:00:00 | 8000 | 群众 | 刘诗诗 | 27 | 1990-12-23 00:00:00 | 副秘书长 |
| 1003 | 10 | 2015-04-19 00:00:00 | 8500 | 群众 | 迪丽热巴 | 30 | 1992-04-13 00:00:00 | 司机 |
| 1003 | 11 | 2016-05-20 00:00:00 | 8200 | 群众 | 古力娜扎 | 31 | 1982-04-13 00:00:00 | 小蜜 |
| 1004 | 12 | 2012-06-10 00:00:00 | 11200 | 群众 | 肖战 | 30 | 1992-07-17 00:00:00 | 法务助理 |
| 1004 | 13 | 2013-04-10 00:00:00 | 10200 | 群众 | 郑爽 | 32 | 1994-04-14 00:00:00 | 法务专员 |
| 1004 | 14 | 2013-09-19 00:00:00 | 10500 | 群众 | 岳云鹏 | 29 | 1987-05-14 00:00:00 | 法务主管 |
| 1004 | 15 | 2014-04-19 00:00:00 | 10000 | 党员 | 郭德纲 | 45 | 1977-07-14 00:00:00 | 法务总监 |
+---------+-----------+---------------------+--------+-------------+-------------+------------+---------------------+------------+
15 rows in set (0.00 sec)

2.查询所有职工所属部门的部门号，不显示重复的部门号
mysql> select dept_id,avg(salary),worker_name from worker group by dept_id,worker_name;
+---------+-------------+-------------+
| dept_id | avg(salary) | worker_name |
+---------+-------------+-------------+
| 1001 | 5000.0000 | 樊富田 |
| 1001 | 6000.0000 | 哈思瑗 |
| 1001 | 5200.0000 | 李言 |
| 1002 | 4200.0000 | 张三 |
| 1002 | 4200.0000 | 杨幂 |
| 1002 | 4000.0000 | 张韶涵 |
| 1002 | 5000.0000 | 杨洋 |
| 1003 | 9000.0000 | 赵丽颖 |
| 1003 | 8000.0000 | 刘诗诗 |
| 1003 | 8500.0000 | 迪丽热巴 |
| 1003 | 8200.0000 | 古力娜扎 |
| 1004 | 11200.0000 | 肖战 |
| 1004 | 10200.0000 | 郑爽 |
| 1004 | 10500.0000 | 岳云鹏 |
| 1004 | 10000.0000 | 郭德纲 |
+---------+-------------+-------------+
15 rows in set (0.00 sec)

3.求出各部门职工的人数
mysql> select dept_id,count(worker_name) from worker group by dept_id;
+---------+--------------------+
| dept_id | count(worker_name) |
+---------+--------------------+
| 1001 | 3 |
| 1002 | 4 |
| 1003 | 4 |
| 1004 | 4 |
+---------+--------------------+
4 rows in set (0.00 sec)
4.列出各部门最高工资和最低工资。
mysql> select dept_id,max(salary) from worker group by dept_id;
+---------+-------------+
| dept_id | max(salary) |
+---------+-------------+
| 1001 | 6000 |
| 1002 | 5000 |
| 1003 | 9000 |
| 1004 | 11200 |
+---------+-------------+
4 rows in set (0.00 sec)

mysql> select dept_id,main(salary) from worker group by dept_id;
ERROR 1305 (42000): FUNCTION mydb.main does not exist
mysql> select dept_id,min(salary) from worker group by dept_id;
+---------+-------------+
| dept_id | min(salary) |
+---------+-------------+
| 1001 | 5000 |
| 1002 | 4000 |
| 1003 | 8000 |
| 1004 | 10000 |
+---------+-------------+
4 rows in set (0.00 sec)

5.列出职工的平均工资和总工资
mysql> select dept_id,avg(salary*12) from worker group by dept_id;
+---------+----------------+
| dept_id | avg(salary*12) |
+---------+----------------+
| 1001 | 64800.0000 |
| 1002 | 52200.0000 |
| 1003 | 101100.0000 |
| 1004 | 125700.0000 |
+---------+----------------+
4 rows in set (0.00 sec)

mysql> select dept_id,sum(salary) from worker group by dept_id;
+---------+-------------+
| dept_id | sum(salary) |
+---------+-------------+
| 1001 | 16200 |
| 1002 | 17400 |
| 1003 | 33700 |
| 1004 | 41900 |
+---------+-------------+
4 rows in set (0.00 sec)

6.创建一个只有职工号、姓名和参加工作的新表，名为工作日期表
mysql> create table employee(
-> emp_no int primary key auto_increment comment '员工编号',
-> emp_name varchar(30) not null comment '员工姓名',
-> emp_job varchar(30) not null comment '参加工作'
-> );
Query OK, 0 rows affected (0.04 sec)
7.列出所有姓张的职工的职工号、姓名和出生日期
mysql> select worker_no,worker_name,brith_date from worker where worker_name like'张%';
+-----------+-------------+---------------------+
| worker_no | worker_name | brith_date |
+-----------+-------------+---------------------+
| 4 | 张三 | 1992-07-15 00:00:00 |
| 6 | 张韶涵 | 1987-04-25 00:00:00 |
+-----------+-------------+---------------------+
2 rows in set (0.00 sec)
8.列出1987-04-25年以前出生的职工的姓名、编号，参加工作日期
mysql> select worker_name,worker_no,hire_date from worker where brith_date > '1987-04-25';
+-------------+-----------+---------------------+
| worker_name | worker_no | hire_date |
+-------------+-----------+---------------------+
| 樊富田 | 1 | 2008-08-15 00:00:00 |
| 哈思瑗 | 2 | 2008-06-14 00:00:00 |
| 李言 | 3 | 2009-05-24 00:00:00 |
| 张三 | 4 | 2008-05-14 00:00:00 |
| 赵丽颖 | 8 | 2014-02-18 00:00:00 |
| 刘诗诗 | 9 | 2014-05-17 00:00:00 |
| 迪丽热巴 | 10 | 2015-04-19 00:00:00 |
| 肖战 | 12 | 2012-06-10 00:00:00 |
| 郑爽 | 13 | 2013-04-10 00:00:00 |
| 岳云鹏 | 14 | 2013-09-19 00:00:00 |
+-------------+-----------+---------------------+
10 rows in set (0.00 sec)

9.列出工资在4000－8000之间的所有职工姓名
mysql> select worker_name from worker where salary > 4000 and salary < 8000;
+-------------+
| worker_name |
+-------------+
| 樊富田 |
| 哈思瑗 |
| 李言 |
| 张三 |
| 杨幂 |
| 杨洋 |
+-------------+
6 rows in set (0.00 sec)
10.列出所有李姓和张姓的职工姓名
mysql> select worker_name from worker where worker_name like'李%' or worker_name like'张%';
+-------------+
| worker_name |
+-------------+
| 李言 |
| 张三 |
| 张韶涵 |
+-------------+
3 rows in set (0.00 sec)
11.列出所有部门号为1002和1003的职工号、姓名、党员否
mysql> select dept_id,worker_name,worker_face from worker where dept_id in (1002,1003);
+---------+-------------+-------------+
| dept_id | worker_name | worker_face |
+---------+-------------+-------------+
| 1002 | 张三 | 群众 |
| 1002 | 杨幂 | 群众 |
| 1002 | 张韶涵 | 群众 |
| 1002 | 杨洋 | 群众 |
| 1003 | 赵丽颖 | 党员 |
| 1003 | 刘诗诗 | 群众 |
| 1003 | 迪丽热巴 | 群众 |
| 1003 | 古力娜扎 | 群众 |
+---------+-------------+-------------+
8 rows in set (0.00 sec)
12.将职工表worker中的职工按出生的先后顺序排序
mysql> select worker_name,hire_date,salary from worker order by hire_date;
+-------------+---------------------+--------+
| worker_name | hire_date | salary |
+-------------+---------------------+--------+
| 张三 | 2008-05-14 00:00:00 | 4200 |
| 哈思瑗 | 2008-06-14 00:00:00 | 6000 |
| 樊富田 | 2008-08-15 00:00:00 | 5000 |
| 李言 | 2009-05-24 00:00:00 | 5200 |
| 杨幂 | 2010-05-14 00:00:00 | 4200 |
| 张韶涵 | 2011-09-14 00:00:00 | 4000 |
| 肖战 | 2012-06-10 00:00:00 | 11200 |
| 杨洋 | 2013-01-14 00:00:00 | 5000 |
| 郑爽 | 2013-04-10 00:00:00 | 10200 |
| 岳云鹏 | 2013-09-19 00:00:00 | 10500 |
| 赵丽颖 | 2014-02-18 00:00:00 | 9000 |
| 郭德纲 | 2014-04-19 00:00:00 | 10000 |
| 刘诗诗 | 2014-05-17 00:00:00 | 8000 |
| 迪丽热巴 | 2015-04-19 00:00:00 | 8500 |
| 古力娜扎 | 2016-05-20 00:00:00 | 8200 |
+-------------+---------------------+--------+
15 rows in set (0.00 sec)