Организация баз данных

не добавляет ничего нового в язык SQL.

Стандарт SQL2 допускает еще один вариант запроса на простое внутреннее объединение таблиц Students и Groups. Так как связанные столбцы этих таблиц имеют одинаковые имена и сравниваются на предмет равенства (что делается довольно часто), то можно использовать альтернативную форму предложения ON, в которой задается список имен связанных столбцов:

SELECT StName, GrName

FROM Students INNER JOIN Groups USING (GrNo)

Ниже приведен синтаксис оператора JOIN:

естественное соединение.

FROM спецификация_таблиц,…,

таблица1

NATURAL {INNER|FULL[OUTER]|LEFT[OUTER]|RIGHT[OUTER]}JOIN

таблица2 …

соединение с использованием выражения.

FROM спецификация_таблицы,…,

таблица1

{INNER|[OUTER] FULL|[OUTER]LEFT|[OUTER]RIGHT} JOIN

таблица2

ON условие | USING(список_столбцов),…

объединение или декартово произведение.

FROM спецификация_таблицы,…,

таблица1 {UNION | CROSS JOIN} таблица2 ,…

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

SELECT StName, GrName

FROM Students NATURAL INNER JOIN Groups


Внешние объединения в стандарте SQL2

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

SELECT Chief.TName, SubOrdinate.TName

FROM Teachers AS Chief FULL OUTER JOIN Teachers AS SubOrdinate

ON Chief.TNo = SubOrdinate.TChiefNo

Результат такого запроса (данные из Приложения А) приведен на .


Chief.TName SubOrdinate.TName
NULL Иванов
Иванов Петров
Петров Стрельцов
Петров Сидоров
Сидоров NULL
Стрельцов NULL


рис. 17.2 Результатом такого запроса на внешнее объединение.


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

Ключевое слово OUTER, так же как и ключевое слово INNER, в стандарте SQL2 не является обязательным. Поэтому предыдущий запрос можно, было бы переписать следующим образом:

SELECT Chief.TName, SubOrdinate.TName

FROM Teachers AS Chief FULL JOIN Teachers AS SubOrdinate

ON Chief.TNo = SubOrdinate.TChiefNo

По слову FULL СУБД сама определяет, что запрашивается внешнее объединение.

Вполне естественно, что в стандарте SQL2 левое и правое внешние объединения обозначаются словами LEFT и RIGHT вместо слова FULL. Вот вариант того же запроса, определяющий левое внешнее объединение:

SELECT Chief.TName, SubOrdinate.TName

FROM Teachers AS Chief LEFT OUTER JOIN Teachers AS SubOrdinate

ON Chief.TNo = SubOrdinate.TChiefNo

В результате такого запроса (данные из Приложения А.) будет получено следующее отношение ().


Chief.TName SubOrdinate.TName
Иванов Петров
Петров Стрельцов
Петров Сидоров
Сидоров NULL
Стрельцов NULL


рис. 17.2 Результатом такого запроса на внешнее объединение


Перекрестные объединения и запросы на объединение в SQL2

Расширенное предложение FROM в стандарте SQL2 поддерживает также два других способа соединения данных из двух таблиц – декартово произведение и запросы на объединение. Строго говоря, ни один из них не является операцией "объединения", но они поддерживаются в стандарте SQL2 с помощью тех же самых предложений, что и внутренние и внешние объединения. Вот запрос, создающий декартово произведение таблиц Students и Groups:

SELECT *

FROM Students CROSS JOIN Groups


Многотабличные объединения в стандарте SQL2

Одно из крупных преимуществ расширенного предложения FROM заключается в том, что оно дает единый стандарт для определения как внутренних и внешних объединений, так и произведений и запросов на объединение. Другим, даже еще более важным преимуществом этого предложения является то, что оно обеспечивает очень ясную и четкую спецификацию объединений трех и четырех таблиц, а также произведений и запросов на объединение. Для построения этих сложных объединений любые выражения описанные ранее, могут быть заключены в круглые скобки. Результирующее выражение, в свою очередь, можно использовать для создания других выражений объединения, как если бы оно было простой таблицей. Точно так же, как SQL позволяет с помощью круглых скобок комбинировать различные арифметические операции (+, –, * и /) и строить сложные выражения, стандарт SQL2 дает возможность создавать сложные выражения для объединений.


Итоговые запросы на чтение. Агрегатные функции


