20、MySQL基础之连接查询

sql99语法----推荐使用

select 查询列表
from 表1 别名 
连接类型 join 表2 别名 
on 连接条件
where 筛选条件
group by 分组
having 筛选条件
order by 排序列表
limit 子句

1、交叉（笛卡尔）连接

-- 语法
select 查询列表
from 表1 cross join 表2
-- 关键字cross jion 可以省略，若省略，不同表之间以逗号隔开。
-- 交叉连接产生的结果是笛卡尔积，没有实际应用。
-- 若表1有m行，表2有n行，那么交叉连接的结果表有m*n行
-- 连接表在实际的数据库表中不存在，它存在于查询的执行当中。

示例

FROM girls.boys,girls.beauty;
-- 或
SELECT *
FROM girls.boys CROSS join girls.beauty;

2、内连接

select 查询列表
from 表1 别名
[inner] join 表2 别名
on 连接条件
where 筛选条件
-- n表连接至少需要n-1个连接条件
-- 只有存在连接条件时才可以连接
-- 内连接的结果是多表的交集部分

等值连接

案例1：查询员工姓名、部门名

SELECT CONCAT(e.first_name,e.last_name),department_name
FROM employees e 
INNER JOIN departments d
on e.department_id = d.department_id;
-- 或
SELECT CONCAT(e.first_name,e.last_name),department_name
FROM employees e 
JOIN departments d
on e.department_id = d.department_id;

案例2：查询姓名中包含e的员工名和工种名

SELECT CONCAT(e.first_name,e.last_name),j.job_title
FROM employees e 
JOIN jobs j
on e.job_id = j.job_id;
WHERE CONCAT(e.first_name,e.last_name) LIKE '%e%'

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

SELECT d.department_name,count(*)
FROM employees e
INNER JOIN departments d
on e.department_id = d.department_id
GROUP BY d.department_name
HAVING COUNT(*)>3
ORDER BY COUNT(*);

案例4：查询员工名、部门名、工种名，并按部门名降序

SELECT e.last_name,d.department_name,j.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;

非等值连接

数据源

/*
 Navicat Premium Data Transfer

 Source Server         : mysql5
 Source Server Type    : MySQL
 Source Server Version : 50727
 Source Host           : localhost:3306
 Source Schema         : myemployees

 Target Server Type    : MySQL
 Target Server Version : 50727
 File Encoding         : 65001

 Date: 26/03/2020 20:20:34
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for job_grades
-- ----------------------------
DROP TABLE IF EXISTS `job_grades`;
CREATE TABLE `job_grades`  (
  `grade_level` varchar(3) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
  `lowest_sal` int(11) NULL DEFAULT NULL,
  `highest_sal` int(11) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = gb2312 COLLATE = gb2312_chinese_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of job_grades
-- ----------------------------
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);
SET FOREIGN_KEY_CHECKS = 1;

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

SELECT salary,j.grade_level
FROM employees e
JOIN job_grades j
on e.salary BETWEEN j.lowest_sal AND j.highest_sal;

案例2：查询每个工资级别的个数大于20的工资级别以及个数，并且按工资级别降序

SELECT j.grade_level,COUNT(*)
FROM employees e
JOIN job_grades j
on e.salary BETWEEN j.lowest_sal AND j.highest_sal
GROUP BY j.grade_level
HAVING COUNT(*) > 20
ORDER BY  j.grade_level DESC

自连接

案例2：查询员工的名字、上级的名字

SELECT t1.last_name 领导名,t2.last_name 员工名
FROM employees t1
JOIN employees t2 
on t1.employee_id=t2.manager_id;

3、外连接

-- 语法
select 查询列表
from 表1 别名
left|right|full join 表2 别名
on 连接条件
where 筛选条件
group by 分组列表
having 分组筛选
order by 排序列表
limit 子句;
-- 应用场景：用于查询一个表中有，另一个表没有的记录
-- 特点：包含主表的全部记录，从表和主表匹配上，则显示从表记录，否则以全null记录填充从表 
-- 左外连接：left join左边的是主表，右边的是从表【常用】
-- 右外连接：right join右边的是主表，左边的是从表
-- 全外连接（mysql不支持）：full join 两边的都是主表

案例1：查询那个部门没有员工

SELECT d.*,e.department_id
FROM departments d 
LEFT JOIN employees e
on d.department_id = e.department_id
WHERE e.department_id is NULL;