连接查询

又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

1.连接查询的分类

1. 按年代分类：

   sql92标准:仅仅支持内连接
   sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接

2. 按功能分类

2. 特点

1. 多表等值连接的结果为多表的交集部分
2. n个表的连接，至少需要 n - 1个连接条件
3. 多表的顺序没有要求
4. 一般需要为表起别名
5. 可以搭配排序，分组，筛选等一起使用

   等值查询

   1. 简单举例

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

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

   2. 为表起别名

   ①提高语句的简洁度
   ②区分多个重名的字段
   注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定

   # 案例：查询员工名及其对应的员工名，工种号，工种名
   SELECT e.last_name,e.job_id,j.job_title
   FROM employees  e,jobs j
   WHERE e.`job_id`=j.`job_id`;

   3. 两个表的顺序可以调换

   # 案例：查询员工名及其对应的员工名，工种号，工种名
   SELECT e.last_name,e.job_id,j.job_title
   FROM employees  e,jobs j
   WHERE e.`job_id`=j.`job_id`;

   4.添加筛选

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

   5. 添加分组

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

   6. 添加排序

   # 案例：查询每个工种的工种名和员工的个数，并且按员工个数降序
   SELECT job_title,COUNT(*)
   FROM employees e,jobs 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 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;

   8. 非等值连接

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

   9. 自连接

   #案例：查询 员工名和上级的名称
   
   SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
   FROM employees e,employees m
   WHERE e.`manager_id`=m.`employee_id`;

   SQL99下的查询

   1. 语法：

   SELECT 查询列表
   FROM 表1 别名 [连接类型]
   JOIN 表2 别名 
   ON 连接条件
   [WHERE 筛选条件]
   [GROUP BY 分组]
   [HAVING 筛选条件]
   [ORDER BY 排序列表]

   分类：

   2. 内连接

   1. 等值连接

   特点：

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

  # 案例：查询员工名和对应的部门名
  SELECT last_name,department_name
  FROM employees e 
  INNER JOIN departments d 
  ON e.department_id = d.department_id;

  # 案例：查询名字中包含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%';

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

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

  # 案例：查询员工名，部门名，工种名，并且按部门名降序(多表连接)
  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 
  ORDER BY d.department_name DESC;

  2.非等值连接

  # 案例：查询员工的工资级别  
  SELECT salary ,grade_level
  FROM employees e
  INNER JOIN job_grades j 
  ON e.salary BETWEEN j.lowest_sal AND j.highest_sal;

  # 案例：查询工资级别>20级别的个数，并且按工资级别降序
  SELECT COUNT(*),grade_level
  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;

  3. 自连接

  # 案例: 查询员工的名字以及其上级的名字
  SELECT e.last_name , m.last_name
  FROM employees e
  INNER JOIN employees m
  ON e.manager_id = m.employee_id;

  外连接

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

  特点：
  1.外连接的查询结果为主表中的所有记录，如果从表中有和它匹配的，则显示匹配的值，如果从表中没有和它匹配的，则显示为null。
  外连接的查询结果 = 内连接结果 + 主表中有而从表中没有的记录
  2.左外连接，left join 左边的是主表
    右外连接，right join  右边的是主表
  3.左外和右外交换两个表的顺序，可以实现同样的效果
  4.全外连接 = 内连接的结果 + 表1中有但表二中没有的 + 表2中有但表1中没有的

  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;

  2. 右外连接

  案例：
  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;

  3. 全外连接

  USE girls;
  SELECT b.*,bo.*
  FROM beauty b
  FULL OUTER JOIN boys bo
  ON b.`boyfriend_id` = bo.id;

  4. 交叉连接

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

赏

谢谢你请我吃糖果

微信