PosgreSQL. CRUD таблиць, CRUD записів.

План

Теорія БД
PostgreSQL
SQL
CRUD баз даних
CRUD таблиць

Теорія БД

Ефективно опрацьовувати великий обсяг інформації можна лише за умови її зберігання у структурованому вигляді та наявності добре налагодженого доступу до неї. Для зберігання, накопичення, опрацювання та швидкого пошуку інформації існують електронні бази даних — файли (сукупності файлів) спеціального формату, які містять структуровані дані.

База даних, БД (англ. atabase, DB) - це структурований набір даних, що зберігається відповідно до визначених правил у пам’яті комп’ютерних систем.

В загальному випадку базою даних можна вважати будь-який впорядкований набір даних. Наприклад, паперову картотеку з формулярами про працівників підприємства у відділі кадрів. Але ми зосередимось на використанні баз даних в інформаційних системах.

Прикладами баз даних є Державний реєстр фізичних осіб України, онлайн-база ДАІ України, бібліотечні каталоги, банківські документи (облік клієнтів та їхніх рахунків) тощо.

База даних є сховищем упорядкованої сукупності даних. Для роботи з БД використовують системи керування базами даних.

Систе́ма керування (управління) ба́зами да́них (СУБД, СКБД англ. Database Management System, DBMS) - це програмний комплекс, що забезпечує введення, зберігання, пошук, опрацювання даних у базі даних.

СКБД дозволяють ефективно працювати з базами даних, обсяг яких робить неможливим їх ручне опрацювання.

Через тісний зв’язок баз даних з СУБД під терміном «база даних» інколи необґрунтовано та неточно мають на увазі систему керування базами даних. Але варто розрізняти базу даних — сховище даних, та СУБД — засоби для роботи з базою даних. СУБД з інформаційної системи може бути видалена, але база даних продовжить існувати. І навпаки: СУБД може функціонувати без жодної бази даних.

Сучасні СКБД забезпечують функції щодо керування даними, які можна поділити на такі групи:

Оголошення даних — створення, зміна та видалення визначень, які описують організацію даних.
Модифікація даних — додавання даних, їх редагування та видалення.
Отримання даних — надання даних за запитом застосунку у формі, яка дозволяє їх безпосереднє використання. Дані можуть надаватись або у формі, в якій вони зберігаються у базі даних, або в іншій формі (наприклад, через поєднання різних даних).
Адміністрування даних — реєстрування та відслідковування дій користувачів, дотримання безпеки роботи з даними, забезпечення надійності та цілісності даних, моніторинг продуктивності, резервне копіювання та відновлення даних тощо.

До популярних СКБД належать:
Комерційні

Microsoft SQL Server
Oracle DB2
Interbase
Informix
Sybase

З відкритим кодом

MySQL
PostgreSQL
Firebird
SQLite

Види баз даних.
Бази даних класифікують за різними критеріями.

За моделлю організації даних розрізняють такі бази даних:

Ієрархічна. Модель, де використовується представлення бази даних у вигляді деревовидної (ієрархічної) структури, що складається з об’єктів (даних) різних рівнів. За ієрархічною моделлю функціонує файлова система комп’ютера.
Мережева складається з набору екземплярів певного типу запису і набору екземплярів певного типу зв’язків між цими записами. Мережева модель даних об’єднує бази даних різних банків.
Різниця між ієрархічною моделлю даних і мережевою полягає в тому, що в ієрархічних структурах запис-нащадок повинен мати тільки одного предка, а в мережевій структурі даних у нащадка може бути будь-яке число предків.
Реляційна (від англ. relation — зв’язок) являє собою сукупність зв’язаних таблиць, що містять дані про об’єкти певного виду. Найвживаніші СКБД використовують реляційну модель
даних.
Об’єктно-орієнтована - це модель БД, в якій дані зберігаються як об’єкти, наділені певними властивостями та можливостями (методами) взаємодіяти з іншими об’єктами.
Об’єктно-реляційна. Має спільні риси з двома попередніми моделями.
Функціональна. Використовуються для вирішення аналітичних задач фінансового моделювання та управління продуктивністю. Підтримує інтерактивні обчислення: значення залежних клітинок автоматично оновлюються, коли змінюється значення клітинки.
Модель «сутність-зв’язок» або ER-модель ( англ. Entityrelationship model або entity-relationship diagram) — модель даних, яка дозволяє описувати концептуальні схеми за допомогою узагальнених конструкцій блоків. ER-модель — це мета-модель даних, тобто засіб опису моделей даних. Існує ряд моделей для представлення знань, але одним з найзручніших інструментів уніфікованого представлення даних, незалежного від програмного забезпечення, що його реалізує, є модель «сутність-зв’язок». Важливим є той факт, що з моделі «сутністьзв’язок» можуть бути породжені всі існуючі моделі даних (ієрархічна, мережева, реляційна, об’єктна), тому вона є найзагальнішою.

