SQLiteSELECT查询

SQLite SELECTLe istruzioni SELECT vengono utilizzate per ottenere dati da tabelle di database SQLite, che vengono restituiti sotto forma di tabelle di risultati. Queste tabelle di risultati sono anche detteresult sets.

Sintassi

Di seguito è riportata la sintassi di base dell'istruzione SELECT di SQLite.

SELECT column1, column2, columnN FROM table_name;

Ecco, column1, column2 ... sono i campi della tabella, di cui desideri ottenere i valori. Se desideri ottenere tutti i campi disponibili in tale campo, puoi utilizzare la seguente sintassi-

SELECT * FROM table_name;

示例

考虑带有以下记录的COMPANY表-

ID                                    NAME                                    AGE                                    ADDRESS                                    SALARY
----------                            ----------                            ----------                            ----------                            ----------
1                                     Paul                                 32                                     California                                 20000.0
2                                     Allen                                 25                                     Texas                                     15000.0
3                                     Teddy                                 23                                     Norway                                     20000.0
4                                     Mark                                 25                                     Rich-Mond                                 65000.0
5                                     David                                 27                                     Texas                                     85000.0
6                                     Kim                                 22                                     South-Hall                                 45000.0
7                                     James                             24                                     Houston                                     10000.0

以下是使用SELECT语句获取并显示所有这些记录的示例。在这里，前三个命令已用于设置正确格式的输出。

sqlite>.header on
sqlite>.mode column
sqlite> SELECT * FROM COMPANY;

最后，您将获得以下结果。

ID                                    NAME                                    AGE                                    ADDRESS                                    SALARY
----------                            ----------                            ----------                            ----------                            ----------
1                                     Paul                                 32                                     California                                 20000.0
2                                     Allen                                 25                                     Texas                                     15000.0
3                                     Teddy                                 23                                     Norway                                     20000.0
4                                     Mark                                 25                                     Rich-Mond                                 65000.0
5                                     David                                 27                                     Texas                                     85000.0
6                                     Kim                                 22                                     South-Hall                                 45000.0
7                                     James                             24                                     Houston                                     10000.0

如果您只想获取COMPANY表的选定字段，则使用以下查询-

sqlite> SELECT ID, NAME, SALARY FROM COMPANY;

上面的查询将产生以下结果。

ID                                    NAME                                    AGE                                    ADDRESS                                    SALARY
----------                            ----------                            ----------                            ----------                            ----------
1                                     Paul                                 32                                     California                                 20000.0
2                                     Allen                                 25                                     Texas                                     15000.0
3                                     Teddy                                 23                                     Norway                                     20000.0
4                                     Mark                                 25                                     Rich-Mond                                 65000.0
5                                     David                                 27                                     Texas                                     85000.0
6                                     Kim                                 22                                     South-Hall                                 45000.0
7                                     James                             24                                     Houston                                     10000.0

设置输出列宽

有时，.mode column由于要显示的列的默认宽度，您会遇到与截断输出有关的问题。您可以做的是，可以使用以下.width num, num....命令设置列可显示的列宽：

sqlite>.width 10, 20, 10
sqlite>SELECT * FROM COMPANY;

Sopra}}.widthIl comando imposta la larghezza della prima colonna a 10, la seconda colonna a 20 e la terza colonna a 10. Infine, l'espressione SELECT sopra fornirà i seguenti risultati.

ID                                            NAME                                                   AGE                                            ADDRESS        SALARY
----------                                   --------------------                                 ----------                                 ----------
1                                             Paul                                                  32                                             California     20000.0
2                                             Allen                                                  25                                             Texas           15000.0
3                                             Teddy                                                 23                                             Norway         20000.0
4                                             Mark                                                  25                                             Rich-Mond     65000.0
5                                             David                                                  27                                             Texas           85000.0
6                                             Kim                                                   22                                             South-Hall     45000.0
7                                             James                                                 24                                             Houston         10000.0

Informazioni sullo schema

Poiché tutti i comandi dot possono essere utilizzati sotto il prompt di SQLite, quando programmi in SQLite userai le seguenti espressioni SELECT e la tabella principale di SQLite per elencare tutte le tabelle create nel database.

sqlite> SELECT tbl_name FROM sqlite_master WHERE type = 'table';

假设您的testDB.db中只有COMPANY表，这将产生以下结果。

tbl_name----------COMPANY

Puoi elencare tutte le informazioni complete sulla tabella COMPANY come segue:

sqlite> SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'COMPANY';

假设您的testDB.db中只有COMPANY表，这将产生以下结果。

CREATE　TABLE　COMPANY(
　　　ID　INT　PRIMARY　KEY　　　　　NOTNULL,
　　　NAME　　　　　　　　　　　TEXT　　　　NOTNULL,
　　　AGE　　　　　　　　　　　　INT　　　　　NOTNULL,
　　　ADDRESS　　　　　　　　CHAR(50),
　　　SALARY　　　　　　　　　REAL)