Ko te ao o nga papaa raraunga kua roa e whakahaeretia ana e nga DBMS hononga, e whakamahi ana i te reo SQL. Na te mea ko nga momo rereke ka kiia ko NoSQL. I kaha ratou ki te whakairo i tetahi waahi mo ratou i tenei maakete, engari kaore nga DBMS hononga e mate, ka kaha tonu te whakamahi mo o raatau kaupapa.
I roto i tenei tuhinga e hiahia ana ahau ki te whakaahua i te ariā o te putunga raraunga mahi. Kia pai ake te maarama, ka mahia e au ma te whakatairite ki te tauira whanaungatanga puāwaitanga. Ko nga raruraru mai i nga momo whakamatautau SQL ka kitea i runga i te Ipurangi ka whakamahia hei tauira.
Whakataki
Ka mahi nga papaunga raraunga hononga ki nga ripanga me nga mara. I roto i te patengi raraunga mahi, ka whakamahia nga karaehe me nga mahi. Ka whakaatuhia he mara i roto i te ripanga me nga taviri N hei mahi o nga tawhā N. Engari i nga hononga i waenga i nga ripanga, ka whakamahia nga mahi hei whakahoki i nga taonga o te akomanga i hono ai. Ka whakamahia te tito taumahi hei utu mo JOIN.
I mua i te neke tika ki nga mahi, ka whakaahua ahau i te mahi o te arorau rohe. Mo te DDL ka whakamahi ahau i te whakatakotoranga PostgreSQL. Mo te mahi kei a ia ano te wetereo.
Ripanga me nga mara
He ahanoa Sku ngawari me te ingoa me nga mara utu:
Whanaungatanga
CREATE TABLE Sku
(
id bigint NOT NULL,
name character varying(100),
price numeric(10,5),
CONSTRAINT id_pkey PRIMARY KEY (id)
)
mahi
CLASS Sku;
name = DATA STRING[100] (Sku);
price = DATA NUMERIC[10,5] (Sku);
Ka panui matou e rua mahi, ka tango i tetahi tawhā Sku hei whakaurunga ka whakahoki mai i te momo taketake.
Ko te whakaaro kei roto i te DBMS mahi ka whai waehere o roto o ia ahanoa ka hangaia aunoa, ka taea te uru mena e tika ana.
Me whakarite te utu mo te hua/toa/kaituku. Ka huri pea i roto i te waa, na me taapiri he mara wa ki te ripanga. Ka pekehia e au te whakapuaki i nga ripanga mo nga raarangi i roto i te paataka hononga hei whakapoto i te waehere:
Whanaungatanga
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)
)
mahi
CLASS Sku;
CLASS Store;
CLASS Supplier;
dateTime = DATA DATETIME (Sku, Store, Supplier);
price = DATA NUMERIC[10,5] (Sku, Store, Supplier);
Ngā Tauira
Mo te tauira whakamutunga, ka hangaia e matou he tohu mo nga taviri katoa me te ra kia tere ai te rapu utu mo tetahi wa.
Whanaungatanga
CREATE INDEX prices_date
ON prices
(skuId, storeId, supplierId, dateTime)
mahi
INDEX Sku sk, Store st, Supplier sp, dateTime(sk, st, sp);
mahi
Me timata ma nga raruraru ngawari noa i tangohia mai i nga korero e pa ana
Tuatahi, me whakaatu te arorau rohe (mo te patengi raraunga hononga ka mahia tika i roto i te tuhinga o runga ake nei).
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);
Mahi 1.1
Whakaatuhia he rarangi o nga kaimahi ka whiwhi utu nui ake i to te kaitirotiro tata.
Whanaungatanga
select a.*
from employee a, employee b
where b.id = a.chief_id
and a.salary > b.salary
mahi
SELECT name(Employee a) WHERE salary(a) > salary(chief(a));
Mahi 1.2
Whakarārangihia nga kaimahi e whiwhi ana i te utu nui i roto i to raatau tari
Whanaungatanga
select a.*
from employee a
where a.salary = ( select max(salary) from employee b
where b.department_id = a.department_id )
mahi
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));
He rite nga whakatinanatanga e rua. Mo te keehi tuatahi, ka taea e koe te whakamahi CREATE VIEW i roto i te papaunga raraunga, ma te pera ano ka tatau i te utu morahi mo tetahi tari motuhake kei roto. I nga mea e whai ake nei, mo te maarama, ka whakamahia e au te keehi tuatahi, na te mea he pai ake te whakaata i te otinga.
Mahi 1.3
Whakaatuhia he rarangi o nga ID tari, te maha o nga kaimahi kaore e neke ake i te 3 tangata.
Whanaungatanga
select department_id
from employee
group by department_id
having count(*) <= 3
mahi
countEmployees 'Количество сотрудников' (Department d) =
GROUP SUM 1 IF department(Employee e) = d;
SELECT Department d WHERE countEmployees(d) <= 3;
Mahi 1.4
Whakaatuhia he rarangi o nga kaimahi kaore he kaiwhakahaere kua tohua e mahi ana i te tari kotahi.
Whanaungatanga
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
mahi
SELECT name(Employee a) WHERE NOT (department(chief(a)) = department(a));
Mahi 1.5
Kimihia he rarangi o nga ID tari me te utu nui o nga kaimahi.
Whanaungatanga
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 )
mahi
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();
Me neke atu ki nga mahi uaua mai i tetahi atu
Mahi 2.1
Ko wai nga kaihoko i hoko neke atu i te 1997 nga waahanga o te hua Nama 30 i te tau 1?
Te arorau rohe (pera i mua i runga i te RDBMS ka pekehia e matou te whakapuakitanga):
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);
Whanaungatanga
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
mahi
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;
Mahi 2.2
Mo ia kaihoko (ingoa, ingoa ingoa), kimihia nga taonga e rua (ingoa) i whakapau moni te kaihoko i te tau 1997.
Ka whakawhānuihia e matou te arorau rohe mai i te tauira o mua:
CLASS Customer 'Клиент';
contactName 'ФИО' = DATA STRING[100] (Customer);
customer = DATA Customer (Order);
unitPrice = DATA NUMERIC[14,2] (Detail);
discount = DATA NUMERIC[6,2] (Detail);
Whanaungatanga
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
mahi
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;
Ka mahi te kaiwhakahaere PARTITION i runga i te maapono e whai ake nei: ka whakarapopotohia te korero i tohua i muri i te SUM (konei 1), i roto i nga roopu kua tohua (i konei Kaihoko me te Tau, engari ka taea he kii tetahi), ka tohatoha ki roto i nga roopu ma nga korero kua tohua i roto i te ORDER ( konei hokona, a ki te rite, ka rite ki te waehere hua o roto).
Mahi 2.3
E hia nga taonga me ota mai i nga kaiwhakarato hei whakatutuki i nga ota o naianei.
Kia whakawhānuihia ano te arorau rohe:
CLASS Supplier 'Поставщик';
companyName = DATA STRING[100] (Supplier);
supplier = DATA Supplier (Product);
unitsInStock 'Остаток на складе' = DATA NUMERIC[10,3] (Product);
reorderLevel 'Норма продажи' = DATA NUMERIC[10,3] (Product);
Whanaungatanga
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
mahi
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;
He raru ki te whetūriki
A ko te tauira whakamutunga naku ake. Kei reira te arorau o te whatunga hapori. Ka taea e te tangata te whakahoa ki a raua ano me te pai ki a raua ano. Mai i te tirohanga pātengi raraunga mahi ka penei te ahua:
CLASS Person;
likes = DATA BOOLEAN (Person, Person);
friends = DATA BOOLEAN (Person, Person);
He mea tika ki te kimi i nga kaitono mo te whakahoahoa. Ko te tikanga, me rapu koe i nga tangata katoa A, B, C kia hoa a A ki a B, a B he hoa ki a C, he pai a A ki a C, engari ehara a A i te hoa ki a C.
Mai i te tirohanga pātengi raraunga mahi, ka penei te ahua o te patai:
SELECT Person a, Person b, Person c WHERE
likes(a, c) AND NOT friends(a, c) AND
friends(a, b) AND friends(b, c);
Ka akiakihia te kaipānui ki te whakaoti i tenei raruraru i roto i te SQL i a ia ake. E kiia ana he iti ake nga hoa i nga tangata e pai ana ki a koe. Na reira kei roto i nga tepu motuhake. Ki te angitu, he mahi ano me nga whetu e rua. I roto i te reira, e kore e hangarite te auhoaraa. I runga i te patengi raraunga mahi ka penei te ahua:
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: otinga ki te raruraru me te whetūriki tuatahi me te tuarua mai i
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
mutunga
Me tohu ko te wetereo reo kua homai ko tetahi noa o nga whiringa mo te whakatinana i te ariā kua homai. I tangohia a SQL hei turanga, ko te whainga kia rite ki a ia. Ko te tikanga, kaore pea etahi e pai ki nga ingoa o nga kupu matua, nga rehita kupu, aha atu. Ko te mea nui i konei ko te kaupapa ake. Ki te hiahia, ka taea e koe te hanga C++ me te Python te wetereo rite.
Ko te ariā pātengi raraunga kua whakaahuatia, ki taku whakaaro, ko nga painga e whai ake nei:
- mamatanga. He tohu tino kaupapa tenei kaore e kitea i roto i nga keehi ngawari. Engari ki te titiro koe ki nga keehi uaua ake (hei tauira, nga raru me nga whetūriki), na, ki taku whakaaro, he maamaa ake te tuhi i aua patai.
- Tuhinga. I etahi tauira i kii ahau i nga mahi takawaenga (hei tauira, hokona, hokona etc.), i hangaia ai nga mahi o muri mai. Ma tenei ka taea e koe te whakarereke i te arorau o etahi mahi, mehemea e tika ana, me te kore e whakarereke i te arorau o te hunga e whakawhirinaki ana ki a raatau. Hei tauira, ka taea e koe te hoko hoko hokona i tatauhia mai i nga mea rereke katoa, ko te toenga o te arorau kaore e huri. Ae, ka taea te whakatinana i roto i te RDBMS ma te whakamahi i te WHAKAMAHI WHAKAMAHI. Engari mena ka tuhia nga whakaaro katoa penei, kaore e tino kitea te panui.
- Karekau he mokowhiti kupu. Ko taua putunga korero e mahi ana i runga i nga mahi me nga karaehe (hei utu mo nga ripanga me nga mara). Pērā anō i roto i te hōtaka puāwaitanga (mehemea ka whakaaro tatou he mahi te tikanga me te tawhā tuatahi i roto i te ahua o te karaehe kei a ia). No reira, he maamaa ake te "whakahoa" ki nga reo hotaka o te ao. I tua atu, ka taea e tenei ariā te whakatinana i nga mahi uaua ake. Hei tauira, ka taea e koe te whakauru i nga kaiwhakahaere penei:
CONSTRAINT sold(Employee e, 1, 2019) > 100 IF name(e) = 'Петя' MESSAGE 'Что-то Петя продает слишком много одного товара в 2019 году';
- Te tuku iho me te polymorphism. I roto i te patengi raraunga mahi, ka taea e koe te whakauru i nga taonga tuku iho maha ma te CLASS ClassP: Class1, Class2 hanga me te whakatinana i te polymorphism maha. Ka tuhi pea au me pehea i roto i nga tuhinga a muri ake nei.
Ahakoa he ariā noa tenei, kei a maatau etahi whakatinanatanga i Java e whakamaori ana i nga arorau mahi katoa ki te arorau hononga. I tua atu, ko te arorau o nga whakakitenga me te maha atu o nga mea e piri ana ki a ia, na reira ka whiwhi tatou i te katoa.
Source: will.com