17221

Реализация запросов на модификацию данных средствами SQL

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

Информатика, кибернетика и программирование

Лабораторная работа № 5 Тема: Реализация запросов на модификацию данных средствами SQL. Цель работы: Изучить синтаксические конструкции инструкций SQL на модификацию данных. Используя инструкции удаления добавления и изменения данных а также совместное использова...

Русский

2013-06-30

84.5 KB

3 чел.

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

Тема: Реализация запросов на модификацию данных средствами SQL.

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

Структура лабораторной работы

  1.  Общие сведения об инструкциях SQL манипулирования данными
    1.  Создание копии таблицы
    2.  Добавление данных
    3.  Удаление данных
    4.  Изменение данных
  2.  Задание к лабораторной работе
    1.  Реализовать запросы средствами SQL
  3.  Оформление отчета
  4.  Контрольные вопросы

1. Общие сведенья об инструкциях SQL манипулирования данными

1.1. Создание копии таблицы

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

Запрос SELECTINTO указывает таблицы или запросы, которые содержат поля, перечисленные в инструкции SELECT.

Синтаксис

SELECT поле_1[, поле_2[, ...]] INTO новаяТаблица FROM источник

Ниже перечислены аргументы инструкции SELECT...INTO:

Элемент

Описание

поле_1, поле_2

Имена полей, которые следует скопировать в новую таблицу.

новаяТаблица

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

источник

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

Замечания

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

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

Например.

1) Создать таблицу titles_2, содержащую информацию об изданиях:

SELECT title, yearpub, pub_id INTO titles_2 FROM titles

2) Создать таблицу publisher_titles, содержащую информацию об изданиях и авторах книг:

SELECT title, yearpub, pub_id INTO publisher_titles FROM titles AS a, publishers AS b WHERE a.pub_id=b.pub_id

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

Для добавления строк в таблицу используется инструкция SQL для модификации одной таблицы INSERT INTO.

Инструкция образует запрос на добавление записей.

Синтаксис

Запрос на добавление одной записи:

INSERT INTO назначение [(поле_1[, поле_2[, ...]])]

VALUES (значение_1[, значение_2[, ...])

Запрос на добавление записей из таблиц входящих в БД:

INSERT INTO назначение [(поле_1[, поле_2[, ...]])]

SELECT поле_1[, поле_2[, ...] FROM выражение 

Ниже перечислены аргументы инструкции INSERT INTO:

Элемент 

Описание

назначение

Имя таблицы или запроса, в который добавляются записи.

источник

Имя таблицы или запроса, откуда копируются записи.

поле_1, поле_2

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

выражение

Имена таблицы или таблиц, откуда вставляются данные. Это выражение может быть именем отдельной таблицы или результатом операции INNER JOIN, LEFT JOIN или RIGHT JOIN, а также сохраненным запросом.

значение_1, значение_2

Значения, добавляемые в указанные поля новой записи. Каждое значение будет вставлено в поле, занимающее то же положение в списке: значение_1 вставляется в поле_1 в новой записи, значение_2 в поле_2 и т.д. Каждое значение текстового поля следует заключать в кавычки (' '); для разделения значений используйте запятые.

Дополнительные сведения

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

Инструкцию INSERT INTO можно использовать для добавления набора записей из другой таблицы (таблиц) или запроса с помощью предложения SELECT ... FROM.

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

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

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

Например.

1) Добавить в таблицу titles информацию о новой книге:

insert into titles

values (24, ‘Введение в системы баз данных’, 2001, 345)

2) Добавить идентификатор издательства VHS (таблица publishers) в таблицу titles:

insert into publishers (pub_id)

select pub_id from publishers where publisher = ‘VHS’

В запросах на добавление можно комбинировать данные, часть выбирать из таблиц БД, часть задавать в виде констант. Для реализации такого запроса необходимо использовать INSERT совместно с select.

Например.

1) Добавить в таблицу titles информацию о новой книге издательства 'VHS'. Здесь, как видно, все данные кроме pup_id определяются как константа, а номер необходимо определить по данным из таблицы publishers:

insert into titles (title_id, title, yearpub, pub_id)

select 24, ‘Введение в системы баз данных’, 2001,pub_id 

from publishers where publisher = ‘VHS’

Внимание! 

В запросах такого типа обязательно указывать поля для занесения данных после имени таблицы в разделе INSERT INTO.

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

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

Для удаления одной или несколько строк таблицы используется инструкция SQL DELETE.

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

Синтаксис

DELETE [таблица.*] FROM таблица WHERE условиеОтбора

Ниже перечислены аргументы инструкции DELETE:

Элемент

Описание

таблица

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

таблица

Имя таблицы, из которой удаляются записи.

условиеОтбора

Выражение, определяющее удаляемые записи.

Дополнительные сведения

При применении инструкции DELETE удаляются только данные, при этом сохраняются структура таблицы и все свойства атрибутов.

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

Если удалять данные нарушающие требования целостности, операция будет блокироваться ядром СУБД.

Запрос удаляет данные только из одной таблицы. Если необходимо удалить данные по условию, накладываемому на поле другой таблицы, то можно использовать технологию вложенных запросов (см. пример запроса 2).

Внимание!

Нельзя восстановить записи, удаленные с помощью запроса DELETE. Чтобы узнать, какие записи будут удалены, можно сначала просмотреть результаты, используя запрос на выборку SELECT с тем же условием отбора.

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

Например.

1) Удалить информацию о web-сайтах (таблица wwwsites) все авторов:

delete from wwwsites

2) Удалить все книги (таблица titles) издательства VHS (таблица publishers):

delete from titles where pub_id in

(select pub_id from publishers where publisher=’VHS’)

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

Для изменения значений одного или нескольких полей используется инструкция SQL для модификации данных UPDATE.

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

Синтаксис

UPDATE таблица SET новоеЗначение WHERE условиеОтбора;

Ниже перечислены аргументы инструкции UPDATE:

Элемент

Описание

таблица

Имя таблицы, данные в которой следует изменить (для нескольких таблиц использовать INNER JOIN).

новоеЗначение

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

условиеОтбора

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

Дополнительные сведения

Изменение данных можно осуществлять одновременно в нескольких таблицах. Для этого необходимо использовать либо операцию соединения (INNER JOIN) в разделе UPDATE, либо технологию вложенных запросов (см. пример запроса 2).

Внимание!

Чтобы узнать, какие записи будут изменены, сначала можно просмотреть результаты запроса на выборку SELECT с тем же условием отбора.

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

Например.

1) Изменить год издания книги “Введение в системы баз данных” на 2001 год:

Update titles set yearpub=2001

where title=’Введение в системы баз данных’

2) Изменить год издания книги (таблица titles) автора Ульмана (таблица authors):

Update titles set yearpub=2001

where title_id IN 

(select title_id from authors AS a

inner join titleauthors AS b on a.au_id=b.au_id

where author=’Ульман’)

Внимание!

В инструкции UPDATE в качестве источника данных можно использовать несколько таблиц соединив их INNER JOIN аналогично оператору инструкции SELECT.

2. Задание к лабораторной работе

2.1. Реализовать следующие запросы средствами SQL

  1.  Создать копию (со всеми данными) таблицы POST с именем POST_1.
  2.  Создать копию (со всеми данными) таблицы DETAL с именем DETAL _1.
  3.  Создать копию (со всеми данными) таблицы POSTKA с именем POSTKA_1.
  4.  Создать копию (со всеми данными) таблицы SP_MATER с именем SP_MATER_1.

В дальнейшем все действия выполнять с новыми таблицами.

  1.  Добавить нового поставщика САША с номером 10 проживающего в Харькове.
  2.  Добавить детали:
    1.  гайка с номер материала M2 по цене 24 с номером Д10.
    2.  болт с номер материала M1 по цене 34,75 с номером Д11.
    3.  Стальной корпус по цене 64,44 с номером Д12 (воспользоваться инструкцией SELECT).
    4.  Пластиковый корпус по цене 44,65 с номером Д13 (воспользоваться инструкцией SELECT).
  3.  Добавить поставку чугунных гаек по цене 24 в количестве 40 шт. поставщиком с номером 1.
  4.  Добавить поставку стальных корпусов по цене 64,44 поставляемых поставщиком Маша из Харькова в количестве 55 шт.
  5.  Изменить количество поставок стальных корпусов по цене 64,44 на 100 шт.
  6.  Изменить количество на 150 шт. всех поставок из Сумм.
  7.  Изменить все поставки деталей, поставляемых КолЕЙ на чугунные болты по цене 34,75 (номер Д11).
  8.  Изменить номер детали пластиковый корпус по цене 44,65 на Д25.
  9.  Изменить количество каждой поставки на 200 шт., всех деталей, цена которых меньше чем 10.
  10.  Изменить адрес на Киев поставщиков, поставляющих ЧУГУННЫЙ фланец в количестве более 20 шт.
  11.  Удалить детали, поставляемые поставщиком Маша из Харькова.
  12.  Удалить детали поставляемые поставщиками КОЛЕЙ из ХАРЬКОВА.
  13.  Удалить поставщиков, не поставляющих ни одной детали.
  14.  Удалить информацию о поставках деталей из таблицы DETAL_1, которые поставляются в количестве не менее 20 шт. и не более 40 шт.
  15.  Удалить поставщиков поставляемых детали одновременно Д1 и Д2.
  16.  Удалить поставщиков, не поставляющих одновременно детали Д3 и Д4.

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

  1.  Титульный лист оформляется согласно традиционным требованиям, включая Номер работы, Номер группы и ФИО студента, а также кто принимал данную работу.
  2.  Содержание должно включать Тему лабораторной работы и Ход ее выполнения.
  3.  Ход выполнения работы должен содержать все инструкции SQL реализованные в лабораторной работе. Условия заданий записывать не обязательно.
  4.  Выводы.

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

  1.  Свойства инструкции selectinto.
  2.  Свойства инструкции insert.
  3.  Свойства инструкции delete.
  4.  Свойства инструкции update.


 

А также другие работы, которые могут Вас заинтересовать

14716. Определение вязкости жидкости по методу падающего шарика 87.5 KB
  ОТЧЕТ По лабораторной работе № 4 Определение вязкости жидкости по методу падающего шарика 1. Расчетная формула для измеряемой величины: где – плотность материала шариков; – плотность жидкости; ...
14717. Определение работы выхода электрона по прямым Ричардсона 323.5 KB
  ОТЧЕТ ПО ЛАБОРАТОРНОЙ РАБОТЕ № 2 Определение работы выхода электрона по прямым Ричардсона по дисциплине Физика твердого тела Цель работы Построить график зависимости анодного тока от анодного напряжения при трех разных значениях тока като...
14718. Визначення втрат тепла з відпрацьованими газами двигуна внутрішнього згоряння 135.5 KB
  Лабораторна робота №5 Визначення втрат тепла з відпрацьованими газами двигуна внутрішнього згоряння Мета роботи: експериментальне визначення витрат тепла поршневого двигуна внутрішнього згоряння з відпрацьованими газами на різних режимах його роботи. Обладн
14719. Вплив регулювань системи запалювання на потужнісні характеристики автомобільного двигуна 356 KB
  Лабораторна робота №2 Вплив регулювань системи запалювання на потужнісні характеристики автомобільного двигуна Мета роботи: визначення впливу регулювань системи запалювання автомобільного двигуна на показники його потужності і приймістості. Обладнання: дви...
14720. Визначення втрат тепла через систему охолодження автомобільного двигуна 405.5 KB
  Лабораторна робота № 4 Визначення втрат тепла через систему охолодження автомобільного двигуна Мета роботи: Вивчення теплового балансу двигуна і практичне визначення втрат тепла через систему охолодження автомобільного двигуна. Обладнання: Двигун ЗІЛ 130 ...
14721. Алгоритм анализа качества системы при детерминированных и случайных воздействиях 80.51 KB
  Алгоритм анализа качества системы при детерминированных и случайных воздействиях Задача анализу известной динамической системы в конкретных условиях ее эксплуатации состоит в определении выходных реакций и сигналов управления систем при определенных входных сигнал...
14722. Таблицы решений 128 KB
  Лабораторная работа № 3. Таблицы решений Цель работы Целью работы является изучение таблиц решений и спецификация с помощью данного механизма логики вычислительных процессов. Содержание отчета Итоговым документом выполнения лабораторной работы является отчет с
14723. Flow-формы и диаграммы Насси-Шнейдермана 44 KB
  Лабораторная работа № 2. Flowформы и диаграммы НассиШнейдермана Цель работы Изучение и практическое применение принципов разработки спецификаций вычислительных процессов с помощью визуальных языков Flowформ и диаграмм НассиШнейдермана. Содержание отчета Итоговы
14724. Схемы алгоритмов 76 KB
  Контрольная работа. Схемы алгоритмов Цель работы Целью работы является практическое изучение процесса спецификации алгоритмов с помощью схем. Содержание отчета Итоговым документом выполнения контрольной работы является отчет состоящий из следующих пунктов. ...