Ụwa nke ọdụ data ejirila DBMS ndị nwere mmekọrịta na-achị ụwa ogologo oge, nke na-eji asụsụ SQL. Nke mere na a na-akpọ ụdị dị iche iche na-apụta NoSQL. Ha jisiri ike wepụta onwe ha otu ebe n'ahịa a, mana DBMS nwere mmekọrịta agaghị anwụ, ma na-aga n'ihu na-arụsi ọrụ ike maka ebumnuche ha.
N'isiokwu a, achọrọ m ịkọwa echiche nke nchekwa data na-arụ ọrụ. Maka nghọta ka mma, m ga-eme nke a site n'iji ya tụnyere ụdị mmekọrịta oge gboo. A ga-eji nsogbu sitere na ule SQL dị iche iche achọtara na ịntanetị dịka ọmụmaatụ.
Okwu Mmalite
Ebe nchekwa data mmekọrịta na-arụ ọrụ na tebụl na ubi. N'ime nchekwa data na-arụ ọrụ, a ga-eji klaasị na arụ ọrụ, n'otu n'otu. A ga-anọchi anya ubi dị na tebụl nwere igodo N dị ka ọrụ nke paramita N. Kama mmekọrịta dị n'etiti tebụl, a ga-eji ọrụ na-eweghachi ihe nke klaasị nke ejikọtara ya. A ga-eji ihe mejupụtara arụ ọrụ kama jikọọ.
Tupu ịkwaga ozugbo na ọrụ ndị ahụ, m ga-akọwa ọrụ nke mgbagha ngalaba. Maka DDL, m ga-eji syntax PostgreSQL. Maka ịrụ ọrụ ọ nwere syntax nke ya.
Tebụl na ubi
Ihe Sku dị mfe nwere aha na ọnụahịa:
Mmekọrịta
CREATE TABLE Sku
(
id bigint NOT NULL,
name character varying(100),
price numeric(10,5),
CONSTRAINT id_pkey PRIMARY KEY (id)
)
Na-arụ ọrụ
CLASS Sku;
name = DATA STRING[100] (Sku);
price = DATA NUMERIC[10,5] (Sku);
Anyị mara ọkwa abụọ ọrụ, nke na-ewere Sku otu paramita dị ka ntinye wee weghachi ụdị ochie.
A na-eche na na DBMS na-arụ ọrụ ihe ọ bụla ga-enwe ụfọdụ koodu ime nke na-emepụta na-akpaghị aka ma nwee ike ịnweta ma ọ bụrụ na ọ dị mkpa.
Ka anyị tọọ ọnụ ahịa ngwaahịa/ụlọ ahịa/onye na-ebubata ya. Ọ nwere ike ịgbanwe ka oge na-aga, yabụ ka anyị tinye oge na tebụl. Aga m ahapụ ikwusa tebụl maka akwụkwọ ndekọ aha na nchekwa data metụtara iji belata koodu ahụ:
Mmekọrịta
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)
)
Na-arụ ọrụ
CLASS Sku;
CLASS Store;
CLASS Supplier;
dateTime = DATA DATETIME (Sku, Store, Supplier);
price = DATA NUMERIC[10,5] (Sku, Store, Supplier);
Ndekọ
Maka ihe atụ ikpeazụ, anyị ga-ewu ihe ndeksi na igodo niile na ụbọchị ka anyị nwee ike ịchọta ọnụ ahịa ngwa ngwa maka otu oge.
Mmekọrịta
CREATE INDEX prices_date
ON prices
(skuId, storeId, supplierId, dateTime)
Na-arụ ọrụ
INDEX Sku sk, Store st, Supplier sp, dateTime(sk, st, sp);
ihe aga-eme
Ka anyị malite na nsogbu ndị dị mfe ewepụtara site na nke kwekọrọ na Habr.
Nke mbụ, ka anyị kwupụta mgbagha ngalaba (maka nchekwa data mmekọrịta nke a na-eme ozugbo na isiokwu dị n'elu).
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);
Ọrụ 1.1
Gosipụta ndepụta ndị ọrụ na-anata ụgwọ ọnwa karịrị nke onye nlekọta ha ozugbo.
Mmekọrịta
select a.*
from employee a, employee b
where b.id = a.chief_id
and a.salary > b.salary
Na-arụ ọrụ
SELECT name(Employee a) WHERE salary(a) > salary(chief(a));
Ọrụ 1.2
Depụta ndị ọrụ na-anata ụgwọ ọnwa kachasị na ngalaba ha
Mmekọrịta
select a.*
from employee a
where a.salary = ( select max(salary) from employee b
where b.department_id = a.department_id )
Na-arụ ọrụ
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));
Ihe omume abụọ a bụ otu. Maka ikpe mbụ, na nchekwa data mmekọrịta ị nwere ike iji CREATE VIEW, nke n'otu ụzọ ahụ ga-ebu ụzọ gbakọọ ụgwọ kacha elu maka otu ngalaba dị na ya. N'ime ihe na-esote, maka idoanya, m ga-eji ikpe mbụ mee ihe, ebe ọ bụ na ọ na-egosipụta nke ọma ngwọta.
Ọrụ 1.3
Gosipụta ndepụta ID ngalaba, ọnụọgụ ndị ọrụ na-agaghị agafe mmadụ 3.
Mmekọrịta
select department_id
from employee
group by department_id
having count(*) <= 3
Na-arụ ọrụ
countEmployees 'Количество сотрудников' (Department d) =
GROUP SUM 1 IF department(Employee e) = d;
SELECT Department d WHERE countEmployees(d) <= 3;
Ọrụ 1.4
Gosipụta ndepụta ndị ọrụ na-enweghị onye njikwa họpụtara na-arụ ọrụ n'otu ngalaba ahụ.
Mmekọrịta
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
Na-arụ ọrụ
SELECT name(Employee a) WHERE NOT (department(chief(a)) = department(a));
Ọrụ 1.5
Chọta ndepụta ID ngalaba nwere oke ụgwọ ọnwa ndị ọrụ.
Mmekọrịta
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 )
Na-arụ ọrụ
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();
Ka anyị gaa n'ihu n'ọrụ ndị dị mgbagwoju anya site na nke ọzọ . O nwere nyocha zuru ezu nke otu esi emejuputa ọrụ a na MS SQL.
Ọrụ 2.1
Kedu ndị na-ere ere karịa 1997 nkeji ngwaahịa Nke 30 na 1?
Echiche ngalaba (dị ka ọ dị na mbụ na RDBMS anyị na-amali nkwupụta ahụ):
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);
Mmekọrịta
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
Na-arụ ọrụ
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;
Ọrụ 2.2
Maka onye ọ bụla zụrụ (aha, nna nna), chọta ngwaahịa abụọ (aha) nke onye zụrụ ya ji ego kachasị na 1997.
Anyị na-agbatị mgbagha ngalaba site na ọmụmaatụ gara aga:
CLASS Customer 'Клиент';
contactName 'ФИО' = DATA STRING[100] (Customer);
customer = DATA Customer (Order);
unitPrice = DATA NUMERIC[14,2] (Detail);
discount = DATA NUMERIC[6,2] (Detail);
Mmekọrịta
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
Na-arụ ọrụ
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;
Onye na-arụ ọrụ PARTITION na-arụ ọrụ na ụkpụrụ ndị a: ọ na-achịkọta okwu akọwapụtara mgbe SUM (ebe a 1), n'ime otu ndị a kapịrị ọnụ (ebe a Onye ahịa na Afọ, ma ọ nwere ike ịbụ okwu ọ bụla), na-ahazi n'ime otu site na okwu ndị akọwapụtara na ORDER ( ebe a zụtara, ma ọ bụrụ nha nha, mgbe ahụ dịka koodu ngwaahịa dị n'ime).
Ọrụ 2.3
Ego ole ka achọrọ ka enye iwu n'aka ndị na-ebubata ya iji mezuo iwu dị ugbu a.
Ka anyị gbasaa mgbagha ngalaba ọzọ:
CLASS Supplier 'Поставщик';
companyName = DATA STRING[100] (Supplier);
supplier = DATA Supplier (Product);
unitsInStock 'Остаток на складе' = DATA NUMERIC[10,3] (Product);
reorderLevel 'Норма продажи' = DATA NUMERIC[10,3] (Product);
Mmekọrịta
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
Na-arụ ọrụ
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;
Nsogbu dị na akara mmuke
Ma ihe atụ ikpeazụ si n’aka mụ onwe m. Enwere ezi uche nke netwọk mmekọrịta. Ndị mmadụ nwere ike ịbụ enyi na ibe ha. Site na nchekwa data na-arụ ọrụ ọ ga-adị ka nke a:
CLASS Person;
likes = DATA BOOLEAN (Person, Person);
friends = DATA BOOLEAN (Person, Person);
Ọ dị mkpa ịchọta ndị nwere ike ime enyi. N'ụzọ doro anya, ịchọrọ ịchọta mmadụ niile A, B, C nke na A bụ enyi B, yana B bụ enyi C, A masịrị C, mana A abụghị enyi na C.
Site na nchekwa data na-arụ ọrụ, ajụjụ a ga-adị ka nke a:
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 na-agba onye na-agụ ume ka ọ dozie nsogbu a na SQL n'onwe ya. A na-eche na e nwere ndị enyi pere mpe karịa ndị masịrị gị. Ya mere, ha nọ na tebụl dị iche iche. Ọ bụrụ na ịga nke ọma, enwerekwa ọrụ nwere kpakpando abụọ. N'ime ya, ọbụbụenyi abụghị ihe atụ. Na nchekwa data na-arụ ọrụ ọ ga-adị ka nke a:
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: ngwọta nke nsogbu na nke mbụ na nke abụọ akara si :
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
nkwubi
Okwesiri iburu n'uche na syntax asusu enyere bu nani otu n'ime nhọrọ maka imejuputa echiche enyere. E weere SQL dị ka ihe ndabere, na ihe mgbaru ọsọ bụ ka ọ dị ka o kwere mee na ya. N'ezie, ụfọdụ nwere ike ọ gaghị amasị aha isiokwu, ndekọ okwu, wdg. Isi ihe ebe a bụ echiche n'onwe ya. Ọ bụrụ na-ịchọrọ, ị nwere ike ịme ma C++ na Python syntax yiri ya.
Echiche nchekwa data akọwara, n'uche nke m, nwere uru ndị a:
- mfe. Nke a bụ ihe nrịba ama nke na-apụtachaghị ìhè n'okwu ndị dị mfe. Ma ọ bụrụ na ị na-eleba anya n'okwu ndị dị mgbagwoju anya (dịka ọmụmaatụ, nsogbu na asterisks), mgbe ahụ, n'echiche m, ịde ajụjụ ndị dị otú ahụ dị mfe karị.
- Tinye. N'ụfọdụ ọmụmaatụ, ekwupụtara m ọrụ etiti (dịka ọmụmaatụ, rere, zụrụ wdg), site na nke e si rụọ ọrụ ndị sochirinụ. Nke a na-enye gị ohere ịgbanwe echiche nke ụfọdụ ọrụ, ma ọ bụrụ na ọ dị mkpa, na-enweghị ịgbanwe echiche nke ndị na-adabere na ha. Dịka ọmụmaatụ, ị nwere ike ịzụ ahịa rere a gbakọrọ site na ihe dị iche iche kpamkpam, ebe mgbagha ndị ọzọ agaghị agbanwe. Ee, enwere ike itinye nke a na RDBMS site na iji CREATE VIEW. Ma ọ bụrụ na e si otú a dee ezi uche nile, ọ gaghị adị ka ihe a pụrụ ịgụ nke ọma.
- Enweghị oghere semantic. Ebe nchekwa data dị otú ahụ na-arụ ọrụ na klaasị (kama tebụl na ubi). Dị ka na mmemme oge gboo (ọ bụrụ na anyị chere na usoro bụ ọrụ na paramita mbụ n'ụdị klas nke ọ bụ). N'ihi ya, ọ ga-adị mfe karị 'imeta enyi' na asụsụ mmemme zuru ụwa ọnụ. Na mgbakwunye, echiche a na-enye ohere ka arụ ọrụ dị mgbagwoju anya karịa ka emejuputa ya. Dịka ọmụmaatụ, ịnwere ike itinye ndị ọrụ dịka:
CONSTRAINT sold(Employee e, 1, 2019) > 100 IF name(e) = 'Петя' MESSAGE 'Что-то Петя продает слишком много одного товара в 2019 году';
- Ihe nketa na polymorphism. Na nchekwa data na-arụ ọrụ, ị nwere ike iwebata ọtụtụ ihe nketa site na CLASS ClassP: Class1, Class2 na-ewu ma mejuputa ọtụtụ polymorphism. Aga m ede otú kpọmkwem n'ime akụkọ n'ọdịnihu.
N'agbanyeghị na nke a bụ naanị echiche, anyị enweelarị ụfọdụ mmejuputa na Java nke na-atụgharị uche niile na-arụ ọrụ na mgbagha mmekọrịta. Na mgbakwunye, mgbagha nke ihe nnọchianya na ọtụtụ ihe ndị ọzọ na-ejikọta ya nke ọma na ya, ekele nke anyị nwetara n'ozuzu ya. . N'ezie, anyị na-eji RDBMS (naanị PostgreSQL maka ugbu a) dị ka "igwe mebere". Nsogbu na-ebilite mgbe ụfọdụ na ntụgharị asụsụ a n'ihi na njikarịcha ajụjụ RDBMS amaghị ụfọdụ ọnụ ọgụgụ FDBMS maara. Na tiori, ọ ga-ekwe omume ịmejuputa usoro nchịkwa nchekwa data nke ga-eji usoro ụfọdụ dị ka nchekwa, na-emegharị kpọmkwem maka mgbagha arụ ọrụ.
isi: www.habr.com