（MySQL学习笔记）连接查询

补发一下之前做的笔记

#连接查询

/*
含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

笛卡尔乘积现象： 表1-m行，表2-n行，结果有m*n行
产生原因：没有有效的连接条件
如何避免：添加有效的连接条件

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

SELECT * FROM beauty;

SELECT * FROM boys;

#没有有效的连接条件
SELECT NAME,boyName FROM beauty,boys;

#添加有效的连接条件
SELECT NAME,boyName FROM beauty,boys
WHERE beauty.boyfriend_id = boys.id;


#一、sql92标准
#1、等值连接

/*
①多表等值连接的结果为多表的交集部分
②n表连接至少需要n-1个连接条件
③多表的顺序没有要求
④一般要为表起别名
⑤可以搭配前面介绍的所有子句可以使用
*/

#例：查询女生名和对应的男生名
SELECT
	NAME,boyName
FROM
	beauty,boys
WHERE
	beauty.boyfriend_id = boys.id;
	
#例：查询员工名和对应的部门名
USE myemployees;
SELECT
	last_name,department_name
FROM 
	employees,departments
WHERE
	employees.department_id=departments.department_id;
	

#2、为表起别名
/*
①区分多个重名的字段
②如果为表起了别名，则查询的字段就不能使用原来的表名去限定
*/
#例：查询员工名、工种号、工种名
SELECT
	last_name,e.job_id,job_title
FROM
	employees AS e,jobs AS j
WHERE
	e.job_id=j.job_id;

	
#3、两个表的顺序可以调换


#4、可以加筛选

#例：查询有奖金的员工名和部门名
SELECT
	last_name,department_name,commission_pct
FROM
	employees,departments
WHERE
	employees.`department_id`=departments.`department_id` 
	AND
	employees.`commission_pct` IS NOT NULL;

#例：查询城市名中第二个字符为 o 的部门名和城市名
SELECT
	department_name,city
FROM
	departments,locations
WHERE
	departments.`location_id` = locations.`location_id`
	AND
	locations.`city` LIKE '_o%';
	

#5、可以加分组

#例：查询每个城市的部门个数
SELECT
	COUNT(*),city
FROM 
	locations,departments
WHERE
	departments.`location_id`=locations.`location_id`
GROUP BY
	locations.`city`;

#例：查询出有奖金的每个部门的部门名和部门编号以及该部门的最低工资
SELECT
	MIN(salary),d.department_name,d.manager_id
FROM
	employees e,departments d
WHERE
	e.`department_id` = d.`department_id`
	AND
	e.commission_pct IS NOT NULL
GROUP BY
	d.department_name,d.`manager_id`;

#6、可以加排序

#例：查询每个工种的工种名和员工的个数，按员工个数降序
SELECT
	job_title,COUNT(*)
FROM
	jobs j,employees e
WHERE
	e.`job_id` = j.`job_id`
GROUP BY
	job_title
ORDER BY
	COUNT(*) DESC;
	
	
#7、可以实现三表连接

#例：查询员工名、部门名和所在的城市
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`;
	
	
	
	
	
#2、非等值连接

#例：查询员工的工资和工资级别
/*
select 
	salary,grade_level
from
	employees e,job_grade g
where
	salary between g.lowest_sal and g.highest_sal;
*/




#3、自连接

#例：查询员工名和上级的名字
SELECT
	e1.last_name,e2.last_name
FROM
	employees e1,employees e2
WHERE
	e1.`manager_id` = e2.`employee_id`;
	





#例：显示员工表的最大工资，工资平均值
SELECT
	MAX(salary),AVG(salary),employee_id
FROM
	employees
GROUP BY
	employee_id;

#例：查询员工表的employee_id,job_id,last_name,按department_id降序，salary升序
SELECT
	employee_id,job_id,last_name
FROM
	employees
ORDER BY
	department_id DESC,
	salary ASC;

#例：查询员工表的job_id中包含a和e的，并且a在e前面
SELECT
	DISTINCT job_id
FROM
	employees
WHERE
	job_id LIKE '%a%e%'

#例：显示当前日期，以及去前后空格，截取子字符串的函数
SELECT NOW();
SELECT TRIM(' ' FROM NOW());
SELECT SUBSTR(NOW(),1,4);



#例：显示所有员工的姓名、部门号和部门名称
SELECT
	e.last_name,e.department_id,d.department_name
FROM 
	employees e,departments d
WHERE
	e.department_id = d.`department_id`;


#例：查询90号部门员工的job_id和90号部门的location_id
SELECT
	e.job_id,d.location_id
FROM
	employees e,departments d
WHERE
	d.`department_id`=90
	AND
	e.`department_id` = d.`department_id`;


#例：选择所有有奖金的员工的姓名，部门名，部门所在地编号，城市
SELECT
	e.last_name,d.department_name,d.location_id,l.city
FROM
	employees e,departments d,locations l
WHERE
	e.`commission_pct` IS NOT NULL
	AND 
	e.`department_id` = d.`department_id`
	AND 
	d.`location_id` = l.`location_id`;


#例：选择city在Toronto工作的员工的姓名，工作编号，部门编号，部门名
SELECT
	e.last_name,e.job_id,e.department_id,d.department_name
FROM
	employees e,departments d,locations l
WHERE
	e.`department_id` = d.`department_id`
	AND
	d.`location_id` = l.`location_id`
	AND
	l.`city`='Toronto';
	

#例：查询每个工种、每个部门的部门名、工种名和最低工资
SELECT
	d.department_name,j.job_title,MIN(salary)
FROM
	employees e,departments d,jobs j
WHERE	
	e.`job_id` = j.`job_id`	
	AND
	e.`department_id` = d.`department_id`
GROUP BY
	j.job_title,d.`department_id`;


#例：查询每个国家下的部门个数大于2的国家编号
SELECT
	l.country_id,COUNT(*)
FROM 
	locations l,departments d
WHERE
	d.`location_id` = l.`location_id`
GROUP BY
	l.`country_id`
HAVING 
	COUNT(*)>2;


#例：选择指定员工的姓名、员工号 以及他的管理者的姓名和员工号。结果类似于下面的格式
SELECT
	e1.last_name,e1.employee_id,e2.last_name,e2.`employee_id`
FROM
	employees e1,employees e2
WHERE
	e1.`manager_id` = e2.`employee_id`
	AND
	e1.`last_name` = 'kochhar';




#二、sql99
/*

语法：
	select 查询列表
	from 表1 别名 【连接类型】
	join 表2 别名 on 连接条件
	【where 筛选条件】
	【group by】
	【having 筛选条件】
	【order by 排序列表】

连接类型：
	内连接:inner
	外连接:
		左外:left[outer]
		右外:right[outer]
		全外:full[outer]
		#outer都可以省略
	交叉连接：cross
*/


#（一）内连接
/*
select 查询列表
from 表1 别名 
inner join 表2 别名 
on 连接条件
where 筛选条件
group by 
having
order by 


分类：
	等值连接
	非等值连接
	自连接


特点：
	可以添加排序，分组，筛选
	inner可以省略
	筛选条件放在where后面，连接条件放在on后面
	inner join与sql92中的等值连接效果是一样的，都是查询多表的交集
*/



#1、等值连接

#例：查询员工名、部门名
SELECT
	last_name,department_name
FROM 
	departments d
INNER JOIN
	employees e
ON
	e.`department_id`= d.`department_id`;

#例：查询名字中包含e的员工名和工种名
SELECT
	e.last_name,j.job_title
FROM
	employees e
INNER JOIN 
	jobs j
ON
	e.`job_id` = j.`job_id`
WHERE
	e.`last_name` LIKE '%e%';
	
#例：查询部门个数>3的城市名和部门个数
SELECT
	l.city,COUNT(*)
FROM
	locations l
INNER JOIN 
	departments d
ON 
	d.`location_id` = l.`location_id`
GROUP BY
	l.city
HAVING 
	COUNT(*)>3;

#例：查询哪个部门的部门员工个数>3的部门名和员工个数，并按个数降序排序
SELECT
	d.department_name,COUNT(*)
FROM
	departments d
INNER JOIN 
	employees e
ON 
	e.`department_id` = d.`department_id`
GROUP BY 
	d.`department_id`
HAVING 
	COUNT(*)>3
ORDER BY 
	COUNT(*) DESC;
	
#例：查询员工名、部门名、工种名，并按部门名降序
SELECT
	e.last_name,d.department_name,j.job_title
FROM 
	employees e
INNER JOIN 
	departments d
ON
	e.`department_id` = d.`department_id`
INNER JOIN 
	jobs j
ON 
	e.`job_id` = j.`job_id`
ORDER BY 
	department_name;
	


#2、非等值连接

#例：查询员工的工资级别
/*
select salary,grade_level
from employees e
inner join job_grades g
on e.salary between g.lowest_sal and g.highest_sal;
*/

#例：查询每个工资级别的个数>2的个数，并且按工资级别降序
/*
select count(*),grade_level
from employees e
inner join job_grades g
on e.salary between g.lowest_sal and g.highest_sal;
group by grade_level
having count(*)>2
order by grade_level desc;
*/


#3、自连接

#查询姓名中包含字符k的员工的名字以及他上级的名字
SELECT
	e1.last_name,e2.last_name
FROM
	employees e1
INNER JOIN 
	employees e2
ON 
	e1.`manager_id` = e2.`employee_id`
WHERE
	e1.`last_name` LIKE '%k%';
	
	
#（二）外连接
/*
应用场景：用于查询一个表中有，另一个表没有的记录

特点：	
	外连接的查询结果为主表中的所有记录
		如果从表中有匹配值，则显示匹配值
		如果从表中无匹配值，则显示null
		外连接查询结果 = 内连接结果+主表中有而从表中没有的记录
	左外连接，left左边的是主表
	右外连接，right右边的是主表
	左外和右外交换两个表的顺序，可以实现同样的效果
	全外连接 = 内连接的结果+表1中有但表2中无的+表2中有但表1中无的
*/
#例：查询没有男朋友的女生名
SELECT
	be.name,bo.*
FROM
	boys bo
LEFT JOIN
	beauty be
ON 
	bo.`id`= be.`boyfriend_id`
WHERE
	bo.`id` = NULL;
		
#例：查询哪个部门没有员工
SELECT
	d.department_name,e.*
FROM
	departments d
LEFT JOIN 
	employees e
ON
	d.`department_id` = e.`department_id`
WHERE
	e.employee_id IS NULL;
	
#（三）全外连接
SELECT b.*,bo.*
FROM beauty b
FULL OUTER JOIN boys bo
ON b.`boyfriend_id` = bo.id;




#交叉连接:笛卡尔乘积
SELECT
	b.*,bo.*
FROM
	beauty b
CROSS JOIN 
	boys bo;
	
	
	
#例：查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有则用null填充
SELECT
	be.name,bo.*
FROM
	beauty be
LEFT JOIN 
	boys bo
ON
	be.`boyfriend_id` = bo.`id`
WHERE
	be.id>3;

#例：查询哪个城市没有部门
SELECT
	l.city,d.*
FROM
	locations l
LEFT JOIN
	departments d
ON 
	d.`location_id` = l.`location_id`
WHERE
	d.`location_id` IS NULL;
	
#例：查询部门名为SAL或IT的员工信息
SELECT
	d.department_name,e.*
FROM 
	departments d
RIGHT JOIN
	employees e
ON
	d.`department_id` = e.`department_id`
WHERE
	d.`department_name` = 'SAL'
	OR
	d.`department_name` = 'IT';