Like Share Discussion Bookmark Smile

J.J. Huang   2020-06-11   MySQL   瀏覽次數:

MySQL - 第四章 | 檢索(SELECT)資料

準備

準備資料在表裡面。

SQL:

1
2
3
4
5
6
7
8
9
INSERT INTO `testdb`.`users` (`relation_id`, `vendor_id`, `role_id`, `domain_id`, `level`, `admins`, `status`, `create_time`, `update_time`, `user_name`, `pwd`, `real_name`, `tel`, `mobile`, `email`, `creator`, `operator`) VALUES ('1', '1', '1', '1', '1', '1', '1', now(), now(), 'J.J.', '123123', 'J.J.', '0223345678', '0911123123', 'jj@test.com', 'SYSTEM', 'SYSTEM');

INSERT INTO `testdb`.`users` (`relation_id`, `vendor_id`, `role_id`, `domain_id`, `level`, `admins`, `status`, `create_time`, `update_time`, `user_name`, `pwd`, `real_name`, `tel`, `mobile`, `email`, `creator`, `operator`) VALUES ('2', '2', '2', '2', '2', '1', '1', now(), now(), 'K.K.', '123123', 'K.K.', '031234567', '0933321321', 'kk@test.com', 'SYSTEM', 'SYSTEM');

INSERT INTO `testdb`.`users` (`relation_id`, `vendor_id`, `role_id`, `domain_id`, `level`, `admins`, `status`, `create_time`, `update_time`, `user_name`, `pwd`, `real_name`, `tel`, `mobile`, `email`, `creator`, `operator`) VALUES ('2', '2', '2', '2', '2', '1', '1', now(), now(), 'L.L.', '123123', 'L.L.', '04123123123', '092233455', 'll@test.com', 'SYSTEM', 'SYSTEM');

INSERT INTO `testdb`.`users` (`relation_id`, `vendor_id`, `role_id`, `domain_id`, `level`, `admins`, `status`, `create_time`, `update_time`, `user_name`, `pwd`, `real_name`, `tel`, `mobile`, `email`, `creator`, `operator`) VALUES ('3', '3', '3', '3', '3', '1', '1', now(), now(), 'M.M.', '123123', 'M.M.', '0544433322', '0922334455', 'mm@test.com', 'SYSTEM', 'SYSTEM');

INSERT INTO `testdb`.`users` (`relation_id`, `vendor_id`, `role_id`, `domain_id`, `level`, `admins`, `status`, `create_time`, `update_time`, `user_name`, `pwd`, `real_name`, `tel`, `mobile`, `email`, `creator`, `operator`) VALUES ('3', '3', '3', '3', '3', '1', '1', now(), now(), 'N.N.', '123123', 'N.N.', '06213213', '0944555566', 'nn@test.com', 'SYSTEM', 'SYSTEM');

執行:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> INSERT INTO `testdb`.`users` (`relation_id`, `vendor_id`, `role_id`, `domain_id`, `level`, `admins`, `status`, `create_time`, `update_time`, `user_name`, `pwd`, `real_name`, `tel`, `mobile`, `email`, `creator`, `operator`) VALUES ('1', '1', '1', '1', '1', '1', '1', now(), now(), 'J.J.', '123123', 'J.J.', '0223345678', '0911123123', 'jj@test.com', 'SYSTEM', 'SYSTEM');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> INSERT INTO `testdb`.`users` (`relation_id`, `vendor_id`, `role_id`, `domain_id`, `level`, `admins`, `status`, `create_time`, `update_time`, `user_name`, `pwd`, `real_name`, `tel`, `mobile`, `email`, `creator`, `operator`) VALUES ('2', '2', '2', '2', '2', '1', '1', now(), now(), 'K.K.', '123123', 'K.K.', '031234567', '0933321321', 'kk@test.com', 'SYSTEM', 'SYSTEM');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `testdb`.`users` (`relation_id`, `vendor_id`, `role_id`, `domain_id`, `level`, `admins`, `status`, `create_time`, `update_time`, `user_name`, `pwd`, `real_name`, `tel`, `mobile`, `email`, `creator`, `operator`) VALUES ('2', '2', '2', '2', '2', '1', '1', now(), now(), 'L.L.', '123123', 'L.L.', '04123123123', '092233455', 'll@test.com', 'SYSTEM', 'SYSTEM');
Query OK, 1 row affected (0.03 sec)

mysql>
mysql> INSERT INTO `testdb`.`users` (`relation_id`, `vendor_id`, `role_id`, `domain_id`, `level`, `admins`, `status`, `create_time`, `update_time`, `user_name`, `pwd`, `real_name`, `tel`, `mobile`, `email`, `creator`, `operator`) VALUES ('3', '3', '3', '3', '3', '1', '1', now(), now(), 'M.M.', '123123', 'M.M.', '0544433322', '0922334455', 'mm@test.com', 'SYSTEM', 'SYSTEM');
Query OK, 1 row affected (0.01 sec)

mysql>
mysql> INSERT INTO `testdb`.`users` (`relation_id`, `vendor_id`, `role_id`, `domain_id`, `level`, `admins`, `status`, `create_time`, `update_time`, `user_name`, `pwd`, `real_name`, `tel`, `mobile`, `email`, `creator`, `operator`) VALUES ('3', '3', '3', '3', '3', '1', '1', now(), now(), 'N.N.', '123123', 'N.N.', '06213213', '0944555566', 'nn@test.com', 'SYSTEM', 'SYSTEM');
Query OK, 1 row affected (0.01 sec)

SELECT語句

SQL語句是由簡單的英語單詞構成的。這些單詞稱為關鍵字,每個SQL語句都是由一個或多個關鍵字構成的。
最經常使用的SQL語句就是SELECT語句了。它的用途是從一個或多個表中檢索訊息。
為了使用SELECT檢索表資料,必須至少給出兩條訊息——想選擇什麼,以及從什麼地方選擇。

檢索單個列

簡單的SQL SELECT語句,此語句如下所示:

1
2
3
4
5
6
7
8
9
10
11
12
mysql> select real_name from users;
+-----------+
| real_name |
+-----------+
| J.J. |
| K.K. |
| L.L. |
| M.M. |
| N.N. |
+-----------+
5 rows in set (0.00 sec)
2 rows in set (0.00 sec)

分析:上述語句利用SELECT語句從users表中檢索一個名為real_name的列。所需的列名在SELECT關鍵字之後給出,FROM關鍵字指出從其中檢索資料的表名。

說明:未排序資料 如果自己試驗這個查詢,可能會發現顯示輸出的資料順序與這裡的不同。出現這種情況很正常。如果沒有明確排序查詢結果,則返回的資料的順序沒有特殊意義。返回資料的順序可能是資料被添加到表中的順序,也可能不是。只要返回相同數目的行,就是正常的。

說明:結束SQL語句 多條SQL語句必須以分號(;)分隔。MySQL如同多數DBMS一樣,不需要在單條SQL語句後加分號。但特定的DBMS可能必須在單條SQL語句後加上分號。當然,如果願意可以總是加上分號。事實上,即使不一定需要,但加上分號肯定沒有壞處。如果你使用的是mysql指令行,必須加上分號來結束SQL語句。

說明:SQL語句和大小寫 請注意,SQL語句不區分大小寫,因此SELECT與select是相同的。同樣,寫成Select也沒有關係。許多SQL開發人員喜歡對所有SQL關鍵字使用大寫,而對所有列和表名使用小寫,這樣做使程式碼更易於閱讀和調試。不過,一定要認識到雖然SQL是不區分大小寫的,但有些標識符(如資料庫名、表名、列名)可能不同:在MySQL 4.1及之前的版本中,這些標識符默認是區分大小寫的;在MySQL 4.1.1 版本中,這些標識符默認是不區分大小寫的。最佳方式是按照大小寫的慣例,且使用時保持一致。

提示:使用空格 在處理SQL語句時,其中所有空格都被忽略。SQL語句可以在一行上給出,也可以分成許多行。多數SQL開發人員認為將SQL語句分成多行更容易閱讀和調試。

檢索多個列

要想從一個表中檢索多個列,使用相同的SELECT語句。唯一的不同是必須在SELECT關鍵字後給出多個列名,列名之間必須以逗號分隔。

