Текст книги "Основы использования и проектирования баз данных"

Поскольку БД является моделью реального мира, то отдельные элементы данных в ней в какой-то конкретный момент времени будут либо отсутствовать, либо не подходить для характеристики объекта, либо иметь неизвестные значения. Такое значение в SQL обозначается величиной NULL. Например, в столбце SUMD таблицы PERSON содержатся величины общих доходов для каждого жителя. Для нового жителя, который только что зарегистрирован, общий доход неизвестен, поэтому для него в качестве значения в столбце SUMD следует поставить значение NULL.

Язык SQL предназначен в первую очередь для выполнения запросов. Для построения SQL-запросов, задающих выборку данных, используется оператор SELECT, который является наиболее функциональным из всех операторов SQL.

Оператор SELECT читает данные из БД и возвращает их в виде таблицы результатов запроса (рис. 3.3).

Рис. 3.3. Выполнение SQL-запроса на чтение.

Оператор SELECT состоит из шести предложений, или фраз (рис. 3.4). Предложения SELECT и FROM являются обязательными. Четыре остальные включаются в оператор по мере необходимости. Функции каждого из предложений следующие:

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

2) в предложении FROM указывается список таблиц, которые содержат элементы данных, считываемые запросом;

3) предложение WHERE показывает, что в таблицу результатов запроса следует включать только строки, удовлетворяющие условию поиска;

4) предложение GROUP BY позволяет задавать итоговый запрос. Обычный запрос включает в таблицу результатов по одной строке для каждой строки из БД. Итоговый запрос вначале группирует строки БД по определенному признаку, а затем включает в таблицу результатов одну итоговую строку для каждой группы;

Рис. 3.4. Синтаксическая диаграмма оператора SELECT.

5) предложение HAVING показывает, что в таблицу результатов запроса следует включать только некоторые из групп, созданных с помощью предложения GROUP BY. Для отбора включаемых групп используется условие поиска;

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

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

Имя столбца идентифицирует один из столбцов, содержащихся в таблицах, которые перечислены в предложении FROM. Когда в качестве возвращаемого столбца указывается имя столбца из таблицы БД, то СУБД просто берет значение этого столбца для каждой из строк таблицы БД и помещает его в соответствующую строку таблицы результатов запроса.

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

Выражение показывает, что СУБД должна вычислять значение, помещаемое в таблицу результатов запроса, по формуле, заданной выражением.

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

SELECT 'ГРАЖДАНИН', FIO, 'ПОЛУЧАЕТ', SUMD, 'ПЛАТИТ', SUMD*0.13 FROM PERSON

будет создана таблица, показанная в табл. 3.2.

Таблица 3.2. Денежные доходы граждан

Количество строк в таблице результатов запроса может быть любым, в частности, равным нулю. Например, выполнение оператора

SELECT FIO FROM PERSON WHERE NOM<0

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

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

SELECT AVG (SUMD) FROM PERSON

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

То, что SQL-запрос всегда возвращает таблицу данных, очень важно с практической точки зрения. Во-первых, результаты запроса можно записать обратно в БД в виде таблицы. Во-вторых, таблицы результатов двух запросов, имеющие похожую структуру, можно объединить в одну таблицу. И в-третьих, таблица результатов запроса сама может стать объектом дальнейших запросов. Таким образом, табличная структура реляционной БД тесно связана с реляционными запросами: к таблицам можно посылать запросы, а запросы возвращают таблицы.

Нередко требуется получить содержимое всех столбцов таблицы, чтобы ознакомиться с ее структурой и хранимыми в ней данными. С учетом этого в операторе SELECT разрешается использовать символ звездочки, который означает, что требуется прочитать все столбцы из таблиц, перечисленных в предложении FROM. Например, содержимое таблицы PERSON можно получить с помощью оператора

SELECT * FROM PERSON

В этом случае таблица результатов запроса будет содержать все 6 столбцов таблицы PERSON, которые расположены в том же порядке, что и в исходной таблице, хранящейся в БД.

По умолчанию в таблицу результатов запроса включаются все строки, в том числе и повторяющиеся (см. ключевое слово ALL на синтаксической диаграмме рис. 3.4). Например, оператор

SELECT ADR FROM PERSON

