| Каталог документации / Раздел "Базы данных, SQL" / Оглавление документа |
| |
|
Эта глава обеспечивает введение в MySQL, показывая, как использовать
клиент mysql, чтобы создавать и использовать простую базу
данных. mysql (иногда упоминаемый как "terminal monitor" или
"monitor") представляет собой интерактивную программу, которая позволяет Вам
соединяться с сервером MySQL, выполнять запросы и просматривать результаты.
Она может также использоваться в пакетном режиме: Вы помещаете Ваши запросы в
файл заранее, затем сообщаете, чтобы клиент mysql выполнил
содержимое файла. Оба пути использования будут здесь рассмотрены.
Чтобы увидеть список параметров mysql, вызовите его с опцией
--help:
shell> mysql --help
Эта глава предполагает, что mysql установлен на Вашей машине,
и что сервер MySQL доступен.
Эта глава описывает весь процесс создания и использования базы данных. Если Вы заинтересованы только в работе с уже существующей базой данных, Вы можете пропустить части, которые описывают, как создать базу данных и таблицы, которые она хранит.
Чтобы соединяться с сервером, Вы обычно должны обеспечить имя пользователя,
когда Вы вызываете mysql и, вероятно, пароль. Если сервер
выполняется на другой машине, Вы будете также должны определить hostname.
Войдите в контакт с Вашим администратором, чтобы выяснить, какие параметры
соединения Вы должны использовать, чтобы подключиться (то есть, какой
hostname, порт, имя пользователя и пароль). Как только Вы узнаете
соответствующие параметры, Вы должны иметь возможность соединиться так:
shell> mysql -h host -u user -p Enter password: ********
Здесь ******** представляет Ваш пароль. Введите его, когда
mysql отобразит сообщение Enter password:.
Если это работает, Вы должны увидеть некоторую вводную информацию,
сопровождаемую подсказкой mysql>:
shell> mysql -h host -u user -p Enter password: ******** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 459 to server version: 3.22.20a-log Type 'help' for help. mysql>
Подсказка сообщает Вам, что mysql готов принять команды.
Некоторые инсталляции MySQL позволяют пользователям соединяться как
анонимным (неназванным) пользователям с сервером на локальной машине. Если у
Вас дело обстоит именно так, Вы можете соединиться с таким сервером, вызывая
mysql без параметров:
shell> mysql
После того, как Вы успешно соединились, Вы можете разъединять соединение в
любое время, набрав команду QUIT в ответ на подсказку
mysql>:
mysql> QUIT Bye
Вы можете также разъединять связь, нажимая Control-D.
Большинство примеров в следующих разделах считает, что Вы связаны с
сервером. Они указывают это подсказкой mysql>.
В этой точке более важно выяснить немного относительно того, как выдавать
запросы, чем правильно создавать базы данных. Этот раздел описывает базисные
принципы ввода команд, использование нескольких запросов, которые Вы можете
испытывать, чтобы ознакомиться с тем, как работает клиент mysql.
Имеется простая команда, которая просит, чтобы сервер сообщил свою версию
и текущую дату. Напечатайте это как показано ниже после подсказки
mysql> и нажмите клавишу RETURN:
mysql> SELECT VERSION(), CURRENT_DATE; +--------------+--------------+ | version() | CURRENT_DATE | +--------------+--------------+ | 3.22.20a-log | 1999-03-19 | +--------------+--------------+ 1 row in set (0.01 sec) mysql>
Этот запрос иллюстрирует несколько вещей относительно mysql:
QUIT. Мы доберемся
до других позже.
mysql посылает ее серверу для
выполнения и отображает результаты, затем печатает другой запрос
mysql>, чтобы указать, что готов для приема другой команды.
mysql отображает вывод запроса как таблицу (строки и
столбцы). Первая строка содержит метки для столбцов. Следующие строки задают
результаты запроса. Обычно, метки столбца представляют собой имена столбцов,
которые Вы выбираете из таблиц базы данных. Если Вы получаете значение
выражения, а не столбец таблицы, mysql маркирует столбец,
используя выражение непосредственно.
mysql показывает, сколько строк было возвращено, и как долго
обрабатывался запрос, что дает Вам грубые данные относительно эффективности
сервера. Эти значения неточны потому, что они представляют не машинное время
или затраты времени CPU, а рамки выполнения запроса. На эти данные действуют,
например, задержки в сетевом канале. Для краткости строка ``rows in set'' не
показывается в оставшихся примерах из этой главы.Ключевые слова могут быть введены в любом регистре. Следующие запросы эквивалентны:
mysql> SELECT VERSION(), CURRENT_DATE; mysql> select version(), current_date; mysql> SeLeCt vErSiOn(), current_DATE;
Имеется другой запрос. Он показывает, что Вы можете использовать
mysql как простой калькулятор:
mysql> SELECT SIN(PI()/4), (4+1)*5; +-------------+---------+ | SIN(PI()/4) | (4+1)*5 | +-------------+---------+ | 0.707107 | 25 | +-------------+---------+
Команды, показанные к настоящему времени, были относительно короткие, с одной строкой инструкции. Вы можете даже вводить много инструкций на одной строке. Только закончите каждую их них точкой с запятой:
mysql> SELECT VERSION(); SELECT NOW(); +--------------+ | version() | +--------------+ | 3.22.20a-log | +--------------+ +---------------------+ | NOW() | +---------------------+ | 1999-03-19 00:15:33 | +---------------------+
Однако, команды бывают и длинными. Но длинные команды, которые требуют
нескольких строк, не проблема. mysql определяет конец оператора,
ища точку с запятой, а не конец строки. Другими словами, mysql
принимает ввод свободного формата: он собирает вводимые строки, но не
выполняет их, пока не увидит точку с запятой.
Имеется простая инструкция с длинной строкой:
mysql> SELECT
-> USER()
-> ,
-> CURRENT_DATE;
+--------------------+--------------+
| USER() | CURRENT_DATE |
+--------------------+--------------+
| joesmith@localhost | 1999-03-18 |
+--------------------+--------------+
В этом примере обратите внимание, как подсказка изменяется с
mysql> на -> после того, как Вы вводите
первую строку запроса. Этим mysql указывает, что пока не видел
полную инструкцию и ждет остальное. Подсказка Ваш друг потому, что
обеспечивает ценную обратную связь. Если Вы используете эту обратную связь,
Вы будете всегда знать, чего ждет от Вас mysql.
Если Вы решаете, что Вы не хотите выполнять команду, но находитесь в
процессе ввода, отмените ее вводом \c:
mysql> SELECT
-> USER()
-> \c
mysql>
Здесь также обратите внимание на подсказку. Она переключилась назад в
mysql> после ввода \c, обеспечивая обратную
связь, чтобы указать, что mysql готов для новой команды.
Следующая таблица показывает все подсказки и обобщает их смысл:
| Подсказка | Смысл |
mysql> | Ожидание новой команды |
-> | Ожидание следующей строки многострочной команды |
'> | Ожидание следующей строки при сборе строки, которая начинается с одиночной кавычки (') |
"> | Ожидание следующей строки при сборе строки, которая начинается с двойной кавычки (") |
Иногда многострочные инструкции происходят случайно, когда Вы
предполагаете выдавать команду на одной строке, но забываете точку с запятой
для завершения. В этом случае mysql ждет продолжение ввода:
mysql> SELECT USER()
->
Если это случается с Вами (Вы думаете, что ввели оператор, но единственный
ответ представляет собой подсказку ->), наиболее вероятно,
что mysql ждет точку с запятой. Введите точку с запятой, чтобы
завершить инструкцию, и mysql ее выполнит:
mysql> SELECT USER()
-> ;
+--------------------+
| USER() |
+--------------------+
| joesmith@localhost |
+--------------------+
Подсказки '> и "> появляются при работе
со строкой в кавычках. В MySQL Вы можете писать строки в символах
' или " (например, 'hello' или
"goodbye"), и mysql позволяет Вам вводить длинные
строки в виде нескольких коротких строк. Когда Вы видите подсказку
'> или ">, это означает, что Вы ввели
строку, которая начинается с символа кавычки ' или
", но еще не ввели вторую кавычку, которая завершает эту строку.
Это также может сообщать и об ошибке:
mysql> SELECT * FROM my_table WHERE name="Smith AND age < 30;
">
Если Вы вводите эту инструкцию SELECT, то нажатие RETURN
ничего не вернет. Вместо того, чтобы задаваться вопросом, почему этот запрос
так долго выполняется, обратите внимание на подсказку ">.
Это сообщает Вам, что mysql ожидает увидеть остальную часть
незавершенной строки. Вы видите ошибку в инструкции? Строка
"Smith пропускает вторую кавычку.
Что делать? Самая простая вещь: отменить команду. Однако, Вы не можете
в этом случае просто напечатать \c, так как mysql
интерпретирует это как часть, которая вводится! Вместо этого, введите
заключительный символ кавычки (чтобы mysql понял, что Вы
закончили строку), а уж потом \c:
mysql> SELECT * FROM my_table WHERE name = "Smith AND age < 30;
"> "\c
mysql>
Подсказка изменяется обратно на mysql>, указывая, что
mysql готов для новой команды.
Важно знать, что сообщают подсказки '> и
">, так как если Вы по ошибке вводите незавершенную строку,
любые дальнейшие строки, которые Вы введете, будут игнорироваться
mysql, включая строку QUIT! Это может крепко
запутать, особенно, если Вы не знаете, что должны ввести кавычку завершения
прежде, чем Вы сможете отменить текущую команду.
Теперь, когда Вы знаете, как вводить команды, самое время обратиться к базе данных.
Предположим, что Вы имеете несколько домашних животных, и Вы хотели бы следить за различными типами информации относительно их. Вы можете сделать это, создавая таблицы, чтобы хранить Ваши данные и загружая их желательной информацией. Затем Вы можете отвечать на различные типы вопросов относительно Ваших животных, получая данные из таблиц. Этот раздел показывает Вам как:
База данных животных будет простой, но нетрудно думать о реальных
ситуациях, в которых подобный тип базы данных мог бы использоваться.
Например, база данных, подобная этой, могла бы использоваться фермером, чтобы
следить за домашним скотом, или ветеринаром, чтобы наблюдать за пациентами.
Дистрибутив базы животных, содержащий некоторые из запросов и типовых данных,
используемых в следующих разделах, может быть получен с Web-сайта MySQL в
сжатом tar формате или в
Zip-формате.
Используйте инструкцию SHOW, чтобы выяснить то, какие базы
данных в настоящее время существуют на Вашем сервере:
mysql> SHOW DATABASES; +----------+ | Database | +----------+ | mysql | | test | | tmp | +----------+
Список баз данных, вероятно, иной на Вашей машине, но базы с именами
mysql и test, вероятно, будут среди них. База
данных mysql вообще обязательна потому, что она описывает
привилегии доступа пользователей. База данных test часто
обеспечивается как рабочая область для начинающих пользователей (лягушатник).
Именно там проводятся разные "опыты со взрывами".
Если существует база данных test, попробуйте обратиться к ней:
mysql> USE test Database changed
Обратите внимание, что USE, подобно QUIT, не
требует точки с запятой. Вы можете завершать такие инструкции точкой с
запятой, если Вы находите это приятным, такой подход не несет никакого вреда.
Инструкция USE должна быть задана в одной строке.
Вы можете использовать базу данных тестов test (если Вы
имеете доступ к ней) для примеров, но все, что Вы там насоздавали, может быть
удалено кем-либо еще с доступом к ней. По этой причине, Вы должны, вероятно,
спросить у Вашего администратора MySQL разрешения использовать собственную
базу данных. Предположим, что Вы хотите вызвать Ваш звероуголок
(menagerie). Администратор должен выполнить такую команду:
mysql> GRANT ALL ON menagerie.* TO your_mysql_name;
Здесь your_mysql_name представляет собой имя пользователя
MySQL, назначенное Вам.
Если администратор создал базу данных для Вас и установил Ваши права доступа, Вы можете начинать использовать ее. Иначе, Вы должны создать базу данных самостоятельно. Например:
mysql> CREATE DATABASE menagerie;
Под Unix имена баз данных чувствительны к регистру (в отличие от ключевых
слов SQL), так что Вы должны всегда обращаться к Вашей базе данных как к
menagerie, но не как Menagerie,
MENAGERIE или еще как-то. Это также верно для имен таблицы. Под
Windows это ограничение не применяется, хотя Вы должны обращаться к базам
данных и таблицам, используя тот же самый регистр символов в течение запроса.
Создание базы данных не выбирает ее для использования, Вы должны сделать
это явно. Чтобы объявить menagerie текущей (актуальной) базой
данных, используйте эту команду:
mysql> USE menagerie Database changed
Ваша база данных должна быть создана только однажды, но Вы должны выбирать
ее для использования каждый раз, когда Вы начинаете сеанс mysql.
Вы можете сделать это, выдавая инструкцию USE как показано выше.
Альтернативно, Вы можете выбирать базу данных в командной строке, когда Вы
вызываете mysql. Только определите имя после любых параметров
подключения к серверу, которые Вы должны обеспечить. Например:
shell> mysql -h host -u user -p menagerie Enter password: ********
Обратите внимание, что menagerie не Ваш пароль. Если Вы
хотите указать пароль в командной строке после опции -p, Вы
должны сделать это без пробела (например, как -pmypassword, но
не как -p mypassword). Однако, помещение Вашего пароля в
командную строку не рекомендуется потому, что это выставит его на всеобщее
обозрение всем пользователям Вашей машины.
Создание базы данных дело простое, но сейчас база пустая, о чем и сообщает
команда SHOW TABLES:
mysql> SHOW TABLES; Empty set (0.00 sec)
Наиболее трудная часть этого дела: решить, какова будет структура Вашей базы данных, в каких таблицах Вы будете нуждаться, и какие столбцы будут в каждой из них.
Вы будете иметь таблицу, которая содержит запись для каждого из Ваших
домашних животных. Она может быть названа pet и должна
содержать, как минимум, имя каждого животного. Так как имя само по себе не
очень интересно, таблица должна содержать и другую информацию. Например, если
больше, чем один человек в Вашем семействе имеет домашних животных, Вы могли
бы внести в список владельца каждого животного. Вы могли бы также записывать
некоторую базисную описательную информацию типа разновидности и пола.
Каков приблизительный возраст? Это могло бы быть интересным, но лучше не вписывать такие данные в таблицу. Возраст изменяется, а это означает, что Вы должны будете часто модифицировать Ваши записи. Вместо этого лучше сохранить фиксированное значение, типа даты рождения. Затем всякий раз, когда Вы нуждаетесь в данных о возрасте, Вы можете вычислить его как различие (но не разность!) между текущей датой и датой рождения. MySQL обеспечивает функции для выполнения арифметики даты, так что это нетрудно. Сохранение даты рождения имеет также и другие преимущества:
Пока в таблице pet ограничимся данными об имени, владельце,
разновидности, поле, рождении и гибели, если она произошла.
Используйте инструкцию CREATE TABLE, чтобы определить
размещение Вашей таблицы:
mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
-> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
VARCHAR представляет собой хороший выбор для столбцов имени,
владельца и разновидности потому, что значения столбца изменяются по длине.
Длины столбцов name, owner и species
не должны превышать 20 символов. Вообще-то, для этого типа столбцов Вы можете
выбирать любую длину от 1 до 255, какая кажется
наиболее приемлемой Вам. Если Вы делаете столбец недостаточных размеров,
а позже окажется, что Вы нуждаетесь в более длинном поле, MySQL обеспечивает
инструкцию ALTER TABLE.
Пол животных можно представить разными путями, например, "m"
и "f" или словами "male" и "female".
Самое простое: использовать одиночные символы "m" и
"f".
Использование типа данных DATE для столбцов рождения и гибели
(birth и death) явно довольно очевидный выбор.
Теперь, когда Вы создали таблицу, 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 в любое время, например, если
Вы забываете имена столбцов в Вашей таблице, или каких они типов.
После создания Вашей таблицы Вы должны заполнить ее. Инструкции
LOAD DATA и INSERT полезны для этого.
Предположим, что Ваши записи о живности могут быть описаны как показано
ниже. Заметьте, что MySQL ожидает даты в формате YYYY-MM-DD, это
может быть отлично от того, к чему Вы привыкли.
| name | owner | species | sex | birth | death |
| Fluffy | Harold | кошка | f | 1993-02-04 | |
| Claws | Gwen | кошка | m | 1994-03-17 | |
| Buffy | Harold | собака | f | 1989-05-13 | |
| Fang | Benny | собака | m | 1990-08-27 | |
| Bowser | Diane | собака | m | 1989-08-31 | 1995-07-29 |
| Chirpy | Gwen | птичка | f | 1998-09-11 | |
| Whistler | Gwen | птичка | 1997-12-09 | ||
| Slim | Benny | змея | m | 1996-04-29 |
Поскольку Вы начинаете работу с пустой таблицей, самый простой способ заполнить ее состоит в том, чтобы создать текстовый файл, содержащий строку для каждого из Ваших животных, затем загрузить содержимое файла в таблицу всего одной инструкцией.
Вы могли бы создать текстовый файл pet.txt, содержащий одну
запись на строку, со значениями, отделяемыми позициями табуляции, указанными
в том порядке, в котором столбцы были перечислены в инструкции CREATE
TABLE. Для отсутствующих значений (типа неизвестного пола или дат
гибели для животных, которые все еще живут), Вы можете использовать значения
NULL. Чтобы представить их в Вашем текстовом файле, используйте
\N. Например, запись для птички Whistler выглядит примерно так
(пробелом я обозначил табуляцию):
Whistler Gwen птичка \N 1997-12-09 \N
Чтобы загрузить текстовый файл pet.txt в таблицу
pet, используйте эту команду:
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);
Обратите внимание, что строка и значения даты определены как цитируемые
строки. Вы можете вставлять NULL непосредственно, чтобы
представить отсутствие значения.
Из этого примера Вы видите, что для непосредственной загрузки в таблицу
пришлось бы печатать довольно много. Инструкция LOAD DATA
сберегла много времени.
Инструкция SELECT используется, чтобы получить информацию из
таблицы. Общая форма инструкции:
SELECT what_to_select FROM which_table WHERE conditions_to_satisfy
what_to_select указывает то, что Вы хотите увидеть. Это может
быть список столбцов или *, чтобы указать все столбцы.
which_table указывает таблицу, из которой Вы хотите получить
данные. Предложение WHERE факультативно. Если оно представлено,
conditions_to_satisfy определяет условия, которым должны
удовлетворять искомые строки.
Самая простая форма 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 полезна, если Вы хотите сделать обзор всей
Вашей таблицы, например, после того, как Вы только что загрузили ее начальным
набором данных. Удобно искать ошибки.
Как показано выше, просто получить всю таблицу. Но обычно Вы не хотите
делать это, особенно, когда таблица становится большой. Вместо этого, Вы
обычно больше заинтересованы в ответе на специфический вопрос, когда Вы
определяете некоторые ограничения на информацию, которую Вы хотите получить.
Давайте рассматривать некоторые запросы 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, а не 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 |
+-------+--------+---------+------+------------+-------+
Если Вы не хотите видеть все строки из Вашей таблицы, только укажите
столбцы, в которых вы заинтересованы, отделяя их запятыми. Например, если Вы
хотите знать, когда Ваши животные были рождены, выберите столбцы
name и birth:
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 | +--------+
Однако, обратите внимание, что запрос просто находит
поле owner из каждой записи, и некоторые из них появляются
больше, чем однажды. Чтобы минимизировать вывод, получите каждую уникальную
запись вывода только однажды, добавляя ключевое слово 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 |
+--------+---------+------------+
Вы, возможно, отметили в предшествующих примерах, что строки результатов
не отображаются ни в каком специфическом порядке. Однако, часто проще
исследовать вывод запроса, когда строки сортируются некоторым способом. Чтобы
отсортировать результат, используйте предложение 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 | +----------+------------+
Чтобы сортировать в обратном порядке, добавьте к имени столбца, по
которому Вы сортируете, ключевое слово 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 сортируются в порядке возрастания.
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 даты.
Часть выражения, которое сравнивает значения MM-DD, выставляется
в 1 или 0, что корректирует различие лет, если CURRENT_DATE
стоит ранее в году, чем рождение (birth). Полное выражение
несколько перегружено, так что псевдоним (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 |
+----------+------------+--------------+------+
Чтобы отсортировать вывод по возрасту (age), а не по имени
(name), примените другое предложение 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. Затем, для тех,
которые не-NULL, вычислите разницу между death и
birth:
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 специальное значение. Подробности в
разделе "2.3.4.6 Работа со значениями
NULL".
Что делать, если Вы хотите знать, какие животные имеют дни рождения в
следующем месяце? Для этого типа вычисления, год и день не годятся. Вы просто
хотите извлекать часть месяца из столбца birth. MySQL
обеспечивает несколько функций извлечения частей даты, типа
YEAR(), MONTH() и DAYOFMONTH(). Чтобы
увидеть, как это работает, выполните простой запрос, который отображает
значения 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 | +----------+------------+--------------+
Поиск животных с днями рождения в наступающем месяце прост. Предположим,
что текущий месяц апрель. Значение month равно 4, и Вы ищете
животных, рожденных в мае (month=5):
mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5; +-------+------------+ | name | birth | +-------+------------+ | Buffy | 1989-05-13 | +-------+------------+
Имеется маленькое осложнение, если текущий месяц декабрь. Здесь надо искать животных, рожденных в январе.
Вы можете даже написать запрос так, чтобы это работало независимо от того,
каков текущий месяц. DATE_ADD() позволяет Вам добавлять интервал
времени к заданной дате. Если Вы добавляете месяц к значению
NOW(), а затем извлекаете часть месяца с помощью
MONTH(), результатом как раз и будет тот месяц, в котором надо
искать дни рождения:
mysql> SELECT name, birth FROM pet
-> WHERE MONTH(birth) = MONTH(DATE_ADD(NOW(), INTERVAL 1 MONTH));
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.
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, которые являются синонимами).
Некоторые характеристики расширенных регулярных выражений:
Чтобы проиллюстрировать, как выполняется работа регулярных выражений,
показанные выше запросы 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 Version 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} в операторе repeat-n-times:
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 |
+-------+--------+---------+------+------------+-------+
Базы данных часто используются, чтобы ответить на вопрос о том, как часто некоторые данные попадаются в таблице. Например, Вы могли бы узнать, сколько домашних животных Вы имеете, или сколько домашних животных имеет каждый владелец, или Вы могли бы выполнять различные виды переписи животных.
Подсчет общего количества животных, которых Вы имеете, аналогичен вопросу
о том, сколько строк находятся в таблице pet? Дело в том, что
имеется одна запись на домашнее животное. Функция COUNT()
считает число не-NULL результатов, так что запрос для подсчета
животных выглядит следующим образом:
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,
чтобы сгруппировать вместе все записи для каждого владельца
(owner). Без него все, что Вы получите, это сообщение об ошибках:
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 |
+---------+------+----------+
Таблица pet описывает, каких домашних животных Вы имеете.
Если Вы хотите записывать другую информацию относительно их, типа событий в
их жизнях, подобно посещениям ветеринара, или когда рождалось потомство, Вы
нуждаетесь в другой таблице. Что эта таблица должна включать? Требуется:
Согласно этим данным инструкция CREATE TABLE для таблицы
событий (event) могла бы выглядеть следующим образом:
mysql> CREATE TABLE event (name VARCHAR(20), date DATE,
-> type VARCHAR(15), remark VARCHAR(255));
Как с таблицей pet, проще всего загрузить начальные записи,
создав разграниченный табуляциями текстовый файл, содержащий информацию:
| Fluffy | 1995-05-15 | Потомство | 4 котенка, 3 female, 1 male |
| Buffy | 1993-06-23 | Потомство | 5 щенков, 2 female, 3 male |
| Buffy | 1994-06-19 | Потомство | 3 щенка, 3 female |
| Chirpy | 1999-03-21 | Ветеринар | Необходимо выпрямление клюва |
| Slim | 1997-08-03 | Ветеринар | Сломано ребро |
| Bowser | 1991-10-12 | Конура | |
| Fang | 1991-10-12 | Конура | |
| Fang | 1998-08-28 | День рождения | Подарок: новая жевательная игрушка |
| Claws | 1998-03-17 | День рождения | Подарок: ошейник от блох |
| Whistler | 1998-12-09 | День рождения | Первый день рождения |
Загрузите записи так:
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 вносит в список две таблицы потому, что
запрос должен получить информацию из обеих таблиц.
name.
Запрос использует предложение WHERE, чтобы заставить совпасть
записи в двух таблицах, основываясь на значениях name.
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 |
+--------+------+--------+------+---------+
В этом запросе мы определяем псевдонимы для имени таблицы, чтобы обратиться к столбцам и указываем прямо, с которым образцом таблицы каждая ссылка столбца связана.
Что, если Вы забыли имя базы данных, или таблицы или ее структуру (например, как столбцы именованы)? MySQL решает эту проблему через несколько инструкций, которые обеспечивают информацию относительно баз данных и таблиц, которые это поддерживают.
Вы уже видели вызов SHOW DATABASES, который вносит в список
базы данных, управляемые сервером. Чтобы выяснять, которая база данных в
настоящее время выбрана, используйте функцию DATABASE():
mysql> SELECT DATABASE(); +------------+ | DATABASE() | +------------+ | menagerie | +------------+
Если Вы не выбрали базу данных, результат пуст.
Чтобы выяснять какие таблицы хранит текущая база данных (например, когда Вы не уверены относительно имени таблицы), используйте эту команду:
mysql> SHOW TABLES; +---------------------+ | Tables in menagerie | +---------------------+ | event | | 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 | | +---------+-------------+------+-----+---------+-------+
Field указывает имя столбца, Type тип данных для
столбца, Null указывает, может или нет столбец содержать
значения NULL, Key указывает, индексирован или нет
столбец, а Default определяет значение столбца по умолчанию.
Если Вы имеете индексы на таблице, SHOW INDEX FROM tbl_name
производит информацию относительно их.
Имеются примеры того, как решить некоторые общие проблемы с MySQL.
Некоторые из примеров используют таблицы shop, чтобы хранить
цену каждого изделия (номер элемента, item) для некоторых
торговцев (trader). Предположим, что каждый торговец имеет одну
фиксированную цену на изделие, тогда пара (item,
trader) является первичным ключом для записей.
Запустите инструмент командной строки mysql и
выберите базу данных:
mysql your-database-name
В большинстве инсталляций MySQL Вы можете использовать имя базы данных test.
Вы можете создать таблицу для примера так:
CREATE TABLE shop (article INT(4) UNSIGNED ZEROFILL DEFAULT '0000'
NOT NULL, dealer CHAR(20) DEFAULT '' NOT NULL,
price DOUBLE(16,2) DEFAULT '0.00' NOT NULL,
PRIMARY KEY(article, dealer));
INSERT INTO shop VALUES
(1,'A',3.45),(1,'B',3.99),(2,'A',10.99),
(3,'B',1.45),(3,'C',1.69),(3,'D',1.25),(4,'D',19.95);
Посмотрим данные примера:
mysql> SELECT * FROM shop; +---------+--------+-------+ | article | dealer | price | +---------+--------+-------+ | 0001 | A | 3.45 | | 0001 | B | 3.99 | | 0002 | A | 10.99 | | 0003 | B | 1.45 | | 0003 | C | 1.69 | | 0003 | D | 1.25 | | 0004 | D | 19.95 | +---------+--------+-------+
Что является самым большим значением для элемента?
SELECT MAX(article) AS article FROM shop +---------+ | article | +---------+ | 4 | +---------+
Допустим, надо найти код, торговца и цену наиболее дорогого изделия.
В ANSI SQL это легко может быть выполнен подзапросом:
SELECT article, dealer, price FROM shop
WHERE price=(SELECT MAX(price) FROM shop)
В MySQL (который еще не имеет поддержки вложенных запросов) это делается за два шага:
SELECT.
SELECT article, dealer, price FROM shop WHERE price=19.95
Другое решение состоит в том, чтобы сортировать все строки по убыванию
цены и получить только первую из них, используя специфическое для MySQL
предложение LIMIT:
SELECT article, dealer, price FROM shop ORDER BY price DESC LIMIT 1
ОБРАТИТЕ ВНИМАНИЕ: Если имеется несколько изделий с
одинаковой ценой (например, каждое по 19.95), то LIMIT
показывает только одно из них.
Что является самой высокой ценой на изделие?
SELECT article, MAX(price) AS price FROM shop GROUP BY article +---------+-------+ | article | price | +---------+-------+ | 0001 | 3.99 | | 0002 | 10.99 | | 0003 | 1.69 | | 0004 | 19.95 | +---------+-------+
Для каждого изделия, найдите торгового агента с наиболее высокой ценой.
В ANSI SQL я сделал бы это подзапросом:
SELECT article, dealer, price FROM shop s1
WHERE price=(SELECT MAX(s2.price) FROM shop s2
WHERE s1.article = s2.article);
В MySQL проще всего добиться такого эффекта так:
Это может быть легко выполнено через временную таблицу:
CREATE TEMPORARY TABLE tmp (article INT(4) UNSIGNED ZEROFILL
DEFAULT '0000' NOT NULL, price DOUBLE(16,2) DEFAULT '0.00'
NOT NULL);
LOCK TABLES shop read;
INSERT INTO tmp SELECT article, MAX(price) FROM shop GROUP BY article;
SELECT shop.article, dealer, shop.price FROM shop, tmp
WHERE shop.article=tmp.article AND shop.price=tmp.price;
UNLOCK TABLES;
DROP TABLE tmp;
Если Вы не используете таблицу TEMPORARY, Вы должны также
блокировать таблицу tmp.
Можно это сделать одиночным запросом?
Да, но только используя совершенно неэффективный прием MAX-CONCAT trick:
SELECT article, SUBSTRING(MAX(CONCAT(LPAD(price,6,'0'),dealer)), 7)
AS dealer, 0.00+LEFT(MAX(CONCAT(LPAD(price,6,'0'),dealer)), 6)
AS price FROM shop GROUP BY article;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0001 | B | 3.99 |
| 0002 | A | 10.99 |
| 0003 | C | 1.69 |
| 0004 | D | 19.95 |
+---------+--------+-------+
Вы можете применять переменные пользователя MySQL, чтобы помнить результаты без того, чтобы сохранять их во временных переменных на клиенте.
Например, чтобы найти изделия с самыми высокими и низкими ценами, Вы можете сделать следующее:
select @min_price:=min(price),@max_price:=max(price) from shop; select * from shop where price=@min_price or price=@max_price; +---------+--------+-------+ | article | dealer | price | +---------+--------+-------+ | 0003 | D | 1.25 | | 0004 | D | 19.95 | +---------+--------+-------+
Вы не нуждаетесь во внешних ключах, чтобы соединить 2 таблицы.
Единственная вещь, которую MySQL не делает, это CHECK, чтобы
удостовериться, что ключи, которые Вы используете, действительно, существуют
в таблице и автоматически не удаляет строки из таблицы с внешним ключом. Если
Вы используете Ваши ключи обычным порядком, это только улучшит работу:
CREATE TABLE persons (id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(60) NOT NULL, PRIMARY KEY (id));
CREATE TABLE shirts (id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
color ENUM('red', 'blue', 'orange', 'white', 'black')
NOT NULL, owner SMALLINT UNSIGNED NOT NULL REFERENCES persons,
PRIMARY KEY (id));
INSERT INTO persons VALUES (NULL, 'Antonio Paz');
INSERT INTO shirts VALUES (NULL, 'polo', 'blue', LAST_INSERT_ID()),
(NULL, 'dress', 'white', LAST_INSERT_ID()),
(NULL, 't-shirt', 'blue', LAST_INSERT_ID());
INSERT INTO persons VALUES (NULL, 'Lilliana Angelovska');
INSERT INTO shirts VALUES (NULL, 'dress', 'orange', LAST_INSERT_ID()),
(NULL, 'polo', 'red', LAST_INSERT_ID()),
(NULL, 'dress', 'blue', LAST_INSERT_ID()),
(NULL, 't-shirt', 'white', LAST_INSERT_ID());
SELECT * FROM persons;
+----+---------------------+
| id | name |
+----+---------------------+
| 1 | Antonio Paz |
| 2 | Lilliana Angelovska |
+----+---------------------+
SELECT * FROM shirts;
+----+---------+--------+-------+
| id | style | color | owner |
+----+---------+--------+-------+
| 1 | polo | blue | 1 |
| 2 | dress | white | 1 |
| 3 | t-shirt | blue | 1 |
| 4 | dress | orange | 2 |
| 5 | polo | red | 2 |
| 6 | dress | blue | 2 |
| 7 | t-shirt | white | 2 |
+----+---------+--------+-------+
SELECT s.* FROM persons p, shirts s WHERE p.name LIKE 'Lilliana%'
AND s.owner = p.id AND s.color <> 'white';
+----+-------+--------+-------+
| id | style | color | owner |
+----+-------+--------+-------+
| 4 | dress | orange | 2 |
| 5 | polo | red | 2 |
| 6 | dress | blue | 2 |
+----+-------+--------+-------+
MySQL не оптимизирует запрос, когда Вы ищете на двух различных ключах,
объединенных с помощью OR. Поиск на одном ключе с
OR оптимизирован хорошо.
SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1'
OR field2_index = '1'
Причина в том, что авторы не имели времени, чтобы придумать эффективный
способ обработать это в общем случае. Обработка AND теперь
полностью общая и работает очень хорошо.
Сейчас Вы можете сделать все это очень эффективно, используя таблицу
TEMPORARY. Этот тип оптимизации также очень хорош, если Вы
используете очень сложные запросы, где сервер SQL путается и делает
оптимизацию в неправильном порядке.
CREATE TEMPORARY TABLE tmp
SELECT field1_index, field2_index FROM test_table
WHERE field1_index = '1';
INSERT INTO tmp
SELECT field1_index, field2_index FROM test_table
WHERE field2_index = '1';
SELECT * from tmp;
DROP TABLE tmp;
Вышеупомянутый способ выполнить этот запрос в действительности вызывает
UNION для двух запросов.
Следующее предлагает идею относительно того, как Вы можете использовать разрядные функции группы, чтобы вычислить число дней, которое пользователь посетил страничку в сети.
CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL,
day INT(2) UNSIGNED ZEROFILL);
INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),
(2000,2,2),(2000,2,23),(2000,2,23);
SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days
FROM t1 GROUP BY year,month;
+------+-------+------+
| year | month | days |
+------+-------+------+
| 2000 | 01 | 3 |
| 2000 | 02 | 2 |
+------+-------+------+
Вышеупомянутый запрос вычисляет, сколько различных дней использовались для данной комбинации лет с конкретным месяцем с автоматическим удалением двойных записей.
mysql в пакетном режимеВ предыдущих разделах Вы использовали mysql в интерактивном
режиме, чтобы ввести запросы и рассматривать результаты. Вы можете также
выполнять mysql в пакетном режиме. Чтобы сделать это, поместите
команды, Вы хотите выполнить в файл, затем сообщать, чтобы mysql
читал ввод из файла:
shell> mysql < batch-file
Если Вы должны определить параметры подключения в командной строке, команда вызова могла бы выглядеть следующим образом:
shell> mysql -h host -u user -p < batch-file Enter password: ********
Когда Вы используете mysql этим способом, Вы создаете файл
скрипта, а затем выполняете скрипт.
Почему использование скриптов удобно? Имеется несколько причин:
mysql выполнил его снова.
shell> mysql < batch-file|more
shell> mysql < batch-file > mysql.out
cron. В этом случае
Вы должны использовать пакетный режим.Заданный по умолчанию выходной формат несколько иной (более краткий),
когда Вы выполняете mysql в пакетном режиме. Например, вывод
SELECT DISTINCT species FROM pet напоминает этот образец, когда
выполнен в интерактивном режиме:
+---------+ | species | +---------+ | bird | | cat | | dog | | hamster | | snake | +---------+
Но когда выполнен в пакетном режиме, он подобен этому:
species bird cat dog hamster snake
Если Вы хотите получать интерактивный выходной формат в пакетном режиме,
используйте mysql -t. Для добавления к выводу команд, которые
были выполнены, используется mysql -vvv.
Два университета ведут большой исследовательский проект. Проект включает часть опроса, где все близнецы в Швеции старше, чем 65 лет, интервьюируются по телефону. Близнецы, у которых встречаются некоторые критерии, передаются на следующюю стадию. На этой последней стадии близнецы, которые хотят участвовать, посещаются группой врачей и подвергаются всесторонним тестам. Большее количество информации относительно проекта есть по адресу:
http://www.imm.ki.se/TWIN/TWINUKW.HTM
Последняя часть проекта управляется Web-интерфесом, написанным на Perl с базой данных под MySQL.
Каждую ночь (исследования ведутся днем) все данные из интервью перемещаются в базу данных MySQL.
Следующий запрос используется, чтобы определить, кто из близнецов идет во вторую часть проекта:
select concat(p1.id, p1.tvab) + 0 as tvid,
concat(p1.christian_name, " ", p1.surname) as Name,
p1.postal_code as Code, p1.city as City, pg.abrev as Area,
if(td.participation = "Aborted", "A", " ") as A,
p1.dead as dead1, l.event as event1, td.suspect as tsuspect1,
id.suspect as isuspect1, td.severe as tsevere1,
id.severe as isevere1, p2.dead as dead2, l2.event as event2,
h2.nurse as nurse2, h2.doctor as doctor2, td2.suspect as tsuspect2,
id2.suspect as isuspect2, td2.severe as tsevere2,
id2.severe as isevere2, l.finish_date from twin_project as tp
left join twin_data as td on tp.id = td.id and tp.tvab = td.tvab
left join informant_data as id on tp.id = id.id and tp.tvab=id.tvab
left join harmony as h on tp.id = h.id and tp.tvab = h.tvab
left join lentus as l on tp.id = l.id and tp.tvab = l.tvab
left join twin_data as td2 on p2.id = td2.id and p2.tvab = td2.tvab
left join informant_data as id2 on p2.id = id2.id and
p2.tvab=id2.tvab left join harmony as h2 on p2.id = h2.id and
p2.tvab = h2.tvab left join lentus as l2 on p2.id = l2.id and
p2.tvab = l2.tvab, person_data as p1, person_data as p2,
postal_groups as pg where p1.id = tp.id and p1.tvab = tp.tvab and
p2.id = p1.id and p2.ptvab = p1.tvab and tp.survey_no = 5 and
(p2.dead = 0 or p2.dead=9 or (p2.dead=1 and (p2.death_date=0 or
(((to_days(p2.death_date)-to_days(p2.birthday))/365) >= 65))))
and ((td.future_contact = 'Yes' and td.suspect = 2) or
(td.future_contact = 'Yes' and td.suspect = 1 and id.suspect=1) or
(ISNULL(td.suspect) and id.suspect=1 and id.future_contact='Yes') or
(td.participation = 'Aborted' and id.suspect = 1 and
id.future_contact = 'Yes') or (td.participation = 'Aborted' and
ISNULL(id.suspect) and p2.dead = 0)) and l.event = 'Finished'
and substring(p1.postal_code, 1, 2) = pg.code
and (h.nurse is NULL or h.nurse=00 or h.doctor=00)
and not (h.status = 'Refused' or h.status = 'Aborted'
or h.status = 'Died' or h.status = 'Other') order by tvid;
Как Вам такой запрос? Даю некоторые объяснения:
concat(p1.id, p1.tvab) + 0 as tvid
id и
tvab в числовом порядке. Добавление 0 к результату
заставляет MySQL обрабатывать его именно как число.
id
tvab
1 или 2.
ptvab
tvab. Когда tvab равно
1, ptvab принимает значение 2, и
наоборот. Это существует, чтобы проще оптимизировать запрос.Этот запрос показывает среди других вещей, как делать поиск на таблице из
той же самой таблицы с объединением (p1 и p2). В
примере это используется, чтобы проверить, умер ли партнер близнецов до 65
лет. Если так, строка не будет возвращена.
Все вышеприведенное существует во всех таблицах с информацией по
близнецам. Мы имеем ключ на паре id,tvab (все таблицы) и другой
паре id,ptvab (только в person_data), чтобы
сделать запросы быстрее.
На нашей машине (200MHz UltraSPARC) этот запрос возвращает приблизительно 150-200 строк и требует времени меньше, чем одна секунда.
Текущее число записей в таблицах, используемых выше:
| Таблица | Строк |
person_data | 71074 |
lentus | 5291 |
twin_project | 5286 |
twin_data | 2012 |
informant_data | 663 |
harmony | 381 |
postal_groups | 100 |
Каждое интервью завершается кодом состояния event. Запрос,
показанный ниже, используется, чтобы отобразить таблицу по всем парам
близнецов, у которых код одинаковый.
select t1.event, t2.event, count(*) from lentus as t1, lentus as t2,
twin_project as tp where t1.id = tp.id and t1.tvab=tp.tvab
and t1.id = t2.id and tp.survey_no = 5 and t1.tvab='1' and
t2.tvab='2' group by t1.event, t2.event;
|
Закладки на сайте Проследить за страницей |
Created 1996-2024 by Maxim Chirkov Добавить, Поддержать, Вебмастеру |