mysql 分组 拼接函数 mysql分组连接

分组查询(GROUP BY)

语法

SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];

• 查询列表必须是分组函数和group by后面出现的字段(column)
• 支持多个字段分组，即可以有多个column，用逗号分隔

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

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

添加筛选

例2：查询哪个部门员工数大于2
在原表内没有的字段是不可以使用在WHERE子句中的，WHERE是对分组前的每一行数值进行筛选，HAVING则是对分组后的每一行数据进行筛选。

比如下面的代码中，不可以写成

WHERE COUNT(*)>2
#因为count(*)不是已有字段

正确解法是：

SELECT department_id,COUNT(*) 员工个数
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;

• 分组函数的筛选全都是使用HAVING(如上述count)
• 在可以使用WHERE时，优先使用WHERE
• GROUP BY和HAVING都支持别名

连接查询

当查询的字段涉及多个表，就要使用连接查询，简单来说就是给字段加上前缀，说明其来自哪个表
分类：
按年代分类：

分类
按年代分类	sql92标准	仅支持内连
	sql99标准	【推荐】支持内+左外+右外+交叉
按功能分类	内连接	等值连接
		非等值连接
		自连接
	外连接	左外连接
		右外连接
		全外连接
	交叉连接

sql92标准

1. 等值连接

#例：查询员工名和对应部门名
SELECT last_name,department_name
FROM employees,departments
WHERE employees.`department_id`=departments.`department_id`;

• 由于连接的使用，为了方便简洁，有时会为表起别名
• 如果为表起了别名，查询字段不可以使用原表名限定

#查询员工名，工种号，工种名
SELECT e.last_name,e.job_id,j.job_title
FROM employees e,jobs j
WHERE e.`job_id`=j.`job_id`;
#查询字段可以加前缀限制，也可以不加

• 多表等值连接的结果为多表交集部分
• n个表连接，至少需要n-1个连接条件

2. 非等值连接
   即连接条件处不再是=
   例：查询员工工资和工资级别

#创建等级表
CREATE TABLE job_grades
(grade_level VARCHAR(3),
lowest_sal INT,
highest_sal INT);
INSERT INTO job_grades
VALUES('A',1000,2999);
INSERT INTO job_grades
VALUES('B', 3000,5999);
INSERT INTO job_grades
VALUES('C', 6000,9999);
INSERT INTO job_grades
VALUES('D', 10000,14999);
INSERT INTO job_grades
VALUES('E', 15000,24999);
INSERT INTO job_grades
VALUES('F', 25000,40000);

SELECT salary, grade_level
FROM employees e, job_grades j
WHERE e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;

3. 自连接
   自己连接自己，类似于等值连接，其使用技巧在于利用别名创建了两个一样的表。

#案例：查询员工名和上级名称，在这个例子中，每个人都有员工id和其上级id，
#故需要先查询每个员工，根据员工对应的上级id再进行查询，从而找到上级的名字。

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

sql99标准

语法

select 查询列表
from 表1 别名 
【连接类型】join 表2 别名 on 连接条件
【where 筛选条件】
【group by 分组】
......

内连接（inner）

1. 等值连接
   例：查询员工名，部门名

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

• 两个表可以交换顺序
• inner可以省略
• inner join和92的等值连接效果一致

2. 非等值连接
   例：查询工资级别

SELECT salary, grade_level
FROM employees e
JOIN job_grades j
ON e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;

3. 自连接
   类似地：

#查询员工名，上级名
SELECT e.`last_name`,e.`employee_id`,m.`last_name`,m.`manager_id`
FROM employees e
JOIN employees m
ON m.`employee_id`=e.`manager_id`;

外连接

外连接用于查询一个表中有，另一个表中没有的记录。
比如表A的id范围是0-10，表B的id为1，3，5，7，12，此时就无法使用上述内连接的方法，因为两个表的id不是一一对应的。

左、右外连接（left、right）

• 左外连接：left join 左边是主表
• 右外连接：right join右边是主表
• 左外和右外交换表的顺序可以实现同样的效果
• 外连接的查询结果为主表中的所有记录，如果从表中有和它匹配的，则显示匹配值；若没有匹配的，则显示NULL。所以区分主从表就在于看查询的目标是什么，若A表中的某字段是查询对象，则A为主表。
  例：查询部门名（departments）为SAL的员工信息（employees），此处需要部门名的所有信息，员工信息可以为空，故主表是departments。
• 外连接查询结果=内连接结果+主表有而从表没有的记录

左外语法（右外类似）：

select 查询列表
from 主表
left outer join 从表
on 连接条件

注意：选择连接条件的字段的时候，最好选择主键。

#查询哪个部门没有员工
SELECT department_name
FROM employees e
RIGHT JOIN departments d
ON e.`department_id`=d.`department_id`
WHERE employee_id IS NULL;

全外连接（outer）

• 全外连接和左（右）外连接的区别在于，不仅能查出来主表的记录，还能查出主表没有但从表有的记录。
  比如表A中id为1，2，3，4，5；表B中id为2，4，5，6；则最后中不仅有1，2，3，4，5的结果（1，3中B对应字段为NULL），还有id为6的结果（A对应字段为NULL）。
• 简单来说，全外连接结果就是左外连接+右外连接的并集。（A left join B + A right join B）

语法：

select 查询列表
from 表1
full outer join 表2
on 连接条件;

• 表1与表2顺序可以交换

交叉连接（cross）

select 查询列表
from 表1
cross join 表2;

• 最后的结果类似于笛卡尔乘积，比如A有4行，B有10行，最后结果是44行的表

总结