лекция. Учебник по sql если вы хотите узнать, что такое sql этот сайт для вас

• Название: Учебник по sql если вы хотите узнать, что такое sql этот сайт для вас
• Анкор: лекция
• Дата: 21.12.2022
• Размер: 7.88 Mb.
• Имя файла: Moiseenko_SQL.pdf
• Тип: Учебник; #857633
• страница: 28 из 47

STUFF дает NULL. Ну что ж, вытерпим до конца мучения, связанные с использованием этой функции в данном примере, попутно применив еще одну строковую функцию.

Добавим конечные пробелы, чтобы длина имени была заведомо больше 6.
Для этого имеется специальная функция SPACE(число пробелов):
1.
SELECT
name, STUFF
(
name + SPACE
(
6
)
,
6
, LEN
(
name
)
,
'_'
+STR
(
launched,
4
))
2.
FROM
Ships;
Функции LTRIM(строковое выражение) и RTRIM(строковое выражение)
Отсекают, соответственно, лидирующие и конечные пробелы строкового выражения, которое неявно приводится к типу varchar.
Пусть требуется построить такую строку:
<имя пассажира>_<идентификатор пассажира>
на базе таблицы Passenger. Если мы напишем:
1.
SELECT
name +
'_'
+
CAST
(
id_psg
AS
VARCHAR
)
2.
FROM
Passenger; то в результате получим что-то типа:
A _1
Это связано с тем, что столбец name имеет тип CHAR(30). Для этого типа короткая строка дополняется пробелами до заданного размера (у нас 30 символов). Здесь нам как раз и поможет функция RTRIM:
1.
SELECT
RTRIM
(
name
)
+
'_'
+
CAST
(
id_psg
AS
VARCHAR
)
2.
FROM
Passenger;
Для усечения концевых пробелов в SQL Server изначально имелось две функции -
LTRIM и RTRIM
- для усечения пробелов слева и справа соответственно. Чтобы удалить пробелы с обеих сторон строки, последовательно применялись обе функции (в примере для наглядности используется функция DATALENGTH
, возвращающая число символов с учетом пробелов):

1. declare @s varchar
(
10
)
=
' x '
-- слева 2 пробела, справа
- 1
2.
SELECT
datalength
(
@s
)
s, datalength
(
ltrim
(
@s
))
ls,
3. datalength
(
rtrim
(
@s
))
rs, datalength
(
ltrim
(
rtrim
(
@s
)))
bs;
s
ls
rs
bs
4
2 3
1
Начиная с версии SQL Server 2017, к ним добавилась новая функция - TRIM, которая выполняет усечение с обеих сторон строки-аргумента:
1.
SELECT
datalength
(
@s
)
s, datalength
(
trim
(
@s
))
ts;
s
ts
4
1
Кроме того, функция TRIM приобрела дополнительный функционал - возможность усекать произвольные концевые символы.
При этом усекаемые символы можно задавать списком, перечисляя их в произвольном порядке. Усекаться (с обеих сторон) будут все входящие в список символы, пока не появится "посторонний", т.е. не входящий в список.
Лучше всего продемонстрировать сказанное на примере.
1. declare @s1 varchar
(
10
)
=
'xxaxbxy'
2.
SELECT
trim
(
'yx'
FROM
@s1
)
ts1,trim
(
'x'
FROM
@s1
)
ts2;
ts1
ts2
axb
axbxy

