【转】SQLite

1.SQL语法关键字

关键字

描述

CreateTable

创建数据表

AlterTable

修改数据表

DropTable

删除数据表

CreateIndex

创建索引

DropIndex

删除索引

CreateTrigger

创建触发器

DropTrigger

删除触发器

CreateView

创建视图

DropView

删除视图

Insert

插入数据

Delete

删除数据

Update

更新数据

Select

查询数据

Begin

启动事务

Commit

提交事务

Rollback

回滚事务

2.SQL数据类型

2.1.本地类型--5种基本类型

数据名称

说明

INTEGER

整数值是全数字(包括正和负)。整数可以是1,2,3,4,6或8字节。整数的最大范围(8bytes)是{-9223372036854775808,0,+9223372036854775807}。SQLite根据数字的值自动控制整数所占的字节数。

空注：参可变长整数的概念。

REAL

实数是10进制的数值。SQLite使用8字节的符点数来存储实数。

TEXT

文本(TEXT)是字符数据。SQLite支持几种字符编码，包括UTF-8和UTF-16。字符串的大小没有限制。

BLOB

二进制大对象(BLOB)是任意类型的数据。BLOB的大小没有限制。

NULL

NULL表示没有值。SQLite具有对NULL的完全支持。

SQLite通过值的表示法来判断其类型，下面就是SQLite的推理方法：

lSQL语句中用单引号或双引号括起来的文字被指派为TEXT。

l如果文字是未用引号括起来的数据，并且没有小数点和指数，被指派为INTEGER。

l如果文字是未用引号括起来的数据，并且带有小数点或指数，被指派为REAL。

l用NULL说明的值被指派为NULL存储类。

l如果一个值的格式为X'ABCD'，其中ABCD为16进制数字，则该值被指派为BLOB。X前缀大小写皆可。

2.2.兼容的SQL92类型

数据类型

类型描述

对应类型

integer(size)

int(size)

smallint(size)

tinyint(size)

仅容纳整数。在括号内规定数字的最大位数。

INTEGER

decimal(size,d)

numeric(size,d)

容纳带有小数的数字。

"size"规定数字的最大位数。"d"规定小数点右侧的最多位数。

REAL

char(size)

容纳固定长度的字符串（可容纳字母、数字以及特殊字符）。

在括号中规定字符串的长度。

TEXT

varchar(size)

容纳可变长度的字符串（可容纳字母、数字以及特殊的字符）。

在括号中规定字符串的最大长度。

TEXT

date(yyyymmdd)

容纳日期。

TEXT

3.表-Table

3.1.CreateTable

3.1.1.语法格式

CREATETABLE[数据库名.]表名(

字段名称1字段类型字段约束，

字段名称2字段类型字段约束，

字段名称3字段类型字段约束，

字段名称4字段类型字段约束，

……

分组约束1,

分组约束2,

……

);

3.1.2.字段约束

约束名称

约束说明

NOTNULL

非空，约束强制列不接受NULL值，约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新纪录或者更新记录。

PRIMARYKEY

主键，约束唯一标识数据库表中的每条记录。

主键必须包含唯一的值。

主键列不能包含NULL值。

每个表应该都一个主键，并且每个表只能有一个主键。

FOREIGNKEY

外键，约束本字段的值必须存在于另一个表中主键字段，

当使用外键约束时，如果外键在其所依赖的表中不存在，则记录插入失败。

UNIQUE

唯一性，约束唯一标识数据库表中的每条记录，即插入的字段值不可重复，唯一性约束可以包含NULL值，但每张表也只能有一个记录为NULL值。

DEFAULT

默认值，约束字段的默认值，如果插入数据时没有提供该字段的数值，则会使用默认值进行填充。

3.1.3.数据字典

表名：tbl_goods_category

描述：商品类别

字段名称

字段类型

字段长度

字段描述

category_code

CHAR

8

类别编码

category_name

VARCHAR

128

类别名称

category_desc

VARCHAR

255

类别描述

表名：tbl_goods_info

描述：商品信息

字段名称

字段类型

字段长度

