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

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

Цель работы: Сформировать умения создавать запросы с помощью языка структурированных запросов SQL.

 

В предыдущих лабораторных работах мы научились создавать запросы с помощью таких средств, как мастер и конструктор. В данной работе мы научимся использовать для этих целей язык структурированных запросов SQL (Structured Query Language).

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

 

SELECT <список колонок, включаемых в ответ> FROM <список таблиц>

WHERE <условие>;

 

Предложения SELECT (отобрать) и FROM (из) должны присут­ствовать обязательно. Условие WНERE (где) может быть опущено. Тогда в ответ войдут все строки, имеющиеся в таблице (SQL позволяет управлять выводом в ответ повторяющихся строк, и можно добиться как вывода только уникальных строк, так и включения в ответ повто­ряющихся строк).

Оператор SELECT может включать в себя и другие предложения, позволяющие, в частности, осуществлять упорядоченность ответа, выполнять обобщающие функции. Если в ответ должны войти все колонки, имеющиеся в исходной таблице, то вместо их перечисления в SELECT можно поставить знак «*».

Так, например, запрос «Выдать всю информацию о читателях из таблицы Читатели, которые проживают на улице Чкалова» может быть представлен на SQL следующим образом:

 

SELECT Читатели.*

FROM Читатели

WHERE ((Читатели.[Домашний адрес]) Like "ул. Чкалова" & "*");

 

Условие, задаваемое в предложении WHERE, может быть про­стым и сложным. Для формулирования сложного условия могут быть использованы логические операторы And и Or. Так, например, ранее составленный запрос Операторы сравнения для поиска цены может быть представлен на SQL следующим образом:

 

SELECT Книги.Автор, Книги.Название, Книги.[Год издания], Книги.Стоимость

FROM Книги

WHERE ((Книги.Стоимость)>=20000 And (Книги.Стоимость) <=30000)

ORDER BY Книги.Стоимость;

 

Оператор SELECT оперирует над множествами и результатом обработки в общем случае является множество строк. К этим множе­ствам могут быть применены теоретико-множественные операции объединение (UNION), пересечение (INTERSECTION), разность (DIFFERENCE, MINUS, EXCEPT) и др. В разных реализациях языка SQL наборы теоретико-множественных операций различаются.

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

 

SELECT Книги.Автор, Книги.Название, Книги.[Год издания], [Стоимость] /2165 AS [Цена в у_е]

FROM Книги;

 

С помо­щью конструкции AS в этом запросе задано имя столбца-результата.

Запрос может быть простым, состоящим из одного оператора SELECT, и вложенным, кoгдa один оператор SELECT включается в состав другого оператора. Этот включенный оператор называется подзапросом (subselect) или подчиненным запросом. Существуют два типа вложенных подзапросов: обычный и коррелированный. В обычном подзапросе внутренний запрос выполняется первым, и его результат используется для выполнения основного запроса. В коррелирован­ном подзапросе внешний запрос выполняется первым, и его резуль­тат используется для выполнения внутреннего запроса. Внутренний запрос выполняется для каждой строки, возвращенной внешним зап­росом.

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

Возможна подгруппировка данных в целях получения подытогов или других обобщающих величин (среднее, минимум, максимум и др.). Набор агрегатных функций отличается в разных системах. В запросе допускается только один уровень группировки. Груп­пировка может осуществляться как по одному полю, так и по сово­купности полей.

В некоторых реализациях языка SQL отобранные оператором SELECT данные могут быть сохранены в виде таблицы базы данных

При выполнении запроса может возникнуть необходимость со­единения двух или более таблиц. Возможны разные способы задания условия соединения (вложенные запросы, задание условия соедине­ния в предложении WHERE, операция JOIN в предложении FROM).

 

Общая характеристика оператора SELECТ

Для отбора информации из базы данных служит оператор SELECT. Синтаксис оператора выгля­дит следующим образом:

 

SELECT [DISTINCT]

{{функция агрегирования | выражение для вычисления значения [AS имя столбца]}.,}

| {спецификатор.*}

|*

FROM {{ имя таблицы [АS][имя корреляции].[(имя столбца.,..)]}

| {подзапрос [АS][имя корреляции.[имя столбца.,..]}

| соединенная таблица }.,..

[WHERE предикат]

[GROUP ВУ {{[ имя таблицы | имя корреляции]}.| имя столбца}.,..}] [HAVING предикат]

[UNION IINTERSECT | ЕХСЕРТ}[АLL]

[CORRESPONDING [ВУ (имя столбца.,..)]]

оператор  SELECT | TABLE  имя  таблицы | конструктор  значений  таб­лицы]

[ORDER ВУ{{столбец-результат [ASC | DESC]}.,..}

| {{положительное число[АSС | DESC]}.,..}]};

 

