11102010 (Лекции)

SeregaProMai.Narod.ru ©

Лекция от 11.10.2010. (на основе Диминых лекции)

Язык структурированных запросов SQL.

SQL используется для определения объектов базы данных, а так же для манипулирования данными и для управления. Следует различать стандарт языка SQL и его расширения, используемые в различных СУБД. Мы будем рассматривать Transact-SQL из MySQL.

Операнды манипулирования данными:

SELECT - выборка

INSERT - добавление

UPDATE - изменение

DELETE — удаление

Наиболее важным операндом является SELECT, имеющий следующий формат:

SELECT [ALL, DISTINCT]

FROM

[WHERE ]

[ORDER BY поле сортировки [ASC, DESC]]

[CROUP BY поле группировки]

[HAVING условия на группы]

Состоит из текстовых предложений (min из 2; max из 6). Первое предложение содержит список полей (столбцов). По умолчанию SQL не исключает дублирование строк, для исключения дублирования строк в результирующие данные следует указать параметр DISTINCT.

В предложении FROM указываются источники данных. Основными источниками являются таблицы БД, запросы, представления (сохраненный запрос, которому присвоено имя).

Если в запросе используется текстовые таблица источников данных, то необходимо указывать условия соединения, это можно сделать в пределах формы с помощью операнда JOIN или в предложении WHERE.

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

В предложении WHERE содержится условие выбора записей и возможно условие соединения записей в многотабличные запросы.

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

Для группировки записей результата выполнения запроса используется предложение GROUP BY. В результате несколько записей объединяются в одну. Если используется группировка, то в списке столбцов могут быть использованы только те столбцы, что указаны в предложении GROUP BY и статистические функции SUM, MAX, MIN. При наличии группировки можно указывать дополнительные условия группы HAVING.

Пример: Вывести список материалов, из которого изготовлены детали.

SELECT Материал FROM Детали

Ответ: Сталь, железо, медь, медь

SELECT DISTINCT Материал FROM Детали

Ответ: Сталь, железо, медь

Для указания условия выборки можно использовать различные операторы сравнения ( =), для объединения условия — логические операции OR, NOT, AND и специальные операторы.

LIKE 'Шаблон', где шаблон содержит:

% - любая последовательность символов;

_ - один любой символ.

Пример:

Материал LIKE 'C%' - Сталь

Материал LIKE '_e%' - Железо, Медь

Получить данные о поставщиках, имена которых начинаются на букву И или статус > 2

SELECT * FROM Поставщики WHERE имя LIKE 'И%' OR статус >2

IN <список значений>

SELECT * FROM Поставщики WHERE СтатусП IN (1,3)

или IN (запросы)

BETWEEN (начальное значение AND конечное значение)

Пример. Получить данные о поставках, выполненных с 01.01.2010 по 28.02.2010.

SELECT * FROM Поставки

WHERE Дата BETWEEN ('01.01.2010' AND '28.02.2010')

В БД не должно быть полей, значения которых вычисляются по значениям других полей (возможны исключения полей).

Выражение AS имя

Пример. Вычислить ∑ каждой поставки

SELECT НомерП, НомерД, Дата, Цена, Кол-во, Цена*Кол-во AS Сумма

FROM Поставки

Для удаления времени в дате:

CONVERT (Varchar, Data, 104) AS Дата

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

SELECT

FROM [Таблица1], [Таблица2], [Таблица3]

WHERE Таблица1.Столбец=Таблица2.Столбец

[AND Таблица2.Столбец = Таблица3.Столбец]

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

Пример. Вывести поставки, которые осуществлялись поставщиками, расположенными в городе Москва.

SELECT Поставки.НомерП, НомерД, Дата, Цена, Кол-во

FROM Поставщики, Поставки

WHERE Поставщики.НомерП = Поставки.НомерП AND Город='Москва'

Для каждого поставщика указать номер детали, которые он имеет право поставлять, используя правило. Статус поставщика должен быть больше или равен статусу детали.

SELECT НомерП, СтатусП, НомерД, СтатусД

FROM Поставщики, Детали

WHERE СтатусП >= СтатусД

Условие соединения должно указываться в FROM c помощью JOIN. Внутреннее соединение [INNER] JOIN. Результат будет содержать только связанные строки.

Левое внешнее соединение LEFT JOIN. Результат включает все строки левой таблицы и связанные с ними строки правой. Правые внешние соединения RIGHT JOIN включает все строки правой таблицы и связанные все строки с ними левой.

Структура запроса с использованием структуры JOIN.

SELECT * FROM Таблица1

(INNER, LEFT, RIGHT) JOIN Таблица2

ON Таблица1.Поле=Таблица2.Поле

Пример. Вывести данные о поставщиках и поставках, выполненных каждым поставщиком.

SELECT * FROM Поставщики

(LEFT) JOIN Поставки

ON Поставщики.НомерП = Поставки.НомерП

1 - без LEFT - данные о поставщиках с поставками

2 - с LEFT - по всем поставкам.

Группировка и использование агрегатных (статистических) функций.

COUNT - возвращает количество строк в таблице или группе

SUM - ∑ значений столбцов

AVG - следующее значение

MAX

MIN

COUNT считывает не данные в таблице или группе в независимости от положения DISTINCT.

SELECT (DISTINCT) COUNT (*)

FROM (SELECT Название FROM Детали)

Как правило, статистические функции используются в запросах с группировкой.

Пример. Для каждой детали подсчитать общее количество поставок всеми поставщиками.

SELECT НомерД, SUM (Кол-во) AS Всего

FROM Поставки

GROUP BY НомерД

SELECT НомерП, SUM (Кол-во*Цена) AS Всего

FROM Поставки

GROUP BY НомерП

SELECT НомерП, НомерД, SUM (Кол-во) AS Всего

FROM Поставки

GROUP BY НомерП, НомерД

В одном запросе можно использовать, как условие отбора строк в предложении WHERE, так и условие отбора групп в предложении HAVING.

Пример. Требуется определить номера, деталей, которые имеют статус > 2 и общее кол-во, которых во всех поставках > 400

SELECT НомерД, SUM (Кол-во) AS Всего

FROM Поставки JOIN Детали ON Поставки.НомерД=Детали.НомерД

WHERE СтатусД >=2

GROUP BY Поставки.НомерД

HAVING SUM (Кол-во) > 400

4