700字范文 > MySQL学习笔记之连接查询(SQL99标准) inner left outer right outer full outer cross等

MySQL学习笔记之连接查询(SQL99标准) inner left outer right outer full outer cross等

时间：2018-09-30 06:24:39

前言：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连接和sql 92语法中的等值连接效果是一样的，都是查询多表的交集

1.等值连接

案例1.查询员工名、部门名

SELECT last_name, department_nameFROM employees eINNER JOIN departments dON e.`department_id` = d.`department_id`;

调换一下顺序后效果与案例1等价

SELECT last_name, department_nameFROM departments dINNER JOIN employees eON e.`department_id` = d.`department_id`;

案例2.查询名字中包含有e的员工名和工种名(添加筛选)

SELECT last_name, job_titleFROM employees eINNER JOIN jobs j ON e.`job_id` = j.`job_id`WHERE e.`last_name` LIKE '%e%';

案例3.查询部门个数大于3的城市名和部门个数(添加筛选和分组)

SELECT city, COUNT(*)FROM departments dINNER JOIN locations lON d.`location_id` = l.`location_id`GROUP BY cityHAVING COUNT(*)>3;

案例4.查询员工的个数大于3的部门的名字和员工个数，并按照个数排序

SELECT department_name, COUNT(*) AS 员工人数FROM employees eINNER JOIN departments dON e.`department_id` = d.`department_id`GROUP BY d.`department_name`HAVING COUNT(*) > 3ORDER BY COUNT(*) DESC;

案例5.查询员工名、部门名、工种名，并按照部门名降序

SELECT last_name, department_name, job_titleFROM employees eINNER JOIN departments dON e.`department_id` = d.`department_id`INNER JOIN jobs jON e.`job_id` = j.`job_id`ORDER BY d.department_name DESC;

2.非等值连接

案例6.查询员工的工资级别

SELECT COUNT(*),grade_levelFROM employees eINNER JOIN job_grades j ON e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`GROUP BY grade_levelHAVINGCOUNT(*) > 20ORDER BY grade_level DESC;

3.自连接

案例7.查询姓名中包含字符k的员工的名字、上级的名字

SELECT e.last_name, m.last_nameFROM employees eINNER JOIN employees mON e.`manager_id` = m.`employee_id`WHERE e.`last_name` LIKE '%k%';

二、外连接

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

左外连接和右外连接

案例8. 查询男友不在男生表的女生名

SELECT NAMEFROM beauty gLEFT OUTER JOIN boys boON g.`boyfriend_id` = bo.`id` #匹配过程中，如果匹配不到，那么就设置为nullWHERE bo.`id` IS NULL;#筛选条件最好用主键，因为主键在原始从表中必然不为空#如果有null那一定是匹配不到产生的结果

使用右外连接实现上述案例8的要求

SELECT NAMEFROM boys boRIGHT OUTER JOIN beauty gON bo.`id` = g.`boyfriend_id`WHERE bo.`id` IS NULL;

案例9 查询哪个部门没有员工(左外连接)

SELECT d.department_id,department_name,employee_idFROM departments dLEFT OUTER JOIN employees eON d.`department_id` = e.`department_id`WHERE e.`employee_id` IS NULL;

右外连接实现上述案例9的要求

SELECT d.department_id, department_name, employee_idFROM employees eRIGHT OUTER JOIN departments dON d.`department_id` = e.`department_id`WHERE e.`employee_id` IS NULL;