关于sql92和sql99

最大的区别就是
sql92只支持内连接
——from t1.id=t2.id
sql99支持内连接，外连接，使用的范围更广，能满足更多的要求
——join 表一 on 表2
——left join
——right join
。。。。。

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

笛卡尔乘积现象：表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;

32
二、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