За розміщенням даних :

Локальна, або централізована. Така база даних підтримується на одному комп’ютері.
Розподілена. Частини такої бази даних розміщують на різних комп’ютерах мережі.

За технологією фізичного зберігання виділяють:

БД у вторинній пам’яті (традиційні) – використовують жорсткий диск.
БД в оперативній пам’яті (in-memory database).
БД у третинній пам’яті (tertiary database) – середовищем постійного зберігання є від’єднаний від сервера пристрій масового зберігання.

Реляційна модель

Реляційна модель даних — логічна модель даних. Вперше була запропонована британським ученим співробітником компанії IBM Едгаром Франком Коддом (E. F. Codd) в 1970 році в статті «A Relational Model of Data for Large Shared Data Banks». В даний час ця модель є фактичним стандартом, на який орієнтуються практично всі сучасні комерційні системи керування базами даних.

Реляційна модель орієнтована на організацію у вигляді двовимірних таблиць.
Кожна реляційна таблиця являє собою двовимірний масив і має такі властивості:

кожний елемент таблиці — один елемент даних
всі комірки в стовпці таблиці однорідні, тобто всі елементи в стовпці
мають однаковий тип
кожний стовпець має унікальне ім’я
однакові рядки в таблиці відсутні
порядок наступності рядків і стовпців може бути довільним

Базовими поняттями реляційних моделей є

відношення
атрибут
кортеж

Відношення зручно представляти у формі таблиць, де кожен рядок є кортеж, а кожен стовпець — атрибут, визначений на деякому домені. Даний неформальний підхід до поняття відношення дає більш звичну для розробників і користувачів форму представлення, де реляційна база даних подається як кінцевий набір таблиць.

Термін SQL	Термін реляційної бази даних	Опис
Рядок	Кортеж або Запис	Набір даних, що представляє один елемент
Колонка	Атрибут або Поле	Позначений елемент кортежу, наприклад «Адреса» або «Дата народження»
Таблиця	Відношення або базове відношення	Набір кортежів, що мають однакові атрибути; набір стовпців і рядків
Відображення або Набір результатів	Похідне відношення	Будь-який набір кортежів; звіт про дані з RDBMS у відповідь на запит

Будь-який кортеж будь-якого відношення відмінний від будь-якого іншого кортежу цього відношення, тобто іншими словами, будь-яке відношення має володіти первинним ключем. Вимога цілісності щодо посилань, або вимога зовнішнього ключа полягає в тому, що для кожного значення зовнішнього ключа, що з’являється у відношенні, на яке веде посилання, повинен знайтися кортеж з таким же значенням первинного ключа, або значення зовнішнього ключа повинно бути невизначеним (тобто ні на що не вказувати).

Атрибут, значення якого однозначно ідентифікує кортежі, називається ключовим (або просто ключем). Якщо кортежі ідентифікуються тільки зчепленням значень декількох атрибутів, то говорять, що відношення має складений ключ. Відношення може містити кілька ключів. Завжди один із ключів оголошується первинним, його значення не можуть оновлюватися.

Переваги реляційної моделі:

простота і доступність для розуміння користувачем. Єдиною використовуваною інформаційною конструкцією є «таблиця»;
суворі правила проектування, які базуються на математичному апараті;
повна незалежність даних. Зміни в прикладній програмі при зміні реляційної БД мінімальні;
для організації запитів і написання прикладного ПЗ немає необхідності знати конкретну організацію БД у зовнішній пам’яті.

Недоліки реляційної моделі:

далеко не завжди предметна область може бути представлена у вигляді «таблиць»;
в результаті логічного проектування з’являється множина «таблиць». Це призводить до труднощів розуміння структури даних;
БД займає відносно багато зовнішньої пам’яті;
відносно низька швидкість доступу до даних.

Реляційна база даних — база даних, заснована на реляційній моделі даних.
Слово «реляційний» походить від англ. relation.
Для роботи з реляційними БД застосовують реляційні СКБД. (РСКБД, RDBMS - relational database management system)

Ключі.
Ключ — це стовпець (може бути декілька стовпців), що додається до таблиці і дозволяє встановити зв’язок із записами в іншій таблиці.

Існують ключі двох типів:

первинні (PRIMARY KEY, PK)
вторинні (зовнішні). (FOREIGN KEY, FK)

Первинний ключ — це одне або кілька полів (стовпців), комбінація значень яких однозначно визначає кожний запис у таблиці. Первинний ключ не допускає значень Null і завжди повинен мати унікальний індекс. Первинний ключ використовується для зв’язування таблиці з зовнішніми ключами
в інших таблицях.
Зовнішній (вторинний) ключ — це одне або кілька полів (стовпців) у таблиці, що містять посилання на поле або поля первинного ключа в іншій таблиці. Зовнішній ключ визначає спосіб об’єднання таблиць.

З двох логічно пов’язаних таблиць одну називають таблицею первинного ключа або головною таблицею, а іншу таблицею вторинного (зовнішнього) ключа або підпорядкованою таблицею. СУБД дозволяють зіставити споріднені записи з обох таблиць і спільно вивести їх у формі, звіті або запиті.

Існує три типи первинних ключів:

ключові поля лічильника (лічильник)
простий ключ
складовий ключ.

Поле лічильника (Тип даних «Лічильник»).
Для кожного запису цього поля таблиці автоматично заноситься унікальне числове значення.
Простий ключ.
Якщо поле містить унікальні значення, такі як коди чи інвентарні номери, то це поле можна визначити як первинний ключ. В якості ключа можна визначити всі поля, що містить дані, якщо це поле не містить повторювані значення або
значення Null.
Складені ключі.
У випадках, коли неможливо гарантувати унікальність значень кожного поля, існує можливість створити ключ, що складається з декількох полів. Найчастіше така ситуація виникає для таблиці, використовуваної для зв’язування двох таблиць відношенням «багато — до — багатьох».

PostgreSQL

PostgreSQL (вимовляється «Пост-грес-К’ю-ель», або «постгрес») — об’єктно-реляційна система керування базами даних (СКБД).
Сервер PostgreSQL написаний на мові C.

Історія
PostgreSQL — широко розповсюджена система керування базами даних з відкритим початковим кодом. Прототип був розроблений в Каліфорнійському університеті Берклі в 1987 році під назвою POSTGRES, після чого активно розвивався і доповнювався. В червні 1990 року з’явилась друга версія із переробленою системою правил маніпулювання та роботи з таблицями, у 1991 році — третя версія, із доданою підтримкою одночасної роботи кількох менеджерів збереження, покращеним механізмом запитів і доповненою системою внутрішніх правил. В цей час POSTGRES використовувався для реалізації великих систем, таких як: система аналізу фінансових даних, пакет моніторингу функціональності потоків, база даних відстеження астероїдів, система медичної інформації, кілька географічних систем. POSTGRES також використовувався як навчальний інструмент в кількох університетах. 1992 року POSTGRES став головною СКБД наукового комп’ютерного проєкту Sequoia 2000. 1993 року кількість користувачів подвоїлась. Стало зрозуміло, що для підтримки й подальшого розвитку необхідні великі витрати часу на дослідження баз даних, тому офіційно проєкт Берклі було зупинено на версії 4.2. 1994 року Andrew Yu і Jolly Chen додали інтерпретатор мови SQL, вдосконалили початковий код і виклали в Інтернеті свою реалізацію під назвою Postgres95. 1996 року програмний продукт було перейменовано на PostgreSQL із початковою версією 6.0. Подальшою підтримкою й розробкою займається група спеціалістів у галузі баз даних, які добровільно приєднались до цього проєкту.

Як встановити - Тут

Як підключити PgAdmin 4

SQL

