#进阶7：连接查询
/*
多表连接：查询的字段来自多个表

按功能分类：
		内连接：
				等值连接
				非等值连接
                自连接
		外连接：
				左外连接
                右外连接
                全外连接
        交叉连接
*/

USE girls;

SELECT * FROM beauty; 
SELECT * FROM boys; 

#SQL92语法
#一、内连接
#1、等值连接
#案例1：查询女生对应的男生名
SELECT name, boyName
FROM beauty, boys
WHERE beauty.boyfriend_id = boys.id;


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


#使用别名
#如果有重名，可以这样解决。但如果表使用了别名，就不能再在查询中用原名
SELECT E.last_name, D.department_name
FROM myemployees.employees AS E, myemployees.departments AS D
WHERE E.department_id = D.department_id;


USE myemployees;


#加筛选
#案例3：查询有奖金的员工名、部门名
SELECT last_name, department_name, commission_pct
FROM employees as e, departments as d
WHERE (e.department_id = d.department_id) AND (e.commission_pct IS NOT NULL);


#案例4：查询城市名中第二个字符为o的部门名和城市名
SELECT department_name, city
FROM departments as d, locations as l 
WHERE (d.location_id = l.location_id) AND (department_name LIKE ‘_O%‘);


# 加分组
#案例5：查询每个城市的部门个数
SELECT count(*), city
FROM departments as d, locations as l 
WHERE d.location_id = l.location_id
GROUP BY city;


#加排序
#案例6：查询每个工种的工种名和员工个数，并且按员工个数降序
SELECT job_title, count(*)
FROM employees as e, jobs as j
WHERE e.job_id = j.job_id
GROUP BY job_title
ORDER BY count(*) DESC;


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


SELECT * FROM job_grades;

#2、非等值连接
#案例8：查询员工工资和工资级别
SELECT salary, grade_level
FROM employees as e, job_grades as g
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal
ORDER BY grade_level;


#3、自连接
#案例9：查询员工名和领导的名称
SELECT e1.last_name as 员工, e2.last_name as 领导
FROM employees as e1, employees as e2
WHERE e1.manager_id = e2.employee_id;



#SQL99语法
/*
SELECT 查询列表
FROM 表1 别名 【连接类型】
JOIN 表2 别名
ON 连接条件
【WHERE 筛选条件】
*/
#一、内连接：INNER
/*
SELECT 查询列表
FROM 表1 别名
INNER JOIN 表2 别名
ON 连接条件
【WHERE 筛选条件】

用于查询多个表中都有的记录，两个表的交集
*/
#1、等值连接
#案例1：查询员工名、部门名
SELECT last_name, department_name
FROM employees as e
INNER JOIN departments as d
ON e.department_id = d.department_id;

#案例2：查询员工名、部门名、工种名，并按部门名降序
SELECT last_name, department_name, job_title
FROM employees as e
INNER JOIN departments as d ON e.department_id = d.department_id
INNER JOIN jobs as j ON e.job_id = j.job_id
ORDER BY department_name DESC;


#2、非等值连接
#案例3：查询员工工资和工资级别
SELECT salary, grade_level
FROM employees as e
INNER JOIN job_grades as g
ON salary BETWEEN g.lowest_sal AND g.highest_sal
ORDER BY grade_level;

#案例4：查询每个工资级别>20的个数，并且按工资级别降序
SELECT count(*), grade_level
FROM employees as e
INNER JOIN job_grades as g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal
GROUP BY grade_level
HAVING count(*) > 20
ORDER BY grade_level DESC;


#3、自连接
#案例5：查询包含k字符的员工名、及其领导的名称
SELECT e1.last_name as 员工, e2.last_name as 领导
FROM employees as e1
INNER JOIN employees as e2
ON e1.manager_id = e2.employee_id
WHERE e1.last_name LIKE ‘%k%‘;


#二、外连接
/*
用于查询一个表中有、另一个表中没有的记录。主从表的交集+主从表的差集

有主从表之分

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

3、交换顺序，左外、右外等价
*/
#1、左外连接：LEFT 【OUTER】

USE girls;
#案例1：查询男友不在男生表中的的女生名
SELECT b.name
FROM beauty as b
LEFT OUTER JOIN boys as bo
ON b.boyfriend_id = bo.id
WHERE bo.id IS NULL;  # id是主键


#2、右外连接：RIGHT 【OUTER】
#交换顺序，主表在后，和左外连接等价
SELECT b.name
FROM boys as bo
RIGHT OUTER JOIN beauty as b
ON b.boyfriend_id = bo.id
WHERE bo.id IS NULL; 

#案例2：查询没有员工的部门
USE myemployees;
SELECT d.*, e.employee_id
FROM departments as d
LEFT OUTER JOIN employees as e
ON d.department_id = e.department_id
WHERE e.department_id IS NULL;


#3、全外连接：FULL 【OUTER】
#查询的是两个表的并集

#三、交叉连接：CROSS
#两个表的笛卡尔积


#多表连接案例
#查询编号>3的女生的男友信息，如果有则列出详细，如果没有则用null填充
USE girls;
SELECT b.id, b.name, bo.*
FROM beauty as b
LEFT OUTER JOIN boys as bo
ON b.boyfriend_id = bo.id
WHERE b.id > 3; 

#查询哪个城市没有部门
USE myemployees;
SELECT l.city
FROM locations as l
LEFT OUTER JOIN departments as d
ON l.location_id = d.location_id
WHERE d.location_id IS NULL; 


#查询部门名为Sal或IT的员工信息
SELECT e.*, d.department_name
FROM employees as e
INNER JOIN departments as d
ON e.department_id = d.department_id
WHERE d.department_name IN (‘Sal‘, ‘IT‘);