Раздел 3. Языки баз данных

     Неотъемлемая  и важнейшая часть любой системы, применяющей базы данных, языковые средства, обеспечивающие возможность  доступа и действий над данными, определения их структур, способов использования и интерпретации. Язык SQL появился в 1970-е годы как одно из таких средств. Его прототип был разработан фирмой IBM и известен под названием SEQUEL (Structured English Query Language). SQL вобрал в себя достоинства реляционной модели, в частности достоинства лежащего в ее основе математического аппарата реляционной алгебры и реляционного исчисления, используя при этом сравнительно небольшое число операторов и относительно простой синтаксис. Благодаря своим качествам язык SQL стал вначале "де-факто", а затем и официально утвержденным в качестве стандарта  языком работы с реляционными базами данных. Этот стандарт поддерживается всеми ведущими мировыми фирмами, действующими в сфере технологий баз данных. Использование выразительного и эффективного стандартного языка позволило обеспечить независимость разрабатываемых прикладных программных систем от конкретного типа используемой СУБД.

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

     Язык  работы с данными, который способна воспринимать СУБД, состоит из двух частей: язык определения данных (DDL) и язык манипулирования данными (DML).

     DDL используется для определения схемы БД, а язык DML  - для чтения и обновления данных, хранимых в базе. Эти языки называются подъязыками данных, т.к. в них нет конструкций для выполнения вычислительных операций, условных операторов и операторов цикла.

Во многих СУБД предусмотрена возможность включения операторов подъязыка данных в программы, написанные на языках программирования высокого уровня. Кроме того предусмотрены средства интерактивного выполнения операторов, вводимых пользователем непосредственно с компьютера.

Унификация полных языков современных профессиональных СУБД достигается за счет внедрения  объектно-ориентированного языка четвертого поколения 4GL. Последний позволяет  организовывать циклы, условные предложения, меню, экранные формы, сложные запросы к базам данных с интерфейсом, ориентированным как на алфавитно-цифровые терминалы, так и на оконный графический интерфейс (X-Windows, MS-Windows).

     Непроцедурный язык SQL (Structured Query Language - структурированный  язык запросов) ориентирован на операции с данными, представленными в виде логически взаимосвязанных совокупностей таблиц.

Реализация в SQL концепции операций, ориентированных  на табличное представление данных, позволило создать компактный язык с небольшим (менее 30) набором предложений.

В нем существуют:

• предложения определения данных (определение баз данных, а также определение и уничтожение таблиц и индексов);
• запросы на выбор данных (предложение SELECT);
• предложения модификации данных (добавление, удаление и изменение данных);
• предложения управления данными (предоставление и отмена привилегий на доступ к данным, управление транзакциями и другие).

 Кроме того, он предоставляет возможность  выполнять в этих предложениях:

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

 
 

Создание SQL-запросов

 SQL-запрос – это структурированный язык выбора данных из одной или нескольких таблиц. 

 

Команда SELECT 

Для формирования запроса используется команда SELECT.

Обобщенный синтаксис:

SELECT [DISTINCT] Список Выбираемых Полей

FROM Список Таблиц

[WHERE Условие Выборки]

[GROUP BY Условие Группировки]

[HAVING Условие ограничения, накладываемое на  группу]

[ORDER BY Условие Упорядочивания] 

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

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

Команда INTO направляет запрос в новую таблицу.

В списке выбираемых полей необходимо перечислить поля разделять запятой. В конце запроса ставится точка с запятой. При выборке всех полей ставится *. 

Дано:

Таблица "Pokup" (Покупатели), с полями:

Фамилия   Cfam

Код товара   Nkod

Вид оплаты   Cvid

Стоимость товара  Ntov

Стоимость доставки  Ndos

Дата поступления  заявки Dpos

Дата и время  выполнения Tvip

Pokup

 

Таблица "Tovary" (Товары):

Код товара   Nkod

Наименование  товара Cnaim

Цена    Nzena

Сорт    Nsort

Tovary

 
 

Импортировать заданные таблицы в новую базу данных.

 

Создать запросы к таблицам Pokup и Tovary,  используя команду SELECT 

1. Выбрать поля "Фамилия" и "Дата поступления  заявки" из таблицы "Pokup".

               SELECT Cfam, Dpos

               FROM Pokup; 

2. Выбрать все  поля таблицы "Pokup".

               SELECT Pokup.*

               FROM Pokup; 

Итоговые запросы

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

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

SUM()  вычисляет сумму всех значений,  содержащихся в столбце;

MIN()  Минимальное значение;

MAX() Максимальное значение;

