mysql多表查询

一、环境准备　

#创建部门
CREATE TABLE IF NOT EXISTS dept (
    did int not null auto_increment PRIMARY KEY,
    dname VARCHAR(50) not null COMMENT '部门名称'
)ENGINE=INNODB DEFAULT charset utf8;


#添加部门数据
INSERT INTO `dept` VALUES ('1', '教学部');
INSERT INTO `dept` VALUES ('2', '销售部');
INSERT INTO `dept` VALUES ('3', '市场部');
INSERT INTO `dept` VALUES ('4', '人事部');
INSERT INTO `dept` VALUES ('5', '鼓励部');

-- 创建人员
DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` tinyint(4) DEFAULT '0',
  `sex` enum('男','女','人妖') NOT NULL DEFAULT '人妖',
  `salary` decimal(10,2) NOT NULL DEFAULT '250.00',
  `hire_date` date NOT NULL,
  `dept_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

-- 添加人员数据

-- 教学部
INSERT INTO `person` VALUES ('1', 'alex', '28', '人妖', '53000.00', '2010-06-21', '1');
INSERT INTO `person` VALUES ('2', 'wupeiqi', '23', '男', '8000.00', '2011-02-21', '1');
INSERT INTO `person` VALUES ('3', 'egon', '30', '男', '6500.00', '2015-06-21', '1');
INSERT INTO `person` VALUES ('4', 'jingnvshen', '18', '女', '6680.00', '2014-06-21', '1');

-- 销售部
INSERT INTO `person` VALUES ('5', '歪歪', '20', '女', '3000.00', '2015-02-21', '2');
INSERT INTO `person` VALUES ('6', '星星', '20', '女', '2000.00', '2018-01-30', '2');
INSERT INTO `person` VALUES ('7', '格格', '20', '女', '2000.00', '2018-02-27', '2');
INSERT INTO `person` VALUES ('8', '周周', '20', '女', '2000.00', '2015-06-21', '2');

-- 市场部
INSERT INTO `person` VALUES ('9', '月月', '21', '女', '4000.00', '2014-07-21', '3');
INSERT INTO `person` VALUES ('10', '安琪', '22', '女', '4000.00', '2015-07-15', '3');

-- 人事部
INSERT INTO `person` VALUES ('11', '周明月', '17', '女', '5000.00', '2014-06-21', '4');

-- 鼓励部
INSERT INTO `person` VALUES ('12', '苍老师', '33', '女', '1000000.00', '2018-02-21', null);

创建表和数据

二、前期知识铺垫

　　1、多表查询语法　

select  字段1,字段2... from 表1,表2... [where 条件]

　　2、笛卡尔乘积

　　如果不加条件直接进行查询,则会出现以下效果,这种结果我们称之为 笛卡尔乘积

　　笛卡尔乘积公式 : A表中数据条数 * B表中数据条数 = 笛卡尔乘积

　　举例：

#查询人员和部门所有信息
select * from person,dept;

　　结果：

