github.com/mithrandie/csvq@v1.18.1/docs/_posts/2006-01-02-statement.md (about) 1 --- 2 layout: default 3 title: Statements - Reference Manual - csvq 4 category: reference 5 --- 6 7 # Statements 8 9 * [Basics](#basics) 10 * [Parsing](#parsing) 11 * [Comments](#comments) 12 * [Reserved Words](#reserved_words) 13 14 ## Basics 15 {: #basics} 16 17 You can pass a query or statements(it's also called procedure) as a csvq command argument or a source file. 18 Statements have to be encoded in UTF-8. 19 20 A statement is terminated with a semicolon. 21 In statements, character case of keywords is ignored. 22 23 If you want to execute a single query, you can omit the terminal semicolon. 24 25 ### Interactive Shell 26 27 When the csvq command is called with no argument and no "--source" (or "-s") option, the interactive shell is launched. 28 You can use the interactive shell in order to sequencial input and execution. 29 30 If you want to continue to input the statement on the next line, you can use Backslash(U+005C `\`) at the end of the line to continue. 31 32 #### Command options in the interactive shell 33 34 --out 35 : Ignored 36 37 --stats 38 : Show only Query Execution Time 39 40 ```bash 41 # Execute a single query 42 $ csvq 'SELECT id, name FROM user' 43 44 # Execute statements 45 $ csvq 'VAR @id := 0; SELECT @id := @id + 1 AS id, name FROM user;' 46 47 # Load statements from file 48 $ cat statements.sql 49 VAR @id := 0; 50 SELECT @id := @id + 1 AS id, 51 name 52 FROM user; 53 54 $ csvq -s statements.sql 55 56 # Execute in the interactive shell 57 $ csvq 58 csvq > SELECT id, name FROM users; 59 +----+-------+ 60 | id | name | 61 +----+-------+ 62 | 1 | Louis | 63 | 2 | Sean | 64 +----+-------+ 65 csvq > UPDATE users SET name = 'Mildred' WHERE id = 2; 66 1 record updated on "/home/mithrandie/docs/csv/users.csv". 67 csvq > SELECT id, name FROM users; 68 +----+----------+ 69 | id | name | 70 +----+----------+ 71 | 1 | Louis | 72 | 2 | Mildred | 73 +----+----------+ 74 csvq > COMMIT; 75 Commit: file "/home/mithrandie/docs/csv/users.csv" is updated. 76 csvq > IF (SELECT name FROM users WHERE id = 2) = 'Mildred' THEN 77 > PRINT TRUE; \ 78 > ELSE 79 > PRINT FALSE; \ 80 > END IF; 81 TRUE 82 csvq > EXIT; 83 ``` 84 85 ## Parsing 86 {: #parsing} 87 88 You can use following types in statements. 89 90 Identifier 91 : An identifier is a word starting with any unicode letter or a Low Line(U+005F `_`) and followed by a character string that contains any unicode letters, any digits or Low Lines(U+005F `_`). 92 You cannot use [reserved words](#reserved_words) as an identifier. 93 94 Notwithstanding above naming restriction, you can use most character strings as an identifier by enclosing in Grave Accents(U+0060 \` ) or Quotation Marks(U+0022 `"`) if [--ansi-quotes]({{ '/reference/command.html#options' | relative_url }}) is specified. 95 Enclosure characters are escaped by backslashes or double enclosures. 96 97 Identifiers represent tables, columns, functions or cursors. 98 Character case is insensitive except file paths, and whether file paths are case-insensitive or not depends on your file system. 99 100 String 101 : A string is a character string enclosed in Apostrophes(U+0027 `'`) or Quotation Marks(U+0022 `"`) if [--ansi-quotes]({{ '/reference/command.html#options' | relative_url }}) is not specified. 102 In a string, enclosure characters are escaped by backslashes or double enclosures. 103 104 Integer 105 : An integer is a word that contains only \[0-9\]. 106 107 Float 108 : A float is a word that contains only \[0-9\] with a decimal point, or its exponential notation. 109 110 Ternary 111 : A ternary is represented by any one keyword of TRUE, UNKNOWN or FALSE. 112 113 Null 114 : A null is represented by a keyword NULL. 115 116 Variable 117 : A [variable]({{ '/reference/variable.html' | relative_url }}) is a word starting with "@" and followed by a character string that contains any unicode letters, any digits or Low Lines(U+005F `_`). 118 119 Flag 120 : A [flag]({{ '/reference/flag.html' | relative_url }}) is a word starting with "@@" and followed by a character string that contains any unicode letters, any digits or Low Lines(U+005F `_`). Character case is ignored. 121 122 Environment Variable 123 : A [environment variable]({{ '/reference/environment-variable.html' | relative_url }}) is a word starting with "@%" and followed by a character string that contains any unicode letters, any digits or Low Lines(U+005F `_`). 124 If a environment variable includes other characters, you can use the variable by enclosing in Back Quotes(U+0060 ` ). 125 126 Runtime Information 127 : A [runtime information]({{ '/reference/runtime-information.html' | relative_url }}) is a word starting with "@#" and followed by a character string that contains any unicode letters, any digits or Low Lines(U+005F `_`). Character case is ignored. 128 129 System Defined Constant 130 : A [system defined constant]({{ '/reference/system-defined-constant.html' | relative_url }}) is a group of words represented by two words separated by "::". Character case is ignored. 131 132 ``` 133 abcde -- identifier 134 識別子 -- identifier 135 `abc\`de` -- identifier 136 `abc``de` -- identifier 137 'abcd\'e' -- string 138 'abcd''e' -- string 139 123 -- integer 140 123.456 -- float 141 true -- ternary 142 null -- null 143 @var -- variable 144 @@FLAG -- flag 145 @%ENV_VAR -- environment variable 146 @%`ENV_VAR` -- environment variable 147 @#INFO -- runtime information 148 CATEGORY::NAME -- system defined constant 149 150 /* if --ansi-quotes is specified */ 151 "abcd\"e" -- identifier 152 "abcd""e" -- identifier 153 154 /* if --ansi-quotes is not specified */ 155 "abcd\"e" -- string 156 "abcd""e" -- string 157 ``` 158 159 ## Comments 160 {: #comments} 161 162 Line Comment 163 : A single line comment starts with a string "--" and ends with a line-break character. 164 165 Block Comment 166 : A block comment starts with a string "/\*" and ends with a string "\*/". 167 168 169 ```sql 170 /* 171 * Multi Line Comment 172 */ 173 VAR @id /* In Line Comment */ := 0; 174 175 -- Line Comment 176 SELECT @id := @id + 1 AS id, -- Line Comment 177 name 178 FROM user; 179 ``` 180 181 ## Reserved Words 182 {: #reserved_words} 183 184 ABSOLUTE ADD AFTER AGGREGATE ALTER ALL AND ANY AS ASC AVG 185 BEFORE BEGIN BETWEEN BREAK BY 186 CASE CHDIR CLOSE COMMIT CONTINUE COUNT CREATE CROSS CSV_INLINE CUME_DIST CURRENT CURSOR 187 DECLARE DEFAULT DELETE DENSE_RANK DESC DISPOSE DISTINCT DO DROP DUAL 188 ECHO ELSE ELSEIF END EXCEPT EXECUTE EXISTS EXIT 189 FALSE FETCH FIRST FIRST_VALUE FOLLOWING FOR FROM FULL FUNCTION 190 GROUP 191 HAVING 192 IF IGNORE IN INNER INSERT INTERSECT INTO IS 193 JOIN JSONL JSON_AGG JSON_INLINE JSON_OBJECT JSON_ROW JSON_TABLE 194 LAG LAST LAST_VALUE LATERAL LEAD LEFT LIKE LIMIT LISTAGG 195 MAX MEDIAN MIN 196 NATURAL NEXT NOT NTH_VALUE NTILE NULL 197 OFFSET ON ONLY OPEN OR ORDER OUTER OVER 198 PARTITION PERCENT PERCENT_RANK PRECEDING PREPARE PRINT PRINTF PRIOR PWD 199 RANGE RANK RECURSIVE RELATIVE RELOAD REMOVE RENAME REPLACE RETURN RIGHT ROLLBACK ROW ROW_NUMBER 200 SELECT SEPARATOR SET SHOW SOURCE STDEV STDEVP STDIN SUBSTRING SUM SYNTAX 201 TABLE THEN TO TRIGGER TRUE 202 UNBOUNDED UNION UNKNOWN UNSET UPDATE USING 203 VALUES VAR VARP VIEW 204 WHEN WHERE WHILE WITH WITHIN 205