MySQL基本SQL查詢語句:多表查詢和子查詢示例

mysql中表單查詢用到select命令了，如果是多表查詢我們用很多方法，如select union與left join之類的聯合查詢了，下面我給各位mysql初學者來介紹sql查詢示例。

一、簡單查詢：
基本語法：

 代碼如下 復制代碼 SELECT * FROM tb_name;

查詢全部

 代碼如下 復制代碼 SELECT field1,field2 FROM tb_name;

投影

 代碼如下 復制代碼 SELECT [DISTINCT] * FROM tb_name WHERE qualification;

 選擇
說明：

FROM子句： 要查詢的關系         表、多個表、其它SELECT語句

WHERE子句：布爾關系表達式，主要包含如下這幾類表達式：

比較：=、>、>=、<=、<
邏輯關系：
AND
OR
NOT

BETWEEN ... AND ...   ：在兩個值之間
LIKE ‘’
%: 任意長度任意字符
_：任意單個字符
REGEXP, RLIKE :正則表達式，此時索引無效
IN
IS NULL
IS NOT NULL
 

如下查詢本博客的wp-links和wp_posts表：

 代碼如下 復制代碼

mysql> select * from wp_links; 查詢全部 mysql> select link_name,link_url from wp_links; 投影
+-------------------+--------------------------------------+
| link_name               | link_url                                              |
+-------------------+--------------------------------------+
| 旺旺騰訊微博          | http://www.111cn.net              |
| 旺旺新浪微博          | http://weibo.com/gz100ww               |
| 51CTO技術博客      | http://www.111cn.net/ |
+-------------------+--------------------------------------+
10 rows in set (0.00 sec)

mysql> select ID,post_title,post_date from wp_posts where ID>1290 and post_status='publish';選擇
+------+----------------------------------------------------------------+----------------------+
| ID   | post_title                                                                                      | post_date                   |
+------+----------------------------------------------------------------+----------------------+
| 1291 | 【轉】HP 3PAR存儲概念之三                                                      | 2013-08-29 17:21:27 |
| 1298 | 【轉】HP 3PAR存儲概念之四                                                      | 2013-08-29 17:22:33 |
| 1351 | 【轉】XenDesktop 5.5+vSphere 5創建虛擬機報錯                      | 2013-09-04 17:41:26 |
| 1357 | linux下強大的網絡工具Netcat                                                     | 2013-09-09 22:26:45 |
| 1360 | MySQL常用命令、技巧和注意事項                                              | 2013-09-20 11:04:15 |
| 1369 | 【轉】數據庫設計原理知識--B樹、B-樹、B+樹、B*樹都是什麼     | 2013-09-21 12:30:18 |
| 1379 | MySQL基本SQL語句之常用管理SQL                                           | 2013-09-21 12:39:23 |
+------+----------------------------------------------------------------+---------------------+
7 rows in set (0.01 sec)

對查詢結果排序：
ORDER BY field_name {ASC|DESC}
如下：

 代碼如下 復制代碼 mysql> select ID,post_title,post_date from wp_posts where ID>1290 and post_status='publish' ORDER by ID; 

  ##升序,ID是排序的字段

 代碼如下 復制代碼 mysql> select ID,post_title,post_date from wp_posts where ID>1290 and post_status='publish' ORDER by ID DESC; 

##降序
字段別名：AS
select col_name AS COL_Aliases … ：對字段使用別名

select col_name,… from tb_name AS tb_Aliases …  ：對表使用別名
如下：

mysql> select post_title AS 文章標題 from wp_posts where ID>1290 and post_status='publish';
+----------------------------------------------------------------+
| 文章標題                                                                                    |
+----------------------------------------------------------------+
| 【轉】HP 3PAR存儲概念之三                                                      |
| 【轉】HP 3PAR存儲概念之四                                                      |
| 【轉】XenDesktop 5.5+vSphere 5創建虛擬機報錯                      |
| linux下強大的網絡工具Netcat                                                     |
| MySQL常用命令、技巧和注意事項                                              |
| 【轉】數據庫設計原理知識--B樹、B-樹、B+樹、B*樹都是什麼     |
| MySQL基本SQL語句之常用管理SQL                                           |
+----------------------------------------------------------------+
7 rows in set (0.02 sec)

