Bazy_dannykh_Uchebnik_novy

используемые чтобы удалять определенные группы из вывода, точно также как предложение WHERE делает это для индивидуальных строк. Аргументы в предложении HAVING следуют тем же самым правилам, что и в предложении

SELECT, состоящем из команд использующих GROUP BY. Они должны иметь одно значение на группу вывода.

В операторе HAVING можно использовать агрегатные функции IN, BETWEEN.

Пример 38:

Чтобы увидеть максимальную стоимость приобретений свыше $3000.00,

необходимо сделать запрос

SELECT Покупатель, Дата, MAX (Стоимость) FROM ЗАКАЗЫ

GROUP BY Покупатель, Дата

HAVING MAX (Стоимость) > 3000.00;

---------------------------------------------------------------------------------------------------------

Продавец Дата MAX (Стоимость)

---------------------------------------------------------------------------------------------------------

Пример 39:

Предположим, что мы хотим получить сгруппированный отчет по всем заказам,

сделанным 10.03.1988.

SELECT Покупатель, MAX (Стоимость) FROM ЗАКАЗЫ

GROUP BY Покупатель

HAVING Дата = 10/03/1988;

Некорректный запрос - поле odate нельзя использовать в операторе HAVING, т.к. оно

имеет больше, чем одно значение на группу вывода. Корректная форма запроса

SELECT Покупатель, MAX (Стоимость) FROM ЗАКАЗЫ

WHERE Дата = 10/03/1990

GROUP BY Покупатель;

----------------------------------------------------------------------------------------------------------

Продавец MAX (Стоимость)

----------------------------------------------------------------------------------------------------------

Предложение ORDER BY (СОРТИРОВАТЬ ПО )

81

Таблицы - это неупорядоченные наборы данных, и данные, которые выходят из них, не обязательно появляются в какой-то определенной последовательности. SQL использует команду ORDER BY, чтобы позволять вам упорядочивать ваш вывод. Эта команда упорядочивает вывод запроса согласно значениям в том или ином количестве выбранных столбцов.

Многочисленные столбцы упорядочиваются один внутри другого, также как с GROUP BY, и вы можете определять возрастание (ASC) или убывание (DESC) для каждого столбца.

Пример 40:

Предположим, мы хотим отобрать все данные из таблицы ПРОДАВЦЫ,

упорядоченные по фамилии продавца. Для этого надо выполнить следующий запрос.

SELECT *

FROM ПРОДАВЦЫ

ORDER BY ФИО;

По умолчанию установлено возрастание – режим ASC. Упорядочение может быть выполнено по нескольким столбцам, перечисленным через запятую, и по порядковому номеру столбца в списке вывода.

Пример 41:

Предположим, мы хотим отобрать все данные из таблицы ЗАКАЗЫ,

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

SELECT * FROM ЗАКАЗЫ

ORDER BY Покупатели DESC, Стоимость DESC;

----------------------------------------------------------------------------------------------------------

Покупатель Стоимость

----------------------------------------------------------------------------------------------------------

82

Обратите внимание, что выводится код покупателя в таблице ЗАКАЗЫ, что узнать фамилию покупателя необходимо выполнить связанный запрос (см. ниже).

Пример 42:

ORDER BY может, кроме того, использоваться с GROUP BY для упорядочения групп. Если это так, то ORDER BY всегда приходит последним.

SELECT Покупатель, Дата, MAX (Стоимость) FROM ЗАКАЗЫ

GROUP BY Покупатель, Дата ORDER BY Покупатель;

Пример 43:

Вместо имен столбцов вы можете использовать их порядковые номера для указания поля, используемого в упорядочении вывода. Эти номера могут ссылаться не на порядок столбцов в таблице, а на их порядок в выводе.

SELECT sname, comm FROM Salespeople GROUP BY 2 DESC;

Форматирование вывода

Предположим, что вы хотите выполнять простые числовые вычисления данных,

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

Пример 44:

Например, вы можете пожелать, представить комиссионные вашего продавца в процентах, а не как десятичные числа. Выполняем запрос

SELECT Код, ФИО, Город, Комиссия * 100

FROM ПРОДАВЦЫ;

----------------------------------------------------------------------------------------------------------

Код ФИО Город Комиссия * 100

----------------------------------------------------------------------------------------------------------

Пример 45:

Вы можете усовершенствовать предыдущий пример, представив комиссионные как

проценты со знаком процента (%).

SELECT Код, ФИО, Город, Комиссия * 100, '%’

FROM ПРОДАВЦЫ;

83

----------------------------------------------------------------------------------------------------------

Код ФИО Город Комиссия * 100

----------------------------------------------------------------------------------------------------------

Пример 46:

Предположим, что вы генерируете вывод для отчета, который бы указывал число

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

SELECT ' На ', Дата, ', получено ', COUNT (DISTINCT Стоимость), 'заказов.' FROM ЗАКАЗЫ

GROUP BY Дата;

---------------------------------------------------------------------------------------------------------

' На ', Дата, ', получено ' COUNT (DISTINCT Стоимость), 'заказов.'

----------------------------------------------------------------------------------------------------------

