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子句:布爾關系表達式，主要包含如下這幾類表達式:

1. 比較：=、>、>=、<=、< 
2. 邏輯關系： 
3. AND 
4. OR 
5. NOT 
6.  
7. BETWEEN ... AND ...   ：在兩個值之間 
8. LIKE ‘’ 
9. %: 任意長度任意字符 
10. _：任意單個字符 
11. REGEXP, RLIKE :正則表達式，此時索引無效 
12. IN 
13. IS NULL 
14. IS NOT NULL 

如下查詢本博客的wp-links和wp_posts表,代碼如下:

1. mysql> select * from wp_links; 查詢全部 mysql> select link_name,link_url from wp_links; 投影 
2. +-------------------+--------------------------------------+ 
3. | link_name               | link_url                                              | 
4. +-------------------+--------------------------------------+ 
5. | 旺旺騰訊微博          | http://m.survivalescaperooms.com              | 
6. | 旺旺新浪微博          | http://weibo.com/gz100ww               | 
7. | 51CTO技術博客      | http://m.survivalescaperooms.com/ | 
8. +-------------------+--------------------------------------+ 
9. 10 rows in set (0.00 sec) 
10.  
11. mysql> select ID,post_title,post_date from wp_posts where ID>1290 and post_status='publish';選擇 
12. +------+----------------------------------------------------------------+----------------------+ 
13. | ID   | post_title                                                                                      | post_date                   | 
14. +------+----------------------------------------------------------------+----------------------+ 
15. | 1291 | 【轉】HP 3PAR存儲概念之三                                                      | 2013-08-29 17:21:27 | 
16. | 1298 | 【轉】HP 3PAR存儲概念之四                                                      | 2013-08-29 17:22:33 | 
17. | 1351 | 【轉】XenDesktop 5.5+vSphere 5創建虛擬機報錯                      | 2013-09-04 17:41:26 | 
18. | 1357 | linux下強大的網絡工具Netcat                                                     | 2013-09-09 22:26:45 | 
19. | 1360 | MySQL常用命令、技巧和注意事項                                              | 2013-09-20 11:04:15 | 
20. | 1369 | 【轉】數據庫設計原理知識--B樹、B-樹、B+樹、B*樹都是什么     | 2013-09-21 12:30:18 | 
21. | 1379 | MySQL基本SQL語句之常用管理SQL                                           | 2013-09-21 12:39:23 | 
22. +------+----------------------------------------------------------------+---------------------+ 
23. 7 rows in set (0.01 sec) 

對查詢結果排序:ORDER BY field_name {ASC|DESC},代碼如下:

1. mysql> select ID,post_title,post_date from wp_posts where ID>1290 and post_status='publish' ORDER by ID;   
2.  
3.   ##升序,ID是排序的字段 
4.  
5. mysql> select ID,post_title,post_date from wp_posts where ID>1290 and post_status='publish' ORDER by ID DESC;    
6.  
7. ##降序 
8. 字段別名：AS 
9. select col_name AS COL_Aliases … ：對字段使用別名 

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

1. mysql> select post_title AS 文章標題 from wp_posts where ID>1290 and post_status='publish'; 
2. +----------------------------------------------------------------+ 
3. | 文章標題                                                                                    | 
4. +----------------------------------------------------------------+ 
5. | 【轉】HP 3PAR存儲概念之三                                                      | 
6. | 【轉】HP 3PAR存儲概念之四                                                      | 
7. | 【轉】XenDesktop 5.5+vSphere 5創建虛擬機報錯                      | 
8. | linux下強大的網絡工具Netcat                                                     | 
9. | MySQL常用命令、技巧和注意事項                                              | 
10. | 【轉】數據庫設計原理知識--B樹、B-樹、B+樹、B*樹都是什么     | 
11. | MySQL基本SQL語句之常用管理SQL                                           | 
12. +----------------------------------------------------------------+ 
13. 7 rows in set (0.02 sec) 
14.  
15. ##還可以這樣: 
16.  
17.  代碼如下 復制代碼  
18. mysql> select 3+2 AS SUM; 
19. +-----+ 
20. | SUM | 
21. +-----+ 
22. |   5    | 
23. +-----+ 
24. 1 row in set (0.00 sec) 

