Help for SQL Query
Outline
The 'SQL Query' is the search function using the SQL.
A SQLite DB table is created from the current workbook or a file and is searched.
The search result can be pasted to a sheet.
Or it can be loaded as a new sheet.
Cell styles are not maintained in DB tables.
The following screen is shown, when the 'SQL Query' is selected from the menu of the cell/column/row/sheet or when the 'Load via SQL Query' is selected from the menu of the data file.
- A is used for creating a DB table.
- B is used for searching the DB table.
- C is used for showing result.
- D shows the menu. It is used for loading the result as a new sheet in the main screen. It is also used for displaying the help and the DB table list.
Creating DB Table
A DB table is created from the current workbook or a file
by using 'create' and 'insert' statements of SQLite.
The creation from the current workbook is as follows.
- On first line, select 'Current Workbook'.
- On second line, specify the sheet number and range indicating the source data for a DB table.
Appoint the range of all data using for SQL processing including search and display.
You can appoint a cell range before calling SQL query.
- On third line, specify the creating table name, and touch the button 'Create'.
- At success, the result field shows 'Success'.
The creation from a file is as follows.
- On first line, select 'File'.
- On second line, pick up the file.
- On third line, specify the creating table name, and touch the button 'Create'.
- At success, the result field shows 'Success'.
The column names of the DB table are same as the column references of the source.
And the column 'id' is added for the serial numbers.
Except 'id', each item is stored as String.
When an existing DB table is specified, the DB table is overwritten.
Plural DB tables can be made by changing the DB table name.
Searching Data
Data is searched with the 'select' statement of SQLite by the SQL statement configured from input fields.
select [Showing items] from [DB Table Names] [Conditions] limit [Showing limit]
- Specify the following fields.
- Showing items: '*' all columns in default
- DB Table Names: 't1' in default
- Conditions: None in default
- Showing limit: '500' in default. First 500 lines are shown.
The offset(1000) and count(500) can be specified as '1000,500' or '500 offset 1000'.
Whwn empty string is set, there is no limit.
- Touch the button 'Search'.
- At success, the result field shows the preview.
Only the lead part of each item is shown.
Except the column 'id', each item is stored as String.
To treat it as the Numeric type, convert it by the cast function such as
'cast(A as numeric)'.
Following are examples. Refer documents of SQL for details.
- select * from t1 where A!="" and B!="" limit 500
- select count(*) from t1 where cast(B as numeric)>10
- select A,B from t1 order by C desc
- select A,B from t1 order by cast(C as numeric) asc
- select * from t1 limit 10 offset 2
- select C,count(C) from t1 group by C
- select * from t1 where A like '%tokyo%'
- select * from t1 where A not like '%tokyo%'
Using Result
- The search results are copied to the clipboard automatically.
- After returning to the main screen, long-touch a cell and choose 'Paste'. Too big data may be failed.
The clipboard text is the TSV (Tab Separated Values) form. It can be pasted in other applications.
In addition, the search result can be loaded to the main screen as a new sheet,
if you choose "Load result" from the top-right 3-dots icon.
Removing DB Tables
The created DB tables are maintained until the delete instruction.
The table of the same name is overwritten.
To delete DB tables, show DB table list from the top-right 3-dots icon.
All DB tables can be deleted from the top-right 3-dots icon of the list screen.
Each DB table can be deleted by long-touching the DB table name in the list.