SQL (/ˌɛsˌkjuːˈɛl/ або /ˈsiːkwəl/, англ. Structured query language — мова структурованих запитів) — декларативна мова програмування для взаємодії з базами даних, що застосовується для формування запитів, оновлення і керування реляційними БД, створення схеми бази даних та її модифікації, системи контролю за доступом до бази даних.
Сама по собі SQL не є ані системою керування базами даних, ані окремим програмним продуктом. На відміну від дійсних мов програмування (C або Pascal), SQL може формувати інтерактивні запити або, бувши вбудованою в прикладні програми, виступати як інструкції для керування даними. Окрім цього, стандарт SQL містить функції для визначення зміни, перевірки та захисту даних.

SQL — це діалогова мова програмування для здійснення запиту і внесення змін до бази даних, а також керування базами даних. Багато баз даних підтримує SQL з розширеннями до стандартної мови. Ядро SQL формує командна мова, яка дозволяє здійснювати пошук, вставку, оновлення і вилучення даних за допомогою використання системи керування і адміністративних функцій.

Синтаксис SQL

Загальний формат інструкції SQL:

SELECT field_1  
FROM table_1  
WHERE criterion_1;

Ключові слова SQL НЕ чутливі до регістру: select те саме, що і SELECT
Кожна інструкція закінчується крапкою з комою (;). Вона може стояти як у кінці останнього речення, так і окремо в рядку в кінці інструкції SQL.

Деякі з найважливіших команд SQL

SELECT - витягує дані з бази даних
UPDATE - оновлює дані в базі даних
DELETE - видаляє дані з бази даних
INSERT INTO - вставляє нові дані в базу даних
CREATE DATABASE - створює нову базу даних
ALTER DATABASE - змінює базу даних
CREATE TABLE - створює нову таблицю
ALTER TABLE - змінює таблицю
DROP TABLE - видаляє таблицю
CREATE INDEX - створює індекс (ключ пошуку)
DROP INDEX - видаляє індекс

Оператор	Опис	Приклад
`=`	дорівнює	`Author = 'Alcott'`
`<>`	не дорівнює (багато СКБД приймають крім `<>` також `!=`)	`Dept <> 'Sales'`
`>`	більше ніж	`Hire_Date > '2012-01-31'`
`<`	менше ніж	`Bonus < 50000.00`
`>=`	більше або дорівнює	`Dependents >= 2`
`<=`	менше або дорівнює	`Rate <= 0.05`
`BETWEEN`	між двома величинами включно	`Cost BETWEEN 100.00 AND 500.00`
LIKE	співставлення тексту з шаблоном	`First_Name LIKE 'Will%'`
`IN`	одне з багатьох можливих значень	`DeptCode IN (101, 103, 209)`
`IS` or `IS NOT`	Порівняння з null (відсутністю даних)	`Address IS NOT NULL`
`IS [NOT] TRUE` or `IS [NOT] FALSE`	булева перевірка істинності	`PaidVacation IS TRUE`
`IS NOT DISTINCT FROM`	значення рівні або обоє null (обоє відсутні)	`Debt IS NOT DISTINCT FROM - Receivables`
`AS`	використовується для зміни назви колонки в результаті запиту	`SELECT employee AS "department1"`

Підключення до СУБД PostgreSQL

Якщо в системі встановлено СУБД PostgreSQL, можна запустити відповідну консольну програму PostgreSQL, яка дозволяє виконувати всі доступні операції з базами даних.

PostgreSQL не вміє працювати без бази даних. Ще до підключення до СУБД PostgreSQL необхідно створити їй базу даних або вибрати одну з існуючих. Для перегляду існуючих баз даних є зовнішня команда консолі:

➜ psql -l
                         List of databases
   Name    | Owner    | Encoding | Collate | Ctype | Access privileges
-----------+-------+----------+---------+-------+-------------------
 postgres  | postgres | UTF8     | C       | C     |
 template0 | postgres | UTF8     | C       | C     | =c/postgres       +
 template1 | postgres | UTF8     | C       | C     | =c/postgres       +
 test      | postgres | UTF8     | C       | C     |
(4 rows)

Консольна команда для створення бази даних у PostgreSQL.

createdb test

Після цього підключаємося до неї:

psql test

Загальний вигляд команди підключення:

psql -U {{ user }} -d {{ dbname }} -h {{ host }}

