Разработка урока: Язык SQL. Основные понятия

Урок 1

Язык SQL. Основные понятия

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

Что такое База Данных

База данных (БД) - упорядоченный набор логически взаимосвязанных данных, используемых совместно, и которые хранятся в одном месте. Если коротко, то простейшая БД это обычная таблица со строками и столбцами в которой хранится разного рода информация (примером может служить таблица в Excel). Так, часто, с БД нераздельно связывают Системы управления базами данных (СУБД), которые предоставляют функционал для работы с БД. Язык SQL как раз и является частью СУБД, которая осуществляет управление информацией в БД. Мы будем считать БД набором обычных таблиц, которые хранятся в отдельных файлах.

Что такое SQL

Итак, переходим к SQL.

SQL - простой язык программирования, который имеет немного команд и которой может научиться любой желающий. Расшифровывается как Structured Query Language - язык структурированных запросов, который был разработан для работы с БД, а именно, чтобы получать /добавлять /изменять данные, иметь возможность обрабатывать большие массивы информации и быстро получать структурированную и сгруппированную информацию. Есть много вариантов языка SQL, но у них всех основные команды почти одинаковы. Также существует и много СУБД, но основными из них являются: MicrosoftAccess, Microsoft SQL Server, MySQL, Oracle SQL, IBM DB2 SQL, PostgreSQL та SybaseAdaptiveServer SQL. Чтобы работать с SQL кодом, нам понадобится одна из вышеперечисленных СУБД. Для обучения мы будем использовать СУБД MicrosoftAccess.

SQL как и другие языки программирования имеет свои команды (операторы), с помощью которых отдаются инструкции для выборки данных. Чтобы рассмотреть как работают операторы SQL, мы будем использовать мнимую БД с информацией о реализованной продукции:

Выборка данных (SELECT)

Самым первым и главным оператором в SQL является SELECT. С его помощью мы можем отбирать необходимые нам поля данных в таблице.

Выборка отдельных полей.

SELECT Product FROM Sumproduct

Видим, что наш SQL запрос отобрал колонку Product из таблицы Sumproduct.

Выборка нескольких полей

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

SELECT Product, Quantity FROM Sumproduct

Выборка всех столбцов

Если же нам необходимо получить всю таблицу со всеми полями, тогда просто ставим знак звездочка (*):

SELECT * FROM Sumproduct

Поздравляю, Вы сделали первые SQL запросы.

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

Сортировка (ORDER BY)

В будущем нам может понадобиться сортировать нашу выборку - в алфавитном порядке для текста или по возрастанию / убыванию - для цифровых значений. Для таких целей в SQL есть специальный оператор ORDER BY.

Сортировка выбранных данных.

Давайте всю нашу таблицу посортируем по сумме реализации продукции, а именно по столбцу Amount.

SELECT * FROM Sumproduct ORDER BY Amount

Видим, что запрос посортировал записи по возрастанию в поле Amount. Обязательно нужно соблюдать последовательность расположения операторов, т.е. оператор ORDER BY должен идти в самом конце запроса. В противном случае будет получено сообщение об ошибке.

Также особенностью оператора ORDER BY является то, что он может сортировать данные по полю, которого мы не выбирали в запросе, то есть достаточно, чтобы оно вообще было в БД.

Сортировка по нескольким полям.

Теперь посортируем наш пример дополнительно за еще одним полем. Пусть это будет поле City, которое отображает место реализации продукции.

SELECT * FROM Sumproduct ORDER BY Amount, City

Очередность сортировки будет зависеть от порядка расположения полей в запросе. То есть, в нашем случае сначала данные будут рассортированы по колонке Amount, а затем по City.

3. Направление сортировки.

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

SELECT * FROM Sumproduct ORDER BY Amount DESC, City

В данном примере, значение в поле Amount были посортированы по убыванию, а в поле City - по возрастанию. Оператор DESC применяется только для одного столбца, поэтому при необходимости его нужно прописывать после каждого поля, которое принимает участие в сортировке.

Фильтрация данных (WHERE)

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

Простое фильтрование оператором WHERE.

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

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

SELECT * FROM Sumproduct WHERE Product = 'Bikes'

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

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

SELECT * FROM Sumproduct WHERE Amount > 40000 ORDER BY Amount

В этом примере мы отобрали записи, в которых выручка от реализации составила более 40 тыс. $ и, дополнительно, все записи посортировали по возрастанию по полю Amount.

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

Фильтрация по диапазону значений (BETWEEN).

Для отбора данных, которые лежат в определенном диапазоне, используется оператор BETWEEN. В следующем запросе будут отобраны все значения, лежащие в пределах от 1000 $ в 2000 $ включительно, в поле Amount.

SELECT * FROM Sumproduct WHERE Amount BETWEEN 1000 AND 2000

Очередность сортировки будет зависеть от порядка расположения полей в запросе. То есть, в нашем случае сначала данные будут посортированы по колонке Amount, а затем по City.

