воощем вот

Создание таблиц с помощью SQL-запросов 
--------------------------------------------------------------------------------

Содержание 

Обзор 
Создание таблиц с помощью SQL 
Заключение 
Обзор 

На данном уроке мы познакомимся еще с одной возможностью создания таблиц - через посылку SQL-запросов. Как Вы, наверное, могли заметить на предыдущем уроке, Database Desktop не обладает всеми возможностями по управлению SQL-серверными базами данных. Поэтому с помощью Database Desktop удобно создавать или локальные базы данных или только простейшие SQL-серверные базы данных, состоящие из небольшого числа таблиц, не очень сильно связанных друг с другом. Если же Вам необходимо создать базу данных, состоящую из большого числа таблиц, имеющих сложные взаимосвязи, можно воспользоваться языком SQL (вообще говоря, для этих целей лучше всего использовать специализированные CASE-средства, которые позволяют в интерактивном режиме сгенерировать всю структуру базы данных и сформировать все связи; описание двух наиболее удачных CASE-средств - System Architect и S-Designor - дано в дополнительных уроках). При этом можно воспользоваться компонентом Query в Delphi, каждый раз посылая по одному SQL-запросу, а можно записать всю последовательность SQL-предложений в один так называемый скрипт и послать его на выполнение, используя, например, Windows Interactive SQL (WISQL.EXE) - интерактивное средство посылки SQL-запросов к InterBase (в том числе и локальному InterBase), входящее в поставку Delphi. Конечно, для этого нужно хорошо знать язык SQL, но, уверяю Вас, сложного в этом ничего нет! Конкретные реализации языка SQL незначительно отличаются в различных SQL-серверах, однако базовые предложения остаются одинаковыми для всех реализаций. Практика показывает, что если нет необходимости создавать таблицы во время выполнения программы, то лучше воспользоваться WISQL. 

Создание таблиц с помощью SQL 

Если Вы хотите воспользоваться компонентом TQuery, сначала поместите его на форму. После этого настройте свойство DatabaseName на нужный Вам алиас (если базы данных еще не существует, удобней создать ее в WISQL командой File|Create Database..., а затем уже настроить на нее новый алиас). После этого можно ввести SQL-предложение в свойство SQL. Для выполнения запроса, изменяющего структуру, вставляющего или обновляющего данные на сервере, нужно вызвать метод ExecSQL компонента TQuery. Для выполнения запроса, получающего данные с сервера (т.е. запроса, в котором основным является оператор SELECT), нужно вызвать метод Open компонента TQuery. Ёто связано с тем, что BDE при посылке запроса типа SELECT открывает так называемый курсор, с помощью которого осуществляется навигация по выборке данных (подробней об этом см. в уроке, посвященном TQuery). 

Как показывает опыт, проще воспользоваться утилитой WISQL. Для этого в WISQL выберите команду File|Run an ISQL Script... и выберите файл, в котором записан ваш скрипт, создающий базу данных. После нажатия кнопки УOKФ ваш скрипт будет выполнен, и в нижнее окно будет выведен протокол его работы. 

Приведем упрощенный синтаксис SQL-предложения для создания таблицы на SQL-сервере InterBase (более полный синтаксис можно посмотреть в online-справочнике по SQL, поставляемом с локальным InterBase): 

CREATE TABLE table
( [,  |  ...]);

где 

table - имя создаваемой таблицы, 
- описание поля, 
- описание ограничений и/или ключей (квадратные скобки [] означают необязательность, вертикальная черта | означает УилиФ). 

Описание поля состоит из наименования поля и типа поля (или домена - см. урок 9), а также дополнительных ограничений, накладываемых на поле: 


 = col {datatype | COMPUTED BY () | domain}
[DEFAULT {literal | NULL | USER}]
[NOT NULL] []
[COLLATE collation]

Здесь: 

col - имя поля; 
datatype - любой правильный тип SQL-сервера (для InterBase такими типами являются - см. урок 11 - SMALLINT, INTEGER, FLOAT, DOUBLE PRECISION, DECIMAL, NUMERIC, DATE, CHAR, VARCHAR, NCHAR, BLOB), символьные типы могут иметь CHARACTER SET - набор символов, определяющий язык страны. Для русского языка следует задать набор символов WIN1251; 
COMPUTED BY () - определение вычисляемого на уровне сервера поля, где - правильное SQL-выражение, возвращающее единственное значение; 
domain - имя домена (обобщенного типа), определенного в базе данных; 
DEFAULT - конструкция, определяющая значение поля по умолчанию; 
NOT NULL - конструкция, указывающая на то, что поле не может быть пустым; 
COLLATE - предложение, определяющее порядок сортировки для выбранного набора символов (для поля типа BLOB не применяется). Русский набор символов WIN1251 имеет 2 порядка сортировки - WIN1251 и PXW_CYRL. Для правильной сортировки, включающей большие буквы, следует выбрать порядок PXW_CYRL. 
Описание ограничений и/или ключей включает в себя предложения CONSTRAINT или предложения, описывающие уникальные поля, первичные, внешние ключи, а также ограничения CHECK (такие конструкции могут определяться как на уровне поля, так и на уровне таблицы в целом, если они затрагивают несколько полей): 


 = [CONSTRAINT constraint ] 

[an error occurred while processing this directive] 

Здесь 

 = {{PRIMARY KEY | UNIQUE} (col[,col...]) | 
FOREIGN KEY (col [, col ...]) REFERENCES other_table 
| CHECK ()}

 = 
{  { | ()} 
|  [NOT] BETWEEN  AND  
|  [NOT] LIKE  [ESCAPE ] 
|  [NOT] IN ( [,  ...] | 

 = {
col [] |  |  | 
| NULL | USER | RDB$DB_KEY } [COLLATE collation]
 
 = num | "string" | charsetname "string"
 = {
COUNT (* | [ALL]  | DISTINCT )
| SUM ([ALL]  | DISTINCT )
| AVG ([ALL]  | DISTINCT )
| MAX ([ALL]  | DISTINCT )
| MIN ([ALL]  | DISTINCT )
| CAST ( AS )
| UPPER ()
| GEN_ID (generator, )
}

 = {= | < | > | <= | >= | !< | !> | <> | !=}

 = выражение SELECT по одному полю, 
которое возвращает в точности одно значение.

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

Пример A: Простая таблица с конструкцией PRIMARY KEY на уровне поля 


CREATE TABLE REGION (
REGION REGION_NAME NOT NULL PRIMARY KEY,
POPULATION INTEGER NOT NULL);

Предполагается, что в базе данных определен домен REGION_NAME, например, следующим образом: 


 
CREATE DOMAIN REGION_NAME 
AS VARCHAR(40) CHARACTER SET WIN1251 COLLATE PXW_CYRL;

Пример B: Таблица с предложением UNIQUE как на уровне поля, так и на уровне таблицы 


CREATE TABLE GOODS (
MODEL SMALLINT NOT NULL UNIQUE,
NAME CHAR(10) NOT NULL, 
ITEMID INTEGER NOT NULL, CONSTRAINT MOD_UNIQUE 
UNIQUE (NAME, ITEMID));

Пример C: Таблица с определением первичного ключа, внешнего ключа и конструкции CHECK, а также символьных массивов 


CREATE TABLE JOB (
JOB_CODE JOBCODE NOT NULL,
JOB_GRADE JOBGRADE NOT NULL,
JOB_REGION REGION_NAME NOT NULL,
JOB_TITLE VARCHAR(25) CHARACTER SET WIN1251 COLLATE PXW_CYRL NOT NULL,
MIN_SALARY SALARY NOT NULL,
MAX_SALARY SALARY NOT NULL,
JOB_REQ BLOB(400,1) CHARACTER SET WIN1251,
LANGUAGE_REQ VARCHAR(15) [5],
PRIMARY KEY (JOB_CODE, JOB_GRADE, JOB_REGION),
FOREIGN KEY (JOB_REGION) REFERENCES REGION (REGION),
CHECK (MIN_SALARY < MAX_SALARY));

Данный пример создает таблицу, содержащую информацию о работах (профессиях). Типы полей основаны на доменах JOBCODE, JOBGRADE, REGION_NAME и SALARY. Определен массив LANGUAGE_REQ, состоящий из 5 элементов типа VARCHAR(15). Кроме того, введено поле JOB_REQ, имеющее тип BLOB с подтипом 1 (текстовый блоб) и размером сегмента 400. Для таблицы определен первичный ключ, состоящий из трех полей JOB_CODE, JOB_GRADE и JOB_REGION. Далее, определен внешний ключ (JOB_REGION), ссылающийся на поле REGION таблицы REGION. И, наконец, включено предложение CHECK, позволяющее производить проверку соотношения для двух полей и вызывать исключительное состояние при нарушении такого соотношения. Пример D: Таблица с вычисляемым полем 


CREATE TABLE SALARY_HISTORY (
EMP_NO EMPNO NOT NULL,
CHANGE_DATE DATE DEFAULT "NOW" NOT NULL,
UPDATER_ID VARCHAR(20) NOT NULL,
OLD_SALARY SALARY NOT NULL,
PERC_CHANGE DOUBLE PRECISION DEFAULT 0 NOT NULL
CHECK (PERC_CHANGE BETWEEN -50 AND 50),
NEW_SALARY COMPUTED BY
(OLD_SALARY + OLD_SALARY * PERC_CHANGE / 100),
PRIMARY KEY (EMP_NO, CHANGE_DATE, UPDATER_ID),
FOREIGN KEY (EMP_NO) REFERENCES EMPLOYEE (EMP_NO));

Данный пример создает таблицу, где среди других полей имеется вычисляемое (физически не существующее) поле NEW_SALARY, значение которого вычисляется по значениям двух других полей (OLD_SALARY и PERC_CHANGE). 

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

Заключение 

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

Отредактировано baitur (07-03-2008 20:36:32)

Здесь представлены ответы на наиболее типичные вопросы, возникающие при проектировании баз данных.

Содержание Разновидности СУБД
Q1: Что такое СУБД?
Q2: Чем отличаются «серверная» и «настольная» СУБД?
Q3: Что такое реляционная база данных?
Q4: Кто такой SQL-сервер?
Q5: Какой SQL-сервер лучше всего использовать?
Q6: Что такое объектно-ориентированная СУБД?
Q7: Какие объектно-ориентированные СУБД существуют в природе?
Q8: Что такое пост-реляционная база данных?
Q9: Какие еще бывают разновидности СУБД?
Сервер и клиент
Q1: Что такое сервер баз данных?
Q2: Что такое клиент?
Q3: Как клиент общается с сервером?
Q4: В чем отличие «многозвенной» архитектуры от «двухзвенной»?
Q5: Зачем нужен сервер приложений?
Q6: Чем отличается «тонкий» клиент от «толстого»?
Реляционные базы данных: Теория
Q1: Как данные хранятся в реляционной базе?
Q2: Как записи отличаются друг от друга?
Q3: Каким образом таблицы связаны между собой?
Q4: Что такое нормализация?
Q5: Имеет ли значение порядковый номер записи в таблице?
Реляционные базы данных: Особенности SQL-серверов
Q1: Что можно делать при помощи SQL?
Q2: Зачем нужны транзакции?
Q3: Что такое журнал транзакций?
Q4: Когда появляются блокировки?
Q5: Чем отличаются «версионники» от «блокировочников»?
Q6: Почему возникает deadlock?
Q7: Зачем нужны индексы?
Q8: Для чего необходим первичный ключ в таблице?
Q9: Что такое триггер?
Q10: Можно ли использовать свою функцию в SQL-запросе?
Q11: Как автоматически генерировать первичный ключ?
Q12: Какую функцию выполняет оптимизатор запросов?
Q13: Что такое представление?
Q14: Для чего нужны хранимые процедуры?
Q15: Какие типы данных есть в SQL-сервере?
Q16: Чему равен null?
Q17: Зачем нужен внешний ключ?
Q18: Существует ли стандарт SQL, общий для всех серверов?
Q19: Есть ли стандартное клиентское API для доступа к SQL-серверам?
Q20: Что такое репликация?
Реляционные базы данных: Практические вопросы
Q1: Что такое суррогатный ключ, и чем он лучше или хуже естественного ключа?
Q2: Можно ли полностью избежать дублирования информации?
Q3: Хотелось бы иметь возможность восстанавливать удаленные записи. Как это сделать?
Q4: Как организовать хранение истории изменений записей, с возможностью просмотра и отката этих изменений?
Q5: Есть объекты, которые меняются с течением времени. Нужно получать их состояния на любую дату. Каким образом для этого надо организовать хранение объектов в базе?
Q6: Каким образом можно хранить в реляционной базе иерархические объекты?
Q7: Как получить полную информацию об определенной таблице в базе (какие поля есть в таблице, их тип, размер, связи с другой таблицей и т.п.)?
Q8: Необходимо хранить в базе много картинок (файлов). Как лучше всего это сделать?
Q9: Есть запрос, который возвращает данные, отсортированные по какому-либо признаку. Можно ли получить не весь результат, а только определенную его часть, например, записи с 20 по 40?
Q10: Как из таблицы получить записи, в которых одно из полей максимально (или минимально)?
Q11: Можно ли сделать в SQL перекрестный запрос? То есть, в качестве полей для результата, брались бы не поля таблицы, а данные из нее?
Q12: Как обеспечить нумерацию записей в результате запроса?
Q13: Есть необходимость добавлять произвольные виды объектов (еще неизвестные на этапе разработке) в БД. Создавать и менять таблицы на «живой» базе не хочется. Как быть?
Дополнительно
Стандарные аббревиатуры
Литература

--------------------------------------------------------------------------------
Разновидности СУБД Q1: Что такое СУБД?
A1: СУБД — Система Управления Базами Данных (DBMS — DataBase Management System).
Программа, либо комплекс программ, предназначенных для полнофункциональной работы с данными. Как правило, включает в себя инструменты для создания и изменения структуры хранения наборов данных, а также средства доступа к хранимым данным, с возможностью их чтения, добавления, изменения и удаления. При этом, у большинства СУБД имеется собственный встроенный язык (возможно не один) для работы с данными.
Сама база данных (БД) обычно находится просто в файлах закрытого, либо открытого формата.

Q2: Чем отличаются «серверная» и «настольная» СУБД?
A2: Под настольной (desktop) обычно подразумевается СУБД, которая всегда запускается на компьютере пользователя, хотя сама база данных может находиться в другом месте. В результате несколько копий СУБД могут обращаться к одной базе данных.
Серверная (server) СУБД, как правило, запускается в на той же машине (сервере баз данных), где находятся файлы БД. Непосредственно к базе данных обращается лишь один экземпляр СУБД. Пользовательские приложения общаются только с этой СУБД через ее API, независимо от того, работают они на той же машине или на другой.
Для многопользовательских баз данных более эффективным и надежным вариантом является серверная СУБД. В ней гораздо быстрее происходит доступ к данным, и значительно проще решаются конфликты между разными пользователями.

Q3: Что такое реляционная база данных?
A3: Реляционная (relational) БД отличается способом представления информации, находящейся в ней.
Данные в такой базе хранятся в плоских таблицах. Каждая таблица имеет собственный, заранее определенный набор именованных колонок (полей). Поля таблицы обычно соответствуют атрибутам сущностей, которые необходимо хранить в базе. Количество строк (записей) в таблице неограниченно, и каждая запись соответствует отдельной сущности.
Каждая таблица должна иметь первичный ключ (ПК) — поле или набор полей, содержимое которых однозначно определяет запись в таблице и отличает ее от других.
Связь между двумя таблицами обычно образуется при добавлении в первую таблицу поля, содержащего значение первичного ключа второй таблицы.
Реляционные СУБД (РСУБД) предоставляют средства для всевозможных пересечений и объединений любых таблиц, отбора записей по разнообразным условиям, группировки и сортировки результатов.
Реляционная база данных сочетает наглядность представления информации с простотой (относительной) реализации своей концепции и является наиболее популярной структурой для хранения данных на сегодняшний день.
Достаточно подробно реляционные БД описаны в книгах [1], [2] и [3].

Q4: Кто такой SQL-сервер?
A4: Сервер для управления реляционными БД обычно называют SQL-сервером.
SQL (Structured Query Language — язык структурированных запросов) является стандартным языком для работы с реляционными БД. Кроме стандартных реляционных операций, этот язык предоставляет возможности для изменений структуры таблиц.
Различные варианты SQL используются во всех, как серверных, так и в настольных реляционных СУБД.

Q5: Какой SQL-сервер лучше всего использовать?
A5: На какой хватит денег.
Вообще-то это может сильно зависеть от постановки задачи, количества пользователей и прихотей заказчика.
Ниже приведена таблица самых распространенных SQL-серверов в порядке (примерно) убывания их возможностей:

Сервер                         Достоинства                         Недостатки 
IBM DB2 Universal Database  Самый навороченный язык запросов, лучший оптимизатор, возможность писать функции на других языках.  Высокая стоимость. 
Oracle Database  Великое множество дополнительных возможностей. Версионный сервер.  Очень высокая стоимость сервера и поддержки. 
Microsoft SQL Server  Быстро развивающийся продукт, уже вплотную приближающийся к своим более развитым конкурентам. Средняя стоимость.  Существует только для одной платформы (Win32). 
IBM Informix Dynamic Server  Довольно развитый быстрый сервер.  — 
Sybase Adaptive Server Enterprise  Достаточно развитый сервер. Средняя стоимость.  — 
Sybase Adaptive Server Anywhere  Существует под множество платформ, включая самые экзотичные. Низкая стоимость.  — 
Borland InterBase  Приличный набор возможностей. Версионный сервер. Бесплатный.  Относительно медленно работает. 
PostgreSQL  Поддерживает историческую модель. Возможность создавать свои типы данных. Бесплатный.  — 
MySQL  Быстро работает на простых запросах. Бесплатный.  Очень бедный язык запросов. Мало дополнительных возможностей.

Q6: Что такое объектно-ориентированная СУБД?
A6: В объектно-ориентированных БД (ООБД), данные представлены в виде объектов различных классов.
Как правило, имеются возможности создавать новые классы, наследовать их от уже имеющихся, задавать произвольные атрибуты и методы для классов.
Для доступа к объектам, в каждой ООБД обычно предусматривается свой собственный язык, либо расширение другого языка. Пока еще ООБД недостаточно развиты и не представляют серьезной конкуренции SQL-серверам, хотя и выглядят более предпочтительными для разработчиков.
Производители SQL-серверов тоже, в свою очередь, иногда делают попытки соорудить над реляционным ядром сервера объектно-ориентированную надстройку.
Более подробно ООБД описаны в статье [4].

Q7: Какие объектно-ориентированные СУБД существуют в природе?
A7: Достаточно распространены следующие ООБД:
Cache
FastObjects
GemStone/S
Jasmine
ObjectStore
Objectivity/DB
Versant

Q8: Что такое пост-реляционная база данных?
A8: Пост-реляционными, часто называют многомерные базы данных.
Данные в многомерных базах, представляются в виде разреженных многомерных массивов, а не плоских таблиц, как в реляционных базах. Для определенных задач, многомерные базы могут давать значительный выигрыш в быстродействии, по сравнению с реляционными.
Наиболее известные многомерные СУБД:
Cache
Teradata

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

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

Q2: Что такое клиент?
A2: Клиентом к БД, обычно называют пользовательское приложение, которое общается с сервером БД.
Модель работы, в которой клиент общается непосредственно с сервером, не используя промежуточных приложений, называется архитектурой клиент-сервер.

Q3: Как клиент общается с сервером?
A3: На пользовательских машинах, обычно устанавливаются специальные программы-шлюзы, которые, через сетевой протокол, обеспечивают связь с сервером БД. Через эти шлюзы, приложения передают запросы серверу и получают результаты.
Часто, дополнительно устанавливается библиотека (ODBC, OLE DB и т.п.), предоставляющая приложениям API для работы с сервером БД.

Q4: В чем отличие «многозвенной» архитектуры от «двухзвенной»?
A4: Довольно распространена модель работы, когда клиент обращается не непосредственно к серверу БД, а к промежуточной программе. Эта программа обычно называется сервером приложений.
Такую архитектуру называют «трехзвенной», в отличие от «двухзвенной» архитектуры клиент-сервер.

Q5: Зачем нужен сервер приложений?
A5: Сервер приложений может использоваться для многих целей.
Как правило, сервер приложений находится на отдельной машине. На него можно переложить всю функциональность программы, оставив клиенту только интерфейсную часть. Это разгрузит клиента и сервер БД от вычислений.
Также, при большом количестве пользователей, можно использовать несколько серверов приложений для распределения нагрузки.
А для ускорения доступа к часто используемым таблицам, их обычно кэшируют на сервере приложений.

Q6: Чем отличается «тонкий» клиент от «толстого»?
A6: «Тонким» клиентом обычно называют пользовательское приложение, не содержащее никакой функциональности, и предназначенное только для ввода/вывода информации. Вся обработка данных производится на сервере БД, либо на сервере приложений.
Зачастую, такой клиент изначально не содержит вообще никаких возможностей, а подгружает дополнительные модули с сервера, по мере необходимости. Обычно, в качестве «тонкого» клиента, выступают Web броузер + HTML/ASP/Java.
«Толстый» клиент содержит всю функциональность и интерфейсную часть в себе, и при любом изменении, требует замены у всех пользователей.

--------------------------------------------------------------------------------
Реляционные базы данных: Теория Q1: Как данные хранятся в реляционной базе?
A1: Данные в реляционной БД хранятся в плоских таблицах. Каждая таблица имеет собственный, заранее определенный набор именованных колонок (полей). Поля таблицы обычно соответствуют атрибутам сущностей, которые необходимо хранить в базе. Количество строк (записей) в таблице неограниченно, и каждая запись соответствует отдельной сущности.
Полное описание реляционных БД можно найти в книгах [1], [2] и [3].

Q2: Как записи отличаются друг от друга?
A2: Записи в таблице отличаются только содержимым их полей.
Две записи, в которых все поля одинаковы, считаются идентичными.
Каждая таблица должна иметь первичный ключ (ПК) — поле или набор полей, содержимое которых однозначно определяет запись в таблице и отличает ее от других.
Отсутствие первичного ключа и наличие идентичных записей в таблице обычно возможно, но крайне нежелательно.

Q3: Каким образом таблицы связаны между собой?
A3: Простейшая связь между двумя таблицами образуется при добавлении в первую таблицу поля, содержащего значение первичного ключа второй таблицы.
В общем случае, реляционные БД предоставляют очень гибкий механизм для всевозможных пересечений и объединений любых таблиц, с разнообразными условиями.
Для описания множеств, получающихся при пересечении и объединении таблиц, используется специальный математический аппарат — реляционная алгебра.

Q4: Что такое нормализация?
A4: Упорядочивание модели БД.
Грубо говоря, нормализацией называют процесс выявления отдельных независимых сущностей и вынесения их в отдельные таблицы. При этом, связи с такими таблицами, обычно организуют по их первичному ключу.
В результате нормализации, увеличивается гибкость работы с БД. Также, уменьшается содержание дублирующей информации в БД, а это сильно понижает вероятность возникновения ошибок.

Q5: Имеет ли значение порядковый номер записи в таблице?
A5: Нет.
Реляционная алгебра оперирует множествами, в которых порядковый номер элемента не несет никакой смысловой нагрузки. Записи отличатся только содержимым их полей. Две записи, в которых все поля одинаковы, будут абсолютно идентичны в реляционной БД.

--------------------------------------------------------------------------------
Реляционные базы данных: Особенности SQL-серверов Q1: Что можно делать при помощи SQL?
A1: SQL (Structured Query Language — язык структурированных запросов) является стандартным языком для работы с реляционными БД.
Разделяется на две основные части: DDL (Data Definition Language — язык определения данных) и DML (Data Manipulation Language — язык обработки данных).
DDL предоставляет средства для создания и изменения структуры хранения данных (БД, таблиц, процедур, типов данных и т.п.).
DML предназначен для чтения и изменения данных.
Основные операторы DML: select — выборка, insert — вставка, update — изменение, delete — удаление.
Также, с помощью SQL, часто реализован доступ к служебным функциям SQL-сервера (заведение пользователей, создание резервных копий БД и т.д.).
Подробное описание операторов и возможностей SQL имеется в книге [1].

Q2: Зачем нужны транзакции?
A2: Во многих случаях, необходимо проведение группы операций по изменению данных таким образом, чтобы эта группа обладала свойством атомарности (либо вся целиком выполняется, либо вся целиком не выполняется).
Такая группа операций называется транзакцией.
В SQL-серверах существуют операторы, позволяющие обозначить начало транзакции (begin transaction), ее успешное завершение (commit transaction), либо откат транзакции (rollback transaction).

Q3: Что такое журнал транзакций?
A3: Любые изменения данных, проведенные внутри транзакции, записываются в специальный журнал транзакций (transaction log).
При откате транзакции, данные восстанавливаются в прежнем виде, а записи об изменениях удаляются из журнала транзакций.

Q4: Когда появляются блокировки?
A4: При изменении данных внутри транзакции, модифицируемые записи блокируются сервером до окончания этой транзакции.
Если какая-нибудь другая транзакция пытается изменить заблокированные записи, то ее выполнение останавливается, пока не будет снята блокировка, то есть, пока не завершится первая транзакция.
Некоторые сервера имеют неприятную особенность блокировать данные не отдельными записями, а целыми страницами, которые могут содержать довольно много записей.

Q5: Чем отличаются «версионники» от «блокировочников»?
A5: Классические «блокировочники» не дают возможности разным транзакциям одновременно изменять одни и те же записи, блокируя их на время транзакции. В результате, при попытке изменить заблокированную запись, другая транзакция будет простаивать, пока не завершиться первая.
В свою очередь, «версионники» позволяют одновременно модифицировать одни и те же записи, создавая при этом разные версии одной записи.

Q6: Почему возникает deadlock?
A6: Перекрестная блокировка (deadlock) двух транзакций возникает при изменении одних и тех же записей в разном порядке.
Последовательность действий, приводящая к перекрестной блокировке:
1. Транзакция A изменяет запись X. Заблокирована X.
2. Транзакция B изменяет запись Y. Заблокирована Y.
3. Транзакция A пытается изменить запись Y. Остановлена A.
4. Транзакция B пытается изменить запись X. Остановлена B.
Сервер определяет перекрестную блокировку и откатывает одну из транзакций, возвращая ошибку соответствующему соединению. Аминь.
Чтобы не выводить ошибку пользователю, обломанное соединение должно молча повторить транзакцию.

Q7: Зачем нужны индексы?
A7: Для ускорения операций выборки данных.
При поиске полей с определенным значением, сервер вынужден перебирать все записи в таблице. В этом случае, время поиска линейно зависит от размера таблицы.
Индекс по полю, обычно представляющий собой бинарное дерево, дает возможность резко сократить время поиска, превратив эту зависимость в логарифмическую.
Однако, наличие индексов в таблице, замедляет операции модификации данных.

Q8: Для чего необходим первичный ключ в таблице?
A8: Первичный ключ (ПК) — поле или набор полей, содержимое которых однозначно определяет запись в таблице и отличает ее от других. Служит для однозначной идентификации записей и в таблице может быть только один.
Обычно, при определении первичного ключа, по нему автоматически создается уникальный индекс.

Q9: Что такое триггер?
A9: Триггер — процедура, выполняемая сервером автоматически при модификации данных в таблице.
В основном, триггеры используются для поддержания целостности дублирующей информации в денормализованной БД.

Q10: Можно ли использовать свою функцию в SQL-запросе?
A10: Можно, практически во всех современных SQL-серверах. Различия только в синтаксисе определения и вызова функции.
Кроме того, некоторые сервера позволяют использовать функции, написанные на других языках (не SQL).

Q11: Как автоматически генерировать первичный ключ?
A11: Обычно, в SQL-серверах можно указать одно из числовых полей как автоматически нумеруемое, причем синтаксис определения сильно зависит от разновидности сервера.
При вставке новой записи, такому полю присваивается значение поля предыдущей записи + заданное приращение. Как правило, значение подобного поля нельзя явно изменять с помощью DML.

Q12: Какую функцию выполняет оптимизатор запросов?
A12: Оптимизатор предназначен для определения самого быстрого способа выполнения запроса (в основном для выборки данных), при участии в нем нескольких таблиц.
В простейшем случае, задача оптимизатора заключается в правильном использовании индексов при соединении таблиц и применении дополнительных условий. Иногда, можно задать в запросе дополнительную информацию (hint) для оптимизатора.

Q13: Что такое представление?
A13: Представление (view) — это запрос на выборку, хранящийся на сервере, как отдельный объект.
Так как, результат этого запроса можно рассматривать в качестве таблицы, представление допускается использовать в других запросах, также как любую обычную таблицу.
Материализованное представление хранится на сервере в виде таблицы, которая автоматически обновляется при изменении данных, имеющих отношение к этому представлению.

Q14: Для чего нужны хранимые процедуры?
A14: Хранимые процедуры (SP — Stored Procedure) представляют собой последовательность команд на расширениях SQL, либо на других языках, поддерживаемых сервером. Могут принимать параметры и возвращать значение заданного типа.
Часто используются для выполнения операций, напрямую связанных с логикой задачи, для которой проектировалась БД.
Иногда, используются вместе с представлениями, для обеспечения безопасности БД (все изменения через SP, все выборки через view).

Q15: Какие типы данных есть в SQL-сервере?
A15: Обычно, для полей в таблицах могут использоваться только самые простые типы: числа (целые и дробные), строки (сильно ограниченные по длине), дата (и время), бинарные данные большого размера (для текста, графики и т.п.).
В некоторых серверах допускается использование массивов и самодельных структур.

Q16: Чему равен null?
A16: А хрен его знает. И это совсем не шутка, а его фактическое определение.
Null не является пустым значением. Поэтому null + 1 = null, а не 1.
Использование null означает переход от двузначной логики (true/false) к трехзначной (true/false/unknown), которая является более сложной и несколько непривычной.
С другой стороны, логические операторы SQL обычно приспособлены для работы только с двузначной логикой. Поэтому использовать null для значений полей следует очень осторожно и только в тех случаях, когда без него никак нельзя обойтись. Поведение null хорошо описано в статье [5].

Q17: Зачем нужен внешний ключ?
A17: Внешний ключ (FK — Foreign Key) используется для создания жесткой связи (многие к одному) между двумя таблицами.
Внешний ключ задается только в том случае, если в первой таблице есть поле, содержащее значение первичного ключа из второй таблицы.
При изменении значения первичного ключа во второй таблице, могут быть изменены все соответствующие значения связанного поля в первой таблице.
При удалении записи с определенным первичным ключом из второй таблице, могут быть удалены все записи с соответствующим значением связанного поля в первой таблице.
Обычно, при определении внешнего ключа, по нему автоматически создается индекс, который используется в запросах при объединении этих двух таблиц.

Q18: Существует ли стандарт SQL, общий для всех серверов?
A18: Вообще-то он есть (ANSI SQL 92), только вот поддержка его у производителей СУБД сильно хромает.
Причем дело с эти настолько плохо, что может различаться даже синтаксис основных операторов DML, не говоря уже о функциях, триггерах и прочих особенностях SQL. Поэтому о совместимости говорить сложно, хотя кое-какие сдвиги в этом направлении имеются.
Наиболее близки к стандарту: Borland InterBase, Microsoft SQL Server, IBM DB2 Universal Database.

Q19: Есть ли стандартное клиентское API для доступа к SQL-серверам?
A19: Наиболее распространенным клиентским API является ODBC (Open DataBase Connectivity).
ODBC-драйвера существуют на многих платформах и почти для всех SQL-серверов.

Q20: Что такое репликация?
A20: Репликацией обычно называют процесс синхронизации данных между несколькими БД.
Наиболее развитые SQL-сервера содержат встроенные средства репликации. Для остальных могут быть использованы продукты сторонних фирм.
Одностороняя репликация подразумевает изменение данных только в одной базе, с последующей передачей изменений на остальные. Соответственно, довольно проста в реализации и надежна в работе.
Двустороняя репликация предоставляет гораздо более мощный инструмент распределенной работы между SQL-серверами. Плата за это — сложность и большая вероятность конфликтов при работе.

--------------------------------------------------------------------------------
Реляционные базы данных: Практические вопросы Q1: Что такое суррогатный ключ, и чем он лучше или хуже естественного ключа?
A1: Естественным ключом (ЕК) обычно называют первичный ключ, который является смысловым атрибутом (либо набором атрибутов) сущности. Как и любой другой атрибут, ЕК подвержен изменениям, а его уникальность часто бывает притянута за уши.
Суррогатным ключом (СК), как правило, называется уникальное автонумеруемое целочисленное поле в таблице, которое и является первичным ключом. СК гораздо компактнее, чем ЕК, везде занимает одно поле одинакового типа, всегда неизменен и уникален.
Более обоснованное сравнение СК и ЕК производится в статье [6].

Q2: Можно ли полностью избежать дублирования информации?
A2: Только в теории.
Полностью нормализованная БД хороша для хранения данных. Но, на практике постоянно встречаются структуры, получение информации из которых, слишком сложно и медленно (а иногда и невозможно с помощью лишь оператора select).
Дублирование информации в том или ином виде обычно необходимо для получения результатов выборки простыми запросами за приемлемое время.

Q3: Хотелось бы иметь возможность восстанавливать удаленные записи. Как это сделать?
A3: А не надо их удалять, тогда и проблемы не будет.
Проще всего держать в таблице дополнительное поле Deleted (default = false), а вместо удаления записи устанавливать Deleted = true. Для выборки можно использовать представление с внутреннем условием:

Deleted = false
Возможен и более сложный вариант: два дополнительных поля DateBegin (defaul = текущая_дата) и DateEnd (defaul = максимальная_дата). Вместо удаления записи необходимо установить DateEnd = текущая_дата, а условие для выборки будет:

текущая_дата beetwen DateBegin and DateEnd
Во втором варианте есть возможность, не только иметь даты заведения/удаления записей, но и получать наборы записей на любую определенную дату с условием:

заданная_дата beetwen DateBegin and DateEnd
Q4: Как организовать хранение истории изменений записей, с возможностью просмотра и отката этих изменений?
A4: Самый простой вариант — это создать таблицу-журнал, в которую, при изменении записи, помещать следующую информацию: дату изменения, имя (Id) пользователя, имя (Id) изменяемой таблицы, ПК записи, имя (Id) изменяемого поля и предыдущее значение поля, преобразованное в строку.
Запись в журнал можно делать либо триггерами, либо из приложения.
Откат изменений следует производить специальной процедурой, которая должна восстановить значения полей на заданную дату и удалить соответствующие записи из журнала.
Один из вариантов журнала имеется в статье [9].

Q5: Есть объекты, которые меняются с течением времени. Нужно получать их состояния на любую дату. Каким образом для этого надо организовать хранение объектов в базе?
A5: Можно использовать сервер, который поддерживает историческую модель, например, PostgreSQL Server.
В ином случае, можно, в дополнение к основной таблице, создать аналогичную таблицу истории, с двумя дополнительными полями: DateBegin и DateEnd.
При вставке записи в основную таблицу, необходимо добавить такую же запись в таблицу истории, где DateBegin = текущая_дата, DateEnd = максимальная_дата.
При модификации записи в основной таблице, нужно вставить измененную запись в таблицу истории, где DateBegin = текущая_дата, DateEnd = максимальная_дата. При этом у предыдущей соответствующей записи в таблице истории необходимо установить DateEnd = текущая_дата.
При удалении записи в основной таблице, у последней соответствующей записи в таблице истории надо установить DateEnd = текущая_дата.
Выборка из основной таблицы производится обычным образом, а из таблицы истории с условием:

заданная_дата beetwen DateBegin and DateEnd
Сходные варианты представления истории рассматриваются в статье [10].

Q6: Каким образом можно хранить в реляционной базе иерархические объекты?
A6: В таблицу, которую надо сделать иерархической, надо добавить поле Parent, содержащее значение ПК родительской записи.
Далее, все зависит от задачи и возможностей сервера. Если нет необходимости углубляться в иерархию более чем на один уровень, либо сервер поддерживает рекурсивные запросы, то этого вполне хватит.
Иначе, придется использовать дополнительные поля, либо вспомогательную таблицу.
Подробно способы представления деревьев в реляционных БД описаны в статьях [7] и [8].

Q7: Как получить полную информацию об определенной таблице в базе (какие поля есть в таблице, их тип, размер, связи с другой таблицей и т.п.)?
A7: Вообще-то, в большинстве SQL-серверов есть системный каталог — набор стандартных таблиц, содержащих информацию обо всех объектах сервера и БД.
Правда, таблицы эти предназначены не для разработчика, а для внутреннего использования самим сервером, поэтому найти там что-либо весьма проблематично. При этом, структура их может меняться даже в разных версиях одного сервера.
Поэтому, желательно создать в БД собственный системный каталог, полностью описывающий структуру базы и взаимосвязи ее объектов. Такой каталог можно переносить на другой сервер вместе с БД, не заботясь о совместимости, а информацию из него можно легко использовать в приложении для каких-либо действий (вплоть до автоматической генерации самого приложения).
Пример системного каталога приведен в статье [9].

Q8: Необходимо хранить в базе много картинок (файлов). Как лучше всего это сделать?
A8: Проще всего файлы держать отдельно в выделенной директории на сервере, а в базе хранить только их имена. Единственной проблемой в таком случае будет раздача прав на эту директорию.
Кроме того, в SQL-серверах есть возможность определять бинарные поля большого размера, которые можно использовать для хранения разных видов файлов. Чтобы не замедлять работу с обычными данными, рекомендуется создавать отдельно таблицу, содержащую такие поля. Также, при использовании больших полей, можно обнаружить разнообразные ограничения на сервере и клиенте.

Q9: Есть запрос, который возвращает данные, отсортированные по какому-либо признаку. Можно ли получить не весь результат, а только определенную его часть, например, записи с 20 по 40?
A9: Такие действия проще всего выполнять на клиенте.
Если надо на сервере, то лучше использовать предназначенные для этого возможности оператора выборки:

1. select top n * from t order by a
2. select * from t order by a limit n,m

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

select * from t as t_outer where
( select count( * ) from t as t_inner where t_inner.a <= t_outer.a ) between n and m
order by a

В крайнем случае, можно выбирать данные во временную таблицу с дополнительным автонумеруемым полем RowNumber, а из нее затем делать выборку:

select ... where RowNumber >= n and RowNumber <= m

Q10: Как из таблицы получить записи, в которых одно из полей максимально (или минимально)?
A10: Самый правильный способ — это использовать вложенный запрос, в котором ищется максимальное значение поля. Затем найденное значение используется в условии основного запроса.

select *
from t
where a = ( select max( a ) from t )

Если максимальное значение необходимо искать не во всей таблице, а по определенному полю, то запрос будет следующего вида:

select *
from t as t_outer
where a = ( select max( t_inner.a ) from t as t_inner where t_inner.b = t_outer.b )

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

Q11: Можно ли сделать в SQL перекрестный запрос? То есть, в качестве полей для результата, брались бы не поля таблицы, а данные из нее?
A11: Подобные запросы обычно умеют делать разнообразные генераторы отчетов. SQL же не предусматривает такой возможности.
Хотя, при желании, можно сделать процедуру, использующую динамический SQL. В ней придется составлять запрос на основе данных из таблицы, а затем выполнять его.

Q12: Как обеспечить нумерацию записей в результате запроса?
A12: Нумерация записей очень легко делается на любом клиенте.
Чтобы в запросе получить поле, соответствующее номеру записи, можно использовать подзапрос, возвращающий количество записей, меньших либо равных (сравнение — по полю сортировки) текущей записи:

select *,
( select count( * ) from t as t_inner where t_inner.a <= t_outer.a ) as RowNumber
from t as t_outer
order by a

Если поле сортировки не уникально, то в подзапрос необходимо добавить дополнительное условие сравнения (например, по первичному ключу) для обеспечения уникальности значений, возвращаемых подзапросом.

Q13: Есть необходимость добавлять произвольные виды объектов (еще неизвестные на этапе разработке) в БД. Создавать и менять таблицы на «живой» базе не хочется. Как быть?
A13: Можно вообще хранить все дополнительные объекты в одной таблице, содержащей идентификатор объекта, идентификатор атрибута и само значение атрибута, преобразованное в строку.
Чтобы не было мучительно больно при получении результатов запросов, лучше все-таки использовать несколько таблиц — отдельно для каждого типа данных.
Более серьезный вариант универсального хранилища объектов рассмотрен в статье [11].
PS: А чем была плоха идея насчет создания/изменения таблиц?

--------------------------------------------------------------------------------
Стандарные аббревиатуры Английские
API — Application Program Interface
DB — DataBase
DBMS — DataBase Management System
DDL — Data Definition Language
DML — Data Manipulation Language
FK — Foreign Key
ODBC — Open DataBase Connectivity
PK — Primary Key
RDBMS — Relational DataBase Management System
SP — Stored Procedure
SQL — Structured Query Language

Русские
БД — База Данных
ЕК — Естественный Ключ
ООБД — Объектно-Ориентированная База Данных
ООСУБД — Объектно-Ориентированная Система Управления Базами Данных
ПК — Первичный Ключ
РСУБД — Реляционная Система Управления Базами Данных
СУБД — Система Управления Базами Данных
СК — Суррогатный Ключ