MySQL. Библиотека профессионала - Аткинсон Л

222Глава Инструкции SQL

Влистинге 13.24 приведены результаты инструкции DESCRIBE. В первых двух лонках указаны имя и тип каждого столбца. В колонке "Null" будет стоять если столбец допускает значения NULL. Для столбцов, являющихся частью первичного ключа, в четвертой колонке будет стоять PRI. Если же столбец входит в состав друго го индекса, то в этой колонке будет указано В пятой колонке отображается зна чение по умолчанию. В последней колонке приводится дополнительная информация

остолбце. В частности, здесь указывается, является ли столбец

mysql> DESCRIBE

DROP DATABASE

Инструкция DROP DATABASE удаляет базу данныхиз системы:

DROP DATABASE [IF EXISTS] имя

Вместе с базой данных удаляются все таблицы и вся хранимая информация. Спе цификация IF EXISTS подавляет вывод сообщения об ошибке, выдаваемого в случае, если указанная база данных не существует.

Базы данных реализованы в виде каталогов, содержащих файлы данных и индек сов. Инструкция DROP DATABASE удаляет в заданном каталоге все файлы, созданные программой MySQL. Другие файлы остаются. Если каталог пуст, он тоже удаляется. Таким образом, можно удалить все таблицы, но сама база данных останется, если в ней есть посторонние файлы. Удаление соответствующего каталога файловой систе мы тоже приводит к удалению базы данных.

Таблицы типа нужно удалять вручную.

Полныйсписокинструкций 223

DROP FUNCTION

Инструкция DROP FUNCTION удаляет из памяти код функции, загруженной с по мощью инструкции CREATE FUNCTION:

DROP FUNCTION имя

DROP INDEX

Инструкция DROP INDEX удаляет индекс таблицы, выполняя для этого инструк цию ALTER TABLE:

DROP INDEX имя ON таблица

DROP TABLE

Инструкция DROP TABLE удаляет все файлы, относящиеся к таблице. Она имеет следующий синтаксис:

Спецификация IF EXISTS подавляет вывод сообщения об ошибке, выдаваемого в случае, если заданная таблица не существует. Можно указывать несколько имен таб лиц, разделяя их запятыми.

Флаги RESTRICT и CASCADE игнорируются в MySQL версии 3.23. Они предназна чены для выполнения сценариев, созданных в другихСУБД.

В листинге 13.25 демонстрируется удаление двух таблиц: и log.

DROP TABLE IF EXISTS message, log

EXPLAIN

Инструкция EXPLAIN описывает способ указанного запроса или же эмулируетинструкциюDESCRIBEдлязаданнойтаблицы:

EXPLAIN I

Вкачестве запроса разрешается вводить любую допустимую инструкцию SELECT.

Врезультате будет выдана таблица с описанием индексов, используемых для выпол нения операции объединения. Благодаря этому можно узнать, влияют ли индексы на повышение производительности объединений. Подробнее об оптимизации запросов рассказывается в главе 26, "Оптимизация". В листинге 13.26 показаны типичные ре выдаваемые инструкцией EXPLAIN.

224 Глава Инструкции SQL

mysql> EXPLAIN

SELECT *

FROM user, WHERE

Таблица, возвращаемая инструкцией EXPLAIN, содержит одну строку для каждой таблицы в порядке их упоминания. В первой колонке приводится имя таблицы, во второй—типобъединения. Возможныследующие типы:ALL, const, index, range, ref, system.

Тип ALL означает полное сканирование таблицы. Обычно для такой таблицы тре буется индекс.

Тип const означает, что в таблице есть одна совпадающая запись, а тип system указывает на таблицу, состоящую из одной записи. В обоих случаях программа MySQL может хранить запись в памяти в течение всего времени выполнения запроса, что приведет к повышению производительности.

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

Тип index означает, что сканированию подлежит весь индекс. Это почти так же плохо, как и тип ALL, но чуть быстрее, поскольку количество элементов индекса обычно меньше, чем число записей в таблице.

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

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

Вколонке possible_keys перечислены индексы, которые могут быть использо ваны для повышения эффективности запроса. Здесь может стоять NULL.

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

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

Вколонке Extra приводитсядополнительная информация об объединении. Ключевое

слово Distinct говорит о том, что MySQL перестает сканировать записи при нии первого совпадения. Сообщение Not exists означает, что программа вала левое внешнее пропустив последующие операции сканирования для совпавшей комбинации. Сообщение range checked each record выдается, когда

226 Глава Инструкции SQL

GRANT

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

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

столбцов допустимы привилегии INSERT, SELECT и UPDATE. К таблицам и базам данных применимы эти же привилегии, плюс ALTER, CREATE, DELETE, DROP и INDEX.

щие привилегии являются глобальными: FILE, PROCESS, RELOAD и SHUTDOWN.

Полный список инструкций 227

Предложение ON и список столбцов задают контекст привилегии. Таблица может идентифицироваться по собственному имени либо в сочетании с именем базы данных. Метасимвол * соответствует всем объектам диапазона. Например, запись * оз начает выдачу привилегий для всех таблиц базы данных store (листинг 13.27). ный символ * соответствует всем таблицам текущей базы данных. В случае записи * * привилегииявляютсяглобальными.

GRANT SELECT, INSERT, UPDATE, DELETE

ON

TO

WITH GRANT OPTION

