MySQL数据库基础（多表关联查询、内外全连接、复合条件查询、子查询）

ゝ一纸荒年。 2023-02-11 10:14 6阅读 0赞

### 文章目录 ###

*   *   *   *  一、笛卡尔积查询
             *  二、内、外、全连接查询
             *  三、复合条件查询
             *  四、子查询
             *  五、EXIST判断

*本篇文章主要介绍MySQL里的多表关联查询，其中包括了笛卡尔积查询、内连接查询、外连接查询、全连接查询，另外还会介绍复合条件查询和子查询。*

#### 一、笛卡尔积查询 ####

**1、首先创建表并插入数据**  
1）doctor表

--创建一个doctor表
    create table doctor(
        doc_id int auto_increment primary key not null,
        doc_name varchar(50),
        age int,
        dept_id int
    );
    
    --给doctor插入数据
    insert into doctor(doc_name,age,dept_id) values
            ('A',19,200),
            ('B',26,201),
            ('C',30,201),
            ('D',24,202),
            ('E',20,200),
            ('F',38,204);

2）department表

create table department(
        dept_id int,
        dept_name varchar(100)
    );
    
    --给department插入数据
    insert into department values
          (200,'Lin'),
          (201,'Cure'),
          (202,'Check'),
          (203,'Found');

3）参考结果  
![1][]

**2、笛卡尔积表**  
简单来说就是将两张表合在一起显示出来，该表成为笛卡尔积表（用处不大）；  
也就是说，如果A表里有m条记录，B表里有n条记录，那这样取出来的表将有m✖n条记录

SELECT * from doctor, department;

![2][]

#### 二、内、外、全连接查询 ####

**1、内外全连接查询关键字**

*  内连接：inner join
 *  外连接：left join， right join
 *  全连接：full join

**2、内连接查询（inner join）：**  
通过对笛卡尔积表筛选后得到的表

--筛选doctor里的dept_id与department里的dept_id相对应的部分
    SELECT * from doctor, department where doctor.dept_id=department.dept_id;
    
    --如上例子，使用内连接查询
    --格式为：（A inner join B on A.C=B.D）
    SELECT * from doctor inner join department on doctor.dept_id=department.dept_id;

![3][]  
**3、外连接查询（left join， right join）：**

*  通过对笛卡尔积表筛选后得到的表
 *  left以左表为主（不管右表是否有对应的值，都会显示左表），right以右表为主（同理）
 *  能匹配的就匹配上，不能匹配的置为null

SELECT * from doctor left join department on doctor.dept_id=department.dept_id;
    
    SELECT * from doctor right join department on doctor.dept_id=department.dept_id;
    
    --筛选两表dept_id相等的doctor的doc_name和department的dept_name
    SELECT doctor.doc_name, department.dept_name from doctor right join department on doctor.dept_id=department.dept_id;

![4][]

**4、全连接查询（full join）：**  
但是在MySQL里不支持这种写法；  
最主要的是，全连接基本用不上。不过可以用左右连接的拼接（UNION）来模拟全连接的效果

-- 模拟全连接
    select * from doctor RIGHT JOIN department on doctor.dept_id = department.dept_id
       UNION
       select * from doctor LEFT JOIN department on doctor.dept_id = department.dept_id;

![5][]

#### 三、复合条件查询 ####

简单来说就是多条件复合成一条复杂的select查询语句

--查找所有Cure部（编号201）的doctor名字
    SELECT doctor.doc_name, department.dept_name FROM doctor, department
    WHERE doctor.dept_id=department.dept_id AND department.dept_name="Cure";
    
    --找出大于25的doctor的名字和所在的部门
    SELECT doctor.doc_name,department.dept_name FROM doctor, department
    WHERE doctor.dept_id=department.dept_id AND doctor.age>25;

![6][]

#### 四、子查询 ####

**1、查询内容在A表，查询条件在B表**

--查询doctor的dep_id在department的dep_id中出现过的doctor
    SELECT * from doctor WHERE dept_id in (201,202,203,204);

*但是以上把(201,202,203,204)给写死了，不灵活，因此可以再查询一遍得到department中所有的dep\_id，也就是用到子查询*

**2、子查询**  
其实就是嵌套查询，查询里面嵌套查询（select里套着select）

SELECT * from doctor WHERE dept_id in (SELECT dept_id from department);

两种查询结果当然也是一样的：  
![7][]  
这里还有另外一些练习：

--创建表也可以用嵌套：得到doctor的字段信息和数据信息
    CREATE table AA (SELECT * from doctor);
    
    --嵌套查询：大于25的doctor的所在的部门
    SELECT dept_name from department
               WHERE department.dept_id IN
              (SELECT doctor.dept_id from doctor where doctor.age>=25);

#### 五、EXIST判断 ####

*  EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录
 *  而是返回一个真假值。True或False
 *  当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询

--查询doctor里面是否存在dep_id=203的全部信息（如果存在就打印doctor的全部信息，这个案例里是存在的）
    select * from doctor
                WHERE EXISTS
                (SELECT dept_name from department where dept_id=203);
                
    -- 查询doctor里面是否存在dep_id=205的全部信息（本案例中不存在）
    select * from doctor
                WHERE EXISTS
                (SELECT dept_name from department where dept_id=205);

![8][]

[1]: https://img-blog.csdnimg.cn/20200522212956362.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1ZpZXdpbmZpbml0ZWx5,size_16,color_FFFFFF,t_70
[2]: https://img-blog.csdnimg.cn/20200522213052454.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1ZpZXdpbmZpbml0ZWx5,size_16,color_FFFFFF,t_70
[3]: https://img-blog.csdnimg.cn/20200522213744727.png
[4]: https://img-blog.csdnimg.cn/20200522213902861.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1ZpZXdpbmZpbml0ZWx5,size_16,color_FFFFFF,t_70
[5]: https://img-blog.csdnimg.cn/20200522214504126.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1ZpZXdpbmZpbml0ZWx5,size_16,color_FFFFFF,t_70
[6]: https://img-blog.csdnimg.cn/20200522214541448.png
[7]: https://img-blog.csdnimg.cn/2020052221495354.png
[8]: https://img-blog.csdnimg.cn/20200522215318378.png