Про всяк випадок вкажу команду створення спеціального користувача для роботи з PostgreSQL з усіма можливостями:

# Створення користувача з паролем
CREATE USER postgres WITH SUPERUSER PASSWORD 'password';
# Передача прав на основну настроювальну базу даних новому користувачу
ALTER DATABASE postgres OWNER TO postgres;
# (НЕБЕЗПЕЧНО) Видалення користувача, під яким зайшли (НЕБЕЗПЕЧНО)
DROP USER USER_NAME;

Створення баз даних та таблиць

Створення своєї бази даних зсередини консолі psql - справа нескладна:

CREATE DATABASE test;

База створена. Підключемося до неї:

\c test
You are now connected to database "test" as user "postgres".

CREATE TABLE cars(id SERIAL PRIMARY KEY, brand TEXT NOT NULL);

Типи даних у PostgreSQL

Окремо про SERIAL
Важливо зазначити, що SERIAL не створює неявно індекс для стовпця та не робить стовпець стовпцем первинного ключа. Однак це можна легко зробити, вказавши обмеження PRIMARY KEY для стовпця SERIAL.

Для того, щоб подивитися список таблиць, використовуйте команду \dt або \dt+

postgresdb-# \dt
        List of relations
Schema | Name | Type  |    Owner
--------+------+-------+--------------
public | cars | table | postgres
(1 row)

Подивимося на результуючу таблицю:

\d cars
                            Table "public.cars"
 Column |  Type   | Collation | Nullable |             Default
--------+---------+-----------+----------+----------------------------------
 id     | integer |           | not null | nextval('cars_id_seq'::regclass)
 brand  | text    |           | not null |

Команда \d (скорочення від describe) з ім’ям таблиці виводить короткий опис таблиці.

Редагування таблиці

Для зміни існуючої таблиці є команда alter table:

Додати стовпець

ALTER TABLE cars add column model varchar(100) not null;
ALTER TABLE

\d cars
                                    Table "public.cars"
 Column |          Type          | Collation | Nullable |             Default
--------+------------------------+-----------+----------+----------------------------------
 id     | integer                |           | not null | nextval('cars_id_seq'::regclass)
 brand  | text                   |           | not null |
 model  | character varying(100) |           | not null |

Ми додали до таблиці машин колонку із моделлю машини, використавши команду alter table. Тим самим шляхом видалимо цю колонку:

Видалити стовпець

alter table cars drop column model;
ALTER TABLE
\d cars
                            Table "public.cars"
 Column |  Type   | Collation | Nullable |             Default
--------+---------+-----------+----------+----------------------------------
 id     | integer |           | not null | nextval('cars_id_seq'::regclass)
 brand  | text    |           | not null |

Перейменувати стовпець

Щоб перейменувати стовпець у таблиці, використовуйте наведений нижче синтаксис:

ALTER  TABLE  table_name 
RENAME  COLUMN  old_name  to  new_name;

Команда alter table досить складна, але багато що дозволяє. Команда, як і при створенні таблиці, дозволяє додавати і видаляти колонки, змінювати їх різними способами, додавати на них різні індекси, вішати ключі на таблиці і видаляти їх і так далі.

Видалення таблиці

Для видалення таблиць є дві цікаві команди:
drop table з ім’ям таблиці просто видаляє таблицю.

DROP TABLE cars;

truncate table з ім’ям таблиці видаляє лише вміст таблиці, не змінюючи її структури. Serial, якщо він є, зберігатиме своє значення, але є можливість скинути його в нуль, додавши параметр RESTART IDENTITY:

TRUNCATE fattable RESTART IDENTITY;

База даних видаляється командою drop database з ім’ям таблиці та крапкою коми в кінці.

CRUD даних

Коли база даних створена та таблиці в ній створені, коли все це зроблено правильним користувачем з правильними правами та рівнем доступу, саме час заповнити таблиці даними.
У роботі з даними є лише 4 дії: створення, отримання, зміна та видалення даних. Для позначення цих процесів використовується абревіатура
CRUD: Create, Read, Update, Delete.

CRUD	SQL
Create	INSERT
Read	SELECT
Update	UPDATE
Delete	DELETE

Для експериментів із даними нам знадобиться таблиця. Наприклад, таблиця слів зі словника та таблиця словників. Про всяк випадок - команди створення цих таблиць та їх опис:

CREATE TABLE vocabulary  
(  
    id SERIAL PRIMARY KEY,  
    name VARCHAR(255),  
    info TEXT  
);  
  
CREATE TABLE word  
(  
    id SERIAL PRIMARY KEY,  
    word VARCHAR(255),  
    vocabulary_id INTEGER REFERENCES vocabulary(id)  
);

\d vocabulary
                                    Table "public.vocabulary"
 Column |          Type          | Collation | Nullable |                Default
--------+------------------------+-----------+----------+----------------------------------------
 id     | integer                |           | not null | nextval('vocabulary_id_seq'::regclass)
 name   | character varying(255) |           |          |
 info   | text                   |           |          |

\d word
                                       Table "public.word"
    Column     |          Type          | Collation | Nullable |             Default
---------------+------------------------+-----------+----------+----------------------------------
 id            | integer                |           | not null | nextval('word_id_seq'::regclass)
 word          | character varying(255) |           |          |
 vocabulary_id | integer                |           |          |

Create - додавання даних: INSERT

Для додавання даних до таблиці використовується оператор INSERT INTO. Оператор INSERT INTO буває декількох видів, і ми розглянемо основні:

Simple insert

Найпростіший варіант вставки даних у таблицю виглядає так:

insert into vocabulary (name) values ('verbs');
INSERT 0 1

Перевіримо наші дані найпростішим select:

select * from vocabulary;
 id |          name           | info
----+-------------------------+------
  1 | verbs                   |
(1 row)

У цьому прикладі ми вставляємо запис в таблицю vocabulary, вказуючи конкретне значення для кожного стовпця.

Multiple insert

insert into vocabulary (name) values ('IT'), ('Silicon Valley season 1');
INSERT 0 2

Перевіримо:

select * from vocabulary;

 id |          name           | info
----+-------------------------+------
  1 | verbs                   |
  2 | IT                      |
  3 | Silicon Valley season 1 |
(3 rows)

Код вставляє дані в наступних дужках у відповідні стовпці, зазначені в перших дужках. Таким чином, спочатку необхідно перерахувати стовпці (поля), в які планується вносити дані, а потім через кому перерахувати обгорнуті дужками набори даних для цих полів. Такий запит дозволяє додавати кілька записів разом.

Insert from select

Можлива також вставка даних із результату запиту:

insert into vocabulary select * from vocabulary;
INSERT 0 3

select * from vocabulary;

 id |          name           | info
----+-------------------------+------
  1 | verbs                   |
  2 | IT                      |
  3 | Silicon Valley season 1 |
  1 | verbs                   |
  2 | IT                      |
  3 | Silicon Valley season 1 |
(6 rows)

Тут наведено досить простий приклад, але, по суті, якщо ви збудуєте запит даних SELECTтаким чином, щоб кількість стовпців в результаті відповідала необхідному, вказаному в зовнішньому запиті INSERT**, ви можете вбудувати запит досить серйозного рівня складності.

Read - вибірка даних: SELECT

Запит SELECT використовується для отримання даних, і жодним чином їх не змінює. Структура його досить складна, і спробуємо розібрати її поступово і поетапно.

Мінімальний можливий запит має такий вигляд:

select 1;
 ?column? 
----------
        1
(1 row)

Єдиним обов’язковим ключовим словом у запиті select є слово SELECT - вибрати.

Після цього слова слід писати:

функції та оператори PostgreSQL (наприклад, функції роботи з датою та часом)

select CURRENT_TIME;

    current_time
--------------------
 16:54:45.183026+03
(1 row)

рядки та числа
поля таблиць, тимчасових таблиць та уявлень, які ми збираємося вибирати
похідні від цих полів
Вирази

Найчастіше SELECT використовується для роботи з даними з таблиць, і для цього потрібно вказати ці таблиці. Таблиці, якими здійснюється вибірка, перераховуються після ключового слова FROM:

SELECT * FROM books;

Наведений вище запит вибирає всі поля (за це відповідає зірочка) із таблиці books.

Максимально докладна схема запиту select виглядає так:

SELECT
    <field1>,
    <field2>,
    <field3>
    ...
