6812

Манипулирование данными в SQL Oracle

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

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

Манипулирование данными в SQLOracle Цели лабораторной работы Изучить возможности SQL Oracle по вставке, обновлению и удалению строк в таблице. Приобрести практический опыт по вставке, обновлению и удалению строк в таблице с и...

Русский

2013-01-08

121 KB

8 чел.

Манипулирование данными в SQL Oracle

  1.  Цели лабораторной работы
  •  Изучить возможности SQL Oracle по вставке, обновлению и удалению строк в таблице.
  •  Приобрести практический опыт по вставке, обновлению и удалению строк в таблице с использованием SQL*Plus.
  1.  Теоретические основы

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

  •  Предложение INSERT – позволяет добавлять новые строки в таблицу.
  •  Предложение UPDATE – позволяет изменять значения столбцов существующих строк таблицы.
  •  Предложение DELETE – позволяет удалять строки таблиц.
    1.  Вставка строк в таблицу
      1.  Синтаксис

  1.  Назначение

Вставка новых строк в таблицу.

  1.  Предварительные требования

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

Для вставки строк в таблицы других схем необходима системная привилегия INSERT ANY TABLE.

  1.  Ключевые слова и параметры 

schema  

Имя схемы, содержащей таблицу. Если схема опущена, то Oracle предполагает, что таблица находится в вашей схеме.  

table

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

column  

 

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

Если в списке опущено имя столбца, то ему будет присвоено ьзначение по умолчанию, заданное при определении таблицы. Для получения дополнительной информации о значениях по умолчанию см. лабораторную работу 1. Если такой столбец имеет ограничение NOT NULL и для него не определено значение по умолчанию, то Oracle выдаст ошибку, указывающую, что нарушено ограничение NOT NULL, и произведет полный откат результатов выполнения предложения INSERT.

Если список столбцов отсутствует, то фраза VALUES или подзапрос должны содержать значения для всех столбцов таблицы.  

values_clause  

specifies a row of values to be inserted into the table or view. You must specify a value in the values_clause for each column in the column list. If you omit the column list, then the values_clause must provide values for every column in the table.  

subquery  

 

 

Подзапрос, которые возвращает строки, вставляемые в таблицу. Если подзапрос не возвращает строк, Oracle не вставляет ни одной строки в таблицу.

Если подзапрос специфицирован с фразой VALUES, то подзапрос возвращает значения, вставляемые в одну строку.

Если подзапрос специфицирован без фразы VALUES, то подзапрос возвращает значения, вставляемые в боле, чем одну строку.

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

t_alias  

Предоставляет имя-синоним для таблицы, которое может использоваться в любом месте предложения INSERT.  

  1.  Примеры

Примеры с использованием фразы VALUES

В следующем примере вставляется стока в таблицу FACULTY. Так как вставляются значения всех столбцов, список столбцов опускается:

INSERT INTO FACULTY   

  VALUES (1, 'informatics', 'NULL', '5', 93517);

В следующем примере вставляются значения в 7 столбцов таблицы TEACHER. Двум столбцам явно присваивается значение NULL. Значения двух столбцов (Tel и Commission) не определены. Поэтом столбцу Tel присваивается значение NULL, а Commission – значение по умолчанию 0. Последний столбец указывает, что данный преподаватель имеет в качестве своего руководителя преподавателя с первичным ключом 2:

INSERT INTO TEACHER (TchPK, DepFK, name, post, hiredate, salary, ChiefFK)

  VALUES (15, 7, 'Ivanov', NULL, NULL, TO_DATE('25.07.2001', 'DD.MM.YYYY'), 350.00, 2);

Пример с использование подзапроса

В следующем примере производится копирование значений всех столбцов для преподавателей-профессоров с надбавками, превышающими 25% ставки, в таблицу BONUS:

INSERT INTO bonus

  SELECT *

  FROM TEACHER

  WHERE Commission > 0.25 * salary AND post 'proessor';

  1.  Обновление строк в таблице
    1.  Синтаксис

  1.  Назначение

