MySQL数据库篇4

2020-08-25 本文已影响0人 Hold丶张

本章主要为多表联查的相关操作。

关键词：left join、reght join、inner join、join

1. 左连接查询：

语法：select * from [PrimaryTable] left join [ForeignTable] on PrimaryTable.PrimarykeyField = ForeignTable.ForeignkeField where [Term];

示例：select * from class left join student on class.c_id = student.s_cid;

注释：left join ... on 的连表查询结果，只显示左侧表的全部信息，右侧表只显示可对应的数据，如对应不上的数据显示null。

2. 右连接查询：

语法：select * from [PrimaryTable] reght join [ForeignTable] on PrimaryTable.PrimarykeyField = ForeignTable.ForeignkeyField where [Term];

示例：select * from class reght join student on class.c_id = student.s_cid;

注释：reght join ... on 的连表查询结果，只显示右侧表的全部信息，左侧表只显示可对应数据，其余不显示。

3. 内连接查询：

语法：select * from [PrimaryTable] inner join [ForeignTable] on PrimaryTable.PrimarkeyField = ForeignTable.ForeignkeyField where [Term];

示例：select * from class inner join student on class.c_id = student.s_cid;

注释：inner join ... on 的作用和 join ... on 的作用相同，有匹配数据就显示。

4. 其它联表查询方式：

语法：select * from [TableName1], [TabelName2] where Table1.FieldName = Table2.FieldName and [Term];

示例：select * from class, student where class.c_id = student.s_cid;

注释：空。

5. 嵌套查询方式：

语法①：select * from [TableName1] where Table1.FieldName = (select Table2.FieldName from TableName2 where [Term]);

语法②：select * from [TableName1] where Table1.FieldName in (select Table2.FieldName from TableName2 where [Term]);