возвращает адреса всех жителей, и среди этих адресов будут повторяющиеся, если в одной квартире проживают несколько человек.

Чтобы в таблице результатов запроса содержались только неповторяющиеся строки, в операторе SELECT перед списком возвращаемых столбцов записывается ключевое слово DISTINCT. Например, чтобы получить список разных адресов квартир, предыдущий оператор нужно дополнить этим ключевым словом:

SELECT DISTINCT ADR FROM PERSON

SQL-запросы, считывающие из таблицы все строки, полезны при просмотре базы данных и создании итоговых отчетов. Однако чаще требуется выбрать из таблицы базы данных несколько строк и включить в таблицу результатов запроса только их. Чтобы указать, какие строки требуется отобрать, следует использовать предложение WHERE.

Предложение WHERE содержит условие поиска, определяющее, какие именно строки требуется прочитать. Например, чтобы получить информацию о том, кто проживает в квартире по адресу «802-12», нужно выполнить оператор

SELECT FIO FROM PERSON WHERE ADR= '802 – 12'

Фактически условие поиска служит фильтром для строк таблицы. Строки, удовлетворяющие условию поиска, проходят через фильтр и становятся частью таблицы результатов запроса (рис. 3.5).

Рис. 3.5. Предложение WHERE в роли фильтра.

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

1. Сравнение. Значение одного выражения сравнивается со значением другого выражения.

2. Проверка на принадлежность диапазону значений. Проверяется, попадает ли указанное значение в определенный диапазон значений.

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

4. Проверка на соответствие шаблону. Проверяется, соответствует ли строковое значение, содержащееся в столбце, определенному шаблону.

5. Проверка на равенство значению NULL. Проверяется, содержится ли в столбце значение NULL.

Имеется шесть различных способов сравнения значений двух выражений (рис. 3.6). Например, можно вывести список жителей, родившихся после определенной даты:

SELECT FIO, ADR FROM PERSON

WHERE RDATE > '12/31/1970'

СУБД при сравнении значений двух выражений может вычислить один из таких результатов:

1) если сравнение истинно, то результат проверки имеет значение TRUE;

2) если сравнение ложно, то результат проверки имеет значение FALSE;

3) если хотя бы одно из двух выражений имеет значение NULL, то результатом проверки будет значение NULL.

В таблицу результатов запроса включаются только те строки, для которых условие поиска имеет значение TRUE. Например, если в предложении WHERE можно было бы задать условие SUMD = NULL, то в таблице результатов не оказалось бы ни одной строки, поскольку в этом случае результатом проверки был бы NULL для любых значений, имеющихся в столбце SUMD. Поэтому, чтобы не искушать пользователя, в сравнениях запрещено использовать ключевое слово NULL.

Рис. 3.6. Синтаксическая диаграмма сравнения.

В условие поиска этого вида входят три выражения (рис. 3.7):

1) выражение, которое задает проверяемое значение;

2) выражение, задающее нижний предел проверяемого диапазона;

3) выражение, задающее верхний предел проверяемого диапазона.

Типы данных во всех трех выражениях должны быть сравнимыми.

Рис. 3.7. Синтаксическая диаграмма проверки на принадлежность диапазону значений.

Например, формирование списка жителей, родившихся в I квартале 1955 г., задается оператором

SELECT FIO, ADR FROM PERSON

WHERE RDATE BETWEEN '01/01/1955' AND '03/31/1955'

При проверке на принадлежность диапазону верхний и нижний пределы считаются частью диапазона.

Для проверки выхода значения за пределы диапазона следует использовать ключевое слово NOT Например, список жителей, которые будут платить налоги меньше 100 руб. или больше 1200 руб., можно получить с помощью оператора

SELECT FIO, ADR FROM PERSON

WHERE (SUMD*0.13) NOT BETWEEN 100.00 AND 1200.00

В стандарте SQL1 определены следующие правила обработки значений NULL при проверке принадлежности диапазону (рис. 3.8):

1) если проверяемое выражение (а) имеет значение NULL либо оба выражения (б), определяющие диапазон, имеют значения NULL, то проверка BETWEEN возвращает значение NULL;

Рис. 3.8. Правила обработки значений NULL.