mysql> select * from person,dept;
+----+------------+-----+------+---------+------------+---------+-----+--------+
| id | name       | age | sex  | salary  | hire_date  | dept_id | did | dname  |
+----+------------+-----+------+---------+------------+---------+-----+--------+
|  1 | alex       |  28 | 人妖 | 53000   | 2010-06-21 |       1 |   1 | 教学部 |
|  1 | alex       |  28 | 人妖 | 53000   | 2010-06-21 |       1 |   2 | 销售部 |
|  1 | alex       |  28 | 人妖 | 53000   | 2010-06-21 |       1 |   3 | 市场部 |
|  1 | alex       |  28 | 人妖 | 53000   | 2010-06-21 |       1 |   4 | 人事部 |
|  1 | alex       |  28 | 人妖 | 53000   | 2010-06-21 |       1 |   5 | 鼓励部 |
|  2 | wupeiqi    |  23 | 男   | 8000    | 2011-02-21 |       1 |   1 | 教学部 |
|  2 | wupeiqi    |  23 | 男   | 8000    | 2011-02-21 |       1 |   2 | 销售部 |
|  2 | wupeiqi    |  23 | 男   | 8000    | 2011-02-21 |       1 |   3 | 市场部 |
|  2 | wupeiqi    |  23 | 男   | 8000    | 2011-02-21 |       1 |   4 | 人事部 |
|  2 | wupeiqi    |  23 | 男   | 8000    | 2011-02-21 |       1 |   5 | 鼓励部 |
|  3 | egon       |  30 | 男   | 6500    | 2015-06-21 |       1 |   1 | 教学部 |
|  3 | egon       |  30 | 男   | 6500    | 2015-06-21 |       1 |   2 | 销售部 |
|  3 | egon       |  30 | 男   | 6500    | 2015-06-21 |       1 |   3 | 市场部 |
|  3 | egon       |  30 | 男   | 6500    | 2015-06-21 |       1 |   4 | 人事部 |
|  3 | egon       |  30 | 男   | 6500    | 2015-06-21 |       1 |   5 | 鼓励部 |
|  4 | jingnvshen |  18 | 女   | 6680    | 2014-06-21 |       1 |   1 | 教学部 |
|  4 | jingnvshen |  18 | 女   | 6680    | 2014-06-21 |       1 |   2 | 销售部 |
|  4 | jingnvshen |  18 | 女   | 6680    | 2014-06-21 |       1 |   3 | 市场部 |
|  4 | jingnvshen |  18 | 女   | 6680    | 2014-06-21 |       1 |   4 | 人事部 |
|  4 | jingnvshen |  18 | 女   | 6680    | 2014-06-21 |       1 |   5 | 鼓励部 |
|  5 | 歪歪       |  20 | 女   | 3000    | 2015-02-21 |       2 |   1 | 教学部 |
|  5 | 歪歪       |  20 | 女   | 3000    | 2015-02-21 |       2 |   2 | 销售部 |
|  5 | 歪歪       |  20 | 女   | 3000    | 2015-02-21 |       2 |   3 | 市场部 |
|  5 | 歪歪       |  20 | 女   | 3000    | 2015-02-21 |       2 |   4 | 人事部 |
|  5 | 歪歪       |  20 | 女   | 3000    | 2015-02-21 |       2 |   5 | 鼓励部 |
|  6 | 星星       |  20 | 女   | 2000    | 2018-01-30 |       2 |   1 | 教学部 |
|  6 | 星星       |  20 | 女   | 2000    | 2018-01-30 |       2 |   2 | 销售部 |
|  6 | 星星       |  20 | 女   | 2000    | 2018-01-30 |       2 |   3 | 市场部 |
|  6 | 星星       |  20 | 女   | 2000    | 2018-01-30 |       2 |   4 | 人事部 |
|  6 | 星星       |  20 | 女   | 2000    | 2018-01-30 |       2 |   5 | 鼓励部 |
|  7 | 格格       |  20 | 女   | 2000    | 2018-02-27 |       2 |   1 | 教学部 |
|  7 | 格格       |  20 | 女   | 2000    | 2018-02-27 |       2 |   2 | 销售部 |
|  7 | 格格       |  20 | 女   | 2000    | 2018-02-27 |       2 |   3 | 市场部 |
|  7 | 格格       |  20 | 女   | 2000    | 2018-02-27 |       2 |   4 | 人事部 |
|  7 | 格格       |  20 | 女   | 2000    | 2018-02-27 |       2 |   5 | 鼓励部 |
|  8 | 周周       |  20 | 女   | 2000    | 2015-06-21 |       2 |   1 | 教学部 |
|  8 | 周周       |  20 | 女   | 2000    | 2015-06-21 |       2 |   2 | 销售部 |
|  8 | 周周       |  20 | 女   | 2000    | 2015-06-21 |       2 |   3 | 市场部 |
|  8 | 周周       |  20 | 女   | 2000    | 2015-06-21 |       2 |   4 | 人事部 |
|  8 | 周周       |  20 | 女   | 2000    | 2015-06-21 |       2 |   5 | 鼓励部 |
|  9 | 月月       |  21 | 女   | 4000    | 2014-07-21 |       3 |   1 | 教学部 |
|  9 | 月月       |  21 | 女   | 4000    | 2014-07-21 |       3 |   2 | 销售部 |
|  9 | 月月       |  21 | 女   | 4000    | 2014-07-21 |       3 |   3 | 市场部 |
|  9 | 月月       |  21 | 女   | 4000    | 2014-07-21 |       3 |   4 | 人事部 |
|  9 | 月月       |  21 | 女   | 4000    | 2014-07-21 |       3 |   5 | 鼓励部 |
| 10 | 安琪       |  22 | 女   | 4000    | 2015-07-15 |       3 |   1 | 教学部 |
| 10 | 安琪       |  22 | 女   | 4000    | 2015-07-15 |       3 |   2 | 销售部 |
| 10 | 安琪       |  22 | 女   | 4000    | 2015-07-15 |       3 |   3 | 市场部 |
| 10 | 安琪       |  22 | 女   | 4000    | 2015-07-15 |       3 |   4 | 人事部 |
| 10 | 安琪       |  22 | 女   | 4000    | 2015-07-15 |       3 |   5 | 鼓励部 |
| 11 | 周明月     |  17 | 女   | 5000    | 2014-06-21 |       4 |   1 | 教学部 |
| 11 | 周明月     |  17 | 女   | 5000    | 2014-06-21 |       4 |   2 | 销售部 |
| 11 | 周明月     |  17 | 女   | 5000    | 2014-06-21 |       4 |   3 | 市场部 |
| 11 | 周明月     |  17 | 女   | 5000    | 2014-06-21 |       4 |   4 | 人事部 |
| 11 | 周明月     |  17 | 女   | 5000    | 2014-06-21 |       4 |   5 | 鼓励部 |
| 12 | 苍老师     |  33 | 女   | 1000000 | 2018-02-21 | NULL    |   1 | 教学部 |
| 12 | 苍老师     |  33 | 女   | 1000000 | 2018-02-21 | NULL    |   2 | 销售部 |
| 12 | 苍老师     |  33 | 女   | 1000000 | 2018-02-21 | NULL    |   3 | 市场部 |
| 12 | 苍老师     |  33 | 女   | 1000000 | 2018-02-21 | NULL    |   4 | 人事部 |
| 12 | 苍老师     |  33 | 女   | 1000000 | 2018-02-21 | NULL    |   5 | 鼓励部 |
+----+------------+-----+------+---------+------------+---------+-----+--------+
60 rows in set