字段描述

category_code

CHAR

8

类别编码

goods_id

CHAR

16

商品编码

goods_name

VARCHAR

128

商品名称

goods_unit

VARCHAR

8

商品单位

prime_cost

NUMBER

(6,2)

进货价格

sale_price

NUMBER

(6,2)

零售价格

vip_price

NUMBER

(6,2)

会员价格

remark

VARCHAR

255

备注信息

表名：tbl_stock_bill

描述：商品入库单

字段名称

字段类型

字段长度

字段描述

goods_id

CHAR

16

商品编码

bill_id

CHAR

20

进货单单号

stock_time

DateTime

入库时间

stock_amount

NUMBER

(6,2)

入库数量

prime_cost

NUMBER

(6,2)

进货价格

3.1.4.脚本示例

Ø创建商品类别表tbl_goods_category

--创建商品类别表

--类别编码为主键

--类别名称必须具有唯一性

createtabletbl_goods_category(

category_codeCHAR(8)primarykey,--类别编码

category_nameVARCHAR(128)UNIQUE,--类别名称

category_descVARCHAR(255));--类别描述

Ø创建商品信息表tbl_goods_info

--创建商品信息表

--商品编码为主键

--商品名称必须具有唯一性

createtabletbl_goods_info(

category_codeCHAR(8),--类别编码

goods_idCHAR(16)primarykey,--商品编码

goods_nameVARCHAR(128)UNIQUE,--商品名称

goods_unitVARCHAR(8),--商品单位

prime_costNUMBER(6,2),--进货价格

sale_priceNUMBER(6,2),--零售价格

vip_priceNUMBER(6,2),--会员价格

remarkVARCHAR(255)，

FOREIGNKEY(category_code)

REFERENCEStbl_goods_category(category_code));--备注信息

Ø创建商品入库单tbl_stock_bill

--创建商品入库单

--商品编码和入库时间为组合主键

createtabletbl_stock_bill(

goods_idCHAR(16),--商品编码

stock_timeDateTime,--入库时间

stock_amountNUMBER(6,2),--入库数量

prime_costNUMBER(6,2),--进货价格

primarykey(goods_id,stock_time),

FOREIGNKEY(goods_id)

REFERENCEStbl_goods_info(goods_id));

3.2.AlterTable

3.2.1.语法格式

表重命名

ALTERTABLE[数据库名.]表名RENAMETO新表名

添加字段

ALTERTABLE[数据库名.]表名ADD字段名称字段类型字段约束

3.2.2.脚本示例

Ø创建一个学生信息表tbl_student

createtabletbl_student(

std_idchar(20)primarykey,

std_namevarchar(16),

std_ageinteger);

Ø修改学生信息表名为tbl_student_info

altertabletbl_studentrenametbl_student_info

Ø为学生信息表添加班级字段

altertabletbl_student_infoaddclasschar(8)

3.3.DropTable

3.3.1.语法格式

DROPTABLE[数据库名.]表名

3.3.2.脚本示例

删除一个名为tbl_student_info的数据表

DROPTABLEtbl_student_info

4.索引-Index

　4.1.CreateIndex

4.1.1.语法格式

CREATEINDEX[数据库名.]索引名称ON表名(

字段名称1[ASC/DESC]，

字段名称1[ASC/DESC],

……);//在相应的表的列字段或多个列字段上建立相应的索引

4.1.2.脚本示例

创建一个名为tbl_student的学生信息表，并为该学生信息表创建索引

createtabletbl_student(

std_idchar(20)primarykey,

std_namevarchar(16),

std_ageinteger);

--为学号创建升序索引

createindexidx_std_idONtbl_student(std_idASC);

--为姓名和年龄创建姓名为升序，年龄为降序的索引

createindexidx_name_ageONtbl_student(std_nameASC,std_ageDESC);

4.2.DropIndex

4.2.1.语法格式

DROPINDEX[数据库名.]索引名

4.2.2.脚本示例

删除一个名为idx_name_age的索引

DROPINDEXidx_name_age

5.触发器-Trigger

　5.1.CreateTrigger

