LV07-03-SQLite-语句

发表于 分类于 ， ， 本文字数： 5k 阅读时长 ≈ 18 分钟

本文主要是SQLite数据库的语句相关笔记，若笔记中有错误或者不合适的地方，欢迎批评指正😃。

点击查看使用工具及版本
Windows windows11
Ubuntu Ubuntu16.04的64位版本
VMware® Workstation 16 Pro 16.2.3 build-19376536
SecureCRT Version 8.7.2 (x64 build 2214) - 正式版-2020年5月14日
开发板 正点原子 i.MX6ULL Linux阿尔法开发板
uboot NXP官方提供的uboot，NXP提供的版本为uboot-imx-rel_imx_4.1.15_2.1.0_ga(使用的uboot版本为U-Boot 2016.03)
linux内核 linux-4.15(NXP官方提供)
STM32开发板 正点原子战舰V3(STM32F103ZET6)
点击查看本文参考资料
参考方向 参考原文
SQLite官网SQLite Home Page
SQLite中文网SQLite 中文网
SQLite 基础教程SQLite参考手册 - 在线原生手册
菜鸟教程SQLite 教程
易百教程SQLite 教程
点击查看相关文件下载
--- ---

下边介绍语句的时候，可能会用到SQLite的一些命令，这些命令会在后边的笔记中进行说明，这里就先知道怎么用就好啦。

一、SQLite语句与注释

1. 语句语法

在SQLite中的语句，严格意义上来说，更像是命令，SQLite中的语句有以下特点：

  • 都是以关键字(如：SELECT，INSERT，UPDATE，DELETE，ALTER，DROP等)开始的。
  • 语句可以多行书写，并且所有的语句都以分号（;）结尾。
  • SQLite 是不区分大小写，但是需要注意的是，也有一些命令是大小写敏感的，比如 GLOB和 glob在 SQLite 的语句中有不同的含义。

我看网上的大部分教程都是全部写的大写，但是我上的网课中，老师都是用的小写，也都没啥问题，那些个特殊的，遇到的时候再总结就好啦。（所以，后边在写笔记的时候，记录语句格式的时候用的都是大写，但是写实例的时候我自己就都用的小写了）

2. 注释

SQLite的语句是可以有注释的，它的注释是连续两个-字符（ASCII 0x2d）开始，并扩展至下一个换行符（ASCII 0x0a）或直到输入结束，以先到者为准。也可以使用 C 风格的注释，以 /* 开始，并扩展至下一个 */字符对或直到输入结束，以先到者为准。

【注意】

（1）注释不能嵌套。

（2）SQLite的注释可以跨越多行。

二、子句

这里还是先学习一下子句，毕竟后边很多命令都会与子句相结合使用。

1. where

1.1 子句说明

SQLite的 WHERE子句用于指定从一个表或多个表中获取数据的条件。它通常与SELECT，UPDATE和DELETE语句一起使用来，以便于对特定的对象进行相关操作。以SELECT为例，一般语法格式如下：

1
2
3
4
5
6
7
-- 多行语句
SELECT column1, column2, columnN   
FROM table_name  
WHERE [condition];

-- 单行语句
SELECT column1, column2, columnN FROM table_name WHERE [condition];

实例这里就不再写了，后边学习SELECT、UPDATE等语句的时候会加上关于WHERE的使用实例。

1.2 使用实例

暂无

2. like

2.1 子句说明

SQLite中的LIKE运算符用于使用通配符将文本值与模式进行匹配。 在搜索表达式与模式表达式匹配的情况下，LIKE运算符将返回真（true），即：1。这里有两个通配符与 LIKE 子句一起使用：

  • 百分号 （%），代表零个、一个或多个数字或字符。
  • 下划线 （_），代表一个单一的数字或字符。

以SELECT为例，一般语法格式如下：

1
2
3
4
5
6
7
8
9
SELECT column_list FROM table_name WHERE column LIKE 'XXX%';
-- or 
SELECT column_list FROM table_name WHERE column LIKE '%XXX%';
-- or
SELECT column_list FROM table_name WHERE column LIKE 'XXX_';
-- or
SELECT column_list FROM table_name WHERE column LIKE '_XXX';
-- or
SELECT column_list FROM table_name WHERE column LIKE '_XXX_';

我们可以使用 AND 或 OR 运算符来结合 N 个数量的条件。在这里，XXX 可以是任何数字或字符串值。

【注意】

（1）一般来说LIKE会与WHERE连用。

（2）这些符号可以被组合使用。

2.2 使用实例

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

三、常用语句

1. CREATE TABLE

1.1 语句格式

1
2
3
4
5
6
7
8
CREATE TABLE table_name(
   column1 datatype  PRIMARY KEY(one or more columns),
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
);

【语句说明】该语句用于在一个数据库中创建一个数据表，并添加表头。这个表就类似于Windows下的Excel中的工作簿下边的一张工作表一样，一个工作簿可以有多张工作表，一个数据库也可以有多个数据表。

【语句参数】

  • CREATE：这是一个关键字，表示创建一个数据表。
  • table_name：数据表的名称。
  • columnN：这表示数据表中的表头名称。
  • datatype：：表示这列数据的存储类。
  • PRIMARY KEY：主关键字(primary key)，它是表中的一个或多个字段，它的值用于唯一的标识表中的某一条记录。在两个表的关系中，主关键字用来在一个表中引用来自于另一个表中的特定记录。主关键字是一种唯一关键字，它是表定义的一部分。一个表的主键可以由多个关键字共同组成，并且主关键字的列不能包含空值，另外主关键字是可选的。这个主键后边会再专门介绍一次，因为有个作业用到了这个主关键字。

【注意】语句最后的分号（;）是语句结束的标志，不可缺少。

1.2 相关命令

1.2 查看数据库中的表

怎么知道当前数据库下有哪些表呢，我们可以使用下边的命令：

1
.tables

1.3 查看表信息

我们可以通过下边的命令来查看创建表时完整的信息：

1
.schema

1.3 使用实例

我们在一个创建好的数据库中创建一个数据表，可以在SQLite命令提示符后边输入以下语句：

1
2
3
4
5
6
7
8
9
10
-- 多行语句
create table stuinfo(
     id    integer primary key, 
     name  text, 
     age   integer, 
     score float
);

-- 单行语句
create table stuinfo(id integer primary key, name text, age integer, score float);

为了确认我们是否创建成功，我们需要知道在SQLite命令提示符后边输入以下命令就可以查看当前数据库中的所有数据表：

1
sqlite> .tables -- 后边会再具体说明这种类型的命令如何使用

然后我们就会看到如下信息输出：

1
stuinfo

这说明，我们创建的stuinfo表已经创建成功了。

有时候我们需要查看表的完整信息，我们可以在SQLite3中使用以下命令来查看：

1
sqlite> .schema stuinfo -- 后边会再具体说明这种类型的命令如何使用

然后我们会看到有如下信息输出：

1
CREATE TABLE stuinfo(id integer primary key, name text, age integer, score float);

这个时候会显示我们创建表时使用的语句，我们使用的小写关键字此时都会变成大写关键字。

2. INSERT INTO

2.1 语句格式

1
2
3
4
5
6
7
8
9
10
11
INSERT INTO table_name(column1, column2, ..., columnN) VALUES(value1, value2, ..., valueN);

-- or(为所有列添加值)

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];

【语句说明】该语句用于在一个数据表中添加一条数据记录。

【语句参数】

  • INSERT：这是一个关键字，表示插入数据。
  • table_name：记录要插入的数据表的名称。
  • columnN：表示数据表中的表头名称，就是我们要插入到哪一列。
  • valueN：表示要插入列的数据的值，当我们要插入的数据个数与列数一一对应的时候，上边的columnN是可以省略掉的，但要确保值的顺序与列在表中的顺序一致。若是要添加的值少于创建表时的列数，我们就需要加上columN，表示我们想要把这条记录的这个数据插入在哪一列。

【注意】语句最后的分号（;）是语句结束的标志，不可缺少。

2.2 使用实例

我们在一个创建好的数据表中插入一条数据记录，可以在SQLite命令提示符后边输入以下语句：

1
2
3
4
insert into stuinfo values(1001, 'fanhua', 18, 80);
insert into stuinfo (id, name, score) values(1002, 'qidaink', 90);

insert into stuinfo values(1001, 'fanhua', 18); -- 会报错

当我们执行前两条语句的时候，是没有任何问题的，执行第三条时，会报以下错误：

1
Parse error: table stuinfo has 4 columns but 3 values were supplied

这就是因为我们并未指定数据要插入到哪一列，而数据的个数又少于了创建表时创建的列数了。那我们插入数据是否插入成功了呢？我们这个时候就需要使用select来显示记录啦。

3. SELECT

3.1 语句格式

