Условие выборки указывается во фразах 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) дата, получаемая прибавлением частей даты к другой дате

Примеры:

1) select getdate();

2) select datediff(month, pubdate, ”Nov 30 1985”) from titles;

3) select dateadd(day, 3, pubdate) from titles

Наши рекомендации