Целостность внешних ключей (ссылочная целостность) обеспечивается тем, что внешние ключи не могут ссылаться на строки, отсутствующие в родительских таблицах.
Ссылочную целостность могут нарушить следующие операции над данными:
Обновление первичного ключа в строке родительской таблицы
Удаление строки в родительской таблице
Вставка новой строки в дочернюю таблицу
Обновление внешнего ключа в дочерней таблице
Для поддержания ссылочной целостности используются следующие 2 стандартных правила:
Ограничить (restrict) (не разрешать выполнение операции, приводящей к нарушению. Требует проверки существования связи между данными родительской и дочерней таблица)
Каскадировать (cascade) (Разрешить выполнение требуемой операции, но внести при этом изменения в связанные таблицы так, чтобы не допустить нарушения ссылочной целостности. Изменения начинаются в главной родительской таблице и каскадом выполняются в подчинённых таблицах (сложное правило, но оно хорошо тем, что после выполнения операции с соответствующими каскадными изменениями будут сохранены все имеющиеся связи между данными))
Дополнительные правила:
Установить в NULL (SET NULL) – разрешить выполнение требуемой операции, однако все некорректные значения внешнего ключа изменить на NULL значений
Установить по умолчанию (SET DEFAULT). Разрешить выполнение требуемой операции, однако все некорректные значения внешнего ключа изменить на некоторое значение, принятое по умолчанию
Замечание
Дополнительные правила ссылочной целостности могут использоваться только тогда, когда дочерняя таблица является независимой от родительской
Язык SQL (Structured Query Language - структурированный язык запросов) представляет собой стандартный высокоуровневый язык описания данных и манипулирования ими в системах управления базами данных (СУБД), построенных на основе реляционной модели данных.
Язык SQL был разработан фирмой IBM в конце 70-х годов. Первый международный стандарт языка был принят международной стандартизирующей организацией ISO в 1989 г., а новый (более полный) - в 1992 г. В настоящее время все производители реляционных СУБД поддерживают с различной степенью соответствия стандарт SQL92.
Основные категории команд языка SQL:
DDL, или язык определения данных
DML, или язык манипулирования данными
DQL, или язык запросов
DCL, или язык управления данными
Команды администрирования данных
Команды управления транзакциями
Язык манипулирования данными (DML) — это часть языка SQL, предназначенная для реального внесения пользователем изменений в информацию, содержащуюся в реляционной базе данных. С помощью команд языка манипулирования данными пользователь может загружать в таблицы новые данные, а также изменять и удалять существующие. Команды DML также могут быть использованы при выполнении простых запросов к базе данных.
В языке SQL существует три основных команды DML:INSERT, UPDATE, DELETE.
Единственной структурой представления данных (как прикладных, так и системных) в реляционной базе данных (БД) является двумерная таблица.
В реляционной модели данных таблица обладает следующими основными свойствами:
идентифицируется уникальным именем;
имеет конечное ненулевое количество столбцов;
имеет конечное (возможно, нулевое) число строк;
столбцы таблицы идентифицируются своими уникальными именами и номерами;
содержимое всех ячеек столбца принадлежит одному типу данных;
в общем случае ячейки таблицы могут оставаться пустыми, такое их состояние обозначается как NULL.
Язык SQL: вставка новой записи в таблицу
INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name [(col_name,...)]
VALUES (expression,...),(...),...
либо
INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name
SET col_name=expression, col_name=expression, ...
либо
INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name [(col_name,...)]
SELECT ...
Общие положения работы команды INSERT:
tbl_name
- Задает имя таблицы, в которую будет вставлена новая строка. На момент запуска команды INSERT таблица с таким именем должна существовать в базе данных.
LOW_PRIORITY
- Если указан этот параметр, то вставка новой записи будет отложена до тех пор, пока другие сценарии не закончат чтение из этой таблицы. Надо отметить, что если таблица часто используется, то при указании этого параметра может пройти достаточно много времени, прежде чем данная команда будет выполнена.
DELAYED
- Если указан этот параметр, то после выполнения команды INSERT сценарий сразу же получит ответ от БД о успешной вставке новой записи, а запись будет вставлено только после завершения использования данной таблицы другим сценарием. Это может быть удобно, если требуется высокая скорость работы скрипта. Данный параметр работает только с таблицами типа ISAM и MyISAM. Следуем отметить, что если таблица, в которую происходит вставка записи, в данный момент не используется другими запросами, то команда INSERT DELAYED будет работать медленнее, нежели INSER. Так что рекомендуется использовать параметр DELAYED только при большой нагрузке на таблицу.
IGNORE
- Если некоторые поля таблицы имеют ключи PRIMARY или UNIQUE, и производится вставка новой строки, в которой эти поля имеют дублирующее значение, то действие команды аварийно завершается и выдается ошибка №1062 ("Duplicate entry 'val' for key N"). Если в команде INSERT указано ключевое слово IGNORE, то вставка записей не прерывается, а строки с дублирующими значениями просто не вставляются.
Для добавления новых записей в существующую таблицу при помощи команды INSERT существует три основных синтаксиса:
INSERT ... VALUES
- В этом случае в команде четко указывается порядок следования устанавливаемых полей и их значений.
INSERT INTO Поставщик (КодПоставщика, ИмяПоставщика, КодРегиона)
VALUES (123, 'ЗАО Магистраль', 101)
INSERT INTO Регион
VALUES (301, 'Украина', 'Крымская', 'Алушта', 'ул.Франко, 24',
NULL, '46-49-16')
GO
Синтаксис оператора DELETE FROM
DELETE [LOW_PRIORITY | QUICK] FROM table_name
[WHERE where_definition]
[ORDER BY ...]
[LIMIT rows]
или
DELETE [LOW_PRIORITY | QUICK] table_name[.*] [,table_name[.*] ...]
FROM table-references
[WHERE where_definition]
или
DELETE [LOW_PRIORITY | QUICK]
FROM table_name[.*], [table_name[.*] ...]
USING table-references
[WHERE where_definition]
Команда DELETE удаляет из таблицы table_name все записи, удовлетворяющие условию where_definition. Если условие WHERE where_definition не задано, то из таблицы table_name удаляются все записи.
LOW_PRIORITY
- Если указано это ключевое слово, то удаление записей из таблицы table_name не будет произведено до тех пор, пока другие процессы не закончат чтение их этой таблицы.
QUICK
- Если указано это ключевое слово, то при удалении записей обработчик таблицы не будет объединять индексы, что может ускорить операцию удаления.
table_name
- Имя таблицы в базе данных, из которой будет происходить удаление строк.
LIMIT
- Задает максимальное количество строк, которые могут быть удалены за текущий запрос.
SET DATEFORMAT dmy -- задаем привычный формат даты: день.месяц.год
DELETE FROM Заказ WHERE СрокПоставки < '01.01.2013' GO
Предикаты помимо имен, помимо констант обычно включают в себя операторы сравнения и логические связи (и, или, не), которые перечислены в порядке убывания их старшинства.
В свою очередь операторы сравнения имеют более высокий приоритет, чем логическая связка, что часто позволяет обходиться без круглых скобок.
В остальном построение предикатов подчинаяется страндартным правилам принятым в большинстве языков программирования
Скобки необходимы:
WHERE (фамилия= «Иванов» OR фамилия = «Петров») AND (N_группы=1234 OR N_группы=4321)
Предикаты могут включать в себя операторы IN, BETWEEN
IN задает множество значений
BETWEEN задает интервал значений, который используется в качестве критериев отбора данных
WHERE фамилия IN(‘Иванов’, ‘Петров’)
AND N_группы BETWEEN 472301 AND 472303
Оператор BETWEEN всегда используется со связкой AND, разделяющий граничные значения интервала.
Для текстовых полей предикатор может использовать оператор LIKE, который задает строку символов в качестве критерия отбора данных.
Эта строка может включать в себя метасимволы «%» и «_»
«%» - любое (в том числе нулевое) число символов
«_» - один любой символ
WHERE фамилия LIKE ‘A%’
WHERE фамииля LIKE ‘Я_о%ий’ AND N_группы 472301
WHERE фамилия LIKE ‘И%ов’ OR фамилия ‘П%ов’
Зарезервированное слово NULL всегда используется в предикатах со связкой IS
DELETE FROM Студент
WHERE N_группы IS NULL AND фамилия IS NOT NULL
Синтаксис команды UPDATE
UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
SET col_name1=expr1 [, col_name2=expr2, ...]
[WHERE where_definition]
[LIMIT #]
tbl_name
- Задает имя таблицы, в которой будут обновляться записи. На момент запуска команды UPDATE таблица с таким именем должна существовать в базе данных.
LOW_PRIORITY
- Если указан этот параметр, то обновление записи будет отложена до тех пор, пока другие сценарии не закончат чтение из этой таблицы.
IGNORE
- Если некоторые поля таблицы имеют ключи PRIMARY или UNIQUE, и производится обновление строки, в которой эти поля имеют дублирующее значение, то действие команды аварийно завершается и выдается ошибка №1062 ("Duplicate entry 'val' for key N"). Если в команде INSERT указано ключевое слово IGNORE, то обновление записей не прерывается, а строки с дублирующими значениями просто не изменяются.
SET
- После этого ключевого слова должен идти список полей таблицы, которые будут обновлены и непосредственно сами новые значения полей в виде:
WHERE
- Задает условие отбора записей, подлежащих изменению.
LIMIT
- Задает максимальное количество строк, которые могут быть изменены.
Конструкция выражения может включать в себя константы, имена полей и знаки арифметических операций (чаще всего просто константа)
SELECT[DISTINCT] {*|<имя_поля>[<,имя_поля>…]}
FROM<имя_поля>[<,имя_поля>…]
[WHERE<предикат>]
[GROUP BY < имя_поля > [, < имя_поля >…]]
[Having<предикат>]
[ORDER BY < имя_поля > [, < имя_поля >…]]
(только первые 2 обязательные)
В отличие от предыдущих команд, которые не возвращают результата, команда на выборку возвращает результирующий набор данных
SELECT *
FROM студент,
SELECT фамилия, год_рождения
FROM студент,
SELECT *
FROM студент,
WHERE №группы = 473602
С помощью зарезервированного слова DISTINCT устраняются повторы строк в результирующем наборе данных:
SELECT DISTINCT год_рождения
FROM студент
WHERE №группы = 473602
В предложении ORDER BY указываются поля по которым выполняется сортировка строк результирующего набора данных ( в порядке убывания их приоритета).
С помощью ASC и DESC можно указать соответственно возраст или убывающий порядок сортировки для которого поля, входящего в предложение ORDER BY (по умолчанию – сортировка по возрастанию(ASC)).
SELECT *
FROM студент
ORDER BY год_рождения DESC, фамилия
Использование агрегатных функций
Агрегатная функция предназначена для подсчета итоговых значений, применительно ко всему набору данных, определяемому командами:
COUNT –подсичтать число строк или значения поля, отличных от нуля.
SUM -суммарное значение
AVG-среднее значение
MAX/MIN – max и min значение
SELECT COUNT (*), COUNT (год_рождения),
COUNT (DISTINCT год_рождения)
FROM студент
WHERE №группы = 473602
SELECT MIN(оценка), AVG(оценка), MAX(оценка), SUM(оценка)
FROM экзамен
WHERE семестр = 3 AND №зачетки = 12345;
Использование вычислительных полей
Вычисляемые поля- поля, содержащиеся в результирующем наборе данных, но отсутствующие в таблицах и представления БД.
SELECT фамилия, год_рождения – год_рождения AS возраст_поступления
FROM студент
WHERE №группы = 473602
AS( можно задать или вычисл. поля)
SELECT фамилия, год_поступления – год_рождения AS возраст_поступления
FROM студент
WHERE №группы = 473602
Выражение вычисляемых полей могут включать в себя имена полей, константы, агрегатные функции и знаки арифметических операций
При этом знак + используется для контрапенации(соединения, слиния) текстовых значений.
SELECT AVG(год_поступленеия – год_рождения)
AS средний возраст
FROM студент
Команда выборки данных с предложением GROUP BY позволяет группировать строки набора данных на основе значений одного или нескольких полей и для каждой группы подсчитать итоговые данные с помощью агрегатных функций.
SELCT факультет, COUNT(*)
FROM группа
GROUP BY факультет
SELECT год_рождения, COUNT(*)
FROM группа
WHERE №группы = 473602