Информационное обеспечение



бет6/56
Дата05.03.2023
өлшемі256,49 Kb.
#71567
түріПрактикум
1   2   3   4   5   6   7   8   9   ...   56
Байланысты:
СУБДOracle

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





  1. Что такое реляционная таблица и из каких элементов она состоит?

  2. Какие типы данных применяются для определения столбцов таблиц?

  3. Какие столбцы называют псевдостолбцами и почему?

  4. Какие типы ограничений используются при создании таблиц?

  5. Что понимают под бизнес-логикой?

  6. Какие правила можно использовать для обеспечения ссылочной це- лостности при создании связи между таблицами БД?

  7. Объясните порядок определения столбцов и ограничений при создании таблиц в инструкции CREATE TABLE.

  8. В чем разница между значениями 0, пустой строкой и NULL?

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

  10. Для чего и как используется команда DESCRIBE?

  11. Как можно создать копию существующей таблицы?


  12. 20
    Что такое индексы и для чего они используются в БД?

  13. Какие особенности работы индексов относят к их недостаткам?

  14. Какие изменения можно вносить в определение таблицы инструкцией ALTER TABLE?

  15. Для чего может применяться синоним таблицы, в чем его преимуще- ство по сравнению с псевдонимами?

  16. Как создать последовательность и для чего она необходима?

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

  18. Что такое словарь данных, какие сведения в нем хранятся?

ЛАБОРАТОРНАЯ РАБОТА №2 СОЗДАНИЕ ЗАПРОСОВ




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


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




Условные запросы. Инструкция SELECT извлекает информацию из базы данных и возвращает ее в виде таблицы результатов запроса. Данная инструк- ция состоит из шести основных предложений: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY.
Предложения SELECT и FROM являются обязательными, остальные че- тыре включаются в запрос при необходимости.
В предложении SELECT указывается список столбцов, которые должны быть возвращены инструкцией.
В предложении FROM обычно указывается список таблиц или представ- лений, которые содержат элементы данных, извлекаемые запросом. Например, следующий запрос извлекает из таблицы Staff три столбца, содержащих имя, фамилию и занимаемую должность каждого сотрудника:

SELECT fname, lname, position FROM Staff;


Помимо этого, в предложении SELECT могут содержаться вычисляемые столбцы, например, выдать строки сотрудников с указанием зарплаты с 10%-й надбавкой:


SELECT fname, lname, position, (salary + 0.1*salary) AS percent FROM Staff;


В данном запросе percent – это псевдоним столбца, который определяет название столбца в результирующей таблице запроса. Ключевое слово AS явля- ется необязательным, тогда псевдоним указывается через пробел.


Предложение WHERE показывает, что в результаты запроса следует включать только те строки, которые соответствуют условию отбора.
В SQL обычно используются пять основных условий отбора (в стандарте ANSI/ISO они называются предикатами):

  • сравнение;

  • проверка на принадлежность диапазону;

  • проверка на членство во множестве;

  • проверка на соответствие шаблону;

  • проверка на равенство значению NULL.

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

SELECT fname, lname FROM Staff


WHERE dob < TO_DATE('01.01.1988', 'dd.mm.yyyy');

Формат записи данных типа дата/время зависит от серверных настроек и при необходимости его можно уточнить запросом SELECT sysdate FROM dual или использовать функции TO_CHAR или TO_DATE, как в примере.


Пример на принадлежность диапазону: найти служащих, родившихся в интервале времени с 1 октября 1963 года по 31 декабря 1971 года.

SELECT fname, lname FROM Staff


WHERE dob BETWEEN TO_DATE('01.10.73', 'dd.mm.yyyy') AND TO_DATE('31.12.91', 'dd.mm.yyyy');

Здесь следует отметить, что проверка на принадлежность диапазону не расширяет возможностей SQL, поскольку ее можно выразить в виде двух срав- нений, т. е. выражение A BETWEEN B AND C эквивалентно (A>=B) AND (A<=C).


Пример на членство во множестве: вывести информацию об офисах, рас- положенных в Минске, Витебске и Бресте.

SELECT address, tel_no FROM Branch


WHERE city IN ('Минск', 'Витебск', 'Брест');

Проверка IN также не добавляет новых возможностей, так как условие X IN (A, B, C) полностью эквивалентно условию (X=A) OR (X=B) OR (X=C).


Пример на соответствие шаблону: вывести информацию о всех сотрудни- ках, фамилии которых начинаются на букву К.

SELECT lname, address, tel_no FROM Staff


WHERE lname LIKE 'K%';

В Oracle символ '%' замещает произвольную последовательность симво- лов, а '_' − одиночный символ. Строки-шаблоны так же, как и обыкновенные


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

TO_CHAR (поле, 'шаблон')


где шаблон – это строка, содержащая до 40 параметров.


В табл. 1 приведены наиболее часто используемые параметры для работы с полями типа дата/время.
Таблица 1
Основные параметры поля дата/время


Обозначение

Значение

MM

Номер месяца (1–12)

MON

Сокращенное название месяца

MONTH

Полное название месяца

DD

День месяца (1–31)

DY

Сокращенное название дня

DAY

Полное название дня (не более 9 символов)

DY

Сокращенное название дня недели

WW

Неделя года (1–52)

W

Неделя месяца

Q

Квартал(1–4)

YYYY

Год, в виде четырех цифр

YYY|YY|Y

3, 2 или 1 последние цифры года

YEAR

Произношение года

AM (или PM)

Индикатор меридиана (до или после полудня)

HH

Час дня (1–12)

HH24

Час суток (0–23)

MI

Минуты (0–59)

SS

Секунды (0–59)

Также при работе с датами используется функция EXTRACT для извле- чения года, месяца или дня. Например:


SELECT EXTRACT (YEAR FROM sysdate) FROM dual; SELECT EXTRACT (MONTH FROM sysdate) FROM dual; SELECT EXTRACT (DAY FROM sysdate) FROM dual;

Можно изменить заданный по умолчанию формат DATE в Oracle с 'DD- MON-YY' на какой-либо другой, используя следующую команду в SQL*Plus:


ALTER SESSION SET NLS_DATE_FORMAT = 'новый_формат';

Изменение действует только для текущего сеанса SQL*Plus. Параметрические запросы. В SQL*Plus можно выделять именованную об-


ласть памяти для хранения некоторой информации. Такая область используется внутри программ PL/SQL и SQL-операторов, однако находится вне программных блоков, поэтому можно по очереди выделять ее разным програм- мным блокам и после выполнения каждого из них выводить ее содержимое. Эта именованная область памяти называется переменной привязки (подстановки) ‒ bind variable. При использовании переменной привязки в запросах ее имени пред- шествует знак «&» (в iSQLPlus OracleXE11g используется символ двоеточия «:»).

SELECT * FROM &table_name WHERE bno=&v_bno;


Также практикуется использование двойного знака «&&» для переменной подстановки. Значение введенной после сдвоенного знака «&» переменной подстановки запоминается в SQL*Plus, и в следующий раз при обращении к этой переменной подстановки ее значение вводится автоматически.


Многотабличные запросы с внутренним соединением таблиц. Если необ- ходимо получить информацию более чем из одной таблицы, то можно либо при- менить подзапрос, либо выполнить соединение таблиц. Для выполнения соеди- нения достаточно в предложении FROM указать имена соединяемых таблиц, а в предложении WHERE – условие соответствия столбцов соединения. Например, составить список всех сотрудников, работающих в минских отделениях.

SELECT fname, lname, position, S.tel_no FROM Branch B, Staff S


WHERE B.bno=S.bno and city = 'Минск';

При выполнении запроса СУБД сначала просматривает столбец city с це- лью фильтрации строк со значениями, отличными от значения «Минск», далее для отфильтрованных строк таблицы находятся значения столбца bno, опреде- ляющие минские отделения. После этого просматривается таблица Staff и вы- являются строки со значениями в столбце bno, соответствующими идентифика- тору первого минского офиса. В найденных строках оставляются значения столбцов, указанных после ключевого слова SELECT. Далее эта же таблица просматривается для выявления строк, соответствующих второму минскому офису, и опять заново до последнего найденного соответствия. В итоге форми- руется таблица результатов с запрошенной информацией. Кроме того, необхо- димо помнить, что в многотабличных запросах велик риск появления дублика- тов строк, избавиться от которых помогает предикат DISTINCT. В рассмотрен- ном примере DISTINCT не используется, так как предполагается, что сотруд- ники не могут работать одновременно в нескольких отделениях и их телефоны уникальны, иначе предикат был бы необходим.


Отметим некоторые особенности многотабличных запросов. Как видно из примера, в многотабличном запросе часто используются полные имена столб- цов, при этом в предложении FROM через пробел могут указываться псевдо- нимы таблиц, чтобы упростить обращение к столбцам по полному имени, а также обеспечить однозначность ссылок на столбцы. Кроме этого, особый смысл может иметь выбор всех столбцов (SELECT *), например, в Oracle под- держивается следующий синтаксис:

SELECT s.*, city FROM Staff s, Branch b WHERE b.bno=s.bno;


Помимо соединения двух таблиц SQL допускает также соединение трех и более таблиц. Ограничений по количеству соединяемых таблиц ни стандарт, ни разработчики СУБД не предусматривают, однако следует иметь в виду, что при их увеличении в запросе снижается его «читабельность» и скорость выполне- ния в силу значительного увеличения затрат ресурсов и машинного времени при обработке. Для написания запросов с внутренним соединением можно ис- пользовать и стандартный синтаксис операторов внутреннего соединения: JOIN ON, JOIN USING, NATURAL JOIN.


Кроме внутренних соединений используются внешние, например, можно вывести список всех отделений и количество сотрудников в них, включая отде- ления, где пока никто на работает.

SELECT b.bno, count(s.bno) FROM Branch b, Staff s WHERE b.bno=s.bno(+) GROUP BY b.bno


ORDER BY 2;

Для запросов с внешним соединением также можно использовать стан- дартный синтаксис операторов внешнего соединения: FULL JOIN, LEFT JOIN, RIGHT JOIN.


Итоговые запросы. Результирующую таблицу итогового запроса можно рассматривать как некий отчет. Для получения подобных отчетов в запросе на получение итоговой информации требуется указывать предложение GROUP BY и возможное HAVING для отбора групп. Запрос, включающий в себя пред- ложение GROUP BY, называется запросом с группировкой, поскольку он объ- единяет строки исходных таблиц в группы и для каждой группы строк генери- рует одну строку в таблице результатов запроса.
Ограничением при выполнении итоговых запросов является то, что здесь в предложении SELECT могут употребляться лишь столбцы группировки (т. е. те, которые указываются в предложении GROUP BY), строковые константы и статистические функции. Таких функций в SQL пять:
− SUM (имя_столбца) – для вычисления суммы всех значений столбца- аргумента;
− AVG (имя_столбца) – для вычисления среднего значения столбца;
− MIN (имя_столбца) – определяет минимальное значение столбца;
− MAX (имя_столбца) – определяет максимальное значение столбца;
− COUNT (имя_столбца) – подсчитывает число всех определенных зна- чений столбца;
− COUNT (*) – подсчитывает число строк в запросе.
Определить, сколько в среднем получают сотрудники в зависимости от занимаемой ими должности и вывести отсортированный список:

SELECT position, AVG(salary) FROM Staff


GROUP BY position ORDER BY 2;

Условие отбора групп (предложение HAVING). Точно так же, как пред- ложение WHERE используется для отбора отдельных строк, участвующих в за- просе, предложение HAVING можно применить для отбора групп строк. Его формат соответствует формату предложения WHERE, т. е. состоит из ключево- го слова HAVING, за которым следует условие отбора. Рассмотрим пример: подсчитать количество сотрудников, работающих в каждом из офисов, исклю- чив офисы, в которых работает менее двух человек:


SELECT bno, COUNT(sno) FROM Staff


GROUP BY bno
HAVING COUNT(sno) > 2;

