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

Базовый синтаксискоманды SELECT

Дадим теперь полный синтаксис команды SELECT.

SELECT [TRANSACTION Transaction]

[{DISTINCT | ALL]} LIST_<val>

[into LIST_Vars]

from LIST_<TableRef>

[WHERE <SearchCondition>]

[GROUP BY LIST_<Column>]

[HAVING <SearchCondition>]

[UNION <SelectExpr> [ALL] ]

[PLAN <PlanExpr>]

[order BY LIST_<Orders>]

[FOR UPDATE /0/-’LIST_Col ] ] ;

<Val> ::= {

Col [ <Array_dim> ] | : Variable

| <Constant> | <Expr> | <Function> j udf f [LIST_<Val>] )

| NULL | USER | RDB$DB KEY | ?

} [COLLATE Collation] [ [AS] Alias]

Col - имя столбца,

<Array_dim> ;:= [LIST_Dim]

Dim = fx ■] у у - задает размерность массива.

х - задает нижнюю границу массива (если задано х:у, то индекс в масс иве меняется от х до у)

<constant> ;;=число I ' строка1 I Charsetname ' строка' Charsetname - имя используемого набора символов, например WIN1251, WIN1252 и т.д.

<ехрг> ■; .•= Любое корректное SQL выражение, дающее в результате единственное значение.

<function> : := {

COUNT (* | [ALL] <val> | DISTINCT <val>)

| SUM <val> | DISTINCT <val>)

| AVG ( [AIiL] <val> | DISTINCT <val>)

| MAX ( [ALL] <val> | DISTINCT <val>)

^ ([Abb] <val> | DISTINCT <val>)

| CAST ( <val> AS <datatype>)

| UPPER ( <val>)

| GEN_ID (generator, <val>)

}

В версии InterBase 6 добавлена также такая функция как:

EXTRACT(part FROM <DTExpr>)

Управление доступом в InterBase на основе SQL 35

part ::= DAY | HOUR | MINUTE | MONTH | SECOND | WEEKDAY

| YEAR | YEARDAY }

<DTExpr> ::= Любое корректное SQL выражение, дающее в результате единственное значение типа дата - время.

Vars : := :Var

<tableref> ::= <joined_table> | table | view | procedure [( [LIST_<Val>] ) ] [alias]

<joined_table> ::= <tableref> <join_type> JOIN <tableref>

ON <search_condition> | (<joined_table>)

<join-type> ::= { [inner] | {LEFT | RIGHT | FULL}

[OUTER] } JOIN

