五、DQL语言的学习

5.5 分组查询

如果想要查询每个部门的平均值，此时就需要用到分组查询，需要用到group_by：
image.png
图34. 分组查询的案例

5.5.1 简单使用

案例1：查询每个工种的最高工资

  1. # 查询每个工种的最高工资
  3. SELECT MAX(salary),job_id
  4. FROM employees
  5. GROUP BY job_id;

image.png
图35. 案例1查询结果
案例2：查询每个位置上的部门个数

  1. # 查询每个位置上的部门个数
  2. /* count(*)用来查询某个表的行数 */
  4. SELECT COUNT(*),location_id
  5. FROM departments
  6. GROUP BY location_id;

image.png
图36. 案例2查询结果

5.5.2 配合筛选条件

案例3：查询邮箱中包含a字符的，每个部门的平均工资

  1. # 查询邮箱中包含a字符的，每个部门的平均工资
  2. /* where关键字在group by之前 */
  4. SELECT AVG(salary),department_id
  5. FROM employees
  6. WHERE email LIKE '%a%'
  7. GROUP BY department_id;

image.png
图37. 案例3查询结果
案例4：查询有奖金的每个领导手下员工的最高工资

  1. # 查询有奖金的每个领导手下员工的最高工资
  3. SELECT MAX(salary),manager_id
  4. FROM employees
  5. WHERE commission_pct IS NOT NULL
  6. GROUP BY manager_id;

image.png
图38. 案例4查询结果

5.5.3 复杂筛选条件

案例5：查询部门员工个数>2的

按照正常的逻辑，我们首先得知道每个部门的员工个数，然后再找出个数>2，所以就需要两个步骤，而这两个步骤得用having关键字连接起来。

  1. /* 查询部门员工个数 */
  2. SELECT COUNT(*),department_id
  3. FROM employees
  4. GROUP BY department_id;
  6. /* 查询部门员工个数>2的 */
  8. SELECT COUNT(*),department_id
  9. FROM employees
  10. GROUP BY department_id
  11. HAVING COUNT(*)>2;

image.png
图39. 案例5查询结果

案例6：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

  1. # 对每个工种筛选出有奖金员工的工种编号和最高工资
  2. SELECT MAX(salary),job_id
  3. FROM employees
  4. WHERE commission_pct IS NOT NULL
  5. GROUP BY job_id;
  7. /* 最高工资>12000是对第一次查询后的，又一次筛选 */ 
  8. SELECT MAX(salary),job_id
  9. FROM employees
  10. WHERE commission_pct IS NOT NULL
  11. GROUP BY job_id
  12. HAVING MAX(salary)>12000;

image.png
图40. 案例6查询结果

案例7：查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，以及其最低工资

这次就需要使用3次步骤来执行了，首先要查询每个领导手下的员工固定最低工资，然后对领导添加筛选条件，最后添加筛选条件最低工资>50000

  1. SELECT MIN(salary),manager_id
  2. FROM employees
  3. WHERE manager_id>102
  4. GROUP BY manager_id
  5. HAVING MIN(salary)>5000;

image.png
图41. 案例7查询结果

5.5.4 按函数分组

案例8：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些

  1. 先查询每个长度的员工个数

    1. SELECT COUNT(*),LENGTH(last_name)
    2. FROM employees
    3. GROUP BY LENGTH(last_name);

  2. 根据分组结果进行筛选

    1. SELECT COUNT(*),LENGTH(last_name)
    2. FROM employees
    3. GROUP BY LENGTH(last_name)
    4. HAVING COUNT(*)>5;

    image.png
    图42. 案例8查询结果

    5.5.5 按多个字段分类

    案例9：查询每个部门每个工种的员工的平均工资

    1. SELECT AVG(salary),department_id,job_id
    2. FROM employees
    3. GROUP BY job_id,department_id;

    image.png
    图43. 案例9查询结果

    5.5.6 添加排序

    案例10：查询部门编号不为空的部门每个工种的员工的平均工资，查询后筛选出>10000的，然后由高到低显示工资

    1. SELECT AVG(salary),department_id,job_id
    2. FROM employees
    3. WHERE department_id IS NOT NULL        # 部门编号不为空
    4. GROUP BY job_id,department_id            # 部门、工种分组
    5. HAVING AVG(salary)>10000                    # 筛选>10000的
    6. ORDER BY AVG(salary) DESC;                # 排序

    image.png
    图44. 案例10查询结果

    5.5.7 测试

  3. 查询各job_id 的员工工资的最大值，最小值，平均值，总和，并按job_id 升序

  4. 查询员工最高工资和最低工资的差距（DIFFERENCE）
  5. 查询各个管理者手下员工的最低工资，其中最低工资不能低于 6000，没有管理者的员工不计算在内
  6. 查询所有部门的编号，员工数量和工资平均值,并按平均工资降序
  7. 选择具有各个job_id 的员工人数

    5.6 连接查询

    连接查询，又称多表查询，当查询的字段来自多个表时，就会用到连接查询
    image.png
    图45. 连接查询示例

