SQL dotazy
Kapitola popisuje základné stavebné prvky dotazov použitých v ArcGIS. Dotazy v ArcGIS používajú spoločnú SQL syntax.
Poznámka: SQL syntax nefunguje v prostredí Kalkulátora (Field Calculator).
Ak chcete zadať názov poľa v SQl, je potrebné toto pole oddeliť, ináč by bolo pre dotaz nečitateľné. Syntax by ho považoval za vyhradené kľúčové slovo SQL.
Pretože existuje množstvo vyhradených slov a môžu pribúdať nové (podľa verzie) je potrebné názov poľa oddeliť oddeľovačom.
Názov oddeľovača sa líši od verzie DBMS. Ak používate súborovú alebo SDE geodatabázu, môžete názov poľa oddeliť uvodzovkami:
"AREA" |
Pre personálnu geodatabázu sa ako oddeľovač použije hranatá zátvorka:
[AREA] |
Pre rastrové dáta uložené v personálnej geodatabáze názvy polí sa uzavrú uvodzovkymi:
"AREA" |
Textové hodnoty musia byť uzavreté jednoduchými uvodzovkami:
STATE_NAME = 'California' |
UPPER("STATE_NAME") = 'RHODE ISLAND' |
V dotazoch môžete použiť aj zástupné znaky na vyhľadávanie časti textových hodnôt. Príklad ukazuje ako vyhľadáme názov Mississippi a Missouri vo vrstve štátov:
"STATE_NAME" LIKE 'Miss%' |
Na mieste % (zástupný znak) môže byť jeden, sto alebo žiadny znak. Na dotazovanie môžete použiť aj hviezdičku (*) pre ľubovoľné čísla alebo ? pre jeden znak.
V dotaze môžu byť použité aj textové reťazce. Napríklad, funkcia LEFT vráti určitý počet znakov začínajúcich na ľavej strane reťazca. Dotaz vráti všetky štáty začínajúce na písmeno A:
LEFT("STATE_NAME",1) = 'A' |
Zoznam podporovaných funkcií nájdete v dokumentácii k Vašej DBMS.
Desatinná bodka (.) sa vždy používa ako oddeľovač, bez ohľadu na regionálne nastavenie. Čiarka sa nepoužíva ani na oddelenie desiatok, ani tisícok v dotazoch.
V číselných dotazoch môžete použiť aj operátory = (rovná sa), <> (rôzne), > (väčší), < (menší), => (väčší alebo rovný), <= (menší alebo rovný), alebo BETWEEN. Napríklad:
"POPULATION" >= 5000 |
Číselné funkcie môžu byť použité na formátované čísiel. Napríklad, funkcia ROUND zaokrúhli číslo na daný počet desatinných miest v súborovej geodatabáze:
ROUND("SQKM",0) = 500 |
Zoznam podporovaných funkcií nájdete v dokumentácii k Vašej DBMS.
Pravidlá
Geodatabázy umožňujú vytvárať dátumové aj dátumovo-časové polia, ArcInfo coverage a shapefile nie.
Z tohto dôvodu, väčšina zo syntaxe uvedenej nižšie sa bude venovať predovšetkým časovým funkciám. V niektorých prípadoch možeme čas ako časť dotazu bezpečne vynechať, ak vieme že pole obsahje iba dátumové hodnoty. V inom prípade je toto potrebné uviesť na začiatku dotazu, pretože ináč dotaz vráti chybu syntaxu
Prioritou pre dátumové pole v ArcMap je ukladanie dátumu nie času. Je možné ukladať iba čas, ak databáze obsahuje dátumovo-časové pole, ale to sa nedoporučuje. Napr. 12:30:05 p.m. sa uloží ako '1899-12-30 12:30:05'.
Poznámka: Dátumy sú uložené v databáze ako odkaz na December 30, 1899, 00:00:00. Toto platí pre všetky databázy.
Účelom tohto odstavca je pomôcť Vám vytvoriť dotaz s dátumovými poliami nie časovými. Ak je vyplnený čas spolu s dátumovými hodnotami (napr. January 12, 1999, 04:00:00), dotaz iba s dátumom nevráti záznam pokiaľ použijeme iba dátum v dátumovo-časovom poli. Čas sa nastaví na nulu a vrátia sa iba záznamy kde čas je 12:00:00 a.m.
Atribučná tabuľka ukazuje dátum a čas v user-friendly formáte, v závislosti od miestnych nastavení. Pre väčšinu operácií je to v poriadku, ale má to aj niekoľko nevýhod:
Informix
Datefield = 'yyyy-mm-dd hh:mm:ss' |
hh:mm:ss ako časť dotazu nemožno vynechať, ak je čas rovný 00:00:00
Oracle
Datefield = date 'yyyy-mm-dd' |
Treba pamätať na to, že dotaz nevráti záznamy ak čas nie je prázdny.
Alternatívny formát pre Oracle je:
Datefield = TO_DATE('yyyy-mm-dd hh:mm:ss','YYYY-MM-DD HH24:MI:SS') |
Druhý prameter 'YYYY-MM-DD HH24:MI:SS' popisuje formát pre dotaz. Skutočný dotaz bude vyzerať takto:
Datefield = TO_DATE('2003-01-08 14:35:00','YYYY-MM-DD HH24:MI:SS') |
Môžete použiť kratšiu verziu:
TO_DATE('2003-11-18','YYYY-MM-DD') |
Opäť, dotaz nevráti záznam pokiaľ čas je prázdny.
SQL Server
Datefield = 'yyyy-mm-dd hh:mm:ss' |
hh:mm:ss ako časť dotazu nemožno vynechať, ak nie je nastavený v záznamoch.
Alternatívny formát:
Datefield = 'mm/dd/yyyy' |
IBM DB
Datefield = TO_DATE('yyyy-mm-dd hh:mm:ss','YYYY-MM-DD HH24:MI:SS') |
hh:mm:ss ako časť dotazu nemožno vynechať, ak je čas rovný 00:00:00.
PostgreSQL
Datefield = TIMESTAMP 'YYYY-MM-DD HH24:MI:SS' Datefield = TIMESTAMP 'YYYY-MM-DD' |
Ak v dotaze použijete operátor "rovná sa", musíte zadať presný čas, inak sa nezobrazí žiaden záznam. Úspešní budete iba ak v tabuľke sa nachádzajú dátumové dáta s presnou špecifikáciou (2007-05-29 00:00:00 alebo 2007-05-29 12:14:25):
select * from table where date = '2007-05-29 00:00:00'; |
alebo
select * from table where date = '2007-05-29 12:14:25'; |
Ak používate ostatné operátory (>; <; =>; <=) ale nemáte určený čas, možete ich použiť ak chcete aby výsledok bol presný. Oba dotazy budú fungovať:
select * from table where date < '2007-05-29'; |
select * from table where date < '2007-05-29 12:14:25'; |
Súborová geodatabáza, shapefiles, coverages a iné databázy
Dáta v geodatabázach, shapefiles a coverages sú ukladané iba ako dátum.
"Datefield" = date 'yyyy-mm-dd' |
Súborová geodatabáza podporuje ukladanie aj časových hodnôt v dátumových poliach. Tieto potom môžu byť pridané do dotazu:
"Datefield" = date 'yyyy-mm-dd hh:mm:ss' |
Shapefiles a coverages nepodporujú ukladanie časových hodnôt do dátumových polí.
Poznámka: Všetky SQL dotazy použité so súborovou databázou sú ukladané v štandarde SQL-92.
Personálna geodatabáza
Dátumy v personálnej geodatabáze sú ohraničené znakom (#).
Napríklad:
[Datefield] = #mm-dd-yyyy hh:mm:ss# |
Môžeme to skráťiť na [Datefield] = #mm-dd-yyyy#.
Iný formát:
[Datefield] = #yyyy/mm/dd# |
Tvorba dotazov s dátami vzniknutých prepojením viiacerých tabuliek (join) funguje iba v súborovej geodatabáze, shapefiles a DBF tabuľkách. Avšak je možnosť ako pracovať s takýmito dátami v personálnej geodatabáze a SDE databáze. Pozri nižšie.
Dotazovanie na spojené dáta je možné iba pri použití obmedzenej SQL verzie vyvinutej pre databázy. Ak ju nepoužívate ako zdroj dát, môžete použiť nasledovný formát. Uistite sa, že dotaz zahŕňa aj polia viac ako z jednej pripojenej tabuľky.
Napríklad, ak je feature class a tabuľka (FC1 a Table1) spojená a obe sú z personálnej geodatabázy, nasledujúci dotaz zlyhá alebo výsledkom bud´žiadne dáta:
FC1.date = date #01/12/2001# FC1.date = date '01/12/2001' |
Aby bol dotaz úspešný:
FC1.date = date '01/12/2001' and Table1.OBJECTID > 0 |
Vzhľadom k tomu, že dotaz zahŕňa polia z oboch tabuliek, bude použitá obmedzená verzia SQL. V tomto dotaze Table1.OBJECTID je stále > 0 pre záznamy, ktoré vyhovovali počas celého spojenia. Preto tento dotaz je pravdivý pre všetky riadky, ktoré obsahujú pripojenie join.
Ak chcete zabezpečiť aby každý záznam FC1.date = date '01/12/2001' bol vybratý, použite:
FC1.date = date '01/12/2001' and (Table1.OBJECTID IS NOT NULL OR Table1.OBJECTID IS NULL)
Tento dotaz vyberie všetky záznamy FC1.date = date '01/12/2001', ktoré boli alebo neboli pripojené pre každý konkrétny záznam.
Coverages, shapefiles a iné databázy mimo geodatabáz nepodporujú poddotazy. Poddotazy, ktoré sú vykonávané na verzionovanej SDE databáze nevrátia dáta, ktoré sú uložené v delta tabuľkách. Podpora súborovej geodatabázy je obmedzená, pre SDE je plná podpora poddotazov. Pre podrobnejšie informácie o poddotazoch si pozrite dokumentáciu k svojej DBMS.
Poddotaz je dotaz vnorený do iného dotazu. Môže byť použitý ako agregačná funkcia alebo na porovnanie dát uložených v inej tabuľke. Toto môžeme urobiť pomocou operátorov IN a ANY. Napríklad, dotaz vyberie iba krajiny, ktoré sa nenachádzajú v tabuľke indep_countries:
"COUNTRY_NAME" NOT IN (SELECT "COUNTRY_NAME" FROM indep_countries) |
Tento dotaz vráti dáta, kde GDP2006 je väčšie ako GDP2005 niektorého z prvkov obsiahnutých v tejto vrstve:
"GDP2006" > (SELECT MAX("GDP2005") FROM countries) |
Pre každý záznam v tabuľke, môže poddotaz anylyzovať všetky dáta v cieľovej tabuľke. Toto môže byť veľmi pomalý proces pre veľke sady dát.
Podpora poddotazov v súborovej geodatabáze je obmedzená nasledovne:
"COUNTRY_NAME" NOT IN (SELECT "COUNTRY_NAME" FROM indep_countries) |
"GDP2006" > (SELECT MAX("GDP2005") FROM countries) |
Pre súborovú geodatabázu vstavané funkcie AVG, COUNT, MIN, MAX, SUM a môžu byť použité iba v rámci skalárnych poddotazov.
EXISTS (SELECT * FROM indep_countries WHERE "COUNTRY_NAME" = 'Mexico') |
Toto je úplný zoznam operátorov pre dotazy, ktoré podporujú súborové geodatabázy, shapefile, coverage, a iné databázy. Sú podporované aj personálne aj SDE geodatabázy, aj keď sú tieto dátové zdroje môžu vyžadovať odlišnú syntax alebo názov funkcie. Okrem nižšie uvedených funkcií, personálne a SDE geodatabázy podporujú aj ďalšie funkcie.
Môžete použiť aritmetický operátor sčítanie, odčítanie, násobenie a delenie číselných hodnôt.
| Operátor | Popis |
|---|---|
| * | Aritmetický operátor pre násobenie |
| / | Aritmetický operátor pre delenie |
| + | Aritmetický operátor pre ščítanie |
| - | Aritmetický operátor pre odčítanie |
Môžete použiť porovnávacie operátory na porovnanie jedného dotazu s iným.
| Operátor | Popis | |||||
|---|---|---|---|---|---|---|
| < | Operátor môže byť použitý s reťazcami (porovnanie na základe abecedného poradia), čísel a dát. | |||||
| <= | Operátor môže byť použitý s reťazcami (porovnanie na základe abecedného poradia), čísel a dát. | |||||
| <> | Operátor môže byť použitý s reťazcami (porovnanie na základe abecedného poradia), čísel a dát. | |||||
| > | Operátor môže byť použitý s reťazcami (porovnanie na základe abecedného poradia), čísel a dát. | |||||
| => | Operátor môže byť použitý s reťazcami (porovnanie na základe abecedného poradia), čísel a dát.
|
|||||
| [NOT] BETWEEN x AND y | Vyberie záznam, ak je hodnota väčšia ako alebo rovná hodnote X a menšia alebo rovná Y.
|
|||||
| [NOT] EXISTS | Vráti hodnotu TRUE, ak poddotaz vráti aspoň jeden záznam; inak dotaz vráti FALSE.
Operátor EXISTS je podporovaný pre personálnu, súborovú aj SDE geodatabázu. |
|||||
| [NOT] IN | Vyberie záznam, ak má jeden z niekoľkých reťazcov alebo hodnôt v poli.
|
|||||
| IS [NOT] NULL | Vyberie záznam, ak má hodnotu null pre určené pole.
Ak je prameter NULL nastavený na NOT, vyberie záznam, ktorý obsahuje nejakú hodnotu.
|
|||||
| x [NOT] LIKE y [ESCAPE 'escape-character'] | Pomocou operátora LIKE (miesto = operátor) spolu so zástupnými znakmi sa čiastočne nahradí hľadaný reťazec.
|
| Operátor | Popis | |
|---|---|---|
| AND | Kombinuje dve podmienky dohromady a vyberie záznam, ak sú splnené obe podmienky.
|
|
| OR | Kombinuje dve podmienky dohromady a vyberie záznam, ak je splnená aspoň jedna podmienka.
|
|
| NOT | Vyberie záznam, ktorý nezodpovedá dotazu.
|
Toto je úplný zoznam funkcií, ktoré podporujú súborové geodatabázy, shapefile, coverage, a iné databázy. Sú podporované aj personálne aj SDE geodatabázy, aj keď sú tieto dátové zdroje môžu vyžadovať odlišnú syntax alebo názov funkcie. Okrem nižšie uvedených funkcií, personálne a SDE geodatabázy podporujú aj ďalšie funkcie.
| Funkcia | Popis |
|---|---|
| CURRENT_DATE | Vráti aktuálny dátum |
| EXTRACT(extract_field FROM extract_source) | Vráti extract_field z extract_source. Extract_source argument je dátum, čas dotaz. Extract_field argument môže byť jeden z nasledujúcich kľúčových slov: rok, mesiac, deň, hodina, minúta alebo sekunda |
| CURRENT TIME | Vráti aktuálny čas |
Argumenty označované ako asstring_exp môžu byť názov stĺpca, znak, reťazec-doslovný, alebo výsledok inej skalárnej funkcie, kde môže byť základný dátový typ reprezentovaný ako typ znaku.
Argumenty označené ako ascharacter_exp sú premenné dĺžky reťazca znakov.
Argumenty označené ako asstart alebo lenght môžu byť číselné alebo výsledok inej skalárne funkcie, kde môže byť základný dátový typ reprezentovaný ako číselný typ.
Tieto textové funkcie sú založené na 1; to znamená, že prvý znak v reťazci je znak 1.
| Funkcia | Popis |
|---|---|
| CHAR_LENGTH(string_exp) | Vráti dĺžku reťazca |
| CONCAT(string_exp1, string_exp2) | Vráti reťazec znakov, ktorý je výsledkom spojenia string_exp2 a string_exp1 |
| LOWER(string_exp) | Vráti reťazec, ktorý je rovný string_exp a všetky veľké znaky zmení na malé |
| POSITION(character_exp IN character_exp) | Vracia pozíciu prvého znaku dotazu. Výsledkom je presné číslo. |
| SUBSTRING(string_exp FROM start FOR length) | Vráti reťazec znakov, ktorý je odvodený od string_exp, začínajúceho na pozícii znaku určeného začiatkom a dĺžkou reťazca |
| TRIM(BOTH | LEADING | TRAILING trim_character FROM string_exp) | Vráti reťazec string_exp odstránený reťazcom trim_characterzo začiatku alebo konca reťazca |
| UPPER(string_exp) | Vráti reťazec, ktorý je rovný string_exp a všetky malé znaky zmení na veľké |
Všetky číselné funkcie vracajú číselné hodnoty.
Argumenty označené ako numeric_exp alebo integer_exp možu byť mená stĺpcov alebo výsledok iných skalárnych funkcií alebo čísle, kde môže byť základný dátový typ reprezentovaný ako číselný typ.
| Funkcia | Popis |
|---|---|
| ABS(numeric_exp) | Vráti absolútne číslo numeric_exp |
| ACOS(float_exp) | Vráti arccos float_exp ako uhol v radiánoch |
| ASIN(float_exp) | Vráti arcsin float_exp ako uhol v radiánoch |
| ATAN(float_exp) | Vráti arctangent float_exp ako uhol v radiánoch |
| CEILING(numeric_exp) | Vráti najmenšie celé číslo, väčšie alebo rovnajúce sa numeric_exp |
| COS(float_exp) | Vráti kosínus float_exp ako uhol v radiánoch |
| FLOOR(numeric_exp) | Vráti najväčšie celé číslo, menšie alebo rovnajúce sa numeric_exp |
| LOG(float_exp) | Vráti absolútne číslo numeric_exp |
| LOG10(float_exp) | Vráti logaritmus float_exp |
| MOD(integer_exp1, integer_exp2) | Vráti zvyšok integer_exp1 po delení integer_exp2 |
| POWER(numeric_exp, integer_exp) | Vráti hodnotu numeric_exp ako s váhou integer_exp |
| ROUND(numeric_exp, integer_exp) | Vráti hodnotu numeric_exp zaokrúhlenú na integer_exp miesto napravo od desatinnej čiarky. Ak hodnotainteger_exp je negatívna, numeric_exp sa zaokrúhli integer_exp na miesto vľavo od desatinnej čiarky. |
Funkcia CAST prevedie hodnotu na zadaný dátový typ. Syntax je nasledujúca:
CAST(exp AS data_type)
Argument exp môže byť názov stĺpca, výsledok inej skalárne funkcie, alebo doslovný. Data_type môže byť niektorý z nasledujúcich kľúčových slov, ktoré môžu byť uvedené veľkými alebo malými písmenami: CHAR, VARCHAR, INTEGER, SMALLINT, REAL, DOUBLE, dátum, čas DATETIME, NUMERIC, alebo desatinných miest.