Изменение существующих значений столбцов в соответствующих строках таблицы.

  1.  Предварительные требования 

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

Для обновления строк в таблицах других схем необходима системная привилегия UPDATE ANY TABLE.

  1.  Ключевые слова и параметры 

schema  

Имя схемы, содержащей таблицу. Если схема опущена, то Oracle предполагает, что таблица находится в вашей схеме.  

table

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

set_clause  

 

 

column  

Имя столбца таблицы, значение которого должно быть обновлено. Если столбец отсутствует во фразе the set_clause, значение столбца не изменяется.

subquery  

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

Если вы определили только один столбец во фразе set_clause, подзапрос должен возвратить только одно значение.

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

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

expr  

Новое значение, присваиваемое соответствующему столбцу. 

where_clause  

Ограничивает строки, которые подвергаются обновлению. Строка обновляется, если указанное условие condition принимает значение TRUE. Если эта фраза опущена,  то Oracle обновляет все строки таблицы. Синтаксис условий приведен в лабораторной работе 2 . Для любой строки, которая удовлетворяет условию фразы where_clause, столбцам в левой части оператора присваивания (=) фразы set_clause присваиваются значения соответствующих выражений в правой части. Выражения вычисляются в момент обновления строки.

  1.  Примеры

Простые примеры

В следующем примере всем преподавателям на должности assistant устанавливается значение NULL столбца Commission:

UPDATE TEACHER

   SET Commission = NULL

   WHERE post = 'ассистент';

В следующем примере преподаватель Jones переводится на кафедру 20 с увеличением ставки на 100 (предполагаем, что у нас имеется только один преподаватель Jones):

UPDATE TEACHER

   SET DepNo =20, salary = salary + 100

   WHERE name = 'jones';

Сложный пример

В следующем примере показывается использование следующих синтаксических конструкций предложения UPDATE:

  •  Обе формы фразы set_clause в одном предложении.
  •  Коррелированный подзапрос.
  •  Фраза where_clause, ограничивающая обновляемые строки.
  •  Использование алиаса таблицы.
  •  Обновление нескольких столбцов (salary, Commission) одновременно.

UPDATE TEACHER a

 SET DepNo =

 (SELECT DepNo

  FROM deptartment

  WHERE name = 'DBMS'),

(salary, Commission) =

 (SELECT 1.1*AVG(salary), 1.5*AVG(Commission)

  FROM TEACHER b

  WHERE a.DepNo = b.DepNo)

WHERE DepNo IN

 (SELECT DepNo

  FROM DEPARTMENT

  WHERE FacNo = 1);

Это предложение UPDATE выполняет следующие действия:

  •  Обновляет значения только у тех преподавателей, которые работают на факультете, имеющем FacNo = 1
  •  Устанавливает этим преподавателям значение DepNo, равным DepNo кафедры 'DBMS' (то есть все преподаватели переводятся на эту кафедру)
  •  Устанавливает ставку этим учителям, в 1.1 раз большую средней ставки их предыдущей кафедры.
  •  Устанавливает надбавку этим учителям, в 1.5 раз большую средней надбавки их предыдущей кафедры.
    1.  Удаление строк в таблице 
      1.  Синтаксис

  1.  Назначение 

Удаляет указанные строки в таблице.

  1.  Предварительные требования 

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

Для удаления строк в таблицах других схем необходима системная привилегия DELETE ANY TABLE.

  1.  Ключевые слова и параметры 

schema  

Имя схемы, содержащей таблицу. Если схема опущена, то Oracle предполагает, что таблица находится в вашей схеме.  

table

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

where_clause  

 

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

Если фраза where_clause опущена, Oracle удаляет все строки таблицы.  

t_alias  

Предоставляет имя-синоним для таблицы, которое может использоваться в любом месте предложения DELETE.

  1.  Примеры

В следующем примере удаляются все строки таблицы DEPARTMENT.

DELETE FROM DEPARTMENT;

В следующем примере в таблице TEACHER удаляются строки, имеющие отношение к профессорам с надбавкой менее 100:

DELETE FROM TEACHER

   WHERE post = 'professor' AND Commission  < 100;

  1.  Задание
    1.  Вставка строк в таблицу

Вставьте следующие строки в таблицы.

FACULTY

FacPK

Name

DeanFK

Building

Fund

1

informatics

NULL

5

57398.00

2

economy

NULL

3

NULL

3

linguistics

NULL

4

NULL

DEPARTMENT

DepPK

FacFK

Name

HeadFK

Building

Fund

1

1

SE

NULL

5

14378.00

2

1

CAD

NULL

5

15000.00

3

1

DBMS

NULL

4

22000.00

4

2

Accounts

NULL

3

NULL

TEACHER

TchPK

DepFK

Name

Post

Tel

Hiredate

Salary

Commission

ChiefFK

1

1

Andrew

ассистент

2281319

01.02.2001

250

80

NULL

2

1

John

профессор

2281550

01.07.2001

400

150

1

3

2

Bill

преподаватель

NULL

17.11.2002

240

80

1

4

2

Albert

ассистент

NULL

11.11.2001

260

100

4

SGROUP

GrpPK

DepFK

Course

Num

Quantity

Curator

Rating

1

1

1

101

33

4

20

2

1

1

102

35

4

22

3

3

2

205

20

1

15

4

3

3

305

25

NULL

40

5

3

4

405

25

2

37

SUBJECT

SbjPK

Name

1

pascal

2

C

3

OS

4

inernet

5

dbms

ROOM

RomPK

Num

Seates

Floor

Building

1

101

20

1

5

2

316

150

3

5

3

201

150

2

2

4

202

30

2

5

LECTURE

TchFK

GrpFK

SbjFK

RomFK

Type

Day

Week

Lesson

1

1

1

1

лекция

пон

1

1

1

2

2

1

лабораторная

пон

1

1

2

3

3

1

лекция

втр

1

3

2

4

4

2

практика

срд

1

3

4

4

5

2

практика

чет

2

4

4

4

5

3

семинар

пят

2

1

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

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

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

  •  деканы факультетов никому не подчиняются:
  •  заведующие кафедр подчиняются деканам факультетов (если зав кафедры является деканом, то он никому не подчиняется)
  •  преподаватели, читающие лекции, подчиняются заведующим кафедр
  •  преподаватели, проводящие лабораторные работы по дисциплине, подчиняются преподавателям, читающим лекции по  этой дисциплине.

Обновите таблицу FACULTY, заменяя NULL в столбце DeanFK на внешние ключи соответствующих деканов

Обновите таблицу DEPARTMENT, заменяя NULL в столбце HeadFK на внешние ключи соответствующих заведующих кафедр.

Вставьте в таблицу SGROUP информацию о вашей группе.

Вставьте в таблицу SUBJECT все изучаемые вами в текущий момент дисциплины.

Вставьте в таблицу ROOM информацию о тех аудиториях, где проводятся у вас занятия.

Вставьте в таблицу LECTURE расписание всех ваших занятий.

  1.  Обновление строк в таблицах

Сделайте следующие обновления в таблицах:

  •  В таблице FACULTY для факультета economy установите декана “Bob” и фонд = 3467.00
  •  В таблице DEPARTMENT для кафедры с DepNo =  3 измените заведующего на «Frank» и корпус на ‘3’
  •  В таблицеIn TEACHER для всех преподавателей на должности «assistant» установите значение столбца Commission равным 25% от значения столбца Salary.
  •  В таблице SGROUP установите значение rating = 0 для всех групп первого курса.
  •  В таблице SUBJECT измените название предмета с “internet” на “html
  1.  Контрольные вопросы

Ответьте на следующие вопросы:

  1.  Необходимо ли, чтобы порядок перечисления столбцов в предложении INSERT соответствовал порядку их определения при создании таблицы?
  2.  Какие значение присваиваются столбцу, если он не присутствует в списке столбцов предложения INSERT?
  3.  Что произойдет, если столбец отсутствует в списке столбцов предложения INSERT и этот столбец имеет ограничение целостности NOT NULL и для него не определено значение по умолчанию?
  4.  Что означает отсутствие списка столбцов в предложении INSERT?
  5.  Можно ли указать значение NULL во фразе VALUES?
  6.  Какова цель использования имен-синонимов (алиасов) таблиц?
  7.  Что означает отсутствие фразы WHERE в предложении UPDATE?
  8.  Что означает отсутствие фразы WHERE в предложении DELETE?

