Языки бд. 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
		1	Иванов
		2	Петров
		3	Сидоров
		4	Кузнецов
Детали	D	dnum	dname	Dprice
		1	Болт	10
		2	Гайка	20
		3	Винт	30
		4	Шуруп	40

Пример: Вывести информацию о винтах и болтах:

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
1	1	300
2	2	500
3	3	600
4	4	700

Пример: Пусть требуется вывести информацию в виде:

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)