🥇Inoshanda DBMS

Nyika yemadhatabhesi yagara ichitongwa nehukama maDBMS, ayo anoshandisa mutauro weSQL. Zvakawanda zvekuti akasiyana ari kubuda anonzi NoSQL. Vakakwanisa kuzvivezera imwe nzvimbo mumusika uyu, asi maDBMS ehukama haasi kuzofa, uye anoenderera mberi achishandiswa zvakanyanya pazvinangwa zvavo.

Muchikamu chino ndinoda kutsanangura pfungwa ye database inoshanda. Kuti ndinzwisise zviri nani, ndichaita izvi nekuzvienzanisa neiyo classical relational modhi. Matambudziko kubva kune akasiyana SQL bvunzo anowanikwa paInternet achashandiswa semuenzaniso.

Nhanganyaya

Relational databases inoshanda pamatafura neminda. Mune database inoshanda, makirasi uye mabasa achashandiswa pachinzvimbo, zvichiteerana. Munda uri mutafura ine makiyi eN uchamiririrwa sebasa reN paramita. Panzvimbo pehukama pakati pematafura, mabasa achashandiswa anodzosera zvinhu zvekirasi iyo kubatana kunogadzirwa. Kuumbwa kuchashandiswa pachinzvimbo cheJOIN.

Ndisati ndaenda zvakananga kumabasa, ini ndichatsanangura basa reiyo domain logic. Kune DDL ini ndichashandisa PostgreSQL syntax. Zvekushanda zvine syntax yayo.

Matafura neminda

Chinhu chakareruka cheSku chine zita neminda yemitengo:

Relational

CREATE TABLE Sku
(
    id bigint NOT NULL,
    name character varying(100),
    price numeric(10,5),
    CONSTRAINT id_pkey PRIMARY KEY (id)
)

functional

CLASS Sku; name = DATA STRING[100] (Sku); price = DATA NUMERIC[10,5] (Sku);

Tinozivisa maviri mabasa, iyo inotora imwe parameter Sku sekuisa uye kudzosa rudzi rwechinyakare.

Zvinofungidzirwa kuti mune inoshanda DBMS chinhu chimwe nechimwe chichava neimwe kodhi yemukati inogadzirwa otomatiki uye inogona kuwanikwa kana zvichidikanwa.

Ngatiisei mutengo wechigadzirwa/chitoro/mutengesi. Inogona kuchinja nekufamba kwenguva, saka ngatiwedzerei ndima yenguva patafura. Ini ndichasvetuka kuzivisa matafura edhairekitori mune yehukama dhatabhesi kupfupisa iyo kodhi:

Relational

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)
)

functional

CLASS Sku; CLASS Store; CLASS Supplier; dateTime = DATA DATETIME (Sku, Store, Supplier); price = DATA NUMERIC[10,5] (Sku, Store, Supplier);

Indexes

Pamuenzaniso wekupedzisira, tichavaka indekisi pamakiyi ese uye zuva kuitira kuti tigone kukurumidza kuwana mutengo wenguva yakatarwa.

Relational

CREATE INDEX prices_date
    ON prices
    (skuId, storeId, supplierId, dateTime)

functional

INDEX Sku sk, Store st, Supplier sp, dateTime(sk, st, sp);

mabasa

Ngatitange nematambudziko ari nyore akatorwa kubva kune anowirirana zvinyorwa pana Habr.

Kutanga, ngatizivise iyo domain logic (yeiyo hukama dhatabhesi izvi zvinoitwa zvakananga muchinyorwa chiri pamusoro).

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);

Basa 1.1

Ratidza runyoro rwevashandi vanotambira muhoro wakakura kupfuura wemukuru wavo wepamusoro.

Relational

select a.*
from   employee a, employee b
where  b.id = a.chief_id
and    a.salary > b.salary

functional

SELECT name(Employee a) WHERE salary(a) > salary(chief(a));

Basa 1.2

Nyora vashandi vanogashira mari yakawanda mudhipatimendi ravo

Relational

select a.*
from   employee a
where  a.salary = ( select max(salary) from employee b
                    where  b.department_id = a.department_id )

functional

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));

Zvose zviri zviviri zvinoshandiswa zvakaenzana. Panyaya yekutanga, mune dhatabhesi rehukama unogona kushandisa CREATE VIEW, iyo nenzira imwecheteyo ichatanga kuverenga iyo yakanyanya muhoro wedhipatimendi rakati mariri. Mune zvinotevera, kujekesa, ini ndichashandisa yekutanga kesi, sezvo ichiratidza zviri nani mhinduro.

Basa 1.3

Ratidza rondedzero yedhipatimendi ID, nhamba yevashandi umo isingapfuure vanhu vatatu.

Relational

select department_id
from   employee
group  by department_id
having count(*) <= 3

functional

countEmployees 'Количество сотрудников' (Department d) = GROUP SUM 1 IF department(Employee e) = d; SELECT Department d WHERE countEmployees(d) <= 3;

Basa 1.4

Ratidza runyoro rwevashandi vasina maneja akasarudzwa anoshanda mudhipatimendi rimwe chete.

Relational

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

functional

SELECT name(Employee a) WHERE NOT (department(chief(a)) = department(a));

Basa 1.5

Tsvaga rondedzero yedhipatimendi ID ine muhoro wakakwana wevashandi.

Relational

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 )

functional

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();

Ngatienderere mberi kune mamwe mabasa akaoma kubva kune rimwe zvinyorwa. Iine ongororo yakadzama yekuti ungaite sei basa iri muMS SQL.

Basa 2.1

Ndevapi vatengesi vakatengesa zvinopfuura makumi matatu ezvikamu zvechigadzirwa Nhamba 1997 muna 30?

Domain logic (sepakutanga paRDBMS tinodarika chiziviso):

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);

Relational

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

functional

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;

Basa 2.2

Kune mutengi wega wega (zita, surname), tsvaga zvinhu zviviri (zita) izvo mutengi akashandisa mari yakawanda muna 1997.

Isu tinowedzera iyo domain logic kubva kune yapfuura muenzaniso:

CLASS Customer 'Клиент'; contactName 'ФИО' = DATA STRING[100] (Customer);


customer = DATA Customer (Order);

unitPrice = DATA NUMERIC[14,2] (Detail); discount = DATA NUMERIC[6,2] (Detail);

Relational

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

functional

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;

Mutengi wePARTITION anoshanda panheyo inotevera: inopfupikisa chirevo chakataurwa mushure meSUM (pano 1), mukati memapoka akatsanangurwa (pano Mutengi uye Gore, asi anogona kuve chero kutaura), kuronga mukati memapoka nezvirevo zvakatsanangurwa muORDER ( pano yakatengwa, uye kana yakaenzana, saka maererano nekodhi yechigadzirwa chemukati).

Basa 2.3

Izvinhu zvingani zvinoda kuodha kubva kune vatengesi kuti vazadzise maodha azvino.

Ngatiwedzerei domain logic zvakare:

CLASS Supplier 'Поставщик'; companyName = DATA STRING[100] (Supplier);


supplier = DATA Supplier (Product);

unitsInStock 'Остаток на складе' = DATA NUMERIC[10,3] (Product); reorderLevel 'Норма продажи' = DATA NUMERIC[10,3] (Product);

Relational

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

functional

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;

Dambudziko neasterisk

Uye muenzaniso wekupedzisira unobva kwandiri pachangu. Pane pfungwa yesocial network. Vanhu vanogona kushamwaridzana uye kufanana nemumwe. Kubva pane inoshanda dhatabhesi maonero angaite seizvi:

CLASS Person; likes = DATA BOOLEAN (Person, Person); friends = DATA BOOLEAN (Person, Person);

Izvo zvinodiwa kuti uwane vanogona kukwikwidza kushamwari. Zvikuru zviri pamutemo, unofanirwa kutsvaga vanhu vese A, B, C zvekuti A ishamwari naB, uye B ishamwari naC, A anoda C, asi A haasi shamwari naC.
Kubva pane inoshanda dhatabhesi maonero, iwo mubvunzo waizotaridzika seizvi:

SELECT Person a, Person b, Person c WHERE likes(a, c) AND NOT friends(a, c) AND friends(a, b) AND friends(b, c);

Muverengi anokurudzirwa kugadzirisa dambudziko iri muSQL ari oga. Zvinofungidzirwa kuti kune shamwari shoma pane vanhu vaunoda. Naizvozvo vari mumatafura akasiyana. Kana ikabudirira, panewo basa rine nyeredzi mbiri. Mariri, ushamwari hauna kuenzana. Pa database inoshanda inoita seizvi:

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: mhinduro kudambudziko neyekutanga neyechipiri asterisk kubva dss_kalika:

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

mhedziso

Zvinofanira kucherechedzwa kuti syntax yemutauro yakapihwa ingori imwe yesarudzo dzekushandisa pfungwa yakapihwa. SQL yakatorwa sehwaro, uye chinangwa chaive chekuti ive yakafanana sezvinobvira kwairi. Ehe, vamwe vangasafarira mazita emazwi akakosha, marejista emazwi, nezvimwe. Chinhu chikuru apa ndiyo pfungwa yacho pachayo. Kana uchida, unogona kugadzira ese C ++ uye Python yakafanana syntax.

Iyo yakatsanangurwa dhatabhesi pfungwa, mumaonero angu, ine zvinotevera zvakanakira:

unyore. Ichi chiratidziro chinomiririra icho chisiri pachena mumakesi akareruka. Asi kana iwe ukatarisa mamwe matambudziko akaoma (somuenzaniso, matambudziko ane asterisks), saka, mumaonero angu, kunyora mibvunzo yakadaro iri nyore.
Инкапсуляция. Mune mimwe mienzaniso ndakazivisa mabasa epakati (semuenzaniso, akatengeswa, akatengwa etc.), kubva pakavakirwa mabasa anotevera. Izvi zvinokutendera kuti uchinje logic yemamwe mabasa, kana zvichidikanwa, pasina kushandura logic yeavo vanovimba navo. Semuenzaniso, unogona kuita kutengesa akatengeswa akaverengerwa kubva kuzvinhu zvakasiyana zvachose, nepo mamwe ese epfungwa haashanduke. Ehe, izvi zvinogona kuitwa muRDBMS uchishandisa CREATE VIEW. Asi kana zvese zvine musoro zvakanyorwa nenzira iyi, hazvizotaridzi kuverengeka zvakanyanya.
Hapana gap semantic. Dhatabhesi yakadaro inoshanda pamabasa uye makirasi (panzvimbo yematafura neminda). Sezvakangoita mukirasi yepurogiramu (kana isu tichifungidzira kuti nzira ibasa ine yekutanga parameter muchimiro chekirasi yairi). Saizvozvo, zvinofanirwa kuve zviri nyore "kuita shamwari" nemitauro yepasirese yekuronga. Uyezve, pfungwa iyi inobvumira kuti zvakanyanya kuoma kushanda kuti zviitwe. Semuenzaniso, unogona kunyudza vashandisi se:
CONSTRAINT sold(Employee e, 1, 2019) > 100 IF name(e) = 'Петя' MESSAGE 'Что-то Петя продает слишком много одного товара в 2019 году';
Nhaka uye polymorphism. Mune dhatabhesi inoshanda, unogona kuunza akawanda nhaka kuburikidza neCLASS KirasiP: Kirasi1, Kirasi2 inovaka uye kuita akawanda polymorphism. Ini zvimwe ndichanyora sei chaizvo mune ramangwana zvinyorwa.

Kunyangwe iyi ingori pfungwa, isu tatova nekumwe kuita muJava iyo inoshandura ese anoshanda mantiki kuita hukama hwehukama. Uyezve, iyo pfungwa yekumiririra uye zvimwe zvakawanda zvezvimwe zvinhu zvakanamirwa pairi, nekuda kwatinowana yakazara. chikuva. Chaizvoizvo, isu tinoshandisa iyo RDBMS (chete PostgreSQL ikozvino) se "chaiyo muchina". Matambudziko dzimwe nguva anomuka neshanduro iyi nekuti iyo RDBMS query optimizer haizive dzimwe nhamba dzinozivikanwa neFDBMS. Mune dzidziso, zvinogoneka kuita dhatabhesi manejimendi system iyo inoshandisa imwe chimiro sekuchengetedza, yakagadziridzwa yakanangana nekushanda kwepfungwa.

Source: www.habr.com

Inoshanda DBMS

Nhanganyaya

Matafura neminda

Indexes

mabasa

Basa 1.1

Basa 1.2

Basa 1.3

Basa 1.4

Basa 1.5

Basa 2.1

Basa 2.2

Basa 2.3

Dambudziko neasterisk

mhedziso

Voeg kukanzura mhinduro