 |
Подзапросы. Классификация подзапросов. Реализация операций вычитания и пересечения посредством подзапросов.
|
|
|
|
Создание таблиц посредством CREATE TABLE. Определение ограничений целостности NOT NULL, DEFOULT, CHECK, PRIMARY KEY, FOREIGN KEY.
Создание таблиц выполняется с помощью команды CREATE TABLE
CREATE TABLE <TABLE-NAME>
(<COLUMN name> <DATA type>[(<SIZE>)],
<COLUMN name> <DATA type> [(<SIZE>)]...);
Создать таблицу P, с информацией о поставщиках
CREATE TABLE P
(pnum int, pname char(10))
При создании таблиц могут быть введены определения на вводимые значения. При этом при попытке ввода несовместимых данных будет выведена ошибка. По своей сути ограничения могут быть на один столбец и на неск. столбцов.
Ограничение на один столбец указывается после типа данных в объявлении столбца. Ограничения на несколько столбцов(всю таблицу), указываются после объявления последнего столбца таблицы.
Типы ограничений:
1. NOT NULL
Запрещает ввод NULL значения. Такое ограничение может быть задано только на один столбец.
(pnum int NOT NULL, pnum char(20) NOT NULL)
2. DEFOULT
Ограничение умолчания. Задается только как ограничение на столбец.
dprice money DEFOULT 0
3. CHECK
Ограничение этого типа задает множество значений атрибутов. Может быть задано как на столбец, так и на всю таблицу.
dprice money CHECK (dprice<50)
dname char(20) CHECK (dname like [A-Я, а-я]%
4. PRIMARY KEY
Это специальный случай комбинации NOT NULL и UNIQUE. Простой первичный ключ задается ограничением на один столбец.
CREATE TABLE P
(pnum int PRIMARY KEY, pname char(10) not NULL UNIQUE)
5. FOREIGN KEY
Ограничение внешних ключей определяется в начальной таблице и определяет ее связь с родительской.
CREATE TABLE PD
(pnum int,
dnum int,
volume int not null)
CONSTRAINT PK_PD
PRIMARY KEY (pnum, dnum)
CONSTRAINT FK_PD_P
FOREIGN KEY pnum REFERENCES P(pnum)
CONSTRAINT FK_PD_D
FOREIGN key clnum REFERENCES D(dnum)
Выборка данных с помощью оператора SELECT. Использование агрегатных функций в операторе SELECT.
|
|
|
Этот оператор позволяет производить выборку данных из таблиц и преобразовывать к нужному виду полученные результаты. Результатом выполнения оператора SELECT является таблица, к этой таблице вновь может быть применен оператор SELECT, таким образом запросы могут быть вложенными.
Синтаксис
SELECT <список столбцов>
FROM<список таблиц>
[WHERE<условия выбора строк>]
[GROUP BY<условия группировки строк>]
[HAVING<условия выбора групп>]
[ODER BY<условия сортировки>]
В предложении SELECT перечисляются столбцы, значения которых будут входить в результирующую таблицу. Столбцы размещаются в том же порядке в котором они указаны в SELECT. Если имя столбца содержит разделители, то оно записывается в квадратных скобках. Если столбцы разных таблиц имеют одинаковые имена то такие имена записываются как составные:
<имя таблицы>.<имя столбца>
В предложении FROM перечисляются имена таблиц которые содержат столбцы указанные в предложении SELECT.
Получить всю информацию о деталях
SELECT dnum, dname, dprice
FROM D
Список всех столбцов заменяет символ *
После служебного слова WHERE указывается условия выбора строк помещаемых в результирующую таблицу, могут быть указаны различные типы условий:
<,>,<=,>=,=,<>, OR, AND
Получить информацию о поставщиках иванов и петров.
SELECT *
FROM P
WHERE pname = ‘Иванов’ OR pname=’Петров’
Проверка на принадлежность множеству выполняется с помощью операции IN
WHERE pname IN (‘Иванов’’Петров’)
Проверка на принадлежность диапазону BETWEEN. Операция определяет max и min границы диапазона в который должно попасть значение. Обе границы считаются принадлежащими диапазону.
Вывести информацию о деталях от 10 до 20р.
SELECT*
FROM D
WHERE dprice BETWEEN 10 AND 20
Использование агрегатных функций.
SELECT может иметь агрегатные функции которые дают единственное значение для целой группы строк таблицы.
|
|
|
Агрегатная функция записывается в виде:
<имя функции>(<имя столбца>)
Используются следующие функции:
· SUM – возвращает сумму значений столбца
· MIN – возвращает минимальное значение
· MAX – возвращает максимальное значение
· FIRST – возвращает первое значение в столбце
· LAST – возвращает последнее значение в столбце
· COUNT – возвращает количество значений в столбце
· AVG – возвращает среднее значение
Пример
Вывести общее количество поставляемых деталей.
SELECT SUM(volume) as sum
FROM PD
Оператор объединения UNION. Оператор пересечения INTERSECT. Оператор вычитания EXEPT.
UNION
Оператор указывается между запросами. В упрощенном виде это выглядит следующим образом:
<запрос1> UNION [ ALL ]<запрос2> UNION [ ALL ]<запрос3>.....;По умолчанию любые дублирующие записи автоматически скрываются, если не использовано выражение UNION ALL.
Необходимо отметить, что UNION сам по себе не гарантирует порядок строк. Строки из второго запроса могут оказаться в начале, в конце или вообще перемешаться со строками из первого запроса. В случаях, когда требуется определенный порядок, необходимо использовать выражение ORDER BY.
Существуют два основных правила, регламентирующие порядок использования оператора UNION:
· Число и порядок извлекаемых столбцов должны совпадать во всех объединяемых запросах;
· Типы данных в соответствующих столбцах должны быть совместимы.
Использование UNION при выборке из двух таблиц
Даны две таблицы:
| sales2005 | |
| person | amount |
| Иван | |
| Алексей | |
| Сергей |
| sales2006 | |
| person | amount |
| Иван | |
| Алексей | |
| Петр |
При выполнении следующего запроса:
SELECT * FROM sales2005
UNION
SELECT * FROM sales2006;
получается результирующий набор, однако порядок строк может произвольно меняться, поскольку ключевое выражение ORDER BY не было использовано:
| person | amount |
| Иван | |
| Алексей | |
| Сергей | |
| Иван | |
| Петр |
EXEPT|INTERSECT
<запрос1>
{ EXCEPT | INTERSECT }
<запрос2>
Оператор EXCEPT возвращает все различные значения, возвращенные левым запросом и отсутствующие в результатах выполнения правого запроса.
|
|
|
Оператор INTERSECT возвращает все различные значения, входящие в результаты выполнения, как левого, так и правого запроса.
Основные правила объединения результирующих наборов двух запросов с оператором EXCEPT или INTERSECT таковы:
· количество и порядок столбцов должны быть одинаковыми во всех запросах;
· типы данных должны быть совместимыми.
Примеры
Вывести N деталей, которые поставляются 1 и 2 поставщиками.
SELECT dnum
FROM PD
WHERE pnum=1
INTERSECT
SELECT dnum
FROM PD
WHERE dnum = 2
Вывести N поставщиков, которые сейчас не поставляют детали:
SELECT pnum
FROM P
EXCEPT
SELECT pnum
FROM PD
Подзапросы. Классификация подзапросов. Реализация операций вычитания и пересечения посредством подзапросов.
Представляет собой оператор, вложенный в тело другого оператора. Кодирование подзапроса подчиняется тем же правилам, что и кодирование простых запросов. Внешние операции используют результат выполнения внутреннего оператора для определения окончания результата. По количеству возвращаемых значений подзапросы можно разделить на
· скалярные – возвращает одно значение
· табличные – возвращают множество значений
по способу выполнения запроса:
· простые
· сложные
Простой запрос может рассматривается независимо от внешнего запроса СУБД выполняет такой подзапрос один раз, а затем помещают его результат во внешнем запросе.
Сложный подзапрос не может рассматриваться независимо от внешнего. Выполнение начинается с внешнего запроса, который отбирает каждую отдельную строку. Для каждой такой строки подзапрос выполняется 1 раз.
Пример простого скалярного подзапроса.
Вывести наименование деталей, цена которых больше цены болта.
SELECT dname
FROM D
WHERE dprice > (SELECT dprice FROM D WHERE dname ‘болт’)
Подзапросы можно использовать не только в предложении WHERE, но и в др. Это зависит от диалекта используемого в СУБД.
Табличные подзапросы.
Такие подзапросы возвращают табл. значений поэтому их результат надо обрабатывать спец. образом. Для этого используют операции IN, ANY(SOME), ALL.
|
|
|
Использование IN
Операция IN осуществляет проверку на принадлежность к значению множества, которое получается после выполнения подзапроса.
Пример.
Определить поставщиков, которые поставляют детали в наст. вр.
SELECT pname
FROME P
WHERE pnum IN(SELECT pnum from PD)
Использование ANY
Условие сравнения считается выполненным когда оно выполняется хотя бы для одного значения, полученного после выполнения подзапроса.
Пример.
Определить наимен. деталей, которые поставляются в наст. вр.
SELECT dname
FROM D
WHERE dnum = ANY(SELECT dnum FROM PD)
Использование ALL и ANY
Если ALL условие сравнения считается выполненным когда всех значений полученных после выполнения подзапроса получено пустое множество.
Пример.
Вывести наимен. деталей с макс. ценой.
SELECT dname
FROM D
WHERE dprice >= ALL(SELECT dprice FROM D).
Примеры сложных табличных подзапросов.
Для их реализации используются операции EXISTS и NOT EXISTS.
Для операции EXISTS результат = true, если в возвращенной подзапросом таблице есть хотя бы одна сторона.
Если результирующая таблица пуста, то EXISTS возвращает значение false.
Для NOT EXISTS исп-ся обратн. правила обработки. Так как обе операции проверяют лишь наличие строк в таблице подзапросов, то эта таблица может содержать производное количество строк.
Пример. Определить поставщиков, которые поставляют детали в настоящее время.
SELECT pname
FROM p
WHERE EXIST(SELECT* FROM PD
WHERE PD.pnum = P.pnum)
Реализация пересечения
В SQL нет отдельного оператора реализации пересечения. Поэтому это делается с помощью подзапросов.
Пример.
Вывести наименование поставщиков из таблицы P для которых не существует деталей в таблице D записи о которых не существует в таблице PD.
SELECT pname
FROM P
WHERE NOT EXIST(Select dname FROM D Where NOT EXISTS(Select * FROM PD
WHERE PD.pnum = P.pnum AND pd.dnum = d.dnum)
|
|
|