Объединение таблиц

Одна из наиболее важных особенностей запросов SQL - это их способность определять связи между различными таблицами и выводить информацию из них в терминах этих связей, всю внутри одной команды. Этот вид операции называется - объединением, которое является одним из видов операций в реляционных базах данных.

Используя объединения, мы непосредственно связываем информацию с любым номером таблицы, и таким образом способны создавать связи между сравнимыми фрагментами данных. При объединении, таблицы, представленные списком в предложении FROM

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

Полное имя столбца таблицы фактически состоит из имени таблицы, сопровождаемого точкой и затем именем столбца. Имеются несколько примеров имен: Salespeople.snum, Salespeople.city, Orders.odate. Наиболее эффективно работают запросы, объединенные с помощью связей – с помощью механизма ссылочной целостности.

Пример 47:

SELECT ПОКУПАТЕЛИ.ФИО, ПРОДАВЦЫ.ФИО, ПРОДАВЦЫ. Город FROM ПОКУПАТЕЛИ, ПРОДАВЦЫ

84

WHERE ПРОДАВЦЫ.Город = ПОКУПАТЕЛИ.Город;

----------------------------------------------------------------------------------------------------------

ПОКУПАТЕЛИ.ФИО ПРОДАВЦЫ.ФИО ПРОДАВЦЫ. Город

----------------------------------------------------------------------------------------------------------

Пример 48:

Предположим, мы хотим отобрать все данные из таблицы ЗАКАЗЫ,

упорядоченные по фамилии покупателя и стоимости заказа. Для этого надо

выполнить следующий запрос.

SELECT ПОКУПАТЕЛИ.ФИО, ЗАКАЗЫ.Стоимость FROM ПОКУПАТЕЛИ, ЗАКАЗЫ

WHERE (ЗАКАЗЫ.Покупатель = ПОКУПАТЕЛИ.Код) ORDER BY 1 DESC, 2 DESC;

----------------------------------------------------------------------------------------------------------

Покупатели Стоимость

----------------------------------------------------------------------------------------------------------

Пример 49:

Объединение трех таблиц. Предположим, вы хотите узнать кто приобретал и кто

покупал заказы, на какую сумму, и какая комиссия причитается продавцу.

SELECT Код, Продавец.ФИО, Покупатель.ФИО, ЗАКАЗЫ.Стоимость, ПРОДАВЦЫ.Комиссия * ЗАКАЗЫ.Сумма

FROM ПРОДАВЦЫ, ПОКУПАТЕЛИ, ЗАКАЗЫ WHERE ЗАКАЗЫ.Покупатель = ПОКУПАТЕЛИ.Код

AND ЗАКАЗЫ.Продавец = ПРОДАВЦЫ.Код;

---------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------

3001 Плеханов Данилевский 18.69 2,8

85

Соединения таблиц в запросах

Определяются в предложении FROM cогласно спецификации:

SELECT …

FROM <таблица 1> <вид соединения > JOIN < таблица 2> ON <условие поиска>;

Операция соединения таблиц полностью соответствует реляционной операции

-соединения.

Во многих реализациях SQL имеются ключевые слова INNER JOIN – внутреннее соединение и OUTER JOIN – внешнее соединение. При этом INNER JOIN отбирает все записи таблиц, если <условие поиска> дает непустые значения (NOT NULL) для каждой из таблиц – аналог оператора AND. OUTER JOIN отбирает все записи, из которых таблиц,

для которых <условие поиска> может возвращать пустые значения – аналог оператора

OR.

Пример 50:

Найти всех заказчиков в Заказах на 3-е Октября, т.е покупателей, совершивших хотя бы одну покупку. Одним из способов является выполнение запроса.

SELECT

FROM ПОКУПАТЕЛИ INNER JOIN

ЗАКАЗЫ ON (outer.Покупатель = inner.Код)

----------------------------------------------------------------------------------------------------------

Код ФИО Город Рейтинг Продавец

----------------------------------------------------------------------------------------------------------

Алиасы (псевдонимы)

В стандарте SQL можно для каждому столбцу или таблице присваивать псевдоним – алиас. Алиасы особенно удобно использовать для повторяющихся столбцов или объединения таблицы самой с собой.

86

Пример 51:

Найти все пары заказчиков имеющих один и тот же самый рейтинг. В запросе

таблица соединяется сама с собой и для различения столбцов используется алиас.

Обратите внимание, что наш вывод имеет два значения для каждой комбинации,

причем второй раз в обратном порядке. Это потому, что каждое значение показано

первый раз в каждом псевдониме, и второй раз (симметрично) в предикате.

Следовательно, значение A в псевдониме сначала выбирается в комбинации со

значением B во втором псевдониме, а затем значение A во втором псевдониме

выбирается в комбинации со значением B в первом псевдониме. В нашем примере,

Добролюбов выбрался вместе с Добролюбов, а затем Добролюбов выбрался вместе

с Белинский. Простой способ избежать этого состоит в том, чтобы налагать

порядок на два значения, так, чтобы один мог быть меньше, чем другой или

предшествовал ему в алфавитном порядке. Это делает предикат асимметричным,

