3.3.4.2.選擇特殊行
3.3.4.2. 選擇特殊行
如上所示,檢索整個表是容易的。只需要從SELECT語句中刪掉WHERE子句。但是一般你不想看到整個表,特別地當表變得很大時。相反,你通常對回答一個具體的問題更感興趣,在這種情況下在你想要的信息上進行一些限制。讓我們看一些他們回答的有關你寵物的問題的選擇查詢。
可以從表中只選擇特定的行。例如,如果你想要驗證你對Bowser的生日所做的更改,按下述方法選擇Bowser的記錄:
mysql> SELECT * FROM pet WHERE name = 'Bowser';
+--------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+-------+---------+------+------------+------------+
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
+--------+-------+---------+------+------------+------------+
輸出證實正確的年份記錄為1989,而不是1979。
字符串比較時通常對大小些不敏感,因此你可以將名字指定為"bowser"、"BOWSER"等,查詢結果相同。
你可以在任何列上指定條件,不只僅僅是name。例如,如果你想要知道哪個動物在1998以后出生的,測試birth列:
mysql> SELECT * FROM pet WHERE birth > '1998-1-1';
+----------+-------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+-------+
| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |
| Puffball | Diane | hamster | f | 1999-03-30 | NULL |
+----------+-------+---------+------+------------+-------+
可以組合條件,例如,找出雌性的狗:
mysql> SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
上面的查詢使用AND邏輯操作符,也有一個OR操作符:
mysql> SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';
+----------+-------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+-------+
| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
| Slim | Benny | snake | m | 1996-04-29 | NULL |
+----------+-------+---------+------+------------+-------+
AND和OR可以混用,但AND比OR具有更高的優先級。如果你使用兩個操作符,使用圓括號指明如何對條件進行分組是一個好主意:
mysql> SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm')
-> OR (species = 'dog' AND sex = 'f');
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+