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

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

LOAD TABLE

Инструкция LOAD TABLE копирует таблицу с главного сервера на подчиненный сервер:

LOAD TABLE имя FROM MASTER

OPTIMIZE TABLE

Инструкция OPTIMIZE TABLE исправляет содержимое табличного файла. Ее мат таков:

OPTIMIZE TABLE таблица

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

Обычно лишь таблицы типа и BDB поддерживают оптимизацию. Можно сконфигурировать программу MySQL на поддержку оптимизации таблиц других ти пов, но такая оптимизация реализуется путем перестройки таблицы, как если бы была выполнена инструкция ALTER TABLE. Более того, в настоящее время для таблиц типа BDB инструкция OPTIMIZE TABLE эквивалентна инструкции ANALYZE TABLE.

PURGE MASTER LOGS

Инструкция PURGE MASTER LOGS предназначена для удаления старых журналь ных файлов на главном сервере. Ее синтаксис таков:

PURGE MASTER LOGS TO

Инструкция удаляет заданный журнальный файл и все предыдущие файлы в по следовательном порядке. Эту инструкцию можно вводить даже тогда, когда подчи ненный сервер читает журнальные файлы в процессе репликации. Если инструкция попытается удалить используемый файл, ее выполнение аварийно завершится. С по мощью инструкции SHOW STATUS можно узнать, какие файлы читаются подчинен ными серверами в данный момент. Инструкция SHOW LOGS выдает список журналь ных файлов главного сервера.

RENAME TABLE

Инструкция RENAME TABLE меняет имя одной или нескольких таблиц:

RENAME TABLE таблица ТО таблица ТО

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

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

RESTORE TABLE

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

REVOKE

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

Ее формат таков:

REVOKE тип тип

ONтаблица

FROM пользователь

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

ROLLBACK

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

Инструкция SELECT предназначена для извлечения информации из таблиц. Она

имеет следующий синтаксис:

SELECT

[DISTINCT | | ALL]