FROM
    <table1>,
    <table2>,
    <joins>,
    <views>,
    <temp_table>
    ...
WHERE
    <cond>
    
ORDER BY
    <field1> ASC
    <field3> DESC
GROUP BY
    <field 1>
HAVING
    <cond with aggr function>
LIMIT
    N,M

Ниже приведено текстовое описание основных элементов структуры запроса SELECT, более подробное описание с примерами будет приведено позже.

Після ключового слова SELECT йде перелік полів таблиць, функцій, що обчислюються з цих полів, констант, незалежних від записів функцій. Для вказівки всіх полів використовується зірочка. Цей пункт є єдиним обов’язковим пунктом у запиті SELECT, інші опціональні.
Далі, після ключового слова FROM слідує перелік таблиць, уявлень та тимчасових таблиць, звідки ведеться вибірка. Таблиці можуть бути перераховані, а можуть бути приєднані до інших таблиць за описаними окремо правил, тобто. за допомогою JOIN.
Далі слідує умова WHERE, що пропускає лише ті записи, які задовольняють перерахованим у WHERE умовам. Всі записи, що не пройшли перевірку, відфільтровуються і не демонструються.
Після фільтру WHERE може слідувати групування записів.
Угруповання виконується за допомогою ключових слів GROUP BY. Суть угруповання в тому, що записи можуть об’єднуватися за ознакою або декількома ознаками в один запис, який несе у собі якусь загальну для всіх записів групи інформацію або результат обробки інформації по всій групі. Конструкція GROUP BY може включати ключове слово HAVING, що дозволяє фільтрувати результати груповання.
Важливо відзначити, що груповання дозволяє використовувати аггрегатні функції як в HAVING, так і після SELECT.
Після групування може відбутися сортування записів за допомогою ключових слів ORDER BY. При групуванні вказується поле або перелік полів, за яким необхідно відсортувати, також можна вказати напрямок сортування. За умовчанням здійснюється сортування за зростанням ASC. Сортування за спаданням здійснюється за допомогою ключового слова DESC.
Наприкінці запиту можливе додавання обмежень на кількість записів. Слово LIMIT та цифрою після вказує, скільки записів ви хочете бачити в результаті. Якщо після слова LIMIT написати OFFSET і цифру. -можна пропустити частину записів LIMIT 20 OFFSET 5 пропустить 5 записів та покаже вам 20 наступних.
PostgreSQL надає альтернативу LIMIT - FETCH. На думку розробників, FETCH краще відповідає стандартам SQL мови.

Практика з SELECT та INSERT

DISTINCT

select * from vocabulary;
 id |          name           | info
----+-------------------------+------
  1 | verbs                   |
  2 | IT                      |
  3 | Silicon Valley season 1 |
  1 | verbs                   |
  2 | IT                      |
  3 | Silicon Valley season 1 |
(6 rows)

select distinct * from vocabulary;
 id |          name           | info
----+-------------------------+------
  2 | IT                      |
  1 | verbs                   |
  3 | Silicon Valley season 1 |
(3 rows)

Умова DISTINCT відкидає дублікати в результаті запиту, залишаючи лише унікальні записи.

WHERE

Додамо кілька слів до нашої таблиці слів:

insert into word (word, vocabulary_id) values('have', 1), ('IP', 2), ('Kanban', 3);
INSERT 0 3

insert into word (word, vocabulary_id) values('have', 7), ('TCP/IP', 2), ('Function', 3);
INSERT 0 3

Тепер виберемо всі слова та кілька разів відфільтруємо їх за допомогою where:

select * from word;
 id |   word   | vocabulary_id
----+----------+---------------
  1 | have     |             1
  2 | IP       |             2
  3 | Kanban   |             3
  4 | have     |             7
  5 | TCP/IP   |             2
  6 | Function |             3
(6 rows)

select word from word where id > 5;
   word
----------
 Function
(1 row)

select word from word where id > 3;
   word
----------
 have
 TCP/IP
 Function
(3 rows)

Трохи більше фільтрації та перерахування полів:

select word from word where vocabulary_id < 4 and id < 6;
  word
--------
 have
 IP
 Kanban
 TCP/IP
(4 rows)

select id, word, vocabulary_id from word where vocabulary_id < 4 and id < 6;
 id |  word  | vocabulary_id