<search_condition> : := {<val> <operator>

{<val> | (<select_one>)}

| <val> [NOT] BETWEEN <val> AND <val>

| <val> [NOT] LIKE <val> [ESCAPE <val>]

| <val> [NOT] IN ( [LIST_<Val>] | <select_list>)

| <val> IS [NOT] NULL

| <val> { [NOT] { | < | >} | >= | < = j

{ALL | SOME | ANY} (<select_list>)

| EXISTS (<select_expr>)

| SINGULAR (<select_expr>)

| <val> [NOT] CONTAINING <val>

| <val> [NOT] STARTING [WITH] <val>

| (<search_condition>)

| NOT <search_condition>

| <search_condition> OR <search_condition>

| <search_condition> AND <search_condition>}

<operator> ::= { | < | > | <= | >= | !< | !> | <> | !*}

<select_one> : := SELECT с одним столбцом, возвращающий одну строку,

<select_list> ::= SELECT с одним столбцом, возвращающий несколько (возможно 0) строк,

<select_expr> ::= ‘SELECT с 11 несколькими столбцами,

возвращающий несколько (возможно 0) строк.

<p'lan_expr> : : =

[JOIN | merge] (LIST_<plans>]

<plans'> : : = <plan_item> | <plan_expr>

<plan_item> ::= {table: |i alias}

NATURAL' I INDEX' (LIST_index] I ORDER index

<Orders> : :=■

{col | int} [COLLATE: collation] [ASC [ENDING 1 |

DESC [ENDING] ]!

Отметим, , что . конструкции INTO и FOR UPDATE доступны только' в- процедурах и триггерах . базы ; данных, , либо во внедренном' SQL в ■ приложениях.

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

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

SELECT * FROM <tableref>;

* после SELECT означает выбор всего набора полей таблицы, имя которой задано в tableref.

Пример 3.11

SELECT * FROM TREADER;

Таблица 3.1. . Список читателей' (включая служебные данные)

UNIKEY RDNUMB RDNAME
36 : 1267-89 Арцибашев . С..
37 1369-99 Светлова В.
38 1456-00- Стародуб . Е.
39 1273-92 Гребенкина Н.
40 1400-00 Пащенко. О.
83 1401-99 Грамотный Н.Е.

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

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

SELECT LIST_<val> ERCM <tableref>;

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

Пример 3.2

SELECT RDNUMB , RDNAME FROM TREADER;

Таблица 3.2. Список читателей (выборочные данные)

RDNUMB RDNAME
1267-89 Арцибашев С.
1369-99 Светлова В.
1456-00 Стародуб Е.
1273-92 Гребенкина Н.
1400-00 Пащенко О.
1401-99 Грамотный Н.Е.

А теперь отсортируем их по фамилиям, указав имя поля, по которому ведется сортировка,

SELECT RDNUMB , RDNAME FROM TREADER ORDER BY RDNAME;

или просто его порядковый номер в списке полей выборки (иногда это единственно возможное решение).

Пример 3.3

SELECT RDNUMB ,RDNAME FROM TREADER ORDER BY 2;

Таблица 3.3. Список читателей (выборочные отсортированные данные)

RDNUMB RDNAME
1267-89 Арцибашев С.
1401-99 Грамотный Н.Е.
1273-92 Гребенкина Н.
1400-00 Пащенко О.
1369-99 Светлова В.
1456-00 Стародуб Е.

Пока мы брали из таблицы все ее строки, но это нужно далеко не всегда. Выберем теперь только новых читателей (Номер билета >1300).

Пример 3.4

SELECT RDNUMB , RDNAME FROM TREADER where RDNUMB>='1400' ORDER BY 2;

Таблица З.4. Выборка из списка читателей (выборочные отсортированные данные)

RDNUMB RDNAME
1401-99 Грамотный Н.Е.
1400-00 Пащенко О.
1456-00 Стародуб Е.

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

SELECT * FROM TREADER, TBOOK;

Таблица 3.5. Выборка списка читателей и книг

unikey rdnumb rdname unikey 1 mother-

key

booknm Re-

ferat

36 1267-89 Арцибашев С. 2 0 Программирование
37 1369-99 Светлова В. 2 0 Программирование
38 1456-00 Стародуб Е. 2 0 Программирование
39 1273-92 Гребенкина Н. 2 0 Программирование
40 1400-00 Пащенко О. 2 0 Программирование
83 1401-99 Грамотный Н.Е. 2 0 Программирование
36 1267-89 Арцибашев С. 3 0 Учебники
37 1369-99 Светлова В. 3 0 Учебники

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

SELECT TREADER.rdnumb, TREADER.rdname, TBOOK.booknm FROM TREADER, TBOOK, TBOOK_READER where tbook_reader.reader=treader.unikey and

tbook_reader.bookkey=tbook.unikey;

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

SELECT a.rdnumb, a.rdname, b.booknm FROM TREADER a, TBOOK b, TBOOK_READER ab where ab.reader=a.unikey and ab.bookkey=b.unikey;

Таблица 3.6. Выборка списка читателей и взятъх ими книг

RDNUMB RDNAME BOOKNM
1267-89 Арцибашев С. Тайна
1267-89 Арцибашев С. Математические вопросы динамики вязкой несжимаемой жидкости
1267-89 Арцибашев С. Тесты. Сборник 11 класс. Варианты и ответы государственного тестирования. Пособие для подготовки к тестированию
1369-99 Светлова В. Тайна
1456-00 Стародуб Е. ЯзыкC++
1273-92 Гребенкина Н. The history of England. Absolute Monarchy
1400-00 Пащенко О. Введение в технологию ATM
1400-00 Пащенко О. Word 6 for Windows

Книги, правда, получились без авторов. Попробуем теперь вытащить фамилии авторов.

Пример 3.7

SELECT a.rdnumb, a.rdname, b.booknm, c.AUNAME FROM TREADER a, TBOOK b, TBOOK_READER ab,

TAUTHOR c, TBOOK_AUTHOR be where ab.reader=a.unikey and ab.bookkey=b.unikey and b.unikey= bc.bookkey and be.author=c.author;

Таблица З.7. Выборка списка читателей и взятых ими книг с указанием авторов

RDNUMB RDNAME BOOKNM AUNAME
1400-00 Пащенко О. Word 6 for Windows Фаненштих Клаус
1400-00 Пащенко О. Word 6 for Windows Хаселир Райнер Г.
1456-00 Стародуб Е. ЯзыкC++ Подбельский Вадим Валериевич
RDNUMB RDNAME BOOKNM AUNAME
1400-00 Пащенко 0. Введение в технологию ATM Деманж Мишель
1400-00 Пащенко 0. Введение в технологию ATM Буассо Марк
1400-00 Пащенко 0. Введение в технологию ATM Мюнье Жан- Мари
1273-92 Гребенкина Н. The history of England. Absolute Monarchy Бурова И.И.
1267-89 Арцибашев С. Тесты. Сборник 11 класс. Варианты и ответы государственного тестирования. Пособие для подготовки к тестированию без авторов
1267-89 Арцибашев С. Математические вопросы динамики вязкой несжимаемой жидкости Ладыжинская Ольга Александровна
1369-99 Светлова В. Тайна Хмелевская Иоанна
1267-89 Арцибашев С. Тайна Хмелевская Иоанна

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

Пример 3.8

SELECT a.rdnumb, a.rdname, b.booknm, min(с.AUNAME)

FROM TREADER a, TBOOK b, TBOOK_READER ab, TAUTHOR c, TBOOK_AUTHOR be where ab.reader=a.unikey and ab.bookkey=b.unikey and b.unikey= bc.bookkey and be.author=c.author GROUP BY a.rdnumb, a.rdname, b.booknm

Таблица 3.8. Выборка списка читателей и взятых ими книг с указанием первого автора

RDNUMB RDNAME BOOKNM MIN
1267-89 Арцибашев С. Математические вопросы динамики вязкой несжимаемой жидкости Ладыжинская Ольга Александровна
1267-89 Арцибашев С. Тайна Хмелевская Иоанна
1267-89 Арцибашев С. Тесты. Сборник 11 класс. Варианты и ответы государственного тестирования. Пособие для подготовки к тестированию без авторов
1273-92 Гребенкина Н. The history of England. Absolute Monarchy Бурова И.И.
1369-99 Светлова В. Тайна Хмелевская Иоанна
1400-00 Пащенко О. Word 6 for Windows Фаненштих Клаус
1400-00 Пащенко О. Введение в технологию ATM Буассо Марк
1456-00 Стародуб Е. ЯзыкC++ Подбельский Вадим Валериевич

Использование функции MIN позволило нам выбирать первого в алфавитном списке автора из группы. Группировка задается перечнем полей, не охватываемых агрегатными функциями, которые необходимо перечислить в опции GROUP BY.

Перейдем теперь к более систематическому рассмотрению команды SELECT.

SELECT ПО ОТДЕЛЬНОЙ ТАБЛИЦЕ. ЗАДАНИЕ ВЫБИРАЕМЫХ ПОЛЕЙ Рассмотрим подробнее выборку данных из отдельной таблицы: SELECT [distinct | ALL] {* | LIST_<val>}

HCM <tableref>

Символ * задает выборку всех полей таблицы. Примером может служить уже рассмотренная конструкция примера 3.1:

SELECT * FROM TREADER;

Результат ее работы приведен в табл. 3.1.

Для задания конкретного перечня столбцов их необходимо задать явно, например, как в примере 3.2:

SELECT RDNUMB , RDNAME FROM TREADER;

Результат ее работы приведен в табл. 3.2.

Теперь несколько слов о параметрах запроса DISTINCT и ALL. Значение ALL (если не указано ничего, то принимается ALL) означает, что будут выбраны все строки таблицы, удовлетворяющие условиям выборки. Об условиях выборки мы поговорим чуть позже.

Значение DISTINCT означает, что будут выбраны только различные строки таблицы. Рассмотрим пример выборки из таблицы читателей, имеющих на руках книги, с параметрами DISTINCT и ALL. Выбирать будем их индивидуальные коды (для выбора фамилий обойтись одной таблицей нельзя, такую выборку мы рассмотрим чуть дальше).

Пример 3.9

select ALL reader from TBOOK_READER select DISTINCT reader from TBOOK_READER

Таблица 3.9. Выборка списка читателей с условиями ALL и DISTINCT

select ALL readerfrom TBOOK_READER select DISTINCTreaderfrom TBOOK_READER
READER READER
36 36
36 37
36 38
37 39
38 40
39
40
40

условия ВЫБОРКИ Условия выборки задаются конструкцией WHERE <search_oonditioii>].

<search_condition> : := {<val> <operator>

{ <val> | ( <select_one>)}

| <val> [NOT] BETWEEN <val> AMD <val>

| <val> [NOT] LIKE <val> [ESCAPE <val>]

| <val> [NOT] IN (LIST_<val> | <select_list>)

| <val> IS [NOT] NULL

| <va l > { [NOT] {= | < | >} | >= | <=}

{all | SOME | ANY} (<select_list>)

| EXISTS ( <select_expr>)

| SINGULAR ( <select_expr>)

| <val> [NOT] CONTAINING <val>

| <val> [NOT] ^^^TING [WITH] <val>

| ( <search_condition>)

| NOT <search_condition>

| <search_condition> OR <search_condition>

| ^^^^^^^^ondition> AND <search_condition>}

Первый случай: <val> <operator> <val>

<operator> : : = { = | < | > | <= | >= | !< | !> | <> | | о |

!=})

