SQLite(快速上手版)笔记

1. SQL语法关键字

关键字描述Create Table创建数据表Alter Table修改数据表Drop Table删除数据表Create Index创建索引Drop Index删除索引Create Trigger创建触发器Drop Trigger删除触发器Create View创建视图Drop View删除视图Insert插入数据Delete删除数据Update更新数据Select查询数据Begin启动事务Commit提交事务Rollback回滚事务2. SQL数据类型  2.1. 本地类型--5种基本类型数据名称说明INTEGER整数值是全数字(包括正和负)。整数可以是1, 2, 3, 4, 6或 8字节。整数的最大范围(8 bytes)是{-9223372036854775808, 0, +9223372036854775807}。SQLite根据数字的值自动控制整数所占的字节数。空注：参可变长整数的概念。REAL实数是10进制的数值。SQLite使用8字节的符点数来存储实数。TEXT文本(TEXT)是字符数据。SQLite支持几种字符编码，包括UTF-8和UTF-16。字符串的大小没有限制。BLOB二进制大对象(BLOB)是任意类型的数据。BLOB的大小没有限制。NULLNULL表示没有值。SQLite具有对NULL的完全支持。  　SQLite通过值的表示法来判断其类型，下面就是SQLite的推理方法：  l SQL语句中用单引号或双引号括起来的文字被指派为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)仅容纳整数。在括号内规定数字的最大位数。INTEGERdecimal(size,d) numeric(size,d)容纳带有小数的数字。"size" 规定数字的最大位数。"d" 规定小数点右侧的最多位数。REALchar(size)容纳固定长度的字符串（可容纳字母、数字以及特殊字符）。在括号中规定字符串的长度。TEXTvarchar(size)容纳可变长度的字符串（可容纳字母、数字以及特殊的字符）。在括号中规定字符串的最大长度。TEXTdate(yyyymmdd)容纳日期。TEXT3. 表-Table  3.1. Create Table    3.1.1. 语法格式CREATE TABLE [数据库名.]表名(字段名称1 字段类型 字段约束，字段名称2 字段类型 字段约束，字段名称3 字段类型 字段约束，字段名称4 字段类型 字段约束，… …分组约束1,分组约束2,… …);    3.1.2. 字段约束约束名称约束说明NOT NULL非空，约束强制列不接受 NULL 值，约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新纪录或者更新记录。PRIMARY KEY主键，约束唯一标识数据库表中的每条记录。主键必须包含唯一的值。主键列不能包含 NULL 值。每个表应该都一个主键，并且每个表只能有一个主键。FOREIGN KEY外键，约束本字段的值必须存在于另一个表中主键字段，当使用外键约束时，如果外键在其所依赖的表中不存在，则记录插入失败。UNIQUE唯一性，约束唯一标识数据库表中的每条记录，即插入的字段值不可重复，唯一性约束可以包含NULL值，但每张表也只能有一个记录为NULL值。DEFAULT默认值，约束字段的默认值，如果插入数据时没有提供该字段的数值，则会使用默认值进行填充。    3.1.3. 数据字典    表名：tbl_goods_category    描述：商品类别字段名称字段类型字段长度字段描述category_codeCHAR8类别编码category_nameVARCHAR128类别名称category_descVARCHAR255类别描述    　表名：tbl_goods_info    描述：商品信息字段名称字段类型字段长度字段描述category_codeCHAR8类别编码goods_idCHAR16商品编码goods_nameVARCHAR128商品名称goods_unitVARCHAR8商品单位prime_costNUMBER(6,2)进货价格sale_priceNUMBER(6,2)零售价格vip_priceNUMBER(6,2)会员价格remarkVARCHAR255备注信息    　表名：tbl_stock_bill    描述：商品入库单字段名称字段类型字段长度字段描述goods_idCHAR16商品编码bill_idCHAR20进货单单号stock_timeDateTime 入库时间stock_amountNUMBER(6,2)入库数量prime_costNUMBER(6,2)进货价格        3.1.4. 脚本示例    Ø 创建商品类别表tbl_goods_category--创建商品类别表--类别编码为主键--类别名称必须具有唯一性create table tbl_goods_category(category_code CHAR(8) primary key,  --类别编码category_name VARCHAR(128) UNIQUE, --类别名称category_desc VARCHAR(255)); --类别描述    　Ø 创建商品信息表tbl_goods_info--创建商品信息表--商品编码为主键--商品名称必须具有唯一性create table tbl_goods_info(category_code CHAR(8) , --类别编码goods_id CHAR(16) primary key, --商品编码goods_name VARCHAR(128) UNIQUE, --商品名称goods_unit VARCHAR(8), --商品单位prime_cost NUMBER(6,2), --进货价格sale_price NUMBER(6,2), --零售价格vip_price NUMBER(6,2), --会员价格remark VARCHAR(255)，FOREIGN KEY (category_code)REFERENCES tbl_goods_category(category_code)); --备注信息      　Ø 创建商品入库单tbl_stock_bill--创建商品入库单--商品编码和入库时间为组合主键create table tbl_stock_bill(goods_id CHAR(16), --商品编码stock_time DateTime, --入库时间stock_amount NUMBER(6,2), --入库数量prime_cost NUMBER(6,2),  --进货价格primary key(goods_id, stock_time),FOREIGN KEY (goods_id)REFERENCES tbl_goods_info(goods_id));3.2. Alter Table    3.2.1. 语法格式    表重命名ALTER TABLE [数据库名.]表名 RENAME TO 新表名    　添加字段ALTER TABLE [数据库名.]表名 ADD 字段名称 字段类型 字段约束    　3.2.2. 脚本示例    Ø 创建一个学生信息表tbl_studentcreate table tbl_student(        std_id char(20) primary key,        std_name varchar(16),std_age integer);    　Ø 修改学生信息表名为tbl_student_infoalter table tbl_student rename tbl_student_info    　Ø 为学生信息表添加班级字段alter table tbl_student_info add class char(8)3.3. Drop Table    3.3.1. 语法格式DROP TABLE [数据库名.]表名    　3.3.2. 脚本示例    删除一个名为tbl_student_info的数据表DROP TABLE tbl_student_info4. 索引-Index  　4.1. Create Index    4.1.1. 语法格式CREATE INDEX [数据库名.]索引名称 ON 表名(字段名称1 [ASC/DESC]，字段名称1 [ASC/DESC],… …);//在相应的表的列字段或多个列字段上建立相应的索引    4.1.2. 脚本示例    创建一个名为tbl_student的学生信息表，并为该学生信息表创建索引create table tbl_student(        std_id char(20) primary key,        std_name varchar(16),        std_age integer); --为学号创建升序索引create index idx_std_id ON tbl_student(std_id ASC); --为姓名和年龄创建姓名为升序，年龄为降序的索引create index idx_name_age ON tbl_student(std_name ASC, std_age DESC);  　4.2. Drop Index      4.2.1. 语法格式DROP INDEX [数据库名.]索引名      　4.2.2. 脚本示例      删除一个名为idx_name_age的索引DROP INDEX idx_name_age5. 触发器-Trigger  　5.1. Create Trigger    5.1.1. 语法格式CREATE TRIGGER 触发器名称[BEFORE|AFTER] 数据库事件 ON [数据库名称].表名[FOR EACH ROW][ WHEN expression]BEGIN触发器执行动作END    　数据库事件：    DELETE INSERT    UPDATE    UPDATE OF 字段列表    　5.1.2. 脚本示例    　假设"customers"表存储了****，"orders"表存储了订单信息，下面的触发器确保当用户改变地址时所有的 关联订单地址均进行相应改变：CREATE TRIGGER trg_on_update_customer_address AFTER UPDATE OF address ON customers  BEGIN UPDATE orders SET address = new.address WHERE customer_name = old.name; （DML语句）END;  　5.2. Drop Trigger    5.2.1. 语法格式DROP TRIGGER触发器名称    　5.2.2. 脚本示例    删除一个名为trg_on_update_customer_address的触发器DROP TRIGGER trg_on_update_customer_address6. 视图-View在 SQL 中，视图是基于SQL 语句的结果集的可视化的表。视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加SQL 函数、WHERE 以及 JOIN 语句，我们也可以提交数据，就像这些来自于某个单一的表。  　6.1. Create View    6.1.1. 语法格式CREATE VIEW [数据库名称].视图名称 AS Select查询语句    注释：视图总是显示最近的数据。每当用户查询视图时，数据库引擎通过使用 SQL 语句来重建数据。    6.1.2. 脚本示例    假设有一个学生数据库，其中有班级表，还有学生信息表    　以学生信息表为基本，创建一个班级编号WF1103班的学生信息视图CREATE VIEW view_student_wf1103 as select * from student where class = ‘WF1103’  　6.2. Drop View    6.2.1. 语法格式DROP VIEW [数据库名称].视图名称    　6.2.2. 脚本示例    删除一个名为view_student_wf1103的学生信息表DROP VIEW view_student_wf11037. 数据操作-Insert,Update,Delete  7.1. Insert    7.1.1. 语法格式INSERT INTO [数据库名称].表名 VALUES(记录内容)//对应表的顺序进行添加和加入值INSERT INTO [数据库名称].表名(字段列表) VALUES(对应字段内容)//按字段列表添加值    　7.1.2. 脚本示例create table tbl_student(        std_id char(20) primary key,        std_name varchar(16),        std_age integer); INSERT INTO tbl_student VALUES（‘WF110301’,’张三’,23）;INSERT INTO tbl_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. 脚本示例UPDATE tbl_student SET std_age=24 where std_id=‘WF110301’  　7.3. Delete    7.3.1. 语法格式DELETE FROM [数据库名称].表名 where 条件表达式    　7.3.2. 脚本示例DELETE FROM tbl_student where std_id=‘WF110301’8. 数据查询-Selectcreate table tbl_class(class_id varchar(8) primary key,class_name varchar(64),class_desc varchar(128)); create table tbl_student(class_id varchar(8),std_id varchar(16) primary key,std_name varchar(8),std_age integer,std_phone varchar(16),std_school varchar(40));  　8.1. 基本查询    8.1.1. 语法格式SELECT [DISTINCT] [*|字段列表] FROM [数据库名称].表名    　8.1.2. 脚本示例--查询学生信息表中的所有信息Select * from tbl_student --查询学生信息表中所学生的姓名及年龄的信息Select std_name, std_age from tbl_student  　8.2. Where子句    Where子句通过条件表达式筛选满足条件的记录,条件表达式可以使用SQLite中的各种逻辑运算符号对字段进行筛选。  Where的操作符操作符描述=等于<>不等于>大于<小于>=大于等于<=小于等于BETWEEN在某个范围内LIKE搜索某种模式    　8.2.1. 语法格式SELECT [DISTINCT] [*|字段列表]FROM [数据库名称].表名 WHERE 条件表达式    　8.2.2. 脚本示例--查询学生信息表中学号等于WF110301的学号Select * from tbl_student where std_id = ‘WF110301’ --查询学生信息表中所年龄大于23的学生Select * from tbl_student where std_age > 23 --查询学生信息表中所有名字中姓‘刘’的学生或着名字最后一个字为‘刚’的学生Select * from tbl_student where std_name like ’刘%’ or std_name like ’%刚’ --查询学生信息表中农林大学，并且手机**中带有6的学生Select * from tbl_student where std_school = ’农林大学’ and std_phone like ’%6%’  　8.3. Group by子句  GROUP BY子句可以在查询将指定的字段表数值相同的记录合并成一条输出， 它与count(*)函数相结合，可以统计在列表中指字段表数值相同的记录的条数。    8.3.1. 语法格式SELECT [DISTINCT] [*|字段列表] FROM [数据库名称].表名 GROUP BY 字段1，字段2，… … SELECT [*|字段列表] [,count(*) as 新的字段名] FROM [数据库名称].表名 GROUP BY 字段1，字段2，… …    　8.3.2. 脚本示例--统计各个学校的学生数Select std_school, count(*) as std_count from tbl_student group by std_school, std_age  　8.4. Order By子句  ORDER BY子句对所得结果根据表达式排序。    8.4.1. 语法格式SELECT [DISTINCT] [*|字段列表] FROM [数据库名称].表名 ORDER BY 字段名1 [ASC/DESC],字段名2 [ASC/DESC],… …    　8.4.2. 脚本示例--查询学生信息，并按年龄递减，学号递增的排序方式显示Select * from tbl_student order by std_age DESC,std_id ASC  　8.5. Limit Offset子句  LIMIT子句限定行数的最大值。负的LIMIT表示无上限。后跟可选的OFFSET说明跳过结果集中的前多少行,Limit Offset在分页显示中十分有用。  8.5.1. 语法格式SELECT [DISTINCT] [*|字段列表] FROM [数据库名称].表名 Limit 单次最多读取行数 offset 跳过前面行数 SELECT [DISTINCT] [*|字段列表] FROM [数据库名称].表名 Limit跳过前面行数，单次最多读取行数    　8.5.2. 脚本示例--查询学生信息，跳过前面10行，获取5 条记录Select * from tbl_student Limit 5 offset 10或 跳过前面10行，获取5 条记录Select * from tbl_student Limit 10,58.6.  多表联合查询  8.6.1. 语法格式SELECT [DISTINCT] [*|字段列表] FROM [数据库名称].表名1, [数据库名称].表名2 … WHERE 条件表达式      一般的连接  　8.6.2. 脚本示例--查询学生信息表中所有班级为WF1103的学生信息，并在结果中输出班级名称和学员姓名Select tbl_class.class_name,tbl_student.std_name from tbl_student, tbl_classwhere tbl_student.class_id = tbl_class.class_id8.7. Join子句  JOIN用于根据两个或多个表中的列之间的关系，从这些表中查询数据,JOIN会将两个表的数据合并起来，输出具有两个表有字段的记录。  JOIN对于两个表格来说是相乘的关系，（inner join 与join就是普通的连接）    8.7.1. 语法格式--在两个表中存在至少都存在一个能够满足条件表达式的匹配时--INNER JOIN 关键字返回行。INNER JOIN 与 JOIN 是相同的。 SELECT [DISTINCT] [*|字段列表] FROM [数据库名称].左表名… INNER JOIN [数据库名称].右表名ON 条件表达式 SELECT [DISTINCT] [*|字段列表] FROM [数据库名称].左表名… JOIN [数据库名称].右表名ON 条件表达式 --以左表为主，当左表有存在满足条件的记录时，就会从左表返回所有的行--即使右表都没有满足条件。SELECT [DISTINCT] [*|字段列表] FROM [数据库名称].左表名… LEFT JOIN [数据库名称].右表名ON 条件表达式    　8.7.2. 脚本示例  例如，学生信息管理**中有学生信息和课程两张表，它们的格式和内容分别如下：  Tbl_student    Tbl_course    Ø INNER JOIN示例，查询班级表中的每一位学生需要学习的课程内容：Select Tbl_student.class_id, Tbl_student.std_id, Tbl_student.std_name, Tbl_course.course, TBL_course.teacher from Tbl_student INNER JOIN Tbl_course ON Tbl_student.class_id = Tbl_course.class_id    　注：由于课程表中没有WF1104班的课程，学生信息表中没有WF1105班的学生，所以使用INNER JOIN时，只会返回两个表同时存在的WF1103班中的学生信息      Ø LEFT JOIN示例，查询班级表中的每一位学生需要学习的课程内容：Select Tbl_student.class_id, Tbl_student.std_id, Tbl_student.std_name, Tbl_course.course, TBL_course.teacher from Tbl_student LEFT JOIN Tbl_course ON Tbl_student.class_id = Tbl_course.class_id    　注：采用LEFT JOIN无论如何都会返回左表的所有记录，即使右表中没有满足条件的记录，所以，即使课程表中没有WF1104班的课程，在左表中的WF1104班的学生信息      仍然会被返回。  8.8. Union子句  UNION 操作符用于合并两个或多个 SELECT 语句的结果集。请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。（个数和类型相同）    8.8.1.  语法格式SELECT [*|字段列表] FROM [数据库名称].表名1UNIONSELECT [*|字段列表] FROM [数据库名称].表名2  SELECT [*|字段列表] FROM [数据库名称].表名1UNION ALLSELECT [*|字段列表] FROM [数据库名称].表名2    　8.8.2. 脚本示例   　9. 事务和锁-Transaction and Lock  9.1. Begin（事务启动）  9.2. Commit（提交）  9.3. Rollback（回滚）  9.4. Sqlite  old.db.dump | sqlite3 new.db(这样可以3<-->2.8)  9.5. 运算符号操作符类型作用||StringConcatenation*ArithmeticMultiply/ArithmeticDivide%ArithmeticModulus+ArithmeticAdd–ArithmeticSubtract<<BitwiseRight shift>>BitwiseLeft shift&LogicalAnd|LogicalOr<RelationalLess than<=RelationalLess than or equal to>RelationalGreater than>=RelationalGreater than or equal to=RelationalEqual to==RelationalEqual to<>RelationalNot equal to!=RelationalNot equal toINLogicalInANDLogicalAndORLogicalOrLIKERelationalString matchingGLOBRelationalFilename matching


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()