Sqlite3数据库

安装
sudo apt-get install sqlite sqlite3
Sudo apt-get install libsqlite3-dev

sudo apt-get install sqlitebrowser

sqlitebrowser test.db

数据类型
NULL : 表示该值为NULL值。
INTEGER : 无符号整型值。
REAL : 浮点值。
TEXT : 文本字符串，存储使用的编码方式为UTF-8、UTF-16BE、UTF-16LE。

BLOB : 存储Blob数据，该类型数据和输入数据完全相同，1表示true，0表示false。

sqlite3也接受如下的数据类型：
smallint 16位的整数。
interger 32位的整数。
decimal(p,s) 精确值p是指全部有几个十进制数,s是指小数点后可以有几位小数。如果没有特别指定，则系统会默认为p=5 s=0 。
float 32位元的实数。
double 64位元的实数。
char(n) n 长度的字串，n不能超过 254。
varchar(n) 长度不固定且其最大长度为 n 的字串，n不能超过 4000。
graphic(n) 和 char(n) 一样，不过其单位是两个字节， n不能超过127。这个形态是为了支持两个字节长度的字体，如中文字。
vargraphic(n) 可变长度且其最大长度为n的双字元字串，n不能超过2000
date 包含了 年份、月份、日期。
time 包含了 小时、分钟、秒。
timestamp 包含了 年、月、日、时、分、秒、千分之一秒。
约束
SQLite 数据库常用约束如下：
NOT NULL - 非空
UNIQUE - 唯一
PRIMARY KEY - 主键
FOREIGN KEY - 外键
CHECK - 条件检查

DEFAULT - 默认

主键
一般是整数或者字符串，只要保证唯一就行。 在 SQLite 中，主键如果是整数类型，该列的值可以自动增长。
默认值 DEFAULT
有一些特别的字段列，在每一条记录中，他的值基本上都是一样的。只是在个别情况下才改为别的值，这样的字段列我们可以给他设一个
默认值。
非空 NOT NULL
有一些字段我们可能一时不知到该填些什么，同时它也没设定默认值， 当添加数据时，我们把这样的字段空着不填，系统认为他是
NULL 值。但是还有另外一类字段，必须被填上数据，如果不填，系统就会报错。 这样的字段被称为 NOT NULL 非空字段,需要在定义表
的时候事先声明。
唯一
除了主列以为，还有一些列也不能有重复值
条件检查 CHECK
某些值必须符合一定的条件才允许存入，这是就需要用到这个 CHECK 约束。

CREATE TABLE Teachers(Id integer PRIMARY KEY,Age integer CHECK(Age>22));

外键 FOREIGN KEY
我们的数据库中已经有 Teachers 表了，假如我们再建立一个 Students 表， 要求 Students 表中的每一个学生都对应一个 Teachers 表中的
教师。
很简单，只需要在 Students 表中建立一个 TeacherId 字段，保存对应教师的 Id 号， 这样，学生和教师之间就建立了关系。
问题是：我们有可能给学生存入一个不在 Teachers 表中的 TeacherId 值， 而且发现不了这个错误。
这种情况下，可以把 Students 表中 TeacherId 字段声明为一个外键， 让它的值对应到 Teachers 表中的 Id 字段上。

这样，一旦在 Students 表中存入一个不存在的教师 Id ，系统就会报错。