AVG()  Среднее значение;

SUM()  Сумма;

COUNT() подсчитывает количество значений, содержащихся в столбце;

COUNT(*) подсчитывает количество строк в таблице результатов запроса. 

Новому полю, являющемуся выражением от других полей  присваивается имя:

AS имя поля.  Ключевое слово AS можно использовать для присваивания псевдонимов столбцам. 

3. Выбрать поля "Фамилия", "КодТовара", рассчитать сумму стоимости товара и доставки, название поля СуммарнаяЦена.

                  SELECT Pokup.Cfam, Pokup.Nkod, Ntov+Ndos AS s_m

                  FROM Pokup; 

3_1. Выбрать поля "Cnaim", "Nzena" из 2 таб, вывести новую цену , увеличенную в 3 раза, название поля  «НоваяЦена».

               SELECT tovary.Cnaim, tovary.Nzena, nzena*3 AS НоваяЦена

               FROM tovary; 

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

SELECT COUNT(cfam) AS kol_vo, AVG(ntov) AS sred, max(ntov) AS m_x, min(ndos) AS m_n

         FROM Pokup; 

 Применение  ключевого слова DISTINCT, записанного перед аргументом агрегатной функции к столбцу, приводит к удалению из него повторяющихся значений. 

Отбор строк с использованием условий поиска

5. Выберем из таблицы "Pokup" поля "Фамилия покупателя", "Код товара" и "Стоимость товара" для всех товаров с кодом больше 400 (условие выборки)

                  SELECT cfam, nkod, ntov

                  FROM Pokup

                  WHERE nkod>400; 

Условие может строиться с использованием логической операции AND (И), OR (ИЛИ), NOT (НЕ), включать в себя операции сравнения: <, <=, >, >=, =. 

6. Выберем из таблицы "Pokup" поля "Фамилия  покупателя", "Код товара" и "Стоимость товара" для всех товаров с кодом больше 400, но меньше 700.

            SELECT cfam, nkod, ntov

            FROM Pokup

            WHERE nkod>400 And nkod<700; 

6_1. Выберем из  «Pokup» всех кроме Гребенева

               SELECT cfam

               FROM Pokup

               WHERE NOT Cfam="Гребенев А. Н."; 
 

   В условиях выборки можно использовать ключевые слова: BETWEEN, IN, LIKE, IS NULL

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

NOT BETWEEN – значения не ходят в диапазон.

IN – задает список значений, с которым сравниваются отбираемые данные.

NOT IN – отбираются значения не входящие в список

LIKE – проверка на соответствие шаблону значений столбца. Шаблон  - строка символ, в которую входят  подстановочные знаки (* – один произвольный символ; ?-любой одиночный символ, #- любое одиночное число).

NOT LIKE – выбирает строки, которые не соответствуют шаблону.

NULL – пустое поле, проверка на содержание в столбце значения NULL 

При выборке  пустых полей или непустых используется  условие IS NULL или IS NOT NULL 
 

7. Выбрать из  «Pokup» поля cfam, nkod, ntov, при условии,  что код товара находится между 300 и 400.

                  SELECT cfam, nkod, ntov

                  FROM Pokup

                  WHERE nkod BETWEEN 300 AND 400; 

7_1. Выбрать все поля из таблицы "Pokup", при условии, что код товара должен быть равен 310, 600 или 910.

                  SELECT *

                  FROM Pokup

                  WHERE Pokup.Nkod IN (310,360,910);

7_2.  С помощью проверки NOT IN   получить значения данных, не являющихся членами заданного списка.

                  SELECT *

                  FROM Pokup

                  WHERE Pokup.Nkod NOT IN (310,360,910); 

 7_3. Выбрать все поля из таблицы "Pokup", при условии, что фамилия клиента должна начинаться с буквы "С".

                   SELECT *

                   FROM Pokup

                   WHERE cfam LIKE 'С*'; 

Запросы с группировкой

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

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

                  SELECT cvid, SUM(ntov+ndos)

                  FROM Pokup

                  GROUP BY cvid; 

9_1. Можно использовать группировку по нескольким полям. Выбрать из таблицы "Pokup" поля "Вид оплаты" и "Код товара", сгруппировать по этим полям, рассчитать сумму стоимости товара и доставки для каждого товара и вида оплаты.

                  SELECT cvid, nkod, SUM(ntov+ndos)

                  FROM Pokup

                  GROUP BY Pokup.Cvid, Pokup.Nkod; 

9_2. применение агрегатной функции к группе

                  SELECT cvid, SUM(ntov)

                  FROM Pokup

                  GROUP BY Pokup.Cvid; 
 

Условие поиска, используемое в предложении  HAVING накладываемое на группу, применяется не к отдельным строкам, а к группе в целом. В условие поиска может входить:

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

На практике HAVING должно включать как минимум одну агрегатную функцию. 

9. В предыдущем запросе наложить на группу  суммы  условие  > 500 , использовать HAVING.

                  SELECT Pokup.Cvid, Sum(Ntov+Ndos) AS Цена, Nkod

                  FROM Pokup

                  GROUP BY Pokup.Cvid, Nkod

                  HAVING (Sum([Ntov]+[Ndos])>500); 

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

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

Использование ключевого поля TOP  n , n – числовое значение, позволяет отобрать только n  первых строк,  причем набор строк зависит от порядка сортировки.

10. Выбрать все поля из таблицы "Tovary" в порядке возрастания цены

                  SELECT *

                  FROM Tovary

                  ORDER BY nzena; 

10_1. Выбрать все поля из таблицы "Tovary" в порядке убывания сорта.

                  SELECT *

                  FROM Tovary

                  ORDER BY nsort DESC;

10_2. и 10_3. Выбрать 5 первых строк из таблицы Tovary, отсортировать по убыванию Nkod и выбрать опять 5 первых строк. 

            SELECT TOP 5 *

            FROM Tovary; 

                                    SELECT TOP 5 *

                                    FROM Tovary

                                    ORDER BY Nkod; 

При выборке  из двух таблиц необходимо указывать  список таблиц через запятую и  задать условие связи таблиц  по совпадению одноименных полей (WHERE Pokup.nkod = Tovary.nkod) 
 

Запросы на создание таблицы, на добавление, модификацию и удаление строк  

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

SELECT Список Выбираемых Полей INTO новая таблица

FROM Список Таблиц 

   1. Выбрать поля "Фамилия", "Дата поступления заявки", "Стоимость товара" и направить результат в новую таблицу “tab_new”. 

               SELECT cfam, dpos, ntov INTO tab_new

               FROM Pokup; 

Операторы добавления, изменения и удаления данных: 

INSERT  - добавляет новые строки в таблицы

UPDATE – изменяет в таблице существующие строк

DELITE – удаляет строки из таблиц  

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

VALUES –  значения  полей  

Формат команды INSERT:

INSERT INTO Имя Таблицы (ИмяПоля1, ИмяПоля2, …)

VALUES (ЗначениеПоля1, ЗначениеПоля2, …) 

2. Добавим в таблицу Pokup" новую запись содержащую фамилию (Кондратенко) и  код товара (321).

                  INSERT INTO Pokup ( cfam, nkod )

                  VALUES ('Кондратенко  А. В.', 321); 

3. Добавим в таблицу "Pokup" запись, используя запрос с параметром (можно не все поля)

                  INSERT INTO Pokup ( cfam, nkod, cvid, dpos )

                  VALUES ([cfam],[nkod],[cvid],[dpos]); 

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

Формат команды UPDATE:

UPDATE Имя Таблицы

SET ИмяПоля1=Новое ЗначениеПоля1, ИмяПоля2= Новое ЗначениеПоля2, …

WHERE Условие Отбора Записей

В качестве значения поля можно использовать любое выражение.

4.  Изменим инициалы Гребнева, при условии совпадения кода товара

                  UPDATE tab_new SET cfam = 'Гребенев Н.А.'

                  WHERE ntov=389;

5. Команда без условия отбора изменит значение всех записей таблицы, в таблице Pokup изменить значения поля Ndos

                  UPDATE Pokup SET Ndos = '45'; 

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

Формат команды:

DELETE FROM Имя Таблицы WHERE Условие Отбора 

6. Удалим из таблицы все записи, которые соответствуют условию ntov>500

                  DELETE *

                  FROM Pokup

                  WHERE ntov>500; 

Многотабличные  запросы. Выборка из нескольких таблиц  

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

Соединение отношений  имеют  два типа синтаксиса:

• Использование предложений FROM, WHERE
• При помощи ключевого слова  JOIN

 

Синтаксис операции соединения FROM, WHERE:

SELECT [DISTINCT] Список Выбираемых таблиц и Полей

FROM Список таблиц

WHERE имя таблицы1. имя столбца = имя таблицы2. имя столбца 

Где имя столбца является Первичным ключом  таблицы1 и внешним ключом таблицы2

Выберем из таблицы "Pokup" фамилию покупателя и стоимость  товара, а из таблицы "Tovary" –  наименование товара.