SQLite基础语法速用大法（Flutter）

前记

（可跳过这段来自本up的罗里吧嗦。。。）

在做上一个项目的时候，需要用到本地数据库，以前做公司项目用的是轻量级数据库Realm，做自己小项目用的是greenDAO，大学学的是SQL server，但是在flutter中，相关插件用的数据库是SQLite（sqflite插件），但本人还没接触过SQLite，问了后台同事，他们用的也是SQLite，说大同小异，我就想着仅凭大学记忆，快速过一遍SQLite知识点，应该挺快的，想着抓点重要的就够了。现在项目告一段落，就来写这篇博客记录一下（感觉有点标题党了哈哈哈）。

正文

小重点：

1、SQLite 是不区分大小写的，但也有一些命令是大小写敏感的，比如 GLOB 和 glob 在 SQLite 的语句中有不同的含义。

2、注释大法：

（1）-- 这是一句注释

（2）/* 这是一句注释 */

一、数值类型

用于指定任何对象的数据类型的属性。

1、存储类

（1）NULL：NULL值（空值）。

（2）INTEGER：带符号的整数。

（3）REAL：浮点值。

（4）TEXT：文本字符串值。

（5）BLOB：二进制大对象，Binary Large Object。

2、亲和类型（优先采用）

当数据插入时，该字段的数据将会优先采用亲缘类型作为该值的存储方式。

（1）TEXT：数值型数据插入前先转为文本格式再插入。

（2）NUMERIC：如果转换操作不会导致数据丢失以及完全可逆，则会转换为INTEGER和REAL，如果失败则会以TEXT存储。对于NULL和BLOB类型则不作类型转换存储。注意：浮点类型的文本，如"300.0"，转为INTEGER不会丢失数值信息，则会转换为INTEGER类型存储。

（3）INTEGER：基本等同于NUMERIC，区别在于执行CAST表达式（转换类型）时。

eg: 将column字段 文本类型转换为INTEGER类型。

SELECT * FROM Table ORDER BY CAST (column as INTEGER);

（4）REAL：基本等同于NUMERIC，区别在于不会将"300.0"这样的文本数据转为INTEGER类型存储。

（5）NONE：不做转换，以数据本身的数据类型存储。

3、SQLite 亲和类型(Affinity)及类型名称（以下作了解）

（1）亲和类型：INTEGER
对应数据类型：

• INT
• INTEGER
• TINYINT
• SMALLINT
• MEDIUMINT
• BIGINT
• UNSIGNED BIG INT
• INT2
• INT8

（2）亲和类型：TEXT
对应数据类型：

• CHARACTER(20)
• VARCHAR(255)
• VARYING CHARACTER(255)
• NCHAR(55)NATIVE
• CHARACTER(70)
• NVARCHAR(100)
• TEXT
• CLOB

（3）亲和类型：NONE
对应数据类型：

• BLOB
• no datatype specified

（4）亲和类型：REAL
对应数据类型：

• REAL
• DOUBLE
• DOUBLE PRECISION
• FLOAT

（5）亲和类型：NUMERIC
对应数据类型：

• NUMERIC
• DECIMAL(10,5)
• BOOLEAN
• DATE
• DATETIME

4、Boolean数据类型

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

注：在我的项目中，直接存储为INTEGER类型，0为false，1为true。

5、Date 与 Time数据类型

SQLite 没有一个单独的用于存储日期和/或时间的存储类，但 SQLite 能够把日期和时间存储为 TEXT、REAL 或 INTEGER 值。

注：在我的项目中，存储的是时间戳，要用时取出时间戳转换为具体的时间格式 [文本字符串] 显示。

Date 与 Time 数据类型

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

二、数据库相关操作语句

1、创建、附加、分离数据库

在此教程不作sqlite的数据操作的详细讲解，后续有时间补充。

原因：移动端的数据库操作一般根据插件的语句来。

三、表相关操作语句

SQLite语句是以关键字开始，分号结束的语句。

关键字：SELECT、INSERT、UPDATE、DELETE、ALTER、DROP。

注： 在此提出关注 ALTER 关键字，本人因为把 ALTER 写成 ALERT 导致语句执行错误。

1、表的创建

基本语法：（每一字段用逗号隔开，最后一个字段不用写逗号）

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

eg：创建三张公司表，字段id自增，名称不为空，年龄不为空，工作岗位，工资。（结合Flutter中 sqflite 插件的语句写法。）

CREATE TABLE Company(
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   name TEXT NOT NULL,
   age INTEGER NOT NULL,
   job TEXT,
   salary REAL,
   hobby TEXT
);

CREATE TABLE Unimportant(
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   name TEXT NOT NULL,
   age INTEGER NOT NULL,
   job TEXT,
   salary REAL,
   hobby TEXT
);

CREATE TABLE Company_Second (
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   name TEXT NOT NULL,
   age INTEGER NOT NULL,
   job TEXT,
   salary REAL,
   hobby TEXT
);

2、表的删除

注：谨慎使用，一旦删了重要的表，可能会G！因为一旦一个表被删除，表中所有信息也将永远丢失。3（重要事情说三遍！！！）涉及删除操作的（无论是删除库、表，还是数据）一定要慎之又慎！！！*

我的项目里没有删除操作的语句。创建了，即使不用也留着，万一后续又要用了呢。

基本语法：

DROP TABLE database_name.table_name;

eg：删除不重要表。

DROP TABLE Unimportant;

3、表的数据插入

（1）基本语法：

i. 该语句适用于所有字段均插入数据或指定字段插入数据。

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

ii. 或者不需要指定列，即每个字段都有值插入。但用该语句，字段一定要一 一对应，否则数据容易插入错误。

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

eg：往Company表中插入几组数据。

INSERT INTO Company(id, name, age, job, salary, hobby)
VALUES (1, 'Paul', 32, 'CEO', 20000.00, 'basketball');

INSERT INTO Company(id, name, age, job, salary, hobby)
VALUES (2, 'Allen', 25, 'COO', 15000.00, 'soccer');

INSERT INTO Company(id, name, age, job, salary)
VALUES (3, 'Teddy', 23, 'CFO', 10000.00);

INSERT INTO Company(id, name, age, job, salary, hobby)
VALUES (4, 'Mark', 25, 'CTO', 65000.00, 'badminton');

INSERT INTO Company(id, name, age, job, salary)
VALUES (5, 'David', 27, 'CIO', 25000.00);

INSERT INTO Company(id, name, age, job, salary)
VALUES (6, 'Kim', 22, 'CHO', 15000.00);

或：

INSERT INTO Company VALUES (7, 'James', 24, 'COO', 30000.00);

（2）用一个表填充另一个表：

通过在一个有一组字段的表上使用 select 语句，填充数据到另一个表中。WHERE 引出条件，可写可不写。

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

eg：把Company表中id为1的人员的id、名字、年龄、工作岗位、工资填充到Company_Second表中。

INSERT INTO Company_Second [(id, name, age, job, salary, hobby)] 
   SELECT id, name, age, job, salary, hobby
   FROM Company
   WHERE id = 1;

4、表的数据选择

注：选择的返回结果是一个结果集，一般为列表类型。

（1）基本语法：

SELECT column1, column2, columnN FROM table_name;

想获取所有的字段，可以用：

SELECT * FROM table_name;

eg：选择Company表中所有人的id、名称、工作岗位。

SELECT id, name, job FROM Company;

eg2：选择Company表中所有人的所有信息。

SELECT * FROM Company;

5、运算符

（1）算术运算符（+、-、*、/、%）

i. +、-、*、/：就是加、减、乘、除以。

ii. %：取模。被除数 除以 除数 得到的余数。

eg：6 % 2 = 0

eg2： 5 % 2 = 1

（2）比较运算符（加粗为常用的比较运算符）
假设变量 a=10，变量 b=20，则：
比较运算符列表

运算符	描述 + 实例
==	检查两个操作数的值是否相等，如果相等则条件为真。 (a == b) 不为真。
=	检查两个操作数的值是否相等，如果相等则条件为真。 (a = b) 不为真。
!=	检查两个操作数的值是否相等，如果不相等则条件为真。 (a != b) 为真。
<>	检查两个操作数的值是否相等，如果不相等则条件为真。 (a <> b) 为真。
>	检查左操作数的值是否大于右操作数的值，如果是则条件为真。 (a > b) 不为真。
<	检查左操作数的值是否小于右操作数的值，如果是则条件为真。 (a < b) 为真。
>=	检查左操作数的值是否大于等于右操作数的值，如果是则条件为真。 (a >= b) 不为真。
<=	检查左操作数的值是否小于等于右操作数的值，如果是则条件为真。 (a <= b) 为真。
!<	检查左操作数的值是否不小于右操作数的值，如果是则条件为真。 (a !< b) 为假。
!>	检查左操作数的值是否不大于右操作数的值，如果是则条件为真。 (a !> b) 为真。

（3）逻辑运算符（常用，重点掌握）

例子结合WHERE子句使用。

逻辑运算符列表

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

（4）位运算符（不常用）
假设变量 a=10，变量 b=20，则：

位运算符列表

运算符	描述 + 实例
&	如果同时存在于两个操作数中，二进制 AND 运算符复制一位到结果中。 (A & B) 将得到 12，即为 0000 1100
	如果存在于任一操作数中，二进制 OR 运算符复制一位到结果中。 将得到 61，即为 0011 1101
~	二进制补码运算符是一元运算符，具有"翻转"位效应，即0变成1，1变成0。 (~A ) 将得到 -61，即为 1100 0011，一个有符号二进制数的补码形式。
<<	二进制左移运算符。左操作数的值向左移动右操作数指定的位数。 A << 2 将得到 240，即为 1111 0000
>>	二进制右移运算符。左操作数的值向右移动右操作数指定的位数。 A >> 2 将得到 15，即为 0000 1111

6、表达式

表达式是一个或多个值、运算符和计算值的SQL函数的组合。

（1）基本语法：

SELECT column1, column2, columnN 
FROM table_name 
WHERE [CONDITION | EXPRESSION];

（2）布尔表达式

基本语法：

SELECT column1, column2, columnN 
FROM table_name 
WHERE SINGLE VALUE MATCHING EXPRESSION;

eg：查询Company表中 工资为15000 的人员的所有信息。

SELECT * FROM Company WHERE salary = 15000;

（3）数值表达式

基本语法：

SELECT numerical_expression as  OPERATION_NAME
[FROM table_name WHERE CONDITION] ;

eg：以下数值表达式的结果为：ADDITION = 21

SELECT (15 + 6) AS ADDITION

注：有几个内置的函数，比如 avg()、sum()、count()，等等，执行被称为对一个表或一个特定的表列的汇总数据计算。

eg：查询Company表中数据的总条数，并赋值给RECORDS，结果为：RECORDS = 7

SELECT COUNT(*) AS "RECORDS" FROM Company; 

（4）日期表达式

基本语法：以下日期表达式的结果为：datetime('now','localtime') = 2022-09-21 16:47:36

SELECT datetime('now','localtime');

7、WHERE 子句

用于指定从一个表或多个表中获取数据的条件。

基本语法：

SELECT column1, column2, columnN 
FROM table_name
WHERE [condition]

eg：查询Company表中 年龄大于等于25，并且工资大于等65000的人员所有信息。

SELECT * FROM Company WHERE Age >= 25 AND Salary >= 65000;

eg2：查询Company表中年龄大于等于25，或者工资大于等65000的人员所有信息。

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

eg3：查询Company表中 name 字段以 'Ja' 开始，'Ja' 之后的字符不做限制 的所有人员的所有信息。

SELECT * FROM Company WHERE name LIKE 'Ja%';

或：

SELECT * FROM Company WHERE name GLOB 'ja*';

eg4：查询Company表中 age 的值为 25 或 27 的所有人员的所有信息。

SELECT * FROM Company WHERE age IN (25, 27);

eg5：查询Company表中 age 的值既不是 25 也不是 27 的所有人员的所有信息。

SELECT * FROM Company WHERE age NOT IN (25, 27);

eg6：查询Company表中 age 的值在 25 和 27 之间 的所有人员的所有信息。

SELECT * FROM Company WHERE age BETWEEN 25 AND 27;

eg7：查询Company表中 工资大于55000 的人员的年龄。

SELECT age FROM Company 
        WHERE EXISTS (SELECT age FROM Company WHERE salary > 55000);

解析：查询从内往外剥，就是从最小的条件语句开始看起（最后一个条件看起），即 WHERE salary > 55000，查询存在的工资大于55000人员的年龄，查询结果集是age列。

eg8：查询Company表中 年龄大于【工资大于55000 的人员的年龄】的所有人员的所有信息。

SELECT * FROM Company 
        WHERE age > (SELECT age FROM Company WHERE salary > 55000);

解析：同理，从内往外剥，最底层的查询条件为小括号中的 SELECT age FROM Company WHERE salary > 55000;，即查询工资大于55000的年龄，假设查询结果为年龄 25。接着继续上一层的条件查询：age > (...)，即为 age > 25，所以最后一层的查询可以简化为：SELECT * FROM Company WHERE age > 25;， 结果就是查询 年龄大于25 的所有员工的所有信息。

8、AND 和 OR 运算符

允许在一个 SQL 语句的 WHERE 子句中的多个条件的存在。

（1）AND 运算符

使用 AND 运算符时，只有当所有条件都为真（true）时，整个条件为真（true）。例如，只有当 condition1 和 condition2 都为真（true）时，[condition1] AND [condition2] 为真（true）。

基本语法：

SELECT column1, column2, columnN 
FROM table_name
WHERE [condition1] AND [condition2]...AND [conditionN];

eg：查询Company表中 age大于等于25 并且 salary大于等于65000 的所有员工的所有信息。

SELECT * FROM Company WHERE age >= 25 AND salary >= 65000;

（2）OR 运算符

使用 OR 运算符时，只要当条件中任何一个为真（true）时，整个条件为真（true）。例如，只要当 condition1 或 condition2 有一个为真（true）时，[condition1] OR [condition2] 为真（true）。

基本语法：

SELECT column1, column2, columnN 
FROM table_name
WHERE [condition1] OR [condition2]...OR [conditionN]

eg：查询Company表中 age大于等于 25 或salary大于等于 65000 的所有员工的所有信息。

SELECT * FROM Company WHERE age >= 25 OR salary >= 65000;

9、UPDATE 语句

用于修改表中已有的记录。可以使用带有 WHERE 子句的 UPDATE 查询来更新选定行，否则所有的行都会被更新。

基本语法：

UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

eg：更新Company表中 id为6的员工的hobby为Football 。

UPDATE Company SET hobby = 'Football' WHERE id = 6;

eg2：更新Company表中 所有hobby 和 salary 列的值。

UPDATE Company SET hobby = 'Badminton', salary = 20000.00;

执行以上语句，Company表中hobby 和 salary 列的值全部都为Badminton和20000.00。

10、DELETE 语句

用于删除表中已有的记录。（轻易不使用！！！若要删除数据，可使用软删除，即用一个字段来标识该数据是否被删除）

基本语法：

DELETE FROM table_name
WHERE [condition];

eg：删除Company表中 id为7 的员工数据。

DELETE FROM Company WHERE id = 7;

eg2：删除Company表中 所有 数据。

DELETE FROM Company;

11、LIKE 子句（不区分大小写）

用来匹配通配符指定模式的文本值。LIKE 运算符将返回真（true），也就是 1。以下两个通配符与 LIKE 运算符一起使用（这两个符号可以被组合使用）。

（1）百分号 （%）：表示零个、一个或多个数字或字符。

（2）下划线 （_）：表示一个单一的数字或字符。

基本语法：

SELECT column_list 
FROM table_name
WHERE column LIKE 'XXXX%'

or 

SELECT column_list 
FROM table_name
WHERE column LIKE '%XXXX%'

or

SELECT column_list 
FROM table_name
WHERE column LIKE 'XXXX_'

or

SELECT column_list 
FROM table_name
WHERE column LIKE '_XXXX'

or

SELECT column_list 
FROM table_name
WHERE column LIKE '_XXXX_'

eg：SELECT * FROM Company + (以下语句)

语句	描述
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 结尾的任意值

12、GLOB子句（区分大小写）

用来匹配通配符指定模式的文本值。GLOB 运算符将返回真（true），也就是 1。以下两个通配符与 GLOB 运算符一起使用（这两个符号可以被组合使用）。

（1）星号 （*）：表示零个、一个或多个数字或字符。

（2）问号 （?）：表示一个单一的数字或字符。

基本语法：

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 '????'

eg：SELECT * FROM Company + (以下语句)

语句	描述
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 结尾的任意值

13、LIMIT 子句

用于限制由 SELECT 语句返回的数据数量。
基本语法：

SELECT column1, column2, columnN 
FROM table_name
LIMIT [no of rows]

LIMIT 子句与 OFFSET 子句一起使用：

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

eg：从Company表中提取 6名员工 的所有信息。

SELECT * FROM Company LIMIT 6;

eg2：从Company表中的 第二位开始提取 3 名员工 的所有信息。

SELECT * FROM Company LIMIT 3 OFFSET 2;

14、ORDER BY 子句

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

关键词：ASC | DESC

（1）ASC：从小到大，升序排列，默认值ASC。

（2）DESC：从大到小，降序排列。

基本语法：

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

可以使用多个列排序：

SELECT
   select_list
FROM
   table
ORDER BY
    column_1 ASC,
    column_2 DESC;

column_1 与 column_2 如果后面不指定排序规则，默认为 ASC 升序，以上语句按 column_1 升序，column_2 降序读取。

SELECT
   select_list
FROM
   table
ORDER BY
    column_1,
    column_2 DESC;

eg：查询Company表中 按照工资升序排序 的所有员工的所有信息。

SELECT * FROM Company ORDER BY salary ASC;

eg2：查询Company表中 按 name 和 salary 升序排序 的所有员工的所有信息。

SELECT * FROM Company ORDER BY name, salary ASC;

eg3：查询Company表中 按 NAME 降序排序 的所有员工的所有信息。

SELECT * FROM Company ORDER BY name DESC;

15、GROUP BY 子句

用于对相同的数据进行分组。

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

基本语法：

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

eg：查询Company表中 每个客户的工资总额。

SELECT name, SUM(salary) FROM Company GROUP BY name;

eg2：查询Company表中 按 name 列进行分组的、按 name 列 升序 的每个客户的工资总额。

SELECT name, SUM(salary) FROM Company GROUP BY name ORDER BY name;

以上也是把 ORDER BY 子句与 GROUP BY 子句一起使用的例子。

16、HAVING 子句

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

注：HAVING 子句必须放在 GROUP BY 子句之后，必须放在 ORDER BY 子句之前。

基本语法：

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

eg：查询Company表中 按名称计数小于 2 的所有记录。

SELECT * FROM Company GROUP BY name HAVING count(name) < 2;

17、DISTINCT 关键字

用来消除所有重复的记录，并只获取唯一一次记录。

基本语法：

SELECT DISTINCT column1, column2,.....columnN 
FROM table_name
WHERE [condition]

eg：查询Company表中 所有不重复的name。

SELECT DISTINCT name FROM Company;

---------------------------------------------------------分割线---------------------------------------------------------------

以上，后续会再补充。各位小伙伴，欢迎提出问题~

我的学习文档是 菜鸟教程，致谢。

• 还没有人评论，欢迎说说您的想法！