连接查询

#连接查询
/*
 多表查询
 笛卡尔乘积现象：表1有m行，表2有n行，结果n*m行
 发生原因：没有有效的连接条件
 如何避免: 添加有效的连接条件
 
 
 
 分类：

    按年代分类：
    sql92标准:仅仅支持内连接
    sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接
    
    按功能分类：
        内连接：
            等值连接
            非等值连接
            自连接
        外连接：
            左外连接
            右外连接
            全外连接
        
        交叉连接
*/
SELECT * FROM `beauty`;
SELECT * FROM `boys`;
SELECT NAME,`boyName` FROM `beauty`,`boys`;#每一个都全连接 ：n*m



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

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


*/
#案例1： 查询女神名和对应的男神名
SELECT NAME,`boyName` FROM `beauty`,`boys`
WHERE `beauty`.`boyfriend_id`  = `boys`.`id`;#要用表名限制是谁的id
#案例2： 查询员工名和对应的部门名
SELECT `last_name`,`department_name`
FROM `departments`,`employees`
WHERE `departments`.`department_id` = `employees`.`department_id`;


#2、为表起别名
/*
①提高语句的简洁度
②区分多个重名的字段

注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定

*/
#查询员工名、工种号、工种名



SELECT e.`last_name`,e.`job_id`,j.`job_title`  #job_id 有歧义
FROM `employees`  e,`jobs` j
WHERE e.`job_id` = j.`job_id`;

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

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


#4、可以加筛选


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



#案例2：查询城市名中第二个字符为o的部门名和城市名
SELECT d.`department_name`,l.`city`
FROM `departments` d,`locations` l
WHERE d.`location_id` = l.`location_id`
AND l.`city` LIKE '_o%';
#5、可以加分组
#每个城市的部门个数
SELECT COUNT(*),city
FROM `locations` l,`departments` d
WHERE l.`location_id` = d.`location_id`
GROUP BY city;
#查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT `department_name`,d.`manager_id`,MIN(salary),`commission_pct`
FROM `employees` e,`departments` d
WHERE  e.`department_id` = d.`department_id` AND `commission_pct` IS NOT NULL
GROUP BY `department_name`,d.`manager_id`;#更保险



#6、可以加排序
#案例：查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT `job_title`,COUNT(*)
FROM `employees` e,`jobs` j
WHERE e.`job_id` = j.`job_id`
GROUP BY j.`job_title`#必须分组，不然直接count() 所有
ORDER BY COUNT(*) DESC;

#7、可以实现三表连接？

#案例：查询员工名、部门名和所在的城市

SELECT `last_name`,d.`department_name`,`city`
FROM `employees` e,`departments` d,`locations` l
WHERE e.`department_id` = d.`department_id` 
AND d.`location_id` = l.`location_id`
AND city LIKE 's%'
ORDER BY department_name DESC;

 

 

#创建工资等级表
CREATE TABLE job_grades
(grade_level VARCHAR(3),
 lowest_sal  INT,
 highest_sal INT);

INSERT INTO job_grades
VALUES ('A', 1000, 2999);

INSERT INTO job_grades
VALUES ('B', 3000, 5999);

INSERT INTO job_grades
VALUES('C', 6000, 9999);

INSERT INTO job_grades
VALUES('D', 10000, 14999);

INSERT INTO job_grades
VALUES('E', 15000, 24999);

INSERT INTO job_grades
VALUES('F', 25000, 40000);

#2、非等值连接


#案例1：查询员工的工资和工资级别
SELECT `last_name`,salary,`grade_level` 
FROM `job_grades` j,`employees`
WHERE `salary` BETWEEN  j.`lowest_sal` AND j.`highest_sal`
AND j.`grade_level` ='A';



#3、自连接



#案例：查询 员工名和上级的名称
SELECT a.`employee_id`,a.`last_name` "employee_last_name",b.`employee_id` "manager_id", b.`last_name`  "managet_last_name"
FROM `employees` a,`employees`b
WHERE a.`manager_id` =b.`employee_id`;#b.`employee_id` 不能为null

 

 

例题讲解

#查询所有员工的姓名，部门号和部门名称
SELECT `last_name`,`employees`.`department_id`,`department_name`
FROM `departments`,`employees`
WHERE `departments`.`department_id` = `employees`.`department_id`;
#查询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;
#查询所有有奖金的员工的`department_name``last_name``location_id``city`
SELECT d.`department_name`,e.`last_name`,l.`location_id`,l.`city`
FROM `departments` d,`employees` e,`locations` l
WHERE d.`department_id` = e.`department_id` 
AND l.`location_id` = d.`location_id` AND e.`commission_pct` IS NOT NULL;

#选择city在Toronto工作员工的 `last_name``job_id``department_id``department_name`
SELECT `last_name`,`job_id`,d.`department_id`,`department_name`
FROM `locations` l,`employees` e,`departments` d
WHERE d.`department_id` = e.`department_id` AND d.`location_id` = l.`location_id`
AND `city` = 'Toronto';

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

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

