Размещено на http://www.allbest.ru/

Размещено на http://www.allbest.ru/

Урок 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	Между двумя значениями
IS NULL	Отсутствует запись

Фильтрация по диапазону значений (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, однако с восклицательным знаком (!) запись будет короче.

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

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

Выполнение математических операций

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

SELECT DISTINCT Product, Amount/Quantity FROM Sumproduct

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

Использование псевдонимов

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

SELECT DISTINCT Product, Amount/Quantity AS AvgPrice FROM Sumproduct



Видим, наше расчетное поле получило собственное название AvgPrice. Для этого мы использовали оператор AS, после которого указали необходимое нам название. Стоит отметить, что в SQL поддерживаются только основные математические операции: сложение (+), вычитание (-), умножение (*), деление (/). Также для изменения очередности выполнения операции можно использовать круглые скобки.

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

Соединение полей (конкатенация)

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

SELECT Month + ' ' + Product AS NewField, Quantity FROM Sumproduct

В этом примере мы соединили значение в двух столбцах и вывели результат в новое поле NewField.

Функции обработки данных

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

Обычно СУБД поддерживается стандартный набор типов функций, а именно:

· Текстовые функции, которые используются для обработки текста (выделение части символов в тексте, определение длины текста, перевод символов в верхний или нижний регистр…)

· Числовые функции. Используются для выполнения математических операций над числовыми значениями

· Функции даты и времени (осуществляют манипулирования датой и временем, рассчитывают период между датами, проверяют даты на корректность и т.п.)

· Статистические функции (для вычисления максимальных /минимальных значений, средних значений, подсчет количества и суммы…)

· Системные функции (предоставляют разного рода служебную информацию о СУБД, пользователе и др.).

Функции SQL для обработки текста

Реализация SQL в СУБД Access имеет следующие функции для обработки текста:

Знак операции	Значение
LEFT()	Отбирает символы в тексте слева
RIGHT()	Отбирает символы в тексте справа
MID()	Отбирает символы с середины текста
UCase()	Переводит символы в верхний регистр
LCase()	Переводит символы в нижний регистр
LTrim()	Удаляет все пустые символы слева от текста
RTrim()	Удаляет все пустые символы справа от текста
Trim()	Удаляет все пустые символы с обеих сторон текста

Переведем названия товаров в верхний регистр с помощью функции UCase():

SELECT Product, UCase(Product) AS Product_UCase FROM Sumproduct

Выделим первые три символа в тексте с помощью функции LEFT():

SELECT Product, LEFT (Product, 3) AS Product_LEFT FROM Sumproduct

Функции SQL для обработки чисел

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

Знак операции	Значение
SQR()	Возвращает корень квадратный указанного числа
ABS()	Возвращает абсолютное значение числа
EXP()	Возвращает экспоненту указанного числа
SIN()	Возвращает синус указанного угла
COS()	Возвращает косинус указанного угла
TAN()	Возвращает тангенс указанного угла

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

Например, напишем запрос для получения корня квадратного для чисел в столбце Amount с помощью функции SQR():

SELECT Amount, SQR(Amount) AS Amount_SQR FROM Sumproduct

Функции SQL для обработки даты и времени

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

Список некоторых функций для обработки даты и времени в СУБД Access:

Знак операции	Значение
DatePart()	Возвращает часть даты: год, квартал, месяц, неделя, день, час, минуты, секунды
Year(), Month()	Возвращает год и месяц соответственно
Hour(), Minute(), Second()	Возвращает час, минуты и секунды указанной даты
WeekdayName()	Возвращает название дня недели

Посмотрим на примере как работает функция DatePart():

SELECT Date1, DatePart («m», Date1) AS Month1 FROM Sumproduct

Функция DatePart () имеет дополнительный параметр, который нам позволяет отобразить необходимую часть даты. В примере мы использовали параметр «m», который отображает номер месяца (таким же образом мы можем отразить год - «yyyy», квартал - «q», день -» d», неделю -» w», час -» h», минуты - «n», секунды - «s» и т.д.).

Статистические функции SQL

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

Список статистических функций в СУБД Access

Знак операции	Значение
COUNT()	Возвращает число строк в таблице или столбце
SUM()	Возвращает сумму значений в столбце
MIN()	Возвращает наименьшее значение в столбце
MAX()	Возвращает наибольшее значение в столбце
AVG()	Возвращает среднее значение в столбце

Примеры использования функции COUNT():

SELECT COUNT(*) AS Count1 FROM Sumproduct - возвращает количество всех строк в таблице

SELECT COUNT(Product) AS Count2 FROM Sumproduct - возвращаетколичествовсехнепустыхстроквполе Product

Мы намеренно удалили одно значение в столбце Product, чтобы показать разницу в работе двух запросов.

Примеры использования функции SUM():

SELECT SUM(Quantity) AS Sum1 FROM Sumproduct WHERE Month = 'April'

Данным запросу мы отразили общее количество проданного товара в апреле.

SELECT SUM (Quantity*Amount) AS Sum2 FROM Sumproduct

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

Пример использования функции MIN():

SELECT MIN(Amount) AS Min1 FROM Sumproduct

Пример использования функции MAX():

SELECT MAX(Amount) AS Max1 FROM Sumproduct

Пример использования функции AVG():

SELECT AVG(Amount) AS Avg1 FROM Sumproduct

Группировка данных (GROUP BY)

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

Создание групп (GROUP BY)

Группы создаются с помощью предложения GROUP BY оператора SELECT. Рассмотрим на примере.

SELECT Product, SUM(Quantity) AS Product_num FROM Sumproduct GROUP BY Product

Данным запросом мы извлекли информацию о количестве реализованной продукции в каждом месяце. Оператор SELECT приказывает вывести два столбца Product - название продукта и Product_num - расчетное поле, которое мы создали для отображения количества реализованной продукции (формула поля SUM (Quantity)). Предложение GROUP BY указывает СУБД сгруппировать данные по столбцу Product. Стоит также отметить, что GROUP BY должен идти после предложения WHERE и перед ORDER BY.

Фильтрующие группы (HAVING)

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

SELECT Product, SUM(Quantity) AS Product_num FROM Sumproduct GROUP BY Product HAVING SUM(Quantity)>4000

Видим, что после того, как была посчитана количество реализованного товара в разрезе каждого продукта, СУБД «отсекла» те продукты, которых было реализовано меньше 4000 шт.

Как видим, оператор HAVING очень похож на оператора WHERE, однако между собой они имеют существенное отличие: WHERE фильтрует данные до того, как они будут сгруппированы, а HAVING - осуществляет фильтрацию после группировки. Таким образом, строки, которые были изъяты предложением WHERE НЕ будут включены в группу. Итак, операторы WHERE и HAVING могут использоваться в одном предложении. Рассмотрим пример:

SELECT Product, SUM(Quantity) AS Product_num FROM Sumproduct WHERE Product<>'Skis Long' GROUP BY Product HAVING SUM(Quantity)>4000

Мы к предыдущему примеру добавили оператор WHERE, где указали товар SkisLong, что в свою очередь повлияло на группирование оператором HAVING. Как результат видим, что товар SkisLong не попал в перечень групп с количеством реализованной продукции больше 4000 шт.

Группировка и сортировка

Как и при обычной выборке данных, мы можем сортировать группы после группировки оператором HAVING. Для этого мы можем использовать уже знакомый нам оператор ORDER BY. В данной ситуации его применения аналогичное предыдущим примерам. К примеру:

SELECT Product, SUM(Quantity) AS Product_num FROM Sumproduct GROUP BY Product HAVING SUM(Quantity)>3000 ORDER BY SUM(Quantity) или просто укажем номер поля по порядку, по которому хотим сортировать:

SELECT Product, SUM(Quantity) AS Product_num FROM Sumproduct GROUP BY Product HAVING SUM(Quantity)>3000 ORDER BY 2



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

Подзапросы

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

Фильтрация с помощью подзапросов

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



Теперь мы имеем две таблицы - Sumproduct и Sellers, которые имеют одинаковое поле City. Предположим, нам нужно посчитать сколько товаров было продано только в Канаде. Сделать это нам помогут подзапросы. Итак, сначала напишем запрос для выборки городов, которые находятся в Канаде:

SELECT City FROM Sellers WHERE Country = 'Canada'

Теперь передадим эти данные в следующий запрос, который будет выбирать данные из таблицы Sumproduct:

SELECT SUM(Quantity) AS Qty_Canada FROM Sumproduct WHERE City IN ('Montreal', 'Toronto')

Также мы можем объединить эти два запроса в один. Таким образом, один запрос, который выводит данные будет главным, а второй запрос, которий передает входные данные, будет вспомогательным (подзапросом). Для вложения подзапроса используем конструкцию WHERE… IN (…), о которой говорилось в разделе Расширенное фильтрование:

SELECT SUM(Quantity) AS Qty_Canada FROM Sumproduct WHERE City IN (SELECT City FROM Sellers WHERE Country = 'Canada')

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

Использование подзапросов в качестве расчетных полей

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

SELECT Seller_name, (SELECT SUM(Quantity) FROM Sumproduct WHERE Sellers. City = Sumproduct. City) AS Qty FROM Sellers

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

Также в подзапросе, предложение WHERE выполняет функцию объединения, поскольку с помощью WHERE мы соединили две таблицы по полю City, использовав полные названия столбцов (Таблиця. Поле).

Объединение таблиц (INNER JOIN)

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

Создание объединения таблиц

Объединение таблиц очень простая процедура. Нужно указать все таблицы, которые будут включены в объединение и «объяснить» СУБД, как они будут связаны между собой. Объединение делается с помощью слова WHERE, например:

SELECT DISTINCT Seller_name, Product FROM Sellers, Sumproduct WHERE Sellers. City = Sumproduct. City

Соединив две таблицы, мы смогли увидеть какие товары реализует каждый продавец. Рассмотрим код запроса подробнее, поскольку он немного отличается от обычного запроса. Оператор SELECT начинается с указанием столбцов, которые мы хотим вывести, однако эти поля находятся в разных таблицах, предложение FROM содержит две таблицы, которые мы хотим объединить в операторе SELECT, таблицы объединяются с помощью слова WHERE, указывающее столбцы для объединения. Обязательно нужно указывать полное название поля (Таблиця. Поле), поскольку поле City есть в обоих таблицах.

Внутреннее объединение

В предыдущем примере для объединения таблиц мы использовали слово WHERE, которое осуществляет проверку на основе эквивалентности двух таблиц. Объединение такого типа называется также «внутренним объединением». Существует также и другой способ объединения таблиц, который явно указывает на тип объединения. Рассмотрим следующий пример:

SELECT DISTINCT Seller_name, Product FROM Sellers INNER JOIN Sumproduct ON Sellers. City = Sumproduct. City

В этом запросе вместо WHERE мы использовали конструкцию INNER JOIN… ON…, которая дала аналогичный результат. Несмотря на то, что объединение с предложением WHERE короче, все же лучше использовать INNER JOIN, поскольку она является более гибкой, о чем будет подробнее рассказано в следующих разделах.

Расширенное объединение таблиц (OUTER JOIN)

В предыдущем разделе мы рассмотрели самые простые способы объединения таблиц - с помощью предложений WHERE и INNER JOIN. Эти объединения называются внутренними объединениями или объединениями по эквивалентности. Однако SQL имеет в своем арсенале гораздо больше возможностей объединить таблицы, а именно существуют также и другие виды объединений: внешние объединения, природные объединения и самообъединения. Но для начала рассмотрим, каким образом мы можем присваивать таблицам псевдонимы, поскольку в дальнейшем, мы будем вынуждены использовать полные названия полей (Таблиця. Поле), которыми без сокращений будет очень трудно оперировать из-за их большой длины.

Использование псевдонимов таблиц

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

SELECT Seller_name, SUM(Amount) AS Sum1

FROM Sellers AS S, Sumproduct AS SP

WHERE S. City = SP. City

GROUP BY Seller_name

Мы отобразили общую сумму реализованного товара по каждому продавцу. В нашем SQL запросе мы использовали такие псевдонимы: для расчетного поля SUM (Amount) псевдоним Sum1 для таблицы Sellers псевдоним S и для Sumproduct псевдоним SP. Заметим, что псевдонимы таблиц могут быть применены и в других предложениях, как ORDER BY, GROUP BY и других.

Самообъединения

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

SELECT City, Country, Seller_name

FROM Sellers

WHERE Country = (SELECT Country FROM Sellers WHERE Seller_name = 'John Smith')

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

SELECT S1. Address, S1. City, S1. Country, S1. Seller_name

FROM Sellers AS S1, Sellers AS S2

WHERE S1. Country = S2. Country AND S2. Seller_name = 'John Smith'

Для решения этой задачи использовались псевдонимы. Первый раз для таблицы Sellers присвоили псевдоним S1, второй раз - псевдоним S2. После этого эти псевдонимы можно применять в качестве имен таблиц. В операторе WHERE мы с названием каждого поля добавляем префикс S1, для того, чтобы СУБД понимала поля которой таблицы нужно выводить (поскольку мы из одной таблицы сделали две виртуальные). Предложение WHERE сначала объединяет таблицы, а затем фильтрует данные второй таблицы по полю Seller_name, чтобы вернуть только необходимые значения.

Самообъединения часто используют для замены подзапросов, которые выбирают данные из той же таблицы, что и внешний оператор SELECT. Хотя конечный результат получается тем самым, многие СУБД обрабатывают объединения гораздо быстрее подзапросов. Стоит поэкспериментировать, чтобы определить, какой запрос работает быстрее.

Естественное объединение

Естественное объединение - это объединение, в котором вы выбираете только те столбцы, которые не повторяются. Обычно это делается с помощью записи (SELECT *) для одной таблицы и указанием перечня полей - для остальных таблиц. Например:

SELECT SP.*, S. Country

FROM Sumproduct AS SP, Sellers AS S

WHERE SP. City = S. City

В этом примере метасимвол (*) используется только для первой таблицы. Все остальные столбцы указаны явно, поэтому дубликаты столбцов не выбираются.

Внешнее объединение (OUTER JOIN)

Обычно при объединении связывают строки одной таблицы с соответствующими строками другой, однако в некоторых случаях может потребоваться включать в результат строки, не имеющие связанных строк в другой таблице (т.е. выбираются совершенно все строки из одной таблицы и добавляются только связанные строки из другой). Объединение такого типа называется внешним. Для этого используются ключевые слова OUTER JOIN… ON… с приставкой LEFT или RIGHT. Рассмотрим пример, предварительно добавив в таблицу Sellers нового продавца - SemuelPiter, который не имеет продаж:

SELECT Seller_name, SUM(Quantity) AS Qty

FROM Sellers LEFT OUTER JOIN Sumproduct ON Sellers. City=Sumproduct. City

GROUP BY Seller_name

Данным запросом мы вытащили перечень всех продавцов в базе и подсчитали для них общее количество проданного товара за все месяцы. Видим что по новому продавцу SemuelPiter отсутствуют продажи. Если бы мы использовали внутреннее объединение, то нового продавца мы бы не увидели, поскольку он не имеет записей в таблице Sumproduct. Мы можем изменять направление объединения не только прописывая LEFT или RIGHT, но и просто меняя порядок таблиц (т.е. две записи будут давать одинаковый результат: Sellers LEFT OUTER JOIN Sumproduct та Sumproduct RIGHT OUTER JOIN Sellers).

Также некоторые СУБД позволяют осуществлять внешнее объединение по упрощенной записи, используя знаки * = и = *, что соответствует LEFT OUTER JOIN и RIGHT OUTER JOIN соответственно. Таким образом предыдущий запрос можно было бы переписать так:

SELECT Seller_name, SUM(Quantity) AS Qty

FROM Sellers, Sumproduct

WHERE Sellers. City *= Sumproduct. City

К сожалению Access не поддерживает сокращенную запись для внешнего объединения.

Полное внешнее объединение (FULL OUTER JOIN)

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

SELECT Seller_name, Product

FROM Sellers FULL OUTER JOIN Sumproduct ON Sellers. City=Sumproduct. City

Опять же, полное внешнее объединение не поддерживают такие СУБД: Access, MySQL, SQL Server и Sybase. Как обойти эту несправедливость, мы рассмотрим в следующем разделе.

SQL-Урок 12. Комбинированные запросы (UNION)

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

Использование оператора UNION

Запросы в языке SQL комбинируются с помощью оператора UNION. Для этого необходимо указать каждый запрос SELECT и разместить между ними ключевое слово UNION. Ограничений по количеству использованного оператора UNION в одном общем запросе нет. В предыдущем разделе мы отмечали, что Access не имеет возможности создавать полное внешнее объединение, теперь мы посмотрим, как можно этого достичь через оператор UNION.

SELECT *

FROM Sumproduct LEFT JOIN Sellers ON Sumproduct. City = Sellers. City

UNION

SELECT *

FROM Sumproduct RIGHT JOIN Sellers ON Sumproduct. City = Sellers. City



Видим, что запрос отобразил как все колонки из первой таблицы - так и с другой, независимо от того, все ли записи имеют соответствия в другой таблице.

Также стоит отметить, что во многих случаях вместо UNION мы можем использовать предложение WHERE со многими условиями, и получать аналогичный результат. Однако из-за UNION записи выглядят более лаконичными и понятными. Также необходимо соблюдать определенные правила при написании комбинированных запросов:

· запрос UNION должен включать два и более операторов SELECT, отделенных между собой ключевым словом UNION (т.е. если в запросе используется четыре оператора SELECT, то должно быть три ключевых слова UNION)

· каждый запрос в операторе UNION должен иметь одни и те же столбцы, выражения или статистические функции, которые, к тому же, должны быть перечислены в одинаковом порядке

· типы данных столбцов должны быть совместимыми. Они не обязательно должны быть одного типа, однако обязаны иметь подобный тип, чтобы СУБД могла их однозначно преобразовать (например, это могут быть различные числовые типы данных или различные типы даты).

Включение или выключение повторяющихся строк

Запрос с UNION автоматически удаляет все повторяющиеся строки из набора результатов запроса (то есть, ведет себя как предложения WHERE с несколькими условиями в одном операторе SELECT). Такое поведение оператора UNION по умолчанию, но при желании мы можем изменить это. Для этого нам следует использовать оператор UNION ALL вместо UNION.

Сортировка результатов комбинированных запросов

Результаты выполнения оператора SELECT сортируются с помощью предложения ORDER BY. При комбинировании запросов с помощью UNION только одно предложение ORDER BY может быть использовано, и оно должно быть проставлено в последнем операторе SELECT. Действительно, на практике нет особого смысла часть результатов сортировать в одном порядке, а другую часть - в другом. Поэтому несколько предложений ORDER BY применять не разрешается.

Добавление данных (INSERT INTO)

В предыдущих разделах мы рассматривали работу по получению данных с заранее созданных таблиц. Теперь пора разобрать, каким же образом мы можем создавать / удалять таблицы, добавлять новые записи и удалять старые. Для этих целей в SQL существуют такие операторы, как: CREATE - создает таблицу, ALTER - изменяет структуру таблицы, DROP - удаляет таблицу или поле, INSERT - добавляет данные в таблицу. Начнем знакомство с данной группой операторов из оператора INSERT.

Добавление целых строк

Как видно из названия, оператор INSERT используется для вставки (добавления) строк в таблицу базы данных. Добавление можно осуществить несколькими способами:

· - добавить одну полную строку

· - добавить часть строки

· - добавить результаты запроса.

Итак, чтобы добавить новую строку в таблицу, нам необходимо указать название таблицы, перечислить названия колонок и указать значение для каждой колонки с помощью конструкции INSERT INTO название_таблицы (поле1, поле2…) VALUES (значение1, значение2…). Рассмотримнапримере.

INSERT INTO Sellers (ID, Address, City, Seller_name, Country) VALUES ('6', '1st Street', 'Los Angeles', 'Harry Monroe', 'USA')



Также можно изменять порядок указания названий колонок, однако одновременно нужно менять и порядок значений в параметре VALUES.

Добавление части строк

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

INSERT INTO Sellers (ID, City, Seller_name) VALUES ('6', 'Los Angeles', 'Harry Monroe')

В данном примере мы не указали значение для двух столбцов Address и Country. Вы можете исключать некоторые столбцы из оператора INSERT INTO, если это позволяет производить определение таблицы. В этом случае должно соблюдаться одно из условий: этот столбец определен как допускающий значение NULL (отсутствие какого-либо значения) или в определение таблицы указанное значение по умолчанию. Это означает, что, если не указано никакое значение, будет использовано значение по умолчанию. Если вы пропускаете столбец таблицы, которая не допускает появления в своих строках значений NULL и не имеет значения, определенного для использования по умолчанию, СУБД выдаст сообщение об ошибке, и это строка не будет добавлена.

Добавление отобранных данных

В предыдущей примерах мы вставляли данные в таблицы, прописывая их вручную в запросе. Однако оператор INSERT INTO позволяет автоматизировать этот процесс, если мы хотим вставлять данные из другой таблицы. Для этого в SQL существует такая кострукция как INSERT INTO… SELECT…. Данная конструкция позволяет одновременно выбирать данные из одной таблицы, и вставить их в другую. Предположим мы имеем еще одну таблицу Sellers_EU с перечнем продавцов нашего товара в Европе и нам нужно их добавить в общую таблицу Sellers. Структура этих таблиц одинакова (то же количество колонок и те же их названия), однако другие данные. Дляэтогомыможемпрописатьследующийзапрос:

INSERT INTO Sellers (ID, Address, City, Seller_name, Country) SELECT ID, Address, City, Seller_name, Country FROM Sellers_EU

Нужно обратить внимание, чтобы значение внутренних ключей не повторялись (поле ID), в противном случае произойдет ошибка. Оператор SELECT также может включать предложения WHERE для фильтрации данных. Также следует отметить, что СУБД не обращает внимания на названия колонок, которые содержатся в операторе SELECT, для нее важно только порядок их расположения. Поэтому данные в первом указанном столбце, что были выбраны из-за SELECT, будут в любом случае заполнены в первый столбец таблицы Sellers, указанной после оператора INSERT INTO, независимо от названия поля.

Копирование данных из одной таблицы в другую

Часто при работе с базами данных возникает необходимость в создании копий любых таблиц, с целью резервирования или модификации. Чтобы сделать полную копию таблицы в SQL предусмотрен отдельный оператор SELECT INTO. Например, нам нужно создать копию таблицы Sellers, нужно будет прописать запрос следующим образом:

SELECT * INTO Sellers_new FROM Sellers

В отличие от предыдущей конструкции INSERT INTO… SELECT…, когда данные добавляются в существующую таблицу, конструкция SELECT… INTO… FROM… копирует данные в новую таблицу. Также можно сказать, что первая конструкция импортирует данные, а вторая - экспортирует. При использовании конструкции SELECT… INTO… FROM… следует учитывать следующее:

· - можно использовать любые предложения в операторе SELECT, такие как GROUP BY и HAVING

· - для добавления данных из нескольких таблиц можно использовать объединение

· - данные возможно добавить только одну таблицу, независимо от того, из скольких таблиц они были взяты.

Создание таблиц (CREATE TABLE)

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

Создание таблиц

Для создания таблиц программным способом используют оператор CREATE TABLE. Для этого нужно указать следующие данные:

· - имя таблицы, которое указывается после ключевого слова CREATE TABLE

· - имена и определения столбцов таблицы, отделены запятыми

· - в некоторых СУБД также требуется, чтобы было указано местоположение таблицы.

Давайте создадим новую таблицу и назовем ее Customers:

CREATE TABLE Customers (

ID CHAR(10) NOT NULL Primary key,

Custom_name CHAR(25) NOT NULL,

Custom_address CHAR(25) NULL,

Custom_city CHAR(25) NULL,

Custom_Country CHAR(25) NULL,

ArcDate CHAR(25) NOT NULL, DEFAULT NOWO)

Так мы сначала указываем название новой таблицы, затем в скобках перечисляем столбцы, которые будем создавать, причем их названия не могут повторяться в пределах одной таблицы. После названий столбцов указывается тип данных для каждого поля (CHAR (10)), затем отмечаем может ли поле содержать пустые значения (NULL или NOT NULL), а также нужно указать поле, которое будет первичным ключом (Primarykey).

Язык SQL также позволяет определять для каждого поля значение по умолчанию, то есть, если пользователь не укажет значение определенного поля - оно будет автоматически проставлено СУБД. Значение по умолчанию определяется ключевым словом DEFAULT при определении столбцов оператором CREATE TABLE.

Обновление таблиц

Для того чтобы изменить таблицу в SQL используется оператор ALTER TABLE. При использовании данного оператора необходимо ввести следующую информацию:

· - имя таблицы, которую мы хотим изменить

· - перечень изменений, которые мы хотим сделать.

Для примера давайте добавим новую колонку в таблицу Sellers, в которой будем указывать телефон реализатора:

ALTER TABLE Sellers ADD Phone CHAR (20)

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

ALTER TABLE Sellers DROP COLUMN Phone

Удаление таблиц

Удаление таблиц осуществляется с помощью оператора DROP TABLE. Чтобы удалить таблицу Sellers_new, мы можем прописать следующий запрос:

DROP TABLE Sellers_new

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



Источники

1. https://site-do.ru/db/sql1.php

2. https://info-comp.ru/obucheniest/716-create-table-in-ms-sql-server.html

3. https://docs.microsoft.com/ru-ru/sql/t-sql/lesson-1-creating-database-objects? view=sql-server-ver15#create-a-database

Размещено на Allbest.ru