3.3.4.2选择特定的行
如前一节所示,很容易检索整个表格。只要省略声明中的WHERE
条款即可SELECT
。但通常你不想看到整个表格,特别是当它变大时。相反,您通常对回答特定问题更感兴趣,在这种情况下,您需要对所需信息指定一些限制。让我们看看有关您的宠物回答的问题。
您只能从表中选择特定的行。例如,如果您想验证您对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 |
+-------+--------+---------+------+------------+-------+
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 |
+-------+--------+---------+------+------------+-------+
本文由学什么网翻译