MySQL（4）

基本命令

左连接

获取左表所有记录，获取左边数据表所有符合要求的字段数据信息

 

 

 

 

select 要获取的字段要求（以（，）来区分每一个要求 from 定义的别名 left join 共同的点

例子： select p.name,p.age,p.sex,w.copany from person as p left join work as w on p.code=w.code;

 

 

 

 

练习（三个关联）：SELECT p.name,p.age,w.copany,w.salary,d.type,d.issafe FROM person AS p LEFT JOIN work as w  on p.`code`=w.`code` left JOIN workinfo as d on w.workid=d.workid;

 

 

 

右连接

获取右表所有记录的信息，获取右边数据表所有的数据信息

 

 

 select 要获取的字段要求（以（，）来区分每一个要求 from 定义的别名 right join 共同的点

例子（关联两个表）：SELECT w.copany,w.salary,info.type,info.issafe FROM work as w RIGHT JOIN workinfo as info ON w.workid=info.workid;

 

 

 

例子（关联五个表）：select p.name,p.age,w.copany,w.salary,winfo.type,winfo.issafe,s.schoolname,sinfo.xueli,sinfo.schooltype FROM person AS p RIGHT JOIN work as w on p.`code`=w.`code` RIGHT JOIN workinfo as winfo ON w.workid=winfo.workid RIGHT JOIN school as s on w.`code`=s.`code` RIGHT JOIN schoolinfo as sinfo on s.schoolid=sinfo.schoolid;

 

 内链接：inner join 外链接： left join right join

子查询

子查询就是以in中的查询结果为条件查询in语句前面的，如命令：select * from person where code in(select code from work)；就是指以select code from work的查询结果为条件，然后在person中进行查询。

select * from person where code in(select code from work);