SQLite入门介绍（二）

在之前的SQLite入门介绍（一）中我们介绍了SQLite的背景，命令和一些基本的增删改查的语法，现在就介绍SQLite其它常用的语句。

SQLite语句

创建表

creat table 表名(字段名称 数据类型 约束，字段名称 数据类型 约束……);

creat table person(
       _id int primary key, //primary key主键
       name varchar(10), 
       age Integer not null
);

可以使用 SQLite 命令中的 .tables 命令来验证表是否已成功创建，该命令用于列出附加数据库中的所有表。

可以使用 SQLite 命令中的.schema 命令得到表的完整信息

.schema person

删除表

drop table 表名;
drop table person;

插入数据

insert into 表名[字段，字段] values(值1，值2……);
insert into person(_id, age) values(1, 20);
insert into person values(2, 'lht', 30);

修改数据

update 表名 set 字段=新值 where 修改条件;
update person set name="ht", age=30 where _id=1;

删除数据

delete from 表名 where 删除的条件;
delete from person; //删除表中所有数据
delete from person where _id=2;

查询表

select 字段名 from 表名 where 查询条件 group by 分组的字段 having 筛选条件 order by 排序字段

select * from person; //查询表中所有数据
select _id, name from person;
select * from person where _id=1;
select * from person where _id<>1; //不等于
select * from person where _id=1 and age>18;
select * from person where name like "%小%"; 
select * from person where name like "_小%"; 
select * from person where name is null;
select * from person where age between 10 and 20;
select * from person where age>10 order by _id;

约束条件

NOT NULL

默认情况下，列可以保存 NULL 值。如果您不想某列有 NULL 值，那么需要在该列上定义此约束，指定在该列上不允许 NULL 值。

NULL 与没有数据是不一样的，它代表着未知的数据。

create table person(
       _id int primary key not null,
       name varchar(10), 
       age Integer not null
);
insert into person(_id, name) values(2, 'jack');

Error: NOT NULL constraint failed: person.age

UINQUE

UNIQUE 约束防止在一个特定的列存在两个记录具有相同的值。在 PERSON 表中，例如，您可能要防止两个或两个以上的人具有相同的名字。

create table person(
       _id int primary key not null,
       name varchar(10) UNIQUE, 
       age Integer not null
);

如果添加了数据库中已经存在的数据，就会出现下面提示。

Error: UNIQUE constraint failed: person.name

PRIMARY KEY

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

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

主键是表中的一个字段，唯一标识数据库表中的各行/记录。主键必须包含唯一值。主键列不能有 NULL 值。

一个表只能有一个主键，它可以由一个或多个字段组成。当多个字段作为主键，它们被称为复合键。
如果一个表在任何字段上定义了一个主键，那么在这些字段上不能有两个记录具有相同的值。

Error: UNIQUE constraint failed: person._id

CHECK

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

create table person(
       _id int primary key not null,
       name varchar(10), 
       age Integer not null CHECK(AGE >= 18)
);

我们为age添加了CHECK，于是凡是AGE小于18的数据都无法添加到数据库中。

Error: UNIQUE constraint failed: person.name

DEFAULT

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

create table person(
       _id int primary key not null,
       name varchar(10), 
       age Integer not null DEFAULT 18
);

运算符

算术运算符

在SQLite中的算术运算符与各种编程语言中的是一样的，同样是将运算符左右的数去做该运算。

+, -, *, /, %

比较运算符

== 与 =, != 与 <>, >, <, >=, <=, !>, !< 

在这里== 与 = 都是判断是否相等。

逻辑运算符

运算符	描述
AND	允许在一个 SQL 语句的 WHERE 子句中的多个条件的存在。
BETWEEN	用于在给定最小值和最大值范围内的一系列值中搜索值。
EXISTS	用于在满足一定条件的指定表中搜索行的存在。
IN	用于把某个值与一系列指定列表的值进行比较。
NOT IN	IN 运算符的对立面，用于把某个值与不在一系列指定列表的值进行比较。
LIKE	用于把某个值与使用通配符运算符的相似值进行比较。
GLOB	用于把某个值与使用通配符运算符的相似值进行比较。GLOB 与 LIKE 不同之处在于，它是大小写敏感的。
NOT	NOT 运算符是所用的逻辑运算符的对立面。比如 NOT EXISTS、NOT BETWEEN、NOT IN，等等。它是否定运算符。
OR	用于结合一个 SQL 语句的 WHERE 子句中的多个条件。
IS NULL	用于把某个值与 NULL 值进行比较。
IS	IS 运算符与 = 相似。
IS NOT	IS NOT 运算符与 != 相似。
‖	连接两个不同的字符串，得到一个新的字符串。
UNIQUE	搜索指定表中的每一行，确保唯一性（无重复）。

SELECT * FROM PERSON WHERE AGE >= 18 AND _ID >= 2;
SELECT * FROM PERSON WHERE AGE >= 18 OR _ID >= 2;
SELECT * FROM PERSON WHERE AGE IS NOT NULL;
SELECT * FROM PERSON WHERE NAME LIKE 'Ki%';
SELECT * FROM PERSON WHERE NAME GLOB 'Ki*';
SELECT * FROM PERSON WHERE AGE IN ( 25, 27 );
SELECT * FROM PERSON WHERE AGE NOT IN ( 25, 27 );
SELECT * FROM PERSON WHERE AGE BETWEEN 25 AND 27;
SELECT * FROM PERSON WHERE EXISTS (SELECT * FROM PERSON WHERE _ID = 1);

位运算符

假设变量 A=60，变量 B=13（A：00111100，B：00001101），则：

运算符	描述	实例
&	如果同时存在于两个操作数中，二进制 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

select 60 << 2;

SQLite子句

Like

SQLite 的 LIKE 运算符是用来匹配通配符指定模式的文本值。如果搜索表达式与模式表达式匹配，LIKE 运算符将返回真（true），也就是 1。这里有两个通配符与 LIKE 运算符一起使用：

• 百分号 （%）

• 下划线 （_）

百分号（%）代表零个、一个或多个数字或字符。下划线（_）代表一个单一的数字或字符。这些符号可以被组合使用。

可以使用 AND 或 OR 运算符来结合 N 个数量的条件。

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

SQLite 的 GLOB 运算符是用来匹配通配符指定模式的文本值。如果搜索表达式与模式表达式匹配，GLOB 运算符将返回真（true），也就是 1。与 LIKE 运算符不同的是，GLOB 是大小写敏感的，对于下面的通配符，它遵循 UNIX 的语法。

• 星号 （*）

• 问号 （?）

星号（*）代表零个、一个或多个数字或字符。问号（?）代表一个单一的数字或字符。这些符号可以被组合使用。

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

SQLite 的 LIMIT 子句用于限制由 SELECT 语句返回的数据数量。

SELECT column1, column2, columnN FROM table_name LIMIT [no of rows] OFFSET [row num];   //SQLite 引擎将返回从第一行偏移OFFSET开始，直到给定的 LIMIT 为止的所有行

select * from person limit 6 offset 3; //从第四条开始返回六条数据
select * from person limit 3, 6; //与上面效果一样 

Order By

SQLite 的 ORDER BY 子句是用来基于一个或多个列按升序或降序顺序排列数据。未设置默认升序。

SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1, column2, .. columnN] [ASC | DESC];

• ASC（升序）
• DESC（降序）

select * from person where age > 20 order by age, name asc; //优先对在前面的列排序

Group By

SQLite 的 GROUP BY 子句用于与 SELECT 语句一起使用，来对相同的数据进行分组。

GROUP BY 子句必须放在 WHERE 子句中的条件之后，必须放在 ORDER BY 子句之前。

SELECT column-list FROM table_name WHERE [ conditions ] GROUP BY column1, column2....columnN ORDER BY column1, column2....columnN;

select * from person group by age; //以年龄分组，相同年龄的返回最后一个该年龄的数据，还会按age升序。

Having

HAVING 子句允许指定条件来过滤将出现在最终结果中的分组结果。

WHERE 子句在所选列上设置条件，而 HAVING 子句则在由 GROUP BY 子句创建的分组上设置条件。

在一个查询中，HAVING 子句必须放在 GROUP BY 子句之后，必须放在 ORDER BY 子句之前。

SELECT column1, column2 FROM table1, table2 WHERE [ conditions ] GROUP BY column1, column2 HAVING [ conditions ] ORDER BY column1, column2;

SELECT * FROM PERSON GROUP BY AGE HAVING count(age) < 3; //返回表中相同age的数量小于3个的数据

Distinct

SQLite 的 DISTINCT 关键字与 SELECT 语句一起使用，来消除所有重复的记录，并只获取唯一一次记录。
有可能出现一种情况，在一个表中有多个重复的记录。当提取这样的记录时，DISTINCT 关键字就显得特别有意义，它只获取唯一一次记录，而不是获取重复记录。

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

select distinct name from person;

结束语：本文仅用来学习记录，参考查阅。