Сообщить об ошибке.

MySQL: Инструкция SELECT, cоставление запросов.

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

Содержание:


Синтаксис инструкции SELECT.

Примечание: символ | говорит о том что в данной SQL-инструкции можно использовать только один из параметров.

SELECT [ALL | DISTINCT] * | col1, col2 [AS name1], ..., expr AS name2, ... FROM table [AS tbl]
    WHERE condition 
    GROUP BY col | expr | pos [HAVING condition]
    ORDER BY col | expr | pos [ASC | DESC] 
    LIMIT count [offset, count]
    [OFFSET offset]

Примечание: при объединении таблиц, в SELECT используются инструкции UNION и JOIN, которые рассмотрены в отдельном материале.

Параметры, принимаемые SELECT:

  • * - указание этого символа приведет к выбору всех колонок таблицы. Его использование с другими элементами в списке выбора может привести к ошибке синтаксического анализа. Например, следующий запрос выдаст ошибку:

    SELECT AVG(score), * FROM t1 ...
    

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

  • col1, col2 - колонки таблицы table, данные из которых необходимо получить в итоговом запросе.

  • col2 [AS name1] - необязательное предложение AS name1, присваивает колонке таблицы col2 новое имя (псевдоним) name1. По этому псевдониму можно будет извлечь записи col2, отобранные в запросе. Например, если таблица с товаром products имеет столбцы с ценой за единицу товара и количеством товара то в запросе можно посчитать сумму и извлечь ее по псевдониму:

    SELECT product, (price * lot) AS amount FROM products
    

    При использовании модуля MySQLdb со стандартным классом курсора cursorclass извлечь данные можно следующим образом:

    with closing(db.cursor()) as cursor:
      # SQL-запрос
      sql = "SELECT product, (price * lot) AS amount FROM products WHERE amount > 0"
      # выполняем SQL-запрос
      cursor.execute(sql)
      # извлекаем результаты запроса
      for row in cursor.fetchall():
          product = row[0]
          amount = row[1]
    

    Этот псевдоним можно также использовать при сортировке (ORDER BY) и групповых выражениях (GROUP BY, HAVING).

    SELECT product, (price * lot) AS amount FROM products 
    WHERE amount > 0 ORDER BY amount
    
  • expr AS name2 - Выражение expr может быть строковой функцией, функцией преобразования даты и т.д. Если колонка имеет числовое значение, то expr может быть простым арифметическим действием (сложение, умножение и т.д.) и/или математической функцией (в выражении могут участвовать несколько числовых колонок). Предложение AS name2 - присваивает выражению псевдоним name2, по которому можно будет извлечь итоговые результаты вычисления expr для каждой отобранной строки в запросе. Этот псевдоним можно также использовать при сортировке (ORDER BY) и групповых выражениях (GROUP BY, HAVING). Простой пример объединения колонок first_name и last_name в запросе:

    SELECT CONCAT(first_name, last_name`) AS full_name FROM studend ORDER BY full_name
    

    Выражение expr также может быть подзапросом к другой таблице. Например ниже, к запросу из таблицы успеваемости студентов score добавляются их полные имена CONCAT(first_name, last_name)`.

    -- неоптимизированный запрос, представлен в качестве примера
    SELECT id_studend, score,
      (SELECT CONCAT(first_name, last_name`) 
      FROM studend WHERE studend.id=state.id_studend) AS full_name  
    FROM state WHERE score > 3 ORDER BY full_name
    

    Внимание! Этот неоптимизированный запрос представлен в качестве примера. Для получения аналогичного результата лучше использовать инструкцию объединения таблиц LEFT JOIN.

    Или может быть подзапросом к самой себе (коррелирующий подзапрос). Например ниже, из таблицы products выбираются товары product, стоимость которых price выше средней цены товаров AVG(price) для данного производителя factory:

    SELECT product, factory, price, 
      (SELECT AVG(price) FROM products AS SubProds 
      WHERE SubProds.factory=Prods.factory) AS AvgPrice
    FROM products AS Prods
    WHERE price > 
      (SELECT AVG(price) FROM products AS SubProds 
       WHERE SubProds.factory=Prods.factory);
    
  • table [AS tbl] - указывает таблицу или таблицы, из которых нужно получить выборку строк. Необязательное предложение AS tbl присваивает таблице table новое имя (псевдоним) tbl, которое можно использовать при указании колонок в виде tbl.col при объединении таблиц инструкцией JOIN.

  • condition - выражение, которое оценивается как истинное для каждой выбранной строки. Инструкция SELECT выбирает все строки, если нет предложения WHERE. В условии condition можно использовать любые функции и операторы, которые поддерживает MySQL, кроме агрегатных (групповых) функций.

  • pos - позиции столбцов, выбранные для вывода в инструкции SELECT. Позиции столбцов являются целыми числами и начинаются с 1.

    -- не рекомендуется использовать
    SELECT col1, col2, col3 FROM table ORDER BY 1, 3;
    
  • count - целое число, количество возвращаемых строк с начала результирующего набора.

  • offset - целое число, смещение выборки запроса до конца результирующего набора.

Возвращаемое значение SELECT.

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

Описание операторов, принимаемых инструкцией SELECT.


ALL | DISTINCT:

Модификаторы ALL и DISTINCT указывают, следует ли возвращать повторяющиеся строки. Модификатор ALL (по умолчанию) указывает, что должны быть возвращены все совпадающие строки, включая дубликаты. Модификатор DISTINCT указывает на удаление повторяющихся строк из набора результатов. Указание обоих модификаторов является ошибкой.

Что значит совпадающие строки? Это означает буквальный смысл этих слов. Например, если из таблицы в инструкции SELECT отбираются несколько колонок, то модификатор DISTINCT будет искать совокупное совпадение каждой из колонок с другими записями в выборке. Если в результирующей выборки из двух колонок будут встречаться записи [(foo, bar), (foo, baz), ...], то они не будут считаться как совпадающие строки. Для удаления записей модификатором DISTINCT совпадения должны встречаться в обоих колонках [(foo, bar), (foo, bar)] => в этом случае, результирующий набор будет содержать только одну запись [(foo, bar), ...]

В следующем примере запрос вернет уникальные записи col1 из таблицы table, удовлетворяющих условию WHERE.

SELECT DISTINCT col1 FROM table WHERE col2 > 10

WHERE condition:

Предложение WHERE, если оно задано, указывает условие или условия, которым должны удовлетворять итоговая выборка. Условие condition - это выражение, которое оценивается как ИСТИННОЕ для каждой выбранной строки.

Инструкция SELECT выбирает все строки, если нет предложения WHERE.

В предложении WHERE можно использовать результаты выполнения встроенных или пользовательских функций (математических выражений), которые, например, что-то делают со значением колонки и возвращают логическое значение (TRUE или 1, FALSE или 0). Пример с функцией REGEXP_LIKE().

-- отберет все записи из `str_col`, которые соответствуют 
-- регулярному выражению `regexp_pattern`
SELECT * FROM table WHERE REGEXP_LIKE(str_col, "regexp_pattern", "c");

Несколько выражений/условий condition могут соединяться логическими операторами OR и/или AND. Условия могут носить противоположный характер, если перед ним стоит отрицание NOT.

Операторы OR, AND и NOT можно выстраивать в логические цепочки, например:

SELECT * FROM table WHERE NOT ((a > x) OR ((a = x) AND (b > y)))

Более подробно об операторе WHERE смотрите в материале "Операторы, используемые с предложением WHERE в БД MySQL".

GROUP BY col [WITH ROLLUP]:

Предложение GROUP BY col1[, col2, ...] позволяет группировать результаты по указанным столбцам таблицы col1, col2, ... при их извлечении из базы данных. К сгруппированным результатам (столбцам) можно применять агрегатные (групповые) функций БД MySQL.

Предположим, что в таблице продаж есть столбцы год year, страна country, продукт product и прибыль profit. Чтобы суммировать содержимое таблицы за год, нужно использовать простую группу GROUP BY следующим образом:

SELECT year, SUM(profit) AS profit
       FROM sales
       GROUP BY year;
+------+--------+
| year | profit |
+------+--------+
| 2000 |   4525 |
| 2001 |   3010 |
+------+--------+

Выходные данные показывают общую (совокупную) прибыль profit за каждый год. Чтобы также определить общую прибыль, суммированную за все годы, необходимо самостоятельно сложить отдельные значения или выполнить дополнительный запрос. Или можно использовать необязательный оператор ROLLUP, который обеспечивает оба уровня анализа с помощью одного запроса. Добавление модификатора WITH ROLLUP к предложению GROUP BY приводит к тому, что запрос создает еще одну (суперагрегированную) строку, которая показывает общую сумму значений за все годы:

SELECT year, SUM(profit) AS profit
       FROM sales
       GROUP BY year WITH ROLLUP;
+------+--------+
| year | profit |
+------+--------+
| 2000 |   4525 |
| 2001 |   3010 |
| NULL |   7535 |
+------+--------+

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

Более подробную информацию о том, как можно использовать инструкцию GROUP BY col [WITH ROLLUP] для аналитической обработки смотрите в материале "Использование инструкций GROUP BY и HAVING в БД MySQL".

HAVING condition:

Предложение HAVING, как и предложение WHERE, определяет условия выбора. Предложение WHERE задает условия condition для столбцов в списке SELECT, но не может ссылаться на агрегатные функции. Предложение HAVING определяет условия condition для групп, обычно формируемые предложением GROUP BY. Результат запроса включает только группы, удовлетворяющие условиям HAVING.

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

SELECT COUNT(col1) AS col2 FROM tbl GROUP BY col2 HAVING col2=2;

Предпочтение отдается стандартному поведению SQL, поэтому, если имя столбца HAVING используется как в GROUP BY, так и в качестве столбца с псевдонимом в списке столбцов SELECT, то предпочтение отдается столбцу в столбце GROUP BY.

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

-- НЕПРАВИЛЬНО
SELECT col_name FROM tbl_name HAVING col_name > 0;
-- ПРАВИЛЬНО
SELECT col_name FROM tbl_name WHERE col_name > 0;

Более подробную информацию об инструкции HAVING смотрите в материале "Использование инструкций GROUP BY и HAVING в БД MySQL".

ORDER BY col [ASC | DESC]:

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

Чтобы получить строки в случайном порядке, можно использовать встроенную в MySQL функцию RAND():

-- выбор записей из таблицы в случайном порядке
SELECT col1, col2, col3  FROM tbl_name ORDER BY RAND();
-- получение выборки из 100 строк
-- выбранных в случайном порядке
SELECT col1, col2, col3 FROM tbl_name ORDER BY RAND() LIMIT 100;

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

Использование позиций столбцов (смотрите параметр pos синтаксиса SELECT) не рекомендуется, т.к. этот синтаксис был удален из стандарта SQL.

SELECT college, region, seed FROM tournament
  ORDER BY region, seed;
-- использование сортировки по двум столбцам: 
-- `region` - прямая и `seed` - обратная сортировка
SELECT college, region AS r, seed AS s FROM tournament
  ORDER BY r, s DESC;
-- использование позиций столбцов для сортировки
-- (не рекомендуется)
SELECT college, region, seed FROM tournament
  ORDER BY 2, 3;

LIMIT count [offset, count]:

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

  • В подготовленных запросах, параметры LIMIT можно указать с помощью маркера-заполнителя ? (%s - для модуля MySQLdb в Python) .
  • В хранимых функциях и процедурах MySQL, параметры LIMIT могут быть указаны с использованием целочисленных подпрограммных параметров или локальных переменных.

В инструкции LIMIT с двумя аргументами, - первый аргумент указывает смещение первой возвращаемой строки, а второй указывает максимальное количество возвращаемых строк. Смещение начальной строки равно 0 (НЕ 1):

-- запрос извлечет с 6 по 15 строки 
SELECT * FROM tbl LIMIT 5,10;

Чтобы получить все строки с определенного смещения до конца результирующего набора, можно использовать какое-то большое число для второго параметра. Этот оператор извлекает все строки с 96-й строки до последней:

SELECT * FROM tbl LIMIT 95,18446744073709551615;

С одним аргументом значение указывает количество возвращаемых строк с начала результирующего набора:

-- запрос извлечет первые 5 строк
SELECT * FROM LIMIT 5;

Другими словами, LIMIT count эквивалентен LIMIT 0, count.

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

SET @a=1;
PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?';
EXECUTE STMT USING @a;

Следующие MySQL-запросы возвращают строки со второй по шестую из таблицы tbl:

SET @skip=1; SET @numrows=5;
PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?, ?';
EXECUTE STMT USING @skip, @numrows;

Для совместимости с PostgreSQL, БД MySQL также поддерживает синтаксис смещения LIMIT count OFFSET n.

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

OFFSET offset:

Инструкция OFFSET указывает смещение первой возвращаемой строки выборки. Смещение начальной строки равно 0 (НЕ 1)

В синтаксисе MySQL эта инструкция используется для совместимости с БД PostgreSQL, например LIMIT count OFFSET n

-- запрос извлечет с 6 по 15 строки 
SELECT * FROM tbl LIMIT 10 OFFSET 5;