【page】mysql笔记 （八） sql99语法--连接查询

sql99语法

语法：

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

关键字：

内连接：【inner】 join
外连接:
　 左外:left join
　 右外:right join
　 全外:full join
交叉连接:cross join

一、内连接

语法：

select 查询列表
from 表1 别名
【inner】 join 表2 别名
on 连接条件;

分类：

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

特点：

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

1.等值连接

案例1：查询员工名，部门名

SELECT
	last_name,department_name
FROM
	employees AS e
JOIN
	departments AS d
ON
	e.`department_id`=d.`department_id`;
	#两张表可以调换顺序

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

SELECT
	last_name,job_title
FROM
	employees AS e
INNER JOIN
	jobs AS j
ON
	e.`job_id`=j.`job_id`
WHERE
	e.`last_name` LIKE '%e%';

案例3：查询部门个数>3的城市名和部门个数

SELECT
	COUNT(*) AS 部门个数,city
FROM
	locations AS l
INNER JOIN
	departments AS d
ON
	l.`location_id`=d.`location_id`
GROUP BY
	city
HAVING
	部门个数>3;

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

SELECT
	department_name AS 部门名,COUNT(*) AS 员工个数
FROM
	employees AS e
INNER JOIN
	departments AS d
ON
	e.`department_id`=d.`department_id`
GROUP BY
	department_name
HAVING
	员工个数>3
ORDER BY
	员工个数 DESC;

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

#多表连接(表的数量>=3)
SELECT
	last_name AS 员工名,job_title AS 工种名,department_name AS 部门名
FROM
	employees AS e
INNER JOIN
	jobs  AS j
ON
	e.`job_id`=j.`job_id`
INNER JOIN
	departments AS d
ON
	e.`department_id`=d.`department_id`
ORDER BY
	部门名 DESC;

2.非等值连接

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

SELECT
	salary,grade_level
FROM
	employees AS e
INNER JOIN
	job_grades AS j
ON
	e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;

案例2：查询每个工资级别>20的个数,并且进行按工资级别降序排序

SELECT
	grade_level,COUNT(*) AS 工资级别个数
FROM
	employees AS e
INNER JOIN
	job_grades AS j
ON
	e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`
GROUP BY
	grade_level
HAVING
	工资级别个数>20
ORDER BY
	工资级别个数 DESC;

3.自连接

案例1：查询员工的名字和上级的名字

SELECT
	e.last_name AS 员工名,m.last_name AS 上级名
FROM
	employees AS e
JOIN
	employees AS m
ON 
	e.`manager_id`=m.`employee_id`;

二、外连接

特点：

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

1.左外连接

返回指定左表的所有行，如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择的列均为空值(null)

SELECT 
	* 
FROM 
	table1#主表
LEFT JOIN 
	table2 
ON 
	table1.id=table2.id;

2.右外连接

返回指定右表的所有行。如果右表的某行在左表中没有匹配行，则在相关联的结果集行中左表的所有选择的列均为空值(null)

SELECT 
	* 
FROM 
	table1
RIGHT JOIN 
	table2#主表
ON 
	table1.id=table2.id;

3.全外连接

返回左表和右表中的所有行。
当某行在另一个表中没有匹配行时，则另一个表的所有选择的列均为空值。
如果表之间有匹配行，则整个结果集行包含基表的数据值

SELECT 
	* 
FROM 
	table1
FULL JOIN 
	table2
ON 
	table1.id=table2.id;

三、交叉连接

产生联接所涉及的表的笛卡尔积

SELECT 
	* 
FROM 
	table1
CROSS JOIN 
	table2
ON 
	table1.id=table2.id;

四、sql92 VS sql99

1.功能：sql99支持的较多
2.可读性：sql99实现连接条件和筛选条件的分离，可读性较高