对于两张表的查询，我们必须要让他们相互匹配（有效的连接条件）才可以

  1. SELECT NAME,boyName FROM boys,beauty
  2. WHERE beauty.`boyfriend_id`=boys.`id`;        # 两个id要匹配

image.png
图46. 连接查询结果
对于连接查询，按照其功能进行分类：

  • 内连接
    • 等值连接
    • 非等值连接
    • 自连接
  • 外连接
    • 左外连接
    • 右外连接
    • 全外连接
  • 交叉连接

    5.6.1 等值连接

    案例1：查询员工名和对应的部门名
    1. SELECT last_name,department_name
    2. FROM employees,departments
    3. WHERE employees.`department_id`=departments.`department_id`;
    image.png
    图47. 案例1查询结果

很多情况下，我们的字段会存在歧义，比如last_name这个字段可能在两个表中都存在，这个时候就需要加上表名进行限制，如果表名过长，就需要给它起别名。

案例2：给表起别名，查询员工名、工种号和工种名

  1. select e.last_name,e.job_id,j.job_title
  2. from employees as e,jobs j
  3. where e.job_id=j.job_id;

上面涉及到两个表，那么这两个表顺序是否可以调换呢？

案例3：调换两个表的顺序，查询员工名、工种号和工种名

  1. select e.last_name,e.job_id,j.job_title
  2. from jobs as j,employees as e
  3. where e.job_id=j.job_id;

案例4：查询有奖金的员工名、部门名

  1. select last_name,department_name,commission_pct
  2. from employees,departments 
  3. where employees.department_id=departments.department_id
  4. and employees.commission_pct is not null;

image.png
案例5：分组查询每个城市的部门个数

  1. select count(*) 个数,city
  2. from departments d,locations l
  3. GROUP BY city;

image.png

案例6：查询每个工种的工种名和员工的个数，并且按员工个数降序

  1. select job_title,count(*)
  2. from employees e,jobs j
  3. where e.job_id=j.job_id
  4. group by job_title
  5. ORDER BY count(*) DESC;

image.png

5.6.2 非等值连接

案例：查询员工的工资和工资级别

  1. select salary,grade_level
  2. from employees e,job_grades g
  3. where salary between g.`lowest_sal` and g.`highest_sal`        # 根据salary的大小来筛选
  4. and g.`grade_level`='A';        # 这个只是多了一个筛选

image.png

5.6.3 自连接

把自己一张表当成两张表来使用，因为其中的两个列要进行匹配
image.png

  1. select e.employee_id,e.last_name,m.employee_id,m.last_name
  2. from employees e,employees m            # 一张表当作两张表来使用
  3. where e.manager_id=m.employee_id;

image.png

5.6.4 sql92语法测试

  1. 查询编号>3 的女神的男朋友信息，如果有则列出详细，如果没有，用 null 填充
  2. 查询哪个城市没有部门
  3. 查询部门名为SAL 或IT 的员工信息

    5.7 sql99语法

    从这节开始，使用到的是sql99的语法。
  1. /*
  2. 语法：
  3. select 查询列表
  4. from 表1 别名 [连接类型]
  5. join 表2 别名
  6. on 连接条件
  7. [where 筛选条件]
  8. [group by 分组]
  9. [having 筛选条件]
  10. [order by 排序列表]
  11. */

分类：

  • 内连接：inner
  • 外连接
    • 左外：left[outer]
    • 右外：right[outer]
    • 全外
  • 交叉连接（就是笛卡尔乘积）

5.7.1 内连接

  1. /*语法：
  2. select 查询列表
  3. from 表1 别名
  4. inner join 表2 别名
  5. on 连接条件;
  6. */
  8. /* 特点：
  9. 1.添加排序、分组、筛选
  10. 2.inner可以省略
  11. 3.筛选条件可以放在where后面，连接条件可以放在on后面，提高分离性，便于阅读
  12. 4.inner join连接和sql92语法中的等值连接效果是一样的，都是查询夺标的交集。
  13. */

等值连接

案例1：查询员工名、部门名

  1. SELECT last_name,department_name
  2. FROM employees e
  3. INNER JOIN departments d
  4. ON e.`department_id`=d.`department_id`;

image.png
案例2：查询名字中包含e的员工名和工种名（添加筛选）

  1. SELECT last_name,job_title
  2. FROM employees e
  3. INNER JOIN jobs j
  4. ON e.`job_id`=j.`job_id`
  5. WHERE e.`last_name` LIKE '%e%';

image.png
案例3：查询部门个数>3的城市名和部门个数

  1. # 筛选每个城市的部门个数
  2. # 筛选满足条件的
  3. SELECT city,COUNT(*) 部门个数
  4. FROM departments d
  5. INNER JOIN locations l
  6. ON d.`location_id`=l.`location_id`
  7. GROUP BY city
  8. HAVING COUNT(*)>3;

image.png
案例4：查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）

  1. # 1.查询每个部门的员工个数
  2. SELECT COUNT(*),department_name
  3. FROM employees e
  4. INNER JOIN departments d
  5. ON e.`department_id`=d.`department_id`
  6. GROUP BY department_name
  7. # 2.在1结果上筛选员工个数>3的记录，并排序
  8. HAVING COUNT(*)>3
  9. ORDER BY COUNT(*) DESC;

