mysql left join 交集

转载

mob64ca14048514 2024-09-17 22:26:21

文章标签 mysql left join 交集 ci 字段等值连接 文章分类 MySQL 数据库

进阶6：连接查询

含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件
按年代分类：

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

按功能分类：

内连接：

等值连接
非等值连接
自连接

外连接：

左外连接
右外连接
全外连接

交叉连接

一、sql92标准

1、等值连接

语法：

SELECT 查询列表
 FROM 表1 AS 别名,表2 AS 别名
 WHERE 表1.key=表2.key
【AND 筛选条件】
【GROUP BY分组字段】
【HAVING 分组后的筛选】
【ORDER BY 排序字段】

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

#案例1：查询女神名和对应的男神名

SELECT NAME,boyName 
FROM boys,beauty
WHERE beauty.boyfriend_id= boys.id;

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

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

(1)、为表起别名
①提高语句的简洁度
②区分多个重名的字段
注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定
#案例：查询员工名、工种号、工种名

SELECT `last_name`,e.`job_id`,`job_title`
FROM `employees` AS e,`jobs` AS j
WHERE e.`job_id`=j.`job_id`;

(2)、两个表的顺序是否可以调换
#案例：查询员工名、工种号、工种名

SELECT e.last_name,e.job_id,j.job_title
FROM jobs j,employees e
WHERE e.`job_id`=j.`job_id`;

(3)、可以加筛选
#案例1：查询有奖金的员工名、部门名

SELECT e.`last_name`,d.`department_name`,`commission_pct`
FROM `employees` e,`departments` d
WHERE d.`department_id`=e.`department_id` AND 
    	e.`commission_pct` IS NOT NULL;

#案例2：查询城市名中第二个字符为o的部门名和城市名

SELECT `department_name`,`city`
FROM `departments` d,`locations` l
WHERE d.`location_id`=l.`location_id`
	AND l.`city` LIKE "_o%";

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

SELECT COUNT(*) AS 个数,`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(`salary`)
FROM `departments` d,`employees` e
WHERE d.`department_id`=e.`department_id`
	AND e.`commission_pct` IS NOT NULL
GROUP BY `department_name`,d.`manager_id`;

(5)、可以加排序
#案例：查询每个工种的工种名和员工的个数，并且按员工个数降序

SELECT `job_title`,COUNT(*) 员工个数
FROM `employees` e,`jobs` j
WHERE e.`job_id`=j.`job_id`
GROUP BY j.`job_title`
ORDER BY COUNT(*) DESC;

(6)、可以实现三表连接
#案例：查询员工名、部门名和所在的城市

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` AND city LIKE 'T%'
ORDER BY `department_name` DESC;

2、非等值连接

语法：

SELECT 查询列表
 FROM 表1 AS 别名,表2 AS 别名
 WHERE 非等值的连接条件

#案例1：查询员工的工资和工资级别

SELECT `salary`,`grade_level`
FROM `employees` e,`job_grades` g
WHERE `salary` BETWEEN `lowest_sal` AND `highest_sal`;
#用`salary`去对应

3、自连接

语法：

SELECT 查询列表
 FROM 表1 AS 别名1,表1 AS 别名2
 WHERE 条件

把原表当做两张表甚至更多的表来使用，看的角度不同

#案例：查询员工名和上级的名称
(1)

SELECT `employee_id`,`last_name`,`manager_id`
FROM `employees`;

#结果前几行

employee_id	last_name	manager_id
100	King	(NULL)
101	Kochhar	100
102	De lHaan	100
103	Hunold	102
104	ErnSt	103

#解释
先看第二行，员工编号为101，对应的领导编号为100
在通过领导编号100，有对应员工编号第一行的100
因此Kochhar的领导为king
(2)

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`;

#结果前几行

employee_id	last_name	employee_id	last_name
101	Kochhar	100	K_ing
102	De Haan	100	K_ing
103	Hunold	102	De Haan

测试

1.显示员工表的最大工资，工资平均值

SELECT MAX(`salary`) 最大工资,AVG(`salary`) 平均工资
FROM `employees`;

2.查询员工表的employee_id，job_id，last_name，按department_id降序，salary升序

SELECT `employee_id`,`last_name`,`job_id`
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.已知表student，里面有id（学号），NAME，gradeId（年级编号）
已知表grade，里面有id（年级编号），NAME（年级名）
已知表result，里面有id，score，studentNo（学号）
要求查询姓名、年级名、成绩

SELECT  s.name,g.name,r.score
FROM student s,grade g,result r
WHERE s.id=r.studentNO AND s.gradeID=g.id;

5.显示当前日期，以及去前后空格，截取子字符串的函数

SELECT NOW()
SELECT TRIM("  aa  ")
SELECT SUBSTR("aaaa",2,4)

6.显示所有员工的姓名，部门号和部门名称

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

7.查询90号部门员工的job_id和90号部门的location_id

SELECT e.`job_id`,d.`location_id`
FROM `employees` e,`departments` d
WHERE d.`department_id`=e.`department_id` AND
      e.`department_id`=90;

8.选择所有有奖金的员工的last_name，department_name，location_id，city

SELECT e.`last_name`,d.`department_name`,l.`location_id`,l.`city`
FROM `departments` d,`employees` e,`locations` l
WHERE d.`department_id`=e.`department_id` AND d.`location_id`=l.`location_id`
	AND e.`commission_pct`IS NOT NULL;

9.选择city在Toronto工作的员工的last_name，job_id，department_id，department_name

SELECT e.`last_name`,e.`department_id`,d.`department_name`
FROM `departments` d,`employees` e,`locations` l
WHERE d.`department_id`=e.`department_id` AND d.`location_id`=l.`location_id`
	AND l.`city`="Toronto";

10.查询每个工种、每个部门的部门名、工种名和最低工资

SELECT d.`department_name`,j.`job_title`,j.`min_salary`
FROM `departments` d,`jobs` j,`employees` e
WHERE d.`department_id`=e.`department_id` AND e.`job_id`=j.`job_id`
GROUP BY d.`department_name`,j.`job_title`;

11.查询每个国家下的部门个数大于2的国家编号

SELECT `country_id`,COUNT(*)
FROM `locations` l,`departments` d
WHERE d.`location_id`=l.`location_id`
GROUP BY l.`country_id`
HAVING COUNT(*)>2;
#想分组前还是分组后

12.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
employees Emp manager Mgr
kochhar 101 king 100

#自连接
SELECT e.`last_name`,e.`employee_id` AS Emp,m.`last_name` AS manager,m.`employee_id` AS Mgr
FROM `employees` e,`employees` m
WHERE e.`employee_id`=m.`employee_id`;