Конечно, это еще не стандартное поведение, но уже близко. А вот что говорит стандарт относительно функции TRIM:
< trim function> ::= TRIM < left paren> < trim operands> < right paren>
< trim operands> ::= [ [ < trim specification> ] [ < trim character> ] FROM ] < trim source>
< trim source> ::= < character value expression>
< trim specification> ::= LEADING | TRAILING | BOTH
< trim character> ::= < character value expression>
MySQL
В MySQL используется стандартный синтаксис функции TRIM. При этом, в отличие от SQL Server, удаляется указанная подстрока, а не все символы из списка:
1.
SELECT
TRIM
(
LEADING
'xy'
FROM
'xyxybarxyx'
)
ls,
2. TRIM
(
TRAILING
'xy'
FROM
'xyxybarxyx'
)
rs,
3. TRIM
(
BOTH
'yx'
FROM
'xyxybarxyx'
)
bs;
ls
rs
bs
barxyx xyxybarxyx xyxybarx
PostgreSQL
PostgreSQL сочетает поведение MySQL и SQL Server, т.е. удаляются все символы из списка:
1.
SELECT
TRIM
(
LEADING
'xy'
FROM
'xyxybarxyx'
)
ls,
2. TRIM
(
TRAILING
'xy'
FROM
'xyxybarxyx'
)
rs,
3. TRIM
(
BOTH
'yx'
FROM
'xyxybarxyx'
)
bs;
ls
rs
bs
barxyx xyxybar bar

Oracle
Oracle допускает усечение только одного символа, а не подстроки или символов из списка:
1.
SELECT
TRIM
(
LEADING
'x'
FROM
'xxybarxyx'
)
ls,
2. TRIM
(
TRAILING
'x'
FROM
'xxybarxyx'
)
rs,
3. TRIM
(
BOTH
'x'
FROM
'xxybarxyx'
)
bs
4.
FROM
dual;
ls
rs
bs
ybarxyx xxybarxy ybarxy
Функции LOWER,
UPPER, SOUNDEX и
DIFFERENCE
Функции LOWER(строковое
выражение) и UPPER(строковое
выражение) преобразуют все символы аргумента, соответственно, к нижнему и верхнему регистру.
Эти функции оказываются полезными при сравнении регистрозависимых строк.
Пара интересных функций SOUNDEX(строковое
выражение) и DIFFERENCE(строковое
выражение_1, строковое выражение_2) позволяют определить близость звучания слов. При этом SOUNDEX() возвращает четырехсимвольный код, используемый для сравнения, а DIFFERENCE() собственно и оценивает близость звучания двух сравниваемых строковых выражений. Поскольку эти функции не поддерживают кириллицы, отсылаем

интересующихся к
BOL
за примерами их использования.
В заключение приведем функции и несколько примеров применения UNICODE.
Функции
UNICODE
и NCHAR
Функция UNICODE(строковое выражение) возвращает номер в кодировке Unicode первого символа строкового выражения. Функция NCHAR(целое) возвращает символ по его номеру в кодировке Unicode. Приведем несколько примеров.
1.
SELECT
ASCII
(
'а'
)
, UNICODE
(
'а'
)
;
Возвращает код ASCII и номер русской буквы «а» в кодировке Unicode: 224 и 1072.
1.
SELECT
CHAR
(
ASCII
(
'а'
))
, CHAR
(
UNICODE
(
'а'
))
;
Здесь мы пытаемся восстановить символ по его коду. Получаем а и NULL. NULL-значение возвращается потому, что кода 1072 нет в обычной кодовой таблице.
1.
SELECT
CHAR
(
ASCII
(
'а'
))
, NCHAR
(
UNICODE
(
'а'
))
;
Теперь все нормально, в обоих случаях мы получаем а.

Наконец,
1.
SELECT
NCHAR
(
ASCII
(
'а'
))
; даст à, так как номер 224 в кодировке Unicode соответствует именно этой букве.
Функция
ROUND
Рассмотрим такую задачу.
Посчитать средний размер жесткого диска ПК.
Результат представить с двумя знаками после
десятичной точки.
Выполнив запрос
1.
SELECT
AVG
(
hd
)
AS
avg_hd
FROM
pc; мы получим такой результат:
avg_hd
13.6666666666667
Чтобы выполнить требуемое округление можно воспользоваться функцией ROUND:
1.
SELECT
round
(
AVG
(
hd
)
,
2
)
AS
avg_hd
FROM
pc;
avg_hd
13.67

