MySQL sql99语法—等值连接

等值连接

特点：

1. 可以添加排序、分组、筛选
2. inner 可以省略
3. 筛选条件放在where后，连接条件放在on后
4. 和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的城市名和部门个数(添加分组和筛选）

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

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

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

• 查询员工名、部门名、工种名，并按部门名降序（多表连接）

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;