Язык SQL

федеральное агентство по образованию

государственное образовательное учреждение высшего профессионального образования

Российский государственный гуманитарный университет

институт ИНФОРМАЦИОННЫХ  НАУК И ТЕХНОЛОГИЙ БЕЗОПАСНОСТИ

Математическая логика

Язык SQL

Москва 2007

Содержание

Язык запросов SQL                                                                                                                       3         

Основные инструменты                                                                                                               9

Оператор SELECT                                                                                                                         11

Использование имён и вложенных запросов                                                                             12

Объединения и внешние соединения                                                                                         13

Математические функции и средства работы с датами                                                            15       

Группы и агрегатные функции                                                                                                   17

Оператор CASE                                                                                                                             18

Создание таблиц и манипуляции с данными                                                                            20

Язык запросов SQL

Первыми попытками уйти от пост­роения баз данных (БД) на основе физической структуры их разме­щения на носителях являлись индексные файлы. Они обеспечивали доступ к запи­санной в них информации посредством индексных ключей, то есть для поиска не­ких конкретных записей в файле исполь­зовалась совокупность указателей. К недо­статкам такого подхода можно отнести, в частности, неоптимальное хранение ин­формации (дублирование, недостаточное структурирование) и значительное время поиска в больших файлах, не говоря уже о существенных требованиях, предъявляемых к аппаратному обеспечению.

Одним из решений упомянутых проблем стали иерархические БД. В таких базах элементы строго упорядочены, причем так, что данные одного уровня подчиняются (является подмножеством) данным друго­го, вышестоящего уровня. В такой модели связи могут быть отражены в виде дерева, причем допускаются только односторон­ние — от старших уровней к младшим. Подобная структура все еще напоминает древовидную файловую систему, где ди­ректории являются верхними уровнями, а файлы — нижними. Несмотря на то что по сравнению с индексными файлами это был существенный шаг вперед, иерархические БД наследовали многочисленные недостатки предыдущих систем, заключав­шиеся в сложности алгоритмов доступа к данным нижних уровней и повышенных аппаратных требованиях.

Иерархические БД не получили широкого распространения, уступив место новой кон­цепции хранения данных (реляционные БД). Она заключалась в использовании таб­личного метода хранения и доступа к кон­кретным записям, который используются и в настоящее время.

Прогресс в области сетевых технологий поспособствовал возникновению проблем организации доступа к данным, располо­женным на серверах, с удаленных компью­теров — участников сети. Для оптимизации этого процесса и снижения сетевого трафи­ка Международной организацией по стан­дартизации (ISO) был разработан и внедрен структурированный язык запросов SQL.

С развитием Глобальной сети и ростом объемов обращающейся в ней информации задачи предоставления доступа к последней практически повсеместно стали решаться с помощью технологий БД в общем и SQL в ча­стности. В настоящее время рядовой пользо­ватель, сам того не зная, активно работает с базами данных, даже просто просматривая веб-странички и форумы, или пользуясь сер­висами электронной почты и ICQ.

            Несмотря на то что иерархические БД еще не канули в лету окончательно, они, как упоми­налось выше, используются крайне редко — в основном при решении неких специализи­рованных задач, вследствие чего не имеет смысла подробно останавливаться на их уст­ройстве. Гораздо интереснее рассмотреть реляционные БД.

Итак, данные в таких базах размещены во взаимосвязанных таблицах, строки которых называются записями, а столбцы — полями. При этом данные в ячейках одного поля долж­ны

быть одинакового типа. В каждой таблице, как правило, имеются специальные поля, кото­рые позволяют однозначно идентифицировать ту или иную запись — они называются пер­вичными ключами или первичными индекса­ми. Такие поля помогают отличать одну запись от другой, даже если все остальные поля не­скольких таких записей абсолютно идентичны. Например, представьте, что вы разрабатываете справочник сотрудников своей организации, и при этом каждая запись хранит данные об од­ном сотруднике, а их выборка осуществляется по полю «Фамилия». Может оказаться так, что в организации работает несколько человек с одинаковой фамилией. Чтобы отличить эти за­писи друг от друга, применяются первичные индексные поля. Чаще всего за тип данных первичного ключа берется целочисленное зна­чение счетчика — в таком случае при добав­лении новой записи в таблицу значения этого поля заполняются автоматически. Однако не запрещается использовать в качестве первич­ного ключа поле, имеющее, к примеру, сим­вольный тип данных, хотя подобные ситуации возникают крайне редко.

Помимо задачи идентификации записей первичные индексы также часто используют­ся для связывания между собой данных из разных таблиц. Кроме первичных индексов существуют и вторичные индексы, которые обеспечивают механизм быстрого поиска и доступа к данным таблицы. Чтобы получить ответ на запрос к таблице, не имеющей ин­дексного поля, SQL-серверу придется скани­ровать полностью всю таблицу, считывая строки целиком. Очевидно, что такой подход при больших объемах информации слишком расточителен с точки зрения затрат аппарат­ных ресурсов. Тем не менее необходимо по­мнить, что с увеличением количества индек­сов растет и объем базы данных.

По признаку метода доступа БД делятся на локальные, сетевые и распределенные.

К локальным базам доступ возможен только с того компьютера, на котором они расположены. Сетевые базы призваны обеспечить работу с данными с других компьюте­ров посредством локальной сети или Интернета. Распределенные БД — это, по сути, подвид сетевых баз с той лишь разни­цей, что различные части информации на­ходятся на множестве разных компьютеров.

На сегодняшний день основным видом является второй тип БД — сетевые базы. Именно они применяются в Интернете для организации доступа пользователей к ин­формации сайтов, форумов, гостевых книг и каталогов товаров.

Такие БД делятся на файл-серверные и клиент-серверные. В файл-серверной мо­дели при подключении СУБД клиента к удаленной базе все данные скачиваются на локальный компьютер, а после их обработ­ки или изменения снова закачиваются на сервер для обновления в полном составе. Таким образом, происходит весьма интен­сивный информационный обмен с серве­ром, что чрезвычайно нагружает сеть. Кро­ме того, в такой модели весьма сложно организовать одновременную работу не­скольких пользователей, поэтому в настоя­щее время она используется редко и толь­ко для простых баз. Клиент-серверная модель организована совершенно иначе. Система управления такой базой состоит из двух частей — клиента и сервера. Кли­ентская часть программы посылает запро­сы с помощью языка запросов SQL,

сервер­ная часть обрабатывает их и отправляет обратно только те данные, которые были нужны пользователю. Информация об из­менении этих данных возвращается на сер-

вер, который обрабатывает ее и фиксирует в общей базе. Нагрузка на сеть при такой организации работы минимальна, а органи­зацию одновременной работы нескольких пользователей берет на себя сервер.

Целью любой СУБД являться предоставление пользователю простых механизмов доступа и манипулирования данными. Существует много различных методов ее достижения, одним из которых является язык SQL. Расши­фрованная и переведенная на русский язык эта аббревиатура будет выглядеть как Струк­турированный Язык Запросов.

Стандарт языка SQL. определяется Аме­риканским национальным институтом стан­дартов (ANSI) и Международной органи­зацией по стандартизации (ISO). Однако некоторые производители БД вносят в язык свои изменения и дополнения. Например, компания Огас1е создала язык PL/SQL, кото­рый является процедурным расширением оригинального SQL.

Однако и стандартный SQL может исполь­зоваться в двух различных вариантах — интерактивном и вложенном. Первый пред­ставляется собой отдельный программный модуль на SQL, который сам выполняет за­просы и отображает результаты работы. Второй — это внедрение элементов SQL в другой язык высокого уровня, например С или Delphi. В этом случае основная про­грамма самостоятельно формирует запросы для сервера SQL, а потом использует ре­зультаты выборок из базы, не предъявляя их пользователю в чистом виде.

В целом же SQL — это язык, ориентиро­ванный на работу с реляционными базами    данных. Его использование позволяет на по­рядок сократить объем работ, который пона­добился бы в случае создания приложений, использующих БД на универсальном языке программирования, например на том же С.

Действительно, чтобы сформировать ре­ляционную базу данных на С, нужно было бы описать как минимум один объект (дву­мерный массив), называемый в SQL. табли­цей, который должен иметь возможность менять размер для вмещения любого необ­ходимого числа строк. Затем пришлось бы создавать процедуры для помещения зна­чений в такую таблицу, а также поиска и извлечения этих значений. Это непросто даже на первый взгляд. Так, если бы вы захотели найти все строки в таблице «TAB», в которых значения некоего поля «num» равно 5, то необходимо было бы выполнить по шагам весьма сложную про­цедуру.

Грамотный листинг программы, реализую­щий такой алгоритм на С и соблюдающий все подобающие проверки займет, пожалуй, всю эту страницу. В случае же использования SQL достаточно написать вот такой запрос:

SELECT *

FROM tab

WHERE num = 5

Дело в том, что команды SQL могут ра­ботать со всеми записями таблицы как с единым объектом. Необходимость органи­зации циклов и проверок значений вруч­ную отпадает.

Упомянутый в примере оператор SELECT является основным и наиболее часто ис­пользуемым для выборки данных из таб­лиц. Все обращения к таблицам происхо­дят в виде запросов, которые состоят из таких же операторов, — меняются только условия поиска.

В настоящее время трудно найти язык программирования высокого уровня, кото­рый бы не поддерживал работу с SQL. Для этих целей разработчиками различных компиляторов предусмотрены специаль­ные библиотеки компонентов, которые обеспечивают связь между приложения­ми, написанными на их языке, и сервера­ми SQL, в результате чего данные, которые будут выбираться из базы, можно исполь­зовать в клиентской программе обработки по своему усмотрению.

Сразу предупредим, что SQL Server 2000 возможно установить только на сервер­ные версии операционной системы Windows — 2000 Server и 2003 Server. Попытки инсталляции ее на пользовательские версии ОС, такие как Windows ХР Professional, если только речь не идет о клиентской части, не увенчаются успехом. Если же вы непременно хотите пользо­ваться языком запросов, работая под пользовательской ОС, существует MSDE — это урезанная версия SQL Server, которая входит в дистрибутив МS Office 2003.

После запуска инсталлятора с компакт-диска в двух первых окнах мастера уста­новки необходимо последовательно вы­брать пункты «Components» и «Install Database Server». Результатом этих дейст­вий станет появление окна с предложением указать расположение будущего сервера. Здесь возможны следующие варианты: «Local Computer» — локальный компьютер, «Remote computer» — удаленный компью­тер в вашей сети, «Virtual Server» — вирту­альный сервер сети. Этот выбор зависит только от конкретной задачи, которую вы решаете в данный момент.

Если вы выбрали компьютер, где до этого уже существовала некая версия SQL Server, то в следующем окне будет доступен пункт «Upgrade, remove or add components to an existing instance of SQL Server» — добавле­ние, изменение и удаление существующих компонентов сервера. Иначе здесь можно будет выбрать лишь «Create a new instance of SQL Server or install Client Tolls» — со­здать новый экземпляр SQL-сервера или ус­тановить клиентскую часть. Кстати, это по-

следнее «или» способствует появлению еще одного ветвления в дереве вариантов установки. Следующее окно «Installation Definition» предоставляет на выбор пункты: «Client Tools Only» — поставить клиентскую часть, которая используется для организа­ции доступа к серверу с клиентских машин; «Server and Client Tools» — серверную и клиентскую части, а также «Connectivity only» — только драйверы для присоедине­ния к базе. Последний тип установки при­меняется исключительно на клиентских ма­шинах, которые должны работать с базами данных, то есть утилиты для работы с сер­вером, подобные Enterprise Manadger, ин­сталлироваться не будут. Поскольку мы устанавливаем именно сервер — здесь следует выбрать второй вариант.

Ввод имени («Instance Name») в следу­ющем окне не должен вызвать затрудне­ний. По умолчанию оно будет идентично NETBIOS-имени вашей машины. Если тре­буется его изменить, придется снять га­лочку в пункте «Default», после чего мож­но будет ввести другое название.

Из предложенных далее вариантов уста­новки «Typical», «Minimum» и «Custom» полезно использовать именно последний вариант. При этом выбирать что-то из списка предложенных компонентов не придется — пунктов, отмеченных здесь по умолчанию, более чем достаточно для начала работы сервера.

Однако параметрам запуска сервисов SQL Server в следующем окне стоит уделить бо­лее пристальное внимание: «Auto start SQL Service» — все сервисы стартуют автомати­чески; «Customize the setting for each Service» — каждому сервису назначить свои параметры запуска. Если вы выберете второй вариант, то в левой части окна на панели «Services» станут доступны элемен­ты «SQL Server» и «SQL Server Agent». При выборе любого из них в правой части окна на панели «Service Setting» можно будет настроить параметры запуска, выбрав один из трех стандартных вариантов: отключено, авто или вручную.

На этом настройки самого сервера факти­чески завершены, осталось только указать параметры учетных записей, авторизации и режима лицензирования. Итак, «Use the Local System account» — использовать учетную запись локальной системы, «Use a Domain User account» — использовать доменную учетную запись. Выбор целиком зависит от ваших предпочтений.

Для указания метода авторизации (Authentication Mode) необходимо вы­брать: «Windows Authentication Mode» — использовать авторизацию Windows NT или «Mixed Mode (Windows Authentication and SQL Server Authentication)» — использо­вать смешанную авторизацию Windows NT и SQL-сервера). При использовании второ­го метода не забудьте указать пароль учетной записи администратора «sа», которая создается по умолчанию.

И, наконец, последнее окно мастера «Choose Licensing Mode» посвящено выбору режима лицензирования, который зависит от условий, на которых вы приобрели дан­ную версию SQL Server 2000.

Основные инструменты

Наиболее часто используемой утилитой для работы с SQL Server является Enterprise Manager. Этот инструмент создавался с це­лью облегчения выполнения наиболее сложных административных задач, сочетая простоту работы с высокой функциональ­ностью. Среди них такие как управление системой безопасности, создание баз дан­ных и ее объектов, создание и восстановле­ние резервных копий, запуск и установка служб, а также конфигурирование связан­ных и удаленных серверов.

Например, создание новой базы данных с помощью Enterprise Manager сводится к нескольким кликам мыши и вводу имени БД. В левой части экрана утилиты необхо­димо выбрать тот SQL Server, на котором она будет размещена, и нажать правой кнопкой мыши на папке Database, после че­го в контекстном меню выбрать пункт «New Database». В появившемся окне «Database Properties» в поле «Name» необходимо впи­сать название базы и нажать кнопку «ОК». Описание этого процесса заняло больше времени, чем он длился бы на деле.

Создание таблиц происходит совершен­но аналогично: клик сначала правой кнопкой мыши на значке «Table», затем левой — на пункте «New Table» в контек­стном меню. Выше был приведен пример алгоритма выборки из базы данных для классического языка высокого уровня. Просто представьте, сколько времени и сил заняло бы описание на нем такой структуры как БД с несколькими взаимо­связанными таблицами.

Как бы хорошо не была настроена база данных, всегда существует вероятность потери информации по независящим от администратора причинам. Для сведения подобных потерь к разумному минимуму Enterprise Manager содержит инструмент резервного копирования БД.

Чтобы выполнить резервное копирова­ние базы вручную, нужно щелкнуть правой кнопкой мыши по названию базы и выбрать «Все задачи • > Backup Database». Далее в появившемся окне надо нажать кнопку «Аdd'», после чего выбрать каталог для со­хранения и в поле «File name» ввести имя файла, в котором будет содержаться база. Имя этого файла с названием базы может быть никак не связано. После подтвержде­ния намерения остается только дожидаться конца процесса копирования.

Для того чтобы восстановить данные из ранее сохраненного файла, необходимо иметь на SQL Server базу данных с названи­ем, идентичным имени родительской базы. Другими словами, если вы сохраняли БД как Data_Base, то для восстановления дан­ных необходимо создать на сервере базу с таким же названием (не путать с именем файла резервной копии).

Для восстановления информации из файла нужно нажать правой кнопкой мыши на базу, предназначенную для приема данных и вы­брать «Все задачи – Restore Database». Да­лее в

последовательно сменяющих друг дру­га окнах нужно выбрать пункты «FromDevise», «Select Devise» и «Аdd» и указать ката­лог, в котором расположен резервный файл.

 Вторая по важности и частоте использо­вания утилита после Enterprise Manager это Query Analyzer. Она предназначена для выполнения, отладки и анализа запросов. Окно «Query Analizer» разделено на три части. Слева находится браузер объектов (Object Browser), с помо­щью которого можно посмотреть список всех объектов, расположенных в любой базе дан­ных сервера, а также перечень всех функций и типов данных. Правая часть разделена на верхнюю и нижнюю, при этом верхняя поло­вина является полем для ввода запросов, а нижняя используется для вывода результатов их работы и отладочной информации.

При отладке хранимых процедур весьма удобна возможность трассировки их вы­полнения: для этого необходимо кликнуть на нужной процедуре правой кнопкой мыши и в появившимся контекстном меню выбрать пункт «Debug».

Помимо выполнения процедур и запросов в Query Analyzer предусмотрена возможность оценки скорости работы. Эту функцию мож­но включить, если открыть меню «Query» и выбрать в нем, соответственно, пункты «Display Estimated», «Execution Plan» или «Display Execution Plan».

Оператор SELECT

         Основной оператор языка SQL, предназначенный для выборки данных, - SELECT:

SELECT * FROM Table 1

Звёздочка означает все столбцы, а Table1 – имя таблицы, из которой мы эти столбцы хотим извлечь. Практическую цен­ность оператору SELECT придает ключевое слово WHERE, позволяющее выводить ис­ключительно те строки таблицы, которые соответствуют условию. Предположим, у нас есть таблица с информацией о персона­ле (Employees), где указаны имя работника (Name) и его заработная плата (Salary). Если нам нужно увидеть данные обо всех работ­никах, получающих заработную плату более 30 000 рублей, мы формулируем запрос:

SELECT Name FROM Employees WHERE Salary > 30000

На практике существует необходимость за­прашивать информацию одновременно из нескольких таблиц. Предположим, что у нас есть таблица Agents с информацией о торговых агентах: идентификационный номер (Agent_id), имя (Name) и дата рож­дения (Birth_Date). Есть еще одна табли­ца — Contacts, где содержатся данные о контрактах, заключенных агентами: идентификационный номер клиента (Client_id), номер агента (Agent_id), да­та заключения контракта (CDate) и сум­ма сделки (Gross_Income).

Предположим, необходимо премировать агентов, заключавших с начала года кон­тракты на сумму более 500 000 рублей. Запрос к базе данных будет выглядеть так:

SELECT Name, Bitth_Date FROM Agents, Contacts WHERE Agents.Agent_id = Contracts.Agent_id AND Contracts.CDate >`31.12.2004` AND CONTRACTS.Gross_Income > 500000

Условия WHERE связывает друг с другом две таблицы через номер агента, отбрасы­вает старые достижения и выбирает значи­тельные контракты. Логический оператор AND позволяет задавать несколько условий. Запись <Название таблицы>.<Название столбца> применяется для того, чтобы раз­личать столбцы с одним и тем же названи­ем из разных таблиц.

Поскольку один агент может заключить не­сколько больших контрактов, его данные могут быть несколько раз продублированы. Чтобы избежать этого, необходимо исполь­зовать ключевое слово DISTINCT:

SELECT DISTINCT Name, Birth_Day From …

Использование имен и вложенных запросов

В SQL-конструкциях назначение новых имен применяется, чтобы сохранить для дальнейших операций результаты, возвра­щаемые запросами и встроенными функ­циями, и сделать текст запроса более ком­пактным за счет сокращений. Для демонстрации эффектов переназна­чения имен, возьмем, к примеру, таблицу Rooms с информацией о жилых комнатах в многоквартирном доме со следующими столбцами: идентификатор комнаты (Room_id), тип (Room_type), длина (Length) и ширина (Width). Предположим, мы хотим получить информацию о жилой площади всех спален и гостиных в доме. Для этого формулируем запрос:

SELECT Room_Type, Length * Width AS

Living_Space

FROM Rooms

WHERE Room_Type = `Гостиная OR Room_Type =`Спальня`

В результирующей таблице не будет дан­ных о длине и ширине, зато появится столбец с информацией о площади, ко­торая была вычислена непосредственно при исполнении запроса

Второй вариант применения ключевого слова AS можно проиллюстрировать на примере о торговых агентах из предыду­щей заметки, который теперь будет вы­глядеть так:

SELECT DISTINCT Name, Birth_Date

FROM Agents AS A1, Contracts AS C1

WHERE A1.Agent_id = C1.Agent_id AND C1.CDate

>`31.12.2004` AND C1.Gross_Income > 500000

Дав с помощью ключевого слова АS табли­цам Agents и Contacts сокращенные име­на, мы сделали текст более компактным. Запрос можно сделать многоступенчатым, тогда результат вложенного запроса ста­нет исходными данными. Тот же самый пример с агентами можно выполнить в виде вложенного запроса:

SELECT Name, Birth_Day

FROM Agents

WHERE Agent_id IN (SELECT Agent_id

                        FROM Contracts

                        WHERE CDate >`31.12.2004` AND

Gross_Income >500000

В данном случае предикат IN последова­тельно проверяет, имеется ли среди ре­зультатов вложенного запроса по базе кон­трактов идентификатор каждого из аген­тов. Если он есть, то в результирующую таблицу головного запроса добавляются его данные (для противоположного резуль­тата можно использовать предикат NOT IN). В большинстве случаев предпочти­тельнее вместо вложенных запросов при­менять соединение таблиц по общим столбцам (...WHERE  Agents.Ag_Num = Contracts.Ag_Num...), однако иногда быва­ет, что все-таки без вложений не обойтись.

Объединения и внешние соединения

Выше мы вос­пользовались оператором OR для выборки данных о спальнях и гостиных. Можно пой­ти другим путем и использовать оператор UNION для объединения двух запросов:

(SELECT Room_Type, Length1 * Width1 AS

Living_Space FROM Rooms WHERE Room_Type  =

`Гостиная`)   UNION (SELECT Room_Type, Length1 *

Width1 AS Living_Space FROM Rooms WHERE

Room_Type = `Спальня`)

Оператор UNION строит на основе двух таблиц третью, куда попадают строки, ко­торые есть либо в первой исходной, либо во второй, либо в обеих вместе; строки-дубликаты при этом удаляются. Иногда для подобных целей удобнее пользоваться оператором OR, однако если условия объе­диняемых подзапросов сложные, UNION для их составления подходит больше. Суть такого инструмента как внешнее со­единение можно пояснить на следующем примере. Допустим, нам необходимо сде­лать выборку по контрактам, заключенным агентами в июне 2005 года. Мы можем вос­пользоваться для этого таким запросом:

SELECT Name, CDate, Gross_Income

FROM Agents AS A1, Contracts AS C1

WHERE A1.Agent_id = C1.Agent_id AND C1.CDate

BETWEEN `01.06.2005`AND`30.06.2005`

Он, разумеется, выдаст правильные резуль­таты, однако наличие имени агента после обработки запроса зависит от того, заклю­чил ли он сделку в этот период. Если необ­ходимо, чтобы в результирующей таблице всегда присутствовали все агенты, необхо­димо использовать так называемое левое внешнее соединение (LEFT OUTER JOIN). Его смысл состоит в том, что все строки таб­лицы, указанной слева от оператора LEFT OUTER JOIN, попадают в таблицу-результат, а из таблицы справа берутся только дан­ные, которые соответствуют условию:

SELECT Name, CDate, Gross_Income

FROM Agents LEFT OUTER JOIN Contracts ON

Agents.Agent_id = Contracts.Agent_id

AND Contracts.CDate BETWEEN `01.06.2005` AND

`30.06.2005`

Каждый агент из таблицы Agents записан­ной слева от LEFT OUTER JOIN, попадет в результат запроса, даже если ему нельзя будет подобрать соответствующих строк из правой таблицы (поскольку не все агенты заключали контракты в июне 2005 года). Необходимо обратить внимание, что вместо ключевого слова WHERE здесь используется слово ON. Если использовать слово WHERE, результат будет тот же самый, что и с обыч­ным запросом. Следует также помнить, что синтаксис левого внешнего соединения мо­жет сильно различаться в разных системах.

Математические функции и средства работы с датами

Поскольку SQL ориентирован на выборку данных, а не на управление вычисления­ми, его математический инструментарий довольно ограничен. Впрочем, перечень доступных функций в продуктах различ­ных разработчиков может варьироваться. Как правило, в большинстве реализаций присутствуют следующие функции: POWER (возведение в степень), SQRT (квад­ратный корень), АВS (модуль), LN и LOG10 (натуральный и десятичный логарифмы), ЕХР (экспоненциальная функция). Функ­ция ROUND(х, р) округляет число х до р десятичных знаков, TRUNCATE(х, р) — усекает. Функции FLOOR(х) и CEILING(х) возвращают ближайшие к нецелому х це­лые числа снизу и сверху соответственно. Предположим, нам зачем-то понадобилось найти не площадь, а диагональ каждой спальни из таблицы Rooms и округлить ее до двух знаков после запятой. Запрос будет иметь следующий вид:

SELECT Room_id,

ROUND(SQRT(POWER(Length,2) +

POWER(Width,2)), 2) AS Bias

FROM Rooms

WHERE Room_Type = `Спальня`

На выходе у нас получится таблица из двух столбцов, содержащих информацию об идентификаторе комнаты и ее длине по диагонали.

В большинстве реализаций SQL присутст­вует предикат BETWEEN, который не­сколько облегчает работу с интервалами чисел, в частности с временными и ка­лендарными интервалами (мы столкну­лись в предыдущем разделе в примере с выборкой контактов за июнь). В общем случае синтаксис предиката таков:

Val1 BETWEEN Low AND High

Предикат вернет TRUE, если значение Val1 будет находиться внутри диапазона, ограниченного значениями Low и High, или в противном случае False. Для простого формирования дат в диа­лектах SQL многих современных СУБД присутствует соответствующая функция MAKEDATE, которая вызывается с такими аргументами:

MAKEDATE(Year, Month, Day)

Если необходимо выполнить обратную за­дачу — вычленить год, месяц или день из даты, применяют оператор EXTRACT. В ча­стности, когда нужно определить текущий год, пользуются таким выражением:

EXTRACT (YEAR FROM CURRENT_DATE)

Для того чтобы сместиться относительно некой даты на заданное количество дней, месяцев или лет, используется ключевое слово INTERVAL. Например, следующее выражение возвращает дату, смещенную на пятнадцать дней вперед относительно даты MyDate:

MyDate + INTERVAL 15 DAYS

Группы и агрегатные функции

Иногда возникают ситуации, когда необ­ходимо произвести группировку данных, отбросив ненужную индивидуальную ин­формацию, зато добавив количествен­ные оценки групп. Для этого в SQL есть оператор GROUP BY.

Допустим, что нам необходимо получить из таблицы Contracts данные относительно ко­личества контрактов и общего объема про­даж, приходящихся на одного агента. Каж­дая запись в таблице Contracts описывает один контракт. Одному агенту может соот­ветствовать несколько таких записей. Следовательно, чтобы получить нужный ре­зультат, надо сгруппировать таблицу по по­лю «Ag_Num», содержащему индекс агента:

SELECT Agent_id, SUM(Gross_Income) AS

Gr_Income, COUNT(*) AS Contracts_Num

FROM Contracts

GROUP BY Agent_id

В результирующей таблице будет три столб­ца: в первом — номер агента, во втором — сумма всех заключенным им контрактов, в третьем — количество этих контрактов. Функции SUM, COUNT (а также AVG, MIN и МАХ) называются агрегатными. Их отличие от математических функций состоит в том, что аргументом может быть произвольное множество чисел. В нашем случае функция SUM складывает все значения Gross_Income для каждой отдельной группы, а COUNT(*) подсчитывает количество записей в ней. Усложним пример, чтобы увидеть исполь­зование функции AVG (вычисления сред­него арифметического).

Начальнику отдела продаж нужно внима­тельно следить за отстающими — теми, чей объем продаж ниже среднего уровня. Для этого ему следует написать такой запрос:

SELECT Name, Gr_Income

FROM Agents AS A1, (SELECT Agent_id,

SUM(Gross_Income)

                        FROM Contracts

                        GROUP BY Agent_id) AS T1(Agent_id,

Gr_Income)

WHERE A1.Agent_id = T1.Agent_id, Gr_Income<

AVG (T1.Gr_Income)

В раздел FROM вложен уже знакомый нам (но слегка сокращенный) запрос, занима­ющийся компоновкой. С помощью ключе­вого слова АS мы даем временной таблице его результатов и столбцам этой таблицы символьные имена, чтобы сослаться на них в основном запросе. Интересующий нас столбец «Объем контрактов на одного работника» называется теперь Gr_Income. А дальше в разделе WHERE основного за­проса мы отбираем тех агентов, у которых это значение ниже среднего.

Оператор CASE

Иногда бывает необходимо прямо в ходе выполнения запроса преобразовывать сим­вольные данные в числовые, и наоборот. В предыдущей заметке мы рассмотрели простейший случай компоновки, когда для вычисления общих параметров достаточно было просуммировать значения, содержав­шиеся в группируемых записях. Но не все значения можно просуммировать. Предположим, что мы имеем дело со школьной ведомостью School_Sheet, в ко­торой содержится информация относи­тельно идентификатора ученика (Pupil_id), его имени (Name), пола (Gender) и класса, в котором он учится (Group_id). Если теперь возникнет задача сгруппиро­вать детей по классам и определить, сколько человек учится в том или ином классе, то мы уже знаем, как это делать:

SELECT Group_id, COUNT(*) AS Total

FROM School_Sheet

GROUP BY Group_id

Однако куда бежать и за что хвататься, если нужно подсчитать, сколько в каждом классе мальчиков и девочек? Здесь к нам и придет на помощь оператор CASE:

SELECT Group_id,

SUM (CASE WHEN Gender=`M` THEN 1 ELSE

0)AS Boys,

SUM (CASE WHEN Gender = `F` THEN 1 ELSE

0)AS Girls,

Boys +Girls AS Total

FROM School_Sheet

GROUP BY Group_id

На каждой записи оператор CASE возвра­щает 0 или 1 в зависимости от содержи­мого поля Gender — следовательно, на единицу увеличивается счетчик мальчи­ков или счетчик девочек. Возможна и другая нотация для записи оператора CASE. Допустим, нам необходимо перевести буквенные оценки знаний уча­щихся в цифровые для нахождения сред­него бала. Соответствующий оператор пе­ревода будет записан так:

CASE Mark               WHEN A THEN 5

                                   WHEN B THEN 4

                                   WHEN C THEN 3

WHEN D THEN 2

WHEN E THEN 1

Некоторые системы не поддерживают оператор CASE. Обойти эту проблему можно с помощью таблиц соответствия. В нашем примере это будет таблица Convert_Table с полями «NMark» и «LMark», содержащими цифровой и буквенный варианты. Если в исходной таблице с оценками School_Marks значения прописаны в символьной форме, то конверсию можно осуществить так:

SELECT Name, Discipline, NMark

FROM School_Marks AS S1, Convert_Table AS C1

WHERE S1.Mark = C1.LMark

Создание таблиц и манипуляции с данными

Возможности SQL выходят за пределы од­ного лишь составления запросов. С его по­мощью можно создавать новые таблицы, добавлять, обновлять и удалять данные. Преимущество перед ручным редактирова­нием таблиц с помощью оболочки СУБД очевидно: редактирование осуществляется автоматически по заданным правилам при минимальном участии оператора — а зна­чит, очень быстро и без ошибок. Очень важная область применения авто­матических манипуляций данными — по­строение промежуточных таблиц. В систе­мах, не в полной мере поддерживающих SQL-92, часто возникают ситуации, когда результат промежуточного запроса необ­ходимо сохранить в новой таблице. Таблица создается с помощью оператора CREATE TABLE, после чего в скобках указы­ваются наименования и типы полей:

CREATE TABLE Table1

(Field1 INTEGER NOT NULL,

Field2 VARCHAR(20) NOT NULL,

…)

Для каждого столбца можно задать допол­нительные опции/ограничения, например NOT NULL (это означает, что в данном столбце не может быть «пустых» значе­ний) или UNIQUE (означает, что в столбце не может быть повторяющихся значений). Наконец, при помощи оператора CONSTRAINT можно накладывать сложные ог­раничения на содержимое таблицы с при­менением полноценных запросов. Напри­мер, представим, что в нашей таблице Agents добавлен еще один столбец — «Work_Start_Date», в котором указано, ког­да агент поступил на работу. Поэтому можно задать ограничение Contract_Date, кото­рое во избежание ошибок оператора будет контролировать, чтобы дата контракта, за­ключенного агентом, не была более ранней, чем дата его поступления на работу:

CREATE TABLE Contracts

(Agent_id INTEGER NOT NULL,

Client_id INTEGER NOT NULL,

CDate DATE NOT NULL,

Gross_Income Number NOT NULL,

CONSTRAINT Contract_Date

CHECK(EXISTS

                        (SELECT Agent_id, Work_Start_Date

                        FROM Agents AS A1

                        WHERE A1.Agent_id=

Contracts.Agent_id AND Contracts.CDate>

A1.Work_Start_Date)))

В данном случае комбинация СНЕСК (ЕХISTS (SELECT проверяет, существует ли вообще агент, на идентификатор которо­го ссылается добавляемая в таблицу за­пись, и если он существует — является ли дата заключения контракта более по­здней, чем дата поступления агента на работу. Если оба условия выполняются, SELECT возвращает непустые результаты запроса, оператор EXISTS, соответствен­но, принимает значение TRUE, и СНЕСК оказывается удовлетворен. Кроме того, с помощью ключевого слова DEFAULT можно задать значения, которые хранятся в данном столбце по умолчанию. Оператор INSERT INTO позволяет автома­тически добавлять в таблицу данные, по­лученные в результате запроса. К примеру, если создана промежуточная таблица Т1, где должна храниться информа­ция относительно объема продаж каждого агента, то заполняться она будет с помощью знакомого нам запроса, выдающего сгруп­пированные результаты:

CREATE TABLE T1

(Agent_id INTEGER,

Gr_Income NUMBER);

INSERT INTO T1

SELECT Agent_id, SUM(Gross_Income) FROM

Contracts GROUP BY Agent_id

Существует версия этого оператора, кото­рая позволяет добавлять в таблицу зара­нее определенные значения, например:

INSERT INTO Table1 VALUES (`John Smith`, 2, 34, 15)

Удаление строк из таблицы осуществляется с помощью оператора DELETE FROM, которо­му придает гибкость ключевое слово WHERE. Предположим, что мы хотим удалить из таб­лицы Contacts данные обо всех контрактах, заключенных до 2005 года. Для этого нам понадобится следующее выражение:

DELETE FROM Contracts

WHERE Cdate < `01.01.2005`

Условие поиска удаляемых записей можно сделать настолько сложным, насколько не­обходимо: с использованием данных из других таблиц и применением полноценных запросов. Чтобы добиться этого, необходи­мо воспользоваться комбинацией WHERE (EXISTS (SELECT, которая аналогична только что рассмотренной. Если запрос, идущий после ключевого слова SELECT возвращает непустые результаты, EXISTS и WHERE воз­вращают TRUE, и запись удаляется. Для корректировки данных в таблице при­меняется оператор UPDATE. Например, следующее выражение увеличивает в пол­тора раза значение в поле Field1 в тех строках, где это значение больше двух:

UPDATE Table1

            SET Field1 = 1.5 * Field1

            WHERE Field > 2

Ключевое слово WHERE здесь действует точно так же, как в случае с оператором DELETE.

Целиком таблицу можно уничтожить с по­мощью оператора DROP TABLE. Когда наша промежуточная таблица T1, которую мы обсуждали выше, будет уже не нужна, ее необходимо удалить с помощью следую­щей команды:

DROP  TABLE T1

Список использованной литературы:

1.     Перегудов Ф.И., Тарасенко Ф.П. Введение в системный анализ. Учебное пособие  для  для ВУЗов. - М.: Высшая школа, 1989. - 367 с.

2.     Дэвид А. Марка, Клемент Ман Гоуэн. Методология структурного анализа и проектирования/ Пер. с англ. - М.: Метатехнология, 1993, 240 с.

3.     Маклаков С. В. Bpwin, Erwin: Case-средства разработки информационных систем.  М.: Диалог-МИФИ, 2000, 254с.

4.     Леоненков Ф. В. Самоучитель UML. СПб.: BHV-Петербург, 2001, 304с.