SQLite语法基础

数据库是每个程序员应该熟悉的知识，本篇文章就介绍Linux下SQLite的基本使用。

SQLite简介

SQLite是一个软件库，实现自给自足的、无服务器的、零配置的、事务性的SQL数据库引擎。

SQLite是一个增长最快的数据库引擎，这是在普及方面的增长，与它的尺寸大小无关。而且SQLite源代码不受版权控制。

SQLite安装

在windows下安装的过程就不多说了，这里主要讲在Linux上安装SQLite，而几乎所有版本的linux操作系统都附带SQLite，因此，我们先检查自己的系统有没有安装SQLite。

$sqlite3SQLite version 3.6.20Enter ".help" for instructionsEnter SQL statements terminated with a ";"

如果没有在Linux上安装SQLite，则接下来安装SQLite。

下载网址：http://www.sqlite.org/download.html，下载sqlite-autoconf-*.tar.gz。

$tar xvfz sqlite-autoconf-3071502.tar.gz$cd sqlite-autoconf-3071502$./configure --prefix=/usr/local$make$make install

然后呢按照开始的命令来检查自己是否安装成功，出现”SQLite>“提示符即可在这里输入我们的SQLite语句。

SQLite命令

SQLite的一般命令在前面加个“.”，然后要以“；”结束。这些命令可以用“.help”查看。下面给出一些基本的命令以及说明。

命令 描述 .databases 列出附加数据库的名称和文件 .exit 退出SQLite描述符 .header(s) ON OFF .help 显示消息 .import FILE TABLE 导入来自 FILE 文件的数据到 TABLE 表中 .mode MODE csv：逗号分隔的值、column：左对齐的列、html：HTML的代码、 insert：TABLE表的SQL插入语句、 line：每行一个值、 list：由.separator字符串分隔的值、 tabs：由Tab分隔的值、 tcl：TCL列表元素 .separator STRING 改变输出模式和 .import 所使用的分隔符 .show 显示各种设置的当前值 .tables ?PATTERN? 列出匹配 LIKE 模式的表的名称 .width NUM NUM 为 “column” 模式设置列宽度 .separator STRING 改变输出模式和 .import 所使用的分隔符 .schema ?TABLE? 显示 CREATE 语句。如果指定了 TABLE 表，则只显示匹配 LIKE 模式的 TABLE 表 .load FILE ?ENTRY? 加载一个扩展库 .backup ?DB? FILE 备份 DB 数据库（默认是 “main”）到 FILE 文件

下面用“.show”命令来查看SQLite命令提示符的默认设置

sqlite>.showecho: off  explain: off  headers: off     mode: listnullvalue: ""   output: stdoutseparator: "|"    width:

SQLite**不区分大小写，只是**GLOB和glob在语句中有不同含义。

SQLite的注释用两个连续的“-”字符开始，扩展至下一个换行符结束，也可以用C风格的注释“/* … */”。

SQLite数据类型

我们知道数据都有其相对应的类型来表示，而且SQLite数据类型可以指定任何对象的数据类型的属性，我们可以在创建表的同时使用数据类型，SQLite使用一个更普遍的动态类型系统，在SQLite中，值的数据类型与值本身相关，而不是与它的容器相关。

SQLite存储类

存储类 描述 NULL 值是一个NULL值 INTEGER 值是一个带符号的整数，根据值的大小存储在1、2、3、4、6或8字节中 REAL 值为一个浮点值，存储为8字节的IEEE浮点数字 TEXT 值是一个文本字符串，使用数据库编码(UTF-8、UTF-16BE、UTF-16LE)存储 BLOB 值是一个blob数据，完全根据它的输入存储

SQLite亲和(Affinity)类型

所谓亲和类型，即如果数据的类型与数据不符合，则会自行转换成符合的数据类型。

SQLite支持以下五种亲缘类型

亲和类型 概述 TEXT 数值型数据在被插入之前，需要先被转换为文本格式，之后再插入到目标字段中 NUMERIC 当文本数据被插入到亲缘性为NUMERIC的字段中时，如果转换操作不会导致数据信息丢失以及完全可逆，那么SQLite就会将该文本数据转换为INTEGER或REAL类型的数据，如果转换失败，SQLite仍会以TEXT方式存储该数据。对于NULL或BLOB类型的新数据，SQLite将不做任何转换，直接以NULL或BLOB的方式存储该数据。需要额外说明的是，对于浮点格式的常量文本，如”30000.0”，如果该值可以转换为INTEGER同时又不会丢失数值信息，那么SQLite就会将其转换为INTEGER的存储方式。 INTEGER 对于亲缘类型为INTEGER的字段，其规则等同于NUMERIC，唯一差别是在执行CAST表达式时 REAL 其规则基本等同于NUMERIC，唯一的差别是不会将”30000.0”这样的文本数据转换为INTEGER存储方式 NONE 不做任何的转换，直接以该数据所属的数据类型进行存储

SQLite亲和类型及类型名称

亲和类型 数据类型 INTEGER INT、INTEGER、TINYINT、SMALLINT、MEDIUMINT、BIGINT、UNSIGNED BIG INT、INT2、INT8 TEXT CHARACTER(20)、VARCHAR(255)、VARYING CHARACTER(255)、NCHAR(55)、NATIVE CHARACTER(70)、NAVRCHAR(100)、TEXT、CLOB NONE BLOB、no datatype specified REAL REAL、DOUBLE、DOUBLE PRECISION、FLOAT NUMERIC NUMERIC、DECIMAL(10，5)、BOOLEAN、DATE、DATETIME

SQLite没有单独的Boolean存储类，布尔值被存储为整数0（flase）和1（true）。

Date和Time数据类型

SQLite没有单独用于存储日期和时间的存储类，SQLite将日期和时间存储为TEXT、REAL、INTEGER值

存储类 日期格式 TEXT 格式为 “YYYY-MM-DD HH:MM:SS.SSS” 的日期 REAL 从公元前 4714 年 11 月 24 日格林尼治时间的正午开始算起的天数 INTEGER 从 1970-01-01 00:00:00 UTC 算起的秒数

SQLite创建数据库

$sqlite3 DatabaseName.db

一般数据库名称在RDBMS内是唯一的。

当我们创建数据库成功后，会出现“SQLite>”提示符，并且可以用“.databases”命令来检查是否在数据库列表中

sqlite>.batabasesseq  name             file                                                      ---  ---------------  ----------------------------------------------------------0    main             /home/tangyanjun/SQL/first.db                             1    temp

“.quit”命令退出sqlite提示符

“.dump”命令导出完整的数据库在一个文本文件中

$sqlite3 testDB.db .dump > testDB.sql

这条命令将转换整个testDB.db数据库的内容到SQLite的语句中，并将其转储到ASCII文本文件testDB.sql中，可以恢复

$sqlite3 testDB.db < testDB.sql

SQLite附加数据库

在同一时间有多个数据库可用时，SQLite的ATTACH DATABASE语句用来选择一个特定的数据库，所有的SQLite语句将在附加的数据库下执行。

ATTACH DATABASE 'DatabaseName' AS 'Alias-Name';

如果数据库不存在，则会新建一个数据库，如果已经存在，则会将数据库文件名称与逻辑数据库’Alias-Name‘绑定在一起。

现在附加一个现有的数据库testDB.db：

sqlite> ATTACH DATABASE 'testDB.db' as 'TEST';

显示附加的数据库：

sqlite> .databaseseq  name             file---  ---------------  ----------------------0    main             /home/sqlite/testDB.db2    test             /home/sqlite/testDB.db

数据库名称 main 和 temp 被保留用于主数据库和存储临时表及其他临时数据对象的数据库。这两个数据库名称可用于每个数据库连接，且不应该被用于附加，否则将得到一个警告消息，如下所示：

sqlite>  ATTACH DATABASE 'testDB.db' as 'TEMP';Error: database TEMP is already in usesqlite>  ATTACH DATABASE 'testDB.db' as 'main';Error: database TEMP is already in use

SQLite分离数据库

DETACH DATABASE 'xxDB';

SQLite创建表

创建表涉及到命名表，定义列及每一列的数据类型

基本语法

CREATE TABLE database_name.table_name(   column1 datatype  PRIMARY KEY(one or more columns),   column2 datatype,   column3 datatype,   .....   columnN datatype,);

这里要提到表中的主键(primary key)以及外键(foreign key)了，主键即为能唯一标识一条记录的一个属性组，例如学生表中，学生的学号是唯一的，不可能冲突的，则学生的学号这条信息即为本表的主键。而成绩表中学号不是成绩表的主键，但是它与学生表的学号相对应，而学生表中的主键为学号，因此成绩表中的学号是学生表的外键。

用“.table”可以验证自己的表是否创建成功，然后“.schema 表名”可以查看表的完整信息。

SQLite删除表

DROP TABLE database_name.table_name;

当然，要先确认这个表存在，删除表后再次使用“.tables”查看是否成功。

Insert语句

INSERT INTO 语句用于向数据库的某个表中添加新的数据行

两种基本语法：

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)]  VALUES (value1, value2, value3,...valueN);

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

第二种方式的值要按顺序来指定，不然会出错误。

用一个表填充另一个表

INSERT INTO first_table_name [(column1, column2, ... columnN)]    SELECT column1, column2, ...columnN    FROM second_table_name   [WHERE condition];

Select语句

SELECT语句用于从SQLite数据库表中获取数据，以结果表的形式返回数据。

基本语法：

SELECT column1, column2, columnN FROM table_name;

SELECT * FROM table_name; //查询所有的信息

在查询数据之前可以设置数据的显示格式

sqlite>.header onsqlite>.mode columnsqlite> SELECT * FROM COMPANY;

也可以查询指定表中的指定信息

SELECT  INFORMATION  FROM  table_name;

设置输出列的宽度

sqlite>.width 10, 20, 10sqlite>SELECT * FROM COMPANY;ID          NAME                  AGE         ADDRESS     SALARY----------  --------------------  ----------  ----------  ----------1           Paul                  32          California  20000.02           Allen                 25          Texas       15000.03           Teddy                 23          Norway      20000.04           Mark                  25          Rich-Mond   65000.05           David                 27          Texas       85000.06           Kim                   22          South-Hall  45000.07           James                 24          Houston     10000.0

上面的“-”的数量即为我们设置列的宽度

列出在数据库中创建的表：

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

列出表的完整信息：

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

SQLite的运算符：

除了几个生疏的，其余的都一样

SELECT 30 + 20;30 + 20 = 50

运算符 描述 <> 检查两个操作数的值是否相等，如果不相等则条件为真。a<>b

这些运算符都是用来我们查询的时候设置条件的。

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

逻辑运算符

运算符 描述 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 运算符搜索指定表中的每一行，确保唯一性（无重复）

sqlite> SELECT * FROM COMPANY WHERE NAME LIKE 'Ki%';sqlite> SELECT * FROM COMPANY WHERE NAME GLOB 'Ki*';sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;sqlite> SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );sqlite> SELECT AGE FROM COMPANY         WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

位运算符也是一样。

SQLite日期表达式

sqlite>  SELECT CURRENT_TIMESTAMP;2017-07-06 06:11:19

where、and、or语句与条件运算符一起用

SQLite Update语句

sqlite> UPDATE COMPANY SET ADDRESS = 'Texas' WHERE ID = 6;

这个语句更新ID为6的客户地址

SQLite Delete语句

sqlite> DELETE FROM COMPANY WHERE ID = 7;

Delete语句删除其ID为7的记录

SQLite Like 子句

SQLite 的 LIKE 运算符是用来匹配通配符指定模式的文本值。如果搜索表达式与模式表达式匹配，LIKE 运算符将返回真（true），也就是 1。这里有两个通配符与 LIKE 运算符一起使用： % 和* _*

%表示零个，一个或多个数字或字符，_ 代表一个单一的数字或字符

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 结尾的任意值

SQLite Limit子句

SQLite Limit 子句用于限制由SELECT语句返回的数据数量。

SELECT column1, column2, columnN FROM table_nameLIMIT [no of rows] OFFSET [row num]

SQLite 引擎将返回从下一行开始直到给定的 OFFSET 为止的所有行。

sqlite> SELECT * FROM COMPANY LIMIT 3 OFFSET 2;

这里注意OFFSET是下一行开始算。

SQLite Order By 子句

用来基于一个或多个列按生序或降序顺序排列数据。

基本语法：

SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1, column2, .. columnN] [ASC | DESC];

工资按升序排序：

sqlite> SELECT * FROM COMPANY ORDER BY SALARY ASC;

结果按NAME和SALARY升序排列：

sqlite> SELECT * FROM COMPANY ORDER BY NAME, SALARY ASC;

这里由NAME排序为主，SALARY为辅。

按NAME降序排列：

sqlite> SELECT * FROM COMPANY ORDER BY NAME DESC;

SQLite Group By

SQLite 的 GROUP BY 子句用于与 SELECT 语句一起使用，来对相同的数据进行分组。
在 SELECT 语句中，GROUP BY 子句放在 WHERE 子句之后，放在 ORDER BY 子句之前

对所有记录按NAME列进行分组（按姓名一样的进行求和分组）：

sqlite> SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME;

sqlite>  SELECT NAME, SUM(SALARY)          FROM COMPANY GROUP BY NAME ORDER BY NAME DESC;

SQLite Having 子句

HAVING 子句允许指定条件来过滤将出现在最终结果中的分组结果。
WHERE 子句在所选列上设置条件，而 HAVING 子句则在由 GROUP BY 子句创建的分组上设置条件。

SELECTFROMWHEREGROUP BYHAVINGORDER BY

语法

SELECT column1, column2FROM table1, table2WHERE [ conditions ]GROUP BY column1, column2HAVING [ conditions ]ORDER BY column1, column2

显示名称（重复）计数小于2的所有记录

sqlite > SELECT * FROM COMPANY GROUP BY name HAVING count(name) < 2;

SQLite Distinct 关键字

SQLite 的 DISTINCT 关键字与 SELECT 语句一起使用，来消除所有重复的记录，并只获取唯一一次记录。
有可能出现一种情况，在一个表中有多个重复的记录。当提取这样的记录时，DISTINCT 关键字就显得特别有意义，它只获取唯一一次记录，而不是获取重复记录。

语法：

SELECT DISTINCT column1, column2,.....columnN FROM table_nameWHERE [condition]

查询结构没有任何重复的条目

sqlite> SELECT DISTINCT name FROM COMPANY;