mysql的sqlyog学习笔记（高级查询部分）（代码用命令行显示 ）

分组查询

语法：
select 查询列表
from 表名
where 筛选条件
group by 分组列表
having 分组后筛选
order by 排序列表;

执行顺序：
①from子句
②where子句
③group by 子句
④having子句
⑤select子句
⑥order by子句

分组前筛选 原始表 where group by 的前面

分组后筛选 分组后的结果集 having group by的后面

where——group by ——having

分组函数做条件只可能放在having后面！！！

简单分组

查询每个工种的员工平均工资

mysql> select avg(salary),job_id
    -> from employees
    -> group by job_id;
+--------------+------------+
| avg(salary)  | job_id     |
+--------------+------------+
|  8300.000000 | AC_ACCOUNT |
| 12000.000000 | AC_MGR     |
|  4400.000000 | AD_ASST    |
| 24000.000000 | AD_PRES    |
| 17000.000000 | AD_VP      |
|  7920.000000 | FI_ACCOUNT |
| 12000.000000 | FI_MGR     |
|  6500.000000 | HR_REP     |
|  5760.000000 | IT_PROG    |
| 13000.000000 | MK_MAN     |
|  6000.000000 | MK_REP     |
| 10000.000000 | PR_REP     |

查询每个领导的手下人数

mysql> select count(*),manager_id
    -> from employees
    -> where manager_id is not null
    -> group by manager_id;
+----------+------------+
| count(*) | manager_id |
+----------+------------+
|       14 |        100 |
|        1 |        102 |
|        4 |        103 |
|        5 |        101 |
|        5 |        108 |

可以实现分组前的筛选

查询邮箱中包含a字符的 每个部门的最高工资

mysql> select max(salary) 最高  ,department_id #最高后要加空格
    -> from employees
    -> where email like '%a%'
    -> group by department_id;
+----------+---------------+
| 最高     | department_id |
+----------+---------------+
|  7000.00 |          NULL |
|  4400.00 |            10 |
| 13000.00 |            20 |
| 11000.00 |            30 |
|  6500.00 |            40 |
|  8200.00 |            50 |

可以实现分组后的筛选

查询哪个部门的员工个数>5

1. 查询每个部门的员工个数
2. 在刚才的结果基础上，筛选哪个部门的员工个数>5

mysql> select count(*) 员工个数 ,department_id
    -> from employees
    -> group by department_id
    -> having count(*)>5;
+--------------+---------------+
| 员工个数     | department_id |
+--------------+---------------+
|            6 |            30 |
|           45 |            50 |
|           34 |            80 |
|            6 |           100 |
+--------------+---------------+

领导编号>102的 每个领导手下的最低工资大于5000的最低工资

1. 查询每个领导手下员工的最低工资
2. 筛选刚才1的结果

mysql> SELECT MIN(salary) 最低工资,manager_id
    -> from employees
    -> WHERE manager_id>102
    -> GROUP BY manager_id
    -> HAVING MIN(salary)>5000 ;
+--------------+------------+
| 最低工资     | manager_id |
+--------------+------------+
|      6900.00 |        108 |
|      7000.00 |        145 |
|      7000.00 |        146 |
|      6200.00 |        147 |
|      6100.00 |        148 |
|      6200.00 |        149 |
|      6000.00 |        201 |
|      8300.00 |        205 |
+--------------+------------+

可以实现排序

查询没有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序

1. 按工种分组，查询每个工种有奖金的员工的最高工资

2. 筛选刚才的结果，看哪个最高工资>6000

3. 按最高工资升序

mysql> SELECT MAX(salary) 最高工资,job_id
    -> FROM employees
    -> WHERE commission_pct IS  NULL
    -> GROUP BY job_id
    -> HAVING MAX(salary)>6000
    -> ORDER BY MAX(salary) ASC;
+--------------+------------+
| 最高工资     | job_id     |
+--------------+------------+
|      6500.00 | HR_REP     |
|      8200.00 | ST_MAN     |
|      8300.00 | AC_ACCOUNT |
|      9000.00 | FI_ACCOUNT |
|      9000.00 | IT_PROG    |
|     10000.00 | PR_REP     |
|     11000.00 | PU_MAN     |
|     12000.00 | AC_MGR     |
|     12000.00 | FI_MGR     |
|     13000.00 | MK_MAN     |
|     17000.00 | AD_VP      |
|     24000.00 | AD_PRES    |
+--------------+------------+

按多个字段分组

查询每个工种每个部门的最低工资,并按最低工资降序

mysql> SELECT MIN(salary) 最低工资,job_id,department_id
    -> FROM employees
    -> GROUP BY job_id,department_id
    -> order by job_id,department_id;
+--------------+------------+---------------+
| 最低工资     | job_id     | department_id |
+--------------+------------+---------------+
|      8300.00 | AC_ACCOUNT |           110 |
|     12000.00 | AC_MGR     |           110 |
|      4400.00 | AD_ASST    |            10 |
|     24000.00 | AD_PRES    |            90 |
|     17000.00 | AD_VP      |            90 |
|      6900.00 | FI_ACCOUNT |           100 |
|     12000.00 | FI_MGR     |           100 |
|      6500.00 | HR_REP     |            40 |
|      4200.00 | IT_PROG    |            60 |
|     13000.00 | MK_MAN     |            20 |
|      6000.00 | MK_REP     |            20 |
|     10000.00 | PR_REP     |            70 |

连接查询（SQL99语法）

内连接

语法：

select 查询列表
from 表名1 别名
【inner】 join 表名2 别名
on 连接条件
where 筛选条件
group byY 分组列表
having分组后筛选
order by 排序列表;

等值连接

简单查询

查询员工名和部门名

SELECT last_name,department_name
FROM departments d 
JOIN  employees e 
ON e.department_id =d.department_id;

添加筛选条件

查询部门编号>100的部门名和所在的城市名

SELECT department_name,city
FROM departments d
JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE d.`department_id`>100;

添加分组+筛选

查询每个城市的部门个数

SELECT COUNT(*) 部门个数,l.`city`
FROM departments d
JOIN locations l
ON d.`location_id`=l.`location_id`
GROUP BY l.`city`;

添加分组+筛选+排序

查询部门中员工个数>10的部门名，并按员工个数降序

SELECT COUNT(*) 员工个数,d.department_name
FROM employees e
JOIN departments d
ON e.`department_id`=d.`department_id`
GROUP BY d.`department_id`
HAVING 员工个数>10
ORDER BY 员工个数 DESC;

非等值连接

查询部门编号在10-90之间的员工的工资级别，并按级别进行分组

SELECT COUNT(*) 个数,grade
FROM employees e
JOIN sal_grade g
ON e.`salary` BETWEEN g.`min_salary` AND g.`max_salary`
WHERE e.`department_id` BETWEEN 10 AND 90
GROUP BY g.grade;

自连接

查询员工名和对应的领导名

SELECT e.`last_name`,m.`last_name`
FROM employees e
JOIN employees m
ON e.`manager_id`=m.`employee_id`;