常用命令
下面开始学习Shell模式中的各常用命令的使用，具体命令的练习如下所示。通过练习掌握每个命令的功能。
sqlite>.help --可显示shell模式中可使用的所有命令列表
.database --显示数据库信息；包含当前数据库的位置
sqlite>.mode column --使得SQL语句处理的结果以列对齐的方式显示
sqlite> .mode list|column|insert|line|tabs|tcl|csv其他输出格式
sqlite>.headers on/off - -打开关闭列标题显示开关，以使得查询结果在屏幕显示时
具有列标题
sqlite>.tables --列出当前打开的数据库中共有多少张表
sqlite>Select * from foods; --查询foods表中的记录数据
sqlite>.exit --退出SQLite环境
F:\sqlite3>Sqlite3 foods.db --再次从DOS环境进入SQLite环境，
同时打开数据库文件foods.db
sqlite>.schema foods --显示表foods 创建时的SQL语句
sqlite>.schema --显示所有表被创建时的语句
sqlite>.nullvalue STRING 查询时用指定的串代替输出的NULL串 默认为.nullvalue ''
数据库的备份和恢复
sqlite>.dump --把创建表及向表中插入记录的所有SQL语句显示在屏幕上
sqlite>.output foodsdb.sql --指定dump命令输出到文件的文件名
sqlite>.dump --输出创建并插入数据到基本表的SQL语句到output指定的文件
sqlite>.output stdout --恢复输出内容到标准输出设备(屏幕)
sqlite>.dump --此时输出的SQL语句转回到屏幕
sqlite>Drop table foods; --删除foods表语句
说明：上述Drop为SQL语句，用于删除指定的表。因为是SQL语句，因此以“;”结尾
sqlite>.read foodsdb.sql --执行foodsdb.sql中的包含的所有SQL语句，用来重建刚删除的4
张表及相关数据
sqlite>.show --显示shell模式中定义的与输出相关的一些设置
sqlite>.output file.csv --设置输出文件格式为CSV，文件名为file.csv
sqlite>.separator , --设置select语句输出的列数据间以“ ，”分隔
sqlite>Select * from foods; --查询显示foods表中所有的数据
sqlite>.output stdout --恢复输出内容到标准输出设备(屏幕)
注意：下面的4个命令与上述的4个命令效果相同
sqlite>.output file.csv
sqlite>.mode csv
sqlite>Select * from foods;

sqlite>.output stdout

其次，了解在DOS环境中使用SQLite的相关命令
F:\sqlite3>Sqlite3 foods.db .dump --在显示器上显示创建foods数据库中各表的语句
及数据插入语句
F:\sqlite3>Sqlite3 foods.db .dump>fooddb.sql --将创建foods数据库中各表的语句
及数据插入语句输出到文件fooddb.sql中保存
F:\sqlite3>Sqlite3 foods.db "select * from food_types" --查询foods数据库中food_types表中内容，并在dos 环境中显示查询结果
F:\sqlite3>Sqlite3 foods2.db <foodsdb.sql
--用foodsdb.sql中的语句创建数据库foods2.db
F:\sqlite3>Sqlite3 –init foodsdb.sql foods3.db .exit

--用foodsdb.sql中的语句创建数据库foods3.db

SQL 使用实例
(1) 创建教学管理“JXGL”数据库。
在DOS命令窗口中，使用如下命令。

F:\sqlite3>Sqlite3 JXGL.db