三、多表查询类型

　　1、多表联合查询　

#查询人员和部门所有信息
select * from person p,dept d where p.dept_id = d.did;

　　结果：

mysql> select * from person p,dept d where p.dept_id = d.did;
+----+------------+-----+------+--------+------------+---------+-----+--------+
| id | name       | age | sex  | salary | hire_date  | dept_id | did | dname  |
+----+------------+-----+------+--------+------------+---------+-----+--------+
|  1 | alex       |  28 | 人妖 | 53000  | 2010-06-21 |       1 |   1 | 教学部 |
|  2 | wupeiqi    |  23 | 男   | 8000   | 2011-02-21 |       1 |   1 | 教学部 |
|  3 | egon       |  30 | 男   | 6500   | 2015-06-21 |       1 |   1 | 教学部 |
|  4 | jingnvshen |  18 | 女   | 6680   | 2014-06-21 |       1 |   1 | 教学部 |
|  5 | 歪歪       |  20 | 女   | 3000   | 2015-02-21 |       2 |   2 | 销售部 |
|  6 | 星星       |  20 | 女   | 2000   | 2018-01-30 |       2 |   2 | 销售部 |
|  7 | 格格       |  20 | 女   | 2000   | 2018-02-27 |       2 |   2 | 销售部 |
|  8 | 周周       |  20 | 女   | 2000   | 2015-06-21 |       2 |   2 | 销售部 |
|  9 | 月月       |  21 | 女   | 4000   | 2014-07-21 |       3 |   3 | 市场部 |
| 10 | 安琪       |  22 | 女   | 4000   | 2015-07-15 |       3 |   3 | 市场部 |
| 11 | 周明月     |  17 | 女   | 5000   | 2014-06-21 |       4 |   4 | 人事部 |
+----+------------+-----+------+--------+------------+---------+-----+--------+
11 rows in set

　　备注：多表查询时,一定要找到两个表中相互关联的字段,并且作为条件使用

　　图解：

　　2、多表连接查询　

#多表连接查询语法(重点)
SELECT 字段列表
    FROM 表1  INNER|LEFT|RIGHT JOIN  表2
