一、环境准备

#创建部门
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.字段;内连接查询 (只显示符合条件的数据)

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

#查询人员和部门所有信息
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

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

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

#查询人员和部门所有信息
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

图解：

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

#查询人员和部门所有信息
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

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

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

定义：是在内连接的基础上增加左右两边没有显示的数据
注意：
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等关键字. 还可以包含比较运算符：= 、 !=、> 、<等.

(1)作为表名使用

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

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

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

结果：

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

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

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

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

结果：


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

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

(2)练习

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

关键字
any
all
some

5、其他查询方式

(1)临时表查询

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

#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

(2)判断查询 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

MySQL函数 DATE_SUB()

一、定义

DATE_SUB() 函数从日期减去指定的时间间隔。

二、语法

DATE_SUB(date, INTERVAL expr type)<br />        date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。<br />        type 参数可以是下列值：

Type值说明

MICROSECOND 返回时间或日期时间表达式expr的微秒，这个数字范围为 0 到 999999
SECOND 返回时间秒值，范围为0〜59。
MINUTE 返回时间的分钟，范围为0至59。
HOUR 返回时间的小时部分。返回值的范围为0至23的小时值。然而，TIME值的范围实际上要大得多，所以HOUR可以返回大于23的值。
DAY 返回给定日期的月份的日期部分。
WEEK 返回日期的星期数
MONTH 返回日期的月份，取值范围为0〜12。
QUARTER 返回年份日期的季度值，范围为1〜4
YEAR 返回年份日期，范围为1000〜9999或0

三.某公司面试题:

--表一：
CREATE TABLE `yuhuang_supply_card` ( 
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `orderId` varchar(50) NOT NULL COMMENT '对内订单号',
  `userId` bigint(11) NOT NULL COMMENT '用户id，对应yuhuang_sup_user 表user_id',
  `productType` int(11) NOT NULL COMMENT '运营商',
  `settlePrice` decimal(7,2) NOT NULL DEFAULT '0.00' COMMENT '订单金额',
  `status` int(2) NOT NULL DEFAULT '3' COMMENT '状态，0在售，1已售，2停售，3可售',
  `startTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入库时间',
  PRIMARY KEY (`id`),
  KEY `startTime` (`startTime`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB AUTO_INCREMENT=66158 DEFAULT CHARSET=utf8 COMMENT='卡密供货订单';
--表二：
CREATE TABLE `yuhuang_sup_user` (
  `user_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `account` varchar(255) DEFAULT NULL COMMENT '账号',
  `status` tinyint(3) NOT NULL DEFAULT '1' COMMENT '0 关闭 1开启',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=70372 DEFAULT CHARSET=utf8 COMMENT='用户表';

 要求查询出 <br />三天内每个用户已售的订单总金额、<br />已售的订单总笔数、<br />账号，<br />需要按用户账号排序 <br />其中三天内函数为：date_sub(curdate(),interval 3 day)

SELECT
    u.account,
    i.sumPrice,
    i.countOrder 
FROM
    (
    SELECT
        userId,
        sum( settlePrice ) sumPrice,
        count( userId ) countOrder 
    FROM
        yuhuang_supply_card 
    WHERE
        STATUS = 1 
        AND date_sub( curdate(), INTERVAL 3 DAY ) < startTime 
    GROUP BY
        userId 
    ) i,
    yuhuang_sup_user u 
WHERE
    i.userId = u.user_id 
ORDER BY
    u.account ASC

后端学习笔记

MySQL多表查询

一、环境准备

二、概述

1、多表查询语法

2、笛卡尔乘积

三、多表查询类型

1、多表联合查询

2、多表连接查询

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

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

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

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

3、复杂条件多表查询

4、子查询

(1)作为表名使用

(2)练习

5、其他查询方式

(1)临时表查询

(2)判断查询 IF关键字

MySQL函数 DATE_SUB()

一、定义

二、语法

Type值说明

三.某公司面试题:

MySQL多表查询

一、环境准备

二、概述

1、多表查询语法

2、笛卡尔乘积

三、多表查询类型

1、多表联合查询

2、多表连接查询

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

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

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

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

3、复杂条件多表查询

4、子查询

(1)作为表名使用

(2)练习

5、其他查询方式

(1)临时表查询

(2)判断查询 IF关键字

MySQL函数 DATE_SUB()

一、定义

二、语法

Type值 说明

三.某公司面试题:

Type值说明