Задание на лабораторную работу

Для выполнения заданий необходимо запустить утилиту QueryAnalyzer. Выполнить все приведенные в лабораторной работе примеры. Реализовать правила в своих программах. Написать не менее трех запросов по определенным критериям, в зависимости от предметной области (прил.).

Создать необходимые для работы сводные таблицы, реализующие отношения "многие-ко-многим" для таблиц, созданных в лабораторной работе №1.

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

 

Контрольные вопросы

1. При помощи какого символа можно выбрать все столбцы в запросе?

2. Какие типы связывания таблиц существуют и чем они отличаются?

3. Какие условия связывания двух таблиц допустимы?

4. Какие существуют виды сортировки в запросе?

 

Лабораторная работа № 3.

ВСТАВКА, ИЗМЕНЕНИЕ И УДАЛЕНИЕ ДАННЫХ

Цель работы

- Закрепление теоретических знаний по вставке, изменению и удалению данных.

- Приобретение практических навыков по вставке, изменению и удалению данных.

- Написание запросов по работе с данными предметной области.

 

Теоретическая часть

Добавление данных

Перед тем как начать изменение данных в таблицах или выборку данных из таб­лиц, необходимо их вставить. Данные в таблицу SQL Server могут быть внесены различными способами.

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

С помощью команды SELECT INTO. В этом случае на основе результата выборки, возвращаемого запросом, сервер автоматически создает новую таблицу.

 

Использование команды INSERT

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

INSERT [INTO] таблица_или_представление [(список_столбцов)]

VALUES (список_значений)

Рассмотрим назначение каждого из аргументов команды.

[ INTO ]. Дополнительное ключевое слово, которое может быть использовано в команде между словом INSERT и именем таблицы (представления) для обозначения, что следующий параметр является именем таблицы, в которую будут вставлены данные.

Если список столбцов опущен, то сервер будет вставлять данные последовательно во все столбцы, начиная с первого. Значения для столбцов указываются после ключевого слова VALUES. Для каждого столбца должен быть указан аргумент, имеющий соответствующий тип данных. Если список столбцов не указан, то количество значений VALUES должно соответствовать количеству столбцов таблицы. Если же пользователь явно задал список столбцов, то это определяет порядок зна­чений VALUES (и, соответственно, их типы). Можно не указывать явно значения для столбцов, если для них определено значение по умолчанию или разрешено хранение значений NULL. Кроме того, запрещается указание явных значений для столбцов-счетчиков (с установленным свойством IDENTITY) и столбцов с типом данных timestamp.

- VALUES (DEFAULT | NULL | expression). Ключевое слово VALUES определяет набор данных, которые будут вставлены в таблицу. Количество аргу­ментов VALUES определяется количеством столбцов в таблице или количеством столбцов в списке (если таковой имеется). Для каждого столбца таблицы можно указать один из трех возможных вариантов.

- DEFAULT. Будет вставлено значение по умолчанию, определенное для столбца. Если для столбца разрешено хранение значений NULL, а значение по умолчанию не определено, то в столбец будет вставлено значение NULL. Параметр DEFAULT нельзя использовать при вставке значений в столбцы-счетчики.

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

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

При использовании оператора INSERT для модификации представления следует учитывать следующие ограничения:

- представление не должно содержать функций агрегирования, таких как COUNT или AVG;

- представление не должно содержать операторов TOP, GROUP BY, UNION, DISTINCT.

- представление не должно содержать вычисляемых столбцов;

- представление должно ссылаться на таблицу во фразе FROM;

- оператор INSERT модифицирует столбцы только из одной таблицы.

 

Рассмотрим пример добавления студента Алексея Петрова в таблицу Students:

Пример 1

Insert Students (id, family, Name, _ID_Group) Values (13, ‘Петров’, ‘Алексей’, 10)

 

Использование команды SELECT...INTO

Если необходимо быстро создать таблицу со структурой, позволяющей сохранить результат выполнения запроса, то лучшим выходом будет использование коман­ды SELECT...INTO. При выполнении запроса SELECT...INTO сервер автоматически создаст новую таблицу с нужной структурой и вставит в нее полученный набор строк. Заметим, что в базе данных не должно существовать таблицы или представления, имя которых совпадает с именем таблицы, указанной в команде SELECT...INTO.

Синтаксис команды SELECT...INTO следующий:

SELECT <столбец> AS <псевдоним> INTO <новая_таблица> FROM <список_источников>

Рассмотрим назначение приведенных аргументов:

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

- аргумент <новая_таблица> содержит имя создаваемой таблицы. Это имя должно быть уникальным в пределах базы данных. Можно задать имя временной таблицы, если перед именем указать символ # или ##.

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

Рассмотрим пример

Пример 2.

Select *

Into #t1

From Students.

 

Изменение данных

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

UPDATE таблица_или_представление

SET объекты_изменения

[WHERE (условие)]

Для обновления представления с помощью оператора UPDATE существуют те же ограничения, что и для обновления представления с помощью оператора INSERT.

За ключевым словом SET следует перечень подлежащих обновлению столбцов, отделяемых запятыми, а также их новые значения. Форма записи при этом будет следующая: имя_столбца=новое_значение. Новое значение может быть константой или выражением, которое также может ссылаться на сам столбец. Например, выражение SalesPrice=SalesPrice*.90 будет уменьшать значения в столбце SalesPrice на 10 процентов. Фраза WHERE является необязательной. Если она имеется, то должна задавать строки, подлежащие обновлению. Если фраза WHERE в операторе UPDATE отсутствует, будут модифицироваться все строки в таблице.

Оператор UPDATE использует фразу FROM для извлечения значений из другой таблицы:

UPDATE таблица_или_представление

SET объекты_изменения

FROM таблица_или_представление оператор_связывания условие_связывания

[WHERE (условие_ограничения)].

Фраза FROM здесь имеет тот же формат, что и фраза FROM в операторе SELECT. После того как источник данных указан в разделе FROM, в разделах WHERE и SET можно ссылаться на столбцы этого источника данных. Назначение раздела WHERE, используемого в запросе UPDATE, полностью соответствует назначению, которое раздел имеет в запросе SELECT. To есть с помощью раздела WHERE можно сузить диапазон строк, в кото­рых будет выполняться изменение данных. Пользователь должен указать логическое условие, на основе которого будет приниматься решение об изме­нении данных конкретной строки. Если в контексте значений строки указан­ное логическое условие выполняется (то есть она возвращает значение TRUE), то данные этой строки будут изменены. В противном случае изменение не выполняется. Предполагается, что логическое условие включает имена стол­бцов изменяемой таблицы. Однако это не обязательно.

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

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

Для каждого изменяемого столбца нужно определить значение, которое он примет после выполнения изменения. С помощью ключевого слова DEFAULT можно присвоить столбцу значение, определенное для него по умолчанию. Можно также установить для столбца значение NULL. Если необходимо установить определенное значение, следует указать константу, переменную или выражение. Изменению подвергнутся все строки, удовлетворяющие критериям ограничения области действия запроса UPDATE, которые задаются с помощью раздела WHERE. При составлении выражения можно ссылаться на любые столбцы таблицы, включая изменяемые. При этом следует учитывать, что изменения в данные вносятся только после выполнения команды. Таким образом, при ссылке на изменяемые столбцы будут использоваться старые значения.

Рассмотрим пример изменения ссылки на номер группы с 10-го на 20-ый:

Пример 3.

Update Students

Set _ID_Group=20

Where _ID_Group=10.

 

Удаление данных

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

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

DELETE таблица_или_представление

[FROM источники_таблиц]

[WHERE условие_отбора].

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

Если таблица участвует в отношении, важно не допустить, чтобы при удалении в главной таблице остались “висячие” строки. Висячими строками называют строки в зависимой таблице (таблице внешнего ключа), которые не имеют соответствующих строк в главной таблице (таблице первичного ключа). Новинкой в SQL Server является возможность каскадных удалений в отношении. Если для отношения задается каскадное удаление, SQL Server автоматически удалит строки в зависимой таблице, чтобы не оставлять висячих строк.

Удаление с помощью оператора TRUNCATE TABLE.

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

Рассмотрим пример удаления всех студентов из таблицы Students, имена которых начинаются на букву ‘A’:

Пример 4.

Delete Students

Where Name like ‘A%’.

 

⇐ Предыдущая12345678Следующая ⇒

Поделиться:

Воспользуйтесь поиском по сайту: