SYBASE (Задания по бд), страница 5

[ignore_dup_row | allow_dup_row]}]

[on <имя сегмента>]

Опция fillfactor определяет процент заполнения памяти в каждой странице индекса. Число x может принимать значение от 1 до 100. Опция fillfactor полезна в том случае, когда нужно предусмотреть будущие изменения данных (добавление и обновление записей), т. е. зарезервировать память для будущих расширений индекса.

Опция ignore_dup_key позволяет избежать “откатки” большой транзакции во время попытки получения дублируемого значения в поле, по которому создан уникальный индекс. Другими словами, если в транзакции одной из команд является insert или update, которая формирует дублируемое значение в поле с уникальным индексом, то эта команда не выполняется, а вся транзакция в целом выполняется.

Опция sorted_data убыстряет процесс создания индекса, если данные в таблице уже отсортированы.

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

Опция on <имя сегмента> позволяет определить сегмент памяти, в котором создается индекс.

Примеры создания индексов:

1) create unique index nomind on poss (nomer)

2) create index polind on poss (pol)

3) create index vuzind on poss (vuz_k)

4) create unique index codvind on vuz (cod)

5) create unique index codgind on gorod (cod)

6) create unique index codvgind on vuz_gorod (cod_vuza)

7) create index codgoind on vuz_gorod (cod_goroda)

Уничтожение умолчаний и правил

drop default [<владелец>.]<имя умолчания> [, [<владелец>.]<имя умолчания>] …

drop rule [<владелец>.]<имя правила> [, [<владелец>.]<имя правила>] …

Информацию о правилах и умолчаниях можно получить с помощью процедуры sp_help.

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

Пример создания триггера для контроля колонок таблицы poss1 vuz_k (учебное заведение) и spec (специальность):

1. создание таблиц только кодов учебных заведений и только шифров специальностей:

create table codv (vkod integer)

create table cods (skod char(6))

1. загрузка этих таблиц данными:

insert codv values (<код>) …

insert cods values (<код>) …

1. создание триггера:

create trigger vuz_spec on poss1 for insert as

if ( select count(*) from codv, inserted where codv.vkod = inserted.vuz_k) = 0

select “значение поля vuz_k не принадлежит списку”

if ( select count(*) from cods, inserted where cods.skod = inserted.spec) = 0

select “значение поля spec не принадлежит списку”

Триггер vuz_spec сравнивает введенное значение поля vuz_k или spec со всеми значениями поля в таблице codv или cods и в случаях несовпадения выдает соответствующие сообщения.

Удаление элементов структуры БД осуществляется оператором DROP, например:

1) Удаление таблицы:

drop table [[<имя БД>.]<âëàäåëåö>.]<имя таблицы>

[,[[<имя БД>.<âëàäåëåö>.]<имя таблицы>]...

Пример: drop table poss

2) Удаление индекса:

drop index <имя таблицы>.<имя индекса>

[,<имя таблицы>.<имя индекса>]...

Пример: drop index poss.nomind

3. ВЫБОРКА ДАННЫХ

ОБЩЕЕ ОПИСАНИЕ ОПЕРАТОРА SELECT

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

select [all | distinct] <список полей SELECT>

[into [[<имя БД>.]<владелец>.]<имя таблицы>

[from [[<имя БД>.]<владелец>.]{<имя таблицы> | <имя представления>}

[holdlock | noholdlock][shared]

[,[[<имя БД>.]<владелец>.]{<имя таблицы | <имя представления>}

[holdlock | noholdlock][shared]]...]

[where <условие поиска>]

[group by [all]<aggregate_free_expression>

[,<aggregate_free_expression]...]

[having <условие поиска>]

[order by {[[[<имя БД>.]<владелец>.]{<имя табл.>. | <имя представления>.}]

<имя колонки> | <номер в списке SELECT> | <выражение>}

[asc | desc]

[,{[[[<имя БД>.<владелец>.]{<имя таблицы>. | <имя представления>.}]

<имя колонки> | <номер в списке SELECT> | <выражение>}

[asc|desc]]...]

[compute row_aggregate (<имя колонки>)

[,row_aggregate (<имя колонки>)]...

[by <имя колонки> [,<имя колонки>]...]]

[for browse]

Список выборки - это перечень колонок, определенных в главной фразе предложения SELECT. Список выборки представляет собой один или более разделенных запятыми элементов данных. Элемент данных может быть именем колонки, константой или комбинацией имен колонок и констант, связанных арифметическими операторами: + (сложить), - (вычесть), * (умножить), / (разделить), % (остаток от деления). Эти арифметические операторы используются для связки данных только числового типа. Кроме этого, элементу в списке выборки может предшествовать одна из агрегатных функций, описанных ниже.

Если вместо списка выборки ставится символ *, то это означает, что данные выбираются по всем колонкам таблицы.

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

<заголовок колонки> = <имя колонки>

или

<имя колонки> <заголовок колонки>

Если в <заголовок колонки> входят специальные символы, то он заключается в кавычки.

Примеры:

1. select Publisher = pub_name, pub_id from publishers

2. select pub_name Publisher, pub_id from publishers

3. select “Publisher’s Name” = pub_name from publishers

4. select title_id, total_sales * price - advance / 2 from titles

Перед списком выборки могут задаваться ключевые слова all или distinct. All определяет, что должны быть выбраны все повторяющиеся значения (all предполагается по умолчанию). Distinct определяет, что повторяющиеся значения не должны выбираться, т. е. исключаются дубликаты.

Структура into используется для создания новых таблиц БД путем выполнения запроса в уже существующих таблицах БД. Например:

select pub_id, pub_name into newtable from publishers

Предложение select…into не только создает новую таблицу, но и загружает ее данными.

Структура from должна включать в себя список всех таблиц и представлений, содержащих колонки, которые перечислены в списке выборки и структуре where. Максимальное количество таблиц и представлений в структуре from равно 16. Ключевые слова holdlock, noholdlock и shared используются системным администратором и здесь не рассматриваются.

Условие выборки указывается во фразах where и having.

В условие выборки включается следующее:

1) Операции сравнения: =, <, >, ! =, < >, >=, <=, !>, !<.

2) Диапазоны (between и not between).

3) Списки (in, not in).

4) Символы сравнения (like, not like).

5) Неопределенные значения (is null, is not null).

6) Комбинации из логических операторов: and, or, not.

7) Условия соединения таблиц.

8) Подзапросы.

Операции сравнения используются следующим образом:

where <выражение1> <операция сравнения> <выражение2>

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

select * from titleauthor where royaltyper < 50

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

<выражение1> [not] between <выражение2> and <выражение3>

Условие считается выполненным, если <выражение1> равно или больше, чем <выражение2> и равно или меньше, чем <выражение3>. Если используется опция not, то условие считается выполненным, если <выражение1> меньше <выражения2> или больше <выражения3>. Например:

select title_id, total_sales from titles where total_sales between 4095 and 12000

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

<выражение> [not] in (<список значений>)

Условие удовлетворяется, если <выражение> равно одному из значений, указанных в <списке значений>. Если используется опция not, то не равно ни одному из значений из <списка>. Например:

select au_name, state from authors where state in (‘CA’, ‘IN’, ‘MD’)

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

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

<имя колонки> [not] like <символьная строка>

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

1. символ “%” - представляет любую строку, состоящую из 0 или более символов;

2. символ “_” (подчеркивания) - представляет любой одиночный символ;

3. [<описатель>] - определяет диапазон или множество символов, в которые должен входить одиночный символ;

4. [^<описатель>] - определяет диапазон или множество символов, в которые не должен входить одиночный символ.

То есть <описатель> задается двумя способами:

а) в виде диапазона: r1-r2, например: [a-f]

б) в виде множества: r1r2…, например: [abcdef]

Условие not like является истинным, если ложно соответствующее условие like.

Примеры:

1. like ‘M%’ - строка начинается на М;

2. like ‘%er’ - строка заканчивается на er;

3. like ‘%en%’ - строка имеет подстроку en в любом месте;

4. like ‘___ryl’ - строка имеет в длину 6 символов и заканчивается на ryl;

5. like ‘[CK]ars[eo]n’ - строка имеет в длину 6 символов, начинается либо с С, либо с K, а пятый символ - либо e, либо o;

6. like ‘[M-Z]ing’ - 4хсимвольная строка, заканчивается на ing, а начинается с буквы, принадлежащей диапазону от M до Z;

7. like ‘M[^C]%’ - строка начинается на M, а в качестве второй буквы (цифры) берется любой символ, отличный от C.

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

<имя колонки> is [not] null

В структуре where отдельные условия могут соединяться логическими операторами and, or и not. Операция and(и) используется для определения двух и более условий, которые одновременно удовлетворяются в отбираемых строках, например:

select * from quotations where qonorder > 0 and suppno = 54

Оператор or(или) используется для определения двух и более условий, которое обеспечивает отбор строк, удовлетворяющих по крайней мере одному из этих условий, например:

select * from authors where au_fname = ‘Anne’ or au_fname = ‘Ann’

Оператор not используется для определения отрицания условия, например:

select * from authors where not state = ‘CA’

Для группирования условий могут быть использованы скобки, например:

select * from quotations where suppno = 61 and (purtno = 221 or purtno = 222)

Структура group by определяет группы совпадающих значений в указанных колонках. Максимальное количество колонок (выражений) равно 16. В ответе на запрос возвращается только одна результирующая строка для каждой группы. Почти всегда в структуре group by используются агрегатные функции. Например, функция count(*) оценивает количество строк в группе. Если какие-либо строки в группируемой колонке содержат null значения, то каждая такая строка рассматривается как принадлежащая отдельной группе, содержащей одну строку.

Пример:

select nazvanie_goroda from vuz_gorod where cod_stran = ‘01’ group by nazvanie_goroda

Структура having определяет одно или более условий, накладываемых на группы, т. е. заставляет СУБД возвращать результат только для тех групп, которые удовлетворяют заданному <условию поиска>. Having - это то же самое для структуры group by, что и where для структуры select.

Пример:

select nomer from poss group by nomer having count(*) > 1