九、多表数据记录查询

---------------------------------------------------------       小路原创           ------------------------------------------------

一、关系数据操作概念

1、并union：并就是把具有相同字段数目和字段类型的表合并到一起，和数学上集合的并有相似性。

2、笛卡尔积cartesian product：没有连接条件表关系返回的结果。

3、内连接inner join ：在表关系的笛卡尔积数据记录中，保留表关系中所有匹配的数据记录，舍弃不匹配的数据记录。

按照匹配的条件分为：

自然连接natural join：笛卡尔积中，根据表关系中相同名称的字段自动进行记录匹配，然后去掉重复的字段。

等值连接：在笛卡尔积中选择所匹配字段值相等（要用=指定匹配条件）的记录。

不等连接：在笛卡尔积中选择匹配字段不相等（要用!=指定匹配条件）的记录。

4、外连接outer join ：在笛卡尔积中保留表关系中所有匹配的数据记录，而且会保留部分不匹配的数据记录。

按照保留不匹配条件数据记录的来源分为;

左外连接:保留左边表中的不匹配记录。

右外连接：保留右边表中不匹配的记录。

全外连接：保留左右两边表中不匹配的记录。目前MySQL5.6还不支持全外连接！

有两种连接方式：一种是在from子句里面利用”,“号区分多个表，在where子句里面通过逻辑表达式来实现匹配条件，从而实现表的连接。

另一种是ANSI连接：在from子句中使用join on 关键字，在on关键字子句里指定连接条件。MySQL推荐使用ANSI连接方式。

二、内连接查询

use db_name;

select field,field2,…… --来自多个连接表的字段

from join_tablename1 inner join join_tablename2 [inner join join_tablenamen]

on join_condition; --等值连接或者不等值连接

1、自连接：表与其自身进行连接。

example:

use school;

select t1.Name,t2.Dept 

from students t1 inner join students t2

on t1.ID = t2.ID;--在from子句中指定表的别名

ANSI语法：

use school;

select t1.Name,t2.Dept 

from students t1, students t2

where t1.ID = t2.ID;--在from子句中指定表的别名

2、等值连接

example:

use school;

select t1.Name '学生姓名',t1.Dept '所在院系',t2.Name '指导老师'

from students t1 inner join teachers t2 

on t1.teacherID = t2.ID;

ANSI语法：

use school;

select t1.Name '学生姓名',t1.Dept '所在院系',t2.Name '指导老师'

from students t1,teachers t2 

where t1.teacherID = t2.ID;

3、不等值连接

example:

use school;

select t1.Name '学生姓名',t1.Dept '所在院系',t2.Name '指导老师',t2.Dept '老师所在系'

from students t1 inner join teachers t2 

on t1.Dept != t2.Dept;

ANSI语法：

use school;

select t1.Name '学生姓名',t1.Dept '所在院系',t2.Name '指导老师',t2.Dept '老师所在系'

from students t1,teachers t2 

where t1.Dept != t2.Dept;

三、外连接

MySQL中外连接查询会返回所操作的表中至少一个表的所有记录。

use db_name;

select field1,field2……

from join_tablename1 left|right|full [outer] join join_tablename2

on join_condition;

 

1、左外连接

--students表的ID跟老师的ID不存在相等的。但是结果把学生表中学生名字和ID保留了下来，这正是左连接的特点。

example:

use school;

select t1.Name '学生姓名',t1.ID '学生ID',t2.Name '指导老师',t2.ID '老师ID'

from students t1 left outer join teachers t2 

on t1.ID = t2.ID;

2、右外连接

--students表的ID跟老师的ID不存在相等的。但是结果把老师表中老师名字和ID保留了下来，这正是右连接的特点。

example:

use school;

select t1.Name '学生姓名',t1.ID '学生ID',t2.Name '指导老师',t2.ID '老师ID'

from students t1 right outer join teachers t2 

on t1.ID = t2.ID;

全外连接

--目前不支持！

四、合并查询数据记录

通过union关键字可将多个select语句的查询结果合并在一起组成新的关系。

select field1,field2……

from table_name1

union|union all 

select field1,field2……

from table_name2

……

……

注意：union合并会去掉重复数据！

example:

use school;

select * from teachers

union 

select * from students;

union all合并不会去掉重复的数据！

use school;

select * from teachers

union all

select * from students;

五、子查询

注意：连接查询的性能很差，所以推荐使用子查询。

子查询是指在一个查询中嵌套了其他查询语句，可以在from子句和where子句中。

example:

use school;

select * 

from students

where Age<(

select Age 

from teachers

where Name = '李四光'

);

use school;

select * 

from students

where (Age,Name)=(

select Age,Name

from students

where ID = 23

);

use school;

select *

from students

where Age in(

select Age 

from students 

where Age>=20

);

带有关键字any的子查询

any表示主查询的条件为满足子查询返回的结果中的任何一条记录，有=any(跟in一样)，>any(>=any)、<any(<=any)三种匹配方式

use school;

select *

from students

where Age >any(

select Age 

from students 

where Age>=20

);

带有关键字all的子查询

all关键字表示主查询的条件为满足子查询返回查询结果中所有记录，有>all(>=all)、<all(<=all)两种匹配方式

use school;

select *

from students

where Age>all(

select Age 

from students 

where Age>=19

);

带有关键字exists的子查询

use school;

select *

from students

where exists(

select Age 

from students 

where Age>=100

);

use school;

select t1.Name,t1.Dept

from students t1 inner join(

select Name,Dept

from teachers 

group by ID DESC

) t2

on t1.Dept = t2.Dept;

---------------------------------------------------------       小路原创           ------------------------------------------------