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

Текст из файла (страница 4)

Схема базы данных: ρ=(S,P,J,SPJ).

здесь:

S - таблица "Поставщик", описывающая поставщиков деталей. Имеет следующие поля:

• номер поставщика - идентификационный номер поставщика (ключевое поле);

• имя - фамилия, имя и отчество поставщика;

• состояние - состояние поставщика (состояние счета);

• город - город, в котором проживает поставщик и в котором находится его центральный офис.

P - таблица "Деталь". Описывает поставляемые детали и включает следующие поля:

• номер детали - идентификационный номер детали (ключевое поле);

• название - название детали;

• цвет - цвет детали;

• вес - все детали в фунтах;

• город - город, в котором изготавливается деталь.

J - таблица "Изделие". Описывает изготавливаемые изделия и включает следующие поля

• номер изделия - идентификационный номер изделия (ключевое поле);

• название - название изделия;

• город - город, где изготавливается изделие.

SPJ - таблица "Сборка". Имеет следующие поля:

• номер поставщика — идентификационный номер поставщика, поставляющего деталь (ключевое поле);

• номер детали - идентификационный номер детали, поставляемой поставщиком (ключевое поле);

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

• количество - количество деталей, поставляемых поставщиком для данного изделия (количество деталей в поставке).

SQL состоит из языка описания данных (генерируется CASE-средствами) и языка манипулирования данными (SELECT, INSERT, UPDATE, DELETE).

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

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

Существуют различные способы встраивания SQL в процедурный язык:

• API;

• операторы добавляются в язык (SQLJ = Java + SQL);

• операторы используются как обычные операторы процедурного языка (PL/SQL).

Оператор SELECT

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

Порядок выполнения оператора SELECT на логическом уровне:

1. из исходной таблицы выбираются записи по условию. Это операция селекции;

2. из полученных записей выделяются указанные атрибуты;

3. если есть GROUP BY, то результирующие записи группируется по атрибутам (по одинаковым значениям), указанным в условии;

4. если есть ORDER BY, то результирующие записи сортируются по указанному атрибуту.

Лекция №9 - SQL (продолжение)

Некоторые возможности языка SQL

Запросы без соединения таблиц

Вывести веса деталей

Пример таблицы P

SELECT номер_детали, вес

FROM P;

Выборка с ограничением

Выдать номер поставщиков, которые живут в Париже и имеют состояние более 20.

Пример таблицы S

SELECT номер_поставщика, состояние, город

FROM S

WHERE город = 'Париж' AND состояние > 20;

Ещё пример: выдать номера поставщиков с состоянием от 10 до 20:

SELECT номер_поставщика, состояние, город

FROM S

WHERE состояние BETWEEN 10 AND 20;

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

Упорядочивание:

• ASC - по возрастание (стоит по умолчанию);

• DESC - по убыванию.

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

1. записи упорядочиваются по первому атрибуту;

2. записи с одинаковым значением первого атрибута упорядичиваются по второму атрибуту;

3. и так далее.

Пример: выдать номера поставщиков, города проживания и состояния поставщиков с именем Смит в алфавитном порядке городов и в порядке убывания состояния.

Таблица S:

SELECT номер_поставщика, город, состояние

FROM S

WHERE имя = 'Смит'

ORDER BY город ASC, состояние DESC;

Выборка с конструкцией LIKE

Позволяет организовать контекстный поиск в символьных полях.

Пример: выдать номера поставщиков, названия городов которых называются с "Л".

SELECT номер_поставщика

FROM S

WHERE город LIKE "Л%";

Выборка с конструкцией IN

Проверка принадлежности атрибута какому-либо множеству.

Выдать поставщиков, состояние которых равно 25, 40, 60 или 70.

SELECT *

FROM S

WHERE состояние IN(25, 40, 60, 70);

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

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

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

Пример таблиц:

SELECT DISTINCT S.город, P.город

FROM S, P, SPJ

WHERE SPJ.номер_поставщика = S.номер_поставщика AND SPJ.номер_детали = P.номер_детали;

Соединение таблицы с самой собой

Выдать все пары номеров поставщиков, которые проживают в одном городе.

Пример таблицы:

Используются псевдонимы.

SELECT PS1.номер_поставщика, PS2.номер_поставщика

FROM S PS1, S PS2

-- чтобы исключить дубли, вводим отношение порядка

WHERE PS1.город = PS2.город AND PS1.номер_поставщика < PS2.номер_поставщика;

Использование конструкции INNER JOIN

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

SELECT имя

FROM S JOIN SPJ ON S.номер_поставщика = SPJ.номер_поставщика

JOIN P ON SPJ.номер_детали = P.номер_детали AND цвет = 'красный';

Подзапросы

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

Пример

Выдать имена поставщиков, которые поставляют деталь с номером P2.

Примеры таблиц:

SELECT имя

FROM S

WHERE номер_поставщика IN

(

SELECT номер_поставщика

FROM SPJ

WHERE номер_детали = 'P2'

);

Лекция №10 - SQL (продолжение)

Некоторые возможности языка SQL

Подзапросы

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

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

Примеры таблиц:

SELECT имя

FROM S

WHERE номер_поставщика IN

(

SELECT номер_поставщика

FROM SPJ

WHERE номер_детали IN

(

SELECT номер_детали

FROM P

WHERE цвет_детали = 'красный'

)

);

Вычисление начинается с самого внутреннего подзапроса и наружу до самого внешнего.

Коррелирование подзапросов

Выдать имена поставщиков, которые поставляют детали с номером P2.

SELECT имя_поставщика

FROM S

WHERE 'P2' IN

(

SELECT номер_детали

FROM SPJ

WHERE номер_поставщика = S.номер_поставщика

);

Просматривается таблица S, и для каждой записи из неё номер поставщика подставляется во внутренний подзапрос; он выполняется, и система проверяет, принадлежит ли P2 найденному множеству.

Подзапросы в качестве таблицы в выражении FROM

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

SELECT имя_поставщика

FROM S, (

SELECT номер_поставщика

FROM SPJ, P

WHERE SPJ.номер_детали = P.номер_детали

AND цвет = 'красный'

) NP -- псевдоним

WHERE S.номер_поставщика = NP.номер_поставщика;

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

Некоторые законы математической логики:

∀a(P)=∃¯a(P¯¯¯)

A→B=A¯¯¯∨B

A∨B¯¯¯¯¯¯¯¯¯=A¯¯¯∧B¯¯¯

NOT EXISTS

Выдать номера поставщиков, которые поставляют, по крайней мере, все те детали, которые поставляет поставщик с номером S2.

∀ деталь (деталь поставляет S2)→деталь поставляет SX

=1∃¯ деталь(деталь поставляет S2→деталь поставляет SX¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯)

=2∃¯ деталь(деталь поставляет S2¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯∨деталь поставляет SX¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯)

=3∃¯ деталь(деталь поставляет S2∧деталь поставляет SX¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯)

В SQL это будет:

SELECT номер_поставщика SX

FROM SPJ SPJX

WHERE NOT EXISTS

(

SELECT номер_детали

FROM SPJ, SPJX

WHERE номер_поставщика = 'S2'

AND NOT EXISTS

(

SELECT *

FROM SPJ

WHERE номер_поставщика = SPJX.номер_поставщика

AND номер_детали = SPJX.номер_детали

)

);

Некоторые стандартные функции SQL

COUNT(атрибут) - возвращает число строк таблицы по атрибуту. COUNT(DISTINCT атрибут) - возвращает мощность атрибута. COUNT(*) - возвращает число строк в таблице.

SUM(атрибут) - сумма значений атрибута.

AVG(атрибут) - среднее значение атрибута.

MAX(атрибут) - максимальное значение атрибута.

MIN(атрибут) - минимальное значение атрибута.

Пример

Выдать информацию по поставщикам, состояние которых не ниже среднего.

SELECT имя, состояние, город

FROM S SX

WHERE состояние >=

(

SELECT AVG(состояние)

FROM S

WHERE город = SX.город

);

Ещё пример

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

SELECT номер_детали, SUM(количество), COUNT(DISTINCT номер_поставщика)

FROM SPJ

-- HAVING выполняется после того, как группа сформирована

GROUP BY номер_детали HAVING COUNT(DISTINCT номер_поставщика) > 1;

Особенности разработки приложений для работы с БД в сети

Сервер БД должен обеспечивать:

1. блокировку обновляемых записей;

2. ведение транзакций;

3. обработку тупиковых ситуаций.

Блокировка обновляемых записей

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

На языке PL/SQL:

1)

CREATE PROCEDURE проводка -- создание процедуры

Характеристики

Список файлов лекций