mysql sql99语法内连接等值连接

原创

wx5d21d5e6e5ab1 2019-09-13 23:00:49 ©著作权

文章标签 mysql 文章分类 MySQL 数据库

©著作权归作者所有：来自51CTO博客作者wx5d21d5e6e5ab1的原创作品，请联系作者获取转载授权，否则将追究法律责任

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

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;