ProHoster > Blog > Tsamaiso > DBMS e sebetsang

DBMS e sebetsang

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 Lingoloa ka Habr.

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 Lingoloa. E na le tlhahlobo e qaqileng ea mokhoa oa ho kenya tšebetsong mosebetsi ona ho MS SQL.

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

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. sethala. Ha e le hantle, re sebelisa RDBMS (PostgreSQL feela hajoale) joalo ka "mochini o sebetsang". Mathata ka linako tse ling a hlaha ka phetolelo ena hobane optimizer ea lipotso tsa RDBMS ha e tsebe lipalo-palo tse itseng tseo FDBMS e li tsebang. Ka khopolo, hoa khoneha ho kenya ts'ebetsong tsamaiso ea polokelo ea boitsebiso e tla sebelisa sebopeho se itseng e le polokelo, e lokiselitsoeng ka ho khetheha bakeng sa logic e sebetsang.

Source: www.habr.com

Eketsa ka tlhaloso