二、数据库查询语句(多表查询篇)

二、数据库查询语句(多表查询篇)

1、笛卡尔积

​ 前面涉及的都是单张表的查询，如果我们的查询条件相对比较复杂，需要涉及多张表进行查询，如果是两张无关的表联合查询，列出所有的可能的结果，如下图：

• 如果没有连接条件，则是以左表为驱动表，从左表的第一条数据开始和右表的每一条数据相拼接组成的集合，叫做笛卡尔积，如上图共有4种情况，但我们实际需要的只是两条。
• 没有连接条件时，我们必须列举所有的可能性，就会产生上边的一张大表，如果两个表的数据量变大，比如每张表1000条数据，那笛卡尔积，就会扩张到1百万，如果是三张表关联，就必须再乘以1000，只要是多表连接，就会涉及到笛卡尔积，所以笛卡尔积是一个很恐怖的事情，我们要依据情况加上合适的连接条件，过滤掉我们不需要的数据。
• 多表连接的方式主要分为：
  1. 交叉连接、自然连接、JOIN…USING、JOIN…ON
  2. 自连接、内连接、外连接

---

2、多表连接

该篇所需要的数据在文章最底部。

---

1.CROSS JOIN

简介：CROSS JOIN又称交叉连接，对连接的两张表记录做笛卡尔集，产生最终结果输出，分为无过滤条件和带过滤两种

• 语法：select xxx from 模式.表1 cross join 模式.表2

• 无过滤条件：student表和scores表做交叉连接

  • 代码：

    -- 交叉连接
    -- 10条数据 42 条数据
    select count(*) from other.student;
    select count(*) from other.scores;
    
    -- 无过滤条件,结果是420条数据
    select count(*) from other.student cross join other.scores;
    select count(*) from other.student,other.scores;
    
    -- 带过滤条件,结果是42条数据
    select count(*) from other.student t cross join other.scores s where t.id = s.s_id;
    

  • 效果图：

---

2.NATURAL JOIN

简介：NATURAL JOIN又称为自然连接，自然连接会自动把两种表的同名列进行连接条件，进行等值连接，我们称这样的连接为自然连接。

自然连接具有以下特点：

1. 连接表中存在同名列
2. 如果有多个同名列，则会产生多个等值连接条件
3. 如果连接表中的同名列类型不匹配，则报错处理

• 语法：select xxx from 模式.表1 natural join 模式.表2

• 代码：

  -- 自然连接
  create table other.t1(id int,name varchar(32));
  create table other.t2(id int,age int);
  insert into other.t1 values(1,'张三');
  insert into other.t1 values(2,'李四');
  insert into other.t1 values(3,'王五');
  insert into other.t2 values(2,18);
  insert into other.t2 values(3,20);
  commit;
  
  select * from other.t1 natural join other.t2;
  

• 效果图：

---

3.JOIN…USING

简介：这是自然连接的另一种写法，JOIN 关键字指定连接的两张表，USING 指明连接列。要求USING 中的列存在于两张连接表中

• 语法：select xxx from 模式.表1 join 模式.表2 using(列名)

• 代码：

  select * from other.t1 join other.t2 using(id);
  

• 效果图：

---

4.JOIN…ON

简介：这是一种连接查询的常用写法，说明是一个连接查询。JOIN 关键字指定连接的两张表， ON 子句指定连接条件表达式，其中不允许出现 ROWNUM。具体采用何种连接方式，由数据库内部分析确定

• 语法：select xxx from 模式.表1 join 模式.表2 on 连接条件

• 代码：查询学生的姓名和对应的成绩

  -- 查询学生的姓名和对应的成绩
  select st.name,s.score from other.student st join other.scores s on st.id = s.s_id;
  

• 效果图：

---

5.自连接

简介：数据表与自身进行连接，我们称这种连接为自连接。 自连接查询至少要对一张表起别名，否则，服务器无法识别要处理的是哪张表，太过简单，不做演示

