Извлечение данных из отношений

Извлечение данных из отношений выполняется с помощью команды SELECT (селекция). Эта команда не изменяет данные в БД.

Результатом выполнения команды SELECT является временное отношение, которое помещается в курсор (специальную область памяти СУБД) и обычно сразу выводится на экран. Синтаксис этой команды:

SELECT * | { [ ALL | DISTINCT ] <список выбора>.,..} FROM {<имя таблицы> [<алиас>] }.,.. [ WHERE <условие>] [ GROUP BY {<имя поля> | <целое>}.,.. [ HAVING <условие>] ] [ ORDER BY {<имя поля> | <целое> [ ASC | DESC ] }.,..] [ UNION [ALL] SELECT …];

Расшифровка элементов описания приведена в табл. 2.

Таблица 2. Элементы команды SELECT

Элемент	Описание
<список выбора>	Список элементов, разделённых запятыми. Элемент списка выбора – выражение и необязательный алиас. Выражение может включать имена полей, знаки операций, вызовы функций и константы.
<имя таблицы>	Имя или синоним имени таблицы или представления.
<алиас>	Временный синоним имени таблицы, определённый только внутри запроса.
<условие>	Условие, которое может быть истинным или ложным для каждого поля или комбинации полей из таблицы (таблиц), определённых предложением FROM.
<имя поля>	Имя поля (столбца) таблицы.
<целое>	Число без десятичной точки. Номер поля в <списке полей>.

DISTINCT – предикат удаления из результирующего отношения повторяющихся кортежей.

ALL – предикат, обратный к DISTINCT (используется по умолчанию).

Рассмотрим основные предложения команды SELECT:

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

sal*0.87+bonus as salary

Если надо вывести все поля из тех отношений, к которым обращается данный запрос, можно указать символ * (если в отношениях нет полей с одинаковыми именами). В этом случае сначала будут выведены поля таблицы, стоящей первой в предложении FROM, затем – второй и т.д. Поля, относящиеся к одной таблице, будут выводиться в том порядке, в каком они были записаны при создании таблицы.

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

WHERE – содержит условия выбора отдельных записей.

GROUP BY – группирует записи по значению одного или нескольких полей. Каждой группе в результирующем отношении соответствует одна запись.

HAVING – позволяет указать условия выбора для групп записей. Может использоваться только после group by.

ORDER BY – упорядочивает результирующие записи по значению одного или нескольких полей: ASC – по возрастанию, DESC – по убыванию.

Порядок выполнения операции SELECT такой:

1. Выбор из указанной таблицы тех записей, которые удовлетворяют условию отбора (where).

2. Группировка полученных записей (group by).

3. Выбор тех групп, которые удовлетворяют условию отбора (having).

4. Сортировка записей в указанном порядке (order by).

5. Извлечение из записей полей, заданных в списке выбора, и формирование результирующего отношения.

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

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

• IN:

field IN (список значений)

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

• BETWEEN:

field BETWEEN значение1 AND значение2

– определяет, входит ли значение поля field в указанные границы. Если значение поля меньше, чем значение1, или больше, чем значение2, предикат возвращает "ложь".

• LIKE:

field LIKE 'образец'

– используется для поиска подстрок, применяется только в полям типа CHAR, VARCHAR. Возможно использование шаблонов: '_' – один любой символ и '%' – произвольное количество символов (в т.ч., ни одного);

• IS [NOT] NULL:

field IS [NOT] NULL

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

Функции агрегирования:

• COUNT – определяет в результате количество строк (записей) или значений поля, не являющихся NULL-значениями.

• SUM – определяет арифметическую сумму значений указанного числового поля в результирующем множестве записей.

• AVG – определяет среднее арифметическое значений указанного числового поля в результирующем множестве записей;

• MAX, MIN – определяет максимальное (минимальное) значение указанного поля в результирующем множестве.

Правила уточнения использования агрегирующих функций:

SUM (distinct <поле>) – суммирование различных значений поля;

AVG (distinct <поле>) – среднее арифметическое разных значений поля;

COUNT (distinct <поле>) – подсчёт количества разных значений поля;

COUNT (<поле>) – подсчёт количества ненулевых значений поля;

COUNT (*) – подсчёт количества строк в результате.

Предложение UNION позволяет объединять результаты нескольких запросов SELECT для реализации соответствующей операции реляционной алгебры. Результаты этих запросов должны быть построены по одной схеме. Предложение ORDER BY может встречаться в таком запросе один раз – в конце последнего предложения SELECT.

Работа с представлениями

Представление (view, обзор) – это хранимый запрос, создаваемый на основе команды SELECT. Представление реально не содержит данных. Запрос, определяющий представление, выполняется тогда, когда к представлению происходит обращение с другим запросом, например, SELECT, UPDATE и т.д.

Создание представления выполняется командой CREATE VIEW:

CREATE VIEW <имя представления> [(<имя столбца>.,..)] AS <запрос>;

Запрос, на основании которого создаётся представление, называется определяющим запросом, а таблицы, к которым происходит обращение в определяющем запросе – базовыми таблицами. Определяющий запрос не может включать предложение ORDER BY.

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

Изменения будут произведены в базовой таблице и отразятся в представлении. По стандарту SQL-2 представление не является обновляемым, если определяющий запрос:

1. содержит ключевое слово DISTINCT;

2. ссылается на другое необновляемое представление;

3. содержит вычисляемые выражения в списке выбора;

4. выбирает данные более чем из одной таблицы;

5. содержит предложение GROUP BY.

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

Удаление объектов базы данных

Удаление объектов БД выполняется с помощью команды DROP.

• DROP TABLE – удаление таблицы:

DROP TABLE <имя таблицы> [RESTRICT | CASCADE];

Таблица будет удалена без дополнительного запроса на подтверждение. При указании CASCADE вместе с таблицей каскадно удаляются все зависящие от неё объекты БД. Если указать RESTRICT, то при наличии зависимых от удаляемой таблицы объектов операция будет отменена.

• DROP VIEW – удаление представления:

DROP VIEW <имя представления>;

NULL-значения

Стандарт SQL включает понятие неопределённого значения – NULL-значения. В тех случаях, когда при добавлении записи значение какого-либо поля неизвестно, его можно не устанавливать, пропустив это поле в списке полей или указав для него значение NULL (но только для тех полей, на которые не наложено ограничение целостности NOT NULL).

Значение NULL не сравнимо ни с каким другим значением, даже со значением NULL. Тем не менее, предложение GROUP BY объединяет все NULL-значения в одну группу, DISTINCT оставляет только одно NULL-значение, а функция AVG не учитывает NULL-значения, и сумма значений поля делится на количество ненулевых значений.

Подзапросы

Подзапросы можно разделить на следующие группы в зависимости от возвращаемых результатов:

• запросы, возвращающие от 0 до нескольких элементов (начинаются с оператора IN или модифицированного оператора сравнения);

• запросы на существование (начинаются с оператора EXISTS);

• запросы, возвращающие единственное значение (начинаются с немодифицированного оператора сравнения).

Подзапросы бывают коррелированные и некоррелированные. Коррелированные подзапросы содержат условия, зависящие от значений полей в основном запросе. Запросы на существование обычно являются коррелированными.

Рассмотрим операторы, которыми модифицируются операторы сравнения:

• ALL – оператор, эквивалентный понятию "все". Например:

> ALL (< ALL) – больше (меньше) каждого значения элементов результирующего множества.

• ANY (SOME) – оператор, эквивалентный понятию "любой".

= ANY – равно одному из значений элементов результирующего множества (эквивалентно использованию предиката IN).

> ANY (< ANY) – больше (меньше) любого значения элементов результирующего множества.

• EXISTS – оператор, эквивалентный понятию "существует". Часто используется с логическим оператором NOT.

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

Выражение <>ANY(…) не эквивалентно NOT IN: оно выполняется всегда, кроме случаев NULL-значений.

Сложные запросы

Существуют варианты запросов, которые в принципе не могут быть реализованы в рамках одного запроса. Например, составление ведомости на получение зарплаты при условии, что подоходным налогом (13%) не облагается один минимальный оклад (300 рублей) на каждого ребенка. Для этого запроса необходима информация из двух отношений ("Сотрудники" и "Дети"), причём для определения количества детей необходимо использовать агрегирующую функцию.

create view nc(tabno, num) as select tabno, count(tabno) from children group by tabno;

select distinct emp.name, salary*0.87+(300*0.13)*num as sal from emp, nc where emp.tabno = nc.tabno union select name, salary*0.87 from emp where tabno not in (select distinct tabno from children);

Программное обеспечение MySQL-КРАТКИЕ СВЕДЕНИЯ

• MySQL - это система управления базами данных [4].

База данных представляет собой структурированную совокупность данных. Эти данные могут быть любыми - от простого списка предстоящих покупок до перечня экспонатов картинной галереи или огромного количества информации в корпоративной сети. Для записи, выборки и обработки данных, хранящихся в компьютерной базе данных, необходима система управления базой данных, каковой и является ПО MySQL. Поскольку компьютеры замечательно справляются с обработкой больших объемов данных, управление базами данных играет центральную роль в вычислениях. Реализовано такое управление может быть по-разному - как в виде отдельных утилит, так и в виде кода, входящего в состав других приложений.

• MySQL - это система управления реляционными базами данных.

В реляционной базе данных данные хранятся не все скопом, а в отдельных таблицах, благодаря чему достигается выигрыш в скорости и гибкости. Таблицы связываются между собой при помощи отношений, благодаря чему обеспечивается возможность объединять при выполнении запроса данные из нескольких таблиц. SQL как часть системы MySQL можно охарактеризовать как язык структурированных запросов плюс наиболее распространенный стандартный язык, используемый для доступа к базам данных [4].

• Программное обеспечение MySQL - это ПО с открытым кодом.

ПО с открытым кодом означает, что применять и модифицировать его может любой желающий. Такое ПО можно получать по Internet и использовать бесплатно. При этом каждый пользователь может изучить исходный код и изменить его в соответствии со своими потребностями. Использование программного обеспечения MySQL регламентируется лицензией GPL (GNU General Public License).

Для более наглядного изучения предмета познакомимся с различными вариантами практической реализации построения БД.