DBMS hana

Ua hoʻomalu ʻia ka honua o nā ʻikepili e nā DBMS pili, e hoʻohana ana i ka ʻōlelo SQL. No laila ua kapa ʻia nā ʻano like ʻole ʻo NoSQL. Ua hiki iā lākou ke kālai i kahi wahi no lākou iho ma kēia mākeke, akā ʻaʻole e make nā DBMS pili, a hoʻomau ʻia e hoʻohana ikaika ʻia no kā lākou hana.

Ma kēia ʻatikala makemake wau e wehewehe i ka manaʻo o kahi waihona hana. No ka hoʻomaopopo maikaʻi ʻana, e hana wau i kēia ma ka hoʻohālikelike ʻana me ke kumu hoʻohālike pili. E hoʻohana ʻia nā pilikia mai nā hoʻāʻo SQL like ʻole ma ka Pūnaewele.

Hōʻike

Hana ʻia nā ʻikepili pili ma nā papa a me nā kahua. I loko o kahi waihona hana, e hoʻohana ʻia nā papa a me nā hana. E hōʻike ʻia kahi kahua ma ka pākaukau me nā kī N ma ke ʻano he hana o nā ʻāpana N. Ma kahi o nā pilina ma waena o nā papa, e hoʻohana ʻia nā hana e hoʻihoʻi i nā mea o ka papa kahi i hana ʻia ai ka pilina. E hoʻohana ʻia ka haku mele ma kahi o JOIN.

Ma mua o ka neʻe pololei ʻana i nā hana, e wehewehe wau i ka hana o ka logic domain. No DDL e hoʻohana wau i ka syntax PostgreSQL. No ka hana, aia kona syntax pono'ī.

Nā papaʻaina a me nā māla

He mea Sku maʻalahi me ka inoa a me nā kahua kumukūʻai:

Pilina

CREATE TABLE Sku
(
    id bigint NOT NULL,
    name character varying(100),
    price numeric(10,5),
    CONSTRAINT id_pkey PRIMARY KEY (id)
)

Mea hana

CLASS Sku;
name = DATA STRING[100] (Sku);
price = DATA NUMERIC[10,5] (Sku);


Hoʻolaha mākou i ʻelua oihana, e lawe i hoʻokahi ʻāpana Sku i mea hoʻokomo a hoʻihoʻi i kahi ʻano primitive.

Manaʻo ʻia aia i loko o kahi DBMS hana e loaʻa i kēlā me kēia mea kekahi code kūloko i hana ʻia a hiki ke kiʻi ʻia inā pono.

E hoʻonoho kāua i ke kumukūʻai no ka huahana/hale kūʻai/mea hoʻolako. Hiki ke loli i ka manawa, no laila e hoʻohui i kahi kahua manawa i ka papaʻaina. E hoʻokuʻu wau i ka haʻi ʻana i nā papa kuhikuhi no nā papa kuhikuhi i loko o kahi waihona pili e hōʻemi i ke code:

Pilina

CREATE TABLE prices
(
    skuId bigint NOT NULL,
    storeId bigint NOT NULL,
    supplierId bigint NOT NULL,
    dateTime timestamp without time zone,
    price numeric(10,5),
    CONSTRAINT prices_pkey PRIMARY KEY (skuId, storeId, supplierId)
)

Mea hana

CLASS Sku;
CLASS Store;
CLASS Supplier;
dateTime = DATA DATETIME (Sku, Store, Supplier);
price = DATA NUMERIC[10,5] (Sku, Store, Supplier);


Papa Kuhikuhi

No ka laʻana hope loa, e kūkulu mākou i kahi kuhikuhi ma nā kī āpau a me ka lā i hiki iā mākou ke loaʻa koke ke kumukūʻai no kahi manawa kikoʻī.

Pilina

CREATE INDEX prices_date
    ON prices
    (skuId, storeId, supplierId, dateTime)

Mea hana

INDEX Sku sk, Store st, Supplier sp, dateTime(sk, st, sp);


hana

E hoʻomaka kākou me nā pilikia maʻalahi i lawe ʻia mai ka pili nāʻatikala ma Habr.

ʻO ka mea mua, e haʻi aku i ka logic domain (no ka ʻikepili pili i hana pololei ʻia ma ka ʻatikala ma luna).

CLASS Department;
name = DATA STRING[100] (Department);

CLASS Employee;
department = DATA Department (Employee);
chief = DATA Employee (Employee);
name = DATA STRING[100] (Employee);
salary = DATA NUMERIC[14,2] (Employee);


Hana 1.1

Hōʻike i kahi papa inoa o nā limahana i loaʻa ka uku ʻoi aku ka nui ma mua o ko lākou luna hoʻokele.

Pilina

select a.*
from   employee a, employee b
where  b.id = a.chief_id
and    a.salary > b.salary

Mea hana

SELECT name(Employee a) WHERE salary(a) > salary(chief(a));


Hana 1.2

E papa inoa i nā limahana i loaʻa ka uku kiʻekiʻe ma kā lākou ʻoihana

Pilina

select a.*
from   employee a
where  a.salary = ( select max(salary) from employee b
                    where  b.department_id = a.department_id )

Mea hana

maxSalary 'Максимальная зарплата' (Department s) = 
    GROUP MAX salary(Employee e) IF department(e) = s;
SELECT name(Employee a) WHERE salary(a) = maxSalary(department(a));

// или если "заинлайнить"
SELECT name(Employee a) WHERE 
    salary(a) = maxSalary(GROUP MAX salary(Employee e) IF department(e) = department(a));


Ua like nā hoʻokō ʻelua. No ka hihia mua, ma kahi waihona relational hiki iā ʻoe ke hoʻohana i ka CREATE VIEW, a ma ke ʻano like e helu mua i ka uku kiʻekiʻe no kahi keʻena kikoʻī i loko. Ma ka mea e hiki mai ana, no ka maopopo, e hoʻohana wau i ka hihia mua, no ka mea, ʻoi aku ka maikaʻi o ka hōʻike ʻana i ka hopena.

Hana 1.3

Hōʻike i kahi papa inoa o nā ID o ka ʻoihana, ka helu o nā limahana ʻaʻole i ʻoi aku ma mua o 3 mau kānaka.

Pilina

select department_id
from   employee
group  by department_id
having count(*) <= 3

Mea hana

countEmployees 'Количество сотрудников' (Department d) = 
    GROUP SUM 1 IF department(Employee e) = d;
SELECT Department d WHERE countEmployees(d) <= 3;


Hana 1.4

E hōʻike i ka papa inoa o nā limahana i loaʻa ʻole kahi luna i koho ʻia e hana ana ma ka ʻoihana like.

Pilina

select a.*
from   employee a
left   join employee b on (b.id = a.chief_id and b.department_id = a.department_id)
where  b.id is null

Mea hana

SELECT name(Employee a) WHERE NOT (department(chief(a)) = department(a));


Hana 1.5

E ʻimi i kahi papa inoa o nā ID Oihana me ka nui o ka uku o nā limahana.

Pilina

with sum_salary as
  ( select department_id, sum(salary) salary
    from   employee
    group  by department_id )
select department_id
from   sum_salary a       
where  a.salary = ( select max(salary) from sum_salary )

Mea hana

salarySum 'Максимальная зарплата' (Department d) = 
    GROUP SUM salary(Employee e) IF department(e) = d;
maxSalarySum 'Максимальная зарплата отделов' () = 
    GROUP MAX salarySum(Department d);
SELECT Department d WHERE salarySum(d) = maxSalarySum();


E neʻe kākou i nā hana paʻakikī mai kekahi nāʻatikala. Loaʻa iā ia kahi kikoʻī kikoʻī o ka hoʻokō ʻana i kēia hana ma MS SQL.

Hana 2.1

ʻO wai nā mea kūʻai aku i kūʻai aku ma mua o 1997 mau ʻāpana o ka huahana No. 30 i ka makahiki 1?

Domain logic (e like me ka mea ma mua ma RDBMS ke hoʻokuʻu nei mākou i ka hoʻolaha):

CLASS Employee 'Продавец';
lastName 'Фамилия' = DATA STRING[100] (Employee);

CLASS Product 'Продукт';
id = DATA INTEGER (Product);
name = DATA STRING[100] (Product);

CLASS Order 'Заказ';
date = DATA DATE (Order);
employee = DATA Employee (Order);

CLASS Detail 'Строка заказа';

order = DATA Order (Detail);
product = DATA Product (Detail);
quantity = DATA NUMERIC[10,5] (Detail);


Pilina

