MySQL学习记录（六）--连接查询

#进阶六：连接查询
/*
含义：多表查询，查询字段涉及多个表

笛卡尔乘积现象：表1：m行；表二：n行，结果=m*n行

发生原因：没有有效的连接条件

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

*/  

SELECT * FROM `beauty`;
SELECT * FROM `boys`;

#笛卡尔乘积
SELECT NAME,`boyName`FROM `boys`,`beauty`;
#连接
SELECT NAME,`boyName`FROM `boys`,`beauty`
WHERE boys.`id` = `beauty`.boyfriend_id;



#sql92标准
/*
1.多表等值连接好结果为多表的交集部分
2.多表顺序没有要求
3.一般需要为表起别名
4.可以使用所有子句使用，排序、分组、筛选

*/

#1.等值连接

#案例1：查询女神对应的男神名
SELECT NAME,`boyName`
FROM `boys`,`beauty` 
WHERE boys.`id` = `beauty`.boyfriend_id;

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

#2.为表起别名
/*
1.提高简洁度
2.区分多个重名的字段
3.起了别名、则查询字段不能使用原表名了
*/

#查询工种号、工种名、员工名
SELECT e.`job_id`,`last_name`,`job_title`
FROM `employees`AS e,`jobs`
WHERE `jobs`.`job_id` = e.`job_id`

#3.表的顺序是否可以调换
SELECT e.`job_id`,`last_name`,`job_title`
FROM `jobs`,`employees`AS e
WHERE `jobs`.`job_id` = e.`job_id`

#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

#案例2：城市中第二个字符为o的部门名和城市名
SELECT `city`,`department_name`
FROM `departments`,`locations`
WHERE `departments`.`location_id` = `locations`.`location_id`
AND SUBSTR(`locations`.`city` ,2,1) = 'o'

#5.是否可以加分组
#案例1：查询每个城市的部门个数
SELECT `city`,`department_id`,COUNT(*)
FROM `departments`,`locations`
WHERE `departments`.`location_id` = `locations`.`location_id`
GROUP BY `city`

#案例2：查询有奖金的部门名和部门领导编号和该部门最低工资
SELECT `department_name`,MIN(`salary`)
FROM `employees`,`departments`
WHERE `employees`.`department_id` = `departments`.`department_id`
AND `employees`.`commission_pct` IS NOT NULL 
GROUP BY `department_name`;

#6.可不可以加排序
#查询每个工种的工种名和员工的个数，并按员工个数降序
SELECT `job_title`,COUNT(*) AS geshu
FROM `employees`,`jobs`
WHERE `employees`.`job_id`=`jobs`.`job_id`
GROUP BY `job_title`
ORDER BY  geshu DESC ;

#7.三表连接
#案例：查询员工名和部门名和所在的城市
SELECT `last_name`,`job_title`,`city`
FROM `locations`,`jobs`,`employees`,`departments`
WHERE `locations`.`location_id` = `departments`.`location_id`
AND `employees`.`department_id` = `departments`.`department_id`
AND `jobs`.`job_id` = `employees`.`job_id`

#二.非等值连接

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

#三、自连接
#案例：查询 员工名 及上级名称
SELECT e.`last_name`,e.`employee_id`,m.`employee_id`,m.`last_name`
FROM `employees`AS e ,`employees` AS m
WHERE e.`employee_id`  = m.`employee_id`


#练习
SELECT MAX(`salary`),AVG(`salary`)
FROM `employees`;

SELECT `employee_id`,`job_id`,`last_name`
FROM `employees`
ORDER BY `department_id`DESC ,`salary`ASC;

SELECT DISTINCT(`job_id`)
FROM `employees`
WHERE `job_id`LIKE '%a%e%';

SELECT NOW();


#练习
SELECT `last_name`,`departments`.`department_id`,`department_name`
FROM `departments`,`employees`
WHERE `employees`.`department_id` = `departments`.`department_id`;

SELECT `location_id`,`job_id`
FROM `departments`,`employees`
WHERE `employees`.`department_id` = `departments`.`department_id`
AND `departments`.`department_id` = 90;

SELECT `last_name`,`department_name`,`locations`.`location_id`,`city`
FROM `departments`,`employees`,`locations`
WHERE `employees`.`department_id` = `departments`.`department_id`
AND`departments`.`location_id` = `locations`.`location_id`
AND `commission_pct` IS NOT NULL;

SELECT `last_name`,`job_id`,e.`department_id`,`department_name`,`city`
FROM `departments` d,`employees` e,`locations` l
WHERE e.`department_id`=d.`department_id`
AND d.`location_id` = l.`location_id`
AND city = 'toronto';

