Основы построения SQL запросов

[NevesOma]

Программирование и SQL

Курс программирования SQL 25™ – это практический компьютерный курс изучения SQL запросов - структурированных запросов к базе данных. Почему именно курс программирования? Несмотря на то, что многие “буквоеды” будут утверждать, что SQL – это не язык программирования, а язык запросов, я считаю, что курсы по его изучению можно и нужно относить к курсам программирования. Во-первых, не называть же их курсами составления запросов, поскольку так их могут перепутать с курсами для начинающих чиновников или прокуроров. Во-вторых, работа с базами данных настолько тесно переплетается с программированием, что невладение навыками SQL довольно сильно сужает область применения того или иного программиста. Ну и в-третьих, структурированные запросы (не обязательно к базам данных) уже давно не на подступах к границам языков программирования – они ее уже перешагнули, и примером этому может служить технология LINQ.

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

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

Для кого этот компьютерный курс

Курс программирования SQL 25™ - компьютерный курс изучения SQL для тех, кто на практике хочет разобраться с принципами построения структурированных запросов. Никаких начальных знаний о SQL не требуется, но пройдя весь путь в 25 шагов до конца, вы вполне сможете продолжить самостоятельно изучать всевозможные расширения SQL, такие как PL/SQL, T-SQL и прочие. Знакомство с трудами Кристофера Дейта приветствуется.

Легенда

Курс программирования SQL 25™ – практический курс изучения SQL, а, следовательно, он проводится с использованием обучающей программы, которая позволяет составить и выполнить запрос на реальном наборе данных. Скачать и ознакомиться с работой обучающей программы можно и нужно здесь. Есть также возможность выполнять практически все запросы в режиме on-line, перейдя на страницу специально созданного для этих целей веб-приложения, работающего с СУБД MySQL.

Все примеры построены вокруг запросов к трем таблицам

содержащим следующую информацию:

D_STAFF - список сотрудников компании;

XD_IID – Уникальный идентификатор записи;
S_NAME – Ф.И.О.
S_POSITION – должность (справочник);
S_EXPERIENCE – стаж работы (целое количество лет);
S_CHIEF_ID – вышестоящий сотрудник компании (отношение “начальник”-“подчиненный”);
S_COMMENTS – примечание.

D_PROFIE – список профилей пользователей информационной системы, которая, по легенде, развернута на территории рассматриваемого предприятия и помогает автоматизировать некоторые его бизнес-процессы. Этой системой могла бы быть как отечественная разработка “1С – Предприятие”, так и система на базе программного комплекса SAP R/3, являющегося наиболее ярким представителем такого класса программного обеспечения за рубежом;

XD_IID – Уникальный идентификатор записи;
P_NAME – наименование профиля (роли);
P_COMMENTS – примечание.

D_STAFF_PROFILE – таблица, связывающая сотрудников компании (D_STAFF) с профилями пользователей системы (D_PROFILE). Каждая запись в этой таблице - это связь, которая определяет возможность сотрудника входить в систему со всеми правами и разрешениями выбранного для него профиля. Каждый сотрудник может иметь несколько разрешенных ему профилей, а любой профиль может быть связан с несколькими различными сотрудниками. Таким образом, таблица D_STAFF_PROFILE определяет связь "многие-ко-многим".

STAFF_ID – ссылка на сотрудника;
PROFILE_ID – ссылка на профиль пользователя.

Краткий обзор синтаксиса рассматриваемых SQL запросов

Перед тем, как перейти к самому курсу изучения SQL запросов, хочу сделать краткий обзор синтаксиса рассматриваемых в нем команд. Нужно понимать, что все SQL запросы делятся на четыре группы: инструкции или операторы для определения данных (Data Definition Language, DDL), для манипуляции данными (Data Manipulation Language, DML), для определения параметров доступа к данным (Data Control Language, DCL) и для управления транзакциями (Transaction Control Language, TCL). Определение данных подразумевает создание, редактирование и удаление различных объектов базы данных, таких как таблицы (tables), табличные представления (views), синонимы (synonyms), хранимые процедуры, профили пользователя и т.п. Определение параметров доступа к данным – это процесс награждения или лишения объектов базы данных различного рода разрешениями, привилегиями и полномочиями, например, предоставление конкретному пользователю базы данных (имеется в виду объект типа user/schema, который определяет права доступа к разделам базы данных в распределенных СУБД, например, в Oracle) возможности осуществлять запросы к конкретной таблице. Управление транзакциями, в самом простом варианте, сводится к возможности сохранить текущие изменения, накопившиеся в результате выполнения последовательности запросов манипуляции данными, или целиком их все отменить.

Чаще всего, под SQL запросами понимается именно группа операторов манипуляции данными. Каждая отдельно взятая СУБД поддерживает ту или иную группу SQL запросов в разной мере/объеме, но, наибольшим образом все они пересекаются именно в реализации операций манипуляции данными. По этой причине, в ходе изучения будет рассматриваться только эта группа команд: выбор, обновление, добавление и удаление записей из таблиц. Более того, первые 6 этапов будут посвящены исключительно процессу выбору данных – команде SELECT, и только последний этап рассмотрению оставшихся трех SQL команд. Команда выбора данных может использоваться, как сама по себе, так и в качестве подзапросов в контексте других SQL запросов, и ей необходимо уделить особое внимание.
Выбор данных или команда SELECT

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

SELECT <что> FROM <откуда> [WHERE <список условий>] [ORDER BY {<колонка> [ASC|DESC]}]

Под “что” подразумевается список полей таблиц или колонок подзапроса (через запятую), под “откуда” – сами эти таблицы и подзапросы (также через запятую). В квадратных скобках указаны необязательные части SQL инструкции, к которым, в том числе, относится инструкция WHERE (где). После ключевого слова WHERE перечисляются ограничения на значения колонок, объединенные логическими операциями И (AND) и ИЛИ (OR). Самый обычный SQL запрос выглядит примерно так:

SELECT ТАБЛИЦА1.ПОЛЕ1, ТАБЛИЦА2.ПОЛЕ2 FROM ТАБЛИЦА1, ТАБЛИЦА2 WHERE ТАБЛИЦА1.ПОЛЕ1>10 AND ТАБЛИЦА1.ПОЛЕ2=ТАБЛИЦА2.ПОЛЕ1 
ORDER BY ТАБЛИЦА1.ПОЛЕ1 ASC, ТАБЛИЦА2.ПОЛЕ2 DESC

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

Обновление данных или команда UPDATE

Назначение команды UPDATE – обновление существующих записей в указанной таблице. Указываем, где и чего хотим изменить, а после ключевого слова WHERE устанавливаем критерии отбора обновляемых записей. В команде обновления данных, как и в других SQL запросах, можно использовать подзапросы: например, можно определить в качестве присваиваемого полю значения результат подзапроса, который возвращает только одну колонку и одну строчку.

UPDATE <таблица> SET {<поле> = <выражение>} [WHERE <список условий>]
или
UPDATE <таблица> SET {<поле> = (SELECT <значение> FROM <откуда> WHERE <список условий>)} [WHERE <список условий>]

Удаление данных или команда DELETE

Удаление строк из одной таблицы или сразу из нескольких таблиц, строки которых объединены условиями. Здесь все также очевидно. Указываем, из какой таблицы удаляем данные, а в части WHERE отбираем удаляемые данные. Во втором варианте SQL запроса с использованием инструкции DELETE показан обобщенный синтаксис удаления записей сразу из нескольких таблиц. В условиях отбора (в части WHERE) также можно использовать подзапросы.

DELETE FROM <таблица> [WHERE <список условий>]
или
DELETE <список таблиц> FROM <список источников> [WHERE <список условий>]