mysql连接查询(内联)_mysql下面join连接查询的使用方法

格式：

join连接查询(左联，右联，内联)

select * from 表1 inner/left/right

join 表2 on 表1.* = 表2.*

mysql> select * from

newstype;

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

| id | type |

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

| 1 | sport  |

| 2 | socity |

| 3 | culture

|

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

3 rows in set (0.00 sec)

mysql> select * from

news n inner join newstype nt on n.typeid =

nt.type;

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

| id | title  | typeid | id | type

|

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

| 1 |

asgdgdsg |

sport | 1 |

sport |

| 2 | sdgfsdghdsh | socity

| 2 | socity |

| 3 | dfgsdh | sport | 1 | sport |

| 4 | dfgsdgh

| socity | 2 | socity |

| 5 |

dfgsfhfh | socity

| 2 | socity |

| 6 | dfagcg | socity | 2 | socity |

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

6 rows in set (0.00 sec)

mysql> select * from

news n left join newstype nt on n.typeid =

nt.type;

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

| id | title  | typeid | id

| type  |

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

| 1 |

asgdgdsg |

sport | 1 |

sport |

| 3 | dfgsdh | sport | 1 | sport |

| 2 | sdgfsdghdsh | socity

| 2 | socity

|

| 4 | dfgsdgh

| socity | 2 | socity |

| 5 |

dfgsfhfh | socity

| 2 | socity

|

| 6 | dfagcg | socity | 2 | socity |

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

6 rows in set (0.00 sec)

mysql> select * from

newstype nt left join news n on n.typeid =

nt.type;

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

| id | type | id  | title  | typeid |

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

| 1 | sport  | 1 |

asgdgdsg |

sport |

| 2 | socity | 2 | sdgfsdghdsh | socity

|

| 1 | sport  | 3 | dfgsdh | sport |

| 2 | socity | 4 | dfgsdgh

| socity |

| 2 | socity | 5 |

dfgsfhfh | socity

|

| 2 | socity | 6 | dfagcg | socity |

| 3 | culture | NULL |

NULL | NULL  |

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

7 rows in set (0.00 sec)

mysql> select * from

news n right join newstype nt on n.typeid =

nt.type;

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

| id  | title

| typeid |

id | type |

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

| 1 |

asgdgdsg |

sport | 1 | sport

|

| 2 |

sdgfsdghdsh | socity | 2 |

socity |

| 3 |

dfgsdh |

sport | 1 | sport

|

| 4 |

dfgsdgh  | socity

| 2 | socity |

| 5 |

dfgsfhfh | socity

| 2 | socity |

| 6 |

dfagcg |

socity | 2 | socity |

| NULL | NULL | NULL

| 3 | culture

|

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

7 rows in set (0.00 sec)

mysql> select * from

newstype nt right join news n on

n.typeid=nt.type;

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

| id  | type

| id | title  | typeid |

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

| 1 |

sport | 1 |

asgdgdsg |

sport |

| 1 |

sport | 3 |

dfgsdh |

sport |

| 2 |

socity | 2 | sdgfsdghdsh | socity

|

| 2 |

socity | 4 | dfgsdgh  | socity |

| 2 |

socity | 5 | dfgsfhfh | socity |

| 2 |

socity | 6 | dfagcg | socity |

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

6 rows in set (0.00 sec)