ProHoster > Ibhulogi > Ulawulo > I-DBMS esebenzayo

I-DBMS esebenzayo

Ihlabathi logcino lwedatha kudala lilawulwa ziiDBMS zobudlelwane, ezisebenzisa ulwimi lweSQL. Kangangokuba ukwahluka okuvelayo kubizwa ngokuba yiNoSQL. Bakwazile ukuzikrobela indawo ethile kule marike, kodwa ii-DBMS zobudlelwane aziyi kufa, kwaye ziyaqhubeka zisetyenziselwa iinjongo zabo.

Kule nqaku ndifuna ukuchaza ingcamango yedatha esebenzayo. Ukuqonda ngcono, ndiya kwenza oku ngokuyithelekisa nemodeli yobudlelwane beklasiki. Iingxaki ezivela kwiimvavanyo ezahlukeneyo zeSQL ezifunyenwe kwi-Intanethi ziya kusetyenziswa njengemizekelo.

Intshayelelo

Oovimba beenkcukacha bobudlelwane basebenza kwiitheyibhile nakwiindawo. Kwisiseko sedatha esisebenzayo, endaweni yoko kuya kusetyenziswa iiklasi nemisebenzi, ngokulandelelanayo. Indawo ekwitheyibhile enezitshixo ze-N izakuboniswa njengomsebenzi we-N parameters. Endaweni yobudlelwane phakathi kweetafile, imisebenzi iya kusetyenziswa ebuyisela izinto zeklasi apho uqhagamshelwano lwenziwa khona. Ulwakhiwo lomsebenzi luya kusetyenziswa endaweni JOIN.

Ngaphambi kokuhamba ngokuthe ngqo kwimisebenzi, ndiya kuchaza umsebenzi we-domain logic. Kwi-DDL ndiya kusebenzisa i-syntax ye-PostgreSQL. Kumsebenzi une-syntax yayo.

Iitafile kunye namabala

Into elula ye-Sku enegama kunye neendawo zamaxabiso:

Ubudlelwane

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

esebenzayo

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

Sibhengeza ezimbini msebenzi, ethatha iparameter enye Sku njengegalelo kwaye ibuyise uhlobo lwakudala.

Kucingelwa ukuba kwi-DBMS esebenzayo into nganye iya kuba nekhowudi yangaphakathi eyenziwa ngokuzenzekelayo kwaye inokufumaneka ukuba kuyimfuneko.

Masibeke ixabiso lemveliso / ivenkile / umthengisi. Inokutshintsha ngokuhamba kwexesha, ngoko ke masiyongeze indawo yexesha etafileni. Ndizakutsiba ukubhengeza iitheyibhile zezalathisi kwisiseko sedatha esinxulumeneyo ukwenza mfutshane ikhowudi:

Ubudlelwane

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)
)

esebenzayo

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

Izalathiso

Umzekelo wokugqibela, siya kwakha isalathisi kuzo zonke izitshixo kunye nomhla ukuze sifumane ngokukhawuleza ixabiso lexesha elithile.

Ubudlelwane

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

esebenzayo

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

Iinjongo

Masiqale ngeengxaki ezilula ezithatyathwe kokuhambelanayo amanqaku kuHabr.

Okokuqala, makhe sibhengeze i-domain logic (yesiseko sedatha yobudlelwane oku kwenziwa ngokuthe ngqo kwinqaku elingasentla).

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 wesi-1.1

Bonisa uluhlu lwabasebenzi abafumana umvuzo ongaphezu kwalowo wosuphavayiza wabo osondeleyo.

Ubudlelwane

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

esebenzayo

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

Umsebenzi wesi-1.2

Dwelisa abasebenzi abafumana owona mvuzo uphezulu kwisebe labo

Ubudlelwane

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

esebenzayo

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));

