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）参考结果

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

SELECT * from doctor, department;

二、内、外、全连接查询

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、外连接查询（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、全连接查询（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;

三、复合条件查询

简单来说就是多条件复合成一条复杂的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;

四、子查询

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);

两种查询结果当然也是一样的：

这里还有另外一些练习：

--创建表也可以用嵌套：得到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);