ON 表1.字段 = 表2.字段;内连接查询 (只显示符合条件的数据)

　　a、内连接查询 (只显示符合条件的数据)

#查询人员和部门所有信息
select * from person inner join dept  on person.dept_id =dept.did;

　　结果：

mysql> #查询人员和部门所有信息
select * from person inner join dept  on person.dept_id =dept.did;
+----+------------+-----+------+--------+------------+---------+-----+--------+
| id | name       | age | sex  | salary | hire_date  | dept_id | did | dname  |
+----+------------+-----+------+--------+------------+---------+-----+--------+
|  1 | alex       |  28 | 人妖 | 53000  | 2010-06-21 |       1 |   1 | 教学部 |
|  2 | wupeiqi    |  23 | 男   | 8000   | 2011-02-21 |       1 |   1 | 教学部 |
|  3 | egon       |  30 | 男   | 6500   | 2015-06-21 |       1 |   1 | 教学部 |
|  4 | jingnvshen |  18 | 女   | 6680   | 2014-06-21 |       1 |   1 | 教学部 |
|  5 | 歪歪       |  20 | 女   | 3000   | 2015-02-21 |       2 |   2 | 销售部 |
|  6 | 星星       |  20 | 女   | 2000   | 2018-01-30 |       2 |   2 | 销售部 |
|  7 | 格格       |  20 | 女   | 2000   | 2018-02-27 |       2 |   2 | 销售部 |
|  8 | 周周       |  20 | 女   | 2000   | 2015-06-21 |       2 |   2 | 销售部 |
|  9 | 月月       |  21 | 女   | 4000   | 2014-07-21 |       3 |   3 | 市场部 |
| 10 | 安琪       |  22 | 女   | 4000   | 2015-07-15 |       3 |   3 | 市场部 |
| 11 | 周明月     |  17 | 女   | 5000   | 2014-06-21 |       4 |   4 | 人事部 |
+----+------------+-----+------+--------+------------+---------+-----+--------+
11 rows in set

　　备注：内连接查询与多表联合查询的效果是一样的.

　图解：

　　b、左外连接查询 (左边表中的数据优先全部显示)　　

#查询人员和部门所有信息
select * from person p left join  dept d on  p.dept_id =d.did;

　　结果：

mysql> select * from person p left join  dept d on  p.dept_id =d.did;
+----+------------+-----+------+---------+------------+---------+------+--------+
| id | name       | age | sex  | salary  | hire_date  | dept_id | did  | dname  |
+----+------------+-----+------+---------+------------+---------+------+--------+
|  1 | alex       |  28 | 人妖 | 53000   | 2010-06-21 |       1 |    1 | 教学部 |
|  2 | wupeiqi    |  23 | 男   | 8000    | 2011-02-21 |       1 |    1 | 教学部 |
|  3 | egon       |  30 | 男   | 6500    | 2015-06-21 |       1 |    1 | 教学部 |
|  4 | jingnvshen |  18 | 女   | 6680    | 2014-06-21 |       1 |    1 | 教学部 |
|  5 | 歪歪       |  20 | 女   | 3000    | 2015-02-21 |       2 |    2 | 销售部 |
|  6 | 星星       |  20 | 女   | 2000    | 2018-01-30 |       2 |    2 | 销售部 |
|  7 | 格格       |  20 | 女   | 2000    | 2018-02-27 |       2 |    2 | 销售部 |
|  8 | 周周       |  20 | 女   | 2000    | 2015-06-21 |       2 |    2 | 销售部 |
|  9 | 月月       |  21 | 女   | 4000    | 2014-07-21 |       3 |    3 | 市场部 |
| 10 | 安琪       |  22 | 女   | 4000    | 2015-07-15 |       3 |    3 | 市场部 |
| 11 | 周明月     |  17 | 女   | 5000    | 2014-06-21 |       4 |    4 | 人事部 |
| 12 | 苍老师     |  33 | 女   | 1000000 | 2018-02-21 | NULL    | NULL | NULL   |
+----+------------+-----+------+---------+------------+---------+------+--------+
12 rows in set

　　图解：

　　c、右外连接查询 (右边表中的数据优先全部显示)　

#查询人员和部门所有信息
select * from person p right join  dept d  on  p.dept_id =d.did;

