 |
Манипуляционная часть реляционной модели данных.
|
|
|
|
Реляционная алгебра.
Реляционная алгебра представляет собой набор операций, использующих отношения в качестве операндов и возвращают отношения в качестве результата. Таким образом реляционные отношения
R= f(R1, R2,, Rn)
- это функция, с отношение в качестве аргументов.
Реляционная алгебра является замкнутой.
R= f(f(R11, R12,.., R1m), R2,.., Rn)
Реляционные операции разбиваются на 2 группы:
1. Теоретико-множественные операции (основные): объединение, декартово произведение.
2. Специфические реляционные операции: выборка, проекция, соединение, деление, переименование.
Некоторые из перечисленных операций требуют, чтобы отношения, использующиеся в качестве аргументов, были совместимы по типу.
Отношения считаются совместимыми по типу, если
1. Отношения имеют одно и то же множество аргументов.
2. Атрибуты с одинаковыми именами, определённые на одних и тех же доменах.
Некоторые отношения не являются совместимыми по типу, но становятся такими после применения операции переименования.
Операции над множествами
Над множествами можно выполнить 4 операции:
1. Присвоение
Пусть А – множество, тогда операция присвоения имеет следующий вид:
А:=значение, где значением может быть переменная или константа типа множество.
Данная операция является одноместной. Остальные операции двухместные. Операндами в этих операциях могут быть переменные и константы типа множество.
Пусть А и В – множества.
2. Определение: Пересечение множеств – это новое множество, состоящее из элементов, которые одновременно принадлежат множеству А и В (АᴖВ).
3. Определение: Объединение множеств – это новое множество, в которое входят элементы и из элементов множества А, и из элементов множества В, и элементы, принадлежащие одному и второму множествам одновременно (АᴗВ)
|
|
|
4. Определение: Разность множеств – это новое множество, в которое входят элементы уменьшенного множества (пусть это множество А) и не входят элементы вычитаемого множества (В) (А\В)
Последние 2 операции используются для выполнения следующих действий:
1. (А+В) – используется для включения в множество отдельных элементов.
2. (А-В) – используется для исключения отдельных элементов из множества.
Основные реляционные операции (объединение, пересечение, разность)
Для всех основных операций справедливо требуемое равенство схем, включаемых в операцию отношений.
Пусть есть 2 отношения r(R) и s(R). Причём схем R=(A, B, C).
| r | (A | B | C) | s | (A | B | C) | |
| a1 | b1 | c1 | a1 | b2 | c1 | |||
| a1 | b2 | c2 | a2 | b2 | c1 | |||
| a2 | b1 | c2 | a2 | b2 | c2 | |||
| rᴖs | (A | B | C) | |||||
| a1 | b2 | c1 | ||||||
| rᴗs | (A | B | C) | |||||
| a1 | b1 | c1 | ||||||
| a1 | b2 | c1 | ||||||
| a1 | b2 | c1 | ||||||
| a2 | b2 | c1 | ||||||
| a2 | b2 | c2 | ||||||
| r-s | (A | B | C) | |||||
| a1 | b1 | c1 | ||||||
| a2 | b1 | c2 |
Результатом операции объединения отношений r и s является отношение q, в которое входят все кортежи, принадлежащие или отношение r или отношению s.
Результатом операции пересечения отношений r и s является отношение q, включающее в себя кортежи, входящие и в r и в s.
Результатом операции разности является отношение q, в которое входят все кортежи, принадлежащие отношению r и не принадлежащие отношению s.
Пример:
|
|
|
Отношение r – клиенты банка A.
Отношение s – клиенты банка В.
q – результат.
| r | id | Город | Фамилия |
| К11 | Москва | Петров | |
| К12 | СПБ | Смирнов | |
| К12 | Воронеж | Соколов | |
| s | id | Город | Фамилия |
| К21 | Самара | Петров | |
| К22 | Москва | Петров | |
| К23 | Тверь | Семёнов |
Д./з. Пусть имеется 3 отношения с одной и той же схемой (R1, R2, R3)
R=(ФИО, Паспорт, Школа).
R1- список абитуриентов, сдавших пробные (репетиционные) экзамены.
R2 – список абитуриентов, сдавших на общих основаниях.
R3 – список абитуриентов, принятых в институт.
С помощью реляционных операторов (объединение, пересечение, разность) получить список абитуриентов
- которые поступали 2 раза и не постпили;
- которые поступили с первого раза;
- поступали лишь 1 раз и не поступили.
Определение: Декартовым произведением называется отношение, заголовок которого является сцеплением заголовков А и В, а тело представляет собой сцепление каждого кортежа из А с кортежом из В.
A TIMES B
| № студ. билета | ФИО | № предмета | Название | |
| … | … | |||
| … | … | |||
| № студ. билета | ФИО | № предмета | Название | |
| … | … | |||
| … | … | |||
| … | … | |||
| … | … |
Если в отношениях А и В есть атрибуты, с одинаковыми именами, то их необходимо перед выполнением операции переименовать.
(Студенты RENAME Номер AS НомерСт) TIMES Предметы
Дополнительные реляционные операции (выбор, проекция, соединение, деление)
1. Операция выбора или ограниченная выборка на отношении А с условием С называется отношение с тем же заголовком и телом, составляющих из кортежей, удовлетворяющих условию С.
Вид операции:
Выбор (Имя отношения, если Условие)
С= X ѲY, где Ѳ – одна из операций сравнения. X и Y – атрибуты отношения
A WHERE C
A WHERE ФИО=Петров
Выборка даёт горизонтальный срез отношения.
Условие может быть простым или сплошным. Простое условие имеет следующий вид:
1) А операция сравнения В, где А и В – имена атрибутов отношения.
2) А операция сравнения const, где А – атрибут отношения, а const – некоторая константа (численная, строковая и т. д.).
В сложном условии может использоваться логическое выражение, где несколько простых условий могут быть связаны логическими операндами:
AND, OR, NOT.
|
|
|
2. Проекция отношения
Определение 1: Проекцией отношения А по атрибутам X, Y, Z, где X, Y, Z – атрибуты отношения А, называется отношение с заголовком (X, Y, Z). Тело формируется путём извлечения значений указанных атрибутов, исключая из отношения строки-дубликаты.
Определение 2: Проекция отношения А – это операция (как операция выбора), выполняющаяся над одним отношением. Операция проекции формирует новые отношения в соответствии с заданным (в качестве второго операнда операции) подмножеством атрибутов и порядком расположения атрибутов как в исходном отношении.
Проекция даёт вертикальный срез отношения. Проекция осуществляется в 2 этапа. На первом из исходного отношения изо всех кортежей удаляются все столбцы, не входящие в схему (на которую осуществляется проекция), заданную вторым операндом. На втором удаляются дубли кортежей. В итоге новое отношение может содержать не только меньше атрибутов, но и меньше кортежей.
3. Соединение отношений.
Сцеплением (конкатенацией) двух кортежей называется кортеж, полученный добавлением значения второго кортежа в конец первого.
C={a,b,c}
R={d,e,f}
Q={a,b,c,d,e,f}
Соединение выполняется над двумя логически связанными (по общему атрибуту) отношениями для заданного условия соединения. Исходные отношения C и R имеют разные схемы, в которых есть одинаковые атрибуты. Как правило это внешний и первичный ключи (ключи связи). Операция соединения формирует новые отношения, схема которых включает все атрибуты исходных отношений, то есть результирующие кортежи формируются соединением (конкатенацией) каждого кортежа C с теми же кортежами R, для которых выполняется условие.
Существуют следующие виды соединений:
1) Естественное соединение.
2) Эквивалентное соединение (экви-соединение).
3) Ѳ-соединение.
4)Внешнее соединение.
В зависимости от условия соединения может требоваться равенство значений общих атрибутов отношений C и R), то есть условие задаётся в виде a=b, где а и b – первичные и внешние ключи соответственно или экви-соединение (требуется равенство значений не общих атрибутов, а тех, которые заданы в условии). Или Ѳ соединение (в условии задаётся не операция равенства, а какая-либо другая операция).
У операции соединения 3 операнда: первый и второй – это соединяемые отношения, а третий – условие.
Естественное отношение эквивалентно последовательности реляционных операций:
1)Переименование одинаковых атрибутов в отношениях.
2) Выполнение декартова произведения отношений.
3) Выполнение выборки по совпадающим значениям атрибутов, имеющих одинаковые имена.
4) Выполнение проекции, удаление повторяющихся атрибутов.
5) Возвращение атрибутов, возвращение первоначальных имён.
|
|
|
Пусть есть исходное отношение
| r1 | (ФИО | Группа) | Студенты | |
| Иванов | ||||
| Петров | ||||
| Сидоров | ||||
| Воронов | ||||
| Киреев | ||||
| Широков | ||||
| r2 | (Группа | Факультет) | Группы | |
| ФВТ | ||||
| ФВТ | ||||
| ФВТ | ||||
| ФВТ | ||||
| ФЭ | ||||
Естественное соединение
Соединим r1 и r2 по правилам естественного соединения:
| Иванов | ФВТ | |
| Петров | ФВТ | |
| Сидоров | ФВТ | |
| Воронов | ФВТ | |
| Киреев | ФВТ | |
| Широков | ФЭ |
Ѳ соединение
Ѳ соединением отношения А по атрибуту х с отношением В по атрибуту у называют отношение R, получаемое так:
R=(A TIMES B) WHERE x Ѳ y
Экви-соединение
Экви-соединение – это частный случай Ѳ соединения, когда условия <>, >, < и т. д.
Внешнее соединение
Внешнее соединение бывает следующих видов:
1. Левое.
2. Правое.
3. Полное.
Левое внешнее соединение отличается от естественного тем, что в результирующее отношение добавляются все кортежи отношения А (левая таблица). При этом отсутствующие значения полей из отношения В будут заполняться NULL значениями.
Правое внешнее соединение выполняется аналогично левому.
В результате полного внешнего соединения создаётся новое отношение, в которое помещаются все кортежи из обоих отношений. Отсутствующие значения заполняются NULL.
A JOIN B – естественное соединение.
A RIGHT JOIN B – правое соединение.
A FULL JOIN B - полное соединение.
Д./з. Привести примеры.
1.
2.
3.
4. Деление
Пусть отношение А содержит множество атрибутов X, а множество B - содержит множество атрибутов Y. Пусть Z=X-Y, тогда результатом операции A/B является отношение C с заголовком, содержащим множество атрибутов Z. Тело отношения C представляет собой набор кортежей, состоящих из значений множества атрибутов Z, которые составляют комбинации всех кортежей B.
|
|
|
A DIVIDE BY B
Операция деления позволяет сделать выборку кортежей совместно с проекцией.
Д./з. Пример деления.
Языки БД. QBE и SQL
По способу формирования запросы подразделяются на структурированные, которые реализуются с помощью операторов языка SQL и запросы по образу, формируемые с помощью языка QBE.
Язык QBE использует визуальный подход для реализации доступа к данным в БД. Работа в нём осуществляется посредством задания образцов знаний в таблице запросов. При создании QBE запроса СУБД автоматически генерирует SQL запрос, эквивалентный по выполненным действиям. СУБД позволяет перейти от QBE запроса к SQL запросу и обратно.
Д./з. В каких СУБД и с помощью каких средств реализован язык QBE.
Язык SQL – более мощное средство для создания запросов, чем QBE, но при наличии большого числа таблиц в БД, при наличии сложных имён таблиц и атрибутов целесообразно использовать QBE запросы.
Рекомендуется формировать запросы следующим образом:
· использовать QBE, для определения имён таблиц, атрибутов и их связей;
· перейти к сгенерированному SQL запросу и добавить критерий отбора из таблиц;
Международный стандарт языка SQL (разработанный в 1992 году) называется SQL-92. Следующие версии языка SQL, поддерживают ОО подход.
Каждая СУБД, претендующая на звание реляционной, используют тот или иной диалект SQL. Цель стандартизации – переносимость приложений между различными СУБД (диалектами).
Проблема совместимости в том, что в настоящее время ни одна реляционная СУБД не поддерживает стандарт в полном объёме.
Язык SQL является реляционно-полным. Это означает, что операции реляционной алгебры могут быть выражены подходящими операторами языка SQL.
Язык SQL состоит из 2 разделов:
1. DDL – язык описания данных (Data Definition Language).
2. DML – язык манипуляции данными (Data Manipulation Data).
Язык DML
Оператор SELECT позволяет производить выборку данных из таблиц и преобразовывать к нужному виду полученные результаты.
Оператор SELECT состоит из следующих предложений:
SELECT <список столбцов>;
FROM <список таблиц>;
[WHERE <спецификация выбора строк>];
[GROUP BY <спецификация группировки>]
[ORDER BY <спецификация выбора групп>]
В предложении SELECT перечисляются имена столбцов, значения которых будут вводить в результат запроса (таблица).
В предложении FROM перечислены имена таблиц, которые содержат столбцы,указанные после слова SELECT.
SELECT dnum, dname, dprice
FROM D
SELECT *
FROM В
Пример: нужно вывести имена поставщиков, которые поставили хотя бы одну деталь
SELECT name
FROM P
WHERE num <> NULL
Чтобы исключить дублирующиеся значения результата нужно указать служебное слово DISTINCT.
SELECT DISTINCT name
FROM P
Альтернативой использования служебного слова DISTINCT является использование служебного слова ALL, которое выполняет вывод всех строк по-умолчанию.
Для одного оператора SELECT служебное слово используется 1 раз.
В предложении WHERE указывается условие выбора строк результирующей страницы. Условие может содержать:
1. Арифметические операции.
2. Логические операции.
3. Операции отношения.
4. Операции IN (проверка на положительность).
5. Операция BETWEEN (проверка на принадлежность).
6. Операция LIKE (проверка соответствия строкового значения).
7. Оперцаия IS NULL (проверка на наличие NULL значений)
| Поставщики | P | pnum | Pname | |
| Иванов | ||||
| Петров | ||||
| Сидоров | ||||
| Кузнецов | ||||
| Детали | D | dnum | dname | Dprice |
| Болт | ||||
| Гайка | ||||
| Винт | ||||
| Шуруп |
Пример: Вывести информацию о винтах и болтах:
SELECT *
FROM D
WHERE (dname=”Винт” OR dname=”Болт”)
SELECT *
FROM D
WHERE (dname in (”Винт”, ”Болт”))
Пример: Вывести информацию о деталях с ценой от 10 до 20 рублей:
SELECT *
FROM D
WHERE dprice BETWEEN (10 AND 20)
% - любое количество любых символов
_ - любой символ
[…] – вместо символа строки можно поставить любой из перечисленных символов строки
[^…] – вместо символа можно указать любой, кроме тех, которые указаны в скобках
Пример:
SELECT pname
FROM P
WHERE pname=%[ов]
Пример: Вывести информацию о деталях, для которых цена не указана
SELECT *
FROM D
WHERE dprice IS NULL
В операторе SELECT можно использовать агрегаторные функции, которые дают единственное значение для целой группы строк в столбце.
SUM, MIN, MAX, COUNT, AVG, FIRST, LAST
Пример: Найти суммарную поставку деталей
SELECT SUM(volume) AS sum1
FROM PD
Возможности использования COUNT
COUNT (<имя столбца>) – количество не NULL значений
COUNT (*) – всех значений
Ограничить множество строк, к которому в последствии будет применяться агрегатная функция, позволяет функция WHERE.
Строки сначала отбираются в соответствии с критерием, указанным после WHERE, а затем к выбранным строкам применяется агрегатная функция.
Пример: Вывести максимальную поставку деталей
SELECT MAX (volume) AS max1
FROM PD
WHERE dnum=1
Использование предложения GROUP BY
Позволяет разбить любую таблицу на логические группы и применяет агрегатные функции к каждой из этих групп. В результате получается единственное значение для каждой из групп.
Пример: Вывести суммарное количество деталей, поставляемое каждым поставщиком
SELECT pname, SUM (volume) AS sum1
FROM PD
GROUP BY pnum
Если предложение GROUP BY идёт после WHERE, то группы будут созданы из строк, выбранных предложением WHERE.
Пример: Для каждой из деталей 1 и 2 определить суммарный объём поставок и количество поставок:
SELECT dnum, SUM(volume) AS sum1, count(pnum) AS count1
FROM PD
WHERE dnum=1 OR dnum=2
GROUP BY dnum
Чтобы организовать вложенные группировки в предложении GROUP BY следует указать несколько группировочных столбцов.
Все имена полей, перечисленные после слова SELECT должны присутствовать и в предложении GROUP BY, за исключением тех столбцов, когда имя столбца является аргументом агрегатной функции. Хотя в GROUP BY могут использоваться имена столбцов, не перечисленных на вывод.
Агрегатные функции нельзя вкладывать друг в друга.
Предложение HAVING
Определяет условие поиска для группы или статистического выражения..
Выполнение HAVING похоже на WHERE, но WHERE исключает строки до выполнения группировки, а HAVING - после группировки.
Поэтому в предложении HAVING можно использовать агрегатные функции.
Пример: Определить номера поставщиков, которые в сумме поставляют более 500 деталей
SELECT pnum, SUM(volume) AS sum1
FROM PD
GROUP BY pnum
HAVING SUM(volume)>500
Пример: Вывести номера поставщиков, которые поставляют только 1 вид деталей
SELECT pnum, COUNT(dnum) AS count1
FROM PD
GROUP BY pnum
HAVING COUNT(dnum)=1
Предложение ORDER BY позволяет выполнить сортировку результата в соответствии со значениями одного или нескольких столбцов.
ASC – возрастающий порядок.
DESC – убывающий порядок сортировки.
Пример:
SELECT *
FROM PD
ORDER BY dnum ASC, colume DESC
Стандарт SQL требует, чтобы при сортировке NULL значения трактовались либо как превосходящее значение, либо как наименьшее.
Так как конкретный вариант в стандарте не оговаривается, то в зависимости от использующейся СУБД при сортировке NULL значения могут располагаться раньше всех, либо внизу результата.
Выбор данных из нескольких таблиц
При выборе данных из нескольких таблиц может возникать ситуация, когда используются столбцы таблиц, имеющие одинаковые имена. В этом случае используются составные имена столбцов.
<имя таблицы>.<имя столбца>
SELECT P.pnum, D.dnum
FROM P, D
Стандарт SQL подразумевает 2 способа соединения таблиц:
· условие соединения указывается в предложении WHERE;
· условие соединения указывается в предложении FROM.
PD – объём поставки
| pnum | dnum | Volume |
Пример: Пусть требуется вывести информацию в виде:
Pname, dnum, volume
SELECT pname, dnum, volume
FROM P, PD
WHERE P.pnum=PD.pnum
Недостаток: Формируется набор записей, к которым нельзя применять операции вставки, удаления, изменения.
SELECT <список столбцов>
FROM <таблица 1>
INNER JOIN / LEFT JOIN/ RIGHT JOIN <таблица 2> ON <таблица 1>.<столбец 1>=<таблица 2>.<столбец 2>
В соответствии с приведённым синтаксисом различают 3 вида соединения:
1. Внутреннее левое соединение.
2. Внутренне правое соединение.
3. Внешнее соединение.
SELECT pname, dnum, volume
FROM P INNER JOIN PD
ON P.pnum=PD.pnum
При мер: Требуется получить результирующую таблицу
Q(pname, dname, volume)
SELECT pname, dname, volume
FROM P, D, PD
WHERE P.pnum=PD.pnum AND D.dnum=PD.dnum
Д./з. Сделать то же самое с применением INNER JOIN
Пусть требуется получить таблицу следующего вида:
Q(pnum, dnum, dprice, volume, dprice1=const*volume*dprice)
Const=28%
SELECT pnum, dnum, dprice, volume, (volume*dprice*0,28) as dprice1
FROM D, PD
WHERE D.dnum=PD.dnum
Объединение таблиц или подзапросов
Для этих целей используется оператор UNION.
TABLE <имя таблицы>
TABLE P UNION TABLE P1
Или
SELECT * FROM P
UNION [ALL]
SELECT * FROM P1
Если после UNION указать ALL, то сохранятся дубликаты строк.
Объединяемые таблицы или запросы должны быть совместимы по типу.
В терминологии SQL это означает одинаковое количество столбцов. Правило объединения таблиц и запросов:
1. При объединении данных из столбцов с различными именами результирующему столбцу присваивается имя столбца из первой таблице при запросе.
2. Если типы данных в столбцах не совпадают, то для результирующего столбца подбирается тип, совместимый с исходными типами.
Пример: Получить номера деталей, если их цена > 20 или суммарное количество поставленных деталей > 100
SELECT dnum
FROM PD
GROUP BY dnum
HAVING SUM(volume)>1000
UNION
SELECT dnum
FROM D
WHERE dprice>20
Пример: Вывести информацию о деталях. В том случае, если цена детали не указана, вывести фразу цена неизвестна.
SELECT *
FROM В
WHERE dprice IS NOT NULL
UNION
SELECT dnum, dname, dprice=”цена неизвестна”
FROM D
WHERE dprice IS NULL
Пример: Вывести информацию о деталях, в соответствии с таблицей преобразования цен:
| dprice | discount |
| <15 | 5% |
| 15..25 | 10% |
| >25 | 15% |
Схема: (dname, oldPrice, discount, newPrice)
SELECT dname, oldprice AS dprice, discount =”5%”, newProce=dprice*0,95
FROM D
WHERE dprice<15
UNION
SELECT dname, oldprice AS dprice, discount =”10%”, newProce=dprice*0,9
FROM D
WHERE dprice BETWEEN (10,25)
UNION
SELECT dname, oldprice AS dprice, discount =”15%”, newProce=dprice*0,85
FROM D
WHERE dprice>25
В стандарте SQL определены операции пересечения INTERSECT и разности EXEPT, которые выполняются аналогично объединению.
Однако большинством СУБД они не поддерживаются.
Часто невозможно реализовать задачу с помощью одного запроса. В таких случаях используются подзапросы, представляющие собой операторы SELECT, внедрённые в тело других операторов.
Содержание каждого подзапроса подчиняется тем же правилам, что и содержание простого оператора SELECT.
Внешний оператор использует результат выполнения внутреннего оператора для определения окончательного результата. По количеству возможных значений различают
· cкалярные подзапросы, возвращающие единственное значение;
· табличные подзапросы, возвращающие множество значений.
По способу выполнения различают
· простые подзапросы – может выполняться независимо от внешнего запроса;
· сложные запросы – не могут выполняться независимо от внешнего запроса.
Выполнение начинается с внешнего запроса, затем для каждой выбранной строки выполняется подзапрос.
Пример скалярного подзапроса: Определить наименование деталей, цена которых больше цены болта
SELECT dname
FROM D
WHERE dprice> (SELECT dprice
FROM D
WHERE dname=”болт”)
Пример табличного подзапроса: Вывести наименование деталей, цена которых больше средней цены деталей
SELECT dname
FROM D
WHERE dprice>(SELECT AVG(dprice)
FROM D)
Пример: Определить наименование поставщика, который выполнил поставку с максимальным объёмом
SELECT pname
FROM P
WHERE pnum=(SELECT pnum
FROM PD
WHERE MAX(volume))
Пример: Вывести следующую информацию о деталях: наименование, цена, отклонение от минимальной цены. Без подзапроса.
SELECT dname, dprice, (dprice-min(dprice)) AS dif
FROM D
Пример: Вывести номер поставщиков, суммарный объём которых > 100 деталей
SELECT pnum, (SELECT SUM(volume)
FROM PD
GROUP BY pnum
HAVING SUM(volume)>100) AS volume1
FROM PD
Табличные подзапросы
Если подзапрос возвращает множество значений, то его результат следует обрабатывать специальным образом. Для этого существуют следующие специальные операции: IN, ANY, SOME, ALL
Использование операции IN
Операция IN осуществляет проверку на принадлежность значения множеству.
Пример: Определить наименование поставщика, которые поставляют детали
SELECT pname
FROM P
WHERE pnum IN (SELECT pnum
FROM PD)
Пример: Определить наименования поставщиков, которые не поставляют деталей
SELECT pname
FROM P
WHERE pnum NOT IN (SELECT pnum
FROM PD)
Использование операций ANY, SOME, ALL
Такие операции могут использоваться с подзапросами, возвращающими 1 столбец значений. Если подзапросу предшествует слово ANY, то условие считается выполненным, когда оно выполняется хотя бы для одного из значений в результирующем столбце подзапроса.
Если подзапросу предшествует слово ALL, то условие считается выполненным, если оно выполняется для всех условий подзапроса.
Слово SOME является синонимом слова ANY.
Пример: Определить наименование деталей, которые входят в поставки со стоимостью, не превышающей среднюю стоимость поставок на 20%.
SELECT dname
FROM D
WHERE dnum =(SELECT DISTINCT dnum
FROM D INNER JOIN PD ON D.dnum=PD.dnum
WHERE volume*dprice<=(SELECT 1,2*volume*dprice
FROM D INNER JOIN PD ON D>dnum=PD.dnum))
Пример: Определить наименование поставщиков, которые в сумме поставляют больше, чем поставщик Петров.
SELECT pname
FROM P INNER JOIN PD ON P.pnum=PD.pnum
GROUP BY pname
HAVING SUM(PD.volume)>(SELECT SUM(volume)
FROM P INNER JOIN PD ON P.pnum=PD.pnum
WHERE P.pname=”Петров”)
Использование операторов EXISTS и NOT EXISTS
Результат выполнения операций EXISTS и NOT EXISTS равен TRUE или FALSE. Для EXISTS результат равен TRUE, если в возвращаемой подзапросом таблице присутствует хотя бы одна строка. Если строк в таблице нет, то возвращается FALSE.
Для операции NOT EXISTS используется обратное правило. Так как обе операции проверяют лишь наличие строк в таблице, то эта таблица может содержать произвольное количество столбцов в таблице.
Пример: Определить поставщиков, которые поставляют детали
SELECT pname
FROM P
WHERE EXISTS (SELECT *
FROM P, PD
WHERE P.pnum=PD.pnum)
Использование операции TOP
При использовании операции TOP результирующий набор записей, полученный после выполнения запроса, ограничивается первыми n строками от общего количества строк результата.
Синтаксис:
SELECT TOP(2[PERCENT]) <список столбцов>
Пример: Вывести первые 2 номера поставщиков.
SELECT TOP(2) pnum
FROM P
Пример: Вывести наименование двух деталей с наименьшей ценой.
SELECT TOP(2)
FROM D
ORDER BY dprice ASC
Запросы модификации данных
В стандарте SQL определены следующие модификаторы данных:
1. INSERT INTO – запрос на добавление.
2. UPDATE – запрос на обновление.
3. DELETE – запрос на удаление.
Оператор INSERT INTO применяется для добавления записей в существующую таблицу. Определены 2 формы синтаксиса данного оператора:
1. INSERT INTO <таблица-приёмник> [<список столбцов>] VALUES <список значений>
Пример: Добавить в таблицу D информацию о новой детали.
INSERT INTO D (dnum, dname, dprice) VALUES(5, “Шайба”, 20)
Если перечислены значения всех столбцов и они включены в список в том же порядке, в котором следует описание таблицы, то список столбцов таблицы можно не указывать.
2. Позволяет скопировать в указанную таблицу строки, которые выбраны оператором SELECT
INSERT INTO <таблица-приёмник>
<SELECT – оператор>
Таблица, возвращаемая оператором SELECT должна быть совместимой по типу с приёмником (должна иметь одинаковое количество столбцов).
Пример: Записать в следующую таблицу PD1(pnum,dnum,volume) информацию о поставках детали 1.
INSERT INTO PD1
SELECT *
FROM PD
WHERE dnum=1
Запросы на обновление реализуются с помощью оператора UPDATE, который используется для изменения значений одного или нескольких значений.
Синтаксис:
UPDATE <имя таблицы>
SET <значение>
[WHERE <спецификация выбора строк>]
Пример: Увеличить цену на 10% детали болт.
UPDATE В
SET dprice=1,1*dprice
WHERE dname=”Болт”
Пример: Увеличить цену на 10% деталей, которые поставляются.
UPDATE D
SET dprice=1,1dprice
WHERE PD.volume IS NOT NULL
В большинстве современных СУБД синтаксис UPDATE расширен.
UPDATE <имя таблицы>
SET …
FROM <имя таблицы>
[WHERE <спецификация выбора строк>]
Пример: Уменьшить на 10% цену тех деталей, суммарный объём которых <500 шт.
UPDATE D
SET dprice=0,9dprice
FROM PD
WHERE dnum IN (SELECT dnum
FROM PD
GROUP BY dnum
HAVING SUM(volume)<500)
Запросы на удаление выполняются с помощью DELETE, который позволяет удалить 1 или несколько строк из таблицы.
DELETE
FROM <имя таблицы>
[WHERE <спецификация выбора строк>]
Пример: Удалить из таблицы D детали, которые не поставляются.
DELETE
FROM D
WHERE dnum NOT IN (SELECT dnum
FROM PD)
|
|
|