PAGE  5


 

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

18019. Деньги и Кредит 2.59 MB
  Деньги и Кредит СОДЕРЖАНИЕ: РАЗДЕЛ. ДЕНЬГИ Глава. НЕОБХОДИМОСТЬ ДЕНЕГ ИХ ВОЗНИКНОВЕНИЕ И СУЩНОСТЬ. Предпосылки и значение появления денег. Сущность денег. ФУНКЦИИ ВИДЫ ДЕН
18020. Деньги, кредит, банки 2.17 MB
  Кравцовa Г.И. Деньги кредит банки Содержание ПредисловиеРаздел I. ДЕНЬГИГлава 1. Необходимость и сущность денегКонцепции происхождения денег. Сущность денегВиды денегТеории денегГлава 2. Функции денегДеньги как мера стоимостиДеньги как средство обращенияДеньги к...
18021. Директ-маркетинг. Учебное пособие 2.3 MB
  И. В. Есинова С. В. Бачило Мишина Л.А. Директмаркетинг Глава 1. Директмаркетинг как инструмент маркетинга Директмаркетинг представляет собой вид рыночной деятельности в которой проявляется особый интерес к индивидуальным запросам потребителя и его лично
18022. ЭКОНОМИЧЕСКИЙ РИСК И МЕТОДЫ ЕГО ИЗМЕРЕНИЯ 376.5 KB
  Конспект лекций по дисциплине Экономический риск и методы его измерения 1 Оценка риска деятельности предприятия 1.1 Суть и виды рисков. 1.2 Методы анализа рисков. 1.3 Способы снижения риска самостоятельная проработка 1.4 Учет рисков при финансировании проект
18023. Экономическая диагностика предприятия с помощью финансовых коэффициентов 351 KB
  Экономическая диагностика предприятия с помощью финансовых коэффициентов Система финансовых коэффициентов В настоящем разделе речь пойдет об использовании системы коэффициентов для анализа финансового положения компании на основании ее финансовой отчетнос...
18024. ЭФФЕКТИВНЫЕ МЕТОДЫ ПРОДАЖ 1.19 MB
  БРАЙАН ТРЕЙСИ ЭФФЕКТИВНЫЕ МЕТОДЫ ПРОДАЖ Введение Я считаю что все самые лучшие продавцы во многом схожи. Иногда на семинарах для продавцов я провожу опыт суть которого состоит в том что я описываю лучших работников их фирмы. Более или менее подробно представляю их х...
18025. Эффективный управляющий 768.5 KB
  Питер Ф. Друкер. Эффективный управляющий Многие утверждают что ни одна личность не оказала такого всеобъемлющего влияния на развитие бизнеса в 20 века какое оказал Питер Ф. Друкер. Фактически он создал менеджмент как дисциплину в 50е гг. превратив эту непопулярную и н...
18026. Эконометрические методы 1.78 MB
  Эконометрические методы Введение Данное пособие появилось как результат факультатива и спецкурса прочитанных автором для студентов экономического факультета Новосибирского университета в 1996 г. Пособие состоит из двух самостоятельных разделов. Раздел I основан ...
18027. ЭКОНОМИЧЕСКАЯ СТАТИСТИКА 1.55 MB
  379 ЭКОНОМИЧЕСКАЯ СТАТИСТИКА СОДЕРЖАНИЕ ПРЕДИСЛОВИЕ3 Глава 1. ПРЕДМЕТ МЕТОДЫ И ЗАДАЧИ 6 ЭКОНОМИЧЕСКОЙ СТАТИСТИКИ6 1. Понятие об экономической статистике 6 ее предмет и методы6 2. Задачи экономической статистики и области 9 применения ее данных...