　　结果：

mysql> select * from person p right join  dept d  on  p.dept_id =d.did;
+------+------------+------+------+--------+------------+---------+-----+--------+
| id   | name       | age  | sex  | salary | hire_date  | dept_id | did | dname  |
+------+------------+------+------+--------+------------+---------+-----+--------+
|    1 | alex       |   28 | 人妖 | 53000  | 2010-06-21 |       1 |   1 | 教学部 |
|    2 | wupeiqi    |   23 | 男   | 8000   | 2011-02-21 |       1 |   1 | 教学部 |
|    3 | egon       |   30 | 男   | 6500   | 2015-06-21 |       1 |   1 | 教学部 |
|    4 | jingnvshen |   18 | 女   | 6680   | 2014-06-21 |       1 |   1 | 教学部 |
|    5 | 歪歪       |   20 | 女   | 3000   | 2015-02-21 |       2 |   2 | 销售部 |
|    6 | 星星       |   20 | 女   | 2000   | 2018-01-30 |       2 |   2 | 销售部 |
|    7 | 格格       |   20 | 女   | 2000   | 2018-02-27 |       2 |   2 | 销售部 |
|    8 | 周周       |   20 | 女   | 2000   | 2015-06-21 |       2 |   2 | 销售部 |
|    9 | 月月       |   21 | 女   | 4000   | 2014-07-21 |       3 |   3 | 市场部 |
|   10 | 安琪       |   22 | 女   | 4000   | 2015-07-15 |       3 |   3 | 市场部 |
|   11 | 周明月     |   17 | 女   | 5000   | 2014-06-21 |       4 |   4 | 人事部 |
| NULL | NULL       | NULL | NULL | NULL   | NULL       | NULL    |   5 | 鼓励部 |
+------+------------+------+------+--------+------------+---------+-----+--------+
12 rows in set

　　备注：正好与左外连接相反

　　d、全连接查询(显示左右表中全部数据)

　　　　定义：是在内连接的基础上增加左右两边没有显示的数据

　　　　注意：

　　　　1、mysql并不支持全连接 full JOIN 关键字

　　　　2、mysql 提供了 UNION 关键字.使用 UNION 可以间接实现 full JOIN 功能　

#查询人员和部门的所有数据
SELECT * FROM person  LEFT JOIN dept ON person.dept_id = dept.did
UNION
SELECT * FROM person RIGHT JOIN dept ON person.dept_id = dept.did;

　　　　结果：　

mysql> SELECT * FROM person  LEFT JOIN dept ON person.dept_id = dept.did
UNION
SELECT * FROM person RIGHT JOIN dept ON person.dept_id = dept.did;
+------+------------+------+------+---------+------------+---------+------+--------+
| id   | name       | age  | sex  | salary  | hire_date  | dept_id | did  | dname  |
+------+------------+------+------+---------+------------+---------+------+--------+
|    1 | alex       |   28 | 人妖 | 53000   | 2010-06-21 |       1 |    1 | 教学部 |
|    2 | wupeiqi    |   23 | 男   | 8000    | 2011-02-21 |       1 |    1 | 教学部 |
|    3 | egon       |   30 | 男   | 6500    | 2015-06-21 |       1 |    1 | 教学部 |
|    4 | jingnvshen |   18 | 女   | 6680    | 2014-06-21 |       1 |    1 | 教学部 |
|    5 | 歪歪       |   20 | 女   | 3000    | 2015-02-21 |       2 |    2 | 销售部 |
|    6 | 星星       |   20 | 女   | 2000    | 2018-01-30 |       2 |    2 | 销售部 |
|    7 | 格格       |   20 | 女   | 2000    | 2018-02-27 |       2 |    2 | 销售部 |
|    8 | 周周       |   20 | 女   | 2000    | 2015-06-21 |       2 |    2 | 销售部 |
|    9 | 月月       |   21 | 女   | 4000    | 2014-07-21 |       3 |    3 | 市场部 |
|   10 | 安琪       |   22 | 女   | 4000    | 2015-07-15 |       3 |    3 | 市场部 |
|   11 | 周明月     |   17 | 女   | 5000    | 2014-06-21 |       4 |    4 | 人事部 |
|   12 | 苍老师     |   33 | 女   | 1000000 | 2018-02-21 | NULL    | NULL | NULL   |
| NULL | NULL       | NULL | NULL | NULL    | NULL       | NULL    |    5 | 鼓励部 |
+------+------------+------+------+---------+------------+---------+------+--------+
13 rows in set

