Назад    Оглавление    Далее

2. Первое, что нужно знать

Типы данных

В SQL используются следующие основные типы данных, форматы которых могут несколько различаться для разных СУБД:

Тип данных

Описание

NUMBER(p,s)

Числовое значение, максимальное количество цифр в котором равно "р", а количество десятичных знаков - "s".

INTEGER

Целое число (обычно до 7 значащих цифр и знак). Аналог NUMBER(7).

VARCHAR2(s)

Символьная строка переменной длины, максимальный размер которой равен "s". В системе Oracle 7 максимально возможное значение s составляет 2000 символов.

DATE

Значение даты и времени между 1 января 4712 г. до нашей эры и 31 декабря 4712 г. нашей эры.

CHAR(s)

Символьное значение постоянной длины "s". В системе Oracle 7 максимально возможное значение s составляет 256 символов.

LONG

Символьные значения переменной длины размером до 2Гб

RAW и LONG RAW

Эквиваленты VARCHAR2 и LONG для двоичных данных.

В некоторых СУБД еще существует тип данных LOGICAL, DOUBLE и ряд других. Некоторые СУБД предоставляет пользователю возможность самостоятельного определения новых типов данных, например, плоскостные или пространственные координаты, единицы различных метрик, пяти- или шестидневные недели (рабочая неделя, где сразу после пятницы или субботы следует понедельник), дроби, графика, большие целые числа и т.п.

Правила присвоения имен объектам базы данных

Арифметические выражения

Иногда требуется изменить способ вывода данных, произвести вычисления или просмотреть сценарии "а что, если ...". Это можно сделать с помощью арифметических выражений. Арифметическое выражение может содержать имена столбцов, числовые константы и арифметические операторы.

Арифметические операторы

Ниже перечислены арифметические операторы, доступные в SQL. Использовать их можно в любом предложении команды SQL, кроме FROM.

Оператор

Описание

+

Сложение

-

Вычитание

*

Умножение

/

Деление

||

Конкатенация

Порядок выполнения операторов

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

Пример Вычисление годовых компенсационных. Сумма выплат за год вычисляется путем умножения заработной платы (значение переменной salary) на 12 и прибавления одноразовой премии в размере 100.

Примечание: Для изменения порядка действий и упрощения чтения можно использовать скобки. Если, например, записать вышеуказанное выражение в виде (12*SALARY) + 100, то результат не изменится.

Пример Вывод фамилии, заработной платы и суммы выплат за год для каждого служащего. Размер выплат за год вычисляется путем прибавления к заработной плате ежемесячной премии в размере 100 и умножения суммы на 12.

Оператор конкатенации “||” позволяет соединять значения одних столбцов с другими столбцами, арифметическими выражениями или постоянными значениями для создания символьных выражений. Столбцы, указанные по обе стороны этого оператора, объединяются для вывода в один столбец.

Пример Соединение двух строковых констант.

Операторы сравнения.

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

Операторы сравнения проверяются следующими условиями:

Оператор

Значение

=

Равно

>

Больше

>=

Больше или равно

<

Меньше

<=

Меньше или равно

Имеется четыре оператора SQL, используемых с данными всех типов:

Оператор

Значение

BETWEEN...AND…

Между двумя значениями (включительно)

IN(список)

Совпадает с каким-то из значений в списке

LIKE

Соответствует символьному шаблону

IS NULL

Является неопределенным значением

Логические операторы:

Оператор

Значение

AND

Если обе части условия истинны, то условие истинно.

OR

Если хотя бы одна часть условия истинна, то условие истинно.

NOT

Возвращает противоположное условие.

Отрицание выражений:

Оператор

Значение

!=

Не равно (VAX. UNISX. PC)

^=

Не равно (IBM)

<>

Не равно (все операционные системы)

NOT имя столбца =

Не равно

NOT имя столбца >

Не больше

Отрицание операторов SQL:

Оператор

Значение

NOT BETWEEN...AND…

НЕ между двумя значениями (включительно)

NOT IN(список)

НЕ входит в список значений

NOT LIKE

Не подобно заданной строке

IS NOT NULL

Не является неопределенным значением

Порядок выполнения операций:

Порядок вычисления

Оператор

1

Все операторы сравнения

2

AND

3

OR

Стандартный порядок выполнения операций отменяется скобками.

Строки символов (литералы).

Литерал — это любой символ, выражение или число, включенные в список SELECT и не являющиеся ни именем, ни псевдонимом столбца. Они печатаются для каждой возвращаемой строки. Литералы в виде текста произвольного формата могут быть включены в результат запроса. В списке SELECT они рассматриваются как столбцы. Символьные литералы и литералы-даты должны быть заключены в апострофы (‘’), а числовые литералы - нет.

Пример:

Выражения с датами.

Для данных типа дата в SQL возможно применять некоторые арифметические операторы:

Операция

Результат

Описание

Дата + число

Дата

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

Дата - число

Дата

Вычитание количества дней из даты

