mysql重点攻克：分组查询和连接查询（建议收藏逐一攻克）_分组查询和连接查询作用一样嘛

前言：

一、分组查询

1、语法

2、特点

3、案例解析

二、连接查询

1、内连接

2、外连接

3、交叉连接

总结

---

前言：

     在学习mysql或者其它数据库语言的时候，吃透DQL查询部分，就已经掌握大半了。所以这里就单独的来吃透分组查询和连接查询，当然子查询作为最难的部分单独出来，可以收藏作为复习文章哦。

使用的数据库一览表（作为案例提供）：

数据库名：`myemployees`

数据库各表及其字段：

• 部门表：
• 员工表： 
• 工资等级表： 
• 工作信息表： 
• 地址表： 

---

一、分组查询

介绍：

     一看到分组查询，我就想到了分组函数（max,min,avg）。当然，我还想到了group by（难以忘记刚学sql的时候支支吾吾和别人解释了半天什么叫分组查询，group by的作用......实在尴尬）。之前我们介绍过mysql的常见函数，其中就有分组函数，我们分组函数查询的结果往往是所有行的，而我们使用group by就将它进行了分组。

     例如：我们使用count(*)计算出了全校的人数，如果要求每个年级的人数，那么我就需要group by 将年级作为分组条件来进行分组查询。那么让我们一起来看看吧~

 

1、语法

select 分组函数，列（要求出现在group by的后面）
from 表
【where 筛选条件】
group by 分组的列表
【having 筛选条件】
【order by 字句】

     而我们常见的分组函数就有：sum求和、avg平均值、max最大值、min最小值、count计算个数，上面语法中需要注意的是筛选条件的位置什么时候放在having之后，我的口诀就是分组函数的查询结果做列表的话条件放在having之后，否则放在where之后。

 

2、特点

     分组查询中的筛选条件分为两类

 

3、案例解析

1）查询每个工种的最高工资

     从题目我们了解到需要最高工资salary，分组条件就是工种号，那么代码如下：

SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id;

 

2）查询每个位子的部门个数

     查部门个数用count(*)，然后通过location_id分组

SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;

 

3）查询有奖金的每个领导手下的最高工资

     首先用到max查最高工资，然后通过领导分组，之后在where后面添加有奖金的这个条件（因为没有用到分组函数结果，所以不再group by后面添加）。

SELECT MAX(salary),manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;

 

4）查询每个部门每个工种的员工的平均工资

     这里一看就是两个分组条件：部门和工种，然后分组函数为avg。所以有

SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id;

 

5）查询每个部门每个工种的员工的平均工资（大于1500）

    那么大于1500的条件一定是放在having之后的：

SELECT AVG(salary) a,department_id,job_id
FROM employees
GROUP BY department_id,job_id
HAVING a>1500;

     如果我们将平均工资排个序，那么就用到了order by

 

6）查询每个部门每个工种的员工的平均工资（大于1500）并降序排序

SELECT AVG(salary) a,department_id,job_id
FROM employees
GROUP BY department_id,job_id
HAVING a>1500
ORDER BY a DESC;

     group by后面不可以起别名哦......

---

二、连接查询

介绍：

     提起连接查询，你可能想到了笛卡尔积，而我想到的就是字面上的连接，脑海里也会浮现初次学习时的内连接，外连接，交叉连接......

我们的连接按照功能分类：

内连接：

• 等值连接
• 非等值连接
• 自连接

外连接：

• 左外连接
• 右外连接
• 全外连接

交叉连接

语法：

     在我们学习语法之前，我们需要知道连接查询标准分为sql92标准和sql99标准（顾名思义就是1992年和1999年提出的标准），虽然两者都是很好理解的，但是建议使用sql99标准的语法，不仅仅是因为它的逻辑性，也是因为sql92仅仅支持内连接，而sql99支持内连接+外连接（左外和右外）+交叉连接。全外连接很少用到，但是oracle支持（虽然建议使用sql99，但是内连接查询案例时也会展示sql92语法）。

sql92语法：
 
select 查询列表
from 表 别名
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】

sql99语法：
 
select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】
其中连接类型有：
内连接：inner
外连接：
    左外：left 【outer】
    右外：right 【outer】
    全外：full 【outer】
交叉连接：
    cross

     观察一下其实sql92和sql99语法区别不是很大，表达的也是一致的。

使用的数据库一览表（作为案例提供）：

