Mysql的查询

　　出现在select语句中的select语句，称为子查询或内查询。
　　外部的select查询语句，称为主查询或外查询。 子查询分类按结果集的行列数不同分为4种标量子查询（结果集只有一行一列） 列子查询（结果集只有一列多行） 行的查询（结果集有一行多列） 表子查询（结果集一般为多行多列） 按子查询出现在主查询中的不同位置分select后面 ：仅仅支持标量子查询。 from后面 ：支持表子查询。 where或having后面 ：支持标量子查询（单列单行）、列子查询（单列多行）、行子查询（多列多行） exists后面（即相关子查询） ：表子查询（多行、多列） 准备测试数据
　　测试数据比较多，放在我的个人博客上了。
　　浏览器中打开链接：http://www.itsoku.com/article/196
　　mysql中执行里面的 javacode2018_employees库  部分的脚本。
　　成功创建 javacode2018_employees  库及5张表，如下：
　　表名
　　描述
　　departments
　　部门表
　　employees
　　员工信息表
　　jobs
　　职位信息表
　　locations
　　位置表（部门表中会用到）
　　job_grades
　　薪资等级表 select后面的子查询
　　子查询位于select后面的， 仅仅支持标量子查询 。 示例1
　　查询每个部门员工的个数 SELECT   a.*,   (SELECT count(*)    FROM employees b    WHERE b.department_id = a.department_id) AS 员工个数 FROM departments a;示例2
　　查询员工号=102个部门名称 SELECT (SELECT a.department_name         FROM departments a, employees b         WHERE a.department_id = b.department_id               AND b.employee_id = 102) AS 部门名;from后面的子查询将自己查询的结果集充当一张表，要求必须起别名，否则这个表找不到。
　　然后将真实地表格和自己查询结果表进行连接查询。 示例1
　　查询每个部门平均工资的工资等级 -- 查询每个部门平均工资 SELECT   department_id,   avg(a.salary) FROM employees a GROUP BY a.department_id;  -- 薪资等级表 SELECT * FROM job_grades;  -- 将上面2个结果连接查询，筛选条件:平均工资 between lowest_sal and highest_sal; SELECT   t1.department_id,   sa AS ＂平均工资＂,   t2.grade_level FROM (SELECT         department_id,         avg(a.salary) sa       FROM employees a       GROUP BY a.department_id) t1, job_grades t2 WHERE   t1.sa BETWEEN t2.lowest_sal AND t2.highest_sal;
　　运行最后一条结果如下： mysql> SELECT           t1.department_id,           sa AS ＂平均工资＂,           t2.grade_level         FROM (SELECT                 department_id,                 avg(a.salary) sa               FROM employees a               GROUP BY a.department_id) t1, job_grades t2         WHERE           t1.sa BETWEEN t2.lowest_sal AND t2.highest_sal; +---------------+--------------+-------------+ | department_id | 平均工资     | grade_level | +---------------+--------------+-------------+ |          NULL |  7000.000000 | C           | |            10 |  4400.000000 | B           | |            20 |  9500.000000 | C           | |            30 |  4150.000000 | B           | |            40 |  6500.000000 | C           | |            50 |  3475.555556 | B           | |            60 |  5760.000000 | B           | |            70 | 10000.000000 | D           | |            80 |  8955.882353 | C           | |            90 | 19333.333333 | E           | |           100 |  8600.000000 | C           | |           110 | 10150.000000 | D           | +---------------+--------------+-------------+ 12 rows in set (0.00 sec)where和having后面的子查询where或having后面，可以使用标量子查询（单行单列子查询） 列子查询（单列多行子查询） 行的查询（一行多列） 特点子查询放在小括号内。 子查询一般放在条件的右侧。 标量子查询，一般搭配着单行单列操作符使用  >、<、>=、<=、=、<>、!= 列子查询，一般搭配着多行操作符使用 in(not in)：列表中的＂任意一个＂any或者some：和子查询返回的＂某一个值＂比较，比如a>some(10,20,30)，a大于子查询中任意一个即可，a大于子查询中最小值即可，等同于a>min(10,20,30)。all：和子查询返回的＂所有值＂比较，比如a>all(10,20,30)，a大于子查询中所有值，换句话说，a大于子查询中最大值即可满足查询条件，等同于a>max(10,20,30); 子查询的执行优先于主查询执行，因为主查询的条件用到了子查询的结果。 mysql中的in、any、some、all
　　in，any，some，all分别是子查询的关键词之一。
　　in ：in常用于where表达式中，其作用是查询某个范围内的数据
　　any和some一样 ： 可以与=、>、>=、<、<=、<>结合起来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的任何一个数据。
　　all ：可以与=、>、>=、<、<=、<>结合使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的所有数据。
　　下文中会经常用到这些关键字。 标量子查询一般标量子查询，示例
　　查询谁的工资比Abel的高？ /*①查询abel的工资【改查询是标量子查询】*/ SELECT salary FROM employees WHERE last_name = ＂Abel＂;  /*②查询员工信息，满足salary>①的结果*/ SELECT * FROM employees a WHERE a.salary > (SELECT salary                   FROM employees                   WHERE last_name = ＂Abel＂);多个标量子查询，示例
　　返回job_id与141号员工相同，salary比143号员工多的员工、姓名、job_id和工资 /*返回job_id与141号员工相同，salary比143号员工多的员工、姓名、job_id和工资*/ /*①查询141号员工的job_id*/ SELECT job_id FROM employees WHERE employee_id = 141; /*②查询143好员工的salary*/ SELECT salary FROM employees WHERE employee_id = 143; /*③查询员工的姓名、job_id、工资，要求job_id=① and salary>②*/ SELECT   a.last_name 姓名,   a.job_id,   a.salary    工资 FROM employees a WHERE a.job_id = (SELECT job_id                   FROM employees                   WHERE employee_id = 141)       AND       a.salary > (SELECT salary                   FROM employees                   WHERE employee_id = 143);子查询+分组函数，示例
　　查询最低工资大于50号部门最低工资的部门id和其最低工资【having】 /*查询最低工资大于50号部门最低工资的部门id和其最低工资【having】*/ /*①查询50号部门的最低工资*/ SELECT min(salary) FROM employees WHERE department_id = 50; /*②查询每个部门的最低工资*/ SELECT   min(salary),   department_id FROM employees GROUP BY department_id; /*③在②的基础上筛选，满足min(salary)>①*/ SELECT   min(a.salary) minsalary,   department_id FROM employees a GROUP BY a.department_id HAVING min(a.salary) > (SELECT min(salary)                         FROM employees                         WHERE department_id = 50);错误的标量子查询，示例
　　将上面的示例③中子查询语句中的min(salary)改为salary，执行效果如下： mysql> SELECT           min(a.salary) minsalary,           department_id         FROM employees a         GROUP BY a.department_id         HAVING min(a.salary) > (SELECT salary                                 FROM employees                                 WHERE department_id = 500000); ERROR 1242 (21000): Subquery returns more than 1 row错误提示：自己查询返回的结果超过了1行记录。
　　说明：上面的子查询只支持 最多一列一行记录 。 列子查询(子查询结果集一列多行)列子查询需要搭配多行操作符使用：in(not in)、any/some、all。
　　为了提升效率，最好去重复一下 distinct 关键字。 示例1
　　返回location_id是1400或1700的部门中的所有员工姓名 /*返回location_id是1400或1700的部门中的所有员工姓名*/ /*方式1*/ /*①查询location_id是1400或1700的部门编号*/ SELECT DISTINCT department_id FROM departments WHERE location_id IN (1400, 1700);  /*②查询员工姓名，要求部门是①列表中的某一个*/ SELECT a.last_name FROM employees a WHERE a.department_id IN (SELECT DISTINCT department_id                           FROM departments                           WHERE location_id IN (1400, 1700));  /*方式2：使用any实现*/ SELECT a.last_name FROM employees a WHERE a.department_id = ANY (SELECT DISTINCT department_id                              FROM departments                              WHERE location_id IN (1400, 1700));  /*拓展，下面与not in等价*/ SELECT a.last_name FROM employees a WHERE a.department_id <> ALL (SELECT DISTINCT department_id                              FROM departments                              WHERE location_id IN (1400, 1700));示例2
　　返回其他工种中比job_id为’IT_PROG’工种任意工资低的员工的员工号、姓名、job_id、salary /*返回其他工种中比job_id为＂IT_PROG＂工种任一工资低的员工的员工号、姓名、job_id、salary*/ /*①查询job_id为＂IT_PROG＂部门任-工资*/ SELECT DISTINCT salary FROM employees WHERE job_id = ＂IT_PROG＂;  /*②查询员工号、姓名、job_id、salary，slary<①的任意一个*/ SELECT   last_name,   employee_id,   job_id,   salary FROM employees WHERE salary < ANY (SELECT DISTINCT salary                     FROM employees                     WHERE job_id = ＂IT_PROG＂) AND job_id != ＂IT_PROG＂;  /*或者*/ SELECT   last_name,   employee_id,   job_id,   salary FROM employees WHERE salary < (SELECT max(salary)                 FROM employees                 WHERE job_id = ＂IT_PROG＂) AND job_id != ＂IT_PROG＂;示例3
　　返回其他工种中比job_id为’IT_PROG’部门所有工资低的员工的员工号、姓名、job_id、salary /*返回其他工种中比job_id为＂IT_PROG＂部门所有工资低的员工的员工号、姓名、job_id、salary*/ SELECT   last_name,   employee_id,   job_id,   salary FROM employees WHERE salary < ALL (SELECT DISTINCT salary                     FROM employees                     WHERE job_id = ＂IT_PROG＂) AND job_id != ＂IT_PROG＂;  /*或者*/ SELECT   last_name,   employee_id,   job_id,   salary FROM employees WHERE salary < (SELECT min(salary)                 FROM employees                 WHERE job_id = ＂IT_PROG＂) AND job_id != ＂IT_PROG＂;行子查询（子查询结果集一行多列）示例
　　查询员工编号最小并且工资最高的员工信息，3种方式。 /*查询员工编号最小并且工资最高的员工信息*/ /*①查询最小的员工编号*/ SELECT min(employee_id) FROM employees; /*②查询最高工资*/ SELECT max(salary) FROM employees; /*③方式1：查询员工信息*/ SELECT * FROM employees a WHERE a.employee_id = (SELECT min(employee_id)                        FROM employees)       AND salary = (SELECT max(salary)                     FROM employees);  /*方式2*/ SELECT * FROM employees a WHERE (a.employee_id, a.salary) = (SELECT                                      min(employee_id),                                      max(salary)                                    FROM employees); /*方式3*/ SELECT * FROM employees a WHERE (a.employee_id, a.salary) in (SELECT                                      min(employee_id),                                      max(salary)                                    FROM employees);
　　方式1比较常见，方式2、3更简洁。 exists后面（也叫做相关子查询）语法：exists(完整的查询语句)。 exists查询结果：1或0，exists查询的结果用来判断子查询的结果集中是否有值。 一般来说，能用exists的子查询，绝对都能用in代替，所以exists用的少。 和前面的查询不同，这先执行主查询，然后主查询查询的结果，在根据子查询进行过滤，子查询中涉及到主查询中用到的字段，所以叫相关子查询。 示例1
　　简单示例 mysql> SELECT exists(SELECT employee_id               FROM employees               WHERE salary = 300000) AS ＂exists返回1或者0＂; +----------------------+ | exists返回1或者0     | +----------------------+ |                    0 | +----------------------+ 1 row in set (0.00 sec)示例2
　　查询所有员工的部门名称 /*exists入门案例*/ SELECT exists(SELECT employee_id               FROM employees               WHERE salary = 300000) AS ＂exists返回1或者0＂;  /*查询所有员工部门名*/ SELECT department_name FROM departments a WHERE exists(SELECT 1              FROM employees b              WHERE a.department_id = b.department_id);  /*使用in实现*/ SELECT department_name FROM departments a WHERE a.department_id IN (SELECT department_id                           FROM employees);示例3
　　查询没有员工的部门 /*查询没有员工的部门*/ /*exists实现*/ SELECT * FROM departments a WHERE NOT exists(SELECT 1                  FROM employees b                  WHERE a.department_id = b.department_id AND b.department_id IS NOT NULL); /*in的方式*/ SELECT * FROM departments a WHERE a.department_id NOT IN (SELECT department_id                               FROM employees b                               WHERE b.department_id IS NOT NULL);
　　上面脚本中有 b.department_id IS NOT NULL  ，为什么，有大坑，向下看。 NULL的大坑示例1
　　使用 not in 的方式查询没有员工的部门，如下： SELECT * FROM departments a WHERE a.department_id NOT IN (SELECT department_id                               FROM employees b);
　　运行结果： mysql> SELECT *     -> FROM departments a     -> WHERE a.department_id NOT IN (SELECT department_id     ->                               FROM employees b); Empty set (0.00 sec)
　　not in的情况下，子查询中列的值为NULL的时候，外查询的结果为空。
　　建议：建表是，列不允许为空。