mysql(六)连接查询

#连接查询 又称为多表查询
/*
  笛卡尔乘积现象：表1 m行， 表2 n行。 结果 m*n行
  原因是：没有有效的连接条件
 
  分类：
      SQL92:
      SQL99: 支持内连接和外连接（左外和右外）+交叉连接
  
  按功能分类：
       内连接：
              等值连接
              非等值连接
              自连接
       外连接：
              左外连接
              右外连接
              全外连接
       交叉连接：
*/
USE girls;
SELECT NAME,boyName FROM boys,beauty
WHERE beauty.boyfriend_id =boys.id;

SQL92

一、等值连接

#一、SQL92 标准
#(1)、等值连接
/*
   A:可以多表连接

*/
#案例1：查询女神名对应的 男神名
SELECT NAME,girls.boys.boyName FROM boys,beauty
WHERE girls.beauty.boyfriend_id=girls.boys.id;
#案列2：查询员工名和对应的部门名
SELECT myemployees.employees.last_name,myemployees.departments.department_name FROM
myemployees.employees,myemployees.departments
WHERE employees.department_id=departments.department_id;

#(2)为表起别名
/*
(2.1)、提高语句的间接度
(2.2)、避免二义性
注意：如果用了别名，就不要用 原始的表名了;  其实这是 替换了
*/
#查询员工名、工种号、工总名
SELECT e.last_name,e.job_id,j.job_title
FROM myemployees.employees e,myemployees.jobs j
WHERE e.job_id=j.job_id;

#(3)可以交换 表的顺序


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

#(5)、可以加分组
#案列：查询每个城市的部门个数

SELECT count(*),l.city
FROM myemployees.departments d,myemployees.locations l
WHERE d.location_id=l.location_id
GROUP BY l.city;
#(6)、可以加having 和排序

二、非等值连接

 非等值连接
案列1：查询员工的工资和工资级别


USE myemployees;

SELECT e.salary,j.grade_level
FROM myemployees.employees e,myemployees.job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal
ORDER BY grade_level;

三、自连接

#自连接;就是自己在自己表中找
#案列:查询 员工名和上级的名称

SELECT e.last_name,e.employee_id,m.employee_id,m.last_name
FROM myemployees.employees e,myemployees.employees m
WHERE e.manager_id=m.employee_id;

SQL99

/*
语法：
select 表1 别名 【连接类型】
from 表2 别名 on 连接条件
WHERE 筛选条件

内连接 ；inner
外连接   左外LEFT、右外 RIGHT、全外
交叉连接

*/

一、内连接

/*
SELECT 查询列表 from 表 1 INNER JOIN 表2 别名
ON 连接条件;
分类：
等值、非等值、自连接
*/

#1、等值连接
#案列1：查询员工名、部门名
SELECT e.last_name,d.department_name
FROM myemployees.employees e INNER JOIN myemployees.departments d
ON e.department_id=d.department_id;

#案列2：查询哪个部门的员工个数 >3 的部门名和员工个数，并按个数降序

SELECT d.department_name,COUNT(*)AS 个数
FROM myemployees.departments d INNER JOIN myemployees.employees e
ON e.department_id=d.department_id
GROUP BY d.department_name
HAVING 个数>3
ORDER BY 个数 DESC;

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

-----------非等值连接

#非等值连接

#查询员工的工资级别

SELECT e.salary,g.grade_level
FROM myemployees.employees e INNER JOIN myemployees.job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal
ORDER BY grade_level;

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

------------内连接

#自连接
#案列1、查询员工的名字、上级的名字
SELECT e.last_name,e.manager_id,m.employee_id,m.last_name
FROM employees e INNER JOIN employees m
ON e.manager_id=m.employee_id;

外连接

/*
 应用场景:用于查询一个表中有，另一个表没有的记录
 特点:
 1、外连接的查询结果为主表中的所有记录
    如果从表中有和它匹配的，则显示匹配的值
    如果从表中没有和它匹配的，则显示为null
   
    外连接查询结果=内连接结果+主表中有而从表没有的记录
  
 2、左外连接(LEFT 左边是主表)
    右外连接(right 右边是主表)
 3、全外连接 是主从表的并集
 eg:{1,2,3,4,5,6}主 {3,4,7,8}从
 全外就是 {1,2,3,4,5,6,7,8}
*/

#案列1:查询男盆友 不在 男神表的女神名

SELECT g.name,b.*
FROM girls.beauty g LEFT JOIN girls.boys b
ON g.boyfriend_id = b.id
WHERE b.id IS NULL;

#全外
FULL OUTER JOIN

交叉连接
就是 笛卡尔乘积