目录
数据类型
存储类型 | 描述 |
---|---|
NULL | 值是一个 NULL 值。 |
INTEGER | 值是一个带符号的整数,根据值的大小存储在 1、2、3、4、6 或 8 字节中。 |
REAL | 值是一个浮点值,存储为 8 字节的 IEEE 浮点数字。 |
TEXT | 值是一个文本字符串,使用数据库编码(UTF-8、UTF-16BE 或 UTF-16LE)存储。 |
BLOB | 值是一个 blob 数据,完全根据它的输入存储。 |
创建表
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
删除表
DROP TABLE COMPANY;
查询表是否存在
SELECT COUNT(*) FROM sqlite_master where type='table' and name='COMPANY';
插入
// 方式1
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );
// 方式2
INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 );
使用一个表来填充另一个表
INSERT INTO first_table_name [(column1, column2, ... columnN)]
SELECT column1, column2, ...columnN
FROM second_table_name
[WHERE condition];
查询
SELECT * FROM COMPANY;
SELECT ID, NAME, SALARY FROM COMPANY;
// Schema信息
SELECT tbl_name FROM sqlite_master WHERE type = 'table';
// 列出关于 COMPANY 表的完整信息
SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'COMPANY';
// 结果:
/*
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
)
*/
运算符
算术运算符
运算符 | 描述 | 实例 |
---|---|---|
+ | 加法 - 把运算符两边的值相加 | a + b 将得到 30 |
- | 减法 - 左操作数减去右操作数 | a - b 将得到 -10 |
* | 乘法 - 把运算符两边的值相乘 | a * b 将得到 200 |
/ | 除法 - 左操作数除以右操作数 | b / a 将得到 2 |
% | 取模 - 左操作数除以右操作数后得到的余数 | b % a will give 0 |
比较运算符
假设变量 a=10,变量 b=20,则:
运算符 | 描述 | 实例 |
---|---|---|
== | 检查两个操作数的值是否相等,如果相等则条件为真。 | (a == b) 不为真。 |
= | 检查两个操作数的值是否相等,如果相等则条件为真。 | (a = b) 不为真。 |
!= | 检查两个操作数的值是否相等,如果不相等则条件为真。 | (a != b) 为真。 |
<> | 检查两个操作数的值是否相等,如果不相等则条件为真。 | (a <> b) 为真。 |
> | 检查左操作数的值是否大于右操作数的值,如果是则条件为真。 | (a > b) 不为真。 |
< | 检查左操作数的值是否小于右操作数的值,如果是则条件为真。 | (a < b) 为真。 |
>= | 检查左操作数的值是否大于等于右操作数的值,如果是则条件为真。 | (a >= b) 不为真。 |
<= | 检查左操作数的值是否小于等于右操作数的值,如果是则条件为真。 | (a <= b) 为真。 |
!< | 检查左操作数的值是否不小于右操作数的值,如果是则条件为真。 | (a !< b) 为假。 |
!> | 检查左操作数的值是否不大于右操作数的值,如果是则条件为真。 | (a !> b) 为真。 |
SELECT * FROM COMPANY WHERE SALARY <> 20000;
逻辑运算符
运算符 | 描述 |
---|---|
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 运算符搜索指定表中的每一行,确保唯一性(无重复)。 |
SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;
SELECT * FROM COMPANY WHERE AGE IS NOT NULL;
// NAME 以 'Ki' 开始的所有记录,'Ki' 之后的字符不做限制:
SELECT * FROM COMPANY WHERE NAME LIKE 'Ki%';
// NAME 以 'Ki' 开始的所有记录,'Ki' 之后的字符不做限制(大小写敏感):
SELECT * FROM COMPANY WHERE NAME GLOB 'Ki*';
// AGE 的值为 25 或 27 的所有记录
SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );
// AGE 的值既不是 25 也不是 27 的所有记录:
SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 );
// AGE 的值在 25 与 27 之间的所有记录:
SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;
// 子查询查找 SALARY > 65000 的带有 AGE 字段的所有记录,后边的 WHERE 子句与 EXISTS 运算符一起使用,列出了外查询中的 AGE 存在于子查询返回的结果中的所有记录:
SELECT AGE FROM COMPANY
WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
//子查询查找 SALARY > 65000 的带有 AGE 字段的所有记录,后边的 WHERE 子句与 > 运算符一起使用,列出了外查询中的 AGE 大于子查询返回的结果中的年龄的所有记录:
SELECT * FROM COMPANY
WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
位运算符
下表中列出了 SQLite 语言支持的位运算符。假设变量 A=60,变量 B=13,则:
运算符 | 描述 | 实例 |
---|---|---|
& | 如果同时存在于两个操作数中,二进制 AND 运算符复制一位到结果中。 | (A & B) 将得到 12,即为 0000 1100 |
| | 如果存在于任一操作数中,二进制 OR 运算符复制一位到结果中。 | (A | B) 将得到 61,即为 0011 1101 |
~ | 二进制补码运算符是一元运算符,具有"翻转"位效应,即0变成1,1变成0。 | (~A ) 将得到 -61,即为 1100 0011,一个有符号二进制数的补码形式。 |
<< | 二进制左移运算符。左操作数的值向左移动右操作数指定的位数。 | A << 2 将得到 240,即为 1111 0000 |
>> | 二进制右移运算符。左操作数的值向右移动右操作数指定的位数。 | A >> 2 将得到 15,即为 0000 1111 |
sqlite> select 60 | 13;
60 | 13 = 61
sqlite> select 60 & 13;
60 & 13 = 12
sqlite> select (~60);
(~60) = -61
sqlite> select (60 << 2);
(60 << 2) = 240
sqlite> select (60 >> 2);
(60 >> 2) = 15
更新
//更新 ID 为 6 的客户地址
UPDATE COMPANY SET ADDRESS = 'Texas' WHERE ID = 6;
//修改 COMPANY 表中 ADDRESS 和 SALARY 列的所有值
UPDATE COMPANY SET ADDRESS = 'Texas', SALARY = 20000.00;
删除
DELETE FROM COMPANY WHERE ID = 7;
// 从 COMPANY 表中删除所有记录
DELETE FROM COMPANY;
Like
这里有两个通配符与 LIKE 运算符一起使用:
-
百分号 (%) 代表零个、一个或多个数字或字符
-
下划线 (_)代表一个单一的数字或字符
这些符号可以被组合使用。
// 查找以 200 开头的任意AGE
SELECT * FROM COMPANY WHERE AGE LIKE '200%';
// COMPANY 表中 ADDRESS 文本里包含一个连字符(-)的所有记录:
SELECT * FROM COMPANY WHERE ADDRESS LIKE '%-%';
下面一些实例演示了 带有 '%' 和 '_' 运算符的 LIKE 子句不同的地方:
语句 | 描述 |
---|---|
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 结尾的任意值 |
Glob
与LIKE 运算符不同的是,GLOB 是大小写敏感的,对于下面的通配符,它遵循 UNIX 的语法。
-
星号 (*)代表零个、一个或多个数字或字符
-
问号 (?)代表一个单一的数字或字符
这些符号可以被组合使用。
// 查找以 200 开头的任意值
SELECT * FROM COMPANY WHERE AGE GLOB '200*';
// COMPANY 表中 ADDRESS 文本里包含一个连字符(-)的所有记录:
SELECT * FROM COMPANY WHERE ADDRESS GLOB '*-*';
下面一些实例演示了 带有 '*' 和 '?' 运算符的 GLOB 子句不同的地方:
语句 | 描述 |
---|---|
WHERE SALARY GLOB '200*' | 查找以 200 开头的任意值 |
WHERE SALARY GLOB '*200*' | 查找任意位置包含 200 的任意值 |
WHERE SALARY GLOB '?00*' | 查找第二位和第三位为 00 的任意值 |
WHERE SALARY GLOB '2??' | 查找以 2 开头,且长度至少为 3 个字符的任意值 |
WHERE SALARY GLOB '*2' | 查找以 2 结尾的任意值 |
WHERE SALARY GLOB '?2*3' | 查找第二位为 2,且以 3 结尾的任意值 |
WHERE SALARY GLOB '2???3' | 查找长度为 5 位数,且以 2 开头以 3 结尾的任意值 |
Limit
LIMIT 子句用于限制由 SELECT 语句返回的数据数量。
// 它限制了您想要从表中提取的行数:
SELECT * FROM COMPANY LIMIT 6;
// 在某些情况下,可能需要从一个特定的偏移开始提取记录。下面是一个实例,从第三位开始提取 3 个记录:
SELECT * FROM COMPANY LIMIT 3 OFFSET 2;
Order By
// 将结果按 NAME 和 SALARY 升序排序:
SELECT * FROM COMPANY ORDER BY NAME, SALARY ASC;
// 将结果按 NAME 降序排序:
SELECT * FROM COMPANY ORDER BY NAME DESC;
Group By
SQLite 的 GROUP BY 子句用于与 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
8 Paul 24 Houston 20000.0
9 James 44 Norway 5000.0
10 James 45 Texas 5000.0
// 如果您想了解每个客户的工资总额,则可使用 GROUP BY 查询
SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME DESC;
NAME SUM(SALARY)
---------- -----------
Teddy 20000
Paul 40000
Mark 65000
Kim 45000
James 20000
David 85000
Allen 15000
Having
HAVING 子句允许指定条件来过滤将出现在最终结果中的分组结果。
WHERE 子句在所选列上设置条件,而 HAVING 子句则在由 GROUP BY 子句创建的分组上设置条件。
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY在一个查询中,HAVING 子句必须放在 GROUP BY 子句之后,必须放在 ORDER BY 子句之前
// 显示名称计数小于 2 的所有记录:
SELECT * FROM COMPANY GROUP BY name HAVING count(name) < 2;
Distinct
SQLite 的 DISTINCT 关键字与 SELECT 语句一起使用,来消除所有重复的记录,并只获取唯一一次记录。
SELECT DISTINCT name FROM COMPANY;