Umhlaba wolwazi sekuyisikhathi eside ubuswa ama-DBMS ahlobene, asebenzisa ulimi lwe-SQL. Kangangokuthi okuhlukile okuvelayo kubizwa nge-NoSQL. Bakwazile ukuzenzela indawo ethile kule makethe, kodwa ama-DBMS ahlobene ngeke afe, futhi ayaqhubeka nokusetshenziselwa izinjongo zawo.
Kulesi sihloko ngifuna ukuchaza umqondo we-database esebenzayo. Ukuze ngiqonde kangcono, ngizokwenza lokhu ngokuyiqhathanisa nemodeli yobudlelwano yakudala. Izinkinga ezivela ekuhlolweni okuhlukahlukene kwe-SQL okutholakala ku-inthanethi zizosetshenziswa njengezibonelo.
Isingeniso
Imininingo egciniwe yobudlelwano isebenza kumathebula nezinkambu. Kusizindalwazi esisebenzayo, amakilasi nemisebenzi izosetshenziswa esikhundleni salokho, ngokulandelana. Inkambu kuthebula enokhiye ongu-N izomelwa njengomsebenzi wamapharamitha angu-N. Esikhundleni sobudlelwane phakathi kwamathebula, imisebenzi izosetshenziswa ebuyisela izinto zekilasi lapho uxhumano lwenziwa khona. Ukwakhiwa komsebenzi kuzosetshenziswa esikhundleni sokuthi JOIN.
Ngaphambi kokuthuthela ngqo emisebenzini, ngizochaza umsebenzi we-domain logic. Nge-DDL ngizosebenzisa i-syntax ye-PostgreSQL. Ukuze isebenze ine-syntax yayo.
Amathebula nezinkambu
Into ye-Sku elula enegama nezinkambu zentengo:
Ezobudlelwano
CREATE TABLE Sku
(
id bigint NOT NULL,
name character varying(100),
price numeric(10,5),
CONSTRAINT id_pkey PRIMARY KEY (id)
)
ukusebenza
CLASS Sku;
name = DATA STRING[100] (Sku);
price = DATA NUMERIC[10,5] (Sku);
Simemezela ezimbili imisebenzi, okuthatha ipharamitha eyodwa i-Sku njengokufaka bese ibuyisela uhlobo lwakudala.
Kucatshangwa ukuthi ku-DBMS esebenzayo into ngayinye izoba nekhodi yangaphakathi ekhiqizwa ngokuzenzakalelayo futhi ingafinyelelwa uma kunesidingo.
Masisethe intengo yomkhiqizo/isitolo/umphakeli. Ingase ishintshe ngokuhamba kwesikhathi, ngakho-ke ake sengeze inkambu yesikhathi etafuleni. Ngizokweqa ukumemezela amathebula ezinkomba kusizindalwazi esihlobene ukuze ngifinyeze ikhodi:
Ezobudlelwano
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)
)
ukusebenza
CLASS Sku;
CLASS Store;
CLASS Supplier;
dateTime = DATA DATETIME (Sku, Store, Supplier);
price = DATA NUMERIC[10,5] (Sku, Store, Supplier);
Izinkomba
Ngesibonelo sokugcina, sizokwakha inkomba kubo bonke okhiye kanye nedethi ukuze sithole ngokushesha inani lesikhathi esithile.
Ezobudlelwano
CREATE INDEX prices_date
ON prices
(skuId, storeId, supplierId, dateTime)
ukusebenza
INDEX Sku sk, Store st, Supplier sp, dateTime(sk, st, sp);
Imisebenzi
Ake siqale ngezinkinga ezilula ezithathwe kokuhambisanayo ngoHabr.
Okokuqala, ake simemezele isizindalwazi (kusizindalwazi esihlobene lokhu kwenziwa ngokuqondile esihlokweni esingenhla).
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);
Umsebenzi 1.1
Bonisa uhlu lwabasebenzi abathola iholo elikhulu kunelomphathi wabo oseduze.
Ezobudlelwano
select a.*
from employee a, employee b
where b.id = a.chief_id
and a.salary > b.salary
ukusebenza
SELECT name(Employee a) WHERE salary(a) > salary(chief(a));
Umsebenzi 1.2
Faka ohlwini abasebenzi abathola iholo eliphezulu emnyangweni wabo
Ezobudlelwano
select a.*
from employee a
where a.salary = ( select max(salary) from employee b
where b.department_id = a.department_id )
ukusebenza
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));
Kokubili ukusetshenziswa kuyalingana. Esimweni sokuqala, ku-database ehlobene ungasebenzisa i-CREATE VIEW, ngendlela efanayo ezoqala ukubala umholo omkhulu womnyango othize kuwo. Ngokulandelayo, ukuze kucace, ngizosebenzisa icala lokuqala, ngoba libonisa kangcono isisombululo.
Umsebenzi 1.3
Bonisa uhlu lwama-ID omnyango, inani labasebenzi elingeqi kubantu abangu-3.
Ezobudlelwano
select department_id
from employee
group by department_id
having count(*) <= 3
ukusebenza
countEmployees 'Количество сотрудников' (Department d) =
GROUP SUM 1 IF department(Employee e) = d;
SELECT Department d WHERE countEmployees(d) <= 3;
Umsebenzi 1.4
Bonisa uhlu lwabasebenzi abangenaye imenenja eqokiwe esebenza emnyangweni ofanayo.
Ezobudlelwano
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
ukusebenza
SELECT name(Employee a) WHERE NOT (department(chief(a)) = department(a));
Umsebenzi 1.5
Thola uhlu lwama-ID omnyango anenani eliphelele lomholo wabasebenzi.
Ezobudlelwano
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 )
ukusebenza
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();
Masiqhubekele emisebenzini eyinkimbinkimbi ukusuka kwenye . Iqukethe ukuhlaziya okuningiliziwe kokuthi uwenza kanjani lo msebenzi ku-MS SQL.
Umsebenzi 2.1
Yibaphi abathengisi abathengisa ngaphezu kwamayunithi angama-1997 omkhiqizo onguNombolo 30 ngo-1?
I-logic yesizinda (njengangaphambili ku-RDBMS siyeqa isimemezelo):
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);
Ezobudlelwano
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
ukusebenza
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;
Umsebenzi 2.2
Kumthengi ngamunye (igama, isibongo), thola izimpahla ezimbili (igama) umthengi asebenzise imali eningi kuzo ngo-1997.
Sandisa i-logic yesizinda kusukela kusibonelo sangaphambilini:
CLASS Customer 'Клиент';
contactName 'ФИО' = DATA STRING[100] (Customer);
customer = DATA Customer (Order);
unitPrice = DATA NUMERIC[14,2] (Detail);
discount = DATA NUMERIC[6,2] (Detail);
Ezobudlelwano
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
ukusebenza
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;
Umsebenzisi we-PARTITION usebenza ngomgomo olandelayo: uhlanganisa inkulumo ecaciswe ngemva kwe-SUM (lapha 1), ngaphakathi kwamaqembu ashiwo (lapha Ikhasimende Nonyaka, kodwa kungaba noma yisiphi isisho), ihlunga ngaphakathi kwamaqembu ngamagama ashiwo KU-ORDER ( lapha kuthengiwe, futhi uma kulinganayo, ngokusho kwekhodi yangaphakathi yomkhiqizo).
Umsebenzi 2.3
Zingaki izimpahla ezidinga uku-odwa kubahlinzeki ukuze kugcwaliseke ama-oda amanje.
Masinwebe i-logic yesizinda futhi:
CLASS Supplier 'Поставщик';
companyName = DATA STRING[100] (Supplier);
supplier = DATA Supplier (Product);
unitsInStock 'Остаток на складе' = DATA NUMERIC[10,3] (Product);
reorderLevel 'Норма продажи' = DATA NUMERIC[10,3] (Product);
Ezobudlelwano
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
ukusebenza
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;
Inkinga ngenkanyezi
Futhi isibonelo sokugcina sivela kimi uqobo. Kukhona i-logic yenethiwekhi yokuxhumana nabantu. Abantu bangaba abangani omunye nomunye futhi bathandane. Ngokombono wedatha esebenzayo izobukeka kanje:
CLASS Person;
likes = DATA BOOLEAN (Person, Person);
friends = DATA BOOLEAN (Person, Person);
Kuyadingeka ukuthola abantu abangase bangenele ubungane. Ngokusemthethweni, udinga ukuthola bonke abantu A, B, C njengokuthi u-A ungumngani no-B, futhi u-B ungumngane no-C, u-A uthanda u-C, kodwa u-A akayena u-C.
Ngokombono wesizindalwazi esisebenzayo, umbuzo ungabukeka kanje:
SELECT Person a, Person b, Person c WHERE
likes(a, c) AND NOT friends(a, c) AND
friends(a, b) AND friends(b, c);
Umfundi uyakhuthazwa ukuthi axazulule le nkinga ku-SQL eyedwa. Kucatshangwa ukuthi kunabangane abambalwa kakhulu kunabantu obathandayo. Ngakho-ke basematafuleni ahlukene. Uma uphumelele, kukhona nomsebenzi onezinkanyezi ezimbili. Kuyo, ubungane abulingani. Ku-database esebenzayo izobukeka kanje:
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));
I-UPD: isixazululo senkinga ngenkanyezi yokuqala neyesibili evela :
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
isiphetho
Kufanele kuqashelwe ukuthi i-syntax yolimi enikeziwe ingenye yezinketho zokusebenzisa umqondo onikeziwe. I-SQL yathathwa njengesisekelo, futhi inhloso kwakuwukuba ifane nayo ngangokunokwenzeka. Yiqiniso, abanye bangase bangawathandi amagama angukhiye, amarejista amagama, njll. Into eyinhloko lapha umqondo ngokwawo. Uma uthanda, ungenza kokubili i-C++ ne-Python i-syntax efanayo.
Umqondo wesizindalwazi ochaziwe, ngokombono wami, unezinzuzo ezilandelayo:
- adambise. Lesi yinkomba ehambelanayo engabonakali ezimweni ezilula. Kodwa uma ubheka amacala ayinkimbinkimbi (isibonelo, izinkinga ngezinkanyezi), ngakho-ke, ngombono wami, ukubhala imibuzo enjalo kulula kakhulu.
- Ukuncipha. Kwezinye izibonelo ngimemezele imisebenzi emaphakathi (isibonelo, kuthengiswa, uthenge njll.), okwakhiwa ngayo imisebenzi elandelayo. Lokhu kukuvumela ukuthi uguqule i-logic yemisebenzi ethile, uma kunesidingo, ngaphandle kokushintsha i-logic yalabo abancike kuyo. Isibonelo, ungenza ukuthengisa kuthengiswa zibalwa kusukela ezintweni ezihluke ngokuphelele, kuyilapho yonke i-logic ngeke ishintshe. Yebo, lokhu kungenziwa ku-RDBMS kusetshenziswa i-CREATE VIEW. Kodwa uma yonke i-logic ibhalwe ngale ndlela, ngeke ibukeke ifundeka kakhulu.
- Alikho igebe le-semantic. Isizindalwazi esinjalo sisebenza ngemisebenzi namakilasi (esikhundleni samatafula nezinkambu). Njengakuhlelo lwakudala (uma sicabanga ukuthi indlela iwumsebenzi onepharamitha yokuqala ngendlela yekilasi okungeyalo). Ngakho-ke, kufanele kube lula kakhulu "ukwenza ubungane" ngezilimi zokuhlela zomhlaba wonke. Ukwengeza, lo mqondo uvumela ukusebenza okuyinkimbinkimbi kakhulu ukuthi kusetshenziswe. Isibonelo, ungashumeka ama-opharetha afana nalokhu:
CONSTRAINT sold(Employee e, 1, 2019) > 100 IF name(e) = 'Петя' MESSAGE 'Что-то Петя продает слишком много одного товара в 2019 году';
- Ifa kanye ne-polymorphism. Kusizindalwazi esisebenzayo, ungakwazi ukwethula ifa eliningi nge-CLASS ClassP: I-Class1, i-Class2 yakha futhi isebenzise i-polymorphism eminingi. Cishe ngizobhala ukuthi kanjani kahle ezihlokweni ezizayo.
Noma lokhu kuwumqondo nje, sesivele sinokuqaliswa okuthile ku-Java okuhumusha yonke i-logic esebenzayo ibe ingqondo ehlobene. Ngaphezu kwalokho, i-logic yezethulo nezinye izinto eziningi zinamathiselwe kahle kukho, ngenxa yalokho esithola konke. . Empeleni, sisebenzisa i-RDBMS (kuphela i-PostgreSQL okwamanje) “njengomshini obonakalayo”. Izinkinga ngezinye izikhathi ziyavela ngalokhu kuhumusha ngoba isilungiseleli semibuzo se-RDBMS asizazi izibalo ezithile i-FDBMS ezaziyo. Ngokombono, kungenzeka ukusebenzisa uhlelo lokuphathwa kwedathabhesi oluzosebenzisa isakhiwo esithile njengesitoreji, esiguqulelwe ngokuqondile ku-logic yokusebenza.
Source: www.habr.com