5.1.1.语法格式

CREATETRIGGER触发器名称

[BEFORE|AFTER]数据库事件ON[数据库名称].表名

[FOREACHROW][WHENexpression]

BEGIN

触发器执行动作

END

数据库事件：

DELETEINSERT

UPDATE

UPDATEOF字段列表

5.1.2.脚本示例

　假设"customers"表存储了客户信息，"orders"表存储了订单信息，下面的触发器确保当用户改变地址时所有的关联订单地址均进行相应改变：

CREATETRIGGERtrg_on_update_customer_address

AFTERUPDATEOFaddressONcustomers

BEGIN

UPDATEordersSETaddress=new.addressWHEREcustomer_name=old.name;（DML语句）

END;

5.2.DropTrigger

5.2.1.语法格式

DROPTRIGGER触发器名称

5.2.2.脚本示例

删除一个名为trg_on_update_customer_address的触发器

DROPTRIGGERtrg_on_update_customer_address

6.视图-View

在SQL中，视图是基于SQL语句的结果集的可视化的表。

视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加SQL函数、WHERE以及JOIN语句，我们也可以提交数据，就像这些来自于某个单一的表。

　6.1.CreateView

6.1.1.语法格式

CREATEVIEW[数据库名称].视图名称ASSelect查询语句

注释：视图总是显示最近的数据。每当用户查询视图时，数据库引擎通过使用SQL语句来重建数据。

6.1.2.脚本示例

假设有一个学生数据库，其中有班级表，还有学生信息表

以学生信息表为基本，创建一个班级编号WF1103班的学生信息视图

CREATEVIEWview_student_wf1103asselect*fromstudentwhereclass=‘WF1103’

6.2.DropView

6.2.1.语法格式

DROPVIEW[数据库名称].视图名称

6.2.2.脚本示例

删除一个名为view_student_wf1103的学生信息表

DROPVIEWview_student_wf1103

7.数据操作-Insert,Update,Delete

7.1.Insert

7.1.1.语法格式

INSERTINTO[数据库名称].表名VALUES(记录内容)//对应表的顺序进行添加和加入值

INSERTINTO[数据库名称].表名(字段列表)VALUES(对应字段内容)//按字段列表添加值

7.1.2.脚本示例

createtabletbl_student(

std_idchar(20)primarykey,

std_namevarchar(16),

std_ageinteger);

INSERTINTOtbl_studentVALUES（‘WF110301’,’张三’,23）;

INSERTINTOtbl_student(std_id,std_name,std_age)VALUES（‘WF110301’,’张三’,23）;

7.2.Update

7.2.1.语法格式

UPDATE[数据库名称].表名SET字段1=字段1值,字段2=字段2值…where条件表达式

7.2.2.脚本示例

UPDATEtbl_studentSETstd_age=24wherestd_id=‘WF110301’

7.3.Delete

7.3.1.语法格式

DELETEFROM[数据库名称].表名where条件表达式

7.3.2.脚本示例

DELETEFROMtbl_studentwherestd_id=‘WF110301’

8.数据查询-Select

createtabletbl_class(

class_idvarchar(8)primarykey,

class_namevarchar(64),

class_descvarchar(128));

createtabletbl_student(

class_idvarchar(8),

std_idvarchar(16)primarykey,

std_namevarchar(8),

std_ageinteger,

std_phonevarchar(16),

std_schoolvarchar(40));

8.1.基本查询

8.1.1.语法格式

SELECT[DISTINCT][*|字段列表]FROM[数据库名称].表名

8.1.2.脚本示例

--查询学生信息表中的所有信息

Select*fromtbl_student

--查询学生信息表中所学生的姓名及年龄的信息

Selectstd_name,std_agefromtbl_student

8.2.Where子句

Where子句通过条件表达式筛选满足条件的记录,条件表达式可以使用SQLite中的各种逻辑运算符号对字段进行筛选。

Where的操作符

操作符

描述

=

等于

<>

不等于

>

大于

<

小于

>=

大于等于

<=

小于等于

BETWEEN

在某个范围内