Для подведения итогов по информации, содержащейся в базе данных, в SQL предусмотрены агрегатные (статистические) функции. Агрегатная функция принимает в качестве аргумента какой-либо столбец данных целиком, а возвращает одно значение, которое определенным образом подытоживает этот столбец. Например, агрегатная функция AVG() принимает в качестве аргумента столбец чисел и вычисляет их среднее значение.

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

функция SUM() вычисляет сумму всех значений, содержащихся в столбце:

SUM(выражение | [DISTINCT] имя_столбца)

функция AVG() вычисляет среднее всех значений, содержащихся в столбце:

AVG(выражение | [DISTINCT] имя_столбца)

функция MIN() находит наименьшее среди всех значений, содержащихся в столбце:

MIN(выражение | имя_столбца)

функция МАХ() находит наибольшее среди всех значений, содержащихся в столбце:

MAX(выражение | имя_столбца)

функция COUNT() подсчитывает количество значений, содержащихся в столбце:

COUNT([DISTINCT] имя_столбца)

функция COUNT(*) подсчитывает количество строк в таблице результатов запроса:

COUNT(*)


Агрегатные функции и значения NULL

В стандарте ANSI/ISO также определены следующие точные правила обработки значений NULL в агрегатных функциях:

если какие-либо из значений, содержащихся в столбце, равны NULL, при вычислении результата функции они исключаются;

если все значения в столбце равны NULL, то функции SUM(), AVG(), MIN() и MAX () возвращают значение NULL; функция COUNT () возвращает ноль;

если в столбце нет значений (т.е. столбец пустой), то функции SUM() , AVG(), MIN() и МАХ() возвращают значение NULL; функция COUNT() возвращает ноль;

функция COUNT(*) подсчитывает количество строк и не зависит от наличия или отсутствия в столбце значений NULL; если строк в таблице нет, эта функция возвращает ноль.

Запросы с группировкой (предложение GROUP BY)


Итоговые запросы, о которых до сих пор шла речь в настоящей главе рассчитывают итоговые результаты на основании всех записей в таблице. Однако необходимость в таких вычислениях возникает достаточно редко, чаще бывает необходимо получать промежуточные итоги на основании групп записей в таблице. Эту возможность предоставляет предложение GROUP BY оператора SELECT.

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

SELECT StName, AVG(Mark)

FROM Marks INNER JOIN Students USING(StNo)

GROUP BY StName


Несколько столбцов группировки

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

SELECT StName, Semester, AVG(Mark)

FROM Marks INNER JOIN Students USING(StNo)

GROUP BY StName, Semester


Ограничения на запросы с группировкой

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

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

константа;

агрегатная функция, возвращающая одно значение для всех строк, входящих в группу;

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

выражение, включающее в себя перечисленные выше элементы.

На практике в список возвращаемых столбцов запроса с группировкой всегда входят столбец группировки и агрегатная функция. Если последняя не указана, значит, запрос можно более просто выразить с помощью ключевого слова DISTINCT без использования предложения GROUP BY. И наоборот, если не включить в результаты запроса столбец группировки, вы не сможете определить, к какой группе относится каждая строка результатов.


Значения NULL в столбцах группировки

В стандарте ANSI определено, что два значения NULL в предложении GROUP BY равны.

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

Условия поиска групп (предложение HAVING)

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


Ограничения на условия поиска групп

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


Предложение HAVING без GROUP BY

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


Вложенные запросы


Вложенным (или подчиненным) запросом называется запрос, содержащийся в предложении WHERE или HAVING другого оператора SQL. Вложенные запросы позволяют естественным образом обрабатывать запросы, выраженные через результаты других запросов.

Чаще всего вложенные запросы указываются в предложении WHERE оператора SQL. Когда вложенный запрос содержится в данном предложении, он участвует в процессе отбора строк.

Например, вывести список фамилий студентов, средний балл которых выше 4,5:

SELECT StName

FROM Students

WHERE (SELECT AVG(Mark)

FROM Marks

WHERE Marks.StNo = Students.StNo) > 4.5

Вложенный запрос всегда заключается в круглые скобки, но по-прежнему сохраняет знакомую структуру оператора SELECT, содержащего предложение FROM и необязательные предложения WHERE, GROUP BY и HAVING. Структура этих предложений во вложенном запросе идентична их структуре в оператора SELECT; во вложенном запросе эти предложения выполняют свои обычные функции. Однако между вложенным запросом и оператором SELECT имеется ряд отличий:

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

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

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

Имена столбцов во вложенном запросе могут являться ссылками на столбцы таблиц главного запроса. Это так называемые внешние ссылки (ссылка на Students.StNo в предыдущем примере). Внешние ссылки необязательно должны присутствовать во вложенном запросе.

Условия поиска во вложенном запросе


Вложенный запрос всегда является частью условия поиска в предложении WHERE или HAVING. Ранее были рассмотрены простые условия поиска, которые могут использоваться в этих предложениях. Кроме того, в SQL имеются следующие условия поиска во вложенном запросе.

Сравнение с результатом вложенного запроса (=, <>, <, <=, >, >=). Сравнивает значение выражения с одним значением, возвращенным вложенным запросом. Эта проверка напоминает простое сравнение.

проверяемое_выражение  | = | <> | < | <= | > | >= | вложенный_запрос

Сравнение с результатом вложенного запроса является модифицированной формой простого сравнения. Значение выражения сравнивается со значением, возвращенным вложенным запросом, и если условие сравнения выполняется, то проверка возвращает значение TRUE. Эта проверка используется для сравнения значения из проверяемой строки с одним значением, возвращенным вложенным запросом, как показано первом примере.

Проверка на принадлежность результатам вложенного запроса (IN). Проверка на принадлежность результатам вложенного запроса (ключевое слово IN) является видоизмененной формой простой проверки на членство в множестве.

проверяемое_выражение [NOT] IN вложенный_запрос

Одно значение сравнивается со столбцом данных, возвращенных вложенным запросом, и если это значение равно одному из значений в столбце, проверка возвращает TRUE. Данная проверка используется, когда необходимо сравнить значение из проверяемой строки с множеством значений, возвращенных вложенным запросом. Например, вывести всю информацию о студентах, учащихся в группах сназваниями, начинающимися на букву ІАІ:

SELECT *

FROM Students

WHERE GrNo IN (SELECT GrNo FROM Groups WHERE GrName LIKE ‘А%’)

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

[NOT] EXISTS вложенный_запрос

Например, вывести фамилии студентов, которые в 1-м семестре сдали хотябы одну дисциплину:

SELECT StName

FROM Students

WHERE EXISTS (SELECT *

FROM Marks

WHERE Marks.StNo = Students.StNo AND Semester = 1)

Многократное сравнение (ANY и ALL). В проверке IN выясняется, не равно ли некоторое значение одному из значений, содержащихся в столбце результатов вложенного запроса. В SQL имеется две разновидности многократного сравнения – ANY и ALL, расширяющие предыдущую проверку до уровня других операторов сравнения. В обеих проверках некоторое значение сравнивается со столбцом данных, возвращенным вложенным запросом.

проверяемое_выражение  | = | <> | < | <= | > | >= |  ANY | ALL вложенный_запрос

Проверка ANY. В проверке ANY используется один из шести операторов сравнения (=, <>, <, <=, >, >=) для того, чтобы сравнить одно проверяемое значение со столбцом данных, возвращенным вложенным запросом. Проверяемое значение поочередно сравнивается с каждым значением, содержащимся в столбце. Если любое из этих сравнений дает результат TRUE, то проверка ANY возвращает значение TRUE.

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

SELECT StName

FROM Students

WHERE 5 = ANY (SELECT Mark

FROM Marks

WHERE Marks.StNo = Students.StNo AND Semester = 1)

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

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

если операция сравнения имеет значение TRUE хотя бы для одного значения в столбце, то проверка ANY возвращает значение TRUE (имеется некоторое значение, полученное вложенным запросом, для которого условие сравнения выполняется).

если операция сравнения имеет значение FALSE для всех значений в столбце, то проверка ANY возвращает значение FALSE (можно утверждать, что ни для одного значения, возвращенного вложенным запросом, условие сравнения не выполняется);

если операция сравнения не имеет значение TRUE ни для одного значения в столбце, но в нем имеется одно или несколько значений NULL то проверка ANY возвращает результат NULL. (В этой ситуации невозможно но с определенностью утверждать, существует ли полученное вложенным запросом значение, для которого выполняется условие сравнения; может быть, существует, а может и нет – все зависит от "настоящих" значений неизвестных данных.)

Проверка ALL. В проверке ALL, как и в проверке ANY, используется один из шести операторов (=, <>, <, <=, >, >=) для сравнения одного проверяемого значения со столбцом данных, возвращенным вложенным запросом. Проверяемое значение поочередно сравнивается с каждым значением, содержащимся в столбце. Если все сравнения дают результат TRUE, то проверка ALL возвращает значение TRUE.

Например, вывести список фамилий студентов, получивших в первом семестре только удовлетворительные оценки.

SELECT StName

FROM Students

WHERE 3 = ALL (SELECT Mark

FROM Marks

WHERE Marks.StNo = Students.StNo AND Semester = 1)

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

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

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

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

если операция сравнения не дает результат FALSE ни для одного значения в столбце, но для одного или нескольких значений дает результат NULL, то проверка ALL возвращает значение NULL. В этой ситуации нельзя с определенностью утверждать, для всех ли значений, полученных вложенным запросом, справедливо условие сравнения; может быть, для всех, а может и нет – все зависит от "настоящих" значений неизвестных данных.


Вложенные запросы и объединения

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


Уровни вложенности запросов

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


Вложенные запросы в предложении HAVING

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

Литература:


Джеймс Р. Грофф, Пол Н. Вайнберг. SQL: полное руководство: пер.с англ. –К.: Издательская группа BHV, 2000.–608с. Стр. 169–217.

Язык SQL. (продолжение)


11.1 Внесение изменений в базу данных.

11.2 Удаление существующих данных (Оператор DELETE)

11.3 Обновление существующих данных (Оператор UPDATE)

11.4 Определение структуры данных в SQL

11.5 Понятие представления.

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

11.7 Системный каталог (самостоятельное изучение)


Внесение изменений в базу данных.


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

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


Добавление новых данных (оператор INSERT).

Однострочный оператор INSERT, синтаксис которого описан ниже, добавляет в таблицу новую строку. В предложении INTO указывается таблица, в которую добавляется новая строка (целевая таблица), а в предложении VALUES содержатся значения данных для новой строки. Список столбцов определяет, какие значения в какой столбец заносятся.

INSERT INTO имя_таблицы (имя_столбца,…) VALUES (константа | NULL,…)

Ниже приведен пример оператора INSERT, который добавляет информацию о новой группе ІК-99-51І в учебную базу данных:

INSERT INTO Groups(GrNo, EnterYear, GrName)

VALUES(6, 1999, 'К-99-51')

Добавление значений NULL. При добавлении в таблицу новой строки всем столбцам, имена которых отсутствуют в списке столбцов оператора INSERT, автоматически присваивается значение NULL.

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

INSERT INTO Groups

VALUES(6, 1999, 'К-99-51')

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

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

INSERT INTO имя_таблицы (имя_столбца,…) запрос


Удаление существующих данных (Оператор DELETE)

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

Оператор DELETE, синтаксис которого изображен ниже, удаляет выбранные строки данных из одной таблицы.

DELETE FROM имя_таблицы [WHERE условие_поиска]

В предложении FROM указывается таблица, содержащая строки, которые требуется удалить. В предложении WHERE указываются строки, которые должны быть удалены.

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

DELETE FROM Students

WHERE StNo = 3

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

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


Обновление существующих данных (Оператор UPDATE)


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

UPDATE имя_таблицы

SET имя_столбца = выражение,...

[WHERE условие_поиска]

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

Например, Изменить фамилию студента ІПетроваІ с кодом 2 на фамилию ІПетренкоІ:

UPDATE Students

SET StName = 'Петренко'

WHERE StNo = 2

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

Как и оператор DELETE, оператор UPDATE может одновременно обновить несколько строк, соответствующих условию поиска.

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

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


Определение структуры данных в SQL


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

Создание таблицы (оператор CREATE TABLE)

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

После выполнения оператора CREATE TABLE появляется новая таблица, которой присваивается имя, указанное в операторе. Имя таблицы должно быть идентификатором, допустимым в SQL, и не должно конфликтовать с именами существующих таблиц.

CREATE TABLE имя_таблицы(

определение столбцов

имя_столбца тип_данных [DEFAULT значение] [NOT NULL],…

определение первичного ключа

PRIMARY KEY(имя_столбца,…),

определение внешних ключей

FOREIGN KEY имя_ограничения (имя_столбца,…)

REFERENCE имя_таблицы

[ON DELETE CASCADE | SET NULL | SET DEFAULT | NO ACTION]

[ON UPDATE CASCADE | SET NULL | SET DEFAULT | NO ACTION],

условие уникальности данных

UNIQUE(имя_столбца,…),

условие проверки

CHECK(условие_поиска)

)

