Условие выборки указывается во фразах 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

Сортировка значений полей по возрастанию или убыванию осуществляется с помощью структуры order by, в которой перечисляются через запятую либо названия колонок, либо номера этих колонок в списке полей SELECT. Опция asc определяет сортировку по возрастанию, а опция desc - по убыванию. Если опция не определена, то предполагается упорядочение по возрастанию.

Пример:

select cod_vuza,nazvanie_vuza from vuz_gorod where cod_stran = ‘01’ order by 1

Структура compute используется с агрегатными функциями для получения в отчетах дополнительных записей, содержащих итоговые значения по каждой группе значений полей. С помощью структур compute можно подсчитать итоговые значения и для подгрупп, а также можно задавать несколько структур compute для одной группы. В структуре compute используются следующие агрегатные функции: sum, avg, min, max, count (sum и avg используются только для числовых колонок). Элемент данных, следующий за одной из этих функций, должен быть заключен в круглые скобки.

Агрегатные функции

Функция avg вычисляет среднее среди отобранных значений элемента. Эта функция предназначена только для числовых колонок и может быть использована с ключевым словом distinct. При вычислении среднего null значения игнорируются.

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

Функция max находит наибольшее среди отобранных значений элемента. Эта функция может быть применена к колонке любого типа и null значения игнорируются.

Функция min находит наименьшее среди отобранных значений элемента и также может быть применена к колонке любого типа, null значения игнорируются.

Функция count используется одним из двух способов:

n count(distinct <имя колонки>) возвращает число, равное количеству отличных друг от друга строк, удовлетворяющих условию поиска;

n count(*) возвращает число, равное количеству строк, удовлетворяющих условию поиска.

Правила использования структуры compute:

1) в агрегатных функциях нельзя использовать distinct;

2) поля в структуре compute должны обязательно присутствовать в списке выборки;

3) в операторе select, использующего compute, нельзя применять структуру into;

4) при использовании compute by обязательно присутствие структуры order by, при этом список полей в compute by либо идентичен списку полей в order by, либо является его подмножеством с сохранением следования полей слева направо, начинающегося с одного и того же выражения и без пропусков каких-либо выражений.

Пример: если указывается order by a, b, c , то можно использовать:

compute <агр. функция> (<имя колонки>) by a, b, c

compute <агр. функция> (<имя колонки>) by a, b

compute <агр. функция> (<имя колонки>) by a

и нельзя использовать:

compute <агр. функция> (<имя колонки>) by b,c

compute <агр. функция> (<имя колонки>) by a, c

compute <агр. функция> (<имя колонки>) by c

5) для подсчета общих итогов используется compute без by.

Примеры:

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

select type, pub_id, price from titles

order by type, pub_id, price

compute sum(price) by type, pub_id

2) если агрегатную функцию нужно подсчитать на каждом уровне группирования, то необходимо использовать compute больше одного раза:

select type, pub_id, price from titles

order by type, pub_id, price

compute sum(price) by type, pub_id

compute sum(price) by type

Кроме того, в структуре compute можно указывать одну и ту же агрегатную функцию для нескольких колонок, а также разные агрегатные функции для различных колонок.

3) если необходимо подсчитать только общие итоги, которые печатаются в конце отчета, то надо использовать compute без by:

select type, price,advance from titles

where price > $20

compute sum(price), sum(advance)

Оператор union

Оператор union служит для объединения двух и более запросов в один, для которого

можно использовать структуры order by и compute. Синтаксис оператора:

<запрос1>

[union [all] <запрос N>]…

[<структура order by>]

[<структура compute>]

где <запрос1>: select <список выборки>

[<структура into>]

[<структура from]

[<структура where>]

[<структура group by>]

[<структура having>]

а <запрос N>: select <список выборки>

[<структура from]

[<структура where>]

[<структура group by>]

[<структура having>]

Пример:

select * from t1 union select * from t2

Оператор union, по умолчанию, уничтожает дублируемые записи из результата запросов. Если используется опция all, то все записи включаются в отчет.

Правила использования оператора union:

1) все списки выборки в операторе union должны иметь одинаковую структуру (одинаковое количество однотипных выражений);

2) заголовки колонок в отчете по объединенному запросу берутся из первого запроса;

3) структуру into можно использовать только в первом запросе;

4) структуры order by и compute можно указывать только после последнего запроса для сортировки и подсчета итогов в объединенном запросе;

5) структуры group by и having можно использовать только в индивидуальных запросах;

6) оператор union можно использовать в операторе insert, например:

insert into tour

select city, state from stores

union

select city, state from authors

7) нельзя использовать union в операторе create view;

8) нельзя использовать опцию browse в предложениях select оператора union.

Встроенные функции

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

- системные функции, которые применяются к системным таблицам БД;

- строковые функции, которые применяются к значениям следующих типов: char, nchar, varchar, nvarchar, binary, varbinary;

- текстовые функции, которые применяются к значениям типа text и image;

- математические функции (тригонометрические, геометрические и др.);

- функции даты, которые применяются к значениям типа datetime и smalldatetime;

- функции преобразования одних типов данных в другие и форматирования дат.

Системные функции

Системные функции обеспечивают наиболее быстрый способ обращения к системным таблицам. Общий синтаксис вызова системной функции:

select <имя функции> (<аргумент[ы]>)

Таблица 1

Функция	Аргумент(ы)	Результат
1) col_name	( <ид. объекта>, <ид. колонки>)	имя колонки
2) col_length	(“<имя объекта>”, “<имя колонки>”)	длина колонки в таблице
3) data_pgs	(<ид. объекта>, {doampg \| ioampg})	количество страниц, занимаемое таблицей или индексом (не включаются страницы, используемые внутренними структурами)
4) datalength	(<выражение>)	длина выражения в байтах
5) db_id	(“<имя БД>”)	номер идентификатора БД
6) db_name	(<номер ид. БД>)	имя БД
7) host_id	()	номер идентификатора главного процесса
8) host_name	()	имя текущей главной ЭВМ
9) index_col	(“<имя объекта>”, <ид. индекса>, <ключ>)	имя колонки индекса
10) isnull	(<выражение>, <значение>)	замена заданного значения null значением
11) object_id	(“<имя объекта БД>”)	номер идентификатора объекта БД
12) object_name	(<ид. объекта БД>)	имя объекта БД
13) reserved_pgs	(<ид. объекта>, {doampg \| ioampg})	количество страниц, занимаемое таблицей или индексом (включаются страницы, используемые внутренними структурами
14) rowcnt	(doampg)	количество записей в таблице
15) sused_id	([“<имя клиента>”])	номер идентификатора клиента
16) sused_name	([<ид. клиента>])	имя клиента
17) tsequal	(<вр. метка>, <вр. метка2>)	сравнивает значения временных меток измененной записи; <вр. метка> - временная метка после выборки записи для просмотра; <вр. метка2> - временная метка сохраненной записи после обновления
18) used_pgs	(<ид. объекта>, doampg, ioampg)	общее количество страниц, занимаемое таблицей и ее индексом (включаются страницы, используемые внутренними структурами)
19) user_id	([“<имя пользов.>”])	номер идентификатора пользователя
20) user_name	([<ид.пользователя>])	имя пользователя
21) valid_name	(“<строка>”)	возвращает 0, если <строка> содержит недопустимые символы или длиной больше 30 байт, и не 0 - в противном случае

Примеры:

1) select x = col_length (“titles”, “title”)

2) select length = datalength (pub_name), pub_name from publishers

3) select name from sysusers where name = user_name(1)

Строковые функции

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

Синтаксис вызова строковых функций:

select <имя функции> (<аргументы>)

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

select (<выражение> + <выражение> [ + <выражение>]…)

Обозначение типов аргументов в таблице2:

1) char_expr - типы char, varchar, nchar и nvarchar;

2) expression - типы char_expr и типы binary и varbinary;

3) pattern - типы данных char_expr, которые можно включать в сопоставление с образцами;

4) approx_numeric - типы float, real и double precition;

5) integer_expr - типы tinyint, smallint и int;

6) start - тип integer_expr;

7) length - тип integer_expr.

Таблица 2

Функция	Аргумент(ы)	Результат
1) ascii	(char_expr)	код ASCII для первого символа в выражении
2) char	(integer_expr)	преобразует цифру, занимающую 1 байт в символ длиной 1 байт
3) charindex	(expression1, expression2)	ищет первое вхождение выражения1 в выражение2 и возвращает номер позиции; если не находит вообще, то возвращает 0
4) char_length	(char_expr)	количество символов в строке или тексте
5) difference	(char_expr1, char_expr2)	разность между двумя значениями типа soundex (см. ниже)
6) lower	(char_expr)	преобразует большие буквы в маленькие
7) ltrim	(char_expr)	убирает начальные пробелы
8) patindex	(“%pattern%”, char_expr [using {bytes \| chars \| characters}])	возвращает номер первой позиции вхождения pattern в char_expr или 0, если вхождения нет
9) replicate	(char_expr, integer_expr)	возвращает строку типа char_expr, содержащуюся в аргументе1 и повторяемую <аргумент2> раз (max длина строки 255 байт)
10) reverce	(char_expr)	реверс строки, например: “abcd” - “dcba”
Функция	Аргумент(ы)	Результат
11) right	(char_expr, integer_expr)	возвращает часть строки, состоящей из <аргумента2> символов, считая справа
12) rtrim	(char_expr)	убирает замыкающие пробелы
13) soundex	(char_expr)	возвращает четырехсимвольный код символьной строки, состоящий из римских букв
14) space	(integer_expr)	возвращает строку из указанного количества пробелов
15) str	(approx_numeric [, length [, decimal]])	символьное представление числа с плавающей точкой; length устанавливает общее число знаков, а decimal - число знаков после десятичной точки; если length и decimal не указываются, то по умолчанию length=10, а decimal=0
16) stuff	(char_expr1, start, length, char_expr2)	удаляет length символов из expr1, начиная с start, а затем вставляет expr2 в expr1, начиная с start; если expr2=null, то только удаляет
17) substring	(expression, start, length)	выделение подстроки в expression длиной length, начиная с start
18) upper	(char_expr)	преобразование маленьких букв в большие
19) +	expression + expression	конкатенация двух и более символьных или бинарных выражений

Примеры:

1) select au_lname, substring (au_fname, 1, 1) from authors

2) select charindex(“wonderful”, notes), patindex(“wonderful”, notes) from titles

where title_id = “TC3218”

3) select stuff(“abc”, 2, 3, “xyz”)

4) select (“abc” + “def”)

Строковые функции могут вкладываться друг в друга.

5) select substring(pub_id + title_id, 1, 6) from titles where price > $20

Текстовые функции

Текстовые функции используются для работы с данными типа text и image.

Таблица 3

Функция	Аргумент(ы)	Результат
1) patindex	(“%pattern%”,char_expr [using {bytes \| chars \| characters}])	возвращает числовое представление значения первой позиции первого вхождения pattern в символьную строку или 0 - если pattern не найден
2) textptr	(<имя текстовой колонки>)	возвращает указатель на текст (16байтное двоичное число)
3) textvalid	(“<имя таблицы>..<имя колонки>”, <указатель на текст>)	возвращает 1, если указатель допустимый и 0 - в противном случае
4) set textsize	{n \| 0}	задает max длину в байтах для колонки типа text/image в select-предложении; если 0, то max длина равна 32 K

Пример:

declare @val varbinary(16)

select @val = textptr(blurb) from texttest

Математические функции

Общий синтаксис вызова математической функции:

<имя функции> (<аргументы>)

Типы аргументов:

1) approx_numeric - это типы float, real и double precition;

2) integer - типы tinyint, smallint и int;

3) numeric - это типы approx_numeric, numeric, dec, decimal, все integer и money;

4) power - это типы numeric, approx_numeric и money.

Таблица 4

Функция	Аргумент(ы)	Результат
1) abs	(numeric)	абсолютное значение аргумента
2) acos	(approx_numeric)	арккосинус (в радианах)
3) asin	(approx_numeric)	арксинус (в радианах)
4) atan	(approx_numeric)	арктангенс (в радианах)
5) atn2	(approx_numeric1, approx_numeric2)	арктангенс деления аргумента1 на аргумент2
6) ceiling	(numeric)	округление до ближайшего целого, большего или равного аргументу
7) cos	(approx_numeric)	косинус (в радианах)
8) cot	(approx_numeric)	котангенс (в радианах)
9) degrees	(numeric)	преобразование радианов в градусы
10) exp	(approx_numeric)	число e в степени аргумент
11) floor	(numeric)	округление до ближайшего целого, меньшего или равного аргументу
12) log	(approx_numeric)	натуральный логарифм
13) log10	(approx_numeric)	десятичный логарифм
14) pi	()	число пи
15) power	(numeric, power)	преобразование числа типа numeric в число типа power
16) radians	(numeric)	преобразование градусов в радианы
17) rand	([integer])	функция random на отрезке [0;1] или для числа типа integer
18) round	(numeric, integer)	округление числа типа numeric до числа из integer знаков
19) sign	(numeric)	знак числа
20) sin	(approx_numeric)	синус (в радианах)
21) sqrt	(approx_numeric)	квадратный корень
22) tan	(approx_numeric)	тангенс (в радианах)

Примеры:

1) select ceiling(123.45) => 124.

2) select round(123.4545,2) => 123.4500

Функции даты (времени)

Таблица 5

Часть даты	Аббревиатура	Значения
1) год	1) yy	1) 1753¸9999
2) квартал	2) qq	2) 1¸4
3) месяц	3) mm	3) 1¸12
4) день года	4) dy	4) 1¸366
5) день	5) dd	5) 1¸31
6) неделя	6) wk	6) 1¸54
7) день недели	7) dw	7) 1¸7 (1-Sunday)
8) час	8) hh	8) 0¸23
9) минута	9) mi	9) 0¸59
10) секунда	10) ss	10) 0¸59
11) миллисекунда	11) ms	11) 0¸999

Таблица 6

Функция	Аргумент(ы)	Результат
1) getdate	( )	Текущая системная дата и время
2) datename	(datepart, date)	часть даты как строка ASCII
3) datepart	(datepart, date)	часть даты как целое число
4) datediff	(datepart, date, date)	количество времени между 2-мя датами в частях даты
5) dateadd	(datepart, number, date)	дата, получаемая прибавлением частей даты к другой дате