mysql数据库常用的查询语句(多表查询)

例如： 原表country mysql select * from country; ------------------------------------------ | id | name | population | area | language | ------------------------------------------ |1 | mySQL | 13 | 960| chinese | |2 | American | 4 |60 | Eng

例如：

原表country

mysql> select * from country;

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

| id | name | population | area | language |

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

| 1 | mySQL | 13 | 960 | chinese |

| 2 | American | 4 |60 | English |

| 3 | Japan | 89 | 30 | Jpanese |

| 4 | England | 2 | 300 | English |

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

原表library

mysql> select * from library;

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

| id | name | author | price |

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

| 1 | java范例大全 | 张帆 | 99 |

| 2 | mySQL | 潘凯华 | 50 |

| 3 | SQLserver2005 | 刘智勇 | 80 |

| 4 | mySQL | 李慧 | 50 |

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

mysql> select area,author from country,library where country.name=library.name;

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

| area | author |

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

| 960 | 潘凯华 |

| 960 | 李慧 |

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

2 左外连接

mysql> select language,area,author from country left join library on country.name=library.name; //返回的结果除内连接的数据外，还包括左表中不符合条件数据

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

| language | area | author |

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

| chinese | 960 | 潘凯华 |

| chinese | 960 | 李慧 |

| English | 60 | NULL |

| Jpanese | 30 | NULL |

| English | 300 | NULL |

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

3 右外连接

mysql> select language,area,author from country right joinlibrary on country.name=library.name; // //返回的结果除内连接的数据外，还包括右表中不符合条件数据

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

| language | area | author |

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

| NULL | NULL | 张帆 |

| chinese | 960 | 潘凯华 |

| NULL | NULL | 刘智勇 |

| chinese | 960 | 李慧 |

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

4 复合条件连接查询

mysql>select population,area,author,price from country,library where country.name=library.name and price>30;

5 子查询

1 带IN关键字的子查询

mysql> select * from country wherename in(select name from library);