Ero sivun ”Loki5ac:SQL-mallit” versioiden välillä
>Oh5xp Ei muokkausyhteenvetoa |
>Oh5xp p (→Päivämäärät) |
||
(32 välissä olevaa versiota samalta käyttäjältä ei näytetä) | |||
Rivi 4: | Rivi 4: | ||
==Johdanto== | ==Johdanto== | ||
[[wikipedia:fi:SQL|SQL]]-kyselyiden suunnittelua helpottaa kun ajattelee että kirjoitettu kysely käy rivi-riviltä läpi jokaisen [[loki]]n [[QSO]]:n. Ei siis ole mahdollista viitata seuraavaan ja edelliseen riviin, mutta niissä tilanteissa voidaan käyttää [[Loki5ac:SQL-mallit#Alikyselyt|alikyselyitä]] . | [[wikipedia:fi:SQL|SQL]]-kyselyiden suunnittelua helpottaa kun ajattelee että kirjoitettu kysely käy rivi-riviltä läpi jokaisen [[loki]]n [[QSO]]:n. Ei siis ole mahdollista viitata seuraavaan ja edelliseen riviin, mutta niissä tilanteissa voidaan käyttää [[Loki5ac:SQL-mallit#Alikyselyt|alikyselyitä]]. | ||
Alla olevat esimerkkien esittelykappaleet on järjestetty siten että ensin on SQL-esimerkit Loki5ac:n kannalta katsottuna, artikkelin lopussa vasta on tarkemmat kuvaukset eri funktioista. | Alla olevat esimerkkien esittelykappaleet on järjestetty siten että ensin on SQL-esimerkit Loki5ac:n kannalta katsottuna, artikkelin lopussa vasta on tarkemmat kuvaukset eri funktioista. Esimerkkikokoelmaan on kerätty käyttötarkoituksen mukaan esimerkkejä, niistä löytyy mm. joidenkin awardien QSOjen listausta helpottavia hakulauseita. | ||
==Perusteita== | ==Perusteita== | ||
Rivi 12: | Rivi 12: | ||
SELECT on avainsana hakuja tehtäessä, sen jälkeen tulee mitkä tiedot lokista näytetään. Jos haluat kaikki tiedot näkyville voi käyttää tähteä.[[Kuva:loki5ac_sqlcase1.jpg|thumb|Esimerkki bandi, mode ja DXCC hakuehtojen antamasta lopputuloksesta]] | SELECT on avainsana hakuja tehtäessä, sen jälkeen tulee mitkä tiedot lokista näytetään. Jos haluat kaikki tiedot näkyville voi käyttää tähteä.[[Kuva:loki5ac_sqlcase1.jpg|thumb|Esimerkki bandi, mode ja DXCC hakuehtojen antamasta lopputuloksesta]] | ||
Kun SQL-ristikon ehtokenttiin syötetään seuraavat tiedot... | |||
<table cellpadding="2" cellspacing="0" width="70%"> | <table cellpadding="2" cellspacing="0" width="70%"> | ||
<tr> | <tr><td width=10></td><td>'''Kenttä'''</td><td>'''Ehto'''</td><td>'''Arvo'''</td><td>'''Funktio'''</td></tr> | ||
<tr><td align="center">'''1'''</td><td>Bandi</td><td>=</td><td>3.5</td><td>AND</td></tr> | |||
<tr><td align="center">'''2'''</td><td>Mode</td><td>=</td><td>CW</td><td>AND</td></tr> | |||
<td width= | <tr><td align="center">'''3'''</td><td>DXCC</td><td>=</td><td>230,14</td><td>Napsauta [[Kuva:loki5ac_sql-ristikko_luo_kysely_painike.gif]]</td></tr> | ||
<tr><td align="center">'''1'''<td>Bandi<td | |||
<tr><td align="center">'''2'''<td>Mode<td | |||
<tr><td align="center">'''3'''<td>DXCC<td | |||
</table> | </table> | ||
... saadaan seuraavanlainen lauseke | |||
SELECT * FROM loc4ac | |||
WHERE | |||
[Bandi] = '3.5' | |||
AND | |||
[Mode] = 'CW' | |||
AND | |||
DXCC = '230,14'; | |||
Ylläkirjoitettu hakukysely tarkoittaa samaa kuin: Valitse kaikista QSO:ista tässä lokissa ne joissa bandi on 3.5 ja mode on CW. Kun klikataan painiketta <Suorita> lauseke suoritetaan ja alle SQL-ristikkoon ilmestyvät ne lokissa olevat QSO:t, jotka täyttävät nämä ehdot. Kenttien ympärille tulee hakasulut. Välttämättä niitä ei tarvita kuin kentissä, joiden nimessä on välilyöntejä (esim. [QSL saatu]). | |||
SQL-ristikossa on mahdollista näyttää vain valitut kentät. Tällöin kyselytekstikenttään on kirjoitettava kysely itse. Periaate on seuraava: <code>SELECT kenttä1, kenttä2, kenttä3 ...</code> | ===Näytä vain valitut kentät=== | ||
[[Kuva:loki5ac_sqlcase_kenttienvalinta.gif|thumb|Esimerkki kenttien valinnasta]] | |||
SQL-ristikossa on mahdollista näyttää vain valitut kentät. Tällöin kyselytekstikenttään on kirjoitettava kysely itse. Periaate on seuraava: | |||
<code>SELECT kenttä1, kenttä2, kenttä3 ...</code> | |||
Ristikossa esitettävien sarakkeiden leveyksiin ei voi vaikuttaa kyselyn luontivaiheessa, mutta kyselyn suorityksen jälkeen kenttien leveyksiä voi muuttaa vanhaan tapaan. | |||
Esimerkki: | |||
SELECT Asema, Päivä, Aika, Bandi, Mode | |||
FROM loc4ac; | |||
Tässä tulostuvat nuo viisi kenttää luetellussa järjestyksessä. Tässä ei ole käytetty []-sulkuja, koska kenttien nimissä ei ole välilyöntejä. | Tässä tulostuvat nuo viisi kenttää luetellussa järjestyksessä. Tässä ei ole käytetty []-sulkuja, koska kenttien nimissä ei ole välilyöntejä. | ||
===Erisuuruus=== | ===Erisuuruus=== | ||
Erisuuruusmerkkejä voidaan käyttää esimerkiksi aikavalinnoissa ottamalla kentäksi DTfield, joka on muotoa VVVVKKPPTTMM (esim. 200512311930 tarkoittaa 31.12.2005 19:30). Lopusta alkaen voidaan korvata aikamääreitä nollilla, jos niitä ei haluta ottaa huomioon. Kun aikaa ei huomioida pannaan sen tilalle 4 nollaa, edellä olevan esimerkin mukaan siis 200512310000. Ohjelma osaa lisätä loppuun nollat, jos käytetään | Erisuuruusmerkkejä voidaan käyttää esimerkiksi aikavalinnoissa ottamalla kentäksi DTfield, joka on muotoa VVVVKKPPTTMM (esim. 200512311930 tarkoittaa 31.12.2005 19:30). Lopusta alkaen voidaan korvata aikamääreitä nollilla, jos niitä ei haluta ottaa huomioon. Kun aikaa ei huomioida pannaan sen tilalle 4 nollaa, edellä olevan esimerkin mukaan siis 200512310000. Ohjelma osaa lisätä loppuun nollat, jos käytetään [[Loki5ac:SQL-mallit#Perusteita|perusteissa]] kuvattua valintamenetelmää. Jos kysely kirjoitetaan käsin tekstikenttään, pitää nollat lisätä itse perään. (Nollia käytetty siksi että silloin on vuorokauden vaihto, jolloin QSO:ja ei tule väärältä päivältä.) | ||
Esimerkki: | |||
SELECT * FROM loc4ac | |||
WHERE DTfield >= 199911230000 | |||
AND DTfield < 200601010000 | |||
WHERE DTfield >= 199911230000 | Näytetään QSO:t, joissa pvm on suurempi tai yhtäsuuri kuin 23.11.1999 kello 00:00 ja pienempi kuin 1.1.2006 kello 00:00 (eli 23.11.1999 ja 31.12.2005 välillä pidetyt QSOt). | ||
AND DTfield < 200601010000 | |||
Näytetään | |||
===ORDER BY=== | ===ORDER BY=== | ||
[[Kuva:loki5ac_sqlcase_orderby.gif|thumb|Esimerkki ORDER BY -kyselyn lopputuloksesta]] | |||
Tuloksen lajittelu tapahtuu ORDER BY -avainsanoilla. | |||
Esimerkki: | |||
SELECT * FROM loc4ac | |||
ORDER BY Bandi, Mode; | |||
Järjestää ristikon niin, että QSO't ovat ensisijaisesti bandin ja toissijaisesti moden mukaan järjestyksessä. | Järjestää ristikon niin, että QSO't ovat ensisijaisesti bandin ja toissijaisesti moden mukaan järjestyksessä. | ||
===LIKE=== | ===LIKE=== | ||
Kyselylauseissa voit käyttää merkkijonojen kohdalla korvaavia merkkejä: <code>%</code> tarkoittaa mitä merkkejä tahansa, kuinka monta tahansa. <code>_</code> tarkoittaa yksi merkki, mikä tahansa. | Kyselylauseissa voit käyttää merkkijonojen kohdalla korvaavia merkkejä (jokerimerkki): <code>%</code> tarkoittaa mitä merkkejä tahansa, kuinka monta tahansa. <code>_</code> tarkoittaa yksi merkki, mikä tahansa. Jokerimerkkiä käytettäessä <code>=</code>-merkki on korvattava <code>LIKE</code>-avainsanalla. | ||
Esimerkki 1: | |||
SELECT * FROM loc4ac | |||
WHERE QTH LIKE '%Vaasa%'; | |||
Yllä oleva esimerkki palauttaa kaikki QSOt, joiden QTH:sta löytyy Vaasa jostain kohdasta. Seuraava esimerkki puolestaan palauttaa kaikki OH-asemat, joissa [[suffix]] on MM ja piirinumero mikä tahansa. | |||
SELECT * FROM loc4ac | Esimerkki 2: | ||
WHERE QTH LIKE '%Vaasa%'; | SELECT * FROM loc4ac | ||
WHERE Asema LIKE 'OH_MM'; | |||
===IN=== | |||
Jos halutaan vertailla QSO:n yhtä tietoa useampaan arvoon voidaan käyttää IN-operaatiota usean OR-operaation sijaan. | |||
Esimerkki: | |||
SELECT * FROM loc4ac | SELECT * FROM loc4ac | ||
WHERE Asema LIKE 'OH_MM'; | WHERE Bandi IN("50", "70", "144"); | ||
Yllä oleva esimerkki palauttaa kaikki QSOt, joiden bandiksi on merkitty 50, 70 tai 144 MHz. Saman lopputuloksen saa myös ehdolla: | |||
WHERE Bandi="50" OR Bandi="70" OR Bandi="144" | |||
Jos bandit on merkitty metreinä, tällöin pitää käyttää vastaavia metrimääriä (tai tehdä sopiva muunnos SQL:llä). | |||
==Eri tapoja maiden listaukseen== | |||
Maan numeron saa helposti selville kun kirjaa prefiksin Asemakenttään, jolloin kenttien 'Nr' ja '[[CQ]]' arvot kirjoitetaan peräkkäin pilkulla erotettuna, aivan kuten ne näkyvät tietokantaristikossa. Ne saa selville myös klikkaamalla pääikkunan työkalupalkissa olevaa Maapallo-kuvaketta, mikä avaa maaluettelon. Siellä näkyy mm. rivi | Maan numeron saa helposti selville kun kirjaa [[prefix|prefiksin]] Asemakenttään, jolloin kenttien 'Nr' ja '[[CQ]]' arvot kirjoitetaan peräkkäin pilkulla erotettuna, aivan kuten ne näkyvät tietokantaristikossa. Ne saa selville myös klikkaamalla pääikkunan työkalupalkissa olevaa Maapallo-kuvaketta, mikä avaa maaluettelon. Siellä näkyy mm. rivi | ||
<code>Germany_EU ;DL _14__(230)_</code>, jossa suluissa on maan numero (230) ja 14 on CQ. | <code>Germany_EU ;DL _14__(230)_</code>, jossa suluissa on maan numero (230) ja 14 on CQ. | ||
===Listaa tiettyjen maiden QSO:t=== | |||
Tässä tulostuvat Asema-, Päivä-, Aika-, Bandi-, Mode -kentät kaikista saksalaistunnusten kanssa pidetyistä QSO'ista: | |||
SELECT Asema, Päivä, Aika, Bandi, Mode | |||
FROM loc4ac | |||
SELECT Asema, Päivä, Aika, Bandi, Mode | WHERE DXCC = '230,14'; | ||
FROM loc4ac | |||
WHERE DXCC = '230,14'; | Tulostetaan kaikki USA:n QSO:t. USA:n kusoissa maa on 291 CQ-arvot vaihtelevat tai ovat "?", tästä syystä määritettiin että DXCC:n CQ-osa saa olla mitä tahansa (?): | ||
SELECT * FROM loc4ac | |||
WHERE DXCC [[Loki5ac:SQL-mallit#LIKE|LIKE]] "291,%"; | |||
Palauttaa kaikki QSOt, joiden asema kentän alussa on "9A", ts. kaikki Kroatialaisten asemien kanssa pidetyt QSOt: | |||
WHERE Asema [[Loki5ac:SQL-mallit#LIKE|LIKE]] '9A%'; | |||
==Awardilistaukset== | |||
SELECT * FROM loc4ac | |||
WHERE DXCC [[Loki5ac:SQL-mallit#LIKE|LIKE]] "291,%"; | ===Award Football World Cup in Germany 2006=== | ||
</ | Saksalaisten QSO:t ajalla 1.5.2006-31.7.2006 ovat kelvollisia. Lisäksi on joitakin erikoisasemia DR- ja DQ -prefikseillä. | ||
Tässä | [http://www.amateurradio2006.de/ WFC06-awardin] erikoisasemat: | ||
SELECT * FROM loc4ac WHERE | |||
Val([DTField])>=200605010000 AND Val([DTField])<200608010000 AND | |||
< | Val(DXCC)=230 AND Left(Asema,2) IN ('DR','DQ'); | ||
<code> | |||
WHERE | Joukossa saattaa olla muitakin DR- ja DQ-asemia, mutta ne voi karsia käsin pois. Toinen vaihtoehto on naputella kaikki 35 erikoisasemaa IN-sanan jälkeen oleviin sulkuihin DR:n ja DQ:n tilalle. | ||
</code> | |||
< | [http://www.amateurradio2006.de/ WFC06-awardin] kaikki, paitsi erikoisasemat: | ||
SELECT * FROM loc4ac WHERE | |||
Val([DTField])>=200605010000 AND Val([DTField])<200608010000 AND | |||
Val(DXCC)=230 AND Left(Asema,2) NOT IN ('DR','DQ'); | |||
Tässä vastaavasti voi jäädä muutama tämän awardin kannalta ei-erikoisasema uupumaan, mutta tähän pätee sama kuin edelliseen. Edellisestä listasta voi kätevästi siirtää taulukkolaskennassa QSO:t tämän listauksen jatkoksi. | |||
Määrät saa laskettua vaihtamalla <code>COUNT(*)</code> tähden tilalle, tällöin tulee kuitenkin tuplistakin laskettua määrät. Alla olevassa esimerkissä lasketaan määrä DL-asemista ilman tuplia: | |||
SELECT COUNT(*) FROM | |||
(SELECT DISTINCT [Asema] FROM loc4ac WHERE | |||
Val([DTField])>=200605010000 AND Val([DTField])<200608010000 AND | |||
Val([DXCC]) = 230 AND Left([Asema],2) NOT IN ('DQ','DR')); | |||
==Virheellisten tietojen korjailut== | |||
===Bandit=== | |||
Etsi kaikki eri Bandit lokista. | |||
SELECT DISTINCT Bandi FROM Loc4ac | |||
Listauksesta voi silmämääräisesti etsiä sellaiset bandit, jotka eivät ole kelvollisia. Esim. joistain lokiohjelmista tuotaessa tietoja, bandit voivat olla 3,5, 14000 tai metreinä 160. Tämän jälkeen voi listauksen virheelliset korjata [[Loki5ac]]:n erikoistoiminnolla QSO-korjaukset. Toiminto korvaa <u>kaikkien</u> annetun bandin QSOjen bandit toisella, joten kannattaa olla tarkkana. Jos täytyy korjata vain joidenkin QSOjen bandit (esim. kontesti-QSO:t), niin silloin voi käyttää esim. seuraavaa SQL:ää (sisältää rankkoja olettamuksia): | |||
UPDATE loc4ac SET loc4ac.Bandi = "28" | |||
WHERE (((loc4ac.Bandi)="10") AND (((loc4ac.Memo) Is Not Null) AND ((loc4ac.Memo)<>""))); | |||
Tuossa oletetaan että kaikki QSO:t, joiden bandi on "10" ja Memo ei ole tyhjä ovat kontesti-QSOja. Näin monesti onkin, jos ajetaan paljon kilpailuja, eikä samassa kannassa muita QSOja ole. Eikä olisi pahaksi siirtää kilpailuohjelmasta QSOja tyhjään tietokantaan, jolloin korjausten tekeminen on suoraviivaisempaa. | |||
===Päivämäärät=== | |||
Eritilanteissa vastaantulleiden päivämäärien muuntotarpeiden yhteydessä kyhättyjä SQL-lauseita on listattu alla. | |||
QSOjen päivämäärien muunnos muodosta <code>VVVV/KK/PP</code> muotoon <code>PP.KK.VVVV</code>. Varmistetaan ensin että muunnos menee oikein: | |||
SELECT Päivä, (Mid(Trim([Päivä]),9.2)+"."+Mid(Trim([Päivä]),6,2) | |||
+"."+Left(Trim([Päivä]),4)) AS UusiPaiva | |||
FROM loc4ac | |||
WHERE ([Päivä] Like "????/??/??*"); | |||
Jos todetaan että oikealta näyttää, voidaan suorittaa päivitys: | |||
UPDATE loc4ac SET Päivä = (Mid(Trim([Päivä]),9.2)+"."+Mid(Trim([Päivä]),6,2) | |||
+"."+Left(Trim([Päivä]),4)) | |||
WHERE ([Päivä] Like "????/??/??*"); | |||
Vuoden 2001 QSOjen päivämäärien muunnos muodosta <code>PP.KK.VV-V</code> muotoon <code>PP.KK.VVVV</code>. Varmistetaan ensin että muunnos menee oikein: | |||
SELECT Päivä, Left([Päivä],8)+"01" AS UusiPäivä | |||
FROM loc4ac | |||
WHERE ([Päivä] Like "*-*"); | |||
Jos todetaan että oikealta näyttää, voidaan suorittaa päivitys: | |||
UPDATE loc4ac SET Päivä = Left([Päivä],8)+"01" | |||
WHERE ([Päivä] Like "*-*"); | |||
==Alikyselyt== | ==Alikyselyt== | ||
Rivi 168: | Rivi 196: | ||
<table border="1" cellpadding="2" cellspacing="0"> | <table border="1" cellpadding="2" cellspacing="0"> | ||
<tr> | <tr> | ||
<td>'''Funktio''' | <td>'''Funktio'''</td> | ||
<td>'''Tarkoitus''' | <td>'''Tarkoitus'''</td> | ||
<td>'''Esimerkki''' | <td>'''Esimerkki'''</td> | ||
<tr> | </tr><tr> | ||
<td width=100>Left(''a'', ''n'') | <td width=100>Left(''a'', ''n'')</td> | ||
<td width=250>Palauttaa merkkijonosta ''a'' vasemmalta alkaen ''n'' kappaletta merkkejä | <td width=250>Palauttaa merkkijonosta ''a'' vasemmalta alkaen ''n'' kappaletta merkkejä</td> | ||
<td>Left(''Mode'',''3'') palauttaa esim. modesta löytyvästä ''SSTV'' -merkkijonosta ''SST'' | <td>Left(''Mode'',''3'') palauttaa esim. modesta löytyvästä ''SSTV'' -merkkijonosta ''SST''</td> | ||
<tr> | </tr><tr> | ||
<td>Right(''a'', ''n'') | <td>Right(''a'', ''n'')</td> | ||
<td>Palauttaa merkkijonosta ''a'' oikealta alkaen ''n'' kappaletta merkkejä | <td>Palauttaa merkkijonosta ''a'' oikealta alkaen ''n'' kappaletta merkkejä</td> | ||
<td>Right(''Mode'',''3'') palauttaa esim. modesta löytyvästä ''SSTV'' -merkkijonosta ''STV'' | <td>Right(''Mode'',''3'') palauttaa esim. modesta löytyvästä ''SSTV'' -merkkijonosta ''STV''</td> | ||
<tr> | </tr><tr> | ||
<td>Mid(''a'', ''b'', ''n'') | <td>Mid(''a'', ''b'', ''n'')</td> | ||
<td>Palauttaa merkkijonosta a paikasta ''b'' alkaen ''n'' kappaletta merkkejä | <td>Palauttaa merkkijonosta a paikasta ''b'' alkaen ''n'' kappaletta merkkejä</td> | ||
<td>Mid(''[Oma QTH]'', ''3'', ''7'') palauttaa esim. Oma QTH:sta löytyvästä ''Lappeenranta'' -merkkijonosta ''ppeenra'' | <td>Mid(''[Oma QTH]'', ''3'', ''7'') palauttaa esim. Oma QTH:sta löytyvästä ''Lappeenranta'' -merkkijonosta ''ppeenra''</td> | ||
<tr> | </tr><tr> | ||
<td>Len(''a'') | <td>Len(''a'')</td> | ||
<td>Palauttaa merkkijonon ''a'' pituuden | <td>Palauttaa merkkijonon ''a'' pituuden</td> | ||
<td>Len(''[Oma QTH]'') palauttaa esim. ''12'', jos Oma QTH:ssa lukee ''Lappeenranta'' | <td>Len(''[Oma QTH]'') palauttaa esim. ''12'', jos Oma QTH:ssa lukee ''Lappeenranta''</td> | ||
<tr> | </tr><tr> | ||
<td>InStr(''a'', ''b'') | <td>InStr(''a'', ''b'')</td> | ||
<td>Palauttaa alimerkkijonon ''b'' alkukohdan merkkijonosta ''a'' | <td>Palauttaa alimerkkijonon ''b'' alkukohdan merkkijonosta ''a''</td> | ||
<td>InStr(''[Oma QTH]'', ''pp'') palauttaa esim. ''3'', jos Oma QTH:ssa lukee esim. ''Lappeenranta'' | <td>InStr(''[Oma QTH]'', ''pp'') palauttaa esim. ''3'', jos Oma QTH:ssa lukee esim. ''Lappeenranta''</td> | ||
<tr> | </tr><tr> | ||
<td>Int(''a'') | <td>Int(''a'')</td> | ||
<td>Muuntaa merkkijonon ''a'' kokonaisluvuksi | <td>Muuntaa merkkijonon ''a'' kokonaisluvuksi</td> | ||
<td>Int(''"40"'') palauttaa esim. ''40'' | <td>Int(''"40"'') palauttaa esim. ''40''</td> | ||
</table> | </tr></table> | ||
Yksinään näistä funktioista ei juurikaan ole hyötyä, mutta yhdistettynä niillä saadaan purettua lokin tietoja vielä pienempiin osiin. | Yksinään näistä funktioista ei juurikaan ole hyötyä, mutta yhdistettynä niillä saadaan purettua lokin tietoja vielä pienempiin osiin. | ||
Rivi 205: | Rivi 233: | ||
<table border="1" cellpadding="2" cellspacing="0"> | <table border="1" cellpadding="2" cellspacing="0"> | ||
<tr> | <tr> | ||
<td>'''Funktioyhdistelmä''' | <td>'''Funktioyhdistelmä'''</td> | ||
<td>'''Tulos''' | <td>'''Tulos'''</td> | ||
<tr> | </tr><tr> | ||
<td> | <td> | ||
<table border="0" cellpadding="2" cellspacing="0"> | <table border="0" cellpadding="2" cellspacing="0"> | ||
<tr><td>Puretaan DXCC:n maanumero ja CQ-alue erikseen | <tr><td>Puretaan DXCC:n maanumero ja CQ-alue erikseen</td></tr> | ||
<tr><td>Maanumero: <code>Int(Left([DXCC], InStr([DXCC],",")-1))</code> | <tr><td>Maanumero: <code>Int(Left([DXCC], InStr([DXCC],",")-1))</code></td></tr> | ||
<tr><td>CQ-numero: <code>Int(Right([DXCC], Len([DXCC])- InStr([DXCC],",")))</code> | <tr><td>CQ-numero: <code>Int(Right([DXCC], Len([DXCC])- InStr([DXCC],",")))</code></td></tr> | ||
</table> | </table> | ||
<td> | </td><td> | ||
<table border="0" cellpadding="2" cellspacing="0"> | <table border="0" cellpadding="2" cellspacing="0"> | ||
<tr><td>Maa,CQ<td>Maa<td>CQ | <tr><td>Maa,CQ</td><td>Maa</td><td>CQ</td></tr> | ||
<tr><td>263,14<td>263<td>14 | <tr><td>263,14</td><td>263</td><td>14</td></tr> | ||
<tr><td>387,26<td>387<td>26 | <tr><td>387,26</td><td>387</td><td>26</td></tr> | ||
</table> | </table> | ||
<tr> | </td></tr> | ||
<td> | <tr><td> | ||
<table border="0" cellpadding="2" cellspacing="0"> | <table border="0" cellpadding="2" cellspacing="0"> | ||
<tr><td>Erotellaan raportin annettu saatu -osat erikseen | <tr><td>Erotellaan raportin annettu saatu -osat erikseen</td></tr> | ||
<tr><td>Annettu: <code>Left([Raportti], InStr([Raportti],"/")-1)</code> | <tr><td>Annettu: <code>Left([Raportti], InStr([Raportti],"/")-1)</code></td></tr> | ||
<tr><td>Saatu: <code>Right([Raportti], Len([Raportti])- InStr([Raportti],"/"))</code> | <tr><td>Saatu: <code>Right([Raportti], Len([Raportti])- InStr([Raportti],"/"))</code></td></tr> | ||
</table> | </table> | ||
<td> | </td><td> | ||
<table border="0" cellpadding="2" cellspacing="0"> | <table border="0" cellpadding="2" cellspacing="0"> | ||
<tr><td>Raportti <td>Annettu<td>saatu | <tr><td>Raportti</td><td>Annettu</td><td>saatu</td></tr> | ||
<tr><td>599/599 <td>599<td>599 | <tr><td>599/599</td><td>599</td><td>599</td></tr> | ||
<tr><td>56/59+20 <td>56<td>59+20 | <tr><td>56/59+20</td><td>56</td><td>59+20</td></tr> | ||
</table> | </table> | ||
</table> | </td></tr></table> |
Nykyinen versio 26. maaliskuuta 2007 kello 16.50
Tämän artikkelin tarkoitukena on tarjota pääasiassa vinkkejä ja esimerkkejä Loki5ac:n SQL-ristikon käyttöön. Muita Loki5ac:een liittyviä käyttövinkkejä löytyy OH2KXO:n Niksinurkasta.
Johdanto
SQL-kyselyiden suunnittelua helpottaa kun ajattelee että kirjoitettu kysely käy rivi-riviltä läpi jokaisen lokin QSO:n. Ei siis ole mahdollista viitata seuraavaan ja edelliseen riviin, mutta niissä tilanteissa voidaan käyttää alikyselyitä.
Alla olevat esimerkkien esittelykappaleet on järjestetty siten että ensin on SQL-esimerkit Loki5ac:n kannalta katsottuna, artikkelin lopussa vasta on tarkemmat kuvaukset eri funktioista. Esimerkkikokoelmaan on kerätty käyttötarkoituksen mukaan esimerkkejä, niistä löytyy mm. joidenkin awardien QSOjen listausta helpottavia hakulauseita.
Perusteita
SELECT on avainsana hakuja tehtäessä, sen jälkeen tulee mitkä tiedot lokista näytetään. Jos haluat kaikki tiedot näkyville voi käyttää tähteä.
Kun SQL-ristikon ehtokenttiin syötetään seuraavat tiedot...
Kenttä | Ehto | Arvo | Funktio | |
1 | Bandi | = | 3.5 | AND |
2 | Mode | = | CW | AND |
3 | DXCC | = | 230,14 | Napsauta |
... saadaan seuraavanlainen lauseke
SELECT * FROM loc4ac WHERE [Bandi] = '3.5' AND [Mode] = 'CW' AND DXCC = '230,14';
Ylläkirjoitettu hakukysely tarkoittaa samaa kuin: Valitse kaikista QSO:ista tässä lokissa ne joissa bandi on 3.5 ja mode on CW. Kun klikataan painiketta <Suorita> lauseke suoritetaan ja alle SQL-ristikkoon ilmestyvät ne lokissa olevat QSO:t, jotka täyttävät nämä ehdot. Kenttien ympärille tulee hakasulut. Välttämättä niitä ei tarvita kuin kentissä, joiden nimessä on välilyöntejä (esim. [QSL saatu]).
Näytä vain valitut kentät
SQL-ristikossa on mahdollista näyttää vain valitut kentät. Tällöin kyselytekstikenttään on kirjoitettava kysely itse. Periaate on seuraava:
SELECT kenttä1, kenttä2, kenttä3 ...
Ristikossa esitettävien sarakkeiden leveyksiin ei voi vaikuttaa kyselyn luontivaiheessa, mutta kyselyn suorityksen jälkeen kenttien leveyksiä voi muuttaa vanhaan tapaan.
Esimerkki:
SELECT Asema, Päivä, Aika, Bandi, Mode FROM loc4ac;
Tässä tulostuvat nuo viisi kenttää luetellussa järjestyksessä. Tässä ei ole käytetty []-sulkuja, koska kenttien nimissä ei ole välilyöntejä.
Erisuuruus
Erisuuruusmerkkejä voidaan käyttää esimerkiksi aikavalinnoissa ottamalla kentäksi DTfield, joka on muotoa VVVVKKPPTTMM (esim. 200512311930 tarkoittaa 31.12.2005 19:30). Lopusta alkaen voidaan korvata aikamääreitä nollilla, jos niitä ei haluta ottaa huomioon. Kun aikaa ei huomioida pannaan sen tilalle 4 nollaa, edellä olevan esimerkin mukaan siis 200512310000. Ohjelma osaa lisätä loppuun nollat, jos käytetään perusteissa kuvattua valintamenetelmää. Jos kysely kirjoitetaan käsin tekstikenttään, pitää nollat lisätä itse perään. (Nollia käytetty siksi että silloin on vuorokauden vaihto, jolloin QSO:ja ei tule väärältä päivältä.)
Esimerkki:
SELECT * FROM loc4ac WHERE DTfield >= 199911230000 AND DTfield < 200601010000
Näytetään QSO:t, joissa pvm on suurempi tai yhtäsuuri kuin 23.11.1999 kello 00:00 ja pienempi kuin 1.1.2006 kello 00:00 (eli 23.11.1999 ja 31.12.2005 välillä pidetyt QSOt).
ORDER BY
Tuloksen lajittelu tapahtuu ORDER BY -avainsanoilla.
Esimerkki:
SELECT * FROM loc4ac ORDER BY Bandi, Mode;
Järjestää ristikon niin, että QSO't ovat ensisijaisesti bandin ja toissijaisesti moden mukaan järjestyksessä.
LIKE
Kyselylauseissa voit käyttää merkkijonojen kohdalla korvaavia merkkejä (jokerimerkki): %
tarkoittaa mitä merkkejä tahansa, kuinka monta tahansa. _
tarkoittaa yksi merkki, mikä tahansa. Jokerimerkkiä käytettäessä =
-merkki on korvattava LIKE
-avainsanalla.
Esimerkki 1:
SELECT * FROM loc4ac WHERE QTH LIKE '%Vaasa%';
Yllä oleva esimerkki palauttaa kaikki QSOt, joiden QTH:sta löytyy Vaasa jostain kohdasta. Seuraava esimerkki puolestaan palauttaa kaikki OH-asemat, joissa suffix on MM ja piirinumero mikä tahansa.
Esimerkki 2:
SELECT * FROM loc4ac WHERE Asema LIKE 'OH_MM';
IN
Jos halutaan vertailla QSO:n yhtä tietoa useampaan arvoon voidaan käyttää IN-operaatiota usean OR-operaation sijaan.
Esimerkki:
SELECT * FROM loc4ac WHERE Bandi IN("50", "70", "144");
Yllä oleva esimerkki palauttaa kaikki QSOt, joiden bandiksi on merkitty 50, 70 tai 144 MHz. Saman lopputuloksen saa myös ehdolla:
WHERE Bandi="50" OR Bandi="70" OR Bandi="144"
Jos bandit on merkitty metreinä, tällöin pitää käyttää vastaavia metrimääriä (tai tehdä sopiva muunnos SQL:llä).
Eri tapoja maiden listaukseen
Maan numeron saa helposti selville kun kirjaa prefiksin Asemakenttään, jolloin kenttien 'Nr' ja 'CQ' arvot kirjoitetaan peräkkäin pilkulla erotettuna, aivan kuten ne näkyvät tietokantaristikossa. Ne saa selville myös klikkaamalla pääikkunan työkalupalkissa olevaa Maapallo-kuvaketta, mikä avaa maaluettelon. Siellä näkyy mm. rivi
Germany_EU ;DL _14__(230)_
, jossa suluissa on maan numero (230) ja 14 on CQ.
Listaa tiettyjen maiden QSO:t
Tässä tulostuvat Asema-, Päivä-, Aika-, Bandi-, Mode -kentät kaikista saksalaistunnusten kanssa pidetyistä QSO'ista:
SELECT Asema, Päivä, Aika, Bandi, Mode FROM loc4ac WHERE DXCC = '230,14';
Tulostetaan kaikki USA:n QSO:t. USA:n kusoissa maa on 291 CQ-arvot vaihtelevat tai ovat "?", tästä syystä määritettiin että DXCC:n CQ-osa saa olla mitä tahansa (?):
SELECT * FROM loc4ac WHERE DXCC LIKE "291,%";
Palauttaa kaikki QSOt, joiden asema kentän alussa on "9A", ts. kaikki Kroatialaisten asemien kanssa pidetyt QSOt:
WHERE Asema LIKE '9A%';
Awardilistaukset
Award Football World Cup in Germany 2006
Saksalaisten QSO:t ajalla 1.5.2006-31.7.2006 ovat kelvollisia. Lisäksi on joitakin erikoisasemia DR- ja DQ -prefikseillä.
WFC06-awardin erikoisasemat:
SELECT * FROM loc4ac WHERE Val([DTField])>=200605010000 AND Val([DTField])<200608010000 AND Val(DXCC)=230 AND Left(Asema,2) IN ('DR','DQ');
Joukossa saattaa olla muitakin DR- ja DQ-asemia, mutta ne voi karsia käsin pois. Toinen vaihtoehto on naputella kaikki 35 erikoisasemaa IN-sanan jälkeen oleviin sulkuihin DR:n ja DQ:n tilalle.
WFC06-awardin kaikki, paitsi erikoisasemat:
SELECT * FROM loc4ac WHERE Val([DTField])>=200605010000 AND Val([DTField])<200608010000 AND Val(DXCC)=230 AND Left(Asema,2) NOT IN ('DR','DQ');
Tässä vastaavasti voi jäädä muutama tämän awardin kannalta ei-erikoisasema uupumaan, mutta tähän pätee sama kuin edelliseen. Edellisestä listasta voi kätevästi siirtää taulukkolaskennassa QSO:t tämän listauksen jatkoksi.
Määrät saa laskettua vaihtamalla COUNT(*)
tähden tilalle, tällöin tulee kuitenkin tuplistakin laskettua määrät. Alla olevassa esimerkissä lasketaan määrä DL-asemista ilman tuplia:
SELECT COUNT(*) FROM (SELECT DISTINCT [Asema] FROM loc4ac WHERE Val([DTField])>=200605010000 AND Val([DTField])<200608010000 AND Val([DXCC]) = 230 AND Left([Asema],2) NOT IN ('DQ','DR'));
Virheellisten tietojen korjailut
Bandit
Etsi kaikki eri Bandit lokista.
SELECT DISTINCT Bandi FROM Loc4ac
Listauksesta voi silmämääräisesti etsiä sellaiset bandit, jotka eivät ole kelvollisia. Esim. joistain lokiohjelmista tuotaessa tietoja, bandit voivat olla 3,5, 14000 tai metreinä 160. Tämän jälkeen voi listauksen virheelliset korjata Loki5ac:n erikoistoiminnolla QSO-korjaukset. Toiminto korvaa kaikkien annetun bandin QSOjen bandit toisella, joten kannattaa olla tarkkana. Jos täytyy korjata vain joidenkin QSOjen bandit (esim. kontesti-QSO:t), niin silloin voi käyttää esim. seuraavaa SQL:ää (sisältää rankkoja olettamuksia):
UPDATE loc4ac SET loc4ac.Bandi = "28" WHERE (((loc4ac.Bandi)="10") AND (((loc4ac.Memo) Is Not Null) AND ((loc4ac.Memo)<>"")));
Tuossa oletetaan että kaikki QSO:t, joiden bandi on "10" ja Memo ei ole tyhjä ovat kontesti-QSOja. Näin monesti onkin, jos ajetaan paljon kilpailuja, eikä samassa kannassa muita QSOja ole. Eikä olisi pahaksi siirtää kilpailuohjelmasta QSOja tyhjään tietokantaan, jolloin korjausten tekeminen on suoraviivaisempaa.
Päivämäärät
Eritilanteissa vastaantulleiden päivämäärien muuntotarpeiden yhteydessä kyhättyjä SQL-lauseita on listattu alla.
QSOjen päivämäärien muunnos muodosta VVVV/KK/PP
muotoon PP.KK.VVVV
. Varmistetaan ensin että muunnos menee oikein:
SELECT Päivä, (Mid(Trim([Päivä]),9.2)+"."+Mid(Trim([Päivä]),6,2) +"."+Left(Trim([Päivä]),4)) AS UusiPaiva FROM loc4ac WHERE ([Päivä] Like "????/??/??*");
Jos todetaan että oikealta näyttää, voidaan suorittaa päivitys:
UPDATE loc4ac SET Päivä = (Mid(Trim([Päivä]),9.2)+"."+Mid(Trim([Päivä]),6,2) +"."+Left(Trim([Päivä]),4)) WHERE ([Päivä] Like "????/??/??*");
Vuoden 2001 QSOjen päivämäärien muunnos muodosta PP.KK.VV-V
muotoon PP.KK.VVVV
. Varmistetaan ensin että muunnos menee oikein:
SELECT Päivä, Left([Päivä],8)+"01" AS UusiPäivä FROM loc4ac WHERE ([Päivä] Like "*-*");
Jos todetaan että oikealta näyttää, voidaan suorittaa päivitys:
UPDATE loc4ac SET Päivä = Left([Päivä],8)+"01" WHERE ([Päivä] Like "*-*");
Alikyselyt
Tietokantafunktiot
- COUNT
- SUM
- MIN
- MAX
- AVG
- DISTINCT
- DISTINCTROW
Visual Basic -funktiot ja SQL-ristikko
MSDN Visual Basic 6.0 Reference sisältää täydellisen ja ajantasaisen listan käytettävissä olevista funktioista. Funktioiden parametrit erotetaan pilkulla (,), joissain vanhemmissa tietokantaversioissa erottimena oli puolipiste (;).
Merkkijonojen käsittely
SQL-ristikossa VB-funktioiden merkkijono-parametri on useimmiten joku tietokannan kentistä, eli lokista poimittu yksi tieto. Samoin on joskus muidenkin parametrien kanssa, kuten esimerkeistä saattaa huomata.
Funktio | Tarkoitus | Esimerkki |
Left(a, n) | Palauttaa merkkijonosta a vasemmalta alkaen n kappaletta merkkejä | Left(Mode,3) palauttaa esim. modesta löytyvästä SSTV -merkkijonosta SST |
Right(a, n) | Palauttaa merkkijonosta a oikealta alkaen n kappaletta merkkejä | Right(Mode,3) palauttaa esim. modesta löytyvästä SSTV -merkkijonosta STV |
Mid(a, b, n) | Palauttaa merkkijonosta a paikasta b alkaen n kappaletta merkkejä | Mid([Oma QTH], 3, 7) palauttaa esim. Oma QTH:sta löytyvästä Lappeenranta -merkkijonosta ppeenra |
Len(a) | Palauttaa merkkijonon a pituuden | Len([Oma QTH]) palauttaa esim. 12, jos Oma QTH:ssa lukee Lappeenranta |
InStr(a, b) | Palauttaa alimerkkijonon b alkukohdan merkkijonosta a | InStr([Oma QTH], pp) palauttaa esim. 3, jos Oma QTH:ssa lukee esim. Lappeenranta |
Int(a) | Muuntaa merkkijonon a kokonaisluvuksi | Int("40") palauttaa esim. 40 |
Yksinään näistä funktioista ei juurikaan ole hyötyä, mutta yhdistettynä niillä saadaan purettua lokin tietoja vielä pienempiin osiin.
Funktioiden yhdistelyesimerkkejä
Alla olevassa taulukossa on esitetty edellisen taulukon funktioiden yhdistelyä.
Funktioyhdistelmä | Tulos | ||||||||||||
|
| ||||||||||||
|
|