Построение запросов к бд и использование статистических функций

Запросы на выборку конструируются с помощью команды SELECT, которая имеет следующий синтаксис:

SELECT */{DISTINCT/ALL]<список полей>.,…}

FROM {<имя таблицы>/[<алиас>]}.,...

WHERE [<предикат>]

[GROUP BY {<имя столбца>/<целое>},..]

HAVING[<предикат>]

ORDER BY [{<имя столбца>/<целое>}.,..]

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

«Имя таблицы» - задается имя или синоним таблицы или представления.

«Алиас» - временный синоним имени таблицы, которое определяется и используется только в одной команде.

«Предикат» - условие, которое может задаваться для каждого столбца либо для комбинации столбцов.

«Целое» - число, которое определяет значение из списка выбираемых полей, указывая его расположение в этом списке.

Аргумент DISTINCT позволяет исключить дублирования значений из результата.

ALL принимает значения по умолчанию и оказывает противоположное действие аргументу DISTINCT.

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

Предложение GROUP BY позволяет группировать столбцы в таблице.

Предложение ORDER BY позволяет упорядочить множество столбцов один внутри другого.

В примерах будет использоваться реляционная БД, состоящая из трех таблиц.

Таблица 1. Salespeople (продавцы)

snum	sname	city	comm.
	Peel	London	.12
	Serres	Sanhose	.15
	Motica	London	.11
	Rifkin	Barselona	.15
	Axelrod	New York	.10

Таблица 2. Customers(Покупатели)

cnum	cname	city	rating	snum
	Hoffman	London
	Giovamn	Rome
	Lia	Sanhose
	Grass	Berlin
	Cleimes	London
	Cisneros	Sanhose
	Rersira	Rome

Таблица 3. Ordes (заявки).

onum	amt	odate	cnum	snum
	18.69	10.03.1990
	767.19	10.03.1990
	1900.10	10.03.1990
	5160.45	10.03.1990
	1098.16	10.03.1990
	1713.23	10.04.1990
	75.75	10.04.1990
	4723.00	10.05.1990
	1309.95	10.06.1990
	9891.88	10.06.1990

Таблица 1: snum – уникальный номер продавца, sname – имя продавца, city – место продаж, comm – вознаграждение.

Таблица 2: cnum – номер покупателя, rating – определяет уровень предпочтения покупателя.

Таблица 3: onum – уникальный номер заявки, amt – сумма заявки, odate – дата заявки, cnum - номер покупателя, snum – номер продавца.

SELECT * FROM Salespeople;

получена полностью таблица продавцы.

SELECT snum, comm FROM Salespeople;

выдается два столбца таблицы Продавцы.

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

SELECT DISTINCT snum FROM Orders;

Получим 5 значений.

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

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

SELECT sname, city FROM Salespeople

WHERE city = ‘London’;

Результат: Peel London & Motica London.

Получить сведения обо всех покупателях, которые либо проживают в Лондоне, либо имеют рейтинг больше чем 200.

SELECT * FROM Customers

WHERE city = ‘London’ OR rating > 200;

Результат: строки 1, 4, 5 и 6.

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

SELECT * FROM Salespeople

WHERE city IN (‘Barcelona’, ‘London’)

Можно в предложении WHERE задавать границы, в которых лежит значение:

SELECT * FROM Salespeople

WHERE comm BETWEEN.10 AND.12;

Можно задавать условие, используя оператор LIKE:

SELECT * FROM Customers

WHERE cname LIKE ‘G*’;

Результат: 2 и 4 записи.

* - любое количество символов, _ - один символ.

Для нахождения полей с пропущенными значениями можно воспользоваться оператором IS NULL:

SELECT * FROM Customers

WHERE city IS NULL;

Для обобщения данных в SQL предусмотрены следующие функции агрегирования (статистические функции):

COUNT – определяет количество значений, выбранных посредством запроса и не являющимися NULL.

SUM – вычисляет арифметическую сумму всех выбранных значений одного поля.

AVG – вычисляет среднее значение для выбранных значений данного поля.

MAX – наибольшее из выбранных значений данного поля

MIN – наименьшее из выбранных значений данного поля.

Чтобы найти сумму, на которую сделаны заявки:

SELECT SUM(amt) FROM Orders;

Подсчитать число продавцов, имеющих заказы:

SELECT COUNT(DISTINCT snum) FROM Orders;

Результат: 5.

Для подсчета общего числа строк, например, в таблице покупатели:

SELECT COUNT(*) FROM Customers;

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

SELECT snum, MAX(amt) FROM Orders

GROUP BY snum;

Результат: 1098.16 для 1007

9891.98 для 1001

1900.10 для 1004

5160.45 для 1002

1713.23 для 1003.

Можно этот запрос расширить и просматривать наибольший заказ на каждую дату:

SELECT snum, MAX(amt), odate

FROM Orders

GROUP BY snum, odate;

Можно в дополнение к предыдущему запросу включить предложение HAVING, который будет выдавать заявки превышающие 3000.00:

HAVING MAX(amt) > 3000.00;

В предложении WHERE использовать агрегированные функции нельзя.

Дата добавления: 2015-10-16