Введение в SQL

Первые языки запросов для реляционных баз данных пояились в 70-е годы. В то время существовало несколько различных языков запросов, что порождало определенные неудобства у производителей СУЬД. Непосредственным предшественником языка SQL явился язык SEQUEL.

Стандарт языка SQL постоянно расширяется, поэтому к настоящему времени имеется несколько официально утвержденных вариантов:

1. SQL-89, первый неполный вариант, практика быстро показала, что он нуждается в расширении.

2. SQL-92 (или SQL-2), значительно расширенная версия, многие СУБД в настоящее время гарантируют полную поддержку SQL-2 и частичную – более поздних стандартов.

3. SQL-99, введены еще некоторые расширения.

4. SQL-2003, самый полный вариант стандарта, в котором учтены многие решения, уже реализованные разработчиками СУБД и ставшие стандартом de-facto (например, стандартизирован объект sequence, который давно используется в некоторых СУБД, например, Oracle, стандартизированы почти все встроенные типы данных, используемые в различных СУБД).

Стардарт SQL-2003 содержит довольно много команд, которые охратывают различные аспекты функционирования ИС и разделены на 7 классов команд. Тем не менее, основу языка SQL составляют два класса команд, которые принято называть языками: язык определения данных и язык манипулирования данными. В оригинале - DDL (data definition language) и DML (data manipulation language).

В данном учебном модуле рассмотрим только эти два класса (DDL и DML). Остальные классы, которые в стандарте SQL-92 объединены общим названием «язык управления доступом» (data control language – DCL), будут частично рассмотрены в следующем учебном модуле.

При изложении материала будем ориентироваться на стандарт SQL 2003, однако следует иметь в виду, что те команды, которые нам предстоит изучить, практически не изменились со времен SQL-92.

Производители СУБД, в целом поддерживая стандарт SQL, тем не менее, иногда допускают незначительные изменения синтаксиса в отдельных командах. Практически все СУБД содержат те или иные расширения языка SQL. Поэтому изучать язык SQL, не привязываясь ни к какой конкретной СУБД, можно только чисто теоретически. Поскольку целью данного модуля является получение компетенций (т.е. умения применить полученные знания на практике), необходимо остановиться на какой-либо конкретной СУБД, которая будет использоваться для отладки демонстрационных примеров и практических заданий модуля.

В качестве такой СУБД была выбрана самая распространенная на мировом рынке Oracle. В диалекте SQL для СУБД Oracle отклонения от стандарта минимальны. Приведенные в главе примеры отлаживались с использованием клиентской консольной утилиты SQL *Plus на сервере Oracle 10g.

Везде в описании команд, где имеется хотя бы небольшое отклонение синтаксиса Oracle от стандарта SQL 2003, это будет отдельно оговариваться. Учитывая широкое распространение таких СУБД как Microsoft SQL Server, MySQL и ряда других, иногда будут оговариваться особенности и этих СУБД.

Формат записи операторов SQL свободный. Везде, где имеется пробел или знак препинания, может быть вставлено любое число пробелов или переходов на новую строку. При работе в консольной утилите SQL *Plus используется символ; (точка с запятой) как признак окончания запроса – текст запроса, завершенный точкой с запятой, немедленно отсылается на сервер для выполнения. Однако точка с запятой не является частью команды SQL, поэтому в приводимых в лекциях примерах она будет отсутствовать.

При описании языка мы сочли возможным не приводить полный синтаксис каждой команды в формальной форме (формы Бэкуса-Наура или синтаксические диаграммы), учитывая лаконичность и относительную простоту синтаксиса, а также наличие отклонений от стандарта в диалектах языка и изменчивость самого стандарта и диалектов. Синтаксис команд описывается неформально, с пропуском некоторых незначительных, с нашей точки зрения, конструкций, но достаточно строго:

· все ключевые слова языка записываются прописными буквами, а имена, формируемые пользователями, - строчными, хотя, с точки зрения лексики языка SQL, различия в регистре символов несущественны;

· все необязательные элементы команды заключаются в квадратные скобки;

· в тех случаях, где пробел может трактоваться неоднозначно, он заменяется знаком подчеркивания;

· при пропуске части команды используется многоточие.

Возможные недостатки неформального подхода к описанию компенсируются большим количеством примеров и дополнительными словесными комментариями.

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

Первые разработки реляционных СУБД были выполнены в компании IBM в начале 1970-х годов. Тогда же был создан язык данных, предна­значенный для работы в этих системах. Экспериментальная вер­сия этого языка называлась SEQUEL - структурированный английский язык запросов. Официальная версия была названа - SQL (Structured Query Language). SQL- это подъязык данных, поскольку СУБД содержит и другие языковые средства.

В 1981 году IВМ выпускает реляционную СУБД SQL/DS. К этому времени компания Relation Software Inc. (сегодня это Oracle Cor­poration) уже выпустила свою реляционную СУБД. Эти продукты сразу же стали стандартом систем, предназначенных для управ­ления базами данных. В состав этих продуктов вошел и SQL, ко­торый фактически стал стандартом для подъязыков данных. Про­изводители других СУБД выпустили свои версии SQL- некоторые расширения SQL, чтобы получить некоторое преимущество для "своей" СУБД. Вместе с тем, начались работы по созданию общепризнанного стандарта SQL.

В 1986 году Американский национальный институт стандартов (American National Standards Institute, ANSI) выпустил официаль­ный стандарт SQL-86, который в 1989 году был обновлен и получил новое название SQL-89. В 1992 году этот стандарт был назван SQL-92. Последней версией стандарта SQL является SQL: 2003.

Любая реализация SQL в конкретной СУБД несколько отлича­ется от стандарта, соответствие которому объявлено произво­дителем. Так, многие СУБД (например, Microsoft Access 2003, PostgreSQL 7.3) поддерживают SQL-92 не в полной мере, а лишь с некоторым уровнем соответствия. Кроме того, они поддержива­ют и элементы, которые не входят в стандарт. Однако разработчи­ки СУБД стремятся к тому, чтобы новые версии их продуктов как можно в большей степени соответствовали стандарту SQL.

ПРИМЕЧЕНИЕ: Будем рассматривать элементы SQL:2003, не все из которых поддерживаются существующими СУБД. Прежде чем применять их на практике, следует убедиться, что они будут работать в ва­шей СУБД. Об этом можно узнать из технической документации. Большинство описанных элементов соответствуют и более ранним версиям SQL, в частности, широко распространенному SQL-92.

SQL задумывался как простой язык запросов к реляционной базе данных, близкий к естественному (точнее, к английскому) языку. Предполагалось, что близость по форме к естественному языку сделает SQL средством, доступным для широкого применения обычными пользователями баз данных, а не только программи­стами. Первоначально SQL не содержал никаких управляющих структур, свойственных обычным языкам программирования. Запросы, синтаксис которых довольно прост, вводились прямо с консоли последовательно один за другим и в этой же последо­вательности выполнялись. Однако SQL так и не стал инструмен­том простых служащих, для них простой SQL оказался слишком сложным и неудобным. На практике с базой данных обычно работают посредством при­ложений, написанных программистами на процедурных языках, например, на С, Visual Basic, Pascal, Java и др. Часто приложения создаются в специальных средах визуальной разработки, таких как Delphi, Microsoft Access, Visual dBase и т. п. При этом разра­ботчику приложения практически не приходится писать коды программ, поскольку за него это делает система разработки. Во всяком случае, работа с программным кодом оказывается мини­мальной. Эти приложения имеют удобный графический интер­фейс, не вынуждающий пользователя непосредственно вводить запросы на языке SQL. Вместо него это делает приложение. Впрочем, приложение может как использовать, так и не исполь­зовать SQL для обращения к базе данных. SQL не единственное, хотя и очень эффективное средство получения, добавления и из­менения данных, и если есть возможность использовать его в приложении, то это следует делать.

Реляционные базы данных могут существовать и действительно существуют вне зависимости от приложений, обеспечивающих пользовательский интерфейс. Если по каким-либо причинам тако­го интерфейса нет, то доступ к базе данных можно осуществить с помощью SQL, используя консоль или какое-нибудь приложе­ние, с помощью которого можно соединиться с базой данных, вве­сти и отправить SQL-запрос (например, Borland SQL Explorer). Язык SQL считают декларативным (описательным) языком, в отличие от языков, на которых пишутся программы. Это озна­чает, что выражения на языке SQL описывают, что требуется сде­лать, а не каким образом.

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

SELECT Фамилия, Должность FROM Сотрудники WHERE Отдел=102;

По-русски данное выражение звучит так:

ВЫБРАТЬ Фамилия, Должность ИЗ Сотрудники ПРИ УСЛОВИИ, ЧТО отдел = 102;

Например, чтобы изменить значение "Иванов" на "Петров" столбца Фамилия, достаточно выполнить следующий запрос:

UPDATE Сотрудники SET Фамилия="Петров" WHERE Фамилия= "Иванов" ;

По-русски данное выражение выглядит так:

ОБНОВИТЬ Сотрудники УСТАНОВИВ Фамилия РАВНЫМ "Петров" ГДЕ Фамилия = "Иванов";

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

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

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

DML (Data Manipulation Language - язык манипулирования данными) предназначен для поддержки базы данных: выбора (select), добавления (insert), изменения (update) и удаления (delete) данных из таблиц. Эти операторы (команды) могут со­держать выражения, в том числе и вычисляемые, а также подза­просы - запросы, содержащиеся внутри другого запроса. В об­щем случае выражение запроса может быть настолько сложным, что сразу и не скажешь, что он делает. Однако слож­ный запрос можно мысленно разбить на части, которые легче анализировать. Аналогично, сложные запросы создаются из от­носительно простых для понимания выражений (подзапросов).

DDL (Data Definition Language - язык определения данных) предназначен для создания, модификации и удаления таблиц и всей базы данных. Примерами операторов, входящих в DDL, являются CREATE TABLE (создать таблицу), CREATE VIEW (создать представление), CREATE SHEMA (создать схему), ALTER TABLE (изменить таблицу), DROP (удалить) и др.

СУБД предусматривает некото­рую защиту данных автоматически. Однако в ряде случаев следует предусмотреть дополнительные меры, предоставляе­мые DCL.

ПРИМЕЧАНИЕ: Ключевые слова в выражениях на языке SQL могут записываться как прописными, так и строчными буквами, в одну или несколько строк. В конце выражения должна стоять точка с запятой. Од­нако во многих системах, обеспечивающих ввод, редактирование и отправку на выполнение SQL-выражений, в случае ввода одного такого выражения допускается не указывать признак окончания. Если же требуется выполнить блок из нескольких SQL-выражений, то они обязательно должны быть разделены точкой с запятой.

Bce ключевые слова SQL (команды, операторы и проч.) являются зарезервированными и не должны использоваться в качестве имен таблиц, столбцов, переменных и т. п.

Реляционное исчисление на доменах

Реляционное исчисление на кортежах

Выражение в реляционном исчислении на кортежах имеют вид:

{S | F(S)} , где S – переменная-кортеж, F -формула.

Пример: все продавцы, получающие зарплату больше 10000

{S | Продавцы(S) & S.Зарплата>10000}

Формулы могут строиться с помощью логических операций конъюнкции, дизъюнкции, отрицания и операций сравнения. Используются кванторы существования и всеобщности, например:

Выбрать все сделки Иванова:

{S | Сделки(S) & (B)(Продавцы(B) & B.N = S.N_Продавца & B.Имя=’Иванов’)}

Выражение имеет вид: {d1, d2,…,dn | F(d1,d2,…, dm)}; m>=n , где d1…dm – переменные области определения (домена). F – формула, которая может быть отношением или выражением сравнения.

Пример: Найти номера и имена всех продавцов, зарплата которых > 10000.

{N, Имя | (Город, Отдел, N_Начальника)(Продавцы(N, Имя, Отдел, Город, Зарплата, N_Начальника)& (Зарплата>10000))}

Д/З 5. Для примера реляционной БД из Д/З 4 приведите примеры всех операций, запишите запрос в формах реляционного исчисления на кортежах и на доменах.

Вопросы для самопроверки:

1. Что из трех математических основ требует процедурного программирования (то есть программирования с поэтапным получением результата), а что – декларативного (то есть описания желаемого результата)?

Язык SQL (Structure Query Language – язык структурированных запросов) представляет собой декларативный язык для работы с реляционными БД, основанный на реляционном исчислении на кортежах. Декларативность языка заключается в том, что, в отличие от императивных языков (например, С++), программист не пишет последовательность действий для получения нужного ему результата, а описывает желаемый результат. СУБД сама подбирает алгоритм нахождения результата по его описанию.

Язык SQL состоит из трех подъязыков, которые описаны в Табл. 8:

Табл. 8. Подъязыки языка SQL

Операторы определения и управления данными просты, часто они скрыты от разработчика, если он пользуется мастерами при создании БД. Их рассмотрение выходит за пределы данной книги. Все богатство языка SQL заключено в операторе Select, который используется со вспомогательными операторами, приведенными в Табл. 9.

Табл. 9. Вспомогательные операторы для Select

Синтаксис оператора Select сложный, мы будем рассматривать его на примерах от простых к сложным, обращая внимание на типичные ошибки, с которыми сталкивается начинающий программист на SQL.

SQL (Structured Query Language) - это сокращенное название структурированного языка запросов, предоставляющего средства создания и обработки данных в реляционных БД. Независимость от специфики компьютерных технологий, а также поддержка SQL лидерами промышленности в области технологии реляционных баз данных сделали его основным стандартным языком БД.

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

Появление теории реляционных БД дало толчок к разработке ряда языков запросов, которые можно отнести к двум классам:

алгебраические языки, позволяющие выражать запросы средствами специализированных операторов, применяемых к отношениям;

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

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

СУБД, работающие с файловыми серверами, позволяют множеству пользователей разных ЭВМ, расположенных достаточно далеко друг от друга, получать доступ к одним и тем же БД. При этом упрощается разработка различных автоматизированных систем управления организациями, учебных комплексов, информационных и других систем, где множество сотрудников или учащихся должны использовать общие данные и обмениваться создаваемой в процессе работы информацией. Однако при таком подходе вся обработка запросов из программ или с терминалов пользовательских ЭВМ на них и выполняется, поэтому для реализации даже простого запроса необходимо считывать из файлового сервера или записывать на сервер целые файлы, а это ведет к конфликтным ситуациям и перегрузке сети, Для исключения указанных недостатков была предложена технология клиент/сервер, однако при этом нужен единый язык общения с сервером - и в его качестве был выбран SQL.

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

предложения определения данных - определение БД, а также определение и уничтожение таблиц и индексов;

запросы на выбор данных - предложение SELECT;

предложения модификации данных - добавление, удаление и изменение данных;

предложения управления данными - предоставление и отмена привилегий на доступ к данным, управление транзакциями и другие.

Кроме того. SQL предоставляет возможность выполнять в этих предложениях:

Арифметические вычисления, включая разнообразные функциональные преобразования, обработку текстовых строк и выполнение операции сравнения значений арифметических выражений и текстов;

Упорядочение строк или столбцов при выводе содержимого таблиц на печать или экран дисплея;

Создание представлений, позволяющих пользователям интерпретировать данные без увеличения их объема в БД;

Сохранение выводимого по запросу содержимого таблицы, нескольких таблиц или представления в другой таблице;

Группирование данных и применение к этим группам таких операций, как среднее, сумма, максимум, минимум, число элементов и т.п.

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

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

Основные достоинства языка SQL заключается в следующем:

Стандартность языка SQL - как уже было сказано, его использование в программах стандартизировано международными организациями;

Независимость от конкретных СУБД - все распространенные СУБД используют SQL, т.к. реляционную БД и программы, которые с ней работают, можно перенести с одной СУБД на другую с минимальными доработками;

Возможность переноса с одной вычислительной системы я другую - СУБД может быть ориентирована на различны, вычислительные системы, однако приложения, созданные с помощью SQL, допускают использование как для локальных БД так и для крупных многопользовательских систем;

Реляционная основа языка - SQL является языком реляционных БД, поэтому он стал популярным тогда- когда популярной стала реляционная модель представления данных. Табличная структура реляционной БД хорошо понятна, поэтому язык SQL является простым и легким для изучения;

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

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

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

Поддержка архитектуры клиент/сервер - SQL одно из лучших средств для реализации приложений на платформе клиент/сервер. При этом SQL служит связующим звеном между клиентской системой, взаимодействующей с пользователем, и серверной системой, управляющей БД, позволяя каждой из них сосредоточиться на выполнении своих прямых функций

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

В SQL определены два подмножества языка:

· SQL-DDL (Data Definition Language) - язык определения структур и ограничений целостности баз данных. Сюда относятся команды создания и удаления баз данных; создания, изменения и удаления таблиц; управления пользователями и т.д.

· SQL-DML (Data Manipulation Language) - язык манипулирования данными: добавление, изменение, удаление и извлечение данных, управления транзакциями

Здесь не дается строгое описание всех возможностей SQL-92. Во-первых, ни одна СУБД не поддерживает их в полной мере, а во-вторых, производители СУБД часто предлагают собственные расширения SQL, несовместимые друг с другом. Поэтому мы рассматриваем некое подмножество языка, которое дает общее представление о его специфике и возможностях. В то же время, этого подмножества достаточно, чтобы начать самостоятельную работу с любой СУБД. Более формальный (и более полный) обзор стандартов SQL сделан в статье С. Д. Кузнецова "Стандарты языка реляционных баз данных SQL: краткий обзор",журнал СУБД N 2, 1996 г. Ознакомится с русским переводом стандарта SQL можно на сервере Центра информационных технологий, сравнительное описание различных версий языка (для СУБД Sybase SQL Server, Sybase SQL Anywhere, Microsoft SQL Server, Informix, Oracle Server) приводится в книге Дж.Боуман, С.Эмерсон, М.Дарновски "Практическое руководство по SQL", Киев, Диалектика, 1997.

Следует также отметить, что в отличие от "теретической" терминологии, используемой при описании реляционной модели (отношение, атрибут, кортеж ), в литературе при описании SQL часто используется терминология "практическая" (соответственно - таблица, столбец, строка ). Здесь мы следуем этой традиции.

Все примеры построены применительно к базе данных publications , содержащей сведения о публикациях (как печатных, так и электронных), относящихся к теме данного курса. Структуру этой базы данных можно посмотреть здесь, ее проектирование описано в разделе 5.4, доступ к ней для практических занятий можно получить через Internet посредством СУБД Leap (реляционная алгебра) или СУБД PostgreSQL. (язык SQL).