МІНІСТЕРСТВО ОСВІТИ, НАУКИ, МОЛОДІ ТА СПОРТУ УКРАЇНИ
НАЦІОНАЛЬНИЙ ТЕХНІЧНИЙ УНІВЕРСИТЕТ УКРАЇНИ
“КИЇВСЬКИЙ ПОЛІТЕХНІЧНИЙ ІНСТИТУТ”
РАБОТА З СУБД PostgreSQL
МЕТОДИЧНІ ВКАЗІВКИ
к вивченню та лабораторним роботам з дисципліни
"Обчислювальна техніка та програмування"
для студентів напряму
6.051001 - Метрологія та інформаційно-вимірювальні технології
Київ НТУУ "КПІ" 2011
Робота з СУБД PostgreSQL. Методичні вказівки до курсового проектування з дисципліни "Організація баз даних" для студентів напрямку 6.051001 - Метрологія та інформаційно-вимірювальні технології. / Укл. В.І. Павловський, Д.В. Победа, М.В. Харченко. - Чернігів: ЧДТУ, 2010.-50 с. Рос. мовою.
Гриф надано Методичною радою НТУУ "КПІ"
(Протокол № … від ….)
Навчальне видання
ВВЕДЕНИЕ
PostgreSQL [12] имеет некоторые неприятные особенности!:
1. PostgreSQL чувствителен к регистру. Поэтому имена БД, таблиц, полей и значений полей в SQL следует записывать строго так, как они занесены в БД.
2. Имена БД, таблиц и полей следует заключать в двойные кавычки "…..", а строковые значения в одинарные кавычки '…'.
3. При программном доступе к БД в запросах – строковых переменных или константах, следует предусмотреть особенность представления символа ", например
String query = "SELECT * FROM \"BOOKS\" WHERE \"BookID\" < 5
AND \"BName\" = '10 минут на урок Windows 98'";
СОЗДАНИЕ НОВОЙ БД
Запустите менеджер БД pgAdmin III с паролем, например qwerty. Имя пользователя postgres автоматически создавалось при развертывании PostgreSQL. Далее выполните соединение с сервером БД. Возможны 2-ва варианта:
ü с помощью меню Сервис (рисунок 2.1);
ü путем двойного нажатия мышью по имени сервера в окне Браузер объектов.
И в том, и в другом случае появится окно ввода пароля (рисунок 2.2).
Рисунок 2.1 – Меню Сервис
Рисунок 2.2 – Окно вода пароля
Внешние ключи и создание связей между таблицами
После того, как все таблицы БД созданы, необходимо связать их между собой. Для задания связей между таблицами используются первичные и внешние ключи. Для БД «Деканат ВУЗа» связь между таблицами Students и Groups задается с помощью первичного ключа таблицы Groups и внешнего ключа таблицы Students. Первичный ключ таблицы Groups к этому времени уже должен быть создан.
Ограничение внешнего ключа говорит, что значение в колонке (или группе колонок) одной таблицы должно полностью совпадать со значениями, которые существуют в некоторых строках другой таблицы. Для примера: у таблицы Students значения в колонке GrNo должны совпадать с соответствующими значениями в колонке GrNo таблицы Groups.
Чтобы создать внешний ключ нужно выбрать в окне Браузер объектов требуемую таблицу, например Students, активизировать ее контекстное меню и в нем выбрать пункт Свойства (рисунок 2.20).
Рисунок 2.20 – Контекстное меню объекта Таблица
После этого откроется окно характеристик выбранной таблицы (рисунок 2.21).
Рисунок 2.21 – Закладка Ограничения окна свойств таблицы
В нем нужно открыть закладку Ограничения, выбрать в выпадающем списке тип ограничения вторичный (внешний) ключ и нажать кнопку Добавить. В результате выполнения этих действий появится окно Новый внешний ключ (рисунок 2.22).
Рисунок 2.22 – Окно Новый внешний ключ. Закладка Свойства
Чтобы создать новый внешний ключ необходимо выполнить следующие действия:
− на закладке Свойства задать имя внешнего ключа, например, GrNoFK (FK от англ. Foreign Key – внешний ключ);
− выбрать из списка таблицу, на которую ссылается внешний ключ (свойство Ссылается);
− отметить свойство Совпадение полное, которое говорит о том, что значения внешнего ключа должны полностью совпадать со значениями первичного ключа;
− на закладке Колонки (рисунок 2.23) выбрать колонку исходной таблицы (Локальная колонка), которая определяет внешний ключ (для приведенного примера это колонка GrNo таблицы Students), и колонку, на которую ссылается внешний ключ (колонка GrNo таблицы Groups);
Рисунок 2.23 – Закладка Колонки окна Новый внешний ключ
− на закладке Действие (рисунок 2.24) выбрать действие, которое будет выполняться при изменении или удалении значения первичного ключа.
Рисунок 2.24 – Закладка Действие окна Новый внешний ключ
Возможные варианты действий:
а) NO ACTION – никаких дополнительных действий и ограничений;
б) RESTRICT – удаление/изменение значения первичного ключа запрещается, если на него ссылается какой-либо внешний ключ. Это действие установлено по умолчанию;
в) CASCADE – удаление строки в родительской таблице приводит к удалению всех связанных с ней строк дочерней таблицы (например, если удалить запись о группе в таблице Groups, то автоматически будут удалены все записи о ее студентах в таблице Students); изменение значения первичного ключа родительской таблицы приводит к соответствующему изменению значений внешних ключей дочерней таблицы (например, если изменить какое-либо значение в колонке GrNo таблицы Groups, то автоматически будут изменены связанные с ним значения колонки GrNo в таблице Students);
г) SET NULL – удаление/изменение первичного ключа родительской таблицы приводит к установке в значение NULL всех внешних ключей дочерней таблицы, которые ссылаются на удаленное/измененное значения первичного ключа родительской таблицы (например, если удалить/изменить значение в колонке GrNo таблицы Groups, то связанные с ним значения колонки GrNo в таблице Students автоматически будут установлены в NULL);
д) SET DEFAULT – удаление/изменение первичного ключа родительской таблицы приводит к установке в значение по умолчанию всех внешних ключей дочерней таблицы, которые ссылаются на удаленное/измененное значения первичного ключа родительской таблицы.
Чтобы увидеть результат изменений колонки, таблицы или БД необходимо выделить соответствующий объект в окне Браузер объектов и выполнить его обновление (рисунок 2.25).
Рисунок 2.25 – Обновление объекта БД
ВВОД ДАННЫХ В ТАБЛИЦУ
Для ввода данных в определенную таблицу необходимо выбрать ее в окне Браузер объектов и нажать соответствующую кнопку (рисунок 3.1)
Рисунок 3.1 – Выбор таблицы для редактирования
После этого откроется окно редактирования данных выбранной таблицы (рисунок 3.2).
Рисунок 3.2 – Окно редактирования данных таблицы
Резервное копирование и восстановление БД
Создание SQL-дампа БД
Идея создания SQL-дампа БД заключается в создании текстового файла с командами SQL INSERT(см. подраздел 9.3), с помощью которых на сервере можно воссоздать БД в реляционной СУБД любого производителя, что обеспечивает перенос БД между разными СУБД.
Примечание 1. В принципе pgAdmin III предусматривает создание SQL‑дампа, но на практике возникают некоторые ошибки при его восстановлении. С другой стороны менеджер PgMaestro хорошо выполняет работу по созданию и восстановлению SQL‑дампа, который восстанавливается и pgAdmin III.
Восстановление SQL-дампа средствами pgAdmin III
Вызвать окно SQL–запроса.
Рисунок 9.8 – Вызов окна SQL–запроса
Выполнить поиск файла с SQL-дампом
Рисунок 9.9 – Выбор файла SQL-дампа
Открыть файл с SQL-дампом
Рисунок 9.10 – Открытие файла SQL-дампа
Рисунок 9.11 – Восстановление SQL-дампа
ОСОБЕННОСТИ ВЗАИМОДЕЙСТВИЯ СУБД Access И PostgreSQL
Ниже приведено описание удобного способа копирования таблиц Access в таблицы PostgreSQL, а также возможности подключения таблиц PostgreSQL к таблицам Access.
Предварительное условие: Драйвер PostgreSQL ODBC должен быть инсталлирован.
Характеристики PostgreSQL
http://www.scribd.com/doc/4951927/PostgreSQL-7
http://postgresql.ru.net/docs/overview.html#langs
Размер базы данных
Максимальный размер БД | Неограничен |
Максимальный размер таблицы | 32 TB |
Максимальный размер записи (строки) в таблице | 1.6 TB |
Максимальный размер поля в записи (строке) | 1 GB |
Максимальное количество записей (строк) в таблице | не ограничено |
Максимальное количество полей (колонок) в таблице | 250 - 1600 в зависимости от типа данных в колонке |
Максимальное количество индексов на таблицу | не ограничено |
АДМИНИСТРИРОВАНИЕ PostgreSQL
По умолчанию PostgreSQL автоматически запускается вместе с запуском операционной системы и потребляет значительный ресурс оперативной памяти. Если PostgreSQL используется редко, то имеет смысл дезактивировать эту службу или сервис и активировать ее по мере необходимости.
В предыдущих версиях PostgreSQL это можно было сделать через меню Пуск.
Рисунок 12.1 – Запуск/останов сервиса PostgreSQL в предыдущих версиях
В 9 версии PostgreSQL эту службу можно активировать и дезактивировать, используя Диспетчер задач.
Рисунок 12.2 – Останов службы PostgreSQL в версии 9
По различным вопросам администрирования и, в частности, управления пользователями см. [11].
ЛИТЕРАТУРА
1. http://postgresql.ru.net/manual/tutorial-createdb.html
2. http://postgresql.ru.net/postgis/ch04_5.html
3. http://postgresql.ru.net/docs/overview.html#index
4. http://www.scribd.com/doc/4846380/-GIN-GiST-PostgreSQL
5. http://postgresql.ru.net/manual/gist.html
6. http://postgresql.ru.net/manual/gin.html
7. http://postgresql.ru.net/manual/sql-createfunction.html
8. http://www.postgresql.org/docs/8.3/static/plpgsql.html
9. http://www.postgresql.org/docs/8.1/static/sql-grant.html
10. http://www.sai.msu.su/~megera/postgres/talks/gist_tutorial.html
11. http://www.opennet.ru/links/info/685.shtml
12. http://www.citforum.ru/database/postgres/what_is/
13. http://www.nestor.minsk.by/kg/2003/48/kg34802.html
14. http://ru.wikipedia.org/wiki/PostgreSQL#.D0.A4.D1.83.D0.BD.D0.BA.D1.86.D0.B8.D0.B8
15.