----+--------+---------------
  1 | have   |             1
  2 | IP     |             2
  3 | Kanban |             3
  5 | TCP/IP |             2
(4 rows)

Умови можна комбінувати за допомогою AND та OR.

GROUP BY

Групувати можна дані, які повторюються у групах і не суперечитимуть умовам угруповання. При групуванні можна логічно використовувати агрегатні функції. Агрегатні функції - особливі функції SQL, які застосовуються до всіх записів в результатів вибірки, або до груп.
COUNT – одна з таких функцій.
Більше агрегатних функції тут (SUM, MIN, MAX)

select vocabulary_id from word where vocabulary_id < 4 
and id < 6 group by vocabulary_id;
 vocabulary_id
---------------
             3
             1
             2
(3 rows)

select count(*), vocabulary_id from word where vocabulary_id < 4
and id < 6 group by vocabulary_id;
 count | vocabulary_id
-------+---------------
     1 |             3
     1 |             1
     2 |             2
(3 rows)

GROUP BY - HAVING

Ключове слово HAVING додається лише після GROUP BY для додаткової фільтрації результатів запиту. WHERE фільтрує їх до угруповання, HAVING фільтрує згруповані.

select count(*), vocabulary_id from word where vocabulary_id < 4
and id < 6 group by vocabulary_id having count(*) > 1;

 count | vocabulary_id
-------+---------------
     2 |             2
(1 row)

У цьому запиті відображаються лише ті групи слів, які зустрілися більше одного разу.

ORDER BY

Окрім сортування за зростанням ASC або спаданням DESC, можна ще вказувати, за яким полем це робити. Поле можна прописати як за назвою, так і номером за порядком.

select * from word order by vocabulary_id;
 id |   word   | vocabulary_id
----+----------+---------------
  1 | have     |             1
  2 | IP       |             2
  5 | TCP/IP   |             2
  3 | Kanban   |             3
  6 | Function |             3
  4 | have     |             7
(6 rows)

select * from word order by 3;
 id |   word   | vocabulary_id
----+----------+---------------
  1 | have     |             1
  2 | IP       |             2
  5 | TCP/IP   |             2
  3 | Kanban   |             3
  6 | Function |             3
  4 | have     |             7
(6 rows)

select * from word order by 3, 2;
 id |   word   | vocabulary_id
----+----------+---------------
  1 | have     |             1
  2 | IP       |             2
  5 | TCP/IP   |             2
  6 | Function |             3
  3 | Kanban   |             3
  4 | have     |             7
(6 rows)

LIMIT и OFFSET

select * from word order by 3, 2 limit 3;
 id |  word  | vocabulary_id
----+--------+---------------
  1 | have   |             1
  2 | IP     |             2
  5 | TCP/IP |             2
(3 rows)

select * from word order by 3, 2 limit 3 offset 3;
 id |   word   | vocabulary_id
----+----------+---------------
  6 | Function |             3
  3 | Kanban   |             3
  4 | have     |             7
(3 rows)

Зверніть увагу!
UPDATE та DELETE виконувати з WHERE (умовою).
Без цього оновляться чи видаляться усі записи!

Update - UPDATE

Якщо треба оновити дані, використовуємо UPDATE
Загальний вигляд:

UPDATE  table_name  
SET  column1 = value1, column2 = value2_, ...  
WHERE  condition;

UPDATE  word  
SET  word = 'Agile'  
WHERE  id = 3;

Delete - DELETE

Для видалення використовуємо DELETE
Загальний вигляд:

DELETE  FROM  table_name 
WHERE  condition;

DELETE  FROM  word 
WHERE  id = 6;

Домашнє завдання:

Результати напрацювань заливаємо на Github так, щоб можна було легко знайти їх за номером уроку.
Розширення файлу .sql
У якому і будуть запити один за одним

Встановити PostgreSQL
Практикуємо все, що пройдено на уроці.
Cтворити базу даних фільмів із таблицями акторів, фільмів, режисерів.
Додати дані в таблицях.
Додатково ознайомитися з FETCH (аналог LIMIT) та TRUNCATE (аналог DROP TABLE + CREATE TABLE)

Поиск по этому блогу

PyLevelUA

#11. PosgreSQL. CRUD таблиць і записів