(2) 显示命令提示符sqlite3>后，输入如下SQL语句：
CREATE TABLE IF NOT EXISTS STUDENT( Sno integer primary key, Sname text not null, Ssex text default ‘男’,Sage integer default
19,Sdept text default ‘CS’）;
结构创建完成后，用下述语句插入数据：
INSERT INTO STUDENT VALUES(‘95001’,’李勇’,’M’,20,’CS’);
INSERT INTO STUDENT VALUES(‘95002’,’刘晨’,’F’,19,’IS’);
INSERT INTO STUDENT VALUES(‘95003’,’王敏’,’F’,18,’MA’);
INSERT INTO STUDENT VALUES(‘95004’,’张立’,’M’,18,’IS’);
插入的数据只初始化部分值
insert into student(sname,sage) values ('pengd',19);
删除一行信息
delete from student where sname='zhangli';
修改一条记录的某个内容

UPDATE student SET sage=19 WHERE sname='lilei';

(3) 用SELECT语句查看三个表中的内容。
SELECT * FROM STUDENT;
Select * from student where sname=’lilei’;
Select * from student where sno=20;
select spwd from student where sname='lilei';
sqlite> select *from stu where sname='lilei' and spwd='123456';
select sno,sname from stu; 显示某两列的数据
select * from student order by age;

insert into stu values(‘1342051222','fuhuan','m',21,'math');

(4) 修改数据表结构。
向STUDENT表增加“入学时间”列，其数据类型为TEXT型，用SELECT命令查看表中内容。
ALTER TABLE STUDENT ADD spwd TEXT default ‘123456’;
SELECT * FROM STUDENT;

update student set spwd='123456' where sname='lilei';

(5) 删除数据表。

DROP TABLE STUDENT;

（6）修改表名字
alter table student rename to stu
删除一列
sqlite> create table stu as select no, name, score from student
sqlite> drop table student

sqlite> alter table stu rename to student

（7）创建时间表
CREATE TABLE times ( id int,
date NOT NULL DEFAULT CURRENT_DATE,
time NOT NULL DEFAULT CURRENT_TIME,
timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP );
insert into times (id) values (1);
INSERT INTO times(1);
INSERT INTO times(2);

SELECT * FROMS times;

(8) group by用法：
CREATE TABLE employee(id integer primary key, name text, dep text, salary integer);
1 |XiaoLi |market |6000
2 |XiaoLi |tech |7000
3 |XiaoMing |trs |8600
4 |XiaoZhang |trs |6000
5 |XiaoSong |tech |8900
6 |LaoZheng |market |4000
insert into employee values (1,'pengd','market',6000);
insert into employee values (2,'pengd1','tech',7000);
insert into employee values (3,'pengd2','trs',8000);
insert into employee values (4,'pengd3','trs',6000);
insert into employee values (5,'pengd4','tech',8900);
insert into employee values (6,'pengd5','market',4000);
统计整个公司工资总和：
select sum(salary) from employee;
统计每个部门的工资总和：
select dep, sum(salary) from employee group by dep;
select dep, sum(salary) from employee where id>3 group by dep; where子句要放在group by的前面。
select dep, sum(salary) from employee where id>3 group by dep having sum(salary)>5000; having子句是group by
的条件子句，where子句先发生，然后才是having 子句执行。
select id from employee group by id,name,dep,salary having count(*)>1;
查找重复记录的方法。
select id,name from employeenew group by name,dep,salary having count(*) > 1;
显示名字相同的多项
select id,name,dep,salary from employeenew group by name,dep,salary having count(*) = 1;
显示table中所有的记录
select count(*) from employeenew;
显示所有记录的个数
select dep,avg(salary) from employeenew group by dep;
显示dep下每一组的平均值
select * from studentnew where id > 3 intersect select * from studentnew where id < 9;
显示id > 3 && id < 9 的所有记录：即4 - 8 的记录
select * from studentnew where id > 3 union all select * from studentnew where id < 9;
显示所有的大于3并且小于9的，并集（如果有相同的，会重复显示）
select * from studentnew where id > 3 union all select * from studentnew where id < 9;
显示大于9的记录
select * from studentnew where id > 3 union all select * from studentnew where id < 6;
显示大于6的记录，（与上一个进行比较）
select *from student where score = (select score from student order by score desc limit 1);
显示最高分的所有学生的记录
select distinct name from fruit
显示table中名字不相同的水果;
select all name from fruit

显示所有水果的名字;

函数
SQLite 有许多内置函数用于处理字符串或数字数据。下面列出了一些有用的 SQLite 内置函数，且所有函数都是大小写不敏感，这意味着您可以使用这些函数的
小写形式或大写形式或混合形式。欲了解更多详情，请查看 SQLite 的官方文档：
序号函数 & 描述
1 SQLite COUNT 函数
SQLite COUNT 聚集函数是用来计算一个数据库表中的行数。
2 SQLite MAX 函数
SQLite MAX 聚合函数允许我们选择某列的最大值。
3 SQLite MIN 函数
SQLite MIN 聚合函数允许我们选择某列的最小值。
4 SQLite AVG 函数
SQLite AVG 聚合函数计算某列的平均值。
5 SQLite SUM 函数
SQLite SUM 聚合函数允许为一个数值列计算总和。
6 SQLite RANDOM 函数
SQLite RANDOM 函数返回一个介于 -9223372036854775808 和 +9223372036854775807 之间的伪随机整数。
7 SQLite ABS 函数
SQLite ABS 函数返回数值参数的绝对值。
8 SQLite UPPER 函数
SQLite UPPER 函数把字符串转换为大写字母。
9 SQLite LOWER 函数
SQLite LOWER 函数把字符串转换为小写字母。
10 SQLite LENGTH 函数
SQLite LENGTH 函数返回字符串的长度。
11 SQLite sqlite_version 函数

SQLite sqlite_version 函数返回 SQLite 库的版本。

在我们开始讲解这些函数实例之前，先假设 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
SQLite COUNT 刃敶
SQLite COUNT 聚集函数是用来计算一个数据库表中的行数。下面是实例：
sqlite> SELECT count(*) FROM COMPANY;
上面的 SQLite SQL 语句将产生以下结果：
count(*)
----------
7
SQLite MAX 刃敶
SQLite MAX 聚合函数允许我们选择某列的最大值。下面是实例：
sqlite> SELECT max(salary) FROM COMPANY;
上面的 SQLite SQL 语句将产生以下结果：
max(salary)
-----------
85000.0
SQLite MIN 刃敶
SQLite MIN 聚合函数允许我们选择某列的最小值。下面是实例：
sqlite> SELECT min(salary) FROM COMPANY;
上面的 SQLite SQL 语句将产生以下结果：
min(salary)
-----------
10000.0
SQLite AVG 刃敶
SQLite AVG 聚合函数计算某列的平均值。下面是实例：
sqlite> SELECT avg(salary) FROM COMPANY;
上面的 SQLite SQL 语句将产生以下结果：
avg(salary)
----------------
37142.8571428572
SQLite SUM 刃敶
SQLite SUM 聚合函数允许为一个数值列计算总和。下面是实例：
sqlite> SELECT sum(salary) FROM COMPANY;
上面的 SQLite SQL 语句将产生以下结果：
sum(salary)
-----------
260000.0
SQLite RANDOM 刃敶
SQLite RANDOM 函数返回一个介于 -9223372036854775808 和 +9223372036854775807 之间的伪随机整数。下面是实例：
sqlite> SELECT random() AS Random;
上面的 SQLite SQL 语句将产生以下结果：
Random
-------------------
5876796417670984050
SQLite ABS 刃敶
SQLite ABS 函数返回数值参数的绝对值。下面是实例：
sqlite> SELECT abs(5), abs(-15), abs(NULL), abs(0), abs("ABC");
上面的 SQLite SQL 语句将产生以下结果：
abs(5) abs(-15) abs(NULL) abs(0) abs("ABC")
---------- ---------- ---------- ---------- ----------
5 15 0 0.0
SQLite UPPER 刃敶
SQLite UPPER 函数把字符串转换为大写字母。下面是实例：
sqlite> SELECT upper(name) FROM COMPANY;
上面的 SQLite SQL 语句将产生以下结果：
upper(name)
-----------
PAUL
ALLEN
TEDDY
MARK
DAVID
KIM
JAMES
SQLite LOWER 刃敶
SQLite LOWER 函数把字符串转换为小写字母。下面是实例：
sqlite> SELECT lower(name) FROM COMPANY;
上面的 SQLite SQL 语句将产生以下结果：
lower(name)
-----------
paul
allen
teddy
mark
david
kim
james
SQLite LENGTH 刃敶
SQLite LENGTH 函数返回字符串的长度。下面是实例：
sqlite> SELECT name, length(name) FROM COMPANY;
上面的 SQLite SQL 语句将产生以下结果：
NAME length(name)
---------- ------------
Paul 4
Allen 5
Teddy 5
Mark 4
David 5
Kim 3
James 5
SQLite sqlite_version 刃敶
SQLite sqlite_version 函数返回 SQLite 库的版本。下面是实例：
sqlite> SELECT sqlite_version() AS 'SQLite Version';
上面的 SQLite SQL 语句将产生以下结果：
SQLite Version
--------------
3.6.20
SQLite旼闺/旫朥刃敶ⅹ
datetime() 产生日期和时间 无参数表示获得当前时间和日期
sqlite> select datetime();
2012-01-07 12:01:32
有字符串参数则把字符串转换成日期
sqlite> select datetime('2012-01-07 12:01:30');
2012-01-07 12:01:30
select date('2012-01-08','+1 day','+1 year');
2013-01-09
select datetime('2012-01-08 00:20:00','+1 hour','-12 minute');
2012-01-08 01:08:00
select datetime('now','start of year');
2012-01-01 00:00:00
select datetime('now','start of month');
2012-01-01 00:00:00
select datetime('now','start of day');
2012-01-08 00:00:00
select datetime('now','start of week');错误
select datetime('now','localtime');
结果：2006-10-17 21:21:47
date()产生日期
sqlite> select date('2012-01-07 12:01:30');
2012-01-07
同理 有参和无参
select date('now','start of year');
2012-01-01
select date('2012-01-08','+1 month');
2012-02-08
time() 产生时间
select time();
03:14:30
select time('23:18:59');
23:18:59
select time('23:18:59','start of day');
00:00:00
select time('23:18:59','end of day');错误
在时间/日期函数里可以使用如下格式的字符串作为参数：
YYYY-MM-DD
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSS
HH:MM
HH:MM:SS
HH:MM:SS.SSS
now
其中now是产生现在的时间。
日期不能正确比较大小,会按字符串比较，日期默认格式 dd-mm-yyyy
select hiredate from emp order by hiredate;
17-11-1981
17-12-1980
19-04-1987
20-02-1981
22-02-1981
strftime() 对以上三个函数产生的日期和时间进行格式化
strftime()函数可以把YYYY-MM-DD HH:MM:SS格式的日期字符串转换成其它形式的字符串。 strftime(格式, 日期/时间, 修正符, 修正符, …)
select strftime('%d',datetime());
它可以用以下的符号对日期和时间进行格式化：
%d 在该月中的第几天, 01-31
%f 小数形式的秒，SS.SSS
%H 小时, 00-23
%j 算出某一天是该年的第几天，001-366
%m 月份，00-12
%M 分钟, 00-59
%s 从1970年1月1日到现在的秒数
%S 秒, 00-59
%w 星期, 0-6 (0是星期天)
%W 算出某一天属于该年的第几周, 01-53
%Y 年, YYYY
%% 百分号
select strftime('%Y.%m.%d %H:%M:%S','now');
select strftime('%Y.%m.%d %H:%M:%S','now','localtime');
结果：2006.10.17 21:41:09
select hiredate from emp
order by strftime('%Y.%m.%d %H:%M:%S',hiredate); 正确
select strftime('%Y.%m.%d %H:%M:%S',hiredate) from emp
order by strftime('%Y.%m.%d %H:%M:%S',hiredate); 错误
SQLite Where 子句
SQLite的 WHERE 子句用于指定从一个表或多个表中获取数据的条件。
如果满足给定的条件，即为真（true）时，则从表中返回特定的值。您可以使用 WHERE 子句来过滤记录，只获取需要的记录。
WHERE 子句不仅可用在 SELECT 语句中，它也可用在 UPDATE、DELETE 语句中，等等，这些我们将在随后的章节中学习到。
语法
SQLite 的带有 WHERE 子句的 SELECT 语句的基本语法如下：
SELECT column1, column2, columnN FROM table_name WHERE [condition]
实例
您还可以使用比较或逻辑运算符指定条件，比如 >、<、=、LIKE、NOT，等等。假设 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
下面的实例演示了 SQLite 逻辑运算符的用法。下面的 SELECT 语句列出了 AGE 大于等于 25 且工资大于等于 65000.00 的所有记录：
sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
下面的 SELECT 语句列出了 AGE 大于等于 25 或工资大于等于 65000.00 的所有记录：
sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
下面的 SELECT 语句列出了 AGE 不为 NULL 的所有记录，结果显示所有的记录，意味着没有一个记录的 AGE 等于 NULL：
sqlite> SELECT * FROM COMPANY WHERE AGE IS NOT NULL;
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 语句列出了 NAME 以 'Ki' 开始的所有记录，'Ki' 之后的字符不做限制：
sqlite> SELECT * FROM COMPANY WHERE NAME LIKE 'Ki%';
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
6 Kim 22 South-Hall 45000.0
下面的 SELECT 语句列出了 NAME 以 'Ki' 开始的所有记录，'Ki' 之后的字符不做限制：
sqlite> SELECT * FROM COMPANY WHERE NAME GLOB 'Ki*';
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
6 Kim 22 South-Hall 45000.0
下面的 SELECT 语句列出了 AGE 的值为 25 或 27 的所有记录：
sqlite> SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
2 Allen 25 Texas 15000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
下面的 SELECT 语句列出了 AGE 的值既不是 25 也不是 27 的所有记录：
sqlite> SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 );
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
3 Teddy 23 Norway 20000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0
下面的 SELECT 语句列出了 AGE 的值在 25 与 27 之间的所有记录：
sqlite> SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
2 Allen 25 Texas 15000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
下面的 SELECT 语句使用 SQL 子查询，子查询查找 SALARY > 65000 的带有 AGE 字段的所有记录，后边的 WHERE 子句与 EXISTS 运算
符一起使用，列出了外查询中的 AGE 存在于子查询返回的结果中的所有记录：
sqlite> SELECT AGE FROM COMPANY
WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
AGE
----------
32
25
23
25
27
22
24
下面的 SELECT 语句使用 SQL 子查询，子查询查找 SALARY > 65000 的带有 AGE 字段的所有记录，后边的 WHERE 子句与 > 运算符一起
使用，列出了外查询中的 AGE 大于子查询返回的结果中的年龄的所有记录：
sqlite> SELECT * FROM COMPANY
WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
SELECT LIKE like用法
在SQL结构化查询语言中，LIKE语句有着至关重要的作用。
LIKE语句的语法格式是：select * from 表名 where 字段名 like 对应值（子串），它主要是针对字符型字段的，它的作用是在一个字
符型字段列中检索包含对应子串的。
A:% 包含零个或多个字符的任意字符串：
1、LIKE'Mc%' 将搜索以字母 Mc 开头的所有字符串（如 McBadden）。
2、LIKE'%inger' 将搜索以字母 inger 结尾的所有字符串（如 Ringer、Stringer）。
3、LIKE'%en%' 将搜索在任何位置包含字母 en 的所有字符串（如 Bennet、Green、McBadden）。
B:_（下划线） 任何单个字符：LIKE'_heryl' 将搜索以字母 heryl 结尾的所有六个字母的名称（如 Cheryl、Sheryl）。
C：[ ] 指定范围 ([a-f]) 或集合 ([abcdef]) 中的任何单个字符： 1，LIKE'[CK]ars[eo]n' 将搜索下列字符串：Carsen、
Karsen、Carson 和 Karson（如 Carson）。
2、LIKE'[M-Z]inger' 将搜索以字符串 inger 结尾、以从 M 到 Z 的任何单个字母开头的所有名称（如 Ringer）。
D：[^] 不属于指定范围 ([a-f]) 或集合 ([abcdef]) 的任何单个字符：LIKE'M[^c]%' 将搜索以字母 M 开头，并且第二个字母不
是 c 的所有名称（如MacFeather）。
E：* 它同于DOS命令中的通配符，代表多个字符：c*c代表cc,cBc,cbc,cabdfec等多个字符。
F：？同于DOS命令中的？通配符，代表单个字符 :b?b代表brb,bFb等
G：# 大致同上，不同的是代只能代表单个数字。k#k代表k1k,k8k,k0k 。

F：[!] 排除 它只代表单个字符

下面我们来举例说明一下：
例1，查询name字段中包含有“明”字的。
select * from table1 where name like '%明%'
例2，查询name字段中以“李”字开头。
select * from table1 where name like '李*'
例3，查询name字段中含有数字的。
select * from table1 where name like '%[0-9]%'
例4，查询name字段中含有小写字母的。
select * from table1 where name like '%[a-z]%'
例5，查询name字段中不含有数字的。
select * from table1 where name like '%[!0-9]%'
以上例子能列出什么值来显而易见。但在这里，我们着重要说明的是通配符“*”与“%”的区别。
很多朋友会问，为什么我在以上查询时有个别的表示所有字符的时候用"%"而不用“*”？先看看下面的例子能分别出现什么结果：
select * from table1 where name like '*明*'
select * from table1 where name like '%明%'
大家会看到，前一条语句列出来的是所有的记录，而后一条记录列出来的是name字段中含有“明”的记录，所以说，当我们作字符型字段
包含一个子串的查询时最好采用“%”而不用“*”,用“*”的时候只在开头或者只在结尾时，而不能两端全由“*”代替任意字符的情况

下。

SQLite外键(Foreign Key)支持
从SQLite 3.6.19 开始支持 外键约束. (Ubuntu 10.04 的SQLite版本是 3.6.22, Debian 6.0 的SQLite版本是 3.7.0)
外键约束用来强制 两个表之间”存在”的关系. 比如, 考虑下面的SQL命令建立的schema
CREATE TABLE artist(
artistid INTEGER PRIMARY KEY,
artistname TEXT
); CREATE TABLE track(
trackid INTEGER,
trackname TEXT,
trackartist INTEGER — 必须映射到一个 artist.artistid 记录
);使用这个数据库的应用可以假定: 对于在track表里每一行, 都在artist表都存在一个对应的行. 不幸的是, 如果用户使用外部工具编辑数据
库, 或者在应用程序中存在一个bug. 那么可能在track表中插入一行,而在artist表中没有相应的记录. 或者, 在artist表中删除某些行, 就会在
track表里留下孤儿行(orphaned rows), 它们在artist表中剩下的记录找到任何对应的行. 这可能在以后会导致应用的功能出错. 或者至少让
编写应用程序更复杂.
一个解决方法就是, 为数据库添加一个外键约束,在artist和track这两个表之间强制实施一个约束. 增加外键定义的track表的定义如下:
CREATE TABLE track(
trackid INTEGER,
trackname TEXT,
trackartist INTEGER,
FOREIGN KEY(trackartist) REFERENCES artist(artistid)
);
这样,外键约束就由SQLite强制实施. 往 track表插入一行 在 artist表中没有对应的数据的记录的企图注定是要失败的,o(∩∩)o
如果在track表还存在依赖于artist中的某行的记录, 那么尝试从 artist表删除该行, 也会失败.
也就是说, 对于在track表中的每一行,,下面的表达式都是真:
trackartist IS NULL OR EXISTS(SELECT 1 FROM artist WHERE artistid=trackartist)
在SQLite中启用外键支持
1)为了在SQLite中使用外键约束, 编译sqlite库时, 不能使用 忽略 外键和触发器, 也就是 SQLITE_OMIT_FOREIGN_KEY 和
SQLITE_OMIT_TRIGGER不能被定义
2)必须在运行时打开, 因为 默认是关闭的
PRAGMA foreign_keys = ON;
要求和建议的数据库索引
通常, 外键约束的父键在父表里是主键. 如果它们不是主键, 那么父键栏 必须受一个UNIQUE约束 或者 有一个 UNIQUE 索引.
如果数据库schema还有外键错误, 就需要查看多个表才能找到错误. 数据表创建时不会检测这些错误,
这些错误会阻止应用程序 用SQL语句来修改子表或者父表的内容. 当内容被改变时,报告”DML errors”;当schema被改变时报告”DDL
errors”
也就是说,错误地配置外键约束,要求检查子表和父表的是DML错误, 一般显示”foreign key mismatch” 或者 “no such table”
SELECT rowid FROM <child-table> WHERE <child-key> = :parent_key_value
如果这个SELECT返回数据, 那么SQLite就断定, 从父表删除某行, 将会违背外键约束,并返回错误. 如果父键的值被修改或者在父表插入新
的一行,也会产生类似的查询.
如果这些查询没有使用索引, 它们将强迫对整个子表做线性 查找(scan), 这代价可太大了
在大多数实际系统中, 应该在子键这一栏建立索引. 子键的索引不必(并且是通常都不必) 有一个UNIQUE 索引(因为在子表中的多行对应于
父表中的一行)

