mysql等值连接语法_mysql sql99语法 内连接等值连接

#案例：查询员工名、部门名

SELECT

last_name,department_name

FROM

employees e

INNER JOIN

departments d

ON

e.`department_id`=d.`department_id`

#案例：查询名字中包含e的员工名和工种名(筛选)

SELECT

last_name,job_title

FROM

employees e

INNER JOIN

jobs s

ON

e.`job_id`=s.job_id

WHERE

e.`last_name` LIKE ‘%a%‘ ;

#案例：查询部门个数大于3的城市名和部门个数

SELECT

city,COUNT(*)

FROM

departments d

INNER JOIN

locations l

ON

d.`location_id`=l.`location_id`

GROUP BY

city

HAVING

COUNT(*)>3;

#查询：员工名、部门名、工种名，并按部门名排序(三表连接)

SELECT

last_name,department_name,job_title

FROM

employees e

INNER JOIN

departments d #内连接的表得和from的表有连接条件

ON

e.`department_id`=d.department_id

INNER JOIN

jobs j

ON

j.job_id=e.job_id

ORDER BY

department_name DESC;

#案例：查询哪个部门的员工个数>3的部门名和员工个数，并按个数排序(添加排序)

SELECT

COUNT(*),department_name

FROM

departments d

INNER JOIN

employees e

ON

d.`department_id`=e.`department_id`

GROUP BY

department_name

HAVING

COUNT(*)>3

ORDER BY

COUNT(*) DESC;