LV07-05-SQLite-NULL、约束和主键

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

本文主要是SQLite数据库的SQLite数据库中经常见到的NULL、约束和PRIMARY KEY相关笔记，若笔记中有错误或者不合适的地方，欢迎批评指正😃。

点击查看使用工具及版本
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 教程
点击查看相关文件下载
--- ---

一、NULL值

先来学习一个NULL，SQLite 的NULL是用来表示一个缺失值的项。表中的一个 NULL 值是在字段中显示为空白的一个值。带有 NULL 值的字段是一个不带有值的字段。NULL 值与零值或包含空格的字段是不同的。

1. 语法格式

创建数据表的时候使用NULL的基本语法如下：

1
2
3
4
5
6
7
SQLite> CREATE TABLE company(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

在这里，NOT NULL表示列总是接受给定数据类型的显式值。这里有两个列没有使用 NOT NULL，这意味着这两个列可以为 NULL。带有 NULL 值的字段在记录创建的时候可以保留为空。

2. 使用实例

2.1 数据准备

我们先来准备一个数据表用于测试：

2.1.1 创建数据表

1
2
3
4
5
6
7
SQLite> create table test(
   id int primary key     not null,
   name           TEXT    not null,
   age            INT     not null,
   address        char(50),
   salary         real
);

2.1.2 添加数据

1
2
3
SQLite> insert into test values (1, "qidaink", 18, "China", 2000);
SQLite> insert into test values (2, "Allen", 19, "Texas", 3000);
SQLite> insert into test values (3, "fanhua", 25,  "Houston", 1000);

2.1.3 格式化输出

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

2.1.4 查看数据情况

1
2
3
4
5
6
sqlite> select * from test;
id          name        age         address     salary    
----------  ----------  ----------  ----------  ----------
1           qidaink     18          China       2000.0    
2           Allen       19          Texas       3000.0    
3           fanhua      25          Houston     1000.0

2.1.5 将部分数据设为NULL

1
sqlite> update test set address = null, salary = null where id in(2, 3);

2.1.6 查看当前数据情况

1
2
3
4
5
6
sqlite> select * from test;
id          name        age         address     salary    
----------  ----------  ----------  ----------  ----------
1           qidaink     18          China       2000.0    
2           Allen       19                                
3           fanhua      25

2.2 null数据查询

2.2.1 列出所有 salary 不为 null 的记录

1
2
3
4
sqlite> select * from test where salary is not null;
id          name        age         address     salary    
----------  ----------  ----------  ----------  ----------
1           qidaink     18          China       2000.0

2.2.2 列出所有address为null的记录

1
2
3
4
5
sqlite> select * from test where address is null;
id          name        age         address     salary    
----------  ----------  ----------  ----------  ----------
2           Allen       19                                
3           fanhua      25

二、约束

1. 约束的概念

其实上边新创建表的时候使用的NOT NULL就属于一种约束，约束是在表的数据列上强制执行的规则。这些是用来限制可以插入到表中的数据类型。这确保了数据库中数据的准确性和可靠性。约束可以是列级或表级。列级约束仅适用于列，表级约束被应用到整个表。

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

2. NOT NULL约束

默认情况下，列可以保存 NULL 值。如果我们不希望某列有 NULL 值，那么需要在该列上定义此约束，指定在该列上不允许 NULL 值。NULL 与没有数据是不一样的，它代表着未知的数据。例如，

1
2
3
4
5
6
7
CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

该语句创建一个新的表 COMPANY，并增加了五列，其中 ID、NAME 和 AGE 三列指定不接受 NULL 值。

3. DEFAULT约束

DEFAULT 约束在 INSERT INTO 语句没有提供一个特定的值时，为列提供一个默认值。例如，

1
2
3
4
5
6
7
CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL    DEFAULT 100.00
);

该语句创建一个新的表 COMPANY，并增加了五列。在这里，SALARY 列默认设置为 100.00。所以当 INSERT INTO 语句没有为该列提供值时，该列将被设置为 100.00。

4. UNIQUE约束

UNIQUE 约束防止在一个特定的列存在两个记录具有相同的值。例如，在 COMPANY 表中，我们可能要防止两个或两个以上的人具有相同的年龄。例如，

1
2
3
4
5
6
7
CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL UNIQUE,
   ADDRESS        CHAR(50),
   SALARY         REAL    DEFAULT 50000.00
);

该语句创建一个新的表 COMPANY，并增加了五列。在这里，AGE 列设置为 UNIQUE，所以不能有两个相同年龄的记录。

5. PRIMARY KEY约束

PRIMARY KEY 约束唯一标识数据库表中的每个记录。在一个表中可以有多个 UNIQUE 列，但只能有一个主键。在设计数据库表时，主键是很重要的，主键是唯一的 ID。

我们使用主键来引用表中的行。可通过把主键设置为其他表的外键，来创建表之间的关系。由于”长期存在编码监督”，在 SQLite 中，主键可以是 NULL，这是与其他数据库不同的地方。

主键是表中的一个字段，唯一标识数据库表中的各行或者说是各条记录。主键必须包含唯一值。主键列不能有 NULL 值。一个表只能有一个主键，它可以由一个或多个字段组成。当多个字段作为主键，它们被称为复合键。如果一个表在任何字段上定义了一个主键，那么在这些字段上不能有两个记录具有相同的值。

1
2
3
4
5
6
7
CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

该语句创建一个新的表 COMPANY，并增加了五列。在这里，ID列就是主键。

6. CHECK约束

CHECK 约束启用输入一条记录要检查值的条件。如果条件值为 false，则记录违反了约束，且不能输入到表。例如

1
2
3
4
5
6
7
CREATE TABLE COMPANY3(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL    CHECK(SALARY > 0)
);

该语句创建一个新的表 COMPANY，并增加了五列。在这里，我们为 SALARY 列添加 CHECK，所以工资不能为零。

三、主键

前边我们已经多次使用到这个主键了，SQLite主键是用于唯一定义行记录的一个简单字段或多个字段的组合。一个表只能有一个主键。在SQLite中主键的值可以是一个NULL值。

1. 创建主键

我们可以在创建表的时候直接定义主键，格式如下：

1
2
3
4
5
6
CREATE TABLE table_name(  
    column1 datatype PRIMARY KEY,  
    column2 datatype,  
    ......  
    columnN datatype,  
);

2. 添加主键

当我们没有在CREATE TABLE语句中定义主键时，也可以在创建表后再添加主键。需要注意的是，不能使用ALTER TABLE语句来创建主键。在SQLite中需要先创建一个与原表一样的新表，并在这个新表上创建主键，然后复制旧表中的所有数据到新表中就可以了。步骤如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
PRAGMA foreign_keys=off;  

BEGIN TRANSACTION;  

ALTER TABLE table_name RENAME TO old_table; 

CREATE TABLE table_name(  
    column1 datatype PRIMARY KEY,  
    column2 datatype,  
    ......  
    columnN datatype,  
); 

INSERT INTO table_name SELECT * FROM old_table;  

COMMIT;  

PRAGMA foreign_keys=on;

DROP TABLE old_table; -- 删除原来的表

3. 删除主键

与添加主键一样，不能使用ALTER TABLE语句来删除主键。需要创建一个没有(删除)主键的新表，并将数据复制到此新表中。格式如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
PRAGMA foreign_keys=off;  

BEGIN TRANSACTION;  

ALTER TABLE table_name RENAME TO old_table;  

CREATE TABLE table_name(  
    column1 datatype PRIMARY KEY,  
    column2 datatype,  
    ......  
    columnN datatype,  
);  

INSERT INTO table_name SELECT * FROM old_table;  

COMMIT;  

PRAGMA foreign_keys=on;

DROP TABLE old_table; -- 删除原来的表

四、自动递增

SQLite 的 AUTOINCREMENT 是一个关键字，用于表中的字段值自动递增。我们可以在创建表时在特定的列名称上使用 AUTOINCREMENT 关键字实现该字段值的自动增加。

【注意】

（1）关键字 AUTOINCREMENT 只能用于整型（INTEGER）字段，否则会报错，INT类型也是不可以的。

（2）只能用于PRIMARY KEY字段，否则会报错。

1. 语法格式

一般语法格式如下：

1
2
3
4
5
6
CREATE TABLE table_name(
   column1 INTEGER PRIMARY KEY AUTOINCREMENT,
   column2 datatype,
   .....
   columnN datatype,
);

2. 自增字段赋值

自增字段赋值有三种方法：一是不赋值，二是直接赋值，三是置为NULL。若是进行了赋值，下一条数据记录的自增字段若是不进行赋值，那么这条数据记录的自增字段将会在上一条即基础上加1。

3. 使用实例

  • 创建带自增字段的数据表
1
sqlite> create table test(id integer primary key autoincrement, name text not null);
  • 设置输出格式
1
2
sqlite>.head on
sqlite>.mode column
  • 添加数据
1
2
3
4
5
6
7
8
9
10
11
12
13
-- 不赋值
sqlite> insert into test(name) values("a");
sqlite> insert into test(name) values("b");
sqlite> insert into test(name) values("c");

-- 赋值
sqlite> insert into test(id, name) values(100, "d");
sqlite> insert into test(id, name) values(200, "e");
sqlite> insert into test(name) values("f");

-- 赋值为null
sqlite> insert into test(id, name) values(NULL, "g");
sqlite> insert into test(name) values("h");
  • 查看数据
1
2
3
4
5
6
7
8
9
10
11
sqlite> select * from test;
id          name      
----------  ----------
1           a         
2           b         
3           c         
100         d         
200         e         
201         f         
202         g         
203         h