DB pamati

Atjaunots: 2025-01-06

Tālāk ir apkopotas pamata zināšanas par datubāzes modelēšanu, izveidošanu un lietošanu.

Saturs

Datubāze ir strukturēts datu kopums, kas tiek glabāts elektroniski. Datubāzes tiek izmantotas, lai efektīvi uzglabātu, organizētu un izgūtu lielus datu apjomus. Tās ir būtiska daudzu mūsdienu lietojumprogrammu sastāvdaļa, jo ļauj lietojumprogrammai uzglabāt un piekļūt datiem strukturētā un organizētā veidā.

Pastāv daudz dažādu datubāzu veidu, tostarp relāciju (relational) datubāzes, bez-relāciju (non-relational) datubāzes, plakanās datubāzes un citas. Katram datubāzu veidam ir savas stiprās puses, un tās ir piemērotas dažādiem lietojumprogrammu veidiem.

Relāciju datubāzēs, piemēram, MySQL, SQLite un PostgreSQL, dati tiek glabāti tabulās, kas ir savstarpēji saistītas ar attiecībām. Bez-relāciju datubāzes, piemēram, MongoDB un Cassandra, glabā datus elastīgākā, uz dokumentiem orientētā formātā. Plakano failu datubāzēs, piemēram, CSV datnēs, dati tiek glabāti vienkāršā, uz tekstu balstītā formātā.

Datubāzēs var glabāt visu veidu datus, tostarp tekstu, skaitļus, attēlus un citus. Tās var izmantot, lai uzglabātu datus visdažādākajām lietojumprogrammām, tostarp tiešsaistes veikaliem, sociālo plašsaziņas līdzekļu lietojumprogrammām un citām lietojumprogrammām.

Lai mijiedarbotos ar datubāzi, lietojumprogrammās tiek izmantota datubāzes pārvaldības sistēma (DBVS), kas ir programmatūra, kura ir īpaši izstrādāta darbam ar datubāzēm. DBVS nodrošina rīkus datubāzes izveidei un pārvaldībai, kā arī mijiedarbībai ar datubāzē saglabātajiem datiem.

Relāciju (relational) un bez-relāciju (non-relational) datubāzes

Relācijas datubāzes un bez-relāciju datubāzes ir divi galvenie datubāzu veidi, ko izmanto datu glabāšanai un pārvaldīšanai.

Relācijas datubāzes

Relāciju datubāzēs, piemēram, MySQL, SQLite un PostgreSQL, dati tiek glabāti tabulās, kas ir savstarpēji saistītas ar attiecībām. Šīs attiecības tiek definētas, izmantojot ārējās atslēgas, kas ir lauki, kuri atsaucas uz citas tabulas primāro atslēgu.

Piemēram, aplūkojot bibliotēkas datubāzi, kurā ir tabulas grāmatām, autoriem un lasītājiem. Grāmatu tabulā būtu ārējā atslēga, kas atsaucas uz autora primāro atslēgu autoru tabulā. Tas ļauj datubāzei uzglabāt informāciju par katras grāmatas autoru un pēc vajadzības iegūt šo informāciju.

Grāmatu, autora, lasītāju tabulas:

Book_ID	Title	Author_ID
06Mke70257auAY46SFI3	Mātes piens	21EYg5vXCEKwYDEXEl4W
UOsCJK1YLWqIZpMp69rj	Pieci pirksti	VGnwLKM6UzM7qY9JDnJA
1xhx5CEkd2eEkghtl1qU	Nāve - tās nav beigas	3yi5PG8ZSWIxjL1GsiGA

Author_ID	Name
21EYg5vXCEKwYDEXEl4W	Nora Ikstena
VGnwLKM6UzM7qY9JDnJA	Māra Zālīte
3yi5PG8ZSWIxjL1GsiGA	Nils Sakss

Reader_ID	Name	Email
4R27VVb5Mpww8Gsfn25u	Jānis	[email protected]
5coeAlXySxDnN6teH38o	Rihards	[email protected]
Sq9klqPmDmVjELrUfS3b	Santa	[email protected]

Relāciju datubāzes ir strukturētas un organizētas, un, lai mijiedarbotos ar datiem, tās izmanto SQL (strukturēto vaicājumu valodu). Tās ir labi piemērotas lietojumprogrammām, kurās nepieciešami strukturēti dati un sarežģīti pieprasījumi.

Bez-relāciju datubāzes

Bez-relāciju datubāzes, piemēram, MongoDB un Cassandra, glabā datus elastīgākā, uz dokumentiem orientētā formātā. Tā vietā, lai datus uzglabātu tabulās ar rindām un stabiņiem, bez-relāciju datubāzes datus glabā kā dokumentus, kuriem var būt jebkāda struktūra un kuri var saturēt ieliktu informāciju.

Piemēram, aplūkojiet sociālo mediju lietojumprogrammas datubāzi, kurā ir lietotāju kolekcija. Katrs lietotāja dokuments varētu saturēt informāciju par lietotāja vārdu, e-pasta adresi un draugu sarakstu, kā arī citus datus, piemēram, ziņas un komentārus.

users/
      A22WVcD4Mpww8Gsfn25u/
                            name: "Rinalds"
                            email: '[email protected]'
                            accountCreated: 2022-02-01
                            numberOfFriends: 2
      AcSeS12ySxDnN6teTveo/
                            name: "Rolands"
                            email: '[email protected]'
                            accountCreated: 2022-02-03
                            numberOfFriends: 3
                            isActive: true
                            hasPosts: false
                            subscribed: ['groups', 'market']

Bez-relāciju datubāzes ir elastīgākas nekā relācijas datubāzes, taču tās var nebūt tik labi piemērotas lietojumprogrammām, kurās nepieciešamas sarežģītas datu savstarpējās attiecības vai kurās jāveic sarežģīti pieprasījumi. Tās bieži tiek izmantotas lietojumprogrammās, kurās nepieciešama ātra lasīšanas un rakstīšanas veiktspēja un kurās tiek apstrādāts liels daudzums nestrukturētu datu.

Galvenā atšķirība starp relāciju un bez-relāciju datubāzēm ir veids, kā tās glabā un organizē datus. Relāciju datubāzēs izmanto tabulas un sakarības, savukārt bez-relāciju datubāzēs izmanto dokumentus un kolekcijas. Katram datubāzes veidam ir savas stiprās puses, un tas ir piemērots dažādiem lietojumu veidiem.

Turmpāk detalizētāk tiks apskatīta relāciju datubāzes struktūra.

Datubāzes struktūra

Datubāzes struktūra attiecas uz veidu, kā dati tiek organizēti un glabāti datubāzē. Datubāzes struktūra parasti sastāv no tabulām, rindām un kolonnām.

Tabulas ir datubāzes organizācijas pamatvienības. Tajās ir datu rindas, un katrai rindai ir kolonnu kopums, kurās tiek glabāta dažāda informācija. Piemēram, tiešsaistes veikala datubāzē var būt tabula ar informāciju par klientiem, kurā ir slejas ar klienta vārdu, e-pasta adresi un norēķinu adresi.

Rindas ir atsevišķi ieraksti tabulā. Katra rinda ir unikāla vienība, piemēram, klients vai produkts. Tabulas kolonnās tiek saglabāti katras rindas dati. Piemēram, klientu tabulā katra rinda var pārstāvēt atšķirīgu klientu, un kolonnās tiek saglabāta tāda informācija kā klienta vārds, e-pasta adrese un norēķinu adrese.

Kolonnas ir tabulas lauki, kuros tiek saglabāti konkrēti dati. Katrai kolonnai ir nosaukums un datu tips, kas norāda datu veidu, ko var saglabāt kolonnā. Piemēram, slejā ar nosaukumu "email" var būt datu tips "text", kas norāda, ka tajā var saglabāt teksta datus, piemēram, e-pasta adresi.

Datubāzes struktūra ir svarīga, jo tā nosaka datu glabāšanas un piekļuves veidu. Labi izstrādāta datubāzes struktūra var uzlabot datubāzes veiktspēju un uzticamību, kā arī atvieglot darbu ar datiem. No otras puses, slikti izstrādāta datubāzes struktūra var radīt tādas problēmas kā datu dublēšanās, datu nekonsekvence un lēna veiktspēja.

Datubāzu drošība

Datubāzu drošība ir datubāzes aizsardzība pret nesankcionētu piekļuvi, izmantošanu, izpaušanu, traucējumiem, pārveidošanu vai iznīcināšanu. Tas ir svarīgs datubāzes un tajā glabāto datu integritātes, konfidencialitātes un pieejamības uzturēšanas aspekts.

Datubāzi var aizsargāt vairākos veidos, tostarp:

Autentifikācija: Tas ir process, kurā tiek pārbaudīta tā lietotāja identitāte, kurš mēģina piekļūt datubāzei. To var veikt, izmantojot paroles, biometrisko autentifikāciju vai citas metodes.
Autorizācija: Tas ir process, kurā, pamatojoties uz lietotāja privilēģijām, tiek piešķirta vai liegta piekļuve konkrētiem resursiem vai darbībām datubāzē. Lietotājiem var piešķirt dažādus piekļuves līmeņus, pamatojoties uz viņu lomu vai pienākumiem organizācijā.
Šifrēšana: Tas ir process, kurā dati tiek pārveidoti tādā formā, ko var nolasīt tikai persona, kurai ir pareizā atšifrēšanas atslēga. Šifrēšanu var izmantot, lai aizsargātu datus gan glabāšanas, gan pārnēsāšanas laikā, palīdzot novērst nesankcionētu piekļuvi sensitīvai informācijai.
Piekļuves kontrole: Tas ir process, kurā tiek regulēts, kas var piekļūt datubāzei un kādas darbības var veikt. Piekļuves kontroli var īstenot, izmantojot atļaujas, lomas un citas metodes.
Audits: Tas ir datubāzes darbības izsekošanas un reģistrēšanas process, piemēram, kas piekļuvis datubāzei un kādas darbības veicis. Audits var palīdzēt atklāt un novērst nesankcionētu piekļuvi vai darbības, kā arī to var izmantot atbilstības nodrošināšanai.

Datubāzu drošība ir svarīgs jautājums visu lielumu organizācijām, jo datubāzēs glabātie dati bieži ir sensitīvi un vērtīgi.

Darbs ar datubāzi

Pirms sākat darbu ar datubāzi, ir svarīgi izvēlēties savām vajadzībām atbilstošu datubāzes pārvaldības sistēmu (DBVS). Populārākās relāciju DBVS ir MySQL, PostgreSQL,SQLite un Oracle. Katrai DBVS ir savas stiprās puses, un tās ir piemērotas dažādiem lietojumprogrammu veidiem, tāpēc ir svarīgi izvēlēties to, kas vislabāk atbilst lietojumprogrammas vajadzībām.

Kad ir izvēlēta DBVS, tā būs jāinstalē serverī un jāiestata datubāze un lietotāja konts lietojumprogrammai. Vai jāizmanto kāds mākoņpakalpojums, kur serveris jau ir konfigurēts ar DBVS atbilstošajai datubāzei.

Pēc tam būs jāizveido datubāzes struktūra, tostarp tabulas un attiecības starp tām. Visbeidzot, būs jāsavieno lietojumprogramma ar datubāzi un jāpārbauda savienojums, lai pārliecinātos, ka viss darbojas pareizi.