Оператор состоит из предложений SELECT, FROM, WHERE, GROUP ВУ, HAVING, ORDER ВУ, которые должны быть записаны в команде именно в той последовательности, в которой они перечисле­ны в синтаксической формуле.

Предложение SELECT определяет столбцы таблицы, полу­чаемой в результате выполнения запроса. Столбец результатной таб­лицы может быть задан именем столбца исходной таблицы. Если в запросе используется несколько таблиц и в них имеются поля, имею­щие одинаковые имена, то для указания такого поля используется кон­струкция <имя таблицы>.<имя поля>. Кроме того, в предложении SELECT могут использоваться любые допустимые выражения, кото­рые зададут формулу для определения вычисляемого поля. С помо­щью конструкции [AS <имя столбца>] можно задать имя столбца-результата. Конструкцию AS можно использовать не только тогда, когда определя­ются вычисляемые поля, но и во всех других случаях, когда нужно задать имя столбца-результата, отличающееся от имени столбца ис­ходной таблицы.

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

Запросы могут использовать функции агрегирования. Стандарт языка SQL предусматривает использование следующих функций агрегирования: Count – подсчет, Sum – сумма, Мax – максимум, Min – минимум, Avg - среднее.

Чаще всего функции агрегирования используются совместно с предложением GROUP ВУ, но могут применяться и самостоятельно. В последнем случае результат относится не к какой-то группе, а ко всей выборке.

Существуют два типа функции COUNT. Первый тип использует символ «*». В этом случае функция подсчитывает количество строк в группе. Отдельные значения столбцов при этом не учитываются, и результат не будет зависеть от того, имеются ли в полях значения Null и указан ли параметр DISTINCT. Второй тип функции COUNT игно­рирует значения Null.

Если в ответ требуется включить все поля таблицы, то для этого можно использовать символ «*». Если запрос многотабличный, то следует применять конструкцию {спецификатор. *}.

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

Начиная со стандарта SQL-92, в предложение FROM можно вклю­чaть встроенный оператор JOIN, который служит для задания разно­образных условий соединения таблиц, участвующих в запросе.

В предложении WHERE задается условие отбора записей. Предложение может включать одно выражение или несколько. Части слож­ного условия соединяются логическими операторами AND (И) или OR (ИЛИ). В выражениях могут использоваться следующие операторы срав­нения: = (равно), <> (не равно), < (меньше), <= (меньше или равно), > (больше), >= (больше или равно), которые могут предваряться опера­тором NOT. Выражение может принимать одно из трех значений: TRUE, FALSE, UNKNOWN. В результатную таблицу переносятся те строки, для ко­торых значение предиката равно TRUE.

Кроме стандартных операторов сравнения в SQL можно использовать специальные операторы предикатов:  <интервальный предикат >, <предикат IN>,  <предикат проверки на неопределенное значение>, <предикат подобия>.

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

WHERE [NОТ] <выражение> BETWEEN <нижнее выражение> AND <верхнее выражение>

При использовании предиката IN предложение WHERE будет иметь следующий вид:

WHERE [NOT] <выражение> [NOT] IN <список значений>|<под­запрос>

Предикат подобия применяется для поиска подстроки в указан­ной строке. Предложение WHERE при использовании предиката это­го типа будет иметь следующий вид:

WHERE [NОТ] <выражение для вычисления значения строки 1> [NOT] LIKE <выражение для вычисления значения строки 2>

Предикат проверки на неопределенное значение имеет вид

предикат NULL::= конструктор значения строки IS [NОТ] NULL

При использовании подзапросов в условии WHERE может быть использован квантор существования EXISTS. Формат условия WHERE в этом случае имеет вид

WHERE [NОТ] EXISTS <подзапрос>

Предложение GROUP ВY используется для определения групп выходных строк, к которым могут применяться те или иные агрегатные функции. Предложение GROUP ВУ всегда используется со встроенными агрегатными функциями. Обратное утверждение не­верно. Агрегатные функции могут использоваться в предложениях SELECT, HAVING. Если агрегатные функции используются без пред­ложения GROUP ВY, то они будут применяться ко всему набору строк, удовлетворяющему условию запроса. Конструкция GROUP ВY работает только на одном уровне. Нельзя разбить каждую из этих групп на группы более низкого уровня, а за­тем применять стандартную функцию на каждом уровне подчинен­ности.

Фраза GROUP ВY означает логическую перекомпоновку (груп­пировку) таблицы по указанной колонке (колонкам). Физически таб­лицы в базе данных не перекомпоновываются. Логика выполнения запроса при использовании GROUP ВY несколько отличается от реа­лизации обычного запроса. Фраза SELECT при использовании GROUP ВY применяется к каждой группе, а не к каждой строке, как обычно.

