MySQL 9.0 发行说明
如前一节所示,检索整个表很容易。只需从 SELECT
语句中省略 WHERE
子句即可。但通常情况下,您并不希望看到整个表,尤其是当表变得很大时。相反,您通常更感兴趣的是回答一个特定的问题,在这种情况下,您需要对所需信息指定一些约束条件。让我们以关于宠物的问题为例,看看一些选择查询是如何回答这些问题的。
您可以只选择表中的特定行。例如,如果您想验证对 Bowser 出生日期所做的更改,请像这样选择 Bowser 的记录:
Press CTRL+C to copymysql> 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
列:
Press CTRL+C to copymysql> 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 | +----------+-------+---------+------+------------+-------+
您可以组合条件,例如,查找雌性犬:
Press CTRL+C to copymysql> 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
运算符:
Press CTRL+C to copymysql> 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
。如果同时使用这两个运算符,最好使用括号明确指示条件的组合方式:
Press CTRL+C to copymysql> 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 | +-------+--------+---------+------+------------+-------+