alan_beaulieu-learning_sql-ru (865932), страница 46
Текст из файла (страница 46)
ERCдиаграмма примера базы данныхbranchbranch_id:smallint unsignedname:varchar(20)address:varchar(30)city:varchar(20)state:varchar(2)zip:varchar(12)product_typeproduct_type_cd:varchar(10)dept_id:smallint unsignedname:varchar(20)employeeemp_id:smallint unsignedname:varchar(50)fname:varchar(20)lname:varchar(20)start_date:dateend_date:datesuperior_emp_id:smallint unsigned (FK)dept_id:smallint unsigned (FK)title: varchar(20)assigned_branch_id:smallint unsigned (FK)productproduct_cd:varchar(10)name:varchar(50)product_type_cd:varchar(10) (FK)date_offered:datedate_retired:datedepartmentaccountaccount_id:integer unsignedproduct_cd:varchar(10) (FK)cust_id:integer unsigned (FK)open_date:dateclose_date:datelast_activity_date:datestatus:varchar(10)open_branch_id:smallint unsigned (FK)open_emp_id:smallint unsigned (FK)avail_balance:float(10,2)pending_balance:float(10,2)transactiontxn_id: integer unsignedtxn_date:datetimeaccount_id:integer unsigned (FK)txn_type_cd:varchar(10)amount:double(10,2)teller_emp_id:smallint unsigned (FK)execution_branch_id:smallint unsigned (FK)funds_avail_date:datetimecustomercust_id:integer unsignedfed_id:varchar(12)cust_type_cd:char(2)address:varchar(30)city:varchar(20)state:varchar(20)postal_code:varchar(10)officerofficer_id:smallint unsignedcust_id:integer unsigned (FK)fname:varchar(30)lname:varchar(30)title:varchar(20)start_date:dateend_date:dateРис.
A.1. ERдиаграммаbusinesscust_id:integer unsigned (FK)name:varchar(40)state_id:varchar(10)incorp_date:dateindividualcust_id:integer unsigned (FK)fname:varchar(30)lname:varchar(30)birth_date:dateMySQLрасширения языка SQLПоскольку для всех примеров в данной книге используется серверMySQL, я подумал, что для читателей, планирующих продолжать ра ботать с MySQL, будет полезным включить приложение, посвященноеMySQL расширениям языка SQL. Это приложение рассматривает не которые MySQL расширения выражений select, insert, update и delete,очень полезные в определенных ситуациях.Расширения выражения SelectРеализация выражения select в MySQL включает два дополнительныхблока, обсуждаемых в следующих разделах.Блок limitВ некоторых ситуациях нас не интересуют все строки, возвращаемыезапросом.
Например, можно создать запрос, выбирающий всех опера ционистов банка и все номера счетов, открытых каждым из них. Еслицель запроса – выявить трех лучших операционистов для вручениянаграды от банка, необязательно знать, кто будет четвертым, пятыми т. д. Для разрешения подобных ситуаций выражение select MySQLвключает блок limit, позволяющий ограничить число возвращаемыхзапросом строк.Чтобы продемонстрировать использование блока limit, начнем с по строения запроса, показывающего количество счетов, открытых каж дым операционистом банка:mysql> SELECT open_emp_id, COUNT(*) how_many> FROM account> GROUP BY open_emp_id;+++260Приложение B| open_emp_id | how_many |+++|1 |8 ||10 |7 ||13 |3 ||16 |6 |+++4 rows in set (0.31 sec)Результат показал, что счета открывали четверо разных сотрудников.Если требуется ограничить результирующий набор только тремя запи сями, можно добавить блок limit.
Он определяет, что должны бытьвозвращены только три записи:mysql> SELECT open_emp_id, COUNT(*) how_many> FROM account> GROUP BY open_emp_id> LIMIT 3;+++| open_emp_id | how_many |+++|1 |8 ||10 |7 ||13 |3 |+++3 rows in set (0.06 sec)Теперь благодаря блоку limit (четвертая строка запроса) результирую щий набор включает только три строки. Четвертый операционист(служащий с ID 16) удален из результирующего набора.Сочетание блока limit с блоком order byПредыдущий запрос возвращает три записи, но есть небольшая про блема: запрос не описывает, какие три записи из четырех нас интере суют.
Если требуется выбрать конкретные три записи, например трехоперационистов, открывших больше всего счетов, придется использо вать блок limit вместе с блоком order by:mysql> SELECT open_emp_id, COUNT(*) how_many> FROM account> GROUP BY open_emp_id> ORDER BY how_many DESC> LIMIT 3;+++| open_emp_id | how_many |+++|1 |8 ||10 |7 ||16 |6 |+++3 rows in set (0.03 sec)MySQLCрасширения языка SQL261Разница между этим и предыдущим запросами в том, что теперь блокlimit применяется к упорядоченному набору.
В итоге в конечном ре зультирующем наборе имеем трех сотрудников, открывших наиболь шее число счетов. Если требуется не произвольная выборка записей,обычно блок limit используется в паре с блоком order by.Блок limit применяется после всех фильтров, группировок и рас становок, поэтому он никогда не изменит результат выраженияselect, только ограничит число возвращаемых им записей.Необязательный второй параметр блока limitДопустим, теперь вместо поиска трех лучших операционистов постав лена задача выбрать всех, кроме двух лучших (вместо награждениялучших исполнителей банк пошлет несколько самых непроизводи тельных операционистов на тренинг по повышению самооценки). Дляподобных ситуаций блок limit предоставляет необязательный второйпараметр. Если используются оба параметра, первый указывает, с ка кой строки добавлять записи в конечный результирующий набор,а второй – сколько строк включить.
Обозначая записи порядковыминомерами, помните, что в MySQL первой записью является запись подномером 0. Следовательно, если стоит задача найти третьего лучшегоработника, можно сделать следующее:mysql> SELECT open_emp_id, COUNT(*) how_many> FROM account> GROUP BY open_emp_id> ORDER BY how_many DESC> LIMIT 2, 1;+++| open_emp_id | how_many |+++|16 |6 |+++1 row in set (0.00 sec)В этом примере нулевая и первая записи отбрасываются; включаютсязаписи, начиная со второй. Поскольку второй параметр блока limit ра вен 1, получаем только одну запись.Если требуется начать со второй позиции и включить все оставшиесязаписи, можно сделать второй аргумент блока limit достаточно боль шим, чтобы все оставшиеся записи гарантированно вошли в результи рующий набор.
Если неизвестно, сколько операционистов открывалиновые счета, для выбора всех работников, кроме двух лучших, можнобыло бы сделать примерно следующее:mysql>>>>SELECT open_emp_id, COUNT(*) how_manyFROM accountGROUP BY open_emp_idORDER BY how_many DESC262Приложение B> LIMIT 2, 999999999;+++| open_emp_id | how_many |+++|16 |6 ||13 |3 |+++2 rows in set (0.00 sec)В этом варианте запроса отбрасываются нулевая и первая записи, а в ре зультат включаются все записи вплоть до 999 999 999, начиная со вто рой (в данном случае таких записей всего две, но лучше немного пере усердствовать, чем потерять нужные записи, недооценив их количество).Ранжирующие запросыЗапросы, включающие блок limit в сочетании с блоком order by, можноназвать ранжирующими запросами (ranking queries), потому что онипозволяют ранжировать данные.
Я уже продемонстрировал примерранжирования банковских сотрудников по числу открытых счетов.Но ранжирующие запросы используются для решения многих при кладных задач, таких как поиск:• Пяти лучших продавцов 2005 года• Третьего по числу круговых пробежек игрока в истории бейсбола• 98 бестселлеров всех времен и народов, кроме Библии и цитатникаМао• Двух самых непопулярных видов мороженогоУже было рассмотрено, как найти трех лучших операционистов, тре тьего лучшего и всех, кроме двух лучших.
Если я хочу сделать что тоаналогичное для четвертого примера (например, найти худших со трудников), требуется просто изменить порядок сортировки на обрат ный, так чтобы результаты располагались, начиная с наименьшегочисла открытых счетов и до наибольшего:mysql> SELECT open_emp_id, COUNT(*) how_many> FROM account> GROUP BY open_emp_id> ORDER BY how_many ASC> LIMIT 2;+++| open_emp_id | how_many |+++|13 |3 ||16 |6 |+++2 rows in set (0.24 sec)За счет простого изменения порядка сортировки (с ORDER BY how_manyDESC на ORDER BY how_many ASC) теперь запрос возвращает двух наихудшихоперационистов. Таким образом, с помощью блока с возрастающимMySQLCрасширения языка SQL263или убывающим порядком сортировки можно создавать ранжирую щие запросы для решения большинства типовых прикладных задач.Блок into outfileЕсли требуется записать результат запроса в файл, можно выделитьего, скопировать в буфер обмена и вставить в свой любимый редактор.Однако если результирующий набор запроса достаточно велик или ес ли запрос выполняется из сценария, необходим способ записывать ре зультаты в файл без участия пользователя.
Для помощи в таких ситуа циях MySQL включает блок into outfile (в выходной файл), в которомможно задать имя файла для записи результатов. Вот пример записирезультатов запроса в каталог c:\temp:mysql> SELECT emp_id, fname, lname, start_date> INTO OUTFILE 'C:\\TEMP\\emp_list.txt'> FROM employee;Query OK, 18 rows affected (0.20 sec)Как говорилось в главе 7, обратный слэш используется для эк ранирования символов в строке. Поэтому в Windows для зада ния пути потребуется ставить по два обратных слэша подряд.Результаты запроса не выводятся на экран, а записываются в файлemp_list.txt и выглядят так:1234...161718MichaelSmith20010622SusanBarker20020912RobertTyler20000209SusanHawthorne20020424TheresaMarkham20010315BethFowler20020629RickTulman20021212Формат по умолчанию использует символ табуляции ('\t') междустолбцами и символ новой строки ('\n') после каждой записи.
Еслитребуется дополнительное форматирование данных, можно включитьв блок into outfile несколько подблоков. Например, если надо предста вить данные в формате, называемом форматом с разделителем «|»(pipedelimited format), то в подблоке fields (поля) можно в качествесимвола разделителя столбцов задать символ '|':mysql> SELECT emp_id, fname, lname, start_date> INTO OUTFILE 'C:\\TEMP\\emp_list_delim.txt'> FIELDS TERMINATED BY '|'> FROM employee;Query OK, 18 rows affected (0.02 sec)MySQL не позволяет перезаписывать существующий файл с по мощью into outfile, поэтому если один и тот же запрос выпол 264Приложение Bняется больше одного раза, перед каждым выполнением следуетудалить имеющийся файл.Содержимое файла emp_list_delim.txt выглядит так:1|Michael|Smith|200106222|Susan|Barker|200209123|Robert|Tyler|200002094|Susan|Hawthorne|20020424...16|Theresa|Markham|2001031517|Beth|Fowler|2002062918|Rick|Tulman|20021212Кроме формата с разделителями «|» можно форматировать данные за пятыми (формат с разделителямизапятыми, commadelimited format).
В этом случае следует задать fields terminated by ','. Однако еслизаписываемые в файл данные включают строки, применение запятыхв качестве разделителей полей может вызвать проблемы. Запятыевстречаются в строках намного чаще, чем символ «|». Рассмотрим сле дующий запрос, записывающий число и две строки, разделенные за пятыми, в файл comma1.txt:mysql> SELECT data.num, data.str1, data.str2> INTO OUTFILE 'C:\\TEMP\\comma1.txt'> FIELDS TERMINATED BY ','> FROM> (SELECT 1 num, 'This string has no commas' str1,>'This string, however, has two commas' str2) data;Query OK, 1 row affected (0.04 sec)Поскольку третий столбец выходного файла (str2) – строка, содержа щая запятые, можно предположить, что у приложения, считывающе го файл comma1.txt, возникнут проблемы при синтаксическом разборестрок и распределении их по столбцам.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
