MySQL 数据库之 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.等值连接

案例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 j.job_id=e.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 COUNT()>3;

案例 4. 查询哪个部门的员工个数 > 3 的部门名和员工个数，并按个数降序（添加排序）

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

案例 5. 查询员工名、部门名、工种名，并按部门名降序（添加三表连接）

SELECT
last_name,
job_title,
department_name
FROM
departments d
INNER JOIN employees e
ON d.department_id = e.department_id
INNER JOIN jobs j
ON j.job_id = e.job_id
ORDER BY d.department_name DESC ;

2.非等值连接

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

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

SELECT * FROM job_grades;

案例2：查询工资级别的个数>20的个数，并且按工资级别降序

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

3.自连接

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

SELECT
e1.last_name,e2.last_name
FROM
employees e1
JOIN employees e2
ON e1.manager_id=e2.employee_id;

案例2：查询姓名中包含字符k的员工的名字、上级的名字

SELECT
e1.last_name,e2.last_name
FROM
employees e1
JOIN employees e2
ON e1.manager_id=e2.employee_id
WHERE e1.last_name LIKE ‘%k%’;

二)外连接

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

特点：

1、外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示NULL
外连接查询结果=内连接结果+主表中有而从表没有的记录
2、左外连接，LEFT JOIN左边的是主表
右外连接，RIGHT JOIN右边的是主表
3、左外和右外交换两个表的顺序，可以实现同样的效果
4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的

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

左外

SELECT
d.*,employee_id
FROM
departments d
LEFT OUTER JOIN employees e
ON e.department_id=d.department_id
WHERE employee_id IS NULL;

右外

SELECT d.*,employee_id
FROM employees e
RIGHT OUTER JOIN departments d
ON e.department_id=d.department_id
WHERE e.employee_id IS NULL ;

全外

MySQL不支持

交叉连接

SELECT 字段1，字段2
FROM 表名1
CROSS JOIN 表名2

sql92和 sql99pk

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

练习题

1.查询那个城市没有部门

SELECT
city,department_id
FROM
locations l
LEFT OUTER JOIN departments d
ON d.location_id=l.location_id
WHERE d.department_id IS NULL;

2.查询部门名为SAL或IT的员工信息

SELECT
department_name,e.*
FROM
departments d
LEFT OUTER JOIN employees e
ON e.department_id=d.department_id
WHERE department_nameIN(‘SAL’,‘IT’);