USE myemployees;#进阶6:连接查询/*
含义:又称多标查询,当查询的字段来自多个表
笛卡尔乘积现象:表1有m行,表2有n行,结果m*n行
发生原因:没有有效的连接条件
如何避免:添加有效的连接条件
分类:
按年代分类:
sql92标准:仅支持内连接
sql99标准:支持内外连接+交叉连接
按功能分类
内连接:
等值连接
非等值连接
自连接
外连接:
左外连接
右外连接
全外连接
交叉连接
*/#一、sql92标准#1、等值连接/*
1、多表等值连接的结果为多表的交集部分
2、n表开南街,至少需要n-1个连接条件
3、多表的顺序没有要求
4、一般需要为表起别名
5、可以搭配前面介绍的所有子句使用,比如排序,分组,筛选
*/#案例1:查询女神名和对应的男神名USE girls;SELECT NAME ,boyname
FROM boys,beauty
WHERE beauty.boyfriend_id =boys.id;#案例2:查询员工名和对应的部门名USE myemployees;SELECT last_name,department_name
FROM employees,departments
WHERE employees.`department_id`=departments.department_id;#2、为表起别名/*
提高语句简洁度
区分重名字段
注意:如果为表起了别名,则擦护心的字段就不能使用原来的表名去限定
*/#查询员工名、工种号、工种名SELECT last_name,employees.`job_id`,job_title
FROM employees,jobs
WHERE employees.`job_id`= jobs.`job_id`;#3、两个表的顺序是否可以调换SELECT last_name,employees.`job_id`,job_title
FROM employees,jobs
WHERE jobs.`job_id`=employees.`job_id`;#4、可以加筛选#案例:查询有奖金的员工名、部门名SELECT CONCAT(last_name,first_name),department_name,commission_pct
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`AND e.`commission_pct`ISNOTNULL;#案例2:查询城市名中第二个字符为o的部门名和城市名SELECT department_name,city
FROM departments d,locations l
WHERE d.`location_id`= l.`location_id`AND l.`city`LIKE'_o%';#5。可以加分组#案例1 :查询每个城市的部门个数SELECTCOUNT(*),city
FROM locations l, departments d
WHERE l.`location_id`=d.`location_id`GROUPBY city;#案例2:查询出有奖金的每个部门的部门名和部门领导的编号和该部门的最低工资SELECT department_name,d.manager_id,MIN(salary)FROM departments d,employees e
WHERE e.`commission_pct`ISNOTNULLAND d.`department_id`=e.`department_id`;GROUPBY department_name,d.manager_id;#6、可以加排序#案例:查询每个工种的工种名和员工个数,并且按员工个数降序SELECTCOUNT(*),j.job_title
FROM employees e, jobs j
WHERE e.`job_id`= j.`job_id`GROUPBY j.`job_title`ORDERBYCOUNT(*)DESC;#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`=l.`location_id`;#2、非等值连接#案例1:查询员工的工资和工资级别SELECT salary ,grade_level
FROM employees e, job_grades j
WHERE salary BETWEEN lowest_sal AND highest_sal;#3、自连接#案例:查询 员工名和上级名称SELECT e.employee_id ,e.last_name ,e.`employee_id`,e.`last_name`FROM employees e ,employees m
WHERE e.employee_id=m.manager_id ;#二、sql99语法/*
语法:
select 查询列表
from 表1 别名【连接类型】
join 表2 别名 on连接条件
where 筛选条件
group by
having
order by
分类:
内连接 :inner
外连接
左外:left
右外:right
全外:full
交叉连接;cross
*/#一)内连接/* select 查询列表
from 表1 别名inner
join 表2 别名 on连接条件
where 筛选条件
group by
having
order by
分类:
等值
非等值
自连接
*/#1、等值连接/*
特点
添加排序、分组、筛选
inner可以省略
筛选条件可以房子where后面,连接条件放在on后面
inner join 连接和sql92语法中的等值条件连接效果一样都是查询多表交集
*/#案例1:查询员工名、部门名SELECT last_name ,department_name
FROM employees e
INNERJOIN departments d
ON e.`department_id`=d.`department_id`;#案例2:查询名字中包含e的员工名和工种名(添加筛选)SELECT last_name,job_title
FROM employees e
INNERJOIN jobs j
ON e.`job_id`=j.`job_id`WHERE last_name LIKE'%e%';#案例 3:查询部门个数大于三的城市名和部门个数(添加分组+筛选)SELECTCOUNT(*),city
FROM departments d
INNERJOIN locations l
ON d.`location_id`=l.`location_id`GROUPBY city
HAVINGCOUNT(*)>3;#案例4:查询哪个部门的员工个数>3的部门名和员工个数,并按个数降序SELECT department_name,COUNT(*)FROM departments d
INNERJOIN employees e
ON d.`department_id`= e.`department_id`GROUPBY department_name
HAVINGCOUNT(*)>3ORDERBYCOUNT(*)DESC;#5、查询员工名、部门名、工种名、并按部门名降序SELECT
last_name,
department_name,
job_title
FROM
employees e
INNERJOIN departments d
ON e.`department_id`= d.`department_id`INNERJOIN jobs j
ON j.`job_id`= e.`job_id`ORDERBY department_name DESC;#二)非等值连接#查询员工的工资基本SELECT salary,grade_level
FROM employees e
JOIN job_grades g
ON e.`salary`BETWEEN g.`lowest_sal`AND g.`highest_sal`;#c查询每个工资级别的个数>20,并且按工资级别降序SELECTCOUNT(*),grade_level
FROM employees e
INNERJOIN job_grades j
ON e.`salary`BETWEEN j.`lowest_sal`AND j.`highest_sal`GROUPBY grade_level
HAVINGCOUNT(*)>20ORDERBY grade_level DESC;#三)自连接#查询员工的名字、上级的名字SELECT e.employee_id ,e.last_name,m.`manager_id`,m.`last_name`FROM employees e
INNERJOIN employees m
ON e.employee_id=m.manager_id
WHERE e.last_name LIKE('%k%');#二、外连接/*
应用场景查询一个人表中有,另一个表没有的记录
特点:
1、外连接的插叙结果为主表中的所有记录
如果从表中有和它匹配的,则显示匹配的值
若果从表中没有和它匹配的,则显示null
外连接查询结果=内连接结果+主表中有而从表没有的记录
2、左外连接,left join左边的是主表
右外连接,right join右边的是主表
3、左外右外交换两个表的顺序,可以实现同样的效果
4、全外连接 =内连接的结果+表1中有但表2没有的+表2中有但表一中没有的
*/# 案例1 :查询哪个部门没有员工#左外SELECT d.*, e.employee_id
FROM departments d
LEFTOUTERJOIN employees e
ON d.`department_id`= e.department_id
WHERE e.employee_id ISNULL;#右外SELECT e.employee_id,d.*FROM employees e
RIGHTOUTERJOIN departments d
ON e.`department_id`=d.`department_id`WHERE e.`employee_id`ISNULL;#全外连接USE girls;#交叉连接实现笛卡尔乘积SELECT b.*,bo.*FROM beauty b
CROSSJOIN boys bo;