mysql 查询字段聚合逗号隔开 mysql查询结果用逗号连接

#进阶6：连接查询
/*
含义：又称多表查询，当查询的字段来自多个表时

笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行
	发生原因：没有有效的连接条件
	如何避免：添加有效的连接条件
	
分类：
按年代分类：
	sql92标准：仅仅支持内连接
	sql99标准【推荐】：支持内连接、外连接（左外和右外）、交叉连接
	
按功能分类：
	内连接：
		等值连接
		非等值连接
		自连接
	外连接：
		左外连接
		右外连接
		全外连接
	交叉连接
	
*/
SELECT * FROM `beauty`;

SELECT NAME,`boyName` FROM `boys`,`beauty`;-- 笛卡尔乘积现象

SELECT NAME,`boyName` FROM `boys`,`beauty`
WHERE `beauty`.`boyfriend_id` = `boys`.`id`;


#-------------------------------------SQL92语法--------------------
#一、内连接

#一）等值连接
/*
语法：
	select 查询列表
	from 表名1 别名1，表名2 别名2，...
	where 等值连接的连接条件
	and 筛选条件
	group by 分组列表
	having 分组后筛选
	order by 排序列表

执行顺序：

	1、from子句
	2、where子句
	3、and子句
	4、group by子句
	5、having子句
	6、select子句
	7、order by子句
	
多表连接的特点：
	1、多表等值连接的结果为多表的交集部分
	2、n表连接，至少需要n-1个连接条件
	3、多表的顺序无要求
	4、一般需要为表起别名
	5、可以搭配前面所有字句进行使用，比如排序、分组、筛选等
*/
#1、简单的两表连接
USE `myemployees`;
#案例：查询员工名和对应的部门名
SELECT `last_name`,`department_name`
FROM `departments` d,`employees` e
WHERE e.`department_id` = d.`department_id`;
-- 如果为表起了别名，则查询的字段就不能使用原来的表名去限定
-- from表的顺序可以随便

#2、添加筛选条件
#案例1：查询部门编号>100的部门名和所在的城市名
SELECT `department_name`,`city`
FROM `departments` d,`locations` l
WHERE d.`location_id` = l.`location_id` 
AND d.`department_id` >100;
#案例2：查询有奖金的员工名和部门名
SELECT `last_name`,`department_name`
FROM `employees` e,`departments` d
WHERE e.`department_id` = d.`department_id`
AND e.`commission_pct` IS NOT NULL;
#案例3：查询城市名中第二个字符为o的部门名和城市名
SELECT `department_name`,`city`
FROM `departments` d,`locations` l
WHERE d.`location_id` = l.`location_id`
AND l.`city` LIKE '_o%';

#3、添加分组+筛选
#案例1：查询每个城市的部门个数
SELECT COUNT(*),`city`
FROM `departments` d,`locations` l
WHERE d.`location_id` = l.`location_id`
GROUP BY l.`city`;
#案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT `department_name`,d.`manager_id`,MIN(`salary`)
FROM `departments` d,`employees` e
WHERE d.`department_id` = e.`department_id`
AND e.`commission_pct` IS NOT NULL
GROUP BY d.`department_name`,d.`manager_id`;

#4、添加分组、筛选、排序
#案例1：查询哪个部门的员工个数>5,并按员工个数进行降序
SELECT `department_name` 部门名,COUNT(*) 员工个数
FROM `departments` d,`employees` e
WHERE e.`department_id` = d.`department_id`
GROUP BY d.`department_name`
HAVING COUNT(*)>5
ORDER BY 员工个数 DESC;

#案例2：查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT `job_title` 工种名,COUNT(*) 员工个数
FROM `jobs` j,`employees` e
WHERE j.`job_id` = e.`job_id`
GROUP BY e.`job_id`
HAVING 员工个数 >0
ORDER BY 员工个数 DESC;

#5、三表连接
#案例：查询员工名、部门名和所在的城市
SELECT `last_name`,`department_name`,`city`
FROM `employees` e,`departments` d,`locations` l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`;


#二）非等值连接

#案例1：查询员工的工资和工资级别
SELECT `salary`,`grade_level`
FROM `employees` e,`job_grades` j
WHERE e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;


#三）自连接
/**/

#案例：查询 员工名和上级的名称
SELECT e.`employee_id`,e.`last_name`,m.`employee_id`
FROM `employees` e,`employees` m
WHERE e.`manager_id` = m.`employee_id`;

#-------------------------------------SQL99语法--------------------
#一、内连接
/*
语法：
select 查询列表
from 表名1 别名
【inner 】join 表名2 别名
on 连接条件
where 筛选条件
group by 分组列表
having 分组后筛选
order by 排序列表：

92和99对比：
	SQL99，使用JOIN关键字代替了之前的逗号，并且将连接条件和筛选条件进行了分离，提高阅读性
*/
	#一）等值连接
#1、简单连接
#案例：查询员工名和部门名
SELECT `last_name`,`department_name`
FROM `employees` e
INNER JOIN `departments` d
ON e.`department_id` = d.`department_id`;
#2添加筛选条件
#案例1：查询部门编号>100的部门名和所在的城市名
SELECT department_name,city
FROM departments d
JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE d.`department_id`>100;
#3、添加分组+筛选
#案例1：查询每个城市的部门个数
SELECT COUNT(*) 部门个数,`city`
FROM `departments` d
INNER JOIN `locations` l
ON d.`location_id` = l.`location_id`
GROUP BY l.`city`;
#4、添加分组+筛选+排序
#案例1：查询部门中员工个数>10的部门名，并按员工个数降序
SELECT `department_name`,COUNT(*) 员工个数
FROM `departments` d
INNER JOIN `employees` e
ON d.`department_id` = e.`department_id`
GROUP BY `department_name`
HAVING 员工个数>10
ORDER BY 员工个数 DESC;

	#二）非等值连接
#案例：查询部门编号在10-90之间的员工的工资级别，并按级别进行分组
SELECT `grade_level` 工资级别,COUNT(*) 个数
FROM `employees` e
INNER JOIN `job_grades` g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
WHERE e.`department_id` BETWEEN 10 AND 99
GROUP BY g.`grade_level`

	#三）自连接

#案例：查询员工名和对应的领导名
SELECT e.`last_name` 员工名, m.last_name 领导名
FROM `employees` e
INNER JOIN `employees` m
ON e.`manager_id` = m.`employee_id`

#二、外连接
/*
说明：查询结果为主表中所有的记录，如果从表有匹配项，则显示匹配项；如果从表没有匹配项，则显示null
应用场景：一般用于查询主表中有但从表没有的记录

特点：
1、外连接分主从表，两表的顺序不能任意调换
2、左连接的话，left join左边为主表
   右连接的话，right join右边为主表
  
语法：
select 查询列表
from 表1 别名
left|right 【outer】 join 表2 别名
on 连接条件
where 筛选条件;

全外连接：查询结果为所有表的记录，mySQL不支持

语法：
select 查询列表
from 表1 别名
left|right 【outer】full join 表2 别名
on 连接条件
where 筛选条件;
*/
#案例1：查询所有女神记录，以及对应的男神名，如果没有对应的男神，则显示为null
#左连接
SELECT b.*,bo.*
FROM `beauty` b
LEFT OUTER JOIN `boys` bo
ON b.`boyfriend_id` = bo.`id`;

#右连接
SELECT b.*,bo.*
FROM `boys` bo 
RIGHT OUTER JOIN `beauty` b
ON b.`boyfriend_id` = bo.`id`;

#案例2：查哪个女神没有男朋友

SELECT b.*,bo.*
FROM `beauty` b
LEFT OUTER JOIN `boys` bo
ON b.`boyfriend_id` = bo.`id`
WHERE bo.`id` IS NULL;
-- 一般查从表的主键


#案例3：查询哪个部门没有员工，并显示其部门编号和部门名
SELECT COUNT(*) 部门个数
FROM `departments` d
LEFT JOIN `employees` e
ON d.`department_id` = e.`department_id`
WHERE e.`department_id` IS NULL;

#三、自然连接（不常用）

-- ##########################练习
#1、查询编号>3的女神的男朋友信息，如果有则列出来详细，如果没有，用null填充
SELECT b.`id`,bo.*
FROM `beauty` b
LEFT JOIN `boys` bo
ON b.`boyfriend_id` = bo.`id`
WHERE b.`id`>3;
#2、查询哪个城市没有部门
SELECT l.`city`
FROM `locations` l
LEFT JOIN `departments` d
ON l.`location_id` = d.`location_id`
WHERE d.`department_id` IS NULL;
#3、查询部门名为SAL或IT的员工信息
SELECT d.`department_name`,e.*
FROM `departments` d
LEFT JOIN `employees` e
ON d.`department_id` = e.`department_id`
WHERE d.`department_name` = 'SAL'
OR d.`department_name` = 'IT';