2) если выражение, определяющее нижний предел диапазона, имеет значение NULL, то проверка BETWEEN возвращает значение FALSE тогда, когда проверяемое значение больше верхнего предела диапазона, и значение NULL в противном случае;

3) если выражение, определяющее верхний предел диапазона, имеет значение NULL, то проверка BETWEEN возвращает значение FALSE, если проверяемое значение меньше нижнего предела, и значение NULL в противном случае.

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

Рис. 3.9. Синтаксическая диаграмма проверки на принадлежность множеству.

Например, следующий оператор выводит список жителей с заданными номерами:

SELECT * FROM PERSON

WHERE NOM IN (1,3,5,9)

Список жителей, родившихся в указанные дни, формируется оператором

SELECT * FROM PERSON

WHERE RDATE IN ('02/12/55' , '11/11/11')

С помощью проверки NOT IN можно обнаружить значения данных, не являющиеся членами заданного множества (см. учебную БД на рис. 2.11):

SELECT * FROM FLAT

WHERE KCATEGORY NOT IN ('П','К')

В результате будут выбраны строки таблицы FLAT со сведениями о квартирах категории 'Н', т. е. о неприватизированных квартирах.

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

Если результатом проверяемого выражения является значение NULL, то проверка возвращает значение NULL.

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

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

SELECT * FROM PERSON

WHERE FIO = 'Иванов Иван Иванович'

Однако, если необходимо получить сведения о всех Ивановых, то следует воспользоваться проверкой на соответствие шаблону (рис. 3.10).

Рис. 3.10. Синтаксическая диаграмма проверки на соответствие шаблону.

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

Подстановочный знак «%» совпадает с любой последовательностью из нуля или более символов. Например, чтобы получить сведения обо всех Ивановых, нужно воспользоваться шаблоном:

SELECT * FROM PERSON

WHERE FIO LIKE 'Иванов%'

Ключевое слово LIKE указывает, что необходимо сравнивать содержимое столбца FIO с шаблоном 'Иванов%', которому соответствуют все фамилии, начинающиеся словом «Иванов», в том числе «Ивановский». Чтобы получить сведения только о мужчинах Ивановых или только о женщинах Ивановых, следует использовать шаблон 'Иванов %' или 'Иванова %' соответственно.

Подстановочный знак «_» (подчеркивание) совпадает с любым отдельным символом. Например, для получения сведений о жителях, проживающих в 1-м микрорайоне, можно воспользоваться таким шаблоном:

SELECT * FROM PERSON

WHERE ADR LIKE 'Зеленоград, 1_ _-%'

Подстановочные знаки можно помещать в любое место шаблона, и в одном шаблоне может содержаться несколько подстановочных знаков.

С помощью конструкции NOT LIKE можно выбирать строки, которые не соответствуют шаблону.

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

Может случиться так, что среди символов, содержащихся в столбце, окажутся в качестве отдельных символов подстановочные знаки:

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

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

Непосредственно за символом пропуска может следовать либо один из двух подстановочных знаков, либо сам символ пропуска, поскольку он тоже приобретает в шаблоне особое значение.

Символ пропуска определяется в предложении ESCAPE в виде строки, состоящей из одного символа. В следующем примере в качестве символа пропуска используется литера «@», и шаблон обеспечивает выборку адресов, в которых встречается сочетание символов «А_В%» и которые заканчиваются литерой «@»:

Возможность использования символов пропуска реализована не во всех СУБД, поэтому применения предложения ESCAPE стараются избегать.

Результатом вычисления рассмотренных условий поиска может одно из трех значений: TRUE, FALSE или NULL (например, если один из столбцов содержит значение NULL). В ряде случаев бывает необходимо проверять значения в столбцах на равенство значению NULL и помещать соответствующие строки в таблицу результатов. Для этого в языке SQL предусмотрена специальная проверка на равенство значению NULL (рис. 3.11).

Puc. 3.11. Синтаксическая диаграмма проверки на равенство значению NULL.

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

SELECT * FROM TPHONE

WHERE ADR IS NULL

Инвертированная форма проверки на NULL позволяет отыскать строки, которые не содержат значения NULL. Например, список номеров телефонов, установленных в квартирах, формирует оператор

SELECT * FROM TPHONE

WHERE ADR IS NOT NULL