数据库名：girls

各数据表及其字段：

管理员表：

女神表：

男神表：

 

1、内连接

1）等值连接（其实就是找交集部分）

看图理解：

案例：

1.1）查询女神名和对应的男神名

     那么我们的连接条件肯定是id号，即女神表中的男友id和男神表中的男神id，连接在一起就可以了。

sql92标准：
SELECT be.name,bo.`boyName`
FROM beauty be,boys bo
WHERE be.`boyfriend_id`=bo.`id`;
 
 
sql99标准：
SELECT be.name,bo.`boyName`
FROM beauty be
INNER JOIN boys bo
ON be.`boyfriend_id`=bo.`id`;

     这么一对比，的确没有什么区别对吧。

 

1.2）查询每个工种的工种名和员工的个数，并且按照员工个数降序

     我们通过工种名分组，查个数，然后连接条件就是job_id，之后order by降序即可。

sql92标准：
SELECT COUNT(*),j.job_title
FROM jobs j,employees e
WHERE j.job_id=e.job_id
GROUP BY j.job_title
ORDER BY COUNT(*) DESC;
 
sql99标准
SELECT COUNT(*),j.job_title
FROM jobs j
INNER JOIN employees e
ON j.`job_id`=e.`job_id`
GROUP BY j.job_title
ORDER BY COUNT(*) DESC;

 

特点：

• 多表等值连接的结果为多表的交集部分
• n表连接，至少需要n-1个连接条件
• 多表的顺序没有要求
• 一般需要为表起别名（特别是很长的那种表名，起了之后就要用别名去限制）
• 可以搭配排序、分组、筛选（以下均可）

 

2）非等值连接（其实和上面的等值连接区别就是连接条件不是==，而是><或者其它的）

2.1）查询员工的工资和工资级别

     所以一看就知道连接条件是between and。

sql92标准：
SELECT salary,grade_level
FROM employees e,job_grades j
WHERE e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;
 
sql99标准：
SELECT salary,grade_level
FROM employees e
INNER JOIN job_grades j
ON e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;

 

3）自连接（一定要使用别名哦）

3.1）查询员工名和上级的名称

     因为他们都在一张表里面，所以我们使用自连接，也就是连接自己。

sql92标准：
SELECT e1.employee_id,e1.last_name 老板,e2.`employee_id`,e2.last_name 员工
FROM employees e1,employees e2
WHERE e1.`employee_id`=e2.`manager_id`;
 
sql99标准：
SELECT e1.employee_id,e1.last_name 老板,e2.`employee_id`,e2.last_name 员工
FROM employees e1
INNER JOIN employees e2
ON e1.`employee_id`=e2.`manager_id`;

 

2、外连接

应用场景：用于查询一个表中有，另一个表没有的记录。

特点：

• 外连接的查询结果为主表中的所有记录

           如果从表中有和它匹配的，则显示匹配的值

           如果从表中没有和它匹配的，则显示null

           外连接查询结果=内连接结果+主表中有而从表中没有的记录

• 左外连接，left join左边的是主表；右外连接，right join右边的是主表
• 左外和右外交换两个表的顺序，可以实现同样的效果
• 全外连接=内连接的结果+表1中有而表2没有的+表2有但表1没有的

1）左外连接

1.1）查询哪个部门没有员工

     我们将部门表作为主表，之后连接员工表即可。

SELECT d.*,e.employee_id
FROM departments d
LEFT OUTER JOIN employees e
ON d.`department_id`=e.`department_id`
WHERE e.`department_id` IS NULL;

 

2）右外连接

2.1）查询哪个部门没有员工

     和上面一样，交换一下即可，同样的效果。

SELECT d.*,e.department_id
FROM employees e
RIGHT OUTER JOIN departments d
ON d.`department_id`=e.`department_id`
WHERE e.`department_id` IS NULL;

 

3）全外连接

     mysql不支持这个，效果如图示所占面积，这里给出一个例子看看语法形式（如果学习Oracle可以去了解）：

SELECT b.*,bo.*
FROM beauty b
FULL OUTER JOIN boys bo
ON b.boyfriend_id=bo.id;

 

3、交叉连接

    相当于笛卡尔乘积，给出一个代码的例子：

SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;

---

总结

     分组查询和连接查询用的都是挺多的，所以在了解语法之后，还是需要多做练习题，希望对大家有帮助。