mysql支持m1吗 mysql支持sql99吗

连接查询

含义：又称多表查询，当查询的字段来自多个表时，就需要用到连接查询

笛卡尔乘积现象：表1有m行，表2有n行，则结果有m*n行

分类：

• sql92标准：只支持内连接
• sql99标准【推荐】：MySQL支持内、外、交叉连接，但不支持全外连接
• 内连接：

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

• 外连接：

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

• 交叉连接

一、SQL92标准（内连接）

等值连接

• 可以为表起别名，as可以省略
• 为表起别名之后，则查询的字段就不能用原来的表名进行限定
• FROM中表的顺序可以调换
• 可以加筛选、分组、排序
• 多表等值连接的结果为多表的交集部分
• n个表进行连接需要n-1个连接条件

1、 查询员工名和对应的部门名，且要求部门名中包含字母a

SELECT e.last_name, department_name
FROM employees as e, departments d
WHERE e.department_id = d.department_id
AND department_name LIKE '%a%';

2、查询每个城市的部门个数

SELECT count(*), city
FROM departments d, locations l
WHERE d.location_id = l.location_id
GROUP BY l.location_id;

3、查询每个工种的工种名和员工个数，并且按照工种个数进行排序

SELECT job_title, count(*) '个数'
FROM employees e, jobs j
WHERE e.job_id=j.job_id
GROUP BY j.job_id
ORDER BY count(*)

非等值连接

1、查询员工的工资和工资等级

SELECT last_name, salary, grade_level
FROM employees e, job_grades j
WHERE e.salary BETWEEN j.lowest_sal and j.highest_sal;

自连接

1、查询员工名和他上级的名字

SELECT a.last_name, b.last_name
FROM employees a, employees b
WHERE a.manager_id = b.employee_id;

练习

#查询所有员工的姓名、部门号和部门名
SELECT last_name, e.department_id, department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

#查询90号部门员工的job_id和90号部门的location_id
SELECT job_id, location_id
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND e.department_id = 90;

#查询所有有奖金员工的last_name, department_name,location_id, city
SELECT last_name, department_name,l.location_id, city
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND e.commission_pct is not null;


#查询每个工种、每个部门的部门名、工种名和最低工资
SELECT department_name, job_title, min(salary)
FROM departments d, employees e, jobs j;
WHERE d.department_id = e.department_id
AND e.job_id = j.job_id
GROUP BY department_name,job_title;

#查询每个国家下面部门数大于2的国家编号
SELECT country_id, count(*)
FROM departments d, locations l
WHERE d.location_id = l.location_id
GROUP BY country_id
HAVING count(*) > 2;

二、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连接和sql92中的等值连接效果一样，都是查询交集

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 departments d
INNER JOIN locations l
ON d.location_id = l.location_id
GROUP BY city
HAVING count(*) > 3

4、查询部门员工个数大于3的部门名和员工个数，并按个数降序排列（添加排列）

SELECT department_name, count(*) 员工个数
FROM employees e
Inner JOIN departments d
ON e.department_id = d.department_id
GROUP BY e.department_id
HAVING  count(*) > 3
ORDER BY count(*) 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;

非等值连接

1、查询员工工资级别

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

2、查询每个工资级别的人数，并降序排序

SELECT grade_level, count(*)
FROM employees e
JOIN job_grades j 
ON e.salary BETWEEN j.lowest_sal AND j.highest_sal
GROUP BY grade_level
ORDER BY count(*) DESC;

自连接

1、查询员工名和其上级的名字

SELECT e.last_name, m.last_name
FROM employees e
JOIN employees m
ON e.manager_id = m.employee_id

（二）外连接

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

特点：

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

1、查询男朋友不在男神表中的女神名

#左外连接
SELECT g.name
FROM beauty g
LEFT JOIN boys b
ON g.boyfriend_id = b.id
WHERE b.id IS NULL;

#右外连接
SELECT g.name
FROM boys b
RIGHT JOIN beauty g
ON b.id = g.boyfriend_id
WHERE b.id IS NULL;

2、查询哪个部门没有员工

#左外连接
SELECT department_name
FROM departments d
LEFT OUTER JOIN employees e
ON d.department_id  = e.department_id
WHERE e.employee_id IS NULL;

#右外连接
SELECT department_name
FROM employees e
RIGHT OUTER JOIN departments d
ON e.department_id = d.department_id
WHERE e.employee_id IS NULL;

（三）交叉连接

• 结果为笛卡尔乘积：表1有4行，表2有11行，则结果有4*11=44行。
• 和sql92标准中不加连接条件结果一样

SELECT b.*,g.*
FROM boys b
CROSS JOIN beauty g;

总结

SQL99 VS SQL92:

• SQL99支持的功能更多
• SQL99实现了查询条件和连接条件的分离，可读性较高

练习

#查询编号>3的女神的男朋友信息，有则列出详细，没有则用null填充
SELECT g.*, b.*
FROM beauty g
LEFT OUTER JOIN boys b
ON g.boyfriend_id = b.id
WHERE g.id > 3;


#查询哪个城市没有部门
SELECT city, d.*
FROM locations l
LEFT OUTER JOIN departments d
ON d.location_id = l.location_id
WHERE d.department_id IS NULL;


#查询部门名为SAT或IT的员工信息
SELECT e.*, department_name
FROM departments d
LEFT OUTER JOIN employees e
ON e.department_id = d.department_id
WHERE department_name IN ('SAT', 'IT');