Ковалев Язык баз данных SQL

САРАТОВСКИЙ ГОСУНИВЕРСИТЕТ Механико-математический факультет

Язык баз данных SQL

Кафедра математического и компьютерного моделирования

Подготовил Ковалев А.Д.

Дата последнего обновления 1 ноября 2010 г.

1. Поддержка языков баз данных

Для работы с базами данных используются специальные языки, в целом называемые языками баз данных. В ранних СУБД поддерживалось несколько специализированных по своим функ- циям языков. Чаще всего выделялись два языка – язык определения схемы БД (SDL – Schema Definition Language) и язык манипулирования данными (DML - Data Manipulation Language). SDL служил главным образом для определения логической структуры БД, то есть той структу- ры БД, какой она представляется пользователям. DML содержал набор операторов манипули- рования данными, то есть операторов, позволяющих заносить данные в БД, обновлять, удалять или выбирать существующие данные.

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

Название SQL вначале было аббревиатурой, образованной от Structured Query Language (язык структурированных запросов), и его было принято произносить «сиквел». Сейчас, когда язык стал стандартом, SQL – это уже не аббревиатура, а название, которое произносится как «эс-кю- эль».

Язык SQL сочетает средства SDL и DML, то есть позволяет определять схему реляционной БД и манипулировать данными. При этом именование объектов БД (для реляционной БД – это именование таблиц и их столбцов) поддерживается на языковом уровне в том смысле, что ком- пилятор языка SQL производит преобразование имен объектов в их внутренние идентификато- ры на основании специально поддерживаемых служебных таблиц-каталогов. Внутренняя часть СУБД (ядро) вообще не работает с именами таблиц и их столбцов.

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

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

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

2. Модели взаимодействия с БД

2.1Модель с централизованной архитектурой

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

Однако исходная идея создания и использования баз данных предполагала многопользователь- ское использование данных. С этой целью к мейнфрейму подключалось несколько терминалов. При этом в рамках ресурсов одного компьютера (мейнфрейма) приходилось обслуживать весь комплекс возникающих задач, начиная от собственно обработки и хранения данных, до ото- бражения информации и приема запросов от пользователей. Модель использовалась в период широкого распространения больших ЭВМ (IBM-370, ЕС-1045, ЕС-1060). Основным недостат- ком этой модели являлось резкое снижение производительности при увеличении числа пользо- вателей.

2.2Модель с автономными персональными компьютерами

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

Механизм репликации реализован, в частности, в СУБД MS Access. Основным недостатком модели является невозможность оперативного обновления данных на всех компьютерах при изменении их одним из пользователей.

2.3Архитектура «файл-сервер»

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

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

В рамках архитектуры «файл-сервер» были выполнены первые версии таких популярных (так называемых настольных) СУБД, как dBase и MS Access.

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

Что улучшается при использовании трехзвенной архитектуры? Теперь при изменении бизнес- логики больше нет необходимости изменять клиентские приложения и обновлять их у всех пользователей. Кроме того, максимально снижаются требования к клиентской аппаратуре.

2.6Распределенные базы данных

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

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

Пользователи взаимодействуют с распределенной базой данных через локальные и глобальные приложения. Локальные приложения дают пользователю возможность работать со своими ло- кальными данными и не требуют доступа к другим фрагментам. Глобальные приложения дают пользователю возможность работать с другими фрагментами распределенной базы данных, расположенными на других компьютерах сети. Объединение данных организуется виртуально. Соответствующий подход, по сути, отражает организационную структуру предприятия, со- стоящего из отдельных подразделений. Причем, хотя каждое подразделение обрабатывает свой набор данных, существует необходимость доступа к этим данным, как к единому целому (в ча- стности, для управления всем предприятием).

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

Достоинством модели распределенной базы данных является приближение данных к месту их порождения. Недостатком модели является достаточно высокая сложность управления данны- ми как единым целым.

2.7Технология тиражирования данных

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

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

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

3. Введение в язык SQL

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

Язык SQL имеет следующие основные категории команд (операторов):

1)DDL – язык определения данных. Включает операторы создания новых объектов базы данных, изменения их определений, а также удаления.

2)DML – язык манипулирования данными. Представлен тремя командами модификации существующих объектов базы данных, а именно командами вставки, обновления и уда- ления их составных частей (insert, update, delete).

3)DQL – язык запросов. Представлен оператором select, имеющим многочисленные опции и фразы.

4)DCL – язык управления данными. Включает команды распределения полномочий между пользователями.

5)Команды управления транзакциями.

6)Команды администрирования данных. С их помощью пользователь осуществляет кон- троль над выполняемыми действиями, анализирует операции базы данных и производи- тельность системы в целом. Не следует путать администрирование данных с админист- рированием базы данных. Последнее представляет собой общее управление базой дан- ных и подразумевает использование команд всех уровней.

Кроме того, реализации языка SQL в конкретных СУБД могут включать следующие группы операторов:

1)операторы, реализующие операции над программными объектами,

2)операторы управления ходом выполнения программного кода,

3)операторы для работы с курсорами (CURrent Set Of Records),

4)операторы комментариев.

Далее в курсе рассматривается язык Transact-SQL, реализованный в системе Microsoft SQL Server.

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

Базовыми объектами баз данных являются

1)отношения, реализуемые в виде а) таблиц (table),

б) представлений (view),

в) табличных пользовательских функций (user defined function);

2)объекты для таблиц, включающие

а) умолчания (default),

б) правила (rule),

в) пользовательские типы данных (user defined data type); 3) хранимые процедуры (stored procedure);

4)триггеры (trigger);

5)скалярные пользовательские функции (user defined function).

Таблицы содержат первичные данные, хранящиеся в базе данных, и в этом смысле являются базовыми таблицами.

Представления являются виртуальными таблицами. Реально в представлении хранятся не дан- ные, а только оператор select, описывающий запрос к базе данных. Обращение к представлению эквивалентно обращению к результату выполнения этого запроса.

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

Табличные пользовательские функции аналогичны неиндексированным представлениям, но в отличие от них

1)могут принимать параметры,

2)не ограничиваются единственным оператором select при описании запроса, то есть могут быть многооператорными.

Объекты для таблиц являются объектами баз данных, то есть хранятся в них и могут быть при- менены при определении любых таблиц.

Хранимые процедуры представляют собой процедуры, написанные разработчиком базы данных на языке Transact-SQL. Хранимые процедуры являются объектами базы данных и, естественно, копируются и перемещаются вместе с копированием и перемещением базы данных.

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

Скалярные пользовательские функции, как и другие объекты баз данных, хранятся в базах дан- ных.

Далее при описании синтаксических конструкций используются следующие металингвистиче- ские символы:

единицы;

6),.. – ( запятая и две точки) означает возможность повторения предшествующей син- таксической единицы через запятую. Таким образом, следующие синтаксические конст- рукции эквивалентны:

единица [, единица] … единица ,..

4. Таблицы и индексы

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

create table имя_таблицы

(

{ имя_столбца тип_данных [ null | not null ] default константное_выражение

identity (начальное_значение, шаг_приращения)

constraint имя_ограничения

{primary key [ clustered | nonclustered ]

} ,.. [ , [ ,

{primary key [ clustered | nonclustered ] ({ имя_столбца [asc | desc] } ,..)

] ...

)

4.1Определение хранимого столбца

При определении хранимого столбца задается имя столбца, тип данных и опция допустимости null-значений (действует но умолчанию).

Фраза default задает значение по умолчанию, используемое при вставке новой строки в табли- цу, если значение столбца в новой строке явно не задано. Константное выражение, определяю- щее значение по умолчанию, может содержать встроенные функции. Значение null для кон- стантного выражения допустимо, если только для столбца установлена опция null.

Вместо фразы default может быть задана фраза идентичности identity, определяющая столбец как идентификационный. Идентификационный столбец должен иметь целочисленный тип, то есть либо тип int или его разновидность, либо тип decimal (p[,0]) с нулевым числом знаков по- сле десятичной точки. При вставке новой строки в таблицу значение для идентификационного столбца явно не указывается. Оно генерируется автоматически, исходя из заданного начального значения и шага приращения, который может быть и отрицательным. Генерируемые значения никогда не повторяются. Поэтому фраза идентичности наиболее часто используется при опре- делении первичных ключей как суррогатных. Свойство идентичности может быть задано не бо- лее чем для одного столбца таблицы. Если при определении столбца не задано ни значение по