【转】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()