Второй аргумент этой функции как раз указывает число десятичных знаков результата.
Как видно, результат был округлен по арифметическим правилам. Однако у функции ROUND есть третий необязательный аргумент, который говорит о том, округлять ли результат (значение 0 - принимается по умолчанию) или отбрасывать цифры сверх удерживаемых (ненулевое значение).
Т.е. если мы перепишем наш запрос так:
1.
SELECT
round
(
AVG
(
hd
)
,
2
,
1
)
AS
avg_hd
FROM
pc; то получим другой результат:
avg_hd
13.66
Кстати, округлять можно до любого знака, не только десятичного. Например, чтобы округлять до десятков, сотен и т.д., используются отрицательные значения второго аргумента. Следующий запрос округляет результат до десятков.
1.
SELECT
round
(
AVG
(
hd
)
,
-1
)
AS
avg_hd
FROM
pc;
avg_hd
10
Следует отметить, что функция ROUND выполняет округление, но не меняет тип результата. Т.е. если аргумент будет иметь тип dec(12,6), то и результат округления будет того же типа, а именно,
avg_hd
13.670000
В этом легко убедиться, выполнив запрос
1.
SELECT
round
(
CAST
(
AVG
(
hd
)
AS
dec
(
12
,
6
))
,
2
)
AS
avg_hd
FROM
pc;

Поэтому, если вы хотите избавиться от хвостовых нулей, используйте преобразование к нужному вам типу, например, dec(12,2). Тогда нам и функция ROUND не потребуется.
1.
SELECT
CAST
(
AVG
(
hd
)
AS
DEC
(
12
,
2
))
AS
avg_hd
FROM
pc;
Функции
CEILING
и FLOOR
Функция CEILING
Функция CEILING возвращает наименьшее целое число, которое больше или равно числовому выражению, являющемуся аргументом функции.
Следующий запрос
1.
SELECT
6.28
val, CEILING
(
6.28
)
pos_val, CEILING
(
-6.28
)
neg_val; даст такие результаты:
val
pos_val
neg_val
6.28 7
-6
Возвращаемый функцией результат имеет тот же тип, что и аргумент функции.
Однако рассмотренный пример вроде бы говорит об обратном. Более того, даже выполнив явное преобразование типа для чисел с фиксированной и плавающей точкой, мы получим в результате целое число за исключением константы типа MONEY (в Management Studio):
1.
SELECT
CEILING
(
CAST
(
6.28
AS
DEC
(
6
,
2
)))
ex_num,

2. CEILING
(
CAST
(
6.28
AS
FLOAT
))
apr_num, CEILING
(
$
6.28
)
money_num;
ex_num
apr_num
money_num
7
7 7,00
При использовании других клиентских программ/драйверов вы вполне можете получить другое визуальное представление данных. Выполните, например, последний запрос непосредственно в учебнике. Что у вас получилось?
Можно сказать, что формат отображения является лишь "косвенной уликой" относительно вердикта о типе данных результата. Более надежным критерием является объём, который значение занимает в памяти.
1.
SELECT
DATALENGTH
(
6.28
)
val, DATALENGTH
(
CEILING
(
6.28
))
num_val,
2. DATALENGTH
(
CAST
(
CEILING
(
6.28
)
AS
INT
))
int_val;
val
num_val
int_val
5
5 4
Т.е. результат применения функции CEILING занимает в памяти столько же места, что и исходное значение, в то время как результат, явно преобразованный к целому типу, - 4 байта.
Функция FLOOR
Функция FLOOR, напротив, возвращает наибольшее целое число, которое меньше или равно числовому выражению, являющемуся аргументом функции.
1.
SELECT
6.28
val, FLOOR
(
6.28
)
pos_val, FLOOR
(
-6.28
)
neg_val;

val
pos_val
neg_val
6.28 6
-7 1.
SELECT
LOG
(
2
)
ln, LOG
(
2
,
10
)
log10;
ln
log10
0.693147180559945 0.301029995663981
Этот запрос возвращает натуральный и десятичный логарифмы числа 2.
Имеется также унаследованная функция LOG10(x), возвращающая десятичный логарифм выражения x. Она изначально являлась избыточной, ввиду известной формулы преобразования логарифмов:
LOG
b
a = LOG
c
a/LOG
c
b
Соответственно три следующих выражения дадут один и тот же результат
(0.301029995663981):
1.
SELECT
LOG
(
2
,
10
)
log_1, LOG10
(
2
)
log_2, LOG
(
2
)
/LOG
(
10
)
log_3;
Функция EXP(x) - экспонента - возвращает число e в степени x. Тип возвращаемого значения FLOAT.
Эта функция является обратной к функции LOG:
Функции
LOG и
EXP
Функция LOG(x) возвращает натуральный логарифм выражения x. Результат имеет тип float
Начиная с версии
SQL
Server 2012, эта функция приобрела необязательный аргумент, который задает основание логарифма.

1.
SELECT
EXP
(
LOG
(
2
))
a, LOG
(
EXP
(
2
))
b;
a
b
2
2
Еще одно полезное свойство преобразования логарифмов - а именно, логарифм произведения равен сумме логарифмов сомножителей - позволит нам вычислить произведение значений в столбце таблицы, т.е.
1. log
(
a*b*c
)
= log
(
a
)
+ log
(
b
)
+ log
(
c
)
В справедливости данного свойства мы можем легко убедиться на примере:
1.
SELECT
LOG
(
2
*
5
*
7
)
log_prod, LOG
(
2
)
+ LOG
(
5
)
+ LOG
(
7
)
sum_log;
log_prod
sum_log
4,24849524204936 4,24849524204936
Среди агрегатных функций SQL нет функции произведения значений. Тем не менее, используя вышеупомянутое свойство логарифмов и элементарные преобразования, мы можем свести эту задачу к вычислению суммы.
Действительно,
1. a*b*c = exp
(
log
(
a*b*c
))
= exp
(
log
(
a
)
+ log
(
b
)
+ log
(
c
))
Вычислить факториал числа, равного количеству строк в таблице
Laptop.
Решение
1.
SELECT
EXP
(
SUM
(
LOG
(
rn
)))
FROM
(
2.
SELECT
ROW_NUMBER
()
OVER
(
ORDER
BY
code
)
rn
FROM
laptop
3.
)
X;

720
Здесь во внутреннем запросе мы пронумеровали записи таблицы Laptop с помощью ранжирующей функции
ROW_NUMBER
. (Заметим, что порядок, в котором нумеруются строки, в данном случае значения не имеет). Затем просто перемножили эти номера.
Функции
POWER
и SQRT
Функция SQL Server POWER (x, y ) возводит x в степень y. x является выражением типа FLOAT, или типа, неявно приводимого к FLOAT. y - выражение числового типа.
Возвращаемое значение имеет тип выражения x.
Функция SQRT (x) вычисляет корень квадратный из x, при этом x является выражением типа FLOAT, или неявно приводимого к нему. Результат имеет тип FLOAT.
Функция SQRT является обратной к функции POWER(x,2), т.е. SQRT(POWER(x,2)) должна возвращать x.
Проверим это
1.
SELECT
3
x, power
(
3
,
2
)
y, sqrt
(
power
(
3
,
2
))
sqrt_y;
x
y
sqrt_y
3
9 3
Правильно. Однако
1.
SELECT
3.1
x, power
(
3.1
,
2
)
y, sqrt
(
power
(
3.1
,
2
))
sqrt_y; даст

