mysql数据库join查询默认值_mysql数据库join查询

参考BeginMan的博客：网址：http://www.cnblogs.com/BeginMan/p/3754322.html

join可用于多表的查询

... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON conditiona

A表id,name B表id,name,remark

inner join(内连接)，取得2个表中符合条件的记录。--同时符合A，B 2个表。----此为A与B的交集

mysql> select * from A inner join B on A.name = B.name;

+------+----+--------+------+----+

| name | id | remark | name | id |

+------+----+--------+------+----+

| zhou |  1 | zhou   | zhou |  5 |

| ma   |  2 | ma     | ma   |  4 |

+------+----+--------+------+----+

2 rows in set

左连接：

左表为全记录，如果右表没有对照的记录，则右面为Null

mysql> select * from A left join B on A.name = B.name;

+------+----+--------+------+------+

| name | id | remark | name | id   |

+------+----+--------+------+------+

| zhou |  1 | zhou   | zhou |    1 |

| ma   |  2 | ma     | ma   |    4 |

| kong |  3 | NULL   | NULL | NULL |

+------+----+--------+------+------+

3 rows in set

右连接：

右面为全记录，如果左表无，则左表为null

mysql> select * from A right join B on A.name = B.name;

+------+------+--------+------+----+

| name | id   | remark | name | id |

+------+------+--------+------+----+

| zhou |    1 | zhou   | zhou |  1 |

| ma   |    2 | ma     | ma   |  4 |

| kong |    3 | kong   | kong |  5 |

| NULL | NULL | sun    | sun  |  6 |

+------+------+--------+------+----+

4 rows in set

多表查询“

select username,psw,gname,tel from (t1 left join t2 on t1.t1_id=t2.t1_id) left join t3 on t1.t1_id=t3.t1_id”

select dayrecord.dayRID from dayrecord  left join  operationrecord on dayrecord.dayRID=operationrecord.dayRdIDwhere dayrecord.conve='2' and 表2.id=XX  and 表三.id=xx;

即where 语句，放到left join on 的后面，即可。