Илюшечкин В.М. Базы данных
Подождите немного. Документ загружается.
Обязательными являются фразы (или предложения) SELECT и FROM.
Все другие компоненты оператора SELECT, если они используются, должны
записываться в том порядке, в котором они перечислены в формате оператора.
Рассмотрим правила записи отдельных компонентов оператора SELECT.
Фраза SELECT имеет следующий формат:
SELECT [DISTINCT] { * | <список выражений>}
Если указано DISTINCT, то в результирующую таблицу отбираются
только уникальные строки. По умолчанию отбираются все строки, в том числе
повторяющиеся.
Знак “*” указывает, что в результирующую таблицу выводятся все
столбцы из таблиц, указанных в фразе FROM.
В списке выражений обычно перечисляются имена столбцов, которые
будут выводиться в результирующую таблицу. Но могут быть указаны и
константы; тогда это значение будет повторено во всех строках результирующей
таблицы. Кроме того, в качестве элемента в списке выражений может быть
задано арифметическое выражение для определения вычисляемого столбца.
В выражениях могут употребляться стандартные функции, в том числе:
SUM (сумма), AVG (среднее), MIN (минимальное), MAX (максимальное),
COUNT (число значений). Каждая из этих функций оперирует совокупностью
значений в столбце, заданном в качестве аргумента функции.
Аргументу функции может предшествовать ключевое слово DISTINCT,
которое указывает, что дублирующие значения не должны учитываться при
определении результата. Например:
SELECT ‘ВСЕГО ЖИТЕЛЕЙ’, COUNT (DISTINCT nom),
‘СУММА ДОХОДОВ=’, SUM (money) FROM Have_D
Фраза FROM содержит список имен таблиц, которые используются для
формирования результирующей таблицы. Например, чтобы получить таблицу с
фамилиями жителей и их телефонами, нужен запрос
SELECT person.Fio,tphone.Ntel
FROM person,tphone
WHERE person.Adr=tphone.Adr
71
Тема 11. Отбор строк из таблиц. Условия поиска строк
Фраза WHERE задает условие, по которому выбираются строки для
выполнения запроса. в предыдущем примере использован следующий формат
фразы WHERE:
WHERE [NOT] [<имя таблицы1>.] <имя столбца>
<знак сравнения> [<имя таблицы2>.] <имя столбца>
Условие отбора строк может задаваться с использованием логических
операций AND и OR. Например, если нужны данные только о жителях с
фамилиями ИВАНОВ и ПЕТРОВ, то фраза WHERE может быть такой:
WHERE Fio=‘ИВАНОВ’ OR Fio=‘ПЕТРОВ’
Помимо логических операций можно использовать предикат IN
(находится в, принадлежит). Фраза WHERE в этом случае имеет такой формат:
WHERE [NOT] <выражение> [NOT] IN
(<список значений><подзапрос>)
Например, WHERE Fio IN (‘ИВАНОВ’, ‘ПЕТРОВ’)
Если нужно выдать сведения о всех жителях, кроме Ивановых и
Петровых, то запрос можно задать так:
SELECT * FROM person WHERE Fio NOT IN (‘ИВАНОВ’, ‘ПЕТРОВ’)
В условии может быть задан диапазон:
WHERE [NOT] <выражение> [NOT] BETWEEN
<нижнее выражение> AND <верхнее выражение>
Например, получить сведения о жителях, родившихся в 1960 году, можно
с помощью запроса
SELECT * FROM person WHERE Rdate BETWEEN '01/01/60' AND
'31/12/60'
Предусмотрена возможность поиска символьной информации по шаблону
(маске). Символ “%” обозначает в шаблоне любую последовательность
символов, а символ “_” (подчеркивание) - любой единичный символ. Поиск по
шаблону задается фразой
WHERE [NOT] <имя столбца> [NOT] LIKE ‘<шаблон>’
Так, запрос
SELECT * FROM person WHERE Fio LIKE ‘И%’
обеспечит выдачу сведений о всех жителях, фамилии которых начинаются
буквой “И”.
72
Вопросы для самоконтроля
1. Для чего предназначено предложение SELECT оператора SELECT?
2. Для чего предназначено предложение FROM оператора SELECT?
3. Для чего предназначено предложение WHERE оператора SELECT?
Тема 12. Сортировка таблиц
Информация, получаемая в результате выполнения запроса, может быть
упорядочена с помощью фразы
ORDER BY <имя столбца><целое>[ASCDESC]
[,<имя столбца><целое>[ASCDESC]...]
Вместо имени столбца может записываться целое число, которое задает
номер столбца в таблице, считая слева направо.
Если столбцы вычисляемые или являются результатом операции UNION,
то вместо имени столбца должен использоваться номер столбца.
Параметр ASCDESC означает вид сортировки (по возрастаниюпо
убыванию соответственно). По умолчанию принимается значение ASC.
Если во фразе ORDER BY задается список полей, то это означает
упорядочение по составному ключу. Фраза ORDER BY соответствует операции
SORT. Столбцы, перечисленные во фразе ORDER BY, должны присутствовать
во фразе SELECT.
Тема 13. Использование псевдонимов для обозначения таблиц базы
данных. Самосоединение таблиц. Итоговые запросы и агрегатные
функции
Вместо длинных имен таблиц в запросах можно использовать их
псевдонимы, которые задаются в фразе FROM оператора SELECT:
FROM <имя таблицы> [<псевдоним>] [,<имя таблицы>
[<псевдоним>]…]
В качестве псевдонима используется последовательность символов,
соответствующая правилам записи имен на языке SQL.
Псевдоним удобно использовать также для обозначения копии таблицы
при записи запроса. Например, определить пары жителей, получающих
одинаковые доходы, можно соединив по равенству значений в столбце SumD
копию А и копию В таблицы PERSON:
73
SELECT A.Fio,B.Fio FROM person A, person B WHERE
A.SumD=B.SumD
Такой прием называется самосоединением таблиц.
Во многих запросах к базе данных не требуется той степени детализации,
как в ранее рассмотренных запросах. Например, во всех запросах,
перечисленных ниже, требуется узнать всего одно или несколько значений,
которые подытоживают информацию, хранящуюся в базе данных:
- Какова сумма общих доходов, получаемых всеми жителями?
- Каковы минимальный и максимальный размеры общий доходов?
- Каков средний размер общего дохода?
Запросы такого типа (итоговые запросы) создаются с помощью
агрегатных функций и фраз GROUP BY и HAVING оператора SELECT.
Агрегатные функции предназначены для подведения итогов по
информации, хранящейся в базе данных. Агрегатная функция принимает в
качестве аргумента какой-либо столбец данных целиком, а возвращает одно
значение, которое определенным образом подытоживает этот столбец.
Имеется шесть агрегатных функций, которые позволяют получать
различные виды итоговой информации:
- функция SUM( ) вычисляет сумму всех значений, содержащихся в
столбце;
- функция AVG( ) вычисляет среднее всех значений, содержащихся в
столбце;
- функция MIN( ) находит наименьшее среди всех значений,
содержащихся в столбце;
- функция MAX( ) находит наибольшее среди всех значений,
содержащихся в столбце;
- функция COUNT( ) подсчитывает количество значений, содержащихся
в столбце;
- функция COUNT(*) подсчитывает количество строк в таблице
результатов запроса.
Например, получить ответы на запросы, перечисленные выше, можно
оператором
74
SELECT SUM(SumD), MIN(SumD), MAX(SumD), AVG(SumD) FROM
person
Вопросы для самоконтроля
1. Для чего предназначено предложение GROUP BY оператора SELECT?
2. Для чего предназначено предложение HAVING оператора SELECT?
3. Для чего предназначено предложение ORDER BY оператора SELECT?
4. В каком предложении оператора SELECT задаются псевдонимы таблиц?
5. Приведите пример использования псевдонимов таблиц в операторе SELECT
(для учебной БД).
6. Для учебной БД приведите пример использования самосоединения в запросе
на языке SQL.
7. Для чего предназначена агрегатная функция AVG( )? Приведите правила
записи аргумента.
8. Для чего предназначена агрегатная функция SUM( )? Приведите правила
записи аргумента.
9. Для чего предназначена агрегатная функция MIN( )? Приведите правила
записи аргумента.
10. Для чего предназначена агрегатная функция MAX( )? Приведите правила
записи аргумента.
11. Для чего предназначена агрегатная функция COUNT( )? Приведите правила
записи аргумента.
12. Для чего предназначена агрегатная функция COUNT(*)?
13. Для учебной БД напишите запрос, вычисляющий среднедушевой налог.
Тема 14. Запросы с группировкой
Группировка данных задается фразой GROUP BY. При этом
обеспечивается группировка только на одном уровне, т.е. нельзя разбить каждую
из групп на группы более низкого уровня. Например, получить сведения о сумме
доходов каждого жителя позволяет запрос
SELECT nom, SUM(money) FROM Have_D GROUP BY nom
В этом примере используется группировка по одному столбцу. В общем
случае строке таблицы можно группировать по любой комбинации ее столбцов.
При этом имена столбцов в фразе GROUP BY перечисляются через запятую.
75
Фраза GROUP BY означает логическую группировку таблицы по
указанному столбцу или столбцам. Физически строки в таблице не
перекомпоновываются.
Логика выполнения запроса при наличии фразы GROUP BY несколько
отличается от реализации обычного запроса. Фраза SELECT при использовании
GROUP BY применяется к каждой группе, а не к каждой строке, как обычно.
Каждое выражение во фразе SELECT должно принимать единственное
значение для группы, т.е. оно может быть либо самим столбцом, по которому
производится группировка, либо арифметическим выражением, включающим
этот столбец, либо функцией, которая получает в результате единственное
значение для группы. Кроме того, во фразу SELECT может быть включена
константа.
Вместе с GROUP BY может использоваться фраза HAVING, которая для
группы играет ту же роль, что и фраза WHERE для строк. Например, запрос на
выдачу списка номеров жителей, которые имеют более одного источника
дохода, будет выглядеть следующим образом:
SELECT nom FROM Have_D GROUP BY nom
HAVING COUNT(*)>1
Выражение во фразе HAVING должно принимать единственное значение
для группы. Функция COUNT(*) означает подсчет числа строк в группе (или
таблице).
Тема 15. Вложенные запросы
Условие отбора в фразе WHERE может формулироваться с помощью
подзапроса, т.е. оператора SELECT, вложенного в другой оператор SELECT.
Например, если надо получить сведения о жителях, имеющих телефон с
номером 532-98-54, то это можно сделать, задав такой запрос:
SELECT * FROM person WHERE Adr=
(SELECT Adr FROM tphone WHERE Ntel=‘5329854’)
При реализации запроса, содержащего вложенные подзапросы, система
обрабатывает вложенный подзапрос самого низкого уровня, затем следующего
уровня и так последовательно “поднимается” вверх.
76
Если подзапрос выдает множество значений, то в фразе WHERE можно
использовать предикат IN, предназначенный для выяснения принадлежности
элемента множеству. Например, запрос на выдачу фамилий жителей, имеющих
источник дохода “РАБОТА1”, может быть записан с подзапросом:
SELECT Fio FROM person WHERE nom IN
(SELECT nom FROM Have_D WHERE source=‘РАБОТА1’)
Во многих случаях можно избежать использования вложенных запросов,
заменяя их на запросы, в которых задано условие соединения таблиц. Так, два
последних запроса можно представить иначе:
SELECT person.* FROM tphone,person WHERE
person.Adr= tphone.Adr AND Ntel=‘5329854’
SELECT Fio FROM Have_D,person WHERE
person.nom= Have_D.nom AND source=‘РАБОТА1’
Такие запросы выглядят более простыми и менее “процедурными”, так как
при задании запроса не надо определять порядок, в котором просматриваются
таблицы.
В приведенных выше примерах были рассмотрены запросы с одним
уровнем вложенности, однако допускается задавать несколько уровней
вложенности запросов.
Вопросы для самоконтроля
1. Для учебной БД напишите запрос, вычисляющий сумму доходов,
полученных жителями от источника с названием "Стипендия".
2. Для учебной БД напишите запрос, определяющий даты рождения самого
молодого и самого старого жителя.
3. Для учебной БД напишите запрос, определяющий количество источников
дохода, реально используемых жителями.
4. Для учебной БД напишите запрос, определяющий количество жителей,
имеющих источники дохода.
5. Для учебной БД напишите запрос, определяющий количество источников
дохода у жителя с номером 222.
6. Какой запрос называется запросом с группировкой?
7. Какие столбцы называются столбцами группировки?
77
8. Как изменяется действие агрегатных функций в запросах с группировкой по
сравнению с обычным итоговым запросом?
9. Перечислите требования к столбцам группировки.
10. Что может быть элементом списка возвращаемых столбцов в запросе с
группировкой?
11. В каком предложении записывается условие поиска групп?
12. Что можно использовать в условии поиска групп для запроса с
группировкой?
13. Для учебной БД напишите запрос, определяющий количество жителей в
каждой квартире.
14. Какой запрос называется вложенным?
15. Перечислите особенности вложенного запроса.
16. Что называется внешней ссылкой во вложенном запросе?
17. Какой вложенный запрос называется связанным подзапросом?
18. Укажите особенность связанного подзапроса.
19. Для учебной БД напишите запрос, формирующий список самых молодых
жителей.
Тема 16. Изменение данных в базе данных
16.1. Корректировка таблиц БД
В таблицу можно включать новую строку с помощью оператора
INSERT INTO <список значений> [(<список столбцов>)]
{VALUES (<список значений>) | <подзапрос>}
Если список столбцов не задан, то значения должны вводиться в каждое
поле новой строки; если список столбцов задан, то значения соответственно
должны вводиться в те поля, которые перечислены в списке столбцов, и в том
порядке, в каком они расположены в списке. Например:
INSERT INTO Have_D VALUES (999111, ‘РАБОТА1’,
500000)
Если значения, которые надо ввести, являются результатом выполнения
подзапроса, то эти значения также помещаются в указанные столбцы и должны
соответствовать им по типу. При использовании <подзапроса> в таблицу
вводятся данные, отобранные из другой таблицы или даже нескольких таблиц.
Для изменения значения заданных столбцов служит оператор
78
UPDATE <имя таблицы> SET <имя столбца> =<значение>,
. . . [WHERE <условие>]
Оператор позволяет изменить значения указанных столбцов для всех
строк таблицы или для отобранных строк, если используется фраза WHERE.
Например:
UPDATE Have_D SET source=‘ПОСОБИЕ’,money=100
WHERE nom=999111 AND source=‘РАБОТА1’
Для удаления строк таблицы предназначен оператор
DELETE FROM <имя таблицы> [WHERE <условие>]
Если фраза WHERE отсутствует, то удаляются все строки таблицы. При
использовании фразы WHERE <условие> из таблицы удаляются строки,
удовлетворяющие заданному условию.
16.2. Создание объектов БД
Для того чтобы обращаться к данным на языке SQL, надо предварительно
создать БД. БД создается при помощи оператора
CREATE DATABASE <имя БД>
В БД должны быть включены таблицы и, если надо, индексные файлы.
этого можно достичь несколькими способами. Первый из них заключается в том,
чтобы создать таблицы средствами самого языка SQL, другие способы создания
таблиц связаны с их экспортированием из других систем.
Для создания таблицы предназначен оператор
CREATE TABLE <имя таблицы> (<имя столбца>
<тип данных>, ...);
Например,
CREATE TABLE Have_D (nom NUMERIC(6,0),
Source CHAR(10), money NUMERIC(10,0))
Средствами SQL могут быть созданы также индексы. Для этого
предназначен оператор
CREATE INDEX <имя индекса> ON <имя таблицы>
Опция UNIQUE определяет уникальный индекс. В языке SQL это
означает, что значения в столбцах таблицы должны быть уникальны, и, как
следствие, уникальными являются и элементы индекса. Если объявлен
уникальный индекс, то при вводе данных в столбцы, указанные в операторе
CREATE INDEX, могут быть включены только уникальные значения.
79
Если в операторе указаны несколько имен столбцов, то индексирование
будет производиться по составному ключу. Компоненты этого составного ключа
могут быть разнотипными.
При создании индекса может быть указан порядок сортировки: ASC - по
возрастанию; DESC - по убыванию. По умолчанию принимается значение ASC.
Пример создания индекса:
CREATE UNIQUE INDEX H_D_index ON Have_D
(nom,source,money)
16.3. Создание представлений
В языке SQL различают несколько видов таблиц: базовые и таблицы
представления (просмотры).
Базовые таблицы - это таблицы, созданные оператором CREATE TABLE.
Представление (VIEW) является виртуальной таблицей. Оно отображает
“взгляд” пользователя БД на существующие таблицы, т.е. является внешней
моделью БД, описанной в виде подсхемы.
Например, при работе с БД Zgrad работника домоуправления,
расположенного в 9-м микрорайоне интересуют только общие сведения о
жителях этого микрорайона и характеристики их квартир, а не номера телефонов
и получаемые жителями доходы.
Создать представление можно с помощью оператора
CREATE VIEW <имя представления> [(<список столбцов
выборки>)] AS <оператор SELECT> [WITH CHECK OPTION]
Так, для описанного примера можно создать следующее представление:
CREATE VIEW region_9 AS SELECT * FROM person, flat
WHERE person.Adr=flat.Adr AND Adr LIKE ‘9%’
В этом примере person и flat являются базовыми таблицами, содержащими
сведения о жителях и квартирах Зеленограда, а region_9 - выборкой, содержащей
сведения только о жителях и квартирах 9-го микрорайона.
Представление не хранится в виде физической таблицы. При выполнении
оператора CREATE VIEW подзапрос, следующий за фразой AS, не исполняется,
а запоминается в каталоге базы данных.
При обращении к представлению region_9 запрос преобразуется системой
с учетом описания представления, и такой “комплексный” запрос исполняется.
80