LIMIT子句:LIMIT [offset,]Count,代碼如下:

1. mysql> select ID,post_title,post_date from wp_posts where ID>1290 and post_status='publish' limit 5; 
2. +------+--------------------------------------------------+---------------------+ 
3. | ID      | post_title                                                                | post_date               | 
4. +------+--------------------------------------------------+---------------------+ 
5. | 1291 | 【轉】HP 3PAR存儲概念之三                                 | 2013-08-29 17:21:27 | 
6. | 1298 | 【轉】HP 3PAR存儲概念之四                                 | 2013-08-29 17:22:33 | 
7. | 1351 | 【轉】XenDesktop 5.5+vSphere 5創建虛擬機報錯  | 2013-09-04 17:41:26 | 
8. | 1357 | linux下強大的網絡工具Netcat                                 | 2013-09-09 22:26:45 | 
9. | 1360 | MySQL常用命令、技巧和注意事項                          | 2013-09-20 11:04:15 | 
10. +------+--------------------------------------------------+---------------------+ 
11. 5 rows in set (0.01 sec) 
12.  
13. mysql> select ID,post_title,post_date from wp_posts where ID>1290 and post_status='publish' limit 2,3;  ##紅色部分（逗號前的數字）表示偏移量 
14. +------+--------------------------------------------------+---------------------+ 
15. | ID      | post_title                                                              | post_date                 | 
16. +------+--------------------------------------------------+---------------------+ 
17. | 1351 | 【轉】XenDesktop 5.5+vSphere 5創建虛擬機報錯  | 2013-09-04 17:41:26 | 
18. | 1357 | linux下強大的網絡工具Netcat                                 | 2013-09-09 22:26:45 | 
19. | 1360 | MySQL常用命令、技巧和注意事項                          | 2013-09-20 11:04:15 | 
20. +------+--------------------------------------------------+---------------------+ 
21. 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,一般配合聚合運算使用,如下:

1. mysql> select count(post_status) AS 各狀態數量,post_status AS 狀態名稱 from wp_posts group by post_status; 
2. +-----------------+--------------+ 
3. | 各狀態數量           | 狀態名稱        | 
4. +-----------------+--------------+ 
5. |               1           | auto-draft     | 
6. |               9           | draft             | 
7. |             251         | inherit           | 
8. |             238         | publish          | 
9. |               2           | trash             | 
10. +-----------------+--------------+ 
11. 5 rows in set (0.01 sec) 

注意:可以使用HAVING qualification將GROUP BY的結果再次過濾,用法同where.

二、多表查詢

連接:

交叉連接:笛卡爾乘積

自然連接:將兩張表某字段中相等連接起來,代碼如下:

1. mysql> SELECT students.Name,students.Age,courses.Cname,students.Gender FROM students,courses WHERE students.CID1 = courses.CID; 
2. +--------------+------+------------------+--------+ 
3. | Name             | Age   | Cname                 | Gender | 
4. +--------------+------+------------------+--------+ 
5. | GuoJing          |   19   | TaiJiquan              | M        | 
6. | YangGuo        |   17   | TaiJiquan              | M        | 
7. | DingDian        |   25   | Qishangquan       | M         | 
8. | HuFei             |   31   | Wanliduxing         | M         | 
9. | HuangRong    |   16   | Qianzhuwandushou | F       | 
10. | YueLingshang |   18   | Wanliduxing         | F          | 
11. | ZhangWuji      |   20   | Hamagong           | M         | 
12. | Xuzhu             |   26   | TaiJiquan              | M         | 
13. +--------------+------+------------------+--------+ 
14. 8 rows in set (0.00 sec) 

外連接:

左外連接:left_tb LEFT JOIN right_tb ON ...:以左表為標準,代碼如下:

