изучаем SQL. Она позволяет решать многошаговые задачи одним выражением
 Скачать 1.6 Mb.
|
|
Глава 10 10.1 Напишите запрос, возвращающий все типы счетов и открытые счета этих типов (для соединения с таблицей product используйте столбец product_cd таблицы account). Должны быть включены все типы счетов, даже если не был открыт ни один счет определенного типа. mysql> SELECT p.product_cd, a.account_id, a.cust_id, a.avail_balance > FROM product p LEFT OUTER JOIN account a > ON p.product_cd = a.product_cd; + + + + + | product_cd | account_id | cust_id | avail_balance | + + + + + | AUT | NULL | NULL | NULL | | BUS | 21 | 10 | 0.00 | | BUS | 22 | 11 | 9345.55 | | CD | 3 | 1 | 3000.00 | | CD | 13 | 6 | 10000.00 | | CD | 14 | 7 | 5000.00 | | CD | 19 | 9 | 1500.00 | | CHK | 1 | 1 | 1057.75 | Решения к упражнениям 285 | CHK | 4 | 2 | 2258.02 | | CHK | 6 | 3 | 1057.75 | | CHK | 8 | 4 | 534.12 | | CHK | 11 | 5 | 2237.97 | | CHK | 12 | 6 | 122.37 | | CHK | 15 | 8 | 3487.19 | | CHK | 17 | 9 | 125.67 | | CHK | 20 | 10 | 23575.12 | | CHK | 23 | 12 | 38552.05 | | MM | 7 | 3 | 2212.50 | | MM | 10 | 4 | 5487.09 | | MM | 18 | 9 | 9345.55 | | MRT | NULL | NULL | NULL | | SAV | 2 | 1 | 500.00 | | SAV | 5 | 2 | 200.00 | | SAV | 9 | 4 | 767.77 | | SAV | 16 | 8 | 387.99 | | SBL | 24 | 13 | 50000.00 | + + + + + 26 rows in set (0.01 sec) 10.2 Переформулируйте запрос из упражнения 10.1 и примените другой тип внешнего соединения (т. е. если в упражнении 10.1 использова лось левостороннее внешнее соединение, используйте правосторон нее), так чтобы результаты были, как в упражнении 10.1. mysql> SELECT p.product_cd, a.account_id, a.cust_id, a.avail_balance > FROM account a RIGHT OUTER JOIN product p > ON p.product_cd = a.product_cd; + + + + + | product_cd | account_id | cust_id | avail_balance | + + + + + | AUT | NULL | NULL | NULL | | BUS | 21 | 10 | 0.00 | | BUS | 22 | 11 | 9345.55 | | CD | 3 | 1 | 3000.00 | | CD | 13 | 6 | 10000.00 | | CD | 14 | 7 | 5000.00 | | CD | 19 | 9 | 1500.00 | | CHK | 1 | 1 | 1057.75 | | CHK | 4 | 2 | 2258.02 | | CHK | 6 | 3 | 1057.75 | | CHK | 8 | 4 | 534.12 | | CHK | 11 | 5 | 2237.97 | | CHK | 12 | 6 | 122.37 | | CHK | 15 | 8 | 3487.19 | | CHK | 17 | 9 | 125.67 | | CHK | 20 | 10 | 23575.12 | | CHK | 23 | 12 | 38552.05 | 286 Приложение C | MM | 7 | 3 | 2212.50 | | MM | 10 | 4 | 5487.09 | | MM | 18 | 9 | 9345.55 | | MRT | NULL | NULL | NULL | | SAV | 2 | 1 | 500.00 | | SAV | 5 | 2 | 200.00 | | SAV | 9 | 4 | 767.77 | | SAV | 16 | 8 | 387.99 | | SBL | 24 | 13 | 50000.00 | + + + + + 26 rows in set (0.02 sec) 10.3 Проведите внешнее соединение таблицы account с таблицами individ ual и business (посредством столбца account.cust_id) таким образом, чтобы результирующий набор содержал по одной строке для каждого счета. Должны быть включены столбцы count.account_id, account.prod uct_cd , individual.fname, individual.lname и business.name. mysql> SELECT a.account_id, a.product_cd, > i.fname, i.lname, b.name > FROM account a LEFT OUTER JOIN business b > ON a.cust_id = b.cust_id > LEFT OUTER JOIN individual i > ON a.cust_id = i.cust_id; + + + + + + | account_id | product_cd | fname | lname | name | + + + + + + | 1 | CHK | James | Hadley | NULL | | 2 | SAV | James | Hadley | NULL | | 3 | CD | James | Hadley | NULL | | 4 | CHK | Susan | Tingley | NULL | | 5 | SAV | Susan | Tingley | NULL | | 6 | CHK | Frank | Tucker | NULL | | 7 | MM | Frank | Tucker | NULL | | 8 | CHK | John | Hayward | NULL | | 9 | SAV | John | Hayward | NULL | | 10 | MM | John | Hayward | NULL | | 11 | CHK | Charles | Frasier | NULL | | 12 | CHK | John | Spencer | NULL | | 13 | CD | John | Spencer | NULL | | 14 | CD | Margaret | Young | NULL | | 15 | CHK | Louis | Blake | NULL | | 16 | SAV | Louis | Blake | NULL | | 17 | CHK | Richard | Farley | NULL | | 18 | MM | Richard | Farley | NULL | | 19 | CD | Richard | Farley | NULL | | 20 | CHK | NULL | NULL | Chilton Engineering | | 21 | BUS | NULL | NULL | Chilton Engineering | | 22 | BUS | NULL | NULL | Northeast Cooling Inc. | Решения к упражнениям 287 | 23 | CHK | NULL | NULL | Superior Auto Body | | 24 | SBL | NULL | NULL | AAA Insurance Inc. | + + + + + + 24 rows in set (0.05 sec) 10.4 (дополнительно) Разработайте запрос, который сформирует набор {1, 2, 3,…, 99, 100}. (Совет: используйте перекрестное соединение как минимум с двумя подзапросами в блоке from.) SELECT ones.x + tens.x + 1 FROM (SELECT 0 x UNION ALL SELECT 1 x UNION ALL SELECT 2 x UNION ALL SELECT 3 x UNION ALL SELECT 4 x UNION ALL SELECT 5 x UNION ALL SELECT 6 x UNION ALL SELECT 7 x UNION ALL SELECT 8 x UNION ALL SELECT 9 x) ones CROSS JOIN (SELECT 0 x UNION ALL SELECT 10 x UNION ALL SELECT 20 x UNION ALL SELECT 30 x UNION ALL SELECT 40 x UNION ALL SELECT 50 x UNION ALL SELECT 60 x UNION ALL SELECT 70 x UNION ALL SELECT 80 x UNION ALL SELECT 90 x) tens; Глава 11 11.1 Перепишите следующий запрос, использующий простое выражение case , таким образом, чтобы получить аналогичные результаты с помо щью выражения case с перебором вариантов. Попытайтесь свести к ми нимуму количество блоков when. SELECT emp_id, CASE title WHEN 'President' THEN 'Management' WHEN 'Vice President' THEN 'Management' WHEN 'Treasurer' THEN 'Management' WHEN 'Loan Manager' THEN 'Management' WHEN 'Operations Manager' THEN 'Operations' 288 Приложение C WHEN 'Head Teller' THEN 'Operations' WHEN 'Teller' THEN 'Operations' ELSE 'Unknown' END FROM employee; SELECT emp_id, CASE WHEN title LIKE '%President' OR title = 'Loan Manager' OR title = 'Treasurer' THEN 'Management' WHEN title LIKE '%Teller' OR title = 'Operations Manager' THEN 'Operations' ELSE 'Unknown' END FROM employee; 11.2 Перепишите следующий запрос так, чтобы результирующий набор со держал всего одну строку и четыре столбца (по одному для каждого от деления). Назовите столбцы branch_1, branch_2 и т. д. mysql> SELECT open_branch_id, COUNT(*) > FROM account > GROUP BY open_branch_id; + + + | open_branch_id | COUNT(*) | + + + | 1 | 8 | | 2 | 7 | | 3 | 3 | | 4 | 6 | + + + 4 rows in set (0.00 sec) mysql> SELECT > SUM(CASE WHEN open_branch_id = 1 THEN 1 ELSE 0 END) branch_1, > SUM(CASE WHEN open_branch_id = 2 THEN 1 ELSE 0 END) branch_2, > SUM(CASE WHEN open_branch_id = 3 THEN 1 ELSE 0 END) branch_3, > SUM(CASE WHEN open_branch_id = 4 THEN 1 ELSE 0 END) branch_4 > FROM account; + + + + + | branch_1 | branch_2 | branch_3 | branch_4 | + + + + + | 8 | 7 | 3 | 6 | + + + + + 1 row in set (0.02 sec) Дополнительные источники Теперь, прочитав эту книгу, вы должны уверенно продвигаться к про фессиональному владению языком SQL. Я набрался смелости и сделал книгу не просто ознакомительной, а более глубокой. Поэтому некото рые из рассмотренных тем могут по прежнему вызывать определенные трудности. Это хорошо, потому что, по моему, покупать техническую книгу, которую можно прочитать всего один раз, – все равно что бро сать деньги на ветер. Надеюсь, что вы будете перечитывать отдельные главы и продолжать экспериментировать с примером БД до тех пор, пока не сформируете твердое представление об основных концепциях. Следующий шаг этого путешествия зависит от конкретных целей, ко торые вы перед собой ставите. За годы работы я встречался с многими людьми и могу ориентировочно разделить читателей на следующие категории: • Вы программист (или работаете над диссертацией для получения ученой степени по вычислительной технике) без или с небольшим предварительным знанием теории баз данных и хотите расширить свои навыки или вас попросили помочь разобраться с аспектами проекта, связанными с БД. • Вы непрограммист, но получили задание разработать проект созда ния отчетов или управления интеллектуальными ресурсами компа нии, возможно, включая установку и администрирование сервера BI (Business Intelligence – интеллектуальные ресурсы), такого как Business Objects, Actuate, Microstrategy или Cognos. • Вы системный администратор и хотите распространить свою дея тельность и на администрирование БД. • Вы владелец малого предприятия, которому требуется БД для от слеживания клиентов, материально производственных запасов, за казов и т. д. или используете «коробочное» приложение и хотите создавать специальные отчеты. 290 Приложение D • Вы не относитесь к вышеупомянутым категориям, но каким то об разом на вашем ноутбуке (надеюсь, это не рабочий компьютер) ока залась база данных. В зависимости о того, какая из указанных категорий лучше всего опи сывает вашу ситуацию, вас заинтересует одна или несколько следую щих тем: • Углубленное изучение SQL • Программирование баз данных • Проектирование баз данных • Настройка баз данных • Администрирование баз данных • Создание отчетов Далее в этом приложении представлены все эти темы и предложены некоторые источники, помогающие овладеть этими дополнительными навыками. Углубленное изучение SQL Независимо от вашей роли в организации, как то администратор, про граммист, специалист по вопросам производительности, составитель отчетов или даже разработчик БД, для личного успеха и успеха вашего проекта нет более важного навыка, чем владение языком SQL в совер шенстве. Все так, но довольно сложно понять, владеете ли вы языком, который включает лишь горстку команд (я имею в виду категорию SQL выражений для работы с данными, включающую выражения se lect , insert, update и delete). Лучший способ определить, освоили ли вы SQL, – пройти курс углубленного изучения SQL или прочитать книгу для профессионалов. После этого большинство почувствует, что вопрос не настолько хорошо знаком, как это представлялось. Вот чем вам может помочь совершенное владение языком SQL: • Программисты, работающие с базами данных, в конечном счете от вечают за соответствующую реализацию спецификаций проекта и за общую производительность системы. Лучшие программисты могут создавать лаконичные и эффективные SQL выражения, пра вильно реализующие спецификации проекта, не приводя к пробле мам производительности. • Администрирование БД часто объединяет несколько навыков, вклю чая проектирование и реализацию БД, программирование БД и на стройку БД. Чтобы выполнять повседневные задачи администриро вания, администраторы должны в совершенстве владеть SQL выра жениями управления схемой данных (такими как create table и alter index ), а превосходное знание SQL выражений для работы с данны ми поможет находить наилучшие решения при проектировании, программировании и настройке. Дополнительные источники 291 • Специалисты по вопросам производительности, как правило, согла шаются, что большинство проблем производительности обусловле но плохо продуманными SQL выражениями, а не недостатком ре сурсов сервера. Однако при возникновении проблем с производи тельностью обычно проклинают сервер и затевают дорогостоящие и ненужные обновления оборудования. • Хорошо спроектированная БД должна быть довольно простой в на вигации, иначе те, кто ее реализует, будут делать ошибки. Чем больше вы знаете о том, как будут извлекаться данные из вашей БД, тем лучше будут решения, принимаемые вами на этапе проек тирования. При углубленном изучении SQL вы осмелитесь выйти за рамки базово го языка, задействовав специализированные интерфейсы и наборы ко манд. Например, ваша реализация SQL может позволить генерировать данные в формате XML (Extensible Markup Language – расширяемый язык разметки) прямо из запроса к БД или сохранять, проводить син таксический анализ и извлекать документы XML. Ваша реализация SQL может также включать специальную функциональность для хра нилищ данных и запросов к интеллектуальным ресурсам, такую как возможность ранжирования (например: «покажите мне 10 лучших продавцов прошлого года»). Кроме того, вам может понадобиться вза имодействие с объектно ориентированными языками программирова ния, включая применение специальных команд для хранения, извле чения и создания объектов и коллекций объектов. Ни одна из этих тем обычно не рассматривается в ознакомительных книгах по SQL. Следующие книги помогут перейти на следующий уровень в изучении SQL: Санжей Мишра (Sanjay Mishra) и Алан Бьюли (Alan Beaulieu) «Mas tering Oracle SQL», Second Edition, O’Reilly, 2004. 1 Поль Дюбуа (Paul DuBois) «MySQL Cookbook», O’Reilly, 2002. 2 Пол Нильсен (Paul Nielsen) «Microsoft SQL Server 2000 Bible», Wiley, 2002. Можно также пройти курс обучения в одном из следующих учебных центров: • Oracle University (http://education.oracle.com) • Learning Tree International (http://www.learningtree.com) • Microsoft Learning (learning) • MySQL Training (training) 1 Санжей Мишра, Алан Бьюли «Секреты Oracle SQL», перевод 1 го издания, Символ Плюс, 2003. 2 Поль Дюбуа «MySQL. Сборник рецептов», Символ Плюс, 2004. 292 Приложение D Программирование баз данных Если вы программист, желающий добавить к своему профессиональ ному багажу умение организовывать доступ к БД, изучение SQL – только один из кусочков этого пазла. Нужен еще язык программиро вания или API, позволяющий создавать сеансы с БД и взаимодейство вать с ней посредством команд SQL. Может, вы уже работаете с язы ком, обладающим этими возможностями, а может, вам понадобится дополнительный API или драйвер. В табл. D.1 показаны некоторые возможности, доступные в основных языках программирования. Таблица D.1. Возможности доступа к БД Все приведенные в табл. D.1 языки программирования являются язы ками общего назначения. И всем им для доступа к БД нужен дополни тельный драйвер или набор библиотек. Однако есть другие языки, раз работанные специально для работы с БД и включающие определенные команды SQL (как минимум select, update, insert, delete, start transac tion , commit и rollback). В табл. D.2 приведены некоторые из этих язы ков и описаны среды, в которых они работают. Язык програм мирования API Описание Java Java Database Connectivity (JDBC) Набор интерфейсов для взаимо действия с БД. Необходим JDBC драйвер (реализация интерфей сов JDBC) производителя вашей БД или стороннего производите ля C++ Oracle Call Interface (OCI) Набор библиотек С/С++ для со единения с базой данных Oracle и выполнения команд SQL MySQL++ Набор библиотек С++ для соеди нения с базой данных MySQL и выполнения команд SQL RogueWave SourcePro DB Набор библиотек С++ для соеди нения с базой данных MySQL, Oracle или SQL Server (в том чис ле) и выполнения команд SQL Perl DBI Модуль для доступа к MySQL, SQL Server, Oracle и несколь ким другим СУБД посредством единого интерфейса Visual C++ Visual C# Visual Basic Microsoft ActiveX ® Data Objects .NET (ADO.NET) Набор интерфейсов, предостав ляющих сервисы доступа к дан ным для платформы .NET Дополнительные источники 293 Таблица D.2. Специализированные языки программирования баз данных Последние три языка в табл. D.2 – это языки общего назначения, пред назначенные для создания бизнес приложений, а первые три, предо ставляемые тремя серверами БД, рассматриваемыми в данной книге, позволяют формировать модули следующих типов: Хранимые процедуры Именованные подпрограммы, принимающие параметры. Хранимые функции Именованные функции, принимающие параметры и возвращаю щие значение. Триггеры Модули, автоматически запускаемые сервером БД при определен ном событии, например удалении данных из той или иной таблицы. Триггеры выполняются только сервером БД, а хранимые процедуры и функции выполняются в рамках сеанса БД точно так же, как коман ды SQL. Поскольку хранимые функции возвращают значение, они мо гут вызываться из SQL выражений во всех случаях, где может исполь зоваться скалярный подзапрос. Для работы с Oracle Database, SQL Server или MySQL необходимо обязательно ознакомиться с одной из следующих книг: Мэйдин Фишер (Maydene Fisher) и др. «JDBC API Tutorial and Refe rence», Third Edition (Учебное пособие и справочник по JDBC API, 3 е издание), Addison Wesley, 2003. Билл Гамильтон (Bill Hamilton) «ADO.NET Cookbook» O’Reilly, 2003. 1 Язык программирования Разработчик Среда выполнения PL/SQL Oracle Oracle Database Oracle Application Server Transact SQL Microsoft SQL Server SQL2003 Stored Procedure Language MySQL MySQL Server (версии 5.0 и вы ше) PowerBuilder Sybase Sybase EAServer Клиенты для Windows и UNIX PowerHouse 4GL Cognos PowerHouse Application Server Progress 4GL Progress Software OpenEdge Database OpenEdge Application Server 1 Билл Гамильтон «ADO.NET. Сборник рецептов для профессионалов», СПб: Питер, 2004. 294 Приложение D Аллигатор Декарт (Alligator Descartes), Тим Банс (Tim Bunce) «Pro gramming the Perl DBI», O’Reilly, 2000. 1 Стивен Фейерштейн (Steven Feuerstein) и Билл Прибыл (Bill Pribyl) «Oracle PL/SQL Programming», Third Edition, O’Reilly, 2002. 2 Кен Хендерсон (Ken Henderson) «The Guru’s Guide to Transact SQL», Addison Wesley, 2000. 3 Также есть множество учебных курсов по программированию БД. Их можно найти в одном из следующих учебных центров: |