Выборка с использованием оператора BETWEEN

Методические указания

к практическим занятиям и самостоятельной работе

по дисциплине

«Проектирование баз данных»

для студентов

направления подготовки:

Прикладная информатика»

Профиль "Прикладная информатика в экономике"

Квалификация: бакалавр

Ставрополь, 2016

Методические указания, составлены в соответствии Федеральным государственным образовательным стандартом высшего образования для студентов направления: 09.03.03 «Прикладная информатика»

Квалификация: бакалавр

.

Составитель: В.И.Дроздова, д.ф.-м.н., профессор

Рецензент: В.П.Мочалов, д.т.н., профессор

Содержание

Занятие 1. Безусловная выборка. Синтаксис запроса. 4

Занятие 2. Расширенные SQL-запросы. Выборка с условием. Выборка вычисляемых значений 8

Выборка с условием.. 10

Выборка с использованием оператора BETWEEN.. 11

Выборка с использованием оператора IN (принадлежит) 11

Выборка с использованием шаблонов. 12

Занятие 3.Расширенные SQL-запросы. Выборка из связанных таблиц. Группировка данных. Сортировка 15

Выборка из связанных таблиц. 15

Соединение с дополнительным условием.. 16

Использование группировки данных при организации запросов. 17

Сортировка. 19

Занятие 4. Расширенные SQL-запросы. Использование квантора существования в запросах. Коррелированные и некоррелированные подзапросы.. 22

Использование квантора существования в запросах. 22

Коррелированные и некоррелированные подзапросы.. 23

Занятие 5. Расширенные SQL-запросы. Модификация, удаление и добавление данных. Проверка условий целостности данных. 25

Модификация данных в таблицах. 25

Удаление данных. 27

Добавление записей. 28

Занятие 6. Объединение, пересечение и разность отношений. Операторы UNION, INTERSECT, EXEPT 31

Объединение множеств. 31

Занятие 7. Соединение отношений. Виды соединений. Оператор JOIN. 35

Занятие 8. Администрирование БД. Привилегии. Представления. 42

Предоставление доступа к отдельным таблицам.. 43

СПИСОК ИСПОЛЬЗОВАННЫХ ИСТОЧНИКОВ.. 45

Занятие 1. Безусловная выборка. Синтаксис запроса.

Цель занятия: изучение синтаксиса простого SQL-запроса.

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

Классификация команд SQL

Команды sql для удобства работы разделяются на следующие группы: Команды определения данных (Data Definition Commands); Команды манипуляции данными (Data Мanipulation Commands); Команда выборки данных (Data Query Commands); Команды управления транзакциями (Transaction Control Commands); Команды управления данными (Data Control Commands). Приведем список команд языка sql для каждой из этих групп в таблицах 1.1 - 1.6.

Таблица 1.1 - Команды определения данных (DDL)

Команда	Назначение
ALTER TABLE	Изменяет структуру таблицы
CREATE INDEX	Создает индекс
CREATE TABLE	Создает таблицу
CREATE VIEW	Создает представление
DROP	Удаляет таблицу, индекс, представление

Таблица 1.2 - Команды манипуляции данными- DML

Команда	Назначение
DELETE	Удаляет записи таблицы
INSERT	Добавляет записи в таблицу
UPDATE	Изменяет данные таблицы

Таблица 1.3 - Команда выборки данных

Команда	Назначение
SELECT	Выбирает данные из таблиц

Таблица 1.4 - Команды управления транзакциями

Команда	Назначение
СOMMIT	Делает изменения, проведенные с начала транзакции, постоянными
ROLLBACK	Откатывает все проведенные изменения к точке сохранения или к началу транзакции
SAVEPOINT	Устанавливает контрольную точку, к которой впоследствии можно будет выполнить откат

Таблица 1.5 - Команды управления данными

Команда	Назначение
CHECK DATABASE	Проверяет целостность базы данных
GRANT	Предоставляет привилегии
REVOKE	Удаляет предоставленные ранее привилегии
LOCK/UNLOCK	Блокирует данные и отменяет блокировку

Таблица 1.6 - Программный SQL

