Введение в SQL - 1

Введение


От автора


В последнее время всё больше и больше возникает требующих решения прикладных задач, где необходимо использовать базы данных. На данном историческом этапе наибольшее распространение получили реляционные системы управления базами данных (РСУБД или Relational Database Management Systems - RDBMS), основанные на хорошо проработанном математическом аппарате реляционной алгебры. Для работы с реляционными базами данных повсеместно принят структурированный язык запросов (SQL).

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

Целями данного цикла статей являются:

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

Автор полагает, что читатель обладает знаниями в области проектирования баз данных и знаком с операциями реляционной алгебры. (Например, по статьям Alf'а, находящимся на этом сайте).

Краткая история языка


SQL В 70-х гг. XX века Э. Кодд разработал реляционную модель данных. На основе этой модели компания IBM создала реляционную СУБД System R, использующую язык запросов SEQUEL2 (Structured English Query Language 2 - структурированный английский язык запросов). В 1976 г. вышло первое описание этого языка. А в 1979 г. Oracle выпустила первую коммерческую реализацию SQL (слово "английский" из названия исключили). IBM также использовала этот язык для работы со своими СУБД DB2 и SQL/DS. В 1986 г. ANSI выпустил первый стандарт языка (ANSI X3.135-1986). В 1989 г. вышел следующий стандарт ANSI X3.135-1989 "Database Language SQL with Intergity Enhancement" (известный, как ANSI SQL-89). Этот же стандарт был принят ISO (ISO 9075-1989 "Database Language SQL with Integrity Enhancement"). В настоящее время стандарт ISO/IEC 9075 постоянно обновляется. Большая часть имеющихся средств использует стандарт SQL-92 и более поздние стандарты 90-х годов.

Structured Query Language, SQL. Аббревиатура читается в настоящее время двояко: либо "эс-кью-эль", либо, по старинке, "сиквел".

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

Общее описание языка


SQL является полнофункциональным языком для работы с реляционными базами данных. Как и всякий язык для работы с СУБД, SQL состоит из двух больших частей: языка определения данных (ЯОД или Data Definition Language - DDL) и языка манипулирования данными (ЯМД или Data Manipulation Language - DML). Первая часть отвечает за создание, изменение и удаление различных объектов базы данных, например, таблиц, колонок таблиц, триггеров и т.п. Вторая часть реализует обработку хранящихся в базе данных: обновление, удаление, вставку и обработку запросов. ЯМД является языковой реализацией операций реляционной алгебры. Объектом операций является отношение - это надо усвоить чётко - не запись (кортеж), не значение атрибута, а именно отношение - вся таблица целиком, проще выражаясь. Но позже мы к этому ещё вернёмся.

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

О примерах в этих статьях


При изучении DDL SQL мы будем пользоваться достаточно мощной и широко используемой коммерческой СУБД Microsoft SQL Server.


Автор будет использовать 2000 версию, но большая часть примеров (а то и все) пойдут и на версии 7.0.

При изучении DML SQL также будем использовать SQL Server, однако большая часть запросов будет работать и на Microsoft Access. Следует позаботиться о наличии базы Northwind, устанавливающейся в качестве примера как с Access, так и с SQL Server. Автор будет испытывать примеры как на SQL Server 2000, так и на Access 2003.

Примечание для тех, кто не сталкивался с SQL Server


Данная СУБД существует в нескольких редакциях. Для работы можно использовать любую. Следует отметить, что редакция Desktop Engine не содержит утилит управления сервером. Работа с сервером осуществляется через сеть (даже, если он и пользователь физически находятся на одной машине). Сервер может использовать самые разнообразные сетевые протоколы. По умолчанию на операционных системах Windows 2000 и выше связь с сервером осуществляется через именованные каналы (Named Pipes), на операционной системе Windows 98 придётся использовать протокол TCP/IP (по умолчанию порт 1433). При удалённом доступе к серверу на компьютере клиента нужно иметь MDAC 2.6 и выше, который бесплатно можно скачать с сайта Microsoft.

Работать с SQL Server можно через устанавливающиеся с ним утилиты (не в Desktop Engine редакции), также в качестве клиента можно использовать Access 2000 и выше.

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

SQL - язык определения данных


Типичные объекты СУБД на примере Microsoft SQL Server


Главными объектами всякой СУБД являются базы данных (databases). В старых СУБД (например, FoxPro ранних версий под MS-DOS) класс объектов "база данных" не был введен явно. Теперь практически в любой СУБД имеется такой класс объектов. База данных является контейнером и пространством имён для всех использующихся в ней объектов.

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

Внутри всякой базы данных имеются следующие кдассы объектов: таблица (table), представление (view), хранимая процедура (stored procedure), индекс (index), связь (relationship), триггер (trigger), умолчание (default). Также в ряде коммерческих СУБД есть такой полезный класс объектов, как диаграмма (diagram).

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

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

Хранимая процедура является набором инструкций (часто в скомпилированном виде), хранящимся на стороне СУБД. Набор инструкций обычно пишется как последовательность SQL-команд. В SQL Server под компиляцией понимается подготовка плана исполнения процедуры в момент её создания или обновления. Подробнее о планах исполнения мы поговорим, когда будем изучать язык манипулирования данными. В хранимых процедурах можно реализовывать сложные алгоритмы обработки данных внутри базы. Использование хранимых процедур часто значительно сокращает объём кода клиентского приложения и во многих случаях позволяет чётко отделять алгоритмы логики программы от алгоритмов обработки данных. О многоуровневых приложениях скажем несколько слов где-нибудь в следующих статьях.

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

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

Связью (или отношением) называется особый объект, позволяющий установить зависимость между таблицами и сопоставить этой зависимости ряд проверяющих триггеров. В подавляющем большинстве СУБД реализовано лишь один из возможных видов связи: "один ко многим" ("one to many"). В такой связи участвуют две таблицы, в одной из которых определяется первичный ключ (primary key), а в другой внешний ключ (foreign key). Всегда первичный ключ - это "один", внешний ключ - "много". Соответственно, таблица, содержащая первичный ключ, называется главной (master table), а таблица с внешним ключом - подчинённой (detail table). Разумеется, имеется возможность связать таблицу с самой собой.

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

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

Здесь мы упомянули лишь основные статические объекты, встречающиеся во многих СУБД. В SQL Server помимо вышеперечисленного в базе имеются следующие объекты: пользователи (users), роли (roles), правила (rules), пользовательские типы данных (user defined data types), пользовательские функции (user defined functions), полнотекстовые каталоги (full-text catalogs). Однако, нашей целью является изучение SQL, а не Microsoft SQL Server, поэтому описание данных объектов мы приводить не будем, и не будем использовать эти объекты в последующем.

Следует лишь отметить, что пользователю базы данных назначаются определённые права доступа к базе, и этот объект ассоциируется с объектом учётной записи сервера. Т.е. имея учётную запись на SQL Server не всегда можно получить доступ к той или иной базе данных. Нужно ещё иметь в базе пользователя с определёнными правами.

Типы данных на примере Microsoft SQL Server


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

Числовые типы данных SQL Server


Название

Описание
int Повсеместно используемый тип данных, встречающийся практически везде. В настоящее время представляет собой 32-хразрядное целое со знаком (4 байта). Охватывает дипазон целых от -2147483648 до 2147483647.
bigint Местами встречающийся тип, могущий иметь другое название (например, long). В SQL Server появился лишь начиная с 2000 версии. В настоящее время представляет собой 64-хразрядное целое со знаком (8 байт). Охватывает диапазон целых от -9223372036854775808 до 9223372036854775807.
smallint Также повсеместно распространённый тип данных. Может иметь другие названия (например, short). В настоящее время представляет собой 16-иразрядное целое со знаком (2 байта). Охватывает диапазон целых от -32768 до 32767.
tinyint 8-иразрядное целое без знака (1 байт). Охватывает диапазон целых от 0 до 255.
bit 1-оразрядное целое. Фактически, заменяет логический тип данных. Хранится как 1 байт (8 бит). Может принимать значения: либо 0, либо 1.
decimal или numeric Повсеместно распространённый тип данных. Представляет собой вещественное число с фиксированной запятой. Название может быть сокращено до dec. Синтаксис определения следующий{ [center( decimal( p , s ), [/center( где p - точность (количество десятичных разрядов числа) от 1 до 38, s - масштаб (количество десятичных разрядов после запятой) от 0 до p. В памяти значения данного типа занимают различное чило байт, зависящее от точности{ при точности 1-9 знаков - 5 байт, при точности 10-19 знаков - 9 байт, при точности 20-28 знаков - 13 байт, при точности 29-38 знаков - 17 байт. Максимальный диапазон возможных значений при decimal(38, 0) охватывает целые от -10[sup(38[/sup(+1 до 10[sup(38[/sup(-1.
float Повсеместно распространённый тип данных. Представляет собой вещественное число с плавающей запятой. Синтаксис определения следующий:
float[( p )],
где p - точность (число знаков основания) от 1 до 53. При точности 1-24 знаков для хранения числа выделяется 4 байта памяти, а при точности 25-53 знака - 8 байт. Указывать значение точности не обязательно. По умолчанию p = 53. В SQL Server 2000 заданная точность всегда приводится к максимально возможной в данном объёме памяти. Т.е. любое p от 1 до 24 рассматривается как 24. Аналогично, точность от 25 до 53 рассматривается как 53. Для значения float(53) имеется синоним double precision, а для float(24) - синоним real. При точности 53 объект типа может хранить числа в примерном диапазоне от 2,23*10-308 до 1,79*10308 любого знака. При точности 24 объект типа может хранить числа в примерном диапазоне от 1,18*10-38 до 3,40*1038 любого знака.

SQL Server 2000 автоматически преобразует любые целочисленные типы (кроме bit) к bigint при возврате значений функций. Для SQL Server 7.0 преобразование производится в int.

Денежные типы данных SQL Server


Название

Описание
money Специальный тип для хранения значений денежных единиц. Является вещественным с фиксированной запятой (4 знака после запятой). Занимает 64 разряда (8 байт). Может принимать значения от -922337203685477,5808 до 922337203685477,5807.
smallmoney Аналогично money, только 32-хразрядное (4 байта), но также 4 знака после запятой. Может принимать значения от -214748,3648 до 214748,3647.

Символьные типы данных SQL Server


Название

Описание
char Повсеместно распространённый тип. Синтаксис определения типа следующий{ [center( char[( n )(, [/center( где [i(n[/i( - число символов от 1 до 8000. Объект данного типа хранит массив 8-иразрядных символов фиксированного размера. Для n значение по умолчанию - 30. Синоним character.
varchar Повсеместно распространённый тип. Синтаксис определения типа следующий{ [center( varchar[( n )(, [/center( где n - число символов от 1 до 8000. Полностью аналогичен char за исключением того, что при хранении строк памяти отводится по длине строки, а не всегда n байт, но не более n. Синоним character varying.
text Часто встречающийся тип. Может иметь другое название. Позволяет хранить массив 8-иразрядных символов размером до 2 Гб.
nchar Синтаксис определения типа следующий:
nchar[( n )],
где n - число символов от 1 до 4000. Объект данного типа хранит массив 16-иразрядных символов фиксированного размера. Для n значение по умолчанию - 30. Символы хранятся в кодировке UNICODE UCS-2. Синонимы national character или national char.
nvarchar Синтаксис определения типа следующий:
nvarchar[( n )],
где n - число символов от 1 до 4000. Полностью аналогичен nchar за исключением того, что при хранении строк памяти отводится по длине строки, а не всегда n байт, но не более n. Синонимы national character varying и national char varying. Для nvarchar(128) в SQL Server имеется синоним (точнее, тип, определённый пользователем) sysname.
ntext Позволяет хранить массив 16-иразрядных символов размером до 2 Гб. Символы хранятся в кодировке UNICODE UCS-2.

Двоичные данные в SQL Server


Название

Описание
binary Синтаксис определения типа следующий:
binary[( n )],
где n - число байтов от 1 до 8000. Объект данного типа хранит массив байтов фиксированного размера, занимающий в памяти n+4 байт. Для n значение по умолчанию - 30.
varbinary Синтаксис определения типа следующий:
binary[( n )],
где n - число байтов от 1 до 8000. Объект данного типа хранит массив байтов переменного размера, занимающий в памяти число введённых байт плюс ещё 4 байта, но не более n+4. Может принимать значения длиной 0 байт (не обращаясь в null). Синонимом является binary varying.
image Широко распространённый тип, более известный как BLOB. Позволяет хранить массив двоичных данных размером до 2 Гб.

Типы даты и времени в SQL Server


Название

Описание
datetime Широко распространённый тип данных, позволяющий хранить дату и время. Для SQL Server принят диапазон дат от 1 января 1753 года до 31 декабря 9999 года с точностью до 1/3 сантисекунды (0,003333 секунды). Округление миллисекунд происходит следующим образом: 0,000-0,001 = 0,000; 0,002-0,004 = 0,003; 0,005-0,008 = 0,007; 0,009 = 0,010. Объект типа занимает 8 байт памяти, где первые 4 байта хранят число дней до или после начальной даты (1 января 1900), а оставшиеся 4 байта - число миллисекунд от полуночи.
smalldatetime Объкт типа может хранить дату в диапазоне от 1 января 1900 года до 6 июня 2079 года с точностью до минуты и занимает 4 байта памяти, где первые 2 байта хранят число дней после начальной даты, а оставшиеся 2 байта - число минут от полуночи.
timestamp Широко распространённый специальный тип данных, предназначенный для хранения времени обновления записи. В SQL Server 2000 и более ранних версиях реализация типа не соответствует стандарту SQL, в более поздних версиях, возможно, будет приведена к стандарту. В настоящий момент тип эквивалентен datatime или binary(8) (или varbinary(8), если допустим null). Может использоваться как тип колонки таблицы (атрибута отношения), причём такая колонка должена быть единственной и не иметь специального названия. Значение атрибута обновляется текущим временем при каждой операции вставки или обновления для записи (кортежа). Синонимом является rowversion.

Прочие типы данных SQL Server


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

Название

Описание
uniqueidentifier То же, что и GUID (global unique identifier - глобальный уникальный идентификатор). 128-иразрядное целое, задаваемое в виде "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx", где каждый символ "x" означает шестнадцатиричную цифру (0-F).
sql-variant Универсальный тип, позволяющий хранить значения всех типов, кроме text, ntext, text, image, timestamp и самого sql-variant. Особенно удобен для использования в различных определённых пользователем функциях и хранимых процедурах. Может занимать памяти до 8016 байт. Перед выполнением операций над переменной типа должен быть приведён к какому-либо базовому типу. Может быть индексирован, однако индексируются лишь первые 900 байт. Не полностью поддерживается Microsoft OLE DB Provider for ODBC (MSDASQL).
cursor Специальный тип данных, для организации построчного доступа к таблицам из хранимых процедур и пр. Не может быть типом колонки таблицы (атрибута отношения).
table Тип, позволяющий в переменной хранить таблицу (отношение). Предназначен для использования в хранимых процедурах для записи промежуточных результатов обработки. Не может быть типом колонки таблицы (атрибута отношения). Появился только в SQL Server 2000, в более ранних версиях отсутствует.

Общие замечания о типах SQL Server


Для переменных, используемых в хранимых процедурах, нельзя использовать типы данных большой ёмкости памяти (за исключением table, который по сути является указателем). Переменные имеют ограничение размера 8000 байт.

Большая часть перечисленных типов соответствует типам, описанным в стандарте SQL-92.

Заключение статьи


В этой первой статье мы определили цели цикла, познакомились с базовыми понятиями языка и объектами СУБД (на примере Microsoft SQL Server) и начали изучение языка определения данных, познакомившись с типами данных.

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