Операции: выборка данных, обновление записей, группировка данных. Использование обобщающих функций, создание запросов для вычисления выражения

Запросы на выборку

1.1 Указать имена и статус поставщиков в Париже.

 

 

 

1.2 Показать номера поставщиков, статус которых более 20 и которые находятся в Лондоне.

 

 

 

1.3 Показать имена поставщиков, расположенных в Лондоне или имеющих статус более 20 (или - две строки).

 

 

Запросы на обновление

Скорректировать статус поставщика S2 сделав его равным 77.

 

QBE запрос можно посмотреть из режима Конструктора в SQL

3. Запросы с использованием группировки и обобщающего набора функций:

SUM – суммирование значений по полю.

AVG – нахождение средне-арифметического.

MIN, MAX – минимальная и максимальная величины.

COUNT – количество строк.

FIRST – первая запись.

LAST – последняя запись.

 

Определить суммарное, максимальное, минимальное, а так же среднее значение поставок. Подсчитать количество строк в таблице SP.

 

QBE запрос в режиме Конструктора (табл. SP)

 

QTY	QTY	QTY	QTY	QTY
SP	SP	SP	SP	SP
SUM	MAX	MIN	AVG	COUNT

 

Результат

 

SUM - QTY	MAX - QTY	MIN - QTY	AVG - QTY	COUNT - QTY

 

Запрос для вычисления выражения

Определить веса, количества и общий вес детали

 

 

 

Результат запроса:

 

Лекция 7. Язык SQL. Определение данных, манипулирование данными.

 

Язык SQL для взаимодействия с БД появился в середине 70-х и был разработан в рамках проекта экспериментальной реляционной СУБД System R. Исходное название языка SEQUEL (Structered English Query Language) только частично отражает суть этого языка. Конечно, язык был ориентирован главным образом на удобную и понятную пользователям формулировку запросов к реляционной БД, но на самом деле уже являлся полным языком БД, содержащим помимо операторов формулирования запросов и манипулирования БД средства определения и манипулирования схемой БД; определения ограничений целостности и триггеров; представлений БД; возможности определения структур физического уровня, поддерживающих эффективное выполнение запросов; авторизации доступа к отношениям и их полям; точек сохранения транзакции и откатов. Рассмотрим эти свойства языка немного более подробно.

Общая характеристика языка

Язык SQL предназначен для выполнения операций над таблицами (создание, удаление, изменение структуры) и над данными таблиц (выборка, изменение, добавление и удаление), а также некоторых сопутствующих операций. SQL является непроцедурным языком и не содержит операторов управления, организации подпрограмм, ввода-вывода и т.п. в связи с этим SQL автономно не используется, обычно он погружен в среду встроенного языка программирования СУБД.

В современных СУБД с интерактивным интерфейсом можно создавать запросы, используя другие средства, например QBE. Однако применение SQL зачастую позволяет повысить эффективность обработки данных к базе. Например, при подготовке запроса в среде Access можно перейти из окна Конструктора запросов (формулировки запросов по образцу на языке QBE) в окно с эквивалентным оператором SQL. Подготовку нового запроса путем редактирования уже имеющегося в ряде случаев проще выполнить путем изменения оператора SQL. В различных СУБД состав операторов SQL может несколько отличаться.

Язык SQL не обладает функциями полноценного языка разработки, а ориентирован на доступ к данным, поэтому его включают в состав средств разработки программ. В этом случае его называют встроенным SQL. Стандарт языка SQL поддерживают современные реализации языков программирования. В специализированных системах разработки приложений типа клиент – сервер среда программирования, кроме того, обычно дополнена коммуникационными средствами (установление и разъединение соединений с серверами БД, обнаружение и обработка возникающих в сети ошибок и т.п.), средствами разработки пользовательских интерфейсов, средствами проектирования и отладки.

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

 

Спецификация запроса

В запросе задается список выборки (список арифметических выражений над значениями столбцов результата табличного выражения и констант). В результате применения списка выборки к результату табличного выражения производится построение новой таблицы, содержащей то же число строк, но вообще говоря другое число столбцов, содержащих результаты вычисления соответствующих арифметических выражений из списка выборки. Кроме того, в спецификации запроса могут содержаться ключевые слова ALL или DISTINCT. При наличии ключевого слова DISTINCT из таблицы, полученной применением списка выборки к результату табличного выражения, удаляются строки-дубликаты; при указании ALL (или просто при отсутствии DISTINCT) удаление строк-дубликатов не производится.

