Sql notes

Da Wikipedia, l'enciclopedia libera.

Contents 1 Descrizione 2 Funzioni 3 Operatori 4 Note 5 Esempi 6 Vedi anche

Descrizione

Supporto SQL per GRASS.

E' supportato un gruppo limitato di funzioni e operatori SQL.

Funzioni

ALTER TABLE table ADD [COLUMN] columndef
CREATE TABLE table ( columndefs )
DROP TABLE table
SELECT columns FROM table
SELECT columns FROM table WHERE condition
DELETE FROM table
DELETE FROM table WHERE condition
INSERT INTO table values
INSERT INTO table ( columns ) values
UPDATE table SET assignments
UPDATE table SET assignments WHERE condition

Operatori

"="  : equal
"<"  : smaller than
"<=" : smaller/equal than
">"  : larger than
">=" : larger/equal than
"<>" : not equal
"~"  : Substring matching

Note

• SQL non supporta '.' (punto) nei nomi delle tabelle.
• I caratteri supportati nei nomi di tabella sono solo:
  [A-Za-z][A-Za-z0-9_]*
• Un nome di tabella deve iniziare con un carattere alfabetico e non con un numero.
• Brackets are needed, e.g. for

select * from some_table where (3*(some_field - another_field) > 5)

• The parser does not know about precedence yet, so the sub-clause has to be in brackets to avoid ambiguity.
• Text-string matching requires the text part to be 'single quoted' and multiple queries should be contained in "double quotes". e.g.

d.vect map where="individual='juvenile' and area='beach'"

• An error message such as "dbmi: Protocol error" either indicates an invalid column name or an unsupported column type (then the GRASS SQL parser needs to be extended).
• DBF column names are limited to 10 characters (DBF API definition)

Esempi

Display all vector points except for LAMAR valley and extensive trapping:

d.vect trapping_sites_points fcol=black icon=basic/diamond col=white size=13 \
where="valley <> 'LAMAR' OR (valley = 'LAMAR' AND description = 'extensive trapping')"

Select all attributes from table where str1 column values are not 'No Name':

echo "SELECT * FROM archsites WHERE str1 <> 'No Name'" | db.select

Vedi anche

• db.execute
• db.select
• db.tables
• d.vect
• SQL reference links