SQLite 的 SELECT 语句用于从 SQLite 数据库表中获取数据，以结果表的形式返回数据。这些结果表也被称为结果集。
SQLite 的 SELECT 语句的基本语法如下：
|
1 |
<span class="pln">SELECT column1</span><span class="pun">,</span><span class="pln"> column2</span><span class="pun">,</span><span class="pln"> columnN FROM table_name</span><span class="pun">;</span> |
在这里，column1, column2…是表的字段，他们的值即是您要获取的。如果您想获取所有可用的字段，那么可以使用下面的语法：
|
1 |
<span class="pln">SELECT </span><span class="pun">*</span><span class="pln"> FROM table_name</span><span class="pun">;</span> |
假设 COMPANY 表有以下记录：
|
1 2 3 4 5 6 7 8 9 |
<span class="pln">ID NAME AGE ADDRESS SALARY </span><span class="pun">----------</span> <span class="pun">----------</span> <span class="pun">----------</span> <span class="pun">----------</span> <span class="pun">----------</span> <span class="lit">1</span> <span class="typ">Paul</span> <span class="lit">32</span> <span class="typ">California</span> <span class="lit">20000.0</span> <span class="lit">2</span> <span class="typ">Allen</span> <span class="lit">25</span> <span class="typ">Texas</span> <span class="lit">15000.0</span> <span class="lit">3</span> <span class="typ">Teddy</span> <span class="lit">23</span> <span class="typ">Norway</span> <span class="lit">20000.0</span> <span class="lit">4</span> <span class="typ">Mark</span> <span class="lit">25</span> <span class="typ">Rich</span><span class="pun">-</span><span class="typ">Mond</span> <span class="lit">65000.0</span> <span class="lit">5</span> <span class="typ">David</span> <span class="lit">27</span> <span class="typ">Texas</span> <span class="lit">85000.0</span> <span class="lit">6</span> <span class="typ">Kim</span> <span class="lit">22</span> <span class="typ">South</span><span class="pun">-</span><span class="typ">Hall</span> <span class="lit">45000.0</span> <span class="lit">7</span> <span class="typ">James</span> <span class="lit">24</span> <span class="typ">Houston</span> <span class="lit">10000.0</span> |
下面是一个实例，使用 SELECT 语句获取并显示所有这些记录。在这里，前三个命令被用来设置正确格式化的输出。
|
1 2 3 |
<span class="pln">sqlite</span><span class="pun">>.</span><span class="pln">header on sqlite</span><span class="pun">>.</span><span class="pln">mode column sqlite</span><span class="pun">></span><span class="pln"> SELECT </span><span class="pun">*</span><span class="pln"> FROM COMPANY</span><span class="pun">;</span> |
最后，将得到以下的结果：
|
1 2 3 4 5 6 7 8 9 |
<span class="pln">ID NAME AGE ADDRESS SALARY </span><span class="pun">----------</span> <span class="pun">----------</span> <span class="pun">----------</span> <span class="pun">----------</span> <span class="pun">----------</span> <span class="lit">1</span> <span class="typ">Paul</span> <span class="lit">32</span> <span class="typ">California</span> <span class="lit">20000.0</span> <span class="lit">2</span> <span class="typ">Allen</span> <span class="lit">25</span> <span class="typ">Texas</span> <span class="lit">15000.0</span> <span class="lit">3</span> <span class="typ">Teddy</span> <span class="lit">23</span> <span class="typ">Norway</span> <span class="lit">20000.0</span> <span class="lit">4</span> <span class="typ">Mark</span> <span class="lit">25</span> <span class="typ">Rich</span><span class="pun">-</span><span class="typ">Mond</span> <span class="lit">65000.0</span> <span class="lit">5</span> <span class="typ">David</span> <span class="lit">27</span> <span class="typ">Texas</span> <span class="lit">85000.0</span> <span class="lit">6</span> <span class="typ">Kim</span> <span class="lit">22</span> <span class="typ">South</span><span class="pun">-</span><span class="typ">Hall</span> <span class="lit">45000.0</span> <span class="lit">7</span> <span class="typ">James</span> <span class="lit">24</span> <span class="typ">Houston</span> <span class="lit">10000.0</span> |
如果只想获取 COMPANY 表中指定的字段，则使用下面的查询：
|
1 |
<span class="pln">sqlite</span><span class="pun">></span><span class="pln"> SELECT ID</span><span class="pun">,</span><span class="pln"> NAME</span><span class="pun">,</span><span class="pln"> SALARY FROM COMPANY</span><span class="pun">;</span> |
上面的查询会产生以下结果：
|
1 2 3 4 5 6 7 8 9 |
<span class="pln">ID NAME SALARY </span><span class="pun">----------</span> <span class="pun">----------</span> <span class="pun">----------</span> <span class="lit">1</span> <span class="typ">Paul</span> <span class="lit">20000.0</span> <span class="lit">2</span> <span class="typ">Allen</span> <span class="lit">15000.0</span> <span class="lit">3</span> <span class="typ">Teddy</span> <span class="lit">20000.0</span> <span class="lit">4</span> <span class="typ">Mark</span> <span class="lit">65000.0</span> <span class="lit">5</span> <span class="typ">David</span> <span class="lit">85000.0</span> <span class="lit">6</span> <span class="typ">Kim</span> <span class="lit">45000.0</span> <span class="lit">7</span> <span class="typ">James</span> <span class="lit">10000.0</span> |
有时，由于要显示的列的默认宽度导致 .mode column，这种情况下，输出被截断。此时，您可以使用 .width num, num…. 命令设置显示列的宽度，如下所示：
|
1 2 |
<span class="pln">sqlite</span><span class="pun">>.</span><span class="pln">width </span><span class="lit">10</span><span class="pun">,</span> <span class="lit">20</span><span class="pun">,</span> <span class="lit">10</span><span class="pln"> sqlite</span><span class="pun">></span><span class="pln">SELECT </span><span class="pun">*</span><span class="pln"> FROM COMPANY</span><span class="pun">;</span> |
上面的 .width 命令设置第一列的宽度为 10，第二列的宽度为 20，第三列的宽度为 10。因此上述 SELECT 语句将得到以下结果：
|
1 2 3 4 5 6 7 8 9 |
<span class="pln">ID NAME AGE ADDRESS SALARY </span><span class="pun">----------</span> <span class="pun">--------------------</span> <span class="pun">----------</span> <span class="pun">----------</span> <span class="pun">----------</span> <span class="lit">1</span> <span class="typ">Paul</span> <span class="lit">32</span> <span class="typ">California</span> <span class="lit">20000.0</span> <span class="lit">2</span> <span class="typ">Allen</span> <span class="lit">25</span> <span class="typ">Texas</span> <span class="lit">15000.0</span> <span class="lit">3</span> <span class="typ">Teddy</span> <span class="lit">23</span> <span class="typ">Norway</span> <span class="lit">20000.0</span> <span class="lit">4</span> <span class="typ">Mark</span> <span class="lit">25</span> <span class="typ">Rich</span><span class="pun">-</span><span class="typ">Mond</span> <span class="lit">65000.0</span> <span class="lit">5</span> <span class="typ">David</span> <span class="lit">27</span> <span class="typ">Texas</span> <span class="lit">85000.0</span> <span class="lit">6</span> <span class="typ">Kim</span> <span class="lit">22</span> <span class="typ">South</span><span class="pun">-</span><span class="typ">Hall</span> <span class="lit">45000.0</span> <span class="lit">7</span> <span class="typ">James</span> <span class="lit">24</span> <span class="typ">Houston</span> <span class="lit">10000.0</span> |
因为所有的点命令只在 SQLite 提示符中可用，所以当您进行带有 SQLite 的编程时，您要使用下面的带有 sqlite_master 表的 SELECT 语句来列出所有在数据库中创建的表：
|
1 |
<span class="pln">sqlite</span><span class="pun">></span><span class="pln"> SELECT tbl_name FROM sqlite_master WHERE type </span><span class="pun">=</span> <span class="str">'table'</span><span class="pun">;</span> |
假设在 testDB.db 中已经存在唯一的 COMPANY 表，则将产生以下结果：
|
1 2 3 |
<span class="pln">tbl_name </span><span class="pun">----------</span><span class="pln"> COMPANY</span> |
您可以列出关于 COMPANY 表的完整信息，如下所示：
|
1 |
<span class="pln">sqlite</span><span class="pun">></span><span class="pln"> SELECT sql FROM sqlite_master WHERE type </span><span class="pun">=</span> <span class="str">'table'</span><span class="pln"> AND tbl_name </span><span class="pun">=</span> <span class="str">'COMPANY'</span><span class="pun">;</span> |
假设在 testDB.db 中已经存在唯一的 COMPANY 表，则将产生以下结果：
|
1 2 3 4 5 6 7 8 9 |
<span class="pln">CREATE TABLE COMPANY</span><span class="pun">(</span><span class="pln"> ID INT PRIMARY KEY NOT NULL</span><span class="pun">,</span><span class="pln"> NAME TEXT NOT NULL</span><span class="pun">,</span><span class="pln"> AGE INT NOT NULL</span><span class="pun">,</span><span class="pln"> ADDRESS CHAR</span><span class="pun">(</span><span class="lit">50</span><span class="pun">),</span><span class="pln"> SALARY REAL </span><span class="pun">) from:<a href="http://www.runoob.com/sqlite/sqlite-select.html">http://www.runoob.com/sqlite/sqlite-select.html</a></span> |