Дата – дата

Кол-во дней

Вычитание одной даты из другой

Дата + число/24

дата

Прибавление к дате часов

Обработка неопределенных значений.

Неопределенным значением (NULL) называется недоступное, неприсвоенное, неизвестное или неприменимое значение. Неопределенное значение - это не ноль и не пробел. Ноль — это число, а пробел — символ. Издержки "хранения" неопределенного значения - это один байт внутренней памяти.

Неопределенные значения возможны в столбцах любых типов, если при создании таблицы они не были описаны как столбцы только с определенными значениями (NOT NULL) или столбцы, содержащие первичный ключ (PRIMARY KEY).

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

Для преобразования неопределенного значения в фактическое используется функция NVL:

NVL (выражение1, выражение 2)

где:

выражение1

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

Выражение2

Значение, которое подставляется вместо неопределенного значения.

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

Пример: при вычислении значений по следующей формуле

не возникает неопределенности в интерпретации получаемого результата, поскольку если значение переменой salary будет неопределено, функция возвратит значение “ноль”.

Преобразование NVL для различных типов:

Тип данных

Пример преобразования

NUMBER

NVL (числовой столбец, 9)

DATE

NVL (столбец даты, '01-ЯНВ-95')

CHAR или VARCHAR2

NVL (символы|столбец, 'Недоступно')

 

Функции

В языке SQL существуют два класса функций – однострочные и групповые.

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

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

Различие в интерпретации входных данных обуславливает и различие в применении этих функций. Так однострочные функции могут использоваться там, где в качестве результата запроса к базе данных подразумевается получение только одной строки данных. Для использование групповой функции необходимо сначала сформировать из “многострочного” результата запроса группы строк, а затем для каждой из них применить групповую функцию.

Синтаксис:

имя_функции (столбец | выражение, [аргумент1, аргумент2, …])

где:

имя_функции

имя функции

столбец

любой именованный столбец из базы данных.

выражение

любая строка символов или вычисляемое выражение.

аргумент1, аргумент2

любой аргумент, используемый функцией.

Числовые функции.

Функция

Возвращаемое значение

ABS(n)

Абсолютное значение величины n

CEIL(n)

Наименьшее целое, большее или равное n

COS(n)

Косинус n (угла, выраженного в радианах)

COSH(n)

Гиперболический косинус n

ЕХР(n)

e в степени n

FLOOR(n)

Наибольшее целое, меньшее или рапное n

LN(n)

Натуральный логарифм n, где n>0

LOG(m,n)

Логарифм n по основанию m

MOD(m,n)

Остаток от деления m на n

POWER(w,n)

w в степени n

ROUND(n[,m])

n, округленное до m позиций после десятичной точки. По умолчанию m равно нулю

SIGN(n)

-1 (если n<0); 0 (если n=0); 1 (если n>0)

SIN(n)

Синус n (угла, выраженного в радианах)

SINH(n)

Гиперболический синус

SQRT(n)

Квадратный корень от n. Если n<0, возвращает значение NULL

TAN(n)

Тангенс n (угла, выраженного в радианах)

TANH(n)

Гиперболический тангенс n

TRUNC(n[,m])

n, усеченное до m позиций после десятичной точки. По умолчанию m равно нулю

Символьные функции.

Функция

Возвращаемое значение

Символьные функции, возвращающие символьные значения:

CHR(n)

Символ с кодом n

СОNСАТ(сhar1,char2)

Конкатенация символьных строк char1 и char2

INITCAP(char)

Символьная строка сhar, первые буквы всех слов в которой преобразованы в прописные

LOWER(char)

Символьная строка char, все буквы которой преобразованы в строчные

LPAD(char1,n[,char2])

Символьная строка char1, которая дополняется слева последовательностью символов из char2 так, чтобы общая длина строки стала равна n. Значение char2 по умолчанию – (один пробел). Если часть многобайтового символа не помещается в добавляемой строке, то конец строки заполняется пробелами.

LTRIM(char[,set])

Символьная строка char, в которой удалены все символы от начала вплоть до первого символа, которого нет в строке set. Значение set по умолчанию - '' (один пробел).

NLS_INITCAP(char[,nls_sort])

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

NLS_LOWER(char[,nls_sort])

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

NLS_UPPER(char[,nls_sort])

Символьная строка char, все буквы которой преобразованы в прописные. Параметр nts_sort определяет последовательность сортировки

REPLACE(char, search_string [,replacement_string])

Символьная строка char, в которой все фрагменты search_string заменены на replacement_string. Если параметр replacement_string не определен, все фрагменты search_string удаляются

RPAD(char1,n[,char2])

Символьная строка char1, которая дополнена справа последовательностью символов из char2 так, что общая длина строки равна n. Если часть многобайтового символа не помещается в добавляемой строке, то конец строки заполняется пробелами

RTRIM(char[,set])

Символьная строка char, в которой удалены все символы справа вплоть до первого символа, которого нет в строке set. Значение параметра set по умолчанию – ‘ ’ (один пробел).