• 语法：select xxx from 模式.表1 别名, 模式.表2 别名

select * from other.t1 m,other.t2 m where m.id = n.id;

6.内连接

简介：根据连接条件，结果集仅包含满足全部连接条件的记录，我们称这样的连接为内连接，与上述join on相同，对于【内连接】中的两个表，若【驱动表】中的记录在【被驱动表】中找不到与之匹配的记录，则该记录不会被加入到最后的结果集中

• 语法：select xxx from 模式.表1 inner join 模式.表2 on 连接条件

     通俗讲就是根据条件，找到表 A 和 表 B 的数据的交集。

• 代码：下列三条sql语句等效

  -- 查询学生的姓名和对应的成绩
  select st.name,s.score from other.student st inner join other.scores s on st.id = s.s_id;
  select st.name,s.score from other.student st join other.scores s on st.id = s.s_id;
  select st.name,s.score from other.student st,other.scores s where st.id = s.s_id;
  

• 效果图：

---

7.外连接（常用）

简介：外连接对结果集进行了扩展，会返回一张表的所有记录，对于另一张表无法匹配的字段 用 NULL 填充返回。

DM 数据库支持三种方式的外连接：左外连接、右外连接、全外连接。

外连接中常用到的术语：左表、右表。根据表所在外连接中的位置来确定，位于左侧的 表，称为左表；位于右侧的表，称为右表。例如 SELECT * FROM T1 LEFT JOIN T2 ON T1.C1=T2.D1，T1 表为左表，T2 表为右表，对于【外连接】中的两个表，即使【驱动表】中的记录在【被驱动表】中找不到与之匹配的记录，也要将该记录加入到最后的结果集中，针对不同的【驱动表的选择】，又可以将外连接分为【左外连接】和【右外连接】

• 语法：select xxx from 模式.表1 left/right join 模式.表2 on 连接条件

    我们可以使用一个图形来形容左外连接的效果：

• 代码：下面两条语句等效

  -- 外连接
  -- 查询学生的姓名和对应的成绩
  select st.name,s.score from other.student st left outer join other.scores s on st.id = s.s_id;
  select st.name,s.score from other.student st left join other.scores s on st.id = s.s_id;
  

• 效果图：

---

8.总结

内连接和外连接的区别：

• 对于【内连接】中的两个表，若【驱动表】中的记录在【被驱动表】中找不到与之匹配的记录，则该记录不会被加入到最后的结果集中。
• 对于【外连接】中的两个表，即使【驱动表】中的记录在【被驱动表】中找不到与之匹配的记录，也要将该记录加入到最后的结果集中，针对不同的【驱动表的选择】，又可以将外连接分为【左外连接】和【右外连接】。

所以我们可以得出以下结论：

• 对于左外连接查询的结果会包含左表的所有数据
• 对于右外连接查询的结果会包含右表的所有数据

外连接的关键字是【outer join】 也可以省略outter，连接条件一样需要使用【on】关键字；

---

3、所需数据

学习之前我们需要创建数据库并填充部分数据：

drop TABLE if EXISTS student;
CREATE TABLE other.student (
    id INT PRIMARY key,
    name VARCHAR (10),
    age INT  NOT NULL,
    gender varchar(2)
);
drop TABLE if EXISTS course;
CREATE TABLE other.course (
  id INT   PRIMARY key,
  name VARCHAR  ,
  t_id INT  
) ;
drop TABLE if EXISTS teacher;
CREATE TABLE other.teacher(
  id INT   PRIMARY key,
  name VARCHAR  
);
drop TABLE if EXISTS scores;
CREATE TABLE other.scores(
  s_id INT ,
  score INT ,
  c_id INT,
	PRIMARY key(s_id,c_id)
) ;
commit;

表单填充数据：

insert into  other.student (id,name,age,gender)VALUES(1,'白杰',19,'男'),(2,'连宇栋',19,'男'),(3,'邸志伟',24,'男'),(4,'李兴',11,'男'),(5,'张琪',18,'男'),(6,'武三水',18,'女'),(7,'张志伟',16,'男'),(8,'康永亮',23,'男'),(9,'杨涛瑞',22,'女'),(10,'王杰',21,'男');