SELECT `department_name`, `job_title`,MIN(`salary`)
FROM `employees` e ,`departments` d ,`jobs` j
WHERE e.`department_id`=d.`department_id`
AND e.`job_id` = j.`job_id`
GROUP BY `department_name`,`job_title`;

SELECT `department_id`,`country_id`
FROM `locations`,`departments`
WHERE `locations`.`location_id` = `departments`.`location_id`
GROUP BY `city`
HAVING COUNT(`department_id`)>2;

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


#sql99语法
/*
语法；
	select 查询列表
	from 表1 别名 【连接类型】
	join 表2 别名 
	on 连接条件
	【where 筛选条件】
	【group by 筛选条件】
	【order by 排序列表】
分类：	
内连接（※）：inner
外连接
	左外（※）：left【outer】
	右外（※）：right【outer】
	全外：full 【outer】
交叉连接：cross

*/

#一、内连接
/*
语法：
selelct 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件；

分类：
	等值连接
	非等值
	自连接
特点：
1.可添加排序分组筛选
2.inner可以省略
3.筛选条件放在where的后面。连接条件放在on 的后面


*/

#案例1.查询员工名和部门名
SELECT `last_name`,`department_name`
FROM `employees`
INNER JOIN`departments`
ON `employees`.`department_id` = `departments`.`department_id`;

#案例2.查询名字中包含e的员工名和工种名（添加筛选）
SELECT `last_name`,`job_title`
FROM`employees` e 
INNER JOIN `jobs` j
ON e.`job_id` = j.`job_id`
WHERE `last_name` LIKE '%e%';

#案例3.查询部门个数>3的城市名和部门个数（添加分组和筛选）
SELECT `city`,COUNT(*) 个数
FROM `departments`
INNER JOIN `locations`
ON `locations`.`location_id` = `departments`.`location_id`
GROUP BY `city`
HAVING 个数>3

#案例4.查询部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）
SELECT `department_name`,`employees`.`department_id`,COUNT(*) AS 个数
FROM `departments`
INNER JOIN `employees`
ON `departments`.`department_id` = `employees`.`department_id`
GROUP BY `employees`.`department_id`
HAVING 个数>3
ORDER BY 个数 DESC ;

#案例5.查询员工名、部门名、工种名、并按部门名排序
SELECT `last_name`,`department_name`,`job_title`
FROM `employees` e
INNER JOIN `departments` d  ON e.`department_id` =d.`department_id`
INNER JOIN `jobs` j ON j.`job_id` = e.`job_id`
ORDER BY department_name DESC
 
#2、非等值连接

#查询员工的工资级别
SELECT `salary`,`last_name`,`grade_level`
FROM `job_grades` j
JOIN `employees` e
ON e.`salary` BETWEEN `lowest_sal` AND `highest_sal`
ORDER BY `salary` DESC

#查询每个工资级别的个数，并按工资级别排序
SELECT `salary`,`last_name`,`grade_level`,COUNT(*)
FROM `job_grades` j
JOIN `employees` e
ON e.`salary` BETWEEN `lowest_sal` AND `highest_sal`
GROUP BY `grade_level`
HAVING COUNT(*) > 20
ORDER BY `grade_level` DESC

#3、自连接
#查询员工名字和上级的名字
SELECT e.`last_name`,m.`last_name`
FROM `employees` e 
JOIN `employees` m
ON e.`manager_id` = m.`employee_id`



#二、外连接

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

特点：
1.外连接查询结果为主表中的所有记录
	如果有和它匹配的，则显示匹配的值
	如果从表中没有和它匹配的，则显示null
	外连接查询结果 = 内连接结果+主表中有而从表中没有
2.左外连接，left join 左边是主表
右外连接，right join 右边是主表

3.左外和右外 交换两个表的位置，可以实现同样的效果

*/

#引入：查询没有男朋友的女神
SELECT * FROM `beauty`·

SELECT `beauty`.`name`
FROM `beauty`
LEFT JOIN `boys`
ON `beauty`.`boyfriend_id` = `boys`.`id`
WHERE  `boys`.id IS NULL


#案例1：哪个部门没有员工
#左外
SELECT d.*,e.`employee_id`
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 JOIN boys bo

#交叉连接
#笛卡尔乘积

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

#练习

SELECT bo.*,b.`id`
FROM `beauty` b
LEFT JOIN `boys` bo 
ON b.`boyfriend_id` = bo.`id`
WHERE b.`id`>3

SELECT l.city ,`department_id`
FROM `locations` l
LEFT JOIN `departments` d
ON l.`location_id` = d.`location_id`
WHERE `department_id` IS NULL

SELECT e.*
FROM `employees` e
LEFT JOIN `departments` d
ON e.`department_id` = d.`department_id`
WHERE `department_name` = 'SAL'OR`department_name` = 'IT'