Mysql学习三 连接查询

连接查询

/*
含义：又称为多表查询，当查询的字段来自多个表时，用到该表

分类：
按功能分类：
内连接
等值连接
非等值连接
自连接
外连接
左外连接
右外连接
全外连接(Mysql不支持)
交叉连接

*/

一、sql92语法

等值连接

#查询女生和男生的对应

SELECT NAME,boyName
FROM beauty,boys
WHERE beauty.boyfriend_id=boys.id;

#查询员工名和对应的部门名

USE myemployees;
SELECT 	last_name,department_name
FROM 	employees,departments
WHERE	employees.`department_id`=departments.`department_id`;

#为表起别名
/*
如果为表起了别名，则查询的字段就不能使用原来的表名去限定
*/
#查询员工名、工种号、工种名

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

#加筛选
#查询有奖金的员工名，部门名

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

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

SELECT 	department_name,city
FROM 	departments,locations
WHERE 	departments.`location_id`=locations.`location_id` AND city LIKE '_o%';#这里的like不能换为=号，

#加分组
#查询每个城市的部门个数

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_name,d.`manager_id`;

#可以加排序
#查询每个工种的工种名和员工的个数，并且按员工个数排序

SELECT 	job_title,COUNT(*) '个数'
FROM 	jobs,employees
WHERE 	jobs.`job_id`=employees.`job_id`
GROUP BY jobs.`job_id`
ORDER BY COUNT(*);

#实现三表连接
#查询员工名、部门名和所在的城市

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`
AND	city LIKE 's%'
ORDER BY department_name DESC;

非等值连接

#查询员工的工资和工资级别

SELECT 	salary,grade_level
FROM 	employees e,job_grades j
WHERE 	salary BETWEEN j.`lowest_sal` AND j.`highest_sal`
ORDER BY salary ASC ;

自连接

#查询员工名和上级的名称

SELECT  e.employee_id,e.last_name,m.`employee_id`,m.`last_name`
FROM 	employees e,employees m
WHERE	e.`manager_id`=m.`employee_id`;

二、sql99语法

/*
语法：

select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on   连接条件
where 筛选条件
group by 分组
having 筛选条件
order by 排序列表

分类 连接类型
内连接 inner
外连接 左外 left【outer】
右外 right【outer】
全外 full【outer】
交叉连接 cross

*/

内连接

/*
语法

select	查询列表
from	表1 别名
inner join 表2 别名
on 	连接条件

*/

#查询名字中包含e的员工名和工种名

SELECT	last_name,job_title
FROM 	employees e
INNER JOIN jobs j
ON 	e.`job_id`=j.`job_id` #`为着重号
WHERE	last_name LIKE '%e%';

#查询部门个数>3的城市名和部门个数

SELECT	city,COUNT(*)
FROM 	locations l
INNER JOIN departments d
ON	l.`location_id`=d.`location_id`
GROUP BY l.`city`
HAVING  COUNT(*)>3;

#查询员工名、部门名、工种名，并按部门名降序

SELECT last_name,department_name,job_title
FROM 	employees e
INNER JOIN departments d ON e.`department_id`=d.`department_id`
INNER JOIN jobs j ON e.`job_id`=j.`job_id`
ORDER BY department_name DESC;

非等值连接

#查询员工的工资级别

SELECT employee_id,salary,grade_level
FROM 	employees e
INNER JOIN job_grades j
ON	e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;

外连接

/*
用于查询一个表中有，另一个表没有的记录
特点：
1、外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示null
外连接查询结果=内连接结果+主表中有而从表中没有的记录
2、左外，left左边的是主表
右外，right右边的是主表
全外连接=内连接的结果+表1中有但表2中没有的+表2中有表1中没的（Mysql不支持）
*/

SELECT b.`name`,bo.*
FROM 	beauty b
LEFT OUTER JOIN boys bo
ON b.`boyfriend_id`=bo.`id`
WHERE bo.`id` IS NULL;

左外

#查询哪个部门没有员工

SELECT 	department_name
FROM 	departments d
LEFT OUTER JOIN employees e
ON d.`department_id`=e.`department_id`
WHERE  e.`employee_id` IS NULL;

交叉连接（笛卡尔乘积）

SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;