Моя Россыпь

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

Список баз данных

-----------+----------+-----------+-------------+-------------+-----------------------

(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+

Список отношений

--------+-----------+---------+----------+------------+----------

(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 строки)

Моя Россыпь

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

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

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

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

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

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

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