Лабораторная работа N 4. Запросы к базе данных. Команда SELECT

Постановка задачи

Теоретические предпосылки

Постановка задачи

Цель работы:

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

Задание:

а)изучить структуру команды SELECT;

б)научится создавать запросы (на примере задания из лабораторной работы N 4):

-выборка таблицы данных целиком;

-выборка заданного списка полей таблицы;

-выборка по условию;

-преобразование данных при выборке;

-агрегирование данных при выборке;

-запросы к нескольким таблицам данных, объединение данных из разных таблиц;

-фильтрация и сортировка данных.

в)оформить и сдать отчет по лабораторной работе N 4.

Теоретические предпосылки

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

Синтаксис команды

SELECT

[{DISCTINCT | ALL}] <List_val>

FROM <List_table>

[WHERE <SearchCondition>]

[GROUP BY <List_column>]

[HAVING <SearchCondition>]

[UNION <SelectExpr> [ALL]]

[ORDER BY <List_orders>]

Подробное описание данной команды можно получить в документации к InterBase файл Language Reference.

Рассмотрим на примерах виды запросов, которые можно сформировать к базе данных.

Выборка таблицы данных целиком

Самый простой вид запроса - получение всего содержимого одной таблицы данных.

Рассмотрим организацию данного вида запросов на примере отношения

Sotrudnik (Tb_No, FIO, Dolg, Otdel, Deti)

SELECT * FROM Sotrudnik;

Результатом данного запроса будет полное содержимое таблицы Sotrudnik.

Символ * означает выбор всех полей таблицы данных.

Выборка заданного списка полей таблицы

Этот вид запроса относится также к одному из простых, когда требуется получить содержимое нескольких полей одной таблицы. В отличие от предыдущего вида запроса выводится содержимое не всей таблицы, а ее части: конкретных, указанных в запросе полей. На языке реляционной алгебры такая операция носит название проекция.

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

SELECT Tab_No, FIO

FROM Sotrudnik;

Список полей таблицы данных указывается после ключевого слова SELECT через запятую.

Выборка по условию

Условия выборки задаются конструкцией WHERE, подробное описание которой находится в файле документации Language References.

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

Пример 1. Необходимо выбрать сотрудников, работающих в первом отделе.

SELECT FIO

FROM Sotrudnik

WHERE Otdel=1;

Пример 2. Выберем ФИО тех сотрудников, которые работают в первом отделе и имеют более двух детей.

SELECT FIO

FROM Sotrudnik

WHERE (Otdel=1 AND Deti>2);

Преобразование данных при выборке

В ряде случаев при выборке данных из базы с ними необходимо провести некоторые преобразования. В InterBase для преобразования данных к указанному типу используется функция CAST, синтаксис функции:

CAST (<val> AS <datatype>),

где <val> - преобразуемое выражение,

<datatype> - явно указываемый тип данных.

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

Перечень возможных преобразований одних типов данных в другие приведен в таблице 1.

Таблица 1

Исходный тип Возможные преобразования
Numeric Character, VarChar, Date
Character, VarChar Numeric, Date
Date Character, VarChar
Blob, Arrays -

Пример. Преобразуем стаж работы сотрудника в символьный тип.

SELECT FIO, CAST(Stag AS CHARACTER(5))

FROM Sotrudnik;

Функция UPPER обеспечивает перевод текста в режим прописных букв, что бывает важно при сравнении текстов, сортировках и тому подобных случаях. Следует помнить, что символы стандартной латинской кодировки сравниваются корректно, для обработки букв других алфавитов необходимо явно указывать кодировку. Синтаксис UPPER:

UPPER(<val>)

<val> - символьное выражение.

Пример. Предположим, что при объявлении полей FIO и Dolg мы использовали следующие конструкции:

FIO CHARACTER(100) NOT NULL;

Dolg CHARACTER(30) NOT NULL COLLATE PXW_CYRL;

После использование следующего запроса, получим такой результат (рисунок 1).

SELECT UPPER(FIO), UPPER(Dolg)

FROM Sotrudnik;

Рисунок 1 - Пример использования функции UPPER

Как видно из рисунка 1, в первом столбце данные не изменились, во втором - функция UPPER сработала, поскольку при объявлении данного поля явно была указана кодировка.

Агрегирование данных при выборке

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

COUNT(* | [ALL] <val> | DISTINCT <val>) - подсчитывает количество строк, удовлетворяющих условиям запроса.

"*" подсчитывает количество строк, удовлетворяющих условиям запроса, включая NULL величины (подсчет не привязан к значениям конкретного поля).

[ALL] <val> или <val> (поскольку параметр ALL предполагается по умолчанию) подсчитывает количество строк, удовлетворяющих условиям запроса. Если таких строк нет, то возвращается значение NULL.

DISTINCT <val> подсчитывает количество строк, удовлетворяющих условиям запроса, в которых указанное в <val> выражение принимает различные значения.

Пример. Подсчитаем количество сотрудников организации (рисунок 2).

SELECT COUNT(Tab_No)

FROM Sotrudnik;

Рисунок 2

SUM ([ALL] <val> | DISTINCT <val>) - подсчитывает сумму значений указанного выражения по строкам, удовлетворяющим условиям запроса.

Пример. Подсчитаем общее количество детей сотрудников первого отдела (рисунок 3).

SELECT SUM(Deti)

FROM Sotrudnik

WHERE Otdel=1;

Рисунок 3

AVG ([ALL] <val> | DISTINCT <val>) - подсчитывает среднее значение выражения по строкам, удовлетворяющим условиям запроса.

MAX ([ALL] <val> | DISTINCT <val>) - подсчитывает максимальное значение выражения по строкам, удовлетворяющим условиям запроса.

MIN ([ALL] <val> | DISTINCT <val>) - подсчитывает минимальное значение выражения по строкам, удовлетворяющим условиям запроса.

Внутри запроса могут быть использованы простейшие арифметические операции.

Пример, подсчитаем среднее количество детей сотрудников в каждом отделе.

SELECT Otdel, CAST(SUM(Deti)/COUNT(Deti) AS NUMERIC(2,2)) AS Sr_kol

FROM Sotrudnik

GROUP BY Otdel;

Запросы по нескольким таблицам данных, объединение данных из разных таблиц

Очень часто бывает необходимо получить информацию не из одной таблицы, а из нескольких.

Lichnost(ID, Family, Name, Surname, Birthday) - содержит сведения о личности: идентификационный код, фамилия, имя, отчество, день рождения.

Tel(ID, Number, Comment) - содержит сведения о телефоне: номер, комментарий.

Adr(ID, Street, Home, Box, Comment) - содержит сведения о месте проживания личности: улица, дом, квартира, комментарий.

ID служит внешним ключом для связи отношений Tel и Adr с отношением Lichnost, поскольку между отношениями существует связь 1:М.

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

Пример. На рисунке 4 представлено содержимое таблиц Lichnost и Tel.

Рисунок 4

Рассмотрим, как работает следующий запрос, результаты которого представлены на рисунке 5.

SELECT Family, Number FROM Lichnost, Tel

Поскольку поля с названием Family, Number не дублируются в таблицах, указание из какой таблицы они взяты можно опустить.

Рисунок 5

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

Попробуем связать таблицы между собой по внешнему ключу. Связь можно задать, используя конструкцию WHERE. Поскольку поле ID дублируется в таблицах, необходимо указать, из какой таблицы оно используется.

SELECT Family, Number

FROM Lichnost, Tel

WHERE Lichnost.ID=Tel.ID;

В результате выполнения данного запроса получим следующее отношение (рисунок 6).

Рисунок 6

Для удобства обращения к таблицам и к полям таблицы данных можно использовать псевдонимы (alias). Задание псевдонима таблицы осуществляется следующим образом: после имени таблицы в списке FROM через пробел указывается псевдоним (не путать с псевдонимом самой базы данных в SQL Explorer).

Пример. Перепишем предыдущий запрос с использованием псевдонимов.

SELECT Family, Number

FROM Lichnost L, Tel T

WHERE L.ID=T.ID;

Задание псевдонима столбца осуществляется с использованием ключевого слова AS. Использование псевдонимов столбцов удобно в том случае, когда вам необходимо задать собственное имя столбца, а не использовать то, которое предлагает система, как это случилось в случае, представленном на рисунках 1-3.

Пример. Подсчитаем, сколько абонентов имеет номер телефона, начинающийся на 55 и живущих в квартире N 1, результат представлен на рисунке 7.

SELECT COUNT(*) AS Kol_vo

FROM Tel T, Adr A

WHERE (T.ID=A.ID) AND (T.Number LIKE '55%') AND (A.Box=1);


Заметим, что псевдоним столбца - это "декларативное" имя, поскольку его нельзя использовать в других конструкциях команды, таких как WHERE, GROUP, ORDER, HAVING.


Рисунок 7

Фильтрация и сортировка данных

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

Упорядочение данных

Полученные данные могут быть отсортированы по значениям одного или нескольких столбцов запроса с использованием конструкции ORDER BY.

[ORDER BY <order_list>]

Пример. Выведем отсортированный список абонентов и их телефонных номеров, сведения о которых занесены в базу данных (рисунок 8).

SELECT Family, Name, Surname, Number

FROM Lichnost L, Tel T

WHERE L.ID=T.ID

ORDER BY Family, Name, Surname

Рисунок 8

По умолчанию действует сортировка по возрастанию (ASC[ENDING]), однако можно указать явно порядок сортировки:

ASC[ENDING] - по возрастанию,

DESC[ENDING] - по убыванию.

Пример. Перепишем предыдущий запрос, но изменим порядок сортировки (рисунок 9).

SELECT Family, Name, Surname, Number

FROM Lichnost L, Tel T

WHERE L.ID=T.ID

ORDER BY Family DESC, Name, Surname

Рисунок 9

В команде ORDER BY можно использовать вместо имен столбцов их порядковые номера, как это было показано на рисунке 9.

Помимо сортировки используются и другие виды обработки данных. Мы уже рассматривали получение суммарных данных в запросах на основе агрегатных функций. При этом мы получаем единственную строку итогов по базе, однако в ряде случаев необходимо произвести агрегирование данных по заданной группе признаков. Для решения данного класса задач используется конструкция GROUP BY.

[GROUP BY col [COLLATE collation] [, col [COLLATE collation]]...]

Пример. Допустим, нам необходимо узнать, сколько номеров телефонов заведено на каждого абонента (рисунок 10).

SELECT Family, Name, Surname, COUNT(Number) AS Kol_vo

FROM Lichnost L, Tel T

WHERE L.ID=T.ID

GROUP BY Family, Name, Surname

Рисунок 10


Отметим, что в списке выборки (после ключевого слова SELECT) могут находиться только агрегируемые выражения и выражения, перечисленные в списке GROUP BY.


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

Пример. Скорректируем следующий запрос. Выведем список только, тех абонентов, которые имеют только один номер телефона. Такой запрос нельзя сформировать с помощью только одной конструкции WHERE, поскольку расчет количества телефонов выполняется в самом запросе, однако использовать конструкцию HAVING будет очень удобно.

SELECT Family, Name, Surname, COUNT(Number) AS Kol_vo

FROM Lichnost L, Tel T

WHERE L.ID=T.ID

GROUP BY Family, Name, Surname

HAVING COUNT(Number)=1

Рисунок 11