MySQL必知必会_连接查询

最新推荐文章于 2021-08-20 18:07:41 发布

zjxxyz123

最新推荐文章于 2021-08-20 18:07:41 发布

阅读量298

点赞数

分类专栏：数据库笔记文章标签： MySQL

本文链接：https://blog.csdn.net/zjxxyz123/article/details/79155807

版权

数据库笔记专栏收录该内容

24 篇文章 1 订阅

订阅专栏

进阶6：多表连接查询

笛卡尔乘积：如果连接条件省略或无效则会出现
解决办法：添加上连接条件

一、传统模式下的连接：等值连接——非等值连接

1.等值连接的结果 = 多个表的交集
2.n表连接，至少需要n-1个连接条件
3.多个表不分主次，没有顺序要求
4.一般为表起别名，提高阅读性和性能

二、sql99语法：通过join关键字实现连接

含义：1999年推出的sql语法
支持：
等值连接、非等值连接 （内连接）
外连接
交叉连接

语法：

select 字段，...
from 表1
【inner|left outer|right outer|cross】join 表2 on  连接条件
【inner|left outer|right outer|cross】join 表3 on  连接条件
【where 筛选条件】
【group by 分组字段】
【having 分组后的筛选条件】
【order by 排序的字段或表达式】

好处：语句上，连接条件和筛选条件实现了分离，简洁明了！

三、自连接

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

sql99

SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m ON e.`manager_id`=m.`employee_id`;

sql92

SELECT e.last_name,m.last_name
FROM employees e,employees m 
WHERE e.`manager_id`=m.`employee_id`;

#

    一、含义
    当查询中涉及到了多个表的字段，需要使用多表连接
    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 排序字段】


    四、SQL99语法
    1、内连接
    语法：
    select 查询列表
    from 表1 别名
    【inner】 join 表2 别名 on 连接条件
    where 筛选条件
    group by 分组列表
    having 分组后的筛选
    order by 排序列表
    limit 子句;

    特点：
    ①表的顺序可以调换
    ②内连接的结果=多表的交集
    ③n表连接至少需要n-1个连接条件

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




    2、外连接
    语法：
    select 查询列表
    from 表1 别名
    left|right|full【outer】 join 表2 别名 on 连接条件
    where 筛选条件
    group by 分组列表
    having 分组后的筛选
    order by 排序列表
    limit 子句;
    特点：
    ①查询的结果=主表中所有的行，如果从表和它匹配的将显示匹配行，如果从表没有匹配的则显示null
    ②left join 左边的就是主表，right join 右边的就是主表
      full join 两边都是主表
    ③一般用于查询除了交集部分的剩余的不匹配的行

    3、交叉连接

    语法：
    select 查询列表
    from 表1 别名
    cross join 表2 别名;

    特点：
    类似于笛卡尔乘积

################################案例演示

#进阶6：连接查询
/*
含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

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

发生原因：没有有效的连接条件
如何避免：添加有效的连接条件

分类：

    按年代分类：
    sql92标准:仅仅支持内连接
    sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接

    按功能分类：
        内连接：
            等值连接
            非等值连接
            自连接
        外连接：
            左外连接
            右外连接
            全外连接

        交叉连接


*/

SELECT * FROM beauty;

SELECT * FROM boys;


SELECT NAME,boyName FROM boys,beauty
WHERE beauty.boyfriend_id= boys.id;

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

① 多表等值连接的结果为多表的交集部分
②n表连接，至少需要n-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、为表起别名
/*
①提高语句的简洁度
②区分多个重名的字段

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

*/
#查询员工名、工种号、工种名
SELECT last_name,employees.`job_id`,job_title  # 因为两个表中都存在 job_id这个字段，所以必须用表名限定，否则会有歧义
FROM employees,jobs
WHERE employees.`job_id` = jobs.`job_id`;

#为了简洁，给表名起一个别名
SELECT e.last_name,e.job_id,j.job_title  # 此处，另外两个字段不加别名限制也可以
FROM employees  e,jobs j  # 也可用 AS 的方法别名
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、可以加筛选


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

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;  #筛选条件，同样是在此WHERE 中，用 AND 连接

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

SELECT department_name,city
FROM departments d,locations l
WHERE d.`location_id` = l.`location_id`
AND city LIKE '_o%';

#5、可以加分组


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