image.png
案例5：查询员工名、部门名、工种名，并按部门名降序（多表连接）

  1. SELECT last_name,department_name,job_title
  2. FROM employees e
  3. INNER JOIN departments d ON e.`department_id`=d.`department_id`
  4. INNER JOIN jobs j ON e.`job_id`=j.`job_id`
  5. ORDER BY department_name DESC;

image.png

非等值连接

准备工作：先将工资级别这个表打进来

  1. CREATE TABLE job_grades
  2. (grade_level VARCHAR(3),
  3.  lowest_sal  INT,
  4.  highest_sal INT);
  6. INSERT INTO job_grades
  7. VALUES ('A', 1000, 2999);
  9. INSERT INTO job_grades
  10. VALUES ('B', 3000, 5999);
  12. INSERT INTO job_grades
  13. VALUES('C', 6000, 9999);
  15. INSERT INTO job_grades
  16. VALUES('D', 10000, 14999);
  18. INSERT INTO job_grades
  19. VALUES('E', 15000, 24999);
  21. INSERT INTO job_grades
  22. VALUES('F', 25000, 40000);

案例1：查询员工的工资级别

  1. SELECT salary,grade_level
  2. FROM employees e
  3. JOIN job_grades g
  4. ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;

image.png
案例2：查询工资级别的个数>20个数，并且按工资级别降序

  1. SELECT COUNT(*),grade_level
  2. FROM employees e
  3. JOIN job_grades g
  4. ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
  5. GROUP BY grade_level
  6. HAVING COUNT(*)>20
  7. ORDER BY grade_level DESC;

image.png

自连接

案例：查询带’k’员工的名字、上级的名字

  1. SELECT e.last_name,m.last_name
  2. FROM employees e
  3. JOIN employees m
  4. ON e.`manager_id`=m.`employee_id`
  5. WHERE e.`last_name` LIKE '%k%';

image.png

5.7.2 外连接

  1. /*通用场景：用于查询一个表中有，另一个表中没有的记录
  2. 特点：
  3. 1.外连接的查询结果为主表中的所有记录
  4.     如果从表中有和它匹配的，则显示匹配的值
  5.   如果从表中没有和它匹配的，则显示null
  6.   外连接查询结果=内连接查询结果+主表中有而从表没有的
  7.  2.左外连接，left join左边的是主表
  8.       右外连接，right join右边的是主表
  9.  3.左外和右外交换两个表的顺序，可以实现同样的效果
  10. */

案例1：选择没有男朋友的女神

  1. SELECT b.name
  2. FROM beauty b                            # 主表beauty
  3. LEFT OUTER JOIN boys bo        # 从表boys    
  4. ON b.`boyfriend_id`=bo.`id`
  5. WHERE bo.`id` IS NULL;

image.png
案例2：查询哪个部门没有员工

  1. # 左外
  2. SELECT d.*,e.employee_id
  3. FROM departments d
  4. LEFT OUTER JOIN employees e
  5. ON d.`department_id`=e.`department_id`
  6. WHERE e.`employee_id` IS NULL;
  8. # 右外
  9. SELECT d.*,e.employee_id
  10. FROM employees e 
  11. RIGHT OUTER JOIN departments d
  12. ON d.`department_id`=e.`department_id`
  13. WHERE e.`employee_id` IS NULL;

image.png

5.7.3 总结

image.png
内连接
image.png
左外右外及其筛选
image.png
全外连接及其筛选

5.7.4 多表连接案例查询

案例1：查询编号>3的女神的男朋友的信息，如果有则详细列出，如果没有，用null填充

  1. use girls;
  2. SELECT b.id,b.name,bo.*
  3. FROM beauty b
  4. LEFT OUTER JOIN boys bo
  5. ON b.`boyfriend_id` = bo.`id`
  6. where b.`id`>3;

image.png
案例2：查询哪个城市没有部门

  1. use myemployees;
  2. SELECT city,d.*
  3. FROM departments d
  4. RIGHT OUTER JOIN locations l
  5. ON d.`location_id`=l.`location_id`
  6. WHERE d.`department_id` IS NULL;

image.png
案例3：查询部门名为SAL或IT的员工信息

  1. use myemployees;
  2. SELECT e.*,d.department_name
  3. FROM departments d 
  4. LEFT JOIN employees e
  5. ON d.`department_id`=e.`department_id`
  6. WHERE d.`department_name` IN ('SAL', 'IT');

image.png

5.7.5 sql99测试

  1. 显示所有员工的姓名，部门号和部门名称
  2. 查询 90 号部门员工的 job_id 和 90 号部门的location_id
  3. 选择所有有奖金的员工的last_name, department_name, location_id, city
  4. 选择city在Toronto工作的员工的last_name , job_id , department_id , department_name
  5. 查询每个工种、每个部门的部门名、工种名和最低工资
  6. 查询每个国家下的部门个数大于 2 的国家编号
  7. 选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
    | employees | Emp# | manager | Mgr# | | —- | —- | —- | —- | | kochhar | 101 | king | 100 |