mysql資料庫常用的查詢語句(多表查詢)

1  内連接配接查詢

select 字段1,字段2 from 表1,表2 where 表1.字段3=表2.字段3; //要有相同的字段3 查詢的是具有相同的字段3值 （注：表1 inner join 表2 中間的inner on一般可以省略）

例如： 

原表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);