Важно
Перевод - это работа сообщества : ссылка:Вы можете присоединиться. Эта страница в настоящее время переводится |прогресс перевода|.
15.2. Урок: Реализация модели данных
Теперь, когда мы рассмотрели всю теорию, давайте создадим новую базу данных. Эта база данных будет использоваться для наших упражнений в последующих уроках.
Цель урока: Установить необходимое программное обеспечение и использовать его для реализации нашего примера базы данных.
15.2.1. Установить PostgreSQL
Примечание
Пакеты PostgreSQL и инструкции по установке для вашей операционной системы можно найти на сайте https://www.postgresql.org/download/. Обратите внимание, что в документации предполагается, что пользователи работают с QGIS под управлением Ubuntu.
Под Ubuntu:
sudo apt install postgresql-9.1
Вы должны получить сообщение следующего содержания:
[sudo] password for qgis:
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following extra packages will be installed:
postgresql-client-9.1 postgresql-client-common postgresql-common
Suggested packages:
oidentd ident-server postgresql-doc-9.1
The following NEW packages will be installed:
postgresql-9.1 postgresql-client-9.1 postgresql-client-common postgresql-common
0 upgraded, 4 newly installed, 0 to remove and 5 not upgraded.
Need to get 5,012kB of archives.
After this operation, 19.0MB of additional disk space will be used.
Do you want to continue [Y/n]?
Нажмите Y и Enter и дождитесь окончания загрузки и установки.
15.2.2. Справка
PostgreSQL имеет очень хорошую документацию в Интернете <https://www.postgresql.org/docs/9.1/index.html>`_.
15.2.3. Создать пользователя базы данных
Под Ubuntu:
После завершения установки выполните эту команду, чтобы стать пользователем postgres, а затем создайте нового пользователя базы данных:
sudo su - postgres
Введите свой обычный пароль для входа в систему, когда появится запрос (у вас должны быть права sudo).
Теперь в приглашении bash пользователя postgres создайте пользователя базы данных. Убедитесь, что имя пользователя совпадает с вашим именем входа в систему unix: это значительно облегчит вам жизнь, поскольку postgres будет автоматически аутентифицировать вас, когда вы войдете в систему под этим пользователем:
createuser -d -E -i -l -P -r -s qgis
Введите пароль, когда появится запрос. Вы должны использовать пароль, отличный от пароля для входа в систему.
Что означают эти варианты?
-d, --createdb role can create new databases
-E, --encrypted encrypt stored password
-i, --inherit role inherits privileges of roles it is a member of (default)
-l, --login role can login (default)
-P, --pwprompt assign a password to new role
-r, --createrole role can create new roles
-s, --superuser role will be superuser
Теперь вы должны выйти из среды оболочки bash пользователя postgres, набрав:
exit
15.2.4. Подтвердите новую учетную запись
psql -l
Должно получиться что-то вроде этого:
Name | Owner | Encoding | Collation | Ctype |
----------+----------+----------+------------+------------+
postgres | postgres | UTF8 | en_ZA.utf8 | en_ZA.utf8 |
template0 | postgres | UTF8 | en_ZA.utf8 | en_ZA.utf8 |
template1 | postgres | UTF8 | en_ZA.utf8 | en_ZA.utf8 |
(3 rows)
Введите Q, чтобы выйти.
15.2.5. Создать базу данных
Команда createdb используется для создания новой базы данных. Ее следует запускать из приглашения оболочки bash:
createdb address -O qgis
Вы можете проверить существование новой базы данных с помощью этой команды:
psql -l
В результате должно получиться что-то вроде этого:
Name | Owner | Encoding | Collation | Ctype | Access privileges
----------+----------+----------+------------+------------+-----------------------
address | qgis | UTF8 | en_ZA.utf8 | en_ZA.utf8 |
postgres | postgres | UTF8 | en_ZA.utf8 | en_ZA.utf8 |
template0 | postgres | UTF8 | en_ZA.utf8 | en_ZA.utf8 | =c/postgres: postgres=CTc/postgres
template1 | postgres | UTF8 | en_ZA.utf8 | en_ZA.utf8 | =c/postgres: postgres=CTc/postgres
(4 rows)
Введите Q, чтобы выйти.
15.2.6. Запуск сеанса оболочки базы данных
Вы можете подключиться к своей базе данных следующим образом:
psql address
Чтобы выйти из оболочки базы данных psql, введите:
\q
Чтобы получить помощь в использовании оболочки, введите:
\?
Чтобы получить помощь в использовании команд sql, введите:
\help
Чтобы получить справку по конкретной команде, введите (например):
\help create table
См. также Шпаргалку по Psql.
15.2.7. Создание таблиц в SQL
Давайте начнем создавать таблицы! В качестве руководства мы будем использовать нашу ER-диаграмму. Сначала подключитесь к базе данных адресов:
psql address
Затем создайте таблицу улицы:
create table streets (id serial not null primary key, name varchar(50));
серийный и varchar - это типы данных. serial указывает PostgreSQL начать последовательность целых чисел (auto-number) для автоматического заполнения id для каждой новой записи. varchar(50) указывает PostgreSQL создать символьное поле длиной 50 символов.
Вы заметите, что команда заканчивается символом ; - все команды SQL должны завершаться таким образом. Когда вы нажмете Enter, psql сообщит примерно следующее:
NOTICE: CREATE TABLE will create implicit sequence "streets_id_seq"
for serial column "streets.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"streets_pkey" for table "streets"
CREATE TABLE
Это означает, что ваша таблица была создана успешно, с первичным ключом streets_pkey, использующим streets.id.
Примечание: Если вы нажмете return, не введя ;, то получите подсказку, подобную этой: address-#. Это происходит потому, что PG ожидает, что вы введете больше. Введите ;, чтобы выполнить команду.
Чтобы просмотреть схему таблицы, можно сделать следующее:
\d streets
В результате должно получиться что-то вроде этого:
Table "public.streets"
Column | Type | Modifiers
--------+-----------------------+--------------------------------------
id | integer | not null default
| | nextval('streets_id_seq'::regclass)
name | character varying(50) |
Indexes:
"streets_pkey" PRIMARY KEY, btree (id)
Чтобы просмотреть содержимое таблицы, можно сделать следующее:
select * from streets;
В результате должно получиться что-то вроде этого:
id | name
---+------
(0 rows)
Как видите, наш стол в настоящее время пуст.
Попробуйте сами: ★★☆
Используйте подход, показанный выше, чтобы создать таблицу с названием «Люди»:
Добавьте такие поля, как номер телефона, домашний адрес, имя и т. д. (это не все допустимые имена: измените их, чтобы они стали допустимыми). Убедитесь, что в таблице есть столбец ID с тем же типом данных, что и выше.
Ответить
SQL, необходимый для создания правильной таблицы людей, выглядит так:
create table people (id serial not null primary key,
name varchar(50),
house_no int not null,
street_id int not null,
phone_no varchar null );
Схема для таблицы (введите \\\d человека) выглядит следующим образом:
Table "public.people"
Column | Type | Modifiers
-----------+-----------------------+-------------------------------------
id | integer | not null default
| | nextval('people_id_seq'::regclass)
name | character varying(50) |
house_no | integer | not null
street_id | integer | not null
phone_no | character varying |
Indexes:
"people_pkey" PRIMARY KEY, btree (id)
В целях иллюстрации мы намеренно опустили ограничение fkey.
15.2.8. Создание ключей в SQL
Проблема нашего решения заключается в том, что база данных не знает, что между людьми и улицами существует логическая связь. Чтобы выразить эту связь, мы должны определить внешний ключ, который указывает на первичный ключ таблицы Улицы.
Есть два способа сделать это:
Добавьте ключ после создания таблицы
Определите ключ во время создания таблицы
Наша таблица уже создана, поэтому давайте сделаем это первым способом:
alter table people
add constraint people_streets_fk foreign key (street_id) references streets(id);
Это сообщает таблице people, что ее поля street_id должны соответствовать действительной улице id из таблицы streets.
Более привычный способ создать ограничение - сделать это при создании таблицы:
create table people (id serial not null primary key,
name varchar(50),
house_no int not null,
street_id int references streets(id) not null,
phone_no varchar null);
\d people
После добавления ограничений схема нашей таблицы выглядит следующим образом:
Table "public.people"
Column | Type | Modifiers
-----------+-----------------------+---------------------------------
id | integer | not null default
| | nextval('people_id_seq'::regclass)
name | character varying(50) |
house_no | integer | not null
street_id | integer | not null
phone_no | character varying |
Indexes:
"people_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"people_streets_fk" FOREIGN KEY (id) REFERENCES streets(id)
15.2.9. Создание индексов в SQL
Нам нужен молниеносный поиск по именам людей. Чтобы обеспечить это, мы можем создать индекс на столбце имени нашей таблицы Люди:
create index people_name_idx on people(name);
\d people
Что приводит к:
Table "public.people"
Column | Type | Modifiers
-----------+-----------------------+-----------------------------------
id | integer | not null default nextval
| | ('people_id_seq'::regclass)
name | character varying(50) |
house_no | integer | not null
street_id | integer | not null
phone_no | character varying |
Indexes:
"people_pkey" PRIMARY KEY, btree (id)
"people_name_idx" btree (name) <-- new index added!
Foreign-key constraints:
"people_streets_fk" FOREIGN KEY (id) REFERENCES streets(id)
15.2.10. Сбрасывание таблиц в SQL
Если вы хотите избавиться от таблицы, вы можете использовать команду drop:
drop table streets;
В нашем примере приведенная выше команда не сработает. Почему?
Ответить
Причина, по которой команда DROP не сработает в этом случае, заключается в том, что таблица people имеет ограничение Внешний ключ к таблице streets. Это означает, что удаление таблицы улицы приведет к тому, что таблица люди будет содержать ссылки на несуществующие данные улицы.
Можно «принудительно» удалить таблицу улицы с помощью команды CASCADE, но это также приведет к удалению таблицы люди и любой другой таблицы, связанной с таблицей улицы. Используйте с осторожностью!
Если использовать ту же команду drop table для таблицы people, она будет успешной:
drop table people;
Примечание
Если вы действительно ввели эту команду и сбросили таблицу people, сейчас самое время ее восстановить, поскольку она понадобится вам в следующих упражнениях.
15.2.11. Несколько слов о pgAdmin III
Мы показываем вам команды SQL из приглашения psql, потому что это очень полезный способ изучения баз данных. Однако есть более быстрые и простые способы сделать многое из того, что мы вам показываем. Установите pgAdmin III, и вы сможете создавать, удалять, изменять и т. д. таблицы с помощью операций «укажи и щелкни» в графическом интерфейсе.
В Ubuntu вы можете установить его следующим образом:
sudo apt install pgadmin3
Более подробно pgAdmin III будет рассмотрен в другом модуле.
15.2.12. В заключение
Теперь вы увидели, как создать совершенно новую базу данных, начиная с нуля.
15.2.13. Что дальше?
Далее вы узнаете, как использовать СУБД для добавления новых данных.