Путь в IT. Тестирование: SQL и PostgreSQL

SQL и PostgreSQL

База данных (БД) – набор взаимосвязанных данных

Система управления базами данных (СУБД) – комплекс программных средств для управления данными

СУБД отвечает за: поддержку языка БД, механизмы хранения и извлечения данных, оптимизацию процессов извлечения данных и т.д.

Типы СУБД:

Файл-серверные (Microsoft Access) – файлы данных располагаются централизовано н файл-сервере, а система управления на каждом клиентском ПК. Плюсы: низкая нагрузка на процессор файл-сервера; минусы: высокая загрузка локальной сети и невозможность обеспечения таких характеристик: высокая надежность, доступность, безопасность.

Клиент-серверные (MySql, PostgreSQL) – и СУБД и сама БД располагаются на сервере, а к ним осуществляется доступ удаленно с клиентских машин и все клиентские запросы обрабатываются централизовано СУБД.

Встраиваемые (SQLite).

Клиент-серверные: MySql, PostgreSQL, MS SQL, ORACLE – все они реляционные и поддерживают язык SQL.

Structured Query Language (SQL) — язык структурированных запросов

Реляционные БД

Теоретической основой служит реляционная алгебра.

Реляционная алгебра определяет систему операций на отношениями (таблицами): объединение, пересечение, вычитание, соединение и т.д.

Сущность – клиенты, заказы, поставщики и пр. т.п.

Таблица – отношение.

Столбец – атрибут.

Строка/запись – кортеж.

Результирующий – результат запроса SQL

Основные типы данных

Создать базу данных

Раскрыть список и нажать правой кнопкой «Databases» - «Create» - «Databases»

Откроется окно – указываем наименование БД – задается пользователь, который владеет БД – далее вкладка «Definition» - указываем кодировку (по умолчанию стоит UTF-8, как правило для большинства случаев её достаточно) – поле «Template» шаблон по умолчанию, по которому создается БД – поле «Connection limit» ограничение на количество подключений к БД (по умолчанию «-1» т.е. ограничений нет) – на вкладке «Security» можно управлять правами пользователей

Код:

CREATE DATABASE testdb

WITH

OWNER = postgres

ENCODING = `UTF8`

CONNECTION LIMIT = -1;

Нажать «Save» - создана БД. Таблицы смотреть во выпадающем списке «Schemas»

Чтобы удалить БД, нужно перейти в другую БД, открыть редактор запросов (нажать «Query tool»)

и ввести код:

DROP DATABASE testdb

Нажать кнопку в интерфейсе «Execute/Refresh (F5)» или нажать кнопку F5.

Создание таблиц

Первичный ключ – инструмент дифференциации строк друг от друга, для того чтобы определять их уникальность.

Открыть редактор запросов БД «testdb» (выбрать «testdb» и нажать «Query tool»)

CREATE TABLE publisher

(

publisher_id integer PRIMARY KEY,

org_name varchar(128) NOT NULL,

address text NOT NULL

);

CREATE TABLE book

(

book_id integer PRIMARY KEY,

title text NOT NULL,

isbn varchar(32) NOT NULL

)

Выполним код

Удалить таблицы

DROP TABLE publisher;

DROP TABLE book

Создание таблицы через интерфейс

Правой кнопкой на необходимой схеме

На вкладке «General» Указать имя таблицы, владельца, схему

На вкладке «Columns», чтобы добавить колонку нажимаем на плюсик (Add new row)

На вкладке «Constraints» устанавливаем ограничения

Код:

CREATE TABLE public.publisher

(

publisher_id integer NOT NULL,

org_name character varying(128) NOT NULL,

address text T NULL,

CONSTRAINTS pk_publisher_id PRIMARY KEY (publisher_id)

)

WITH (

OIDS = FALSE

);

ALTER TABLE public.publisher

OWNER to postgres;

Добавим данные в БД

Открыть редактор запросов необходимой БД

INSERT INTO book

VALUES

Результат

Для создания связей между таблицами создаем внешние ключи

ALTER TABLE book

ADD COLUMN fk_publisher_id;

ALTER TABLE book

ADD CONSTRAINTS fk_ book_publisher

FOREIGN KEY (fk_publisher_id) REFERENCES publisher (publisher_id)

Создадим таблицу «book» заново.

Для этого сначала удалим таблицу

DROP TABLE book

Теперь создадим заново

CREATE TABLE book

(

book_id integer PRIMARY KEY,

title text NOT NULL,

isbn varchar(32) NOT NULL,

fk_publisher_id integer REFERENCES publisher (publisher_id) NOT NULL

)

Добавим данные

Таблицы

Создадим таблицы

Заполним данными

Добавил ещё столбец

Добавим данные

Таблицы

Выбор данных из таблицы

SELECT *

FROM prducts

Ограничим выборку столбцов

DISTINCT - отличающийся, в данном случае уникальность

COUNT

Ещё SELECT

Ещё DISTINCT

AND и OR

BETWEEN