Операция объединения.

Выражение запросов - это выражение, строящееся по указанным синтаксическим правилам на основе спецификаций запросов. Единственной операцией, которую разрешается использовать в выражениях запросов, является операция UNION (объединение таблиц) с возможной разновидностью UNION ALL. К таблицам-операндам выражения запросов предъявляется то требование, что все они должны содержать одно и то же число столбцов, и соответствующие столбцы всех операндов должны быть одного и того же типа. Выражение запросов вычисляется слева направо с учетом скобок. При выполнении операции UNION производится обычное теоретико-множественное объединение операндов, т.е. из результирующей таблицы удаляются дубликаты.

(SELECT * FROM S)

UNION (SELECT * FROM SS);

 

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

 

Основные операторы SQL. Разделы SELECT, FROM, WHERE. Предикаты сравнения, BETWEEN, IN, EXISTS.

Раздел FROM

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

Рядом с именем таблицы можно указывать еще одно имя "correlation name". Фактически, это некоторый синоним имени таблицы, который можно использовать в других разделах табличного выражения для ссылки на строки именно этого вхождения таблицы.

Если выражение содержит только раздел FROM (это единственный обязательный раздел табличного выражения), то результат табличного выражения совпадает с результатом раздела FROM.

 

SELECT * FROM S;

 

Раздел WHERE

Если в выражении присутствует раздел WHERE, то следующим вычисляется он.

Вычисление раздела WHERE производится по следующим правилам: Пусть R - результат вычисления раздела FROM. Тогда условие поиска применяется ко всем строкам R, и результатом раздела WHERE является таблица, состоящая из тех строк R, для которого результатом вычисления условия поиска является true. Если условие выборки включает подзапросы, то каждый подзапрос вычисляется для каждого кортежа таблицы R (в стандарте используется термин "effectively" в том смысле, что результат должен быть таким, как если бы каждый подзапрос действительно вычислялся заново для каждого кортежа R).

 

SELECT [S], [STATUS]

FROM S

WHERE STATUS>15 And CITY='London';

 

Среди предикатов условия поиска в соответствии с SQL/92 могут находиться следующие предикаты: предикат сравнения, предикат between, предикат in, предикат like, предикат null, предикат с квантором и предикат exists. Сразу заметим, что во всех реализациях SQL на эффективность выполнения запроса существенно влияет наличие в условии поиска простых предикатов сравнения (предикатов, задающих сравнение столбца таблицы с константой). Наличие таких предикатов позволяет СУБД использовать индексы при выполнении запроса, т.е. избегать полного просмотра таблицы. Хотя в принципе язык SQL позволяет пользователям не заботиться о конкретном наборе предикатов в условии выборки (лишь бы они были синтаксически и семантически правильны), при реальном использовании SQL-ориентированных СУБД такие технические детали стоит иметь в виду.

Предикат сравнения

Через "<>" обозначается операция "неравенства". Арифметические выражения левой и правой частей предиката сравнения строятся по общим правилам построения арифметических выражений и могут включать в общем случае имена столбцов таблиц из раздела FROM и константы. Если правый операнд операции сравнения задается подзапросом, то дополнительным ограничением является то, что мощность результата подзапроса должна быть не более единицы. Если хотя бы один из операндов операции сравнения имеет неопределенное значение, или если правый операнд является подзапросом с пустым результатом, то значение предиката сравнения равно unknown.

Заметим, что значение арифметического выражения не определено, если в его вычислении участвует хотя бы одно неопределенное значение. Еще одно важное замечание из стандарта SQL/92: в контексте GROUP BY, DISTINCT и ORDER BY неопределенное значение выступает как специальный вид определенного значения, т.е. возможно, например, образование группы строк, значение указанного столбца которых является неопределенным. Для обеспечения переносимости прикладных программ нужно внимательно оценивать специфику работы с неопределенными значениями в конкретной СУБД.

 

SELECT [COLOR], [WGT]

FROM P

WHERE WGT <> 17;

 

