суббота, 25 марта 2017 г.

PostgreSQL 9.6 Основные команды.

Переходим в консоль пользователя postgres
# su - postgres
-bash-4.2$
Заходим в консоль PostgreSQL
-bash-4.2$ psql
Пароль: <вводим пароль пользователя postgres>
psql (9.6.2)
Введите "help", чтобы получить справку.
postgres=#

Подключение к PostgreSQL под именем пользователя despot5 сразу к базе данных dbdespot5:
# psql -U despot5 -b dbdespot5 -W
Пароль пользователя despot5: <вводим пароль пользователя despot5>
psql (9.6.2)
Введите "help", чтобы получить справку.
dbdespot5=>

Выход из консоли Psql
CTRL + D или \q

Справка по специальным командам:
postgres=#  \?
Общие
  \copyright             условия использования и распространения PostgreSQL
  \errverbose            вывести максимально подробное сообщение о последней ошибке
  \g [ФАЙЛ] или ;        выполнить запрос
                         (и направить результаты в файл или канал |)
  \gexec                 выполнить запрос, а затем выполнить каждую строку в результате
  \gset [ПРЕФИКС]        выполнить запрос и сохранить результаты в переменных
                         psql
  \q                     выйти из psql

Справка по определенной команде SQL
postgres=# \h ALTER INDEX
Команда:     ALTER INDEX
Описание:    изменить определение индекса
Синтаксис:
ALTER INDEX [ IF EXISTS ] имя RENAME TO новое_имя
ALTER INDEX [ IF EXISTS ] имя SET TABLESPACE табл_пространство
ALTER INDEX имя DEPENDS ON EXTENSION имя_расширения
ALTER INDEX [ IF EXISTS ] имя SET ( параметр_хранения = значение [, ... ] )
ALTER INDEX [ IF EXISTS ] имя RESET ( параметр_хранения [, ... ] )
ALTER INDEX ALL IN TABLESPACE имя [ OWNED BY имя_роли [, ... ] ]
    SET TABLESPACE новое_табл_пространство [ NOWAIT ]

Текущие пользователи просматриваются так:
postgres=# \du
                                          Список ролей
 Имя роли |                                Атрибуты                                 | Член ролей
----------+-------------------------------------------------------------------------+------------
 postgres | Суперпользователь, Создаёт роли, Создаёт БД, Репликация, Пропускать RLS | {}

Создание нового пользователя:
postgres=# CREATE USER despot5;

Присвоение пароля пользователю:
postgres=# alter user despot5 with encrypted password 'passwddespot5';

Создание базы данных
postgres=# CREATE DATABASE dbdespot5;

Выдача полных прав пользователю despot5 на базу dbdespot5
postgres=# GRANT ALL privileges ON DATABASE dbdespot5 TO despot5;

Отзыв привелегий:
postgres=# REVOKE ALL privileges ON DATABASE dbdespot5 FROM despot5;

Просмотр имеющихся баз данных:
postgres=# \l
                                  Список баз данных
    Имя    | Владелец | Кодировка | LC_COLLATE  |  LC_CTYPE   |     Права доступа
-----------+----------+-----------+-------------+-------------+-----------------------
 dbdespot5 | postgres | UTF8      | ru_RU.UTF-8 | ru_RU.UTF-8 | =Tc/postgres         +
           |          |           |             |             | postgres=CTc/postgres+
           |          |           |             |             | despot5=CTc/postgres
 postgres  | postgres | UTF8      | ru_RU.UTF-8 | ru_RU.UTF-8 |
 template0 | postgres | UTF8      | ru_RU.UTF-8 | ru_RU.UTF-8 | =c/postgres          +
           |          |           |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8      | ru_RU.UTF-8 | ru_RU.UTF-8 | =c/postgres          +
           |          |           |             |             | postgres=CTc/postgres
 testbook  | postgres | UTF8      | ru_RU.UTF-8 | ru_RU.UTF-8 |
(5 строк)

Подключение к имеющейся базе данных:
postgres=# \c dbdespot5
Вы подключены к базе данных "dbdespot5" как пользователь "postgres".
dbdespot5=#

Установка расширений.
Установка расширений выполняется на конкретной базе данных после подключения к базе данных.
Проведем установку расширений cube,tablefunc, dict_xsyn, fuzzystrmatch, pg_trgm к базе данных testbook
testbook=# create extension cube;
CREATE EXTENSION
Проверка установки этого расширения:
testbook=# SELECT '1'::cube;
 cube
------
 (1)
(1 строка)
Продолжаем установку расширений:
testbook=# create extension tablefunc;
CREATE EXTENSION
testbook=#  create extension dict_xsyn;
CREATE EXTENSION
testbook=#  create extension fuzzystrmatch;
CREATE EXTENSION
testbook=#  create extension pg_trgm;
CREATE EXTENSION

