#进阶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;