x
y
sqrt_y
3.1 9.6 3,09838667696593
Этот неожиданный результат, вероятно, связан с потерей точности при неявном преобразовании результата функции POWER (который соответствует типу аргумента, т.е. numeric) к типу данных FLOAT.
Действительно,
1.
SELECT
SQL_VARIANT_PROPERTY
(
3.1
,
'BASETYPE'
)
basetype
basetype
numeric
Если применить эквивалентное преобразование, которое сохраняет тип
NUMERIC для возвращаемого результата,
1.
SELECT
3.1
x, power
(
3.1
,
2
)
y, power
(
power
(
3.1
,
2
)
,
0.5
)
sqrt_y; то получим ожидаемый результат
x
y
sqrt_y
3.1 9.6 3.1
Аналогичный результат мы получим, применив преобразование типа данных аргумента функции POWER к FLOAT в примере с использованием SQRT. В этом случае функция POWER вернет значение типа FLOAT, и преобразование не потребуется. Действительно,
1.
SELECT
3.1
x, power
(
3.1
,
2
)
y, sqrt
(
power
(
CAST
(
3.1
AS
float
)
,
2
))
sqrt_y;
x
y sqrt_y

3.1 9.6 3,1
Если же изменить порядок применения функций, то никаких "чудес" не возникает:
1.
SELECT
power
(
sqrt
(
9.6
)
,
2
)
power_;
power_
9,6
В этом примере функция SQRT возвращает результат типа FLOAT, что не требует преобразования.
Типичные
проблемы
В этой главе мы рассмотрим несколько типичных проблем, часто возникающих на практике и вызывающих определенные трудности. Следуя нашей традиции, решаться эти задачи будут одним запросом, то есть без создания временных таблиц и использования курсоров.
Кроме того, здесь повторно рассмотрены некоторые аспекты языка для более глубокого понимания.
Генерация
числовой
последовательности
Иногда возникает необходимость получить в запросе числовую последовательность. Это может быть самоцелью или же промежуточным

результатом для получения, скажем, последовательности дат. Пусть, например, требуется получить последовательность целых чисел от 1 до 100 с шагом 1. Можно, конечно, строить такую последовательность в «лоб», то есть:
1.
SELECT
1
AS
num
2.
UNION
ALL
3.
SELECT
2 4. ...
5.
UNION
ALL
6.
SELECT
100
;
А если потребуется 1000 чисел или больше? Помимо затрат времени на написание такого количества операторов, мы будем неэффективно использовать сетевой трафик, передавая на выполнение серверу запросы такого размера.
Помочь нам уменьшить размер запроса может декартово произведение
(CROSS
JOIN), которое редко когда используется непосредственно, но часто является промежуточным результатом в различных алгоритмах получения осмысленных данных. Существенной особенностью декартового произведения является то, что мощность результата (количество строк) равно произведению мощностей участвующих в декартовом произведении таблиц.
Например, если нам нужно получить последовательность 100 чисел, мы можем использовать декартово произведение таблиц, каждая из которых содержит всего по 10 записей. Итак:
1.
SELECT
*
2.
FROM
(
SELECT
1
a
UNION
ALL
SELECT
2
UNION
ALL
SELECT
3
UNION
ALL
SELECT
4 3.
UNION
ALL
SELECT
5
UNION
ALL
SELECT
6
UNION
ALL
SELECT
7 4.
UNION
ALL
SELECT
8
UNION
ALL
SELECT
9
UNION
ALL
SELECT
10 5.
)
x
CROSS
JOIN
6.
(
SELECT
1
b
UNION
ALL
SELECT
2
UNION
ALL
SELECT
3
UNION
ALL
SELECT
4 7.
UNION
ALL
SELECT
5
UNION
ALL
SELECT
6
UNION
ALL
SELECT
7 8.
UNION
ALL
SELECT
8
UNION
ALL
SELECT
9
UNION
ALL
SELECT
10 9.
)
y;

Результатом здесь является двухстолбцовая таблица, содержащая 100 строк.
При этом каждое значение из первого подзапроса (числа от 1 до 10) сочетается с каждым значением из второго (аналогичного) подзапроса: