Руководство по MySQL

Главы

Использование MySQL совместно с Apache

Существуют программы, позволяющие
проводить идентификацию
пользователей с помощью базы данных
MySQL, а также записывать журналы в
таблицу MySQL.

Формат записи журналов Apache можно
привести в легко понятную MySQL форму,
введя в файл настроек Apache следующие
строки:

LogFormat \ "\"%h\",%{%Y%m%d%H%M%S}t,%>s,\"%b\",\"%{Content-Type}o\", \ \"%U\",\"%{Referer}i\",\"%{User-Agent}i\""

В MySQL же можно сделать примерно
следующее:

LOAD DATA INFILE '/local/access_log' INTO TABLE table_name
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'

Создание и использование базы данных

3.3.1. Создание и выбор базы данных

3.3.2. Создание таблицы

3.3.3. Загрузка данных в таблицу

3.3.4. Выборка информации из таблицы

Теперь, когда вы знаете, как вводить
команды, пора начать работу с базой
данных.

Предположим, у вас дома (или в
вольере) живет несколько домашних
любимцев и вы хотите создать
систему для хранения разнообразной
информации о своем живом уголке. Для
того чтобы это сделать, нужно просто
создать таблицы и записать в них
информацию. После этого любые
данные можно будет получить,
извлекая данные из таблиц. В этом
разделе вы научитесь:

• создавать базу данных

• создавать таблицу

• записывать в таблицу данные

• извлекать данные из таблицы
  различными способами

• работать с несколькими таблицами
  сразу

Наша база данных будет чрезвычайно
проста (это мы сделали намеренно), но
совсем несложно придумать реальные
задачи, решение которых потребовало
бы применения подобной базы данных.
Такая база, например, может
пригодиться скотоводу для хранения
информации о поголовье или
ветеринару — для учета пациентов.
Готовая база данных menagerie,
в которой содержатся некоторые из
запросов и данные из приведенных
ниже разделов, находится на web-сайте
MySQL. Ее можно загрузить в виде
сжатого tar-файла
(http://downloads.mysql.com/docs/menagerie-db.tar.gz)
или Zip
(http://downloads.mysql.com/docs/menagerie-db.zip).

Узнать, какие базы существуют в
настоящее время на сервере, можно
при помощи команды SHOW:

mysql> SHOW DATABASES;+----------+
| Database |
+----------+
| mysql |
| test |
| tmp |
+----------+

На вашем компьютере список баз,
вероятно, будет другим, но в нем все
равно, скорее всего, будут
присутствовать базы mysql и
test. База данных
mysql просто необходима, так
как в ней она описываются
пользовательские права доступа.
База test часто применяется
для экспериментов.

Впрочем, всех баз вы можете и не
увидеть, если у вас нет привилегии
SHOW DATABASES. See Раздел 4.3.1, «Синтаксис команд GRANT и
REVOKE».

Если база данных test
существует, попробуйте обратиться к
ней:

mysql> USE testDatabase changed

В команде USE, как и
QUIT, точка с запятой не
нужна (конечно, данные команды тоже
можно завершать точкой с запятой —
никакого вреда от этого не будет).
Команда USE отличается от
остальных и кое-чем еще: она должна
задаваться одной строкой.

Базу данных test (если,
конечно, у вас есть доступ к ней)
можно использовать для работы с
приведенными ниже примерами, но все
созданное в ней может быть
уничтожено любым другим
пользователем, имеющим к ней доступ.
Поэтому вам лучше попросить у
своего администратора MySQL
разрешение на создание собственной
базы. Предположим, вы захотите
назвать ее menagerie («зверинец»). В
таким случае администратору нужно
будет набрать примерно такую
команду:

mysql> GRANT ALL ON menagerie.* TO your_mysql_name;

где your_mysql_name — присвоенное вам имя
MySQL.

3.3.1. Создание и выбор базы данных

Если администратор при выдаче
разрешения создаст для вас базу, с
ней можно сразу начинать работу. В
противном случае вам придется
создать ее самостоятельно:

mysql> CREATE DATABASE menagerie;

В Unix имеет значение регистр
символов в именах баз данных (в
отличие от ключевых слов SQL), так
что в этой ОС вам всегда придется
называть свою базу menagerie,
а не Menagerie,
MENAGERIE или еще как-нибудь.
Это же правило распространяется и
на имена таблиц (в Windows данное
ограничение не действует, однако
при обращении к базам и таблицам в
пределах одного запроса, тем не
менее, можно использовать только
один регистр).

При создании базы данных она
автоматически не выбирается;
выбирать ее нужно отдельно.
Сделать menagerie текущей базой можно с
помощью следующей команды:

mysql> USE menagerieDatabase changed

Создавать базу нужно только
однажды, но выбирать ее приходится
в каждом сеансе работы с
mysql. Делать это можно с
помощью команды USE, представленной
выше. А можно выбирать базу и из
командной строки при запуске
mysql. Для этого достаточно
лишь ввести ее имя после
параметров соединения, которые
нужно вводить в любом случае.
Например:

shell> mysql -h host -u user -p menagerieEnter password: ********

Обратите внимание: в
вышеприведенной команде
menagerie не является
паролем. Ввести пароль в командной
строке после параметра -p можно без
пробела (например,
-pmypassword, а не -p
mypassword). Впрочем, пароль в
командной строке все равно лучше
не вводить, так как таким образом
его могут и подсмотреть.

3.3.2. Создание таблицы

Как вы уже успели убедиться,
создать базу данных было просто.
Однако пока что в ней ничего нет — в
этом можно удостовериться при
помощи команды SHOW TABLES:

mysql> SHOW TABLES;Empty set (0.00 sec)

Гораздо труднее определиться со
структурой своей базы, т.е. с тем,
какие могут понадобиться таблицы,
и какие столбцы должны содержаться
в каждой из них.

Вам обязательно будет нужна
таблица, содержащая по записи на
каждое из животных. Назвать ее
можно pet, и храниться в
ней будут, как минимум, имена. Но
так как само по себе имя
неинформативно, в таблице должны
будут присутствовать и другие
данные. Например, если домашние
животные есть более чем у одного
члена вашей семьи, в таблицу можно
добавить и имя владельца каждого
животного. Кроме того, в базу стоит
внести и описательную информацию —
например, вид и пол животного.

Но вот как быть с возрастом? Эта
информация тоже может оказаться
полезной, но хранить такие данные в
базе неудобно. Возраст со временем
меняется, а это значит, что
придется довольно часто обновлять
записи. Значительно удобнее
хранить фиксированные значения —
например, даты рождения. В таком
случае возраст всегда можно
получить, вычислив разницу между
текущей датой и датой рождения. В
MySQL есть функции для
арифметических действий над
данными, так что это совсем
несложно. Хранение даты рождения
имеет и другие преимущества:

• Базу данных можно использовать
  для выдачи напоминаний о
  приближающихся днях рождения
  животных (не спешите улыбаться:
  та же задача может возникнуть и
  при работе с деловой базой
  данных, которая должна уметь
  напоминать о днях рождения
  клиентов, облегчая таким образом
  рассылку поздравлений).

• Возраст можно подсчитывать
  относительно любой даты, а не
  только для текущей. Например,
  если записать в базу и дату
  смерти животного, всегда можно
  будет узнать, сколько лет ему
  было на момент смерти.

Можно было бы придумать и еще
какие-нибудь данные, которые
неплохо было бы хранить в таблице
pet, но пока что мы ограничимся уже
выбранными: именем (name),
именем владельца (owner),
видом (species), полом
(sex), датой рождения
(birth) и датой смерти
(death).

При помощи команды CREATE
TABLE определим структуру новой
таблицы:

mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

Тип VARCHAR отлично
подойдет для хранения имени
животного, имени владельца и
названия вида, так как длина данных
этого типа может варьироваться.
Конечно, длины таких столбцов
вовсе не должны совпадать и не
должны быть равны 20 — можно выбрать
любое значение в пределах от 1 до 255
(если при выборе длины столбца вы
ошибетесь, и при работе с базой
окажется, что столбец маловат,
можно будет исправить ошибку при
помощи команды ALTER TABLE).

Пол животного можно обозначать
несколькими способами, например
буквами «m» и «f», или словами male
(мужской) и female (женский). С буквами
«m» и «f» будет проще.

Применение типа данных
DATE для хранения дат
рождения и смерти вполне очевидно.

Теперь, когда таблица создана,
команда SHOW TABLES должна
вывести следующее:

mysql> SHOW TABLES;+---------------------+
| Tables in menagerie |
+---------------------+
| pet |
+---------------------+

Проверить, правильно была ли
таблица создана в соответствии с
планом, можно при помощи команды
DESCRIBE:

mysql> DESCRIBE pet;+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| owner | varchar(20) | YES | | NULL | |
| species | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birth | date | YES | | NULL | |
| death | date | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+

Использовать команду
DESCRIBE можно в любое время,
например, если вы забудете имена
столбцов или типы, к которым они
относятся.

3.3.3. Загрузка данных в таблицу

Создав таблицу, нужно позаботиться
об ее заполнении. Для этого
предназначены команды LOAD
DATA и INSERT.

Предположим, ваши записи
соответствуют приведенным в этой
таблице (обратите внимание: MySQL
принимает даты в формате
ГГГГ-ММ-ДД; возможно, к
такой записи вы не привыкли).

Так как вы начинаете работу с
пустой таблицей, заполнить ее
будет проще всего, если создать
текстовый файл, содержащий по
строке на каждое из животных, а
затем загрузить его содержимое в
таблицу одной командой.

Создайте текстовый файл с именем
pet.txt, содержащий по
одной записи в каждой строке
(значения столбцов должны быть
разделены символами табуляции и
даны в том порядке, который был
определен командой CREATE
TABLE). Незаполненным полям
(например, неизвестный пол или даты
смерти живых на сегодняшний день
животных), можно присвоить
значение NULL. В текстовом
файле это значение представляется
символами \N. Например,
запись для птицы Whistler
должна выглядеть примерно так
(между значениями должны
располагаться одиночные символы
табуляции):

Загрузить файл pet.txt в
таблицу можно с помощью следующей
команды:

mysql> LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet;

Маркер конца строки и символ,
разделяющий значения столбцов,
можно специально задать в команде
LOAD DATA, но по умолчанию
используются символы табуляции и
перевода строки. Воспринимая их,
команда сможет корректно
прочитать файл pet.txt.

При добавлении одиночных записей
используется команда
INSERT. В самом простом
варианте ее применения необходимо
задать значения каждого столбца, в
том порядке, в каком они были
перечислены в команде CREATE
TABLE. Предположим, Диана (Diane)
купила хомячка по имени Puffball.
Соответствующую запись в таблицу с
можно внести с помощью команды
INSERT примерно так:

mysql> INSERT INTO pet -> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);

Обратите внимание на то, что здесь
строковые выражения и даты
представлены в виде ограниченных
кавычками строк. Кроме того, в
команде INSERT
отсутствующие данные можно прямо
заменять на NULL.
Пользоваться эвфемизмом
\N, как в команде LOAD
DATA, нужды нет.

Этот пример наглядно показывает,
что если бы с самого начала все
данные вносились в базу при помощи
нескольких команд INSERT, а
не одной команды LOAD DATA,
то набирать пришлось бы гораздо
больше текста.

3.3.4. Выборка информации из таблицы

3.3.4.1. Выборка всех данных

3.3.4.2. Выборка определенных строк

3.3.4.3. Выборка определенных столбцов

3.3.4.4. Сортировка строк

3.3.4.5. Вычисление дат

3.3.4.6. Работа с значениями NULL

3.3.4.7. Сравнение по шаблонам

3.3.4.8. Подсчет строк

3.3.4.9. Использование нескольких таблиц

Информация извлекается из таблиц
при помощи команды SELECT.
Вызывается она так:

SELECT what_to_select
FROM which_table
WHERE conditions_to_satisfy

где what_to_select обозначает
нужные данные. Это может быть
список столбцов или символ
* («все столбцы»). which_table
указывает таблицу, из которой
должны быть извлечены данные.
Условие WHERE использовать
необязательно, но если оно все же
присутствует в вызове команды, то
параметр conditions_to_satisfy
задает условия, которым должны
соответствовать нужные строки.

3.3.4.1. Выборка всех данных

В самом простом варианте вызова
SELECT из таблицы
извлекаются сразу все данные:

mysql> SELECT * FROM pet;+----------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+--------+---------+------+------------+------------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Bowser | Diane | dog | m | 1998-08-31 | 1995-07-29 |
| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
| Slim | Benny | snake | m | 1996-04-29 | NULL |
| Puffball | Diane | hamster | f | 1999-03-30 | NULL |
+----------+--------+---------+------+------------+------------+

Использовать SELECT таким
образом удобно, когда нужно
просмотреть всю таблицу,
например, после того, как в нее
была загружена первая порция
данных. Как часто случается, в
выведенных на экран данных сразу
можно увидеть ошибку в таблице:
Bowser, оказывается, успел
умереть еще до того, как родился!
Заглянув в его родословную
обнаруживаем, что пес родился в
1989, а не в 1998 году.

Исправить ошибку можно как
минимум двумя способами:

• Отредактировать файл pet.txt,
  затем очистить таблицу и снова
  заполнить ее командами DELETE и LOAD
  DATA:

  mysql> SET AUTOCOMMIT=1; # Used for quick re-create of the tablemysql> DELETE FROM pet;mysql> LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet;

  Однако в таком случае придется
  снова ввести запись о Puffball.

• Исправить только ошибочно
  введенные данные при помощи
  команды UPDATE:

  mysql> UPDATE pet SET birth = "1989-08-31" WHERE name = "Bowser";

Как видно из приведенного выше
примера, загрузить всю таблицу
сразу очень просто. Но на практике
обычно этого не требуется,
особенно когда таблица достигает
значительных размеров. Чаще всего
нужно просто ответить на
какой-нибудь вопрос, для чего
необходимо ввести ограничения,
указывающие, какая же информация
вам нужна. Давайте рассмотрим
несколько запросов с точки зрения
вопросов, на которые они отвечают.

3.3.4.2. Выборка определенных строк

Из таблицы можно выбрать и только
нужные строки. Например, если вы
хотите проверить правильность
внесенных в дату рождения собаки
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 |
+-------+--------+---------+------+------------+-------+

3.3.4.3. Выборка определенных столбцов

Если строки целиком вам не нужны,
достаточно просто перечислить
имена нужных столбцов, разделив
их запятыми. Например, если вы
хотите узнать, когда родились
животные, выберите столбцы с
именами и датами рождения:

mysql> SELECT name, birth FROM pet;+----------+------------+
| name | birth |
+----------+------------+
| Fluffy | 1993-02-04 |
| Claws | 1994-03-17 |
| Buffy | 1989-05-13 |
| Fang | 1990-08-27 |
| Bowser | 1989-08-31 |
| Chirpy | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim | 1996-04-29 |
| Puffball | 1999-03-30 |
+----------+------------+

Получить имена владельцев
животных можно с помощью
следующего запроса:

mysql> SELECT owner FROM pet;+--------+
| owner |
+--------+
| Harold |
| Gwen |
| Harold |
| Benny |
| Diane |
| Gwen |
| Gwen |
| Benny |
| Diane |
+--------+

Однако этот запрос просто
загружает поля с именем владельца
из каждой записи, а некоторые
имена встречаются более одного
раза. Сократить количество
выводимых строк можно,
воспользовавшись ключевым словом
DISTINCT — тогда будут
выводиться только уникальные
записи:

mysql> SELECT DISTINCT owner FROM pet;+--------+
| owner |
+--------+
| Benny |
| Diane |
| Gwen |
| Harold |
+--------+

При помощи выражения
WHERE можно комбинировать
выбор строк и столбцов. Например
для того, чтобы загрузить даты
рождения только кошек и собак,
можно воспользоваться следующим
запросом:

mysql> SELECT name, species, birth FROM pet -> WHERE species = "dog" OR species = "cat";+--------+---------+------------+
| name | species | birth |
+--------+---------+------------+
| Fluffy | cat | 1993-02-04 |
| Claws | cat | 1994-03-17 |
| Buffy | dog | 1989-05-13 |
| Fang | dog | 1990-08-27 |
| Bowser | dog | 1989-08-31 |
+--------+---------+------------+

3.3.4.4. Сортировка строк

Вы уже, наверное, заметили, что
результаты, выдававшиеся
запросами из предыдущих примеров,
выводились без какой-либо
сортировки. Но ведь часто
разобраться в результатах легче,
если они отсортированы. Для этого
используется выражение ORDER
BY.

Так выглядят даты рождения
животных в отсортированном виде:

mysql> SELECT name, birth FROM pet ORDER BY birth;+----------+------------+
| name | birth |
+----------+------------+
| Buffy | 1989-05-13 |
| Bowser | 1989-08-31 |
| Fang | 1990-08-27 |
| Fluffy | 1993-02-04 |
| Claws | 1994-03-17 |
| Slim | 1996-04-29 |
| Whistler | 1997-12-09 |
| Chirpy | 1998-09-11 |
| Puffball | 1999-03-30 |
+----------+------------+

Над столбцами с символьными
значениями операция сортировки —
как и все другие операции
сравнения — обычно проводится без
учета регистра символов. Это
значит, что порядок расположения
столбцов, совпадающих во всем,
кроме регистра символов,
относительно друг друга будет не
определен. Провести сортировку с
учетом регистра символов можно
при помощи команды BINARY:
ORDER BY BINARY(поле).

Для сортировки в обратном порядке
к имени столбца следует добавить
ключевое слово DESC (по
убыванию):

mysql> SELECT name, birth FROM pet ORDER BY birth DESC;+----------+------------+
| name | birth |
+----------+------------+
| Puffball | 1999-03-30 |
| Chirpy | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim | 1996-04-29 |
| Claws | 1994-03-17 |
| Fluffy | 1993-02-04 |
| Fang | 1990-08-27 |
| Bowser | 1989-08-31 |
| Buffy | 1989-05-13 |
+----------+------------+

Сортировку можно проводить по
нескольким столбцам сразу.
Например для того, чтобы
отсортировать таблицу сначала по
типу животного, потом — по дате
рождения и затем — расположить
наиболее молодых животных
определенного типа в начале
списка, выполните следующий
запрос:

mysql> SELECT name, species, birth FROM pet ORDER BY species, birth DESC;+----------+---------+------------+
| name | species | birth |
+----------+---------+------------+
| Chirpy | bird | 1998-09-11 |
| Whistler | bird | 1997-12-09 |
| Claws | cat | 1994-03-17 |
| Fluffy | cat | 1993-02-04 |
| Fang | dog | 1990-08-27 |
| Bowser | dog | 1989-08-31 |
| Buffy | dog | 1989-05-13 |
| Puffball | hamster | 1999-03-30 |
| Slim | snake | 1996-04-29 |
+----------+---------+------------+

Обратите внимание: действие
ключевого слова DESC
распространяется только на тот
столбец, имя которого
располагается в запросе прямо
перед этим словом (в данном случае
— birth); значения поля species
по-прежнему отсортированы в
возрастающем порядке.

3.3.4.5. Вычисление дат

В MySQL имеется несколько функций,
реализующих арифметические
операции над датами. Эти функции
позволяют, например, вычислять
возраст или получать части даты.

Определить возраст любого из
животных в базе можно, если
вычислить разницу между текущим
годом и годом его рождения, а из
результата вычесть единицу, если
текущий день находится к началу
календаря ближе, нежели день
рождения животного. Приведенный
ниже запрос выводит дату рождения
каждого животного, его возраст и
текущую дату.

mysql> SELECT name, birth, CURRENT_DATE, -> (YEAR(CURRENT_DATE)-YEAR(birth)) -> - (RIGHT(CURRENT_DATE,5)<RIGHT(birth,5)) -> AS age -> FROM pet;+----------+------------+--------------+------+
| name | birth | CURRENT_DATE | age |
+----------+------------+--------------+------+
| Fluffy | 1993-02-04 | 2001-08-29 | 8 |
| Claws | 1994-03-17 | 2001-08-29 | 7 |
| Buffy | 1989-05-13 | 2001-08-29 | 12 |
| Fang | 1990-08-27 | 2001-08-29 | 11 |
| Bowser | 1989-08-31 | 2001-08-29 | 11 |
| Chirpy | 1998-09-11 | 2001-08-29 | 2 |
| Whistler | 1997-12-09 | 2001-08-29 | 3 |
| Slim | 1996-04-29 | 2001-08-29 | 5 |
| Puffball | 1999-03-30 | 2001-08-29 | 2 |
+----------+------------+--------------+------+

В этом примере функция
YEAR() выделяет из даты
год, а RIGHT() — пять
крайних справа символов,
представляющих календарный день
(MM-DD). Часть выражения,
сравнивающая даты, выдает 1 или 0,
что позволяет уменьшить
результат на единицу, если
текущий день (CURRENT_DATE)
находится к началу календаря
ближе, нежели день рождения
животного. Все выражение
смотрится несколько неуклюже,
поэтому вместо него в заголовке
соответствующего столбца
результатов выводится псевдоним
(age — «возраст»).

Запрос неплохо работает, но
разобраться в результатах было бы
проще, если бы строки
располагались в определенном
порядке. Этого можно достичь,
добавив в запрос выражение
ORDER BY name и отсортировав
таким образом результаты по
имени:

mysql> SELECT name, birth, CURRENT_DATE, -> (YEAR(CURRENT_DATE)-YEAR(birth)) -> - (RIGHT(CURRENT_DATE,5)<RIGHT(birth,5)) -> AS age -> FROM pet ORDER BY name;+----------+------------+--------------+------+
| name | birth | CURRENT_DATE | age |
+----------+------------+--------------+------+
| Bowser | 1989-08-31 | 2001-08-29 | 11 |
| Buffy | 1989-05-13 | 2001-08-29 | 12 |
| Chirpy | 1998-09-11 | 2001-08-29 | 2 |
| Claws | 1994-03-17 | 2001-08-29 | 7 |
| Fang | 1990-08-27 | 2001-08-29 | 11 |
| Fluffy | 1993-02-04 | 2001-08-29 | 8 |
| Puffball | 1999-03-30 | 2001-08-29 | 2 |
| Slim | 1996-04-29 | 2001-08-29 | 5 |
| Whistler | 1997-12-09 | 2001-08-29 | 3 |
+----------+------------+--------------+------+

Отсортировать результаты по
возрасту также можно при помощи
выражения ORDER BY:

mysql> SELECT name, birth, CURRENT_DATE, -> (YEAR(CURRENT_DATE)-YEAR(birth)) -> - (RIGHT(CURRENT_DATE,5)<RIGHT(birth,5)) -> AS age -> FROM pet ORDER BY age;+----------+------------+--------------+------+
| name | birth | CURRENT_DATE | age |
+----------+------------+--------------+------+
| Chirpy | 1998-09-11 | 2001-08-29 | 2 |
| Puffball | 1999-03-30 | 2001-08-29 | 2 |
| Whistler | 1997-12-09 | 2001-08-29 | 3 |
| Slim | 1996-04-29 | 2001-08-29 | 5 |
| Claws | 1994-03-17 | 2001-08-29 | 7 |
| Fluffy | 1993-02-04 | 2001-08-29 | 8 |
| Fang | 1990-08-27 | 2001-08-29 | 11 |
| Bowser | 1989-08-31 | 2001-08-29 | 11 |
| Buffy | 1989-05-13 | 2001-08-29 | 12 |
+----------+------------+--------------+------+

подобный же запрос поможет
определить возраст, которого
достигли умершие животные на
момент смерти. Выделить умерших
животных можно, проверив значение
поля death на предмет
равенства NULL. Затем для
записей, значения поля
death которых не равно
NULL, можно вычислить
разницу между датами смерти и
рождения:

mysql> SELECT name, birth, death, -> (YEAR(death)-YEAR(birth)) - (RIGHT(death,5)<RIGHT(birth,5)) -> AS age -> FROM pet WHERE death IS NOT NULL ORDER BY age;+--------+------------+------------+------+
| name | birth | death | age |
+--------+------------+------------+------+
| Bowser | 1989-08-31 | 1995-07-29 | 5 |
+--------+------------+------------+------+

В этом запросе используется
выражение death IS NOT NULL, а
не death <> NULL, так как
NULL — особое значение
(более подробные пояснения
приведены в разделе see
Раздел 3.3.4.6, «Работа с значениями NULL»).

А как поступать, если потребуется
определить, дни рождения каких
животных наступят в следующем
месяце? Для таких расчетов день и
год значения не имеют; из столбца,
содержащего дату рождения, нас
интересует только месяц. В MySQL
предусмотрено несколько функций
для получения частей дат —
YEAR(), MONTH(), и
DAYOFMONTH(). В данном случае
нам подойдет функция
MONTH(). Увидеть работу
этой функции можно с помощью
простого запроса, выводящего дату
рождения birth и
MONTH(birth):

mysql> SELECT name, birth, MONTH(birth) FROM pet;+----------+------------+--------------+
| name | birth | MONTH(birth) |
+----------+------------+--------------+
| Fluffy | 1993-02-04 | 2 |
| Claws | 1994-03-17 | 3 |
| Buffy | 1989-05-13 | 5 |
| Fang | 1990-08-27 | 8 |
| Bowser | 1989-08-31 | 8 |
| Chirpy | 1998-09-11 | 9 |
| Whistler | 1997-12-09 | 12 |
| Slim | 1996-04-29 | 4 |
| Puffball | 1999-03-30 | 3 |
+----------+------------+--------------+

Найти животных, дни рождения
которых наступят в следующем
месяце, тоже несложно.
Предположим, что сейчас на дворе
апрель. Тогда номер текущего
месяца — 4, а искать надо животных,
родившихся в мае (5-м месяце), таким
образом:

mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5;+-------+------------+
| name | birth |
+-------+------------+
| Buffy | 1989-05-13 |
+-------+------------+

Конечно, в декабре возникают
некоторые осложнения. Если просто
добавить единицу к номеру месяца
(12) и поискать животных,
родившихся в тринадцатом месяце,
найти что-нибудь вряд ли удастся.
Вместо этого нужно искать
животных, родившихся в январе (1-м
месяце).

Можно даже написать небольшой
запрос, который будет работать
вне зависимости от того, какой
нынче месяц. Функция
DATE_ADD() позволяет
прибавить к дате некоторый
интервал времени. Если добавить к
значению, возвращаемому функцией
NOW(), месяц, а затем
извлечь из получившейся даты
номер месяца при помощи функции
MONTH(), мы получим именно
тот месяц, который нам нужен:

mysql> SELECT name, birth FROM pet -> WHERE MONTH(birth) = MONTH(DATE_ADD(NOW(), INTERVAL 1 MONTH));

Ту же задачу можно решить и другим
методом — для этого нужно
прибавить единицу к номеру
месяца, следующего за текущим
(воспользовавшись функцией
расчета по модулю (MOD) для перехода
к 0, если номер текущего месяца
равен 12):

mysql> SELECT name, birth FROM pet -> WHERE MONTH(birth) = MOD(MONTH(NOW()), 12) + 1;

Функция MONTH возвращает
число от 1 до 12, а выражение
MOD(число,12) — число от 0 до
11. Поэтому операцию сложения
нужно проводить после
MOD(), иначе результат
перепрыгнет с ноября (11) сразу на
январь (1).

3.3.4.6. Работа с значениями NULL

К NULL-значениям нужно
привыкнуть. По идее, NULL
обозначает отсутствующее или
неизвестное значение и
обрабатывается отличным от
других значений образом.
Проверить значение на равенство
NULL с помощью обычных
арифметических операторов
сравнения (=, < или <>) нельзя.
Это отлично иллюстрирует
следующий запрос:

mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;+----------+-----------+----------+----------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
+----------+-----------+----------+----------+
| NULL | NULL | NULL | NULL |
+----------+-----------+----------+----------+

Очевидно, что от таких сравнений
значащих результатов ожидать
нечего. Вместо этого нужно
пользоваться операторами IS
NULL и IS NOT NULL:

mysql> SELECT 1 IS NULL, 1 IS NOT NULL;+-----------+---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+
| 0 | 1 |
+-----------+---------------+

Отметим: в MySQL 0 или NULL
приравнивается к логическому false,
а все остальное — к true. По
умолчанию значение «истина» для
булевого оператора равно 1.

Именно из-за того, что при работе с
NULL действуют особые
правила, в предыдущем разделе для
поиска умерших животных
использовалось выражение death
IS NOT NULL, а не death <>
NULL.

Два NULL-значения
считаются одинаковыми в GROUP
BY.

При выполнении ORDER BY,
NULL-значения идут в
первую очередь если вы выполняете
ORDER ... ASC и в последнюю —
если ORDER BY ... DESC.

Обратите внимание, что в MySQL 4.0.2 —
4.0.10, NULL-значения всегда
возвращались в первую очередь,
вне зависимости от сортировки.

3.3.4.7. Сравнение по шаблонам

В MySQL реализовано стандартное для
SQL сравнение по шаблонам, а также
особый тип такого сравнения — он
основан на использовании
выражений, подобных
применяющимся в служебных
программах Unix (таких, как vi, grep и
sed).

В SQL при сравнении по шаблону
символ ‘_’
обозначает любой одиночный
символ, а ‘%’ —
определенное количество символов
(включая ноль символов). В MySQL в
SQL-шаблонах по умолчанию не
учитывается регистр символов. При
работе с шаблонами SQL
использование операторов
= или <> не
допускается, вместо этого
применяются операторы сравнения
LIKE или NOT LIKE.

Найти все имена, начинающиеся с
‘b’, можно
следующим образом:

mysql> SELECT * FROM pet WHERE name LIKE "b%";+--------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+------------+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+

Найти все имена, заканчивающиеся
на ‘fy‘, можно следующим
образом:

mysql> SELECT * FROM pet WHERE name LIKE "%fy";+--------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+--------+--------+---------+------+------------+-------+

Найти все имена, содержащие
‘w’, можно
следующим образом:

mysql> SELECT * FROM pet WHERE name LIKE "%w%";+----------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+------------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
+----------+-------+---------+------+------------+------------+

Найти все имена, содержащие ровно
пять символов, можно при помощи
шаблонного символа
‘_’:

mysql> SELECT * FROM pet WHERE name LIKE "_____";+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+

Во втором типе шаблонов,
предусмотренных в MySQL,
используются расширенные
регулярные выражения. При поиске
совпадений на основе такого
шаблона шаблоном нужно
пользоваться операторами
REGEXP и NOT REGEXP
(или их синонимами — RLIKE
и NOT RLIKE).

Ниже приведены некоторые
характеристики расширенных
регулярных выражений:

• ‘.’ обозначает
  любой символ.

• Класс символов ‘[...]‘
  обозначает любой из символов в
  скобках. Например,
  ‘[abc]‘ обозначает
  ‘a’,
  ‘b’ или
  ‘c’. Набор
  символов можно обозначить с
  помощью дефиса. ‘[a-z]‘
  обозначает любую букву нижнего
  регистра, а ‘[0-9]‘ —
  любую цифру.

• ‘*’ обозначает
  ноль или более экземпляров
  символа, стоящего перед ним.
  Например, ‘x*‘
  обозначает любое количество
  символов ‘x’,
  ‘[0-9]*‘ обозначает
  любое количество цифр, а
  ‘.*‘ — любое количество
  любых символов.

• Для шаблона выдается
  совпадение, если поисковый
  контекст обнаружен в любой из
  частей значения, в котором
  производится поиск (для
  шаблонов SQL совпадение выдается
  только в случае, если совпадает
  все значение).

• «Закрепить» шаблон так, чтобы
  проверять совпадения с началом
  или концом значения можно с
  помощью символов
  ‘^’ (начало) или
  ‘$’ (конец),
  которые располагаются в начале
  или в конце шаблона
  соответственно.

Чтобы продемонстрировать работу
регулярных выражений,
приведенные выше запросы
LIKE здесь переписаны с
использованием REGEXP.

Найти все имена, начинающиеся с
‘b’, можно при
помощи символа
‘^’,
привязывающего шаблон к началу
имени:

mysql> SELECT * FROM pet WHERE name REGEXP "^b";+--------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+------------+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+

В версиях MySQL до 3.23.4 REGEXP
учитывает регистр символов, и
приведенный запрос не возвратит
никаких результатов. Для поиска
символов ‘b’
верхнего или нижнего регистра
воспользуйтесь следующим
запросом:

mysql> SELECT * FROM pet WHERE name REGEXP "^[bB]";

Начиная с версии MySQL 3.23.4,
заставить REGEXP
учитывать регистр символов можно
с помощью ключевого слова
BINARY. В этом запросе
положительный результат поиска
будет получен только при
обнаружении символа ‘b’ нижнего
регистра в начале имени:

mysql> SELECT * FROM pet WHERE name REGEXP BINARY "^b";

Найти все имена, заканчивающиеся
на ‘fy‘, можно при помощи
символа ‘$’,
привязывающего шаблон к концу
имени:

mysql> SELECT * FROM pet WHERE name REGEXP "fy$";+--------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+--------+--------+---------+------+------------+-------+

Найти все имена, содержащие
символ ‘w’ любого
регистра, можно так:

mysql> SELECT * FROM pet WHERE name REGEXP "w";+----------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+------------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
+----------+-------+---------+------+------------+------------+

Поскольку регулярное выражение
выдает положительный результат
при совпадении шаблона с любым
фрагментом значения, в
приведенном выше примере
привязывать поиск к любому из
концов имени для сравнения
полного значения с шаблоном, как
это пришлось бы делать для
шаблона SQL, не нужно.

Найти все имена, содержащие ровно
пять символов, можно, если
привязать поиск к началу и концу
имени с помощью символов
‘^’ и
‘$’ и поставить
пять символов ‘.’
между ними:

mysql> SELECT * FROM pet WHERE name REGEXP "^.....$";+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+

Предыдущий запрос можно записать
и при помощи оператора
‘{n}‘
(«повторить-n-раз»):

mysql> SELECT * FROM pet WHERE name REGEXP "^.{5}$";+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+

3.3.4.8. Подсчет строк

Базы данных часто используются
для получения ответа на вопросы
типа: «как часто данные
определенного типа встречаются в
таблице?» Вам, например, может
понадобиться узнать общее
количество животных, или то,
сколько животных имеется у
каждого из владельцев, или
провести статистические
исследования на базе хранящейся
информации.

Процедура подсчета количества
животных в сущности идентична
подсчету количества строк в
таблице, так как на каждое
животное приходится по одной
записи. Функция COUNT()
подсчитает количество непустых
результатов, и с ее помощью можно
составить следующий запрос для
определения числа животных:

mysql> SELECT COUNT(*) FROM pet;+----------+
| COUNT(*) |
+----------+
| 9 |
+----------+

Ранее мы уже извлекали из таблицы
имена владельцев животных. При
помощи функции COUNT()
можно узнать, сколько животных
принадлежит каждому из
владельцев:

mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;+--------+----------+
| owner | COUNT(*) |
+--------+----------+
| Benny | 2 |
| Diane | 2 |
| Gwen | 3 |
| Harold | 2 |
+--------+----------+

Обратите внимание на
использование команды GROUP
BY для объединения всех
записей по каждому из владельцев.
Без этой команды запрос выдал бы
только сообщение об ошибке:

mysql> SELECT owner, COUNT(owner) FROM pet;ERROR 1140 at line 1: Mixing of GROUP columns (MIN(),MAX(),COUNT()...)
with no GROUP columns is illegal if there is no GROUP BY clause

Команды COUNT() и GROUP
BY очень помогают
характеризовать данные
различными способами. В примерах,
приведенных ниже, вы увидите и
другие способы проведения
статистических подсчетов.

Количество животных каждого вида:

mysql> SELECT species, COUNT(*) FROM pet GROUP BY species;+---------+----------+
| species | COUNT(*) |
+---------+----------+
| bird | 2 |
| cat | 2 |
| dog | 3 |
| hamster | 1 |
| snake | 1 |
+---------+----------+

Количество животных каждого пола:

mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex;+------+----------+
| sex | COUNT(*) |
+------+----------+
| NULL | 1 |
| f | 4 |
| m | 4 |
+------+----------+

(в этой таблице результатов
NULL обозначает, что пол
животного неизвестен)

Количество животных каждого вида
с учетом пола:

mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;+---------+------+----------+
| species | sex | COUNT(*) |
+---------+------+----------+
| bird | NULL | 1 |
| bird | f | 1 |
| cat | f | 1 |
| cat | m | 1 |
| dog | f | 1 |
| dog | m | 2 |
| hamster | f | 1 |
| snake | m | 1 |
+---------+------+----------+

При использовании функции
COUNT() вовсе не
обязательно загружать всю
таблицу. Например, предыдущий
запрос, в котором учитываются
только кошки и собаки, выглядит
следующим образом:

mysql> SELECT species, sex, COUNT(*) FROM pet -> WHERE species = "dog" OR species = "cat" -> GROUP BY species, sex;+---------+------+----------+
| species | sex | COUNT(*) |
+---------+------+----------+
| cat | f | 1 |
| cat | m | 1 |
| dog | f | 1 |
| dog | m | 2 |
+---------+------+----------+

Можно узнать и количество
животных каждого пола с учетом
только тех экземпляров, пол
которых известен:

mysql> SELECT species, sex, COUNT(*) FROM pet -> WHERE sex IS NOT NULL -> GROUP BY species, sex;+---------+------+----------+
| species | sex | COUNT(*) |
+---------+------+----------+
| bird | f | 1 |
| cat | f | 1 |
| cat | m | 1 |
| dog | f | 1 |
| dog | m | 2 |
| hamster | f | 1 |
| snake | m | 1 |
+---------+------+----------+

3.3.4.9. Использование нескольких таблиц

В таблице pet хранятся
только основные данные о
животных. Если же нужно держать в
базе какую-либо дополнительную
информацию о них (скажем, записи о
событиях наподобие посещения
ветеринара или рождения
потомства), понадобится еще одна
таблица. Давайте определимся с ее
структурой. Эта таблица должна
содержать:

• имена животных, чтобы не
  путаться с тем, к какому
  животному относится какое
  событие

• дата события

• поле для описания события

• поле, отражающее тип события,
  для того, чтобы можно было
  распределить их по категориям

С учетом всех приведенных выше
требований можно составить
примерно такую команду CREATE
TABLE:

mysql> CREATE TABLE event (name VARCHAR(20), date DATE, -> type VARCHAR(15), remark VARCHAR(255));

Как и в случае с таблицей
pet, начальные данные в
таблицу проще всего загрузить,
создав текстовый файл с
информацией, разделенной
символами табуляции:

Загрузите записи с помощью
следующей команды:

mysql> LOAD DATA LOCAL INFILE "event.txt" INTO TABLE event;

Используя знания, усвоенные при
работе с таблицей pet, вы
сможете загружать данные из
таблицы event; принципы
здесь те же. Но что если самой по
себе таблицы event
перестанет хватать для получения
нужных вам ответов?

Предположим, нужно узнать, в каком
возрасте животные давали приплод.
В таблице event указаны
даты родов, но для того, чтобы
рассчитать возраст матери, нужно
знать и дату ее рождения. Так как
даты рождения хранятся в таблице
pet, в этом запросе нужно
использовать обе таблицы:

mysql> SELECT pet.name, -> (TO_DAYS(date) - TO_DAYS(birth))/365 AS age, -> remark -> FROM pet, event -> WHERE pet.name = event.name AND type = "litter";+--------+------+-----------------------------+
| name | age | remark |
+--------+------+-----------------------------+
| Fluffy | 2.27 | 4 kittens, 3 female, 1 male |
| Buffy | 4.12 | 5 puppies, 2 female, 3 male |
| Buffy | 5.10 | 3 puppies, 3 female |
+--------+------+-----------------------------+

На некоторые аспекты этого
запроса следует обратить особое
внимание:

• В выражении FROM
  указаны две таблицы, так как
  запрос будет получать
  информацию из обеих.

• При комбинировании
  (объединении) информации из
  нескольких таблиц необходимо
  указать, как строки одной
  таблицы связываются с записями
  другой. Это просто, так как в
  обеих таблицах есть столбец с
  именами. В этом запросе
  выражение WHERE
  используется для сопоставления
  записей из двух таблиц по
  значениям имен.

• Так как столбец name присутствует
  в обеих таблицах, нужно явно
  указать, какую именно таблицу
  вы имеете в виду, ссылаясь на
  данный столбец. Это можно
  сделать, связав имя таблицы с
  именем столбца.

Для объединения не обязательно
иметь две отдельные таблицы;
иногда можно объединить таблицу с
самой собой — если нужно сравнить
одни записи таблицы с другими
записями той же таблицы. Например,
для того, чтобы обнаружить среди
животных «семейные пары», можно
объединить таблицу pet с
ней самой, составив пары животных
разного пола, но одного вида:

mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species -> FROM pet AS p1, pet AS p2 -> WHERE p1.species = p2.species AND p1.sex = "f" AND p2.sex = "m";+--------+------+--------+------+---------+
| name | sex | name | sex | species |
+--------+------+--------+------+---------+
| Fluffy | f | Claws | m | cat |
| Buffy | f | Fang | m | dog |
| Buffy | f | Bowser | m | dog |
+--------+------+--------+------+---------+

В этом запросе мы указываем
псевдонимы имен таблицы, для
обращения к столбцам и
определения, к какой из таблиц
относится каждая ссылка на
столбец.