Visa šī procesa laikā ir svarīgi ņemt vērā drošības paraugpraksi, piemēram, izmantot spēcīgas paroles, iespējot šifrēšanu un konfigurējot piekļuves kontroli. Ievērojot šos soļus, varat izveidot uzticamu un drošu relācijas datubāzi savai tīmekļa lietojumprogrammai.

Turpmākie piemēri balstīti uz SQLite datubāzes pamata.

Izplānot datubāzi

Relāciju datubāzes modelēšana ietver datubāzes struktūras projektēšanu, lai atspoguļotu attiecības starp dažādām saglabātajām vienībām. Relāciju datubāzes modelēšanas process ietver tabulu izveidi un attiecību definēšanu starp tām, izmantojot ārējās atslēgas.

Šis process ietver:

Identificēt vienības (entities): Pirmais solis relāciju datubāzes modelēšanā ir identificēt vienības jeb entitātes, kuras vēlaties saglabāt datubāzē. Entitāte ir persona, vieta, lieta vai jēdziens, par kuru vēlaties saglabāt datus. Piemēram, bibliotēkas datubāzē vienības varētu būt grāmatas, autori un lasītāji.
Izveidot tabulas katrai vienībai: Pēc tam, kad ir identificētas vienības, var izveidot datubāzē tabulu katrai vienībai. Katrai tabulai jābūt primārajai atslēgai (ID), kas ir unikāla kolonna vai kolonnu kopums, kas identificē katru tabulas rindu. Primāro atslēgu izmanto, lai izveidotu attiecības starp tabulām.
Definēt attiecības starp tabulām: Relāciju datubāzē attiecības starp tabulām izveido, izmantojot ārējās atslēgas. Ārējā atslēga ir vienas tabulas kolonna, kas norāda uz citas tabulas primāro atslēgu. Piemēram, bibliotēkas datubāzē grāmatu tabulā var būt ārējā atslēga (Author_ID), kas atsaucas uz tabulas authors primāro atslēgu (Author_ID). Tas ļauj datubāzē saglabāt informāciju par katras grāmatas autoru un pēc vajadzības iegūt šo informāciju.

Veicot šos soļus, var modelēt relāciju datubāzi, kas precīzi atspoguļo attiecības starp saglabātajām vienībām. Tas atvieglos darbu ar datiem un nodrošinās datu integritāti un konsekvenci.

Noteikt attiecības starp tabulām

Relāciju datubāzē attiecības starp tabulām var izmantot, lai attēlotu saiknes starp dažādām vienībām. Ir trīs galvenie attiecību veidi: vienviennozīmīgs (one-to-one), viendaudznozīmīgs (one-to-many) un daudzdaudznozīmīgs (many-to-many).

Vienviennozīmīga attiecība: Šāda veida attiecības pastāv starp divām tabulām, kad rinda vienā tabulā ir saistīta tikai ar vienu rindu otrā tabulā un otrādi. Piemēram, lietotāju tabulai un lietotāju profilu tabulai var būt attiecības viens pret vienu, ja katram lietotājam ir tikai viens profils un katrs profils pieder tikai vienam lietotājam. Šāda veida attiecības īsteno, izmantojot ārējo atslēgu vienā no tabulām, kas atsaucas uz otras tabulas primāro atslēgu.
Viendaudznozīmīga attiecība: Šāda veida attiecības pastāv starp divām tabulām, kad rinda vienā tabulā ir saistīta ar vairākām rindām otrā tabulā, bet katra rinda otrajā tabulā ir saistīta tikai ar vienu rindu pirmajā tabulā. Piemēram, pasūtījumu tabulai un pasūtījumu posteņu tabulai var būt attiecības "viens pret daudz", kur katram pasūtījumam var būt vairāki posteņi, bet katrs postenis pieder tikai vienam pasūtījumam. Šāda veida attiecības arī tiek īstenotas, izmantojot ārējo atslēgu tabulā "daudzi", kas atsaucas uz tabulas "viens" primāro atslēgu.
Daudzdaudznozīmīgs attiecība: Šāda veida attiecības pastāv starp divām tabulām, kad rinda vienā tabulā ir saistīta ar vairākām rindām otrā tabulā un otrādi. Piemēram, studentu tabulai un kursu tabulai var būt attiecības "daudzi-daudz", ja katrs students var reģistrēties vairākos kursos un katram kursam var būt vairāki studenti. Šāda veida attiecības tiek īstenotas, izmantojot trešo tabulu, ko sauc par "savienojuma" tabulu, kurā glabājas atslēgas uz abām pārējām tabulām, definējot attiecības starp tām.

Tabulu struktūras un datu tipu definēšana

Kad datubāze ir izveidota, ir jādefinē tabulu struktūra un katras kolonnas datu tipi. Tabulu struktūra ietver kolonnu nosaukumus un datu tipus, kā arī visus ierobežojumus vai indeksus.

Tabulas struktūras definēšana:

Datu tipu izvēle: Ir svarīgi izvēlēties pareizo datu tipu katrai tabulas kolonnai, jo tas nosaka, kāda veida datus var saglabāt kolonnā un kā tie tiks saglabāti datubāzē. Biežāk sastopamie datu tipi ir teksts, skaitļi, datumi un bolu vērtības. Ir svarīgi izvēlēties datu tipu, kas ir piemērots uzglabājamiem datiem. Piemēram, skaitļu datu tipu INT vai DECIMAL var izmantot skaitlisko datu uzglabāšano, bet datu tipu VARCHAR vai TEXT var izmantot teksta vērtību glabāšanai.
Ierobežojumu definēšana: Ierobežojumi ir noteikumi, kas nosaka, kā jāstrukturē dati tabulā. Tos var izmantot, lai nodrošinātu datu integritāti un konsekvenci. Bieži sastopamie ierobežojumi ir NOT NULL, kas nosaka, ka kolonnā jābūt vienai vērtībai, un UNIQUE, kas nosaka, ka visām kolonnas vērtībām jābūt unikālām.
Indeksu izveide: Indeksi ir struktūras, ko izmanto, lai uzlabotu tabulas vaicājumu veiktspēju. Tie ļauj datubāzei ātri atrast un iegūt konkrētas datu rindas, pamatojoties uz indeksēto kolonnu vērtībām. Var izveidot indeksu vienam vai vairākiem tabulas stabiņiem, izmantojot komandu CREATE INDEX.

Labā prakse

Svarīgi ievērot dažus pamata principus darbā ar datu bāzēm.

Tabulas rindas primārā atslēga (ID) ir unikālā.
ID neidentificē datus. Kā ID nedrīkst izmantot personas kodu, epastu vai kādu citu viegli identificējošu datu vienību.
ID neseko 'loģikai'. Autonumerāciju un citu viegli izsekojamu loģiku nav ieteicams lietot idektifkatoru radīšanai. Tā vietā drošāk ir izmantot UUID ģeneratoru, kas izveido nejaušu unikālu identifikatoru.
Tabulu un kolonnu nosaukumos neizmantot speciālos simbolus (iesk. latviešu valodas) un atstarpes.

SQLite

SQLite ir bezmaksas atvērtā koda iegultās datubāzes pārvaldības sistēma (DBMS), kas ir ļoti populāra tīmekļa lietojumprogrammās. SQLite ir ļoti viegla un ātra, un to var izmantot kā alternatīvu lielākām datubāzu sistēmām, piemēram, MySQL, PostgreSQL un Oracle. SQLite ir iegultās datubāzes pārvaldības sistēma, kas nozīmē, ka tā ir paredzēta iegultām lietojumprogrammām, kurās datubāze ir iekļauta lietojumprogrammas kodā. SQLite ir vienfailu datubāze, kas nozīmē, ka visi dati tiek saglabāti vienā datnē, kas ir ērti pārnēsājama un viegli pārvaldāma.

SQLite ir labi piemērota vienkāršāku programmu un datu struktūru prototipešanai, kā arī tīmekļa lietojumprogrammām, kurās nepieciešama vienkārša datu glabāšana.

SQLite pamta datu klases

SQLite 3 pamtā ir piecas glabāšanas klases, kas var tikt izmantoti, lai definētu kolonnas tabulās:

NULL datu tipu var izmantot, lai definētu kolonnas, kuras var saturēt tikai NULL vērtības.
INTEGER datu tipu var izmantot, lai definētu kolonnas, kuras var saturēt veselu skaitļu vērtības.
REAL datu tipu var izmantot, lai definētu kolonnas, kuras var saturēt skaitļu ar peldošo komata zīmi vērtības.
TEXT datu tipu var izmantot, lai definētu kolonnas, kuras var saturēt teksta vērtības.
BLOB datu tipu var izmantot, lai definētu kolonnas, kuras var saturēt binārās virknes vērtības.

Glabāšanas klase ir vispārīgāka nekā datu tips. Piemēram, INTEGER glabāšanas klase ietver 7 dažādus veselu skaitļu datu tipus ar dažādiem garumiem. Tas padara atšķirību diska uzglabāšanā. Tomēr, tiklīdz INTEGER vērtības tiek ielasītas no diska un atnesotas atmiņā apstrādei, tās tiek pārveidotas par vispārējo datu tipu (8 baitu veselu skaitli). Tāpēc lielākoties "glabāšanas klase" nav atšķirama no "datu tipa", un šie divi termini var tikt lietoti savstarpēji.

Pilnu dokumentāciju ar SQLite datu klasēm un tipiem var atrast šeit.

SQLite datu tipi

SQLite 3 atbalsta plašu datu tipu klāstu, tostarp:

Datu tips	Klase
INT, INTEGER, TINYINT, SMALLINT, MEDIUMINT, BIGINT, UNSIGNED BIG INT, INT2, INT8	INTEGER
NUMERIC, DECIMAL(10, 5), BOOLEAN, DATE, DATETIME	NUMERIC
REAL, DOUBLE, DOUBLE PRECISION, FLOAT	REAL
CHARACTER(20), VARCHAR(255), VARYING CHARACTER(255), NCHAR(55), NATIVE CHARACTER(70), NVARCHAR(100), TEXT, CLOB	TEXT
BLOB	BLOB

SQLite nav atsevišķa glabāšanas klase Boolean vērtībām. Tā vietā Boolean vērtības tiek glabātas kā veseli skaitļi 0 (false) un 1 (true). SQLite atpazīst atslēgas vārdus "TRUE" un "FALSE", sākot ar versiju 3.23.0 (2018-04-02), bet šīs atslēgas vārdi faktiski ir tikai alternatīvi rakstības veidi skaitļu literāļiem 1 un 0 attiecīgi.

SQLite nav speciāla glabāšanas klase datuma un/vai laika informācijas uzglabāšanai. Tā vietā SQLite iebūvētās datuma un laika funkcijas ir spējīgas uzglabāt datuma un laika informāciju kā TEXT, REAL vai INTEGER vērtības:

TEXT kā ISO8601 virknes ("YYYY-MM-DD HH:MM:SS.SSS").
REAL kā Juliāņa dienas numurus, kas ir dienu skaits kopš pulksten 12:00 pēcpusdienā Greničas laikā 4714 p.m.ē. proleptiskajā Gregora kalendārā.
INTEGER kā Unix laiku, kas ir sekunžu skaits kopš 1970. gada 1. janvāra 00:00:00 UTC.

SQLite nosacījumi vaicājumu veidošanai

SQLite 3 atbalsta dažādus nosacījumus, kas var tikt izmantoti, lai veidotu vaicājumus, kas atgriež vērtības, pamatojoties uz noteiktiem nosacījumiem. Nosacījumi var tikt izmantoti, lai filtrētu rezultātus, kā arī lai veiktu darbības, pamatojoties uz noteiktiem nosacījumiem.

SQLite 3 atbalsta šādus biežāk izmantotos nosacījumus:

WHERE nosacījums tiek izmantots, lai filtrētu rezultātus, pamatojoties uz noteiktiem nosacījumiem. Piemēram, WHERE nosacījums var tikt izmantots, lai atgrieztu tikai tās rindas, kurās kolonna "name" satur vērtību "John".
GROUP BY nosacījums tiek izmantots, lai grupētu rezultātus pēc noteiktas kolonnas. Piemēram, GROUP BY nosacījums var tikt izmantots, lai grupētu rezultātus pēc kolonnas "name".
ORDER BY nosacījums tiek izmantots, lai kārtotu rezultātus pēc noteiktas kolonnas. Piemēram, ORDER BY nosacījums var tikt izmantots, lai kārtotu rezultātus pēc kolonnas "name".
LIMIT nosacījums tiek izmantots, lai ierobežotu rezultātu skaitu. Piemēram, LIMIT nosacījums var tikt izmantots, lai atgrieztu tikai pirmās 10 rindas.

SQLite piedāvā dažādus nosacījumu veidošanas paņēmienus, lai veidotu vaicājumus, kas atgriež vērtības, pamatojoties uz noteiktiem nosacījumiem. Ar pilnu dokumentāciju par SQLite nosacījumiem var iepazīties šeit.

Darbs ar SQLite - DB Browser

DB Browser for SQLite ir viegli lietojama grafiska lietotne, kas ļauj izveidot, rediģēt un pārvaldīt SQLite datubāzes. DB Browser for SQLite ir pieejams Windows, macOS un Linux operētājsistēmām un ir bezmaksas un atvērtā koda programmatūra. Lai sāktu darbu ar DB Browser for SQLite, ir nepieciešams instalēt lietotni no oficiālās mājas lapas šeit.

DB Browser for SQLite piedāvā daudz iespēju darbam ar datubāzēm, tostarp:

Tabulu izveide, rediģēšana un pārskatīšana: Var izveidot jaunas tabulas, definēt kolonnas un ievietot datus tabulās.
Datu ievietošana, atjaunināšana un dzēšana: Var veikt dažādus darbības ar datiem, piemēram, ievietot, atjaunināt un dzēst datus tabulās.
Vaicājumu veidošana un izpilde: Var veidot dažādus vaicājumus, lai atgrieztu un filtrētu datus tabulās.
Datu bāzes attiecību pārskats: Var pārskatīt datu bāzes attiecības starp tabulām un veikt nepieciešamos pielāgojumus.

Darbs ar SQLite - VS Code

SQLite datubāzes var izveidot, izmantojot SQLite komandu rindu vai kādu no daudzajiem SQLite klientiem, kas ir pieejami dažādām platformām. SQLite datubāzes var izveidot arī, izmantojot kādu no daudzajām SQLite klientu bibliotēkām, kas ir pieejamas dažādām programmēšanas valodām.

SQLite Extension uzstādīšana un pamata lietošana

Lai būtu ērti strādāt ar SQLite VS Code vidē ir pieejams papildinājums SQLite.

Lai uzstādītu papildinājumu, jāatver VS Code un jādodas uz Extensions sadaļu. Meklējot SQLite jāatrod papildinājums un jāuzstāda.

Pamata lietošana:

Vispirms izveido jaunu datubāzes datni, piemēram, database.db.
Izveido jaunu datubāzes pieslēgumu, izmantojot komandu SQLite: Open Database. Komandas ir pieejamas arī no komandu paneļa: View -> Command Palette.
Izveido jaunu datni, izmantojot komandu SQLite: New Query.
.sql datnē ieraksta SQL vaicājumus.
Izpilda vaicājumus, izmantojot komandu SQLite: Run Query. Pirmo reizi tiks pieprasīts izvēlēties datubāzes pieslēgumu, piemēram, database.db.
Rezultāti tiek parādīti jaunā logā.
Ja nepieciešams, var izveidot jaunu datni un turpināt darbu.

Izveidojot savienojumu ar datubāzi, parādīsies izvēlne "SQLITE EXPLORER", kurā var ērti pārskatīt datubāzes struktūru un tabulu datus.

SQL komandas

Lai apskatītu dažādās SQL komandas, turpmāk apskatīsim šādu situāciju:

Ir nepieciešams izveidot datubāzi, kurā glabāt informāciju par studentiem un viņu kursiem. Katram studentam var būt vairāki kursi, un katram kursam var būt vairāki studenti. Tāpēc šajā gadījumā ir nepieciešama daudzdaudznozīmīga attiecība starp tabulām.

Tabulu izveide

Lai izveidotu tabulu, ir nepieciešams izmantot komandu CREATE TABLE. Komandai jānorāda tabulas nosaukums un kolonnas, kas tiks izveidotas. Katrai kolonnai jānorāda nosaukums un datu tips. Piemēram, izveidojot tabulu students, var izmantot šādu komandu:

CREATE TABLE IF NOT EXISTS students (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    surname TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
);

students ir tabulas nosaukums;
id, name, surname un email ir kolonnu nosaukumi;
INTEGER, TEXT kolonnu datu tipi;
PRIMARY KEY norāda, ka id kolonna ir primārā atslēga;
AUTOINCREMENT norāda, ka vērtības šajā kolonnā tiks automātiski palielinātas (1, 2, 3...);
NOT NULL norāda, ka kolonnā nevar būt NULL vērtības;
UNIQUE norāda, ka kolonnā nevar būt vienādas vērtības.

Datus ievietošana (INSERT INTO)

Lai ievietotu datus tabulā, ir nepieciešams izmantot komandu INSERT INTO. Komandai jānorāda tabulas nosaukums un kolonnas, kurās tiks ievietotas vērtības. Piemēram, ievietojot datus tabulā students, var izmantot šādu komandu:

INSERT INTO students (name, surname, email)
VALUES ('Reinis', 'Kurpnieks', '[email protected]'),
        ('Rinals', 'Kalns', '[email protected]'),
        ('Renāte', 'Kundziņa', '[email protected]');

students ir tabulas nosaukums;
name, surname un email ir kolonnu nosaukumi;
'Reinis', 'Kurpnieks' un '[email protected]' ir vērtības, kas tiks ievietotas kolonnās.
Vērtības jānorāda tādā pašā secībā, kādā norādīti kolonnu nosaukumi.
ar ',' (komatu) atdala vērtības (VALUES), kas tiks ievietotas atsevišķās rindās;
id nav norādīts, jo šī kolonna tiks automātiski aizpildīta.

Datus izvēlēšanās (SELECT)

Lai izvēlētos datus no tabulas, ir nepieciešams izmantot komandu SELECT. Komandai jānorāda kolonnas, kuras tiks izvēlētas, un tabulas nosaukums. Piemēram, izvēloties visus datus no tabulas students, var izmantot šādu komandu:

SELECT *
FROM students;

* norāda, ka tiks izvēlētas visas kolonnas.

rezultāts:

id	name	surname	email
1	Reinis	Kurpnieks	[email protected]
2	Rinals	Kalns	[email protected]
3	Renāte	Kundziņa	[email protected]

Lai izvēlētos konkrētas kolonnas, sakārotu rezultātus pēc kolonnas un ierobežotu rezultātu skaitu, var izmantot šādu komandu:

SELECT name, surname
FROM students
ORDER BY name DESC
LIMIT 2;

name un surname ir kolonnu nosaukumi;
FROM students norāda, ka kolonnas tiks izvēlētas no tabulas students;
ORDER BY name DESC norāda, ka rezultāti tiks kārtoti dilstošā secībā pēc kolonnas name;
LIMIT 2 norāda, ka tiks izvēlētas tikai pirmās divas rindas;

name	surname
Rinalds	Kalns
Renāte	Kundziņa

Datus atjaunošana (UPDATE)

Lai atjaunotu datus tabulā, ir nepieciešams izmantot komandu UPDATE. Komandai jānorāda tabulas nosaukums, kolonnas, kuras tiks atjaunotas, un jaunās vērtības. Piemēram, atjaunojot vērtību kolonnā email tabulā students, var izmantot šādu komandu:

UPDATE students
SET email = '[email protected]'
WHERE id = 3;

students ir tabulas nosaukums;
email ir kolonnas nosaukums, kura tiks atjaunota;
'[email protected]' ir jaunā vērtība, kas tiks ievietota kolonnā email;
WHERE id = 3 norāda, ka tiks atjaunota tikai tā rinda, kurā id kolonnas vērtība ir 3.

Datus dzēšana (DELETE)

Lai dzēstu datus tabulā, ir nepieciešams izmantot komandu DELETE. Komandai jānorāda tabulas nosaukums un nosacījums, pēc kura tiks dzēstas rindas. Piemēram, dzēšot datus no tabulas students, var izmantot šādu komandu:

DELETE FROM students
WHERE id = 3;

students ir tabulas nosaukums;
WHERE id = 3 norāda, ka tiks dzēstas tikai tās rindas, kurās id kolonnas vērtība ir 3.

Tabulu dzēšana (DROP TABLE)

Lai dzēstu tabulu, ir nepieciešams izmantot komandu DROP TABLE. Komandai jānorāda tabulas nosaukums. Piemēram, dzēšot tabulu students, var izmantot šādu komandu:

DROP TABLE students;

students ir tabulas nosaukums.

Tabulu veidošana ar ārējām atslēgām (FOREIGN KEY)

Lai saprastu, kā izveidot tabulu ar ārējām atslēgām, vispirms izveidosim tabulu courses, kurā glabāsies informācija par kursiem:

CREATE TABLE IF NOT EXISTS courses (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    description TEXT NOT NULL
);

un pievienosim dažus ierakstus:

INSERT INTO courses (name, description)
VALUES ('Programmēšana 1', 'Pamatu apguve'),
        ('Programmēšana 2', 'Pamatu apguve'),
        ('Programmēšana 3', 'Pamatu apguve');

Tālāk izveidosim tabulu students_courses, kurā glabāsies informācija par studentiem un kursiem, kuros viņi piedalās:

CREATE TABLE IF NOT EXISTS students_courses (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    student_id INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    FOREIGN KEY (student_id) REFERENCES students (id),
    FOREIGN KEY (course_id) REFERENCES courses (id)
);

students_courses ir tabulas nosaukums;
id, student_id un course_id ir kolonnu nosaukumi;
INTEGER kolonnu datu tipi;
PRIMARY KEY norāda, ka id kolonna ir primārā atslēga;
FOREIGN KEY (student_id) REFERENCES students (id) norāda, ka student_id kolonna ir ārējā atslēga, kas atsaucas uz students tabulas id kolonnu;
FOREIGN KEY (course_id) REFERENCES courses (id) norāda, ka course_id kolonna ir ārējā atslēga, kas atsaucas uz courses tabulas id kolonnu.

Pievienosim dažus ierakstus:

INSERT INTO students_courses (student_id, course_id)
VALUES (1, 1),
        (1, 2),
        (2, 1),
        (2, 3),
        (3, 2),
        (3, 3);

Izveidot pieprasījumu ar ārējām atslēgām (INNER JOIN)

Tālāk izveidosim vaicājumu, kas atgriež informāciju par studentiem un kursiem, kuros viņi piedalās:

SELECT students.name, students.surname, courses.name
FROM students_courses
INNER JOIN students ON students_courses.student_id = students.id
INNER JOIN courses ON students_courses.course_id = courses.id;

students.name, students.surname un courses.name norāda, ka tiks izvēlētas šīs kolonnas;
FROM students_courses norāda, ka kolonnas tiks izvēlētas no tabulas students_courses;
INNER JOIN students ON students_courses.student_id = students.id norāda, ka tiks izvēlētas tikai tās rindas, kurās students_courses.student_id kolonnas vērtība ir vienāda ar students.id kolonnas vērtību;
INNER JOIN courses ON students_courses.course_id = courses.id norāda, ka tiks izvēlētas tikai tās rindas, kurās students_courses.course_id kolonnas vērtība ir vienāda ar courses.id kolonnas vērtību.
students_courses, students un courses ir tabulu nosaukumi.
students_courses.student_id, students_courses.course_id, students.id un courses.id ir kolonnu nosaukumi.

rezultāts:

name	surname	name
Reinis	Kurpnieks	Programmēšana 1
Reinis	Kurpnieks	Programmēšana 2
Rinals	Kalns	Programmēšana 1
Rinals	Kalns	Programmēšana 3
Renāte	Kundziņa	Programmēšana 2
Renāte	Kundziņa	Programmēšana 3

Izveidot pieprasījumu ar ārējām atslēgām (LEFT JOIN)

Tālāk izveidosim vaicājumu, kas atgriež informāciju par studentiem un kursiem, kuros viņi piedalās, bet arī tos studentus, kuri nekādā kursā nepiedalās:

SELECT students.name, students.surname, courses.name
FROM students_courses
LEFT JOIN students ON students_courses.student_id = students.id
LEFT JOIN courses ON students_courses.course_id = courses.id;

LEFT JOIN norāda, ka tiks izvēlētas visas rindas no tabulas, kurai tiek veikts LEFT JOIN, bet no otras tabulas tiks izvēlētas tikai tās rindas, kurām ir atbilstoša vērtība pirmajā tabulā.
students.name, students.surname un courses.name norāda, ka tiks izvēlētas šīs kolonnas;
FROM students_courses norāda, ka kolonnas tiks izvēlētas no tabulas students_courses;
LEFT JOIN students ON students_courses.student_id = students.id norāda, ka tiks izvēlētas visas rindas no tabulas students_courses, bet no tabulas students tiks izvēlētas tikai tās rindas, kurās students_courses.student_id kolonnas vērtība ir vienāda ar students.id kolonnas vērtību;

Kad izmantot INNER JOIN un LEFT JOIN?

INNER JOIN tiek izmantots, lai izvēlētos tikai tās rindas, kurām ir atbilstoša vērtība abās tabulās.
LEFT JOIN tiek izmantots, lai izvēlētos visas rindas no tabulas, kurai tiek veikts LEFT JOIN, bet no otras tabulas tiks izvēlētas tikai tās rindas, kurām ir atbilstoša vērtība pirmajā tabulā.

Izveidot pieprasījumu ar ārējām atslēgām (GROUP BY)

Tālāk izveidosim vaicājumu, kas atgriež informāciju par kursiem un to studentu skaitu, kuri piedalās šajos kursos:

SELECT courses.name, COUNT(students_courses.student_id) AS students_count
FROM students_courses
INNER JOIN courses ON students_courses.course_id = courses.id
GROUP BY courses.name;

courses.name norāda, ka tiks izvēlēta šī kolonna;
COUNT(students_courses.student_id) norāda, ka tiks izvēlēta students_courses.student_id kolonna, un tiks saskaitīts, cik reizes tā atkārtojas;
AS students_count norāda, ka rezultātu kolonna tiks saukta par students_count;
FROM students_courses norāda, ka kolonnas tiks izvēlētas no tabulas students_courses;
INNER JOIN courses ON students_courses.course_id = courses.id norāda, ka tiks izvēlētas tikai tās rindas, kurās students_courses.course_id kolonnas vērtība ir vienāda ar courses.id kolonnas vērtību;
GROUP BY courses.name norāda, ka rezultāti tiks grupēti pēc courses.name kolonnas.

rezultāts:

name	students_count
Programmēšana 1	2
Programmēšana 2	2
Programmēšana 3	2

Darbs ar SQLite - NodeJS

Lai darbotos ar SQLite datubāzēm NodeJS vidē, ir nepieciešams instalēt sqlite3 bibliotēku.

Izveidot jaunu datubāzi NodeJS vidē:

Terminālī pievieno SQLite3 bibliotēku:

npm install sqlite3

Servera failā pievieno bibliotēku un izveido savienojumu ar datubāzi:

const sqlite3 = require("sqlite3");
// Savienojums ar datu bāzi
const db = new sqlite3.Database("database.db");
// Datubāzes izveide
db.run(`CREATE TABLE IF NOT EXISTS example_table (
    id INTEGER PRIMARY KEY,
    name TEXT
)`);

kur database.db ir datubāzes nosaukums.

Izveidot tabulu ar UUID primāro atslēgu NodeJS vidē:

SQLite3 pašā bāzei nav iebūvēta UUID (Universally Unique Identifier) datu tipa. Tomēr vara izmantot teksta kolonnu, lai glabātu UUID vērtības ievietotu UUID vērtības datu bāzē. Pirmkārt, ir nepieciešams instalēt nepieciešamās npm pakotnes:

npm install sqlite3 uuid

Pēc tam var izmantot šādu kodu:

const sqlite3 = require("sqlite3");
const { v4: uuidv4 } = require("uuid");
// Savienojums ar datu bāzi
const db = new sqlite3.Database("example.db");
// Tabulas izveide ar UUID kolonnu
db.run(`
    CREATE TABLE IF NOT EXISTS example_table (
        id TEXT PRIMARY KEY,
        name TEXT
    )
`);
// Funkcija UUID ģenerēšanai un ievietošanai datu bāzē
const generateAndInsertUUID = () => {
  const generatedUUID = uuidv4();
  const stmt = db.prepare("INSERT INTO example_table (id, name) VALUES (?, ?)");
  stmt.run(generatedUUID, "Example Name");
  stmt.finalize();
  console.log(`Inserted record with UUID: ${generatedUUID}`);
};
// Ģenerē un ievieto vairākas UUID vērtības
for (let i = 0; i < 5; i++) {
  generateAndInsertUUID();
}
// Aizvēršana
db.close();

Šajā piemērā tiek izmantota sqlite3 bibliotēka, lai izveidotu savienojumu ar SQLite datu bāzi, un uuid bibliotēka, lai ģenerētu UUID vērtības. Pēc tam tiek izveidota tabula ar id kolonnu, kas ir primārā atslēga, un name kolonnu. Tiek izveidota funkcija, kas ģenerē un ievieto UUID vērtības datu bāzē. Tiek izveidots cikls, kas izsauc funkciju 5 reizes, lai ģenerētu un ievietotu 5 UUID vērtības datu bāzē. Visbeidzot, tiek aizvērts savienojums ar datu bāzi.

PostgreSQL

PostgreSQL, pazīstama arī kā Postgres, ir bezmaksas atvērtā koda objekt-relatīvās datubāzes pārvaldības sistēma (ORDBMS), ko plaši izmanto tīmekļa lietojumprogrammās, datu noliktavās un analīzē. Tā ir pazīstama ar savu uzticamību, robustumu un elastību, un to bieži izmanto kā alternatīvu patentētām datubāzu sistēmām, piemēram, MySQL un Oracle. PostgreSQL atbilst SQL standartam, kas nozīmē, ka tā atbalsta plašu SQL funkciju klāstu un ir saderīga ar daudzām citām datubāzu sistēmām. Tas atvieglo darbu un datu migrēšanu starp dažādām datubāzēm.

PostgreSQL ieteicams izmantot sarežgītākos datu glabāšanas un analīzes scenārijos, kur nepieciešama augsta veiktspēja un drošība. Tas ir piemērots lietojumprogrammām, kurās nepieciešama sarežģīta datu struktūra un kurās tiek apstrādāts liels datu apjoms.

PostgreSQL datu tipi

PostgreSQL atbalsta plašu datu tipu klāstu, tostarp:

Skaitliskos tipus: Tie ietver tipus veselu skaitļu (piemēram, SMALLINT, INTEGER, BIGINT), decimālciparu (piemēram, NUMERIC, DECIMAL) un skaitļu ar peldošo komata zīmi (piemēram, REAL, DOUBLE PRECISION) glabāšanai.
Teksta tipi: Tie ietverer tipus simbolu virknes glabāšanai (CHAR, VARCHAR, TEXT) un fiksēta garuma binārās virknes (piemēram, BYTEA).
Datuma un laika tipi: Tie ietver tipus datuma un laika vērtību (piemēram, DATE, TIME, TIMESTAMP, TIMESTAMPTZ) un intervālu (piemēram, INTERVAL) glabāšanai.
Loģiskais tips: Tas ir tips bolu vērtību (TRUE vai FALSE) glabāšanai.
Indentifikatoru tips: UUID (Universally Unique Identifier) ir datu tips, ko var izmantot, lai ģenerētu unikālu identifikatoru rindai tabulā.
JSON tips: Šis ir tips JSON datu glabāšanai.
Masīvu tipi: Šie ir tipi vērtību masīvu glabāšanai.
Saliktie tipi: Šie ir pielāgotie tipi, kas ļauj definēt struktūru ar vairākiem laukiem.
Uzskaitāmie tipi: Tie ir pielāgotie tipi, kas ļauj definēt kolonnas iespējamo vērtību kopumu.

Pilnu dokumentāciju ar PostgreSQL datu tipiem var atrast šeit.

Autors

Mārtiņš Bērziņš

Uzraksti atsauksmi uz [email protected] [email protected]

DB pamati

Saturs

Datubāzes pamatjēdzieni

Kas ir datubāze?

Relāciju (relational) un bez-relāciju (non-relational) datubāzes

Relācijas datubāzes

Bez-relāciju datubāzes

Datubāzes struktūra

Datubāzu drošība

Darbs ar datubāzi

Izplānot datubāzi

Noteikt attiecības starp tabulām

Tabulu struktūras un datu tipu definēšana

Labā prakse

SQLite

SQLite pamta datu klases

SQLite datu tipi

SQLite nosacījumi vaicājumu veidošanai

Darbs ar SQLite - DB Browser

Darbs ar SQLite - VS Code

SQLite Extension uzstādīšana un pamata lietošana

SQL komandas

Tabulu izveide

Datus ievietošana (INSERT INTO)

Datus izvēlēšanās (SELECT)

Datus atjaunošana (UPDATE)

Datus dzēšana (DELETE)

Tabulu dzēšana (DROP TABLE)

Tabulu veidošana ar ārējām atslēgām (FOREIGN KEY)

Izveidot pieprasījumu ar ārējām atslēgām (INNER JOIN)

Izveidot pieprasījumu ar ārējām atslēgām (LEFT JOIN)

Kad izmantot INNER JOIN un LEFT JOIN?

Izveidot pieprasījumu ar ārējām atslēgām (GROUP BY)

Darbs ar SQLite - NodeJS

PostgreSQL

PostgreSQL datu tipi

Autors

Mārtiņš Bērziņš