Dunida xog-ururinta waxa muddo dheer gacanta ku hayay DBMS-yada xidhiidhka ah, kuwaas oo adeegsada luqadda SQL. Sidaas awgeed noocyo soo baxaya ayaa loo yaqaan NoSQL. Waxay ku guulaysteen inay meel gaar ah ka samaystaan suuqan, laakiin DBMS-yada xidhiidhku ma dhimanayaan, oo waxay sii wadaan in si firfircoon loogu isticmaalo ujeeddooyinkooda.
Maqaalkan waxaan rabaa in aan ku qeexo fikradda xogta xogta shaqaynaysa. Si loo fahmo wanaagsan, waxaan samayn doonaa tan anigoo barbar dhigaya qaabka xidhiidhka caadiga ah. Dhibaatooyinka imtixaannada kala duwan ee SQL ee laga helo internetka ayaa loo isticmaali doonaa tusaale ahaan.
Horudhac
Xogta la xidhiidha waxay ku shaqeeyaan miisaska iyo goobaha. Xogta xogta shaqaynaysa, fasallada iyo shaqooyinka ayaa loo isticmaali doonaa beddelkeeda, siday u kala horreeyaan. Goob ku taal jadwal leh furayaasha N ayaa loo matalayaa sida shaqeynta cabbirrada N. Halkii laga heli lahaa xiriirka ka dhexeeya miisaska, shaqooyinka ayaa loo isticmaali doonaa soo celinta walxaha fasalka kaas oo xiriirka la sameeyay. Halabuurka shaqada ayaa la isticmaali doonaa halkii lagu biiri lahaa.
Kahor intaanan si toos ah ugu dhaqaaqin hawlaha, waxaan ku tilmaami doonaa hawsha macquulka domain. DDL waxaan u isticmaali doonaa PostgreSQL syntax. Shaqaale ahaan waxay leedahay hab-raac u gaar ah.
Miisaska iyo beeraha
Walax fudud oo Sku ah oo leh magac iyo goobo qiimo:
Xiriir
CREATE TABLE Sku
(
id bigint NOT NULL,
name character varying(100),
price numeric(10,5),
CONSTRAINT id_pkey PRIMARY KEY (id)
)
shaqaynaysa
CLASS Sku;
name = DATA STRING[100] (Sku);
price = DATA NUMERIC[10,5] (Sku);
Waxaan ku dhawaaqnay laba hawlaha, kaas oo qaata hal halbeeg oo Sku sidii gelinta oo soo celisa nooc hore.
Waxaa loo malaynayaa in DBMS shaqaynaysa shay kastaa uu yeelan doono kood gudaha ah kaas oo si toos ah loo soo saaray oo la geli karo haddii loo baahdo.
Aynu dejino qiimaha alaabta/dukaanka/ alaab-qeybiyaha. Waxa laga yaabaa inay isbedesho wakhti ka dib, markaa aynu ku darno meel wakhti ah miiska. Waxaan ka boodi doonaa ku dhawaaqista shaxanka hagayaasha ee xogta xogta la xidhiidha si aan u soo gaabiyo koodka:
Xiriir
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)
)
shaqaynaysa
CLASS Sku;
CLASS Store;
CLASS Supplier;
dateTime = DATA DATETIME (Sku, Store, Supplier);
price = DATA NUMERIC[10,5] (Sku, Store, Supplier);
Tirooyinka
Tusaalaha ugu dambeeya, waxaan ku dhisi doonaa tusmaynta dhammaan furayaasha iyo taariikhda si aan dhaqso ugu helno qiimaha waqti gaar ah.
Xiriir
CREATE INDEX prices_date
ON prices
(skuId, storeId, supplierId, dateTime)
shaqaynaysa
INDEX Sku sk, Store st, Supplier sp, dateTime(sk, st, sp);
hawlaha
Aan ku bilowno dhibaatooyin fudud oo laga soo qaatay kuwa u dhigma ee Habr.
Marka hore, aan ku dhawaaqno macquulka domainka (ee database-xiriirka tan waxaa si toos ah loogu sameeyay qodobka kore).
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);
Hawsha 1.1
Soo bandhig liiska shaqaalaha qaata mushahar ka badan kan kormeerahooda dhow.
Xiriir
select a.*
from employee a, employee b
where b.id = a.chief_id
and a.salary > b.salary
shaqaynaysa
SELECT name(Employee a) WHERE salary(a) > salary(chief(a));
Hawsha 1.2
Qor shaqaalaha qaata mushaharka ugu badan ee waaxdooda
Xiriir
select a.*
from employee a
where a.salary = ( select max(salary) from employee b
where b.department_id = a.department_id )
shaqaynaysa
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));
Labada fulinba waa u dhigma. Kiiskii ugu horreeyay, xogta macluumaadka ee xidhiidhka ah waxaad isticmaali kartaa CREATE VIEW, taas oo si la mid ah marka hore xisaabin doonta mushaharka ugu badan ee waax gaar ah. Waxa soo socda, si loo caddeeyo, waxaan isticmaali doonaa kiiska ugu horreeya, maadaama ay si fiican u muujinayso xalka.
Hawsha 1.3
Soo bandhig liiska aqoonsiga waaxda, tirada shaqaalaha oo aan ka badnayn 3 qof.
Xiriir
select department_id
from employee
group by department_id
having count(*) <= 3
shaqaynaysa
countEmployees 'Количество сотрудников' (Department d) =
GROUP SUM 1 IF department(Employee e) = d;
SELECT Department d WHERE countEmployees(d) <= 3;
Hawsha 1.4
Soo bandhig liiska shaqaalaha aan lahayn maareeye la magacaabay oo isla waax ka shaqeeya.
Xiriir
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
shaqaynaysa
SELECT name(Employee a) WHERE NOT (department(chief(a)) = department(a));
Hawsha 1.5
Soo hel liis aqoonsiyada waaxda oo leh wadarta guud ee mushaharka shaqaalaha.
Xiriir
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 )
shaqaynaysa
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();
Aynu u gudubno hawlo kakan oo mid kale ka yimaad . Waxay ka kooban tahay falanqayn faahfaahsan oo ku saabsan sida loo hirgeliyo hawshan MS SQL.
Hawsha 2.1
Iibiyehee ayaa iibiyay in ka badan 1997 unug oo badeecada No. 30 sanadkii 1?
Domain Logic (sidii hore ee RDBMS waanu ka boodnaa bayaanka):
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);
Xiriir
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
shaqaynaysa
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;
Hawsha 2.2
Iibsade kasta (magaca, magaca qoyska), hel labada alaab (magaca) kaas oo iibsaduhu ku qaatay lacagta ugu badan 1997.
Waxaan ka dheeraynnaa macquulka domainka tusaalihii hore:
CLASS Customer 'Клиент';
contactName 'ФИО' = DATA STRING[100] (Customer);
customer = DATA Customer (Order);
unitPrice = DATA NUMERIC[14,2] (Detail);
discount = DATA NUMERIC[6,2] (Detail);
Xiriir
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
shaqaynaysa
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;
Hawlwadeenka PARTITION wuxuu ku shaqeeyaa mabda'a soo socda: wuxuu soo koobayaa tibaaxaha lagu cayimay SUM kadib (halkan 1), gudaha kooxaha la cayimay (halkan Macmiilka iyo Sannadka, laakiin wuxuu noqon karaa tibaax kasta), isagoo u kala sooca kooxaha dhexdooda tibaaxaha lagu cayimay AMARKA ( halkan la iibsaday, iyo haddii loo siman yahay, ka dibna sida ku cad code alaabta gudaha).
Hawsha 2.3
Immisa alaabo ayaa looga baahan yahay in laga dalbado alaab-qeybiyeyaasha si loo fuliyo amarada hadda socda.
Aan balaadhino macquulka domainka mar kale:
CLASS Supplier 'Поставщик';
companyName = DATA STRING[100] (Supplier);
supplier = DATA Supplier (Product);
unitsInStock 'Остаток на складе' = DATA NUMERIC[10,3] (Product);
reorderLevel 'Норма продажи' = DATA NUMERIC[10,3] (Product);
Xiriir
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
shaqaynaysa
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;
Dhibaatada xiddigta
Tusaalaha ugu dambeeyana waa aniga shaqsi ahaan. Waxaa jira macquulnimada shabakada bulshada. Dadku waa ay saaxiibo isku noqon karaan, wayna is jeclaan karaan. Marka loo eego dhinaca xogta xogta shaqaynaysa waxay u ekaan doontaa sidan:
CLASS Person;
likes = DATA BOOLEAN (Person, Person);
friends = DATA BOOLEAN (Person, Person);
Waa lagama maarmaan in la helo musharrixiinta suurtagalka ah ee saaxiibtinimo. Si rasmi ah, waxaad u baahan tahay inaad hesho dhammaan dadka A, B, C sida A waxay saaxiib la yihiin B, Bna waxay la saaxiib yihiin C, A jecel yihiin C, laakiin A saaxiib lama aha C.
Marka loo eego dhinaca xogta xogta shaqaynaysa, waydiistu waxay u ekaan doontaa sidan:
SELECT Person a, Person b, Person c WHERE
likes(a, c) AND NOT friends(a, c) AND
friends(a, b) AND friends(b, c);
Akhristaha waxaa lagu dhiirigelinayaa inuu dhibaatadan SQL ku xalliyo keligiis. Waxaa loo malaynayaa inay jiraan saaxiibo aad uga yar kuwa aad jeceshahay. Sidaa darteed waxay ku jiraan miisas gaar ah. Haddii lagu guuleysto, waxaa sidoo kale jira hawl leh laba xiddigood. Dhexdeeda, saaxiibtinimadu maaha mid isku mid ah. Xogta shaqaynaysa waxay u ekaan doontaa sidan:
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: xalinta dhibaatada iyadoo calaamadda koowaad iyo labaad laga bilaabo :
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
gunaanad
Waa in la ogaadaa in ereyga luqadda ee la bixiyay uu yahay mid ka mid ah fursadaha hirgelinta fikradda la bixiyay. SQL waxa loo qaatay gundhig ahaan, ujeedkuna waxa uu ahaa in ay la mid noqoto sida ugu macquulsan. Dabcan, qaar baa laga yaabaa inaysan jeclayn magacyada ereyada muhiimka ah, diiwaanka erayada, iwm. Waxa ugu muhiimsan halkan waa fikradda lafteeda. Haddii la rabo, waxaad samayn kartaa labadaba C++ iyo Python syntax isku mid ah.
Fikradda xogta macluumaadka ee la sharraxay, fikradayda, waxay leedahay faa'iidooyinka soo socda:
- sahal. Kani waa tilmaame aan caadi ahayn oo aan muuqan xaaladaha fudud. Laakiin haddii aad eegto kiisas aad u adag (tusaale ahaan, dhibaatooyinka asterisks), ka dibna, fikradayda, qorista weydiimaha noocan oo kale ah ayaa aad u fudud.
- Encapsulation. Tusaalooyinka qaar waxaan ku dhawaaqay hawlo dhexdhexaad ah (tusaale ahaan, iibin, iibsaday iwm), kuwaas oo shaqooyin dambe laga dhisay. Tani waxay kuu ogolaaneysaa inaad bedesho macquulka ah ee shaqooyinka qaarkood, haddii loo baahdo, iyada oo aan la beddelin caqli-celinta kuwa ku xiran iyaga. Tusaale ahaan, waxaad samayn kartaa iib iibin waxaa laga soo xisaabiyay walxo gebi ahaanba kala duwan, halka caqliga intiisa kale uusan isbedeli doonin. Haa, tan waxaa lagu hirgelin karaa RDBMS iyadoo la isticmaalayo CREATE VIEW. Laakiin haddii dhammaan caqli-galku sidan u qoran yihiin, uma eka mid aad loo akhriyi karo.
- Ma jiro farqi semantic. Keydka noocaan ah wuxuu ku shaqeeyaa shaqooyinka iyo fasallada (halkii miisaska iyo goobaha). Sida barnaamijyada qadiimiga ah (haddii aan ka soo qaadno in habku yahay shaqo leh cabbirka koowaad ee qaabka fasalka uu ka tirsan yahay). Sidaas awgeed, waa inay aad u sahlanaato in "saaxiibo laga yeesho" luqadaha barnaamijyada caalamiga ah. Intaa waxaa dheer, fikraddaani waxay oggolaanaysaa in la hirgeliyo hawlo badan oo kakan. Tusaale ahaan, waxaad geli kartaa hawl-wadeennada sida:
CONSTRAINT sold(Employee e, 1, 2019) > 100 IF name(e) = 'Петя' MESSAGE 'Что-то Петя продает слишком много одного товара в 2019 году';
- Dhaxalka iyo polymorphism. Xogta shaqaynaysa, waxaad ku soo bandhigi kartaa dhaxal badan iyada oo loo marayo CLASS ClassP: Class1, Class2 wuxuu dhisaa oo hirgeliyaa polymorphism badan. Malaha waxaan ku qori doonaa sida saxda ah ee maqaallada mustaqbalka.
Inkasta oo tani ay tahay kaliya fikradda, waxaan horeyba u haysannay qaar ka mid ah hirgelinta Java taas oo u tarjumeysa dhammaan caqli-galnimada shaqada ee macquulka ah. Intaa waxaa dheer, macquulka ah ee matalaadda iyo waxyaabo kale oo badan ayaa si qurux badan ugu xiran, taas oo ay ugu wacan tahay inaan helno gebi ahaanba. . Asal ahaan, waxaan u isticmaalnaa RDBMS (kaliya PostgreSQL hadda) sida "mashiinka dhabta ah". Dhibaatooyin ayaa mararka qaarkood ka dhasha turjumaaddan sababtoo ah hagaajinta weydiinta RDBMS ma yaqaano tirokoobyo gaar ah oo FDBMS ay ogtahay. Aragti ahaan, waa suurtogal in la hirgeliyo nidaamka maaraynta xogta macluumaadka kaas oo u isticmaali doona qaab-dhismeed gaar ah sida kaydinta, oo si gaar ah loogu habeeyey macquulka shaqada.
Source: www.habr.com