MySQL等值连接的示例

两个表的顺序是否可以调换

#查询员工名、工种号、工种名

SELECT last_name, e.job_id, job_title
FROM jobs AS j, employees AS e
WHERE e.`job_id` = j.`job_id`;

可以加筛选？

#案例1：查询有奖金的员工名、部门名

SELECT last_name, department_name, commission_pct
FROM employees e, departments d
WHERE e.`department_id`=d.`department_id`
AND e.`commission_pct` IS NOT NULL;

#案例2：查询城市名中第二个字符为o的部门名和城市名

SELECT department_name, city
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
AND city LIKE '_o%';

可以加分组？

#案例1：查询每个城市的部门个数

SELECT COUNT(*) 个数, city
FROM departments d, locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY city;

#案例2：查询出有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资

SELECT department_name,d.manager_id,MIN(salary)
FROM departments d, employees e
WHERE d.`department_id`=e.`department_id`
AND commission_pct IS NOT NULL
GROUP BY department_name, d.manager_id;

可以加排序？

#案例：查询每个工种的工种名和员工的个数，并且按员工个数降序

SELECT job_title, COUNT(*)
FROM employees e , jobs j
WHERE e.`job_id`=j.`job_id`
GROUP BY job_title
ORDER BY COUNT(*) DESC;

 可以实现三表连接吗？

#案例：查询员工号、部门名和所在的城市

SELECT last_name,department_name,city
FROM employees e,departments d,locations l
WHERE e.`department_id`=d.`department_id`
AND d.`location_id`=l.`location_id`