36590

Язык SQL

Книга

Информатика, кибернетика и программирование

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

Русский

2013-09-22

499.5 KB

20 чел.

 66

язык SQL

интерпретация на язык машинных кодов, запуск программы обработки

машина данных

информационные потребности

пользователь

БД

разработка и запуск программы обработки

программист

информационные потребности

пользователь

БД

Краткое содержание

[1]
Введение

[1.1] О языке SQL

[1.2] Обозначения

[1.3] Некоторые общие замечания по синтаксису SQL

[1.4] Основной пример

[2] . Запросы на выборку
данных из таблиц

[2.1] .. Простейший вариант запроса

[2.2] .. Условия отбора записей WHERE

[2.3] .. Логические связки AND OR NOT в WHERE

[2.4] .. Оператор IN в WHERE

[2.5] .. Оператор BETWEEN в WHERE

[2.6] .. Оператор LIKE в WHERE

[2.7] .. Значение NULL в WHERE

[2.8] .. Оператор DISTINCT в SELECT

[2.9] .. Переименование полей

[2.10] .. Выражения в SELECT

[2.11] ..Упорядочение выходных данных. ORDER BY.

[2.12] .. Итоги по первой главе.

[3] . Агрегатные функции

[3.1] .. Использование агрегатных функций.

[3.2] .. Название поля агрегатной функции.

[3.3] .. Использование * в COUNT.

[3.4] .. Использование DISTINCT в агрегатных функциях.

[3.5] .. Использование выражений в агрегатных функциях.

[3.6] .. Предложение GROUP BY

[3.7] .. Использование WHERE в запросах с агрегатными функциями

[3.8] .. Предложение HAVING

[4] . Запросы к нескольким таблицам

[4.1] .. Эквисоединения

[4.2] .. Соединение таблицы со своей копией

[4.3] .. JOIN-соединения

[4.4] .. Объединение запросов. Команда UNION.

[5] . Использование вложенных запросов

[5.1] ..Как выполняются подзапросы.

[5.2] .. Использование агрегатных функций в подзапросах.

[5.3] .. Оператор EXISTS.

[6] . Команды DML. Добавление записей, удаление и редактирование.

[6.1] .. Добавление записей. Команда INSERT.

[6.2] .. Добавление множества записей. Вставка результатов запроса.

[6.3] .. Удаление записей. Команда DELETE.

[6.4] .. Редактирование записей. Команда UPDATE.

[7] . Команды DDL. Определение структуры БД

[7.1] .. Создание базы данных

[7.2] ..Типы данных

[7.3] .. Простые варианты команд создания таблиц и изменения их структуры

[7.4] .. Добавление ограничений на таблицу

[7.5] .. Определение домена

[7.6] .. Изменение структуры домена

[7.7] .. Создание таблиц

[7.8] .. Изменение структуры таблицы

[8] . Определение прав доступа к данным

[9] . Некоторые особенности SQL в Interbase

[9.1] .. Генераторы

[9.2] .. Хранимые процедуры

[9.3] .. Триггеры

[9.4]
Представления

[10]
Сокращения

[11] Термины

[12] Литература

Подробное содержание

[1]
Введение

[1.1] О языке SQL

[1.2] Обозначения

[1.3] Некоторые общие замечания по синтаксису SQL

[1.4] Основной пример

[2] . Запросы на выборку
данных из таблиц

[2.1] .. Простейший вариант запроса

[2.2] .. Условия отбора записей WHERE

[2.3] .. Логические связки AND OR NOT в WHERE

[2.4] .. Оператор IN в WHERE

[2.5] .. Оператор BETWEEN в WHERE

[2.6] .. Оператор LIKE в WHERE

[2.7] .. Значение NULL в WHERE

[2.8] .. Оператор DISTINCT в SELECT

[2.9] .. Переименование полей

[2.10] .. Выражения в SELECT

[2.11] ..Упорядочение выходных данных. ORDER BY.

[2.12] .. Итоги по первой главе.

[3] . Агрегатные функции

[3.1] .. Использование агрегатных функций.

[3.2] .. Название поля агрегатной функции.

[3.3] .. Использование * в COUNT.

[3.4] .. Использование DISTINCT в агрегатных функциях.

[3.5] .. Использование выражений в агрегатных функциях.

[3.6] .. Предложение GROUP BY

[3.7] .. Использование WHERE в запросах с агрегатными функциями

[3.8] .. Предложение HAVING

[4] . Запросы к нескольким таблицам

[4.1] .. Эквисоединения

[4.2] .. Соединение таблицы со своей копией

[4.3] .. JOIN-соединения

[4.4] .. Объединение запросов. Команда UNION.

[5] . Использование вложенных запросов

[5.1] ..Как выполняются подзапросы.

[5.2] .. Использование агрегатных функций в подзапросах.

[5.3] .. Оператор EXISTS.

[6] . Команды DML. Добавление записей, удаление и редактирование.

[6.1] .. Добавление записей. Команда INSERT.

[6.2] .. Добавление множества записей. Вставка результатов запроса.

[6.3] .. Удаление записей. Команда DELETE.

[6.4] .. Редактирование записей. Команда UPDATE.

[7] . Команды DDL. Определение структуры БД

[7.1] .. Создание базы данных

[7.2] ..Типы данных

[7.3] .. Простые варианты команд создания таблиц и изменения их структуры

[7.4] .. Добавление ограничений на таблицу

[7.5] .. Определение домена

[7.6] .. Изменение структуры домена

[7.7] .. Создание таблиц

[7.8] .. Изменение структуры таблицы

[8] . Определение прав доступа к данным

[9] . Некоторые особенности SQL в Interbase

[9.1] .. Генераторы

[9.2] .. Хранимые процедуры

[9.3] .. Триггеры

[9.4]
Представления

[10]
Сокращения

[11] Термины

[12] Литература


Введение

  1.  О языке SQL

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

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

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

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

Язык SQL (Structured Query Language, структурированный язык запросов) был создан в 1974г. на основании теоретических идей Кодда. Благодаря удачной концептуальной основе быстро стал стандартным языком, используемым в реляционных СУБД. В 1986г. был признан Американским национальным институтом стандартов (ANSI) и Международной организацией по стандартам (ISO) в качестве стандартного языка описания и обработки данных в реляционных базах данных.

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

В качестве альтернативы языку SQL некоторое время выступал еще один реляционный язык QBE (Query By Example, язык запросов по образцу). В дальнейшем он уступил первенство языку SQL, но на его основе были впоследствии построены многочисленные визуальные конструкторы запросов к БД. Большинство из этих визуальных конструкторов ныне предполагают возможность в любой момент получить текст SQL на основе визуального конструирования, и, наоборот, по тексту SQL получить визуальный вид запроса.

Пример. СУБД Microsoft Access. Один и тот же запрос в режиме конструктора запросов по образцу и (ниже) в режиме SQL.

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

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

Язык SQL включает в себя три раздела: DDL (Data Definition Language, язык определения данных), DML (Data Manipulation Language, язык манипуляции данными) и собственно язык запросов. Впрочем, иногда еще отдельно выделяют язык безопасности данных, то есть те команды DDL, которые позволяют разграничить права отдельных пользователей и групп пользователей в доступе к объектам БД.

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

Мы в настоящем пособии приводим некоторый урезанный вариант SQL, не упоминая все второстепенные детали, имеющиеся в SQL. При этом мы ориентируемся на тот диалект, который используется в СУБД InterBase. Выбор данной СУБД обусловлен несколькими причинами: 1) наличие бесплатного аналога СУБД – СУБД FireBird разрабатывается группой программистов, работа которых оплачивается из благотворительного фонда и является бесплатной для всех пользователей; 2) наличие отличной инструментальной программы для работы с данной СУБД – программа IBExpert разработана российскими программистами и бесплатна для граждан РФ; 3) СУБД Interbase является собственностью Borland  и поэтому отлично интегрирована в среду программирования Delphi; 4) СУБД InterBase является, можно сказать, классической реляционной СУБД – в ней есть практически все, что «положено» иметь реляционным СУБД, и,  в то же время, нет «ничего лишнего», что делает ее удобным примером для преподавания различных аспектов теории реляционных БД.

Обозначения

В тексте мы выделяем следующие фрагменты:

?   обозначает проблему, которую следует решить

!   обозначает, как эта проблема решается

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

При объяснении синтаксиса команд SQL мы будем использовать традиционные для информатики соглашения. Квадратные скобки [ ] указывают, что этот элемент может и отсутствовать. Фигурные скобки { } указывают, что следует выбрать один вариант из тех, которые перечислены в этих скобках, при этом возможные варианты разделены вертикальной чертой |. Многоточие … означает возможность многократного повторения описанных элементов. Угловые скобки <> указывают, что в этой позиции должна быть вписана синтаксически правильная конструкция, смысл которой расшифровывается далее по тексту.

Некоторые общие замечания по синтаксису SQL

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

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

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

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

В некоторых книгах команды SQL заканчиваются символом ; <точка с запятой>. Однако на самом деле этот символ необходим только, если вы взаимодействуете с СУБД с помощью такой инструментальной программы, которая позволяет выполнить сразу несколько последовательных команд SQL. Такие тексты называются «скриптами на языке SQL», а инструментальные программы (или один из инструментов в «большой» программе для работы с  СУБД), позволяющие их записывать и выполнять – «редакторами скриптов».   При выполнении одиночной команды символ  <точка с запятой> в конце команды необязателен.

Основной пример

Для описания большинства примеров мы будем использовать базу данных из двух таблиц - Fakultet и Spisok.

Таблица Fakultet 

Описание: содержит перечень факультетов, ее содержание указано ниже:

Структура таблицы:

Поле

Назначение поля

shortname

Краткий код факультета, строковое поле длины не более 10 символов, первичный ключ

fullname

Полное название факультета, строковое поле с максимальной длиной строки 100 символов

dekan

Фамилия имя отчество декана, строковое поле с максимальной длиной строки 100 символов

Данные в таблице:

shortname

fullname

dekan

ЕГФ

Естественно-географический факультет

ФИиП

Факультет истории и права

ФМФ

Физико-математический факультет

Старовикова И.В.

ФФ

Филологический факультет

Таблица Spisok

Описание: содержит список студентов вуза (в учебных целях для пояснения некоторых подробностей SQL в таблице содержится только малая часть реальных данных)

Структура таблицы:

Поле

Назначение поля

id

Числовой первичный ключ

fam

Фамилия студента, строковое поле с максимальной длиной строки 30 символов

im

Имя студента, строковое поле с максимальной длиной строки 30 символов

ot

Отчество студента, строковое поле с максимальной длиной строки 30 символов

fakultet_kod

Краткий код факультета, строковое поле с максимальной длиной строки 10 символов, вторичный ключ, связывающий данную таблицу с таблицей Fakultet

kurs

Курс (год обучения), числовое поле

date_r

Дата рождения, поле типа ДАТА

Подразумевается, что таблицы связаны между собой через поля связи fakultet_kod (таблица spisok) и shortname (таблица fakultet). Например, чтобы узнать полное название факультета, на котором учится студент, нужно прочитать в  таблице spisok в поле fakultet_kod краткий код факультета, а затем искать в таблице fakultet такую запись, где поле shortname  заполнено таким же значением. Обычно в СУБД устанавливают связь «явно», добавляя в таблицу  spisok ограничение типа «вторичный ключ» (FOREIGN KEY), однако для изучения основ SQL наличие вторичного ключа не является необходимым.

Данные в таблице spisok:

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

INSERT INTO spisok (id, fam, im, ot, date_r, fakultet_kod) VALUES (1001, 'Петров', 'Петр', 'Петрович', '1990-01-01', 'ФМФ');

INSERT INTO spisok (id, fam, im, ot, date_r, fakultet_kod) VALUES (1002, 'Сергеев', 'Сергей', 'Сергеевич', '1990-02-02', 'ЕГФ');

INSERT INTO spisok (id, fam, im, ot, date_r, fakultet_kod) VALUES (1003, 'Викторов', 'Виктор', 'Викторович', '1990-03-03', 'ФМФ');

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

. Запросы на выборку
данных из таблиц

.. Простейший вариант запроса

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

!  Простейший для понимания вариант запроса на языке SQL для выборки данных из одной таблицы в SQL выглядит так:

SELECT <список полей> FROM <имя таблицы>

Пример ..

SELECT fam, im, ot, kurs FROM spisok

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

Напоминаем, что запрос можно записывать и в несколько строк:

SELECT fam, im, ot, kurs

FROM spisok

Первый вариант обеспечивает экономию бумаги, второй вариант дает лучшую «читаемость».

Пример .

SELECT shortname FROM fakultet 

Данный запрос выбирает из таблицы fakultet только краткие названия факультетов.

Правила:

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

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

Пример .

Два запроса SELECT kurs, fam, im, ot FROM spisok и SELECT fam, im, ot, kurs FROM spisok отличаются только тем, что в выходных данных будет другой порядок следования полей.

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

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

Пример .

SELECT * FROM spisok 

Данный запрос выведет все поля из таблицы spisok. Фактически он выведет всю информацию, которая имеется в этой таблице.

.. Условия отбора записей WHERE

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

!   Использование условий выбора легко понять из следующих примеров:

Пример .

SELECT fam, im, ot, kurs

FROM spisok

WHERE fakultet_ kod=’ФМФ

Данный запрос выведет фамилии, имена, отчества и курс студентов только физико-математического факультета.

Пример .

SELECT fam, im, ot, kurs

FROM spisok 

WHERE kurs=2

Данный запрос выведет фамилии, имена, отчества и курс только студентов второго курса.

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

В качестве знаков сравнения можно использовать =, <, >, <= (меньше или равно), >= (больше или равно), <> (не равно).

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

Пример .

SELECT fam, im, ot

FROM spisok

WHERE sum_vneseno<sum_oplata

Такой запрос выбирает клиентов, которые внесли сумму оплаты не полностью. Данный пример надуманный, но вполне правдоподобен.

  Условие отбора записей WHERE не является обязательным в SQL-запросе. Если оно используется, то должно располагаться после FROM.

√  Условие отбора записей WHERE должно оцениваться в пределах одной записи.  В дальнейшем мы увидим и более сложные примеры запросов,  где условие оценивается в терминах группы записей или всей таблицы.

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

.. Логические связки AND OR NOT в WHERE

?  Иногда может потребоваться более сложное условие отбора записей.

!  Пример .

SELECT fam, im, ot, kurs, fakultet_kod

FROM spisok

WHERE fakultet_kod=’ФМФ’ OR fakultet_kod=’ЕГФ’

Такой запрос выбирает студентов двух факультетов.

√  Логические связки AND OR NOT используются для формирования сложных условий из простых.

√  Использование в условиях логических связок  AND OR и NOT достаточно традиционно, поэтому нет смысла описывать их подробно. Понятно также, что для указания приоритета можно использовать круглые скобки.

 Стоит заметить, что в SQL знаки сравнения =, >, < и другие имеют больший приоритет в сравнении с логическими связками, поэтому при записи условий в SQL удается обходиться меньшим количеством круглых скобок для обозначения приоритета (в сравнении, например, с языком программирования Паскаль). Например, в Паскале соответствующее условие обязательно пришлось бы записывать со скобками (fakultet_kod=’ФМФ’) OR (fakultet_kod=’ЕГФ’). Конечно, лишние скобки не изменят результат запроса. Многие программы автоматического построения SQL-запросов грешат тем, что «не экономят» скобки.

.. Оператор IN в WHERE

В SQL имеются нетрадиционные операторы, применяемые в условиях WHERE.

?  Имеется средство для более краткой записи нескольких возможных значений некоторого поля таблицы.

!  Запишем два запроса, имеющие один и тот же смысл.

Пример .

SELECT fam, im, ot

FROM spisok

WHERE fakultet_kod=’ФМФ’ OR fakultet_kod=’ЕГФ’

Пример .

SELECT fam, im, ot

FROM spisok

WHERE fakultet_kod IN (’ФМФ’,’ЕГФ’)

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

.. Оператор BETWEEN в WHERE

!  Приведем пример еще одного нетрадиционного оператора, применяемого в условиях. Запишем два запроса, имеющие один и тот же смысл.

Пример .

SELECT fam, im, ot

FROM spisok

WHERE kurs>=1 AND kurs<=3

Пример .

SELECT fam, im, ot

FROM spisok

WHERE kurs BETWEEN 1 AND 3

  Оператор BETWEEN проверяет принадлежность значения поля некоторому диапазону.

  На взгляд автора ценность оператора BETWEEN незначительна, вместо него вполне можно обойтись первым вариантом.

.. Оператор LIKE в WHERE

?  При работе с текстовой информацией часто возникает потребность найти значение, «похожее» на некоторый образец. Например, часто путаница происходит с женскими именами Наталия и Наталья, которые, по сути, есть одно и то же, но различие в их написании часто создает проблемы при оформлении документов.

!  Пример .

SELECT fam, im, ot

FROM spisok

WHERE im LIKE ‘Натал_я’

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

Пример .

SELECT fam, im, ot

FROM spisok

WHERE fam LIKE ‘%щ%’ OR fam LIKE ‘%Щ%’

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

  Оператор LIKE очень полезен при работе с полями текстового типа. Он позволяет проверить соответствие значения поля некоторому шаблону. В шаблоне разрешается использовать обычные символы и два специальных символа: символ подчеркивания _ обозначает, что в этой позиции может находиться любой символ, но обязательно ровно один символ. Символ процента % обозначает, что в этой позиции может находиться любое количество символов (в том числе нуль символов). Все другие символы обозначают «сами себя». Два этих специальных символа можно комбинировать и повторять в шаблоне несколько раз наряду с обычными символами.

.. Значение NULL в WHERE

?   Требуется выбрать студентов, у которых еще не заполнена дата рождения.

!   Пример .

SELECT fam, im, ot

FROM spisok

WHERE date_r IS NULL

  NULL означает неопределенное значение. Другими словами, никакая информация в это поле еще не внесена. Хотя можно заметить, что отсутствие информации это тоже информация. Например, если у студента поле ДАТА ОТЧИСЛЕНИЯ не заполнено …

  Некоторая неприятность состоит в том, что SQL различает в условиях два случая – значение NULL и значение «пустая строка». Второй вариант проверяется примерно так: ot=’’  (поясняем – записано два апострофа, стоящие рядом, без пробела между ними).

  Противоположный вариант можно записать date_r IS NOT NULL или NOT date_r IS NULL или date_r NOT IS NULL. Очевидно, такое разнообразие вариантов сделано для того, чтобы рядовой пользователь, на которого первоначально был рассчитан SQL, не утруждал себя подробностями правил синтаксиса языка.

.. Оператор DISTINCT в SELECT

?   Требуется выбрать те факультеты, для которых в таблице spisok имеются студенты.

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

SELECT shortname FROM fakultet 

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

Не очень хороший вариант:

SELECT fakultet_kod FROM spisok 

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

!   Пример .

SELECT DISTINCT fakultet_kod FROM spisok

   Служебное слово DISTINCT подавляет повторяющиеся значения, так что каждое из них будет повторяться только один раз.

.. Переименование полей

?   Часто требуется назвать выводимое поле не так, как оно названо в таблице.

!   Пример .

SELECT shortname AS kod_fakulteta

FROM fakultet

В выводимых данных поле КРАТКИЙ КОД ФАКУЛЬТЕТА, которое в таблице имеет имя short name, будет фигурировать под именем kod_fakulteta.

.. Выражения в SELECT

?   Представим себе, что в  таблице Spisok есть два поля: oklad и doplata, смысл которых ясен из названия. Требуется, чтобы запрос выводил бы общую денежную сумму, причитающуюся работнику.

!   Пример .

SELECT fam,im,ot, oklad+doplata FROM spisok

     В запросах можно указывать не только настоящее поле из таблицы, но и выражение из полей данной записи и констант. Естественно, выражение должно быть построено синтаксически правильно, например, oklad+fam явно не годится, поскольку смешаны числовое и строковое поля. С числовыми выражениями ситуация достаточно традиционна и вряд ли требует дополнительно объяснения. Однако выражения для текстовых полей строятся в различных СУБД по-разному. Тем более в разных СУБД сильно различаются перечни встроенных функций, которые также можно использовать в выражениях. Это следует иметь в виду при переносе информационной системы в другую СУБД.

!   Пример .. Выражение для текстовых полей в СУБД InterBase.

SELECT fam||’ ‘||im||’ ‘||ot   FROM spisok

Этот запроса выдает в качестве выходных данных записи, которые состоят из объединенных в единое целое фамилии, имени, отчества с пробелами-разделителями. Именно два знака | указывают на соединение строковых значений, что достаточно нетрадиционно.

..Упорядочение выходных данных. ORDER BY. 

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

!   Пример .

SELECT fam, im, ot, fakultet_kod, kurs

FROM spisok

WHERE kurs=1

ORDER BY fam,im

    Итак, предложение ORDER BY определяет порядок сортировки выходных данных.

    Если после имени поля записать служебное слово DESCENDING или сокращенный вариант DESC, то порядок сортировки по этому полю будет обратным.

!   Пример .

SELECT kurs, fam, im, ot

FROM spisok

ORDER BY kurs DESC, fam, im, ot

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

    Имеется и служебное слово – указатель на прямой порядок сортировки. Это слово ASCENDING или, сокращенно, ASC.  Однако оно избыточно, если порядок сортировки не указан, будет использован как раз прямой порядок.

   Сортировать можно и по тому полю, которое не перечислено в SELECT !

   После ORDER BY можно указывать не имена полей, а их номера

Пример .

SELECT fakultet_kod, fam, im, ot

FROM spisok

WHERE kurs=1

ORDER BY 2,3

Выходные данные будут отсортированы по фамилии и по имени. Нумерация начинается с 1, то есть поле, которое записано сразу после SELECT имеет номер 1.

В простых запросах это вряд ли полезно – явное указание имен полей делает текст запроса более понятным, однако позже мы увидим пример, когда этому варианту ORDER BY нет альтернативы.

    Раздел ORDER BY следует последним в тексте запроса.

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

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

.. Итоги по первой главе. 

К данному моменту мы освоили синтаксис SQL в следующих пределах:

SELECT {*|[DISTINCT]<список полей>}

FROM <имя таблицы>

[ WHERE <условие выбора> ]

[ ORDER BY <порядок сортировки> ]

Знак | означает, что либо записывается * (что означает выбрать все поля из таблицы), либо перечисляются нужные поля.

Квадратные скобки указывают элементы, которые могут отсутствовать.

В дальнейшем мы изучим более сложные варианты SQL-запросов.

. Агрегатные функции

.. Использование агрегатных функций. 

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

!   Пример .

SELECT COUNT( id )

FROM spisok

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

   Агрегатные функции дают обобщенные значения для целой группы записей.

   Список агрегатных функций

Функция

Назначение

COUNT

Вычисляет количество значений данного поля, выбранных посредством запроса и не являющихся NULL-значениями

SUM

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

AVG

Вычисляет среднее арифметическое всех выбранных значений данного поля. (англ. AVERAGE – среднее)

MAX

Вычисляет максимальное среди всех выбранных значений данного поля

MIN

Вычисляет минимальное среди всех выбранных значений данного поля

Примечание. Слова «данного поля» означают, что речь идет о поле, записанном в скобках после имени агрегатной функции.

Примечание. В Interbase имеется одна неприятная особенность у функции AVG. Если ее применить к полю целочисленного типа, то результат тоже будет целочисленным, и поэтому почти всегда неправильным! Для устранения этого следует использовать малоизвестную возможность приведения поля к нужному типу. Прочтите об этом в параграфе 2.5 !

.. Название поля агрегатной функции. 

?   Как будет названо поле, в котором выводится ответ агрегатной функции?

!   Зависит от конкретной СУБД. Некоторые СУБД не называют его никак, оставляя заголовок в ответе пустым, другие СУБД используют свою оригинальную систему именования таких полей, например, count_shortname или count_for_shortname. Однако при записи запроса можно применить способ переименования  полей.

Пример .

SELECT COUNT( shortname ) AS vsego_fakultetov

FROM fakultet

Теперь поле агрегатной функции получит в выходных данных имя (заголовок) vsego_fakultetov.

.. Использование * в COUNT. 

!   Пример .

SELECT COUNT( * ) FROM spisok

В функции COUNT  в скобках можно ставить не имя поля, а символ «звездочка». В этом случае подсчитываются все строки, в том числе и те, у которых в отдельных полях могут быть значения NULL, которые в обычном случае пропускаются. Поэтому указанный вариант лучше, чем SELECT COUNT( ot ) FROM spisok – вдруг поле ОТЧЕСТВО еще не заполнено у некоторых студентов!

.. Использование DISTINCT в агрегатных функциях. 

!   Пример .

SELECT  COUNT( DISTINCT fakultet_kod)

FROM spisok

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

SELECT COUNT( shortname ) FROM fakultet

   Теоретически DISTINCT можно записывать и в других агрегатных функциях, но такие варианты вряд ли будут полезны. Для MAX или MIN это нисколько не изменит результат, а для AVG результату трудно придать смысловую интерпретацию.

.. Использование выражений в агрегатных функциях. 

!   Пример .

SELECT SUM( pole1*pole2 )

FROM spisok

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

?   В Interbase имеется одна неприятная особенность у функции AVG. Если ее применить к полю целочисленного типа, то результат тоже будет целочисленным, и поэтому почти всегда неправильным!

!   Для устранения этого следует использовать малоизвестную возможность приведения поля к нужному типу.

Пример .

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

SELECT AVG( CAST(biblioteka AS DOUBLE PRECISION))

FROM spisok

   Формат функции CAST следующий CAST(<выражение> AS <тип>). Выражение чаще всего есть название поля, значение которого следует преобразовать в нужный тип, но иногда используются и «настоящие» выражения. <Тип> - один из возможных типов Interbase, к которому следует привести выражение. В примере выше вместо DOUBLE PRECISION можно было бы использовать любой вещественный тип.

.. Предложение GROUP BY

?   Требуется определить количество студентов на каждом факультете.

!   Пример .

SELECT fakultet_kod, COUNT(  id )

FROM spisok

GROUP BY fakultet_kod

Выдаст примерно такой результат (в правом столбце получим количество студентов на каждом факультете)

Fakultet_kod

ЕГФ

2

ФМФ

14

 Данный пример ясно показывает действие GROUP BY. В данном случае COUNT будет подсчитывать все записи в группе строк, у которых поле fakultet_kod имеет значение ЕГФ, а затем и для других аналогичных групп записей.

 Предложение GROUP BY используется вместе с агрегатными функциями. Оно  позволяет определять группу записей, для которой применяется агрегатная функция.

!  GROUP BY может производить группировку и по нескольким полям.

Пример .

SELECT fakultet_kod, kurs, COUNT( id )

FROM spisok

GROUP BY fakultet_kod, kurs

Запрос будет подсчитывать количество студентов на каждом факультете на каждом курсе. Пустые группы в выходных данных не будут представлены.

Побочным эффектом использования GROUP BY является то, что выходные данные будут отсортированы по тем полям, которые перечислены после GROUP BY.

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

Пример .

SELECT kurs, fakultet_kod, COUNT( id )

FROM spisok

GROUP BY fakultet_kod, kurs

.. Использование WHERE в запросах с агрегатными функциями

?  Требуется определить количество первокурсников на каждом факультете.

!   Пример .

SELECT fakultet_kod, COUNT(  id )

FROM spisok

WHERE kurs=1

GROUP BY fakultet_kod

  Для правильного использования WHERE в агрегатных функциях следует разобраться с использованием HAVING (см.следующий параграф)

.. Предложение HAVING

?   Требуется выбрать количество студентов только для тех факультетов, где количество студентов более 200.

Запрос 

SELECT fakultet_kod, COUNT(  id )

FROM spisok

WHERE COUNT( id )>200

GROUP BY fakultet_kod

будет неверным по синтаксису, то есть не будет выполняться. Дело в том, что в WHERE предикат должен оцениваться в терминах единственной строки (кроме случая использования подзапросов, который будет рассмотрен позже), а функция COUNT подсчитывается для группы строк.

!  Правильный вариант запроса.

Пример .

SELECT fakultet_kod, COUNT(  id )

FROM spisok

GROUP BY fakultet_kod

HAVING COUNT( id )>200

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

  Условие в HAVING должно быть сформулировано так, чтобы внутри группы оно должно иметь только одно значение. Поэтому запрос

SELECT fakultet_kod, COUNT(  id )

FROM spisok

GROUP BY fakultet_kod

HAVING kurs>1

будет синтаксически неверным.

Правильным вариантом будет

SELECT fakultet_kod, COUNT(  id )

FROM spisok

WHERE kurs>1

GROUP BY fakultet_kod

!  Пример .

SELECT fakultet_kod, COUNT(  id )

FROM spisok

GROUP BY fakultet_kod

HAVING fakultet_kod IN (’ЕГФ’,’ФМФ’)

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

. Запросы к нескольким таблицам

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

  Обычно соединение двух таблиц производят по тем полям, которые используются в ссылочной целостности FOREIGN KEY, такое соединение называется естественным.

  В SQL существует два вида соединения таблиц: эквисоединения и JOIN-соединения, хотя часто для этих двух видов соединений используются другие названия.

.. Эквисоединения

?  Нужно получить выходные данные в следующем виде: фамилия, имя студента, полное название факультета.

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

!  Пример .. Эквисоеднинения.

SELECT spisok.fam, spisok.im, fakultet.fullname

FROM spisok, fakultet

WHERE spisok.fakultet_kod=fakultet.shortname

  Эквисоединения используют оператор WHERE и предикат равенства.

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

!  Пример .. Использование алиасов

SELECT s.fam, s.im, f.fullname

FROM spisok s, fakultet f

WHERE s.fakultet_kod=f.shortname

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

  Пример .. Связь таблиц в WHERE не препятствует использованию в этом же WHERE и обычных условий.

SELECT spisok.fam, spisok.im, fakultet.fullname

FROM spisok, fakultet

WHERE spisok.fakultet_kod=fakultet.shortname AND spisok.kurs=2

  В одном запросе можно соединять также и более двух таблиц.

.. Соединение таблицы со своей копией

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

!  Пример .. Соединения таблицы со своей копией.

SELECT s1.fam, s1.im, s1.fakultet_kod, s1.id

FROM spisok s1, spisok s2

WHERE s1.fam=s2.fam AND s1.id<>s2.id

ORDER BY s1.fam, s1.im, s1.fakultet_kod

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

  У вышеуказанного примера есть недостаток – строки будут многократно повторяться.

.. JOIN-соединения

?  Нужно получить выходные данные в следующем виде: фамилия, имя студента, полное название факультета.

!  Пример .. Запрос с JOIN-соединением.

SELECT s.fam, s.im, f.fullname

FROM spisok s

INNER JOIN fakultet f ON s.fakultet_kod=f.shortname

  Левой таблицей в соединении называется та, которая указана перед (слева) ключевым словом JOIN, а правой таблицей та, которая указана после (справа) от него.

JOIN-соединения бывают внутренним (INNER) и внешним (OUTER). Тип соединения задается ключевым словом INNER или OUTER; если ни одно из них не указано, то принимается INNER.

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

Если указано одно из слов (LEFT, RIGHT, FULL), то соединение является внешним. При этом может быть добавлено ключевое слово OUTER (внешний), которое, впрочем, не является обязательным, и только делает текст запроса более «читаемым».

LEFT (OUTER) - тип соединения "левое (внешнее)". Левое соединение таблиц включает в себя все строки из левой таблицы и те строки из правой таблицы, для которых выполняется условие соединения. Для строк из левой таблицы, для которых не найдено соответствия в правой таблице, в столбцы, извлекаемые из правой таблицы, заносятся значения NULL.

RIGHT (OUTER) - тип соединения "правое (внешнее)". Правое соединение таблиц включает в себя все строки из правой таблицы и те строки из левой таблицы, для которых выполняется условие соединения. Для строк из правой таблицы, для которых не найдено соответствия в левой таблице, в столбцы, извлекаемые из левой таблицы, заносятся значения NULL.

FULL (OUTER) - тип соединения "полное (внешнее)". Это комбинация левого и правого соединений. В полное соединение включаются все строки из обеих таблиц. Для совпадающих строк поля заполняются реальными значениями, для несовпадающих строк поля заполняются в соответствии с правилами левого и правого соединений.

Использование JOIN-соединения в запросах часто облегчает восприятие оператора SELECT, особенно, когда используется естественное соединение.  Дело в том, что в запросах с эквисоединениями в разделе WHERE смешиваются такие семантически различные понятия, как условия связи таблиц и условия отбора строк. Каких-либо иных дополнительных преимуществ конструкция JOIN перед конструкцией WHERE не имеет.

.. Объединение запросов. Команда UNION.

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

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

!  Пример .. Объединение UNION.

SELECT klient, data_poluchenie AS date_s, -summa AS summa,

FROM kredit

UNION

SELECT klient, data_otgruzka AS date_s, summa AS summa

FROM debit

ORDER BY 2,1

  Фактически каждый запрос выполняется самостоятельно, и только выходные данные объединяются вместе. Чтобы выходные данные двух запросов можно было объединить, они должны иметь одинаковый тип и одинаковое название. Здесь нам пригодился способ переименования выходных полей, который был изучен ранее. Далее, объединенные выходные данные отсортированы, и здесь в ORDER BY удобно использовать не названия полей, а их порядковые номера.

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

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

..Как выполняются подзапросы.

?  Представим себе, что нам нужно выбрать всех студентов физико-математического факультета, но мы не знаем краткого кода этого факультета. Один из вариантов действий – это последовательно выполнить два запроса, сначала запрос к таблице fakultet, чтобы выяснить краткий код факультета по его полному названию, а затем, по полученному краткому коду (в нашем случае очевидно, что это будет ФМФ) составить и выполнить второй запрос, который будет выбирать из таблицы spisok студентов, у которых поле fakultet_kod имеет значение ФМФ.

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

Пример .. Использование подзапроса.

SELECT fam, im, ot, kurs

FROM spisok 

WHERE fakultet_kod=( SELECT shortname FROM fakultet WHERE fullname=’Физико-математический факультет’)

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

  Внимание. Подзапрос нельзя поставить справа от знака сравнения, то есть нельзя записать WHERE (SELECT shortname FROM fakultet WHERE fullname=’Физико-математический факультет’)=fakultet_kod

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

Запрос

SELECT fam, im, ot, kurs

FROM spisok

WHERE fakultet_kod=( SELECT shortname FROM fakultet WHERE fullname LIKE ‘Е%’)

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

SELECT fam, im, ot, kurs

FROM spisok

WHERE fakultet_kod=( SELECT shortname FROM fakultet WHERE fullname LIKE ‘Ф%’)

будет признан ошибочным, поскольку в таблице fakultet есть несколько факультетов с названием, начинающимся на Ф.

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

Пример .. Использование подзапроса и IN .

SELECT fam, im, ot, kurs

FROM spisok

WHERE fakultet_kod IN ( SELECT shortname FROM fakultet WHERE fullname LIKE ’Ф%’)

.. Использование агрегатных функций в подзапросах.

?   Требуется выбрать факультеты, на которых обучаются более 200 студентов (наша таблица spisok годится по структуре, но для этого примера должна быть пополнена записями о всех студентах).

!   Пример .. Использование агрегатных функций в подзапросах.

SELECT f.fullname

FROM fakultet f

WHERE 200<

(SELECT COUNT(id) FROM spisok s WHERE s.fakultet_kod=f.shortname)

 Обратите внимание на то, что по стандарту SQL считаются ошибочными запросы, в которых SELECT стоит слева от сравнения. Однако в Interbase это как раз разрешается!  

.. Оператор EXISTS.

?   Требуется выбрать факультеты, на которых срок обучения превышает 5 лет. Иначе говоря, это те факультеты, на которых имеются студенты 6го курса.

!   Пример .. Использование агрегатных функций в подзапросах.

SELECT f.fullname

FROM fakultet f

WHERE EXISTS ( SELECT fam,im,ot FROM spisok s WHERE s.fakultet_kod=f.shortname AND s.kurs=6)

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

  Без оператор EXISTS можно обойтись, но он отличается улучшенной «читаемостью», его смысл более соответствует написанию, чем другие варианты, обеспечивающие тот же результат.  Например, тот же результат можно получить следующим запросом

SELECT f.fullname

FROM fakultet f

WHERE 0<( SELECT COUNT(id) FROM spisok s WHERE s.fakultet_kod=f.shortname AND s.kurs=6)

  Иногда бывает полезным и предикат NOT EXISTS, который обеспечивает противоположное условие.

. Команды DML. Добавление записей, удаление и редактирование.

Для добавления записей, удаления записей и редактирования записей язык SQL включает специальные инструкции, которые называют языком манипулирования данными (Data Manipulation Language, DML). Таким образом DML – часть SQL.

.. Добавление записей. Команда INSERT.

?   Прежде чем использовать данные из таблиц, эти данные следует ввести в таблицы БД.

!   Пример .

INSERT INTO fakultet (shortname, fullname, dekan)

VALUES (‘МПФ’, ‘музыкально-педагогический факультет’, NULL)

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

Обратите внимание, поле dekan получит значение NULL, то есть неопределенное значение. Такое часто приходится делать, если в момент внесения записи значение этого поля еще неизвестно и будет заполнено в дальнейшем.

Более точно, будет сделана попытка добавить запись. Кроме технических причин (наличие связи с БД), попытка добавления может закончиться неудачей, если добавляемая запись не удовлетворяет ограничениям на поля таблицы. Например, если поле shortname является первичным ключом, то нельзя добавить запись с тем же значением этого поля, которое уже имеется у некоторой записи в таблице. Если поле dekan имеет ограничение NOT NULL, то есть обязательное для заполнения, то это ограничение также не позволит добавить вышеуказанную запись. Если мы попытаемся добавить запись в таблицу spisok, то сервер БД будет следить за тем, чтобы поле fakultet_kod имело только такое значение, которое имеется в ссылочной таблице fakultet.

  Итак, запись будет добавлена, только если она удовлетворяет всем ограничениям на данные, которые имеются в БД.

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

  Добавляемая запись займет свое место в таблице согласно значению того поля, которое является первичным ключом.

.. Добавление множества записей. Вставка результатов запроса.

?   Для массового добавления записей можно использовать следующий вариант команды INSERT.

!   Пример .

INSERT INTO spisok2 (id, fam, im, ot)

SELECT id,fam,im,ot FROM spisok WHERE kurs=5

Эта команда добавит в таблицу spisok2 все записи, которые будут результатом запроса SELECT к таблице spisok.

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

.. Удаление записей. Команда DELETE.

?   Иногда требуется удалить некоторые записи из таблицы базы данных.

!   Пример .

DELETE FROM spisok

WHERE kurs=5

Эта команда удалит из таблицы spisok все записи, касающиеся студентов 5го курса.

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

  Более точно говорить о том, что будет предпринята попытка удаления записей. Она может оказаться неудачной, если данные записи связаны ссылочной целостностью с некоторыми записями из другой таблицы. Например, в нашем основном примере, из таблицы fakuktet не удастся удалить записи о тех факультетах, студенты с которых фигурируют в таблице spisok.

  Если требуется удалить только одну запись, нужно обеспечить, чтобы условие после WHERE было истинно только для одной этой записи. Наиболее подходящим является условие вида «поле первичного ключа=..».

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

.. Редактирование записей. Команда UPDATE.

?   Иногда требуется изменить значения некоторых полей у каких-то записей из таблицы.

!   Пример .

UPDATE spisok

SET kurs=kurs+1

WHERE dolgi=0

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

  Та часть команды, которая определяет условие отбора записей для редактирования (буквальный перевод UPDATE – «обновление») записей, и начинается  с WHERE, не является обязательной, но тогда будут изменены все записи из таблицы.

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

  Если требуется удалить только одну запись, нужно обеспечить, чтобы условие после WHERE было истинно только для одной этой записи. Наиболее подходящим является условие вида «поле первичного ключа=..».

  Возможно одновременное изменение нескольких полей в каждой записи.

Пример .

UPDATE spisok 

SET fam=’Елкин’, fam_rp=’Елкина’, fam_dp=’Елкину’

WHERE fam=’Ёлкин’

Данный запрос изменит написание фамилии так, чтобы не использовать букву ‘Ё’, при этом будут изменены и все варианты фамилии в различных падежах.

. Команды DDL. Определение структуры БД

.. Создание базы данных

Для создания и изменения структуры БД в языке SQL имеется специальное подмножество команд – DDL(Data Definition Language, язык определения данных). В данной главе мы описываем его основные возможности. Данная глава в большей степени зависит от конкретной СУБД, однако различия в основном касаются используемых типов данных и не затрагивают концептуальной основы SQL. Мы излагаем тот вариант, который используется в СУБД Interbase.

?  Как создать базу данных?

!  Пример .. Команда создания базы данных.

CREATE DATABASE ‘KINF_2:F:\GDB\employee.gdb’

Пример .. Команда создания базы данных, вариант с большим числом параметров.

CREATE DATABASE ‘KINF_2:F:\GDB\employee.gdb’ PAGE_SIZE 2048 DEFAULT CHARACTER SET WIN1251;

Примечание. KINF_2 - это сетевое имя компьютера, на котором установлен и функционирует сервер СУБД.

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

  Команда CREATE DATABASE создает базу данных и устанавливает ее характеристики.

Синтаксис команды.

CREATE DATABASE  "<имя файла базы данных>"

[USER "<имя пользователя>" [PASSWORD "<пароль>"]]

[PAGE_SIZE [=] int]

[DEFAULT CHARACTER SET charset]

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

Параметр <имя пользователя> определяет владельца базы данных. Сразу после инсталляции СУБД InterBase «знает» только одного пользователя, который называется системным администратором, имеет имя SYSDBA и пароль masterkey. Имя обычно не меняют, а пароль, конечно, следует изменить.  Системный администратор имеет право доступа ко всем базам данных на сервере. Он может также зарегистрировать других пользователей и пароли для них. Параметр <имя пользователя> должен использовать только одно из таких зарегистрированных имен.

В параметре PAGE_SIZE целое число int указывает размер страницы, может быть 1024 (по умолчанию), 2048, 4096 или 8192. Этот параметр касается внутренней структуры файла БД и влияет на производительность, однако можно заметить, что файл базы данных растет путем добавления очередной страницы. Рекомендуется увеличить размер страницы, если база данных содержит таблицы, у которых размер записи велик, и наоборот, если таблицы содержат много записей, но размер записи мал, размер страницы должен быть минимальным.

Параметр DEFAULT CHARACTER SET определяет текущую кодировку для текстовых данных в таблицах. В принципе можно для некоторых таблиц из этой БД установить в дальнейшем и другую кодировку, однако это делают редко.

..Типы данных

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

Здесь мы перечисляем наиболее популярные типы данных СУБД InterBase.

Тип данных

Описание

BLOB

Большой двоичный объект. Сохраняет данные большого объема, такие как графика, текст и цифровой звук. Имеет субтипы, из которых наиболее употребительны 0: данные общего вида; 1: тексты. Cубтип данных BLOB описывается в их контексте. Память под BLOB данные выделяется так называемыми сегментами. При объявлении можно изменять размер сегмента, например BLOB SEGMENT SIZE 1024. По умолчанию размер сегмента 80 байт.

CHAR(<N>)

Строка текста фиксированной длины (от 1 байта до 32767 байт). <N> определяет количество знаков. Альтернативное ключевое слово: CHARACTER

VARCHAR(<N>)

Строка текста переменной длины (от 1 байта до 32767 байт) . <N> определяет количество знаков. Альтернативные ключевые слова VARYING CHAR, VARYING CHARACTER

DATE

Дата (включает также информацию о времени)

DECIMAL (<точность>, <масштаб>)

Вещественные числа. Параметр <точность> (от 1 до 15)  определяет, что сохраняется, по крайней мере столько цифр числа. <Масштаб> (от 1 до 15)   определяет число знаков после запятой. Должно быть меньше или равно параметра <точность>. Размер в памяти зависит от указанной точности.

NUMERIC

(<точность>, <масштаб>)

Вещественные числа. <Точность> (от 1 до 15)  определяет, что сохраняется, по крайней мере столько цифр числа. <Масштаб> (от 1 до 15)   определяет число знаков после запятой. Должно быть меньше или равно параметра < Точность >. Размер в памяти зависит от указанной точности.

FLOAT

Вещественное число. 7 цифр точности. Занимает 4 байта.

DOUBLE PRECISION

Вещественное число повышенной точности. Для научных вычислений: 15 цифр точности. Занимает всегда 8 байт.

INTEGER

Длинное целое от -2 147 483 648 до 2 147 483 648. Занимает 4 байта.

SMALLINT

Короткое целое от -32768 до 32767. Занимает 2 байта.

.. Простые варианты команд создания таблиц и изменения их структуры

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

?  Как создать таблицу в базе данных?

Пример .. Команда создания таблицы Fakultet.

CREATE TABLE fakultet (shortname VARCHAR(10) NOT NULL, fullname VARCHAR(100), dekan VARCHAR(100), PRIMARY KEY( shortname))

Пример .. Команда создания таблицы Spisok.

CREATE TABLE spisok (id INTEGER NOT NULL, fam VARCHAR(30) NOT NULL, im VARCHAR(30) NOT NULL, ot VARCHAR(30), fakultet_kod  VARCHAR(10), kurs INTEGER, date_r DATE, gruppa VARCHAR(20), PRIMARY KEY( id))

Пример .. Изменение структуры таблицы Spisok – добавление вторичного ключа для поля fakultet_kod.

ALTER TABLE spisok ADD CONSTRAINT m18 FOREIGN KEY fakultet_kod

REFERENCES fakultet(shortname)

  m18  - это условное название добавляемого ограничения CONSTRAINT. Оно должно быть образовано по правилам образования идентификаторов и не должно совпадать по имени с ранее объявленными ограничениями.

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

Пример .. Изменение структуры таблицы Spisok – удаление поля из таблицы

ALTER TABLE spisok DROP gruppa

Пример .. Удаление таблицы из БД.

DROP TABLE region

  В дальнейшем мы опишем эти действия более подробно, но сейчас этого вполне достаточно для работы с БД.

.. Добавление ограничений на таблицу

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

Помимо ограничений, связанных с установкой диапазона изменения данных, существуют также ссылочные ограничения (referential constraints, например связь master-detail между таблицами Customers и Orders может быть реализована как ограничение, содержащее требование, чтобы значение поля CustomerId (внешний ключ) в таблице Orders было равно одному из уже имеющихся значений поля CustomerId таблицы Customers.

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

Кроме того, ограничения на таблицу являются одним из средств поддержания целостности данных. Их можно объявлять при создании таблицы, но не возбраняется добавлять и позже, с помощью команд ALTER TABLE. Естественно, нужно следить, чтобы добавляемые ограничения выполнялись для имеющихся в таблице данных, иначе они будут отвергнуты сервером СУБД.

 !  Синтаксис ограничений для полей таблицы

< ограничение > = {

VALUE <operator> <val>

 | VALUE [NOT] BETWEEN <val> AND <val>

 | VALUE [NOT] LIKE <val> [ESCAPE <val>]

 | VALUE [NOT] IN (<val> [, <val> ...])

 | VALUE IS [NOT] NULL

 | VALUE [NOT] CONTAINING <val>

 | VALUE [NOT] STARTING [WITH] <val>

 | (<ограничение >)

 | NOT < ограничение >

 | < ограничение > OR < ограничение >

 | < ограничение > AND < ограничение >

 }

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

Если ограничение добавляется в уже созданную таблицу, вместо VALUE следует записывать имя поля таблицы. Если ограничение добавляется при создании таблицы или вместе с добавлением поля, следует записывать именно служебное слово VALUE.

Пример ..

Представим себе, что таблица spisok создавалась командой  CREATE TABLE spisok ( id INTEGER NOT NULL, fam VARCHAR(30), im VARCHAR(30), ot VARCHAR(30), date_r DATE, fakultet_kod VARCHAR(10) ), при создании никаких ограничений не было установлено. Добавим эти ограничения несколькими командами 

ALTER TABLE spisok ADD CONSTRAINT m15 CHECK (fam IS NOT NULL)

Это ограничение запрещает добавление записей, у которых поле fam не заполнено.

ALTER TABLE spisok ADD CONSTRAINT m16 PRIMARY KEY(id)

Эта команда устанавливает первичным ключом поле id.

ALTER TABLE spisok ADD CONSTRAINT m17 FOREIGN KEY(fakultet_kod) REFERENCES fakultet(shortname)

Эта команда устанавливает на поле fakultet_kod ограничение ссылочной целостности по полю shortname таблицы fakultet.

ALTER TABLE spisok ADD CONSTRAINT m18 CHECK (date_r>=’01.01.2000’)

Эта команда устанавливает для поля ДАТА РОЖДЕНИЯ минимальное значение.

ALTER TABLE spisok ADD CONSRAINT m20 CHECK (kurs BETWEEN 1 AND 5)

Эта команда устанавливает для поля КУРС – значение должно быть в пределах от 1 до 5. В некоторых случаях бываыет

ALTER TABLE fakultet ADD CONSTRAINT m19 UNIQUE (fullname)

Эта команда устанавливает ограничение – в таблице ФАКУЛЬТЕТЫ не может быть двух записей с одинаковым значением поля ПОЛНОЕ НАЗВАНИЕ ФАКУЛЬТЕТА.

.. Определение домена

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

  Синтаксис определения домена

CREATE DOMAIN <имя домена> [AS] <тип данных>  

[DEFAULT {<literal> | NULL | USER}]  [NOT NULL]

[CHECK (<ограничение>)]

[COLLATE <правило сравнения>];

Параметр <имя домена> следует записывать с использованием  только латиницы, цифр (цифра на первой позиции не допускается) и символа подчеркивания. Параметр <тип данных> - это один из типов, описанных в предыдущем параграфе.

Параметр  DEFAULT определяет значение по умолчанию, то есть то значение, которое вводится, если при добавлении данных оно не задано. За этим служебным словом может стоять <literal>, то есть текстовое или числовое значение, подходящее по типу, NULL – пустое значение или USER - имя пользователя. Данный параметр задает значение, которое будет использоваться, если в процессе ввода данных ничего не введено.

Ограничение  NOT NULL указывает, что такое поле обязательно должно быть заполнено.

Параметр COLLATE <правило сравнения> устанавливает способ сортировки для домена. Дело в том, что для одной и той же кодировки может быть несколько правил сравнения. Для кодировки  WIN1251, например, определены правила сравнения WIN1251 и PXW_CYRL. В первом из них заглавные и строчные буквы не различаются, во втором – различаются.

  Параметр CHECK определяет разнообразные ограничения, которые можно наложить на значение поля.

Синтаксис ограничений

< ограничение > = {

VALUE <operator> <val>

 | VALUE [NOT] BETWEEN <val> AND <val>

 | VALUE [NOT] LIKE <val> [ESCAPE <val>]

 | VALUE [NOT] IN (<val> [, <val> ...])

 | VALUE IS [NOT] NULL

 | VALUE [NOT] CONTAINING <val>

 | VALUE [NOT] STARTING [WITH] <val>

 | (<ограничение >)

 | NOT < ограничение >

 | < ограничение > OR < ограничение >

 | < ограничение > AND < ограничение >

 }

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

Удобно ограничения объяснить на примерах.

Ограничение CHECK VALUE>100 определяет, что вводимое значение должно быть больше 100.

Ограничение CHECK VALUE BETWEEN 50 AND 60 определяет, что значение должно быть между 50 и 60.  

Ограничение CHECK VALUE IN ("военнообязанный", "невоеннобязанный") опеределяет, что значение может быть только одним из этих вариантов.

Можно объединять ограничения с помощью логических связок OR, AND, отрицания NOT, и скобок, например, CHECK (VALUE>=50)AND(VALUE<=60) означает то же, что и CHECK VALUE BETWEEN 50 AND 60.

Примеры определений домена

Следующая инструкция создает домен, который может принимать положительные значения больше 1000, со значением по умолчанию 9999. Ключевое слово VALUE заменяется именем столбца основанном на этом домене.

CREATE DOMAIN custno  AS INTEGER  DEFAULT 9999  CHECK (VALUE > 1000);

Следующая инструкция создает домен с типом BLOB-текст и определяет кодировку CYRL (аналог MSDOS 866):

CREATE DOMAIN descript AS BLOB SUB_TYPE TEXT SEGMENT SIZE 80 CHARACTER SET CYRL;

.. Изменение структуры домена

ALTER  DOMAIN изменяет любые свойства существующего домена, кроме типа данных и установки NOT NULL. Изменения, над доменом воздействуют на все столбцы, основанные на домене, которые не были отменены на уровне таблицы.

Обратите внимание: Для изменения типа данных или установки NOT NULL, удалите домен и создаете его заново с желаемыми свойствами. Правда, при этом он не должен использоваться ни в одной таблице. Домен может быть изменен его создателем или пользователем SYSDBA.

Синтаксис

ALTER DOMAIN <имя домена> { [SET DEFAULT {<literal> | NULL | USER}]  | [DROP DEFAULT]

 | [ADD [CONSTRAINT] CHECK (<ограничение>)]   | [DROP CONSTRAINT]   };

Примеры

Следующая инструкция создает домен с допустимыми значениями > 1000, за тем устанавливает его значение по умолчанию к 9999.

CREATE DOMAIN custno   AS INTEGER  CHECK (VALUE > 1000);

ALTER DOMAIN custno SET DEFAULT 9999;

.. Создание таблиц

Команда CREATE TABLE устанавливает новую таблицу, ее столбцы и ограничения целостности в существующей базе данных. Пользователь, который создает таблицу, становится владельцем таблицы и получает полные привилегии для этого, включая возможность предоставления (GRANT) привилегий другим пользователям, триггерам, и сохраненным процедурам.

CREATE TABLE поддерживает несколько опций для определения столбцов:

  •  Локальные столбцы, определяющие имя и тип данных для данных, введенных в столбец.
  •  Вычисляемые столбцы, базирующиеся на расширении. Значение столбца вычисляется каждый раз при доступе к таблице. Столбцы, к которым обращается выражение, должны существовать раньше, чем столбец может быть определен.
  •  Основанные на доменах столбцы, наследуемые все характеристики домена, но определение столбца может включать новое значение по умолчанию, атрибут NOT NULL, дополнительные ограничения CHECK или переопределять порядок сортировки, которые отменяют определение домена.
  •  Описание типа данных для столбца типа CHAR, VARCHAR или BLOB-техт может включать предложение CHARACTER SET определяя специфическую кодировку для одиночного столбца, отличную от определенной для базы данных.
  •  Предложение COLLATE позволяет указать специфический порядок сортировки для типов данных CHAR, VARCAHR и BLOB-текст.
  •  Атрибут NOT NULL предотвращает ввод NULL или неизвестного значения в столбец.
  •  Ограничения целостности могут быть определены для таблицы, когда она создана. Ограничения целостности это правила, которые контролируют базу данных и ее компоненты, связи типа столбец-таблица и таблица-таблица, и проверку вводимых данных. Они охватывают все транзакции к базе данных и автоматически поддерживаются системой. CREATE TABLE может создавать следующие типы ограничений целостности:
  •  PRIMARY KEY (первичный ключ) - уникально идентифицирует каждую строку таблицы. Значение в этом столбце либо в упорядоченном наборе столбцов не могут повторятся в более чем одной строке. Столбец PRIMARY KEY должен быть определен только с атрибутом NOT NULL. Таблица может иметь только один PRIMARY KEY, который может быть определен на одном или более столбцов.
  •  UNIQUE (уникальные) ключи гарантируют, что не существует двух строк имеющих одно и тоже значение в специфическом столбце или упорядоченном наборе столбцов. Уникальный столбец должен быть определен с атрибутом NOT NULL. Таблица может иметь один или более UNIQUE ключей. UNIQUE ключ может быть использован FOREIGN KEY (внешний ключ) в другой таблице.
  •  Справочные ограничения гарантируют, что значения в наборе столбцов, которые определены в FOREIGN KEY принимают те же самые значения, которые присутствуют в столбце UNIQUE или PRIMARY KEY в справочной таблице.
  •  CHECK ограничения предписывают <search_condition>, которые должны принимать значение истинно для добавленных или измененных данных. <search_condition> могут требовать некоторой комбинации или порядка значений или равенства значению, введенному в другие столбцы.
  •  Для не именованных ограничений, система сама создает уникальное имя, сохраненное в системной таблице RDB$RELATION_CONSTRAINTS.

Синтаксис

CREATE TABLE <имя таблицы> (<определение столбца> [,<определение столбца> | <ограничение на столбец> ...]);

 

< определение столбца > =  {<тип данных> | COMPUTED [BY] (<выражение>) | <имя домена>}

[DEFAULT {literal | NULL | USER}]

[NOT NULL] [<ограничение на столбец >]

[COLLATE collation]

Параметр <тип данных> - это один из стандартных типов InterBase,  или домен.

<Выражение> = Допустимое выражение SQL, которое возвращает единственное значение. При построении выражений можно использовать числовые константы, а также имена других полей.  Необычным выглядит соединение строк – для этого используется знак «две вертикальные черты», например, FAM||’ ‘||IM.

Параметры  DEFAULT, NOT NULL,  COLLATE  описаны ранее при определении доменов.  

<col_constraint> = [CONSTRAINT constraint] <constraint_def>

[<col_constraint>]

<constraint_def> = {UNIQUE | PRIMARY KEY

| CHECK (<search_condition>)

 | REFERENCES <название другой таблицы> [(other_col [, other_col ...])]}

<tconstraint> = CONSTRAINT constraint <tconstraint_def>

[<tconstraint>]

<tconstraint_def> = {{PRIMARY KEY | UNIQUE} (col [, col ...])

| FOREIGN KEY (col [, col ...]) REFERENCES other_table

 | CHECK (<search_condition>)}

<search_condition> =

{<val> <operator> {<val> | (<select_one>)}

 | <val> [NOT] BETWEEN <val> AND <val>

 | <val> [NOT] LIKE <val> [ESCAPE <val>]

 | <val> [NOT] IN (<val> [, <val> ...] | <select_list>)

 | <val> IS [NOT] NULL

 | <val> {[NOT] {= | < | >} | >= | <=}

   {ALL | SOME | ANY} (<select_list>)

 | EXISTS (<select_expr>)

 | SINGULAR (<select_expr>)

 | <val> [NOT] CONTAINING <val>

 | <val> [NOT] STARTING [WITH] <val>

 | (<search_condition>)

 | NOT <search_condition>

 | <search_condition> OR <search_condition>

 | <search_condition> AND <search_condition>}

<val> = {

col [<array_dim>] | <constant> | <expr> | <function>

  | NULL | USER | RDB$DB_KEY

 } [COLLATE collation]

<constant> = num | "string" | charsetname "string"

<function> = {

COUNT (* | [ALL] <val> | DISTINCT <val>)

 | SUM ([ALL] <val> | DISTINCT <val>)

 | AVG ([ALL] <val> | DISTINCT <val>)

 | MAX ([ALL] <val> | DISTINCT <val>)

 | MIN ([ALL] <val> | DISTINCT <val>)

 | CAST (<val> AS <datatype>)

 | UPPER (<val>)

 | GEN_ID (generator, <val>)

 }

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

<select_one> = SELECT (выбор) на одном столбце, который возвращает точно одно значение.

<select_list> = SELECT на одном столбце, который возвращает ноль или более значений.

<select_expr> = SELECT на списке значений, который возвращает ноль или более значений.

Примеры

Следующая инструкция создает таблицу с UNIQUE ограничением и на уровне столбца и на уровне таблицы:

 FOREIGN KEY (JOB_COUNTRY) REFERENCES COUNTRY (COUNTRY),

 CHECK (MIN_SALARY < MAX_SALARY));

Следующая инструкция создает таблицу с вычисляемым столбцом fio (фамилия_имя_ отчество по отдельным полям fam, im,ot):

CREATE TABLE spisok ( id  INTEGER NOT NULL, fam VARCHAR(30), im VARCHAR(30), ot VARCHAR(30), fio COMPUTED BY (fam||” “||im” “||ot), PRIMARY KEY (id));   

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

CREATE TABLE BOOKADVANCE

 (BOOKNO CHAR(6), TITLE CHAR(50) COLLATE ISO8859_1,  EUROPUB CHAR(50) CHARACTER SET ISO8859_1 COLLATE FR_FR);

.. Изменение структуры таблицы

Команда ALTER TABLE дает возможность изменить структуру существующей таблицы. Одиночная инструкция ALTER TABLE может выполнить множественные добавления и удаления.

Синтаксис

ALTER TABLE <имя таблицы> (<операция> [, < операция > ...];

< операция > = {ADD < определение столбца > | ADD <ограничение на таблицу> | DROP <имя столбца> | DROP CONSTRAINT <имя ограничения>}

Инструкция ADD позволяет добавить в таблицу поле или ограничение,  при этом используется тот же синтаксис, что и описанный в предыдущем параграфе при определении полей. Инструкция DROP удаляет поле или ограничение.

. Определение прав доступа к данным

При инсталляции сервера СУБД InterBase для системного администратора СУБД в списке пользователей регистрируется запись с именем SYSDBA и паролем masterkey. Он обладает полными правами на СУБД.  В реальной практике следует сразу же изменить пароль администратора (можно изменить и имя, но это обычно не делается).

Администратор может создавать записи для других пользователей. Для этого в SQL добавлены команды, которые иногда рассматривают как отдельный раздел SQL и называют его «язык безопасности данных». Рассмотрим эти команды в варианте SQL для СУБД InterBase.

! Передать привилегии на таблицу другому пользователю можно командой

GRANT <список привилегий> ON <название таблицы> TO <имя пользователя> [WITH GRANT OPTION]

Возможны следующие привилегии:

SELECT – разрешается делать запросы к таблице, то есть обеспечивается доступ к чтению таблицы

INSERT – разрешается добавлять записи в таблицу

DELETE -  разрешается удалять записи из таблицы

UPDATE – разрешается редактировать записи в таблице

REFERENCES – разрешается использовать поля данной таблицы для организации вторичного ключа в другой таблице.

Если администратор желает дать пользователю все привилегии к таблице, можно вместо списка привилегий указывать ALL.

Если  администратор желает дать всем пользователям некоторую привилегию или список привилегий, вместо имени пользователя достаточно указать PUBLIC.

Если в конце команды GRANT указано WITH GRANT OPTION, то пользователь, получивший привилегии к таблице, получает право давать такие же привилегии в дальнейшем другим пользователям. Например, администратор может дать разработчику право на создание и работу с таблицей, а тот, в свою очередь, дает право пользователям работать с этими таблицами.

Командой

GRANT <список привилегий> ON <название таблицы> FROM <имя пользователя>

можно лишить пользователя ранее данной ему привилегии.

Для удобства администрирования введено понятие «роль». Роль – это список привилегий для нескольких таблиц базы данных.

CREATE ROLE <имя роли>;

GRANT <список привилегий> TO <имя роли>;

CONNECT “ database” USER “ username” PASSWORD “ password” ROLE “ rolename”;

GRANT <имя роли> ON <название таблицы> TO <имя пользователя>;

GRANT < privileges> ON [TABLE] { tablename } TO <имя роли>;

< privileges> = {ALL [PRIVILEGES] | < privilege_list>}

< privilege_list> = {

SELECT| DELETE| INSERT| UPDATE [( col [, col …])]| REFERENCES [( col [, col …])][, < privilege_list> …]}}

GRANT UPDATE (CONTACT, PHONE) ON CUSTOMERS TO PUBLIC;

Только  для Update и References

. Некоторые особенности SQL в Interbase

В этой главе описаны некоторые частные случаи, расширения и добавления к SQL, которые связаны со спецификой СУБД Interbase.

.. Генераторы

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

Перед разработчиком часто стоит проблема, как генерировать неповторяющиеся значения для первичного ключа. В некоторых СУБД имеются специальные типы, которые специально придуманы для полей первичного ключа. Они удобны тем, что значения для них автоматически вырабатываются системой управления БД при вставке новых записей в таблицу. Обычно это – автоматически нарастающие целые числа. Например, в MS Access такой тип называется типом «счетчик», в СУБД Paradox имеется тип «autoincremental». В СУБД InterBase такого специального типа данных нет, вместо этого предлагается особый механизм – использование генераторов.

!  Генератор в Interbase – это комплект из целочисленной переменной, хранящейся в базе данных, и механизма ее автоматического увеличения и использования значений этой переменной для заполнения полей в таблицах БД.  

Основное назначение генераторов – вырабатывать неповторяющиеся целочисленные значения, которые можно использовать в качестве значений для первичного ключа.

Для создания генератора в диалект SQL для СУБД Interbase добавлена SQL-команда CREATE GENERATOR <имя_генератора>. Кроме того, после создания генератора бывает необходимо установить некоторое начальное значение (если этого не сделать, то начальным значением будет 0). Это делается командой SET GENERATOR <имя_генератора> TO <начальное_значение>.

Пример .. Создание генератора c именем new_id.

CREATE GENERATOR new_id

Пример .. Установка начального значения для генератора new_id.

SET GENERATOR new_id TO 5000

!   После создания генератора и установки начального его значения можно вызывать функцию GEN_ID(<имя_генератора>,<шаг_приращения>). Эту функцию можно вызывать в триггерах или хранимых процедурах (о них ниже), а также ее можно вызывать в командах добавления записей в таблицу (команда INSERT из DML).

Пример .. Вариант команды  INSERT, в котором  одно из полей заполняется значением из генератора.

INSERT INTO spisok (id, fam, im, ot, date_r, fakultet_kod) VALUES (GEN_ID(new_id,1), 'Николаев', 'Николай', 'Николаевич', '1990-06-06', 'ФМФ');

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

 Удаление неиспользуемого генератора выполняется командой DROP GENERATOR <имя_генератора>.

.. Хранимые процедуры

?  Часто возникает необходимость некоторые типовые процедуры обработки данных хранить в самой БД, а из пользовательских программ только вызывать их.

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

  Приведем краткое описание элементов языка хранимых процедур. Служебные слова  BEGIN и END также как и в Паскале объединяют в единое целое блок операторов для использования там, где по синтаксису требуется одно действие.  

Оператор присваивания выглядит так <Имя_переменной>=<Значение>.

Комментарии оформляются так:   /*  комментарий */ .

Вызов исключений по имени: EXCEPTION <имя_исключения>. Исключение – это ошибка, определяемая пользователем. Обработка ошибок выполняется оператором WHEN. Если  такой обработчик не определен, в вызывающую программу посылается сообщение об ошибке.

Вызов другой хранимой процедуры из текущей

EXECUTE PROCEDURE <имя_процедуры>

[var [, var …]]                               -входные переменные

[RETURNING_VALUES var [, var …]]     выходные переменные

Оператор цикла FOR <выбор> DO <действие > повторяет действие для каждой записи из выбора, записанного после FOR. Выбор – это обычный SELECT за исключением того, что в конце к нему приписано INTO и далее следует имена переменных, в которые следует разместить все поля SELECT.

Оператор выбора IF (<условие>) THEN <действия> [ELSE <действия >]. Отметим, что для условия возможно третье значение UNKNOWN, при котором не будет выполняться ни одна из ветвей ветвления.

Посылка сообщения POST_EVENT <сообщение>. Сообщения должны быть зарегистрированы в БД.

Оператор цикла WHILE (<условие>) DO <действия>

Обработчик ошибок WHEN {<ошибка> [, <ошибка> …]|ANY} DO <действия>

Если ANY, то это обработчик для всех ошибок.

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

Пример .. Простой пример процедуры действия. Процедура возвращает сумму двух чисел.

CREATE PROCEDURE sum (   a INTEGER, b INTEGER  )

RETURNS (   c INTEGER )

as

BEGIN

 c=a+b

END

Процедуры выбора (Select Procedure) возвращают в качестве результата много записей и поэтому в прикладных программах могут быть использованы вместо запросов и таблиц.

Пример .. Простой пример процедуры выбора.

CREATE PROCEDURE mmm (   fk VARCHAR(10)  )

RETURNS (   afam VARCHAR(30),   aim VARCHAR(30)  )

as

BEGIN

 SELECT fam, im

 FROM spisok

 WHERE fakultet_kod=:fk

 INTO :afam,  :aim;

 SUSPEND;

END

Такая процедура эквивалентна запросу

 SELECT fam AS aim, im AS aim

 FROM spisok

 WHERE FAKULTET_KOD=:fk

В процедурах выбора должен быть фрагмент INTO :имена переменных, которых определяет значения, возвращаемые процедурой в качестве результатов. Кроме того, должно присутствовать служебное слово SUSPEND, которое после отправки каждого сформированного набора данных (в нашем случае :afam, :aim) делает паузу, пока вызывающая программа не сообщит, что набор данных принят, после чего формирует следующий набор. Таким образом, наличие SUSPEND в сочетании с циклом обеспечивает циклическую отправку многих наборов данных (записей).

.. Триггеры

?   Часто возникает необходимость автоматически выполнять некоторые типовые процедуры обработки данных при наступлении некоторого события в состоянии БД.

!  В СУБД Intebase имеется механизм триггеров. По сути дела, триггеры – это хранимые процедуры, которые автоматически запускаются при наступлении одного из ниже перечисленных событий в таблице.

Событие

Когда возникает в таблице

BEFORE INSERT

Перед добавлением новой записи в таблицу

BEFORE DELETE

Перед удалением записи из таблицы

BEFORE UPDATE

Перед изменением содержимого записи

AFTER INSERT

После добавления новой записи в таблицу

AFTER DELETE

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

AFTER UPDATE

После изменения содержимого записи

  В отличие от хранимой процедуры, триггер всегда «привязан» к некоторой таблице БД. Имя этой таблицы и событие в таблице, при наступлении которого автоматически запускается триггер, записывается в его заголовке. Триггер не имеет входных или выходных параметров. Его заголовок отличается от заголовка хранимой процедуры.

Пример .. Представим себе, что в БД имеется еще таблица balls, в которой хранятся сведения о каждой оценке студента на экзамене. Она связана с таблицей spisok вторичным ключом по полю id_studenta. Тогда при удалении записи о студенте из таблицы spisok следует удалить и его оценки. Очевидно, это следует делать непосредственно перед удалением записи из spisok.

CREATE TRIGGER mt1 FOR spisok

BEFORE DELETE

AS

BEGIN

   DELETE FROM balls WHERE id_student=OLD.id

END

 Обратите внимание на использование служебного слова OLD. Оно записывается перед одним из полей таблицы, для которой определяется триггер, и указывает, что будет использоваться старое значение поля, то есть то значение, которое было до события, к которому присоединен триггер. Кроме слова OLD, используется еще слово NEW – новое значение поля. Легко понять, что в триггерах BEFORE DELETE и AFTER DELETE можно использовать только OLD, в триггерах BEFORE INSERT и AFTER INSERT – только NEW, а в триггерах BEFORE UPDATE и AFTER UPDATE можно встретить  и OLD и NEW. Например, фразу «если поменялась фамилия студента …» можно «перевести» как «IF (OLD.fam<>NEW.fam) THEN …».

  Общий синтаксис триггера

CREATE TRIGGER <имя_триггера> FOR <имя_таблицы>

[ACTIVE | INACTIVE]    

{BEFORE | AFTER} {DELETE | INSERT | UPDATE}

[POSITION <номер>]

AS 

[<список_локальных_переменных>]

BEGIN

  <команды>

END

  Напоминаем, что фигурные скобки { } обозначают обязательный элемент, который должен быть выбран из вариантов, разделенных вертикальной чертой, а квадратные скобки [ ]  обозначают необязательный элемент.

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

Каждая локальная переменная объявляется «персонально» DECLARE  VARIABLE <имя_переменной> <тип_переменной>; (точка с запятой обязательна).

В остальном синтаксис триггеров такой же, как синтаксис хранимых процедур.


Представления

Практически все реляционные СУБД поддерживают представления (views). Этот объект представляет собой виртуальную таблицу, предоставляющую данные из одной или нескольких реальных таблиц. Нередко такие объекты создаются для хранения в базах данных сложных запросов. Фактически view - это хранимый запрос.

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

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


Сокращения

ANSI - Американский национальный институт стандартов

DDL – Data Definition Language, язык определения данных 

DML  - Data Manipulation Language, язык манипулирования данными

ISO  - Международная организация по стандартам

QBE - Query By Example, язык запросов по образцу.

SQL - Structured Query Language, структурированный язык запросов

SYSDBA – стандартное имя администратора для СУБД InterBase. Регистр символов неважен.

masterkey – стандартный пароль администратора для СУБД InterBase. Регистр символов важен. Некоторые авторы утверждают, что важны только первые 8 символов, однако нельзя ручаться, что это верно во всех версиях InterBase/FireBird.

БД – база данных

СУБД – система управления базами данных

ИС – информационная система

АИС – автоматизированная информационная система

Термины

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

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

Литература

  1.  Гайдамакин Н.А. Автоматизированные системы, базы и банки данных. Вводный курс: Учебное пособие. — М.:Гелиос АРВ, 2002. — 368с.
  2.  Грабер М. Введение в SQL — М.: Издательство «Лори», 1996. - 379с. (перевод книги Gruber М. “Understanding SQL”, 1990, имеются и более новые издания этой же книги в русском переводе)
  3.  Скляр А.Я. Введение в InterBase — М.: Горячая линия-Телеком, 2002. - 517с.

 Нет сомнений, что книгой, где язык SQL описан наиболее доступно, является достаточно давняя книга Gruber М. “Understanding SQL”, 1990.

 Наилучшим учебником по дисциплине «Автоматизированные системы» среди изданных на сегодня является, на наш взгляд, книга Н.А.Гайдамакина.


 

А также другие работы, которые могут Вас заинтересовать

43309. Расчет пружины 1.17 MB
  Выбор марки стали для изготовления пружины Марка стали – 85 1. Высокие свойства максимальные пределы упругости и выносливости пружины и рессоры имеют при твердости HRC 40 45 структура тростит которая достигается после закалки с равномерным и полным мартенситным превращением по всему объему металла и среднего отпуска при 400 500 С в зависимости от стали.Обоснование выбора марки стали для изготовления пружины Особенности работы рессорнопружинных сталей состоят в том что при значительных ударных или статических нагрузках в них не...
43310. Обработка методами типа «перенос-опознание» 289.5 KB
  Управление автоматом задается управляющей таблицей типа : перенос опознание которая задает операцию ПЕРЕНОС ОТВЕРГНУТЬ или процедуру опознания для каждой комбинации магазинного и входного символов. Каждая из процедур опознания просматривает несколько верхних символов магазина и либо выбирает одну из операций СВЕРТКА для некоторого правила либо ДОПУСТИТЬ или ОТВЕРГНУТЬ. Первая из них состоит в том чтобы решить какие элементы таблицы управления должны содержать операции ПЕРЕНОС какие процедуры опознания и какие операции...
43312. Візуалізація графічних зображень 365 KB
  Що стосується візуалізації даних, наданих у вигляді матричних структур, із подальшим їх перетворенням у графові моделі, то сьогодні накопичений відносно невеликий досвід їх вирішення. Основними завданнями при цьому є як фундаментальні (пояснюються недосконалістю існуючих методів візуалізації), так і технічні (пов’язані із складністю вибору та забезпеченням взаємодії різних програмних та апаратних засобів. Отже, актуальним завданням є виокремлення основних аспектів побудови прикладного програмного забезпечення, яке надавало б розробникам можливість із створення високорівневих засобів візуалізації даних, заданих у матричній формі
43313. Розробка програмного забезпечення для визначення інформації про жорсткий диск за допомогою інтерфейсу IDE/ATAPI 710.5 KB
  Програми мовою асемблера дуже точні. Оскільки ця мова дозволяє програмістові безпосередньо працювати з усім апаратним забезпеченням, програми на асемблері можуть робити те, що недоступно ніякій іншій програмі. Безсумнівно, що в програмуванні пристроїв де потрібен контроль над окремими розрядами регістрів пристрою, програмування мовою асемблера - єдиний підходящий вибір. І остання причина для написання програми на мові асемблера. Тільки через написання програм на цьому рівні деталізації можна зрозуміти, як працює машина на самому нижньому рівні.
43314. Автоматизированная система управления воздушным движением 2.05 MB
  В системе автоматизированы процессы обработки и отображения информации от радиолокационных и радиотехнических средств, информации о планировании полетов, метеорологической обстановке и другие процедуры обеспечения процессов обслуживания воздушного движения. Все подсистемы АС УВД построены на базе локальных вычислительных сетей с применением технологий цифровой обработки и передачи данных.
43315. Правова система України 168.21 KB
  Перш ніж будувати власну державу і власну правову систему потрібно подивитись, що ми маємо на сучасному етапі і що хочемо побудувати у майбутньому. Будуючи нову правову систему потрібно звертатись за допомогою до більш досконалих іноземних систем, вибираючи з них найкраще. Нажаль, в Україні, сьогодні, склалась така ситуація, коли люди, що стоять при владі не розуміють цього або, що ще гірше розуміють, але спеціально нічого не роблять. Все на що вони спроможні – це бездумно вирвати з іноземного законодавства, можливо і найкращі, положення та ліпити їх в наше. Багато наших бід саме через це.
43316. Розробка програм на мові Turbo Pascal. Робота з додатками MS Office 97/200 (Word, Excel) і MathCAD 2000 2.5 MB
  Відмінність функції від процедури полягає в тому що результатом виконання операторів що утворюють тіло функції завжди є деяке єдине значення або покажчик тому звертання до функції можна використовувати у відповідних вираженнях поряд зі змінними й констант Підпрограми являють собою інструмент за допомогою якого будьяка програма може бути розбита на ряд певною мірою незалежних друг від друга частин. Поперше цей засіб економії пам'яті: кожна підпрограма існує в програмі в єдиному екземплярі у те час як звертатися до неї можна...
43317. Гарантированное удаление информации 298.5 KB
  Гарантированное удаление информации зачем это нужно Большинство пользователей персональных компьютеров уверены в том что стандартные функции уничтожения файлов предусмотренные в операционной системе позволяют раз и навсегда избавиться от файлов. Также немаловажным фактором является скорость удаления информации. Например если несмотря на все методы защиты каким-то образом был произведен несанкционированный доступ к секретной информации и выгоднее уничтожить эту самую информацию чем позволить злоумышленнику скачать ее нарушив таким...