　　3、复杂条件多表查询

　　　　查询出教学部年龄大于20岁,并且工资小于40000的员工,按工资倒序排列.(要求:分别使用多表联合查询和内连接查询)

#多表联合查询方式
select * from person p,dept d where p.dept_id= d.did
    and  age>20 
    and salary <40000 
ORDER BY salary DESC;

#内连接方式
SELECT * FROM person p INNER JOIN dept d ON p.dept_id= d.did 
    and  age>20 
    and salary <40000 
ORDER BY salary DESC;

　　结果：

+----+---------+-----+-----+--------+------------+---------+-----+--------+
| id | name    | age | sex | salary | hire_date  | dept_id | did | dname  |
+----+---------+-----+-----+--------+------------+---------+-----+--------+
|  2 | wupeiqi |  23 | 男  | 8000   | 2011-02-21 |       1 |   1 | 教学部 |
|  3 | egon    |  30 | 男  | 6500   | 2015-06-21 |       1 |   1 | 教学部 |
|  9 | 月月    |  21 | 女  | 4000   | 2014-07-21 |       3 |   3 | 市场部 |
| 10 | 安琪    |  22 | 女  | 4000   | 2015-07-15 |       3 |   3 | 市场部 |
+----+---------+-----+-----+--------+------------+---------+-----+--------+
4 rows in set

　　　　查询每个部门中最高工资和最低工资是多少,显示部门名称

#联合查询方式
SELECT MAX(p.salary),MIN(p.salary),d.dname from person p , dept d where  p.dept_id = d.did GROUP BY p.dept_id;

#内连接方式
SELECT MAX(p.salary),MIN(p.salary),d.dname from person p INNER JOIN  dept d on p.dept_id = d.did GROUP BY p.dept_id;

　　结果：

+---------------+---------------+--------+
| MAX(p.salary) | MIN(p.salary) | dname  |
+---------------+---------------+--------+
| 53000         | 6500          | 教学部 |
| 3000          | 2000          | 销售部 |
| 4000          | 4000          | 市场部 |
| 5000          | 5000          | 人事部 |
+---------------+---------------+--------+
4 rows in set

　　4、子查询

　　子查询(嵌套查询): 查多次, 多个select

　　注意: 第一次的查询结果可以作为第二次的查询的条件或者表名使用.

　　子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字. 还可以包含比较运算符：= 、 !=、> 、<等.

　　a、作为表名使用

select * from (select * from person) as 表名;
 
ps:大家需要注意的是: 一条语句中可以有多个这样的子查询,在执行时,最里层括号(sql语句) 具有优先执行权.<br>注意: as 后面的表名称不能加引号('')

　　b、求最高工资那个人的姓名和薪水

select p.name,p.salary from person p where p.salary=(select max(salary) from person);

　　结果：

+--------+---------+
| name   | salary  |
+--------+---------+
| 苍老师 | 1000000 |
+--------+---------+
1 row in set

思路：先查询出最高工资，再查询出姓名和薪水，最高工资作为子查询的条件。

　　c、求工资高于所有人员平均工资的人员

SELECT name,salary from person where salary > (SELECT  avg(p.salary) FROM person p);

　　结果：

+--------+---------+
| name   | salary  |
+--------+---------+
| 苍老师 | 1000000 |
+--------+---------+
1 row in set

思路：先查询除平均工资，再查询出姓名和薪水，平均工资作为其子查询的条件

　　d、练习

　　1.查询平均年龄在20岁以上的部门名
　　2.查询教学部 下的员工信息
　　3.查询大于所有人平均工资的人员的姓名与年龄

　　1、查询平均年龄在20以上的部门名称

#查询平均年龄在20以上的部门名称
#多表联合查询方式
SELECT d.did as '部门id',d.dname as "部门名称",AVG(p.age) as "部门平均年龄"  from person p,dept d  WHERE p.dept_id = d.did GROUP BY d.did HAVING AVG(p.age) > 20;


#子查询方式
SELECT * FROM dept WHERE dept.did IN ( SELECT dept_id FROM person GROUP BY dept_id HAVING avg(person.age) > 20 );

　　结果：

+--------+----------+--------------+
| 部门id | 部门名称 | 部门平均年龄 |
+--------+----------+--------------+
|      1 | 教学部   | 24.75        |
|      3 | 市场部   | 21.5         |
+--------+----------+--------------+
2 rows in set

　　2、查询教学部下的员工信息

SELECT * FROM person p WHERE p.dept_id in(SELECT d.did from dept d WHERE d.dname = "教学部");

　　结果：

+----+------------+-----+------+--------+------------+---------+
| id | name       | age | sex  | salary | hire_date  | dept_id |
+----+------------+-----+------+--------+------------+---------+
|  1 | alex       |  28 | 人妖 | 53000  | 2010-06-21 |       1 |
|  2 | wupeiqi    |  23 | 男   | 8000   | 2011-02-21 |       1 |
|  3 | egon       |  30 | 男   | 6500   | 2015-06-21 |       1 |
|  4 | jingnvshen |  18 | 女   | 6680   | 2014-06-21 |       1 |
+----+------------+-----+------+--------+------------+---------+
4 rows in set

　　3、查询大于所有人平均工资的人员的姓名与年龄

SELECT * FROM person where salary >(SELECT AVG(salary) from person);

　　结果：

+----+--------+-----+-----+---------+------------+---------+
| id | name   | age | sex | salary  | hire_date  | dept_id |
+----+--------+-----+-----+---------+------------+---------+
| 12 | 苍老师 |  33 | 女  | 1000000 | 2018-02-21 | NULL    |
+----+--------+-----+-----+---------+------------+---------+
1 row in set

e、关键字

　　any

　　all

　　some

　　5、其他查询方式

　　a、临时表查询

　　查询高于本部门平均工资的人员

#1.先查询部门人员的平均工资
SELECT dept_id,AVG(salary)as sal from person GROUP BY dept_id;
 
#2.再用人员的工资与部门的平均工资进行比较
SELECT * FROM person as p1,
    (SELECT dept_id,AVG(salary)as '平均工资' from person GROUP BY dept_id) as p2
where p1.dept_id = p2.dept_id AND p1.salary >p2.`平均工资`;

ps:在当前语句中,我们可以把上一次的查询结果当前做一张表来使用.因为p2表不是真是存在的,所以:我们称之为 临时表　　
   临时表:不局限于自身表,任何的查询结果集都可以认为是一个临时表.

　　结果：

+----+------+-----+------+--------+------------+---------+---------+--------------+
| id | name | age | sex  | salary | hire_date  | dept_id | dept_id | 平均工资     |
+----+------+-----+------+--------+------------+---------+---------+--------------+
|  1 | alex |  28 | 人妖 | 53000  | 2010-06-21 |       1 |       1 | 18545.000000 |
|  5 | 歪歪 |  20 | 女   | 3000   | 2015-02-21 |       2 |       2 | 2250.000000  |
+----+------+-----+------+--------+------------+---------+---------+--------------+
2 rows in set

　　b、判断查询　IF关键字

　　需求1：根据工资高低,将人员划分为两个级别,分别为高端人群和低端人群。显示效果:姓名,年龄,性别,工资,级别

select p1.*, 
    
    IF(p1.salary >10000,'高端人群','低端人群') as '级别'
 
from person p1;

#ps: 语法: IF(条件表达式,"结果为true",'结果为false');

　　结果：

