История создания датчика движения: Первый прибор для обнаружения движения был изобретен немецким физиком Генрихом Герцем...
Кормораздатчик мобильный электрифицированный: схема и процесс работы устройства...
Топ:
Марксистская теория происхождения государства: По мнению Маркса и Энгельса, в основе развития общества, происходящих в нем изменений лежит...
Эволюция кровеносной системы позвоночных животных: Биологическая эволюция – необратимый процесс исторического развития живой природы...
Интересное:
Принципы управления денежными потоками: одним из методов контроля за состоянием денежной наличности является...
Подходы к решению темы фильма: Существует три основных типа исторического фильма, имеющих между собой много общего...
Аура как энергетическое поле: многослойную ауру человека можно представить себе подобным...
Дисциплины:
2019-07-12 | 707 |
5.00
из
|
Заказать работу |
|
|
Создание и модификация таблиц»
Задания:
Теоретический материал для изучения:
1. Добавление, удаление и изменение данных в таблицах
Запросы, рассмотренные ранее, были направлены на то, чтобы получить данные, содержащиеся в существующих таблицах базы данных. Главным ключевым словом таких запросов на выборку данных является SELECT. Запросы на выборку данных всегда возвращают виртуальную таблицу, которая отсутствует в базе данных и создается временно лишь для того, чтобы представить выбранные данные пользователю. При создании и дальнейшем сопровождении базы данных обычно возникает задача добавления новых и удаления ненужных записей, а также изменения содержимого ячеек таблицы. В SQL для этого предусмотрены операторы INSERT (вставить), DELETE (удалить) и UPDATE (изменить). Запросы, начинающиеся с этих ключевых слов, не возвращают данные в виде виртуальной таблицы, а изменяют содержимое уже существующих таблиц базы данных. Запросы на модификацию (добавление, удаление и изменение) данных могут содержать вложенные запросы на выборку данных из той же самой таблицы или из других таблиц, однако сами не могут быть вложены в другие запросы. Таким образом, операторы INSERT, DELETE и UPDATE в SQL-выражении могут находиться только в самом начале.
Добавление новых записей
Когда создается таблица базы данных, она не содержит никаких записей, т. е. является пустой. Чтобы наполнить таблицу данными, необходимо добавить (вставить) в нее хотя бы одну запись. Для этого служит оператор INSERT (вставить), который имеет несколько форм:
|
- INSERT INTO имяТаблицы VALUES (списокЗнач ений) – вставляет пустую запись в указанную таблицу и заполняет эту запись значениями из списка, указанного за ключевым словом VALUES. При этом первое в списке значение вводится в первый столбец таблицы, второе значение - во второй столбец и т. д. Порядок столбцов задается при создании таблицы. Данная форма оператора INSERT не очень надежна, поскольку нетрудно ошибиться в порядке вводимых значений. Более надежной и гибкой является следующая форма;
- INSERT INTO имяТаблицы (списокСтолбцов) VALUES (списокЗнач ений) - вставляет пустую запись в указанную таблицу и вводит в заданные столбцы значения из указанного списка. При этом в первый столбец из списокСтолбцов вводится первое значение из списокЗначений, во второй столбец- второе значение и т. д. Порядок имен столбцов в списке может отличаться от их порядка, заданного при создании таблицы. Столбцы, которые не указаны в списке, заполняются значением null. Иногда требуется просто добавить пустую запись. В этом случае первая форма оператора INSERT требует после ключевого слова VALUES указать список значений NULL, длина которого равна количеству столбцов. Однако есть лучшее решение:
INSERT INTO имяТаблицы (имяЛюбогоСтолбца) VALUES (NULL);
Рекомендуется использовать именно данную форму оператора INSERT. Следующий запрос добавляет новую запись в таблицу Клиенты, при этом в столбцы Имя, Телефон и Сумма_заказа вводятся значения 'Петров', '444-4444' и 25300 соответственно:
INSERT INTO Клиенты (Имя, Телефон, Сумма_заказа)
VALUES ('Петров', '444-4444', 25300);
Начиная с SQL-92 появилась возможность работать со значениями типа запись. Это позволяет за ключевым словом VALUES указать несколько наборов значений в круглых скобках (записей), которые необходимо вставить в таблицу. Например:
INSERT INTO Клиенты (Имя, Телефон, Сумма _ заказа)
VALUES
(' Петров ', '444-4444', 25300),
(' Иванов ', '555-5555', 45100),
|
(' Сидоров ', '777-7777', 1200),
(' Захаров ', '123-9870', 7800);
- INSERT INTO имяТаблицы (списокСтолбцов) SELECT... - вставляет в указанную таблицу записи, возвращаемые запросом на выборку.
На практике нередко требуется загрузить в одну таблицу данные из другой таблицы. Например, ранее созданная таблица контакты содержит имена и другие данные о клиентах, которые необходимо вставить в таблицу Клиенты. Для этого в операторе INSERT вместо VALUES можно использовать оператор SELECT, выбирающий записи, необходимые для вставки. Допустим, таблицы Клиенты и контакты имеют одноименные и однотипные столбцы Имя, Адрес и Телефон. Требуется добавить все записи из таблицы контакты в таблицу клиенты. Это можно сделать с помощью следующего запроса:
INSERT INTO Клиенты (Имя, Адрес, Телефон)
SELECT Имя, Адрес, Телефон FROM Контакты;
Следующий запрос вставляет в таблицу клиенты только те записи из таблицы контакты, которых в ней еще нет. Таким образом, происходит дополнение первой таблицы данными из второй таблицы:
INSERT INTO Клиенты (Имя, Адрес, Телефон)
SELECT Имя, Адрес, Телефон FROM Контакты
WHERE Клиенты. Имя <> Контакты. Имя
AND
Клиенты. Адрес <> Контакты. Адрес
AND
Контакты. Имя IS NOT NULL;
Здесь в операторе WHERE применено сложное условие на случай, если в таблицах окажутся однофамильцы или в таблице контакты окажутся неопределенные имена.
С помощью оператора INSERT можно добавить одну или несколько записей только в одну таблицу.
Кроме того, необходимо учитывать следующие обстоятельства:
- таблица может иметь столбец типа SERIAL (счетчик), имеющий уникальные значения, которые СУБД назначает автоматически. Это обеспечивает уникальность всех записей таблицы и, следовательно, ее соответствие 1-ой нормальной форме. Поэтому в списке столбцов в операторе INSERT не следует указывать столбцы типа SERIAL;
- если список столбцов, указанный в операторе INSERT, содержит не все столбцы таблицы, то в оставшиеся столбцы (за исключением столбца типа SERIAL) будут введены значения NULL (неопределенное значение), а в столбец типа SERIAL правильное числовое значение введет СУБД;
- при введении значений в столбцы новой записи необходимо следить, чтобы типы значений соответствовали типам столбцов таблицы. При необходимости можно воспользоваться функцией преобразования типов CAST ();
- таблица может иметь ограничения (смотри пункт 2.1). Если вводимые данные не удовлетворяют условиям этих ограничений, то запрос на добавление данных не будет выполнен. Так, например, если в ограничении на какой-либо столбец указано, что он не может иметь значения NULL, то попытка добавить пустую запись приведет к ошибке. В этом случае необходимо использовать оператор INSERT INTO с ключевым словом VALUES, чтобы сразу ввести значение, удовлетворяющее ограничениям.
|
Удаление записей
Для удаления записей из таблицы применяется оператор DELETE (удалить):
DELETE FROM имя Таблицы WHERE условие;
Данный оператор удаляет из указанной таблицы записи (а не отдельные значения столбцов), которые удовлетворяют указанному условию. Условие - это логическое выражение. Следующий запрос удаляет записи из таблицы клиенты, в которой значение столбца имя равно 'Иванов':
DELETE FROM Клиенты WHERE Имя = ' Иванов ';
Если таблица Клиенты содержит несколько записей, в которых есть клиент Иванов, то все они будут удалены. В операторе WHERE может находиться подзапрос на выборку данных (оператор SELECT). Подзапросы в операторе DELETE работают точно так же, как и в операторе SELECT.
Пусть в базе данных имеется таблица Продажи (ID_товара, Сумма_заказа, ID_клиента), содержащая сведения о продажах товаров клиентам. Требуется удалить из этой таблицы записи о тех клиентах, которые приобрели товары на сумму, меньшую 1000. Для этого можно использовать такой запрос:
DELETE FROM Продажи Т 1
WHERE 1000 >=
(SELECT SUM (Т 2. Сумма _ заказа) FROM Продажи Т 2
WHERE Т 1. ID _ клиента = Т 2. ID _ клиента);
Здесь запрос на удаление записей из таблицы продажи содержит связанный (коррелированный) подзапрос, вычисляющий сумму значений столбца Сумма_заказа. Обратите внимание на использование двух различных псевдонимов для одной и той же таблицы. При выполнении данного запроса на удаление происходит, как и положено в случае связанных подзапросов, последовательный просмотр записей в таблице продажи. Для каждой записи проверяется условие оператора WHERE, а именно выполняется подзапрос, вычисляющий сумму значений столбца Cумма_заказа для всех записей, в которых идентификатор клиента равен значению этого идентификатора в текущей записи (текущей называется запись, просматриваемая в данный момент). Если вычисленное значение не превышает 1000, то условие оператора WHERE выполняется и текущая запись удаляется, в противном случае запись не удаляется. Далее происходит переход к следующей записи, рассматриваемой в качестве текущей, и описанные действия повторяются снова.
|
Теперь рассмотрим пример, в котором требуется удалить записи в одной таблице в соответствии с условием, обрабатывающим записи в другой таблице. Допустим, в базе данных имеются две таблицы:
Продажи (ID_товара, Сумма_заказа, ID_клиента);
Клиенты (ID_клиента, Имя, Адрес).
Требуется удалить из таблицы Клиенты те записи о клиентах, которые приобрели товары на сумму, меньшую 1000. Суммы заказов клиентов содержатся в таблице Продажи, а записи необходимо удалять из таблицы клиенты, в которой нет данных о приобретенных товарах. Тем не менее, для решения этой задачи можно использовать запрос, аналогичный предыдущему:
DELETE FROM Клиенты Т 1
WHERE 1000 >=
(SELECT SUM (Т 2. Сумма _ заказа) FROM Продажи Т 2
WHERE Т 1. ID _ клиента = Т 2. ID _ клиента);
Операция удаления записей из таблицы является опасной в том смысле, что связана с риском необратимых потерь данных в случае семантических (но не синтаксических) ошибок при формулировке SQL-выражения. Чтобы избежать неприятностей, перед удалением записей рекомендуется сначала выполнить соответствующий запрос на выборку, чтобы просмотреть, какие записи будут удалены. Так, например, перед выполнением рассмотренного ранее запроса на удаление не помешает выполнить соответствующий запрос на выборку:
SELECT * FROM Клиенты Т 1
WHERE 1000 >=
(SELECT SUM (Т2.Сумма_заказа) FROM Продажи Т2
WHERE Т1. ID _клиента= Т2. ID _клиента);
Для удаления всех записей из таблицы достаточно использовать оператор DELETE без ключевого слова WHERE. При этом сама таблица со всеми определенными в ней столбцами остается и готова для вставки новых записей.
Например:
DELETE FROM Клиенты;
Таблица может иметь ограничения (см. пункт 2.1), которые могут препятствовать удалению некоторых или всех записей.
Изменение данных
Для изменения значений столбцов таблицы применяется оператор UPDATE (изменить, обновить). Чтобы изменить значения в одном столбце таблицы в тех записях, которые удовлетворяют некоторому условию, следует выполнить такой запрос:
UPDATE имяТаблицы SET имяСтолбца = значение WHERE условие;
За ключевым словом SET (установить) следует выражение равенства, в левой части которого указывается имя столбца, а в правой - выражение, значение которого следует сделать значением данного столбца. Эти установки будут выполнены в тех записях, которые удовлетворяют условию в операторе WHERE. Чтобы одним оператором UPDATE установить новые значения сразу для нескольких столбцов, вслед за ключевым словом SET записываются соответствующие выражения равенства, разделенные запятыми:
|
UPDATE имяТаблицы
SET имяСтолбца1 = значение1,
имяСтолбца 2 = значение 2,
...,
имяСтолбца N = значение N
WHERE условие;
Например, следующий запрос изменяет значения столбцов Телефон и Сумма_заказа в таблице Клиенты для тех записей, в которых столбец Имя имеет значение 'Иванов':
UPDATE Клиенты
SET Телефон = '333-1234', Сумма _ заказа = 2570
WHERE Имя = ' Иванов ';
Использование оператора WHERE в операторе UPDATE не обязательно. Если он отсутствует, то указанные в SET изменения будут произведены для всех записей таблицы.
Операция изменения записей, как и их удаление, связана с риском необратимых потерь данных в случае семантических ошибок при формулировке SQL-выражения. Например, стоит только забыть написать оператор WHERE, и будут обновлены значения во всех записях таблицы. Чтобы избежать подобных неприятностей, перед обновлением записей рекомендуется выполнить соответствующий запрос на выборку, чтобы просмотреть, какие записи будут изменены. Например, перед выполнением приведенного ранее запроса на обновление данных не помешает выполнить соответствующий запрос на выборку данных:
SELECT * FROM Клиенты WHERE Имя = 'Иванов';
Таблица может иметь ограничения (см.пункт 2.1). Если устанавливаемые значения столбцов не удовлетворяют условиям этих ограничений, то запрос на обновление данных выполнен не будет.
Условие в операторе WHERE может содержать подзапросы, в том числе и связанные. Пусть в базе данных имеется таблица Продажи (ID_товара, Сумма_заказа, ID_клиента), содержащая сведения о продажах товаров клиентам. Предположим, что требуется сделать 5% скидку тем клиентам, которые приобрели товары на сумму, большую 1000. Для этого следует изменить значения столбца Сумма_заказа, просто умножить их на 0,95. Однако эти изменения должны быть выполнены, только если сумма значений этого поля для данного клиента превышает 1000. Таким образом, запрос на изменение данных должен содержать связанный подзапрос:
UPDATE Продажи Т1
SET Сумма_заказа = Сумма_заказа*0.95
WHERE 1000 <
(SELECT SUM (Т2.Сумма_заказа) FROM Продажи Т2
WHERE Т1. ID _клиента = Т2. ID _клиента);
Здесь запрос на изменение данных из таблицы продажи содержит связанный (коррелированный) подзапрос, вычисляющий сумму значений столбца Cумма_заказа. Обратите внимание на использование двух различных псевдонимов для одной и той же таблицы. При выполнении данного запроса происходит последовательный просмотр записей в таблице продажи. Для каждой записи проверяется условие оператора WHERE, а именно выполняется подзапрос, вычисляющий сумму значений столбца Сумма_заказа для всех записей, в которых идентификатор клиента равен текущему значению этого идентификатора. Если вычисленное значение превышает 1000, то условие оператора WHERE выполняется и происходит изменение данных в соответствии с выражением SET, в противном случае изменения не вносятся. Далее происходит переход к следующей записи, рассматриваемой в качестве текущей, и описанные действия повторяются снова.
Нередко требуется обновить значения столбцов в зависимости от их текущих значений. В SQL:2003 для этого можно использовать оператор CASE, возвращающий значения.
При изменении значений в столбцах таблицы необходимо следить, чтобы типы значений соответствовали типам столбцов. При необходимости можно воспользоваться функцией преобразования типов CAST ().
Создание таблиц
Создание таблицы производится с помощью оператора CREATE TABLE (создать таблицу) с указанием необходимых параметров. При этом создается постоянная таблица. Чтобы удалить ее из базы данных, требуется выполнить специальный SQL-оператор.
Для создания временной таблицы используется оператор CREATE TEMPORARY TABLE (создать временную таблицу). Временная таблица, в отличие от постоянной, существует только в течение сеанса работы с базой данных, в котором она была создана. Однако временная таблица может быть доступна другим пользователям, как и постоянная таблица. Обычно временные таблицы создаются для представления в них текущих итоговых (отчетных) данных, доступных нескольким пользователям базы данных. Далее приведен синтаксис оператора CREATE:
CREATE [ TEMPORARY ] TABLE имяТаблицы (
{ Столбец1 тип [(размер)] [ограничение_столбца] [,...] }
{ [, CONSTRAINT ограничение__таблицы] [,... ] }
);
Здесь квадратные и фигурные скобки, в отличие от круглых, не являются элементами синтаксиса. В квадратных скобках заключены необязательные элементы, а в фигурных - элементы, которые могут повторяться.
Для создания таблицы необходимо указать ее имя и определить столбцы. Определение столбца включает его имя и тип. Если указывается длина столбца, то она заключается в круглые скобки после типа. Кроме того, можно указать ограничения для столбца. Все перечисленные элементы определения столбца указываются друг за другом через пробел. Если создаваемая таблица содержит несколько столбцов, то их определения разделяются запятыми. Ограничение может быть определено и для всей таблицы, а не только для ее столбцов. В этом случае используется ключевое слово CONSTRAINT (ограничение), после которого указывается само ограничение.
Только что созданная таблица пуста, т. е. не содержит ни одной записи.
Далее приведен запрос на создание простой таблицы без ограничений:
CREATE TABLE Студент (
ID _ студента INTEGER,
ФИО CHAR (20),
Специальность CHAR (15),
Примечание VARCHAR
);
Данный запрос создает таблицу Студент, содержащую четыре столбца. Первый столбец целочисленный, а три других - символьные.
Ограничения устанавливаются на данные, которые вводятся в таблицу. Например, на числовой столбец можно наложить ограничение, заключающееся в том, что вводимое число должно находиться в некотором диапазоне. Другой пример ограничения: значение столбца не должно быть неопределенным.
Нередко ограничения устанавливаются и отслеживаются в приложениях, работающих с базами данных. Тем не менее, ограничения могут быть установлены и могут поддерживаться многими современными СУБД. Если вы создаете таблицу посредством SQL, то также имеете возможность задать эти ограничения, а выполнять их будет СУБД. При этом если одна и та же база данных используется несколькими приложениями, то вам придется установить ограничения только один раз, а не столько, сколько имеется приложений.
Ограничения для столбцов
В табл. приведены основные ограничения для столбцов. Однако существуют и более сложные ограничения, которые здесь не рассматриваются.
Примечание: Microsoft Access не поддерживает ключевые слова DEFAULT и CHECK в определениях ограничений.
Рассмотрим в качестве примера создание таблицы Студент (ID_студента, ФИО, Специальность, Примечание). Идентификатор студента (целочисленный столбец ID_студента) должен однозначно идентифицировать запись о студенте, т. е. иметь определенные и уникальные значения. Таким образом, данный столбец должен быть первичным ключом. От столбца ФИО (фамилия, имя и отчество) потребуем, чтобы в нем не было неопределенных значений. Целочисленный столбец Специальность должен содержать номера специальностей, которые не превышают 12. Тогда запрос на создание такой таблицы будет иметь вид:
CREATE TABLE Студент (
ID _ студента INTEGER PRIMARY KEY,
ФИО CHAR(20) NOT NULL,
Специальность INTEGER CHECK (Специальность < 12),
Примечание VARCHAR
);
Предположим, что данный запрос выполнен. Тогда следующий запроса добавление новой записи в таблицу Студент вызовет сообщение об ошибке и не будет выполнен:
INSERT INTO Студент;
Это произойдет потому, что данный запрос добавляет пустую запись, все столбцы которой содержат значение NULL, и, следовательно, не выполняются ограничения для первых двух столбцов. А следующий запрос, добавляющий в таблицу первую запись и устанавливающий определенные значения для столбцов, не вызовет проблем со стороны СУБД:
INSERT INTO Студент (ID _ студента, ФИО, Специальность)
VALUES (1, ' Иванов Иван Иванович ', 9);
Попытаемся вслед за данным запросом добавить еще одну запись:
INSERT INTO Студент (ID __ студента, ФИО, Специальность)
VALUES (1, ' Петров Петр Петрович ', 5);
Данный запрос не будет выполнен из-за нарушения ограничения, наложенного на первый столбец: его значения должны быть не только определенными (отличными от NULL), но и уникальными. Запись можно добавить, если столбцу ID_студента присвоить, например, значение 2.
Ограничения для столбцов можно выразить и иначе - через ограничения доменов. Домен определяется как множество значений. Домен в реляционной теории связывается с атрибутом отношения и, как таковой, определяет некоторое ограничение на этот атрибут (атрибут может принимать значения только из этого домена). В SQL можно создать домен, сначала не связанный ни с какими атрибутами (столбцами), определив для него допустимые значения: тип данных и дополнительные ограничения. Затем этот домен можно задать в качестве ограничений для любого столбца в любой таблице подобно тому, как задаются типы столбцов. Другими словами, домен можно связать с одним или несколькими столбцами различных таблиц. В ряде случаев этот прием очень удобен, особенно если у вас имеется несколько "однотипных" столбцов в различных таблицах базы данных.
Чтобы создать домен, используется такой синтаксис:
Ограничения для таблиц
Ограничения на вводимые данные можно назначить не только для отдельных столбцов, но и для таблицы в целом. Это удобно в тех случаях, когда необходимо для нескольких столбцов назначить одинаковые ограничения. Кроме того, если первичный ключ составной (т. е. состоит из нескольких столбцов), то указать его можно только как ограничение для таблицы, а не для столбца. Все определение ограничения для таблицы указывается после определений столбцов и состоит из ключевого слова CONSTRAINT, за которым следует выражение, определяющее непосредственно само ограничение. В табл. приведены основные ограничения для таблицы.
В следующем примере создается таблица Студент (Фамилия, имя, Отчество, Специальность, Примечание). Предполагается, что комбинация значений первых трех столбцов должна однозначно идентифицировать запись в таблице, т. е. являться первичным ключом.
CREATE TABLE Студент (
Фамилия CHAR(20),
Имя CHAR (15),
Отчество CHAR (20),
Специальность INTEGER,
Примечание VARCHAR,
CONSTRAINT PRIMARY KEY (Фамилия, Имя, Отчество)
);
Следующие два запроса на добавление записей будут выполнены, поскольку комбинации значений столбцов, определяющих первичный ключ, не содержат NULL и отличаются друг от друга:
INSERT INTO Студент (Фамилия, Имя, Отчество, Специальность)
VALUES (' Петров ', ' Петр ', ' Петрович ', 5);
INSERT INTO Студент (Фамилия, Имя, Отчество, Специальность)
VALUES (' Петров ', ' Петр ', ' Иванович ', 5);
Внешние ключи
Одна из важнейших разновидностей ограничений связана с определением внешних ключей. Внешний ключ - это столбец или группа столбцов, соответствующих первичному ключу другой таблицы. Чтобы понять синтаксис выражения, определяющего внешний ключ, рассмотрим пример.
Пусть в базе данных имеются две таблицы:
Заказы (ID_заказа, ID_клиента)- содержит сведения о том, какие заказы сделал тот или иной клиент;
Клиенты (ID_клиента, Имя, Адрес, Телефон)- сведения о клиентах (справочник).
В таблице Клиенты столбец ID_клиента является первичным ключом, т. е. его значения отличны от NULL и уникальны. В таблице Заказы столбец ID_клиента не обязан иметь уникальные значения, поскольку один и тот же клиент может сделать несколько заказов. Вместе с тем любому значению столбца Заказы. ID_клиента соответствует единственное значение столбца Клиенты.ID_клиента. При описанных условиях столбец ID_клиента таблицы Заказы является внешним ключом, ссылающимся на первичный ключ ID_клиента таблицы Клиенты.
Внешний ключ определяется как ограничение для таблицы в выражении с ключевыми словами CONSTRAIN FOREIGN KEY (ограничение "внешний ключ"):
CONSTRAINT FOREIGN KEY внешнийКлюч REFERENCES
внешняяТаблица (первичныйКлюч)
Здесь внешнийКлюч- имя столбца или список столбцов, разделенных запятыми, которые определяют внешний ключ, за ключевым словом REFERENCES (ссылки) указывается внешняя таблица и ее первичный ключ, на который ссылается внешний ключ.
Для рассмотренного ранее примера таблицу Заказы можно определить следующим образом:
CREATE TABLE Заказы (
ID_ заказа INTEGER,
ID_ клиента INTEGER,
CONSTRAINT FOREIGN KEY ID _ клиента REFERENCES Клиенты (ID _ клиента)
);
Использование внешних ключей обеспечивает сохранение ссылочной целостности базы данных при изменении и удалении записей. Если бы таблицы заказы и клиенты не были связаны, то при удалении записи из таблицы Клиенты в таблице Заказы могли остаться ссылки на клиента, о котором уже нет сведений. Этот факт обычно расценивается как аномалия удаления. В случае определения в таблице заказы внешнего ключа ID_клиента из таблицы клиенты не удастся удалить клиента, если он сделал хотя бы один заказ. Если требуется удалить из базы данных все, что касается определенного клиента, то сначала удаляются записи в таблице Заказы, а потом - в таблице Клиенты.
Аналогичная ситуация может произойти и при обновлении данных. Например, в таблице клиенты вы изменили идентификатор клиента, имеющего заказы, и, таким образом, внешнему ключу теперь не на что ссылаться. Это аномалия изменения. В данном случае необходимо сначала добавить новую запись в таблицу клиенты, указав в ней необходимое значение ID_клиента, затем изменить в таблице заказы все старые значения ID_клиента на то, которое вы только что ввели в новой записи таблицы клиенты, а затем удалить из таблицы Клиенты запись со старым идентификатором клиента.
Чтобы в таблицах, связанных внешним ключом, не делать модификацию данных в несколько этапов, в выражении CONSTRAIN FOREIGN KEY можно использовать дополнительные ключевые слова:
- ON DELETE CASCADE | SET NULL (при удалении каскадировать | установить NULL);
- ON UPDATE CASCADE| SET NULL (при обновлении каскадировать | установить NULL).
Здесь вертикальная черта не является элементом синтаксиса, а лишь разделяет возможные варианты ключевых слов.
Так, при использовании ON DELETE CASCADE в случае удаления записи со значением первичного ключа, которое имеется во внешнем ключе другой таблицы, соответствующие записи удаляются автоматически из двух таблиц. Например, при удалении из таблицы Клиенты записи о клиенте, имеющем заказы, в таблице Заказы также будут удалены все записи, ссылающиеся на данного клиента. Чтобы данная стратегия выполнялась, таблица Заказы должна быть определена следующим образом:
CREATE TABLE Заказы (
ID_ заказа INTEGER,
ID_ клиента INTEGER,
CONSTRAINT FOREIGN KEY ID _ клиента REFERENCES Клиенты (ID_ клиента)
ON DELETE CASCADE
);
Однако на практике обычно предпочитают сначала убедиться, что клиент не имеет заказов, и лишь затем вычеркнуть его из справочника.
Вариант SET NULL обычно используется при обновлении данных.
Например:
CREATE TABLE Заказы (
ID _ заказа INTEGER,
ID _ клиента INTEGER,
CONSTRAINT FOREIGN KEY ID_ клиента REFERENCES Клиенты (ID_ клиента)
ON UPDATE SET NULL
);
В данном случае при изменении (в том числе и при удалении) в таблице Клиенты записи, на которую ссылается внешний ключ таблицы Заказы, значения внешнего ключа устанавливаются в NULL. Однако этот вариант не сработает, если на столбец Заказы. ID_клиента наложено ограничение NOT NULL. Обычно так и бывает, поскольку при оформлении заказа клиент должен быть обязательно указан. Поэтому, на всякий случай, лучше использовать ключевые слова ON UPDATE CASCADE.
Удаление таблиц
Удалить таблицу из базы данных можно следующим образом:
DROP TABLE имяТаблицы;
Разумеется, при удалении таблицы теряются и все содержащиеся в ней данные. Во время работы с базой данных нередко создаются таблицы для временного хранения данных, полученных на каком-то промежуточном этапе. Рано или поздно такие таблицы подлежат удалению. Однако можно забыть это сделать. Кроме того, промежуточные таблицы, создаваемые приложениями, могут остаться в базе данных из-за сбоев. Поэтому для создания временных таблиц лучше использовать оператор CREATE TEMPORARY TABLE,а не CREATE TABLE.
Модификация таблиц
Разработка базы данных обычно происходит итеративно. Редко когда удается сразу оптимально определить структуру входящих в нее таблиц, чтобы потом их не переделывать. Кроме того, уже готовая база данных со временем может пополняться новыми таблицами, которые связываются с уже имеющимися. А установка новых связей требует коррекции параметров в старых таблицах. Модификация таблицы - это изменение ее структуры, т. е. добавление, удаление и переименование столбцов, а также изменение их типов и размеров.
Язык SQL обладает специальными средствами модификации таблиц. Если бы их не было или если отказаться их применять, то изменение структуры таблицы можно выполнить следующим образом:
1. Создать новую рабочую таблицу с необходимой структурой.
2. С помощью оператора INSERT INTO вставить в рабочую таблицу данные из исходной таблицы.
3. Удалить исходную таблицу.
4. Переименовать рабочую таблицу, присвоив ей имя исходной.
Все предыдущие операции можно выполнить посредством рассмотренных ранее операторов SQL. Переименование таблицы можно сделать средствами программной среды разработки базы данных. Если же ограничиться только операторами SQL, то вместо переименования придется выполнить следующее:
1. Создать новую таблицу с именем исходной и с такой же структурой, как у рабочей таблицы.
2. С помощью оператора INSERT INTO вставить в новую таблицу данные из рабочей таблицы.
3. Удалить рабочую таблицу.
Как видите, изменение структуры таблицы обычными операторами SQL довольно трудоемко. Значительно проще изменить структуру таблицы с помощью оператора ALTER TABLE (изменить таблицу). С помощью дополнительных ключевых слов можно выполнить следующие операции:
– ADD COLUMN - добавить столбец;
– DROP COLUMN - удалить столбец;
– ALTER COLUMN - изменить тип, размер и ограничение столбца;
– RENAME COLUMN - переименовать столбец;
– RENAME ТО - переименовать таблицу.
Типичной задачей изменения структуры таблицы является добавление столбца. Это можно сделать с помощью SQL-выражения с таким синтаксисом:
ALTER TABLE имяТаблицы
ALTER COLUMN имяСтолбца тип (размер) [ ограничение ];
Здесь квадратные скобки указывают, что заключенное в них выражение не является обязательным.
В следующем примере в таблице Студент уже существующий столбец ФИО приобретает новые параметры. А именно будучи символьным, он получает увеличение длины до 50 символов и становится первичным ключом:
ALTER TABLE Студенты
ALTER COLUMN ФИО CHAR(50) PRIMARY KEY;
Разумеется, если в таблице уже имеется первичный ключ (например, столбец ID_студента), то сделать первичным ключом еще один столбец не удастся. Кроме того, не следует забывать, что при преобразовании типов могут быть потеряны данные. Так, если вы преобразуете столбец символьного типа, содержащий фамилии или адреса, в числовой тип, то все данные будут потеряны. При уменьшении размера символьного столбца его значения могут оказаться обрезанными справа. Таким образом, следует очень внимательно изменять параметры столбцов, содержащих некоторые данные.
Переименовывать столбцы приходится редко, поскольку в выборках и представлениях всегда можно присвоить им нужные псевдонимы. Тем не менее, переименовать столбцы и таблицу можно с помощью оператора ALTER TABLE с ключевыми словами RENAME COLUMN... ТО (переименовать столбец... в).
Для переименования столбца используется следующий синтаксис:
ALTER TABLE имяТаблицы
Задача 1.
Пусть база данных содержит следующие три таблицы:
Студенты (ID_студента, Имя, Телефон, Адрес, ID_курса) - данные о студентах, в том числе и о курсах, которые они изучают;
Курсы (ID_курса, Наименование) - данные о курсах;
Преподаватели (ID__преподавателя, Имя, Телефон, Адрес, ID_курса) - данные о преподавателях, в том числе и о курсах, занятия по которым они проводят.
В таблице Студенты каждый студент может быть записан на несколько курсов. Аналогично, один и тот же преподаватель может проводить занятия по нескольким курсам. Таким образом, столбцы ID_курса в этих таблицах не обязаны содержать уникальные значения. В таблице Курсы, напротив, столбец ID_курса содержит только уникальные значения.
Сформулируйте SQL-выражения для создания указанных таблиц так, чтобы таблицы Студенты и преподаватели были связаны с таблицей курсы.
Задача 2.
Для базы данных, описанной в задаче 1, сформулируйте запрос, возвращающий таблицу, которая содержит:
-наименования курсов и имена преподавателей, которые проводят занятия по данным курсам;
- наименования курсов и имена студентов, которые изучают данные курсы;
- имена студентов и имена преподавателей, которые проводят занятия с данными студентами.
Задача 3.
Пусть в базе данных, описанной в задаче 1, имеется таблица Контакты (Имя, Адрес, Телефон, Примечания). Сформулируйте запрос, добавляющий в нее соответствующие данные из таблиц Студенты и Преподаватели. Решите эту же задачу, записывая при этом в Столбец Примечания слово 'Студент' или 'Преподаватель', в зависимости от того, из какой таблицы берутся данные для вставки в таблицу Контакты.
Создание и модификация таблиц»
Задания:
Теоретический материал для изучения:
1. Добавление, удаление и изменение данных в таблицах
Запросы, рассмотренные ранее, были направлены на то, чтобы получить данные, содержащиеся в существующих таблицах базы данных. Главным ключевым словом таких запросов на выборку данных является SELECT. Запросы на выборку данных всегда возвращают виртуальную таблицу, которая отсутствует в базе данных и создается временно лишь для того, чтобы представить выбранные данные пользователю. При создании и дальнейшем сопровождении базы данных обычно возникает задача добавления новых и удаления ненужных записей, а также изменения содержимого ячеек таблицы. В SQL для этого предусмотрены операторы INSERT (вставить), DELETE (удалить) и UPDATE (изменить). Запросы, начинающиеся с этих ключевых слов, не возвращают данные в виде виртуальной таблицы, а изменяют содержимое уже существующих таблиц базы данных. Запросы на модификацию (добавление, удаление и изменение) данных могут содержать вложенные запросы на выборку данных из той же самой таблицы или из других таблиц, однако сами не могут быть вложены в другие запросы. Таким образом, операторы INSERT, DELETE и UPDATE в SQL-выражении могут находиться только в самом начале.
Добавление новых записей
Когда создается таблица базы данных, она не содержит никаких записей, т. е. является пустой. Чтобы наполнить таблицу данными, необходимо добавить (вставить) в нее хотя бы одну запись. Для этого служит оператор INSERT (вставить), который имеет несколько форм:
- INSERT INTO имяТаблицы VALUES (списокЗнач ений) – вставляет пустую запись в указанную таблицу и заполняет эту запись значениями из списка, указанного за ключевым словом VALUES. При этом первое в списке значение вводится в первый столбец таблицы, второе значение - во второй столбец и т. д. Порядок столбцов задается при создании таблицы. Данная форма оператора INSERT не очень надежна, поскольку нетрудно ошибиться в порядке вводимых значений. Более надежной и гибкой является следующая форма;
- INSERT INTO имяТаблицы (списокСтолбцов) VALUES (списокЗнач ений) - вставляет пустую запись в указанную таблицу и вводит в заданные столбцы значения из указанного списка. При этом в первый столбец из списокСтолбцов вводится первое значение из списокЗначений, во второй
|
|
Своеобразие русской архитектуры: Основной материал – дерево – быстрота постройки, но недолговечность и необходимость деления...
Папиллярные узоры пальцев рук - маркер спортивных способностей: дерматоглифические признаки формируются на 3-5 месяце беременности, не изменяются в течение жизни...
Археология об основании Рима: Новые раскопки проясняют и такой острый дискуссионный вопрос, как дата самого возникновения Рима...
Состав сооружений: решетки и песколовки: Решетки – это первое устройство в схеме очистных сооружений. Они представляют...
© cyberpedia.su 2017-2024 - Не является автором материалов. Исключительное право сохранено за автором текста.
Если вы не хотите, чтобы данный материал был у нас на сайте, перейдите по ссылке: Нарушение авторских прав. Мы поможем в написании вашей работы!