Как писать запросы sql

SQL-запросы: виды и механизм работ

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

Где применяется? Так как разного рода информация присутствует во многих сферах деятельности, то SQL-запросы применяются как в работе с онлайн-ресурсами, так и с программами и приложениями.

В статье рассказывается:

1. Структура базы данных
2. Механизм работы SQL-запроса
3. Виды SQL-запросов
4. Примеры SQL-запросов

Пройди тест и узнай, какая сфера тебе подходит:
айти, дизайн или маркетинг.
Бесплатно от Geekbrains

Структура базы данных

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

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

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

Полученный результат выглядит подобным образом:

Узнай, какие ИТ — профессии
входят в ТОП-30 с доходом
от 210 000 ₽/мес
Павел Симонов
Исполнительный директор Geekbrains

Команда GeekBrains совместно с международными специалистами по развитию карьеры подготовили материалы, которые помогут вам начать путь к профессии мечты.

Подборка содержит только самые востребованные и высокооплачиваемые специальности и направления в IT-сфере. 86% наших учеников с помощью данных материалов определились с карьерной целью на ближайшее будущее!

Скачивайте и используйте уже сегодня:

Павел Симонов
Исполнительный директор Geekbrains

Топ-30 самых востребованных и высокооплачиваемых профессий 2023

Поможет разобраться в актуальной ситуации на рынке труда

Подборка 50+ бесплатных нейросетей для упрощения работы и увеличения заработка

Только проверенные нейросети с доступом из России и свободным использованием

ТОП-100 площадок для поиска работы от GeekBrains

Список проверенных ресурсов реальных вакансий с доходом от 210 000 ₽

Получить подборку бесплатно
Уже скачали 24088

Также одна база данных может состоять из нескольких таблиц. В таком случае запрос SHOW TABLES in employees позволит увидеть полный их список. Визуально это выглядит примерно так:

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

Увидеть их можно с помощью выполнения SQL-запроса Describe engineering. Допустим, таблица содержит столбцы, в которых определен один конкретный признак, к примеру, employee_id, first_name, last_name, email, country и salary.

| Name | Null | Type |

|EMPLOYEE_ID| NOT NULL | INT(6) |

|FIRST_NAME | NOT NULL |VARCHAR2(20) |

|LAST_NAME | NOT NULL |VARCHAR2(25) |

|EMAIL | NOT NULL |VARCHAR2(255) |

|COUNTRY | NOT NULL |VARCHAR2(30) |

|SALARY | NOT NULL |DECIMAL(10,2) |

Для вас подарок! В свободном доступе до 26.11 —>
Скачайте ТОП-10
бесплатных нейросетей
для программирования
Помогут писать код быстрее на 25%
Чтобы получить подарок, заполните информацию в открывшемся окне

Строки таблицы, в которых отражена основная информация, называются записями. То есть, они содержат сведения, соответствующие наименованию столбцов (employee_id, first_name, last_name, e-mail, salary и country). Другими словами, в нашем примере строки определяют и выводят информацию об одном сотруднике из группы.

Механизм работы SQL-запроса

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

Итак, первое действие, которые совершает программа – это грамматическая разбивка и построение синтаксического дерева запроса. Анализ необходим для того, чтобы определить соответствие SQL-запроса требованиям синтаксиса и семантики. С помощью парсера формируется внутреннее определение команды, которое далее поступает обработчику кода.

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

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

Дарим скидку от 60%
на обучение «Разработчик» до 26 ноября
Уже через 9 месяцев сможете устроиться на работу с доходом от 150 000 рублей

Так какой же план может считаться идеальным и пригодным для выполнения?

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

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

Виды SQL-запросов

Существуют следующие виды запросов в SQL:

• DDL (Data Definition Language). Это язык определения данных, с помощью которого создается база данных и дается описание ее структуры. DDL запрос позволяет настроить правила размещения различной информации в таблице базы данных.
• DML (Data Manipulation Language) запрос – это язык работы с данными. Как правило, применяемые команды нужны для внесения изменений в уже существующие данные, их удаления и сохранения, обновления записей и т.д.

SQL для начинающих: 10 правил построения «точных» запросов

Научимся писать SQL-запросы, которые будут предоставлять данные в нужном объёме и за минимальное время.

Денис Карпов
Отдел автоматизации процессов информационных технологий

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

Для наших целей понадобятся тестовые данные. Будем работать с базой данных Oracle Database. Примеры в статье будут приводиться на языке SQL, PL/SQL. Нам важен подход, который можно адаптировать под другую реляционную систему управления базами данных — РСУБД.

Тестовые данные

⚒ Создадим тестовую таблицу 1:

CREATE SEQUENCE TEST_DATA_1_SEQ NOMAXVALUE NOMINVALUE NOCYCLE / CREATE TABLE TEST_DATA_1 ( TEST_DATA_1_ID NUMBER DEFAULT TEST_DATA_1_SEQ.NEXTVAL NOT NULL ,TYPE VARCHAR2(64) NOT NULL ,VALUE VARCHAR2(128) NOT NULL ,PC_USR VARCHAR2(30) DEFAULT USER NOT NULL ,PC_DT TIMESTAMP(6) DEFAULT SYSTIMESTAMP NOT NULL ) / ALTER TABLE TEST_DATA_1 ADD CONSTRAINT TEST_DATA_1_PK PRIMARY KEY (TEST_DATA_1_ID) USING INDEX / ALTER TABLE TEST_DATA_1 ADD CONSTRAINT TEST_DATA_1_TYPE_CHK CHECK (TYPE in ('CITY', 'DATE', 'EMPLOYEE', 'STOCK MARKET')) / CREATE UNIQUE INDEX TEST_DATA_1_UIDX1 ON TEST_DATA_1 (VALUE) / COMMENT ON TABLE TEST_DATA_1 IS 'Тестовые данные 1' / 

⚒ Заполним тестовую таблицу 1 данными:

/* Добавление данных */ INSERT INTO TEST_DATA_1 (TYPE, VALUE) VALUES ('CITY', 'МОСКВА'); INSERT INTO TEST_DATA_1 (TYPE, VALUE) VALUES ('CITY', 'САНКТ-ПЕТЕРБУРГ'); INSERT INTO TEST_DATA_1 (TYPE, VALUE) VALUES ('EMPLOYEE', 'СОТРУДНИК 1. ПОЛ М. ВОЗРАСТ 18'); INSERT INTO TEST_DATA_1 (TYPE, VALUE) VALUES ('EMPLOYEE', 'СОТРУДНИК 2. ПОЛ Ж. ВОЗРАСТ 19'); INSERT INTO TEST_DATA_1 (TYPE, VALUE) VALUES ('EMPLOYEE', 'СОТРУДНИК 3. ПОЛ Ж. ВОЗРАСТ 20'); INSERT INTO TEST_DATA_1 (TYPE, VALUE) VALUES ('DATE', '01 января 2000'); INSERT INTO TEST_DATA_1 (TYPE, VALUE) VALUES ('DATE', '02 января 2000'); INSERT INTO TEST_DATA_1 (TYPE, VALUE) VALUES ('DATE', '01.01.2001'); INSERT INTO TEST_DATA_1 (TYPE, VALUE) VALUES ('DATE', '02.01.2001'); INSERT INTO TEST_DATA_1 (TYPE, VALUE) VALUES ('DATE', '03.01.2001'); INSERT INTO TEST_DATA_1 (TYPE, VALUE) VALUES ('DATE', '04.01.2001'); /* Извлечение всех данных */ SELECT t1.* FROM TEST_DATA_1 t1; 

