mysql连接查询

连接查询

一、含义
当查询中涉及到了多个表的字段，需要使用多表连接
select 字段1，字段2
from 表1，表2,…;
笛卡尔乘积：当查询多个表时，没有添加有效的连接条件，导致多个表所有行实现完全连接
如何解决：添加有效的连接条件

按年代分类：
sql92：
等值
非等值
自连接

	也支持一部分外连接（用于oracle、sqlserver，****mysql不支持****）
sql99【推荐使用】
	内连接
		等值
		非等值
		自连接
	外连接
		左外
		右外
		全外（mysql不支持）
	交叉连接
	三、SQL92语法

1、等值连接
语法：
select 查询列表
from 表1 别名,表2 别名
where 表1.key=表2.key
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】

特点：
① 一般为表起别名
②多表的顺序可以调换
③n表连接至少需要n-1个连接条件
④等值连接的结果是多表的交集部分
2、非等值连接
语法：
select 查询列表
from 表1 别名,表2 别名
where 非等值的连接条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
3、自连接
语法：
select 查询列表
from 表 别名1,表 别名2
where 等值的连接条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】

1.等值连接

#1.sq192标准
#1.等值连接
1.查询女神名对应的男神名
SELECT NAME,boyname
FROM boys,beauty
WHERE beauty.boyfriend_id=boys.id;
#案例2.查询员工名和对应的部门名
SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id=departments.department_id;
#2.为表起别名
/*1.提高语句的简洁度
2.区分多个重名的字段
注意：如果为表起了别名，则查询的字段就不能使用原来的表明去限定
*/
#2.查询员工名，工种名，工种名
SELECT e.last_name,e.job_id,j.job_title
FROM employees e,jobs j
WHERE e.job_id=j.job_id;
#3.两个表的顺序可以交换
#4.可以加筛选
SELECT last_name,department_name,commission_pct
FROM employees e,departments d
WHERE e.department_id=d.department_id
AND e.commission_pct IS NOT NULL;

#案例2 查询城市名中第二个字符为o的部门名和城市名
SELECT department_name,city
FROM departments d,locations l
WHERE d.location_id=l.location_id
AND city LIKE '_o%';
5.可以加分组
#查询每个城市的部门个数
SELECT COUNT(*) 个数,city,department_id
FROM departments d,locations l
WHERE d.location_id=l.location_id
GROUP BY city;
#2.查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT department_name,d.manager_id,MIN(salary),commission_pct
FROM departments d,employees e
WHERE d.`department_id`=e.`department_id`;
#and commission_pct is not null;
GROUP BY department_name,d.manager_id;
#6.查询每个工种的工种名和员工的个数，并且按照员工个数降序
SELECT job_title,COUNT(*)
FROM jobs j,employees e
#7、可以实现三表连接?
#案例:查询员工名、部门名和所在的城市
SELECT last_name, department_name, city
FROM employees e, departments d, locations l
WHERE e.department_id=d.department_id
AND d.location id=1.location id;

2.非等值连接

#1.查询员工的工资和工资级别
SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`
HAVING g.grade_level='A';

3.自连接：

自己和自己进行连接
把自己当成两个表或者多个表

#查询员工名和主管名

#自然连接
SELECT e.employee_id,e.last_name,e.manager_id,m.employee_id,m.last_name,m.manager_id
FROM employees e,employees m
WHERE e.manager_id=m.employee_id;

练习题

练习题

#4.选择citv在Toronto工作的员工的
SELECT last_name,job_id,d.department_id,department_name
FROM employees e,departments d,locations l
WHERE d.`location_id`=l.`location_id`
AND e.`department_id`=d.`department_id`
AND city='Toronto';
#5.查询每个工种、每个部门的部门名、工种名和最低工资
SELECT department_name,job_title,MIN(salary)
FROM employees e,jobs j, departments d
WHERE e.`job_id`=j.`job_id`
AND e.`department_id`=d.`department_id`
GROUP BY job_title; #department_name;
#6.查询每个国家下的部门数大于2的国家编号
SELECT country_id, l.location_id ,COUNT(*) 部门个数
FROM locations l,departments d
WHERE l.`location_id`=d.`location_id`
GROUP BY country_id
HAVING 部门个数>2;
7、选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，
结果类似于下面的格式
SELECT e.last_name employees,e.employee_id "EMP#",m.last_name manager,m.employee_id  "Mgr#"
FROM employees e,employees m
WHERE e.manager_id=m.employee_id;