Команда	Назначение
declare	Определяет курсор для запроса, задает имя курсора, который соответствует виртуальному набору данных.
open	Открыть курсор. Формирует виртуальный набор данных, соответствующий описанию курсора и текущему состоянию БД.
fetch	Считать очередную строку, заданную параметром команды из набора данных, соответствующего открытому курсору.
close	Закрыть курсор
prepare	Подготовить оператор SQL к динамическому выполнению
execute	Выполнить оператор, ранее подготовленный к динамическому выполнению

Курсоры

Если запрос к таблице возвращает несколько строк (больше одной), то для их обработки используется курсор – указатель во множестве строк, выбранных оператором SELECT. Курсор – средство языка SQL, позволяющее получать построчный доступ к результату запроса. Термин «курсор» означает «текущий набор записей - current set of records». Оператором DECLARE в динамическом SQL объявляется курсор для запроса, оператором OPEN этот запрос выполняется и выбранные строки выделяются. Оператор FETCH позволяет перемещаться от одной строки результирующего множества к другой.

Для формирования запросов на языке SQL используется конструкция SELECT. Результатом выполнения запроса является таблица, которая хранится во временном буфере базы данных. Выбранные данные можно использовать для просмотра, формирования графиков или печати отчетов. Длина строки на языке SQL до 255 символов, регистр не имеет значения. Синтаксис команды SELECT:

SELECT [ALL½DISTINCT] список выбираемых полей

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

[WHERE условие выборки или соединения]

[GROUP BY список полей по условию группировки

[HAVING условие выборки группы]]

[ORDER BY список полей, по которым упорядочить вывод]

При формировании запросов можно использовать уточненные имена полей (например, CUSTOMER.CUSTOMERNO, т.е. Имя_таблицы. Имя_поля).

Ключевое слово ALLподразумевается по умолчанию.

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

SELECT*FROM таблица.

Запрос вида

SELECT*FROM R1, R2

соответствует декартову произведению таблиц R1 и R2, т. е R=R1х R2.

Запрос

SELECT R1.А, R2.В FROM R1, R2

соответствует проекции декартова произведения таблиц R1 и R2 на столбцы A из таблицы R1 и B – из таблицы R2, то есть операции проекция реляционной алгебры.

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

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

SELECT kod FROM R1

является столбец kod из таблицы R1.

При выполнении данного запроса в результат выборки будут включены все дубликаты строк.

Выборка уникальных значений

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

SELECT DISTINCT kod FROM R1

Задания. Базовый уровень:

1.Составить запрос для выборки всех столбцов таблицы R1 в том же порядке, в котором они находятся в исходной таблице.

2. Составить запрос для выборки всех столбцов таблицы R1(А,В,С,D,G) таким образом, чтобы порядок столбцов в выборке изменился следующим образом: D,G, В,С,А.

3. Составить запрос для выборки уникальных значений G из таблицы R1(А,В,С,D,G).

4. Составить запрос для выборки всех значений G из таблицы R1(А,В,С,D,G).

Повышенный уровень

5. Составить запрос на естественном языке, средствами реляционной алгебры и на языке SQL.

Вопросы. Базовый уровень

9.1. Как исключить дубликаты строк из результата выборки?

9.2. Как получить Декартово произведение отношений?

Повышенный уровень

9.3. Для чего применяются курсоры в динамическом SQL?

Занятие 2. Расширенные SQL-запросы. Выборка с условием.
Выборка вычисляемых значений

Цель занятия: изучение синтаксиса SQL-запроса для выборки с условием и для выборки вычисляемых значений.

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

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

Пусть в некоторой базе данных содержится таблица Товары, в которой цена (UNITPRICE) указана без учёта налога на добавленную стоимость (НДС). Поля таблицы Товары представлены в таблице 2.1. Тип переменной UNITPRICE – Currency.

Таблица 2.1 – Поля таблицы Товары

Kod1	UNITPRICE
	1000.0000
	2000.0000
	10000.0000

Необходимо организовать выборку информации из таблицы Товары,указавцены с учетом налога на добавленную стоимость (18%).

SQL-запрос, соответствующий примеру:

SELECT Kod1, UNITPRICE * (1 + 0,18) AS ЦЕНА FROM Товары

Результат выполнения данного запроса представлен в таблице 2.2.

Таблица 2.2 – Результат выборки из таблицы Товары

Kod1	ЦЕНА
	1180.0000
	2360.0000
	11800.0000

В запросе после ключевого слова AS записано новое название столбца таблицы.

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

Если в SQL-запрос, соответствующий примеру, добавить строку «Цена указана с учетом НДС», то он примет вид:

SELECT Kod1, UNITPRICE * (1 + 0,18) AS ЦЕНА, «Цена указана с учетом НДС» FROM Товары

Результат выполнения данного запроса представлен в таблице 2.3 .

Таблица 2.3 – Результат выполнения запроса

Kod1	ЦЕНА	Цена указана с учетом НДС
	1180.0000	Цена указана с учетом НДС
	2360.0000	Цена указана с учетом НДС
	11800.0000	Цена указана с учетом НДС

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

1) MIN(X) – вычисляет минимальное значение из множества X;

2) MAX(X) – вычисляет максимальное значение из множества X;

3) AVG(X) – вычисляет среднее арифметическое из множества значений X;

4) SUM(X) – вычисляет сумму значений множества X;

5) COUNT(X) – определяет число элементов множества X.

Примеры использования этих функций:

1) SELECT COUNT(*) FROM GOODS – создает выборку, состоящую из одной строки и одного поля, содержащего количество всех строк таблицы GOODS (товары);

2) SELECT MAX(Цена), MIN(Цена), AVG(Цена) FROM GOODS – создает выборку, состоящую из одной строки и трех полей, содержащих минимальное значение цены, максимальное значение цены и её среднее значение.

Выборка с условием

Для задания условия выборки в SQL-запросе используется команда (ключевое слово) WHERE.Условие, следующее за ключевым словом WHERE, может включать:

1) арифметические операторы сравнения: =, <>, > , <, >=, <=;

2) логические операторы – AND, OR, NOT;

3) скобки, определяющие порядок вычислений.

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

Пример. Пусть существует некоторая база данных, в которой имеется таблица Table3, содержащая информацию о поставщиках, покупателях, товарах. Предполагается, что покупатель может приобретать товар в кредит. Произвести из данной базы выбор всех кодов (kod) и фамилий (NAME)покупателей, которые находятся в Москве и имеют кредит(CREDITLIMIT) более 200 000.

С помощью языка SQL запрос, соответствующий примеру, можно представить в виде:

SELECT kod, NAME

FROM Table3

WHERE CITY=“Москва”

AND CREDITLIMIT>200000

SELECT kod, NAME, UNITPRICE

FROM Table4

SELECT kod, NAME, UNITPRICE

FROM Table4

SELECT kod, NAME, UNITPRICE

FROM Table4

SELECT kod, NAME, UNITPRICE

FROM Table4

WHERE UNITPRICE=100000

OR UNITPRICE=200000

OR UNITPRICE=500000

Как и в случае с оператором BETWEEN можно также использовать конструкцию NOT IN (не принадлежит), например:

SELECT kod, NAME, UNITPRICE

FROM Table4

SELECT kod, NAME, UNITPRICE

FROM Table4

WHERE NAME LIKE “T%”

пользователь может выбрать из базы данных все товары, наименование которых начинается с буквы “Т”( таблица 2.4).

Таблица 2.4 – результат выборки по шаблону

kod	NAME	UNITPRICE
	Toshiba 1200	600.00
	Turbo Pascal	200.00

Шаблоны в языке SQL описываются с помощью оператора LIKE, который может быть представлен в виде:

Задания (повышенный уровень)

9.Составить запрос для выборки цены товара (Таблица 10.1) с кодом Kod1=2 со скидкой 5%. Переименовать столбец UNITPRICEи присвоить ему новое имя «Вычисл_цена»

Вопросы. Базовый уровень

1. Как определить количество строк в таблице?

2. Какой запрос возвращает максимальное значение числового поля?

3. Как переименовать столбец в выборке?

4. Как добавить константу в результат выборки?

5. К каким типам переменных можно применять функцию COUNT(*)?

6. К каким типам переменных можно применять функцию SUM(X)?

7. К каким типам переменных можно применять функцию AVG(X)?

8. К каким типам переменных можно применять функцию MAX(X)?

9. К каким типам переменных можно применять функцию MIN(X)?

10. Какие логические операторы может содержать SQL-запрос?

Повышенный уровень

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

