изучаем SQL. Она позволяет решать многошаговые задачи одним выражением
 Скачать 1.6 Mb.
|
|
• Приложение C «Решения к упражнениям» содержит решения уп ражнений, приводимых в главах. • Приложение D «Дополнительные источники» подсказывает, куда можно обратиться, чтобы получить более глубокие навыки. Условные обозначения, используемые в книге В книге используются следующие типографские обозначения: Курсив Используется для имен файлов, имен каталогов и URL адресов. Также используется для выделения и при первом упоминании тех нического термина. Предисловие 11 Моноширинный шрифт Используется для примеров кода и обозначения ключевых слов SQL в тексте. Моноширинный курсив Используется для обозначения пользовательских терминов. ВЕРХНИЙ РЕГИСТР Используется для обозначения ключевых слов SQL в примерах кода. Моноширинный полужирный шрифт Выделяет ввод пользователя в примерах с интерактивным взаимо действием. Также выделяет элементы кода, на которые следует об ратить особое внимание. Так выделяются советы, рекомендации или общие примечания. Например, с помощью примечаний я обращаю ваше внимание на полезные новые возможности Oracle9i. Обозначает предупреждение или предостережение. Так я преду преждаю, например, о том, что неаккуратное применение неко его блока SQL может иметь неожиданные последствия. Контакты Пожалуйста, присылайте комментарии и вопросы по данной книге издателю: O’Reilly Media, Inc. 1005 Gravenstein Highway North Sebastopol, CA 95472 (800) 998 9938 (в Соединенных Штатах или Канаде) (707) 829 0515 (международный или местный) (707) 829 0104 (факс) Для этой книги издательство O’Reilly поддерживает веб страницу, на которой приведены список опечаток, примеры и вся дополнительная информация. Эту страницу можно найти по адресу: http://www.oreilly.com/catalog/learningsql Чтобы прокомментировать или задать технические вопросы по этой книге, присылайте электронные сообщения по адресу: bookquestions@oreilly.com Более подробная информация о книгах издательства O’Reilly, конфе ренциях, центрах ресурсов и портале O’Reilly Network представлена на веб сайте: http://www.oreilly.com 12 Предисловие Использование примеров кода Цель этой книги – помочь вам выполнить работу. Код, представлен ный в книге, в общем случае можно использовать в программах и доку ментации. На воспроизведение небольших фрагментов кода в вашей программе разрешение не требуется. Для продажи или распростране ния CD ROM с примерами из книг O’Reilly разрешение необходимо. Ес ли, отвечая на вопросы, вы ссылаетесь на книгу и цитируете пример кода, разрешение не требуется. Для включения существенного объема кода примеров из этой книги в документацию собственного продукта разрешение необходимо. Мы признательны за указание авторства, но не требуем этого. Обычно указание источника включает название, автора, издателя и ISBN. На пример: «Learning SQL by Alan Beaulieu. Copyright 2005 O’Reilly Me dia, Inc., 0 596 00727 2.» Если вы сомневаетесь в корректности использования вами примеров кода, обратитесь за разъяснениями по адресу permissions@oreilly.com. Safari Enabled Если на обложке книги есть пиктограмма «Safari ® Enabled», это означает, что книга доступна в Сети через O’Reilly Net work Safari Bookshelf. Safari предлагает намного лучшее решение, чем электронные книги. Это виртуальная библиотека, позволяющая без труда находить тысячи лучших технических книг, вырезать и вставлять примеры кода, за гружать главы и находить быстрые ответы, когда требуется наиболее верная и свежая информация. Она свободно доступна по адресу http:// safari.oreilly.com Благодарности Книга – живой организм, и то, что сейчас перед вами, далеко от моих первоначальных набросков. Эта метаморфоза произошла во многом благодаря моему редактору Джонатану Геннику (Jonathan Gennick). Спасибо тебе за помощь на каждом этапе проекта – как за твою редак торскую доблесть, так и за экспертную поддержку в вопросах, связан ных с языком SQL. Еще я хотел бы поблагодарить трех моих техниче ских рецензентов: Питера Гулутзана (Peter Gulutzan), Джозефа Моли наро (Joseph Molinaro) и Джеффа Кокса (Jeff Cox), побудивших меня сделать эту книгу и технически насыщенной, и подходящей для чита телей, не знакомых с SQL. Также огромная благодарность многим со трудникам O’Reilly Media, помогавшим воплотить эту книгу в реаль ность, в том числе корректору Мэтт Хатчинсон (Matt Hutchinson), ди зайнеру обложки Элли Волкхаузен (Ellie Volckhausen) и художнику оформителю Робу Романо (Rob Romano). Немного истории Прежде чем засучить рукава и приступить к работе, полезно предста вить некоторые базовые концепции и заглянуть в историю компьюте ризированного хранения и извлечения данных. Введение в базы данных База данных – это всего лишь набор взаимосвязанных данных. На пример, телефонный справочник – это база данных имен, телефонных номеров и адресов всех людей, проживающих в определенной местно сти. Будучи, несомненно, широко и часто используемой базой данных, телефонный справочник не лишен следующих недостатков: • Поиск конкретного телефонного номера занимает много времени, особенно если в телефонном справочнике очень много записей. • Телефонный справочник проиндексирован только по именам/фа милиям, поэтому для поиска абонента по определенному адресу та кая база данных практически не используется, хотя теоретически это и возможно. • С момента публикации телефонного справочника адекватность пред ставленной в нем информации постепенно снижается, поскольку лю ди уезжают и приезжают из этой местности, меняют свои телефон ные номера или переезжают по другому адресу в той же местности. Недостатками, присущими телефонным справочникам, обладает любая другая некомпьютеризированная система хранения данных, напри мер регистратура поликлиники, хранящая медицинские карты паци ентов. Из за громоздкости бумажных баз данных одними из первых компьютерных приложений были разработаны системы баз данных (database systems) – средства компьютеризированного хранения и из влечения данных. Поскольку система БД хранит информацию в элек 14 Глава 1. Немного истории тронном виде, а не на бумаге, она может быстрее извлекать данные, индексировать их разными способами и поставлять своим пользовате лям самую свежую информацию. В первых системах БД информация хранилась на магнитных лентах. Количество лент во много раз превышало количество устройств считы вания, поэтому техническим специалистам приходилось постоянно менять ленты, чтобы предоставить ту или иную запрашиваемую ин формацию. Поскольку объем памяти у компьютеров той эпохи был не велик, при многократных запросах одних и тех же данных обычно требовалось многократное считывание этих данных с магнитной лен ты. Хотя эти системы БД и были существенным усовершенствованием по сравнению с бумажными БД, им было еще далеко до возможностей современных технологий. (Современные системы БД могут работать с терабайтами данных, распределенными по многим дискам с быст рым доступом, и держать в быстродействующей памяти десятки гига байт этих данных; впрочем, я немного забегаю вперед.) Нереляционные системы баз данных Первые несколько десятилетий данные в компьютеризированных сис темах БД хранились и представлялись по разному. Например, в иерар хической системе баз данных (hierarchical database system) данные были представлены в виде одной или нескольких древовидных струк тур. На рис. 1.1 показано, как с помощью древовидных структур мож но организовать данные банковских счетов Джорджа Блейка (George Blake) и Сью Смит (Sue Smith). Денежный рынок Текущие расходы Текущие расходы Клиенты Счета Транзакции Сбережения Кредитный лимит Джордж Блейк Сью Смит Кредит $25.00 на 2004 02 05 Дебит $100.00 на 2004 01 22 Дебит $250.00 на 2004 03 09 Дебит $1000.00 на 2004 03 25 Дебит $500.00 на 2004 03 27 Кредит $138.50 на 2004 04 02 Кредит $77.86 на 2004 04 04 Рис. 1.1. Иерархическое представление информации по счетам Введение в базы данных 15 И у Джорджа, и у Сью есть собственное дерево, включающее их счета и транзакции, производимые по этим счетам. Иерархическая система базы данных предоставляет средства для нахождения дерева конкрет ного клиента и последующего обхода этого дерева в поисках нужных счетов и/или транзакций. У каждого узла дерева может быть ни одно го или один родитель и ни одного, один или много дочерних узлов. Та кую конфигурацию называют иерархией с одним родителем (single parent hierarchy ). Другой распространенный подход, называемый сетевой базой данных (network database system), представляет собой наборы записей и набо ры связей (links), определяющих отношения (relationships) между раз ными записями. На рис. 1.2 показано, как выглядели бы те же счета Джорджа и Сью в такой системе. Чтобы найти транзакции, производимые по депозитному счету денеж ного рынка Сью, понадобилось бы сделать следующее: 1. Найти клиентскую запись Сью Смит. 2. Перейти по связи от клиентской записи Сью Смит к списку ее счетов. 3. Просматривать цепочку счетов до тех пор, пока не будет найден счет денежного рынка. Клиенты Счета Транзакции Типы счетов Денежный рынок Текущие расходы Сбережения Кредитный лимит Джордж Блейк Сью Смит Кредит $25.00 на 2004 02 05 Дебит $100.00 на 2004 01 22 Дебит $250.00 на 2004 03 09 Дебит $1000.00 на 2004 03 25 Дебит $500.00 на 2004 03 27 Кредит $138.50 на 2004 04 02 Кредит $77.86 на 2004 04 04 Текущие расходы Кредитный лимит Денежный рынок Сбережения Текущие расходы Рис. 1.2. Сетевое представление информации по счетам 16 Глава 1. Немного истории 4. Перейти по связи от записи денежного рынка к списку его транзак ций. Одну интересную особенность сетевых баз данных демонстрирует на бор записей product (тип счета), на рис. 1.2 крайний справа. Обратите внимание, что каждая запись product (Checking (текущие расходы), Sa vings (сбережения) и т. д.) указывает на список записей account (счет), соответствующих этому типу счета. Поэтому доступ к записям account может быть осуществлен из нескольких мест (и через записи customer, и через записи product), что делает сетевую базу данных иерархией с не сколькими родителями (multiparent hierarchy). И иерархические, и сетевые системы баз данных ныне живы и здоро вы, хотя преимущественно в мире мэйнфреймов. Кроме того, иерархи ческие системы БД возродились в службах каталогов, таких как Active Directory компании Microsoft и Directory Server компании Netscape, а также с появлением XML (Extensible Markup Language, расширяе мый язык разметки). Однако начиная с 1970 х годов все большую по пулярность приобретает новый способ представления данных, более строгий, но при этом более понятный и удобный. Реляционная модель В 1970 году сотрудник исследовательской лаборатории IBM доктор Е. Ф. Кодд (E. F. Codd) опубликовал статью под названием «A Relation al Model of Data for Large Shared Data Banks» (Реляционная модель дан ных для больших банков данных коллективного пользования), в кото рой предложил представлять данные как наборы таблиц. Вместо ука зателей для навигации по взаимосвязанным сущностям используются избыточные данные, связывающие записи разных таблиц. На рис. 1.3 представлена информация счетов Джорджа и Сью в таком контексте. На рис. 1.3 есть четыре таблицы, представляющие четыре обсуждае мые сущности: customer, product, account и transaction (транзакция). По смотрев на таблицу customer, можно увидеть три столбца: cust_id (идентификационный номер клиента), fname (имя клиента) и lname (фа милия клиента). Ниже в таблице customer видим две строки: первая со держит данные Джорджа Блейка, вторая – данные Сью Смит. Макси мально возможное количество столбцов в таблице отличается для раз ных серверов, но обычно это достаточно большое число, и с ним нет проблем (Microsoft SQL Server, например, допускает до 1024 столбцов в таблице). Число строк в таблице – это больше вопрос физических возможностей (т. е. определяется доступным дисковым пространст вом), чем ограничений серверов БД. Каждая таблица реляционной базы данных включает информацию, уникально идентифицирующую строку этой таблицы (первичный ключ (primary key)), а также дополнительные данные, необходимые для пол ного описания сущности. Возвращаясь к таблице customer: в столбце cust_id каждому клиенту соответствует определенный номер. Напри Введение в базы данных 17 мер, Джорджа Блейка можно уникально идентифицировать с помо щью клиентского идентификатора (ID №). Никогда никакому другому клиенту не будет присвоен такой же идентификатор, и этой информа ции достаточно, чтобы обнаружить данные Джорджа Блейка в таблице customer . Хотя в качестве первичного ключа можно было бы выбрать со четание столбцов fname и lname (первичный ключ, состоящий из двух и более столбцов, называют составным ключом (compound key)), у двух и более человек, имеющих счета в банке, могут быть одинаковые имена и фамилии. Поэтому специально для первичных ключей в таблицу cus tomer был включен столбец cust_id. Некоторые из таблиц также содержат информацию, используемую для навигации к другой таблице. Например, в таблице account есть столбец cust_id , содержащий уникальный идентификатор клиента, открывше го счет, и столбец product_cd, содержащий уникальный идентификатор типа счета, которому будет соответствовать счет. Эти столбцы называют внешними ключами (foreign keys). Они служат той же цели, что и ли нии, соединяющие сущности в иерархической и сетевой версиях пред 2004 01 22 $100.00 103 DBT 978 date amount account_id txn_type_cd txn_id 2004 02 05 $25.00 103 CDT 979 2004 03 09 $250.00 104 DBT 980 2004 03 25 $1000.00 105 DBT 981 2004 04 02 $138.50 105 CDT 982 2004 04 04 $77.86 105 CDT 983 2004 03 27 $500.00 106 DBT 984 $75.00 1 CHK 103 balance cust_id $250.00 1 SA V 104 $783.64 2 CHK 105 $500.00 2 MM 106 0 2 LO C 107 Блейк Джордж 1 lname fname cust_id Смит Сью 2 Текущие расходы CHK name product_cd Сбережения SAV Денежный рынок MM Кредитный лимит LOC Транзакция account_id Счет Клиент Тип счета product_cd Рис. 1.3. Реляционное представление информации по счетам 18 Глава 1. Немного истории ставления информации по счетам. Однако, в отличие от жесткой струк туры иерархической/сетевой моделей, реляционные таблицы можно использовать по разному (даже так, как разработчики этой базы дан ных и не представляли себе). Может показаться излишним хранить одни и те же данные в несколь ких местах, но реляционная модель использует избыточность данных очень четко. Например, если таблица account включает столбец для уникального идентификатора клиента, открывшего счет, это правиль но, а если включены также его имя и фамилия, то это неправильно. На пример, если клиент изменяет имя, нужна уверенность, что его имя хранится только в одном месте базы данных. В противном случае дан ные могут быть изменены в одном месте, но не изменены в другом, что приведет к их недостоверности. Правильное решение – хранить эту ин формацию в таблице customer. В другие таблицы следует включить только cust_id. Также неправильно располагать в одном столбце не сколько элементов данных, например в столбец name помещать имя и фамилию человека или в столбце address указывать улицу, город, страну и почтовый индекс. Процесс улучшения структуры базы дан ных с целью обеспечения хранения всех независимых элементов дан ных только в одном месте (за исключением внешних ключей) называ ют нормализацией (normalization). Вернемся к четырем таблицам на рис. 1.3; на первый взгляд может быть непонятно, как использовать их для поиска транзакций Джорджа Блейка по его текущему счету. Во первых, находим уникальный иден тификатор Джорджа Блейка в таблице customer. Затем строку в таблице account , столбец cust_id которой содержит уникальный идентификатор Джорджа, а столбец product_cd соответствует строке таблицы product, столбец name которой содержит значение "Checking". Наконец, в таблице transaction находим строки, столбец account_id которых соответствует уникальному идентификатору из таблицы account. Возможно, все это кажется сложным, но с помощью языка SQL может быть осуществлено одной единственной командой, как вы вскоре увидите. Немного терминологии В предыдущих разделах были введены некоторые новые термины, по этому приведем кое какие формальные определения. В табл. 1.1 при ведены термины, используемые в данной книге, и их определения. Таблица 1.1. Термины и определения Термин Определение Сущность (entity) То, что представляет интерес для пользователей ба зы данных, например клиенты, запчасти, географи ческое положение и т. д. Столбец (column) Отдельный элемент данных, хранящийся в таблице. Что такое SQL? 19 Что такое SQL? Помимо определения реляционной модели Кодд предложил язык для работы с данными в реляционных таблицах, названный DSL/Alpha. Вскоре после публикации статьи Кодда в IBM была организована груп па для создания прототипа языка на базе его идей. Эта группа разрабо тала упрощенную версию DSL/Alpha, которую назвали SQUARE. В ре зультате усовершенствования SQUARE появился язык SEQUEL, кото рый в конце концов получил имя SQL. Сейчас SQL разменял четвертый десяток, претерпев за свой век мно жество изменений. В середине 1980 х Национальный институт стан дартизации США (American National Standards Institute, ANSI) начал разрабатывать первый стандарт языка SQL, который был опубликован в 1986 г. Дальнейшие доработки были отражены в следующих верси ях стандарта SQL (1989, 1992, 1999 и 2003 гг.). Наряду с усовершенст вованием базового языка в SQL появились и новые возможности для обеспечения объектно ориентированной функциональности. SQL идет рука об руку с реляционной моделью, потому что результатом SQL запроса является таблица (в данном контексте также называемая результирующим набором ). Таким образом, в реляционной базе данных можно создать новую постоянную таблицу, просто сохранив результи рующий набор запроса. Аналогично в качестве входных данных запрос может использовать как постоянные таблицы, так и результирующие наборы других запросов (подробно это будет рассмотрено в главе 9). И последнее замечание: SQL не акроним (хотя многие настаивают, что это сокращение от Structured Query Language (Структурированный язык запросов)). Название этого языка произносится по буквам (т. е. «S», «Q», «L») или как «sequel» (сиквел). Строка (row) Набор столбцов, которые вместе полностью описыва ют сущность или некоторое действие, производимое над сущностью. Также называется записью (record). Таблица (table) Набор строк, хранящийся в памяти (непостоянная таблица) или на постоянном запоминающем устрой стве (постоянная таблица). Результирующий набор (result set) Другое название непостоянной таблицы, обычно яв ляющейся результатом SQL запроса. Первичный ключ (primary key) Один или более столбцов, которые можно использо вать как уникальный идентификатор для каждой строки таблицы. Внешний ключ (foreign key) Один или более столбцов, которые можно совместно использовать для идентификации одной строки дру гой таблицы. |