SELECT COUNT(*) 个数,city
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY city;


#案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT department_name,d.`manager_id`,MIN(salary)
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 employees e,jobs j
WHERE e.`job_id`=j.`job_id`
GROUP BY job_title
ORDER BY COUNT(*) 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`
AND city LIKE 's%'

ORDER BY department_name DESC;



#2、非等值连接


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

SELECT * FROM job_grades;

SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`
#AND g.`grade_level`='A';

/*
select salary,employee_id from employees;
select * from 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);

*/




#3、自连接(只涉及一个表，对于特有的表才能执行自连接，由员工id找到上级id和员工名字，再由上级id在本表中找到上级名字)
# 可以看做是两张表（一张员工表，一张领导表，只不过两张表一模一样），连接条件就是 e.`manager_id`=m.`employee_id`



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

SELECT e.employee_id,e.last_name,m.employee_id,m.last_name  # 此刻便体现了 别名的好处，避免歧义
FROM employees e,employees m  # 看做两张表
WHERE e.`manager_id`=m.`employee_id`;  #连接条件，由上级id在本表中找到上级名字

#######################sql99连接查询

#二、sql99语法
/*
语法：
    select 查询列表
    from 表1 别名 【连接类型】
    join 表2 别名 
    on 连接条件
    【where 筛选条件】
    【group by 分组】
    【having 筛选条件】
    【order by 排序列表】


分类：
内连接（★）：inner
外连接
    左外(★):left 【outer】
    右外(★)：right 【outer】
    全外：full【outer】
交叉连接：cross 

*/


#一）内连接
/*
语法：

select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件;

分类：
等值
非等值
自连接

特点：
①添加排序、分组、筛选
②inner可以省略
③ 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
④inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集





*/


#1、等值连接
#案例1.查询员工名、部门名

SELECT last_name,department_name
FROM departments d
 JOIN  employees e
ON e.`department_id` = d.`department_id`;



#案例2.查询名字中包含e的员工名和工种名（添加筛选）
SELECT last_name,job_title
FROM employees e
INNER JOIN jobs j
ON e.`job_id`=  j.`job_id`
WHERE e.`last_name` LIKE '%e%';



#3. 查询部门个数>3的城市名和部门个数，（添加分组+筛选）

#①查询每个城市的部门个数
#②在①结果上筛选满足条件的
SELECT city,COUNT(*) 部门个数
FROM departments d
INNER JOIN locations l
ON d.`location_id`=l.`location_id`
GROUP BY city
HAVING COUNT(*)>3;




#案例4.查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）

#①查询每个部门的员工个数
SELECT COUNT(*),department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`
GROUP BY department_name

#② 在①结果上筛选员工个数>3的记录，并排序

SELECT COUNT(*) 个数,department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`
GROUP BY department_name
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC;

#5.查询员工名、部门名、工种名，并按部门名降序（添加三表连接）

SELECT last_name,department_name,job_title
FROM employees e
INNER JOIN departments d ON e.`department_id`=d.`department_id`
INNER JOIN jobs j ON e.`job_id` = j.`job_id`

