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
ʻ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
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
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.
Source: www.habr.com