LIKE

搜索某种模式

8.2.1.语法格式

SELECT[DISTINCT][*|字段列表]

FROM[数据库名称].表名

WHERE条件表达式

8.2.2.脚本示例

--查询学生信息表中学号等于WF110301的学号

Select*fromtbl_student

wherestd_id=‘WF110301’

--查询学生信息表中所年龄大于23的学生

Select*fromtbl_student

wherestd_age>23

--查询学生信息表中所有名字中姓‘刘’的学生或着名字最后一个字为‘刚’的学生

Select*fromtbl_student

wherestd_namelike'刘%'orstd_namelike'%刚'

--查询学生信息表中农林大学，并且手机号码中带有6的学生

Select*fromtbl_student

wherestd_school='农林大学'andstd_phonelike'%6%'

8.3.Groupby子句

GROUPBY子句可以在查询将指定的字段表数值相同的记录合并成一条输出，它与count(*)函数相结合，可以统计在列表中指字段表数值相同的记录的条数。

8.3.1.语法格式

SELECT[DISTINCT][*|字段列表]FROM[数据库名称].表名

GROUPBY字段1，字段2，……

SELECT[*|字段列表][,count(*)as新的字段名]FROM[数据库名称].表名

GROUPBY字段1，字段2，……

8.3.2.脚本示例

--统计各个学校的学生数

Selectstd_school,count(*)asstd_countfromtbl_student

groupbystd_school,std_age

8.4.OrderBy子句

ORDERBY子句对所得结果根据表达式排序。

8.4.1.语法格式

SELECT[DISTINCT][*|字段列表]FROM[数据库名称].表名

ORDERBY字段名1[ASC/DESC],字段名2[ASC/DESC],……

8.4.2.脚本示例

--查询学生信息，并按年龄递减，学号递增的排序方式显示

Select*fromtbl_student

orderbystd_ageDESC,std_idASC

8.5.LimitOffset子句

LIMIT子句限定行数的最大值。负的LIMIT表示无上限。后跟可选的OFFSET说明跳过结果集中的前多少行,LimitOffset在分页显示中十分有用。

8.5.1.语法格式

SELECT[DISTINCT][*|字段列表]FROM[数据库名称].表名

Limit单次最多读取行数offset跳过前面行数

SELECT[DISTINCT][*|字段列表]FROM[数据库名称].表名

Limit跳过前面行数，单次最多读取行数

8.5.2.脚本示例

--查询学生信息，跳过前面10行，获取5条记录

Select*fromtbl_student

Limit5offset10

或跳过前面10行，获取5条记录

Select*fromtbl_student

Limit10,5

8.6.多表联合查询

8.6.1.语法格式

SELECT[DISTINCT][*|字段列表]FROM[数据库名称].表名1,[数据库名称].表名2…

WHERE条件表达式一般的连接

8.6.2.脚本示例

--查询学生信息表中所有班级为WF1103的学生信息，并在结果中输出班级名称和学员姓名

Selecttbl_class.class_name,tbl_student.std_namefromtbl_student,tbl_class

wheretbl_student.class_id=tbl_class.class_id

8.7.Join子句

JOIN用于根据两个或多个表中的列之间的关系，从这些表中查询数据,JOIN会将两个表的数据合并起来，输出具有两个表有字段的记录。

JOIN对于两个表格来说是相乘的关系，（innerjoin与join就是普通的连接）

8.7.1.语法格式

--在两个表中存在至少都存在一个能够满足条件表达式的匹配时

--INNERJOIN关键字返回行。INNERJOIN与JOIN是相同的。

SELECT[DISTINCT][*|字段列表]FROM[数据库名称].左表名…

INNERJOIN[数据库名称].右表名

ON条件表达式

SELECT[DISTINCT][*|字段列表]FROM[数据库名称].左表名…

JOIN[数据库名称].右表名

ON条件表达式

--以左表为主，当左表有存在满足条件的记录时，就会从左表返回所有的行

--即使右表都没有满足条件。

SELECT[DISTINCT][*|字段列表]FROM[数据库名称].左表名…

