mySQL连接查询(多表查询)---等值连接（基于92版）

 

举例：

表单信息：

练习1、等值连接：查询员工名和部门名称（分别在不同的表单中，表单的共同点为deparments_id）

SELECT 
	e.last_name,d.department_name
FROM
	`employees`e,`departments` d #给不同的表格取好别名，然后可以在前后都被使用
WHERE	d.`department_id` = e.`department_id`;

结果：

#查询所有有奖金的员工名字和所在的部门,并按照奖金率高低排序

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

输出：

#查询各个城市的部门个数，列出城市名

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

结果：

#查询有奖金的每个部门的部门名和部门的领导编号，以及该部门的最低工资

SELECT d.department_name 部门名, d.manager_id 领导编号, MIN(salary) 部门平均工资
FROM
	departments d, employees e
WHERE 
	d.`department_id` = e.`department_id`
	AND
	e.commission_pct IS NOT NULL
GROUP BY
	d.`department_id`;#d.`department_name`

#查询每个工种胡工种名和员工个数,并且按照员工个数进行降序排序

SELECT j.job_title 工种名, COUNT(*) 员工个数
FROM 
	`jobs` j, `employees` e
WHERE 
	j.job_id = e.job_id
GROUP BY 
	j.job_title#j.job_id
ORDER BY 员工个数 DESC;

输出结果:

#查询员工名,部门名和所在城市名

SELECT e.last_name 员工名, d.department_name 所在部门名, l.city 所在城市
FROM 
	employees e, departments d, locations l
WHERE 
	e.`department_id` = d.`department_id`
AND 
	d.`location_id` = l.`location_id`;

输出结果：