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

Вы можете авторизоваться на сайте всего одним кликом:

2016-07-08
SQL → Справочник Основных SQL-Выражений
Какие SQL-Команды нужно знать

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

Основные SQL-Выражения

Справочник построен по следующим правилам:

  • Команды перечислены в алфавитном порядке;
  • Необязательный участок любой команды заключен в квадратные скобки ([ ]);
  • Список элементов, среди которых следует выбрать один, заключен в фигурные скобки ({}), а сами элементы разделены вертикальной чертой (|);
  • Многоточие означает, что предыдущий элемент может повторяться.

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

ALTER TABLE

ALTER [IGNORE] TABLE tbl_name action [ , action ...]

В этом коде action обозначает определенное соответствующим образом действие.

Запросы ALTER TABLE используются для изменения структуры таблицы без потери хранящихся в ней данных (если не считать очевидных случаев, таких как удаление столбца). Ниже приведены основные действия, которые выполняют дан ный запрос.

  • ADD [COLUMN] create_definition [FIRST I AFTER column_name ]
    Добавляет в таблицу новый столбец. Для create_definition используется такой же синтаксис, как и для запроса CREATE TABLE, которому посвящен отдельный раздел. По умолчанию столбец добавляется в конец таблицы, но при желании вы можете задать ему любую позицию, используя ключевые слова FIRST и AFTER column_name. Параметр COLUMN не играет никакой роли и используется исключительно для наглядности записи.
  • ADD INDEX [index_name] (index_col_name , ...)
    Создает новый индекс для ускорения поисковых запросов, в которых фигурируют указанные столбцы. Вы можете указать имя индекса с помощью index_name. Если вы этого не сделаете, ему будет присвоено имя по умолчанию, основанное на имени первого столбца, который в нем используется. Создавая индекс на основе столбцов с типами CHAR и/и ли VARCHAR, вы вправе определять ко личество символов, которое необходимо индексировать, используя параметр index_col_name . Например, myColumn (5) проиндексирует первых пять символов столбца myColumn. Этот параметр обязателен при индексировании столбцов типа BLOB и TEXT.
  • ADD FULLTEXT [index_name] (index_col_name, ... )
    Создает полнотекстовый индекс для указанных столбцов. Особый вид индекса по зволяет выполнять сложный поиск по столбцам типа CHAR, VARCHAR и TEXT, используя функцию MATCH из состава MySQL.
  • ADD FOREIGN KEY [index_name] (index_col_name, ...) reference_definition

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

    Параметр reference_definition указывает таблицу и столбцы, которые фигурируют в ограничении.

    У reference_definition есть необязательные участки с ключевыми словами ON DELETE и ON UPDATE, которые определяют, что должно про исходить с записями, когда удаляются или изменяются соответствующие строки в другой таблице. Более подробно с данным действием вы можете ознакомиться в справочном руководстве по MySQL (http://dev.mysql.com/doc/ mysql/en/innodb-foreign-key-constraints.html).

  • ADD PRIMARY KEY (index_col_name, ...)
    Создает индекс с именем PRIMARY для заданных строк, объявляя его первичным ключом для всей таблицы. Все значения (или их комбинации) должны быть уникальными, как описано в следующем действии ADD UNIQUE. Если таблица уже содержит первичный ключ, возникнет ошибка. Параметр index_col_name определяется так же, как в рассмотренной ранее команде ADD INDEX.
  • ADD UNIQUE [index_name] (index_col_name, ...)
    Создает индекс для заданных столбцов с условием, что все значения в столбце (или ком бинации значений, если столбцов несколько) должны быть уникальными. Параметры index_name и index_col_name играют ту же роль, что и в действии ADD INDEX.
  • ALTER [COLUMN] col_name {SET DEFAULT value I DROP DEFAULT}
    Создает для столбца новое значение по умолчанию (SET DEFAULT) или удаляет старое (DROP DEFAULT). Слово COLUMN роли не играет и является необязательным.
  • CHANGE [COLUMN] col_name create_definition
    Зам еняет существующий столбец col_name на новый, описанный в параметре create_definition (его синтаксис совпадает с тем, что описан в разделе CREATE TABLE). При необходимости имеющиеся данные преобразуются и помещаются в новый столбец. Стоит заметить, что внутри create_definition содержится имя нового столбца, поэтому данное действие используется также для переименования. Если вы хотите оставить старое имя, не забудьте указать его дважды: один раз для col_name , а второй — для create_definition, или воспользуйтесь описанным ниже действием MODIFY.
  • DISABLE KEYS и ENABLE KEYS. При добавлении в таблицу большого коли чества записей MySQL затрачивает много времени на обновление индексов. Если перед этим выполнить команду ALTER TABLE ... DISABLE KEYS, MySQL отложит обновление. Как только записи будут добавлены, выполните ALTER TABLE ... ENABLE KEYS, чтобы обновить индексы для всех новых строк сразу. Такой подход позволяет сэкономить время по сравнению с одиночными обновлениями.
  • DROP [COLUMN] col_name
    Назначение этого действия понятно исходя из его синтаксиса. Оно полностью удаляет столбец из таблицы. Хранящиеся в нем данные уже не подлежат восстановлению, поэтому будьте внимательны, когда указываете имя столбца. Как и в предыдущих случаях, слово COLUMN можно опустить.
  • DROP PRIMARY KEY, DROP INDEX index_name 
    и
    DROP FOREIGN KEY index_name
    Суть этих действий понятна без дополнительных объяснений: они удаляют из таблицы первичный ключ, индекс и ограничение внешнего ключа соответственно.
  • MODIFY [COLUMN] create_definition.
    Почти ничем не отличается от вышеупомянутого действия CHANGE. Позволяет задать новое определение для столбца в таблице, но по умолчанию оставляет ему старое имя. Таким образом, нет необходимости переопределять столбец с тем же именем в па раметре create_definition , чей синтаксис описан в разделе CREATE TABLE. Как и ранее, слово COLUMN является необязательным. Хотя это довольно удобное действие, оно не входит в стандартный синтаксис языка SQL и добавлено для совместимости с базами данных Oracle, имеющими такое же расширение.
  • ORDER BY col_name
    Сортирует записи по конкретному столбцу. Тем не менее это не гарантирует упорядоченность записей, добавленных или из мененных после выполнения запроса. Единственная практическая выгода от использования такого действия заключается в повышении производитель ности таблицы, данные из которой сортируются в приложении в рамках за просов SELECT. В некоторых случаях сортировка происходит быстрее, если строки уже выстроены в (почти) правильном порядке.
  • RENAME [ТО] new_tbl_name
    Переименовывает таблицу. Ключевоеслово TO роли не играет и является необязательным.
  • table_options
    Используя тот же синтаксис, что и в запросе CREATE TABLE, данное действие позволяет добавлять и изменять расширенные свойства таблицы, которые задокументированы в справочном руководстве по MySQL (http://dev.mysql.com/doc/refman/5.5/en/create-table.html).

ANALYZE TABLE

ANALYZE TABLE tbl_name [, tbl_name ...]

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

BEGIN

BEGIN

Выполняет то же действие, что и START TRANSACTION.

COMMIT

COMMIT

После того как выполнена команда START TRANSACTION и начинается транз акция (то есть когда режим автоматического выполнения отключен), MySQL на чинает накапливать изменения, вносимые в базу данных, чтобы применить их одновременно. Это делается с помощью команды COMMIT, которая к тому же за вершает транзакцию.

CREATE DATABASE

CREATE DATABASE [IF NOT EXISTS] db_name

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

CREATE INDEX

CREATE [UNIQUE I F ULLTEXT]  INDEX index_name ON tbl_name (col_name [( length)] , ...)

Запрос создает новый индекс для уже существующей таблицы. Работает точно так же, как команда ALTER TABLE ADD {INDEX I UNIQUE I FULLTEXT}, описанная в разделе ALTER TABLE.

CREATE TABLE

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] [db_name.] tbl_name {
   [(create_definition, ...) ]
   [table_options] [[IGNORE I REPLACE] select_statement]

   I LIKE [db_name.]old_tbl_name }

Запрос CREATE TABLE используется для создания таблицы с именем tbl_name в текущей базе данных или в той, что была указана с помощью параметра db_name. Если ввести ключевое слово TEMPORARY, таблица исчезнет, как только разорвется соединение, в ходе которого ее создали. Временная таблица, созданная под тем же именем, что и существующая, будет скрывать последнюю от текущей клиентской сессии до тех пор, пока временную таблицу не удалят или пока не завершится текущая сессия. Другие клиенты смогут видеть настоящую таблицу.

Запрос завершится неудачно, если таблица с таким именем уже существует, а ключевое слово TEMPORARY не указано. Однако этого можно избежать: команда IF NOT EXISTS позволит проигнорировать запрос. Ошибка также произойдет, если у вас нет необходимых прав для выполнения данного запроса.

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

DELETE

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
   { FROM tbl_name
      [WHERE where_clause]
      [ORDER BY order_by_expr]
      [LIMIT rows]

   | tbl_name[, tbl_name ...]
      FROM table_references
      [WHERE where_clause]

   | FROM tbl_name [ , tbl_name ...]
      USING table_references
      [WHERE where_clause]
}

Если для первой разновидности этого запроса не указать необязательные (но желательные) команды WHERE или LIMIT, он удалит в указанной таблице все строки. Оператор WHERE здесь работает так же, как и в запросе SELECT (см. раздел SELECT). Команда LIMIT позволяет указать максимальное количество удаля емых строк. С помощью оператора ORDER BY можно задать порядок, в котором удаляются записи, а в сочетании с командой LIMIT он позволяет выполнять та кие действия, как, например, удаление десяти строк с самыми старыми записями в таблице.

Вторая и третья разновидности равнозначны. Они позволяют удалять строки из нескольких таблиц с помощью одной команды, подобно тому как запрос SELECT извлекает и объединяет данные из разных источников (см. раздел SELECT). Па раметры в table_references работают точно так же, как и в случае с SELECT: вы можете создавать простые и внешние объединения. Оператор WHERE позволяет сузить диапазон удаляемых строк. Первый список в table_references определяет таблицы, в которых производится удаление. Таким образом, вы имеете воз можность ограничить набор результатов, используя сложные объединения, а потом удалить строки только в одной из охваченных таблиц.

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

DESCRIBE / DESC

{DESCRIBE I DESC} tbl_name [col_name I wild]

Команда предоставляет информацию о столбцах конкретной таблицы. Это может быть как набор столбцов, удовлетворяющих заданному шаблону с метасим символами % и _(wild), так и один столбец с определенным именем (col_name). Полученный результат включает имя столбца, его тип, значение по умолчанию, дополнительные параметры, например AUTO_lNCREMENT, и информацию о том, допускает ли столбец NULL в качестве значения и содержит ли индекс.

DROP DATABASE

DROP DATABASE [IF EXISTS ] db_name

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

DROP INDEX

DROP INDEX index_name ON tbl_name

Работает аналогично команде ALTER TABLE DROP INDEX, описанной в разделе ALTER TABLE.

DROP TABLE

DROP TABLE [IF EXISTS] tbl_name [, tbl_name, ...]

Запрос полностью удаляет одну или несколько таблиц. Использовать его следует осторожно, поскольку после его выполнения данные уже нельзя восстано вить. При нехватке полномочий команда завершится ошибкой. То же произойдет, если указанной таблицы не существует. Чтобы избежать появления сообщения об ошибке во втором случае, укажите параметр IF EXISTS.

EXPLAIN

Запрос имеет две разные формы. Первая является эквивалентом команд DESCRIBE tbl_name и SHOW COLUMNS FROM tbl_name.

EXPLAIN tbl_name

Вторая выглядит так:

EXPLAIN select_statement

В качестве select_statement выступает корректный запрос SELECT. Команда возвращает описание того, как MySQL определяет результаты выполне ния оператора SELECT. Она пригодится при поиске участков выборки, на которых можно ускорить выполнение запроса с использованием индексов. Команда также позволяет определить, выполняются ли многотабличные запросы в оптимальном порядке. Чтобы узнать, как управлять этим порядком, переопределяя оптимизатор MySQL, обратитесь к разделу SELECT и ознакомьтесь с параметром выборки STRAIGHT_J0IN. Исчерпывающую информацию о том, как интерпретировать результаты выполнения запроса EXPLAIN, вы найдете в справочном руководстве по MySQL (http://dev.mysql.com/doc/refman/5.7/en/explain.html).

GRANT

GRANT priv_type [{column_list)] , ...
   ON {tbl_name I * I * . * I db_name.*}
   TO username [IDENTIFIED BY 'password'] , ...
   [WITH GRANT OPTION]

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

Полное описание этого и других запросов, предназначенных для управления учетными записями пользователей, ищите в справочном руководстве по MySQL (http://dev.mysql.com/doc/mysql/en/account-management-sql.html).

INSERT

INSERT [LOW_PRIORITY I DELAYED] [IGNORE] [INTO] tbl_name
   { [(col_name, ...)] VALUES (expression,  ...) , ...
   | SET col_name=expression, col_name=expression, ...
   | [(col_name, ...) SELECT ...]
   [ON DUPLICATE KEY UPDATE col_name=expression[, ...]]
}

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

  • LOW_PRIORITY. Выполнение запроса отложится до тех пор, пока таблицу не перестанут использовать другие клиенты.
  • DELAYED. С точки зрения клиента запрос завершается мгновенно, однако операция добавления продолжает выполняться в фоновом режиме. Данный параметр следует использовать, если необходимо добавить большое количе ство строк, но нет желания ждать завершения процесса. Имейте в виду, что после окончания работы такого отложенного запроса клиент не будет знать идентификатор последней добавленной записи в столбце AUTO_INCREMENT. К примеру, в РНР метод lastInsertId из объекта PDO не сможет работать корректно.
  • IGNORE. Как правило, когда операция добавления вызывает конфликт в столбцах, помеченных как PRIMARY KEY или UNIQUE, она завершается неудачно и возвращает ошибку. Данный параметр позволяет скрыть неудачу: новая строка не добавится, но и не появится сообщение об ошибке.

Использовать слово INTO необязательно: оно не влияет на выполнение запроса.

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

Вторая разновидность команды INSERT используется для добавления одной строки. Значения для полей строки указываются в виде col_name=value.

Третья и последняя форма команды INSERT подразумевает, что в качестве добавляемых строк выступают результаты выполнения запроса SELECT. Если вы хотите опустить список с названиями строк, то результирующий набор выбор ки должен содержать значения для каждого столбца в нужном порядке. Запрос SELECT, входящий в состав выражения INSERT, позволяет обойтись без оператора ORDER BY, но при этом в операторе FROM нельзя использовать таблицу, в которую добавляются записи.

Столбцы, которым ничего не присваивается (например, они не указаны в списке), принимают значения по умолчанию. Как правило, то же происходит со столбцами, помеченными как NOT NULL, если им задается значение NULL. Однако, если в MySQL включен параметр DONT_USE_DEFAULT_FIELDS, данная операция вы зовет ошибку, поэтому таких ситуаций лучше избегать.

Необязательный оператор ON DUPLICATE KEY UPDATE срабатывает, когда запрос INSERT пытается добавить в таблицу новую запись, дублируя уже име ющееся значение. Это происходит только при наличии параметров UNIQUE или PRIMARY KEY. Данный оператор предотвращает ошибку и описывает, каким образом должна обновиться существующая запись. Внешне он очень похож на команду UPDATE: в нем указывается один или несколько столбцов и новые значе ния, которые им следует присвоить. Более подробно об этом рассказано в разделе UPDATE.

LOAD DATA INFILE

LOAD DATA [LOW_PRIORITY I CONCURRENT] [LOCAL] INFILE
   ' file_name.txt ' [REPLACE I IGNORE] INTO TABLE tbl_name
   [FIELDS
      [TERMINATED BY 'string']
      [[OPTIONALLY] ENCLOSED BY 'char ']
      [ESCAPED BY 'char']]
   [LINES [STARTING BY ''] [TERMINATED BY 'string']]
   [IGNORE number LINES]
   [(col_name, ...) ]

Запрос используется для импорта данных из текстового файла, который хранится на MySQL-сервере или в клиентской системе (LOCAL). Это может быть файл, созданный с помощью команды SELECT INTO OUTFILE. Чтобы получить исчерпывающую информацию о запросе и о проблемах, которые возникают в ре зультате его использования, обратитесь к справочному руководству по MySQL (http://dev.mysql.com/doc/mysql/en/load-data.html).

OPTIMIZE TABLE

OPTIMIZE TABLE tbl_name [ , tbl_name ...]

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

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

RENAME TABLE

RENAME TABLE tbl_name TO new_table_name [, tbl_name2 TO ..., ...]

Запрос позволяет быстро и удобно переименовать одну или несколько таблиц. От ALTER TABLE tbl_name RENAME он отличается тем, что на время выполнения запроса все затронутые таблицы блокируются, поэтому ни один клиент не получит к ним доступ. Согласно справочному руководству по MySQL (http://dev.mysql.com/ doc/mysql/en/rename-table.html) атомарность, которая обеспечивается этой командой, позволяет заменить существующую таблицу ее пустым эквивалентом. К примеру, следующий код позволяет создать новую таблицу при достижении какого-то опре деленного количества записей.

CREATE TABLE new_table (...) ;
RENAME TABLE old_table TO backup_table, new_table TO old_table;

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

Чтобы выполнить данный запрос, нужно иметь право на использование комднд ALTER и DROP по отношению к оригинальной таблице, а также INSERT по отношению к новой. Запрос RENAME TABLE, завершившийся неудачей на полпути, автоматически отменяется, при этом изначальное состояние таблицы восстанавливается.

REPLACE

REPLACE [LOW_PRIORITY I DELAYED] [INTO] tbl_name
   { [(col_name, ...)] VALUES (expression, ...) , ...
   | [(col_name, ...)], SELECT ...
   | SET col_name=expression, col_name=expression , ... 
}

Команда похожа на INSERT. Если из-за столбца, отмеченного как PRIMARY KEY или UNIQUE, новая строка станет конфликтовать с уже имеющимся значением, команда REPLACE заменит старую запись.

REVOKE

REVOKE priv_type [{column_list)] , ...
   ON {tbl_name I * I *.* I db_name.*}
   FROM user, ...

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

ROLLBACK

ROLLBACK

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

SELECT

SELECT [select_options]
   select_expression, ...
   [INTO {OUTFILE | DUMPFILE} ' file_name' export_options]
   [FROM table_references
      [WHERE where_definition]
      [GROUP BY {col_name | col_pos} [ASC I DESC], ...]
      [HAVING where_definition]
      [ORDER BY {col_name |  col_pos } [ASC I DESC] , ...]
      [LIMIT [offset, ] rows]]

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

  • LL, DISTINCT и DISTINCTROW. Любой из этих параметров позволяет описывать поведение запроса при дублировании строк в результирующем наборе. ALL применяется по умолчанию и приводит к тому, что в ходе выполнения запроса выводятся все дублирующ иеся строки. Параметры DISTINCT и DISTINCTROW работают одинаково и убирают дубликаты.
  • HIGH__PRIORITY . Назначает запросу SELECT высокий приоритет. Как правило, при обновлении таблицы все запросы, в ходе которых происходит считывание данных (к ним относится и SELECT), переходят в режим ожидания. В этом случае, как только появится возможность выполнить запрос, команда SELECT HIGH_PRIORITY будет выполнена в первую очередь.
  • STRAIGHT_JOIN . Заставляет MySQL объединять таблицы, указанные в table_references в порядке их перечисления. Если вы считаете, что MySQL плохо оптимизирует данный запрос и выполняет его медленно, вы можете вмешаться в процесс с помощью данного аргумента. Более подробно об этом рассказано в подразделе «Объединения».
  • SQL_BUFFER_RESULT. Требует от MySQL сохранить результирующий набор во временной таблице. Пока результаты передаются клиенту, другие процессы имеют возможность получить доступ к таблицам, задействованным в запросе.
  • SQL_CACHE. Сообщает MySQL о том, что результат вы полнения запроса необходимо сохранять в кеше — области памяти, которая вы деляется сервером для хранения результатов работы часто запускаемых запросов. Таким образом, если содержимое соответствующих таблиц осталось прежним, вы полнять запрос заново не нужно. При этом MySQL можно настроить так, чтобы кешировались только запросы с параметром SQL_CACHE. Если кеш выключен, данный параметр ни на что не повлияет.
  • SQL_NO_CACHE . При его указании MySQL не станет кешировать результат текущего запроса. MySQL-сервер можно настроить таким образом, чтобы он помещал в кеш результаты выполнения всех запросов, для которых данный параметр не указан. Если кеш выключен, параметр SQL_NO_CACHE ни на что не повлияет.
  • SQL_CALC_FOUND_ROWS. Применяется в сочетании с оператором LIMIT. Вычисляет и сохраняет общее количество строк, которые были бы возвращены, если бы оператор LIMIT не был указан. Данное число можно извлечь с помощью запроса SELECT FOUND_ROWS ().

Параметр select_expression определяет поле результирующего набора, который возвращается в результате запроса. Обычно это столбец таблицы, за писанный в виде col_name, tbl_name.col_name или db_name.tbl_name.col_name. Форма записи зависит от того, какая точность требуется для верной идентификации сервером MySQL. Данный параметр способен ссылаться не только на столбец базы данных, но и на другие выражения. Вы можете использовать прос тые математические формулы с именами полей в качестве переменных, а также сложные выражения, вычисляемые с помощью функций MySQL. Например, вам необходимо вернуть дату в формате «Январь 1,2017», которая наступит через один месяц с настоящего момента.

SELECT DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%M %D, %Y')

Параметр select_expressions также может содержать псевдоним (или назначенное имя) для итогового столбца. Псевдоним указывается с помощью клю чевого слова [AS] , которое является необязательным. Подобное выражение ис пользуется, если на указанный столбец необходимо сослаться в каком-то другом участке запроса, например в операторах WHERE и ORDER BY.

SELECT, jokedate AS jd FROM joke ORDER BY jd ASC

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

SELECT * INTO OUTFILE '/home/user/myTable.txt'
   FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY
   LINES TERMINATED BY ' \n'
   FROM myTable

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

Чтобы записать все данные в одну строку без переносов и разделителей между столбцами, вместо OUTFILE используйте параметр DUMPFILE. С его помощью можно сбросить в файл хранящийся в таблице бинарный BLOB ( SELECT blobCol INTO DUMPFILE ...). Исчерпывающие сведения об операторе INTO вы найдете в справочном руководстве по MySQL (http://dev.mysql.com/doc/mysql/en/select.html). И н формация о том, как считать данные из созданного файла, находится в разделе LOAD DATA INFILE.

В операторе FROM содержится список таблиц, на основе которых формируется результирующий набор, а также инструкции о том, каким образом их следует объ единить. Параметр table_references, по сути, имя одной таблицы, которая мо жет быть назначена в качестве псевдонима с помощью (или без) ключевого слова AS, как было описано выше для select_expression . Если указать несколько таблиц, они объединятся. Более подробно вы узнаете об этом в подразделе «Объединения».

В участке where_definition оператора WHERE задается условие для строки, которая включается в результирующую таблицу, возвращаемую в ответ на запрос SELECT. Это может быть как простое условие (например, id=5), так и сложное с использованием функций MySQL и комбинаций из нескольких условий, совме щенных с помощью булевых операторов AND, OR или NOT.

Оператор GROUP BY позволяет указать один или несколько столбцов по имени, псевдониму или позиции (единица обозначает первый столбец в результи рующем наборе). Строки, значения которых совпадают по полям, представле ны в результирующем наборе одной записью. Как правило, данный оператор используется в сочетании с функциями группирования, такими как COUNT, МАХ и AVG, чтобы итоговые столбцы содержали сводку о созданных группах. По умолчанию результаты сортируются в порядке возрастания значений, содержащихся в сгруппированных полях. Возрастающий или убывающий порядок со ртировки задается явно с помощью соответствующих аргументов ASC или DESC, указанных после каждого столбца. Связанные наборы строк сортируются вначале по первому столбцу в списке, затем по второму и т. д.

Оператор WHERE выполняется до GROUP BY, поэтому условие не вправе ссылаться на столбцы, которые зависят от операций группирования. Чтобы описать условие для уже сгруппированного результирующего набора, воспользуйтесь оператором HAVING. Его синтаксис аналогичен синтаксису WHERE, но заданные условия обрабатываются непосредственно перед возвращением результатов и не подлежат оптимизации, поэтому применяйте оператор WHERE везде, где это возможно.

Оператор ORDER BY позволяет сортировать результаты в соответствии со значениями в одной или нескольких строках перед тем, как они будут возвращены. Каждый столбец указывается с помощью имени, псевдонима или позиции (единица обозначает первый столбец в результирующем наборе) и содержит аргумент ASC или DESC для задания порядка сортировки — по возрастанию (применяется по умолчанию) или убыванию соответственно. Связанные наборы строк сортируются сначала по первому столбцу, затем по второму и т. д.

Оператор LIMIT позволяет вернуть только часть тех результатов, которые запрос сгенерировал бы в обычных условиях. В простейшем случае LIMIT , возвращает только первые n строк из окончательного результирующего набора. Вы также можете указать сдвиг, используя запись вида LIMIT n , k. Таким образом, из всего результирующего набора вернется не более n строк, начиная с k. Для первой строки k = 0, для второй k = 1 и т. д.

Объединения

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

table_ref
table_references, table_ref
table_references [CROSS] JOIN table_ref
table_references INNER JOIN table_ref join_condition
table_references STRAIGHT_JOIN table_ref
table_references LEFT [OUTER] JOIN table_ref join_condition
   {OJ table_ref LEFT OUTER JOIN table_ref ON cond_expr}
table_references NATURAL [LEFT [OUTER]] JOIN table_ref
table_references RIGHT [OUTER] JOIN table_ref join_condition
table_references NATURAL [RIGHT [OUTER]] JOIN table_ref

Параметр table_ref определяется следующим образом:

table_name [[AS] alias] [USE INDEX { key_list)]
   [IGNORE INDEX {key_list)]

В роли join_condition может выступать одно из выражений:

ON cond_expr
USING (column_list)

Пусть вас не пугает разнообразие типов объединений. Рассмотрим принцип работы каждого из них.

В результате самого простого внутреннего объединения возвращаются все воз можные сочетания строк из первой и второй таблицы. В MySQL при выполнении внутреннего объединения вы можете разделять имена таблиц как посредством запятых, так и с помощью слов JOIN, CROSS JOIN или INNER JOIN (все они равнозначны).

Как правило, для создания внутренних объединений используют запятые, осо бенно в старом PHP-коде. Затем, применяя оператор WHERE внутри запроса SELECT, объявляется условие, сужающее набор возвращаемых комбинированных строк. Н а пример, это может быть сопоставление первичного ключа первой таблицы со столбцом второй. Однако сегодня такой неаккуратный подход считается дурным тоном.

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

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

SELECT * FROM tl INNER JOIN t2 USING (tid)

Это выражение эквивалентно следующему:

SELECT * FROM tl INNER JOIN t2 ON tl.tid = t2.tid

Оператор STRAIGHT_JOIN работает так же, как внутреннее объединение, за исключением того, что таблицы в нем обрабатываются в порядке перечисления (сначала левая, потом правая). Как правило, MySQL выбирает тот порядок, который обеспечивает наиболее быструю обработку, но если вы уверены, что ваш способ лучше, воспользуйтесь STRAIGHT_JOIN.

Второй вид объединений называется внешним и реализуется в MySQL операто рами LEFT/RIGHT [OUTER] JOIN. Слово OUTER не играет особой роли и не является обязательным. При левом внешнем объединении (LEFT) любая запись из таблицы слева, которая не имеет соответствий в таблице справа, определяемых условием join_condition, будет значиться в результирующем наборе как одна строка. Всем столбцам, полученным из правой таблицы, присвоится значение NULL.

Синтаксис {ОJ ... } эквивалентен обычному левому внешнему объединению. Он добавлен для совместимости с другими базами данных, поддерживающими стандарт ODBC (Open Database Connectivity).

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

Естественное объединение является автоматическим в том смысле, что оно само сопоставляет строки из двух разных таблиц по столбцам с одинаковыми именами. Таким образом, если таблица material имеет столбец authorid, который ссылается на записи в таблице author, чей первичный ключ — столбец с анало гичным именем authorid, то вы можете выполнить объединение двух таблиц по общему столбцу (если исходить из того, что больше столбцов с одинаковыми именами нет).

SELECT * FROM joke NATURAL JOIN author

Оператор UNION

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

SELECT ...
   UNION [ALL | DISTINCT]
   SELECT ...
      [UNION [ALL | DISTINCT]
         SELECT ...] ...

По умолчанию оператор UNION устраняет дублирующиеся строки, чтобы все записи в результирующем наборе являлись уникальными. Такое поведение можно задать явно с помощью параметра DISTINCT, но в сущности это ничего не изменит. Чтобы разрешить дублирование результатов, воспользуйтесь параметром ALL.

SET

SET option = value, ...

Запрос позволяет задать набор параметров как на клиентской, так и на серверной стороне.

Например, чтобы отключить режим автоматического выполнения для текущей сессии, используется команда SET autocommi t = 0. В сущности, результат будет тем же, если выполнить запрос START TRANSACTION, а затем запустить его снова после выполнения команды COMMIT или ROLLBACK. Если параметр autocommit отключен, вы всегда будете находиться в режиме транзакции и такие запросы, как INSERT, UPDATE и DELETE не вступят в силу до тех пор, пока вы не примените их с помощью к

1411
0
Пожалуйста, авторизируйтесь, чтобы скачать архив с файлами урока