В условие выборки включается следующее:
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
