Database multi-table link query method

大家好,Share the notes you learn as part of your daily study,希望可以帮助到大家~

This article will introduce the multi-table joint query into three parts：

1. 通过Select子句进行查询

2.  通过内连接 inner join进行查询

3. 通过外连接left join,left outer join,right join,right outer join,union进行查询
 

一、通过SELECT子句进行多表查询

语法：

select  字段名  

from表1,表2 …

where表1.字段 = 表2.字段

and 其它查询条件

例：以学生表student和班级表class为例

Select   student.sid,  student.sname,  student.classid, class.classid,  class.classname

from student,class

where student.classid = class.classid

注意：上面的代码中,The condition is based on the same field information in the two tables,Perform a join query between two tables,But this is not recommended in actual development,最好用主外键约束来实现.

二、通过内连接 inner join进行查询

语法：

select  字段名  

from表1

inner join 表2

on 表1.字段 = 表2.字段

例：以学生表student和班级表class为例

select   student.sid,  student.sname,  student.classid, class.classid,  class.classname

from student

inner join class

on student.classid = class.classid

What you get in this scenario is满足某一条件的student,class内部的数据;正因为得到的是内部共有数据,所以连接方式称为内连接.

三、通过外连接left join,left outer join,right join,right outer join,union进行查询

1.left join

语法：

select  字段名  

from表1

left join 表2

on 表1.字段 = 表2.字段

例：以学生表student和班级表class为例

select   student.*  , class.*

from student

left join class

on student.classid = class.classid

结果如下,classRecords that do not exist in the table are populatedNull:

What you get in this scenario isstudent的所有数据,and satisfy a certain conditionclass的数据;

2.left outer join(相当于left join  +  [where 表2.字段 is null])

语法：

select字段名  

from表1

left join 表2

on 表1.字段 = 表2.字段

where 表2.字段is null

例：以学生表student和班级表class为例

select   student.sid,student.sname,class.classid,class.classname

from student

left join class

on student.classid = class.classid

where class.classid is null

What you get in this scenario isstudentAll data in are subtracted"与classmeet the same conditions 的数据",然后得到的student剩余数据 

3.right join

语法：

select  字段名  

from 表1

right  join表2

on 表1.字段 = 表2.字段

例：以学生表student和班级表class为例

select  student.*  , class.*

from student

right join class

on student.classid = class.classid

 What you get in this scenario isclass的所有数据,and satisfy a certain conditionstudent的数据;

4.right outer join(相当于right join  +  [where 表1.字段 is null])

语法：

select字段名  

from 表1

right join 表2

on 表1.字段 = 表2.字段

where 表1.字段is null

例：以学生表student和班级表class为例

select   student.sid,student.sname,class.classid,class.classname

from student

right join class

on student.classid = class.classid

where student.classid is null

What you get in this scenario isclassAll data in are subtracted "与studentmeet the same conditions 的数据“,然后得到的class剩余数据;

4.left join  union right join

语法：

select  字段名  

from表1

left join 表2

on 表1.字段 = 表2.字段

union

select  字段名  

from表1

right  join表2

​​​​​​​on 表1.字段 = 表2.字段

例：以学生表student和班级表class为例

select   student.*  , class.*

from student

left join class

on student.classid = class.classid

union

select  student.*  , class.*

from student

right join class

on student.classid = class.classid

 What is obtained in this scenario is a public record that satisfies a certain condition,and unique records

Hope the above sharing can help you,Correct any mistakes in time～