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的大小没有限制。 |
NULL | NULL表示没有值。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) | 仅容纳整数。在括号内规定数字的最大位数。 | INTEGER |
decimal(size,d) numeric(size,d) | 容纳带有小数的数字。 "size" 规定数字的最大位数。"d" 规定小数点右侧的最多位数。 | REAL |
char(size) | 容纳固定长度的字符串（可容纳字母、数字以及特殊字符）。 在括号中规定字符串的长度。 | TEXT |
varchar(size) | 容纳可变长度的字符串（可容纳字母、数字以及特殊的字符）。 在括号中规定字符串的最大长度。 | TEXT |
date(yyyymmdd) | 容纳日期。 | TEXT |
3. 表-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_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
--创建商品类别表 --类别编码为主键 --类别名称必须具有唯一性 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_student
create table tbl_student( std_id char(20) primary key, std_name varchar(16), std_age integer); |
Ø 修改学生信息表名为tbl_student_info
alter 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_info |
4. 索引-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_age |
5. 触发器-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_address |
6. 视图-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_wf1103 |
7. 数据操作-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. 数据查询-Select
create 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,5 |
8.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_class where tbl_student.class_id = tbl_class.class_id |
8.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 [数据库名称].表名1 UNION SELECT [*|字段列表] FROM [数据库名称].表名2
SELECT [*|字段列表] FROM [数据库名称].表名1 UNION ALL SELECT [*|字段列表] 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. 运算符号
操作符 | 类型 | 作用 |
|| | String | Concatenation |
* | Arithmetic | Multiply |
/ | Arithmetic | Divide |
% | Arithmetic | Modulus |
+ | Arithmetic | Add |
– | Arithmetic | Subtract |
<< | Bitwise | Right shift |
>> | Bitwise | Left shift |
& | Logical | And |
| | Logical | Or |
< | Relational | Less than |
<= | Relational | Less than or equal to |
> | Relational | Greater than |
>= | Relational | Greater than or equal to |
= | Relational | Equal to |
== | Relational | Equal to |
<> | Relational | Not equal to |
!= | Relational | Not equal to |
IN | Logical | In |
AND | Logical | And |
OR | Logical | Or |
LIKE | Relational | String matching |
GLOB | Relational | Filename 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()