ORDER BY 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`;


 #查询工资级别的个数>20的个数，并且按工资级别降序
 SELECT COUNT(*),grade_level
FROM employees e
 JOIN job_grades g
 ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
 GROUP BY grade_level
 HAVING COUNT(*)>20
 ORDER BY grade_level DESC;


 #三）自连接

 #查询员工的名字、上级的名字
 SELECT e.last_name,m.last_name
 FROM employees e
 JOIN employees m
 ON e.`manager_id`= m.`employee_id`;

  #查询姓名中包含字符k的员工的名字、上级的名字
 SELECT e.last_name,m.last_name
 FROM employees e
 JOIN employees m
 ON e.`manager_id`= m.`employee_id`
 WHERE e.`last_name` LIKE '%k%';


 #二、外连接

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

 ###一般来说，要查询的信息主要来自哪一个表，那个表就是主表。

 #执行原理：拿主表中的每一个元素，去和从表中的每一个元素依靠连接条件去匹配（相当于两层循环）
 #注意 内连接与外连接执行原理的区别，内连接同样是匹配，但没有主从表之分，结果只是两个表的相匹配的结果
 #外连接也是匹配，但是有主从表之分，外连接查询结果=内连接结果（匹配成功）+主表中有而从表没有的记录（匹配不成功的）


 特点：
 1、外连接的查询结果为主表中的所有记录
    如果从表中有和它匹配的，则显示匹配的值
    如果从表中没有和它匹配的，则显示null
    外连接查询结果=内连接结果+主表中有而从表没有的记录
 2、左外连接，left join左边的是主表
    右外连接，right join右边的是主表
 3、左外和右外交换两个表的顺序，可以实现同样的效果 
 4、全外连接=内连接的结果（男女朋友匹配）+表1中有但表2没有的（男朋友不在boys表的女神）+表2中有但表1没有的（女朋友不在beauty表的男生）
  #外连接和全外连接都可以通过添加筛选条件，来查询一个表有，但是另一个没有的记录。

  #####################看ppt的图的演示！！！！！！！！！！！！！！！！！！！
 */
 #引入：查询男朋友 不在男神表的的女神名
 USE girls;
 SELECT * FROM beauty;
 SELECT * FROM boys;

 SELECT *
 FROM beauty b
 JOIN boys bo
 ON b.`boyfriend_id` = bo.`id`;

 #左外连接
 #第1步.
 SELECT *
 FROM beauty b
 LEFT OUTER JOIN boys bo
 ON b.`boyfriend_id` = bo.`id`;

 #第2步.
 SELECT b.name,bo.*
 FROM beauty b
 LEFT OUTER JOIN boys bo
 ON b.`boyfriend_id` = bo.`id`
 WHERE bo.`id` IS NULL;  #注意执行顺训，执行完连接条件后，因为没有分组，可以直接用WHERE进行筛选
 #筛选出男朋友不在男神表的女神名 ，一般选从表的主键列，因为在原表中 主键列不能为null,其他字段列可为null



 USE myemployees;
 #案例1：查询哪个部门没有员工
 #左外
 SELECT d.*,e.employee_id
 FROM departments d
 LEFT OUTER JOIN employees e 
 ON d.`department_id` = e.`department_id`
 WHERE e.`employee_id` IS NULL;


 #右外

  SELECT d.*,e.employee_id
 FROM employees e
 RIGHT OUTER JOIN departments d
 ON d.`department_id` = e.`department_id`
 WHERE e.`employee_id` IS NULL;


 #全外（mysql语法不支持）


 USE girls;
 SELECT b.*,bo.*
 FROM beauty b
 FULL OUTER JOIN boys bo
 ON b.`boyfriend_id` = bo.id;


 #交叉连接（两个表无顺序关系，就是笛卡尔乘积的结果（sql99这样表示，在sql92中直接用 , 连接两个表））

 SELECT b.*,bo.*
 FROM beauty b
 CROSS JOIN boys bo;



 #sql92和 sql99pk
 /*
 功能：sql99支持的较多
 可读性：sql99实现连接条件和筛选条件的分离，可读性较高
 */
 #################练习##################
 # 查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有，用null填充

 SELECT b.name,bo.*
 FROM beauty b
 LEFT OUTER JOIN boys bo
 ON b.`boyfriend_id` = bo.`id`
 WHERE b.`id` >3;

 #############查询哪个城市没有部门
 USE myemployees;
 SELECT city
 FROM locations l
 LEFT OUTER JOIN departments d
 ON l.`location_id` = d.`location_id`
 WHERE d.`department_id` IS NULL;

 ########## 查询部门名为SAL或者IT的员工信息
 # 注意：有可能部门名为SAL或者IT 但是不存在员工,最好用外连接，查的全


 SELECT department_name,e.*
 FROM departments d
 LEFT OUTER JOIN employees e
 ON e.`department_id` = d.`department_id`
WHERE department_name IN('SAL','IT');


 #1.标量子查询★

#案例1：谁的工资比 Abel 高?

SELECT *
FROM employees
WHERE salary>(
    SELECT salary
    FROM employees
    WHERE employees.`last_name`='Abel'
);


#案例2：返回job_id与141号员工相同，salary比143号员工多的员工 姓名，job_id 和工资

SELECT last_name,job_id,salary
FROM employees
WHERE job_id = (
    SELECT job_id
    FROM employees
    WHERE employee_id = 141

)AND salary >(
    SELECT salary
    FROM employees
    WHERE employee_id = 143
);


 #案例3：返回公司工资最少的员工的last_name,job_id和salary

 SELECT last_name,job_id,salary
 FROM employees
 WHERE salary =(
    SELECT MIN(salary)
    FROM employees

 );

 #案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资

 SELECT department_id,MIN(salary)
 FROM employees
 GROUP BY department_id
 HAVING MIN(salary)>(
    SELECT MIN(salary)
    FROM employees
    WHERE department_id = 50
 );


 #2.列子查询（多行子查询，因为结果集是一列多行）★
/*
###操作符：
IN/ NOT IN  等于列表中的任意一个(处于列表中)/不在列表中
ANY/SOME 和子查询返回的列表中的某一个进行比较 a> ANY(10,20,30) 等价于 a> MIN(10,20,30)，此时将转换为了标量子查询
ALL 和子查询返回的列表中的所有值进行比较 a> ALL(10,20,30) 等价于 a> MAX(10,20,30)，此时将转换为了标量子查询
IN {}可以用 = ANY {} 表达， NOT IN 可以用 <> ALL{} 表达
#案例1：返回location_id是1400或1700的部门中的所有员工姓名
*/

SELECT last_name
FROM employees
WHERE department_id IN (
    SELECT DISTINCT department_id
    FROM departments
    WHERE location_id IN(1400,1700)
);

 #案例2：返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id 以及salary

 SELECT employee_id,last_name,job_id,salary
 FROM employees
 WHERE salary < ANY(
    SELECT salary
    FROM employees
    WHERE job_id = 'IT_PROG'
 ) AND job_id <> 'IT_PROG';


 SELECT employee_id,last_name,job_id,salary
 FROM employees
 WHERE salary < (
    SELECT MAX(salary)
    FROM employees
    WHERE job_id = 'IT_PROG'
 )AND job_id <> 'IT_PROG';

#案例3：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工   的员工号、姓名、job_id 以及salary
 SELECT employee_id,last_name,job_id,salary
 FROM employees
 WHERE salary < ALL(
    SELECT salary
    FROM employees
    WHERE job_id = 'IT_PROG'
 ) AND job_id <> 'IT_PROG';

SELECT employee_id,last_name,job_id,salary
 FROM employees
 WHERE salary < (
    SELECT MIN(salary)
    FROM employees
    WHERE job_id = 'IT_PROG'
 )AND job_id <> 'IT_PROG';


#3、行子查询（结果集一行多列或多行多列）

#案例：查询员工编号最小并且工资最高的员工信息

SELECT *
FROM employees
WHERE employee_id = (
    SELECT MIN(employee_id)
    FROM employees

)AND salary = (
    SELECT MAX(salary)
    FROM employees
);


SELECT *
FROM employees
WHERE (employee_id,salary)= (
    SELECT MIN(employee_id),MAX(salary)
    FROM employees
)；

###############################案例讲解

#1.显示所有员工的姓名，部门号和部门名称。
USE myemployees;

SELECT last_name,d.department_id,department_name
FROM employees e,departments d
WHERE e.`department_id` = d.`department_id`;


#2.查询90号部门员工的job_id和90号部门的location_id

SELECT job_id,location_id
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`
AND e.`department_id`=90;



#3. 选择所有有奖金的员工的
last_name , department_name , location_id , city


SELECT last_name , department_name , l.location_id , city
FROM employees e,departments d,locations l
WHERE e.department_id = d.department_id
AND d.location_id=l.location_id
AND e.commission_pct IS NOT NULL;


#4.选择city在Toronto工作的员工的
last_name , job_id , department_id , department_name 

SELECT last_name , job_id , d.department_id , department_name 
FROM employees e,departments d ,locations l
WHERE e.department_id = d.department_id
AND d.location_id=l.location_id
AND city = 'Toronto';



#5.查询每个工种、每个部门的部门名、工种名和最低工资


SELECT department_name,job_title,MIN(salary) 最低工资
FROM employees e,departments d,jobs j
WHERE e.`department_id`=d.`department_id`
AND e.`job_id`=j.`job_id`
GROUP BY department_name,job_title;



#6.查询每个国家下的部门个数大于2的国家编号

SELECT country_id,COUNT(*) 部门个数
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY country_id
HAVING 部门个数>2;





#7、选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
employees   Emp#    manager Mgr#
kochhar     101 king    100


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
AND e.last_name='kochhar';