[原创] SQLite数据库使用清单（上）

1. 介绍

1.1 安装

• 访问 SQLite 下载页面，从 Windows 区下载预编译的二进制文件。

• 您需要下载 sqlite-shell-win32-*.zip 和 sqlite-dll-win32-*.zip 压缩文件。

• 创建文件夹 C:\sqlite，并在此文件夹下解压上面两个压缩文件，将得到 sqlite3.def、sqlite3.dll 和 sqlite3.exe 文件。

• 添加 C:\sqlite 到 PATH 环境变量，最后在命令提示符下，使用 sqlite3 命令，将显示如下结果。

• C:\>sqlite3
  SQLite version 3.7.15.2 2013-01-09 11:53:05
  Enter ".help" for instructions
  Enter SQL statements terminated with a ";"
  sqlite>
  

 1.2 命令

sqlite3 打开

.help 帮助

.quit 退出

.show 查看

.databases 查看所有数据库

.tables 查看数据库内所有表

sqlite>.header on
sqlite>.mode column
sqlite>.timer on
sqlite> //格式化输出

sqlite>.schema sqlite_master 表格

1.3 语法

- 大小写敏感

- 注释：--或//

- 语句：关键字开始，分号结束

- 常用命令语法示例： http://www.runoob.com/sqlite/sqlite-syntax.html

1.4 数据类型

1.4.1 存储类： NULL，INTEGER， REAL， TEXT， BLOB（blob 数据，完全根据它的输入存储）

1.4.2 Affinity类型： TEXT， NUMERIC， INTEGER， REAL， NULL

1.4.3 Boolean 类型：0 / 1

1.4.4 Date与Time类型： TEXT， REAL， INTEGER

 

2. 操作

2.1 创建数据库 sqlite3 DatabaseName.db

 .databases 检查已有数据库

 .dump 导出数据库文件到其他格式（如sql，txt）文件

　　sqlite3 testDB.db .dump > testDB.sql
　　sqlite3 testDB.db < testDB.sql

2.2.a SQLite附加数据库

sqlite> ATTACH DATABASE 'testDB.db' as 'TEST'; //将1或多个数据库附加到主数据库

2.2.b SQLite分离数据库

sqlite> DETACH DATABASE 'TEST'; //将指定数据库从主数据库中分离

2.3 创建表

sqlite> CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);
//语法示例

2.4 删除表

sqlite>DROP TABLE COMPANY;
//语法示例

2.5 SQLite 语句

2.5.1 INSERT 语句

//向已存在表COMPANY中插入数据
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Allen', 25, 'Texas', 15000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'David', 27, 'Texas', 85000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Kim', 22, 'South-Hall', 45000.00 );

//或使用另一语法插入
INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 );
//语法示例

 使用一个表来填充另一个表：

INSERT INTO first_table_name [(column1, column2, ... columnN)] 
   SELECT column1, column2, ...columnN 
   FROM second_table_name
   [WHERE condition];
//语法规范，示例待补充

2.5.2 SELECT 语句

//设置正确的格式化输出
sqlite>.header on
sqlite>.mode column
sqlite> SELECT * FROM COMPANY;

//获取所有字段
SELECT * FROM table_name;

//获取指定字段
SELECT column1, column2, columnN FROM table_name;

eg.) sqlite> SELECT ID, NAME, SALARY FROM COMPANY;

//设置输出列的宽度
sqlite>.width 10, 20, 10
sqlite>SELECT * FROM COMPANY;

//

 Schema信息：因为所有的点命令只在 SQLite 提示符中可用，所以当进行带有 SQLite 的编程时，需要使用下面的带有 sqlite_master 表的 SELECT 语句来列出所有在数据库中创建的表：

//选择引用Schema信息的数据库列表
sqlite> SELECT tbl_name FROM sqlite_master WHERE type = 'table';

//同时列出数据列表的完整信息
sqlite> SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'COMPANY';

2.5.3 UPDATE 语句

//eg.更新指定Key行的指定列的数据
sqlite> UPDATE COMPANY SET ADDRESS = 'Texas' WHERE ID = 6;

//更新所有行的指定列的数据，该列数据改变后相同
sqlite> UPDATE COMPANY SET ADDRESS = 'Texas', SALARY = 20000.00;

2.5.4 DELETE 语句

//eg. 删除指定Key值行，即该ID下“客户”
sqlite> DELETE FROM COMPANY WHERE ID = 7;

//删除表中所有数据
sqlite> DELETE FROM COMPANY;

2.6 Distinct 关键字

SQLite 的 DISTINCT 关键字与 SELECT 语句一起使用，来消除所有重复的记录，并只获取唯一一次记录。

//查询时，相同name值只出现一次
sqlite> SELECT DISTINCT name FROM COMPANY;

2.7 运算符

2.7.1 算数运算符 +, -,*, /, %