CREATE INDEX trackindex ON track(trackartist);

ON DELETE 和 ON UPDATE行为
外键的ON DELETE和 ON UPDATE从句, 可以用来配置 当从父表中删除 某些行时发生的行为(ON DELETE). 或者 修改存在的行的父键
的值, 发生的行为(ON UPDATE)
单个外键约束可以为ON DELETE和ON UPDATE配置不同的行为. 外键行为在很多时候类似于 触发器(trigger)
ON DELETE和ON UPDATE的行为是 NO ACTION, RESTRICT, SET NULL, SET DEFAULT 或者 CASCADE
如果没有明确指定星闻,那么默认就是NO ACTION
NO ACTION: 当父键被修改或者删除时, 没有特别的行为发生
RESTRICT: 存在一个或者多个子键对应于相应的父键时, 应用程序禁止删除(ON DELETE RESTRICT)或者修改(ON UPDATE
RESTRICT) 父键
RESTRICT与普通的外键约束的区别是, 当字段(field)更新时, RESTRICT行为立即发生
SET NULL: 父键被删除(ON DELETE SET NULL) 或者修改 (ON UPDATE SET NULL)
SET DEFAULT: 类似于SET NULL
CASCADE: 将实施在父键上的删除或者更新操作,传播给与之关联的子键.
对于 ON DELETE CASCADE, 同被删除的父表中的行 相关联的子表中的每1行,也会被删除.
对于ON UPDATE CASCADE, 存储在子表中的每1行,对应的字段的值会被自动修改成同新的父键匹配
举例:
CREATE TABLE artist(
artistid INTEGER PRIMARY KEY,
artistname TEXT
);
CREATE TABLE track(
trackid INTEGER,
trackname TEXT,
trackartist INTEGER REFERENCES artist(artistid) ON UPDATE CASCADE
);
INSERT INTO artist VALUES(1, ‘Dean Martin’);
INSERT INTO artist VALUES(2, ‘Frank Sinatra’);
INSERT INTO track VALUES(14, ‘Mr. Bojangles’, 2);
INSERT INTO track VALUES(15, “That’s Amore”, 2);
INSERT INTO track VALUES(12, ‘Christmas Blues’, 1);
INSERT INTO track VALUES(13, ‘My Way’, 2);
sqlite> PRAGMA foreign_keys = ON;
(默认是关闭的, 要在运行时打开)
sqlite> SELECT * FROM artist;
1|Dean Martin
2|Frank Sinatra
sqlite> SELECT * FROM track;
14|Mr. Bojangles|2
15|That’s Amore|2
12|Christmas Blues|1
13|My Way|2
sqlite> UPDATE artist SET artistid = 999 WHERE artistname = ‘Dean Martin’;
(为 Dean Martin更改 artist表中的artistid栏目.
一般情况下, 这将 产生一个约束, 因为会让 track表中的 一条记录成为孤儿记录
但 对外键定义使用了ON UPDATE CASCADE从句后, 会把这个更新传给 子表, 从而让外键约束不被打破)
sqlite> SELECT * FROM artist;
2|Frank Sinatra
999|Dean Martin
sqlite> SELECT * FROM track;
14|Mr. Bojangles|2
15|That’s Amore|2
12|Christmas Blues|999
13|My Way|2
配置一个ON UPDATE或者ON DELETE行为并不意味着 外键约束 并不必要满足.
举例来说, 如果 配置了 ON DELETE SET DEFAULT 行为, 如果在父表中没有 与子表栏目中默认值 相对应 的 行记录, 当依赖的子键存在于
子表中是, 删除父键, 会破坏外键.
举例:
sqlite> PRAGMA foreign_keys = ON;
CREATE TABLE artist(
artistid INTEGER PRIMARY KEY,
artistname TEXT
); CREATE TABLE track(
trackid INTEGER,
trackname TEXT,
trackartist INTEGER DEFAULT 0 REFERENCES artist(artistid) ON DELETE SET DEFAULT
);
INSERT INTO artist VALUES(3, ‘Sammy Davis Jr.’);
INSERT INTO track VALUES(14, ‘Mr. Bojangles’, 3);
sqlite> DELETE FROM artist WHERE artistname = ‘Sammy Davis Jr.’;
Error: foreign key constraint failed
从父表中删除一行, 会引起子表中相关的子键被设置成整数0
然而, 这个值不对应与 附表中的任何一行数据. 所以,外键约束被打破, 就抛出了异常
sqlite> INSERT INTO artist VALUES(0, ‘Unknown Artist’);
往父表中添加一行, 其主键为0
这样删除记录就不会打破外键约束了
sqlite> DELETE FROM artist WHERE artistname = ‘Sammy Davis Jr.’;
sqlite> SELECT * FROM artist;
0|Unknown Artist
sqlite> SELECT * FROM track;
14|Mr. Bojangles|0
这写都很类似于 SQLite 触发器(triggers), ON DELETE SET DEFAULT行为, 在效果上, 同下面的 AFTER DELETE 触发器是类似的
CREATE TRIGGER on_delete_set_default AFTER DELETE ON artist BEGIN
UPDATE child SET trackartist = 0 WHERE trackartist = old.artistid;

END;

外键约束的父表中的某行被删除, 或者存储在父键中的值 被修改时: 时间的逻辑顺序是:
1. 执行在BEFORE 触发器程序
2. 检查本地(非外键)约束
3. 在父表中 更新或者删除行
4. 执行要求的外键行为
5. 执行 AFTER触发器 程序
在ON UPDATE外键行为 和 SQL 触发器之间一个重要区别就是, ON UPDATE 行为只有在 父键的值 被修改并且父键的值修改得跟原来不
一样时,才执行.
如果下UPDATE SET 语句修改的值,跟原来一样, ON UPDATE行为不会执行