1. SQL语法关键字
2. SQL数据类型 2.1. 本地类型--5种基本类型
SQLite通过值的表示法来判断其类型,下面就是SQLite的推理方法: l SQL语句中用单引号或双引号括起来的文字被指派为TEXT。 l 如果文字是未用引号括起来的数据,并且没有小数点和指数,被指派为INTEGER。 l 如果文字是未用引号括起来的数据,并且带有小数点或指数,被指派为REAL。 l 用NULL说明的值被指派为NULL存储类。 l 如果一个值的格式为X’ABCD’,其中ABCD为16进制数字,则该值被指派为BLOB。X前缀大小写皆可。 2.2. 兼容的SQL92类型
3. 表-Table 3.1. Create Table 3.1.1. 语法格式
3.1.2. 字段约束
3.1.3. 数据字典 表名:tbl_goods_category 描述:商品类别
表名:tbl_goods_info 描述:商品信息
表名:tbl_stock_bill 描述:商品入库单
3.1.4. 脚本示例 Ø 创建商品类别表tbl_goods_category
Ø 创建商品信息表tbl_goods_info
Ø 创建商品入库单tbl_stock_bill
3.2. Alter Table 3.2.1. 语法格式 表重命名
添加字段
3.2.2. 脚本示例 Ø 创建一个学生信息表tbl_student
Ø 修改学生信息表名为tbl_student_info
Ø 为学生信息表添加班级字段
3.3. Drop Table 3.3.1. 语法格式
3.3.2. 脚本示例 删除一个名为tbl_student_info的数据表
4. 索引-Index 4.1. Create Index 4.1.1. 语法格式
4.1.2. 脚本示例 创建一个名为tbl_student的学生信息表,并为该学生信息表创建索引
4.2. Drop Index 4.2.1. 语法格式
4.2.2. 脚本示例 删除一个名为idx_name_age的索引
5. 触发器-Trigger 5.1. Create Trigger 5.1.1. 语法格式
数据库事件: DELETE INSERT UPDATE UPDATE OF 字段列表 5.1.2. 脚本示例 假设"customers"表存储了****,"orders"表存储了订单信息,下面的触发器确保当用户改变地址时所有的 关联订单地址均进行相应改变:
5.2. Drop Trigger 5.2.1. 语法格式
5.2.2. 脚本示例 删除一个名为trg_on_update_customer_address的触发器
6. 视图-View在 SQL 中,视图是基于SQL 语句的结果集的可视化的表。视图包含行和列,就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加SQL 函数、WHERE 以及 JOIN 语句,我们也可以提交数据,就像这些来自于某个单一的表。 6.1. Create View 6.1.1. 语法格式
注释:视图总是显示最近的数据。每当用户查询视图时,数据库引擎通过使用 SQL 语句来重建数据。 6.1.2. 脚本示例 假设有一个学生数据库,其中有班级表,还有学生信息表 以学生信息表为基本,创建一个班级编号WF1103班的学生信息视图
6.2. Drop View 6.2.1. 语法格式
6.2.2. 脚本示例 删除一个名为view_student_wf1103的学生信息表
7. 数据操作-Insert,Update,Delete 7.1. Insert 7.1.1. 语法格式
7.1.2. 脚本示例
7.2. Update 7.2.1. 语法格式
7.2.2. 脚本示例
7.3. Delete 7.3.1. 语法格式
7.3.2. 脚本示例
8. 数据查询-Select
8.1. 基本查询 8.1.1. 语法格式
8.1.2. 脚本示例
8.2. Where子句 Where子句通过条件表达式筛选满足条件的记录,条件表达式可以使用SQLite中的各种逻辑运算符号对字段进行筛选。 Where的操作符
8.2.1. 语法格式
8.2.2. 脚本示例
8.3. Group by子句 GROUP BY子句可以在查询将指定的字段表数值相同的记录合并成一条输出, 它与count(*)函数相结合,可以统计在列表中指字段表数值相同的记录的条数。 8.3.1. 语法格式
8.3.2. 脚本示例
8.4. Order By子句 ORDER BY子句对所得结果根据表达式排序。 8.4.1. 语法格式
8.4.2. 脚本示例
8.5. Limit Offset子句 LIMIT子句限定行数的最大值。负的LIMIT表示无上限。后跟可选的OFFSET说明跳过结果集中的前多少行,Limit Offset在分页显示中十分有用。 8.5.1. 语法格式
8.5.2. 脚本示例
8.6. 多表联合查询 8.6.1. 语法格式
8.6.2. 脚本示例
8.7. Join子句 JOIN用于根据两个或多个表中的列之间的关系,从这些表中查询数据,JOIN会将两个表的数据合并起来,输出具有两个表有字段的记录。 JOIN对于两个表格来说是相乘的关系,(inner join 与join就是普通的连接) 8.7.1. 语法格式
8.7.2. 脚本示例 例如,学生信息管理**中有学生信息和课程两张表,它们的格式和内容分别如下: Tbl_student Tbl_course Ø INNER JOIN示例,查询班级表中的每一位学生需要学习的课程内容:
注:由于课程表中没有WF1104班的课程,学生信息表中没有WF1105班的学生,所以使用INNER JOIN时,只会返回两个表同时存在的WF1103班中的学生信息 Ø LEFT JOIN示例,查询班级表中的每一位学生需要学习的课程内容:
注:采用LEFT JOIN无论如何都会返回左表的所有记录,即使右表中没有满足条件的记录,所以,即使课程表中没有WF1104班的课程,在左表中的WF1104班的学生信息 仍然会被返回。 8.8. Union子句 UNION 操作符用于合并两个或多个 SELECT 语句的结果集。请注意,UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同。(个数和类型相同) 8.8.1. 语法格式
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. 运算符号
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()
关键字 | 描述 |
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 | 回滚事务 |
数据名称 | 说明 |
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的完全支持。 |
数据类型 | 类型描述 | 对应类型 |
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 |
CREATE TABLE [数据库名.]表名(字段名称1 字段类型 字段约束,字段名称2 字段类型 字段约束,字段名称3 字段类型 字段约束,字段名称4 字段类型 字段约束,… …分组约束1,分组约束2,… …); |
约束名称 | 约束说明 |
NOT NULL | 非空,约束强制列不接受 NULL 值,约束强制字段始终包含值。这意味着,如果不向字段添加值,就无法插入新纪录或者更新记录。 |
PRIMARY KEY | 主键,约束唯一标识数据库表中的每条记录。主键必须包含唯一的值。主键列不能包含 NULL 值。每个表应该都一个主键,并且每个表只能有一个主键。 |
FOREIGN KEY | 外键,约束本字段的值必须存在于另一个表中主键字段,当使用外键约束时,如果外键在其所依赖的表中不存在,则记录插入失败。 |
UNIQUE | 唯一性,约束唯一标识数据库表中的每条记录,即插入的字段值不可重复,唯一性约束可以包含NULL值,但每张表也只能有一个记录为NULL值。 |
DEFAULT | 默认值,约束字段的默认值,如果插入数据时没有提供该字段的数值,则会使用默认值进行填充。 |
字段名称 | 字段类型 | 字段长度 | 字段描述 |
category_code | CHAR | 8 | 类别编码 |
category_name | VARCHAR | 128 | 类别名称 |
category_desc | VARCHAR | 255 | 类别描述 |
字段名称 | 字段类型 | 字段长度 | 字段描述 |
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 | 备注信息 |
字段名称 | 字段类型 | 字段长度 | 字段描述 |
goods_id | CHAR | 16 | 商品编码 |
bill_id | CHAR | 20 | 进货单单号 |
stock_time | DateTime | 入库时间 | |
stock_amount | NUMBER | (6,2) | 入库数量 |
prime_cost | NUMBER | (6,2) | 进货价格 |
--创建商品类别表--类别编码为主键--类别名称必须具有唯一性create table tbl_goods_category(category_code CHAR(8) primary key, --类别编码category_name VARCHAR(128) UNIQUE, --类别名称category_desc VARCHAR(255)); --类别描述 |
--创建商品信息表--商品编码为主键--商品名称必须具有唯一性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)); --备注信息 |
--创建商品入库单--商品编码和入库时间为组合主键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)); |
ALTER TABLE [数据库名.]表名 RENAME TO 新表名 |
ALTER TABLE [数据库名.]表名 ADD 字段名称 字段类型 字段约束 |
create table tbl_student( std_id char(20) primary key, std_name varchar(16),std_age integer); |
alter table tbl_student rename tbl_student_info |
alter table tbl_student_info add class char(8) |
DROP TABLE [数据库名.]表名 |
DROP TABLE tbl_student_info |
CREATE INDEX [数据库名.]索引名称 ON 表名(字段名称1 [ASC/DESC],字段名称1 [ASC/DESC],… …);//在相应的表的列字段或多个列字段上建立相应的索引 |
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); |
DROP INDEX [数据库名.]索引名 |
DROP INDEX idx_name_age |
CREATE TRIGGER 触发器名称[BEFORE|AFTER] 数据库事件 ON [数据库名称].表名[FOR EACH ROW][ WHEN expression]BEGIN触发器执行动作END |
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; |
DROP TRIGGER触发器名称 |
DROP TRIGGER trg_on_update_customer_address |
CREATE VIEW [数据库名称].视图名称 AS Select查询语句 |
CREATE VIEW view_student_wf1103 as select * from student where class = ‘WF1103’ |
DROP VIEW [数据库名称].视图名称 |
DROP VIEW view_student_wf1103 |
INSERT INTO [数据库名称].表名 VALUES(记录内容)//对应表的顺序进行添加和加入值INSERT INTO [数据库名称].表名(字段列表) VALUES(对应字段内容)//按字段列表添加值 |
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); |
UPDATE [数据库名称].表名 SET 字段1=字段1值,字段2=字段2值… where 条件表达式 |
UPDATE tbl_student SET std_age=24 where std_id=‘WF110301’ |
DELETE FROM [数据库名称].表名 where 条件表达式 |
DELETE FROM tbl_student where std_id=‘WF110301’ |
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)); |
SELECT [DISTINCT] [*|字段列表] FROM [数据库名称].表名 |
--查询学生信息表中的所有信息Select * from tbl_student --查询学生信息表中所学生的姓名及年龄的信息Select std_name, std_age from tbl_student |
操作符 | 描述 |
= | 等于 |
<> | 不等于 |
> | 大于 |
< | 小于 |
>= | 大于等于 |
<= | 小于等于 |
BETWEEN | 在某个范围内 |
LIKE | 搜索某种模式 |
SELECT [DISTINCT] [*|字段列表]FROM [数据库名称].表名 WHERE 条件表达式 |
--查询学生信息表中学号等于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%’ |
SELECT [DISTINCT] [*|字段列表] FROM [数据库名称].表名 GROUP BY 字段1,字段2,… … SELECT [*|字段列表] [,count(*) as 新的字段名] FROM [数据库名称].表名 GROUP BY 字段1,字段2,… … |
--统计各个学校的学生数Select std_school, count(*) as std_count from tbl_student group by std_school, std_age |
SELECT [DISTINCT] [*|字段列表] FROM [数据库名称].表名 ORDER BY 字段名1 [ASC/DESC],字段名2 [ASC/DESC],… … |
--查询学生信息,并按年龄递减,学号递增的排序方式显示Select * from tbl_student order by std_age DESC,std_id ASC |
SELECT [DISTINCT] [*|字段列表] FROM [数据库名称].表名 Limit 单次最多读取行数 offset 跳过前面行数 SELECT [DISTINCT] [*|字段列表] FROM [数据库名称].表名 Limit跳过前面行数,单次最多读取行数 |
--查询学生信息,跳过前面10行,获取5 条记录Select * from tbl_student Limit 5 offset 10或 跳过前面10行,获取5 条记录Select * from tbl_student Limit 10,5 |
SELECT [DISTINCT] [*|字段列表] FROM [数据库名称].表名1, [数据库名称].表名2 … WHERE 条件表达式 一般的连接 |
--查询学生信息表中所有班级为WF1103的学生信息,并在结果中输出班级名称和学员姓名Select tbl_class.class_name,tbl_student.std_name from tbl_student, tbl_classwhere tbl_student.class_id = tbl_class.class_id |
--在两个表中存在至少都存在一个能够满足条件表达式的匹配时--INNER JOIN 关键字返回行。INNER JOIN 与 JOIN 是相同的。 SELECT [DISTINCT] [*|字段列表] FROM [数据库名称].左表名… INNER JOIN [数据库名称].右表名ON 条件表达式 SELECT [DISTINCT] [*|字段列表] FROM [数据库名称].左表名… JOIN [数据库名称].右表名ON 条件表达式 --以左表为主,当左表有存在满足条件的记录时,就会从左表返回所有的行--即使右表都没有满足条件。SELECT [DISTINCT] [*|字段列表] FROM [数据库名称].左表名… LEFT JOIN [数据库名称].右表名ON 条件表达式 |
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 |
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 |
SELECT [*|字段列表] FROM [数据库名称].表名1UNIONSELECT [*|字段列表] FROM [数据库名称].表名2 SELECT [*|字段列表] FROM [数据库名称].表名1UNION ALLSELECT [*|字段列表] FROM [数据库名称].表名2 |
操作符 | 类型 | 作用 |
|| | 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()