MySQL数据库：第八章：连接查询

回退至Mysql数据库理论与实战

#进阶7：连接查询

理解：查询语句中涉及到的字段来自于多张表，将这种查询称为多表连接查询
语法：select 查询列表 from 表名1,表名2;
引入案例：
select name,boyname from beauty,boys;
select name,boyname from beauty,boys where boyfriend_id = boys.id;
笛卡尔乘积：
现象：表1和表2连接，结果为两表的完全连接结果，数据不正确
表1m行，表2 n行，结果为：m*n 行
产生原因：没有有效的连接条件
解决办法：添加两个表的连接条件
★ 找到两个表的关联关系。两个表的关联列的意思肯定是一样，但名称不一定一样！一般通过主外键列进行关联。
连接查询的分类 ★：
sql92语法：
内连接
等值连接
非等值连接
自连接
外连接（支持的不太好，mysql压根不支持）
sql99语法：
内连接
等值连接
非等值连接
自连接
外连接
左外连接
右外连接
全外连接（mysql不支持）

#---------------------------------Sql92语法--------------------------------
#内连接
#一、等值连接

语法：
select 查询列表
from 表名1 别名1,表名2 别名2
where 别名1.关联列 = 别名2.关联列
and 筛选条件
group by 分组字段
having 分组后的筛选条件
order by 排序;
特点：
1、多表连接时，一般为表起别名，提高语句的简洁性
a 、别名要短于 表名
b 、一旦为表起了别名，则只能使用别名限定，不能使用表名限定了!
2、表是否可以调换顺序
答案：可以！不分主次表！
3、等值连接查询，查询的结果为两个表的交集部分
4、n表连接，至少需要n-1个连接条件

#①简单的两表连接
#案例：查询员工名和部门名

SELECT e.last_name,d.department_name
FROM departments d,employees e
WHERE e.`department_id` = d.`department_id`;

#②添加筛选条件
#案例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 city LIKE '_o%';

#③添加分组+筛选 【难点】
#案例1：查询每个城市的部门个数

SELECT COUNT(*) 部门个数,city
FROM departments d,locations l
WHERE d.`location_id` = l.`location_id`
GROUP BY city;

#案例2：查询有奖金的每个部门的部门名和该部门的最低工资

SELECT MIN(salary) 最低工资,department_name
FROM departments d,employees e
WHERE d.`department_id` = e.`department_id`
AND commission_pct IS NOT NULL
GROUP BY department_name

#案例3：查询部门中员工个数>10的部门名

SELECT COUNT(*),department_name
FROM departments d,employees e
WHERE d.`department_id` = e.`department_id`
GROUP BY department_name
HAVING COUNT(*)>10;

#④添加分组+筛选+排序
#案例1：查询部门中员工个数>10的部门名，并按部门名降序

SELECT COUNT(*),department_name
FROM departments d,employees e
WHERE d.`department_id` = e.`department_id`
GROUP BY department_name
HAVING COUNT(*)>10
ORDER BY department_name DESC;

#案例2：查询每个工种的员工个数和工种名，并按个数降序

SELECT COUNT(*) 个数,job_title
FROM employees e,jobs j
WHERE e.`job_id` = j.`job_id`
GROUP BY job_title
ORDER BY 个数 DESC;

#⑤三表连接

#案例：查询员工名、部门名、城市名
SELECT last_name,department_name,city
FROM departments d,employees e,locations l
WHERE d.`department_id` = e.`department_id`
AND d.`location_id` = l.`location_id`;

#二、非等值连接
select 查询列表
from 表名1 别名1,表名2 别名2
where 非等值的连接条件
【and 筛选条件
group by 分组字段
having 分组后的筛选条件
order by 排序】;

#案例：查询员工的姓名、工资、工资级别
SELECT last_name,salary,grade
FROM employees e,sal_grade g
WHERE e.`salary` BETWEEN g.`min_salary` AND g.`max_salary`
ORDER BY grade;

#三、自连接
#案例：查询每个员工的员工名和领导名

SELECT 员工表.last_name,领导表.last_name
FROM employees 员工表,employees 领导表
WHERE 员工表.`manager_id` = 领导表.`employee_id`;
SELECT last_name FROM employees WHERE employee_id = 149;