Каждое выражение во фразе SELECT должно принимать един­ственное значение для группы, т.е. оно может быть либо самой ко­лонкой, либо арифметическим выражением, включающим эту колон­ку, либо агрегатной функцией, которая получает в результате един­ственное значение для группы. Кроме того, в SELECT может быть включена константа.

Вместе с предложением GROUP ВY может использо­ваться предложение HAVING, которое для групп имеет то же значение, что и фраза WНERE – для строк.

Корректирующие операторы

Оператор INSERT позволяет включить в таблицу новые строки. Он имеет следующий вид:

INSERT INTO имя таблицы [(имя столбца,...)]

выражение запроса [конструктор значений таблицы] |{DEFAULT VALUES}

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

Элементы в списке значений могут быть константами, функция­ми, переменными памяти. Если эти элементы являются константами, то при их задании используются определенные разделители в зависи­мости от типа вводимых данных: символьные данные заключаются в кавычки, даты – в фигурные скобки, логические – в точки, числовые данные вводятся без разделителей.

Пример использования оператора INSERT:

INSERT INTO Издательства VALUES (6, "Новое знание", "Минск");

В данном примере значения вводятся во все столбцы таблицы, поэтому <список столбцов> не указан.

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

Командой, позволяющей корректировать со­держание таблицы, является оператор UPDATE, имеющий следую­щий формат:

UPDATE <имя таблицы> SET <имя столбца> = <новое значение> [, <имя столбца> = <новое значение>...] [<предложение WHERE>];

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

Оператор UPDATE Книги SET Стоимость=Стоимость*1.1; увеличивает стоимость книг для всех записей в таблице Книги на 10 %.

Оператор UPDAТЕ Книги SET Стоимость=Стоимость*0.9 WHERE [Год издания] < 2000; уменьшает стоимость книг, изданных до 2000 года, на 10 %.

Оператор DELETE можно использовать для удаления строк таблицы: DELETE FROM <имя таблицы> [<предложение WHERE>];

Следует быть осторожным при использовании оператора DELETE, поскольку, если фраза WHERE в операторе DELETE отсутствует, будут удалены все строки таблицы. То же самое произойдет, если неправильно указать условие отбора и в результате не будет ото­брано ни одной строки в таблице. Оператор DELETE физически уда­ляет строки таблицы.

 

Задание

1. Просмотрите на SQL ранее созданные запросы: Поиск книг по фамилии автора, Рейтинг книг, Список читателей с инициалами. Чтобы увидеть, как выглядит запрос в MS Access на SQL, надо в окне базы данных во вкладке Запросы выделить имя запроса, нажать на кнопку Открыть или Конструктор, а затем в меню Вид выбрать команду Режим SQL. Например, запрос Книги с ключевым словом в теме на SQL будет выглядеть так, как показано на рис. 1.

 

Рис. 1. Окно с текстом запроса на SQL.

 

2. Чтобы приступить к созданию запроса на SQL, надо открыть вкладку Запросы окна базы данных, выполнить двойной щелчок мышью на команде Создание запроса в режиме конструктора, закрыть диалоговое окно Добавление таблицы и в меню Вид выбрать команду Режим SQL. Создайте на SQL запрос, который будет выводить о читателях, заказавших книгу Язык Ада, следующую информацию: Фамилию, Имя, Отчество, Домашний адрес. Запрос назовите Поиск читателей по заказанной книге.

3. Сформулируйте задачу, которую решает следующий запрос на SQL:

SELECT Книги.*

FROM Книги

WHERE Название Like "Я"&"*";

 

4. Выясните, что делает приведенный ниже запрос на SQL.

 

SELECT Автор, Название, Наименование, Город, [Год издания]

FROM Издательства, Книги

WHERE Издательства.[Код издательства]=Книги.[Код издательства];

 

Назовите этот запрос так, чтобы было ясно, что он делает.

5. Выполните следующий запрос на SQL:

 

SELECT Count(*) AS Количество

FROM Книги;

 

и укажите, что он делает. Дайте ему соответствующее имя.

6. Выясните назначение приведенных ниже двух запросов на SQL.

 

SELECT Читатели.[Код читателя], Читатели.Фамилия,

(SELECT COUNT ([Выдача книг].[Код книги])

FROM [Выдача книг]

WHERE [Выдача книг].[Код читателя]=Читатели.[Код читателя]) AS Количество

FROM Читатели;

 

SELECT Читатели.Фамилия, Count([Выдача книг].[Код книги]) AS Количество

FROM [Выдача книг], Читатели

WHERE ([Выдача книг].[Код читателя])=Читатели.[Код читателя]

GROUP BY Читатели.Фамилия;

 

7. Составьте на SQL запрос, который будет вычислять количество прочитанных каждым читателем страниц. Попытайтесь это сделать двумя способами, показанными в предыдущем задании.

Представление данных