Отбор данных из одной таблицы

Пример 6. Выбрать все данные из таблицы поставщиков (ключевые слова SELECT … FROM …):

SELECT *

FROM P;

Замечание. В результате получим новую таблицу, содержащую полную копию данных из исходной таблицы P.

Пример 7. Выбрать все строки из таблицы поставщиков, удовлетворяющих некоторому условию (ключевое слово WHERE …):

SELECT *

FROM P

WHERE P.PNUM > 2;

Замечание. В качестве условия в разделе WHERE можно использовать сложные логические выражения, использующие поля таблиц, константы, сравнения (>, <, = и т.д.), скобки, союзы AND и OR, отрицание NOT.

Пример 8. Выбрать некоторые колонки из исходной таблицы (указание списка отбираемых колонок):

SELECT P.NAME

FROM P;

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

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

Пример 9. Выбрать некоторые колонки из исходной таблицы, удалив из результата повторяющиеся строки (ключевое слово DISTINCT):

SELECT DISTINCT P.NAME

FROM P;

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

Пример 10. Использование скалярных выражений и переименований колонок в запросах (ключевое слово AS …):

SELECT

TOVAR.TNAME,

TOVAR.KOL,

TOVAR.PRICE,

"=" AS EQU,

TOVAR.KOL*TOVAR.PRICE AS SUMMA

FROM TOVAR;

В результате получим таблицу с колонками, которых не было в исходной таблице TOVAR:

TNAME	KOL	PRICE	EQU	SUMMA
Болт			=
Гайка			=
Винт			=

Пример 11. Упорядочение результатов запроса (ключевое слово ORDER BY …):

SELECT

PD.PNUM,

PD.DNUM,

PD.VOLUME

FROM PD

ORDER BY DNUM;

В результате получим следующую таблицу, упорядоченную по полю DNUM:

PNUM	DNUM	VOLUME

Пример 12. Упорядочение результатов запроса по нескольким полям с возрастанием или убыванием (ключевые слова ASC, DESC):

SELECT

PD.PNUM,

PD.DNUM,

PD.VOLUME

FROM PD

ORDER BY

DNUM ASC,

VOLUME DESC;

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

PNUM	DNUM	VOLUME

Замечание. Если явно не указаны ключевые слова ASC или DESC, то по умолчанию принимается упорядочение по возрастанию (ASC).

Отбор данных из нескольких таблиц

Пример 13. Естественное соединение таблиц (способ 1 - явное указание условий соединения):

SELECT

P.PNUM,

P.PNAME,

PD.DNUM,

PD.VOLUME

FROM P, PD

WHERE P.PNUM = PD.PNUM;

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

PNUM	PNAME	DNUM	VOLUME
	Иванов
	Иванов
	Иванов
	Петров
	Петров
	Сидоров

Замечание. Соединяемые таблицы перечислены в разделе FROM оператора, условие соединения приведено в разделе WHERE. Раздел WHERE, помимо условия соединения таблиц, может также содержать и условия отбора строк.

Пример 14. Естественное соединение таблиц (способ 2 - ключевые слова JOIN… USING…):

SELECT

P.PNUM,

P.PNAME,

PD.DNUM,

PD.VOLUME

FROM P JOIN PD USING PNUM;

Замечание. Ключевое слово USING позволяет явно указать, по каким из общих колонок таблиц будет производиться соединение.

Пример 15. Естественное соединение таблиц (способ 3 - ключевое слово NATURAL JOIN):

SELECT

P.PNUM,

P.PNAME,

PD.DNUM,

PD.VOLUME

FROM P NATURAL JOIN PD;

Замечание. В разделе FROM не указано, по каким полям производится соединение. NATURAL JOIN автоматически соединяет по всем одинаковым полям в таблицах.

Пример 16. Естественное соединение трех таблиц:

SELECT

P.PNAME,

D.DNAME,

PD.VOLUME

FROM

P NATURAL JOIN PD NATURAL JOIN D;

В результате получим следующую таблицу:

PNAME	DNAME	VOLUME
Иванов	Болт
Иванов	Гайка
Иванов	Винт
Петров	Болт
Петров	Гайка
Сидоров	Болт

Пример 17. Прямое произведение таблиц:

SELECT

P.PNUM,

P.PNAME,

D.DNUM,

D.DNAME

FROM P, D;

В результате получим следующую таблицу:

PNUM	PNAME	DNUM	DNAME
	Иванов		Болт
	Иванов		Гайка
	Иванов		Винт
	Петров		Болт
	Петров		Гайка
	Петров		Винт
	Сидоров		Болт
	Сидоров		Гайка
	Сидоров		Винт

Замечание. Т.к. не указано условие соединения таблиц, то каждая строка первой таблицы соединится с каждой строкой второй таблицы.

Пример 18. Соединение таблиц по произвольному условию. Рассмотрим таблицы поставщиков и деталей, которыми присвоен некоторый статую (см. пример 8 из предыдущей главы):

PNUM	PNAME	PSTATUS
	Иванов
	Петров
	Сидоров

Таблица 1 Отношение P (Поставщики)

DNUM	DNAME	DSTATUS
	Болт
	Гайка
	Винт

Таблица 2 Отношение D (Детали)

Ответ на вопрос "какие поставщики имеют право поставлять какие детали?" дает следующий запрос:

SELECT

P.PNUM,

P.PNAME,

P.PSTATUS,

D.DNUM,

D.DNAME,

D.DSTATUS

FROM P, D

WHERE P.PSTATUS >= D.DSTATUS;

В результате получим следующую таблицу:

PNUM	PNAME	PSTATUS	DNUM	DNAME	DSTATUS
	Иванов			Болт
	Иванов			Гайка
	Иванов			Винт
	Петров			Винт
	Сидоров			Гайка
	Сидоров			Винт

Использование имен корреляции (алиасов, псевдонимов)

Иногда приходится выполнять запросы, в которых таблица соединяется сама с собой, или одна таблица соединяется дважды с другой таблицей. При этом используются имена корреляции (алиасы, псевдонимы), которые позволяют различать соединяемые копии таблиц. Имена корреляции вводятся в разделе FROM и идут через пробел после имени таблицы. Имена корреляции должны использоваться в качестве префикса перед именем столбца и отделяются от имени столбца точкой. Если в запросе указываются одни и те же поля из разных экземпляров одной таблицы, они должны быть переименованы для устранения неоднозначности в именованиях колонок результирующей таблицы. Определение имени корреляции действует только во время выполнения запроса.

Пример 19. Отобрать все пары поставщиков таким образом, чтобы первый поставщик в паре имел статус, больший статуса второго поставщика:

SELECT

P1.PNAME AS PNAME1,

P1.PSTATUS AS PSTATUS1,

P2.PNAME AS PNAME2,

P2.PSTATUS AS PSTATUS2

FROM

P P1, P P2

WHERE P1.PSTATUS1 > P2.PSTATUS2;

В результате получим следующую таблицу:

PNAME1	PSTATUS1	PNAME2	PSTATUS2
Иванов		Петров
Иванов		Сидоров
Сидоров		Петров

Пример 20. Рассмотрим ситуацию, когда некоторые поставщики (назовем их контрагенты) могут выступать как в качестве поставщиков деталей, так и в качестве получателей. Таблицы, хранящие данные могут иметь следующий вид:

Номер контрагента NUM	Наименование контрагента NAME
	Иванов
	Петров
	Сидоров

Таблица 3 Отношение CONTRAGENTS

Номер детали DNUM	Наименование детали DNAME
	Болт
	Гайка
	Винт

Таблица 4 Отношение DETAILS (Детали)

Номер поставщика PNUM	Номер получателя CNUM	Номер детали DNUM	Поставляемое количество VOLUME

Таблица 5 Отношение CD (Поставки)

В таблице CD (поставки) поля PNUM и CNUM являются внешними ключами, ссылающимися на потенциальный ключ NUM в таблице CONTRAGENTS.

Ответ на вопрос "кто кому что в каком количестве поставляет" дается следующим запросом:

SELECT

P.NAME AS PNAME,

C.NAME AS CNAME,

DETAILS.DNAME,

CD.VOLUME

FROM

CONTRAGENTS P,

CONTRAGENTS C,

DETAILS,

CD

WHERE

P.NUM = CD.PNUM AND

C.NUM = CD.CNUM AND

D.DNUM = CD.DNUM;

В результате получим следующую таблицу:

Наименование Поставщика PNAME	Наименование получателя CNAME	Наименование детали DNAME	Поставляемое количество VOLUME
Иванов	Петров	Болт
Иванов	Сидоров	Гайка
Иванов	Сидоров	Винт
Петров	Сидоров	Болт
Петров	Сидоров	Гайка
Сидоров	Иванов	Болт