#1.查询 90 号部门员工的 job_id 和 90 号部门的 location_id

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

#2. 选择所有有奖金的员工的

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

#sal_grade的创建语句

USE  myemployees;
DROP TABLE IF EXISTS sal_grade;
CREATE TABLE sal_grade (
	id INT PRIMARY KEY AUTO_INCREMENT,
	min_salary DOUBLE ,
	max_salary DOUBLE,
	grade CHAR
);
INSERT INTO sal_grade VALUES(NULL,2000,3999,'A');
INSERT INTO sal_grade VALUES(NULL,4000,5999,'B');
INSERT INTO sal_grade VALUES(NULL,6000,9999,'C');
INSERT INTO sal_grade VALUES(NULL,10000,12999,'D');
INSERT INTO sal_grade VALUES(NULL,13000,14999,'E');
INSERT INTO sal_grade VALUES(NULL,15000,99999,'F');

#--------------------------------------------------SQL99语法--------------------------
#内连接
#一、等值连接

语法：
select 查询列表
from 表 别名1
【inner】 join 表 别名2
on 别名1.关联列 = 别名2.关联列
where 筛选条件
group by 分组
having 分组后条件
order by 条件;
区别：
①sql99语法，使用join连接，并且通过on添加连接条件，语义性更强！
连接条件和筛选条件进行了分离，提高维护性和分离性！
特点：
1、多表连接时，一般为表起别名，提高语句的简洁性
a 、别名要短于 表名
b 、一旦为表起了别名，则只能使用别名限定，不能使用表名限定了!
2、表是否可以调换顺序
答案：可以！不分主次表！
3、等值连接查询，查询的结果为两个表的交集部分
4、n表连接，至少需要n-1个连接条件

#案例：查询部门中员工个数>10的部门名，并按部门名降序
SELECT COUNT(*) 员工个数,department_name
FROM employees e
INNER JOIN departments d ON e.`department_id` = d.`department_id`
GROUP BY department_name
HAVING 员工个数>10
ORDER BY department_name DESC;

#二、非等值连接

SELECT FLOOR(RAND()*77+1) 随机名字;
#案例：查询员工的姓名、工资、工资级别
SELECT last_name,salary,grade
FROM employees e
JOIN sal_grade g 
ON  e.`salary` BETWEEN g.`min_salary` AND g.`max_salary`;

#三、自连接

#案例：查询员工号为165的领导的名字和领导工资
SELECT m.last_name,m.salary
FROM employees e
JOIN employees m
ON e.`manager_id` = m.`employee_id`
WHERE e.`employee_id` = 165;

#外连接

语法：
select 查询列表
from 表 别名1
left|right|full 【outer】 join 表 别名2
on 别名1.关联列 = 别名2.关联列
WHERE 筛选条件
GROUP BY 分组
HAVING 分组后条件
ORDER BY 条件;
功能：查询主表中的所有记录，如果从表有和主表匹配的信息，则显示匹配信息。否则显示null
一般适合查询主表中有，但从表中没有的记录
外连接的结果=内连接结果+主表有从表没有的！
特点：
1、左连接 left join，左边的表就是主表
右连接 right join，右边的表就是主表
从一定角度上讲，左连接和右连接可以通过调换两表顺序，最终实现同样的效果！

#案例1 ：查询哪个部门没有员工
#左连接：
SELECT d.*
FROM departments d
LEFT JOIN employees e ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;
#右连接
SELECT d.*
FROM   employees e 
RIGHT JOIN departments d ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;
#案例2：查询城市名包含a字符的哪个城市没有部门，并按城市名降序
SELECT city
FROM locations l
LEFT JOIN departments d ON l.`location_id` = d.`location_id`
WHERE city LIKE '%a%'
AND d.`department_id` IS NULL
ORDER BY city DESC;
#右连接
SELECT city
FROM departments d
RIGHT JOIN locations l  ON l.`location_id` = d.`location_id`
WHERE city LIKE '%a%'
AND d.`department_id` IS NULL
ORDER BY city DESC;