Методичні вказівки до виконання лабораторних робіт з дисципліни «мова sql» для студентів денної форми навчання спеціальності icon

Методичні вказівки до виконання лабораторних робіт з дисципліни «мова sql» для студентів денної форми навчання спеціальності




Скачати 274.77 Kb.
НазваМетодичні вказівки до виконання лабораторних робіт з дисципліни «мова sql» для студентів денної форми навчання спеціальності
Дата конвертації31.03.2013
Розмір274.77 Kb.
ТипМетодичні вказівки


МІНІСТЕРСТВО ОСВІТИ І НАУКИ УКРАЇНИ


ПОЛТАВСЬКИЙ НАЦІОНАЛЬНИЙ ТЕХНІЧНИЙ УНІВЕРСИТЕТ

імені ЮРІЯ КОНДРАТЮКА


КАФЕДРА ЕКОНОМІЧНОЇ КІБЕРНЕТИКИ


МЕТОДИЧНІ ВКАЗІВКИ

ДО ВИКОНАННЯ ЛАБОРАТОРНИХ РОБІТ З ДИСЦИПЛІНИ

«МОВА SQL»


для студентів денної форми навчання спеціальності

8.050.100 Економічна кібернетика


Полтава 2006

Методичні вказівки до виконання лабораторних робіт з дисципліни «Мова SQL» для студентів денної форми навчання спеціальності 8.050.100 Економічна кібернетика / Полтава: Полтавський національний технічний університет імені Юрія Кондратюка, 2004 р. – 18с.


Укладач: канд. техн. наук, доцент ^ Крещенко Л.Ф.

Відповідальний за випуск: завідувач кафедри економічної кібернетики та комп’ютерних технологій Савенко Р.Г., докт. техн. наук, проф.


Рецензент: Кліменко В. І., канд. техн. наук, доцент


^ Затверджено радою університету

Протокол № __ від _________2004 р.


Науковий редактор

Коректор

Технічний редактор

Вступ


Історія досліджень систем баз даних – це за своєю суттю історія розвитку програмного забезпечення, яке на сьогоднішній день досягло виняткової потужності та продуктивності, що зробило великий вплив на економіку. Досягнення в дослідженнях баз даних стало основою фундаментальних розробок комунікаційних систем, транспорту та логістики, фінансового менеджменту, систем із базами знань, а також великої кількості програм у цивільних та військових установах. Вони також стали основою значного прогресу в провідних галузях науки – від інформатики до медицини.

Можна стверджувати, що поява баз даних стало найвагомішим досягненням в галузі програмного забезпечення. Бази даних є основою інформаційних систем, і це докорінно змінило характер роботи багатьох організацій та установ.

^ Мета запропонованих методичних вказівок – на підставі системи зв’язаних кроків у вигляді ланцюжка лабораторних робіт вивчити та практично закріпити основні можливості мови по роботі з базами даних SQL.

У даних методичних вказівках передбачається, що студент знайомий з основними положеннями та концепціями створення проекту бази даних, який складається з трьох основних кроків: концептуального, логічного та фізичного проектування БД. Крім того передбачається, що студент вміє фізично запроектувати базу даних у середовищі MS SQL Server 2000 та вміє користуватися такими його інструментами, як Enterprise Manager та Query Analyzer.

В даних методичних вказівках основну увагу приділено реалізації основних команд мови SQL для роботи з базою даних як з метою створення її об’єктів та модифікації їх структур (підмова DDL) так і з метою маніпулювання даними у таблицях БД (підмова DML).

На протязі виконання кожної лабораторної роботи кожен із студентів працює з власним варіантом учбової бази даних (додаток 1) з метою самостійної реалізації команд мови SQL у середовищі MS SQL Server 2000.

Лабораторная работа №1. (4 часа)

Запросы с использованием единственной таблицы

2.1. Предложение SELECT

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

Предложение SELECT может использоваться как:

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

  • элемент WHERE- или HAVING-условия (сокращенный вариант предложения, называемый "вложенный запрос");

  • фраза выбора в командах CREAT VIEW, DECLARE CURSOR или INSERT;

  • средство присвоения глобальным переменным значений из строк сформированной таблицы (INTO-фраза).

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

  • звездочка (*) для обозначения "все" - употребляется в обычном для программирования смысле, т.е. "все случаи, удовлетворяющие определению";

  • квадратные скобки ([]) – означают, что конструкции, заключенные в эти скобки, являются необязательными (т.е. могут быть опущены);

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

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

  • прямая черта (|) – означает наличие выбора из двух или более возможностей. Например обозначение ASC|DESC указывает, можно выбрать один из терминов ASC или DESC; когда же один из элементов выбора заключен в квадратные скобки, то это означает, что он выбирается по умолчанию (так, [ASC]|DESC означает, что отсутствие всей этой конструкции будет восприниматься как выбор ASC);

  • точка с запятой (;) – завершающий элемент предложений SQL;

  • запятая (,) – используется для разделения элементов списков;

  • пробелы ( ) – могут вводиться для повышения наглядности между любыми синтаксическими конструкциями предложений SQL;

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

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

  • термины таблица, столбец, ... – заменяют (с целью сокращения текста синтаксических конструкций) термины имя_таблицы, имя_столбца, ..., соответственно;

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

Предложение SELECT (выбрать) имеет следующий формат:

подзапрос [UNION [ALL] подзапрос] ...

[ORDER BY {[таблица.]столбец | номер_элемента_SELECT} [[ASC] | DESC]

[,{[таблица.]столбец | номер_элемента_SELECT} [[ASC] | DESC]] ...;

и позволяет объединить (UNION) а затем упорядочить (ORDER BY) результаты выбора данных, полученных с помощью нескольких "подзапросов". При этом упорядочение можно производить в порядке возрастания - ASC (ASCending) или убывания DESC (DESCending), а по умолчанию принимается ASC.

В этом предложении подзапрос позволяет указать условия для выбора нужных данных и (если требуется) их обработки

SELECT

(выбрать) данные из указанных столбцов и (если необходимо) выполнить перед выводом их преобразование в соответствии с указанными выражениями и (или) функциями

FROM

(из) перечисленных таблиц, в которых расположены эти столбцы

WHERE

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

GROUP BY

(группируя по) указанному перечню столбцов с тем, чтобы получить для каждой группы единственное агрегированное значение, используя во фразе SELECT SQL-функции SUM (сумма), COUNT (количество), MIN (минимальное значение), MAX (максимальное значение) или AVG (среднее значение)

^ HAVING

(имея) в результате лишь те группы, которые удовлетворяют указанному перечню условий отбора групп

и имеет формат

SELECT [[ALL] | DISTINCT]{ * | элемент_SELECT [,элемент_SELECT] ...}

FROM {базовая_таблица | представление} [псевдоним]

[,{базовая_таблица | представление} [псевдоним]] ...

[WHERE фраза]

[GROUP BY фраза [HAVING фраза]];

Элемент_SELECT - это одна из следующих конструкций:

[таблица.]* | значение | SQL_функция | системная_переменная

где значение – это:

[таблица.]столбец | (выражение) | константа | переменная

Синтаксис выражений имеет вид

( {[ [+] | - ] {значение | функция_СУБД} [ + | - | * | ** ]}... )

а синтаксис SQL_функций – одна из следующих конструкций:

{SUM|AVG|MIN|MAX|COUNT} ( [[ALL]|DISTINCT][таблица.]столбец )

{SUM|AVG|MIN|MAX|COUNT} ( [ALL] выражение )

COUNT(*)

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

WHERE [NOT] WHERE_условие [[AND|OR][NOT] WHERE_условие]...

где WHERE_условие – одна из следующих конструкций:

значение { = | <> | < | <= | > | >= } { значение | ( подзапрос ) }

значение_1 [NOT] BETWEEN значение_2 AND значение_3

значение [NOT] IN { ( константа [,константа]... ) | ( подзапрос ) }

значение IS [NOT] NULL

[таблица.]столбец [NOT] LIKE 'строка_символов' [ESCAPE 'символ']

EXISTS ( подзапрос )

Кроме традиционных операторов сравнения (= | <> | < | <= | > | >=) в WHERE фразе используются условия BETWEEN (между), LIKE (похоже на), IN (принадлежит), IS NULL (не определено) и EXISTS (существует), которые могут предваряться оператором NOT (не). Критерий отбора строк формируется из одного или нескольких условий, соединенных логическими операторами:

AND

- когда должны удовлетворяться оба разделяемых с помощью AND условия;

OR

- когда должно удовлетворяться одно из разделяемых с помощью OR условий;

AND NOT

- когда должно удовлетворяться первое условие и не должно второе;

OR NOT

- когда или должно удовлетворяться первое условие или не должно удовлетворяться второе,

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

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

Наконец, синтаксис фразы GROUP BY имеет вид

GROUP BY [таблица.]столбец [,[таблица.]столбец] ... [HAVING фраза]

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

С помощью фразы HAVING (синтаксис которой почти не отличается от синтаксиса фразы WHERE)

HAVING [NOT] HAVING_условие [[AND|OR][NOT] HAVING_условие]...

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

значение { = | <> | < | <= | > | >= } { значение | ( подзапрос )

| SQL_функция }

{значение_1 | SQL_функция_1} [NOT] BETWEEN

{значение_2 | SQL_функция_2} AND {значение_3 | SQL_функция_3}

{значение | SQL_функция} [NOT] IN { ( константа [,константа]... )

| ( подзапрос ) }

{значение | SQL_функция} IS [NOT] NULL

[таблица.]столбец [NOT] LIKE 'строка_символов' [ESCAPE 'символ']

EXISTS ( подзапрос )


^ 2.2. Выборка без использования фразы WHERE

2.2.1. Простая выборка

Запрос выдать название, статус и адрес поставщиков

SELECT Название, Статус, Адрес

FROM Поставщики;

дает результат, приведенный на рис. 2.1,а.

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

SELECT ПС, Название, Статус, Город, Адрес, Телефон

FROM Поставщики;

или использовать его более короткую нотацию:

SELECT *

FROM Поставщики;

Здесь "звездочка" (*) служит кратким обозначением всех имен полей в таблице, указанной во фразе FROM. При этом порядок вывода полей соответствует порядку, в котором эти поля определялись при создании таблицы.

2.2.2. Исключение дубликатов

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

SELECT DISTINCT Основа

FROM Блюда;


2.2.3. Выборка вычисляемых значений

Из синтаксиса фразы SELECT (п.2.1) видно, что в ней может содержаться не только перечень столбцов таблицы или символ *, но и выражения.

Например, если нужно получить значение калорийности всех продуктов, то можно учесть, что при окислении 1 г углеводов или белков в организме освобождается в среднем 4.1 ккал, а при окислении 1 г жиров - 9.3 ккал, и выдать запрос:

SELECT Продукт, ((Белки+Углев)*4.1+Жиры*9.3)

FROM Продукты;

Фраза SELECT может включать не только выражения, но и отдельные числовые или текстовые константы. Следует отметить, что текстовые константы должны заключаться в апострофы ('). На рис. 2.2,б приведен результат запроса:

SELECT Продукт, 'Калорий =', ((Белки+Углев)*4.1+Жиры *9.3)

FROM Продукты;

А что произойдет, если какой-либо член выражения не определен, т.е. имеет значение NULL и каким образом появилось такое значение?

Если при загрузке строк таблицы в какой-либо из вводимых строк отсутствует значение для какого-либо столбца, то СУБД введет в такое поле NULL-значение. NULL-значение "придумано" для того, чтобы представить единым образом "неизвестные значения" для любых типов данных. Действительно, так как при вводе данных в столбец или их изменении СУБД запрещает ввод значений не соответствующих описанию данных этого столбца, то, например, нельзя использовать пробел для отсутствующего значения числа. Нельзя для этих целей использовать и ноль: нет месяца или дня недели равного нулю, да и для чисел ноль не может рассматриваться как неизвестное значение в одном месте и как известное - в другом. При выводе же NULL-значения на экран или печатающее устройство его код воспроизводится каким-либо специально заданным символом или набором символов: например, пробелом (если его нельзя перепутать с текстовым значением пробела) или сочетанием -0-.

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

Например, при выполнении запроса

SELECT ПР, Цена, К_во, (Цена * К_во)

FROM Поставки;

и разных "настройках" СУБД могут быть получены разные результаты.

Лабораторная работа №2. (2 часа)

^ 2.3. Выборка c использованием фразы WHERE

2.3.1. Использование операторов сравнения

В синтаксисе фразы WHERE (п.2.1) показано, что для отбора нужных строк таблицы можно использовать операторы сравнения = (равно), <> (не равно), < (меньше), <= (меньше или равно), > (больше), >= (больше или равно), которые могут предваряться оператором NOT, создавая, например, отношения "не меньше" и "не больше".

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

SELECT Продукт, Белки, Жиры, Углев, K, Ca, Na, B2, PP, C

FROM Продукты

WHERE Углев = 0;

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

SELECT Продукт, Белки, Жиры, Углев, K, Ca, Na, B2, PP, C

FROM Продукты

WHERE Углев = 0 AND Na = 0;

Добавим к этому запросу еще одно условие

SELECT Продукт, Белки, Жиры, Углев, K, Ca, Na, B2, PP, C

FROM Продукты

WHERE Углев = 0 AND Na = 0 AND Продукт <> 'Судак';

и получим на экране сообщение "No rows exist or satisfy the specified clause" или аналогичное (в зависимости от вкусов разработчиков разных СУБД), информирующее об отсутствии строк, удовлетворяющих заданному(ым) условию(ям).

2.3.2. Использование BETWEEN

С помощью BETWEEN ... AND ... (находится в интервале от ... до ...) можно отобрать строки, в которых значение какого-либо столбца находятся в заданном диапазоне.

Например, выдать перечень продуктов, в которых значение содержания белка находится в диапазоне от 10 до 50:


SELECT Продукт, Белки

FROM Продукты

WHERE Белки BETWEEN 10 AND 50;

Можно задать и NOT BETWEEN (не принадлежит диапазону между), например:

SELECT Продукт, Белки, Жиры

FROM Продукты

WHERE Белки NOT BETWEEN 10 AND 50

AND Жиры > 100;

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

Для примера воспользуемся таблицей "минимальных окладов" (табл. 2.1), величина которых непосредственно связана со студенческой стипендией. В этой таблице для текущего значения минимального оклада установлена запредельная дата окончания 9 сентября 9999 года.

Если, например, потребовалось узнать, какие изменения минимальных окладов производились в 1993/94 учебном году, то можно выдать запрос

SELECT Начало, Миноклад

FROM Миноклады

WHERE Начало BETWEEN '1-9-1993' AND '31-8-1994'

Отметим, что при формировании запросов значения дат следует заключать в апострофы, чтобы СУБД не путала их с выражениями и не пыталась вычитать из 31 значение 8, а затем 1994.

Для выявления всех значений минимальных окладов, которые существовали в 1993/94 учебном году, можно сформировать запрос

SELECT *

FROM Миноклады

WHERE Начало BETWEEN '1-9-1993' AND '31-8-1994'

OR Конец BETWEEN '1-9-1993' AND '31-8-1994'

Наконец, для получения минимального оклада на 15-5-1994:




Результат:

SELECT Миноклад

FROM Миноклады

WHERE '15-05-1994' BETWEEN Начало AND Конец







Миноклад




14620





^ 2.4. Выборка с упорядочением

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

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

SELECT Продукт, Белки, Жиры, Углев

FROM Продукты

ORDER BY Белки DESC;

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

Например, выдать содержимое таблицы Блюда, отсортировав ее строки по видам блюд и основе:

SELECT *

FROM Блюда

ORDER BY В Основа

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

Например, запрос

SELECT Продукт, ((Белки+Углев)*4.1+Жиры*9.3)

FROM Продукты

^ ORDER BY 2;

позволит получить список продуктов, показанный на рис.2.2,в – переупорядоченный по возрастанию значений калорийности список рис.2.2,а.

Лабораторная работа №3 (2 часа)

^ 2.5. Агрегирование данных

2.5.1 SQL-функции

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

COUNT

- число значений в столбце,

SUM

- сумма значений в столбце,

AVG

- среднее значение в столбце,

MAX

- самое большое значение в столбце,

MIN

- самое малое значение в столбце.

Для функций SUM и AVG рассматриваемый столбец должен содержать числовые значения.

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

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

2.5.2. Функции без использования фразы GROUP BY

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

Например, выдать данные о массе лука (ПР=10), проданного поставщиками, и указать количество этих поставщиков:




Результат:

SELECT SUM(К_во),COUNT(К_во)

FROM Поставки

^ WHERE ПР = 10;







SUM(К_во)

COUNT(К_во)

220

2

Если бы для вывода в результат еще и номера продукта был сформирован запрос

SELECT ПР,SUM(К_во),COUNT(К_во)

FROM Поставки

^ WHERE ПР = 10;

то было бы получено сообщение об ошибке. Это связано с тем, что SQL-функция создает единственное значение из множества значений столбца-аргумента, а для "свободного" столбца должно быть выдано все множество его значений. Без специального указания (оно задается фразой GROUP BY) SQL не будет выяснять, одинаковы значения этого множества (как в данном примере, где ПР=10) или различны (как было бы при отсутствии WHERE фразы). Поэтому подобный запрос отвергается системой.

Правда, никто не запрещает дать запрос

SELECT 'Кол-во лука =',SUM(К_во),COUNT(К_во)

FROM Поставки

^ WHERE ПР = 10;

Результат:

'Кол-во лука ='

SUM(К_во)

COUNT(К_во)

Кол-во лука =

220

2

Отметим также, что в столбце-аргументе перед применением любой функции, кроме COUNT(*), исключаются все неопределенные значения. Если оказывается, что аргумент - пустое множество, функция COUNT принимает значение 0, а остальные - NULL.

Например, для получения суммы цен, средней цены, количества поставляемых продуктов и количества разных цен продуктов, проданных коопторгом УРОЖАЙ (ПС=5), а также для получения количества продуктов, которые могут поставляться этим коопторгом, можно дать запрос

SELECT SUM(Цена),AVG(Цена),COUNT(Цена),

COUNT(DISTINCT Цена),COUNT(*)

FROM Поставки

^ WHERE ПС = 5;

и получить

SUM(Цена)

AVG(Цена)

COUNT(Цена)

COUNT(DISTINCT Цена)

^ COUNT (*)

6.2

1.24

5

4

7

В другом примере, где надо узнать "Сколько поставлено моркови и сколько поставщиков ее поставляют?":

SELECT SUM(К_во),COUNT(К_во)

FROM Поставки

WHER ПР = 2;

будет получен ответ:

SUM(К_во)

COUNT (К_во)

-0-

0

Наконец, попробуем получить сумму массы поставленного лука с его средней ценой ("Сапоги с яичницей"):




Результат:

SELECT (SUM(К_во) +AVG(Цена))

FROM Поставки

^ WHERE ПР = 10;




SUM(К_во)+AVG(Цена)

220.6


Лабораторная работа №4.

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

^ 3.1. О средствах одновременной работы с множеством таблиц

Затрагивая вопросы проектирования баз данных [2], мы выяснили, что базы данных - это множество взаимосвязанных сущностей или отношений (таблиц) в терминологии реляционных СУБД. При проектировании стремятся создавать таблицы, в каждой из которых содержалась бы информация об одном и только об одном типе сущностей. Это облегчает модификацию базы данных и поддержание ее целостности. Но такой подход тяжело усваивается начинающими проектантами, которые пытаются привязать проект к будущим приложениям и так организовать таблицы, чтобы в каждой из них хранилось все необходимое для реализации возможных запросов. Типичен вопрос: как же получить сведения о том, где купить продукты для приготовления того или иного блюда и определить его калорийность и стоимость, если нужные данные "рассыпаны" по семи различным таблицам? Не лучше ли иметь одну большую таблицу, содержащую все сведения базы данных ПАНСИОН ?

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

SELECT Продукт, Цена, Название, Статус

FROM Продукты, Состав, Блюда, Поставки, Поставщики

WHERE Продукты.ПР = Состав.ПР

AND Состав.БЛ = Блюда.БЛ

AND Поставки.ПР = Состав.ПР

AND Поставки.ПС = Поставщики.ПС

AND Блюдо = 'Сырники'

AND Цена IS NOT NULL;

Продукт

Цена

Название

Статус

Яйца

1.8

ПОРТОС

кооператив

Яйца

2.

КОРЮШКА

кооператив

Сметана

3.6

ПОРТОС

кооператив

Сметана

2.2

ОГУРЕЧИК

Ферма

Творог

1.

ОГУРЕЧИК

Ферма

Мука

0.5

УРОЖАЙ

Коопторг

Сахар

0.94

ТУЛЬСКИЙ

универсам

Сахар

1.

УРОЖАЙ

Коопторг

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

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

Очевидно, что с помощью соединения несложно сформировать запрос на обработку данных из нескольких таблиц. Кроме того, в такой запрос можно включить любые части предложения SELECT, рассмотренные в главе 2 (выражения с использованием функций, группирование с отбором указанных групп и упорядочением полученного результата). Следовательно, соединения позволяют обрабатывать множество взаимосвязанных таблиц как единую таблицу, в которой перемешана информация о многих типах сущностей. Поэтому начинающий проектант базы данных может спокойно создавать маленькие нормализованные таблицы, так как он всегда может получить из них любую "большую" таблицу.

Кроме механизма соединений в SQL есть механизм вложенных подзапросов, позволяющий объединить несколько простых запросов в едином предложении SELECT. Иными словами, вложенный подзапрос - это уже знакомый нам подзапрос (с небольшими огра-ничениями), который вложен в WHERE фразу другого вложенного подзапроса или WHERE фразу основного запроса.

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

SELECT Продукт, Цена, Название, Статус

FROM Продукты, Состав, Блюда, Поставки, Поставщики

WHERE Продукты.ПР = Состав.ПР

AND Состав.БЛ = Блюда.БЛ

AND Поставки.ПР = Состав.ПР

AND Поставки.ПС = Поставщики.ПС

AND Блюдо = 'Сырники'

AND Цена = (SELECT MIN(Цена)

FROM Поставки X

WHERE X.ПР = Поставки.ПР );

Результат запроса имеет вид

Продукт

Цена

Название

Статус

Яйца

1.8

ПОРТОС

кооператив

Сахар

0.94

ТУЛЬСКИЙ

универсам

Мука

0.5

УРОЖАЙ

Коопторг

Сметана

2.2

ОГУРЕЧИК

Ферма

Творог

1.

ОГУРЕЧИК

Ферма

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

^ 3.2. Запросы, использующие соединения

3.2.1. Декартово произведение таблиц

В литературе [2] показано, что соединения - это подмножества декартова произведения. Так как декартово произведение n таблиц - это таблица, содержащая все возможные строки r, такие, что r является сцеплением какой-либо строки из первой таблицы, строки из второй таблицы, ... и строки из n-й таблицы (а мы уже научились выделять с помощью SELECT любое подмножество реляционной таблицы), то осталось лишь выяснить, можно ли с помощью SELECT получить декартово произведение. Для получения декартова произведения нескольких таблиц надо указать во фразе FROM перечень перемножаемых таблиц, а во фразе SELECT – все их столбцы.

Так, для получения декартова произведения Вид_блюд и Трапезы надо выдать запрос

SELECT Вид_блюд.*, Трапезы.*

FROM Вид_блюд, Трапезы;

Получим таблицу, содержащую 5 х 3 = 15 строк:

В другом примере, где перемножаются таблицы Меню, Трапезы, Вид_блюд, Блюда:

SELECT Меню.*, Трапезы.*, Вид_блюд.*, Блюда.*

FROM Меню, Трапезы, Вид_блюд, Блюда;

образуется таблица (рис 3.1), содержащая 21 х 3 х 5 х 33 = 10395 строк.

Из первых 39 строк этой таблицы лишь две актуальных (отмечены "*"): в них совпадают номера блюд таблиц Меню и Блюда. В остальных – полная чепуха: к закускам отнесены супы и напитки, на завтрак предлагается незапланированный суп и т.д.

3.2.2. Соединение таблиц с дополнительным условием

При формировании соединения создается рабочая таблица, к которой применимы все операции, рассмотренные в главе 2: отбор нужных строк соединения (WHERE фраза), упорядочение получаемого результата (ORDER BY фраза) и агрегатирование данных (SQL-функции и GROUP BY фраза).

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

SELECT Вид, Блюдо, Основа, Выход, 'Номер -', БЛ

FROM Меню, Трапезы, Вид_блюд, Блюда

WHERE Меню.Т = Трапезы.Т

AND Меню.В = Вид_блюд.В

AND Меню.БЛ = Блюда.БЛ

AND Трапеза = ’Завтрак’;

В п.3.6 можно познакомиться с достаточно полным примером соединения таблиц с различными дополнительными фразами.
^

Лабораторная работа № 5

3.3. Вложенные подзапросы

3.3.1. Виды вложенных подзапросов


Вложенный подзапрос - это подзапрос, заключенный в круглые скобки и вложенный в WHERE (HAVING) фразу предложения SELECT или других предложений, использующих WHERE фразу. Вложенный подзапрос может содержать в своей WHERE (HAVING) фразе другой вложенный подзапрос и т.д. Нетрудно догадаться, что вложенный подзапрос создан для того, чтобы при отборе строк таблицы, сформированной основным запросом, можно было использовать данные из других таблиц (например, при отборе блюд для меню использовать данные о наличии продуктов в кладовой пансионата).

Существуют простые и коррелированные вложенные подзапросы. Они включаются в WHERE (HAVING) фразу с помощью условий IN, EXISTS или одного из условий сравнения ( = | <> | < | <= | > | >= ). Простые вложенные подзапросы обрабатываютя системой "снизу вверх". Первым обрабатывается вложенный подзапрос самого нижнего уровня. Множество значений, полученное в результате его выполнения, используется при реализации подзапроса более высокого уровня и т.д.

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

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

3.3.2. Простые вложенные подзапросы

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

SELECT Название, Статус

FROM Поставщики

^ WHERE ПС IN

(SELECT ПС

FROM Поставки

WHERE ПР = 11);

Как уже отмечалось в п.3.3.1, при обработке полного запроса система выполняет прежде всего вложенный подзапрос. Этот подзапрос выдает множество номеров поставщиков, которые поставляют продукт с кодом ПР = 11, а именно множество (1, 5, 6, 8). Поэтому первоначальный запрос эквивалентен такому простому запросу:

SELECT Название, Статус

FROM Поставщики

^ WHERE ПС IN (1, 5, 6, 8);

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

SELECT Название, Статус

FROM Поставщики

^ WHERE ПС IN

( SELECT ПС

FROM Поставки

WHERE ПР IN

( SELECT ПР

FROM Продукты

WHERE Продукт = 'Помидоры' ));

В данном случае результатом самого внутреннего подзапроса является только одно значение (11). Как уже было показано выше, подзапрос следующего уровня в свою очередь дает в результате множество (1, 5, 6, 8). Последний, самый внешний SELECT, вычисляет приведенный выше окончательный результат. Вообще допускается любая глубина вложенности подзапросов.

Тот же результат можно получить с помощью соединения

SELECT Название, Статус

FROM Поставщики, Поставки, Продукты

WHERE Поставщики.ПС = Поставки.ПС

AND Поставки.ПР = Продукты.ПР

AND Продукт = 'Помидоры';

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

3.3.3 Функции в подзапросе

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

SELECT Продукт, Цена, Название, Статус

FROM Продукты, Состав, Блюда, Поставки, Поставщики

WHERE Продукты.ПР = Состав.ПР

AND Состав.БЛ = Блюда.БЛ

AND Поставки.ПР = Состав.ПР

AND Поставки.ПС = Поставщики.ПС

AND Блюдо = 'Сырники'

AND Цена = ( SELECT MIN(Цена)

FROM Поставки X

WHERE X.ПР = Поставки.ПР );

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

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

  1. Выдать названия всех мясных блюд.

  2. Выдать количество всех блюд, в состав которых входят помидоры.

  3. Выдать блюда, продукты для которых поставляются агрофирмой ЛЕТО.

Лабораторная работа №6

^ 3.4. Объединение (UNION)

В литературе [2] рассматривалась реляционная операция "Объединение", позволяющая получить отношение, состоящее из всех строк, входящих в одно или оба объединяемых отношения. Но при этом исходные отношения или их объединяемые проекции должны быть совместимыми по объединению. Для SQL это означает, что две таблицы можно объединять тогда и только тогда, когда:

  1. они имеют одинаковое число столбцов, например, m;

  2. для всех i (i = 1, 2, ..., m) i-й столбец первой таблицы и i-й столбец второй таблицы имеют в точности одинаковый тип данных.

Например, выдать названия продуктов, в которых нет жиров, либо входящих в состав блюда с кодом БЛ = 1:

SELECT Продукт

FROM Продукты

WHERE Жиры = 0

UNION

SELECT Продукт

FROM Соста

^ WHERE БЛ = 1

Из этого простого примера видно, что избыточные дубликаты всегда исключаются из результата UNION. Поэтому, хотя в рассматриваемом примере Помидоры, Зелень и Яблоки выбираются обеими из двух составляющих предложения SELECT, в окончательном результате они появляются только один раз.

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

UNION

SELECT Продукт

FROM Продукты

WHERE Ca < 250

позволяющую добавить к списку продуктов Масло, Рис, Мука и Кофе. Однако тот же результат можно получить простым изменением фразы WHERE первой части исходного запроса

WHERE Жиры = 0 OR Ca < 250

^

3.5. Реализация операций реляционной алгебры предложением SELECT


С помощью предложения SELECT можно реализовать любую операцию реляционной алгебры [2].

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

SELECT *

FROM Блюда

WHER Основа = 'Молоко'

AND Выход > 200;

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

SELECT DISTINCT Блюдо, Выход, Основа

FROM Блюда;

Объединение двух таблиц содержит те строки, которые есть либо в первой, либо во второй, либо в обеих таблицах. Пример:

SELECT Блюдо, Основа, Выход

FROM Блюда

WHER Основа = 'Овощи'

UNION

SELECT Блюдо, Основа, Выход

FROM Блюда

WHER В = 'Г';

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

^ SELECT БЛ

FROM Состав

WHERE БЛ IN

( SELECT БЛ

FROM Меню);

Разность двух таблиц содержит только те строки, которые есть в первой, но отсутствуют во второй. Пример:

^ SELECT БЛ

FROM Состав

WHERE БЛ NOT IN

( SELECT БЛ

FROM Меню);

Декартово произведение таблиц и различные виды соединений были подробно рассмотрены в п. 3.2.1-3.2.6.

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

^

Список літератури



[1]. Дейт К.Дж. Введение в системы баз данных. 6-е издание. Диалектика. Киев – Москва. 1998 г. 784 с.


[2]. Хансен Г., Хансен Дж. Базы данных: разработка и управление. Бином. Москва. 1999 г. Пер. с англ. 700 с.


[3]. Коннолли Т., Бегг К., Страчан А. Базы данных: проектирование, реализация и сопровождение. Теория и практика. 2-е издание. Вильямс. Москва-Санкт-Петербург-Киев. 2000 г. 1111 с.


[4]. Кириллов В.В. Основы проектирования реляционных баз данных. Учебное пособие. Санкт-Петербургский Государственный институт точной механики и оптики (технический университет). Кафедра вычислительной техники. - http://www.cs.ifmo.ru


[5]. С.Д. Кузнецов Основы современных баз данных. Информационно-аналитические материалы. - http://www.citmgu.ru/

Зміст







Схожі:

Методичні вказівки до виконання лабораторних робіт з дисципліни «мова sql» для студентів денної форми навчання спеціальності iconМетодичні вказівки до лабораторних занять для студентів спеціальності «Комп’ютерні системи та мережі» денної та заочної форми навчання
Периферійні пристої. Методичні вказівки до лабораторних робіт для студентів спеціальності «Комп’ютерні системи та мережі» денної...
Методичні вказівки до виконання лабораторних робіт з дисципліни «мова sql» для студентів денної форми навчання спеціальності iconМетодичні вказівки до лабораторних занять для студентів спеціальності ксм денної та заочної форм навчання
Операційні системи. Методичні вказівки до лабораторних занять для студентів спеціальності ксм денної та заочної форми навчання. /...
Методичні вказівки до виконання лабораторних робіт з дисципліни «мова sql» для студентів денної форми навчання спеціальності iconМетодичні вказівки до лабораторних робіт для студентів денної форми навчання факультету інформатики та обчислювальної техніки Затверджено Методичною радою нтуу „кпі
Загальна фізика. Квантова фізика : методичні вказівки до виконання лабораторних робіт для студентів денної форми навчання факультету...
Методичні вказівки до виконання лабораторних робіт з дисципліни «мова sql» для студентів денної форми навчання спеціальності iconМетодичні вказівки до лабораторних занять (частина 1) Microsoft Word для студентів спеціальностей
Дані методичні вказівки включають 8 лабораторних робіт, по вивченню текстового редактора Word, тематика яких охоплює частину курсу...
Методичні вказівки до виконання лабораторних робіт з дисципліни «мова sql» для студентів денної форми навчання спеціальності iconМетодичні вказівки до лабораторних занять для студентів спеціальності ксм денної та заочної форм навчання
Операційні системи. Методичні вказівки до лабораторних занять для студентів спеціальності ксм денної та заочної форми навчання
Методичні вказівки до виконання лабораторних робіт з дисципліни «мова sql» для студентів денної форми навчання спеціальності iconМетодичні вказівки щодо виконання контрольних робіт з навчальної дисципліни "фінансове право " для студентів денної формИ навчання
Методичні вказівки щодо виконання контрольних робіт з навчальної дисципліни “Фінансове право ” для студентів денної форми навчання...
Методичні вказівки до виконання лабораторних робіт з дисципліни «мова sql» для студентів денної форми навчання спеціальності iconМетодичні вказівки до виконання самостійної роботи студентів спеціальності ксм денної та заочної форми навчання
Операційні системи. Методичні вказівки до виконання самостійної роботи студентів спеціальності ксм денної та заочної форми навчання....
Методичні вказівки до виконання лабораторних робіт з дисципліни «мова sql» для студентів денної форми навчання спеціальності iconМетодичні вказівки щодо виконання контрольних робіт з навчальної дисципліни «іноземна мова(англійська)» для студентів заочної форми навчання
Методичні рекомендації щодо виконання контрольних робіт з навчальної дисципліни «Англійська мова» для студентів заочної форми навчання...
Методичні вказівки до виконання лабораторних робіт з дисципліни «мова sql» для студентів денної форми навчання спеціальності iconМетодичні вказівки до курсової роботи з дисципліни "Програмування" для студентів спеціальності 091501
Методичні вказівки до курсової роботи з дисципліни "Програмування" для студентів денної форми навчання спеціальності 091501. Укл.:...
Методичні вказівки до виконання лабораторних робіт з дисципліни «мова sql» для студентів денної форми навчання спеціальності iconМетодичні вказівки до виконання економічної частини дипломних проектів, робіт для студентів денної та заочної форми навчання
Методичні рекомендації до виконання економічної частини дипломних проектів, робіт для студентів денної та заочної форми навчання...
Додайте кнопку на своєму сайті:
Документи


База даних захищена авторським правом ©te.zavantag.com 2000-2017
При копіюванні матеріалу обов'язкове зазначення активного посилання відкритою для індексації.
звернутися до адміністрації
Документи