##還可以這樣:

 代碼如下 復制代碼

mysql> select 3+2 AS SUM;
+-----+
| SUM |
+-----+
|   5    |
+-----+
1 row in set (0.00 sec)
LIMIT子句：LIMIT [offset,]Count
如下：

mysql> select ID,post_title,post_date from wp_posts where ID>1290 and post_status='publish' limit 5;
+------+--------------------------------------------------+---------------------+
| ID      | post_title                                                                | post_date               |
+------+--------------------------------------------------+---------------------+
| 1291 | 【轉】HP 3PAR存儲概念之三                                 | 2013-08-29 17:21:27 |
| 1298 | 【轉】HP 3PAR存儲概念之四                                 | 2013-08-29 17:22:33 |
| 1351 | 【轉】XenDesktop 5.5+vSphere 5創建虛擬機報錯  | 2013-09-04 17:41:26 |
| 1357 | linux下強大的網絡工具Netcat                                 | 2013-09-09 22:26:45 |
| 1360 | MySQL常用命令、技巧和注意事項                          | 2013-09-20 11:04:15 |
+------+--------------------------------------------------+---------------------+
5 rows in set (0.01 sec)

mysql> select ID,post_title,post_date from wp_posts where ID>1290 and post_status='publish' limit 2,3;  ##紅色部分（逗號前的數字）表示偏移量
+------+--------------------------------------------------+---------------------+
| ID      | post_title                                                              | post_date                 |
+------+--------------------------------------------------+---------------------+
| 1351 | 【轉】XenDesktop 5.5+vSphere 5創建虛擬機報錯  | 2013-09-04 17:41:26 |
| 1357 | linux下強大的網絡工具Netcat                                 | 2013-09-09 22:26:45 |
| 1360 | MySQL常用命令、技巧和注意事項                          | 2013-09-20 11:04:15 |
+------+--------------------------------------------------+---------------------+
3 rows in set (0.00 sec)
聚合：SUM(), MIN(), MAX(), AVG(), COUNT()，括號中為字段名
mysql> select sum(ID) from wp_posts;計算和
mysql> select min(ID) from wp_posts;查早最小的
mysql> select max(ID) from wp_posts; 查找最大的
mysql> select avg(ID) from wp_posts; 平均值
mysql> select count(ID) from wp_posts;計數
分組：GROUP BY，一般配合聚合運算使用
如下：

mysql> select count(post_status) AS 各狀態數量,post_status AS 狀態名稱 from wp_posts group by post_status;
+-----------------+--------------+
| 各狀態數量           | 狀態名稱        |
+-----------------+--------------+
|               1           | auto-draft     |
|               9           | draft             |
|             251         | inherit           |
|             238         | publish          |
|               2           | trash             |
+-----------------+--------------+
5 rows in set (0.01 sec)
注意：可以使用HAVING qualification將GROUP BY的結果再次過濾，用法同where

 

二、多表查詢
連接：
交叉連接：笛卡爾乘積
自然連接：將兩張表某字段中相等連接起來，如下

 代碼如下 復制代碼 mysql> SELECT students.Name,students.Age,courses.Cname,students.Gender FROM students,courses WHERE students.CID1 = courses.CID;
+--------------+------+------------------+--------+
| Name             | Age   | Cname                 | Gender |
+--------------+------+------------------+--------+
| GuoJing          |   19   | TaiJiquan              | M        |
| YangGuo        |   17   | TaiJiquan              | M        |
| DingDian        |   25   | Qishangquan       | M         |
| HuFei             |   31   | Wanliduxing         | M         |
| HuangRong    |   16   | Qianzhuwandushou | F       |
| YueLingshang |   18   | Wanliduxing         | F          |
| ZhangWuji      |   20   | Hamagong           | M         |
| Xuzhu             |   26   | TaiJiquan              | M         |
+--------------+------+------------------+--------+
8 rows in set (0.00 sec)

    外連接：
左外連接：left_tb LEFT JOIN right_tb ON ...  ：以左表為標准

 代碼如下 復制代碼 mysql> SELECT s.Name,c.Cname FROM students AS s LEFT JOIN courses AS c ON s.CID1=c.CID;
+--------------+--------------------+
| Name             | Cname                    |
+--------------+--------------------+
| GuoJing          | TaiJiquan                 |
| YangGuo        | TaiJiquan                 |
| DingDian        | Qishangquan           |
| HuFei              | Wanliduxing            |
| HuangRong    | Qianzhuwandushou |
| YueLingshang | Wanliduxing            |
| ZhangWuji      | Hamagong              |
| Xuzhu             | TaiJiquan                 |
| LingHuchong  | NULL                      |
| YiLin               | NULL                      |
+--------------+--------------------+
10 rows in set (0.00 sec)

右外連接 : left_tb RIGHT JOIN right_tb ON ... ：以右表為標准

 代碼如下 復制代碼 mysql> SELECT s.Name,c.Cname FROM students AS s RIGHT JOIN courses AS c ON s.CID1=c.CID;
+--------------+--------------------+
| Name             | Cname                    |
+--------------+--------------------+
| GuoJing         | TaiJiquan                  |
| YangGuo       | TaiJiquan                  |
| DingDian       | Qishangquan            |
| HuFei             | Wanliduxing             |
| HuangRong    | Qianzhuwandushou |
| YueLingshang | Wanliduxing            |
| ZhangWuji     | Hamagong               |
| Xuzhu            | TaiJiquan                  |
| NULL             | Yiyangzhi                 |
| NULL             | Jinshejianfa              |
| NULL             | Qiankundanuoyi      |
| NULL             | Pixiejianfa                |
| NULL             | Jiuyinbaiguzhua       |
+--------------+--------------------+
13 rows in set (0.01 sec)

自連接：本表中不同字段間進行連接

 代碼如下 復制代碼

mysql> SELECT c.Name AS student,s.Name AS teacher FROM students AS c,students AS s WHERE c.TID=s.SID;
+-----------+-------------+
| student   | teacher     |
+-----------+-------------+
| GuoJing   | DingDian    |
| YangGuo   | GuoJing     |
| DingDian  | ZhangWuji   |
| HuFei     | HuangRong   |
| HuangRong | LingHuchong |
+-----------+-------------+
5 rows in set (0.02 sec)

注意：使用了別名
三、子查詢：一個查詢中嵌套另外一個查詢
如下：在students表中查詢年齡大於平均年齡的學生

 代碼如下 復制代碼 mysql> SELECT Name,Age FROM students WHERE Age > (SELECT AVG(Age) FROM students);
+-------------+------+
| Name           | Age    |
+-------------+------+
| DingDian      |   25 |
| HuFei           |   31 |
| Xuzhu           |   26 |
| LingHuchong |   22 |
+-------------+------+
4 rows in set (0.08 sec)

子查詢注意事項：

■比較操作中使用子查詢：子查詢只能返回單個值；
■IN(): 使用子查詢；
■在FROM中使用子查詢；
聯合查詢：UNION,將兩個查詢的結果合並

 代碼如下 復制代碼 mysql> (SELECT Name,Age FROM students) UNION (SELECT Tname,Age FROM tutors);
+--------------+------+
| Name             | Age    |
+--------------+------+
| GuoJing          |   19   |
| YangGuo        |   17   |
| DingDian        |   25   |
| HuFei              |   31   |
| HuangRong    |   16   |
| YueLingshang |   18   |
| ZhangWuji      |   20   |
| HuYidao          |   42  |
| NingZhongze  |   49   |
+--------------+------+
19 rows in set (0.00 sec)