Предикат BETWEEN.

SELECT S

FROM S

WHERE STATUS BETWEEN 20 AND 30;

 

Предикат IN

(SELECT SP.S

FROM SP

WHERE SP.P IN

(SELECT P.P FROM P));

 

Предикат LIKE.

 

SELECT *

FROM S

WHERE SName LIKE'%c%';

Предикат NULL.

SELECT [S].[SNAME]

FROM S LEFT JOIN SP ON [S].[S]=[SP].[S]

WHERE [SP].[P] Is NULL

Предикат с квантором EXISTS

Определить имена поставщиков детали с номером P2.

SELECT DISTINCT S.SNAME

FROM S

WHERE EXISTS

(SELECT *

FROM SP

WHERE SP.S# = S.S

AND SP.P# = 'P2);

Значением этого предиката всегда является true или false и, это значение равно true тогда и только тогда, когда результат вычисления подзапроса не пуст.

 

SELECT DISTINCT S. SNAME

FROM S

WHERE Not Exists

(SELECT * FROM P

WHERE Not EXISTS

(SELECT * FROM SP

WHERE SP.S = S.S

AND SP.P = P.P));

 

Раздел GROUP BY

Если в табличном выражении присутствует раздел GROUP BY, то следующим выполняется он.

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

SELECT SP.P, SUM(SP.QTY) AS TOTQTY

FROM SP

GROUP BY SP.P;

Раздел HAVING

Наконец, последним при вычислении табличного выражения используется раздел HAVING (если он присутствует). Синтаксис этого раздела следующий:

Раздел HAVING может осмысленно появиться в табличном выражении только в том случае, когда в нем присутствует раздел GROUP BY. Условие поиска этого раздела задает условие на группу строк сгруппированной таблицы. Формально раздел HAVING может присутствовать и в табличном выражении, не содержащем GROUP BY. В этом случае полагается, что результат вычисления предыдущих разделов представляет собой сгруппированную таблицу, состоящую из одной группы без выделенных столбцов группирования.

 

Условие поиска раздела HAVING строится по тем же синтаксическим правилам, что и условие поиска раздела WHERE, и может включать те же самые предикаты. Однако имеются специальные синтаксические ограничения по части использования в условии поиска спецификаций столбцов таблиц из раздела FROM данного табличного выражения. Эти ограничения следуют из того, что условие поиска раздела HAVING задает условие на целую группу, а не на индивидуальные строки.

SELECT SP.P

FROM SP

GROUP BY SP.P

HAVING COUNT (SP.S) > 1;

Поэтому в арифметических выражениях предикатов, входящих в условие выборки раздела HAVING, прямо можно использовать только спецификации столбцов, указанных в качестве столбцов группирования в разделе GROUP BY. Остальные столбцы можно специфицировать только внутри спецификаций агрегатных функций COUNT, SUM, AVG, MIN и MAX, вычисляющих в данном случае некоторое агрегатное значение для всей группы строк. Аналогично обстоит дело с подзапросами, входящими в предикаты условия выборки раздела HAVING: если в подзапросе используется характеристика текущей группы, то она может задаваться только путем ссылки на столбцы группирования.

Результатом выполнения раздела HAVING является сгруппированная таблица, содержащая только те группы строк, для которых результат вычисления условия поиска есть true. В частности, если раздел HAVING присутствует в табличном выражении, не содержащем GROUP BY, то результатом его выполнения будет либо пустая таблица, либо результат выполнения предыдущих разделов табличного выражения, рассматриваемый как одна группа без столбцов группирования.

Раздел ORDER BY.

SELECT S.CITY

FROM S

ORDER BY CITY ASC

Как видно из запроса, фактически задается список столбцов результата выражения запросов, и для каждого столбца указывается порядок просмотра строк результата в зависимости от значений этого столбца (ASC - по возрастанию (умолчание), DESC - по убыванию). Столбцы можно задавать их именами в том и только в том случае, когда (1) выражение запросов не содержит операций UNION или UNION ALL и (2) в списке выборки спецификации запроса этому столбцу соответствует арифметическое выражение, состоящее только из имени столбца. Во всех остальных случаях в разделе ORDER BY должен указываться порядковый номер столбца в таблице-результате выражения запросов.