изучаем SQL. Она позволяет решать многошаговые задачи одним выражением

• Название: Она позволяет решать многошаговые задачи одним выражением
• Дата: 09.02.2018
• Размер: 1.6 Mb.
• Имя файла: изучаем SQL.pdf
• Тип: Документы; #36127
• страница: 15 из 31

133
| Frank Portman has been a Teller since 2003 04 01 |
| Theresa Markham has been a Head Teller since 2001 03 15 |
| Beth Fowler has been a Teller since 2002 06 29 |
| Rick Tulman has been a Teller since 2002 12 12 |
+
+
13 rows in set (0.12 sec)
Функция concat() может обрабатывать любое выражение, возвращаю щее строку, и даже преобразует числа и даты в строковый формат,
о чем свидетельствует столбец дат (start_date), используемый как ар гумент. Хотя Oracle Database включает функцию concat(), она может принимать только строковые аргументы, поэтому в Oracle предыду щий запрос работать не будет. В этом случае придется использовать оператор конкатенации (||), а не вызов функции:
SELECT fname || ' ' || lname || ' has been a ' ||
title || ' since ' || start_date emp_narrative
FROM employee
WHERE title = 'Teller' OR title = 'Head Teller';
В SQL Server нет функции concat(), поэтому используется такой же подход, что и в предыдущем примере, только с применением операто ра конкатенации SQL Server (+, а не ||).
Функция concat() полезна для добавления символов в начало или конец строки, но также позволяет ввести или заменить символы в середине
строки. Все три сервера БД предоставляют специальные функции для этого, но все они разные, поэтому сначала рассмотрим функцию
MySQL, а затем перейдем к функциям двух других серверов.
MySQL включает функцию insert(), которая принимает четыре аргу мента: исходную строку, начальное положение, число символов, тре бующих замены, и замещающую строку. В зависимости от значения третьего аргумента функция выполняет вставку либо замену символов строки. Если третий аргумент равен нулю, то замещающая строка вставляется со сдвигом всех последующих символов вправо, например:
mysql> SELECT INSERT('goodbye world', 9, 0, 'cruel ') string;
+
+
| string |
+
+
| goodbye cruel world |
+
+
1 row in set (0.00 sec)
В этом примере все символы, начиная с девятого, сдвигаются вправо,
и вставляется строка 'cruel '. Если третий аргумент больше нуля, то замещающая строка замещает указанное количество символов, на пример:
mysql> SELECT INSERT('goodbye world', 1, 7, 'hello') string;
+
+

134
Глава 7. Создание, преобразование и работа с данными
| string |
+
+
| hello world |
+
+
1 row in set (0.00 sec)
В этом примере первые семь символов замещаются строкой 'hello'.
В Oracle Database нет единой функции, обладающей гибкостью in sert()
MySQL, но в Oracle есть функция replace(), замещающая одну подстроку другой. Вот предыдущий пример, переработанный с ис пользованием replace():
SELECT REPLACE('goodbye world', 'goodbye', 'hello')
FROM dual;
Все экземпляры строки 'goodbye' будут замещены строкой 'hello'.
В результате получаем строку 'hello world'. Функция заместит все эк земпляры искомой строки замещающей строкой – будьте вниматель ны, чтобы не получить больше замещений, чем задумано.
В SQL Server есть и функция replace() с той же функциональностью, что и в Oracle, а также функция stuff() (заполнить), функциональные воз можности которой аналогичны функции insert() MySQL. Вот пример:
SELECT STUFF('hello world', 1, 5, 'goodbye cruel')
Во время выполнения этого запроса удаляются пять символов, начи ная с первой позиции, и на их место вставляется строка 'goodbye cru el'
. В результате получаем строку 'goodbye cruel world'.
Кроме вставки символов в строку может понадобиться извлечь из стро ки подстроку. Для этого все три сервера включают функцию sub string()
(подстрока) (правда, в Oracle Database эта функция называет ся substr()). Она извлекает указанное число символов, начиная с за данной позиции. В следующем примере из строки извлекается пять символов, начиная с девятой позиции:
mysql> SELECT SUBSTRING('goodbye cruel world', 9, 5);
+
+
| SUBSTRING('goodbye cruel world', 9, 5) |
+
+
| cruel |
+
+
1 row in set (0.00 sec)
Кроме упомянутых, все три сервера включают множество других встроенных функций для работы со строковыми данными. Хотя на значение многих из них сугубо специальное, например формирование строкового эквивалента восьмеричных или шестнадцатеричных чи сел, есть и функции общего назначения, например удаляющие или до бавляющие пробелы в конце текстовой строки. Более подробную ин формацию можно получить в справочном руководстве по SQL для кон