поэтому те же самые значения в обратном Заказе не будут выбраны снова,

например:

SELECT первый.ФИО, второй.ФИО, первый.Рейтинг

FROM ПОКУПАТЕЛИ первый, ПОКУПАТЕЛИ второй WHERE первый.Рейтинг = второй.Рейтинг

AND первый.ФИО < второй.ФИО;

----------------------------------------------------------------------------------------------------------

ФИО ФИО Рейтинг

----------------------------------------------------------------------------------------------------------

87

Вложенные запросы

Предположим что мы знаем имя - ФИО продавца - Сидоров, но не знаем его номер код, и

хотим извлечь все его Заказы из таблицы Заказов. Имеется один способ, чтобы сделать

это:

SELECT * FROM ЗАКАЗЫ

WHERE Продавец = (SELECT Код

FROM ПРОДАВЦЫ

WHERE ФИО = 'Сидоров');

---------------------------------------------------------------------------------------------------------

Код Стоимость Дата Покупатель Продавец

---------------------------------------------------------------------------------------------------------

3002 1900.10 10.03.1990 2007 1004

Чтобы оценить внешний (основной) запрос, SQL сначала должен оценить внутренний запрос (или подзапрос) внутри предложения WHERE. Он делает это так, как и должен делать запрос имеющий единственную цель — отыскать через таблицу Продавцов все строки, где поле ФИО равно значению «Сидоров», и затем извлечь значения поля Код

этих строк. Единственной найденной строкой, естественно, будет Код = 1004. Однако

SQL не просто выдает это значение, а помещает его в предикат основного запроса вместо самого подзапроса, так чтобы предиката прочитал, что Код = 1004. Основной запрос затем выполняется как обычно с вышеупомянутыми результатами. Конечно же, подзапрос должен выбрать один и только один столбец, а тип данных этого столбца должен совпадать с тем значением, с которым он будет сравниваться в предикате. Часто, как показано выше, выбранное поле и его значение будут иметь одинаковые имена (в этом случае, Код), но это необязательно. Конечно, если бы мы уже знали номер продавца Сидоров, мы могли бы просто напечатать

WHERE Продавец = 1004

DISTICT в подзапросах

Пример 52:

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

88

SELECT * FROM ЗАКАЗЫ

WHERE Продавец = (SELECT DISTINCT Продавец

FROM ЗАКАЗЫ

WHERE Покупатель = 2004);

----------------------------------------------------------------------------------------------------------

Код Стоимость Дата Покупатель Продавец

----------------------------------------------------------------------------------------------------------

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

Пример 53:

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

выше средней на 4-е Октября. Выполняем запрос.

SELECT * FROM ЗАКАЗЫ

WHERE Стоимость > (SELECT AVG (Стоимость) FROM ЗАКАЗЫ

WHERE Дата = 10/04/1990);

----------------------------------------------------------------------------------------------------------

Код Стоимость Дата Покупатель Продавец

----------------------------------------------------------------------------------------------------------

Синтаксис команды SELECT

SELECT [ DISCTINCT | ALL {* | <выражение 1> [, <выражение 2>… ]}

FROM <таблица1 > [, <таблица2 >… ] [ WHERE <условие_поиска>]

[ GROUP BY столбец [collate collation] [, столбец 1 [collate collation]…]

[ HAVING <условия_поиска>]

[ UNION <оператор_SELECT>]

[ PLAN <план_выполнения_запроса>]

[ ORDER BY <список_столбцов>];

Где:

89

[] – необязательный элемент, {} – множество значений

A | B – выбор из двух значений A или B.

< … > - подставить в текст выражение в угловых скобках.

Основное достоинство этой формы записи – компактность. Читается она следующим образом. Команда начинается с ключевого слова SELECT, далее могут следовать необязательные ключевые слова DISTINCT или ALL, далее следует либо символ *, либо выражения, разделенные запятыми, далее следует ключевое слово FROM, после которого следует список названий таблиц, разделенных запятыми, далее ключевое слово WHERE,

за которым следует выражение, задающее условия поиска … (Остальное – домашнее задание.). Представленная запись не полна, т.к. опущено определение того, что такое выражение и условие поиска. Эти определения на стр. 77.

Ввод информации в базу данных.

Все строки в SQL вводятся с использованием команды модификации INSERT.

В самой простой форме, INSERT использует следующий синтаксис:

INSERT INTO <имя_таблицы> VALUES (<значение>, <значение> . . .);

Вместо списка величин можно использовать вложенный запрос с оператором

SELECT.

Пример 54:

Чтобы ввести строку в таблицу Продавцов, вы можете использовать следующее условие:

INSERT INTO ПРОДАВЦЫ

VALUES (1001, 'Иванов', 'Москва', .12);

Пример 55:

Если часть значений неизвестна, то оператор такой

INSERT INTO ПРОДАВЦЫ

VALUES (1001, 'Иванов', NULL, .12);

Пример 56:

Вместо пустых значений можно указать столбцы

INSERT INTO ПРОДАВЦЫ (Город, ФИО, Код)

VALUES ('Москва', 'Чернышевский', 2001);

Корректировка данных в таблице

90