#进阶6：连接查询/*说明：又称多表查询，当查询语句涉及到的字段来自于多个表时，就会用到连接查询
笛卡尔乘积现象：表1有m行，表2有n行，结果=m*n行
发生原因：没有有效的连接条件如何避免：添加有效的连接条件
分类：
按年代分类：1、sql92标准:仅仅支持内连接内连接：等值连接非等值连接自连接2、sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接按功能分类：内连接：等值连接非等值连接自连接外连接：左外连接右外连接全外连接交叉连接
*/#引入案例#查询女神名和对应的男神名SELECT*FROMbeauty;
SELECT*FROMboys;
SELECTNAME,boyNameFROMboys,beautyWHEREbeauty.boyfriend_id=boys.id;
#---------------------------------sql92标准------------------#一、内连接/*语法:select查询列表from表1别名,表2别名where连接条件and筛选条件groupby分组列表having分组后筛选orderby排序列表
执行顺序：
1、from子句2、where子句3、and子句4、groupby子句5、having子句6、select子句7、orderby子句
*/
#一）等值连接/*
①多表等值连接的结果为多表的交集部分②n表连接，至少需要n-1个连接条件③多表的顺序没有要求④一般需要为表起别名⑤可以搭配前面介绍的所有子句使用，比如排序、分组、筛选
#案例1：查询女神名和对应的男神名SELECTNAME,boyNameFROMboys,beautyWHEREbeauty.boyfriend_id=boys.id;
#案例2：查询员工名和对应的部门名
SELECTlast_name,department_nameFROMemployees,departmentsWHEREemployees.`department_id`=departments.`department_id`;
#2、为表起别名/*①提高语句的简洁度②区分多个重名的字段
注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定
*/#查询员工名、工种号、工种名
SELECTe.last_name,e.job_id,j.job_titleFROMemployeese,jobsjWHEREe.`job_id`=j.`job_id`;
#3、两个表的顺序是否可以调换
#查询员工名、工种号、工种名
SELECTe.last_name,e.job_id,j.job_titleFROMjobsj,employeeseWHEREe.`job_id`=j.`job_id`;
#4、可以加筛选
#案例：查询有奖金的员工名、部门名
SELECTlast_name,department_name,commission_pct
FROMemployeese,departmentsdWHEREe.`department_id`=d.`department_id`ANDe.`commission_pct`ISNOTNULL;
#案例2：查询城市名中第二个字符为o的部门名和城市名
SELECTdepartment_name,cityFROMdepartmentsd,locationslWHEREd.`location_id`=l.`location_id`ANDcityLIKE'_o%';
#5、可以加分组
#案例1：查询每个城市的部门个数
SELECTCOUNT(*)个数,cityFROMdepartmentsd,locationslWHEREd.`location_id`=l.`location_id`GROUPBYcity;
#案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资SELECTdepartment_name,d.`manager_id`,MIN(salary)FROMdepartmentsd,employeeseWHEREd.`department_id`=e.`department_id`ANDcommission_pctISNOTNULLGROUPBYdepartment_name,d.`manager_id`;#6、可以加排序
#案例：查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECTjob_title,COUNT(*)FROMemployeese,jobsjWHEREe.`job_id`=j.`job_id`GROUPBYjob_titleORDERBYCOUNT(*)DESC;
#7、可以实现三表连接？
#案例：查询员工名、部门名和所在的城市
SELECTlast_name,department_name,cityFROMemployeese,departmentsd,locationslWHEREe.`department_id`=d.`department_id`ANDd.`location_id`=l.`location_id`ANDcityLIKE's%'
ORDERBYdepartment_nameDESC;
#二）非等值连接
#案例1：查询员工的工资和工资级别
SELECTsalary,grade_levelFROMemployeese,job_gradesgWHEREsalaryBETWEENg.`lowest_sal`ANDg.`highest_sal`ANDg.`grade_level`='A';
/*selectsalary,employee_idfromemployees;select*fromjob_grades;CREATETABLEjob_grades(grade_levelVARCHAR(3),lowest_salint,highest_salint);
INSERTINTOjob_gradesVALUES('A',1000,2999);
INSERTINTOjob_gradesVALUES('B',3000,5999);
INSERTINTOjob_gradesVALUES('C',6000,9999);
INSERTINTOjob_gradesVALUES('D',10000,14999);
INSERTINTOjob_gradesVALUES('E',15000,24999);
INSERTINTOjob_gradesVALUES('F',25000,40000);
#三）自连接
#案例：查询员工名和上级的名称
SELECTe.employee_id,e.last_name,m.employee_id,m.last_nameFROMemployeese,employeesmWHEREe.`manager_id`=m.`employee_id`;
#------------------------SQL99语法#一、内连接语法：
SELECT查询列表FROM表名1别名【INNER】JOIN表名2别名ON连接条件WHERE筛选条件GROUPBY分组列表HAVING分组后筛选ORDERBY排序列表;
SQL92和SQL99的区别：
SQL99，使用JOIN关键字代替了之前的逗号，并且将连接条件和筛选条件进行了分离，提高阅读性！！！
#一）等值连接#①简单连接#案例：查询员工名和部门名
SELECTlast_name,department_nameFROMdepartmentsdJOINemployeeseONe.department_id=d.department_id;
#②添加筛选条件#案例1：查询部门编号>100的部门名和所在的城市名SELECTdepartment_name,cityFROMdepartmentsdJOINlocationslONd.`location_id`=l.`location_id`WHEREd.`department_id`>100;
#③添加分组+筛选#案例1：查询每个城市的部门个数
SELECTCOUNT(*)部门个数,l.`city`FROMdepartmentsdJOINlocationslONd.`location_id`=l.`location_id`GROUPBYl.`city`;
#④添加分组+筛选+排序#案例1：查询部门中员工个数>10的部门名，并按员工个数降序
SELECTCOUNT(*)员工个数,d.department_nameFROMemployeeseJOINdepartmentsdONe.`department_id`=d.`department_id`GROUPBYd.`department_id`HAVING员工个数>10ORDERBY员工个数DESC;
#案例：查询部门编号在10-90之间的员工的工资级别，并按级别进行分组SELECT*FROMsal_grade;
SELECTCOUNT(*)个数,gradeFROMemployeeseJOINsal_gradegONe.`salary`BETWEENg.`min_salary`ANDg.`max_salary`WHEREe.`department_id`BETWEEN10AND90GROUPBYg.grade;
#案例：查询员工名和对应的领导名
SELECTe.`last_name`,m.`last_name`FROMemployeeseJOINemployeesmONe.`manager_id`=m.`employee_id`;
还可以分别提取年月日小时
#二、外连接（只存在于sql99语法里面）
外连接分为左连接右连接和全外连接但是mysql不支持全外连接
/*
说明：查询结果位主表中所有记录，如果从表右匹配项，则显示匹配项：如果从表没有匹配项，则显示null
应用场景：一般用于查询主表中有但从表中没有记录
特点：
外连接分主从表，两表的顺寻不能任意调换
左连接的话，左边(表1)为主表，右链接的话，右边（表2）为主表
语法：
select查询列表
from表1别名
left|right|fullouterjoin表2别名
on连接条件
where筛选条件;
#案例1：查询所有女神记录，以及对应的男神名，如果没有对应的男神，则显示为null
#左连接
selectb.*,bo.*
frombeautyb
leftjoinboysonb.boyfriend_id=bo.id;
案例2：查询那个女神没有男朋友
leftjoinboysonb.boyfriend_id=bo.id
wherebo.idisnull;
#案例2：查询哪个部门没有员工，并显示其部门编号和部门名
selectd.*,e.*
fromdepartmentsd
leftjoinemployeeseond.department_id=e.department_id
wheree.employee_idisnull;
在此基础上没有员工部门个数
selectcount(*)部门个数
案例讲解
一、查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有，用null填充
selectb.id,b.name,bo.*
leftjoinboysboONb.boyfriend_id=bo.id
whereb.id>3;
二、查询哪个城市没有部门
selectl.city
rightjoinlocationslONl.liocation_id=d.loaction_id
whered.department_idISNULL;三、查询部门名为SAL或IT的员工信息