MYSQL--连接_mysql m*n的连接-CSDN博客

本文链接：https://blog.csdn.net/weixin_45215602/article/details/106216918

连接查询

含义：又称多表查询，当查询的字段来自于多个表时，救护用到连接查询

笛卡尔乘积现象：表1有M行，表2有N行，结果=M*N行
select name,boyname from boys,beauty
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件
select name,boyname from boys,beauty
WHERE beauty.boyfriend_id = boys.id;
分类：
按年代分类：
sql92标准：仅仅支持内连接
sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接

  	按功能分类：
  					内连接：
  									等值连接
  									非等值连接
  									自连接
  					外连接：
  									左连接
  									右连接
  									全外连接
  					交叉连接

USE girls;
SELECT * from beauty;
SELECT * FROM boys;

一、sql92标准

1.等值连接

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

#案例1：查询女神名和对应男神名
select name,boyname from boys,beauty
WHERE beauty.boyfriend_id = boys.id;

#案例2：查询员工名和对应的部门名
USE myemployees;

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

为表起别名

提高语句的简洁度
区分多个重名的字段

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

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

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

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

加筛选

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

#查询城市名中第二个字符为o的部门名和城市名
SELECT 
	department_name,
	city
from	
	departments d,
	locations l
WHERE
	d.location_id=l.location_id
AND	l.city LIKE '_o%';

加分组

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

#案例2：查询有奖金的每个部门名和部门的领导编号和该部门的最低工资
SELECT
	d.department_name,
	d.manager_id,
	MIN(e.salary)
from	
	departments d,
	employees e
WHERE
	d.department_id=e.department_id
AND e.commission_pct IS NOT NULL
GROUP BY
	d.department_name,d.manager_id;

加排序

#查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT
	job_title,
	COUNT(*)
from	
	employees e,
	jobs j
WHERE
	e.job_id=j.job_id
GROUP BY
	job_title
ORDER BY
	COUNT(*) DESC;

实现三表连接

#案例：查询员工名，部门名和所在城市
SELECT
	last_name,
	department_name,
	city
from	
	employees e,
	locations l,
	departments d
WHERE
	e.department_id=d.department_id
AND d.location_id=l.location_id
AND city LIKE 's%';

2.非等值连接

#建立等级表
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);

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

3.自连接

#案例：查询员工名和上级的名称
SELECT 
	e.employee_id 员工ID,
	e.last_name 员工名,
	m.employee_id	领导ID,
	m.last_name	领导名
from	
	employees e,
	employees m
WHERE
	e.manager_id=m.employee_id

#--------------------------------练习---------------------------------
#1.显示员工表的最大工资，工资平均值
SELECT	
	MAX(salary),
	AVG(salary)
FROM
	employees;

#2.查询员工表employee_id,job_id,last_name,按department_id降序，salary升序
SELECT
	employee_id,
	job_id,
	last_name
FROM
	employees
ORDER BY
	department_id desc,
	salary asc;

#3.查询员工表的job_id中包含a和e的，并且a在e前面
SELECT
	job_id
FROM
	employees
WHERE
	job_id LIKE '%a%e%';

#4.显示当前的日期，以及去前后空格，截取字符串的函数
SELECT NOW();
SELECT TRIM([remstr FROM] str);
SELECT SUBSTR(str FROM pos FOR len)
#---------------------------------------------------------------------

二、sql99

语法：

  	SELECT 查询列表
  	FROM 表1 别名 【连接类型】
  	jion 表2 别名
  	on 连接条件
  	【WHERE 筛选条件】
  	【group by 分组】
  	【having 筛选条件】
  	【order by 排序列表】

内连接（*） inner
外连接

  	左外（*）	left 【outer】
  	右外（*）	right 【outer】
  	全外	full 【outer】

交叉连接 CROSS

一、内连接

语法：
  	SELECT 查询列表
  	FROM 表1 别名 
  	inner jion 表2 别名
  	on 连接条件
分类：

等值
非等值
自连接

特点：

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

1.等值连接

#案例1.查询员工名、部门名
SELECT
	last_name,
	department_name
FROM
	departments d INNER JOIN employees e 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%';

#案例3.查询部门个数>3的城市名和部门个数（分组+筛选）
SELECT
	city,
	COUNT(*) 个数
FROM
	departments d INNER JOIN locations l on d.location_id=l.location_id
GROUP BY 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	department_name
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
order by department_name DESC;

2.非等值连接

#案例1.查询员工的工资级别
SELECT
	salary,
	grade_level
FROM
	employees e
JOIN job_grades g on e.salary BETWEEN g.lowest_sal and g.highest_sal;

#案例2.查询工资级别的个数>20的个数，并且按工资级别降序
SELECT
	COUNT(*),
	grade_level
FROM
	employees e
JOIN job_grades g on e.salary BETWEEN g.lowest_sal and g.highest_sal
group by grade_level
HAVING COUNT(*)>20
ORDER BY grade_level desc;

3.自连接

#案例1：查询员工名和上级的名称
SELECT 
	e.employee_id 员工ID,
	e.last_name 员工名,
	m.employee_id	领导ID,
	m.last_name	领导名
from	
	employees e 
JOIN
	employees m
on
	e.manager_id=m.employee_id;

#案例2：查询姓名中包含k的员工名和上级的名称
SELECT 
	e.employee_id 员工ID,
	e.last_name 员工名,
	m.employee_id	领导ID,
	m.last_name	领导名
from	
	employees e 
JOIN
	employees m
on
	e.manager_id=m.employee_id
WHERE
	e.last_name LIKE '%k%';

二、外连接

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

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

左外连接，右外连接

#引入：查询没有男朋友的女神名
USE girls;

SELECT
	b.name,
	BO.*
from
	beauty b
LEFT JOIN boys BO on b.boyfriend_id=BO.id
WHERE 
	BO.id is NULL;

#案例1：查询那个部门没有员工
USE myemployees;

SELECT	
	d.*,
	e.employee_id
FROM
	departments d LEFT JOIN employees e on d.department_id=e.department_id
where  
	e.employee_id is null;

全外连接

use girls;
SELECT 
	b.*,
	bo.*
FROM
	beauty b
full OUTER JOIN boys bo
ON b.boyfriend_id=bo.id;

交叉连接(笛卡尔乘积)

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

在这里插入图片描述

#------------------------练习-----------------------------
#1.查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有用NULL填充
USE girls;

SELECT
	b.id,
	b.name,
	BO.*
from
	beauty b
LEFT JOIN boys BO on b.boyfriend_id=BO.id
WHERE 
	B.id>3;
	
#2.查询哪个城市没有部门
use myemployees;

SELECT
	city,
	d.*
FROM
	departments d RIGHT JOIN locations l
ON d.location_id = l.location_id
WHERE
	d.department_id is NULL;

#3.查询部门名为SAL或IT的员工信息
SELECT
	d.department_name,
	e.*
from	
	departments d LEFT JOIN employees e 
ON d.department_id = e.department_id
WHERE 
	d.department_name in ('SAL','IT');

#---------------------------------------------------------