Ограничения на условия отбора групп. Предложение HAVING использу- ется для того, чтобы включать и исключать группы строк из результатов запро- са, поэтому используемое в нем условие отбора применяется не к отдельным строкам, а к группе в целом, т. е. используется только совместно с GROUP BY. Это значит, что в условие отбора может входить:



  • константа;

  • статистическая функция, возвращающая одно значение для всех строк, входящих в группу;

  • столбец группировки, который по определению имеет одно и то же зна- чение во всех строках группы;

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

содержащийся в предложении WHERE или HAVING другой инструкции SQL.
Механизм подчиненных запросов позволяет использовать результаты од- ного запроса в качестве составной части другого.
По типу возвращаемых значений существует три типа подзапросов:

  1. скалярный – возвращает единственное значение;

  2. строковый – возвращает значения нескольких столбцов таблицы, но в одной строке;

  3. табличный – возвращает значения одного и более столбцов таблицы, размещенные более чем в одной строке.

В SQL используются следующие условия отбора в подчиненном запросе:

  1. Сравнение с результатом подчиненного запроса. Значение выражения сравнивается с одним значением, которое возвращается подчиненным запросом.

  2. Проверка на принадлежность результатам подчиненного запроса. Значе- ние выражения проверяется на равенство одному из множества значений, кото- рые возвращаются подчиненным запросом. Эта проверка напоминает простую проверку на членство во множестве (IN).

  3. Проверка на существование. Проверяется наличие строк в таблице ре- зультатов подчиненного запроса (EXISTS/NOT EXISTS).

  4. Многократное сравнение. Значение выражения сравнивается с каждым из множества значений, которые возвращаются подчиненным запросом (ANY/ALL).

При использовании подзапросов необходимо учитывать ряд особенностей:
− таблица результатов подчиненного запроса обычно состоит из одного столбца;
− в подчиненный запрос не может включаться предложение ORDER BY;
− подчиненный запрос не может быть запросом на объединение несколь- ких различных инструкций SELECT (т. е. нельзя использовать UNION);
− имена столбцов в подчиненном запросе могут являться ссылками на столбцы таблиц главного запроса.
Рассмотрим несколько примеров. Рассмотренный ранее запрос (список всех сотрудников, работающих в минских отделениях) можно выполнить с по- мощью подзапроса:

SELECT fname, lname, position, tel_no FROM Staff


WHERE bno IN (SELECT bno FROM Branch WHERE city= 'Минск');

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


SELECT fname, lname FROM Staff


WHERE NOT EXISTS (SELECT * FROM Property_for_rent
WHERE Property_for_rent.bno = Staff.bno);

Здесь в подзапросе используется ссылка на столбец Staff.bno, который находится во внешнем запросе, поэтому такой прием называется внешней ссыл-


кой в подзапросе, а такие запросы коррелированными, так как они выполняются по особому алгоритму: для каждой строки внешнего запроса выполняется подза- прос, в то время как во всех остальных случаях подзапрос выполняется один раз.
С помощью многократного сравнения можно, например, определить от- деления, количество сотрудников которых превышает количество сотрудников любого отделения Минска:
SELECT s.bno, count(s.bno) FROM Staff s, Branch b
WHERE s.bno = b.bno AND city!= 'Минск' GROUP BY s.bno
HAVING count(s.bno)>= ALL (SELECT count(s1.bno) FROM staff s1, branch b1
WHERE s1.bno = b1.bno AND city= 'Минск' GROUP BY s1.bno);
Обновление строк таблицы реализуется с помощью оператора UPDATE, форма записи которого приведена ниже. При отсутствии условия модифициру- ются все строки таблицы для указанного списка столбцов.

UPDATE имя_таблицы


SET {(имя_столбца1 [, имя_столбца2] ...) = (подзапрос) | имя_столбца1=значение1, имя_столбца2={значение2 | (подзапрос)}} [WHERE условие];




Достарыңызбен бөлісу:
1   2   3   4   5   6   7   8   9   ...   56




©emirsaba.org 2024
әкімшілігінің қараңыз

    Басты бет