Выборка из связанных таблиц

Способность «соединять» две или более таблицы в одну представляет собой одну из наиболее мощных возможностей реляционных баз данных.

Простое соединение

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

SELECT Tab8.kod2, Tab9.NAME

FROM Tab8, Tab9

WHERE Tab8.STOCK= Tab9.STOCK

будет получен список кодов (kod2) и наименований (NAME) проданных покупателям товаров, представленный в таблице 3.1.

Таблица 3.1 – Результат выборки из двух таблиц

Kod2	NAME
	BORLAND C++
	NoSQL СУБД
	Книга TURBO C++
	Книга TURBO C++

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

FROM ORDSALE, GOODS

WHERE ORDSALE.STOCK=GOODS.STOCK

and GOODS.NAME=“ Macintosh”

Результат выполнения данного запроса представлен в таблице 3.2.

Таблица 3.2 – Результат выборки из двух таблиц с условием

ORDSALE.CUSTOMERNO	FIRSTNAME
	Иванов
	Иванов
	Петров
	Сидоров
	Кузнецов

Соединение трех таблиц

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

CUSTOMER.LASTNAME

SELECT kod, SUM(QUANT)

FROM ORDSALE

GROUP BY kod

SELECT kod, SUM(QUANT)

FROM ORDSALE

WHERE CUSTOMERNO <> 23

GROUP BY kod

служит для выборки информации о товаре, проданном покупателям кроме покупателя с кодом 23.

Строки, не удовлетворяющие условию WHERE, исключаются перед группировкой данных.

Конструкции GROUP BY свойственно ограничение, которое заключается в том, что она работает только на одном уровне. Невозможно разбить каждую из групп на группы более низкого уровня, а затем применить некоторую стандартную функцию, например, SUM или AVERAGE на каждом уровне группировки.

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

Оператор HAVING используется для ограничения записей, участвующих в группировке, его нельзя использовать отдельно от конструкции GROUP BY. Оператор HAVING используется для того, чтобы исключать группы так же, как WHERE используется для исключения записей. Выражение после конструкции HAVING должно принимать единственное для группы значение. В частности, результатом выполнения запроса

SELECT kod FROM ORDSALE GROUP BY kod HAVING COUNT(*)>1

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

Сортировка

ВкладкаOrdered Byв окне дизайнера запросов позволяет управлять порядком расположения записей в результирующей таблице. Для упорядочивания выделите указателем (курсором) поля, которые будут определять порядок сортировки выбранных данных, и перенесите их последовательно в список Ordering criteria.Для каждого выбранного поля можно с помощью переключателя (кнопки) Order optionsустановить критерий упорядочивания по возрастанию (Ascending) или по убыванию (Descending). Порядок сортировки записей результирующей таблицы определяется порядком следования полей в списке Ordering criteria и критерием упорядочивания отдельных полей.

Пример.Для таблицы Table3необходимо произвести выбор кода (kod), имени (NAME)и суммы кредита (CREDITLIMIT) всех покупателей, проживающих в Ставрополе, расположив их в порядке убывания.

SQL-запрос имеет вид:

FROM Table3

WHERE CITY=“Ставрополь”

ORDER BY CREDITLIMIT DESC

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

имя поля[упорядочение]

[,имя поля [упорядочение]] ...,

где аргумент «упорядочение» может принимать значение ASC(возрастание) или DESC (убывание). По умолчанию устанавливается значение ASC. В качестве аргументов имя поля могут использоваться только поля результирующей таблицы. Поэтому недопустима следующая конструкция:

FROM Table3

ORDER BY CITY

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

SELECT Kod1, UNITPRICE * (1 + 0,18)

FROM Товары

ORDER BY 2

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

Задания:
1. В состав реляционной БД входит отношение GOODS (STOCK, NAME, UNITPRICE). Составить запрос для получения записей, в которых наименование товаров (NAME) начинается с буквы “Т”.

2. В состав реляционной БД входит отношение

GOODS (STOCK, NAME, UNITPRICE). С помощью оператора IN составить SQL-запрос для выборки записей, в которых имя товаров (NAME) равно DVD, HDD или NOTEBOOK.

3. В состав реляционной БД входят отношения
CUSTOMER (CUSTOMERNO, FIRSTNAME, LASTNAME, CITY, CREDITLIMIT) и ORDSALE ( STOCK, CUSTOMERNO, NAME). Составить SQL-запрос для получения таблицы, содержащей без дублирования все атрибуты отношений CUSTOMER и ORDSALE.

4. В состав реляционной БД входит отношение CUSTOMER (CUSTO-MERNO, FIRSTNAME, LASTNAME, CITY, CREDITLIMIT). Вставить с помощью языка SQL запись для покупателя Василия Иванова с кодом (CUSTOMERNO) 23, проживающего в Москве, имеющего кредит (CREDITLIMIT) в размере 10000.

Задание (повышенный уровень)

5. Составить запрос, предусматривающий группировку данных.

Вопросы. Базовый уровень

1. В состав реляционной БД входит отношение GOODS (STOCK, NAME, UNITPRICE). Какую выборку можно получить после выполнения SQL-запроса: SELECT STOCK, NAME, UNITPRICE FROM GOODS WHERE NAME LIKE “Т%” ?

Повышенный уровень

1. 5. В состав реляционной БД входит отношение

2. ORDSALE ( STOCK, CUSTOMERNO, NAME).

3. Cоставить SQL-запрос с помощью оператора GROUP BY [HAVING] для выборки кодов товаров (STOCK), покупаемых более чем одним покупателем.

Занятие 4. Расширенные SQL-запросы. Использование квантора
существования в запросах. Коррелированные и некоррелированные
подзапросы

Цель занятия: изучение синтаксиса расширенных SQL-запросов, содержащих операторы EXISTS и NOT EXISTS.

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

SELECT NAME FROM tab10

WHERE EXISTS (SELECT * FROM tab11

WHERE tab10.kod= tab11.kod and.STOCK = “Macintosh”)

Фактически любой запрос, который может быть выражен с использованием оператора IN, альтернативным образом может быть сформулирован с помощью EXISTS.

Можно сконструировать отрицание существования, используя NOT EXISTS. С помощью SQL-запроса

SELECT NAME FROM tab10

WHERE NOT EXISTS (SELECT * FROM tab11

WHERE tab10.kod= tab11.kod and.STOCK = “Macintosh”)

можно получить информацию о покупателях, которые не купили «Macintosh».

Заключенный в скобки подзапрос, входящий в конструкцию EXISTS не обязательно использует конструкцию SELECT * ... . В конструкции SELECT можно также указать имя поля, т.е. использовать предложение вида: SELECT имя_поля FROM .... Операторы EXISTS и NOT EXISTS всегда помещаются перед подзапросом.

Задания (повышенный уровень)

2.В состав реляционной БД входят отношения

CUSTOMER (CUSTOMERNO, FIRSTNAME, LASTNAME, CITY, CREDITLIMIT) и ORDSALE (STOCK, CUSTOMERNO, NAME). Составить SQL-запрос с помощью оператора EXISTS для выборки фамилий покупателей, которым не был продан товар с наименованием (NAME), содержащим символы “HDD”.

3.В состав реляционной БД входит отношение
CUSTOMER(CUSTOMERNO, FIRSTNAME, LASTNAME, CITY, CREDITLIMIT). Составить SQL-запрос для выборки проживающих в Москве покупателей, имеющих кредит (CREDITLIMIT) больше среднего с указанием кода покупателя (CUSTOMERNO), фамилии и кредита (выборку упорядочить в порядке убывания кредита).

Вопросы. Базовый уровень:

1. Какой подзапрос называют коррелированным?

2. Какой подзапрос называют некоррелированным?

3. Каким образом без оператора EXISTS получить такой же результат, как при выполнении запроса:

SELECT NAME FROM tab10 WHERE EXISTS (SELECT * FROM tab11 WHERE tab10.kod= tab11.kod and.STOCK = “Macintosh”)?

Повышенный уровень

4. В состав реляционной БД входит отношение
CUSTOMER(CUSTOMERNO, FIRSTNAME, LASTNAME, CITY, CREDITLIMIT). Составить SQL-запрос для выборки проживающих в Москве покупателей, имеющих кредит (CREDITLIMIT) меньше среднего с указанием кода покупателя (CUSTOMERNO), фамилии и кредита (выборку упорядочить в порядке убывания кредита).

Занятие 5. Расширенные SQL-запросы. Модификация, удаление и
добавление данных. Проверка условий целостности данных

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

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

Модификация данных в таблицах

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

1) добавлять информацию в таблицу;

2) модифицировать данные в таблице;

3) удалять информацию из таблицы.

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

UPDATE таблица

SET поле=выражение [,поле=выражение] …

[WHERE условие]

В результате выполнения этой конструкции все записи в таблице, которые удовлетворяют условию, обновляются в соответствии с оператором присвоения «поле = выражение».

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

Модификация единственной записи. Для каждой записи, которая должна быть обновлена, т. е. для каждой записи, которая удовлетворяет условию WHERE, или для всех записей, если фраза WHERE опущена, ссылки во фразе SET на поля этой записи обозначают значения этих полей до их модификации. Например, с помощью SQL-запроса

UPDATE tab12

SET NAME =“Macintosh4”,

UNITPRICE = UNITPRICE+10000

WHERE NAME =“Macintosh”

будет изменено название товара «Macintosh» на «Macintosh4» и увеличена стоимость на 10000.

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

UPDATE tab14

SET CREDITLIMIT= CREDITLIMIT*2

WHERE CITY=“Ставрополь”

можно получить список всех покупателей, проживающих в Ставрополе, с увеличенной в 2 раза суммой кредита.

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

UPDATE tab14

SET UNITPRICE = 0.8*UNITPRICE

WHERE “Мичуринск” = (SELECT CITY FROM tab15

WHERE tab15.CUSTOMERNO= tab14.CUSTOMERNO)

можно вывести на экран список всех покупателей, проживающих в Мичуринске, уменьшив стоимость, приобретенного ими товара на 20%.

Удаление данных

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

DELETE FROM таблица [WHERE условие]

В результате выполнения конструкции удаляются все записи, которые удовлетворяют условию.

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

Пример– SQL-запрос, иллюстрирующий удаление одной записи из базы данных

DELETE FROM tab12

WHERE CUSTOMERNO=23

В результате его выполнения будет удален из базы данных покупатель со значением кода, равным «23».

Пример – SQL-запрос, иллюстрирующий удаление из базы множества записей

DELETE FROM tab12 WHERE STOCK=34

После выполнения данного SQL-запроса из таблицы tab12будутудаленывсе записи, в которых поле STOCKравно 34.

Пример – SQL-запрос, иллюстрирующий удаление всех записей из таблицы ORDSALE:

DELETE FROM ORDSALE

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

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

Пример – Удаление с подзапросом

DELETE FROM tab12

WHERE “Киев”= (SELECT CITY FROM tab13

WHERE tab13.CUSTOMERNO= tab12.CUSTOMERNO)

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

Добавление записей

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

INSERT INTO таблица [(поле [,поле] ...)] VALUES (константа [,константа]...)

или

INSERT INTO таблица [(поле [,поле] ...)] подзапрос

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

С помощью конструкции INSERTв базу данных можно вставить одну запись, множество записей.

Пример – SQL-запрос для вставки одной записи в базу данных

INSERT INTO tab15

(kod, NAME, UNITPRICE, CATEGORY)

VALUES

(1001,”Macintosh”,30000.00,2)

В результате выполнения приведенного выше запроса будет создана новая запись для товара с заданным номером, наименованием, стоимостью и категорией товара. В частности, в таблицу tab15будет добавлен товар «Macintosh»,с кодом равным 1001, стоимостью 30000000 и категорией, равной 2.

Пример – SQL-запрос для вставки одной записи в базу данных

Целостность

Целостность - самое важное требование, предъявляемое к базам данных. Для связи таблиц в реляционных базах данных используются совпадающие поля. Например, в таблицах CUSTOMER и ORDSALE связь осуществляется по полю CUSTOMERNO. Наименования совпадающих полей могут и не совпадать, но они должны иметь одинаковый тип. Нарушение целостности возникает в том случае, когда таблица ORDSALE содержит номера покупателей, которых нет в таблице CUSTOMER. Для проверки целостности данных при добавлении в таблицу ORDSALE информации об отпуске товаров можно проверить, имеется ли указанный покупатель в таблице CUSTOMER. Более правильным является использование первичных и внешних ключей таблиц, которые автоматически отслеживают целостность данных.

Для создания первичных и внешних ключей можно использовать команду CREATE TABLE а также команду ALTER TABLE. Приведем синтаксис команды ALTER TABLE:

ALTER TABLE имя таблицы

ADD PRIMARY KEY(имя поля, имя поля ...)

ADD FOREIGN KEY имя внешнего ключа

(имя поля, имя поля ...)

REFERENCES имя ссылочной таблицы

[ON DELETE

RESTRICT çCASCADE çSET NULL]

Для обеспечения целостности покупателей в базе данных создадим первичный ключ для таблицы CUSTOMER и внешние ключи для всех таблиц, которые содержат код покупателя:

ALTER TABLE CUSTOMER

ADD PRIMARY KEY(CUSTOMERNO)

ALTER TABLE ORDSALE

ADD FOREIGN KEY CUSTNO (CUSTOMERNO)

REFERENCES CUSTOMER

ON DELETE RESTRICT

Если указан параметр RESTRICT, то удалить покупателя в таблице CUSTOMER можно только в том случае, если он не указан ни в какой другой таблице, имеющей ссылку на таблицу CUSTOMER. В этом случае для удаления покупателя из таблицы CUSTOMER предварительно необходимо удалить все записи в таблице все записи в таблице ORDSALE, содержащие ссылки на этот номер покупателя. В некоторых случаях желательно, чтобы удаление главной таблицы приводило к удалению подчиненных. Для этого используется фраза: ON DELETE CASCADE.

Задания:

1. Составить запрос для модификации одной записи.

2. Составить запрос для модификации всех записей таблицы.

Задания (повышенный уровень)

3.Составить запросы с операторами RESTRICT и CASCADE.

Вопросы. Базовый уровень

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

2. Каким образом составить запрос, чтобы удаление записи из главной таблицы не приводило к удалению связанных записей из подчиненных таблиц?

Повышенный уровень

13.3. Для чего используются операторы RESTRICT и CASCADE?

Занятие 6. Объединение, пересечение и разность отношений. Операторы UNION, INTERSECT, EXEPT

Цель занятия: изучение синтаксиса теоретико-множественных операций.

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

Объединение множеств

Объединением двух множеств называется множество всех элементов, принадлежащих какому-либо одному или обоим множествам. Поскольку отношение является множеством записей, то можно построить объединение двух отношений. Результатом будет отношение, состоящее из всех строк, входящих в какое-либо одно или в оба сразу отношения. Однако строки этих двух отношений должны быть совместимы по объединению.

В языке SQL две таблицы совместимы по объединению и к ним может быть применен оператор объединения UNION тогда и только тогда, когда:

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

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

В SQL-92 реализованы некоторые операции реляционной алгебры в явном виде с помощью операторов: UNION, Intersect, EXCEPT, JOIN.

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

Пример. SQL-запрос

SELECT kod FROM tab12 WHERE UNITPRICE>1000

UNION

SELECT kod FROM tab13 WHERE CUSTOMERNO=23

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

Использование оператора UNION исключает из результатов выборки повторяющиеся значения. Если это не устраивает пользователя, то вместо оператора UNION используют UNION ALL.Тогда повторы не будут исключены из выборки.

Оператором UNION можно соединить любое количество конструкций SELECT.

Пример. С помощью SQL-запроса

SELECT kod FROM tab12 WHERE UNITPRICE>1000

UNION

SELECT kod FROM tab13 WHERE CUSTOMERNO=23

UNION

SELECT kod FROM tab12 WHERE UNITPRICE< 500

пользователь может получить информацию о кодах товаров, которые имеют стоимость более 1000, либо приобретены покупателем с кодом 23, либо имеют цену менее 500.

Если к запросу, приведенному в примере 1, добавить строку OR UNITPRICE < 500, то его результирующая таблица будет аналогична результирующей таблице, соответствующей запросу 2.

Оператор ORDER BY в запрос с использованием оператора UNION может входить только в последнее предложение SELECT. При использовании критерия упорядочивания используются номера полей результирующей таблицы. Пример с включением константы в результирующую таблицу:

SELECT kod,”Стоимость товара >1000$” FROM tab12

WHERE UNITPRICE>1000

UNION<