2.7.2 比较运算符 ==, =, <>, >, <, >=, <=, !=, !>

说明：== 与 = 同义； <> 与 ！= 同义

//eg.
sqlite> SELECT * FROM COMPANY WHERE SALARY > 50000;

sqlite>  SELECT * FROM COMPANY WHERE SALARY = 20000;

sqlite>  SELECT * FROM COMPANY WHERE SALARY != 20000;

2.7.3 逻辑运算符

AND, BETWEEN, EXISTS, IN, NOT IN, LIKE, GLOB, NOT, OR, IS NULL, IS, IS NOT, ||, UNIQUE

运算符	描述
AND	AND 运算符允许在一个 SQL 语句的 WHERE 子句中的多个条件的存在。
BETWEEN	BETWEEN 运算符用于在给定最小值和最大值范围内的一系列值中搜索值。
EXISTS	EXISTS 运算符用于在满足一定条件的指定表中搜索行的存在。
IN	IN 运算符用于把某个值与一系列指定列表的值进行比较。
NOT IN	IN 运算符的对立面，用于把某个值与不在一系列指定列表的值进行比较。
LIKE	LIKE 运算符用于把某个值与使用通配符运算符的相似值进行比较。
GLOB	GLOB 运算符用于把某个值与使用通配符运算符的相似值进行比较。GLOB 与 LIKE 不同之处在于，它是大小写敏感的。
NOT	NOT 运算符是所用的逻辑运算符的对立面。比如 NOT EXISTS、NOT BETWEEN、NOT IN，等等。它是否定运算符。
OR	OR 运算符用于结合一个 SQL 语句的 WHERE 子句中的多个条件。
IS NULL	NULL 运算符用于把某个值与 NULL 值进行比较。
IS	IS 运算符与 = 相似。
IS NOT	IS NOT 运算符与 != 相似。
||	连接两个不同的字符串，得到一个新的字符串。
UNIQUE	UNIQUE 运算符搜索指定表中的每一行，确保唯一性（无重复）。

//eg.
sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;

sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;

sqlite>  SELECT * FROM COMPANY WHERE AGE IS NOT NULL;

//列出 NAME 以 'Ki' 开始的所有记录，'Ki' 之后的字符不做限制：
sqlite> SELECT * FROM COMPANY WHERE NAME LIKE 'Ki%';

//同上，已验证。
sqlite> SELECT * FROM COMPANY WHERE NAME GLOB 'Ki*';

//列出 AGE 的值为 25 或 27 的所有记录：
sqlite> SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );

//列出 AGE 的值既不是 25 也不是 27 的所有记录：
sqlite> SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 );

//列出 AGE 的值在 25 与 27 之间的所有记录：
sqlite> SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;

//下面的 SELECT 语句使用 SQL 子查询，子查询查找 SALARY > 65000 的带
有 AGE 字段的所有记录，后边的 WHERE 子句与 EXISTS 运算符一起使用，列
出了外查询中的 AGE 存在于子查询返回的结果中的所有记录：
sqlite> SELECT AGE FROM COMPANY 
　　　　　WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

//下面的 SELECT 语句使用 SQL 子查询，子查询查找 SALARY > 65000 的带
有 AGE 字段的所有记录，后边的 WHERE 子句与 > 运算符一起使用，列出了外
查询中的 AGE 大于子查询返回的结果中的年龄的所有记录：
sqlite> SELECT * FROM COMPANY 
　　　　　WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

 2.7.4 位运算符

& 按位与； | 按位或； ~ 按位非； << 左移； >> 右移

2.8 表达式

布尔表达式 eg. sqlite> SELECT * FROM COMPANY WHERE SALARY = 10000;

数值表达式 eg. sqlite> SELECT (15 + 6) AS ADDITION;

日期表达式 eg. sqlite> SELECT CURRENT_TIMESTAMP;

2.9 SQLite 子句

2.9.1 WHERE 子句：条件限定

//eg.
sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;

sqlite> SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;

//子查询
sqlite> SELECT AGE FROM COMPANY 
        WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

2.9.2 LIKE 子句：匹配通配符指定模式的文本值

一起使用的通配符：百分号（%）代表零个、一个或多个数字或字符。下划线（_）代表一个单一的数字或字符。这些符号可以被组合使用。

//可以使用 AND 或 OR 运算符来结合 N 个数量的条件。在这里，XXXX 可以是任何数字或字符串值。
SELECT FROM table_name
WHERE column LIKE 'XXXX%'
or 
SELECT FROM table_name
WHERE column LIKE '%XXXX%'
or
SELECT FROM table_name
WHERE column LIKE 'XXXX_'
or
SELECT FROM table_name
WHERE column LIKE '_XXXX'
or
SELECT FROM table_name
WHERE column LIKE '_XXXX_';

语句	描述
WHERE SALARY LIKE '200%'	查找以 200 开头的任意值
WHERE SALARY LIKE '%200%'	查找任意位置包含 200 的任意值
WHERE SALARY LIKE '_00%'	查找第二位和第三位为 00 的任意值
WHERE SALARY LIKE '2_%_%'	查找以 2 开头，且长度至少为 3 个字符的任意值
WHERE SALARY LIKE '%2'	查找以 2 结尾的任意值
WHERE SALARY LIKE '_2%3'	查找第二位为 2，且以 3 结尾的任意值
WHERE SALARY LIKE '2___3'	查找长度为 5 位数，且以 2 开头以 3 结尾的任意值

2.9.3 GLOB 语句：匹配通配符指定模式的文本值，大小写敏感，遵循 UNIX 的语法。

一起使用的通配符：星号（*）代表零个、一个或多个数字或字符。问号（?）代表一个单一的数字或字符。这些符号可以被组合使用。

//可以使用 AND 或 OR 运算符来结合 N 个数量的条件。在这里，XXXX 可以是任何数字或字符串值。
SELECT FROM table_name
WHERE column GLOB 'XXXX*'
or 
SELECT FROM table_name
WHERE column GLOB '*XXXX*'
or
SELECT FROM table_name
WHERE column GLOB 'XXXX?'
or
SELECT FROM table_name
WHERE column GLOB '?XXXX'
or
SELECT FROM table_name
WHERE column GLOB '?XXXX?'
or
SELECT FROM table_name
WHERE column GLOB '????'

语句	描述
WHERE SALARY GLOB '200*'	查找以 200 开头的任意值
WHERE SALARY GLOB '*200*'	查找任意位置包含 200 的任意值
WHERE SALARY GLOB '?00*'	查找第二位和第三位为 00 的任意值
WHERE SALARY GLOB '2??'	查找以 2 开头，且长度至少为 3 个字符的任意值
WHERE SALARY GLOB '*2'	查找以 2 结尾的任意值
WHERE SALARY GLOB '?2*3'	查找第二位为 2，且以 3 结尾的任意值
WHERE SALARY GLOB '2???3'	查找长度为 5 位数，且以 2 开头以 3 结尾的任意值

2.9.4 LIMIT 子句：限制由 SELECT 语句返回的数据数量

//限制查询6条记录的数据
sqlite> SELECT * FROM COMPANY LIMIT 6;

//从一个特定的偏移开始提取记录，如下为从第（5+1）行提取3条记录
sqlite> SELECT * FROM COMPANY LIMIT 3 OFFSET 5;

2.9.5 ORDER BY 子句：基于一个或多个列按升序(ASC)或降序(DESC)顺序排列数据

//将结果按 SALARY 升序排序：
sqlite> SELECT * FROM COMPANY ORDER BY SALARY ASC;

//将结果按 NAME 和 SALARY 升序排序：
sqlite> SELECT * FROM COMPANY ORDER BY NAME, SALARY ASC;

//将结果按 NAME 降序排序：
sqlite> SELECT * FROM COMPANY ORDER BY NAME DESC;

2.9.6 GROUP BY 子句：与 SELECT 语句一起使用，对相同的数据进行分组

说明：在 SELECT 语句中，GROUP BY 子句放在 WHERE 子句之后，放在 ORDER BY 子句之前。

//语法
SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN

 

具体实例见：http://www.runoob.com/sqlite/sqlite-group-by.html

疑问：Group By 子句按指定列名进行分组后，对同名的行合并成为一行，若所选择的组员其他列为数字，则使用SUM函数求和显示，如例中所示；若为数字不求和会怎样？若为字符等其他类型数据如何处理？

//GROUP BY 语句测试
//以下语句结果为：显示同名的最后一条数据的值
SELECT NAME, SALARY FROM COMPANY GROUP BY NAME ORDER BY NAME;

//一下语句结果为：显示同名的最后一条数据的值
SELECT NAME, ADDRESS FROM COMPANY GROUP BY NAME ORDER BY NAME;

2.9.7 HAVING 子句：允许指定条件来过滤将出现在最终结果中的分组结果

说明：WHERE 子句在所选列上设置条件，而 HAVING 子句则在由 GROUP BY 子句创建的分组上设置条件。

HAVING子句在SELECT语句中的位置：

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

HAVING 子句必须放在 GROUP BY 子句之后，必须放在 ORDER BY 子句之前。下面是包含 HAVING 子句的 SELECT 语句的语法：

SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2

例如：

// 显示名称计数（重复数）小于 2 的所有记录：
sqlite > SELECT * FROM COMPANY GROUP BY name HAVING count(name) < 2;

// 显示名称计数大于 2 的所有记录：
sqlite > SELECT * FROM COMPANY GROUP BY name HAVING count(name) > 2;

 

至此，SQLite的基本语法及操作已罗列完毕，下一篇整理SQLite的一些高级操作。