Пример , .3.^.Д

select UNIKEY, BOOKNM from TBOOK where MATHERKEY= 5;

Таблица 3.10. Список книг - беллетристики

UNIKEY BOOKNM
17 Кровь нерожденных
18 Тайна

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

Например, выберем всех читателей, чья фамилия лежит в диапазоне от А до Н.

Ппимеп 3.11

select RDNUMB, RDNAME from TREADER

where RDNAME>='A' AND RDNAME< 0 ’ ;
Таблица 3.11. Список читателей от А до Н чья фамилия лежит в диапазоне
RDNUMB RDNAME
1267-89 Арцибашев С.
1273-92 Гребенкина Н.
1401-99 Грамотный Н.Е.

Другой случай: <val> <operator> (<select_one>) аналогичен первому, только здесь вместо константы, имени столбца или выражения стоит оператор select, обеспечивающий выборку единственного выражения, которое и участвует в сравнении. В конструкции select_one используем агрегатную функцию SUM, вычисляющую сумму выражений в скобках по таблице.

Пример 3.12

select UNIKEY, BOOKNM from TBOOK

where 2<(select SUM(BNUMBER) from TBOOK_PLACE where BOOKKEY=TBOOK.UNIKEY);

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

UNIKEY BOOKNM
17 Кровь нерожденных
18 Тайна

Третий случай: <val> [NOT] BETWEEN <vall> AND <val2> полностью эквивалентен конструкции NOT/(<va/> >= <val1> AND <val> <= <val2>).

Например, с помощью BETWEEN пример 3.11 можно переписать в виде Ппимеп 3.11-1

select RDNUMB, RDNAME from TREADER where RDNAME BETWEEN 'A' AND 'H';

Четвертый случай: <vall> NOT LIKE <val2> [ESCAPE <val3>] обеспечивает контекстный поиск в символьных выражениях. Конструкция <vall> LIKE <val2> принимает значение истина, если текст <val2> идентичен в определенном смысле тексту <val1>. Для задания текста в <val2> можно использовать помимо обычных символов и символы-заполнители "%" и "_". Символ заменяет любой единичный символ, символ "%" заменяет любое число символов.

'Жил был у бабушки козел Go_home' LIKE 'ил' - ложь, тексты не совпадают.

'Жил был у бабушки козел Go_home' LIKE '%ил%' - истина, тексты с учетом заполнителя "%" совпадают.

'Жил был у бабушки козел Go_home' LIKE '_ил%' - истина, тексты , с учетом заполнителей "%" и "_" совпадают.

'Жил был у бабушки козел Go_home' LIKE '%ыл%' - истина, тексты с учетом заполнителя "%" совпадают.

'Жил был у бабушки козел Go_home' LIKE '_ыл%' - ложь, тексты с учетом заполнителей "%" и "_" не совпадают ("_" заменяет только первый символ, в данном случае "Ж").

'Жил был у бабушки козел Go_home' LIKE '%ил%_ыл%' - истина, тексты с учетом заполнителей "%" и "_" совпадают.

Проблема с таким поиском может возникнуть только в том случае, если исходный текст содержит сами заполнители. В нашем примере символ-заполнитель содержится в имени уважаемого козла - "Go_home". Чтобы обойти эту проблему, в шаблоне соответствия <val2> необходимо уметь отличать искомые символы от символов заполнителей. Для этого можно задать управляющий символ, который бы не участвовал в поиске, а только помечал, что следующий за ним символ является не заполнителем, а поисковым. Это обеспечивается конструкцией ESCAPE. Так

<vall> LIKE - истина для любых <vall>.

'Жил был у бабушки козел Go_home' LIKE '%#_%' escape '#' - истина, тексты с учетом заполнителей "%" совпадают (заполнителя "_" здесь нет!).

'Жил был у бабушки козел Go tome' LIKE '%#_%' escape '#' - ложь, тексты с учетом заполнителей "%" не совпадают (заполнителя "_" здесь нет, а исходный текст не содержит "_"!).