+----+------------+-----+------+---------+------------+---------+----------+
| id | name       | age | sex  | salary  | hire_date  | dept_id | 级别     |
+----+------------+-----+------+---------+------------+---------+----------+
|  1 | alex       |  28 | 人妖 | 53000   | 2010-06-21 |       1 | 高端人群 |
|  2 | wupeiqi    |  23 | 男   | 8000    | 2011-02-21 |       1 | 低端人群 |
|  3 | egon       |  30 | 男   | 6500    | 2015-06-21 |       1 | 低端人群 |
|  4 | jingnvshen |  18 | 女   | 6680    | 2014-06-21 |       1 | 低端人群 |
|  5 | 歪歪       |  20 | 女   | 3000    | 2015-02-21 |       2 | 低端人群 |
|  6 | 星星       |  20 | 女   | 2000    | 2018-01-30 |       2 | 低端人群 |
|  7 | 格格       |  20 | 女   | 2000    | 2018-02-27 |       2 | 低端人群 |
|  8 | 周周       |  20 | 女   | 2000    | 2015-06-21 |       2 | 低端人群 |
|  9 | 月月       |  21 | 女   | 4000    | 2014-07-21 |       3 | 低端人群 |
| 10 | 安琪       |  22 | 女   | 4000    | 2015-07-15 |       3 | 低端人群 |
| 11 | 周明月     |  17 | 女   | 5000    | 2014-06-21 |       4 | 低端人群 |
| 12 | 苍老师     |  33 | 女   | 1000000 | 2018-02-21 | NULL    | 高端人群 |
+----+------------+-----+------+---------+------------+---------+----------+
12 rows in set

　　需求2：根据工资高低,统计每个部门人员收入情况,划分为富人,小资,平民,吊丝四个级别, 要求统计四个级别分别有多少人

#语法一:
SELECT
    CASE WHEN STATE = '1' THEN '成功'
         WHEN STATE = '2' THEN '失败'
         ELSE '其他' END 
FROM 表;
 
#语法二:
SELECT CASE age
           WHEN 23 THEN '23岁'
           WHEN 27 THEN '27岁'
           WHEN 30 THEN '30岁'
        ELSE '其他岁' END
FROM person;

SELECT dname '部门',
             sum(case WHEN salary >50000 THEN 1 ELSE 0 end) as '富人',
             sum(case WHEN salary between 29000 and 50000 THEN 1 ELSE 0 end) as '小资',
             sum(case WHEN salary between 10000 and 29000 THEN 1 ELSE 0 end) as '平民',
             sum(case WHEN salary <10000 THEN 1 ELSE 0 end) as '吊丝'
FROM person,dept where person.dept_id = dept.did GROUP BY dept_id;

　　结果：

+--------+------+------+------+------+
| 部门   | 富人 | 小资 | 平民 | 吊丝 |
+--------+------+------+------+------+
| 教学部 | 1    | 0    | 0    | 3    |
| 销售部 | 0    | 0    | 0    | 4    |
| 市场部 | 0    | 0    | 0    | 2    |
| 人事部 | 0    | 0    | 0    | 1    |
+--------+------+------+------+------+
4 rows in set

知道、想到、做到、得到

相关阅读:
web页面前图标
 leetcode收获
 Shell统计函数耗时（实现数字运算）
Shell判断数值是否存在于列表
 设置Ubuntu虚拟机硬件时间与系统同步
 Python捕获键盘中断^C方法(Ctrl-C)
Shell创建zip文件不包含完整路径方法
 jquery判断复选框checkbox是否被选中
 php Base64编码/解码
 php二维数组排序
原文地址：https://www.cnblogs.com/Durant0420/p/15267772.html

mysql多表查询

一、环境准备

二、前期知识铺垫

1、多表查询语法

2、笛卡尔乘积

三、多表查询类型

1、多表联合查询

2、多表连接查询

a、内连接查询 (只显示符合条件的数据)

b、左外连接查询 (左边表中的数据优先全部显示)

c、右外连接查询 (右边表中的数据优先全部显示)

d、全连接查询(显示左右表中全部数据)

3、复杂条件多表查询

4、子查询

a、作为表名使用

b、求最高工资那个人的姓名和薪水

c、求工资高于所有人员平均工资的人员

5、其他查询方式

a、临时表查询

b、判断查询 IF关键字

一、环境准备　

　　1、多表查询语法　

　　2、笛卡尔乘积

　　1、多表联合查询　

　　2、多表连接查询　

　　a、内连接查询 (只显示符合条件的数据)

　　b、左外连接查询 (左边表中的数据优先全部显示)　　

　　c、右外连接查询 (右边表中的数据优先全部显示)　

　　d、全连接查询(显示左右表中全部数据)

　　3、复杂条件多表查询

　　4、子查询

　　a、作为表名使用

　　b、求最高工资那个人的姓名和薪水

　　c、求工资高于所有人员平均工资的人员

　　5、其他查询方式

　　a、临时表查询

　　b、判断查询　IF关键字