Lub ntiaj teb ntawm cov ntaub ntawv tau ntev tau tswj hwm los ntawm kev sib raug zoo DBMSs, uas siv cov lus SQL. Ntau npaum li cov kev hloov pauv tshiab hu ua NoSQL. Lawv tau tswj xyuas qhov chaw rau lawv tus kheej hauv kev ua lag luam no, tab sis kev sib raug zoo DBMSs yuav tsis tuag, thiab txuas ntxiv mus siv rau lawv lub hom phiaj.
Hauv tsab xov xwm no kuv xav piav qhia txog lub tswv yim ntawm kev ua haujlwm database. Rau kev nkag siab zoo dua, kuv yuav ua qhov no los ntawm kev sib piv nrog cov qauv kev sib raug zoo classical. Teeb meem los ntawm ntau yam kev xeem SQL pom hauv Is Taws Nem yuav raug siv ua piv txwv.
Taw qhia
Relational databases ua haujlwm ntawm rooj thiab teb. Hauv cov ntaub ntawv siv tau, cov chav kawm thiab cov haujlwm yuav raug siv hloov, raws li. Ib daim teb nyob rau hauv ib lub rooj nrog N yuam sij yuav sawv cev raws li ib tug muaj nuj nqi ntawm N tsis. Hloov chaw ntawm kev sib raug zoo ntawm cov ntxhuav, cov haujlwm yuav raug siv uas rov qab cov khoom ntawm cov chav kawm uas qhov kev sib txuas tau ua. Muaj nuj nqi muaj pes tsawg leeg yuav raug siv los ntawm JOIN.
Ua ntej txav ncaj qha mus rau cov dej num, kuv yuav piav qhia txog txoj haujlwm ntawm kev sau logic. Rau DDL Kuv yuav siv PostgreSQL syntax. Rau functional nws muaj nws tus kheej syntax.
Rooj thiab teb
Ib qho yooj yim Sku khoom nrog lub npe thiab nqi teb:
Kev txheeb ze
CREATE TABLE Sku
(
id bigint NOT NULL,
name character varying(100),
price numeric(10,5),
CONSTRAINT id_pkey PRIMARY KEY (id)
)
ua haujlwm
CLASS Sku;
name = DATA STRING[100] (Sku);
price = DATA NUMERIC[10,5] (Sku);
Peb tshaj tawm ob zog, uas siv ib qho parameter Sku raws li cov tswv yim thiab xa rov qab hom qub.
Nws yog assumed tias nyob rau hauv ib tug haumxeeb DBMS txhua yam khoom yuav muaj ib co sab hauv code uas yog cia li generated thiab tuaj yeem nkag tau yog tias tsim nyog.
Cia peb teeb tus nqi rau cov khoom / khw / chaw muag khoom. Tej zaum nws yuav hloov lub sij hawm, yog li cia peb ntxiv ib lub sij hawm teb rau lub rooj. Kuv yuav hla kev tshaj tawm cov lus rau cov npe hauv cov ntaub ntawv sib txheeb kom luv cov cai:
Kev txheeb ze
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)
)
ua haujlwm
CLASS Sku;
CLASS Store;
CLASS Supplier;
dateTime = DATA DATETIME (Sku, Store, Supplier);
price = DATA NUMERIC[10,5] (Sku, Store, Supplier);
Indexes
Rau qhov piv txwv kawg, peb yuav tsim qhov ntsuas ntawm txhua tus yuam sij thiab hnub kom peb tuaj yeem nrhiav tus nqi sai rau lub sijhawm tshwj xeeb.
Kev txheeb ze
CREATE INDEX prices_date
ON prices
(skuId, storeId, supplierId, dateTime)
ua haujlwm
INDEX Sku sk, Store st, Supplier sp, dateTime(sk, st, sp);
paub tab
Cia peb pib nrog cov teeb meem yooj yim coj los ntawm qhov sib thooj
Ua ntej, cia peb tshaj tawm cov logic sau npe (rau cov ntaub ntawv sib raug zoo no yog ua ncaj qha rau hauv kab lus saum toj no).
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);
Ua haujlwm 1.1
Tso cov npe ntawm cov neeg ua haujlwm uas tau txais nyiaj hli ntau dua li ntawm lawv tus thawj saib xyuas tam sim ntawd.
Kev txheeb ze
select a.*
from employee a, employee b
where b.id = a.chief_id
and a.salary > b.salary
ua haujlwm
SELECT name(Employee a) WHERE salary(a) > salary(chief(a));
Ua haujlwm 1.2
Sau cov neeg ua haujlwm uas tau txais nyiaj hli siab tshaj plaws hauv lawv chav haujlwm
Kev txheeb ze
select a.*
from employee a
where a.salary = ( select max(salary) from employee b
where b.department_id = a.department_id )
ua haujlwm
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));
Ob qhov kev siv yog sib npaug. Rau thawj rooj plaub, nyob rau hauv cov ntaub ntawv sib raug zoo koj tuaj yeem siv CREATE VIEW, uas tib txoj kev yuav ua ntej xam cov nyiaj hli siab tshaj plaws rau ib chav haujlwm tshwj xeeb hauv nws. Hauv dab tsi hauv qab no, kom pom tseeb, kuv yuav siv thawj rooj plaub, vim nws zoo dua qhia txog kev daws teeb meem.
Ua haujlwm 1.3
Muab cov npe ntawm cov chaw ua haujlwm ID, cov neeg ua haujlwm uas tsis pub tshaj 3 tus neeg.
Kev txheeb ze
select department_id
from employee
group by department_id
having count(*) <= 3
ua haujlwm
countEmployees 'ΠΠΎΠ»ΠΈΡΠ΅ΡΡΠ²ΠΎ ΡΠΎΡΡΡΠ΄Π½ΠΈΠΊΠΎΠ²' (Department d) =
GROUP SUM 1 IF department(Employee e) = d;
SELECT Department d WHERE countEmployees(d) <= 3;
Ua haujlwm 1.4
Tso tawm cov npe ntawm cov neeg ua haujlwm uas tsis muaj tus thawj tswj hwm uas ua haujlwm hauv tib lub tuam tsev.
Kev txheeb ze
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
ua haujlwm
SELECT name(Employee a) WHERE NOT (department(chief(a)) = department(a));
Ua haujlwm 1.5
Nrhiav cov npe ntawm department IDs nrog cov nyiaj hli siab tshaj plaws ntawm cov neeg ua haujlwm.
Kev txheeb ze
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 )
ua haujlwm
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();
Cia peb txav mus rau ntau txoj haujlwm nyuaj los ntawm lwm qhov
Ua haujlwm 2.1
Cov neeg muag khoom twg muag ntau tshaj 1997 units ntawm cov khoom No.30 hauv 1?
Domain logic (raws li ua ntej ntawm RDBMS peb hla cov lus tshaj tawm):
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);
Kev txheeb ze
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
ua haujlwm
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;
Ua haujlwm 2.2
Rau txhua tus neeg yuav khoom (npe, npe), nrhiav ob yam khoom (npe) uas tus neeg yuav khoom siv nyiaj ntau tshaj plaws hauv xyoo 1997.
Peb txuas ntxiv lub logic ntawm cov piv txwv yav dhau los:
CLASS Customer 'ΠΠ»ΠΈΠ΅Π½Ρ';
contactName 'Π€ΠΠ' = DATA STRING[100] (Customer);
customer = DATA Customer (Order);
unitPrice = DATA NUMERIC[14,2] (Detail);
discount = DATA NUMERIC[6,2] (Detail);
Kev txheeb ze
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
ua haujlwm
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;
Tus neeg teb xov tooj PARTITION ua haujlwm raws li cov hauv qab no: nws suav cov lus qhia tom qab SUM (ntawm no 1), nyob rau hauv cov pab pawg tau teev tseg (ntawm no Cov Neeg Siv Khoom thiab Xyoo, tab sis tuaj yeem yog ib qho kev qhia), sorting hauv cov pab pawg los ntawm cov kab lus teev tseg hauv ORDER ( ntawm no yuav, thiab yog tias sib npaug, ces raws li cov khoom siv sab hauv).
Ua haujlwm 2.3
Muaj pes tsawg cov khoom yuav tsum tau xaj los ntawm cov neeg muag khoom kom ua tiav cov kev txiav txim tam sim no.
Cia peb nthuav lub ntsiab lus ntxiv dua:
CLASS Supplier 'ΠΠΎΡΡΠ°Π²ΡΠΈΠΊ';
companyName = DATA STRING[100] (Supplier);
supplier = DATA Supplier (Product);
unitsInStock 'ΠΡΡΠ°ΡΠΎΠΊ Π½Π° ΡΠΊΠ»Π°Π΄Π΅' = DATA NUMERIC[10,3] (Product);
reorderLevel 'ΠΠΎΡΠΌΠ° ΠΏΡΠΎΠ΄Π°ΠΆΠΈ' = DATA NUMERIC[10,3] (Product);
Kev txheeb ze
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
ua haujlwm
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;
Teeb meem nrog lub hnub qub
Thiab qhov piv txwv kawg yog los ntawm kuv tus kheej. Muaj lub logic ntawm lub social network. Tib neeg tuaj yeem ua phooj ywg nrog ib leeg thiab nyiam ib leeg. Los ntawm kev ua haujlwm database foundations nws yuav zoo li no:
CLASS Person;
likes = DATA BOOLEAN (Person, Person);
friends = DATA BOOLEAN (Person, Person);
Nws yog qhov tsim nyog los nrhiav cov neeg sib tw ua phooj ywg. Ua ntej tshaj plaws, koj yuav tsum nrhiav txhua tus neeg A, B, C xws li A yog phooj ywg nrog B, thiab B yog phooj ywg nrog C, A nyiam C, tab sis A tsis yog phooj ywg nrog C.
Los ntawm kev ua haujlwm database foundations, cov lus nug yuav zoo li no:
SELECT Person a, Person b, Person c WHERE
likes(a, c) AND NOT friends(a, c) AND
friends(a, b) AND friends(b, c);
Tus nyeem ntawv txhawb kom daws tau qhov teeb meem no hauv SQL ntawm nws tus kheej. Nco ntsoov tias muaj cov phooj ywg tsawg dua li cov neeg koj nyiam. Yog li ntawd lawv nyob hauv cov rooj sib cais. Yog tias ua tiav, kuj tseem muaj txoj haujlwm nrog ob lub hnub qub. Nyob rau hauv nws, kev phooj ywg tsis yog symmetrical. Nyob rau hauv lub functional database nws yuav zoo li no:
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: kev daws teeb meem nrog lub hnub qub thawj thiab thib ob los ntawm
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
xaus
Nws yuav tsum tau muab sau tseg tias cov lus syntax tsuas yog ib qho ntawm cov kev xaiv rau kev siv lub tswv yim muab. SQL tau coj los ua lub hauv paus, thiab lub hom phiaj yog rau nws kom zoo ib yam li qhov ua tau rau nws. Tau kawg, qee tus yuav tsis nyiam cov npe ntawm cov ntsiab lus, cov ntawv sau npe, thiab lwm yam. Qhov tseem ceeb ntawm no yog lub tswvyim nws tus kheej. Yog xav tau, koj tuaj yeem ua C ++ thiab Python zoo sib xws syntax.
Cov ntsiab lus piav qhia database, hauv kuv lub tswv yim, muaj qhov zoo hauv qab no:
- Qhov Yooj Yim. Qhov no yog ib qho kev qhia tseem ceeb uas tsis pom tseeb hauv cov xwm txheej yooj yim. Tab sis yog tias koj saib cov teeb meem nyuaj (piv txwv li, teeb meem nrog lub hnub qub), hauv kuv lub tswv yim, sau cov lus nug no yooj yim dua.
- Tshawb nrhiav. Hauv qee qhov piv txwv kuv tau tshaj tawm cov haujlwm nruab nrab (piv txwv li, muag, yuav thiab lwm yam), los ntawm cov haujlwm tom ntej tau tsim. Qhov no tso cai rau koj hloov lub logic ntawm qee yam haujlwm, yog tias tsim nyog, tsis tas yuav hloov lub logic ntawm cov uas nyob ntawm lawv. Piv txwv li, koj tuaj yeem ua kev muag khoom muag raug xam los ntawm cov khoom sib txawv kiag li, thaum tus so ntawm cov logic yuav tsis hloov. Yog lawm, qhov no tuaj yeem ua tiav hauv RDBMS siv CREATE VIEW. Tab sis yog hais tias tag nrho cov logic yog sau li no, nws yuav tsis zoo nyeem heev.
- Tsis muaj qhov sib txawv ntawm semantic. Cov ntaub ntawv zoo li no ua haujlwm ntawm cov haujlwm thiab cov chav kawm (tsis yog cov rooj thiab cov teb). Ib yam li nyob rau hauv classical programming (yog hais tias peb xav hais tias ib txoj kev yog ib tug muaj nuj nqi nrog rau cov thawj parameter nyob rau hauv daim ntawv ntawm cov chav kawm ntawv uas nws belongs). Raws li, nws yuav tsum yooj yim dua rau "ua phooj ywg" nrog cov lus programming thoob ntiaj teb. Tsis tas li ntawd, lub tswv yim no tso cai rau kev siv ntau yam kev ua haujlwm nyuaj. Piv txwv li, koj tuaj yeem embed tus neeg teb xov tooj xws li:
CONSTRAINT sold(Employee e, 1, 2019) > 100 IF name(e) = 'ΠΠ΅ΡΡ' MESSAGE 'Π§ΡΠΎ-ΡΠΎ ΠΠ΅ΡΡ ΠΏΡΠΎΠ΄Π°Π΅Ρ ΡΠ»ΠΈΡΠΊΠΎΠΌ ΠΌΠ½ΠΎΠ³ΠΎ ΠΎΠ΄Π½ΠΎΠ³ΠΎ ΡΠΎΠ²Π°ΡΠ° Π² 2019 Π³ΠΎΠ΄Ρ';
- Keeb kwm thiab polymorphism. Hauv cov ntaub ntawv ua haujlwm, koj tuaj yeem qhia ntau yam qub txeeg qub teg los ntawm CLASS ClassP: Class1, Class2 tsim thiab siv ntau yam polymorphism. Tej zaum kuv yuav sau raws nraim li cas hauv cov ntawv yav tom ntej.
Txawm hais tias qhov no tsuas yog lub tswv yim xwb, peb twb muaj qee qhov kev siv hauv Java uas txhais tag nrho cov kev ua haujlwm rau hauv kev sib raug zoo logic. Ntxiv rau, lub logic ntawm cov neeg sawv cev thiab ntau ntau lwm yam zoo nkauj txuas rau nws, ua tsaug uas peb tau txais tag nrho.
Tau qhov twg los: www.hab.com