CREATE TABLE
CREATE TABLE default_table
(id INTEGER PRIMARY KEY AUTOINCREMENT,value VARCHAR
);
CREATE TEMP TABLE
或者CREATE TEMPORARY TABLE
CREATE TEMP TABLE temp_table
(id INTEGER PRIMARY KEY AUTOINCREMENT,value VARCHAR
);
IF NOT EXISTS
IF NOT EXISTS
时:报错[SQLITE_ERROR] SQL error or missing database (table default_table already exists)
IF NOT EXISTS
时:不报错,也不创建表。schema-name
schema-name
表示要创建的表所属的数据库。只能填main, temp和附加数据库。其中main对应正式表;temp对应临时表;附加数据库对应在某一个附加数据库中创建的表。
要把数据库attached_to_sqlite_study.db作为当前数据库的附加库,只需要执行下述命令即可。如果附加库已存在,则直接关联上,否则先创建一个数据库,再做关联。
-- 把数据库attached_to_sqlite_study.db作为当前数据库的附加库,并取别名(schema-name)为attached
ATTACH DATABASE 'attached_to_sqlite_study.db' AS 'attached';
select-stmt
CREATE TABLE IF NOT EXISTS select_table AS SELECT * FROM default_table WHERE id < 5;
除了CREATE TABLE … AS SELECT类操作外,创建表必须包含列信息。创建没有列信息的数据表CREATE TABLE no_column_table();
会报错。 另外部分约束是针对多列或者整个表而言(table-constraint),可以集中写在列信息后面。表约束也可以用于约束某一列。约束的检查出现在增(INSERT)和改(UPDATE和部分ALERT)时,删和查的时候一般不会检查约束的限制。
PRAGMA ignore_check_constraints=ON
暂时忽略。SQLite中包含5个主要存储类型NULL,INTEGER,REAL,TEXT和BLOB;还有5个主要声明类型:NUMERIC,INTEGER,,REAL,TEXT和BLOB。
为了兼容其他SQL数据库,SQLite还有些其他类型,但是实际上都是上面这五个类型。比如:BOOLEAN:其实是INTEGER类型,1代表true;0代表false。
DEFAULT语句为一列提供了默认值,如果插入一行数据时没有填该列,则该列被DEFAULT语句对应的值填充,否则填充NULL。默认值本身也可以是NILL。DEFAULT后面可以接表达式、具体值和时间。
排序语句用于关键字的排序依据。可以配置为BINARY(默认)、NOCASE和RTRIM。
GENERATED ALWAYS AS
用于修饰生成的列,可以简写为AS。生成的列的值,由同一行其他列的值计算而得到。生成的列可以是虚拟列VIRTUAL(默认)或者是存储列STORED。生成列可以包含其他生成列,但是不能直接或间接地包括自身。
下面的命令创建了一个名为generate_table的表。注意:生成的列不需要都在末尾,可以在表的任意位置。
CREATE TABLE IF NOT EXISTS generate_table(id INTEGER PRIMARY KEY AUTOINCREMENT,rand INTEGER DEFAULT (random()%10),-- 生成虚拟列generated_virtual INTEGER GENERATED ALWAYS AS ( id + rand ) VIRTUAL,-- 生成存储列generated_stored INTEGER GENERATED ALWAYS AS ( id + rand ) STORED,-- 生成虚拟列generated_default INTEGER GENERATED ALWAYS AS ( generated_virtual + generated_stored )
);
PRIMARY KEY代表表的主键,一张表只能有一个主键。主键的性质如下:
UNIQUE和PRIMARY KEY的含义相似,即不能相同。两者通常都是通过创建唯一索引(CREATE UNIQUE INDEX)达到去重的目的。仅无ROWID表上的INTEGER PRIMARY KEY和PRIMARY KEYs例外。INTEGER PRIMARY KEY是RowId的拷贝,而PRIMARY KEYs由多个列构成。
Check约束用于在插入和更新数据时检查数据是否符合要求。Check约束的表达式结果都会被转换为NUMERIC类型,转换方法和Cast命令用的相同。如果是0(包括整型0和浮点类型0.0)则发生约束冲突,即不符合要求;如果是其他值(包括NULL),则代表符合要求。Check约束既可以约束当前列,也可以约束其他列,也可以约束列之间的关系等。
下面的命令创建了一个名为check_table的表。其中列rand10被rand10 > 0和rand100 > rand10两个Check约束限制;而列rand100被约束NULL限制。由于返回值为NULL代表符合约束条件,所以相当于列rand100没有被约束
CREATE TABLE IF NOT EXISTS check_table(id INTEGER PRIMARY KEY AUTOINCREMENT,rand10 INTEGER CHECK ( rand10 > 0 ) CHECK ( rand100 > rand10 ) DEFAULT (random()%10),rand100 INTEGER CHECK ( NULL ) DEFAULT (random()%100)
);
多次插入默认行后,出现了多个报错,它们分为两种,如下所示
[SQLITE_CONSTRAINT_CHECK] A CHECK constraint failed (CHECK constraint failed: rand10 > 0)
[SQLITE_CONSTRAINT_CHECK] A CHECK constraint failed (CHECK constraint failed: rand10 > 0)
NOT NULL约束限制了一列的值不能是NULL。和Check约束一样,NOT NULL约束在插入和更新数据时检测,因此如果出现数据库损坏等问题,查出的数据也有可能是NULL。
PRIMARY KEY, NOT NULL, UNIQUE等语句给数据提出了要求,conflict-clause用于指定数据和要求冲突时的处理方式。可配置的处理方式有ABORT(默认)、ROLLBACK、FAIL、IGNORE和REPLACE。当遇到适用的冲突时,它们有不同的处理方式,如下。
ROWID是默认存在的一列,类型时有符号64位整型。从3.8.2 (2013-12-06)版本开始,SQLite支持了没有ROWID的表
SQLite 的 DROP TABLE 语句用来删除表定义及其所有相关数据、索引、触发器、约束和该表的权限规范。
DROP TABLE 语句的基本语法如下。您可以选择指定带有表名的数据库名称,如下所示:
DROP TABLE database_name.table_name;
SQLite 的 INSERT INTO 语句用于向数据库的某个表中添加新的数据行。
INSERT INTO 语句有两种基本语法,如下所示:
INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)]
VALUES (value1, value2, value3,...valueN);
使用一个表来填充另一个表
您可以通过在一个有一组字段的表上使用 select 语句,填充数据到另一个表中。下面是语法:
INSERT INTO first_table_name [(column1, column2, ... columnN)] SELECT column1, column2, ...columnN FROM second_table_name[WHERE condition];
SQLite 的 SELECT 语句用于从 SQLite 数据库表中获取数据,以结果表的形式返回数据。这些结果表也被称为结果集。
SQLite 的 SELECT 语句的基本语法如下:
SELECT column1, column2, columnN FROM table_name;
在这里,column1, column2…是表的字段,他们的值即是您要获取的。如果您想获取所有可用的字段,那么可以使用下面的语法:
SELECT * FROM table_name;
设置输出列的宽度
有时,由于要显示的列的默认宽度导致 .mode column,这种情况下,输出被截断。此时,您可以使用 .width num, num… 命令设置显示列的宽度,如下所示:
sqlite>.width 10, 20, 10
sqlite>SELECT * FROM COMPANY;
SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] AND [condition2]...AND [conditionN];SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] OR [condition2]...OR [conditionN]
SQLite 的 UPDATE 查询用于修改表中已有的记录。可以使用带有 WHERE 子句的 UPDATE 查询来更新选定行,否则所有的行都会被更新。
带有 WHERE 子句的 UPDATE 查询的基本语法如下:
UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];
SQLite 的 DELETE 查询用于删除表中已有的记录。可以使用带有 WHERE 子句的 DELETE 查询来删除选定行,否则所有的记录都会被删除。
带有 WHERE 子句的 DELETE 查询的基本语法如下:
DELETE FROM table_name
WHERE [condition];
SQLite 的 LIMIT 子句用于限制由 SELECT 语句返回的数据数量。
带有 LIMIT 子句的 SELECT 语句的基本语法如下:
SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows]
SQLite 的 ORDER BY 子句是用来基于一个或多个列按升序或降序顺序排列数据。
ORDER BY 子句的基本语法如下:
SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];
SQLite 的 GROUP BY 子句用于与 SELECT 语句一起使用,来对相同的数据进行分组。
在 SELECT 语句中,GROUP BY 子句放在 WHERE 子句之后,放在 ORDER BY 子句之前。
下面给出了 GROUP BY 子句的基本语法。GROUP BY 子句必须放在 WHERE 子句中的条件之后,必须放在 ORDER BY 子句之前。
SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN
HAVING 子句允许指定条件来过滤将出现在最终结果中的分组结果。
WHERE 子句在所选列上设置条件,而 HAVING 子句则在由 GROUP BY 子句创建的分组上设置条件。
下面是 HAVING 子句在 SELECT 查询中的位置:
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
在一个查询中,HAVING 子句必须放在 GROUP BY 子句之后,必须放在 ORDER BY 子句之前。下面是包含 HAVING 子句的 SELECT 语句的语法:
SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2
SQLite 的 DISTINCT 关键字与 SELECT 语句一起使用,来消除所有重复的记录,并只获取唯一一次记录。
有可能出现一种情况,在一个表中有多个重复的记录。当提取这样的记录时,DISTINCT 关键字就显得特别有意义,它只获取唯一一次记录,而不是获取重复记录。
用于消除重复记录的 DISTINCT 关键字的基本语法如下:
SELECT DISTINCT column1, column2,.....columnN
FROM table_name
WHERE [condition]