Lihtsad SQL-i päringud

Andrus Peetson

Veebiserver koos andmebaasiserveriga on paigas. phpMyAdmin ehk andmebaasiserveri veebipõhine kasutajaliides on paigas. Enne PHP programmeerimise kallale asumist on vaja selgeks saada SQL (Structured Query Language) päringud.

Logi phpMyAdmin heldusliidesesse kasutajaga root ja määratud parooliga.

Päeva päästab järgmine teadmine:

  • Andmebaasi mõista kui Exceli dokumenti (workbook) kus on mitu vahekaarti ehk tabelit.
  • Tabelit mõista kui Exceli töölehte (sheet).
  • Tulpa mõista kui Exceli töölehel olevat andmetulpa (A, B, C jne).
  • Kirjet mõista kui ühte rida töölehel (1, 2, 3 jne).

1. osa. Loome andmebaasi ja tabeli

  • Teeme serverisse uue andmebaasi, vasakpoolses menüüs kliki New.
    • Määra nimi ja jäta kodeering samaks (utf8umb4_general_ci).
  • Andmebaasi lisame tabeli, selleks kliki andmebaasil (vaskpoolne menüü), vormi Create table kirjuta tabeli nimi.
    • Tee 5 tulba jagu andmeid: id, temp, humi, noise, date, probe.
      • id tulba tüübiks vali int (arv), Index vali PRIMARY, A_I (autonumber) märgista
      • temp, humi, noise tulba tüübiks vali float (murdarv, murru eraldaja on punkt)
      • date tulba tüübiks vali DATETIME (spetsiaalne formaat kujul aasta-kuu-päev tund:minut:sekund, nt 2023-04-10 15:28:03)
      • probe tulba tüübiks vali VARCHAR ja Value 32. Selle lahtri väärtus saab olla kuni 32 tähemärki.
    • Salvesta.

2. osa. Loome uue kasutaja just ainult sellele baasile

  • Liigu vaskapoolses menüüs Home.
  • Kliki User accounts -> Add user account.
  • User name lahtrisse kasutajatunnus. Kirjuta üles!
  • Host name lahtrisse localhost.
  • Password lahtrisse genereeri Generate nupuga parool. Kirjuta üles!
  • Vajuta Go nuppu.
  • Vali Database -> Märgista sobilik baas -> nupp Go.
  • Märgista Check all -> nupp Go.
  • Kliki User accounts -> reload the privileges.

Edasi töötame loodud kasutajaga. Logi välja (vasakpoolses menüüs Log out) ja sisene uue kasutajatunnuse/salasõnaga.

3. osa. Andmete sisestamine

Pea meeles, nüüd töötame mitte-juurkasutaja õigustes.

Andmete sisestamine vormi kaudu:

  • Vasakpoolsest menüüst liigu andmebaas -> tabel.
  • Vahekaardil Browse on näha sisestatud andmed (sellest edaspidi).
  • Vahekaardil Insert saab käsitsi sisestada andmeid kirjete kaupa.
    • Saad lisada kaks kirjet korraga aga ei pea.
    • Pane tähele, tulbad on nüüd ridadena esitatud (mugavuse mõttes).
    • Sisestamisel pead jälgima tulba andmete tüüpi.
    • Function on eelseadistatud andmete töötleja, mis kasutab Value välja sisu.
      • id – jäta tühjaks, see on nö indeks/võtme väli ja selle väärtuse (value) määrab andmebaasiserver ise automaatselt.
      • temp, humi, noise – need on murdarv tüüpi ja murdarvu poolitaja on punkt. Sobib 5.6, ei sobi 5,6.
      • date – Function vali NOW, see kirjutab (Go nupu vajutamisel) ise Value lahtrisse hetke kuupäeva ja aja (vaata 1. osa).
      • probe – kirjuta ESP32-te iseloomustav unikaalne väärtus.
    • Vajuta õiget Go nuppu.
    • Sisesta vähemalt 5 kirjet erinevate väärtustega, kahel erineval päeval.

Andmete sisestamine päringu abil:

INSERT INTO andmed (id, temp, humi, noise, date, probe) VALUES (NULL, '1.2', '3.4', '5.6', NOW(), 'andrus1');
  • INSERT – sisesta, kanna sisse
  • INTO – kuhu
  • andmed – tabeli nimi
  • ( id, temp, … ) – tulpade nimetused
  • VALUES (NULL, ‘1.2’, …) – tulpade väärtused. Pane tähele, nimetused ja väärtused arv ja järjekord peab klappima.

Pane tähele, et kasutusel on spetsiaalne NULL sinna kuhu väärtust ei kirjuta. NOW() on spetsiaalne mariadb käsk, mis tagastab sobivas formaadis (vaata osa 1) hetke aja. Päring lõpeb alati semikooloniga.

4. osa. Väljavõtete tegemine

Päring tähendab väljavõtet kirjetest sinu määratud parameetrite alusel. Millised tulpasid, millises järekorras, millise kuupäeva vahemikus, kas arvutada mingi ajaühiku (nt 5min) keskmine jne.

Vahekaardile Browse klikkides tehakse kõige lihtsam päring:

SELECT * FROM andmed;
  • SELECT – märgista või võta.
  • * – kõik tulbad. Võid kirjutada ka tulpade nimed komadega eraldatult.
  • FROM – kuskohast või asukohast.
  • andmed – tabeli nimi, võib kasutada ka ühekordseid jutumärke (single quotes).

Täpsemad päringud: 

Kliki Edit inline ja saad muuta olemasolevat päringut. Go nupp teeb uue päringu.

SELECT date,temp,humi FROM andmed WHERE date >= '2023-04-11' AND time(date) >= '08:00:00';
  • WHERE – kus on, põhimõtteliselt sarnane programmeerimisest tuntud IF lausega. Saab kasutaja JA ja VÕI, vastavalt AND ja OR. Kui tingimusi on rohkem siis on vajalik neid grupeerida sulgudega vältimakse tingimuste valesid omavahelisi soeoseid.
  • date >= ‘2023-04-11’ – tulbas date olev väärtus on suurem või võrdne kui ‘2023-04-11’. Pane tähele, kellaaga pole määratud, seega sobib misiganes kellaaeg. Aga on AND time(date) >= ’08:00:00′ mis ütleb, et lisaks peab veel kellaaja osa date tulbas olema suurem või võrdne kui ’08:00:00′. Seega filtreeritakse välja kõik read kus kuupäev on suurem kui võrdne 2023-04-11 ja kellaaeg suurem või võrdne 08:00:00.

Sellise päringu tegemiseks on oluline tulba tüübi määramine ja kindlal kujul andmed!

Proovi teha erinevaid päringuid näidisandmete peal. Osa 2 ilmub siis kui andurid on rohkem andmeid kogunud.