连接查询

#连接查询
/*
 多表查询
 笛卡尔乘积现象：表1有m行，表2有n行，结果n*m行
 发生原因：没有有效的连接条件
 如何避免: 添加有效的连接条件
 
 
 
 分类：

    按年代分类：
    sql92标准:仅仅支持内连接
    sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接
    
    按功能分类：
        内连接：
            等值连接
            非等值连接
            自连接
        外连接：
            左外连接
            右外连接
            全外连接
        
        交叉连接
*/
SELECT * FROM `beauty`;
SELECT * FROM `boys`;
SELECT NAME,`boyName` FROM `beauty`,`boys`;#每一个都全连接 ：n*m



#一、sql92标准
#1、等值连接
/*

① 多表等值连接的结果为多表的交集部分
②n表连接，至少需要n-1个连接条件
③ 多表的顺序没有要求
④一般需要为表起别名
⑤可以搭配前面介绍的所有子句使用，比如排序、分组、筛选


*/
#案例1： 查询女神名和对应的男神名
SELECT NAME,`boyName` FROM `beauty`,`boys`
WHERE `beauty`.`boyfriend_id`  = `boys`.`id`;#要用表名限制是谁的id
#案例2： 查询员工名和对应的部门名
SELECT `last_name`,`department_name`
FROM `departments`,`employees`
WHERE `departments`.`department_id` = `employees`.`department_id`;


#2、为表起别名
/*
①提高语句的简洁度
②区分多个重名的字段

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

*/
#查询员工名、工种号、工种名



SELECT e.`last_name`,e.`job_id`,j.`job_title`  #job_id 有歧义
FROM `employees`  e,`jobs` j
WHERE e.`job_id` = j.`job_id`;

#3、两个表的顺序是否可以调换  可以的

#查询员工名、工种号、工种名
SELECT e.last_name,e.job_id,j.job_title
FROM jobs j,employees e
WHERE e.`job_id`=j.`job_id`;


#4、可以加筛选


#案例1：查询有奖金的员工名、部门名
SELECT `last_name`,`department_name`,`commission_pct`
FROM `departments`,`employees`
WHERE `departments`.`department_id` = `employees`.`department_id`
AND  `employees`.`commission_pct`IS NOT NULL;



#案例2：查询城市名中第二个字符为o的部门名和城市名
SELECT d.`department_name`,l.`city`
FROM `departments` d,`locations` l
WHERE d.`location_id` = l.`location_id`
AND l.`city` LIKE ‘_o%‘;
#5、可以加分组
#每个城市的部门个数
SELECT COUNT(*),city
FROM `locations` l,`departments` d
WHERE l.`location_id` = d.`location_id`
GROUP BY city;
#查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT `department_name`,d.`manager_id`,MIN(salary),`commission_pct`
FROM `employees` e,`departments` d
WHERE  e.`department_id` = d.`department_id` AND `commission_pct` IS NOT NULL
GROUP BY `department_name`,d.`manager_id`;#更保险



#6、可以加排序
#案例：查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT `job_title`,COUNT(*)
FROM `employees` e,`jobs` j
WHERE e.`job_id` = j.`job_id`
GROUP BY j.`job_title`#必须分组，不然直接count() 所有
ORDER BY COUNT(*) DESC;

#7、可以实现三表连接？

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

SELECT `last_name`,d.`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;

#创建工资等级表
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);

#2、非等值连接


#案例1：查询员工的工资和工资级别
SELECT `last_name`,salary,`grade_level` 
FROM `job_grades` j,`employees`
WHERE `salary` BETWEEN  j.`lowest_sal` AND j.`highest_sal`
AND j.`grade_level` =‘A‘;



#3、自连接



#案例：查询 员工名和上级的名称
SELECT a.`employee_id`,a.`last_name` "employee_last_name",b.`employee_id` "manager_id", b.`last_name`  "managet_last_name"
FROM `employees` a,`employees`b
WHERE a.`manager_id` =b.`employee_id`;#b.`employee_id` 不能为null