1
2
3
4
5
6
7
8
9
10
11
mysql> select real_name, email from users;
+-----------+-------------+
| real_name | email |
+-----------+-------------+
| J.J. | jj@test.com |
| K.K. | kk@test.com |
| L.L. | ll@test.com |
| M.M. | mm@test.com |
| N.N. | nn@test.com |
+-----------+-------------+
5 rows in set (0.00 sec)

分析:與前一個例子一樣,這條語句使用SELECT語句從表users中選擇資料。在這個例子中,指定了2個列名,列名之間用逗號分隔。

提示:當心逗號 在選擇多個列時,一定要在列名之間加上逗號,但最後一個列名後不加。如果在最後一個列名後加了逗號,將出現錯誤。

說明:資料表示 SQL語句一般返回原始的、無格式的資料。資料的格式化是一個表示問題,而不是一個檢索問題。因此,表示(對齊和顯示上面的價格值,用貨幣符號和逗號表示其金額)一般在顯示該資料的應用程序中規定。一般很少使用實際檢索出的原始資料(沒有應用程序提供的格式)。

檢索所有列

除了指定所需的列外(如上所述,一個或多個列),SELECT語句還可以檢索所有的列而不必逐個列出它們。這可以通過在實際列名的位置使用星號(*)萬用字元來達到,如下所示:

1
2
3
4
5
6
7
8
9
10
11
mysql> select * from users;
+---------+-------------+-----------+---------+-----------+-------+--------+--------+---------------------+---------------------+-----------+--------+-------+-----------+-------------+------------+-------------+---------+----------+
| user_id | relation_id | vendor_id | role_id | domain_id | level | admins | status | create_time | update_time | user_name | pwd | token | real_name | tel | mobile | email | creator | operator |
+---------+-------------+-----------+---------+-----------+-------+--------+--------+---------------------+---------------------+-----------+--------+-------+-----------+-------------+------------+-------------+---------+----------+
| 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2020-06-11 09:48:30 | 2020-06-11 09:48:30 | J.J. | 123123 | | J.J. | 0223345678 | 0911123123 | jj@test.com | SYSTEM | SYSTEM |
| 2 | 2 | 2 | 2 | 2 | 2 | 1 | 1 | 2020-06-11 09:48:32 | 2020-06-11 09:48:32 | K.K. | 123123 | | K.K. | 031234567 | 0933321321 | kk@test.com | SYSTEM | SYSTEM |
| 5 | 2 | 2 | 2 | 2 | 2 | 1 | 1 | 2020-06-12 00:52:48 | 2020-06-12 00:52:48 | L.L. | 123123 | | L.L. | 04123123123 | 092233455 | ll@test.com | SYSTEM | SYSTEM |
| 6 | 3 | 3 | 3 | 3 | 3 | 1 | 1 | 2020-06-12 00:52:48 | 2020-06-12 00:52:48 | M.M. | 123123 | | M.M. | 0544433322 | 0922334455 | mm@test.com | SYSTEM | SYSTEM |
| 7 | 3 | 3 | 3 | 3 | 3 | 1 | 1 | 2020-06-12 00:52:50 | 2020-06-12 00:52:50 | N.N. | 123123 | | N.N. | 06213213 | 0944555566 | nn@test.com | SYSTEM | SYSTEM |
+---------+-------------+-----------+---------+-----------+-------+--------+--------+---------------------+---------------------+-----------+--------+-------+-----------+-------------+------------+-------------+---------+----------+
5 rows in set (0.00 sec)

分析:如果給定一個萬用字元(*),則返回表中所有列。列的順序一般是列在表定義中出現的順序。但有時候並不是這樣的,表的模式的變化(如添加或刪除列)可能會導致順序的變化。

注意:使用萬用字元 一般,除非你確實需要表中的每個列,否則最好別使用*萬用字元。雖然使用萬用字元可能會使你自己省事,不用明確列出所需列,但檢索不需要的列通常會降低檢索和應用程序的性能。

提示:檢索未知列 使用萬用字元有一個大優點。由於不明確指定列 名(因為星號檢索每個列),所以能檢索出名字未知的列。

檢索不同的行

正如所見,SELECT返回所有匹配的行。但是,如果你不想要每個值每次都出現,怎麼辦?例如,假如你想得出users表中的level:

1
2
3
4
5
6
7
8
9
10
11
mysql> select level from users;
+-------+
| level |
+-------+
| 1 |
| 2 |
| 2 |
| 3 |
| 3 |
+-------+
5 rows in set (0.00 sec)

SELECT語句返回5行(即使表中只有3個等級),因為users表 中列出了5筆資料。那麼,如何檢索出有不同值的列表呢?
解決辦法是使用DISTINCT關鍵字,顧名思義,此關鍵字指示MySQL 只返回不同的值。

1
2
3
4
5
6
7
8
9
mysql> select DISTINCT level from users;
+-------+
| level |
+-------+
| 1 |
| 2 |
| 3 |
+-------+
3 rows in set (0.01 sec)

分析:select DISTINCT level告訴MySQL只返回不同(唯一)的level行,因此只返回3行。如果使用DISTINCT關鍵字,它必須直接放在列名的前面。

注意:不能部分使用DISTINC DISTINCT關鍵字應用於所有列而 不僅是前置它的列。如果給出SELECT DISTINCT level, real_name,除非指定的兩個列都不同,否則所有行都將被檢索出來。

限制結果

SELECT語句返回所有匹配的行,它們可能是指定表中的每個行。為了返回第一行或前幾行,可使用LIMIT子句。

1
2
3
4
5
6
7
8
mysql> select real_name from users limit 2;
+-----------+
| real_name |
+-----------+
| J.J. |
| K.K. |
+-----------+
2 rows in set (0.00 sec)

分析:此語句使用SELECT語句檢索單個列。 LIMIT 2表示MySQL返回不多於2行。

為得出下一個2行,可指定要檢索的開始行和行數。

1
2
3
4
5
6
7
8
mysql> select real_name from users limit 2, 2;
+-----------+
| real_name |
+-----------+
| L.L. |
| M.M. |
+-----------+
2 rows in set (0.00 sec)

分析:LIMIT 2, 2指示MySQL返回從行2開始的2行。第一個數為開始位置,第二個數為要檢索的行數。

所以,帶一個值的LIMIT總是從第一行開始,給出的數為返回的行數。帶兩個值的LIMIT可以指定從行號為第一個值的位置開始。

注意:行0 檢索出來的第一行為行0而不是行1。因此,LIMIT1,1 將檢索出第二行而不是第一行。

說明:在行數不夠時LIMIT中指定要檢索的行數為檢索的最大行數。如果沒有足夠的行(例如,給出LIMIT 10, 5,但只有13行),MySQL將只返回它能返回的那麼多行。

提示:MySQL 5的LIMIT語法LIMIT 3, 4的含義是從行4開始的3行還是從行3開始的4行? 如前所述,它的意思是從行3開始的4行,這容易把人搞糊塗。由於這個原因,MySQL 5支持LIMIT的另一種替代語法。LIMIT 4 OFFSET 3意為從行3開始取4行,就像LIMIT 3, 4一樣。

使用完全限定的表名

迄今為止使用的SQL例子只通過列名引用列。也可能會使用完全限定的名字來引用列(同時使用表名和列字)。

這定了一個完全限定的列名。

1
2
3
4
5
6
7
8
9
10
11
mysql> select users.real_name from users;
+-----------+
| real_name |
+-----------+
| J.J. |
| K.K. |
| L.L. |
| M.M. |
| N.N. |
+-----------+
5 rows in set (0.00 sec)

另外表名也可以是完全限定的。

1
2
3
4
5
6
7
8
9
10
11
mysql> select users.real_name from testdb.users;
+-----------+
| real_name |
+-----------+
| J.J. |
| K.K. |
| L.L. |
| M.M. |
| N.N. |
+-----------+
5 rows in set (0.00 sec)

結語

這邊的語法基本上都還滿熟的,我想這篇最重要的應該放在LIMIT語法的使用,常常在專案上最常使用的就是分頁功能的實現;另外完全限定的使用,比較會用在多表檢索或是比較複雜的SQL時就必須會用到,這邊先做了解加深一下印象!

註:以上參考了
MySQL必知必会 MySQL Crash Course