LEFTJOIN[数据库名称].右表名

ON条件表达式

8.7.2.脚本示例

例如，学生信息管理系统中有学生信息和课程两张表，它们的格式和内容分别如下：

Tbl_student

Tbl_course

ØINNERJOIN示例，查询班级表中的每一位学生需要学习的课程内容：

SelectTbl_student.class_id,Tbl_student.std_id,Tbl_student.std_name,Tbl_course.course,TBL_course.teacherfromTbl_student

INNERJOINTbl_courseONTbl_student.class_id=Tbl_course.class_id

注：由于课程表中没有WF1104班的课程，学生信息表中没有WF1105班的学生，所以使用INNERJOIN时，只会返回两个表同时存在的WF1103班中的学生信息

ØLEFTJOIN示例，查询班级表中的每一位学生需要学习的课程内容：

SelectTbl_student.class_id,Tbl_student.std_id,Tbl_student.std_name,Tbl_course.course,TBL_course.teacherfromTbl_student

LEFTJOINTbl_courseONTbl_student.class_id=Tbl_course.class_id

注：采用LEFTJOIN无论如何都会返回左表的所有记录，即使右表中没有满足条件的记录，所以，即使课程表中没有WF1104班的课程，在左表中的WF1104班的学生信息　　　　　　仍然会被返回。

8.8.Union子句

UNION操作符用于合并两个或多个SELECT语句的结果集。

请注意，UNION内部的SELECT语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条SELECT语句中的列的顺序必须相同。（个数和类型相同）

8.8.1.语法格式

SELECT[*|字段列表]FROM[数据库名称].表名1

UNION

SELECT[*|字段列表]FROM[数据库名称].表名2

SELECT[*|字段列表]FROM[数据库名称].表名1

UNIONALL

SELECT[*|字段列表]FROM[数据库名称].表名2

8.8.2.脚本示例

9.事务和锁-TransactionandLock

9.1.Begin（事务启动）

9.2.Commit（提交）

9.3.Rollback（回滚）

9.4.Sqliteold.db.dump|sqlite3new.db(这样可以3<-->2.8)

9.5.运算符号

操作符

类型

作用

||

String

Concatenation

*

Arithmetic

Multiply

/

Arithmetic

Divide

%

Arithmetic

Modulus

+

Arithmetic

Add

–

Arithmetic

Subtract

<<

Bitwise

Rightshift

>>

Bitwise

Leftshift

&

Logical

And

|

Logical

Or

<

Relational

Lessthan

<=

Relational

Lessthanorequalto

>

Relational

Greaterthan

>=

Relational

Greaterthanorequalto

=

Relational

Equalto

==

Relational

Equalto

<>

Relational

Notequalto

!=

Relational

Notequalto

IN

Logical

In

AND

Logical

And

OR

Logical

Or

LIKE

Relational

Stringmatching

GLOB

Relational

Filenamematching

10.内建函数

SQLite内建函数表算术函数abs(X)返回给定数字表达式的绝对值。max(X,Y[,...])返回表达式的最大值。min(X,Y[,...])返回表达式的最小值。random(*)返回随机数。round(X[,Y])返回数字表达式并四舍五入为指定的长度或精度。字符处理函数length(X)返回给定字符串表达式的字符个数。lower(X)将大写字符数据转换为小写字符数据后返回字符表达式。upper(X)返回将小写字符数据转换为大写的字符表达式。substr(X,Y,Z)返回表达式的一部分。randstr()quote(A)like(A,B)确定给定的字符串是否与指定的模式匹配。glob(A,B)条件判断函数coalesce(X,Y[,...])

ifnull(X,Y)

nullif(X,Y)集合函数avg(X)返回组中值的平均值。count(X)返回组中项目的数量。max(X)返回组中值的最大值。min(X)返回组中值的最小值。sum(X)返回表达式中所有值的和。其他函数typeof(X)返回数据的类型。last_insert_rowid()返回最后插入的数据的ID。sqlite_version(*)返回SQLite的版本。change_count()返回受上一语句影响的行数。last_statement_change_count()