Создание таблицы countries:
postgres=# CREATE TABLE countries (
 country_code char(2) PRIMARY KEY,
 country_name text UNIQUE
);
Создание таблицы с ограничением внешнего ключа:
CREATE TABLE cities (
 name text NOT NULL,
 postal_code varchar(9) CHECK (postal_code <> ''),
 country_code char(2) REFERENCES countries,
 PRIMARY KEY (country_code, postal_code)
);
Создание таблицы с ограничением внешнего ключа по двум полям:
CREATE TABLE venues (
 id SERIAL PRIMARY KEY,
 name varchar(255),
 street_add text,
 type char(7) CHECK (type in ('public','private')) DEFAULT 'public',
 postal_code varchar(9),
 country_code char(2),
 FOREIGN KEY (country_code, postal_code)
 REFERENCES cities (country_code, postal_code) MATCH FULL
);
Изменение таблицы:
book=# ALTER TABLE venues ADD COLUMN active boolean DEFAULT TRUE;
Здесь создается дополнительный столбец active булевого типа со значением по умолчанию TRUE. Чтобы изменить порядок колонок, необходимо либо пересоздать таблицу, либо создать новые колонки с последующим переносом данных.

Команды просмотра структуры базы и таблиц:
Показать список всех таблиц.
testbook=# \dt
            Список отношений
 Схема  |    Имя    |   Тип   | Владелец
--------+-----------+---------+----------
 public | countries | таблица | postgres
(1 строка)
Показать список всех таблиц с описанием
testbook=# \dt+
                        Список отношений
 Схема  |    Имя    |   Тип   | Владелец |   Размер   | Описание
--------+-----------+---------+----------+------------+----------
 public | countries | таблица | postgres | 8192 bytes |
(1 строка)
Показать структуру таблицы countries
testbook=# \d countries
         Таблица "public.countries"
   Столбец    |     Тип      | Модификаторы
--------------+--------------+--------------
 country_code | character(2) | NOT NULL
 country_name | text         |
Индексы:
    "countries_pkey" PRIMARY KEY, btree (country_code)
    "countries_country_name_key" UNIQUE CONSTRAINT, btree (country_name)

Режим вывода информации в консоль.
Сохранять результаты запросов в файл 12345.
testbook=# \o 12345
Восстановить режим вывода результатов запросов в консоль
testbook=# \o

Удаление базы данных:
postgres=# DROP DATABASE testbook;
DROP DATABASE

Удаление пользователя.
Если просто так удалять пользователя, обладающего некоторыми правами, то будем получать ошибку
postgres=# DROP ROLE test123;
ОШИБКА:  роль "test123" нельзя удалить, так как есть зависящие от неё объекты
ПОДРОБНОСТИ:  права доступа к объекту база данных dbdespot5
То есть сначала нужно отобрать все привилегии у пользователя test123, а затем удалять его.
postgres=# REVOKE ALL privileges ON DATABASE dbdespot5 FROM test123;
REVOKE
postgres=# DROP ROLE test123;
DROP ROLE

Вставка данных в таблицу:
book=# INSERT INTO countries (country_code, country_name)
VALUES ('us', 'США'), ('mx', 'Мексика'),
     ('au', 'Австралия'), ('de', 'Германия'),
     ('gb', 'United Kingdom'), ('lu', 'Лубундия');
Или
INSERT INTO  cities (name, postal_code, country_code) VALUES ('Toronto', '159875', 'ca');
Просмотр содержимого таблицы:
book=# SELECT * FROM countries;
 country_code |  country_name
--------------+----------------
 us           | США
 mx           | Мексика
 au           | Австралия
 de           | Германия
 gb           | United Kingdom
 lu           | Лубундия
(6 строк)
Удаление строки:
book=# DELETE FROM countries WHERE country_code='lu';
DELETE 1
Обновление строки
UPDATE cities SET  name = 'Портлэнд' WHERE postal_code = 'MX345as99';
UPDATE 1

Чтение данных таблицы внутренним и внешним объединением
book-# SELECT cities.*, country_name
FROM cities INNER JOIN countries
ON cities.country_code = countries.country_code;
Или с применением псевдонимов и при использовании составного ключа
SELECT v.id, v.name, c.name
FROM venues AS v INNER JOIN cities AS c
ON v.country_code = c.country_code
AND v.postal_code = c.postal_code;
Иллюстрация внутреннего соединения:

Внешнее соединение - LEFT
SELECT e.starts, e.ends, e.title, v.name
FROM events AS e LEFT JOIN venues AS v
ON v.id = e.venue_id;

Внешнее соединение - RIGHT
SELECT e.starts, e.ends, e.title, v.name
FROM events AS e RIGHT JOIN venues AS v
ON v.id = e.venue_id;

Внешнее соединение - FULL
SELECT e.starts, e.ends, e.title, v.name
FROM events AS e FULL JOIN venues AS v
ON v.id = e.venue_id;

Соединение трех таблиц. Пример:
SELECT e.title, v.name, v.country_code, countries.country_name
FROM events e, venues v, countries
WHERE e.venue_id = v.id
AND v.country_code = countries.country_code
AND e.title = 'Выступление Казимира';

Создание индекса:
book=#  CREATE INDEX events_title ON events USING btree (title);
Просмотр индекса:
book=# \di+

Просмотр всех таблиц с имеющимся первичным ключом по каталогу pg_class:
book=# SELECT relname, relowner, relacl, relpersistence, relhaspkey FROM pg_class WHERE relkind = 'r' AND relhaspkey = 'true' ;
  relname  | relowner | relacl | relpersistence | relhaspkey
-----------+----------+--------+----------------+------------
 countries |       10 |        | p              | t
 cities    |       10 |        | p              | t
 venues    |       10 |        | p              | t
 events    |       10 |        | p              | t
(4 строки)


Комментариев нет:

Отправить комментарий