Выборка пустых записей (IS NULL).

В SQL существует специальный оператор для выборки пустых записей (називаеьбся NULL). Пустой записью считается любая ячейка в таблице, в которую не введены какие-либо символы. Если в ячейку введен 0 или пробел, то считается, что поле заполнено.

SELECT * FROM Sumproduct WHERE Amount IS NULL

В примере выше, мы нарочно удалили два значения в поле Amount, чтобы продемонстрировать работу оператора NULL.

Расширенное фильтрации (AND, OR).

Язык SQL не ограничивается фильтрацией по одному условию, для собственных целей вы можете использовать достаточно сложные конструкции для выборки данных одновременно по многим критериям. Для этого в SQL есть дополнительные операторы, которые расширяют возможности оператора WHERE. Такими операторами являются: AND, OR, IN, NOT. Приведем несколько примеров работы данных операторов.

SELECT * FROM Sumproduct WHERE Amount > 40000 AND City = 'Toronto'

SELECT * FROM Sumproduct WHERE Month= 'April' OR Month= 'March'

Давайте объединим операторы AND и OR. Для этого сделаем выборку велосипедов (Bikes) и коньков (Skates), которые были проданы в марте (March).

SELECT * FROM Sumproduct WHERE Product = 'Bikes' OR Product = 'Skates' AND Month= 'March'

Видим, что в нашу выборку попало за много значений (кроме марта (March), также январь (January), февраль (February) и апрель (April)). В чем же причина? А в том, что SQL имеет приоритеты выполнения команд. То есть оператор AND имеет более высокий приоритет, чем оператор OR, поэтому сначала были отобраны записи с коньками, которие проданные в марте, а потом все записи, касающиеся велосипедов.

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

SELECT * FROM Sumproduct WHERE (Product = 'Bikes' OR Product = 'Skates') AND Month= 'March'

Расширенная фильтрация (оператор IN).

SELECT * FROM Sumproduct WHERE ID IN (4, 12, 58, 67)

Оператор IN выполняет ту же функцию, что и OR, однако имеет ряд преимуществ:

· При работе с длинными списками, предложение с IN легче читать;

· Используется меньшее количество операторов, что ускоряет обработку запроса;

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

Расширенная фильтрация (оператор NOT).

SELECT * FROM Sumproduct WHERE NOT City IN ('Toronto', 'Montreal')

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

Символы подстановки и регулярные выражения (LIKE)

Часто, для фильтрации данных, нам нужно будет осуществить выборку не по точному совпадении условия, а по приближенному значению. То есть когда, например, мы ищем товар, название которого соответствует определенному шаблону или содержит определенные символы или слова. Для таких целей в SQL существует оператор LIKE, который ищет приближенные значения. Для конструирования такого шаблона используются метасимволы (специальные символы для поиска части значения), а именно: «знак процента» (%) или звездочка (*), «символ подчеркивания» (_) или «знак вопроса» (?), «квадратные скобки» ([]).

Метасимвол знак процента (%) или звездочка (*)

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

SELECT * FROM Sumproduct WHERE Product LIKE '*Skis*'

Как видим, СУБД отобрала только те записи, где в колонке Product были товары, содержащие слово Skis. Также отметим, что в данном примере используется метасимвол «звездочка» (*), поскольку СУБД Access не поддерживает «знак процента» (%) для оператора LIKE.

Метасимвол знак подчеркивания (_) или знак (?)

Знак подчеркивания или вопросительный знак применяется для того, чтобы заменить один символ в слове. Давайте в слове Bikes заменим все гласные буквы на «вопросительный знак» (?) и посмотрим на результат:

SELECT * FROM Sumproduct WHERE Product LIKE 'B? k? s'

Мы использовали метасимвол «вопросительный знак» (?), поскольку СУБД Access не поддерживает «знак подчеркивания» (_) для оператора LIKE.

Метасимвол квадратные скобки ([])

Метасимвол «квадратные скобки» ([]) используется для одновременного указания набора символов, по которым нужно выполнить поиск.

SELECT * FROM Sumproduct WHERE City LIKE '[TN]*'

В примере выше, мы отобрали записи, где в поле City названия городов начинаются с буквы T или N. Также, в данном случае, мы можем использовать еще один метасимвол, который выполняет обратное действие. Добавим в наше регулярное выражение восклицательный знак (!), что будет означать «не равно» (для СУБД Access) или знак степени (^) (для других СУБД).

SELECT * FROM Sumproduct WHERE City LIKE '[! TN]*'

То есть, последний созданный нами запрос будет читаться как: выбрать все колонки из таблицы Sumproduct и только те записи, где в поле City названия городов не начинаются на буквы T или N. Дополнительно отметим, что набор букв в метасимволе «квадратные скобки» отвечает только за одну позицию в тексте.

Мы можем получить аналогичный результат, если воспользоваться уже известным нам оператором NOT, однако с восклицательным знаком (!) запись будет короче.

Вычисляемые поля

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