Важно
Перевод - это работа сообщества : ссылка:Вы можете присоединиться. Эта страница в настоящее время переводится |прогресс перевода|.
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
Then create a streets table:
create table streets (id serial not null primary key, name varchar(50));
serial and varchar are data types. serial tells
PostgreSQL to start an integer sequence (auto-number) to populate the id
automatically for every new record. varchar(50) tells PostgreSQL to
create a character field of 50 characters in length.
You will notice that the command ends with a ; - all SQL commands should
be terminated this way. When you press Enter, psql will report something
like this:
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
That means your table was created successfully, with a primary key streets_pkey
using streets.id.
Note: If you hit return without entering a ;, then you will get a prompt like
this: address-#. This is because PostgreSQL is expecting you to enter more. Enter
; to run your command.
Чтобы просмотреть схему таблицы, можно сделать следующее:
\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);
That tells the people table that its street_id fields must match
a valid street id from the streets table.
Более привычный способ создать ограничение - сделать это при создании таблицы:
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
If you want to get rid of a table you can use the DROP command:
drop table streets;
В нашем примере приведенная выше команда не сработает. Почему?
Ответить
The reason the DROP command would not work in this case is because the
people table has a Foreign Key constraint to the streets table. This means
that dropping (or deleting) the streets table would leave the people table
with references to non-existent streets data.
It is possible to „force“ the streets table to be deleted by using the
CASCADE command, but this would also delete the people and any other table
which had a relationship to the streets table. Use with caution!
If you used the same drop table command on the people table, it would
be successful:
drop table people;
Примечание
If you actually did enter that command and dropped the people
table, now would be a good time to rebuild it, as you will need it in the
next exercises.
15.2.11. Несколько слов о pgAdmin III
Мы показываем вам команды SQL из приглашения psql, потому что это очень полезный способ изучения баз данных. Однако есть более быстрые и простые способы сделать многое из того, что мы вам показываем. Установите pgAdmin III, и вы сможете создавать, удалять, изменять и т. д. таблицы с помощью операций «укажи и щелкни» в графическом интерфейсе.
В Ubuntu вы можете установить его следующим образом:
sudo apt install pgadmin3
Более подробно pgAdmin III будет рассмотрен в другом модуле.
15.2.12. В заключение
Теперь вы увидели, как создать совершенно новую базу данных, начиная с нуля.
15.2.13. Что дальше?
Далее вы узнаете, как использовать СУБД для добавления новых данных.