Имя пользователя — это строка длиной не более 16 символов. Если вслед за этим именем стоят символ @ и сетевое имя, то привилегия применима только тогда, когда пользователь регистрируется на сервере с указанного компьютера. Имена пользова теля и компьютера необходимо заключать в кавычки, когда в них присутствуют мета символы. Например, запись 1еоп@% обозначает пользователя под ключающегося с любого компьютера домена Вместо сетевого имени может стоять и IP адрес. Если же узел вообще не указан, то информация о том, какому узлу принадлежит пользователь, не будет учитываться.

Предложение IDENTIFIED BYзадает пароль пользователя. Если учетная запись пользователя уже существует, его пароль меняется. Задать пароль можно также с по мощьюинструкцииSET.

Спецификация WITH GRANT OPTIONS разрешает пользователю предоставлять аналогичные привилегиидругим пользователям.

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

INSERT

Инструкция INSERT добавляетзаписивтаблицу.Еесинтаксистаков:

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

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

228 Глава Инструкции SQL

Клиентская программа блокируется до момента завершения инструкции INSERT, что не всегда удобно. Флаг DELAYED отменяет такую установку, помещая инструкцию INSERT в очередь и немедленно возвращая управление программе. Этот флаг приме ним лишь к таблицам типа

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

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

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

INSERT IGNORE

INTO team (Name)

VALUES

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

В предложении SET указывается список имен столбцов и соответствующих им зна чений. В этом случае список столбцов после имени таблицы не нужен (листинг 13.30).

INSERT IGNORE

INTO team

SET

С помощью подчиненной инструкции SELECT можно отобрать группу записей для вставки в таблицу (листинг 13.31). Стандарт языка SQL запрещает ссылаться на одну и ту же таблицу в главной и подчиненной инструкции.

Полный список инструкций 229

INSERT IGNORE

KILL

Инструкция KILL разрывает соединение с базой данных. Ей передается иденти фикатор потока:

KILL поток

Все потоки пронумерованы. Инструкция SHOW PROCESSLIST отображает список активных потоков. Обычно пользователь имеет право уничтожать только свои венные потоки, но с помощью привилегии PROCESS ему можно разрешить удалять любые потоки.

LOCK TABLES

С помощью инструкции LOCK TABLES можно запретить другим потокам осущест влять запись в ту или иную таблицу. Синтаксис инструкции таков:

Инструкция LOCK TABLE ожидает бесконечно долго, пока требуемые блокировки не будут получены. В ней указывается список имен таблиц, разделенных запятыми. После каждого имени задается тип блокировки: READ (нежесткая) или WRITE (жесткая). Нежесткая блокировка запрещает запись в таблицу всем потокам, включая тот, кому она принадлежит. Жесткая блокировка разрешает доступ к таблице для чтения и записи только потоку владельцу.

В листинге 13.32 показан пример блокирования двух таблиц. Из таблицы team из влекается максимальное значение поля Score, которое заносится в переменную Далее это значение вставляется в таблицу record, после чего обе таблицы раз

блокируются.

LOCK TABLES team t READ, record WRITE;

SELECT

FROM team

INSERT INTO record VALUES

UNLOCK TABLES;

230 Глава 13. Инструкции SQL

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

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

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

Для снятия блокировок предназначена инструкция UNLOCK TABLES. Кроме того, блокировки снимаются при завершении потока или вводе другой инструкции LOCK TABLES. Подробнее о блокировках рассказывалось в главе 9, "Транзакции и парал лельные вычисления". С блокировками связаны также функции GET_LOCK и RELEASE_LOCK описанныевглаве12,"Встроенные функции".

LOAD DATA INFILE

Инструкция LOAD DATA INFILE читает данные из текстового файла и вставляет их в указанную таблицу. Ниже показан общий формат этойинструкции:

ложен, пока не завершатся все операции чтения таблицы. Если отсутствует флаг LOCAL, файл открывается на сервере, в противном случае — в клиентской файловой системе. Для открытия файла на сервере следует иметь привилегию FILE.

Обычно необходимо указывать полное путевое имя файла. Неполные путевые имена вычисляются относительно каталога данных MySQL. Как правило, это каталог Файл, не имеющий путевого имени, открывается в каталоге базы данных, которая задана по умолчанию, например

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

Влистинге 13.33 демонстрируется загрузка содержимого файла .

втаблицу team.

Полный список инструкций 231

LOAD DATA INTO TABLE team

FIELDS TERMINATED BY OPTIONALLY ENCLOSED BY ESCAPED BY

LINES TERMINATED BY IGNORE 1 LINES

(Name, ID)

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

В спецификации TERMINATED BY указывается разделитель полей. Спецификация ENCLOSED BY задает символ (обычно кавычку), который используется для дополни тельного обособления значений. Флаг не играет никакой роли.

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

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

По умолчанию строки заканчиваются символом новой строки, выбор которого диктуется операционной системой. В UNIX это символ перевода строки. С помощью спецификации LINES TERMINATED BY можно задать другой конечный символ. Если

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

файлов, содержащих многобайтовые символы.

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

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

По умолчанию значения NULL записываются как \N. Если используются ограничи вающие кавычки, то значения NULL вводятся в явном виде, причем без кавычек (с ка вычками это будет воспринято как строка To же самое справедливо и для случая, когда управляющий символ незадан.

Инструкция SELECT позволяет записывать табличные данные в файл. Для этого в ней предусмотрено предложение INTO.