1
2
3
4
5
6
SELECT column1, column2, columnN FROM table_name; -- 查看指定列数据
SELECT * FROM table_name;                         -- 查看全部列数据

-- 带WHERE子句的格式

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

【语句说明】该语句用于从 SQLite 数据库表中获取数据，以结果表的形式返回数据，这些结果表也被称为结果集。

【语句参数】

  • SELECT：这是一个关键字，表示获取数据。
  • columnN：表示数据表中的表头名称，就是我们获取哪些列的数据，需要获取所有列数据的时候，可以使用*代替。
  • table_name：记录要获取数据所在数据表的名称。

【注意】语句最后的分号（;）是语句结束的标志，不可缺少。

3.2 相关命令

3.2.1 输出列格式

为了后边使用SELECT语句显示的数据更加直观，我们可以使用SELECT语句之前使用以下SQLite命令来设置输出列的格式：

1
2
.head on
.mode column

3.2.2 输出列宽度

有的时候可能会因为显示宽度不够而导致有些比较长的数据在显示的时候被截断，我们可以使用以下命令来设置输出列的宽度：

1
.width num1, num2, ... -- 设置第一列的宽度为 num1，第二列的宽度为 num2, ...

3.3 创建表时的信息

创建表的时候，我们查看表的完整信息是通过.schema命令来查看，由于点命令（后边再说）只在 SQLite 提示符中可用，所以当我们进行带有 SQLite 的编程时，我们可以使用下面的带有sqlite_master表的 SELECT 语句来列出所有在数据库中创建的表以及表的完整信息：

1
2
SELECT tbl_name  FROM sqlite_master WHERE type = 'table'; -- 列出数据库中所有表
SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'table_name'; -- 列出数据库中指定表的完整信息

3.4 使用实例

3.4.1 设置输出列格式

在使用SELECT之前，我们先来设置一下输出格式：

1
2
sqlite> .head on
sqlite> .mode column

3.4.2 查看表中所有数据

我们在一个上边已经插入了几条数据记录，可以在SQLite命令提示符后边输入以下语句：

1
sqlite> select * from stuinfo;

然后我们便会看到以下信息显示了：

1
2
3
4
id          name        age         score     
----------  ----------  ----------  ----------
1001        fanhua      18          80.0      
1002        qidaink                 90.0

3.4.3 查看表中特定数据

在SQLite命令提示符后边输入以下语句：

1
sqlite> select * from stuinfo where name="fanhua";

然后我们便会看到以下信息显示了：

1
2
3
id          name        age         score     
----------  ----------  ----------  ----------
1001        fanhua      18          80.0

3.4.4 查看表信息

我们再创建一个数据表：

1
sqlite> create table test(name text, age integer);

我们在SQLite命令提示符中输入以下语句来查看所有创建的表：

1
sqlite> select tbl_name  from sqlite_master where type = 'table';

然后我们会看到以下输出：

1
2
3
4
tbl_name  
----------
stuinfo   
test

接下来我们输入以下命令来查看指定表的完整信息：

1
sqlite> select sql from sqlite_master where type = 'table' and tbl_name = 'stuinfo';

然后我们会看到以下输出：

1
2
3
sql                                                                              
---------------------------------------------------------------------------------
CREATE TABLE stuinfo(id integer primary key, name text, age integer, score float)

4. UPDATE

4.1 语句格式

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

【语句说明】该语句用于从 SQLite 数据库表中更新某一列的数据，可以使用带有 WHERE 子句（这它是一种条件判断，意思就是满足某种条件时再执行这条语句）的 UPDATE 查询来更新选定行，否则所有的行都会被更新。

【语句参数】

  • UPDATE：这是一个关键字，表示更新数据。
  • table_name：记录要更新数据所在数据表的名称。
  • columnN：表示数据表中的表头名称，就是我们要更新哪些列的数据。
  • valueN：表示需要更新的列的数据的新值。
  • WHERE：是SQLite的子句，用于指定从一个表或多个表中获取数据的条件。
  • condition：就是要满足的条件。

【注意】语句最后的分号（;）是语句结束的标志，不可缺少。

4.2 使用实例

我们在一个上边已经插入了几条数据记录，并且设置了显示列的格式，我们可以在SQLite命令提示符后边输入以下语句类更新表中的数据：

1
2
sqlite> update stuinfo set name='FANHUA', age=19 where id=1001; -- 更新数据
sqlite> select * from stuinfo; -- 查看更新后的数据

然后我们便会看到以下信息显示了：

1
2
3
4
id          name        age         score     
----------  ----------  ----------  ----------
1001        FANHUA      19          80.0      
1002        qidaink                 90.0

这个时候就会发现数据已经更新完成了。

5. DELETE

5.1 语句格式

1
DELETE FROM table_name WHERE [condition];

【语句说明】该语句用于从 SQLite 数据库表中删除一条数据记录。可以使用带有 WHERE 子句的 DELETE查询来删除选定行，否则所有的记录都会被删除。

【语句参数】

  • DELETE：这是一个关键字，表示删除数据。
  • table_name：记录要删除数据所在数据表的名称。
  • WHERE：是SQLite的子句，用于指定从一个表或多个表中获取数据的条件。
  • condition：就是要满足的条件。

【注意】语句最后的分号（;）是语句结束的标志，不可缺少。

5.2 使用实例

我们在一个上边已经插入了几条数据记录，也已经设置了显示格式，可以在SQLite命令提示符后边输入以下语句来删除表中的数据：

1
2
sqlite> delete from stuinfo where id=1001; -- 删除id为1001的数据
sqlite> select * from stuinfo; -- 查看更新后的数据

然后我们便会看到以下信息显示了：

1
2
3
id          name        age         score     
----------  ----------  ----------  ----------
1002        qidaink                 90.0

这个时候就会发现数据已经只剩下一条了，之前id为1001的数据已经被删除了。

6. DROP

6.1 语句格式

1
DROP TABLE table_name;

【语句说明】该语句用于从 SQLite 数据库表中删除表定义及其所有相关数据、索引、触发器、约束和该表的权限规范。

【语句参数】

  • DROP：这是一个关键字，表示删除数据表。
  • table_name：记录要删除数据表的名称。

【注意】

（1）语句最后的分号（;）是语句结束的标志，不可缺少。

（2）使用此命令时要特别注意，一旦一个表被删除，表中所有信息也将永远丢失。

6.2 使用实例

我们在上边是创建了两个表，我们查看一下数据库中的数据表：

1
2
sqlite> .tables
stuinfo  test

我们在在SQLite命令提示符后边输入以下语句来删除一个数据表：

1
sqlite> drop table test;

然后我们重新查看这个数据库中所有的数据表：

1
2
sqlite> .tables
stuinfo

这个时候就会发现数据只剩下stuinfo了。

7. ALTER

7.1 语句格式

1
2
3
ALTER TABLE table_name RENAME TO new_table_name; -- 重命名数据表
ALTER TABLE table_name ADD COLUMN column_def...; -- 添加新列
ALTER TABLE tablename DROP (columN); -- 删除一个字段

【语句说明】SQLite 的 ALTER TABLE 命令不通过执行一个完整的转储和数据的重载来修改已有的表。我们可以使用 ALTER TABLE 语句重命名表，使用 ALTER TABLE 语句还可以在已有的表中添加额外的列。

【语句参数】这个语句功能和形式较多，这里就不再写参数说明了，主要是通过实例来看。

【注意】语句最后的分号（;）是语句结束的标志，不可缺少。

7.2 使用实例

7.2.1 重命名数据表

我们可以在SQLite命令提示符后边输入以下语句来重命名数据表：

1
sqlite> alter table stuinfo RENAME TO studentsInfo;

然后我们通过.tables命令查看一下当前数据库中的数据表，便会看到以下信息显示了：

1
2
sqlite> .tables
studentsInfo

这个时候就会发现数据表名称已经被修改了，那数据是否还存在呢？我们使用以下命令来查看一下现在表中的数据：

1
select * from studentsInfo; -- 查看重命名表之后表中的数据

然后我们会看到以下信息：

1
2
3
id          name        age         score     
----------  ----------  ----------  ----------
1002        qidaink                 90.0

这条信息就是上一步DELETE实例之后剩下的数据记录了。

7.2.2 添加一个新列

我们可以在SQLite命令提示符后边输入以下语句来添加新列：

1
sqlite> alter table studentsInfo ADD COLUMN SEX char;

我们使用以下命令来查看一下现在表中的数据：

1
sqlite> select * from studentsInfo; -- 查看添加新列之后表中的数据

然后我们会看到以下信息：

1
2
3
id          name        age         score       SEX       
----------  ----------  ----------  ----------  ----------
1002        qidaink                 90.0

由于我们只是赋值，所以这里是没有初始数据的，但是由于我们打开了表头显示，所以这里还是会显示这一新增的列。