Числовые данные
135
кретного сервера или универсальном справочнике по SQL, например
«SQL in a Nutshell» (O’Reilly).
Числовые данные
В отличие от строковых данных (и временных, как вы вскоре увидите)
числовые данные довольно просты. Число можно ввести с клавиату ры, извлечь из другого столбца или сформировать с помощью вычис ления. Для вычислений доступны все обычные арифметические опе раторы (+, , *, /), а для задания порядка вычислений – скобки:
mysql> SELECT (37 * 59) / (78 (8 * 6));
+
+
| (37 * 59) / (78 (8 * 6)) |
+
+
| 72.77 |
+
+
1 row in set (0.00 sec)
Как упоминалось в главе 2, основная проблема при хранении число вых данных – возможное округление (иногда очень грубое) при превы шении предела, заданного для числового столбца. Например, число
999,99 при сохранении в столбце, определенном как float(3,1), будет округлено до 99,9.
Выполнение арифметических операций
Большинство встроенных числовых функций предназначено для вы полнения определенных арифметических операций, таких как вычис ление квадратного корня числа. В табл. 7.1 перечислены некоторые распространенные числовые функции, принимающие один числовой аргумент и возвращающие число.
Таблица 7.1. Одноаргументные числовые функции
Функция
Описание
Acos(x)
Вычисляет арккосинус x
Asin(x)
Вычисляет арксинус x
Atan(x)
Вычисляет арктангенс x
Cos(x)
Вычисляет косинус x
Cot(x)
Вычисляет котангенс x
Exp(x)
Вычисляет e
x
Ln(x)
Вычисляет натуральный логарифм x
Sin(x)
Вычисляет синус x
Sqrt(x)
Вычисляет квадратный корень из x
Tan(x)
Вычисляет тангенс x

136
Глава 7. Создание, преобразование и работа с данными
Эти функции осуществляют очень специальные задачи. Не будем при водить здесь примеры для них (если читатель не узнаёт функцию по на званию или описанию, то, скорее всего, она ему не нужна). Однако дру гие числовые функции, используемые в вычислениях, чуть более гибки и заслуживают некоторого пояснения.
Например, оператор modulo, вычисляющий остаток от деления одного числа на другое, реализован в MySQL и Oracle Database функцией mod().
В следующем примере вычисляется остаток от деления 10 на 4:
mysql> SELECT MOD(10,4);
+
+
| MOD(10,4) |
+
+
| 2 |
+
+
1 row in set (0.02 sec)
Обычно функция mod() используется с целыми аргументами, но в My
SQL 4.1.7 и более поздних версий допустимы и вещественные аргу менты:
mysql> SELECT MOD(22.75, 5);
+
+
| MOD(22.75, 5) |
+
+
| 2.75 |
+
+
1 row in set (0.02 sec)
В SQL Server нет функции mod(). Вместо нее для нахождения ос татка используется оператор %. Следовательно, выражение 10 % 4
дает в результате значение 2.
Другая числовая функция, принимающая два числовых аргумента, –
функция pow() (в Oracle Database или SQL Server – power()), которая возвращает первое число в степени, равной второму числу, например:
mysql> SELECT POW(2,8);
+
+
| POW(2,8) |
+
+
| 256 |
+
+
1 row in set (0.03 sec)
Таким образом, pow(2,8) – эквивалент MySQL для записи 2 8
. Посколь ку память компьютера распределена блоками по 2
x
байт, с помощью функции pow() может быть удобно определять точное число байт в па мяти определенного объема:
mysql> SELECT POW(2,10) kilobyte, POW(2,20) megabyte,
> POW(2,30) gigabyte, POW(2,40) terabyte;

Числовые данные
137
+
+
+
+
+
| kilobyte | megabyte | gigabyte | terabyte |
+
+
+
+
+
| 1024 | 1048576 | 1073741824 | 1099511627776 |
+
+
+
+
+
1 row in set (0.00 sec)
Не знаю как вам, но мне проще запомнить гигабайт как 2 30
байт, а не как число 1 073 741 824.
Управление точностью числовых данных
Числа с плавающей точкой не всегда обязаны взаимодействовать или отображаться полностью. Например, можно хранить данные о денеж ных операциях с точностью до шести десятичных разрядов, но при отображении округлять их до сотых. Для ограничения точности чисел с плавающей точкой предназначены четыре функции – ceil(), floor(),
round()
и truncate(). Все три сервера включают эти функции, только
Oracle Database использует trunc() вместо truncate(), а SQL Server –
ceiling()
вместо ceil().
Функции ceil() (потолок) и floor() (пол) предназначены для округле ния вверх или вниз до ближайшего целого, как показано в следующем примере:
mysql> SELECT CEIL(72.445), FLOOR(72.445);
+
+
+
| CEIL(72.445) | FLOOR(72.445) |
+
+
+
| 73 | 72 |
+
+
+
1 row in set (0.06 sec)
Как видите, любое число в диапазоне между 72 и 73 округляется до 73
(функция ceil()) или до 72 (функция floor()). Необходимо помнить,
что ceil() округлит до 73, даже если десятичная часть числа очень ма ла, и floor() округлит до 72, даже если десятичная часть достаточно велика:
mysql> SELECT CEIL(72.000000001), FLOOR(72.999999999);
+
+
+
| CEIL(72.000000001) | FLOOR(72.999999999) |
+
+
+
| 73 | 72 |
+
+
+
1 row in set (0.00 sec)
Если предыдущие функции предлагают округления, слишком грубые для приложения, можно использовать функцию round() (округлить).
Она округляет в большую или меньшую сторону от середины проме жутка между двумя целыми, например:

138
Глава 7. Создание, преобразование и работа с данными mysql> SELECT ROUND(72.49999), ROUND(72.5), ROUND(72.50001);
+
+
+
+
| ROUND(72.49999) | ROUND(72.5) | ROUND(72.50001) |
+
+
+
+
| 72 | 72 | 73 |
+
+
+
+
1 row in set (0.00 sec)
При использовании функции round() любое число, десятичная часть которого относится к верхней половине диапазона между двумя целы ми, округляется с увеличением, а если его десятичная часть относится к нижней половине диапазона, то выполняется округление с уменьше нием.
Чаще всего требуется не округлять число до ближайшего целого, а со хранить, по крайней мере, несколько разрядов его десятичной части.
Функция round() допускает необязательный второй аргумент, задаю щий число разрядов справа от десятичной точки, до которого прово дится округление. Следующий пример показывает, как можно ис пользовать второй аргумент для округления числа 72,0909 до первого,
второго и третьего десятичного знака:
mysql> SELECT ROUND(72.0909, 1), ROUND(72.0909, 2), ROUND(72.0909, 3);
+
+
+
+
| ROUND(72.0909, 1) | ROUND(72.0909, 2) | ROUND(72.0909, 3) |
+
+
+
+
| 72.1 | 72.09 | 72.091 |
+
+
+
+
1 row in set (0.00 sec)
Как и функция round(), функция truncate() допускает необязательный второй аргумент, задающий число разрядов справа от десятичной точ ки, при этом truncate() просто отбрасывает лишние разряды без округ ления. Пример показывает, как было бы усечено число 72,0909 до од ного, двух и трех десятичных знаков:
mysql> SELECT TRUNCATE(72.0909, 1), TRUNCATE(72.0909, 2),
> TRUNCATE(72.0909, 3);
+
+
+
+
| TRUNCATE(72.0909, 1) | TRUNCATE(72.0909, 2) | TRUNCATE(72.0909, 3) |
+
+
+
+
| 72.0 | 72.09 | 72.090 |
+
+
+
+
1 row in set (0.00 sec)
В SQL Server нет функции truncate(). Ее роль играет функция round()
, допуская третий необязательный аргумент; если он при сутствует и отличен от нуля, выполняется усечение, а не округ ление числа.
Обе функции, truncate() и round(), также допускают отрицательное зна чение второго аргумента, означающее усечение или округление числа

Числовые данные
139
слева от десятичной точки. На первый взгляд эта возможность может показаться странной, но для ее наличия есть веские основания. Напри мер, есть продукт, закупка которого возможна только в количестве,
пропорциональном десяти. Если покупатель закажет 17 единиц, то из менить заказанное количество можно одним из следующих способов:
mysql> SELECT ROUND(17, 1), TRUNCATE(17, 1);
+
+
+
| ROUND(17, 1) | TRUNCATE(17, 1) |
+
+
+
| 20 | 10 |
+
+
+
1 row in set (0.00 sec)
Если рассматриваемый продукт – канцелярские кнопки, то, скорее всего, итоговая сумма не сильно зависит от того, продано 10 или 20
штук при запрошенных 17; однако если речь идет о часах Rolex, для процветания бизнеса лучше будет округлять.
Обработка данных со знаком
При работе с числовыми столбцами, допускающими отрицательные значения (в главе 2 было показано, как сделать столбец беззнаковым,
т. е. допускающим только положительные числа), могут быть полез ными несколько числовых функций. Скажем, требуется составить от чет о текущем состоянии всех банковских счетов. Следующий запрос возвращает три столбца, помогающих сформировать отчет:
mysql> SELECT account_id, SIGN(avail_balance), ABS(avail_balance)
> FROM account;
+
+
+
+
| account_id | SIGN(avail_balance) | ABS(avail_balance) |
+
+
+
+
| 1 | 1 | 1057.75 |
| 2 | 1 | 500.00 |
| 3 | 1 | 3000.00 |
| 4 | 1 | 2258.02 |
| 5 | 1 | 200.00 |
| ... |
| 19 | 1 | 1500.00 |
| 20 | 1 | 23575.12 |
| 21 | 0 | 0.00 |
| 22 | 1 | 9345.55 |
| 23 | 1 | 38552.05 |
| 24 | 1 | 50000.00 |
+
+
+
+
24 rows in set (0.00 sec)
Второй столбец использует функцию sign() (знак), возвращающую:
1
, если баланс счета отрицателен, 0, если баланс нулевой, и 1, если ба ланс положительный. С помощью функции abs() в третьем столбце возвращается абсолютное значение баланса.

