Aye ti awọn data data ti pẹ ti jẹ gaba lori nipasẹ awọn DBMS ti ibatan, eyiti o lo ede SQL. Pupọ tobẹẹ ti awọn iyatọ ti n yọ jade ni a pe ni NoSQL. Wọn ṣakoso lati kọwe aaye kan fun ara wọn ni ọja yii, ṣugbọn awọn DBMS ti o ni ibatan kii yoo ku, ati tẹsiwaju lati lo ni itara fun awọn idi wọn.
Ninu àpilẹkọ yii Mo fẹ lati ṣe apejuwe imọran ti aaye data iṣẹ kan. Fun oye ti o dara julọ, Emi yoo ṣe eyi nipa ifiwera rẹ pẹlu awoṣe ibatan kilasika. Awọn iṣoro lati oriṣiriṣi awọn idanwo SQL ti a rii lori Intanẹẹti yoo ṣee lo bi apẹẹrẹ.
Ifihan
Awọn apoti isura infomesonu ibatan ṣiṣẹ lori awọn tabili ati awọn aaye. Ninu aaye data iṣẹ kan, awọn kilasi ati awọn iṣẹ yoo ṣee lo dipo, lẹsẹsẹ. Aaye kan ninu tabili pẹlu awọn bọtini N yoo jẹ aṣoju bi iṣẹ ti awọn paramita N. Dipo awọn ibatan laarin awọn tabili, awọn iṣẹ yoo ṣee lo ti o da awọn nkan ti kilasi pada si eyiti a ṣe asopọ si. Tiwqn iṣẹ yoo ṣee lo dipo DARAPO.
Ṣaaju ki o to lọ taara si awọn iṣẹ-ṣiṣe, Emi yoo ṣe apejuwe iṣẹ-ṣiṣe ti imọran agbegbe. Fun DDL Emi yoo lo sintasi PostgreSQL. Fun iṣẹ ṣiṣe o ni sintasi tirẹ.
Awọn tabili ati awọn aaye
Nkan Sku ti o rọrun pẹlu orukọ ati awọn aaye idiyele:
Ibasepo
CREATE TABLE Sku
(
id bigint NOT NULL,
name character varying(100),
price numeric(10,5),
CONSTRAINT id_pkey PRIMARY KEY (id)
)
iṣẹ-ṣiṣe
CLASS Sku;
name = DATA STRING[100] (Sku);
price = DATA NUMERIC[10,5] (Sku);
A kede meji awọn faili, eyi ti o gba Sku paramita kan bi titẹ sii ati da iru atijo pada.
O ti ro pe ni DBMS ti iṣẹ-ṣiṣe ohun kọọkan yoo ni diẹ ninu koodu inu ti o jẹ ipilẹṣẹ laifọwọyi ati pe o le wọle si ti o ba jẹ dandan.
Jẹ ki a ṣeto idiyele fun ọja/itaja/olupese. O le yipada ni akoko pupọ, nitorinaa jẹ ki a ṣafikun aaye akoko kan si tabili. Emi yoo foju sisọ awọn tabili fun awọn ilana ni ibi ipamọ data ibatan lati kuru koodu naa:
Ibasepo
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)
)
iṣẹ-ṣiṣe
CLASS Sku;
CLASS Store;
CLASS Supplier;
dateTime = DATA DATETIME (Sku, Store, Supplier);
price = DATA NUMERIC[10,5] (Sku, Store, Supplier);
Atọka
Fun apẹẹrẹ ti o kẹhin, a yoo kọ atọka lori gbogbo awọn bọtini ati ọjọ ki a le yara wa idiyele fun akoko kan pato.
Ibasepo
CREATE INDEX prices_date
ON prices
(skuId, storeId, supplierId, dateTime)
iṣẹ-ṣiṣe
INDEX Sku sk, Store st, Supplier sp, dateTime(sk, st, sp);
Awọn iṣẹ-ṣiṣe
Jẹ ká bẹrẹ pẹlu jo o rọrun isoro ya lati awọn ti o baamu lori Habr.
Ni akọkọ, jẹ ki a sọ asọye agbegbe naa (fun data data ibatan eyi ni a ṣe taara ninu nkan ti o wa loke).
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);
Iṣẹ́ 1.1
Ṣe afihan atokọ ti awọn oṣiṣẹ ti o gba owo-oṣu ti o tobi ju ti alabojuto lẹsẹkẹsẹ wọn.
Ibasepo
select a.*
from employee a, employee b
where b.id = a.chief_id
and a.salary > b.salary
iṣẹ-ṣiṣe
SELECT name(Employee a) WHERE salary(a) > salary(chief(a));
Iṣẹ́ 1.2
Ṣe atokọ awọn oṣiṣẹ ti o gba owo-oṣu ti o pọju ni ẹka wọn
Ibasepo
select a.*
from employee a
where a.salary = ( select max(salary) from employee b
where b.department_id = a.department_id )
iṣẹ-ṣiṣe
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));
Mejeeji imuse ni o wa deede. Fun ọran akọkọ, ni ibi ipamọ data ibatan o le lo CREATE VIEW, eyiti o ni ọna kanna yoo kọkọ ṣe iṣiro owo-oṣu ti o pọju fun ẹka kan pato ninu rẹ. Ninu ohun ti o tẹle, fun asọye, Emi yoo lo ọran akọkọ, nitori pe o dara julọ ṣe afihan ojutu naa.
Iṣẹ́ 1.3
Ṣe afihan atokọ ti awọn ID ẹka, nọmba awọn oṣiṣẹ ninu eyiti ko kọja eniyan 3.
Ibasepo
select department_id
from employee
group by department_id
having count(*) <= 3
iṣẹ-ṣiṣe
countEmployees 'Количество сотрудников' (Department d) =
GROUP SUM 1 IF department(Employee e) = d;
SELECT Department d WHERE countEmployees(d) <= 3;
Iṣẹ́ 1.4
Ṣe afihan atokọ ti awọn oṣiṣẹ ti ko ni oluṣakoso yiyan ti n ṣiṣẹ ni ẹka kanna.
Ibasepo
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
iṣẹ-ṣiṣe
SELECT name(Employee a) WHERE NOT (department(chief(a)) = department(a));
Iṣẹ́ 1.5
Wa atokọ ti awọn ID ẹka pẹlu iye owo osu oṣiṣẹ lapapọ ti o pọ julọ.
Ibasepo
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 )
iṣẹ-ṣiṣe
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();
Jẹ ki a lọ si awọn iṣẹ-ṣiṣe ti o ni idiwọn diẹ sii lati ọdọ miiran . O ni alaye itupalẹ bi o ṣe le ṣe iṣẹ ṣiṣe yii ni MS SQL.
Iṣẹ́ 2.1
Awọn ti o ntaa wo ni o ta diẹ sii ju awọn ẹya 1997 ti ọja No.. 30 ni ọdun 1?
Ilana agbegbe (bii ṣaaju lori RDBMS a fo ikede naa):
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);
Ibasepo
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
iṣẹ-ṣiṣe
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;
Iṣẹ́ 2.2
Fun olura kọọkan (orukọ, orukọ-idile), wa awọn ẹru meji (orukọ) eyiti olura ti lo owo pupọ julọ ni ọdun 1997.
A fa imọ-ọrọ agbegbe naa pọ si lati apẹẹrẹ iṣaaju:
CLASS Customer 'Клиент';
contactName 'ФИО' = DATA STRING[100] (Customer);
customer = DATA Customer (Order);
unitPrice = DATA NUMERIC[14,2] (Detail);
discount = DATA NUMERIC[6,2] (Detail);
Ibasepo
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
iṣẹ-ṣiṣe
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;
Oṣiṣẹ PARTITION ṣiṣẹ lori ipilẹ atẹle: o ṣe akopọ ikosile ti a sọ pato lẹhin SUM (nibi 1), laarin awọn ẹgbẹ ti a ti sọ tẹlẹ (nibi Onibara ati Ọdun, ṣugbọn o le jẹ ikosile eyikeyi), titọ laarin awọn ẹgbẹ nipasẹ awọn ikosile ti a sọ ni ORDER ( nibi ti ra, ati pe ti o ba dọgba, lẹhinna ni ibamu si koodu ọja inu).
Iṣẹ́ 2.3
Awọn ẹru melo ni o nilo lati paṣẹ lati ọdọ awọn olupese lati mu awọn aṣẹ lọwọlọwọ ṣẹ.
Jẹ ki a tun faagun ọgbọn agbegbe lẹẹkansi:
CLASS Supplier 'Поставщик';
companyName = DATA STRING[100] (Supplier);
supplier = DATA Supplier (Product);
unitsInStock 'Остаток на складе' = DATA NUMERIC[10,3] (Product);
reorderLevel 'Норма продажи' = DATA NUMERIC[10,3] (Product);
Ibasepo
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
iṣẹ-ṣiṣe
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;
Isoro pẹlu aami akiyesi
Ati apẹẹrẹ ti o kẹhin jẹ lati ọdọ mi tikalararẹ. Nibẹ ni awọn kannaa ti a awujo nẹtiwọki. Eniyan le jẹ ọrẹ pẹlu ara wọn ati fẹran ara wọn. Lati iwoye data iṣẹ ṣiṣe yoo dabi eyi:
CLASS Person;
likes = DATA BOOLEAN (Person, Person);
friends = DATA BOOLEAN (Person, Person);
O jẹ dandan lati wa awọn oludije ti o ṣeeṣe fun ọrẹ. Ni deede, o nilo lati wa gbogbo eniyan A, B, C iru eyiti A jẹ ọrẹ pẹlu B, ati B jẹ ọrẹ pẹlu C, A fẹran C, ṣugbọn A kii ṣe ọrẹ pẹlu C.
Lati irisi data iṣẹ ṣiṣe, ibeere naa yoo dabi eyi:
SELECT Person a, Person b, Person c WHERE
likes(a, c) AND NOT friends(a, c) AND
friends(a, b) AND friends(b, c);
A gba oluka naa niyanju lati yanju iṣoro yii ni SQL funrararẹ. O ti ro pe awọn ọrẹ diẹ kere ju awọn eniyan ti o fẹ lọ. Nitorina wọn wa ni awọn tabili lọtọ. Ti o ba ṣe aṣeyọri, iṣẹ-ṣiṣe tun wa pẹlu awọn irawọ meji. Ninu rẹ, ọrẹ kii ṣe iṣiro. Lori aaye data iṣẹ kan yoo dabi eyi:
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: ojutu si isoro pẹlu akọkọ ati keji aami akiyesi lati :
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
ipari
O yẹ ki o ṣe akiyesi pe sintasi ede ti a fun jẹ ọkan ninu awọn aṣayan fun imuse ero ti a fun. A mu SQL gẹgẹbi ipilẹ, ati ibi-afẹde naa ni lati jẹ iru bi o ti ṣee ṣe si. Nitoribẹẹ, diẹ ninu awọn le ma fẹran awọn orukọ ti awọn koko, awọn iforukọsilẹ ọrọ, ati bẹbẹ lọ. Ohun akọkọ nibi ni imọran funrararẹ. Ti o ba fẹ, o le ṣe mejeeji C ++ ati Python iru sintasi.
Agbekale data ti a ṣalaye, ni ero mi, ni awọn anfani wọnyi:
- .Остота. Eyi jẹ atọka ara ẹni ti o jo ti ko han gbangba ni awọn ọran ti o rọrun. Ṣugbọn ti o ba wo awọn ọran ti o nira sii (fun apẹẹrẹ, awọn iṣoro pẹlu awọn asterisks), lẹhinna, ni ero mi, kikọ iru awọn ibeere jẹ rọrun pupọ.
- Янкапсуляция. Ni diẹ ninu awọn apẹẹrẹ Mo kede awọn iṣẹ agbedemeji (fun apẹẹrẹ, ta, ra ati be be lo), lati eyiti awọn iṣẹ atẹle ti kọ. Eyi n gba ọ laaye lati yi iṣaro ti awọn iṣẹ kan pada, ti o ba jẹ dandan, laisi iyipada ọgbọn ti awọn ti o dale lori wọn. Fun apẹẹrẹ, o le ṣe tita ta won iṣiro lati patapata ti o yatọ ohun, nigba ti awọn iyokù ti awọn kannaa yoo ko yi. Bẹẹni, eyi le ṣe imuse ni RDBMS ni lilo ṢẸDA WO. Ṣugbọn ti o ba ti gbogbo awọn kannaa ti kọ ni ọna yi, o yoo ko wo gan ṣeékà.
- Ko si aafo atunmọ. Iru data data nṣiṣẹ lori awọn iṣẹ ati awọn kilasi (dipo awọn tabili ati awọn aaye). Gẹgẹ bi ninu siseto kilasika (ti a ba ro pe ọna kan jẹ iṣẹ kan pẹlu paramita akọkọ ni irisi kilasi eyiti o jẹ). Nitorinaa, o yẹ ki o rọrun pupọ lati “ṣe awọn ọrẹ” pẹlu awọn ede siseto gbogbo agbaye. Ni afikun, ero yii ngbanilaaye fun iṣẹ ṣiṣe eka pupọ diẹ sii lati ṣe imuse. Fun apẹẹrẹ, o le fi awọn oniṣẹ ẹrọ bii:
CONSTRAINT sold(Employee e, 1, 2019) > 100 IF name(e) = 'Петя' MESSAGE 'Что-то Петя продает слишком много одного товара в 2019 году';
- Ogún ati polymorphism. Ninu aaye data ti iṣẹ-ṣiṣe, o le ṣafihan ogún pupọ nipasẹ CLASS ClassP: Class1, Class2 ṣe agbero ati imuse polymorphism pupọ. Emi yoo jasi kọ bii gangan ni awọn nkan iwaju.
Paapaa botilẹjẹpe eyi jẹ imọran nikan, a ti ni diẹ ninu imuse ni Java ti o tumọ gbogbo ọgbọn iṣẹ-ṣiṣe sinu ọgbọn ibatan. Pẹlupẹlu, imọran ti awọn aṣoju ati ọpọlọpọ awọn ohun miiran ti wa ni ẹwà si rẹ, o ṣeun si eyi ti a gba odidi . Ni pataki, a lo RDBMS (PostgreSQL nikan fun bayi) bi “ẹrọ foju”. Awọn iṣoro nigba miiran dide pẹlu itumọ yii nitori pe oluṣapejuwe ibeere RDBMS ko mọ awọn iṣiro kan ti FDBMS mọ. Ni imọran, o ṣee ṣe lati ṣe eto iṣakoso data data ti yoo lo eto kan bi ibi ipamọ, ti a ṣe deede fun ọgbọn iṣẹ.
orisun: www.habr.com