Пятый случай: <vall> [NOT] IN (LIST_<val>| <select_list>) задает проверку условия принадлежности <vall> к списку <val2_l>, Icval2_2>___].

Например,

12 in (10, 23, 16) - ложь.

12 in (10, 23, 12) - истина.

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

Ппимеп 3.13

SELECT rdname, rdnumb from treader where unikey in

(select reader from TBOOK_READER where bookkey=18) ;

Таблица 3.13. Выборка читателей заданной книги

RDNAME RDNUMB
Арцибашев С. 1267-89
Светлова В. 1369-99

Шестой случай: <vall> IS [NOT] NULL - проверка на пустое значение (не заполнено).

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

<val> {[NOT] {= | < | >} | >= | <=} {all | SOME | ANY}

(<select_list>)

Здесь с заданным выражением сравниваются результаты одностолбцовой выборки.

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

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

Ппимеп 3.14

SELECT a.reader, a.bookkey, г.rdnumb, г.rdname

from tbook_reader a, treader r where a.reader=r.unikey and

a.bookkey=ANY(SELECT unikey from tbook where matherkey = 5) ;

Таблица 3.14. Список читателей беллетристики

READER BOOKKEY RDNUMB RDNAME
36 18 1267-89 Арцибашев С.
37 18 1369-99 Светлова В.

Восьмой случай реализуется с использованием дополнительного подзапроса [NOT] EXISTS(<select_expr>). Выражение

EXISTS(<select_expr>) принимает значение истина, если в результате выполнения подзапроса находится, по крайней мере, одна строка.

Пример 3.15

SELECT BOOKNM, unikey from TBOOK where matherkey!= and not EXISTS

(select * from TBOOK_READER where bookkey=TBOOK.unikey)

Таблица 3.15. Список книг, не востребованных читателями

BOOKNM UNIKEY
Макрокоманды MS Word 6
Введение в C++ Builder 9
Borland-Технологии. SQL-Link InterBase, Paradox for Windows, Delphi 10
С и C++ Справочник 11
Справочник по правописанию и литературной правке 14
Кровь нерожденных 17

Девятый случай реализуется с использованием дополнительного подзапроса [NOT] SINGULAR (<select_expr>). Выражение SINGULAR (<select_expr>) принимает значение истина, если в результате выполнения подзапроса находится в точности одна строка.

Пример 3.16

SELECT BOOKNM, unikey from TBOOK where matherkey!=0 and

SINGULAR

(select * from TBOOK_READER where bookkey=TBOOK.unikey)

Таблица 3.16. Список книг, имеющих единственного читателя

BOOKNM UN1KEY
Word 6 for Windows 1
ЯзыкC++ 8
Введение в технологию ATM 12
The history of England. Absolute Monarchy 13
Тесты. Сборник 11 класс. Варианты и ответы государственного 15
тестирования. Пособие для подготовки к тестированию
Математические вопросы динамики вязкой несжимаемой жидкости 16

Десятый случай: <vall> [NOT] CONTAINING <val2> обеспечивает контекстный поиск в символьных выражениях. Конструкция <vall> CONTAINING <val2> принимает значение истина, если текст <val2> содержится в тексте <val 1 >.

Пример 3.17

SELECT BOOKNM, unikey from TBOOK where BOOKNM CONTAINING ’C++'

Таблица 3.17. Список книгпо C + + (содержащих C + + в названии)

BOOKNM UNIKEY
ЯзыкC++ 8
Введение в C++ Builder 9
С и C++ Справочник 11

Одиннадцатый случай: <vall> NOT STARTING /WITH7 <val2>

также обеспечивает контекстный поиск в символьных выражениях. Конструкция <vall> STARTING [WITH.] <val2> принимает значение истина. если текст <vall> начинается с текста <val2>.

Пример 3.18

SELECT BOOKNM, unikey from TBOOK where BOOKNM STARTING WITH 'Ma'

Таблица 3.18. Список книг с названием, начинающимся с "Ма "

BOOKNM UNIKEY
Математика 4
Макрокоманды MS Word 6
Математические вопросы динамики вязкой несжимаемой жидкости 16

ПРЕОБРАЗОВАНИЕ ДАННЫХ ПРИ ВЫБОРКЕ ,

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

Функция CAST обеспечивает преобразование данных к явно указанному типу:

CAST (<val> AS <datatype>),

где <val> - преобразуемое выражение, <datatype> - явно указываемый тип данных.

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

В InterBase используются следующие типы данных:

SMALLINT

INTEGER

FLOAT

DOUBLE PRECISION

DECIMAL(precision [, scale])

NUMERIC(precision [, scale])

DATE

CHAR [ (1...32767) ] или CHARACTER[ (1...32767) ]

CHARACTER VARYING [ (1...32767) ] или VARCHAR

NCHAR [VARYING] [ (1...32767) ] или NATIONAL CHARACTER [VARYING] [ (1...32767) ] или NATIONAL CHAR [VARYING]

[ (1...32767) ]

BLOB [ SUB_TYPE { i n t | subtype_name} ] [ SEGMENT SIZE i n t ]

BLOB [ ( seglen [, subtype])] ,

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

Пример 3.19

SELECT BOOKNM,CAST(unikey as DOUBUE PRECISION)* 3.1415926535 from TBOOK where BOOKNM STARTING WITH ' Ma'

Таблица 3.19. Использование функции CAST

BOOKNM F
Математика 12,566
Макрокоманды MS Word 18,850
Математические вопросы динамики вязкой несжимаемой жидкости 50,265

Вообще функция достаточно полезна везде, где используемые данные должны быть строго определенного типа. При преобразовании необходимо, конечно, помнить, что, во-первых, не все преобразования возможны в принципе и, во-вторых, при преобразованиях возможна потеря или искажение информации. Дело не в ошибках преобразований, а в том, например, что при преобразовании вещественного числа в целое мы обязательно потеряем дробную часть, при преобразовании текста из 100 символов в текст из 60, мы потеряем последние 40 символов. Возможно, это именно то, что мы и хотим, а возможно, и нет. Так что прежде чем проводить преобразования, всегда стоит немножко подумать, зачем мы это делаем и к каким искажениям данных это может привести.