1. mysql> SELECT s.Name,c.Cname FROM students AS s LEFT JOIN courses AS c ON s.CID1=c.CID; 
2. +--------------+--------------------+ 
3. | Name             | Cname                    | 
4. +--------------+--------------------+ 
5. | GuoJing          | TaiJiquan                 | 
6. | YangGuo        | TaiJiquan                 | 
7. | DingDian        | Qishangquan           | 
8. | HuFei              | Wanliduxing            | 
9. | HuangRong    | Qianzhuwandushou | 
10. | YueLingshang | Wanliduxing            | 
11. | ZhangWuji      | Hamagong              | 
12. | Xuzhu             | TaiJiquan                 | 
13. | LingHuchong  | NULL                      | 
14. | YiLin               | NULL                      | 
15. +--------------+--------------------+ 
16. 10 rows in set (0.00 sec) 

右外連接:left_tb RIGHT JOIN right_tb ON ...:以右表為標準,代碼如下:

1. mysql> SELECT s.Name,c.Cname FROM students AS s RIGHT JOIN courses AS c ON s.CID1=c.CID; 
2. +--------------+--------------------+ 
3. | Name             | Cname                    | 
4. +--------------+--------------------+ 
5. | GuoJing         | TaiJiquan                  | 
6. | YangGuo       | TaiJiquan                  | 
7. | DingDian       | Qishangquan            | 
8. | HuFei             | Wanliduxing             | 
9. | HuangRong    | Qianzhuwandushou | 
10. | YueLingshang | Wanliduxing            | 
11. | ZhangWuji     | Hamagong               | 
12. | Xuzhu            | TaiJiquan                  | 
13. | NULL             | Yiyangzhi                 | 
14. | NULL             | Jinshejianfa              | 
15. | NULL             | Qiankundanuoyi      | 
16. | NULL             | Pixiejianfa                | 
17. | NULL             | Jiuyinbaiguzhua       | 
18. +--------------+--------------------+ 
19. 13 rows in set (0.01 sec) 

自連接:本表中不同字段間進行連接,代碼如下:

1. mysql> SELECT c.Name AS student,s.Name AS teacher FROM students AS c,students AS s WHERE c.TID=s.SID; 
2. +-----------+-------------+ 
3. | student   | teacher     | 
4. +-----------+-------------+ 
5. | GuoJing   | DingDian    | 
6. | YangGuo   | GuoJing     | 
7. | DingDian  | ZhangWuji   | 
8. | HuFei     | HuangRong   | 
9. | HuangRong | LingHuchong | 
10. +-----------+-------------+ 
11. 5 rows in set (0.02 sec) 

注意:使用了別名

三、子查詢:一個查詢中嵌套另外一個查詢

如下,在students表中查詢年齡大于平均年齡的學生,代碼如下:

1. mysql> SELECT Name,Age FROM students WHERE Age > (SELECT AVG(Age) FROM students); 
2. +-------------+------+ 
3. | Name           | Age    | 
4. +-------------+------+ 
5. | DingDian      |   25 | 
6. | HuFei           |   31 | 
7. | Xuzhu           |   26 | 
8. | LingHuchong |   22 | 
9. +-------------+------+ 
10. 4 rows in set (0.08 sec) 

子查詢注意事項:

■比較操作中使用子查詢:子查詢只能返回單個值；

■IN():使用子查詢；

■在FROM中使用子查詢；

聯合查詢:UNION,將兩個查詢的結果合并,代碼如下:

1. mysql> (SELECT Name,Age FROM students) UNION (SELECT Tname,Age FROM tutors); 
2. +--------------+------+ 
3. | Name             | Age    | 
4. +--------------+------+ 
5. | GuoJing          |   19   | 
6. | YangGuo        |   17   | 
7. | DingDian        |   25   | 
8. | HuFei              |   31   | 
9. | HuangRong    |   16   | 
10. | YueLingshang |   18   | 
11. | ZhangWuji      |   20   | 
12. | HuYidao          |   42  | 
13. | NingZhongze  |   49   | 
14. +--------------+------+ 
15. 19 rows in set (0.00 sec) 
16. //開源代碼Vevb.com