一、环境准备
#创建部门 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