Buy this Reference Manual in softcover from Barnes & Noble!
Из таблицы можно выбрать и только нужные строки. Например, если вы хотите проверить правильность внесенных в дату рождения собаки 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, а не 1998.
В операции сравнения строк обычно не учитывается регистр символов, так что имя можно записать как "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 можно использовать совместно. В таком случае с помощью скобок можно указать порядок группировки условий:
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 |
+-------+--------+---------+------+------------+-------+
| Posted by on Monday July 29 2002, @8:38am | [Delete] [Edit] |
I don't think that this was mentioned specifically, and many
of you know it already, but :
In the Where clause, AND takes precedence over OR, which
means that
WHERE name='Smith' or salary>90000 and department=2
is equivalent to
WHERE name='Smith' OR (salary > ... AND depat...)
which will return anybody who has the name Smith and
anybody in department 2 with a salary greater than 90000.
| Posted by [name withheld] on Friday October 18 2002, @1:26pm | [Delete] [Edit] |
Actually, Nate la Montagne, this is incorrect.
The "order of operations" for logical operators in
mathematics and in PHP and most all other languages is:
NOT
OR
AND
XOR
As a math teacher, I tell my students to remember it with
this pneumonic: NOAh.
So, just remember "noah" and you will always remember:
Not, Or, And.
X not Y or Z and Q is the same as:
(((X not Y) or Z) and Q)
| Posted by glen herrmannsfeldt on Thursday October 24 2002, @10:25am | [Delete] [Edit] |
Fortran, C, Java, Awk, and Perl all have
precedence, from
higher to lower, of Not, And, Or. I hope soon I
will learn the
precedence order for MySQL. XOR is not so
commonly used,
so its precedence rule isn't well standardized.
| Posted by [name withheld] on Saturday November 30 2002, @7:01am | [Delete] [Edit] |
Nate is right, AND takes precedence over OR, e.g.
try: select * from pet where species='dog' and
sex='f' or owner='Gwen';
| Posted by Randy Chrismon on Thursday January 23 2003, @1:45pm | [Delete] [Edit] |
This is a fine kettle of fish! The bottom line is: "When in doubt, use parentheses!" Actually, even if you're sure, use parentheses anyway; the next person might not think the same way you do.
Add your own comment.