Функция UPPER обеспечивает перевод текста в режим только прописных букв (верхний регистр). Последнее бывает важно при операциях сравнения текстов, сортировках и тому подобных случаях, когда нам не нужно различать данные, набранные в разных регистрах. При этом нужно помнить, что стандартная латынь всегда обрабатывается корректно, а для обработки букв национальных алфавитов необходимо явно указывать используемую кодировку (см. CHARACTER SET и COLLATE).

Формат UPPER:

Upper ( <val>)

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

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

Пример 3.20

Таблица 3.20. Использование функции UPPER

SELECT BOOKNMfгот TBOOK where unikey-6 or unikey=7; SELECT upperiBOOKNM) from TBOOK where unikey=6 or unikey=7;
Макрокоманды MS Word Word 6 for Windows Макрокоманды MS WORD WORD 6 FOR WINDOWS

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

Кроме рассмотренных выше функций в операторе SELECT может быть также использована функция GEN_ID, а также дополнительно подключенные пользовательские функции (User Defined). Они могут быть использованы везде, где по синтаксису оператора предполагается конструкция <val>. Применение функции GEN_ID мы рассмотрим подробнее в разделе о триггерах, а пользовательские - в разделе, посвященном подготовке и использованию пользовательских функций.

АГРЕГИРОВАНИЕ ДАННЫХ ПРИ ВЫБОРКЕ При обработке больших объемов информации часто интересуют не детальные данные, а некоторые их обобщения, такие как суммарные показатели, средние, минимальные и т.п.

Нечто подобное уже встречалось в примерах 3.8 и 3.12.

Прежде всего приведем перечень агрегатных функций:

COUNT (* | [ALL] <val> | DISTINCT <val>)

SUM ( [ALL] <val> | DISTINCT <val>)

AVG ( [ALL] <val> | DISTINCT <val>)

MAX ( [ALL] <val> | DISTINCT <val>)

MIN ( [ALL] <val> | DISTINCT <val>)

Функция COUNT подсчитывает количество строк, удовлетворяющих условиям запроса.

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

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

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

Подсчитаем количество читателей в нашей библиотеке Пример 3.21

select COUNT(ALL UNIKEY) FROM TREADER;

ИЛИ

select COUHT(*) FROM TREADER;

ИЛИ

select COUNT (DISTINCT UNIKEY) FROM TREADER;

Всё равно, что применить, поскольку строки в таблице TREADER различны.

Результат будет 6.

Другое дело, если нас интересуют читатели, взявшие книги.

Пример 3.22

select CODNT(*) FROM TBOOk_READER;

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

Пример 3.23

select СОШТ (DISTINCT READER) FROM TBOOk_READER;

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

Пример 3.24

select COUNT(DISTINCT BOOKKEY) FROM TBOOkJREADER;

Результат будет 7, и он отражает общее количество различных книг, выданных читателям.

Функция SUM подсчитывает сумму значений указанного выражения по строкам, удовлетворяющим условиям запроса.

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

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

Рассмотрим модификацию примера 3.21, заменив COUNT(*) на SUM(1):

Пример 3.25

select SUM(1) FROM TREADER;

Результат будет 6.

Посмотрим, что будет, если использовать режим DISTINCT.

Пример 3.26

select SUM(DISTINCT 1) FROM TREADER;

Результат будет 1!

А что если к суммируемому полю добавить другие поля? Попробуем. Пример 3.27

select SUM(l), RDNAME FROM TREADER;

Результат: Dynamic SQL Error. SQL error code = -104. invalid column reference.

Это несколько не то, что хотелось бы получить, но что, собственно, мы ожидали. SUM должна дать одно значение на группу строк, поле RDNAME - одно значение на каждую строку, а это не одно и то же.

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

Функция AVG подсчитывает среднее значение указанного выражения по строкам, удовлетворяющим условиям запроса.

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

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

Подсчитаем среднее количество экземпляров книг в нашей библиотеке.

_Управление доступом в InterBase на основе SQL 55

Пример 3.28

SELECT AVG(BNUMBER) FROM TBOOK_PLACE;

Результат будет 2. Число подозрительно "круглое". Дело в том, что функция AVG дает результат того же типа, что и величины, участвующие в суммировании, а они в нашем случае - целые, поэтому наш результат является следствием округления подлинного среднего. Для получения точного результата необходимо явно указать тип обрабатываемых величин. Для указания преобразования используется функция CAST. Ее формат: CAST(<val> AS <тип>), <val> задает преобразуемое выражение, <тип> - тип к которому нужно преобразовать выражение. Подробнее формат CAST и типы данных мы рассмотрим позже, а пока рассмотрим соответствующую модификацию примера 3.28.

Пример 3.29

SELECT AVG(CAST(BNUMBER as double precision)) FROM TBOOK_PLACE;

Результат будет 1,84615384615385.

А теперь то же самое с опцией DISTINCT.

Пример 3.30

SELECT AVG(DISTINCT CAST(BNUMBER as double precision) )

FROM TBOOK_PLACE;

Результат будет 2. Отметим, что «это 2» - не «то 2», что в примере 3.28, а отражение того факта, что число экземпляров в нашей библиотеки колеблется от 1 до 3, а (1+2+3)/3=2.

И еще одно замечание. Вместо AVG(x) можно использовать SUM(x)/COUNT(*), с той лишь разницей, что здесь нет необходимости в приведении типов.

Пример 3.31

SELECT SUM(BNUMBER)/COUNT(BNUMBER) FROM TBOOK_PLACE;

Результат будет 1,84615384615385.

•Примерв.32

SELECT SUM(DISTINCT BNUMBER)/COUNT(DISTINCT BNUMBER)

FROM TBOOK_PLACE;

Результат будет 2.

Функции MAX и MIN подсчитывают соответственно максимальное и минимальное значения указанного выражения по строкам, удовлетворяющим условиям запроса.

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

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

Разницы между ALL и DISTINCT в данном контексте нет никакой, тем не менее, в документации их описания выделены. Если Вы сумеете понять почему, напишите мне, не хочется оставаться недоумком.

Обобщим примеры 3.29-3.31, выбирая в запросе среднее количество экземпляров книг в нашей библиотеке, общее число книг, а также минимальное и максимальное количество экземпляров одной книги.

Пример 3.33

SELECT SUM(BNUMBER), SUM(BNUMBER)/COUNT(BNUMBER),

MAX(BNUMBER), MIN(BNUMBER)

FROM TBOOK_PLACE;

Таблица 3.21. Характеристики книгохранилища

SUM F_1 MAX MIN
24 1,84615384615385 3 1

SELECT ПО НЕСКОЛЬКИМ ТАБЛИЦАМ. СПОСОБЫ ОБЪЕДИНЕНИЯ ДАННЫХ ИЗ РАЗНЫХ ТАБЛИЦ. ВНЕШНИЕ И ВНУТРЕННИЕ ОБЪЕДИНЕНИЯ Очень часто бывает необходимо в одном запросе получить данные, которые хранятся в различных таблицах.

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

В примере 3.5

SELECT * FROM TREADER, TBOOKS;

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

Управление доступом в InterBase на основе SQL 57

Чтобы увязать их, необходимо указать способ объединения. _В данном случае это можно сделать, используя данные таблицы TBOOKREADER, строки которой связывают читателя со взятой им книгой. С другой стороны данные о читателях (таблица TREADER) можно связать с TBOOKREADER по значениям кода читателя (READER в таблице TBOOKREADER и UNIKEY в таблице TREADER), а данные о книгах -по коду книги (BOOKKEY в таблице ТВООК READER и UNIKEY в таблице ТВООК).

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

SELECT TREADER.rdnumb, TREADER.rdname, TBOOK.booknm I ROM TREADER, TBOOK, TBOOK_READER where tbook_reader.reader=treader.unikey and

tbook_reader.bookkey=tbook.unikey;

Вместо довольно длинного имени таблицы можно указать ее алиас (псевдоним). При этом, конечно, нужно задать сам алиас. Задание алиаса таблицы осуществляется совсем просто: после имени таблицы в списке FROM через пробел указывается алиас.

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

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

SELECT а.rdnumb, а.rdname, b.booknm FROM TREADER а, ТВООК b, TBOOK_READER ab where ab.reader=a.unikey and ab.bookkey=b.unikey;

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

Другим приемом связывания данных является указание способа объединения таблиц в списке <tableref > после конструкции FROM. Формат такого объединения имеет вид

<joined_table> ::= <tableref> <join_type> JOIN <tableref>

ON <search_condition> | (<joined_table>)

<join-type> ::= {[INNER] | {LEFT | RIGHT | FULL }

[OUTER] } JOIN

JOIN ... ON - соединение строк таблиц на основе условия, заданного после ON.

Тип соединения задается ключевым словом INNER или OUTER; если ни одно из них не указано, то принимается INNER.

Тип соединения INNER задает "внутреннее соединение". В таблицах соединяются только те строки, для которых выполняется

<search_condition>.

Ключевое слово OUTER (внешний) не является обязательным, оно не используется ни в каких операциях с данными и имеет смысл только в комбинации со спецификацией типа соединения (LEFT, RIGHT, FULL).

Таблицу в соединении будем называть левой, если она стоит перед (слева) ключевым словом JOIN, и правой, если она стоит после (справа) от него.

LEFT (OUTER) - тип соединения "левое (внешнее)". Левое соединение таблиц включает в себя все строки из левой таблицы и те строки из правой таблицы, для которых выполняется <search_condition>. Для строк из левой таблицы, для которых не найдено соответствия в правой, в столбцы, извлекаемые из правой таблицы, заносятся значения NULL.

RIGHT (OUTER) - тип соединения " правое (внешнее)". Правое соединение таблиц включает в себя все строки из правой таблицы и те строки из левой таблицы, для которых выполняется <search_condition>. Для строк из правой таблицы, для которых не найдено соответствия в левой, в столбцы, извлекаемые из левой таблицы, заносятся значения NULL.

FULL (OUTER) - тип соединения "полное (внешнее)". Это комбинация левого и правого соединений. В полное соединение включаются все строки из обеих таблиц. Для совпадающих строк поля заполняются реальными значениями, для несовпадающих строк поля заполняются в соответствии с правилами левого и правого соединений.

При использовании внутреннего соединения конструкции JOIN и WHERE полностью взаимозаменяемы. Использование соединенных таблиц часто облегчает восприятие оператора SELECT, особенно, когда используется естественное соединение. Если не использовать соединенные таблицы, то при выборе данных из нескольких таблиц необходимо явно указывать условия соединения в разделе WHERE. Если при этом пользователь указывает сложные критерии отбора строк, то в разделе WHERE смешиваются такие семантически различные понятия, как условия связи таблиц, так и условия отбора строк. Каких-либо иных дополнительных преимуществ конструкция JOIN перед конструкцией WHERE не имеет, так что выбор представляется делом вкуса.

Перепишем запрос из примера 3.6. с использованием конструкции JOIN.

Пример 3.34

SELECT a. rdnumb, a.rdname, b.booknm НЮМ TREADER a JOIN IBOOK b on EXISTS (SELECT * FROM TBOOK_READER ab where ab.reader=a.unikey and ab.bookkey=b.unikey);

Таблица. 3.22. Выборка списка читателей и взятых ими книг

RDNUMB RDNAME BOOKNM
1400-00 Пащенко О. Word 6 for Windows
1456-00 Стародуб Е. ЯзыкC++
1400-00 Пащенко О. Введение в технологию ATM
1273-92 Гребенкина Н. The history of England. Absolute Monarchy
1267-89 Арцибашев С. Тесты. Сборник 11 класс. Варианты и ответы государственного тестирования. Пособие для подготовки к тестированию
1267-89 Арцибашев С. Математические вопросы динамики вязкой несжимаемой жидкости
1267-89 Арцибашев С. Тайна
1369-99 Светлова В. Тайна

А теперь проделаем то же самое, но используя внешние соединения. Пример 3.35

SELECT a.rdnumb, a.rdname, b.booknm ШЖ IREADER a LEFT JOIN IBOOK b on EXISTS(SELECT * FROM TBOOK_READER ab where ab.reader=a.unikey and ab.bookkey=b.unikey);

Таблица 3.23. Выборка списка всех читателей и взятых ими книг

RDNUMB RDNAME BOOKNM
1267-89 Арцибашев С. Тесты. Сборник 11 класс. Варианты и ответы государственного тестирования. Пособие для подготовки к тестированию
RDNUMB RDNAME BOOKNM
1267-89 Арцибашев С. Математические вопросы динамики вязкой несжимаемой жидкости
1267-89 Арцибашев С. Тайна
1369-99 Светлова В. Тайна
1456-00 Стародуб Е. ЯзыкC++
1273-92 Гребенкина Н. The history ofEngland. Absolute Monarchy
1400-00 Пащенко О. Word 6 for Windows
1400-00 Пащенко О. Введение в технологию ATM
1401-99 Грамотный Н.Е.

Пример 3.36

SELECT a.rdnumb, a.rdname, b.booknm

FROM TREADER a RIGHT JOIN TBOOK b on EXISTS(SELECT * FROM TBOOK_READER ab where ab.reader=a.unikey and ab.bookkey=b.unikey);

Таблица 3.24. Выборка списка читателей и всех книг

RDNUMB RDNAME BOOKNM
Программирование
Учебники
Математика
Беллетристика
Макрокоманды MS Word
1400-00 Пащенко О. Word 6 for Windows
1456-00 Стародуб Е. ЯзыкC++
Введение в C++ Builder
Borland-Техно.логии. SOL-Link InterBase, Paradox for Windows. Delphi
С и C++ Справочник
RDNUMB RDNAME BOOKNM
1400-00 Пащенко О. Введение в технологию ATM
1273-92 Гребенкина Н. The history of England. Absolute Monarchy
Справочник по правописанию и литературной правке
1267-89 Арцибашев С. Тесты. Сборник 11 класс. Варианты и ответы государственного тестирования. Пособие для подготовки к тестированию
1267-89 Арцибашев С. Математические вопросы динамики вязкой несжимаемой жидкости
Кровь нерожденных
1267-89 Арцибашев С. Тайна
1369-99 Светлова В. Тайна

Пример 3.37

SELECT a.rdnumb, a.rdname, b.booknm

FROM TREADER a FULL JOIN TBOOK b on EXISTS(SELECT * FROM TBOOK_READER ab

where ab.reader=a.unikey and ab.bookkey=b.unikey);

Таблица 3.25. Выборка списка всех читателей и всех книг

RDNUMB RDNAME BOOKNM
Программирование
Учебники
Математика
Беллетристика
Макрокоманды MS Word
1400-00 Пащенко О. Word 6 for Windows
1456-00 Стародуб Е. ЯзыкC++
Введение в C++ Builder

Borland-Техно.логии. SOL-Link InterBase.
Paradox for Windows. Delnhi
RDNUMB RDNAME BOOKNM
С и C++ Справочник
1400-00 Пащенко О. Введение в технологию ATM
1273-92 Гребенкина Н. The history of England. Absolute Monarchy
Справочник по правописанию и литературной правке
1267-89 Арцибашев С. Тесты. Сборник 11 класс. Варианты и ответы государственного тестирования. Пособие для подготовки ктестированию
1267-89 Арцибашев С. Математические вопросы динамики вязкой несжимаемой жидкости
Кровь нерожденных
1267-89 Арцибашев С. Тайна
1369-99 Светлова В. Тайна
1401-99 Грамотный Н.Е.

В табл. 3.23-3.25 выделены: курсивом строки, появившиеся дополнительно за счет использования левого соединения, подчеркиванием -правого.

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

Пример 3.38

select UNIKEY,

(SELECT auname from tauthor where tbook_author.author=tauthor.author) auname,

(SELECT BOOKNM from tbook

where tbook_author.bookkey=tbook.unikey) bookname from tbook_author;

Таблица 3.26. Выборка списка авторов и книг

UNIKEY AUNAME BOOKNAME
58 Культин Н.Б. Макрокоманды MS Word
59 Буассо Марк Введение в технологию ATM
60 Деманж Мишель Введение в технологию ATM
61 Мюнье Жан- Мари Введение в технологию ATM
62 Луис Дерк С и C++ Справочник
63 Дунаев Сергей Borland-Технологии. SQL-Link InterBase, Paradox for Windows, Delphi
64 Елманова Н.З. Введение в C++ Builder
65 Кошель С П. Введение в C++ Builder
66 Подбельский Вадим Валериевич ЯзыкC++
67 Хаселир Райнер Г. Word 6 for Windows
68 Фаненштих Клаус Word 6 for Windows
69 Ладыжинская Ольга Александровна Математические вопросы динамики вязкой несжимаемой жидкости
70 без авторов Тесты. Сборник 11 класс. Варианты и ответы государственного тестирования. Пособие для подготовки к тестированию
71 Розенталь Д.Э. Справочник по правописанию и литературной правке
72 Бурова И.И. The history of^gland. Absolute Monarchy
73 Дашкова Полина Кровь нерожденных
74 Хмелевская Иоанна Тайна

Следует, правда, заметить, что в данном случае то же самое можно было бы получить заметно проще:

select a.UNIKEY, b.auname, с. BOOKNM from tbook_author a, tauthor b, tbook с where a. author=b. author and a.bookkey=c.unikey

ДОПОЛНИТЕЛЬНАЯ ОБРАБОТКА РЕЗУЛЬТАТОВ, ФИЛЬТРАЦИЯ И СОРТИРОВКА В ряде случаев уже выбранные данные нуждаются в дополнительной обработке. Часть такой обработки можно выполнить в рамках SQL запросов.

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

[ORDER BY <order_list>]

<order_list> =

{col | int} [COLLATE collation] [ASC [ENDING | DESC[ENDING]]

[, <order_list>].

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

Пример 3.39

SELECT unikey, bnumber n1, bnuinber*bnumber n2 FROM TBOOK_PLACE

ORDER BY n1 ;

Результат- "Dynamic SQL Error. SQL error code = -206. Column unknown. N1".

А если указать вместо алиаса имя столбца, то все будет в порядке. Пример 3.40

SELECT unikey, bnumber nl, bnumber*bnumber n2 FROM TBOOK_PLACE ORDER BY bnumber;

Таблица 3.27. Тестовая выборка№1 из TBOOK_PLACE

UNIKEY N1 N2
47 1 1
49 1 1
51 1 1
53 1 1
48 2 4
50 2 4
52 2 4
54 2 4
55 2 4
56 2 4
57 2 4
45 3 9
46 3 9

А как отсортировать по столбцу N2, которому не соответствует ни одно поле таблицы? В этом случае единственный возможный путь - явно указать номер столбца в списке выборки. В нашем случае - 3. Поскольку одному значению поля N2 соответствует несколько строк, то для обеспечения однозначности выборки упорядочим ее дополнительно по убыванию значений первого столбца.

Пример 3.41

SELECT unikey, bnumber nl, bnumber*bnumber n2 FROM TBOOK_PLACE

ORDER BY 3 , 1 DESC;

ИЛИ

SELECT unikey, bnumber nl, bnumber*bnuinber n2 FROM TBOOK_PLACE ORDER BY 3, UNIKEY DESC;

Конструкция ASC[ENDING], DESCENDING] указывает на вид сортировки: по возрастанию или убыванию соответственно. Если не указано ничего, то принимается ASC.

Таблица3.28. Тестоваявыборка№2из TBOOK^PLACE

UNIKEY N1 N2
47 1 1
49 1 1
51 1 1
53 1 1
48 2 4
50 2 4
52 2 4
54 2 4
55 2 4
56 2 4
57 2 4
45 3 9
46 3 9

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

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

Рассмотрим модификацию примера 3.27, который у нас не получился. Для этого включим в него группировку по читателям.

select SUM(l) , RENAME FROM TREADER GROUP BY RDNAME;

Получим список читателей и единичек. Запрос сработал, но результат малоинтересен.

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

Пример 3.42

select a.RDNAME, SUM(l)

FROM TREADER a, TBOOK_READER b

where (a.UNIKEY=b.READER) GROUP BY RDNAME;

Таблица 3.29. Список читателей с указанием количества взятых ими книг

RDNAME SUM
Арцибашев С. 3
Гребенкина Н. 1
Пащенко О. 2
Светлова В. 1
Стародуб Е. 1

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

Следующая конструкция HAVING <search_condition> может показаться излишней. В самом деле, фильтрация данных прекрасно выполняется с помощью конструкции WHERE. В чем отличие конструкции HAVING от WHERE? Конструкция WHERE применяется к каждой строке и определяет, будет ли она включена в выборку или нет. Конструкция HAVING применяется к уже полученной выборке и фильтрует ее. В большинстве случаев последнее просто неэффективно, но не всегда, поскольку некоторые данные в процессе выборки просто неизвестны. Например, в случае запроса в примере 3.42 мы не можем отбросить читателей, взявших менее 2 книг, поскольку пока расчет не выполнен, это просто неизвестно. Но когда он уже сделан, такой фильтр легко задать, используя HAVING.

Пример 3.43

select a.RDNAME, SUM(l) sumbook FROM TREADER a, TBOOK_READER b where (a.UNIKEY=b.READER) GROUP BY RDNAME HAVING SUM(l)>1; '

Таблица 3.30. Список читателей, взявших более 1 книги с указанием количества взятых ими книг

RDNAME SUMBOOK
Арцибашев С. 3
Пащенко О. 2

ОБЪЕДИНЕНИЕ НЕСКОЛЬКИХ ОДНОТИПНЫХ ЗАПРОСОВ В ОДИН

UNION [ALL] объединяет результаты выборки из двух или более таблиц, которые идентичны по структуре (результаты выборки, а не таблицы; сами таблицы могут иметь и разную структуру). Если используется конструкция ALL, то в выборке могут присутствовать и одинаковые строки, полученные из разных таблиц.

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

Пример 3.44

select RDNAME, CAST("Активный читатель" as VARCHAR(20))

RD_Active '

FROM TREADER a WHERE EXISTS(

select count(1) from TBOOK_READER b where

b.READER=a.UNIKEY

HAVING count(1)>2)

UNION

select RDNAME, CAST("Средний читатель" as VARCHAR(20) )

RD_Active FROM TREADER a WHERE EXISTS(

select count(1) from TBOOK_READER b where

b,READER=a.UNIKEY

HAVING count(1)<3 and count(1)>0)

UNION

select RDNAME, CAST("Пассивный читатель" as VARCHAR(20))

RD_Active FROM TREADER a WHERE

NOT EXISTS (select * from TBOOK_READER b where

b.READER=a.UNIKEY);

Хаблица 3.31. Список читателей по их активности

RDNAME RD_ACTIVE
Арцибашев С. Активный читатель
Грамотный Н.Е. Пассивный читатель
Гребенкина Н. Средний читатель
Пащенко О. Средний читатель
Светлова В. Средний читатель
Стародуб Е. Средний читатель

Замечание В списке полей выборки могут появляться и отдельные элементы полей - массивы.

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

2.7. описание синтаксиса языка sql | Введение в InterBase | 3.2. добавление данных. команда insert


Введение в InterBase



Новости за месяц

  • Август
    2019
  • Пн
  • Вт
  • Ср
  • Чт
  • Пт
  • Сб
  • Вс