Получение данных из MariaDB

Самый простой способ извлечения данных из MariaDB — использование оператора SELECT. Поскольку оператор SELECT является важным оператором SQL, он имеет множество доступных опций. Нет необходимости знать или использовать все из них—вы можете выполнить очень простые операторы SELECT, если этого достаточно для ваших потребностей. Однако по мере более активного использования MariaDB вам могут потребоваться более мощные операторы SELECT. В этой статье мы разберем основы оператора SELECT и перейдем к более сложным операторам SELECT; мы перейдем от начального уровня к более среднему, и надеемся, что вы найдете пользу от этой статьи независимо от вашего уровня навыков. Для абсолютных новичков, только начинающих работать с MariaDB, вы можете прочитать статью Основы MariaDB.

Основные элементы

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

SELECT * FROM books;

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

SELECT isbn, title, author_id
FROM books;

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

SELECT isbn, title, author_id
FROM books 
LIMIT 5;

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

SELECT isbn, title, author_id
FROM books 
LIMIT 5, 10;

Селективность и порядок

Предыдущие операторы сузили количество извлеченных столбцов и строк, но они не были очень избирательными. Предположим, что нам нужны только книги, написанные определенным автором, скажем, Достоевским. Просматривая таблицу авторов, мы видим, что его идентификационный номер автора — 4729. Используя оператор WHERE, мы можем получить список книг из базы данных для этого конкретного автора следующим образом:

SELECT isbn, title
FROM books
WHERE author_id = 4729
LIMIT 5;

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

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

SELECT isbn, title
FROM books
WHERE author_id = 4729
ORDER BY title ASC
LIMIT 5;

Обратите внимание, что оператор ORDER BY следует за оператором WHERE и предшествует оператору LIMIT. Этот оператор не только отобразит строки в порядке по названию книги, но и извлечет только первые пять в соответствии с упорядочением. То есть, MariaDB сначала извлечет все строки в соответствии с оператором WHERE, отсортирует данные по оператору ORDER BY и затем отобразит ограниченное количество строк в соответствии с оператором LIMIT. Отсюда и порядок операторов. Вы могли заметить, что мы включили флаг ASC. Он указывает MariaDB отсортировать строки в порядке возрастания для имени столбца, за которым он следует. Однако это не обязательно, так как порядок возрастания по умолчанию. Тем не менее, если мы хотим отобразить данные в порядке убывания, мы заменим флаг на DESC. Для сортировки по нескольким столбцам дополнительные столбцы можно указать в операторе ORDER BY в виде списка, разделенного запятыми, каждый с флагами ASC или DESC, если это предпочтительнее.

Более удобные и более сложные запросы

До сих пор мы работали с одной таблицей данных, содержащей информацию о книгах для вымышленного книжного магазина. Конечно, в базе данных обычно содержится более одной таблицы. В этой конкретной базе данных также есть таблица authors, в которой содержатся имена и другая информация об авторах. Чтобы выбрать данные из двух таблиц в одном операторе SELECT, мы должны указать MariaDB, что хотим объединить таблицы, и предоставить точку соединения. Это можно сделать с помощью оператора JOIN, как показано в следующем операторе SQL, с последующими результатами:

SELECT isbn, title, 
CONCAT(name_first, ' ', name_last) AS author
FROM books
JOIN authors USING (author_id)
WHERE name_last = 'Dostoevsky'
ORDER BY title ASC
LIMIT 5;

+-------------+------------------------+-------------------+
| isbn        | title                  | author            |
+-------------+------------------------+-------------------+
|  0553212168 | Brothers Karamozov     | Fyodor Dostoevsky |
|  0679420290 | Crime & Punishment     | Fyodor Dostoevsky |
|  0553211757 | Crime & Punishment     | Fyodor Dostoevsky |
|  0192834118 | Idiot                  | Fyodor Dostoevsky |
|  067973452X | Notes from Underground | Fyodor Dostoevsky |
+-------------+------------------------+-------------------+
5 rows in set (0.00 sec) 

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

...
JOIN authors ON author_id = row_id
...

Этот фрагмент основан на предположении, что ключевое поле в таблице authors называется не author_id, а row_id. Можно сказать гораздо больше о соединениях, но это потребует гораздо более длинной статьи. Если вы хотите узнать больше о соединениях, обратитесь к странице документации MariaDB по синтаксису оператора JOIN.

Вновь взглянув на последний полный оператор SQL выше, вы, должно быть, заметили функцию CONCAT(), которую мы добавили к текущему оператору. Эта функция для строк объединяет указанные значения столбцов и строк, чтобы получить одно упорядоченное поле в результатах. Мы также использовали параметр AS для изменения заголовка набора результатов для поля на author. Это значительно улучшает читабельность. Поскольку мы объединили таблицы books и authors, мы смогли искать книги по фамилии автора, а не искать сначала идентификатор автора. Это гораздо более удобный метод, хотя и более сложный. Кстати, мы можем попросить MariaDB проверить столбцы из обеих таблиц, чтобы сузить поиск. Мы просто добавим пары column = value, разделенные запятыми, в оператор WHERE. Обратите внимание, что строка, содержащая имя автора, заключена в кавычки—в противном случае строка будет интерпретироваться как имя столбца, и мы получим ошибку.

Фамилия Достоевский иногда пишется как Достоевский, а также несколькими другими способами. Если мы не уверены, как она написана в таблице authors, мы можем использовать оператор LIKE вместо знака равенства, а также символ подстановки. Если мы предполагаем, что фамилия автора, вероятно, написана одним из двух упомянутых способов, мы можем ввести что-то вроде этого:

SELECT isbn, title, 
CONCAT(name_first, ' ', name_last) AS author
FROM books
JOIN authors USING (author_id)
WHERE name_last LIKE 'Dostoevsk%'
ORDER BY title ASC
LIMIT 5;

Это будет соответствовать любой фамилии автора, начинающейся с Достоевск. Обратите внимание, что символ подстановки здесь — не звездочка, а знак процента.

Некоторые флаги

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

Первый флаг, который может быть задан, сразу после ключевого слова SELECT, это ALL. По умолчанию выбираются все строки, которые соответствуют условиям различных операторов, поэтому это не обязательно. Если вместо этого мы хотим отобразить только первое вхождение определенного критерия, мы можем добавить опцию DISTINCT. Например, для авторов, таких как Достоевский, будет несколько изданий одной и той же книги. В показанных ранее результатах вы, возможно, заметили, что было две копии Преступления и наказания, однако у них разные номера ISBN и разные издательства. Предположим, что для нашего поиска мы хотим отобразить только одну строку для каждого названия. Мы можем сделать это следующим образом:

SELECT DISTINCT isbn, title
FROM books
JOIN authors USING (author_id)
WHERE name_last = 'Dostoevsky'
ORDER BY title;

Мы немного упростили текущий оператор SQL для большей ясности. Этот оператор приведет к отображению только одной строки для Преступления и наказания, и это будет первая найденная.

Если мы извлекаем данные из очень загруженной базы данных, по умолчанию любые другие операторы SQL, одновременно изменяющие или обновляющие данные, будут выполнены до оператора SELECT. Операторы SELECT считаются иметь более низкий приоритет. Однако если мы хотим, чтобы определенный оператор SELECT имел более высокий приоритет, мы можем добавить ключевое слово HIGH_PRIORITY. Изменив предыдущий оператор SQL с учетом этого фактора, мы введем его следующим образом:

SELECT DISTINCT HIGH_PRIORITY isbn, title
FROM books
JOIN authors USING (author_id)
WHERE name_last = 'Dostoevsky'
ORDER BY title;

Вы, возможно, заметили в одном из примеров ранее, где показаны результаты, что есть строка состояния, которая указывает количество строк в наборе результатов. Это меньше, чем количество строк, найденных в базе данных, которые соответствовали критериям оператора. Это меньше, потому что мы использовали оператор LIMIT. Если мы добавим флаг SQL_CALC_FOUND_ROWS непосредственно перед списком столбцов, MariaDB посчитает количество найденных столбцов, даже если используется оператор LIMIT.

SELECT SQL_CALC_FOUND_ROWS isbn, title
FROM books
JOIN authors USING (author_id)
WHERE name_last = 'Dostoevsky'
LIMIT 5;

Однако для получения этой информации нам необходимо использовать функцию FOUND_ROWS() следующим образом:

SELECT FOUND_ROWS();

+--------------+
| FOUND_ROWS() |
+--------------+
|           26 |
+--------------+

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

Заключение

Есть еще несколько параметров и возможностей оператора SELECT, которые мы пропустили, чтобы сохранить статью разумной длины. Популярный из них — оператор GROUP BY для вычисления агрегированных данных для столбцов (например, среднего значения). Существуют несколько флагов для кеширования результатов и оператор для экспорта набора результатов в текстовый файл. Если вы хотите узнать больше об операторе SELECT и всех доступных параметрах, обратитесь к онлайн-документации по операторам SELECT.