140
Глава 7. Создание, преобразование и работа с данными
Временные данные
Из трех типов данных, обсуждаемых в этой главе (символьные, число вые и временные), временные данные – наиболее сложные с точки зре ния создания и обработки. Сложность временных данных отчасти обу словлена бесконечным множеством способов описания дат и времени.
Например, дату написания этого абзаца можно записать любым из сле дующих способов:
•
суббота, 19 марта 2005
•
3/19/2005 2:14:56 P.M. EST
•
3/19/2005 19:14:56 GMT
•
0782005 (Юлианский формат)
•
Звездная дата [ 4] 82213.47 14:14:56 (формат фильма «Звездный путь»)
Хотя некоторые записи отличаются только форматированием, основ ная сложность заключается в избранной системе отсчета, что рассмат ривается в следующем разделе.
Часовые пояса
Поскольку всюду на планете люди считают полднем наивысшую точку подъема солнца над горизонтом, никто и не пытался ввести одни уни версальные часы на всех. Вместо этого мир был разделен на 24 вообра жаемые секции, названные часовыми поясами (time zones). В рамках одного часового пояса все придерживаются текущего времени, а в дру гом поясе люди живут по другому времени. Все выглядит достаточно просто, но одни географические регионы переводят свое время на час дважды в год (реализуя нак называемое декретное время (Daylight Sa
vings Time
)), а другие – нет. Таким образом, разница во времени между двумя точками планеты может первые полгода составлять четыре часа,
а вторые – пять часов. Даже в рамках одного часового пояса одни ре гионы могут принимать, а другие не принимать декретное время. По этому в одном часовом поясе полгода время может совпадать, а полгода отличаться на час.
Компьютерная эра обострила эту проблему, хотя различия часовых поясов известны со времен великих географических открытий. Чтобы обеспечить общую точку отсчета для хронометрирования, мореплава тели XV столетия устанавливали свои часы по Гринвичу (Англия). Это время назвали временем по Гринвичу (Greenwich Mean Time), или
GMT. Все остальные часовые пояса можно описать разностью между
GMT и местным временем. Например, часовой пояс восточных штатов
Америки, известный как восточное поясное время (Eastern Standard
Time
), можно описать как GMT 5:00 (на пять часов раньше GMT).
Сегодня используется разновидность GMT – универсальное глобальное
время
(coordinated universal time), или UTC, отсчитываемое по атом

Временные данные
141
ным часам (или точнее среднее время 200 атомных часов, размещен ных в 50 точках по всему миру, которое называют всемирным временем
(universal time)). И SQL Server, и MySQL предоставляют функции, воз вращающие текущее время UTC (getutcdate() для SQL Server и utc_ti mestamp()
для MySQL).
Большинство серверов БД по умолчанию используют настройки часо вого пояса сервера, на котором размещены, и предоставляют инстру менты для изменения часового пояса в случае необходимости. Напри мер БД, предназначенная для хранения фондовых операций со всего света, обычно конфигурируется на использование времени UTC, тогда как БД для хранения операций конкретного предприятия розничной торговли может использовать часовой пояс сервера.
MySQL придерживается двух разных настроек часового пояса – гло бальный часовой пояс и сеансовый часовой пояс, который может отли чаться для каждого зарегистрированного пользователя. Следующий запрос позволяет увидеть обе настройки:
mysql> SELECT @@global.time_zone, @@session.time_zone;
+
+
+
| @@global.time_zone | @@session.time_zone |
+
+
+
| SYSTEM | SYSTEM |
+
+
+
1 row in set (0.00 sec)
Значение system (система) сообщает, что сервер использует настройку часового пояса сервера, на котором установлена БД.
Если пользователь находится в Цюрихе (Швейцария) и по сети откры вает сеанс на сервере MySQL, расположенном в Нью Йорке, вероятно,
он захочет изменить часовой пояс для своего сеанса и может сделать это посредством следующей команды:
mysql>