изучаем SQL. Она позволяет решать многошаговые задачи одним выражением
 Скачать 1.6 Mb.
|
|
Термин Определение 20 Глава 1. Немного истории Классы SQL выражений Язык SQL разбит на несколько отдельных частей. В данной книге будут рассмотрены: SQL выражения управления схемой данных (SQL schema statements), предназначенные для определения структур данных, хра нящихся в базе данных; SQL выражения для работы с данными (SQL data statements), предназначенные для работы со структурами данных, ранее определенными с помощью SQL выражений управления схемой; SQL выражения управления транзакциями, предназначенные для на чала, завершения и отката транзакций (рассматриваются в главе 12). Например, новая таблица базы данных создается с помощью SQL выра жения управления схемой create table (создать таблицу), а чтобы за полнить ее данными, потребуется SQL выражение для работы с данны ми insert (вставить). Чтобы дать представление об этих выражениях, приведем SQL выраже ние управления схемой, создающее таблицу corporation (корпорация): CREATE TABLE corporation (corp_id SMALLINT, name VARCHAR(30), CONSTRAINT pk_corporation PRIMARY KEY (corp_id) ); Это выражение создает таблицу с двумя столбцами, corp_id и name, где столбец corp_id определен как первичный ключ таблицы. Подробная информация о данном выражении, например доступные в MySQL ти пы данных, приводится в следующей главе. Теперь рассмотрим SQL выражение для работы с данными, которое вставляет в таблицу corpo ration запись для корпорации Acme Paper Corporation: INSERT INTO corporation (corp_id, name) VALUES (27, 'Acme Paper Corporation'); Это выражение добавляет в таблицу corporation строку со значением 27 в столбце corp_id и значением Acme Paper Corporation в столбце name. Наконец, приведем простое выражение select (выбрать) для извлече ния только что созданных данных: mysql< SELECT name > FROM corporation > WHERE corp_id = 27; + + | name | + + | Acme Paper Corporation | + + Все элементы БД, созданные посредством SQL выражений управления схемой, хранятся в специальном наборе таблиц, который называется словарем данных (data dictionary). Все эти «данные о базе данных» на Что такое SQL? 21 зывают метаданными (metadata). К таблицам словаря данных можно делать запросы с помощью оператора select, в точности как к создан ным вами таблицам. Таким образом, текущие структуры данных, раз вернутые в БД во время выполнения, становятся доступными. Напри мер, если требуется создать отчет о новых счетах, открытых за послед ний месяц, можно жестко закодировать известные на момент написа ния отчета имена столбцов таблицы account либо сделать запрос к словарю данных, получить текущий набор столбцов и динамически генерировать отчет при каждом выполнении. Данная книга посвящена главным образом той части языка для рабо ты с данными, к которой относятся команды select, update (обновить), insert и delete (удалить). SQL выражения управления схемой рассмот рены в главе 2, где создается БД, используемая в примерах данной книги. Вообще говоря, SQL выражения управления схемой не требуют особого внимания, за исключением их синтаксиса, тогда как у SQL вы ражений для работы с данными (хотя их и немного) есть масса нюан сов, нуждающихся в подробном изучении. Поэтому большинство глав данной книги посвящены SQL выражениям для работы с данными. SQL: непроцедурный язык Если в прошлом вам приходилось работать с языками программирова ния, вы привыкли к описанию переменных и структур данных, ис пользованию условной логики (if then else), циклическим конструк циям (do while … end) и разделению кода на небольшие многократно используемые части (объекты, функции, процедуры). Код передается компилятору, и результирующий исполняемый код делает в точности (ну, не всегда в точности) то, что вы запрограммировали. С каким бы языком программирования ни работали, Java, C#, C, Visual Basic или любым другим процедурным языком, вы полностью управляете дейст виями программы. С SQL, однако, понадобится отказаться от привыч ного контроля над выполнением, потому что SQL выражения опреде ляют необходимые входные и выходные данные, а способ выполнения выражения зависит от компонента механизма СУБД (database engine), называемого оптимизатором (optimizer). Работа оптимизатора заклю чается в том, чтобы рассмотреть SQL выражение и с учетом конфигура ции таблиц и доступных индексов принять решение о самом эффектив ном пути выполнения запроса (ну, не всегда самом эффективном). Большинство СУБД позволяют программисту влиять на решения опти мизатора с помощью подсказок оптимизатору (optimizer hints), на пример предложений по использованию конкретного индекса. Однако большинство пользователей SQL никогда не доберется до этого уровня сложности и будет оставлять подобные тонкости администраторам БД или специалистам по вопросам производительности. Следовательно, с SQL писать полные приложения не получится. Если требуется создать что то сложнее простого сценария для работы с оп 22 Глава 1. Немного истории ределенными данными, понадобится интегрировать SQL со своим лю бимым языком программирования. Некоторые производители баз данных сделали это за вас, например Oracle с языком PL/SQL или Mic rosoft с TransactSQL. Благодаря этим языкам SQL выражения для ра боты с данными являются частью грамматики языка программирова ния, что позволяет свободно интегрировать запросы к БД с процедур ными командами. Однако при использовании не характерного для БД языка, такого как Java, для выполнения SQL выражений понадобится специальное средство. Некоторые из этих программных средств пре доставляются производителями БД, тогда как другие создаются сто ронними производителями или разработчиками ПО с открытым ис ходным кодом. В табл. 1.2 показаны некоторые доступные варианты интегрирования SQL в конкретные языки программирования. Таблица 1.2. Средства интегрирования SQL Если требуется только интерактивное выполнение SQL команд, каж дый производитель БД обеспечивает как минимум простой инструмент передачи SQL команд механизму СУБД и просмотра результатов. Боль шинство производителей предлагает также графический инструмент, в одном окне которого вводятся SQL команды, а в другом выводятся ре зультаты их выполнения. Поскольку примеры данной книги работают с базой данных MySQL, для запуска примеров и форматирования ре зультатов я буду использовать утилиту командной строки mysql. Примеры SQL Ранее в этой главе я обещал показать SQL выражение, возвращающее все транзакции текущего счета Джорджа Блейка. Не будем тянуть, вот оно: SELECT t.txn_id, t.txn_type_cd, t.date, t.amount FROM customer c INNER JOIN account a ON c.cust_id = a.cust_id INNER JOIN product p ON p.product_cd = a.product_cd INNER JOIN transaction t ON t.account_id = a.account_id Язык программирования Программное средство Java JDBC (Java Database Connectivity) (JavaSoft) C++ RogueWave SourcePro DB (инструмент сторонних произ водителей для соединения с БД Oracle, SQL Server, MySQL, Informix, DB2, Sybase и PostgreSQL) C/C++ Pro*C (Oracle) MySQL C API (с открытым исходным кодом) DB2 Call Level Interface (IBM) C# ADO.NET (Microsoft) VisualBasic ADO.NET (Microsoft) Что такое SQL? 23 WHERE c.fname = 'George' and c.lname = 'Blake' AND p.name = 'checking'; Без лишних на этом этапе подробностей: данный запрос идентифици рует в таблице account строку Джорджа Блейка, а в таблице product – строку с типом счета 'checking' (текущие расходы), в таблице account находит строку, соответствующую данной комбинации «клиент/тип счета», и возвращает четыре столбца таблицы transaction для всех транзакций по этому счету. Все концепции, присутствующие в данном запросе (и многие другие), будут рассмотрены в следующих главах; здесь мне просто хотелось показать, как выглядел бы запрос. Предыдущий запрос содержит три разных блока (clauses): select, from и where. Практически каждый сформированный вами запрос будет включать, по крайней мере, эти три блока, хотя есть и другие блоки, применяемые для более сложных целей. Роль каждого из этих трех блоков можно продемонстрировать следующим образом: SELECT /* одна или более сущностей */ ... FROM /* одно или более мест */ ... WHERE /* удовлетворяется одно или более условий */ ... Большинство реализаций SQL воспринимают текст, располо женный между тегами /* и */, как комментарии. Обычно первая задача при создании запроса – определить, какая таб лица или таблицы понадобятся, а затем добавить их в блок from. Далее необходимо отсеять данные этих таблиц, которые не помогут ответить на запрос. Для этого в блок where вводятся условия. Наконец, прини мается решение о том, какие столбцы разных таблиц требуется из влечь, и они добавляются в блок select. Вот простой пример поиска всех клиентов по фамилии Smith (Смит): SELECT cust_id, fname FROM customer WHERE lname = 'Smith' Этот запрос выполняет поиск в таблице customer всех строк, столбец lname которых соответствует строке 'Smith', и возвращает столбцы cust_id и fname этих строк. Кроме создания запросов к БД вам, скорее всего, придется заполнять и изменять данные БД. Вот простой пример добавления новой строки в таблицу product: INSERT INTO product (product_cd, name) VALUES ('CD', 'Certificate of Depasit') Ой, кажется, в слове «Deposit» ошибка! Никаких проблем. Это можно исправить с помощью выражения update: UPDATE product 24 Глава 1. Немного истории SET name = 'Certificate of Deposit' WHERE product_cd = 'CD'; Обратите внимание, что в выражении update тоже есть блок where, как и в выражении select, потому что update должно отобрать строки, под лежащие изменению. В данном случае задано, что должны быть изме нены только те строки, столбцы product_cd которых соответствуют строке 'CD'. Поскольку столбец product_cd является первичным клю чом таблицы product, следует ожидать, что выражение update изменит только одну строку (или ни одной, если такого значения в таблице нет). При выполнении любого SQL выражения для работы с данными механизм СУБД выводит отчет с указанием того, сколько строк было подвержено его воздействию. Если используется интерактивный инст румент, например уже упомянутый инструмент командной строки mysql , будет получено сообщение о том, сколько строк было: • возвращено выражением select; • создано выражением insert; • изменено выражением update; • удалено выражением delete. Если используется процедурный язык с одним из уже упомянутых программных средств, то после выполнения SQL выражения для рабо ты с данными это средство включит вызов функции запроса этой ин формации. В общем, не мешает проверять эти данные, чтобы убедить ся, что выражение не сделало ничего непредвиденного (например, ес ли забыть включить в выражение delete блок where, будут удалены все строки таблицы!). Что такое MySQL? Реляционные базы данных продаются уже более двух десятилетий. К самым зрелым и популярным продуктам относятся: • Oracle Database от Oracle Corporation • SQL Server от Microsoft • DB2 Universal Database от IBM • Sybase Adaptive Server от Sybase • Informix Dynamic Server от IBM Все эти серверы БД делают примерно одно и то же, хотя некоторые лучше оснащены для работы с очень большими или высокопроизводи тельными БД. Другие лучше ведут себя при работе с объектами, или очень большими файлами, или XML документами и т. д. Кроме того, очень хорошо, что все эти серверы совместимы с последним стандар том ANSI SQL. Это положительный момент, и я обязательно покажу, как писать SQL выражения, которые будут выполняться на любой из этих платформ (с небольшими изменениями или вообще без них). Дополнительные источники 25 Наряду с этим последние пять лет в сообществе сторонников открыто го исходного кода наблюдалась активная деятельность по созданию жизнеспособной альтернативы коммерческим серверам БД. Два наи более распространенных сервера БД с открытым исходным кодом – PostgreSQL и MySQL. Веб сайт MySQL (http://www.mysql.com) в насто ящее время заявляет о более чем 6 000 000 установок, их сервер досту пен бесплатно, и я убедился, что скачать и установить его чрезвычай но просто. Поэтому я решил, что все примеры для данной книги будут выполняться на БД MySQL (версии 4.1.11). Для форматирования ре зультатов запросов будет использоваться инструмент командной стро ки mysql. Даже если вы уже работаете с другим сервером и вообще не планируете использовать MySQL, я рекомендую установить послед нюю версию сервера MySQL, загрузить схему и данные примера и экс периментировать с примерами этой книги. Однако помните, что: Эта книга не о реализации SQL в MySQL. Скорее, данная книга создана, чтобы обучить читателя создавать SQL выражения, которые будут выполняться на MySQL и последних верси ях Oracle Database, Sybase Adaptive Server и SQL Server с небольшими изменениями или вообще без них. Возможно, при использовании од ного из упомянутых серверов IBM хлопот у вас будет чуть больше. Чтобы по возможности сохранить код из данной книги платформонеза висимым, я воздержусь от демонстрации некоторых интересных ве щей, реализованных в языке SQL для MySQL и не осуществимых в дру гих реализациях БД. Но для читателей, планирующих продолжать ра боту с MySQL, некоторые из этих возможностей рассмотрены в прило жении В. Дополнительные источники Общая цель следующих четырех глав – представить SQL выражения для работы с данными, уделив при этом особое внимание трем основ ным блокам выражения select. Кроме того, приводится множество примеров, использующих банковскую схему (она представлена в сле дующей главе и задействована во всех примерах данной книги). Наде юсь, что постоянное использование одной и той же БД позволит чита телю вникать в суть примера, не тратя время на изучение применяе мых таблиц. Твердо усвоив основы, с помощью оставшихся глав вы изучите допол нительные концепции, по большей части не зависимые друг от друга. Поэтому, столкнувшись с какими либо трудностями, всегда можно двинуться дальше, а позже перечитать главу. Прочитав книгу и прора ботав все примеры, вы уверенно пойдете к вершинам мастерства SQL. 26 Глава 1. Немного истории Вот несколько заслуживающих внимания источников для читателей, желающих узнать больше о реляционных БД, истории компьютеризи рованных систем управления БД или языке SQL: • К. Дж. Дейт (C. J. Date) «Database in Depth: Relational Theory for Practitioners», O’Reilly. • К. Дж. Дейт «An Introduction to Database Systems, Eighth Edition», Addison Wesley. 1 • К. Дж. Дейт «The Database Relational Model: A Retrospective Revi ew and Analysis: A Historical Account and Assessment of E. F. Codd’s Contribution to the Field of Database Technology», Addison Wesley. • http://en.wikipedia.org/wiki/Database_management_system • http://www.mcjones.org/System_R/ 1 К. Дейт «Введение в системы баз данных», 8 е издание, Вильямс, 2005. Создание и заполнение базы данных В этой главе представлена информация, необходимая для создания ва шей первой БД, таблиц и ассоциированных данных, используемых в примерах книги. Также рассказывается о различных типах данных и об их применении при создании таблиц. Поскольку примеры книги выполняются на СУБД MySQL, здесь наблюдается небольшое смеще ние акцентов представляемого материала в сторону возможностей и синтаксиса MySQL, но большинство концепций применимы к любо му серверу. Создание базы данных MySQL Если в вашем распоряжении уже есть СУБД MySQL, можно выпол нять приведенные ниже инструкции, начиная с п. 8. Но не забывайте, что эта книга ориентирована на MySQL версии 4.1.11 или более позд них, поэтому если вы используете более раннюю версию, скорее всего, не помешает обновить ее или установить другой сервер. Следующие инструкции отражают минимальный набор действий, не обходимых для установки сервера MySQL на компьютере, работаю щем под управлением Windows, создания базы данных и загрузки тес товых данных для этой книги: 1. Скачайте MySQL Database Server (версии 4.1.11 или более поздней) с http://dev.mysql.com. Если сервер планируется использовать только для обучения, скачайте Essentials Package (Основной пакет), вклю чающий только широко используемые инструменты, а не Complete Package (Полный пакет). 2. Двойным щелчком по загруженному файлу запустите процесс уста новки. 28 Глава 2. Создание и заполнение базы данных 3. Установите сервер, используя вариант «typical install» (обычная ус тановка). Установка должна пройти быстро и безболезненно, но не стесняйтесь обращаться к онлайновому руководству по установке (http://dev.mysql.com/doc/mysql/en/Installing.html). 4. По завершении установки, перед тем как нажать кнопку заверше ния, убедитесь, что флажок Configure the MySQL Server now (Конфигу рировать сервер MySQL сейчас) установлен. Это нужно, чтобы за пустился Configuration Wizard (Мастер конфигурации). 5. При запуске Configuration Wizard выберите переключатель Standard Configuration (Стандартная конфигурация) и затем установите флаж ки Install as Windows Service (Установить как службу Windows) и Inclu de Bin Directory in Windows Path (Включить каталог Bin в путь поиска Windows). 6. Во время конфигурирования вам будет предложено выбрать пароль для привилегированного пользователя root. Не забудьте записать пароль, он понадобится позже. 7. Откройте консоль (с помощью Start → Run → Command (Пуск → Выпол нить → Command)) и из консоли зарегистрируйтесь как привилегиро ванный пользователь с помощью команды mysql u root p. Вам будет предложено ввести пароль, после этого появится подсказка mysql>. 8. Создайте нового пользователя базы данных. Я создал пользователя lrngsql с помощью команды grant all privileges on *.* to 'lrng sql'@'localhost' identified by 'xxxxx'; (замените xxxxx паролем, кото рый выбрали для этого пользователя). 9. Завершите сеанс с помощью команды quit; (выйти) и зарегистри руйтесь из консоли как новый пользователь посредством команды mysql u lrngsql p. 10. Создайте базу данных. Я создал БД «bank» (банк) с помощью выра жения create database bank;. 11. Выберите новую БД с помощью выражения use bank;. 12. Скачайте тестовые данные для этой книги. Файл можно найти на сайте learningsql в разделе Examples (примеры) для данной книги. 13. Из инструмента командной строки mysql с помощью команды source (источник) загрузите данные из закачанного файла, напри мер source c:\tmp\learning_sql.sql. Вместо пути c:\tmp\ укажите ка талог, в котором находится сценарий с тестовыми данными. Теперь у вас должна быть рабочая БД, заполненная всеми данными, необходимыми для примеров данной книги. |