Главная страница

Лекции и практики (1). Курс лекций и материалы для практических занятий


Скачать 1.01 Mb.
НазваниеКурс лекций и материалы для практических занятий
Дата17.03.2023
Размер1.01 Mb.
Формат файлаdocx
Имя файлаЛекции и практики (1).docx
ТипКурс лекций
#996812
страница16 из 75
1   ...   12   13   14   15   16   17   18   19   ...   75

я форма:


CASE <проверяемое выражение>

WHEN <сравниваемое выражение 1> THEN <возвращаемое значение 1> […

WHEN <сравниваемое выражение N> THEN <возвращаемое значение N>] [ELSE <возвращаемое значение>]

END
  1. я форма:


CASE

WHEN <предикат 1> THEN <возвращаемое значение 1> […

WHEN <предикат N> THEN <возвращаемое значение N>] [ELSE <возвращаемое значение>]

END

Все предложения WHENдолжны иметь одинаковую синтаксическую форму, то есть нельзя смешивать первую и вторую формы. При использовании первой синтаксической формы условие WHENудовлетворяется, как только значение проверяемого выражения станет равным значению выражения, ука- занного в предложении WHEN. При использовании второй синтаксической формы условие WHEN удовлетворяется, как только предикат принимает значе- ние TRUE. При удовлетворении условия оператор CASE возвращает значение, указанное в соответствующем предложении THEN. Если ни одно из условий WHEN не выполнилось, то будет использовано значение, указанное в предло- жении ELSE. При отсутствии ELSE, будет возвращено NULL-значение. Если

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

THEN первого из них, так как остальные просто не будут проверяться. Примерыиспользования операцииCASE:

  1. Вывести список сотрудников с указанием количества сыновей и дочерей:

SELECT e.name,

count(case when sex='м' then 1 else null end) sons, count(case when sex='ж' then 1 else null end) daughters FROM emp e, children c

WHERE e.tabno=c.tabno GROUP BY e.name;


NAME

SONS

DAUGHTERS

Буров Г.О.

0

1

Малова Л.А.

1

1

Рюмин В.П.

1

0

Серова Т.В.

2

1

Обратите внимание: бездетные сотрудники в список не вошли.

  1. Вывести список сотрудников с указанием тех, у кого самая высокая и самая низкая зарплата на предприятии:

SELECT e.name, salary, (case salary

when (select max(salary) from emp) then 'самая высокая' when (select min(salary) from emp) then 'самая низкая' else 'средняя'

end) as note FROM emp e;


NAME

SALARY

NOTE

Рюмин В.П.

48500

средняя

Серова Т.В.

48500

средняя

Волков Л.Д.

46500

средняя

Перова К.В.

32000

самая низкая

Буров Г.О.

42880

средняя

Малова Л.А.

59240

средняя

Сухова К.А.

48500

средняя

Лукина Н.Н.

42880

средняя

Дурова А.В.

43500

средняя

Тамм Л.В.

43500

средняя

Павлов А.А.

80000

самая высокая

Котова И.М.

35000

средняя

Кроль А.П.

70000

средняя

        1. Работа с представлениями

Представление (view, обзор) это хранимый запрос, создаваемый на ос- нове команды SELECT. Представление реально не содержит данных. Запрос,

определяющий представление, выполняется тогда, когда к представлению про- исходит обращение с другим запросом, например, SELECT,UPDATEи т.д.

Назначение представлений:

  1. Хранение сложных запросов.

  2. Представление данных в виде, удобном пользователю.

  3. Сокрытие конфиденциальной информации.

  4. Предоставление дифференцированного доступа к данным. Создание представления выполняется командой CREATEVIEW:

CREATE [ OR REPLACE ] VIEW <имя представления> [ (<список имён столбцов>) ]

AS <запрос> [ WITH CHECK OPTION ];

Запрос (команда SELECT), на основании которого создаётся представле- ние, называется определяющим запросом, а таблицы, к которым происходит обращение в определяющем запросе – базовыми таблицами. Определяющий запрос по стандарту SQL не может включать фразу ORDER BY, хотя большин- ство СУБД позволяют включать эту фразу в определяющий запрос.

