SQLite 数据类型
SQLite 数据类型是一个用来指定任何对象的数据类型的属性。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 的存储类稍微比数据类型更普遍。INTEGER 存储类,例如,包含 6 种不同的不同长度的整数数据类型。
SQLite 亲和(Affinity)类型
SQLite支持列的亲和类型概念。任何列仍然可以存储任何类型的数据,当数据插入时,该字段的数据将会优先采用亲缘类型作为该值的存储方式。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 亲和类型(Affinity)及类型名称
下表列出了当创建 SQLite3 表时可使用的各种数据类型名称,同时也显示了相应的亲和类型:
数据类型 | 亲和类型 |
| INTEGER |
| TEXT |
| NONE |
| REAL |
| NUMERIC |
Boolean 数据类型
SQLite 没有单独的 Boolean 存储类。相反,布尔值被存储为整数 0(false)和 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 创建数据库
SQLite 的 sqlite3 命令被用来创建新的 SQLite 数据库。您不需要任何特殊的权限即可创建一个数据。
语法
sqlite3 命令的基本语法如下:
$sqlite3 DatabaseName.db
通常情况下,数据库名称在 RDBMS 内应该是唯一的。
实例
如果您想创建一个新的数据库 <testDB.db>,SQLITE3 语句如下所示:
$sqlite3 testDB.db
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
上面的命令将在当前目录下创建一个文件 testDB.db。该文件将被 SQLite 引擎用作数据库。如果您已经注意到 sqlite3 命令在成功创建数据库文件之后,将提供一个 sqlite> 提示符。
一旦数据库被创建,您就可以使用 SQLite 的 .databases 命令来检查它是否在数据库列表中,如下所示:
sqlite>.databases
seq name file
--- --------------- ----------------------
0 main /home/sqlite/testDB.db
您可以使用 SQLite .quit 命令退出 sqlite 提示符,如下所示:
sqlite>.quit
$
.dump 命令
您可以在命令提示符中使用 SQLite .dump 点命令来导出完整的数据库在一个文本文件中,如下所示:
$sqlite3 testDB.db .dump > testDB.sql
上面的命令将转换整个 testDB.db 数据库的内容到 SQLite 的语句中,并将其转储到 ASCII 文本文件 testDB.sql 中。您可以通过简单的方式从生成的 testDB.sql 恢复,如下所示:
$sqlite3 testDB.db < testDB.sql
SQLite 创建表
SQLite 的 CREATE TABLE 语句用于在任何给定的数据库创建一个新表。创建基本表,涉及到命名表、定义列及每一列的数据类型。
语法
CREATE TABLE 语句的基本语法如下:
CREATE TABLE database_name.table_name(
column1 datatype PRIMARY KEY(one or more columns),
column2 datatype,
column3 datatype,
.....
columnN datatype,
);
CREATE TABLE 是告诉数据库系统创建一个新表的关键字。CREATE TABLE 语句后跟着表的唯一的名称或标识。您也可以选择指定带有 table_name 的 database_name。
实例
下面是一个实例,它创建了一个 COMPANY 表,ID 作为主键,NOT NULL 的约束表示在表中创建纪录时这些字段不能为 NULL:
sqlite> CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
让我们再创建一个表,我们将在随后章节的练习中使用:
sqlite> CREATE TABLE DEPARTMENT(
ID INT PRIMARY KEY NOT NULL,
DEPT CHAR(50) NOT NULL,
EMP_ID INT NOT NULL
);
您可以使用 SQLIte 命令中的 .tables 命令来验证表是否已成功创建,该命令用于列出附加数据库中的所有表。
sqlite>.tables
COMPANY DEPARTMENT
在这里,可以看到我们刚创建的两张表 COMPANY、 DEPARTMENT。
您可以使用 SQLite .schema 命令得到表的完整信息,如下所示:
sqlite>.schema COMPANY
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
SQLite 删除表
SQLite 的 DROP TABLE 语句用来删除表定义及其所有相关数据、索引、触发器、约束和该表的权限规范。
使用此命令时要特别注意,因为一旦一个表被删除,表中所有信息也将永远丢失。
语法
DROP TABLE 语句的基本语法如下。您可以选择指定带有表名的数据库名称,如下所示:
DROP TABLE database_name.table_name;
实例
让我们先确认 COMPANY 表已经存在,然后我们将其从数据库中删除。
sqlite>.tables
COMPANY test.COMPANY
这意味着 COMPANY 表已存在数据库中,接下来让我们把它从数据库中删除,如下:
sqlite>DROP TABLE COMPANY;
sqlite>
现在,如果尝试 .TABLES 命令,那么将无法找到 COMPANY 表了:
sqlite>.tables
sqlite>
显示结果为空,意味着已经成功从数据库删除表。
SQLite Insert 语句
SQLite 的 INSERT INTO 语句用于向数据库的某个表中添加新的数据行。
语法
INSERT INTO 语句有两种基本语法,如下所示:
INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)]
VALUES (value1, value2, value3,...valueN);
在这里,column1, column2,...columnN 是要插入数据的表中的列的名称。
如果要为表中的所有列添加值,您也可以不需要在 SQLite 查询中指定列名称。但要确保值的顺序与列在表中的顺序一致。SQLite 的 INSERT INTO 语法如下:
INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);
实例
假设您已经在 testDB.db 中创建了 COMPANY表,如下所示:
sqlite> CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
现在,下面的语句将在 COMPANY 表中创建六个记录:
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Allen', 25, 'Texas', 15000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'David', 27, 'Texas', 85000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Kim', 22, 'South-Hall', 45000.00 );
您也可以使用第二种语法在 COMPANY 表中创建一个记录,如下所示:
INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 );
上面的所有语句将在 COMPANY 表中创建下列记录。下一章会教您如何从一个表中显示所有这些记录。
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0
使用一个表来填充另一个表
您可以通过在一个有一组字段的表上使用 select 语句,填充数据到另一个表中。下面是语法:
INSERT INTO first_table_name [(column1, column2, ... columnN)]
SELECT column1, column2, ...columnN
FROM second_table_name
[WHERE condition];
SQLite Select 语句
SQLite 的 SELECT 语句用于从 SQLite 数据库表中获取数据,以结果表的形式返回数据。这些结果表也被称为结果集。
语法
SQLite 的 SELECT 语句的基本语法如下:
SELECT column1, column2, columnN FROM table_name;
在这里,column1, column2...是表的字段,他们的值即是您要获取的。如果您想获取所有可用的字段,那么可以使用下面的语法:
SELECT * FROM table_name;
实例
假设 COMPANY 表有以下记录:
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0
下面是一个实例,使用 SELECT 语句获取并显示所有这些记录。在这里,前三个命令被用来设置正确格式化的输出。
sqlite>.header on
sqlite>.mode column
sqlite> SELECT * FROM COMPANY;
最后,将得到以下的结果:
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0
如果只想获取 COMPANY 表中指定的字段,则使用下面的查询:
sqlite> SELECT ID, NAME, SALARY FROM COMPANY;
上面的查询会产生以下结果:
ID NAME SALARY
---------- ---------- ----------
1 Paul 20000.0
2 Allen 15000.0
3 Teddy 20000.0
4 Mark 65000.0
5 David 85000.0
6 Kim 45000.0
7 James 10000.0
设置输出列的宽度
有时,由于要显示的列的默认宽度导致 .mode column,这种情况下,输出被截断。此时,您可以使用 .width num, num.... 命令设置显示列的宽度,如下所示:
sqlite>.width 10, 20, 10
sqlite>SELECT * FROM COMPANY;
上面的 .width 命令设置第一列的宽度为 10,第二列的宽度为 20,第三列的宽度为 10。因此上述 SELECT 语句将得到以下结果:
ID NAME AGE ADDRESS SALARY
---------- -------------------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0
Schema 信息
因为所有的点命令只在 SQLite 提示符中可用,所以当您进行带有 SQLite 的编程时,您要使用下面的带有 sqlite_master 表的 SELECT 语句来列出所有在数据库中创建的表:
sqlite> SELECT tbl_name FROM sqlite_master WHERE type = 'table';
假设在 testDB.db 中已经存在唯一的 COMPANY 表,则将产生以下结果:
tbl_name
----------
COMPANY
您可以列出关于 COMPANY 表的完整信息,如下所示:
sqlite> SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'COMPANY';
假设在 testDB.db 中已经存在唯一的 COMPANY 表,则将产生以下结果:
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
)
SQLite Update 语句
SQLite 的 UPDATE 查询用于修改表中已有的记录。可以使用带有 WHERE 子句的 UPDATE 查询来更新选定行,否则所有的行都会被更新。
语法
带有 WHERE 子句的 UPDATE 查询的基本语法如下:
UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];
您可以使用 AND 或 OR 运算符来结合 N 个数量的条件。
实例
假设 COMPANY 表有以下记录:
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0
下面是一个实例,它会更新 ID 为 6 的客户地址:
sqlite> UPDATE COMPANY SET ADDRESS = 'Texas' WHERE ID = 6;
现在,COMPANY 表有以下记录:
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 Texas 45000.0
7 James 24 Houston 10000.0
如果您想修改 COMPANY 表中 ADDRESS 和 SALARY 列的所有值,则不需要使用 WHERE 子句,UPDATE 查询如下:
sqlite> UPDATE COMPANY SET ADDRESS = 'Texas', SALARY = 20000.00;
现在,COMPANY 表有以下记录:
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 Texas 20000.0
2 Allen 25 Texas 20000.0
3 Teddy 23 Texas 20000.0
4 Mark 25 Texas 20000.0
5 David 27 Texas 20000.0
6 Kim 22 Texas 20000.0
7 James 24 Texas 20000.0
SQLite Delete 语句
SQLite 的 DELETE 查询用于删除表中已有的记录。可以使用带有 WHERE 子句的 DELETE 查询来删除选定行,否则所有的记录都会被删除。
语法
带有 WHERE 子句的 DELETE 查询的基本语法如下:
DELETE FROM table_name
WHERE [condition];
您可以使用 AND 或 OR 运算符来结合 N 个数量的条件。
实例
假设 COMPANY 表有以下记录:
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0
下面是一个实例,它会删除 ID 为 7 的客户:
sqlite> DELETE FROM COMPANY WHERE ID = 7;
现在,COMPANY 表有以下记录:
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
如果您想要从 COMPANY 表中删除所有记录,则不需要使用 WHERE 子句,DELETE 查询如下:
sqlite> DELETE FROM COMPANY;
现在,COMPANY 表中没有任何的记录,因为所有的记录已经通过 DELETE 语句删除。