Выполнение запросов в mysql

Анализ с получением n-го количества первых показателей (Top-N Analysis)

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

SQL> SELECT emp_id, emp_name, job, manager, salary
FROM
(SELECT emp_id, emp_name, job, manager, salary,
RANK() OVER
(ORDER BY SALARY DESC NULLS LAST) AS Employee_Rank
FROM employees
ORDER BY SALARY DESC NULLS LAST)
WHERE employee_Rank < 5; 

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

Соединения (джойны)

Теперь мы хотим увидеть названия (не обязательно уникальные) всех книг Дэна Брауна, которые были взяты из библиотеки, и когда эти книги нужно вернуть:

Результат:

По большей части запрос похож на предыдущий за исключением секции . Это означает, что мы запрашиваем данные из другой таблицы. Мы не обращаемся ни к таблице “books”, ни к таблице “borrowings”. Вместо этого мы обращаемся к новой таблице, которая создалась соединением этих двух таблиц.

— это, считай, новая таблица, которая была сформирована комбинированием всех записей из таблиц «books» и «borrowings», в которых значения совпадают. Результатом такого слияния будет:

А потом мы делаем запрос к этой таблице так же, как в примере выше. Это значит, что при соединении таблиц нужно заботиться только о том, как провести это соединение. А потом запрос становится таким же понятным, как в случае с «простым запросом» из пункта 3.

Давайте попробуем чуть более сложное соединение с двумя таблицами.

Теперь мы хотим получить имена и фамилии людей, которые взяли из библиотеки книги автора “Dan Brown”.

На этот раз давайте пойдем снизу вверх:

Шаг Step 1 — откуда берем данные? Чтобы получить нужный нам результат, нужно соединить таблицы “member” и “books” с таблицей “borrowings”. Секция JOIN будет выглядеть так:

Шаг 2 — какие данные показываем? Нас интересуют только те данные, где автор книги — “Dan Brown”

Шаг 3 — как показываем данные? Теперь, когда данные получены, нужно просто вывести имя и фамилию тех, кто взял книги:

Супер! Осталось лишь объединить три составные части и сделать нужный нам запрос:

Что даст нам:

Отлично! Но имена повторяются (они не уникальны). Мы скоро это исправим.

5. Агрегирование

Грубо говоря, агрегирования нужны для конвертации нескольких строк в одну. При этом, во время агрегирования для разных колонок используется разная логика.

Давайте продолжим наш пример, в котором появляются повторяющиеся имена. Видно, что Ellen Horton взяла больше одной книги, но это не самый лучший способ показать эту информацию. Можно сделать другой запрос:

Что даст нам нужный результат:

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

Каждая строка в результате представляет собой результат агрегирования каждой группы.

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

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

Результат:

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

6. Подзапросы

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

Операции записи

Большинство операций записи в базе данных довольно просты, если сравнивать с более сложными операциями чтения.

7.1 Update

Синтаксис запроса семантически совпадает с запросом на чтение. Единственное отличие в том, что вместо выбора колонок ‘ом, мы задаем знаения ‘ом.

Если все книги Дэна Брауна потерялись, то нужно обнулить значение количества. Запрос для этого будет таким:

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

7.2 Delete

Запрос это просто запрос или без названий колонок. Серьезно. Как и в случае с и , блок остается таким же: он выбирает строки, которые нужно удалить. Операция удаления уничтожает всю строку, так что не имеет смысла указывать отдельные колонки. Так что, если мы решим не обнулять количество книг Дэна Брауна, а вообще удалить все записи, то можно сделать такой запрос:

7.3 Insert

Пожалуй, единственное, что отличается от других типов запросов, это . Формат такой:

Где , , это названия колонок, а , и это значения, которые нужно вставить в эти колонки, в том же порядке. Вот, в принципе, и все.

Взглянем на конкретный пример. Вот запрос с , который заполняет всю таблицу «books»:

Пример с оператором UPDATE

Ниже приведен пример оператора UPDATE, который использует условие EXISTS:

Oracle PL/SQL

UPDATE suppliers
SET supplier_name = (SELECT customers.name
FROM customers
WHERE customers.customer_id = suppliers.supplier_id)
WHERE EXISTS (SELECT customers.name
FROM customers
WHERE customers.customer_id = suppliers.supplier_id);

SELECT

Подзапрос также можно найти в предложениях SELECT.

Например:

Oracle PL/SQL

SELECT tbls.owner, tbls.table_name,
(SELECT COUNT(column_name) AS total_columns
FROM all_tab_columns cols
WHERE cols.owner = tbls.owner
AND cols.table_name = tbls.table_name) subquery2
FROM all_tables tbls;

В этом примере мы создали вложенный запрос в предложении SELECT, следующим образом:

Oracle PL/SQL

(SELECT COUNT(column_name) AS total_columns
FROM all_tab_columns cols
WHERE cols.owner = tbls.owner
AND cols.table_name = tbls.table_name) subquery2

Этот подзапрос с псевдонимом subquery2. Subquery2 используется для ссылки на этот подзапрос или любое из его полей.

Уловка размещения подзапроса в select предложении является то, что подзапрос должен возвращать одно значение. Именно поэтому в подзапросе обычно используются агрегатные функции, такие как функция SUM, COUNT, MIN или MAX.

Распространённые заблуждения

Вообще-то это не совсем заблуждения. Точнее, во многих СУБД это никакие
не заблуждения, а проза жизни. Потому во многих книгах это дело
описывается, как нечто само собой разумеющееся. Потому многие люди,
не разобравшись, переносят подобные утверждения на InterBase, что приводит
к неожиданным и как правило отрицательным последствиям.

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

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

Вот это и есть то самое заблуждение. Точнее, их тут даже два.

Некоррелированный подзапрос независим от контекста.

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

Некоррелированный подзапрос выполняется один раз.

Это один из
подходов, применяемых в большинстве СУБД. Однако в InterBase это правда
только для подзапросов в скалярном контексте. Для множественного
контекста применяется совершенно другой подход, описанный в следующем
разделе.

Часто задаваемые вопросы

Вопрос: Я создал базу данных клиентов. Я знаю, что вы используете Oracle оператор INSERT для вставки информации в базу данных, но как я могу убедиться, что не ввел ту же информацию о клиенте снова?

Ответ: Вы можете убедиться, что не вставить повторяющуюся информацию, используя условие EXISTS.

Например, если у вас была таблица с названием clients с первичным ключом client_id, вы можете использовать следующий INSERT:

Oracle PL/SQL

INSERT INTO clients
(client_id, client_name, client_type)
SELECT supplier_id, supplier_name, ‘advertising’
FROM suppliers
WHERE NOT EXISTS (SELECT *
FROM clients
WHERE clients.client_id = suppliers.supplier_id);

Это Oracle предложение INSERT вставляет несколько записей с подзапросом.

Если вы хотите вставить одну запись, вы можете использовать следующее Oracle предложение INSERT:

Oracle PL/SQL

INSERT INTO clients
(client_id, client_name, client_type)
SELECT 10345,
‘IBM’,
‘advertising’
FROM dual
WHERE NOT EXISTS (SELECT *
FROM clients
WHERE clients.client_id = 10345);

Использование таблицы dual позволяет ввести значения в операторе select, даже если значения не хранятся в настоящее время в таблице.

Вопрос: Как я могу вставить несколько строк явных данных в одном предложении INSERT в Oracle?

Ответ: Ниже приведен пример того, как можно вставить 3 строки в таблицу suppliers в Oracle, используя оператор INSERT:

Oracle PL/SQL

INSERT ALL
INTO suppliers (supplier_id, supplier_name) VALUES (1000, ‘IBM’)
INTO suppliers (supplier_id, supplier_name) VALUES (2000, ‘Microsoft’)
INTO suppliers (supplier_id, supplier_name) VALUES (3000, ‘Google’)
SELECT * FROM dual;

Однострочные подзапросы

Однострочные подзапросы (one-row subquery) полезны при необходимости получения в рамках главного запроса ответа на вопрос на основе пока что неизвестных значений, например: “У каких сотрудников зарплата выше, чем у сотрудника с идентификационным номером 9999?”. Для получения ответа на этот вопрос сначала (однократно) может выполняться подзапрос или внутренний запрос, результаты которого далее используются в основном или внешнем запросе. Ниже приведен пример однострочного запроса: 

SQL> SELECT first_name||last_name, dept
2 FROM employee
3 WHERE sal >
4 (SELECT sal
5 FROM emp
6 WHERE empno= 9999);