#进阶6；连接查询

/*

含义：多个表格连接，当查询的字段来自于多个表时候，就会用到连接查询

我觉得这里类似于excel中的vlookup函数

笛卡尔乘积现象：表1有m行，表2有n行，结果有m*n行

如何发生：没有有效的连接条件

怎么解决：添加有效的连接条件

分类：

	按照年代分类

	sql92标准：仅仅支持内连接

	sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接

	按照功能分类：

		内连接：等值连接，非等值连接，自连接

		外连接：左外连接，右外连接，全外连接

		交叉连接

*/

use girls;

select * from beauty;#12行

select * from boys;  #4行

#出现笛卡尔乘积现象

select name,boyName from beauty,boys; #48行，这样是错的

#正确写法

select name,boyName from beauty,boys where beauty.boyfriend_id=boys.id; 

#一、sql92标准

#1.等值连接

/*

（1）多表的等值连接的结果为多表的交集

（2）n表连接，至少需要n-1个连接条件

（3）多表的顺序没有要求

（4）一般为表起别名

（5）可以搭配之前讲过的语法，例如order by,group by等

语法：

	select 查询列表 from 表1 别名，表2，别名 【where 筛选】【group by 分组】【having 筛选条件】【order by排序】

*/

#案例1：查询女神名和对应的男神名

select name,boyName from beauty,boys 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.为表取别名

/*

（1）提高语句的简洁度

（2）区分多个重名的字段

注意：如果为表起了别名，则查询的字段就不能使用原来的表名取限定

*/

# 查询员工名，工种号，工种名

select e.last_name,e.job_id,j.job_title from employees as e,jobs as j where e.job_id=j.job_id;

#3.两个表的顺序是否可以调换(可以)

# 查询员工名，工种号，工种名

select e.last_name,e.job_id,j.job_title from jobs as j,employees as e where e.job_id=j.job_id;

#4.可以做筛选吗

#案例1：查询有奖金的员工名、部门名

where e.commission_pct is not null #筛选条件

use myemployees;

select last_name,department_name, commission_pct from employees as e,departments as d where e.department_id=d.department_id and e.commission_pct is not null;

#案例2：查询城市名中第二个字符为o， 对应的部门名和城市名

select department_name,city from departments as d,locations as l where d.location_id=l.location_id and l.city like "_o%";

#5.可以加分组？

#案例1：查询每个城市的部门个数

select count(*) as "个数",city from departments as d,locations as l where d.location_id=l.location_id group by city;

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

select min(salary),department_name,e.manager_id from employees as e,departments as d where e.department_id=d.department_id and e.commission_pct is not null group by d.department_name,e.manager_id;

#6.可以加排序

#7.可以实现三表连接吗？

# 案例：查询员工名、部门名和所在的城市

select last_name,department_name,city from employees as e,departments as d,locations as l where e.department_id=d.department_id and d.location_id=l.location_id;

# 2.非等值连接

# 首先创建一个job_grades表

CREATE TABLE job_grades

(grade_level VARCHAR(3),

 lowest_sal  int,

 highest_sal int);

INSERT INTO job_grades

VALUES ('A', 1000, 2999);

INSERT INTO job_grades

VALUES ('B', 3000, 5999);

INSERT INTO job_grades

VALUES('C', 6000, 9999);

INSERT INTO job_grades

VALUES('D', 10000, 14999);

INSERT INTO job_grades

VALUES('E', 15000, 24999);

INSERT INTO job_grades

VALUES('F', 25000, 40000);

# 查看job_grades表

use myemployees;

select * from job_grades;

#案例1：查询员工的工资和工资级别；

select salary,grade_level from employees as e,job_grades as jg where salary between lowest_sal and highest_sal;

# 加筛选条件

select salary,grade_level from employees as e,job_grades as jg where salary between lowest_sal and highest_sal and jg.grade_level="A";

#3.自连接(没太明白，只知道在同一张表建立连接关系)

#案例：查询 员工名和上级的名称

select employee_id,last_name,manager_id from employees;#错的

#案例：

已知表student 里面有id(学号)，name,gradeID(年级编号)

已知表grade 里面有id(年级编号)，name(年级名)

已知表result 里面有id,score,studentNo(学号)

要求查询姓名、年级名、成绩

select stu.name,gra.name,res.score from student as stu,grade as gra,result as res where stu.gradeID=gra.id and stu.id=res.studentNo;

# 二、sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接

/*

语法：

	select 查询列表 from 表1 别名【连接类型】

		        join 表2 别名 on 连接条件 【where 筛选条件】【group by 分组】【having 筛选条件】【order by排序】

分类【连接类型】：

内连接 innner

外连接:

	左外 left 【outer】

	右外 right 【outer】

	全外 full 【outer】

交叉连接：cross

*/

1)内连接

/*

语法：

	select 查询列表 from 表1 别名 inner

		        join 表2 别名 on 连接条件;

分类：等值连接，非等值连接，自连接

*/

#1.等值连接

#案例1：查询女神名和对应的男神名

use girls;

select name,boyName from beauty inner join boys on beauty.boyfriend_id=boys.id; 

# 在别名，调换顺序，筛选等与sql92的内连接一致

#案例1：查询有奖金的员工名、部门名

where e.commission_pct is not null #筛选条件

use myemployees;

select last_name,department_name, commission_pct from employees as e inner join departments as d on e.department_id=d.department_id where e.commission_pct is not null;

#2.非等值连接

#案例1：查询员工的工资和工资级别；(inner可以省略)

select salary,grade_level from employees as e inner join job_grades as jg on salary between lowest_sal and highest_sal;

# 加筛选条件

select salary,grade_level from employees as e inner join job_grades as jg on salary between lowest_sal and highest_sal where jg.grade_level="A";

#3.自连接(没太明白，只知道在同一张表建立连接关系)

#二、外连接

/*

应用场景：用于查询一个表中有，另外一个表中没有的记录

特点：

	1.外连接的查询结果为主表中的所有记录

	  如果从表中有和他匹配的，则显示匹配的值

	  如果从表中没有和他匹配的，则显示null

	外连接的查询结果 = 内连接的查询结果+主表中有而从表中没有的记录

	2.左外连接：left join左边的是主表

	  右外连接：right join右边的是主表

	3.左外和右外交换2个表的顺序，可以实现同样的效果

	4.全外连接 = 内连接的结果 + 表1中有但是表2中没有的 + 表2中有但是表1中没有的

*/

use girls;

select * from beauty;

select * from boys;

引入：查询男朋友 不在男神表中的女神名字

# 我们先查下男朋友 在男神表中的女神名字

select name,boyName from beauty inner join boys on beauty.boyfriend_id=boys.id;

#左外连接

select name,boyName from beauty left join boys on beauty.boyfriend_id=boys.id;

#右外连接

select name,boyName from boys right join beauty on beauty.boyfriend_id=boys.id;

#以上左外和右外结果一致

#查询没有男朋友的

select name,boyName from beauty left join boys on beauty.boyfriend_id=boys.id where boys.boyName is null;

#案例1：查询哪个部门没有员工

use myemployees;

左外连接

select d.*,e.employee_id from departments as d left join employees as e on e.department_id=d.department_id where e.employee_id is null;

右外连接

select d.*, e.employee_id from employees as e right join departments as d on e.department_id=d.department_id where e.employee_id is null;

#全外 full join

use girls;

#交叉连接

cross join

也就是笛卡尔乘积

select name,boyName from beauty cross join boys;

#sql92  PK sql99