Zombini ukuphunyezwa ziyalingana. Kwimeko yokuqala, kwi-database yobudlelwane ungasebenzisa i-CREATE VIEW, ngendlela efanayo iya kuqala ukubala umvuzo omkhulu wesebe elithile kuwo. Kule nto ilandelayo, ukucaca, ndiya kusebenzisa imeko yokuqala, kuba ibonisa ngcono isisombululo.

Umsebenzi wesi-1.3

Bonisa uluhlu lwee-ID zesebe, inani labasebenzi apho lingadluli abantu aba-3.

Ubudlelwane

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

esebenzayo

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

Umsebenzi wesi-1.4

Bonisa uluhlu lwabasebenzi abangenaye umphathi omiselweyo osebenza kwisebe elinye.

Ubudlelwane

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

esebenzayo

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

Umsebenzi wesi-1.5

Fumana uluhlu lwee-ID zesebe ezinowona mvuzo uphezulu wabasebenzi uwonke.

Ubudlelwane

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 )

esebenzayo

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();

Masiqhubele phambili kwimisebenzi enzima ngakumbi ukusuka kwenye amanqaku. Iqulethe uhlalutyo olucacileyo lwendlela yokuphumeza lo msebenzi kwi-MS SQL.

Umsebenzi wesi-2.1

Ngabaphi abathengisi abathengisa ngaphezu kweeyunithi ze-1997 zemveliso yeNombolo ye-30 ngo-1?

Ingqiqo yesizinda (njengangaphambili kwi-RDBMS siyatsiba isibhengezo):

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);

Ubudlelwane

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

esebenzayo

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 wesi-2.2

Kumthengi ngamnye (igama, ifani), fumana iimpahla ezimbini (igama) apho umthengi wachitha imali eninzi ngo-1997.

Sandisa i-domain logic kumzekelo wangaphambili:

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

customer = DATA Customer (Order);

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

Ubudlelwane

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

esebenzayo

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 ngolu mgaqo ulandelayo: ushwankathela intetho echazwe emva kwe-SUM (apha 1), ngaphakathi kwamaqela akhankanyiweyo (apha uMthengi noNyaka, kodwa inokuba yiyo nayiphi na intetho), ihlela ngaphakathi kwamaqela ngamabinzana axelwe kwi-ORDER ( apha kuthengwa, kwaye ukuba kuyalingana, ngoko ngokwekhowudi yemveliso yangaphakathi).

Umsebenzi wesi-2.3

Zingaphi iimpahla ekufuneka zi-odolwe kubaboneleli ukuze bazalisekise iiodolo zangoku.

Masandise i-domeyini logic kwakhona:

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

supplier = DATA Supplier (Product);

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

Ubudlelwane

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

esebenzayo

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;

Ingxaki ngeenkwenkwezi

Kwaye umzekelo wokugqibela uvela kum ngokobuqu. Kukho ingqiqo yenethiwekhi yoluntu. Abantu banokuba ngabahlobo omnye nomnye kwaye bathandane. Ukusuka kwimbono yedatha esebenzayo iya kujongeka ngolu hlobo:

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

Kuyimfuneko ukufumana abaviwa kunokwenzeka ubuhlobo. Ngokusesikweni, kufuneka ufumane bonke abantu A, B, C ngohlobo lokuba u-A angabahlobo no-B, kunye no-B abahlobo no-C, uA uthanda u-C, kodwa uA akabahlobo no-C.
Ukusuka kwimbono yedatha esebenzayo, umbuzo unokujongeka ngolu hlobo:

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 ukuba asombulule le ngxaki kwi-SQL ngokwakhe. Kucingelwa ukuba bambalwa kakhulu abahlobo kunabantu obathandayo. Ngoko ke bakwiitafile ezahlukeneyo. Ukuba uphumelele, kukho nomsebenzi oneenkwenkwezi ezimbini. Kuyo, ubuhlobo abukho macala. Kwisiseko sedatha esisebenzayo kuya kujongeka ngolu hlobo:

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: isisombululo kwingxaki ngeenkwenkwezi zokuqala nezesibini ukusuka 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

isiphelo

Kufuneka kuqatshelwe ukuba isivakalisi solwimi esinikiweyo yenye nje yokhetho lokuphumeza ingqikelelo enikiweyo. I-SQL yathathwa njengesiseko, kwaye injongo yayikukuba ifane ngokusemandleni kuyo. Ngokuqinisekileyo, abanye banokungawathandi amagama angundoqo, iirejista zamagama, njl. Into ephambili apha yingcamango ngokwayo. Ukuba uyanqwena, unokwenza zombini i-C ++ kunye nePython yesintaksi efanayo.

Ingqikelelo yedatha echaziweyo, ngokoluvo lwam, inezi nzuzo zilandelayo:

  • Ukulula. Esi sisalathiso esinentsingiselo engabonakaliyo kwiimeko ezilula. Kodwa ukuba ujonga iimeko ezinzima ngakumbi (umzekelo, iingxaki ngeenkwenkwezi), ngoko, ngokombono wam, ukubhala imibuzo enjalo kulula kakhulu.
  • Инкапсуляция. Kweminye imizekelo ndibhengeze imisebenzi ephakathi (umzekelo, thengiswa, kuthengwa njl.njl.), apho imisebenzi elandelayo yakhiwa khona. Oku kukuvumela ukuba utshintshe ingqiqo yemisebenzi ethile, ukuba kuyimfuneko, ngaphandle kokutshintsha ingqiqo yezo zixhomekeke kuzo. Umzekelo, unokwenza ukuthengisa thengiswa zibalwe kwizinto ezahlukeneyo ngokupheleleyo, ngelixa enye ingqiqo ayiyi kutshintsha. Ewe, oku kunokuphunyezwa kwi-RDBMS kusetyenziswa CREATE VIEW. Kodwa ukuba yonke ingqiqo ibhalwe ngolu hlobo, ayiyi kujongeka ifundeka kakhulu.
  • Akukho msantsa wesemantic. Uluhlu olunjalo lwedatha lusebenza kwimisebenzi kunye neeklasi (endaweni yeetafile kunye nemimandla). Kanye njengakwinkqubo yeklasikhi (ukuba sicingela ukuba indlela ngumsebenzi kunye neparameter yokuqala ngohlobo lweklasi ekuyo). Ngokufanelekileyo, kufuneka kube lula kakhulu "ukwenza abahlobo" ngeelwimi zenkqubo yendalo yonke. Ukongeza, le ngcamango ivumela ukuba kuphunyezwe umsebenzi onzima ngakumbi. Umzekelo, ungalungisa abaqhubi abanje:

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

  • Ilifa kunye ne-polymorphism. Kwisiseko sedatha esisebenzayo, unokwazisa ilifa elininzi ngeKlasi yeKlasi yeKlasi: iClass1, iClass2 yakha kwaye iphumeze i-polymorphism emininzi. Ndiza kubhala ngokuqinisekileyo kumanqaku azayo.

Nangona oku kungumbono nje, sele sinokuphunyezwa okuthile kwiJava eguqulela yonke ingqiqo yokusebenza kwingqiqo yobudlelwane. Kwaye, ingqiqo yokumelwa kunye nezinye izinto ezininzi zincanyathiselwe kakuhle kuyo, enkosi apho sifumana yonke into. iqonga. Ngokusisiseko, sisebenzisa i-RDBMS (kuphela i-PostgreSQL okwangoku) "njengomatshini obonakalayo". Iingxaki ngamanye amaxesha zivela ngolu guqulelo kuba i-RDBMS yombuzo we-optimizer ayizazi iinkcukacha-manani ezithile i-FDBMS ezaziyo. Kwithiyori, kunokwenzeka ukuphumeza inkqubo yolawulo lwedatha eya kusebenzisa isakhiwo esithile njengokugcinwa, ukulungelelaniswa ngokukodwa kwingqiqo yokusebenza.

umthombo: www.habr.com

Yongeza izimvo