2. Данные лабораторной работы

Применение данных инструкций рассмотрим на примере БД «Детали и поставщики», включающей в себя следующие таблицы: S – поставщики P – детали SP – поставки. Первичными ключами этих таблиц являются соответственно: П – код поставщика, Д – код детали и составной ключ – П, Д. Содержимое таблиц приведено в таблицах..

Таблица S

П	Имя	Статус	Город
S1	Вениамин	20	Москва
S2	Алексей	10	Киев
S3	Федор	30	Киев
S4	Игорь	20	Москва
S5	Вадим	30	Минск

Таблица P

Д	Название	Цвет	Вес	Город
P1	Гайка	Красный	12	Москва
P2	Болт	Черный	17	Киев
P3	Винт	Голубой	17	Ростов
P4	Винт	Красный	14	Москва
P5	Шпилька	Голубой	12	Киев
P6	Палец	Красный	19	Москва

Таблица SP

П	Д	Количество
S1	P1	300
S1	P2	200
S1	P3	400
S1	P4	200
S1	P5	100
S1	P6	100
S2	P1	300
S2	P2	400
S3	P2	200
S4	P2	200
S4	P4	300
S4	P5	400

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

Предположим, что имена доменов (множество допустимых значений) совпадают с именами атрибутов. Характеристики типов данных доменов следующие: П – строка длиной 5 символов, имя – строка длиной 20 символов, статус – числовое данное. При создании таблиц для отдельных полей могут указываться некоторые дополнительные правила контроля вводимых в них значений. Например, конструкция NOT NULL (не пустое) для столбца таблицы означает, что в каждом поле этого столбца должно быть не пустое значение.

CREATE TABLE БАЗОВАЯ_таблица(Столбец_1 тип_данных, столбец_2 тип_данных, ...);

Пример_1: Создать таблицы для БД «Детали и поставщики».

CREATE TABLE S (П CHAR(5) not null, ИМЯ CHAR(20), СТАТУС NUMERIC, ГОРОД CHAR (20), PRIMARY KEY (П));

Примечание: Последнее предложение назначает ключевой параметр.

4. Выборка данных

Рассмотрим более подробно оператор выборки SELECT на конкретных примерах.

Пример_2. Получить цвета и города для деталей «не из Киева» с весом более 10. Перейдите в режим создания запроса на выборку и затем нажмите кнопку режим Вид и выберите режим SQL.

В открывшемся окне наберите инструкции создаваемого запроса

SELECT Р.Цвет, Р.Город FROM Р WHERE (((Р.Город)<>"Киев") And ((Р.Вес)>10));

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

Измените инструкцию запроса, добавив после оператора SELECT слово DISTINCT. Выполните измененный запрос и просмотрите результат, который теперь выдаст только две строки.

Пример_3. Для всех деталей получите номера детали и ее вес в килограммах. Для выполнения этого примера введите следующую инструкцию:

SELECT P.Д, P.ВЕС/1000 AS КГ FROM P;

В результирующей таблице появился новый столбец под названием КГ. Если бы оператор As кг был опущен, то столбец был бы безымянным.

Пример_4. Получить информацию о всех поставщиках. Для выполнения этого примера введите следующую инструкцию:

SELECT * FROM S;

Результатом запроса будет таблица S, т. к. * после SELECT - требует вывести список всех имен столбцов таблицы, на которую делается ссылка в инструкции FROM.

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

SELECT S.П, S.имя, S.статус, S.город, P.Д, P.город FROM S, P WHERE S.город=P.город;

Результатом этого запроса будет естественное соединение таблиц S и P (по городам). Реализация этого запроса происходит следующим образом. Во-первых, после выполнения инструкции FROM получается декартово произведение таблиц S и P. Далее, после выполнения WHERE получается выборка из этого произведения, в которой два значения ГОРОД в каждой строке равны (т.е. построено соединение поставщиков и деталей по эквивалентности городов). После выполнения оператора SELECT получается проекция выборки по столбцам, указанным в инструкции SELECT. Конечным результатом будет естественное соединение. Следовательно, можно сказать, что инструкция FROM в SQL соответствует декартову произведению, инструкция WHERE – выборке, а совместная инструкция SELECT – FROM – WHERE представляет проекцию выборки произведения. Сохраните запрос под именем Пример_9 и выполните его. Просмотрите результаты запроса.

Пример_6: Получить общее число поставщиков. Для выполнения этого примера введите следующую инструкцию:

SELECT COUNT(*) AS N FROM S;

Результатом запроса будет таблица с одним столбцом с именем N и одной строкой, содержащей значение 5. Язык SQL поддерживает обычный набор итоговых функций (count, sum,avg, max, min). Следует отметить, что функция COUNT(*) подсчитывает все строки в таблице без удаления дублирующих строк и не допускает применения слова DISTINCT. Пример_7: Получить максимальное и минимальное количество для детали P2. Для выполнения этого примера введите следующую инструкцию:

SELECT MAX(SP.КОЛИЧЕСТВО) AS MAXQ, MIN(SP.КОЛИЧЕСТВО) AS MINQ FROM SP WHERE SP.Д='P2';

Здесь обе инструкции From и Where фактически являются аргументами для двух итоговых функций. Пример_8: Для каждой поставляемой детали получить номер детали и общее количество поставки.

SELECT SP.Д, SUM(SP.КОЛИЧЕСТВО) AS ОБЩЕЕ_КОЛИЧЕСТВО FROM SP GROUP BY SP.Д;

Следует отметить, что если указана инструкция GROUP BY, то выражение в инструкции SELECT должно быть однозначным (в группе выделяется одна запись). Этот же запрос можно оформить и так:

SELECT P.Д, (SELECT SUM (SP.КОЛИЧЕСТВО) FROM SP WHERE SP.Д = P.Д) AS ОБЩЕЕ_КОЛИЧЕСТВО FROM P;

В этой записи приводится возможность использования вложенных запросов.

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

SELECT S.П FROM S WHERE S.СТАТУС < ( SELECT MAX (S.СТАТУС) FROM S);

Пример_10: Изменить вес гайки с 12 на 13. Для выполнения этого примера введите следующую инструкцию:

UPDATE P SET Вес = 13 WHERE Название = 'Гайка';

Пример_10: Удалить всех поставщиков из города «Киев»

DELETE FROM S WHERE Город = 'Киев';

Пример_11: Добавить нового поставщика

INSERT INTO S (П, Имя, Статус, Город) VALUES ('S10', 'Микола', '42', 'Чернигов');

Пример_12: Изменить количество поставок на 100 для всех красных деталей.

UPDATE SP

SET Количество=Количество+100 WHERE Д IN (SELECT Д FROM P WHERE Цвет='Красный');

Задания

1. Создайте и проверьте выполнение всех видов запросов, описанных в примерах.

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

Тема четвёртой лекции "Язык SQL" Тезисы: Язык структурированных запросов * есть несколько стандартов языка * конкретные СУБД обычно только частично поддерживают стандарт * Отступления от стандартов. У производителей СУБД часто есть свои, ни с чем не совместимые, команды * в последних версиях стандарта SQL появились элементы традиционных языков программирования * Хотя SQL и задумывался как средство работы конечного пользователя, в конце концов он стал настолько сложным, что превратился в инструмент программиста. Типы команд: * DDL - операторы определения структуры данных (создание и изменение структур таблиц, удаление таблиц, DROP TABLE) * DML - операторы манипуляции данными (добавление строк в таблицы, их изменение) * DCL - операторы определения доступа к данным Нужно знать синтаксис команд: * INSERT (вставка одной строки, вставка результатов команды SELECT) * UPDATE * DELETE * MERGE * SELECT Структура команды SELECT SELECT [DISTINCT] ... (какие поля выбирать) FROM  ... (из какой таблицы выбирать) JOIN ... (какую таблицу подсоединить) WHERE ...  (условие выборки из таблицы) GROUP BY ... ("схлопывать" строки по указанному параметру) HAVING ... (условие выборки, действующее после группировки) ORDER BY (сортировка данных) Агрегирующие функции: * SUM * COUNT * MIN * MAX * AVG