лабораторный практикум. Русакова М.С. Лабораторный практикум. Практикум по базам данных самара 2015 министерство образования и науки российской федерации
 Скачать 0.72 Mb.
|
|
Часть 2. Oracle Лабораторная работа №1 Подключение к Oracle через SQL Developer. Создание и заполнение таблиц. Выборка данных Подключение к Oracle через SQL Developer Запустите SQL Developer через Пуск -> Программы -> Средства разработки -> SQL Developer (путь к программе может отличаться от приведенного). При первом запуске программа должна запросить у вас путь к комплекту JDK, следует прописать JDK актуальной установленной версии. После чего SQL Developer должен успешно запуститься. Теперь нам надо создать новое подключение к СУБД. На вкладке Connections щелкните по зеленому плюсу. Появится окно настройки нового подключения. Надо задать следующие параметры: а) Connection Name – любое имя соединения, которое вам нравится. Но лучше называть информативно, можно использовать для имени коннекта ваш логин в университетскую сеть, к примеру; б) Username 1 – логин, который вам выдали для подключения к СУБД Oracle (это не логин в университетскую сеть!!!!) в) Password – пароль, который вам выдали для подключения к СУБД Oracle (это не пароль от вашей учетной записи в университетской сети!). Отметьте галочкой пункт Save Password (чтобы не вводить пароль каждый раз при обращении к подключению) Hostname – укажите callisto.uni-smr.ac.ru (или 195.209.66.41) Port задайте 1521. SID задайте ssu9. Проверьте соединение (кнопка Test). Если все сделано верно, то статус соединения будет success, после чего можно нажимать на Connect. Теперь в списке Connections появится ваше новое соединение, развернув которое, можно увидеть все объекты (таблицы, индексы, хранимые процедуры) вашей БД. Пока что они все пусты. Для создания объектов и написания запросов нам понадобится в дальнейшем окно SQL-редактора, в котором можно писать запросы и запускать их на выполнение. Создание таблиц можно проводить в окне редактора SQL-запросов, а также можно запустить мастер создания таблиц через контекстное меню по объектам «Таблицы» пункт «Create Table». Создавать таблицы и писать/выполнять другие запросы можно и в SQL*PLUS, который можно запустить через Пуск -> Программы -> Средства разработки -> Oracle-OraClient 10g_home1 -> Application Development -> SQL*Plus. Остановимся пока подробнее на проектировании базы и командах SQL для создания и заполнения таблиц. 1 Примечание: если вы будете устанавливать Oracle дома и подключаться дома к нему из SQL Developer, то username будет system, а пароль будет таким, какой вы установите при инсталляции СУБД Oracle (крайне важно его не забыть…) 20 Будем создавать базу данных «Аренда недвижимости». В этой базе, очевидно, будет несколько таблиц, взаимосвязанных между собой. Нам необходимо хранить информацию об арендаторах и владельцах недвижимости, о самой недвижимости и цене на нее, а также о заключенных договорах. Знания терминологии для создания таблиц недостаточно. Дело в том, что среды разработки редко поддерживают русскую кодировку в обозначениях. Поэтому введем англоязычные обозначения таблиц базы данных «Аренда». Таблица 9. Обозначения отношений Русскоязычные обозначения Англоязычные обозначения Арендатор (№Ар, Ар, АдрАр) Tenant (NTn, Tn, AdT) Владелец (№Вл, Вл, АдрВл) Owner (NOn, Ow, AdO) Договор (№Дог, №Ар, №Вл, АдрНд, Дата) Lease (NLease, NTn, NOn, AdR, LDate) Недвижимость (АдрНд, Тип) Realty (AdR, Type) Плата (Тип, Пл) Rent (Type, Rn) Здесь для арендатора и владельца будем хранить их уникальные номера (NTn и Non, числовой тип), фамилии (Tn и Ow, строковый тип) и адреса проживания (AdT, AdO, строковый тип). Номера сделаем первичными ключами. Для недвижимости примем такое соглашение: стоимость аренды зависит только от вида недвижимости. Тогда нам для характеристики недвижимости достаточно знать ее адрес (AdR, строковый тип) и вид (Type, строковый тип), причем адреса уникальны и являются первичным ключом. Тогда плата за недвижимость может быть указана в таблице со следующими атрибутами: вид недвижимости (Type, который для таблицы Rent будет являться первичным ключом) и собственно стоимость (Rn, числовой, либо денежный тип). Наконец, для заключения договора необходимо знать, кто с кем договаривается (NTn, NOn), на какую недвижимость составляется договор (AdR), когда этот договор заключен (LDate, тип дата), и под каким номером он фигурирует в нашей базе (NLease – первичный ключ, числовой тип). Таблица «Плата» является родительской для «Недвижимости», и ее следует создавать первой. «Арендатор», «Владелец» и «Недвижимость» являются, в свою очередь, родительскими таблицами для «Договора». Поэтому они создаются и заполняются во вторую очередь, и самой последней создается и заполняется таблица «Договор» (это логично – мы не можем заключать договор с неизвестным владельцем, или неизвестным арендатором, либо на неизвестный объект недвижимости). Таблицы «Недвижимость» и «Плата» связаны между собой через поле Type (вид недвижимости). «Недвижимость» и «Договор» связаны через поле «Адрес недвижимости» (AdR), «Арендатор» и «Договор» – через номер арендатора (NTn), «Владелец» и «Договор» – через номер владельца (NOn). Все виды связей – один-ко-многим (подумайте, почему). Разумеется, разрабатываемый нами пример не претендует на истину в последней инстанции, конечно же, в договоре указывается на самом деле гораздо больше параметров, а стоимость недвижимости определяется не только ее типом, но и многими другими характеристиками. Кроме того, вводить адрес недвижимости в качестве внешнего ключа в «Договоре» (и в качестве первичного в «Недвижимости») – не самая хорошая практика, здесь можно говорить и об избыточности, и о необходимости дальнейшей нормализации отношений (кстати, в качестве тренировки – попробуйте определить, в каких нормальных формах находятся отношения в разрабатываемой БД). Теперь посмотрим, как можно реализовать таблицу Owner (NOn, Ow, AdO), в которой хранятся данные о владельцах собственности, с нуля. 21 Создание таблиц в Oracle производится командой create table. Например, чтобы создать таблицу «Владелец», структура которой приведена выше, необходимо выполнить следующие команды: CREATE TABLE Owner (NOn NUMBER (5, 0) PRIMARY KEY, Ow VARCHAR2(25) NOT NULL, AdO VARCHAR2(50) NOT NULL) / Рис. 2 Диаграмма базы данных «Аренда» Примечание. В зависимости от версии Oracle, с которой вам приходится работать, в таблицу можно внести следующие типы данных: NUMBER(p, s) Числовое значение, максимальное количество цифр равно p, а количество десятичных знаков – s VARCHAR(s) Символьная строка переменной длины, максимальный размер равен s. Сейчас используют varchar2(s) DATE Значение даты и времени между 1 января 4712г. до н.э. и 31 декабря 4712г. н. э. CHAR(s) Символьное значение постоянной длины s Чтобы заполнить таблицу данными, можно записать, например, вот такой код: 22 INSERT INTO OWNER VALUES (1,’ИВАНОВ А.В.’, ‘КИРОВА, 11’) / INSERT INTO OWNER VALUES (2,'ПЕТРОВА А.Н.','САМАРСКАЯ, 17') / COMMIT / Примечание. Чтобы сохранить внесенные изменения, воспользуйтесь командой COMMIT. Связи между таблицами устанавливаются уже при создании самих таблиц. Для этого необходимо указать внешний ключ и установить ссылку на родительскую таблицу. Например, когда будете создавать таблицу «Договор», не забудьте проставить связи: CREATE TABLE Lease (NLease NUMBER(5,0) PRIMARY KEY, NOn NUMBER(5,0), NTn NUMBER(5,0), AdR VARCHAR(25), LDate DATE, FOREIGN KEY (NOn) REFERENCES Owner, FOREIGN KEY (NTn) REFERENCES Tenant, FOREIGN KEY (AdR) REFERENCES Realty) / SQL Developer предоставляет возможность создания базы без специальных знаний языка запросов. Создать таблицы можно, запустив мастера создания таблиц через контекстное меню по объектам «Таблицы», пункт «Create Table». В мастере задаете имя таблицы, имена полей. Тип, длину (если предусмотрено типом), разрешено ли нулевое значение (для первичных ключей не разрешается) для поля – выбираете в диалоговом окне мастера. Для проставления первичных ключей и связей надо в этом же диалоговом окне перейти на вкладку Constraints и в имени ограничения написать какой-либо уникальный для всей базы идентификатор (если он не сгенерирован системно), в типе – выбрать Primary key. В нижней табличке Constraint Definition выбираете имя поля, которое должно быть первичным ключом. Связи проставляются на той же вкладке. Назовите ограничение уникальным для базы именем (на рис. 2 ограничения названы cnt-07, cnt-08 и т. п.), тип – Foreign key (внешний ключ), Ref Schema – выберите свой коннект, Ref Table – на какую таблицу должен ссылаться. В нижней таблице Constraint Definition выберите имена полей, по которым идет связь. На вкладке Preview SQL можно посмотреть, как будет выглядеть SQL-запрос. После того как вы создадите и заполните таблицы, потренируйтесь и вспомните простые команды языка и выполните следующие задания. 23 Задания Напишите запросы: Вывести информацию об арендаторах и владельцах 3-комнатных квартир. Для столбцов создать псевдонимы. Упорядочить в обратном алфавитном порядке по фамилиям арендаторов. Вывести информацию об адресах домов (имеется в виду конкретный тип недвижимости) и дате начала их аренды. Упорядочить по дате, начиная с самой поздней аренды. Вывести информацию о фамилиях и адресах владельцев, которые сдают 1-комнатные квартиры. Вывести информацию о владельцах, их адреса, арендаторов, их адреса, если они заключили договор ранее 01.01.05 и позже 01.01.04. Упорядочить информацию по алфавиту по адресам владельцев, создать псевдонимы столбцов. Вывести информацию о владельцах, их адреса, арендаторов, их адреса, если они заключили договор на 2-комнатную квартиру ранее 01.01.05 и позже 01.01.04. Упорядочить информацию по алфавиту по адресам владельцев, создать псевдонимы столбцов. 24 Лабораторная работа №2 Однострочные и групповые функции Однострочные функции В Oracle предусмотрен ряд встроенных функций (так называемые однострочные функции), при помощи которых можно управлять отображением данных и их преобразованием. Однострочные функции в Oracle можно подразделить на: символьные; числовые; функции даты; функции преобразования. Синтаксис: FUNCTION_NAME (COLUMN|EXPRESSION,[ARG1,ARG2,…]) Таблица 10. Символьные функции Функция Назначение LOWER(столбец|выражение) Преобразование алфавитных символов в нижний регистр UPPER(столбец|выражение) Преобразование алфавитных символов в верхний регистр INITCAP(столбец|выражение) Преобразование начальных символов в верхний регистр, остальные преобразуются в нижний регистр CONCAT(столбец1|выражение1, столбец2|выражение2) Конкатенация первого символьного значения со вторым. Эквивалентно оператору конкатенации (||) SUBSTR(столбец|выражение, m[,n]) Возвращает n символов из символьного значения, начиная с позиции m. Если число m отрицательно, то отсчет начинается от конца символьного значения LENGTH(столбец|выражение) Возвращает количество символов в значении NVL(столбец|выражение1, столбец|выражение2) Возвращает второе значение, если первое является неопределенным Таблица 11. Числовые функции Функция Назначение ROUND(столбец|выражение, n) Округляет столбец, выражение или значение до n десятичных знаков. Если n опущено, то до целого. Если n отрицательно, округляется целая часть числа TRUNC(столбец|выражение, n) Усекает столбец, выражение или значение до n десятичных знаков. Если n опущено, то до целого. Если n отрицательно, обнуляются разряды целой части числа MOD(m,n) Возвращает остаток от деления m на n Даты в системе Oracle хранятся во внутреннем числовом формате, где представлены столетие, год, месяц, день, часы, минуты, секунды. SYSDATE – функция даты, возвращает текущие дату и время. Обычно выборка SYSDATE производится из фиктивной таблицы DUAL. 25 Таблица 12. Функции даты Функция Назначение MONTHS_BETWEEN(дата1, дата2) Определяет количество месяцев между датами 1 и 2 ADD_MONTHS(дата,n) К дате прибавляет n календарных месяцев. N может быть отрицательным, но должно быть целым NEXT_DAY(дата,’символ’) Определение даты ближайшего дня недели, заданного «символом» после указанной даты. Символ может задавать порядковый номер, название дня недели LAST_DAY(дата) Определение последнего дня месяца, содержащего заданную дату ROUND(дата[,’fmt’]) При отсутствии аргумента ‘fmt’ округляет до даты на момент полуночи (до целого числа суток) TRUNC(дата[,’fmt’]) Если модель ‘fmt’ не задана, то возвращает первый день месяца, указанного в аргументе «дата». Если fmt = YEAR, то возвращает дату первого дня года, содержащего указанную дату. Арифметические операции с датами: Результатом прибавления числа к дате и вычитания числа из даты является дата. Результатом вычитания одной даты из другой будет количество дней, разделяющее эти даты. Прибавление часов к дате осуществляется путем деления количества часов на 24. Таблица 13. Функции преобразования Функция Назначение TO_CHAR(число|дата,[‘fmt’]) Преобразует число или дату в строку с заданной моделью формата TO_NUMBER(симв) Преобразует строку, содержащую цифры, в число TO_DATE(симв,[‘fmt’]) Преобразует стоку символов с датой в дату с заданным форматом 26 Таблица 14. Функция TO_CHAR с датами Элемент Описание YYY или YY или Y Последние 3, 2 или 1 цифра года Y, YYY Год с запятой в указанной позиции IYYY, IYY, IY, I 4,3,2 или 1 цифра года в соответствии со стандартом ISO SYEAR или YEAR Год словами. S означает, что даты до н.э. получают префикс «- » BC или AD Индикатор «до н.э./н.э.» B.C. или A.D. Индикатор «до н.э./н.э.» с точками Q Квартал MM Месяц в виде двузначного числа MONTH Название месяца, дополненное конечными пробелами до 9 символов MON Трехбуквенное сокращенное название месяца RM Номер месяца римскими цифрами WW или W Неделя года или месяца DDD или DD или D День года, месяца или недели DAY Название дня, дополненное конечными пробелами до 9 символов DY Трехбуквенное название дня Таблица 15. Форматы времени Элемент Описание AM или PM Индикатор «до полудня / после полудня» A.M. или P.M. Индикатор «до полудня / после полудня» с точками HH или HH12 или HH24 Время суток, час в 12-часовом или в 24-часовом диапазоне MI Минуты (0-59) SS Секунды (0-59) SSSSS Количество секунд после полуночи (0-86399) Таблица 16. Другие форматы Элемент Описание /., Знаки пунктуации включаются в результат «из множества» Строка, заключенная в кавычки, включается в результат Таблица 17. Суффиксы, влияющие на формат вывода чисел Суффикс Описание TH Порядковый номер (например, DDTH для вывода в формате «4th») SP Вывод числа словами SPTH или THSP Вывод порядкового числительного словами Примечание. Это еще далеко не полный набор однострочных функций. Например, постарайтесь самостоятельно найти, чем отличается формат даты RR от формата YY. Также на самостоятельное изучение остается функция TO_CHAR с числами. 27 Групповые функции В отличие от однострочных функций групповые функции работают над множествами строк и выдают один результат на группу. Групповые функции используются и в списке SELECT, и в предложении HAVING. Групповые функции: AVG; COUNT; МАХ; MIN; STDDEV; SUM; VARIANCE. Предложения GROUP BY и HAVING в команде SELECT По умолчанию все строки таблицы рассматриваются как одна группа. Для разбиения таблицы на меньшие группы строк используется предложение GROUP BY команды SELECT. Кроме того, для отбора возвращаемых групп используется предложение HAVING. Синтаксис запроса, содержащего групповую функцию: SELECT столбец, групповая функция FROM таблица [WHERE условие] [GROUP BY выражение_ группирования] [HAVING условие_группы] [ORDER / BY столбец] Здесь: выражение_группирования задает столбец, на основе значения которого группируются строки; условие_группы включает в выходной результат только те группы, для которых заданное условие будет истинно (TRUE). Каждая функция допускает один аргумент. В следующей таблице показаны возможные варианты синтаксиса. 28 Таблица 18. Варианты синтаксиса групповых функций Функция Описание AVG(DISTINCT|ALL|n) Среднее значение n без учета неопределенных значений COUNT(DISTINCT|ALL| выражение|*) Количество строк только с определенными результатами вычисления выражения. По "*" подсчитываются все строки, включая повторяющиеся и строки с неопределенными значениями MAX(DISTINCT|ALL| выражение) Максимальное значения выражения MIN(DISTINCT|ALL| выражение) Минимальное значения выражения STDDEV(DISTINCT|ALL|n) Стандартное отклонение n без учета неопределенных значений SUM(DISTINCT|ALL|n) Сумма значений n без учета неопределенных значений VARIANCE(DISTINCT|ALL|n) Дисперсия n без учета неопределенных значений Указания Если задано слово DISTINCT, функция учитывает лишь неповторяющиеся значения; при наличии слова ALL рассматриваются все значения. Вариант ALL принимается по умолчанию, поэтому задавать его нет необходимости; Если задано выражение, допустимыми типами данных для аргументов являются CHAR, VARCHAR2, NUMBER и DATE; Все групповые функции, кроме COUNT(*), игнорируют неопределенные значения. Для подстановки значения вместо неопределенного используется функция NVL. |