Практическая работа 1. Проектирование базы данных

• Название: Практическая работа 1. Проектирование базы данных
• Дата: 17.12.2022
• Размер: 2.91 Mb.
• Имя файла: bazy_dannykh._dlia_prakt_.pdf
• Тип: Практическая работа; #849388
• страница: 12 из 19

Тема 3. Внесение изменений в базу данных
7.4.1. Добавление новых данных
Новые данные добавляются оператором INSERT. Наименьшей единицей информации, которую можно добавить в реляционную базу данных, является одна строка таблицы.
Немного упрощенный синтаксис оператора INSERT имеет вид:
INSERT INTO Имя_Таблицы [(Колонка [, Колонка ...])]
{VALUES(<величина> [, <величина> ...]) | <оператор SELECT>};
<величина> = {:Переменная | <константа> | <выражение>
| <функция> | udf([<величина> [, <величина> ...]])
| NULL | USER}
<константа> = Число | 'Строка'
<функция> = CAST(<величина> AS <тип данных>)
UPPER(<величина>)
GEN_ID(Имя_Генератора,
<величина>)
<выражение> = SQL выражение, возвращающее единичное значение
В этом описании можно выделить два варианта оператора:
1. Вставка одной строки. Для этого после ключевого слова VALUES в круглых скобках указывают вставляемые величины.
2. Вставка в таблицу нескольких строк, выбранных с помощью оператора
SELECT * .
В этой практической работе рассматривается только первый вариант оператора
INSERT.
Пример, когда в качестве вставляемых величин применены константы:
INSERT INTO Person(Pr_ID, Pr_LastName, Pr_FirstName)
VALUES(150, 'Иванов', 'Петр');
7.4.2. Удаление существующих данных
Для удаления строк из таблицы используется оператор DELETE. Вот его упрощенный синтаксис:
DELETE FROM Имя_Таблицы
[WHERE <условие поиска>];
<условие поиска> = как в операторе SELECT
Если не использовать предложение WHERE, то будут удалены все строки в таблице.
-- Удаление всех служащих:
DELETE FROM Employee;
-- Удаление всех людей с номерами 150 и больше:
DELETE FROM Person WHERE Pr_ID >= 150;

144
Отбирать строки для удаления не обязательно только на основании содержимого этих строк. Можно составить условие для удаляемых строк, опираясь на данные из других таблиц. Для составления таких условий необходимо сначала изучить оператор
SELECT.
7.4.3. Обновление существующих данных
Оператор UPDATE обновляет значения одного или нескольких столбцов в выбранных строках одной таблицы. Строки для обновления указываются в предложении WHERE. Если пропустить предложение WHERE, то изменятся все строки таблицы.
UPDATE Имя_Таблицы
SET Колонка
=
<величина>[,
Колонка
=
<величина>...]
[WHERE <условие поиска>]
<величина> = { Колонка | :Переменная | <константа>
| <выражение> | <функция>
| udf([<величина> [, <величина> ...]]) | NULL | USER}
<выражение> = SQL выражение, возвращающее единичное значение
<условие поиска> = как в операторе SELECT
Примеры:
-- Увеличить зарплату всем служащим на 10%:
UPDATE Employee
SET Salary = 1.1*Salary;
/* Увеличить зарплату всем служащим, которые имеют зарплату меньше 10000 на 15%:
*/
UPDATE Employee
SET Salary = 1.15*Salary;
WHERE Salary <= 10000;
Отбирать строки для изменения, как и для удаления, можно с использование подчиненного запроса SELECT, который позволит учитывать в условии поиска изменяемых строк данные из других таблиц.
Например, можно выполнить такой запрос: увеличить зарплату на 10% всем служащим, работающим в отделе продаж, которые обслужили за последний месяц клиентов больше чем в полтора раза, чем в среднем по их отделу.
7.5.
Задание
Добавление новых строк
Предложение INSERT вставляет строки в таблицу базы данных. Есть три разновидности этой команды:
INSERT VALUES
INSERT SELECT
INSERT DESALT VALUES

145
Первая из них производит вставку в таблицу явно заданной строки, вторая разновидность – вставку группы строк, выбранных в результате выполнения запроса, а третья — вставку значений по умолчанию.
Вставка отдельных строк
Предложение INSERT... VALUES выполняет вставку в таблицу одной строки. Его удобно использовать для небольших операций, когда в таблицу нужно вставить несколько строк. Синтаксис этого предложения следующий:
INSERT INTO имя_таблицы [<имя_стол6ца[, имя_столбца]...)]
VALUES (значение[, значение]...);
Указание вставляемых столбцов
Этот формат предполагает указания имени таблицы, в которую производится вставка, списка имен столбцов, в которые будут вставляться значения, и списка собственно вставляемых в строку значений. При этом следует придерживаться следующих правил:
–вставляемые данные должны согласовываться с типами данных указанных столбцов;
–размеры данных должны соответствовать размерам столбцов;
–порядок данных во фразе VALUES должен соответствовать порядку перечисления столбцов.
Запрос 30. Выполнить вставку одной строки в таблицу FACULTET
В таблицу вставляется строка со значениями, указанными в списке фразы
VALUES, причем расположение значений в списке соответствует расположению соответствующих столбцов в списке столбцов таблицы.
Примечание. Чтобы данные были добавлены, не забудьте нажать на кнопки , а затем для подтверждения внесенных изменений в таблицу. Просмотрите внесенные изменения в таблицу FACULTET.
В этом примере мы перечислили столбцы в том порядке, в каком они были определены при создании таблицы, однако это не обязательно. При желании порядок перечисления имен столбцов в команде INSERT можно изменить.
Список имен столбцов может быть не полным. Можно указывать только те из них, значения которых известны для вставляемой строки, Столбцы, отсутствующие в списке, будут принимать значения NULL для вводимой строки.
Самостоятельно создайте запрос 31. Выполнить вставку одной строки в таблицу
KAFEDRA для столбцов name_kafedru, fio_zavkaf, kod_faculteta с данными 'Психологии',
'Иванова', 5.
В этом примере в водимой строке отсутствуют значения столбцов Nomer_komnatu,
Num_korpusa и Tel_kafedru. В базе данных они примут значение NULL.

146
Поддержка ограничений целостности
Помните, некоторые из столбцов или наборов столбцов могут иметь ограничения целостности PRIMARY KEY и NOT NULL. Такие столбцы не могут принимать значения
NULL.
Приведенные выше рассуждения относятся ко всем ограничениям целостности, определенным для таблиц. При попытке ввода данных (как, впрочем, и при обновлении и удалении) СУБД проверяет возможное нарушение объявленных ограничений целостности. И если это так, команда будет отклонена с выдачей соответствующего уведомления.
Использование выражений
В качестве вставляемых значений могут использоваться выражения.
Самостоятельно создайте запрос 32.Ввести в таблицу TEACHER данные (50, 10, '
Капуста Леонид Владимирович',
1271, 1271/3, ‘доцент’, GETDATE()-1)
Здесь мы указали, что надбавка равна третьей части ставки (1271 / 3),а дата приема на работу на один день меньше текущей даты (CURRENT_DATE -1).
Результат запроса в качестве вставляемого значения
Вместо вставляемого значения можно использовать запрос. Это оказывается очень удобным в том случае, когда вставляемое значение присутствует в базе данных.
Запрос 33.Например, в следующем предложении в качестве фамилии заведующего вновь вставляемой кафедры выбирается фамилия декана факультета «Компьютерных наук и технологий» .
INSERT INTO KAFEDRA (name_kafedru, kod_faculteta, fio_zavkaf)
VALUES ( 'Философии', 5, (SELECT fio_decana FROM FACULTET
WHERE LOWER(Name_faculteta) = 'международный'));
Обновление существующих данных
Целью предложения обновления является изменение значений отдельных столбцов всех или удовлетворяющих указанному условию строк таблицы. Упрощенный синтаксис предложения следующий:
UPDATE имя_таблицы [[AS] синоним]
SET имя_столбца = выражение[, имя_столбца = выражение]...
[WHERE условие];
Его элементы означают следующее: имя_таблицы — имя обновляемой таблицы; синоним — синоним обновляемой таблицы для ссылки на нее в подзапросе; имя_столбца - имя обновляемого столбца; выражение - допустимое в SQL выражение соответствующего типа, значение которого присваивается обновляемому столбцу; условие - допустимое в SQL выражение условия, которое используется для отбора обновляемых строк.
По одному предложению UPDATE обновлению подвергаются строки только одной базовой таблицы.

147
Обновление всех строк
Как видно из определения синтаксиса команды UPDATE, фраза WHERE является факультативной. При ее отсутствии все строки таблицы подвергаются обновлению согласно фразе SET.
Во фразе SET можно одновременно изменять значения нескольких столбцов таблицы.
Запрос 34. Например, в следующем примере всем преподавателям увеличивается ставка на 12 % и надбавка на 7 %:
UPDATE TEACHER
SET Salary = Salary + Salary * 0.12, Rise = Rise + Rise * 0.08;
Во фразе SET в правой части оператора присваивания может использоваться любое допустимое в SQL выражение того же типа, что и столбец, имя которого приведено слева от оператора присваивания. Если в этом выражении используется имя столбца целевой таблицы, для вычисления выражения применяется значение этого столбца в текущей строке, которое было перед обновлением.
Примечание. Чтобы данные были добавлены, не забудьте нажать на кнопки , а затем для подтверждения внесенных изменений в таблицу. Просмотрите внесенные изменения в таблицу TEACHER.
Обновление по условию
Данный вариант использует фразу WHERE. В этом случае обновляются столбцы только тех строк таблицы, на которых выполняется условие фразы WHERE. Рассмотрим несколько примеров.
Запрос 35. Увеличить всем ассистентам зарплату и надбавку на 10 %:
UPDATE TEACHER
SET Salary = Salary * 1.1, Rise = Rise * 1.1
WHERE LOWER(Dolgnost ) = 'ассистент';
Самостоятельно создать запрос 36. Установить, что деканат юридического факультета переместился в комнату 232 8 корпуса.
Подзапросы во фразе WHERE
Во фразе WHERE можно использовать подзапросы, как мы это делали в предложении SELECT. Это дает возможность отбирать строки для обновления на основе информации из других таблиц.
Запрос 37. Например, увеличить ставку всех преподавателей кафедры прикладной математики факультета математики и информатики в полтора раза:
UPDATE TEACHER
SET Salary = Salary * 1.5
WHERE KOD_kafedru = (SELECT KOD_kafedru FROM KAFEDRA
WHERE LOWER(Name_kafedru) = 'прикладная математика');
Подзапросы во фразе SET
До сих пор новые значения представляли собой константы или выражения с использованием значений обновляемой строки. Однако если такие значения присутствуют в других строках обновляемой таблицы или вообще в других таблицах, можно воспользоваться подзапросом. В этом случае допускается использовать две формы фразы SET:

148
SET {имя_столбца | (список_инен_столбцов)} = (подзапрос)
В обоих вариантах подзапрос должен возвращать одну строку. В первом случае он также должен возвращать значение одного столбца, а во втором возвращаемая строка должна содержать столько значений, сколько столбцов приведено в списке имен столбцов. При этом производится присвоение значений строки подзапроса соответствующим столбцам из списка слева.
Запрос 38. Установить всем ассистентам надбавку, равную 70 % текущей средней надбавки по вузу.
UPDATE TEACHER
SET Rise = (SELECT SUM(Rise) * 0.7 / COUNT(*) FROM TEACHER)
WHERE LOWER(Dolgnost ) = 'ассистент';
Удаление существующих строк
Удалять строки из таблицы можно с помощью предложения DELETE. Оно удаляет только строки целиком, а не индивидуальные значения столбцов. Синтаксис команды следующий:
DELETE FROM имя_таблицы [[AS] синонин]
[WHERE условие];
При использовании предложения DELETE вы прежде всего обнаружите, что предупреждающая подсказка, как правило, не выдается. Обычно, когда пользователь удаляет какой-либо объект операционной среды, он получает сообщение типа «Вы уверены (Д/Н)?». В системах, поддерживающих SQL, строки удаляются без такого сообщения. Поэтому будьте внимательны.
В зависимости от применения фразы WHERE предложение DELETE позволяет удалить отдельную строку, несколько или все строки таблицы. Строки могут быть и не удалены. При использовании предложения DELETE помните о следующем: нельзя удалить значение отдельного столбца (используйте для этого предложение
UPDATE); как и предложения INSERT и UPDATE, удаление строк может нарушить ограничения целостности; сама таблица не удаляется (используйте для этого предложение DROP TABLE).
Удаление всех строк таблицы
Чтобы удалить все содержимое таблицы, не нужно использовать фразу WHERE.
Помните, что вы удаляете не саму таблицу, а только все ее строки.
Запрос 39. Удалить содержимое таблицы Сотрудники базы данных Educator.
use Educator
DELETE FROM Cотрудники;
Удаление по условию
Обычно нужно удалять только некоторые строки из таблицы. Чтобы определить, какие строки будут удалены, нужно использовать условие во фразе WHERE. Приведем несколько примеров.
Самостоятельно создать запрос 40. Удалить сведения об ассистентах, которые были приняты на работу до 01.01.1986.
Удаление одной строки

149
Чтобы удалить одну конкретную строку, нужно сформулировать условие таким образом, чтобы оно идентифицировало эту единственную строку. Обычно для этого в условии используются первичный ключ таблицы или уникальный набор столбцов.
Самостоятельно создать запрос 41. Удалить всех преподавателей под фамилией
Швец.
Задание для практической работы №7
Для созданной базы данных, согласно номеру варианта, самостоятельно создать на
языке Transact-SQL 14 многотабличных запросов:
- 1 запрос с использованием функции COUNT;
- 1 запрос с использованием функции SUM;
- 1 запрос с использованием функций UPPER, LOWER;
- 1 запрос с использованием временных функций;
- 1 запрос с использованием группировки по одному столбцу;
- 1 запрос на использование группировки по нескольким столбцам;
- 1 запрос с использованием условия отбора групп HAVING;
- 1 запрос с использованием фразы HAVING без фразы GROUP BY;
- 1 запрос с использованием сортировки по столбцу;
- 1 запрос на добавление новых данных в таблицу;
- 1 запрос на добавление новых данных по результатам запроса в качестве вставляемого значения;
- 1 запрос на обновление существующих данных в таблице;
- 1 запрос на обновление существующих данных по результатам подзапроса во фразе WHERE;
- 1 запрос на удаление существующих данных.
Все программные инструкции команд SQL сохранять в файлах с расширением
*.sql в папке ФИО_студента/Лаб7.
Для каждого запроса сформулировать текстовое задание, которое должно быть выполнено к базе данных.
Создать текстовый отчет, в котором отобразить sql-команды разработанных запросов и скриншоты результатов работы из СУБД SQL Server Management Studio.

150
ПРАКТИЧЕСКАЯ РАБОТА №8. СОЗДАНИЕ И УПРАВЛЕНИЕ
ПРЕДСТАВЛЕНИЯМИ
8.1.
Цель работы
Изучение назначения представлений баз данных, синтаксиса и семантики команд языка Transact-SQL для их создания, изменения и удаления, системных хранимых процедур для получения информации о представлениях, а также приобретение навыков их создания с помощью графических средств утилиты Enterprise Manager и мастера
Create View Wizard.
8.2. Методические рекомендации для выполнения практической работы
Представление (View) для пользователей баз данных выглядит как таблица, но при этом оно не содержит данных, а лишь представляет данные, расположенные в одной или нескольких таблицах. Таким образом, представления – это виртуальные таблицы, определяемые запросом на языке Transact-SQL. Подобно реальным таблицам представления содержат именованные столбцы и строки с данными, которые они динамически выбирают из таблиц и предлагают эти данные пользователю для просмотра.
Представления часто применяются для ограничения доступа к конфеденциальным данным в таблицах баз данных. Когда в представление не включается столбец исходной таблицы, то считают, что на таблицу наложен вертикальный фильтр. Если в SQL – запросе установлено одно или несколько условий для выборки строк, то считают, что на таблицу наложен горизонтальный фильтр.
Представление может выбирать данные из других представлений, которые, в свою очередь, могут также основываться на представлениях или таблицах. Вложенность представлений не должна превышать 32. Представления можно создавать, используя базы данных одного сервера (текущего). Максимальное количество столбцов в представлении равно 1024. Представление не может ссылаться на временные таблицы.
Кроме того, нельзя создавать временное представление.
Для представления нельзя определить ограничения целостности, триггеры, правила, или умолчания, а также создать обычный или полнотекстовый индекс.
В основном представления используются для выборки данных. Однако с помощью представлений можно выполнять и изменение данных в таблицах, на основе которых построено представление, при этом требуется соблюдение ряда правил: представление должно содержать, как минимум, одну таблицу в параметре FROM команды SELECT, не разрешается использование функций агрегирования и др.
Как и для таблиц, для представлений можно определить следующие права доступа:
SELECT – просмотр данных;
INSERT – добавление данных через представления;
UPDATE – изменение данных в исходных таблицах;
DELETE –удаление данных в исходных таблицах.
Чтобы иметь возможность создавать представления, надо обладать правами владельца баз данных и иметь соответствующие разрешения для любых таблиц или представлений, упомянутых "в запросе на создание этого представления.
Для создания представления используется следующая команда Transact-SQL: