17221

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

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

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

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

Русский

2013-06-30

84.5 KB

4 чел.

Лабораторная работа № 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.


 

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

80421. ОРГАНІЗАЦІЯ ТА ОБЛІК РОЗРАХУНКІВ З ОПЛАТИ ПРАЦІ (НА МАТЕРІАЛАХ СІЛЬСЬКОГОСПОДАРСЬКИХ ПІДПРИЄМСТВ БЕРЕЗАНСЬКОГО РАЙОНУ) 1.56 MB
  Актуальність даного питання посилюється ще й тим, що в сучасних умовах здійснюється адаптація існуючої системи обліку та організації праці відповідно до міжнародних стандартів, впроваджуються нові методики організації обліку оплати праці на підприємствах.
80422. Развитие интеллектуальных умений при обучении математике (на примере умений анализировать, синтезировать, алгоритмизировать) 526 KB
  Чтобы учащиеся могли глубже осознать междисциплинарные связи, понять возможность переноса результатов с одного учебного предмета на другой, у них не должно создаваться впечатления, будто каждый предмет призван решать свои, отдельные от других дисциплин, задачи.
80423. Разработка алгоритма изготовления детали «Втулка-регулирующая» 862 KB
  Простейшие токарные станки были известны еще в глубокой древности. Эти станки были весьма примитивны по конструкции: заготовка вращалась от ножного привода, а режущий инструмент приходилось держать в руках. Работа на таких станках была непроизводительной, утомительной и неточной.
80424. Проектирование высокоскоростной корпоративной сети на базе технологии СЦИ 2.27 MB
  Данная дипломная работа заключается в разработке схемы проектирования и технической реализации корпоративной сети связи, на основе технологии СЦИ, с целью создания каналов связи высокого качества между 11-ми объектами, расположенными на территории Волгоградской области.
80425. Разработка финансовой модели проекта птичника на ЗАО «Агрофирма Боровская» 859 KB
  Необходимость существенного повышения уровня и качества жизни населения выдвигает в качестве неотложной задачи ускорение формирования нового агропромышленного производства на основе модернизации и ускоренного развития инновационных процессов, совокупности последовательно осуществляемых...
80426. СОЗДАНИЕ МОБИЛЬНОГО ИНФОРМАЦИОННОГО РЕСУРСА 143 KB
  В рамках данной дипломной работы будет описан процесс создания мобильной версии информационного ресурса Яковлевской центральной детской библиотеки в городе Строитель ориентированной в частности на учащихся средних школ Яковлевского района Белгородской области.
80427. Принцип разделения властей и его реализация к Конституции Российской федерации 1993-го года 127 KB
  Суть данного принципа заключается в том, чтобы не допустить узурпации власти в руках главы одной ветви власти, чтобы размеренно, путём системы сдержек и противовесов, одна ветвь власти влияла на другую, контролировала и выявляла недостатки в управлении, взаимодействовала, чтобы один орган не оказался таком положении...
80429. Финансово-правовое стимулирование инвестиционной деятельности в субъекте Российской Федерации (на примере Томской области) 43.13 KB
  Как известно, рыночная экономика функционирует по принципу естественного отбора. Но уровень развития производственной, иной хозяйственной деятельности на соответствующей территории едва ли можно назвать исключительно частным делом – ведь от этого зависит уровень жизни проживающих в этой местности граждан.