/* Удаление всех данных без проверок */ TRUNCATE TABLE TEST_DATA_1; 

⚒ Создадим тестовую таблицу 2:

CREATE SEQUENCE TEST_DATA_2_SEQ NOMAXVALUE NOMINVALUE NOCYCLE / CREATE TABLE TEST_DATA_2 ( TEST_DATA_2_ID NUMBER DEFAULT TEST_DATA_2_SEQ.NEXTVAL NOT NULL ,TEST_DATA_1_ID NUMBER NOT NULL ,TYPE VARCHAR2(64) NOT NULL ,VALUE VARCHAR2(128) NOT NULL ,PC_USR VARCHAR2(30) DEFAULT USER NOT NULL ,PC_DT TIMESTAMP(6) DEFAULT SYSTIMESTAMP NOT NULL ) / ALTER TABLE TEST_DATA_2 ADD CONSTRAINT TEST_DATA_2_PK PRIMARY KEY (TEST_DATA_2_ID) USING INDEX / ALTER TABLE TEST_DATA_2 ADD CONSTRAINT TEST_DATA_2_TYPE_CHK CHECK (TYPE in ('STREET', 'DATE', 'EMPLOYEE', 'STOCK MARKET')) / CREATE UNIQUE INDEX TEST_DATA_2_UIDX1 ON TEST_DATA_2 (TEST_DATA_1_ID, VALUE) / COMMENT ON TABLE TEST_DATA_2 IS 'Тестовые данные 2' / 

⚒ Заполним тестовую таблицу 2 данными:

/* Добавление данных */ INSERT INTO TEST_DATA_2 (TEST_DATA_1_ID, TYPE, VALUE) VALUES (1 /*ID МОСКВА*/, 'STREET', 'УЛИЦА КАРЛА МАРКСА'); INSERT INTO TEST_DATA_2 (TEST_DATA_1_ID, TYPE, VALUE) VALUES (1 /*ID МОСКВА*/, 'STREET', 'УЛИЦА КРУПСКОЙ'); INSERT INTO TEST_DATA_2 (TEST_DATA_1_ID, TYPE, VALUE) VALUES (1 /*ID МОСКВА*/, 'STREET', 'МАЛЫЙ ПОЛУЯРОСЛАВСКИЙ ПЕРЕУЛОК'); INSERT INTO TEST_DATA_2 (TEST_DATA_1_ID, TYPE, VALUE) VALUES (2 /*ID САНКТ-ПЕТЕРБУРГ*/, 'STREET', 'УЛИЦА КАРЛА МАРКСА'); INSERT INTO TEST_DATA_2 (TEST_DATA_1_ID, TYPE, VALUE) VALUES (2 /*ID САНКТ-ПЕТЕРБУРГ*/, 'STREET', 'УЛИЦА КРУПСКОЙ'); INSERT INTO TEST_DATA_2 (TEST_DATA_1_ID, TYPE, VALUE) VALUES (3 /*ID СОТРУДНИК 1*/, 'EMPLOYEE', 'ПРОЖИВАЕТ В ГОРОДЕ МОСКВА'); INSERT INTO TEST_DATA_2 (TEST_DATA_1_ID, TYPE, VALUE) VALUES (4 /*ID СОТРУДНИК 2*/,'EMPLOYEE', 'ПРОЖИВАЕТ В ГОРОДЕ МОСКВА'); INSERT INTO TEST_DATA_2 (TEST_DATA_1_ID, TYPE, VALUE) VALUES (5 /*ID СОТРУДНИК 3*/,'EMPLOYEE', 'ПРОЖИВАЕТ В ГОРОДЕ САНКТ-ПЕТЕРБУРГ'); INSERT INTO TEST_DATA_2 (TEST_DATA_1_ID, TYPE, VALUE) VALUES (6 /*ID 01 января 2000*/, 'DATE', 'Формат день числом, месяц словом, год числом'); INSERT INTO TEST_DATA_2 (TEST_DATA_1_ID, TYPE, VALUE) VALUES (7 /*ID 02 января 2000*/, 'DATE', 'Формат день числом, месяц словом, год числом'); INSERT INTO TEST_DATA_2 (TEST_DATA_1_ID, TYPE, VALUE) VALUES (8 /*ID 01.01.2001*/, 'DATE', 'Формат день числом, месяц числом, год числом'); INSERT INTO TEST_DATA_2 (TEST_DATA_1_ID, TYPE, VALUE) VALUES (9 /*ID 02.01.2001*/, 'DATE', 'Формат день числом, месяц числом, год числом'); INSERT INTO TEST_DATA_2 (TEST_DATA_1_ID, TYPE, VALUE) VALUES (10 /*ID 03.01.2001*/, 'DATE', 'Формат день числом, месяц числом, год числом'); INSERT INTO TEST_DATA_2 (TEST_DATA_1_ID, TYPE, VALUE) VALUES (11 /*ID 04.01.2001*/, 'DATE', 'Формат день числом, месяц числом, год числом'); /* Извлечение всех данных */ SELECT t2.* FROM TEST_DATA_2 t2; 

/* Удаление всех данных без проверок */ TRUNCATE TABLE TEST_DATA_2; 

1. Объявляя имена таблиц, обращайся к записям через псевдонимы таблиц

Допустим, есть таблица с некоторым количество колонок. К ней можно обратиться двумя разными способами:

⚠️ Опасный подход:

SELECT TYPE ,VALUE FROM TEST_DATA_1; 

✅ Безопасный подход заключается в обращении через псевдоним:

SELECT t1.TYPE AS TYPE ,t1.VALUE AS VALUE FROM TEST_DATA_1 t1; 

Псевдоним (анг. Alias) — это имя, назначенное источнику данных в SQL-запросе при использовании выражения в качестве источника данных или для упрощения ввода и прочтения инструкции SQL. Это полезно, если имя источника слишком длинное или его трудно вводить.

Псевдонимы можно использовать для переименования таблиц и колонок. В отличие от настоящих имён, они могут не соответствовать ограничениям базы данных и содержать до 255 знаков (включая пробелы, цифры и специальные символы).

В случае извлечения данных из одной таблицы без псевдонимов можно обойтись. Рисков нет. Синтаксический анализатор базы данных однозначно знает, данные из какой колонки таблицы запрашиваются. Но рекомендуется всё же использовать их — чтобы выработать привычку.

В случае извлечения данных из нескольких таблиц отказ от использования псевдонимов увеличивает риск получения некорректного результата. Допустим, что у таблиц есть колонки с одинаковым именем. Когда данные извлекаются и SQL-запрос звучит как: «Получаю записи из таблиц колонку А», то о какой колонке «А» идёт речь: из первой или второй таблицы? Если для таблицы назначен псевдоним, то SQL-запрос может звучать уже так: «Получаю записи из таблицы Т1 колонку А».

К SQL-запросу, возможно, придётся вернуться через какое-то время, чтобы внести в него изменения. В таких случаях подсказки в виде псевдонима (alias) помогут определить нужную колонку. Практически со стопроцентной уверенностью будет понятно, из какой таблицы что извлекали.

⚠️ Опасный подход:

SELECT TEST_DATA_1.TYPE ,TEST_DATA_1.VALUE ,TEST_DATA_2.TYPE ,TEST_DATA_2.VALUE FROM TEST_DATA_1 ,TEST_DATA_2 WHERE TEST_DATA_1.TEST_DATA_1_ID = TEST_DATA_2.TEST_DATA_1_ID; 

✅ Безопасный подход заключается в обращении через псевдоним:

SELECT t1.TYPE AS TYPE_1 /* Колонка TYPE из таблицы TEST_DATA_1 */ ,t1.VALUE AS VALUE_1 /* Колонка VALUE из таблицы TEST_DATA_1 */ ,t2.TYPE AS TYPE_2 /* Колонка TYPE из таблицы TEST_DATA_2 */ ,t2.VALUE AS VALUE_2 /* Колонка VALUE из таблицы TEST_DATA_2 */ FROM TEST_DATA_1 t1 ,TEST_DATA_2 t2 WHERE t1.TEST_DATA_1_ID = t2.TEST_DATA_1_ID; 

2. Извлекай только те данные, которые планируешь использовать

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

Рассмотрим пример «Карточка сотрудника». У нас есть таблица «Сотрудник» с колонками ФИО, пол, возраст. Данные из них извлекаются и выводятся на форму «Карточка сотрудника». SQL-запрос можно написать следующим образом: «Извлекаю все колонки из таблицы по указанному сотруднику». В таком случае извлекаются все колонки.

⚠️ Опасный подход заключается в извлечении всех данных:

SELECT t1.* FROM TEST_DATA_1 t1 WHERE t1.TEST_DATA_1_ID = 3 /* ID EMPLOYEE = СОТРУДНИК 1 */; 

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

/* Добавление новой колонки в таблицу */ ALTER TABLE TEST_DATA_1 ADD DESCRIPTION VARCHAR2(4000) / /* Обновление данных в таблице */ UPDATE TEST_DATA_1 t1 SET T1.DESCRIPTION = 'ДОПОЛНИТЕЛЬНЫЕ ДАННЫЕ ПО СОТРУДНИКУ 1' WHERE t1.TEST_DATA_1_ID = 3 /* ID EMPLOYEE = СОТРУДНИК 1 */; /* Извлечение данных из таблицы */ SELECT t1.* FROM TEST_DATA_1 t1 WHERE t1.TEST_DATA_1_ID = 3 /* ID EMPLOYEE = СОТРУДНИК 1 */; 

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

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

Рассмотрим пример «Телефон». На телефоне пользователя установлено приложение. Сам телефон старый. Пользователь не выполнял обновления программного обеспечения (ПО), но замечает, что с какого-то момента времени приложение начало работать медленнее. У другого пользователя на новом телефоне то же приложение работает быстро. Ошибка «плавающая», но для разработчика неприятная.

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

Чтобы таких неожиданностей не возникало, нужно извлекать строго те данные, которые требуется использовать и показывать на форме. В данном случае нужно было написать: «Извлекаю колонки ФИО, возраст, пол из таблички сотрудника, с фильтрацией по сотруднику».

✅ Безопасный подход заключается в получении нужных данных:

SELECT t1.TYPE AS TYPE_1 /* Колонка TYPE из таблицы TEST_DATA_1 */ ,t1.VALUE AS VALUE_1 /* Колонка VALUE из таблицы TEST_DATA_1 */ FROM TEST_DATA_1 t1 WHERE t1.TEST_DATA_1_ID = 3 /* ID EMPLOYEE = СОТРУДНИК 1 */; 

3. По максимуму используй данные, которые извлёк из таблицы

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

После обращения к таблице Table1, нужно постараться написать SQL-запрос так, чтобы не пришлось извлекать данные из неё несколько раз. Это не всегда возможно, но попытаться стоит.

⚠️ Опасный подход:

SELECT t1.TYPE AS TYPE_1 ,t1.VALUE AS VALUE_1 ,t2.VALUE AS VALUE_2 FROM TEST_DATA_1 t1 ,TEST_DATA_2 T2 WHERE T1.TEST_DATA_1_ID = T2.TEST_DATA_1_ID AND t2.VALUE = 'ПРОЖИВАЕТ В ГОРОДЕ МОСКВА' UNION ALL SELECT t1.TYPE AS TYPE_1 ,t1.VALUE AS VALUE_1 ,t2.VALUE AS VALUE_2 FROM TEST_DATA_1 t1 ,TEST_DATA_2 T2 WHERE T1.TEST_DATA_1_ID = T2.TEST_DATA_1_ID AND t2.VALUE = 'ПРОЖИВАЕТ В ГОРОДЕ САНКТ-ПЕТЕРБУРГ' ORDER BY VALUE_1; 

/* План запроса */ 

✅ Безопасный подход заключается в использовании полученных данных максимально продуктивно:

SELECT t1.TYPE AS TYPE_1 ,t1.VALUE AS VALUE_1 ,t2.VALUE AS VALUE_2 FROM TEST_DATA_1 t1 ,TEST_DATA_2 T2 WHERE T1.TEST_DATA_1_ID = T2.TEST_DATA_1_ID AND t2.VALUE IN ('ПРОЖИВАЕТ В ГОРОДЕ МОСКВА', 'ПРОЖИВАЕТ В ГОРОДЕ САНКТ-ПЕТЕРБУРГ') ORDER BY VALUE_1; 

/* План запроса */ 

Неоптимальный SQL-запрос может выполняться дольше, уронить инфраструктуру и даже повлиять на безопасность системы.

⚒ Рассмотрим тестовый пример:

/* * Тестовый пример * Каждый случай запроса выполняется 1 000 000 раз в “холостую” */ declare start_time pls_integer; end_time pls_integer; begin /* 1 Случай */ start_time := dbms_utility.get_time; for indx in 1 .. 1000000 loop for cur in (select t1.TYPE as TYPE_1 ,t1.VALUE as VALUE_1 ,t2.VALUE as VALUE_2 from TEST_DATA_1 t1 ,TEST_DATA_2 T2 where T1.TEST_DATA_1_ID = T2.TEST_DATA_1_ID and t2.VALUE = 'ПРОЖИВАЕТ В ГОРОДЕ МОСКВА' union all select t1.TYPE as TYPE_1 ,t1.VALUE as VALUE_1 ,t2.VALUE as VALUE_2 from TEST_DATA_1 t1 ,TEST_DATA_2 T2 where T1.TEST_DATA_1_ID = T2.TEST_DATA_1_ID and t2.VALUE = 'ПРОЖИВАЕТ В ГОРОДЕ САНКТ-ПЕТЕРБУРГ' order by VALUE_1) loop null; end loop; end loop; end_time := dbms_utility.get_time; dbms_output.put_line('execution time 1 --> ' || (end_time - start_time) / 100 || ' sec'); /* 2 Случай */ start_time := dbms_utility.get_time; for indx in 1 .. 1000000 loop for cur in (select t1.TYPE as TYPE_1 ,t1.VALUE as VALUE_1 ,t2.VALUE as VALUE_2 from TEST_DATA_1 t1 ,TEST_DATA_2 T2 where T1.TEST_DATA_1_ID = T2.TEST_DATA_1_ID and t2.VALUE in ('ПРОЖИВАЕТ В ГОРОДЕ МОСКВА', 'ПРОЖИВАЕТ В ГОРОДЕ САНКТ-ПЕТЕРБУРГ') order by VALUE_1) loop null; end loop; end loop; end_time := dbms_utility.get_time; dbms_output.put_line('execution time 2 --> ' || (end_time - start_time) / 100 || ' sec'); end; / /* * Результат выполнения * Важно не время, которое зависит от ресурсов на ПК, а разница выполнения */ /* Done in 64,516 seconds */ execution time 1 --> 46.83 sec execution time 2 --> 17.67 sec 

Рассмотрим пример «Работа ЦОД». Есть Центр Обработки Данных (ЦОД). В нём, на одном из ресурсов внутри приложения, выполняется некий SQL-запрос, который постепенно использует всю доступную память без ограничений. И приложениям, которые стоят на том же ресурсе, со временем перестаёт хватать памяти на стабильную работу. Это может привести к их падению.

4. Проверяй запросы SQL на индексы

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

Рассмотрим пример «Брокерская биржа». В рамках отдельного процесса извлекаются данные для покупки-продажи акций. Используя оптимизированный SQL-запрос, можно быстро получать информацию, по какой цене торгуется каждая акция. И делать прогноз — покупать или продавать.

Если SQL-запрос не оптимизирован, извлечение данных занимает больше времени. И пользователь вынужден ждать, хотя мог за это время сделать что-то, что принесло бы ему деньги.

Индексы — это инструмент оптимизации извлечения данных. Конечно, это не панацея, и если таблица маленькая, по ней проще пройти прямым перебором и получить данные.

Добавим в тестовую таблицу 1 новые данные:

/* Добавление новых данных */ INSERT INTO TEST_DATA_1 (TYPE, VALUE) VALUES ('STOCK MARKET', 'АКЦИЯ 1. СТОИМОСТЬ 101 РУБ'); INSERT INTO TEST_DATA_1 (TYPE, VALUE) VALUES ('STOCK MARKET', 'АКЦИЯ 2. СТОИМОСТЬ 102 РУБ'); INSERT INTO TEST_DATA_1 (TYPE, VALUE) VALUES ('STOCK MARKET', 'АКЦИЯ 3. СТОИМОСТЬ 103 РУБ'); INSERT INTO TEST_DATA_1 (TYPE, VALUE) VALUES ('STOCK MARKET', 'АКЦИЯ 4. СТОИМОСТЬ 104 РУБ'); 

⚠️ Опасный подход заключается в игнорировании использования индексов:

/* Извлечение всех данных TYPE = STOCK MARKET */ SELECT t1.TYPE AS TYPE_1 ,t1.VALUE AS VALUE_1 FROM TEST_DATA_1 t1 WHERE t1.TYPE = 'STOCK MARKET'; /* План запроса */ 

Добавим в тестовую таблицу 1 новый индекс

/* Добавление нового индекса */ CREATE INDEX TEST_DATA_1_IDX1 ON TEST_DATA_1 (TYPE) / 

✅ Безопасный подход заключается в использовании индексов:

/* Извлечение всех данных TYPE = STOCK MARKET */ SELECT t1.TYPE AS TYPE_1 ,t1.VALUE AS VALUE_1 FROM TEST_DATA_1 t1 WHERE t1.TYPE = 'STOCK MARKET'; 

/* План запроса */ 

Рассмотрим пример «Доставка почты». Показательный пример работы индексов — доставка почты из точки А в одном городе, в точку Б в другом. Зная, куда конкретно нужно доставить посылку, мы можем идти по индексам и определить, где и когда повернуть, чтобы довезти посылку за максимально короткое время. Если везти посылку на машине, то это сокращает расход топлива — а значит, и материальные издержки на доставку.

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

5. Начинай запрос SQL с таблицы с меньшим набором записей

Допустим, нам нужно соединить две таблицы: с маленьким количеством записей и с большим. Стоит сделать следующее:

• начинать извлечение данных из таблицы с меньшим набором данных;
• продолжать извлечение данных из таблицы с большим набором данных.

⚒ Добавим в тестовую таблицу 2 новые данные:

/* Добавление новых данных */ declare l_type test_data_2.type%type := 'STOCK MARKET'; l_value_2 test_data_2.value%type := ''; l_sql varchar2(128) := ''; begin /* Извлечение данных из тестовой таблицы 1 */ for cur_t1 in (select t1.test_data_1_id as test_data_1_id ,t1.type as type_1 ,t1.value as value_1 from TEST_DATA_1 t1 where type = l_type) loop /* Цикл до 1 000 000 на каждую полученную запись из тестовой таблицы 1 */ for indx in 1 .. 1000000 loop l_value_2 := cur_t1.value_1 || '. ' || 'ЗАПИСЬ ' || indx; l_sql := 'INSERT INTO TEST_DATA_2 (TEST_DATA_1_ID, TYPE, VALUE) VALUES (' || -- cur_t1.TEST_DATA_1_ID || ', ' || -- '''' || l_type || ''', ' || -- '''' || l_value_2 || ''')'; /* Выполнение динамического запроса */ execute immediate l_sql; end loop; end loop; end; / /* Общее число записей в таблице TEST_DATA_1 */ SELECT COUNT(1) AS CNT FROM TEST_DATA_1 / 

/* Общее число записей в таблице TEST_DATA_2 */ SELECT COUNT(1) AS CNT FROM TEST_DATA_2 / 

/* Добавление нового индекса для таблицы TEST_DATA_1 */ CREATE INDEX TEST_DATA_1_IDX2 ON TEST_DATA_1 (TEST_DATA_1_ID, TYPE) / /* Добавление нового индекса для таблицы TEST_DATA_2 */ CREATE INDEX TEST_DATA_2_IDX1 ON TEST_DATA_2 (TEST_DATA_1_ID, TYPE) / CREATE INDEX TEST_DATA_2_IDX2 ON TEST_DATA_2 (TEST_DATA_1_ID) / CREATE INDEX TEST_DATA_2_IDX3 ON TEST_DATA_2 (TYPE) / /* Сбор статистики после добавления данных */ declare l_user varchar2(30 char) := user; begin /* Для таблицы TEST_DATA_1 */ DBMS_STATS.GATHER_TABLE_STATS(ownname => l_user -- ,tabname => 'TEST_DATA_1' ,cascade => true); /* Для таблицы TEST_DATA_2 */ DBMS_STATS.GATHER_TABLE_STATS(ownname => l_user -- ,tabname => 'TEST_DATA_2' ,cascade => true); end; / 

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

⚒ Рассмотрим тестовый пример:

/* * Тестовый пример * Каждый случай запроса выполняется 100 раз в “холостую” * Запросы усложнены и их можно упростить, добиваясь большей производительности и схожего результата * Попробуйте поэкспериментировать */ declare start_time pls_integer; end_time pls_integer; begin /* 1 Случай. От большего к меньшему */ start_time := dbms_utility.get_time; for indx in 1 .. 100 loop for cur in (select t1.type as type_1 ,t1.value as value_1 ,t2_.type_2 as type_2 ,t2_.value_2_min as value_2_min ,t2_.value_2_max as value_2_max ,t2_.value_2_cnt as value_2_cnt from (select t2.TEST_DATA_1_ID as TEST_DATA_1_ID ,t2.TYPE as TYPE_2 ,min(t2.VALUE) as VALUE_2_MIN ,max(t2.VALUE) as VALUE_2_MAX ,count(t2.VALUE) as VALUE_2_CNT from TEST_DATA_2 t2 where t2.type = 'STOCK MARKET' group by t2.TEST_DATA_1_ID ,t2.TYPE order by t2.TEST_DATA_1_ID) t2_ join TEST_DATA_1 t1 on t1.TEST_DATA_1_ID = t2_.TEST_DATA_1_ID and t1.type = 'STOCK MARKET' order by t1.value) loop null; end loop; end loop; 

/* Executed in 269,203 seconds */ execution time 1 --> 149.49 sec execution time 2 --> 119.68 sec 

Рассмотрим пример «Очередь клиентов». Есть поток клиентов, каждого из которых нужно обслужить. Операторы, заполняя форму «Анкета» задают серию вопросов. Один из них, влияет на дальнейший ход общения: «Вам исполнилось 18 лет?». Если клиент отвечает нет, то оператор прекращает общение, иначе продолжает задавать вопросы.

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

6. Не допускай декартового произведения между таблицами

Результатом декартового — или перекрёстного — произведения множеств будет такое множество, элементами которого являются все возможные упорядоченные пары элементов исходных множеств. Рассмотрим пример «Адрес». Возьмём две таблицы «Город», «Улица». В первой таблице «Город» есть две записи: Москва и Санкт-Петербург. Во второй таблице «Улица» сохранены следующие записи:

• улица Карла Маркса, которая одновременно есть и в Москве, и в Санкт-Петербурге;
• улица Крупской аналогично и в Москве, и в Санкт-Петербурге;
• Малый Полуярославский переулок только в Москве.

Пишем запрос: «Получаю из таблицы «Улица», которые принадлежат городу Москва».

⚠️ Опасный подход:

SELECT t1.TYPE AS TYPE_1 /* Колонка TYPE из таблицы TEST_DATA_1 */ ,t1.VALUE AS VALUE_1 /* Колонка VALUE из таблицы TEST_DATA_1 */ ,t2.TYPE AS TYPE_2 /* Колонка TYPE из таблицы TEST_DATA_2 */ ,t2.VALUE AS VALUE_2 /* Колонка VALUE из таблицы TEST_DATA_2 */ FROM TEST_DATA_1 t1 ,TEST_DATA_2 t2 WHERE t1.TYPE = 'CITY' AND t2.TYPE = 'STREET'; 

SQL-запрос написан без условия, то есть: «Извлекаю улицы, относящиеся к городам, без соединения таблиц». База данных, не понимая, по какому городу делается SQL-запрос, соединит со всеми улицами и Москву, и Санкт-Петербург. Всего вернётся 2* 5 = 10 записей.

✅ Безопасный подход заключается в наличии связей:

SELECT t1.TYPE AS TYPE_1 /* Колонка TYPE из таблицы TEST_DATA_1 */ ,t1.VALUE AS VALUE_1 /* Колонка VALUE из таблицы TEST_DATA_1 */ ,t2.TYPE AS TYPE_2 /* Колонка TYPE из таблицы TEST_DATA_2 */ ,t2.VALUE AS VALUE_2 /* Колонка VALUE из таблицы TEST_DATA_2 */ FROM TEST_DATA_1 t1 ,TEST_DATA_2 t2 WHERE t1.TEST_DATA_1_ID = t2.TEST_DATA_1_ID AND t1.TYPE = 'CITY' AND t2.TYPE = 'STREET'; 

Этот SQL-запрос написан с условием, то есть: «Извлекаю улицы, относящиеся к городу Москве, соединяя две таблицы условием». В нём указывается, по какому городу нужно выполнить фильтрацию. Поэтому возвращено 3 записи.
Когда данные извлекаются больше чем из одной таблицы, важно, как они соединяются между собой. Неправильное соединение будет возвращать неверные данные и не в ожидаемом количестве.

7. Проверяй, что имена параметров процедур не совпадают с именами колонок

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

Допустим, есть строковый параметр А, который передаётся на вход процедуры с целью фильтрации. Можно сказать, что написано так: «Обновляю таблицу, задав новое значение для колонки, где выполняется фильтрация по колонке А равной параметру А». В этом случае наблюдается полное совпадение А = А. База данных обновит все записи в этой таблице.

Чтобы этого не было, параметру добавляют префикс или постфикс. Например, параметр будет называться не А, а РА. В изменённом виде можно сказать, что написано так: «Обновляю таблицу, задав новое значение для колонки, где выполняется фильтрация по колонке А равной параметру PА».

⚠️ Опасный подход:

/* 1 вариант процедуры с ошибкой */ create or replace procedure e_test_data_1_upd_description(test_data_1_id in TEST_DATA_1.TEST_DATA_1_ID%type ,description in TEST_DATA_1.DESCRIPTION%type) as begin update TEST_DATA_1 t1 – set t1.DESCRIPTION = description /* Обновление записи */ where t1.TEST_DATA_1_ID = test_data_1_id; exception when others then /* Блок перехвата ошибок */ null; end e_test_data_1_upd_description; / /* Пример вызова 1 варианта процедуры с ошибкой */ declare begin e_test_data_1_upd_description(test_data_1_id => 4 /* ID EMPLOYEE = СОТРУДНИК 2 */ ,description => 'ДОПОЛНИТЕЛЬНЫЕ ДАННЫЕ ПО СОТРУДНИКУ 2'); end; / /* * Результат изменений * Визуально изменений нет */ SELECT T1.TEST_DATA_1_ID AS TEST_DATA_1_ID ,T1.TYPE AS TYPE_1 ,T1.VALUE AS VALUE_1 ,T1.DESCRIPTION AS DESCRIPTION FROM TEST_DATA_1 T1; 

⚠️ Опасный подход:

/* 2 вариант процедуры с ошибкой */ create or replace procedure e_test_data_1_upd_description(test_data_1_id in TEST_DATA_1.TEST_DATA_1_ID%type ,description_new in TEST_DATA_1.DESCRIPTION%type) as begin update TEST_DATA_1 t1 – set t1.DESCRIPTION = description_new /* Обновление записи */ where t1.TEST_DATA_1_ID = test_data_1_id; exception when others then /* Блок перехвата ошибок */ null; end e_test_data_1_upd_description; / /* Пример вызова 2 варианта процедуры с ошибкой */ declare begin e_test_data_1_upd_description(test_data_1_id => 4 /* ID EMPLOYEE = СОТРУДНИК 2 */ ,description_new => 'ДОПОЛНИТЕЛЬНЫЕ ДАННЫЕ ПО СОТРУДНИКУ 2'); end; / /* * Результат изменений * Изменены все записи */ SELECT T1.TEST_DATA_1_ID AS TEST_DATA_1_ID ,T1.TYPE AS TYPE_1 ,T1.VALUE AS VALUE_1 ,T1.DESCRIPTION AS DESCRIPTION FROM TEST_DATA_1 T1; 

✅ Безопасный подход заключается в передаче параметра, имя которого не совпадает с именем колонки в таблице:

/* Вариант процедуры без ошибки */ create or replace procedure e_test_data_1_upd_description(p_test_data_1_id in TEST_DATA_1.TEST_DATA_1_ID%type ,p_description in TEST_DATA_1.DESCRIPTION%type) as begin update TEST_DATA_1 t1 – set t1.DESCRIPTION = p_description /* Обновление записи */ where t1.TEST_DATA_1_ID = p_test_data_1_id; exception when others then /* Блок перехвата ошибок */ null; end e_test_data_1_upd_description; / /* Пример вызова варианта процедуры без ошибки */ declare begin e_test_data_1_upd_description(p_test_data_1_id => 4 /* ID EMPLOYEE = СОТРУДНИК 2 */ ,p_description => 'ДОПОЛНИТЕЛЬНЫЕ ДАННЫЕ ПО СОТРУДНИКУ 2'); end; / /* * Результат изменений * Изменена 1 требуемая запись */ SELECT T1.TEST_DATA_1_ID AS TEST_DATA_1_ID ,T1.TYPE AS TYPE_1 ,T1.VALUE AS VALUE_1 ,T1.DESCRIPTION AS DESCRIPTION FROM TEST_DATA_1 T1; 

8. Следи за временем выполнения SQL-запроса

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

Рассмотрим пример «Мониторинг времени выполнения». Допустим, на уровне базы данных продуктовой среды настроен специальный триггер. Его предназначение сводится к следующему:

• прерывать сессию, которая выполняется дольше N-минут;
• сохранить информацию об SQL-запросе в журнал для последующего анализа или постановки на мониторинг.

Вариант триггера на таблицу с искусственно генерируемой ошибкой в момент обновления данных:

/* Вариант триггера */ create or replace trigger TEST_DATA_1_AIUDR_PTCL after insert or update or delete on TEST_DATA_1 for each row begin if UPDATING then if (:old.test_data_1_id = 5 and :new.description is not null) then DBMS_OUTPUT.PUT_LINE('Log entry.'); raise_application_error(-20001, 'No Update with id 5 and new description.'); rollback; end if; end if; end; / 

Специалисту рассказывали про этот триггер. Он проигнорировал это или забыл — и реализовал, поставленную задачу на непродуктовой среде таким образом, что одно из действий выполняется больше N-минут. Передал всё на установку в продуктовую среду. Получилось, что реализованный функционал не работает полностью или частично.

Вариант процедуры с искусственно завышенным временем выполнения

/* Вариант процедуры */ create or replace procedure e_test_data_1_upd_description(p_test_data_1_id in TEST_DATA_1.TEST_DATA_1_ID%type ,p_description in TEST_DATA_1.DESCRIPTION%type) as begin /* Цикл добавлен для увеличения времени выполнения блока программной логики */ for indx in 1 .. 1000000 loop for cur in (select t1.TYPE as TYPE_1 ,t1.VALUE as VALUE_1 ,t2.VALUE as VALUE_2 from TEST_DATA_1 t1 ,TEST_DATA_2 T2 where t1.TEST_DATA_1_ID = t2.TEST_DATA_1_ID and t1.TEST_DATA_1_ID = p_test_data_1_id) loop null; end loop; end loop; /* Блок программной логики */ update TEST_DATA_1 t1 – set t1.DESCRIPTION = p_description /* Обновление записи */ where t1.TEST_DATA_1_ID = p_test_data_1_id; end e_test_data_1_upd_description; / /* Пример вызова процедуры */ declare begin e_test_data_1_upd_description(p_test_data_1_id => 5 /* ID EMPLOYEE = СОТРУДНИК 3 */ ,p_description => 'ДОПОЛНИТЕЛЬНЫЕ ДАННЫЕ ПО СОТРУДНИКУ 3'); end; / 

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

9. Используй копию данных для построения отчётности

Отчётность — это извлечение массива данных из базы для последующей обработки, аналитики, построения прогноза, прочее. Для неё может извлекаться значительный объём данных.

Рассмотрим пример «Отчёт о расходах за период». У нас есть промышленная среда, на которой развёрнуто приложение с подключением к базе данных. С приложением работают сотрудники. Задачей одних является внесение информации о приходе и расходе денежных средств. Задачей других — подготовка отчёта о расходе денежных средств за период. Информация вносится периодически и в небольшом объёме. Извлекается реже, но вся, что была внесена за конкретный период.

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

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

• Oracle — Setting Up Replication (oracle.com);
• MSSQL — Учебник. Подготовка к репликации – SQL Server | Microsoft Learn;
• PostgreSQL — PostgreSQL : Документация: 15: Глава 27. Отказоустойчивость, балансировка нагрузки и репликация : Компания Postgres Professional.
• MySQL — MySQL :: MySQL 8.0 Reference Manual :: 17.1.2.6 Setting Up Replicas.

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

10. Проверяй формат данных

Бывает, что отчёт, который обычно работает хорошо, возвращает ошибку, если ввести другие входные данные. Это связано с тем, что у новых входных данных другой формат.

Рассмотрим пример «Отчёт». У нас есть отчёт, строящийся на данных, которые заполняются внешним приложением. Одна из его колонок — дата. Поле ввода на форме, в которой происходит её заполнение — строковое. В подавляющем большинстве случаев формат: день числом, месяц числом, год числом, например, 01.01.2001. Изредка — день числом, месяц словом, год числом, например, «1 января 2001».

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

⚠️ Опасный подход заключается в игнорировании формата используемых данных:

SELECT t1.TYPE AS TYPE_1 /* Колонка TYPE из таблицы TEST_DATA_1 */ ,to_date(t1.VALUE, 'DD.MM.RRRR') AS VALUE_1 /* Колонка VALUE из таблицы TEST_DATA_1 */ ,t2.TYPE AS TYPE_2 /* Колонка TYPE из таблицы TEST_DATA_2 */ ,t2.VALUE AS VALUE_2 /* Колонка VALUE из таблицы TEST_DATA_2 */ FROM TEST_DATA_1 t1 ,TEST_DATA_2 t2 WHERE t1.TEST_DATA_1_ID = t2.TEST_DATA_1_ID AND t1.TYPE = 'DATE' AND t2.TYPE = 'DATE'; 

✅ Безопасный подход заключается в понимании формата используемых данных:

SELECT t1.TYPE AS TYPE_1 /* Колонка TYPE из таблицы TEST_DATA_1 */ ,to_date(t1.VALUE, 'DD.MM.RRRR') AS VALUE_1 /* Колонка VALUE из таблицы TEST_DATA_1 */ ,t2.TYPE AS TYPE_2 /* Колонка TYPE из таблицы TEST_DATA_2 */ ,t2.VALUE AS VALUE_2 /* Колонка VALUE из таблицы TEST_DATA_2 */ FROM TEST_DATA_1 t1 ,TEST_DATA_2 t2 WHERE t1.TEST_DATA_1_ID = t2.TEST_DATA_1_ID AND t1.TYPE = 'DATE' AND t2.TYPE = 'DATE' AND t2.VALUE = 'Формат день числом, месяц числом, год числом'; 

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

Вспомним, что написано выше, и закрепим правила:

1. Объявляя имена таблиц, обращайся к записям через имена таблиц.
2. Извлекай только те данные, которые планируешь использовать.
3. По максимуму используй данные, которые извлёк из таблицы.
4. Проверяй запросы SQL на индексы.
5. Начинай запрос SQL с таблицы с меньшим набором записей.
6. Не допускай декартового произведения между таблицами.
7. Проверяй, что имена параметров процедур не совпадают с именами колонок.
8. Следи за временем выполнения SQL-запроса.
9. Используй копию данных для построения отчётности.
10. Проверяй формат данных.

От автора

Подходов к оптимизации великое множество. Цель статьи — пробудить интерес искать и находить места роста производительности и снижения издержек. И помните Зако́н Ме́рфи: «Если что-нибудь может пойти не так, оно пойдёт не так».

Как создать и выполнить SQL запрос к базе данных. Обзор основных инструментов

Приветствую Вас на сайте Info-Comp.ru! Сегодня я продолжаю рассказ о языке SQL, и в этом материале я немного расскажу о том, как создаются и выполняются SQL запросы к базе данных, а точнее какие инструменты (программы) для этого используются.

Как создать SQL запрос? Где писать SQL код?

В одной из прошлых статей я рассказал Вам, что такое SQL и какие СУБД бывают, но у начинающих, кто только начинает работать с базами данных, могут возникнуть определённые вопросы, например, как работать с этими базами данных, как подключиться к базе и как выполнить SQL запрос?

Обычный случай, когда человек только что установил себе какую-нибудь СУБД (например, для изучения SQL) и не знает, что делать дальше, где писать SQL код? какую программу запустить?

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

Поэтому сегодня, специально для начинающих SQL программистов, я расскажу о том, какие инструменты нужны для того, чтобы создавать и выполнять SQL запросы к базе данных, иными словами, где писать SQL запросы. При этом я расскажу про инструменты для всех популярных СУБД: Microsoft SQL Server, Oracle Database, MySQL и PostgreSQL. Так как для каждой СУБД используются отдельные инструменты, но есть, конечно же, и универсальные инструменты, которые умеют работать одновременно практически со всеми из вышеперечисленных баз данных.

Если у Вас возникает вопрос, как послать SQL запрос к базе данных из приложения при его разработке (например, Вы начинающий программист Java, C# или других языков), то это делается непосредственно из самой IDE (среды программирования), используя специальные драйверы для подключения к БД. Устанавливать перечисленные в данной статье инструменты необязательно, они нужны для прямой работы с базой данных: разработка и отладка SQL инструкций, выполнение административных задач и так далее.

Инструменты для создания SQL запросов

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

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

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

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

Microsoft SQL Server

Начну я, конечно же, с Microsoft SQL Server, так как я уже достаточно долго работаю с данной СУБД. Microsoft SQL Server – это система управления базами данных от компании Microsoft. Она очень популярна в корпоративном секторе, особенно в крупных компаниях.

Инструментов для работы с Microsoft SQL Server много, однако самый распространённый и популярный вариант – это, конечно же, SQL Server Management Studio.

SQL Server Management Studio

SQL Server Management Studio (SSMS) — это бесплатная графическая среда для управления инфраструктурой SQL Server, разработанная компанией Microsoft. С помощью Management Studio Вы можете разрабатывать и выполнять инструкции T-SQL, а также администрировать Microsoft SQL Server.

Среда SQL Server Management Studio – это основной, стандартный инструмент для работы с Microsoft SQL Server.

Если стандартного функционала SSMS Вам недостаточно, то для этой среды разработано очень много различных плагинов и надстроек, которые расширяют функционал Management Studio.

Более подробно про SQL Server Management Studio, включая то, как установить данную среду, я рассказывал в статье – Обзор и установка SQL Server Management Studio.

• Страница продукта –https://docs.microsoft.com/ru-RU/sql/ssms/download-sql-server-management-studio-ssms;

• SQL код – книга для изучения языкаSQL.

SQL Server Data Tools

SQL Server Data Tools – это еще один инструмент для работы с Microsoft SQL Server, разработанный компанией Microsoft. Данный инструмент входит в состав Visual Studio, и устанавливается он как отдельная рабочая нагрузка. Предназначен SQL Server Data Tools в первую очередь для разработчиков приложений.

Если Вы разрабатываете программы с помощью Visual Studio, при этом у Вас возникла необходимость работы с Microsoft SQL Server, то SQL Server Data Tools будет для Вас очень удобным и привычным инструментом.

dbForge Studio for SQL Server

dbForge Studio for SQL Server – это мощная среда для разработки и администрирования баз данных в Microsoft SQL Server. Разработчиком данной среды является компания Devart, у которой, кстати, есть много инструментов для работы с Microsoft SQL Server, про один инструмент я уже рассказывал в статье – Как сравнить и синхронизировать две базы данных в Microsoft SQL Server? Кроме того, у Devart есть и инструменты для работы с другими СУБД, про некоторые я сегодня еще расскажу.

Red Gate SQL Prompt

Red Gate SQL Prompt – еще один мощнейший инструмент для работы с Microsoft SQL Server. С помощью него также можно разрабатывать SQL инструкции и администрировать SQL сервер. Данную среду разрабатывает компания Redgate Software, которая специализируется на работе с данными, у нее есть инструменты и для работы с другими СУБД, но основным направлением является Microsoft SQL Server.

Navicat for SQL Server

Navicat for SQL Server – это графический инструмент для разработки и администрирования баз данных в Microsoft SQL Server. С помощью него можно создавать, редактировать и удалять любые объекты базы данных, разрабатывать и выполнять SQL запросы и инструкции, а также просматривать данные в таблицах, включая двоичные и шестнадцатеричные данные.

EMS SQL Management Studio for SQL Server

EMS SQL Management Studio for SQL Server – это комплексное решение для разработки и администрирования баз данных в Microsoft SQL Server. Разработкой занимается компания EMS, которая специализируется на разработке инструментов администрирования баз данных и приложений для управления данными. У нее много инструментов для работы с разными СУБД.

DataGrip

DataGrip – это универсальный инструмент для работы с базами данных, он умеет работать с Microsoft SQL Server, PostgreSQL, MySQL, Oracle, Sybase, DB2 и другими. Разработчиком DataGrip выступает JetBrains.

SQL Enlight

SQL Enlight – еще одно приложение для разработки T-SQL кода. Разработкой занимается компания Ubitsoft.

SQLCMD

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

Oracle Database

Oracle Database – это система управления базами данных от компании Oracle. Это также очень популярная СУБД, и также среди крупных компаний.

Инструментов для работы с Oracle Database также много, вот некоторые из них.

Oracle SQL Developer

Oracle SQL Developer – это стандартный, бесплатный и основной инструмент для разработчика баз данных Oracle.

Разработкой занимается компания Oracle. С помощью Oracle SQL Developer можно разрабатывать инструкции на PL/SQL и выполнять SQL запросы.

SQL Navigator for Oracle

SQL Navigator for Oracle – это удобный и не менее популярный инструмент для работы с Oracle Database.

Navicat for Oracle

Navicat for Oracle – это инструмент для разработки и администрирования баз данных Oracle Database. Этот инструмент имеет широкий набор функций для облегчения управления данными, таких как инструмент моделирования данных, синхронизация данных, импорт и экспорт данных.

EMS SQL Management Studio for Oracle

EMS SQL Management Studio for Oracle – это комплексное решение для разработки и администрирования баз данных Oracle Database. Разработкой занимается компания EMS, продукты которой я уже упоминал сегодня.

dbForge Studio for Oracle

dbForge Studio for Oracle – еще один продукт компании Devart, который предназначен для разработки и обслуживания баз данных Oracle Database, он также имеет очень мощный функционал.

MySQL

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

Для работы с MySQL существует очень много инструментов, вот самые популярные и функциональные.

MySQLWorkbench

MySQL Workbench – это основной и стандартный инструмент для работы с MySQL.

Он позволяет осуществлять разработку на SQL и администрировать MySQL сервер.

PHPMyAdmin

PHPMyAdmin – это бесплатный веб-инструмент для работы с MySQL. Очень широкую популярность он приобрел в интернете, так как именно PHPMyAdmin используют для разработки баз данных на многих web-сайтах, а также на большинстве хостинг-провайдерах для управления базой MySQL используется именно PHPMyAdmin.

• Страница продукта – https://www.phpmyadmin.net/
• Пример установки PHPMyAdmin на Linux Mint

Navicat for MySQL

Navicat for MySQL – это инструмент для администрирования и разработки баз данных MySQL и MariaDB. Navicat for MySQL позволяет подключаться и работать с базами данных в MySQL и MariaDB одновременно.

dbForge Studio for MySQL

dbForge Studio for MySQL – это мощное решение для разработки и управления базами данных MySQL и MariaDB. Данный инструмент позволяет создавать и выполнять SQL запросы, разрабатывать и отлаживать процедуры и функции, а также управлять объектами баз данных MySQL с помощью удобного графического пользовательского интерфейса.

EMS SQL Management Studio for MySQL

EMS SQL Management Studio for MySQL – это еще одно комплексное и мощное решение от компании EMS, на этот раз для разработки и администрирования баз данных MySQL. Данный инструмент содержит все необходимые компоненты для работы с MySQL: редактор SQL запросов, средство импорта, экспорта и сравнения данных и много других, предназначенных не только для разработчиков, но и для администраторов и аналитиков данных.

SQL Maestro for MySQL

SQL Maestro for MySQL – это еще один инструмент разработки и администрирования баз данных MySQL и MariaDB.

PostgreSQL

PostgreSQL – эта бесплатная система управления базами данных, и она очень популярна и функциональна.

Для работы с PostgreSQL можно использовать следующие инструменты.

pgAdmin

pgAdmin – это основное, стандартное средство для разработки баз данных PostgreSQL, которое распространяется бесплатно.

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

• Страница продукта – https://www.pgadmin.org/
• Пример установки pgAdmin 4 на Windows 7

EMS SQL Management Studio for PostgreSQL

EMS SQL Management Studio for PostgreSQL – это комплексное решение для разработки и администрирования баз данных PostgreSQL. Данный инструмент так же, как все остальные продукты компании EMS, имеет очень широкий функционал от простого редактора SQL запросов до инструмента сравнения данных.

Navicat for PostgreSQL

Navicat for PostgreSQL – это простой графический инструмент для разработки баз данных PostgreSQL. Он позволяет писать и выполнять SQL запросы любой сложности.

dbForge Studio for PostgreSQL

dbForge Studio for PostgreSQL – это еще один мощный инструмент от компании Devart, на этот раз для работы с PostgreSQL. Он позволяет разрабатывать и выполнять запросы, редактировать код в удобном интерфейсе, формировать отчеты, модифицировать данные, а также осуществлять импорт и экспорт данных.

psql

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

DataGrip

Также осуществлять разработку баз данных PostgreSQL можно и с помощью уже упомянутого в этой статье универсального инструмента DataGrip от компании JetBrains.

Выводы

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

На сегодня это все, удачи Вам, пока!

8 способов сделать SQL запросы понятнее

За последние пять лет мне довелось поработать в трёх разных компаниях, но ни в одной из них я не встречал SQL запросы, которые выглядели бы опрятно и легко читались (не считая редких исключений). Как правило попадаются запросы написанные как попало: в них случайным образом скачут отступы и меняется регистр, они плохо структурированы и непоследовательны, зачастую ещё и написаны не очень эффективно. Открывая такой запрос приходится потратить немалое время, чтобы начать хоть что-то в нём понимать. А через месяц, встретив этот же запрос снова, придётся опять в нём разбираться. Многие люди вообще относятся к сиквелу как к второсорному языку, не проявляя к нему никакого уважения. Но уважения они не проявляют не только к языку, но и к другим разработчикам, которым в будущем приходится читать и поддерживать такие запросы.

Ниже я привожу 8 простых правил основанных на личном опыте. Следуя им, ваши запросы будут легко читаемыми и простыми для понимания другими разработчиками.

1. Никакого капса

В давние времена, когда редакторы кода не имели возможности подсвечивать синтаксис, было принято писать ключевые слова заглавными буквами. С тех пор эта привычка крепко укоренилась в головах некоторых разработчиков и они продолжают следовать этой традиции. Некоторые пошли ещё дальше, и стали писать капсом вообще всё: имена таблиц, стобцов и пр. На деле же, любой современный редактор кода имеет подстветку ситнаксиса (в т. ч. и для встроенных языков, если вы пишете запрос внутри другого языка). SELECT , FROM , WHERE навряд ли помогут вам понять суть запроса, а вот внимание на себя отвлекать однозначно будут. За 5 лет что я пишу SQL запросы, я редко встречал те которые можно назвать «образцовыми»: где все ключевые слова выделены капсом, а не ключевые нет. Зато смешивание этих стилей попадается сплошь и рядом.

2. Перенос строк

Тут я выделяю понятия остновных ключевых слов и второстепенных (вложенных). Так, например, select , from и where являются основными, join , on , and второстепенными. Основные слова выравнены по левому краю, второстепенные в зависимости от уровня вложенности сдвигаются вправо:

select . from table1 join table2 on and where . and . 

3. Отступы

В SQL предпочтительней использовать отступы из 2 пробелов. Запросы с такими отсупами выглядят опрятней и компактней в сравнении с другими вариантами.

4. Перечисления

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

select id, name, type_id . 

5. Соединения

Тема соединений таблиц всегда была очень болезненной. Когда-то и я перечислял имена таблиц через запятую, а все соединения наряду с предикатами делал в блоке where используя (+) вместо left join . Но, такая запись трудна для восприятия человеком, читающим этот запрос. Основных аргументов в её пользу, которые мне доводилось слышать, два: 1) ANSI соединения в оракле работают медленнее (на данный момент это уже не актуально); 2) глазам не приходится бегать по всему запросу, т. к. все условия находятся в одном месте. Аргумент 2 не выдерживает никакой критики, скорее это закостенелая привычка от которой сложно изавиться людям давно использующим такой синтаксис. Когда все условия собраны в where , это больше похоже на месиво в котором чёрт ногу сломит. Напротив, при использовании ANSI соединений, запрос выглядит опрятным, каждое такое соединение и его уточнение сосредоточено сразу под именем таблицы, а в блоке where находится лишь окончательный предикат глядя на который становится видно саму суть.

. from objects file join parameters path on path.object_id = file.id and path.attr_id = 123 where file.type_id = 404; 

Обратите внимание, что в условии соединения столбец текущей таблицы стоит слева, а столбец внешней таблицы стоит справа от знака равенства.

Нередко авторы запросов заключают в скобки условия соединений:

. from objects file join parameters path on (path.object_id = file.id and path.attr_id = 123) where file.type_id = 404; 

Сути это не меняет, а определённый шум вносит, поэтому лучше обойтись без них.

6. Алиасы для таблиц

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

select obj.name, p.value as message from objects obj join params p on p.object_id = obj.id and p.attr_id = 995 -- content where obj.type_id = 110; -- mail 

select mail.name, content.value as message from objects mail join params content on content.object_id = mail.id and content.attr_id = 995 where mail.type_id = 110; 

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

7. Запятые

Кто-то переносит запятую в перечислении на новую строку:

select field1 , field2 , field3 

Я не сторонник такого подхода. Конечно, это вносит определённое удобство при добавлении новых стобцов в запрос, но выглядит уродско.

8. Скобки

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

select name from table1 where field1 in (. . . ) and field2 in ( . . . ) 

Также обратите внимание, что если в where сразу же идёт какое-то перечисление, то такой предикат следует перенести на новую строчку, чтобы правило скобок не было нарушено:

select . from table1 where field1 in ( . ) and . ; 

select . ( select foo from table where . ) as bar, . 

Ещё несколько примеров

select id, name, parent_id from objects descriptor where type_id = 666 order by name; 

select id, name, type_id from objects descriptor where type_id in ( . ) and name like 'zek%'; 

with table1 as ( . ), table2 as ( . ) select . from table1 join table2 on table2.fieldA = table1.fieldA where table1.fieldB = 'blah'; 

Вывод

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