NOT

ORDER BY

MIN, MAX, AVG, SUM

LIMIT

Check on NULL

GROUP BY

Посчитать кол-во заказов вес которых превышает 50 сгруппировав по странам, в которые идёт поставка.

HAVING

Если нужно прописать условия дважды то сначала WHERE потом HAVING

UNION, INTERSECT, EXCEPT

UNION - объединяет наборы результатов двух или более операторов SELECT в один общий. Оператор UNION убирает все повторяющиеся строки.

Подробнее можно почитать здесь: https://itdoxy.com/оператор-postgresql-union/

UNION ALL - все значения

INTERSECT - пересечение

Подробнее можно почитать здесь: https://itdoxy.com/оператор-postgresql-intersect/

Для сравнения

EXCEPT - разница

Оператор EXCEPT возвращает отдельные строки из первого (левого) запроса, которые не содержатся в выводе второго (правого) запроса.

Подробнее можно почитать здесь: https://itdoxy.com/оператор-postgresql-except/

INNER JOIN

LEFT JOIN

FULL JOIN

RIGHT JOIN

CROSS JOIN

INNER JOIN

INNER JOIN = JOIN

LEFT JOIN

RIGHT JOIN

SELF JOIN

USING & NATURAL JOIN

NATURAL JOIN - лучше не использовать, высокий риск забагованности!

Естественное объединение создаёт неявное объединение на основе одинаковых имён столбцов в объединяемых таблицах. Подробнее можно почитать здесь: https://itdoxy.com/естественное-объединение-в-postgresql/

AS - псевдонимы

Задача:

Найти заказчиков и обслуживающих их заказы сотрудников таких, что и заказчики и сотрудники из города London, а доставка идёт компанией Speedy Express. Вывести компанию заказчика и ФИО сотрудника.

Решение:

Задача:

Найти активные (см. поле discontinued) продукты из категории Beverages и Seafood, которых в продаже менее 20 единиц. Вывести наименование продуктов, кол-во единиц в продаже, имя контакта поставщика и его телефонный номер.

Решение:

Задача:

Найти заказчиков, не сделавших ни одного заказа. Вывести имя заказчика и order_id.

Решение:

EXISTS

Подробнее можно почитать здесь https://oracleplsql.ru/exists-postgresql.html

Вывести продукты количество которых в продаже меньше самого малого среднего количества продуктов в деталях заказов (группировка по product_id). Результирующая таблица должна иметь колонки product_name и units_in_stock.

Напишите запрос, который выводит общую сумму фрахтов заказов для компаний-заказчиков для заказов, стоимость фрахта которых больше или равна средней величине стоимости фрахта всех заказов, а также дата отгрузки заказа должна находится во второй половине июля 1996 года. Результирующая таблица должна иметь колонки customer_id и freight_sum, строки которой должны быть отсортированы по сумме фрахтов заказов.

Напишите запрос, который выводит 3 заказа с наибольшей стоимостью, которые были созданы после 1 сентября 1997 года включительно и были доставлены в страны Южной Америки. Общая стоимость рассчитывается как сумма стоимости деталей заказа с учетом дисконта. Результирующая таблица должна иметь колонки customer_id, ship_country и order_price, строки которой должны быть отсортированы по стоимости заказа в обратном порядке.

Вывести все товары (уникальные названия продуктов), которых заказано ровно 10 единиц (конечно же, это можно решить и без подзапроса).

Управление БД и таблицами. DDL

PRIMARY KEY - на всю таблицу может быть только один, используется для связки с внешним ключем и уникально идентифицирует строку в таблице, всегда NOT NULL

UNIQUE - могут быт только уникальные значения, может иметь значение NULL.

CHECK - ограничение условие

DEFAULT

SEQUENCE - последовательность

CREATE SEQUENCE seq1 - создали последовательность под наименованием seq1.

Чтобы сгенерировать следующее значение в последовательности нужно вызвать функцию nextval и передать в качестве аргумента имя последовательности. По умолчанию последовательность будет в качестве первого значения будет генерировать "1".

Функция currval - возвращает текущее значение.

Функция lastval (не принимает аргумента) - возвращает последнее значение сгенерированное какой-либо из последовательностей в текущей сессии.

Манипулировать последовательностью можно с помощью функции setval

Первое значение по умолчанию "1"

Следующее значение "+16"

increment - прирост

Сейчас мы определили book_id как integer. Если просто вставлять данные в эту таблицу, никакого auto increment по этому полю не будет происходить.

Если прописать serial - то будет integer с auto increment.

Но можно назначить генерацию поля, следующим образом

CREATE SEQUENCE book_book_id_seq

START WITH 1 OWNED BY book.book_id;

Этой командой мы создаем последовательность с наименованием book_book_id_seq, которая начинается с единицы и назначить его на поле book_id в таблице book.

Чтобы не получать ошибку, нужно навесить ограничение DEFAULT, что если никто ничего не вставляет в это поле, то вызывает функцию nextval на созданной последовательности.