В отличие от условий поиска, рассмотренных ранее, проверка на NULL не может возвратить значение NULL в качестве результата. Она всегда возвращает TRUE или FALSE.

Все описанные простые условия поиска можно объединить в более сложные с помощью ключевых слов AND, OR, NOT. Возможность использования составных условий поиска предусмотрена правилами записи предложения WHERE (рис. 3.12), уточняющими ранее приведенную на рис. 3.4 форму этого предложения в составе оператора SELECT.

Рис. 3.12. Синтаксическая диаграмма предложения WHERE.

Ключевые слова AND, OR, NOT являются операторами логических операций И, ИЛИ, НЕ. С помощью этих операторов и круглых скобок можно создавать очень сложные условия поиска. Например, проверку на принадлежность множеству NOM IN (1, 3, 5, 9) можно заменить другим (менее удачным) условием поиска:

(NOM = 1) OR (NOM = 3) OR (NOM = 5) OR (NOM = 9)

Таблицы истинности для операторов AND, OR, NOT учитывают наличие значения NULL (табл. 3.3).

Таблица 3.3. Таблицы истинности

Строки таблицы результатов запроса, как и строки таблицы в БД, не имеют определенного порядка. Чтобы отсортировать таблицу результатов запроса, в оператор SELECT нужно включить предложение ORDER BY (рис. 3.13).

Рис. 3.13. Синтаксическая диаграмма предложения ORDER BY.

Например, таблица результатов следующего запроса отсортирована по двум столбцам ADR и FIO:

SELECT ADR, FIO, POL, SUMD FROM

PERSON ORDER BY ADR, FIO

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

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

В предложении ORDER BY можно задать возрастающий или убывающий порядок сортировки. По умолчанию, данные сортируются в порядке возрастания (ASC). Чтобы сортировать данные по убыванию, следует использовать ключевое слово DESC. Например:

SELECT * FROM PERSON ORDER BY SUMD DESC

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

SELECT FIO, ADR, SUMD*0.13

FROM PERSON

ORDER BY 3 DESC

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

SELECT FIO, ADR, POL, SUMD*0.13

FROM PERSON

ORDER BY POL DESC, 4 ASC

Иногда появляется необходимость объединения результатов двух или более запросов в одну таблицу. Язык SQL предусматривает такую возможность с помощью оператора UNION. Например, можно объединить результаты, выполнив следующий сложный оператор, и получить результат, показанный на рис. 3.14:

SELECT ADR, POL FROM PERSON

WHERE SUMD<2000.00

UNION

SELECT ADR, KCATEGORY FROM FLAT

WHERE SKV<30.00

Рис. 3.14. Использование оператора UNION.

Чтобы таблицы результатов запроса можно было объединить с помощью оператора UNION, они должны удовлетворять следующим требованиям:

1) таблицы должны содержать одинаковое число столбцов;

2) тип данных каждого столбца первой таблицы должен совпадать с типом данных соответствующего столбца во второй таблице;

3) ни одна из таблиц не может быть отсортирована с помощью предложения ORDER BY, однако объединенные результаты запроса можно отсортировать.

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

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

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

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

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

Оператор UNION можно использовать многократно, чтобы объединить результаты трех или более запросов, например:

SELECT * FROM А

UNION (SELECT * FROM В UNION SELECT * FROM C)

Скобки показывают, какой оператор UNION должен выполняться первым.

Если используются одинаковые формы оператора UNION (т. е. только UNION либо только UNION ALL), то порядок выполнения операторов не имеет значения. Следующие операторы полностью эквивалентны:

A UNION (В UNION С)

(A UNION В) UNION С

(A UNION С) UNION В

Эквивалентными являются и такие операторы:

A UNION ALL (В UNION ALL С)

(A UNION ALL В) UNION ALL С

(A UNION ALL C) UNION ALL В

Однако если в запросы на объединения входят как операторы UNION, так и операторы UNION ALL, то порядок следования этих операторов имеет значение. Если операторы A UNION ALL В UNION С проинтерпретировать как А UNION ALL (В UNION С), то таблица результатов может содержать повторяющиеся строки. Однако если эти операторы проинтерпретировать как (A UNION ALL В) UNION С, то в таблице результатов повторяющихся строк не будет.

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