Определения столбцов. Столбцы новой таблицы задаются в операторе CREATE TABLE. Определения столбцов представляют собой заключенный в скобки список, элементы которого отделены друг от друга запятыми. Порядок следования определений столбцов в списке соответствует расположению столбцов в таблице. Каждое такое определение содержит следующую информацию:

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

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

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

Значение по умолчанию для столбца СУБД, которое заносится в столбец в том случае, если в операторе INSERT для таблицы не определено значение данного столбца.

Определения первичного и внешнего ключей. Кроме определений столбцов таблицы, в операторе CREATE TABLE указывается информация о первичном ключе таблицы и о ее связях с другими таблицами базы данных Эта информация содержится в предложениях PRIMARY KEY и FOREIGN KEY.

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

В предложении FOREIGN KEY задается внешний ключ таблицы и определяется связь, которую он создает для нее с другой таблицей (таблицей-предком). В нем указываются:

столбец или столбцы создаваемой таблицы, которые образуют внешний ключ;

таблица, связь с которой создает внешний ключ. Это таблица-предок;

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

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

необязательное правило удаления для данного отношения (CASCADE, SET NULL, SET DEFAULT или NO ACTION), которое определяет действие, предпринимаемое при удалении строки-предка;

необязательное правило обновления для данного отношения, которое определяет действие, предпринимаемое при обновлении первичного ключа в строке-предке;

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

Например, создать таблицу Students.

CREATE TABLE Students (

StNo INT NOT NULL,

GrNo INT NOT NULL,

StName CHAR(30) NOT NULL,

CityNo INT,

PRIMARY KEY (StNo),

FOREIGN KEY Students_Groups(GrNo)

REFERENCES Groups,

FOREIGN KEY Students_Cities(CityNo)

REFERENCES Cities,

CHECK(CHAR_LENGTH(StName)>10)

)


Удаление таблицы (оператор DROP TABLE)

Таблицы можно удалить из базы данных посредством оператора DROP TABLE.

DROP TABLE имя_таблицы CASCADE | RESTRICT

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

Стандарт SQL2 требует, чтобы оператор DROP TABLE включал в себя либо параметр CASCADE, либо RESTRICT, которые определяют, как влияет удаление таблицы на другие объекты базы данных. Если задан параметр RESTRICT и в базе данных имеются объекты, которые содержат ссылку на удаляемую таблицу, то выполнение оператора DROP TABLE закончится неуспешно. В большинстве коммерческих СУБД допускается применение оператора DROP TABLE без каких-либо параметров.


Изменение определения таблицы (оператор ALTER TABLE)

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

добавить в таблицу определение столбца;

изменить значение по умолчанию для какого-либо столбца;

добавить или удалить первичный ключ таблицы;

добавить или удалить новый внешний ключ таблицы;

добавить или удалить условие уникальности;

добавить или удалить условие проверки.


ALTER TABLE имя_таблицы

ADD определение_столбца

ALTER имя_столбца SET DEFAULT значение | DROP DEFAULT

DROP имя_столбца CASCADE | RESTRICT

ADD определение_первичного_ключа

ADD определение_внешнегого_ключа

ADD условие_уникальности_данных

ADD условие_проверки

DROP CONSTRAINT имя_ограничения CASCADE | RESTRICT


Определения доменов

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

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


Индексы (операторы CREATE/DROP INDEX)

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

Для создания и удаления индексов применяются операторы CREATE INDEX и DROP INDEX, синтаксис которых

Если Вам нужна помощь с академической работой (курсовая, контрольная, диплом, реферат и т.д.), обратитесь к нашим специалистам. Более 90000 специалистов готовы Вам помочь.
Бесплатные корректировки и доработки. Бесплатная оценка стоимости работы.

Поможем написать работу на аналогичную тему

Получить выполненную работу или консультацию специалиста по вашему учебному проекту
Нужна помощь в написании работы?
Мы - биржа профессиональных авторов (преподавателей и доцентов вузов). Пишем статьи РИНЦ, ВАК, Scopus. Помогаем в публикации. Правки вносим бесплатно.

Похожие рефераты: