Операторы модификации данных

Цель и задачи работы

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

 

Порядок выполнения работы

- ознакомится с теоретическими сведениями;

- выполнить задание;

- оформить отчет;

- ответить на контрольные вопросы, заданные преподавателем.

 

Оформление отчета

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

 

Теоретические сведения

В SQL для выполнения операций ввода данных в таблицу, их изменения и удаления предназначены три команды языка манипулирования данными (DML).

Это команды – INSERT (вставить), UPDATE (обновить), DELETE (удалить).

Команда INSERT осуществляет вставку в таблицу новой строки. В простейшем случае она имеет следующий вид:

INSERTINTO <имя таблицы> VALUES (<значение>, <значение>,...);

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

Добавление одной строки в таблицу films:

INSERT INTO films VALUES ('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes');

В этом примере столбец len опускается и, таким образом, получает значение по умолчанию:

INSERT INTO films (code, title, did, date_prod, kind)

VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');

В этом примере для столбца с датой задаётся указание DEFAULT, а не явное значение:

INSERT INTO films VALUES

('UA502', 'Bananas', 105, DEFAULT, 'Comedy', '82 minutes');

INSERT INTO films (code, title, did, date_prod, kind)

VALUES ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama');

Добавление строки, полностью состоящей из значений по умолчанию:

INSERT INTO films DEFAULT VALUES;

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

INSERT INTO STUDENT (STUDENT_ID, CITY, SURNAME, NAME)

VALUES (101, ' Москва ',' Иванов ', ' Саша ');

Столбцам, наименования которых не указаны в приведенном в скобках списке, автоматически присваивается значение по умолчанию, если оно назначено при описании таблицы (команда CREATE TABLE), либо значение NULL.

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

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

INSERT INTO STUDENT 1

SELECT * FROM STUDENT WHERE CITY = ^ Москва ';

При этом таблица STUDENT 1должна быть предварительно создана командой CREATE TABLE и иметь структуру, идентичную таблице STUDENT.

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

Удаление всех фильмов, кроме мюзиклов:

DELETE FROM films WHERE kind <> 'Musical';

Очистка таблицы films:

DELETE FROM films;

Удаление завершённых задач с получением всех данных удалённых строк:

DELETE FROM tasks WHERE status = 'DONE' RETURNING *;

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

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

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

Изменение слова Drama на Dramatic в столбце kind таблицы films:

UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama';

Изменение значений температуры и сброс уровня осадков к значению по умолчанию в одной строке таблицы weather:

UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT WHERE city = 'San Francisco' AND date = '2003-07-03';

Более подробная информация: https://www.postgresql.org/docs/9.5/tutorial-sql.html

 

 

Оборудование

Персональный компьютер с установленной операционной системой WindowsXP/7/8, браузер (Например, InternetExplorer, GoogleChrome, Opera), СУБДPostgreSQL.

 

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

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

2. Напишите запрос, обновляющий значение двух полей одной строки.

3. Напишите запрос, удаляющий значение одной строки.

4. Напишите запрос, переносящий для нескольких строк значение одного поля в другое поле.

5. Напишите запрос, переносящий несколько строк одной таблицы в другую таблицу.

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

7. Выполните запрос на вставку данных и получите только что вставленное значение первичного ключа (конструкция RETURNING ID).

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

1. Даны таблицы Город и Разговор. Удалить данные об абонентах, суммарная продолжительность телефонных разговоров которых оказалась меньше 10 мин.

CREATE TABLE Город  (Код_Города INT, Название VARCHAR(20) NOT NULL, Тариф MONEY)

CREATE TABLE Разговор (Код_Разговора INT, Код_ГородаINTNOTNULL, Фамилия      VARCHAR(20), Дата         DATETIME, Продолжительность INT)

2. Даны таблицы Автор и Книга. Удалить сведения об авторах, издавших только одну книгу.

CREATE TABLE Автор (Код_Автора INT, Фамилия VARCHAR(50))

CREATE TABLE Книга (Код_Книги INT, Название VARCHAR(50), Цена  MONEY, Код_Автора INT)

3. Увеличить на 10% стоимость билетов, проданных в день вылета рейса.

4. Уменьшить на 50% продолжительность телефонных разговоров тех абонентов, которые за последний месяц сделали более 1000 звонков.

5. Даны таблицы

CREATE TABLE Город (Код_Города INT, Название VARCHAR(20) NOT NULL, Тариф MONEY)

CREATE TABLE Разговор (Код_Разговора INT, Код_Города INT NOT NULL, Фамилия IVARCHAR(20), Дата DATETIME NOT NULL, Продолжительность INT NOT NULL)

Удалить информацию о городах, с которыми не было ни одного телефонного разговора.

6. Даны таблицы

CREATE TABLE Блюдо (Название_блюдаVARCHAR(20) NOTNULL, Время_приготовления INT NOT NULL, Общая_калорийность INT NOT NULL, Номер_рецепта INT,  Повар VARCHAR(20), Стоимость_блюда MONEY)

CREATE TABLE Компонент (Название_компонента VARCHAR(20), КалорийностьINTNOTNULL, ЖирыINT, БелкиINT, БлюдоVARCHAR(20), Углеводы INT, Стоимость_компонента MONEY NOT NULL)

Удалить сведения о блюдах, стоимость которых меньше средней стоимости компонентов.

7. Даны таблицы:

CREATE TABLE Автор (Код_Автора INT, Фамилия VARCHAR(50) NULL, Имя VARCHAR(50) NULL, Отчество VARCHAR(50) NULL, Пол VARCHAR(50) NOT NULL, Дата_рождения DATETIME, Телефон CHAR(9))

CREATE TABLE Книга (Код_Книги INT, Название VARCHAR(50) NOT NULL, Цена MONEY, Тематика VARCHAR(50) NOT NULL, Издательство VARCHAR(50) NOT NULL, Код_Автора INT NOT NULL)

Удалить сведения об авторах, чьи произведения не издаются.

Лабораторная работа №4