Mysql多表联合查询语句

绝地灬酷狼 2023-10-13 09:54 69阅读 0赞

##### 数据库脚本 #####

创建一个员工表和部门表，并插入相应的值

-- 创建部门表
    CREATE TABLE department (
      id INT PRIMARY KEY,
      name VARCHAR(50)
    );
    -- 创建员工表
    CREATE TABLE employee (
      id INT PRIMARY KEY,
      name VARCHAR(50),
      department_id INT,
      FOREIGN KEY (department_id) REFERENCES department(id)
    );
    -- 插入部门数据
    INSERT INTO department (id, name) VALUES
      (1, '部门A'),
      (2, '部门B'),
      (3, '部门C');
    -- 插入员工数据
    INSERT INTO employee (id, name, department_id) VALUES
      (1, '员工1', 1),
      (2, '员工2', 1),
      (3, '员工3', 2),
      (4, '员工4', 2),
      (5, '员工5', 3),
      (6, '员工6', 3),
      (7, '员工7', 1),
      (8, '员工8', 2),
      (9, '员工9', 3),
      (10, '员工10', NULL);

--------------------

##### 1. 内连接 #####

内连接是连接的两张表交集的部分，即2张表where条件成立的才都查出来  
隐式内连接：

select  字段列表  from  表1，表2 连接条件...;

显示内连接：

select 字段列表 from 表1 inner join 表2 on 连接条件...;

例子：查询每一个员工的姓名，及关联的部门的名称。  
1.隐式内连接：

select  employee.name, department.name from employee,department where employee.department_id=department.id;

2.显示内连接

select  employee.name, department.name  from employee inner join department on employee.department_id=department.id;

两种查询结果都如下：  
![在这里插入图片描述][c793436883fb4d29a16477be61f90e41.png_pic_center]  
因为员工10没有部门，所以不查询出来，所以内连接是查询两张表的交集。

--------------------

##### 2. 外连接 #####

左外连接：

select 字段列表 from 表1 left join 表2 on 连接条件...

查询的是表1（左表）的所有数据 包含表1和表2交集部分的数据  
例子：查询每一个员工的姓名，及关联的部门的名称。

select employee.name, department.name  from employee left join department on employee.department_id=department.id;

查询结果如下：  
![在这里插入图片描述][fbf3c540d8244655ab9a413cd356cda2.png_pic_center]

右外连接：

select 字段列表 from 表1 right join 表2 on 连接条件...

查询的是表2（右表）的所有数据 包含表1和表2交集部分的数据

例子：查询部门表的所有数据和对应的员工信息。

select department.name, employee.name from employee right join department on employee.department_id=department.id;

查询结果为：  
![在这里插入图片描述][2fd36943c86d4a3a9730cb5890b986d4.png_pic_center]

--------------------

##### 3.自连接 #####

自连接就相当于将1张表看作2张表来查询，这个时候就又可以分为自连接中的内连接和外连接。  
创建一个用户表，sql脚本如下：

-- 创建用户表
    CREATE TABLE user (
                          id INT PRIMARY KEY,
                          name VARCHAR(50),
                          manager_id INT,
                          salary DECIMAL(10, 2),
                          age INT,
                          depart_id INT
    );
    -- 插入示例数据
    INSERT INTO user (id, name, manager_id, salary, age) VALUES
                     (1, 'John Doe', NULL, 5000.00, 30,1),
                     (2, 'Jane Smith', 1, 4000.00, 28,2),
                     (3, 'Michael Johnson', 1, 4500.00, 32,3),
                     (4, 'Emily Davis', 2, 3500.00, 26,1),
                     (5, 'David Wilson', 3, 6000.00, 35,2)

表数据如下：  
![在这里插入图片描述][af779d5647114a9ba44b98ae950fb73d.png_pic_center]  
现在要查询用户的名字及其对应的领导名字，隐式内连接sql语句如下：

select u1.name as username, u2.name as managername from user u1,user u2 where u1.manager_id = u2.id;

查询结果如下：  
![在这里插入图片描述][7a08e563657040a2b18457a8c8ab9766.png_pic_center]  
现在还是需要查询用户名字，但是如果用户没有领导，也需要查询出来，这是需要用到外连接，sql脚本如下：

select u1.name as username, u2.name as managername  from user u1 left join user u2 on u1.manager_id=u2.id;

查询结果如下：  
![在这里插入图片描述][7c9a2c8918644fbb858302ce53298408.png_pic_center]

--------------------

##### 4.联合查询-union,union all #####

对于union查询，就是把多次查询的结果合并起来，并形成一个新的查询结果集。

select 字段列表 from 表A ...
    union [ALL]
    select 字段列表 from 表B...

例：现在依旧查询用户表，查询用户薪资大于等于4000，和年龄大于30的全部查询出来，注意这里不是查询薪资大于等于4000且年龄大于30的数据，不能用and,想用union all,sql脚本如下：

select * from user where salary>=4000
    union all
    select * from user where age>30;

查询结果如下：  
![在这里插入图片描述][ba1dbd2e741b4433a68e01dcb0f617a0.png_pic_center]  
注意上面有重复的数据，现在用union，sql脚本如下：

select * from user where salary>=4000
    union
    select * from user where age>30;

查询结果如下：  
![在这里插入图片描述][7074cd5b07084a578761c0f159dfdf30.png_pic_center]  
由以上可以得出结论：对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。union all 会将全部的数据合并在一起，union会对合并之后的数据去重。

--------------------

##### 5. 子查询 #####

概念：sql语句中嵌套select 语句，称为嵌套查询，又称子查询。

select * from t1 where colunm1=(select column1 from t2);

子查询外部的语句可以是insert/update/delete/select的任何一个。  
根据子查询结果不同，分为：  
标量子查询（子查询结果为单个值）;  
列子查询（子查询结果为一列）；  
行子查询（子查询结果为一行）；  
表子查询（子查询结果为多行多列）  
根据子查询位置，分为：where之后，from之后，select之后。

1.标量子查询：子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询成为标量子查询。常用的操作符：= <> > >= < <=  
例子：根据上诉的部门表和员工表，查询’部门A’的所用员工信息表，sql语句如下：

select * from employee where department_id=(select id from department where department.name='部门A')

查询结果如下：  
![在这里插入图片描述][9309fb63d66042a4826fa3243869198c.png_pic_center]  
2.列子查询  
子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。  
常见的操作符：  
IN :在指定的集合范围之内，多选一；  
NOT IN：不在指定的集合范围之内；  
ANY ：子查询返回列表中，有任意一个满足即可；  
SOME ：与ANY等同，使用SOME的地方都可以使用ANY；  
ALL：子查询返回列表的所有值都必须满足。  
例子：查询"部门A"和"部门B"的所有员工信息,sql脚本如下：

select * from employee where department_id in(select id from  department where name='部门A'or name='部门B')

![在这里插入图片描述][48fbc4cfc331468d876cfb8c8930b9dd.png_pic_center]  
例子：在用户表中，查询比部门A工资都高的员工信息,sql脚本如下：

select * from user where salary>all(select salary from user where id in(select id from department where name='部门A'));

查询结果如下：  
![在这里插入图片描述][3ec5f48be9be4e40a263ee4df687171b.png_pic_center]

3.行子查询  
子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。  
常用的操作符：= 、<> 、IN 、NOT IN  
例：查询与Jane Smith薪资相同且直属领导相同的员工信息，sql脚本如下：

select * from user where (salary, manager_id)=(select  salary,manager_id from user where name = 'Jane Smith')

查询结果如下：  
![在这里插入图片描述][042e2937ec074dec8e7d3c9571c171f2.png_pic_center]  
4.表子查询  
子查询的结果是多行多列，这种子查询称为表子查询，常用的操作符为IN。  
例：查询与Jane Smith 和 David Wilson薪资和部门相同的员工信息，sql脚本如下：

SELECT * FROM USER WHERE (salary, depart_id) IN(SELECT salary, depart_id  FROM USER WHERE NAME = 'Jane Smith' OR NAME='David Wilson')

查询结果如下：  
![在这里插入图片描述][140e06d00b454352ad19cf9db349f594.png_pic_center]

[c793436883fb4d29a16477be61f90e41.png_pic_center]: https://img-blog.csdnimg.cn/c793436883fb4d29a16477be61f90e41.png#pic_center
[fbf3c540d8244655ab9a413cd356cda2.png_pic_center]: https://img-blog.csdnimg.cn/fbf3c540d8244655ab9a413cd356cda2.png#pic_center
[2fd36943c86d4a3a9730cb5890b986d4.png_pic_center]: https://img-blog.csdnimg.cn/2fd36943c86d4a3a9730cb5890b986d4.png#pic_center
[af779d5647114a9ba44b98ae950fb73d.png_pic_center]: https://img-blog.csdnimg.cn/af779d5647114a9ba44b98ae950fb73d.png#pic_center
[7a08e563657040a2b18457a8c8ab9766.png_pic_center]: https://img-blog.csdnimg.cn/7a08e563657040a2b18457a8c8ab9766.png#pic_center
[7c9a2c8918644fbb858302ce53298408.png_pic_center]: https://img-blog.csdnimg.cn/7c9a2c8918644fbb858302ce53298408.png#pic_center
[ba1dbd2e741b4433a68e01dcb0f617a0.png_pic_center]: https://img-blog.csdnimg.cn/ba1dbd2e741b4433a68e01dcb0f617a0.png#pic_center
[7074cd5b07084a578761c0f159dfdf30.png_pic_center]: https://img-blog.csdnimg.cn/7074cd5b07084a578761c0f159dfdf30.png#pic_center
[9309fb63d66042a4826fa3243869198c.png_pic_center]: https://img-blog.csdnimg.cn/9309fb63d66042a4826fa3243869198c.png#pic_center
[48fbc4cfc331468d876cfb8c8930b9dd.png_pic_center]: https://img-blog.csdnimg.cn/48fbc4cfc331468d876cfb8c8930b9dd.png#pic_center
[3ec5f48be9be4e40a263ee4df687171b.png_pic_center]: https://img-blog.csdnimg.cn/3ec5f48be9be4e40a263ee4df687171b.png#pic_center
[042e2937ec074dec8e7d3c9571c171f2.png_pic_center]: https://img-blog.csdnimg.cn/042e2937ec074dec8e7d3c9571c171f2.png#pic_center
[140e06d00b454352ad19cf9db349f594.png_pic_center]: https://img-blog.csdnimg.cn/140e06d00b454352ad19cf9db349f594.png#pic_center