Lefatše la datha ke khale le busoa ke li-DBMS tse amanang, tse sebelisang puo ea SQL. Ho joalo hoo mefuta e meng e hlahang e bitsoang NoSQL. Ba khonne ho iketsetsa sebaka se itseng 'marakeng ona, empa li-DBMS tse amanang le tsona li ke ke tsa shoa,' me li tsoela pele ho sebelisoa ka mafolofolo molemong oa tsona.
Sehloohong sena ke batla ho hlalosa khopolo ea database e sebetsang. Bakeng sa kutloisiso e ntle, ke tla etsa sena ka ho e bapisa le mohlala oa khale oa likamano. Mathata a tsoang litekong tse fapaneng tsa SQL tse fumanehang Marang-rang a tla sebelisoa e le mehlala.
Selelekela
Mananeo a amanang le likamano a sebetsa litafoleng le masimong. Sebakeng sa polokelo ea litaba, lihlopha le mesebetsi li tla sebelisoa, ka ho latellana. Tšimo e tafoleng e nang le linotlolo tsa N e tla hlahisoa e le ts'ebetso ea li-parameter tsa N. Sebakeng sa likamano lipakeng tsa litafole, ho tla sebelisoa mesebetsi e khutlisetsang lintho tsa sehlopha seo ho hokahanngoang ho sona. Sebopeho sa tšebetso se tla sebelisoa sebakeng sa JOIN.
Pele ke fetela ka kotloloho mesebetsing, ke tla hlalosa mosebetsi oa domain logic. Bakeng sa DDL ke tla sebelisa syntax ea PostgreSQL. Bakeng sa tshebetso e na le syntax ea eona.
Litafole le masimo
Ntho e bonolo ea Sku e nang le mabitso le masimo a theko:
Kamano
CREATE TABLE Sku
(
id bigint NOT NULL,
name character varying(100),
price numeric(10,5),
CONSTRAINT id_pkey PRIMARY KEY (id)
)
E sebetsa
CLASS Sku;
name = DATA STRING[100] (Sku);
price = DATA NUMERIC[10,5] (Sku);
Re phatlalatsa tse peli mesebetsi, e nkang paramethara e le 'ngoe Sku e le ho kenya le ho khutlisa mofuta oa khale.
Ho nahanoa hore ho DBMS e sebetsang ntho e 'ngoe le e' ngoe e tla ba le khoutu e itseng ea ka hare e hlahisoang ka bo eona 'me e ka fumanoa ha ho hlokahala.
Ha re behelle theko ea sehlahisoa/lebenkele/mofani. E ka 'na ea fetoha ha nako e ntse e ea, kahoo ha re ekeleng sebaka sa nako tafoleng. Ke tla tlola ho phatlalatsa litafole bakeng sa li-directory ho database ea kamano ho khutsufatsa khoutu:
Kamano
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)
)
E sebetsa
CLASS Sku;
CLASS Store;
CLASS Supplier;
dateTime = DATA DATETIME (Sku, Store, Supplier);
price = DATA NUMERIC[10,5] (Sku, Store, Supplier);
Li-index
Bakeng sa mohlala oa ho qetela, re tla haha index holim'a linotlolo tsohle le letsatsi e le hore re ka fumana kapele theko ea nako e itseng.
Kamano
CREATE INDEX prices_date
ON prices
(skuId, storeId, supplierId, dateTime)
E sebetsa
INDEX Sku sk, Store st, Supplier sp, dateTime(sk, st, sp);
mesebetsi
A re qaleng ka mathata a batlang a le bonolo a nkiloeng ho tse tsamaellanang
Taba ea pele, a re phatlalatseng logic ea domain (bakeng sa polokelo ea litaba ea kamano sena se etsoa ka kotloloho sengolong se kaholimo).
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);
Mosebetsi oa 1.1
Hlahisa lenane la basebetsi ba fumanang moputso o moholo ho feta oa mookamedi wa bona wa haufi.
Kamano
select a.*
from employee a, employee b
where b.id = a.chief_id
and a.salary > b.salary
E sebetsa
SELECT name(Employee a) WHERE salary(a) > salary(chief(a));
Mosebetsi oa 1.2
Thathamisa basebetsi ba fumanang meputso e holimo mafapheng a bona
Kamano
select a.*
from employee a
where a.salary = ( select max(salary) from employee b
where b.department_id = a.department_id )
E sebetsa
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));
Lisebelisoa ka bobeli lia lekana. Tabeng ea pele, sebakeng sa polokelo ea likamano u ka sebelisa CREATE VIEW, eo ka tsela e ts'oanang e tla qala ho bala moputso o moholo oa lefapha le itseng ho lona. Ho se latelang, bakeng sa ho hlaka, ke tla sebelisa nyeoe ea pele, kaha e bonahatsa tharollo hamolemo.
Mosebetsi oa 1.3
Hlahisa lenane la li-ID tsa lefapha, palo ea basebetsi eo ho eona e sa feteng batho ba 3.
Kamano
select department_id
from employee
group by department_id
having count(*) <= 3
E sebetsa
countEmployees 'Количество сотрудников' (Department d) =
GROUP SUM 1 IF department(Employee e) = d;
SELECT Department d WHERE countEmployees(d) <= 3;
Mosebetsi oa 1.4
Hlahisa lenane la basebetsi ba se nang mookameli ea khethiloeng ea sebetsang lefapheng le le leng.
Kamano
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
E sebetsa
SELECT name(Employee a) WHERE NOT (department(chief(a)) = department(a));
Mosebetsi oa 1.5
Fumana lenane la li-ID tsa lefapha tse nang le moputso o phahameng ka ho fetesisa oa basebetsi.
Kamano
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 )
E sebetsa
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();
Ha re feteleng mesebetsing e rarahaneng ho feta ho e 'ngoe
Mosebetsi oa 2.1
Ke barekisi ba fe ba rekisitseng likarolo tse fetang 1997 tsa sehlahisoa No. 30 ka 1?
Domain logic (joalo ka pele ho RDBMS re tlola phatlalatso):
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);
Kamano
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
E sebetsa
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;
Mosebetsi oa 2.2
Bakeng sa moreki e mong le e mong (lebitso, fane), fumana thepa e 'meli (lebitso) eo moreki a sebelisitseng chelete e ngata ho eona ka 1997.
Re atolosa domain logic ho tloha mohlaleng o fetileng:
CLASS Customer 'Клиент';
contactName 'ФИО' = DATA STRING[100] (Customer);
customer = DATA Customer (Order);
unitPrice = DATA NUMERIC[14,2] (Detail);
discount = DATA NUMERIC[6,2] (Detail);
Kamano
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
E sebetsa
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;
PARTITION opareitara e sebetsa ka molao-motheo o latelang: e akaretsa polelo e boletsoeng ka mor'a SUM (mona 1), ka har'a lihlopha tse boletsoeng (mona Moreki le Selemo, empa e ka ba polelo efe kapa efe), e hlophisa ka har'a lihlopha ka lipolelo tse boletsoeng HO ORDER ( mona ho rekiloe, 'me haeba ho lekana, joale ho ea ka khoutu ea sehlahisoa sa ka hare).
Mosebetsi oa 2.3
Ke thepa e kae e hlokang ho otara ho tsoa ho barekisi ho phethahatsa liodara tsa hajoale.
Ha re eketse domain logic hape:
CLASS Supplier 'Поставщик';
companyName = DATA STRING[100] (Supplier);
supplier = DATA Supplier (Product);
unitsInStock 'Остаток на складе' = DATA NUMERIC[10,3] (Product);
reorderLevel 'Норма продажи' = DATA NUMERIC[10,3] (Product);
Kamano
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
E sebetsa
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;
Bothata ba linaleli
Mme mohlala wa ho qetela o tswa ho nna ka bonna. Ho na le mabaka a utloahalang a marang-rang a sechaba. Batho ba ka ba metsoalle le ho ratana. Ho latela pono ea database e sebetsang e ka shebahala tjena:
CLASS Person;
likes = DATA BOOLEAN (Person, Person);
friends = DATA BOOLEAN (Person, Person);
Hoa hlokahala ho fumana batho ba ka khonang ho etsa setsoalle. Haele hantle, o hloka ho fumana batho bohle A, B, C joalo ka hore A ke metsoalle ea B, 'me B ke metsoalle ea C, A rata C, empa A ha se metsoalle le C.
Ho latela pono ea database e sebetsang, potso e ka shebahala tjena:
SELECT Person a, Person b, Person c WHERE
likes(a, c) AND NOT friends(a, c) AND
friends(a, b) AND friends(b, c);
'Mali o khothalletsoa ho rarolla bothata bona ho SQL ka boeena. Ho nahanoa hore ho na le metsoalle e fokolang haholo ho feta batho bao u ba ratang. Ka hona ba ka litafole tse arohaneng. Haeba e atlehile, ho boetse ho na le mosebetsi o nang le linaleli tse peli. Ho eona, setsoalle ha se tšoane. Ho database e sebetsang e ka shebahala tjena:
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: tharollo ea bothata ka naleli ea pele le ea bobeli ho tloha
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
fihlela qeto e
Ho ke ho hlokomeloe hore syntax ea puo e fanoeng ke e 'ngoe ea likhetho tsa ho phethahatsa mohopolo o fanoeng. SQL e ile ea nkoa e le motheo, 'me sepheo e ne e le hore e tšoane le eona ka hohle kamoo ho ka khonehang. Ehlile, ba bang ba kanna ba se rate mabitso a mantsoe a bohlokoa, lirekoto tsa mantsoe, joalo-joalo. Ntho e ka sehloohong mona ke khopolo ka boeona. Haeba o lakatsa, o ka etsa li-syntax tsa C ++ le Python ka bobeli.
Mohopolo o hlalositsoeng oa database, ka maikutlo a ka, o na le melemo e latelang:
- ÐŸÑ Ð¾Ñ Ð¾Ñ Ñ‚Ð¾Ñ‚Ð. Sena ke sesupo se batlang se le boemong bo sa bonahaleng maemong a bonolo. Empa haeba u sheba linyeoe tse rarahaneng (mohlala, mathata a linaleli), joale, ka maikutlo a ka, ho ngola lipotso tse joalo ho bonolo haholo.
- Инкапсуляция. Mehlalang e meng ke phatlalalitse mesebetsi e mahareng (mohlala, e rekisoe, rekiloe joalo-joalo), moo mesebetsi e latelang e ileng ea etsoa. Sena se o nolofalletsa ho fetola logic ea mesebetsi e itseng, haeba ho hlokahala, ntle le ho fetola logic ea ba itšetlehileng ka bona. Ka mohlala, u ka etsa thekiso e rekisoe li ne li baloa ho tloha linthong tse fapaneng ka ho feletseng, athe tse ling kaofela tsa logic li ke ke tsa fetoha. Ee, sena se ka kengoa tšebetsong ho RDBMS ho sebelisoa CREATE VIEW. Empa haeba logic eohle e ngotsoe ka tsela ena, e ke ke ea shebahala e baloa haholo.
- Ha ho lekhalo la semantic. Database e joalo e sebetsa mesebetsing le lihlopha (ho e-na le litafole le masimo). Joalo ka lenaneong la khale (haeba re nka hore mokhoa ke ts'ebetso e nang le paramente ea pele ka sebopeho sa sehlopha seo e leng ho sona). Ka hona, ho lokela ho ba bonolo haholo ho "etsa metsoalle" ka lipuo tsa mananeo a bokahohleng. Ho feta moo, mohopolo ona o lumella ts'ebetso e rarahaneng haholo ho kengoa ts'ebetsong. Ka mohlala, o ka kenya lisebelisoa tse kang:
CONSTRAINT sold(Employee e, 1, 2019) > 100 IF name(e) = 'Петя' MESSAGE 'Что-то Петя продает слишком много одного товара в 2019 году';
- Lefa le polymorphism. Ka polokelongtshedimosetso e sebetsang, o ka hlahisa lefa le lengata ka CLASS ClassP: Class1, Class2 e aha le ho kenya tshebetsong polymorphism e ngata. Mohlomong ke tla ngola kamoo hantle lihloohong tse tlang.
Leha sena e le mohopolo feela, re se re ntse re e-na le ts'ebetso e itseng ho Java e fetolelang mohopolo o sebetsang hore e be logic ea likamano. Ho feta moo, mohopolo oa boemeli le lintho tse ling tse ngata li hokahantsoe hantle le eona, ka lebaka leo re fumanang kakaretso.
Source: www.habr.com