#选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号
SELECT a.`last_name` '员工姓名',a.`employee_id` '员工号',b.`last_name` '管理者姓名',b.`employee_id` '员工号'
FROM `employees` a,`employees` b
WHERE a.`manager_id` =b.`employee_id`;

 

 

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

分类：
内连接（★）：inner
外连接
    左外(★):left 【outer】
    右外(★)：right 【outer】
    全外：full【outer】
交叉连接：cross 

*/
#一）内连接
/*
语法：

select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件;

分类：
等值
非等值
自连接

特点：
①添加排序、分组、筛选
②inner可以省略
③ 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
④inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集





*/


#1、等值连接
#案例1.查询员工名、部门名

SELECT `last_name`,`department_name`
FROM `employees` e
INNER JOIN `departments`  d#两表的顺序可换
ON e.`department_id` = d.`department_id`;


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

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



#案例4.查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）
SELECT `department_name`,COUNT(*)
FROM `departments` d
INNER JOIN `employees` e
ON d.`department_id` = e.`department_id`
GROUP BY d.`department_id`
HAVING COUNT(*) >3
ORDER BY COUNT(*) 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 e.`job_id` = j.`job_id`#j在和大的表连接，因此这里只要有一个可以和j连接就可以
ORDER BY `department_name` DESC;
#二）非等值连接

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

 #三）自连接
 
 #查询员工的名字、上级的名字
 SELECT a.`last_name` '员工名字',b.`last_name` '上级的名字'
 FROM `employees` a
 INNER JOIN `employees` b ON a.`manager_id` = b.`employee_id`;
 
  #查询姓名中包含字符k的员工的名字、上级的名字
 SELECT a.`last_name` '员工名字',b.`last_name` '上级的名字'
 FROM `employees` a
 INNER JOIN `employees` b ON a.`manager_id` = b.`employee_id`
 WHERE a.`last_name` LIKE '%k%';
 内连接：只返回两个表中连接字段相等的行。
  #二、外连接
 
 /*
 应用场景：用于查询一个表中有，另一个表没有的记录
 
 特点：
 1、外连接的查询结果为主表中的所有记录
    如果从表中有和它匹配的，则显示匹配的值
    如果从表中没有和它匹配的，则显示null
    外连接查询结果=内连接结果+主表中有而从表没有的记录
 2、左外连接，left join左边的是主表
    右外连接，right join右边的是主表
 3、左外和右外交换两个表的顺序，可以实现同样的效果 
 4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的
 */
 #引入：查询男朋友 不在男神表的的女神名
 
 SELECT b.name
 FROM `beauty` b 
 LEFT JOIN `boys` bo
 ON b.`boyfriend_id` = bo.`id`
 WHERE bo.id IS  NULL;#因为id 为主键不会null，其它的本身也可能是null
 
 
 SELECT b.*,bo.*
 FROM `boys` bo
 LEFT JOIN `beauty` b 
 ON b.`boyfriend_id` = bo.`id`;
 WHERE bo.id IS  NULL;
 
 #案例1：查询哪个部门没有员工
 #左外
 SELECT d.*,e.`employee_id`
 FROM `departments` d
 LEFT OUTER JOIN `employees` e
 ON d.`department_id` = e.`department_id`
 WHERE e.`employee_id` IS NULL;
 
 #右外
 SELECT d.*,e.`employee_id`
 FROM `employees` e
 RIGHT OUTER JOIN `departments` d
 ON d.`department_id` = e.`department_id`
 WHERE e.`employee_id` IS NULL;
 
 
 SELECT * FROM `beauty`;
 #交叉连接  (笛卡尔积)
SELECT b.*,bo.*
FROM `beauty` b
CROSS JOIN `boys` bo;

 #sql92和 sql99pk
 /*
 功能：sql99支持的较多
 可读性：sql99实现连接条件和筛选条件的分离，可读性较高
 */
 
 
 
 
 
 

 

连接示意图

 

 

 

 

 

 

#连接例题 
 #查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有则用null填充
 
 SELECT b.id,b.name,bo.*
 FROM `beauty` b
 LEFT JOIN `boys` bo
ON b.`boyfriend_id` = bo.`id`
 WHERE  b.id>3;
 
 #查询哪个城市没有部门
 SELECT `city`
 FROM `locations` l
 LEFT JOIN `departments` d
 ON l.`location_id` = d.`location_id`
 WHERE d.`department_id` IS NULL;
 
 #查询部门为SAL 或者IT 的员工信息
 SELECT d.`department_name`,e.*
 FROM `employees` e
LEFT JOIN `departments` d #以d为主表会出现NULL信息，改为e为主表即可(left join)
 ON e.`department_id` = d.`department_id`
 WHERE d.`department_name` IN ('SAL','IT');
 
 
----------------------------------
 SELECT * FROM `departments`
  WHERE `department_name` IN ('SAL','IT');