mysql联表查询

userlist 表

 

 userinfo表

1.内连接查询

SELECT 列名1,列名2... FROM 表1 INNER JOIN 表2 ON 表1.外键=表2.主键 WhERE 条件语句;

select * from userinfo inner join userlist on userinfo.userid=userlist.id;

 

select * from userinfo,userlist where userinfo.userid=userlist.id;

 

查询某几个字段：

select id,name,username from userinfo,userlist where userinfo.userid=userlist.id;

 

多条件查询

select * from userinfo,userlist where userinfo.userid=userlist.id and sex>1;

 

2.外连接查询(左，右)

SELECT 列名1 FROM 表1 LEFT OUTER JOIN 表2 ON 表1.外键=表2.主键 WhERE 条件语句;

select * from userinfo left outer join userlist on userinfo.userid=userlist.id;

 

select * from userinfo right outer join userlist on userinfo.userid=userlist.id;