MySql（十一）Sql99之连接查询

Sql99

支持：

1. 内连接（inner）

2. 外连接：左外(left[outer])、右外(right[outer]),全外（full[outer]）mysql中不支持全外

3. 交叉连接(cross[outer])

语法：

​ SELECT 查询列表

​ FROM 表1 别名

​ [连接类型]JOIN 表2 别名

​ ON 连接条件

​ WHERE 筛选条件

​ GROUP BY 分组

​ HAVING 筛选条件

​ ORDER BY 排序里列表

内连接

语法：

​ SELECT 查询列表

​ FROM 表1 别名

​ INNER JOIN 表2 别名

​ ON 连接条件

一. 内连接之等值连接

语法：

​ SELECT 查询列表

​ FROM 表1 别名

​ INNER JOIN 表2 别名

​ ON 连接条件

/**案例1：查询员工名、部门名**/
			SELECT last_name,department_name
			FROM employees e
			INNER JOIN departments d
			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 last_name LIKE ‘%e%’;
/**案例3：查询部门个数>3的城市名和部门个数（分组+筛选） 
			(先查询每个城市的部门个数-筛选满足条件的部门)**/
SELECT city,count(*) 部门个数
			FROM locations l
			INNER JOIN departments d
			ON l.’location_id’ = d.’location_id’
			GROUP BY city
			HAVING 部门个数>3;
/**案例4：查询哪个部门的部门员工个数>3的部门名称和员工个数，并按照个数降序排序**/
			SELECT department_name,count(*) 员工个数
			FROM employees e
			INNER JOIN departments d
			ON e.’department_id’ = d.’department_id’
			GROUP BY department_name
			HAVING 员工个数>3
			ORDER BY 员工个数 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;

Sql99之等值连接特点：

1. 可以添加筛选、分组、筛选

2. Inner可以省略

3. 筛选条件放在where后面，连接条件放在on后面

4. Inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集

二、内连接之非等值连接

/**案例1：查询员工的工资级别**/
			SELECT salary,grade_level
			FROM employees e
			JOIN job_grade jg
			ON e.’salary’ BETWEEN jg.’lowest_	sal’ AND jg.’highest_sal’;			
/**案例2：查询每个工资级别的个数，并且按照个数的降序排序**/
			SELECT grade_level,count(*) 个数
			FROM employees e
			JOIN job_grade jg
			ON e.’salary’ BETWEEN jg.’lowest_	sal’ AND jg.’highest_sal’;		
			ORDER BY 个数 DESC;	

三、内连接之自连接

/**案例1：查询员工的名字、上级的名字**/
		SELECT e.last_name,m.last_name
		FROM employees e
		JOIN employee m
		ON e.’manager_id’ = m.’employee_id’;
/**案例1：查询员工的名字包含k、上级的名字**/
		SELECT e.last_name,m.last_name
		FROM employees e
		JOIN employee m
		ON e.’manager_id’ = m.’employee_id’
		WHERE e.last_name LIKE ‘%K%’;

外连接

应用场景：用于查询一个表中有，另一个表中没有的数据

特点：

1. 查询结果为主表中的所有记录；如果从表中有何它匹配的数据，则显示匹配的数据，如果从表中没有相匹配的则显示为null

2. 外连接的查询结果 = 内连接结果 + 主表中有而从表中没有的数据

主表和从表的区分：

1. 左外连接：left join左边的是主表

2. 右外连接：right join右边的是主表

/**案例1：查询男朋友不在男神表的女神名(使用左外连接)**/
	SELECT bea.name
	FROM beauty bea
	LEFT OUTER JOIN boys b
	ON bea.’boyfriend_id’ = b.’id’
	WHERE b.’id’ IS NULL;
/**案例2：查询男朋友不在男神表的女神名(使用右外连接)**/
	SELECT bea.name
	FROM boys b
	RIGHT OUTER JOIN beauty bea
	ON b.’id’ = bea.’boyfriend_id’
	WHERE b.’id’ IS NULL;
/**案例3：查询哪个部门没有员工
	(首先考虑哪个是主表：由于查询的是部门信息，因此departments为主表)**/
	SELECT d.*,e.employee_id
	FROM departments d
	LEFT OUTER JOIN employees e
	ON d.’department_id’ = e.’department_id’
	WHERE e.’employee_id’ IS NULL; 

全外连接

注意：sql中不支持全外连接

语法格式如下：

​ SELECT b.* ,bea.name

​ FROM boys b

​ FULL OUTER JOIN beauty bea

​ ON b.’id’ = bea.’boyfriend_id’

查询结果有三部分组成:

查询结果 = 内连接的结果 + 表1但表2中没有的+表2有单表1中没有的

交叉连接

定义：即使用sq l99语法的标准来实现笛卡尔乘积的效果

语法格式如下：

​ SELECT b.* ,bea.name

​ FROM boys b

​ CROSS JOIN beauty bea;