[SQL_BIG_RESULT I

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

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

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

Каждому выражению в списке возвращаемых столбцов тоже может быть присвоен псевдоним с помощью предложения AS. Этот псевдоним появится в заголовке воз вращаемой таблицы. Благодаря псевдониму на выражение можно ссылаться в других предложениях инструкции SELECT, например GROUP BY. Если псевдоним представ ляет собой несколько слов с пробелами, требуются кавычки (листинг 13.35).

SELECT AS

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

Флаг DISTINCT указывает на необходимость удаления дублирующихся записей из таблицы результатов. В данном случае дубликатами считаются такие записи, которые имеют одинаковые значения во всех столбцах таблицы результатов. Речь не идет о про верке первичного ключа или ограничения уникальности. Например, если запросить значения поля "Возраст"таблицы сотрудников, то наверняка некоторые значения будут одинаковыми, хотя в исходной таблице все записи различаются по идентификатору.

— это синоним флага DISTINCT. Флаг ALL разрешает появление дуб ликатов и установлен по умолчанию.

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

Флаг SQL_BIG_RESULT заставляет программу MySQL созда вать в случае необходи мости временные таблицы на диске. Это требуется для запросов к большому числу таблиц или запросов, возвращающих большую таблицу результатов, особенно если ее

нужно сортировать при наличии флага DISTINCT или предложения ORDER BY. Флаг SQL_SMALL_RESULT говорит о том, что временные таблицы должны создаваться в памяти. В новых версиях MySQL этот флаг не нужен.

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

Флаг STRAIGHT_JOIN говорит о том, что таблицы должны включаться в объеди нение в том порядке, в котором они перечислены в предложении FROM.

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

[ESCAPED BY [LINES TERMINATED BY

Поскольку запись в файл осуществляется на сервере, необходимо иметь привиле гию FILE. Более того, запрещается перезаписывать существующий файл. Файл, соз данный в режиме OUTFILE, можно будет загрузить с помощью инструкции LOAD DATA INFILE (см. выше). Режим DUMPFILE предназначен для записи столбцов типа BLOB или TEXT. В этом режиме запрос должен возвращать одну запись с полем. Управляющие символы в данном случае не используются.

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

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

Синтаксис табличной ссылки таков:

AS псевдоним

[USE INDEX (индекс, [IGNORE INDEX (индекс,

Перед именем таблицы может стоять имя базы данных. Предложение AS задает псевдоним таблицы. Этот псевдоним можно использовать во всех остальных предло жениях инструкции, в частности в операциях объединения, в условиях отбора, в спи ске возвращаемых столбцов и т.д. Предложение USE INDEX заставляет программу MySQLиспользовать указанные индексы, если это возможно, а предложение IGNORE INDEX, наоборот, запрещает некоторые индексы. Эти подсказки позволяют производительность запроса.

Операция CROSS JOIN создает произведение таблиц. В этом случае каждая строка левой таблицы по очереди объединяется с каждой строкой правой таблицы и ся в таблицу произведения. Операция приводит тем же результатам.

В листинге 13.36 выполняется таблицы, т.е. создается произведе ние двух копий одной и той же таблицы. Если в исходной таблице 4 строки, то в

произведения их будет 16.

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

SELECT

FROM team JOIN team

Операция INNER JOIN включает в таблицу результатов только те строки таблицы которые удовлетворяют определенному критерию. Это называется внутренним Предложение USING задает включаемые в объеди нение. Эти столбцы должны называться одинаково в обеих таблицах. В листин ге 13.37 таблица игроков (player) объединяется с таблицей команд (team) по столб цу идентификатора команды

SELECT

FROM player INNER JOIN team USING (TeamID)

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

SELECT

FROM player INNER JOIN team

ON

В левое внешнее объединение (LEFT JOIN) включаются все записи внутреннего объединения плюс строки первой которые не имеют связи ни с одной стро кой второй таблицы. Правое внешнее объединение (RIGHT JOIN) строится так же, но относительно правой, а не левой таблицы. Строки, которым не найдено соответ ствие, дополняются значениями NULL. Ключевое слово OUTER является необязатель ным. Оно предназначено для сохранения совместимости с другими СУБД.

Естественное объединение (NATURAL JOIN) характерно тем, что сравниваются толь ко одноименные столбцы двух таблиц, причем выполняется их проверка на равенст во. Обычно это действительно самый "естественный" тип объединения, так как не нужно указывать ни имена столбцов, ни условие отбора. По умолчанию создается внутреннее естественное объединение, но с помощью ключевых слов LEFT и RIGHT его можно сделать левым или правым внешним объединением.

Некоторые предложения инструкции SELECT доступны лишь тогда, когда

ствует предложение FROM. Они выполняются в строго определенном порядке: снача ла WHERE, затем GROUP BY, потом HAVING и, наконец ORDER BY.

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

Предложение WHERE задает условия, в соответствии с которыми из таблицы про изведения отбираются строки. Если в предложении FROM была указана одна таблица, то условие отбора является критерием фильтрации ее строк. Если же выполняется операция объединения, то в предложении WHERE указываются правила сопоставле ния столбцов объединяемых таблиц. В листинге 13.39 создается то же самое объеди нение, что и в листинге 13.38, но на этот раз с использованием предложения WHERE.

SELECT

FROM player, team

WHERE

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

SELECT

FROM player team

WHERE

AND

OR

Предложение GROUP BY содержит правило группировки строк по одному или скольким столбцам. Это позволяет применять к столбцам статистические функции (см. главу 12, "Встроенные функции"). Статистические функции разрешены в списке воз вращаемых столбцов и в предложении HAVING, но не в предложении WHERE, поскольку оно вычисляется раньше, чем GROUP BY, т.е. до того, как формируются группы записей.

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

ORDER BY Pitchers

В предложении GROUP BY можно ссылаться на столбцы по имени или по номеру. Столбцы нумеруются слева направо, а нумерация начинается с единицы. Разрешается также группировать записипо не указанным в предложении SELECT. Такие столбцы, естественно, не имеют номера.