mysql 等值连接案例之添加筛选、分组、排序、多表

加筛选

#案例:有奖金的员工名和部门名

SELECT     last_name,department_nameFROM    employees AS e,departments AS dWHERE    e.department_id=d.department_idAND    e.commission_pct IS NOT NULL;

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

SELECT    department_name,cityFROM    departments AS d,locations AS lWHERE    d.location_id=l.location_idAND    l.city LIKE'_o%';

加分组

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

SELECT    COUNT(*),cityFROM    departments AS d,locations AS lWHERE    d.`location_id`=l.`location_id`GROUP BY    city;

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

SELECT    department_name,d.manager_id,MIN(salary)FROM    employees AS e,departments AS dWHERE    d.department_id=e.department_idAND    commission_pct IS NOT NULLGROUP BY    department_name,d.manager_id;

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

SELECT    job_title,COUNT(*)FROM    employees AS e,jobs AS jWHERE    e.job_id=j.job_idGROUP BY    job_titleORDER BY    COUNT(*) DESC;

三表连接
#案例：查询员工名 、部门名、所在的城市

SELECT    last_name,department_name,cityFROM    employees AS e,departments AS d,locations AS lWHERE    e.department_id=d.`department_id`AND    d.`location_id`=l.location_id