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,+9223372036854775807}。sqlite根据数字的值自动控制整数所占的字节数。 空注：参可变长整数的概念。 |
| REAL |
实数是10进制的数值。sqlite使用8字节的符点数来存储实数。 |
| TEXT |
|
| BLOB |
二进制大对象(BLOB)是任意类型的数据。BLOB的大小没有限制。 |
| 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[@R_301_457@名.]表名( 字段名称1字段类型字段约束， 字段名称2字段类型字段约束， 字段名称3字段类型字段约束， 字段名称4字段类型字段约束， …… 分组约束1, 分组约束2, …… ); |
3.1.2.字段约束
| 约束名称 |
约束说明 |
| NOTNULL |
非空，约束强制列不接受NULL值，约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新纪录或者更新记录。 |
| PRIMARYKEY |
主键，约束唯一标识@R_301_457@表中的每条记录。 主键必须包含唯一的值。 主键列不能包含NULL值。 每个表应该都一个主键，并且每个表只能有一个主键。 |
| FOREIGNKEY |
外键，约束本字段的值必须存在于另一个表中主键字段， 当使用外键约束时，如果外键在其所依赖的表中不存在，则记录插入失败。 |
| UNIQUE |
唯一性，约束唯一标识@R_301_457@表中的每条记录，即插入的字段值不可重复，唯一性约束可以包含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,--零售价格 vip_priceNUMBER(6,--会员价格 remarkVARCHAR(255)， FOREIGNKEY(category_code) REFERENCEStbl_goods_category(category_code)); --备注信息 |
Ø创建商品入库单tbl_stock_bill
| --创建商品入库单 --商品编码和入库时间为组合主键 createtabletbl_stock_bill( goods_idCHAR(16),--商品编码 stock_timeDateTime,--入库时间 stock_amountNUMBER(6,--入库数量 prime_costNUMBER(6,--进货价格 primarykey(goods_id,stock_time), FOREIGNKEY(goods_id) REFERENCEStbl_goods_info(goods_id)); |
3.2.AlterTable
3.2.1.语法格式
表重命名
| ALTERTABLE[@R_301_457@名.]表名RENAMETO新表名 |
添加字段
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[@R_301_457@名.]表名 |
3.3.2.脚本示例
删除一个名为tbl_student_info的数据表
| DROPTABLEtbl_student_info |
4.索引-Index
4.1.CreateIndex
4.1.1.语法格式
| CREATEINDEX[@R_301_457@名.]索引名称ON表名( 字段名称1[ASC/DESC]， 字段名称1[ASC/DESC], ……);//在相应的表的列字段或多个列字段上建立相应的索引 |
4.1.2.脚本示例
创建一个名为tbl_student的学生信息表，并为该学生信息表创建索引
| createtabletbl_student( std_idchar(20)primarykey, std_ageinteger); --为学号创建升序索引 createindexidx_std_idONtbl_student(std_idASC); --为姓名和年龄创建姓名为升序，年龄为降序的索引 createindexidx_name_ageONtbl_student(std_nameASC,std_ageDESC); |
4.2.DropIndex
4.2.1.语法格式
| DROPINDEX[@R_301_457@名.]索引名 |
4.2.2.脚本示例
删除一个名为idx_name_age的索引
| DROPINDEXidx_name_age |
5.触发器-Trigger
5.1.CreateTrigger
5.1.1.语法格式
| CREATETRIGGER触发器名称 [BEFORE|AFTER]@R_301_457@事件ON[@R_301_457@名称].表名 [FOREACHROW][WHENexpression] BEGIN 触发器执行动作 END |
@R_301_457@事件：
DELETE INSERT
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
视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个@R_301_457@中的真实的表中的字段。我们可以向视图添加sql函数、WHERE以及JOIN语句，我们也可以提交数据，就像这些来自于某个单一的表。
6.1.CreateView
6.1.1.语法格式
注释：视图总是显示最近的数据。每当用户查询视图时，@R_301_457@引擎通过使用sql语句来重建数据。
6.1.2.脚本示例
假设有一个学生@R_301_457@，其中有班级表，还有学生信息表
以学生信息表为基本，创建一个班级编号WF1103班的学生信息视图
| CREATEVIEWview_student_wf1103asselect*fromstudentwhereclass=‘WF1103’ |
6.2.DropView
6.2.1.语法格式
6.2.2.脚本示例
删除一个名为view_student_wf1103的学生信息表
| DROPVIEWview_student_wf1103 |
7.数据操作-Insert,Update,Delete
7.1.Insert
7.1.1.语法格式
7.1.2.脚本示例
| createtabletbl_student( std_idchar(20)primarykey, std_ageinteger); INSERTINTOtbl_studentVALUES（‘WF110301’,’张三’,23）; INSERTINTOtbl_student(std_id,std_name,std_age)VALUES（‘WF110301’,23）; |
7.2.Update
7.2.1.语法格式
7.2.2.脚本示例
| UPDATEtbl_studentSETstd_age=24wherestd_id=‘WF110301’ |
7.3.Delete
7.3.1.语法格式
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.语法格式
8.1.2.脚本示例
| --查询学生信息表中的所有信息 Select*fromtbl_student --查询学生信息表中所学生的姓名及年龄的信息 Selectstd_name,std_agefromtbl_student |
8.2.Where子句
Where子句通过条件表达式筛选满足条件的记录,条件表达式可以使用sqlite中的各种逻辑运算符号对字段进行筛选。
Where的操作符
| 操作符 |
描述 |
| = |
等于 |
| <> |
不等于 |
| > |
大于 |
| < |
小于 |
| >= |
大于等于 |
| <= |
小于等于 |
| BETWEEN |
在某个范围内 |
| LIKE |
搜索某种模式 |
8.2.1.语法格式
| SELECT[DISTINCT][*|字段列表] WHERE条件表达式 |
8.2.2.脚本示例
| 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[@R_301_457@名称].表名 GROUPBY字段1，字段2，…… SELECT[*|字段列表][,count(*)as新的字段名]FROM[@R_301_457@名称].表名 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[@R_301_457@名称].表名 ORDERBY字段名1[ASC/DESC],字段名2[ASC/DESC],…… |
8.4.2.脚本示例
| Select*fromtbl_student orderbystd_ageDESC,std_idASC |
8.5.LimitOffset子句
LIMIT子句限定行数的最大值。负的LIMIT表示无上限。后跟可选的OFFSET说明跳过结果集中的前多少行,LimitOffset在@R_502_342@中十分有用。
8.5.1.语法格式
| SELECT[DISTINCT][*|字段列表]FROM[@R_301_457@名称].表名 Limit单次最多读取行数offset跳过前面行数 SELECT[DISTINCT][*|字段列表]FROM[@R_301_457@名称].表名 Limit跳过前面行数，单次最多读取行数 |
8.5.2.脚本示例
| Select*fromtbl_student Limit5offset10 或跳过前面10行，获取5条记录 Select*fromtbl_student Limit10,5 |
8.6.多表联合查询
8.6.1.语法格式
| SELECT[DISTINCT][*|字段列表]FROM[@R_301_457@名称].表名1,[@R_301_457@名称].表名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对于两个表格来说是相乘的关系，（inner join 与join就是普通的连接）
8.7.1.语法格式
| --在两个表中存在至少都存在一个能够满足条件表达式的匹配时 --INNERJOIN关键字返回行。INNERJOIN与JOIN是相同的。 SELECT[DISTINCT][*|字段列表]FROM[@R_301_457@名称].左表名… ON条件表达式 SELECT[DISTINCT][*|字段列表]FROM[@R_301_457@名称].左表名… ON条件表达式 --以左表为主，当左表有存在满足条件的记录时，就会从左表返回所有的行 --即使右表都没有满足条件。 SELECT[DISTINCT][*|字段列表]FROM[@R_301_457@名称].左表名… 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_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[@R_301_457@名称].表名1 UNION SELECT[*|字段列表]FROM[@R_301_457@名称].表名2 SELECT[*|字段列表]FROM[@R_301_457@名称].表名1 UNIONALL |
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,...])返回表达式的最小值。 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,...])
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()
