изучаем SQL. Она позволяет решать многошаговые задачи одним выражением
 Скачать 1.6 Mb.
|
|
UPDATE string_tbl > SET vchar_fld = 'This is a piece of extremely long varchar data'; 124 Глава 7. Создание, преобразование и работа с данными Query OK, 1 row affected, 1 warning (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 1 Столбец изменен, но сформировано следующее предупреждение: mysql> SHOW WARNINGS; + + + + | Level | Code | Message | + + + + | Warning | 1265 | Data truncated for column 'vchar_fld' at row 1 | + + + + 1 row in set (0.00 sec) Если извлечь столбец vchar_fld, то получим: mysql> SELECT vchar_fld > FROM string_tbl; + + | vchar_fld | + + | This is a piece of extremely l | + + 1 row in set (0.05 sec) Как видите, в столбце vchar_fld размещены только первые 30 символов 46 символьной строки. Лучший способ избежать усечения строки (или формирования исключений в случае Oracle Database) при работе со столбцами типа varchar – задавать достаточно большой верхний пре дел длины строки, чтобы иметь возможность работать с самыми длин ными из предполагаемых для хранения строк (помня о том, что сервер распределяет для хранения строки лишь необходимое количество па мяти, т. е. при задании большого верхнего предела для столбцов типа varchar память все же не расходуется впустую). Одинарные кавычки (апострофы) Поскольку строки разграничиваются одинарными кавычками, необ ходимо быть внимательными со строками, включающими одинарные кавычки (апострофы). Например, следующую строку вставить не по лучится, потому что сервер подумает, что апостроф в слове «doesn’t» обозначает конец строки: UPDATE string_tbl SET text_fld = 'This string doesn't work'; Чтобы заставить сервер «проигнорировать» апостроф в слове «doesn’t», понадобится добавить в строку знак экранирования символа (escape). Тогда сервер будет воспринимать апостроф как обычный символ стро ки. Все три сервера обеспечивают возможность сохранить апостроф; для этого надо ввести непосредственно перед апострофом еще один апостроф: mysql> UPDATE string_tbl > SET text_fld = 'This string didn''t work, but it does now'; Строковые данные 125 Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 Пользователи Oracle Database и MySQL также могут сохранить одинарную кавычку, разместив прямо перед ней обратный слэш: UPDATE string_tbl SET text_fld = 'This string didn\'t work, but it does now' При извлечении строки для отображения на экране или в поле сообще ния ее внутренние кавычки не требуют какой либо особой обработки: mysql> SELECT text_fld > FROM string_tbl; + + | text_fld | + + | This string didn't work, but it does now | + + 1 row in set (0.00 sec) Однако если строка извлекается для помещения в файл, предназначен ный для другой программы, возможно, вы захотите вставить в извле ченную строку знак экранирования символа. При работе с MySQL мож но использовать встроенную функцию quote() (кавычка), которая за ключает в кавычки всю строку и добавляет знаки экранирования сим вола к любой одинарной кавычке/апострофу, встречающейся в строке. Вот как выглядит строка, извлеченная с применением функции quote(): mysql> SELECT QUOTE(text_fld) > FROM string_tbl; + + | QUOTE(text_fld) | + + | 'This string didn\'t work, but it does now' | + + 1 row in set (0.04 sec) При извлечении данных с целью экспорта вы, возможно, захотите применить функцию quote() ко всем символьным столбцам, сформиро ванным не системой, таким как столбец customer_notes (примечания клиента). Специальные символы Если приложение предполагается применять в разных странах, стро ки могут включать символы, которых нет на клавиатуре разработчи ка. Например, при работе с французским и немецким языками может понадобиться включать символы с диакритическими знаками, такие как eЂ или o . Серверы SQL Server и MySQL включают встроенную функ цию char(), позволяющую создавать строки из всех 255 символов набо ра ASCII (пользователи Oracle Database могут применять функцию 126 Глава 7. Создание, преобразование и работа с данными chr() ). Для примера следующий фрагмент кода извлекает напечатан ную строку и ее эквивалент, собранный из отдельных символов: mysql> SELECT 'abcdefg', CHAR(97,98,99,100,101,102,103); + + + | abcdefg | CHAR(97,98,99,100,101,102,103) | + + + | abcdefg | abcdefg | + + + 1 row in set (0.01 sec) Таким образом, 97 й символ набора символов ASCII – это буква a. При веденные выше символы не являются специальными, а вот следую щий пример показывает местоположение символов с диакритически ми знаками и других специальных символов, таких как знаки валют: mysql> SELECT CHAR(128,129,130,131,132,133,134,135,136,137); + + | CHAR(128,129,130,131,132,133,134,135,136,137) | + + | | + + 1 row in set (0.01 sec) mysql> SELECT CHAR(138,139,140,141,142,143,144,145,146,147); + + | CHAR(138,139,140,141,142,143,144,145,146,147) | + + | | + + 1 row in set (0.01 sec) mysql> SELECT CHAR(148,149,150,151,152,153,154,155,156,157); + + | CHAR(148,149,150,151,152,153,154,155,156,157) | + + | | + + 1 row in set (0.00 sec) mysql> SELECT CHAR(158,159,160,161,162,163,164,165); + + | CHAR(158,159,160,161,162,163,164,165) | + + | | + + 1 row in set (0.01 sec) В примерах данного раздела используется набор символов latin1 . Если сеанс cконфигурирован под другой набор, вы уви дите символы, отличные от приведенных здесь. Идея та же, но, чтобы находить определенные символы, вам придется освоить расположение символов своего набора. Строковые данные 127 Построение строк символ за символом может быть достаточно утоми тельным, особенно если в строке всего лишь несколько символов с диа критическими знаками. К счастью, можно воспользоваться функцией concat() и соединить отдельные строки, часть которых можно ввести с клавиатуры, а другие – сформировать с помощью функции char(). Например, следующий фрагмент кода показывает, как построить фра зу danke scho .. n с помощью функций concat() и char(): mysql> SELECT CONCAT('danke sch', CHAR(148), 'n'); + + | CONCAT('danke sch', CHAR(148), 'n') | + + | danke scho n | + + 1 row in set (0.00 sec) Пользователи Oracle Database вместо функции concat() могут применять оператор конкатенации (||): SELECT 'danke sch' || CHR(148) || 'n' FROM dual; В SQL Server нет функции concat(), поэтому придется использо вать оператор конкатенации (+): SELECT 'danke sch' + CHAR(148) + 'n' Найти ASCII эквивалент нужного символа можно с помощью функ ции ascii(), принимающей самый левый символ строки и возвращаю щей его номер: mysql> SELECT ASCII('o .. '); + + | ASCII('o ') | + + | 148 | + + 1 row in set (0.00 sec) Функции char(), ascii() и concat() (как и операторы конкатенации) по зволяют работать с любым романским языком, даже если клавиатура не включает символы с диакритическими знаками или спецсимволы. Работа со строками Каждый сервер БД включает множество встроенных функций для рабо ты со строками. В данном разделе будут рассмотрены строковые функ ции двух типов: возвращающие числа и строки. Однако прежде чем на чать, возвратим данные таблицы string_tbl к исходному состоянию: mysql> DELETE FROM string_tbl; Query OK, 1 row affected (0.02 sec) mysql> INSERT INTO string_tbl (char_fld, vchar_fld, text_fld) 128 Глава 7. Создание, преобразование и работа с данными > VALUES ('This string is 28 characters', > 'This string is 28 characters', > 'This string is 28 characters'); Query OK, 1 row affected (0.00 sec) Строковые функции, возвращающие числа Одна из наиболее широко используемых строковых функций, возвра щающих числа, – функция length() (длина), которая возвращает чис ло символов в строке (пользователям SQL Server придется использо вать функцию len()). В следующем запросе функция length() применя ется к каждому столбцу таблицы string_tbl: mysql> SELECT LENGTH(char_fld) char_length, > LENGTH(vchar_fld) varchar_length, > LENGTH(text_fld) text_length > FROM string_tbl; + + + + | char_length | varchar_length | text_length | + + + + | 28 | 28 | 28 | + + + + 1 row in set (0.00 sec) Длины столбцов varchar и text вполне ожидаемы, но предполагалось, что столбец char содержит строки длиной 30 символов, – я ведь говорил, что строки, хранящиеся в столбцах типа char, дополняются пробелами спра ва. Но сервер MySQL удаляет пробелы в конце строки при извлечении данных типа char, поэтому для всех строковых функций, независимо от типа столбца, хранящего строки, получены аналогичные результаты. Кроме определения длины строки может потребоваться найти место положение подстроки в строке. Например, если надо определить, где в столбце vchar_fld располагается строка «characters», можно восполь зоваться функцией position() (положение), как показано ниже: mysql> SELECT POSITION('characters' IN vchar_fld) > FROM string_tbl; + + | POSITION('characters' IN vchar_fld) | + + | 19 | + + 1 row in set (0.12 sec) Если не получается найти подстроку, функция position() возвращает 0. Программисты на таких языках, как С или С++, в которых пер вый элемент массива имеет порядковый номер 0, при работе с базами данных должны помнить, что порядковый номер пер вого символа строки равен 1. Если функция position() возвраща ет значение 0, это указывает на то, что подстрока не найдена, а не на то, что подстрока обнаружена в строке на первой позиции. Строковые данные 129 Если требуется начать поиск не с первого символа целевой строки, не обходимо использовать функцию locate(), аналогичную функции po sition() за тем исключением, что допускает третий необязательный параметр, предназначенный для задания стартовой позиции поиска. И еще функция locate() является собственной функцией производите лей БД, тогда как position() – часть стандарта SQL:2003. Вот пример запроса позиции строки 'is', начинающего поиск с пятого символа столбца vchar_fld: mysql> SELECT LOCATE('is', vchar_fld, 5) > FROM string_tbl; + + | LOCATE('is', vchar_fld, 5) | + + | 13 | + + 1 row in set (0.02 sec) В Oracle Database нет функции position() или locate(), но есть функция instr(), которая воспроизводит функцию position(), если задано два аргумента, и функцию locate(), если задано три аргумента. В SQL Server тоже нет функции position() или lo cate() , но есть функция charindx(), которая также принимает два или три аргумента аналогично функции instr() Oracle. Еще одна функция, принимающая строки в качестве аргументов и воз вращающая числа, – функция сравнения строк strcmp(). Strcmp(), ко торая реализована только в MySQL и не имеет аналогов в Oracle Data base или SQL Server. Она принимает в качестве аргументов две строки и возвращает одно из следующих значений: Чтобы проиллюстрировать работу этой функции, сначала покажем с помощью запроса порядок сортировки пяти строк, а затем проведем сравнение строк с помощью функции strcmp(). Вот пять строк, кото рые будут вставлены в таблицу string_tbl: mysql> DELETE FROM string_tbl; Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO string_tbl(vchar_fld) VALUES ('abcd'); Query OK, 1 row affected (0.03 sec) mysql> INSERT INTO string_tbl(vchar_fld) VALUES ('xyz'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO string_tbl(vchar_fld) VALUES ('QRSTUV'); Query OK, 1 row affected (0.00 sec) 1 если первая строка в порядке сортировки расположена до второй строки 0 если строки идентичны 1 если первая строка в порядке сортировки расположена после второй строки 130 Глава 7. Создание, преобразование и работа с данными mysql> INSERT INTO string_tbl(vchar_fld) VALUES ('qrstuv'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO string_tbl(vchar_fld) VALUES ('12345'); Query OK, 1 row affected (0.00 sec) Вот эти пять строк в порядке сортировки: mysql> SELECT vchar_fld > FROM string_tbl > ORDER BY vchar_fld; + + | vchar_fld | + + | 12345 | | abcd | | QRSTUV | | qrstuv | | xyz | + + 5 rows in set (0.00 sec) Следующий запрос проводит шесть сравнений пяти разных строк: mysql> SELECT STRCMP('12345','12345') 12345_12345, > STRCMP('abcd','xyz') abcd_xyz, > STRCMP('abcd','QRSTUV') abcd_QRSTUV, > STRCMP('qrstuv','QRSTUV') qrstuv_QRSTUV, > STRCMP('12345','xyz') 12345_xyz, > STRCMP('xyz','qrstuv') xyz_qrstuv; + + + + + + + | 12345_12345 | abcd_xyz | abcd_QRSTUV | qrstuv_QRSTUV | 12345_xyz | xyz_qrstuv | + + + + + + + | 0 | 1 | 1 | 0 | 1 | 1 | + + + + + + + 1 row in set (0.00 sec) В результате первого сравнения получаем вполне ожидаемое значение 0 , поскольку строка сравнивалась сама с собой. Четвертое сравнение также дает 0, что немного неожиданно, поскольку строки состоят из одних и тех же букв, но в одной строке все буквы в верхнем регистре, а в другой – в нижнем. Причиной такого результата является то, что функция strcmp() MySQL не чувствительна к регистру, о чем надо пом нить при ее использовании. Остальные четыре сравнения дают или 1, или 1 в зависимости от порядка расположения строк в порядке сорти ровки. Например, strcmp('abcd','xyz') дает 1, поскольку строка 'abcd' идет перед строкой 'xyz'. Наряду с функцией strcmp() MySQL позволяет использовать в блоке select операторы like и regexp для сравнения строк. Результатом таких сравнений будет 1 (для true) и 0 (для false). Следовательно, эти опера торы позволяют создавать выражения, возвращающие число, подобно функциям, описанным в этом разделе. Вот пример использования опе ратора like: Строковые данные 131 mysql> SELECT name, name LIKE '%ns' ends_in_ns > FROM department; + + + | name | ends_in_ns | + + + | Operations | 1 | | Loans | 1 | | Administration | 0 | + + + 3 rows in set (0.25 sec) В этом примере выбираются все названия отделов. Также есть выра жение, возвращающее 1, если название отдела заканчивается на «ns» или 0 в противном случае. Для поиска совпадений по более сложному шаблону можно использовать оператор regexp, как показано ниже: mysql> SELECT cust_id, cust_type_cd, fed_id, > fed_id REGEXP '.{3} .{2} .{4}' is_ss_no_format > FROM customer; + + + + + | cust_id | cust_type_cd | fed_id | is_ss_no_format | + + + + + | 1 | I | 111 11 1111 | 1 | | 2 | I | 222 22 2222 | 1 | | 3 | I | 333 33 3333 | 1 | | 4 | I | 444 44 4444 | 1 | | 5 | I | 555 55 5555 | 1 | | 6 | I | 666 66 6666 | 1 | | 7 | I | 777 77 7777 | 1 | | 8 | I | 888 88 8888 | 1 | | 9 | I | 999 99 9999 | 1 | | 10 | B | 04 1111111 | 0 | | 11 | B | 04 2222222 | 0 | | 12 | B | 04 3333333 | 0 | | 13 | B | 04 4444444 | 0 | + + + + + 13 rows in set (0.00 sec) Четвертый столбец этого запроса возвращает 1, если значение в столб це fed_id соответствует формату номера социальной страховки. Пользователи SQL Server и Oracle Database могут получить ана логичные результаты с помощью выражений case, описанных в главе 11. Строковые функции, возвращающие строки В некоторых случаях требуется изменить имеющиеся строки – уда лить/добавить текстовый фрагмент. Для выполнения этих задач у ка ждого сервера БД есть множество функций. Прежде чем начать, еще раз возвратим данные таблицы string_tbl в исходное состояние: 132 Глава 7. Создание, преобразование и работа с данными mysql> DELETE FROM string_tbl; Query OK, 5 rows affected (0.00 sec) mysql> INSERT INTO string_tbl (text_fld) > VALUES ('This string was 29 characters'); Query OK, 1 row affected (0.01 sec) Ранее в этой главе было описано построение слов, включающих симво лы с диакритическими знаками, с помощью функции concat(). Функ ция concat() полезна и во многих других ситуациях, например, если требуется добавить в конец хранящейся строки дополнительные сим волы. В следующем примере строка в столбце text_fld изменяется пу тем добавления в ее конец дополнительной фразы: mysql> UPDATE string_tbl > SET text_fld = CONCAT(text_fld, ', but now it is longer'); Query OK, 1 row affected (0.03 sec) Rows matched: 1 Changed: 1 Warnings: 0 Теперь столбец text_fld содержит следующую строку: mysql> SELECT text_fld > FROM string_tbl; + + | text_fld | + + | This string was 29 characters, but now it is longer | + + 1 row in set (0.00 sec) Таким образом, как и все функции, возвращающие строку, concat() можно использовать для замещения данных, хранящихся в столбце символьного типа. Другое традиционное применение функции concat() – построение стро ки из отдельных частей данных. Например, следующий запрос форми рует строку примечания для каждого операциониста банка: mysql> SELECT CONCAT(fname, ' ', lname, ' has been a ', > title, ' since ', start_date) emp_narrative > FROM employee > WHERE title = 'Teller' OR title = 'Head Teller'; + + | emp_narrative | + + | Helen Fleming has been a Head Teller since 2004 03 17 | | Chris Tucker has been a Teller since 2004 09 15 | | Sarah Parker has been a Teller since 2002 12 02 | | Jane Grossman has been a Teller since 2002 05 03 | | Paula Roberts has been a Head Teller since 2002 07 27 | | Thomas Ziegler has been a Teller since 2000 10 23 | | Samantha Jameson has been a Teller since 2003 01 08 | | John Blake has been a Head Teller since 2000 05 11 | | Cindy Mason has been a Teller since 2002 08 09 | |