SOUNDEX(char)

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

SUBSTR(char,m[,n])

Фрагмент символьной строки char, начинающийся с символа m, длиной n символов (до конца строки, если параметр n не указан).

SUBSTRB(char,m[,n])

Фрагмент символьной строки char, начинающийся с символа m, длиной n байтов (до конца строки, если параметр n не указан).

TRANSLATE(char,from, to)

Символьная строка char, в которой все символы, встречающиеся в строке from, заменены на соответствующие символы из to.

UPPER(char)

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

Символьные функции, возвращающие числовые значения:

ASCII(char)

Возвращает десятичный код первого символа строки char в кодировке, принятой в базе данных. (Код ASCII в системах, использующих кодировку ASCII). Возвращает значение первого байта многобайтового символа.

INSTR(char1,char2[,n[,m]])

Позиция первого символа m-ого фрагмента строки char1, совпадающего со строкой char2, начиная с n-ого символа. По умолчанию n и m равны 1. Номер символа отсчитывается от первого символа строки char1, даже когда n> 1

INSTRB(char1,char2[,n[,m]])

Позиция первого символа n-ого фрагмента строки char1, совпадающего со строкой char2, начиная с m-ого байта. По умолчанию n и m равны 1. Номер байта отсчитывается от первого символа строки char1, даже когда n> 1.

LENGTH(char)

Длина строки char в символах

LENGTHB(char)

Длина строки char в байтах

NLSSORT(char1,char2[,n[,m]])

Зависящее от национального языка значение, используемое при сортировке строки char.

Функции для работы с датами.

Oracle хранит данные во внутреннем цифровом формате: век, год, месяц, число, часы, минуты, секунды. По умолчанию дата выдается в формате “DD-MON-YY”.

Функция

Назначение

MONTHS_BETWEEN(date1, date2)

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

ADD_MONTHS(date,n)

Добавление календарных месяцев к дате.

NEXT_DAY(date, ‘char’)

Ближайшая дата, когда наступит заданный день. Аргумент ‘char’ может задавать порядковый номер или название дня недели.

LAST_DAY(date)

Определение последнего дня месяца, содержащего заданную дату.

ROUND(date[, ‘fmt’])

Округление до целого числа суток. Если fmt=YEAR, определяет первый день года.

TRUNC(date[, ‘fmt’])

Возвращает первый день месяца, указанного в аргументе date. Если fmt=YEAR, возвращает дату первого дня года.

SYSDATE()

Возвращает текущую дату и время.

Функции преобразования типа.

Функция

Возвращаемое значение

TO_CHAR (date[, 'fmt'])

Преобразование даты в строку символов в соответствии с форматной моделью fmt.

TO_CHAR (number[, 'fmt'])

Преобразование числа в строку символов в соответствии с форматной моделью fmt.

TO_NUBER (char)

Преобразование строки символов в числовой формат.

TO_DATE (char[, 'fmt'])

Преобразование строки символов в формат даты в соответствии с форматной моделью fmt.

Форматные модели.

Модель формата:

Модель

Описание

Форматные модели для работы с датами

YY[YY]

Полный год цифрами.

YEAR

Год прописью.

MM

Двузначное цифровое обозначение месяца.

MON

Трехсимвольное сокращенное название месяца.

MONTH

Полное название месяца.

DD

День недели цифрами.

DY

Трехсимвольное сокращенное название дня недели.

DAY

Полное название дня недели.

HH

Часы цифрами в 12-ти часовом формате.

HH24

Часы цифрами в 24-х часовом формате.

MI

Минуты цифрами.

SS

Секунды цифрами.

AM

Символы ‘AM’|’PM’.

Числовые модели формата

9

Вывод цифры с подавлением ведущих нулей.

0

Вывод цифры, если ведущий нуль – вывод нуля.

$

Плавающий знак доллара.

L

Плавающий символ местной валюты

.

Вывод десятичной точки.

,

Вывод разделителя троек цифр.

Групповые функции.

Функция

Возвращаемое значение

AVG([DISTINCT|ALL]n)

Среднее значение от n, нулевые значения опускаются

COUNT([ALL]*)

Число строк, извлекаемых в запросе или подзапросе

COUNT([DISTINCT|ALL] expr)

Число строк, для которых expr принимает не пустое значение

MAX([DISTINCT|ALL] expr)

Максимальное значение выражения eхрr

MIN([DISTINCT|ALL] expr)

Минимальное значение выражения eхрr

STDDEV([DISTINCT|ALL] n)

Стандартное отклонение величины n, нулевые значения опускаются

SUM([DISTINCT|ALL] n)

Сумма значений n

VARIANCE([DIST1NCT|ALL]n)

Дисперсия величины n, нулевые значения опускаются

Примеры использования некоторых функций.

Функция округления:

Функция усечения:

Вычисление остатка от деления двух чисел:

Функции работы с датами:

Назад    Оглавление    Далее