数据库之连接查询

两张表

表1 ：t_user 9条数据
id,u_name,age

表2：t_lover_info 5条数据
id,u_id,lover_name

 

 

单表查询
示例：select * from t_user;

 

 

多表查询
笛卡尔积（无意义，需对结果集进行筛选）
select * from t_user,t_lover_name;

以下是对笛卡尔积的筛选处理

等值连接
1，利用where指定条件
select * from t_user as t1, t_lover_info as t2 where t1.id=t2.u_id;

2，利用join .......on条件
select * from t_user as t1 join t_lover_info as t2 on t1.id=t2.u_id;

3，利用inner join.....on条件
select * from t_user as t1 inner join t_lover_info as t2 on t1.id=t2.u_id;

4，利用cross join.....on条件
select * from t_user as t1 cross join t_lover_info as t2 on t1.id=t2.u_id;

 

 

 

左连接
1，select * from t_user as t1 left join t_lover_info as t2 on t1.id=t2.u_id;

2，select * from t_user as t1 left join t_lover_info as t2 on t1.id=t2.u_id where t2.id is null;

右连接，同左连接，right替换left