Если не указывать имена столбцов, то они получат названия по именам, перечисленным в списке выбора определяющего запроса. Указывать имена столбцов представления обязательно, если список выбора содержит вычисляе- мые поля, константы, функции (в том числе, агрегирующие) или столбцы с одинаковыми именами из разных таблиц. Имена можно указать в списке имён после имени представления (как в примере ниже), а можно указать алиасы для отдельных столбцов результата (например, select name, salary*0.87 AS sal,…). Преимущество имеет список имён, указанный после имени представления.

Примеры:

  1. Создать представление "Сотрудники с детьми" (для удобного представления данных о детях сотрудников):

CREATE VIEW emp_child(depno, name, child, sex, born) AS SELECT e.depno, e.name, c.name, c.sex, c.born

FROM emp e, children c WHERE e.tabno = c.tabno;

SELECT * FROM emp_child;


DEPNO

NAME

CHILD

SEX

BORN

2

Буров Г.О.

Ольга

ж

18.07.2001

2

Малова Л.А.

Илья

м

19.02.1987

2

Малова Л.А.

Анна

ж

26.12.1989











1

Серова Т.В.

Антон

м

06.03.2009

  1. Создать представление "Сотрудники 2-го отдела" (для предоставления пол- ного доступа к данным о сотрудниках 2-го отдела начальнику этого отдела):

CREATE VIEW emp2 AS SELECT *

FROM emp

WHERE depno = 2; SELECT * FROM emp2;

TABNO

DEPNO

NAME

POST

SALARY

BORN

PHONE

110

2

Буров Г.О.

бухгалтер

42880

22.05.75

115-46-32

100

2

Волков Л.Д.

программист

46500

16.10.82




130

2

Лукина Н.Н.

бухгалтер

42880

12.07.79

115-46-32

023

2

Малова Л.А.

гл. бухгалтер

59240

24.11.54

114-24-55

  1. Создать представление "Сотрудники" (без данных о зарплате, для сокрытия конфиденциальной информации):

CREATE VIEW employees

AS SELECT tabno, depno, name, post, born, phone FROM emp;

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

Примеробновлениябазовойтаблицыempчерезпредставлениеemp2:

UPDATE emp2

SET salary = 48000 WHERE tabno = '100';

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

SELECT * FROM emp2;


TABNO

DEPNO

NAME

POST

SALARY

BORN

PHONE

110

2

Буров Г.О.

бухгалтер

42880

22.05.75

115-46-32

100

2

Волков Л.Д.

программист

48000

16.10.82




130

2

Лукина Н.Н.

бухгалтер

42880

12.07.79

115-46-32

023

2

Малова Л.А.

гл. бухгалтер

59240

24.11.54

114-24-55

Вносимые изменения могут выйти за рамки определяющего запроса и по- этому не будут видны через представление. Если необходимо защитить данные от такого вмешательства, то нужно в команде создания представления указать ключевые слова WITH CHECK OPTION: тогда система отвергнет изменения, выходящие за рамки определяющего запроса.

По стандарту SQL-2 представление не является обновляемым, если опре- деляющий запрос:

    1. содержит ключевое слово DISTINCT;

    2. содержит множественные операции (UNIONи др.);

    3. содержит предложение GROUPBY;

    4. ссылается на другое необновляемое представление;

    5. содержит вычисляемые выражения в списке выбора;

    6. выбирает данные более чем из одной таблицы.

Многие СУБД поддерживают т.н. частично обновляемые представления, т.е. такие представления, над которыми допускаются отдельные операции мо- дификации данных. Например, если представление содержит вычисляемое вы-

ражение в списке выбора, то СУБД позволяет менять данные во всех полях, кроме вычисляемого поля. Общее правило определения частичной обновляемо- сти представления такое: если СУБД может однозначно определить, к какой строке и к какому полю таблицы применить изменения, она их применит.
      1. 1   ...   12   13   14   15   16   17   18   19   ...   75


написать администратору сайта