Замечание. Этот же запрос может быть выражен очень большим количеством способов, например, так:

SELECT

P.NAME AS PNAME,

C.NAME AS CNAME,

DETAILS.DNAME,

CD.VOLUME

FROM

CONTRAGENTS P,

CONTRAGENTS C,

DETAILS NATURAL JOIN CD

WHERE

P.NUM = CD.PNUM AND

C.NUM = CD.CNUM;

Использование агрегатных функций в запросах

Пример 21. Получить общее количество поставщиков (ключевое слово COUNT):

SELECT COUNT(*) AS N

FROM P;

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

N

Пример 22. Получить общее, максимальное, минимальное и среднее количества поставляемых деталей (ключевые слова SUM, MAX, MIN, AVG):

SELECT

SUM(PD.VOLUME) AS SM,

MAX(PD.VOLUME) AS MX,

MIN(PD.VOLUME) AS MN,

AVG(PD.VOLUME) AS AV

FROM PD;

В результате получим следующую таблицу с одной строкой:

SM	MX	MN	AV
			333.33333333

Использование агрегатных функций с группировками

Пример 23. Для каждой детали получить суммарное поставляемое количество (ключевое слово GROUP BY …):

SELECT

PD.DNUM,

SUM(PD.VOLUME) AS SM

GROUP BY PD.DNUM;

Этот запрос будет выполняться следующим образом. Сначала строки исходной таблицы будут сгруппированы так, чтобы в каждую группу попали строки с одинаковыми значениями DNUM. Потом внутри каждой группы будет просуммировано поле VOLUME. От каждой группы в результирующую таблицу будет включена одна строка:

DNUM	SM

Замечание. В списке отбираемых полей оператора SELECT, содержащего раздел GROUP BY можно включать только агрегатные функции и поля, которые входят в условие группировки. Следующий запрос выдаст синтаксическую ошибку:

SELECT

PD.PNUM,

PD.DNUM,

SUM(PD.VOLUME) AS SM

GROUP BY PD.DNUM;

Причина ошибки в том, что в список отбираемых полей включено поле PNUM, которое не входит в раздел GROUP BY. И действительно, в каждую полученную группу строк может входить несколько строк с различными значениями поля PNUM. Из каждой группы строк будет сформировано по одной итоговой строке. При этом нет однозначного ответа на вопрос, какое значение выбрать для поля PNUM в итоговой строке.

Замечание. Некоторые диалекты SQL не считают это за ошибку. Запрос будет выполнен, но предсказать, какие значения будут внесены в поле PNUM в результатирующей таблице, невозможно.

Пример 24. Получить номера деталей, суммарное поставляемое количество которых превосходит 400 (ключевое слово HAVING …):

Замечание. Условие, что суммарное поставляемое количество должно быть больше 400 не может быть сформулировано в разделе WHERE, т.к. в этом разделе нельзя использовать агрегатные функции. Условия, использующие агрегатные функции должны быть размещены в специальном разделе HAVING:

SELECT

PD.DNUM,

SUM(PD.VOLUME) AS SM

GROUP BY PD.DNUM

HAVING SUM(PD.VOLUME) > 400;

В результате получим следующую таблицу:

DNUM	SM

Замечание. В одном запросе могут встретиться как условия отбора строк в разделе WHERE, так и условия отбора групп в разделе HAVING. Условия отбора групп нельзя перенести из раздела HAVING в раздел WHERE. Аналогично и условия отбора строк нельзя перенести из раздела WHERE в раздел HAVING, за исключением условий, включающих поля из списка группировки GROUP BY.

 

Задание на лабораторную работу:

 

1.На языке SQL написать команду создания таблицы с именем TABL1 со следующими характеристиками столбцов и ограничений целостности:

А – целый

В – символьный (25 символов)

С – символьный (3 символа)

 

2. На языке SQL написать команду добавления в таблицу TABL1 столбца со следующими характеристиками:

· Имя столбца – D

· Тип данных – строковый

· 10 символов

 

Контрольные вопросы:

1. Кратко охарактеризуйте технологию создания запроса

2. Что такое запрос на удаление

3. Что такое запрос на изменение

4. Что такое запрос на обновление записей

5. Что такое запрос на создание таблицы

6. Что такое перекрестный запрос

Лабораторная работа № 10

 

Тема: Построение запросов вычисления и подведения