insert into  other.course (id,name,t_id)VALUES(1,'数学',1),(2,'语文',2),(3,'c++',3),(4,'java',4),(5,'php',null);


insert into  other.teacher (id,name)VALUES(1,'张楠'),(2,'李子豪'),(3,'薇薇姐'),(4,'猴哥'),(5,'八戒');


insert into  other.scores (s_id,score,c_id)VALUES(1,80,1);
insert into  other.scores (s_id,score,c_id)VALUES(1,56,2);
insert into  other.scores (s_id,score,c_id)VALUES(1,95,3);
insert into  other.scores (s_id,score,c_id)VALUES(1,30,4);
insert into  other.scores (s_id,score,c_id)VALUES(1,76,5);

insert into  other.scores (s_id,score,c_id)VALUES(2,35,1);
insert into  other.scores (s_id,score,c_id)VALUES(2,86,2);
insert into  other.scores (s_id,score,c_id)VALUES(2,45,3);
insert into  other.scores (s_id,score,c_id)VALUES(2,94,4);
insert into  other.scores (s_id,score,c_id)VALUES(2,79,5);

insert into  other.scores (s_id,score,c_id)VALUES(3,65,2);
insert into  other.scores (s_id,score,c_id)VALUES(3,85,3);
insert into  other.scores (s_id,score,c_id)VALUES(3,37,4);
insert into  other.scores (s_id,score,c_id)VALUES(3,79,5);

insert into  other.scores (s_id,score,c_id)VALUES(4,66,1);
insert into  other.scores (s_id,score,c_id)VALUES(4,39,2);
insert into  other.scores (s_id,score,c_id)VALUES(4,85,3);

insert into  other.scores (s_id,score,c_id)VALUES(5,66,2);
insert into  other.scores (s_id,score,c_id)VALUES(5,89,3);
insert into  other.scores (s_id,score,c_id)VALUES(5,74,4);


insert into  other.scores (s_id,score,c_id)VALUES(6,80,1);
insert into  other.scores (s_id,score,c_id)VALUES(6,56,2);
insert into  other.scores (s_id,score,c_id)VALUES(6,95,3);
insert into  other.scores (s_id,score,c_id)VALUES(6,30,4);
insert into  other.scores (s_id,score,c_id)VALUES(6,76,5);

insert into  other.scores (s_id,score,c_id)VALUES(7,35,1);
insert into  other.scores (s_id,score,c_id)VALUES(7,86,2);
insert into  other.scores (s_id,score,c_id)VALUES(7,45,3);
insert into  other.scores (s_id,score,c_id)VALUES(7,94,4);
insert into  other.scores (s_id,score,c_id)VALUES(7,79,5);

insert into  other.scores (s_id,score,c_id)VALUES(8,65,2);
insert into  other.scores (s_id,score,c_id)VALUES(8,85,3);
insert into  other.scores (s_id,score,c_id)VALUES(8,37,4);
insert into  other.scores (s_id,score,c_id)VALUES(8,79,5);

insert into  other.scores (s_id,score,c_id)VALUES(9,66,1);
insert into  other.scores (s_id,score,c_id)VALUES(9,39,2);
insert into  other.scores (s_id,score,c_id)VALUES(9,85,3);
insert into  other.scores (s_id,score,c_id)VALUES(9,79,5);

insert into  other.scores (s_id,score,c_id)VALUES(10,66,2);
insert into  other.scores (s_id,score,c_id)VALUES(10,89,3);
insert into  other.scores (s_id,score,c_id)VALUES(10,74,4);
insert into  other.scores (s_id,score,c_id)VALUES(10,79,5);
commit;

查看创建的表：

select * FROM OTHER.student;
select * FROM OTHER.scores;
select * FROM OTHER.course;
select * FROM OTHER.teacher;