标签：INSERT SQLite 入门 recordTime INTO content record 举例 VALUES

版权声明

本文来自博客园，作者：观心静，转载请注明原文链接：SQLite开发入门学习+复杂业务举例 - 观心静 - 博客园

本文版权归作者和博客园共有，欢迎转载，但必须给出原文链接，并保留此段声明，否则保留追究法律责任的权利。

前言

记录一些SQLite开发中的语句，前面部分说明一些简单语句，后面会重点说明举例复杂情况下的业务使用.

可以用这个网址临时模拟操作Sqlite数据库：SQL OnLine IDE

创建表

一个简单的例子

CREATE TABLE Book(Id INTEGER PRIMARY KEY, Name TEXT);

解释下上面语句的意思，创建了一个叫 Book 的表，其中数据字段分别是 id与name，其中id是整数类型(INTEGER)的同时也是这个表的主键， Name是文本类型(TEXT)。

其他可以创建的数据类型

INTEGER 整数类型

REAL 浮点类型,存储为 8 字节的 IEEE 浮点数字。

TEXT 文本类型，使用数据库编码（UTF-8、UTF-16BE 或 UTF-16LE）存储。

BLOB 是一个可以存储二进制文件的容器,主要用于存储二进制大对象,例如可以存储图片,音视频等文件，也可以存储一些未指定类型的数据

DATE 时间类型

DATETIME 时间类型

BOOLEAN SQLite可以使用BOOLEAN类型，也可以在BOOLEAN类型上插入布尔值，但是返回的数据是 0(flase) 和 1(true)

AUTOINCREMENT 自增主键id

CREATE TABLE Book(Id INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT);
INSERT INTO Book (Name) VALUES ('水浒传');
INSERT INTO Book (Name) VALUES ('三国演绎');
INSERT INTO Book (Name) VALUES ('西游记');
SELECT * FROM Book;

在主键上增加AUTOINCREMENT，在插入的时候就会自动递增主键id

结果：

DROP 删除表

这里举例删除上面创建的Book表

DROP TABLE Book;

INSERT 插入

INSERT 默认插入

INSERT INTO Book (Id,Name) VALUES (1, '三体');

INSERT OR REPLACE冲突处理策略>>替换覆盖插入

INSERT OR REPLACE INTO BOOK (Id,Name) VALUES (1, '新华字典');

如果我们插入数据库已有id，如果用上面的方式会提示约束错误，因为id是唯一的所以不让你插入。这个时候我们需要使用覆盖插入的方式

INSERT OR IGNORE 冲突处理策略>>忽略插入操作

INSERT OR IGNORE INTO Book (Id,Name) VALUES (1, '新华字典');

如果我们插入数据库已有id, 使用INSERT OR IGNORE 可以在插入冲突时，忽略此次插入操作

INSERT OR ABORT 冲突处理策略>>终止插入操作并抛出错误

INSERT OR ABORT INTO Book (Id,Name) VALUES (1, '新华字典');

DELETE 删除

删除表里的全部数据

DELETE FROM Book

删除指定数据

DELETE FROM Book WHERE id = 2;

删除id是2的数据，WHERE 查询子句这里先提一下举例。

UPDATE 更新

根据指定id更新name

UPDATE Book SET name = '新华字典' WHERE id = 1;

SELECT 查询

查询所有数据

SELECT * FROM Book;

查询指定字段数据，这里

SELECT name FROM Book;

结果：

WHERE 条件子句

查询指定名称

CREATE TABLE user(Id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER, height INTEGER, weight INTEGER);
INSERT INTO user (name, age, height, weight) VALUES ('张三', 19, 175, 60);
INSERT INTO user (name, age, height, weight) VALUES ('李四', 19, 175, 60);
INSERT INTO user (name, age, height, weight) VALUES ('王五', 19, 175, 60);
SELECT * FROM user WHERE name = '王五';

结果：

逻辑运算符

下面是 SQLite 中所有的逻辑运算符列表。大多数是配合WHERE这个查询语句使用，部分也可以在SELECT中使用

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

AND 多条件查询举例

CREATE TABLE user(Id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER, height INTEGER, weight INTEGER);
INSERT INTO user (name, age, height, weight) VALUES ('张三', 19, 175, 60);
INSERT INTO user (name, age, height, weight) VALUES ('李四', 18, 178, 62);
INSERT INTO user (name, age, height, weight) VALUES ('王五', 19, 179, 62);
SELECT * FROM user WHERE age = 18 AND weight = 62;

查询年龄等于18 并且体重等于62的数据项

结果：

BETWEEN 范围内查询举例

CREATE TABLE record(Id INTEGER PRIMARY KEY AUTOINCREMENT, content TEXT, recordTime INTEGER);
INSERT INTO record (content, recordTime) VALUES ('记录1', 1732693100);
INSERT INTO record (content, recordTime) VALUES ('记录2', 1732693200);
INSERT INTO record (content, recordTime) VALUES ('记录3', 1732693300);
INSERT INTO record (content, recordTime) VALUES ('记录4', 1732693400);
INSERT INTO record (content, recordTime) VALUES ('记录5', 1732693500);
INSERT INTO record (content, recordTime) VALUES ('记录6', 1732693600);
INSERT INTO record (content, recordTime) VALUES ('记录7', 1732693700);
INSERT INTO record (content, recordTime) VALUES ('记录8', 1732693800);
SELECT * FROM record WHERE recordTime BETWEEN 1732693300 AND 1732693600

结果:

可以直接使用 < 和 >来查询范围值

CREATE TABLE record(Id INTEGER PRIMARY KEY AUTOINCREMENT, content TEXT, recordTime INTEGER);
INSERT INTO record (content, recordTime) VALUES ('记录1', 1732693100);
INSERT INTO record (content, recordTime) VALUES ('记录2', 1732693200);
INSERT INTO record (content, recordTime) VALUES ('记录3', 1732693300);
INSERT INTO record (content, recordTime) VALUES ('记录4', 1732693400);
INSERT INTO record (content, recordTime) VALUES ('记录5', 1732693500);
INSERT INTO record (content, recordTime) VALUES ('记录6', 1732693600);
INSERT INTO record (content, recordTime) VALUES ('记录7', 1732693700);
INSERT INTO record (content, recordTime) VALUES ('记录8', 1732693800);
SELECT * FROM record WHERE recordTime > 1732693300 AND recordTime < 1732693600

结果：

EXISTS 判断某个值是否存在

CREATE TABLE record(Id INTEGER PRIMARY KEY AUTOINCREMENT, content TEXT, recordTime INTEGER);
INSERT INTO record (content, recordTime) VALUES ('记录1', 1732693100);
INSERT INTO record (content, recordTime) VALUES ('记录2', 1732693200);
INSERT INTO record (content, recordTime) VALUES ('记录3', 1732693300);
INSERT INTO record (content, recordTime) VALUES ('记录4', 1732693400);
INSERT INTO record (content, recordTime) VALUES ('记录5', 1732693500);
INSERT INTO record (content, recordTime) VALUES ('记录6', 1732693600);
INSERT INTO record (content, recordTime) VALUES ('记录7', 1732693700);
INSERT INTO record (content, recordTime) VALUES ('记录8', 1732693800);
SELECT EXISTS(SELECT recordTime FROM record WHERE content > '记录4');

结果：

查询结果是0或者1，这里 1=存在 0=不存在

NOT EXISTS 查询是否不存在

CREATE TABLE record(Id INTEGER PRIMARY KEY AUTOINCREMENT, content TEXT, recordTime INTEGER);
INSERT INTO record (content, recordTime) VALUES ('记录1', 1732693100);
INSERT INTO record (content, recordTime) VALUES ('记录2', 1732693200);
INSERT INTO record (content, recordTime) VALUES ('记录3', 1732693300);
INSERT INTO record (content, recordTime) VALUES ('记录4', 1732693400);
INSERT INTO record (content, recordTime) VALUES ('记录5', 1732693500);
INSERT INTO record (content, recordTime) VALUES ('记录6', 1732693600);
INSERT INTO record (content, recordTime) VALUES ('记录7', 1732693700);
INSERT INTO record (content, recordTime) VALUES ('记录8', 1732693800);
SELECT NOT EXISTS(SELECT recordTime FROM record WHERE content > '记录9');

结果：

查询结果是0或者1，这里 1=不存在 0=存在

IN 包含查询

CREATE TABLE record(Id INTEGER PRIMARY KEY AUTOINCREMENT, content TEXT, recordTime INTEGER);
INSERT INTO record (content, recordTime) VALUES ('记录1', 1732693100);
INSERT INTO record (content, recordTime) VALUES ('记录2', 1732693200);
INSERT INTO record (content, recordTime) VALUES ('记录3', 1732693300);
INSERT INTO record (content, recordTime) VALUES ('记录4', 1732693400);
INSERT INTO record (content, recordTime) VALUES ('记录5', 1732693500);
INSERT INTO record (content, recordTime) VALUES ('记录6', 1732693600);
INSERT INTO record (content, recordTime) VALUES ('记录7', 1732693700);
INSERT INTO record (content, recordTime) VALUES ('记录8', 1732693800);
SELECT * FROM record WHERE recordTime IN(1732693300, 1732693600, 1732693100)

查询含有1732693300, 1732693600, 1732693100 这个3个值的数据。

此外你可以跟上面一样改成 NOT IN 查询不包含的数据

结果:

Order By 排序

ORDER BY 子句的基本语法如下：

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

ASC 默认值，从小到大，升序排列
DESC 从大到小，降序排列

LIMIT限制返回数量

语法

带有 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]

Group By 数据分组

///根据一年的范围时间查询每个月的步数数据
///在返回的StepNumberBean数据中的step数据这个月份的步数总数
Future<List<StepNumberBean>> queryMonthDataByYearAsc(
    final DateTime startDateTime, final DateTime endDateTime) async {
  var map = await AppDB.instance.database.rawQuery(
      "SELECT datetime(time / 1000, 'unixepoch','localtime','start of month') AS month,SUM(step) AS total " +
          " FROM ${_TABLE_NAME} " +
          " WHERE time BETWEEN ? AND ? " +
          " GROUP BY month" +
          " ORDER BY month",
      [
        startDateTime.millisecondsSinceEpoch,
        endDateTime.millisecondsSinceEpoch
      ]);
  List<StepNumberBean> list = map
      .map((e) => StepNumberBean(
          DateTime.parse(e['month'] as String).millisecondsSinceEpoch,
          e['total'] as int,
          0))
      .toList();
  return list;
}

匹配文本格式 Glob

QLite 的 GLOB 运算符是用来匹配通配符指定模式的文本值。如果搜索表达式与模式表达式匹配，GLOB 运算符将返回真（true），也就是 1。与 LIKE 运算符不同的是，GLOB 是大小写敏感的，对于下面的通配符，它遵循 UNIX 的语法。

*：匹配零个、一个或多个数字或字符。
?：代表一个单一的数字或字符。
[...]：匹配方括号内指定的字符之一。例如，[abc] 匹配 "a"、"b" 或 "c" 中的任何一个字符。
[^...]：匹配不在方括号内指定的字符之一。例如，[^abc] 匹配不是 "a"、"b" 或 "c" 中的任何一个字符的字符。

以上这些符号可以被组合使用。

语法

* 和 ? 的基本语法如下：

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

匹配文本格式 Like

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

百分号（%）
下划线（_）

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

语法

% 和 _ 的基本语法如下：

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

Distinct 消除重复

sqlite> SELECT DISTINCT name FROM COMPANY;

别名

您可以暂时把表或列重命名为另一个名字，这被称为别名。使用表别名是指在一个特定的 SQLite 语句中重命名表。重命名是临时的改变，在数据库中实际的表的名称不会改变。

列别名用来为某个特定的 SQLite 语句重命名表中的列。

语法

表别名的基本语法如下：

SELECT column1, column2....
FROM table_name AS alias_name
WHERE [condition];

列别名的基本语法如下：

SELECT column_name AS alias_name
FROM table_name
WHERE [condition];

HAVING 语句的使用场景

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

根据某个字段的每日的总数，查询到达目标值(这里语句里写的目标值是2000)的数据

SELECT datetime(time / 1000, 'unixepoch', 'localtime','start of day') AS day, SUM(step) AS total FROM ${_TABLE_NAME} GROUP BY day HAVING total > 2000 ORDER BY day LIMIT 1

累加查询目标步数，返回达成目标步数那个位置的数据

这里用了嵌套SELECT查询, 这里的t是你的表的别名(别名在上面介绍过)

SELECT t.* ,
    (SELECT SUM(step)
    FROM you_table_name
    WHERE time <= t.time) AS cumulativeStep
FROM you_table_name t
GROUP BY  time
HAVING cumulativeStep >= 20000
ORDER BY  time ASC LIMIT 1

解释上面的语句：

1. 首先通过GROUP BY time 进行以time进行分组

2. 然后通过子查询 (SELECT SUM(step) FROM you_table_name WHERE time <= t.time) AS cumulativeStep ，查询小于外面分组后的time的step的总值(cumulativeStep)

3. 然后在通过HAVING 增加分组过滤条件(这里是cumulativeStep需要大于我们的目标值20000)，输出符合目标的数据(t.*)

时间与日期

SQLite 支持以下五个日期和时间函数：

序号	函数	实例
1	date(timestring, modifier, modifier, ...)	以 YYYY-MM-DD 格式返回日期。
2	time(timestring, modifier, modifier, ...)	以 HH:MM:SS 格式返回时间。
3	datetime(timestring, modifier, modifier, ...)	以 YYYY-MM-DD HH:MM:SS 格式返回。
4	julianday(timestring, modifier, modifier, ...)	这将返回从格林尼治时间的公元前 4714 年 11 月 24 日正午算起的天数。
5	strftime(format, timestring, modifier, modifier, ...)	这将根据第一个参数指定的格式字符串返回格式化的日期。具体格式见下边讲解。

----昨天
select * from 表 where Time>=datetime('now','start of day','-1 day') and Time<datetime('now','start of day','+0 day')

----当天
select * from 表 where Time>=datetime('now','start of day','+0 day') and Time<datetime('now','start of day','+1 day')

----当周
select  * from 表 where Time>=datetime('now','start of day','-7 day','weekday 1') AND Time<datetime('now','start of day','+0 day','weekday 1')

----当月
select * from 表 where Time>=datetime('now','start of month','+0 month','-0 day') AND Time < datetime('now','start of month','+1 month','0 day')

----上月
select * from 表 where Time>=datetime('now','start of month','-1 month','-0 day') AND Time <datetime('now','start of month','+0 month','-1 day')

AVG 平均数

///根据一年的范围时间查询每个月的体重平均值
///注意！在返回的WeightBean数据中的weight数据这个月份的平均值
Future<List<WeightBean>> queryMonthDataByYearAsc(
    final DateTime startDateTime, final DateTime endDateTime) async {
  var map = await AppDB.instance.database.rawQuery(
      "SELECT datetime(time / 1000, 'unixepoch','localtime','start of month') AS month, AVG(weight) AS avgWeight " +
          " FROM ${_TABLE_NAME} " +
          " WHERE isDelete = 0 AND time >= ? AND time <= ? " +
          " GROUP BY month" +
          " ORDER BY month",
      [
        startDateTime.millisecondsSinceEpoch,
        endDateTime.millisecondsSinceEpoch
      ]);
  List<WeightBean> list = map
      .map((e) => WeightBean(DateTime.parse(e['month'] as String).millisecondsSinceEpoch, (e['avgWeight'] as double).toInt()))
      .toList();
  return list;
}

SQLite 约束

约束是在表的数据列上强制执行的规则，这些是用来限制可以插入到表中的数据类型，这确保了数据库中数据的准确性和可靠性。

约束可以是列级或表级。列级约束仅适用于列，表级约束被应用到整个表。

以下是在 SQLite 中常用的约束。

NOT NULL 约束：确保某列不能有 NULL 值。
DEFAULT 约束：当某列没有指定值时，为该列提供默认值。
UNIQUE 约束：确保某列中的所有值是不同的。
PRIMARY Key 约束：唯一标识数据库表中的各行/记录。
CHECK 约束：CHECK 约束确保某列中的所有值满足一定条件。

End

标签：INSERT,SQLite,入门,recordTime,INTO,content,record,举例,VALUES
From： https://www.cnblogs.com/guanxinjing/p/18390528

SQLite开发 入门学习+复杂业务举例