select LastName
from Employees as e
where (
  select sum(od.Quantity)
  from [Order Details] as od
  where od.ProductID = 1 and od.OrderID in (
    select o.OrderID
    from Orders as o
    where year(o.OrderDate) = 1997 and e.EmployeeID = o.EmployeeID)
) > 30

Mea hana

sold (Employee e, INTEGER productId, INTEGER year) = 
    GROUP SUM quantity(OrderDetail d) IF 
        employee(order(d)) = e AND 
        id(product(d)) = productId AND 
        extractYear(date(order(d))) = year;
SELECT lastName(Employee e) WHERE sold(e, 1, 1997) > 30;


Hana 2.2

No kēlā me kēia mea kūʻai (inoa, inoa inoa), e ʻimi i nā waiwai ʻelua (inoa) kahi i hoʻolilo ai ka mea kūʻai i ke kālā nui loa i ka makahiki 1997.

Hoʻonui mākou i ka logic domain mai ka laʻana mua:

CLASS Customer 'Клиент';
contactName 'ФИО' = DATA STRING[100] (Customer);

customer = DATA Customer (Order);

unitPrice = DATA NUMERIC[14,2] (Detail);
discount = DATA NUMERIC[6,2] (Detail);


Pilina

SELECT ContactName, ProductName FROM (
SELECT c.ContactName, p.ProductName
, ROW_NUMBER() OVER (
    PARTITION BY c.ContactName
    ORDER BY SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)) DESC
) AS RatingByAmt
FROM Customers c
JOIN Orders o ON o.CustomerID = c.CustomerID
JOIN [Order Details] od ON od.OrderID = o.OrderID
JOIN Products p ON p.ProductID = od.ProductID
WHERE YEAR(o.OrderDate) = 1997
GROUP BY c.ContactName, p.ProductName
) t
WHERE RatingByAmt < 3

Mea hana

sum (Detail d) = quantity(d) * unitPrice(d) * (1 - discount(d));
bought 'Купил' (Customer c, Product p, INTEGER y) = 
    GROUP SUM sum(Detail d) IF 
        customer(order(d)) = c AND 
        product(d) = p AND 
        extractYear(date(order(d))) = y;
rating 'Рейтинг' (Customer c, Product p, INTEGER y) = 
    PARTITION SUM 1 ORDER DESC bought(c, p, y), p BY c, y;
SELECT contactName(Customer c), name(Product p) WHERE rating(c, p, 1997) < 3;


Ke hana nei ka mea hoʻohana PARTITION ma kēia kumu: hōʻuluʻulu ʻo ia i ka huaʻōlelo i hōʻike ʻia ma hope o SUM (ma ʻaneʻi 1), i loko o nā pūʻulu i ʻōlelo ʻia (eia ka mea kūʻai aku a me ka Makahiki, akā hiki ke hoʻohālikelike ʻia i loko o nā pūʻulu e nā ʻōlelo i kuhikuhi ʻia i ka ORDER ( ma ʻaneʻi kūʻai, a inā like, a laila e like me ke code huahana kūloko).

Hana 2.3

ʻEhia mau waiwai e pono ke kauoha ʻia mai nā mea hoʻolako e hoʻokō i nā kauoha o kēia manawa.

E hoʻonui hou i ka loiloi domain:

CLASS Supplier 'Поставщик';
companyName = DATA STRING[100] (Supplier);

supplier = DATA Supplier (Product);

unitsInStock 'Остаток на складе' = DATA NUMERIC[10,3] (Product);
reorderLevel 'Норма продажи' = DATA NUMERIC[10,3] (Product);


Pilina

select s.CompanyName, p.ProductName, sum(od.Quantity) + p.ReorderLevel — p.UnitsInStock as ToOrder
from Orders o
join [Order Details] od on o.OrderID = od.OrderID
join Products p on od.ProductID = p.ProductID
join Suppliers s on p.SupplierID = s.SupplierID
where o.ShippedDate is null
group by s.CompanyName, p.ProductName, p.UnitsInStock, p.ReorderLevel
having p.UnitsInStock < sum(od.Quantity) + p.ReorderLevel

Mea hana

orderedNotShipped 'Заказано, но не отгружено' (Product p) = 
    GROUP SUM quantity(OrderDetail d) IF product(d) = p;
toOrder 'К заказу' (Product p) = orderedNotShipped(p) + reorderLevel(p) - unitsInStock(p);
SELECT companyName(supplier(Product p)), name(p), toOrder(p) WHERE toOrder(p) > 0;


Pilikia me ka asterisk

A ʻo ka laʻana hope loa mai iaʻu iho. Aia ka manaʻo o kahi ʻoihana pūnaewele. Hiki i nā kānaka ke hoaloha i kekahi i kekahi a makemake kekahi i kekahi. Mai kahi hiʻohiʻona ʻikepili hana e like me kēia:

CLASS Person;
likes = DATA BOOLEAN (Person, Person);
friends = DATA BOOLEAN (Person, Person);


Pono e ʻimi i nā moho no ka hoaloha. ʻO ka mea maʻamau, pono ʻoe e ʻimi i nā poʻe a pau ʻo A, B, C e like me A he hoaaloha me B, a he hoaaloha ʻo B me C, makemake ʻo A iā C, akā ʻaʻole hoa ʻo A me C.
Mai kahi hiʻohiʻona waihona waihona, e like me kēia ka nīnau:

SELECT Person a, Person b, Person c WHERE 
    likes(a, c) AND NOT friends(a, c) AND 
    friends(a, b) AND friends(b, c);


Paipai ʻia ka mea heluhelu e hoʻoponopono i kēia pilikia ma SQL ma kāna iho. Ua manaʻo ʻia he ʻuʻuku aʻe nā hoaaloha ma mua o ka poʻe āu e makemake ai. No laila aia lākou ma nā papa ʻokoʻa. Inā lanakila, aia kekahi hana me nā hōkū ʻelua. I loko o ia mea, ʻaʻole symmetrical ka pilina. Ma kahi waihona hana e like me kēia:

SELECT Person a, Person b, Person c WHERE 
    likes(a, c) AND NOT friends(a, c) AND 
    (friends(a, b) OR friends(b, a)) AND 
    (friends(b, c) OR friends(c, b));


UPD: hoʻonā i ka pilikia me ka asterisk mua a me ka lua mai dss_kalika:

SELECT 
   pl.PersonAID
  ,pf.PersonAID
  ,pff.PersonAID
FROM Persons                 AS p
--Лайки                      
JOIN PersonRelationShip      AS pl ON pl.PersonAID = p.PersonID
                                  AND pl.Relation  = 'Like'
--Друзья                     
JOIN PersonRelationShip      AS pf ON pf.PersonAID = p.PersonID 
                                  AND pf.Relation = 'Friend'
--Друзья Друзей              
JOIN PersonRelationShip      AS pff ON pff.PersonAID = pf.PersonBID
                                   AND pff.PersonBID = pl.PersonBID
                                   AND pff.Relation = 'Friend'
--Ещё не дружат         
LEFT JOIN PersonRelationShip AS pnf ON pnf.PersonAID = p.PersonID
                                   AND pnf.PersonBID = pff.PersonBID
                                   AND pnf.Relation = 'Friend'
WHERE pnf.PersonAID IS NULL 

;WITH PersonRelationShipCollapsed AS (
  SELECT pl.PersonAID
        ,pl.PersonBID
        ,pl.Relation 
  FROM #PersonRelationShip      AS pl 
  
  UNION 

  SELECT pl.PersonBID AS PersonAID
        ,pl.PersonAID AS PersonBID
        ,pl.Relation
  FROM #PersonRelationShip      AS pl 
)
SELECT 
   pl.PersonAID
  ,pf.PersonBID
  ,pff.PersonBID
FROM #Persons                      AS p
--Лайки                      
JOIN PersonRelationShipCollapsed  AS pl ON pl.PersonAID = p.PersonID
                                 AND pl.Relation  = 'Like'                                  
--Друзья                          
JOIN PersonRelationShipCollapsed  AS pf ON pf.PersonAID = p.PersonID 
                                 AND pf.Relation = 'Friend'
--Друзья Друзей                   
JOIN PersonRelationShipCollapsed  AS pff ON pff.PersonAID = pf.PersonBID
                                 AND pff.PersonBID = pl.PersonBID
                                 AND pff.Relation = 'Friend'
--Ещё не дружат                   
LEFT JOIN PersonRelationShipCollapsed AS pnf ON pnf.PersonAID = p.PersonID
                                   AND pnf.PersonBID = pff.PersonBID
                                   AND pnf.Relation = 'Friend'
WHERE pnf.[PersonAID] IS NULL 

hopena

Pono e hoʻomaopopo ʻia ʻo ka syntax ʻōlelo i hāʻawi ʻia kekahi o nā koho no ka hoʻokō ʻana i ka manaʻo i hāʻawi ʻia. Ua lawe ʻia ʻo SQL ma ke ʻano he kumu, a ʻo ka pahuhopu e like me ka hiki. ʻOiaʻiʻo, makemake paha kekahi i nā inoa o nā huaʻōlelo, nā papa inoa huaʻōlelo, etc. ʻO ka mea nui ma ʻaneʻi ʻo ka manaʻo ponoʻī. Inā makemake ʻia, hiki iā ʻoe ke hana i nā syntax like C++ a me Python.

ʻO ka manaʻo ʻikepili i wehewehe ʻia, i koʻu manaʻo, loaʻa kēia mau mea maikaʻi:

• hoopau i. ʻO kēia kahi hōʻailona kumuhana i ʻike ʻole ʻia i nā hihia maʻalahi. Akā inā ʻoe e nānā i nā hihia paʻakikī (no ka laʻana, nā pilikia me nā asterisk), a laila, i koʻu manaʻo, ʻoi aku ka maʻalahi o ka kākau ʻana i ia mau nīnau.
• Инкапсуляция. Ma kekahi mau laʻana, ua haʻi aku au i nā hana waena (no ka laʻana, kūʻai, kūʻai etc.), kahi i kūkulu ʻia ai nā hana ma hope. ʻAe kēia iā ʻoe e hoʻololi i ka loiloi o kekahi mau hana, inā pono, me ka hoʻololi ʻole i ka loiloi o nā mea e hilinaʻi nei iā lākou. No ka laʻana, hiki iā ʻoe ke kūʻai aku kūʻai ua helu ʻia mai nā mea like ʻole, ʻoiai ʻaʻole e loli ke koena o ka loiloi. ʻAe, hiki ke hoʻokō ʻia i kahi RDBMS me ka hoʻohana ʻana i CREATE VIEW. Akā inā kākau ʻia nā loina āpau i kēia ʻano, ʻaʻole hiki ke nānā pono ʻia.
• ʻAʻohe semantic āpau. Ke hana nei ia waihona ma nā hana a me nā papa (ma kahi o nā papa a me nā māla). E like me ka papahana maʻamau (inā manaʻo mākou he hana ke ʻano me ka ʻāpana mua ma ke ʻano o ka papa nona ia). No laila, ʻoi aku ka maʻalahi o ka "hoaloha" me nā ʻōlelo papahana honua. Eia kekahi, hiki i kēia manaʻo ke hoʻokō i nā hana paʻakikī. No ka laʻana, hiki iā ʻoe ke hoʻokomo i nā mea hana e like me:

  CONSTRAINT sold(Employee e, 1, 2019) > 100 IF name(e) = 'Петя' MESSAGE  'Что-то Петя продает слишком много одного товара в 2019 году';


• Hoʻoilina a me ka polymorphism. I loko o kahi waihona waiwai, hiki iā ʻoe ke hoʻolauna i nā hoʻoilina lehulehu ma o ka CLASS ClassP: Class1, Class2 kūkulu a hoʻokō i nā polymorphism lehulehu. E kākau paha wau pehea i nā ʻatikala e hiki mai ana.

ʻOiai he manaʻo wale kēia, ua loaʻa iā mākou kekahi hoʻokō ma Java e unuhi i nā loina hana āpau i loko o ka loina pili. Eia kekahi, ua hoʻopili maikaʻi ʻia ka loiloi o nā hōʻike a me nā mea ʻē aʻe, e hoʻomaikaʻi i ka mea i loaʻa iā mākou kahi holoʻokoʻa. kahua hoʻonani. ʻO ka mea nui, hoʻohana mākou i ka RDBMS (PostgreSQL wale nō i kēia manawa) ma ke ʻano he "mīkini virtual". Hiki mai nā pilikia i kekahi manawa me kēia unuhi no ka mea ʻaʻole ʻike ka RDBMS query optimizer i kekahi mau helu i ʻike ʻia e ka FDBMS. Ma ke kumumanaʻo, hiki ke hoʻokō i kahi ʻōnaehana hoʻokele waihona e hoʻohana i kekahi ʻano e like me ka waiho ʻana, i hoʻopili pono ʻia no ka loiloi hana.

Source: www.habr.com