数据处理安全

sqlite语法 (SQL)

2017-02-20  本文已影响125人  liboxiang

官网：http://www.sqlite.org/lang.html （他人的中文翻译http://blog.csdn.net/ejzhang/article/details/6224915/#09 ）
可视化工具可以用 Datum Free
SQL对大小写不敏感，但也有一些命令是大小写敏感的，比如 GLOB 和 glob 在 SQLite 的语句中有不同的含义。一般关键词大写

EXPLAIN


sql-stmt.gif

EXPLAIN命令主要用于辅助开发，用于输出sql命令的执行过程。

ANALYZE


analyze-stmt.gif

使用ANALYZE命令可以使系统收集表格（tables）和索引（indices）的统计数据，并存储到数据库的内部表格中（sqlite_stat开头的表格为内部表格，使用select等命令可以查询或修改内部表格数据，详情看官网介绍）。如果ANALYZE后面不带参数，则所有被连接的数据库都会被分析。
即使数据库的内容被修改了，通过ANALYZE命令收集的数据不会自动更新。如果数据库的内容有重大修改或者数据库结构改变，应该重新运行ANALYZE命令以更行采集数据。
由于读取数据库内容的时候，查询程序会将采集数据读取到缓存中，所以如果应用程序直接修改数据表格，数据库将不会立即响应，因此，应用程序应该通过执行ANALYZE sqlite_master命令强制查询程序重新读取数据表格中的采集数据。

一、创建处理

1、CREATE TABLE

create-table-stmt.gif

注：表格名不能以“sqlite_”开头，“sqlite_”为数据库预留开头

"CREATE TEMP(TEMPORARY) TABLE"创建的是临时表格，临时表和与其相关的索引、触发器和视观表都存放在一个名为temp的临时数据库文件中。而名为temp的数据库文件是在第一次使用"CREATE TEMP(TEMPORARY) TABLE"的时候自动创建的。临时表格和名为temp的临时数据库文件会在数据库被关闭的时候会被自动删除。

column-def.gif column-constraint.gif

eg.
组合键create table test (name char(50),nameID char(50),constraint myKey primary key (name,nameID))；
注释：name和nameID不能同时相等，多条数据name和nameID可同时等于NULL，constraint myKey可要可不要，

eg.
1、CREATE TABLE Persons
(
Id_P int NOT NULL CHECK (Id_P>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
2、CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes')
)
注：CONSTRAINT chk_Person可要可不要

collation-name:

  • BINARY - 使用 memcmp() 对字符串数据进行比较，无论文本编码。
  • NOCASE - 与 BINARY 相同，除了在比较执行之前会将 ASCII 码的 26 个大写字母被折换为与其等值的小写字母。注意，只有 ASCII 码的字符进行大小写折换。由于所需表的大小，SQLite 不会尝试对完整 UTF 的大小写进行折换。
  • RTRIM - 与 BINARY 相同，除了尾随的空格将被忽略。

校对规则:
每个表的每个列都有一个相关联的校对函数。如果没有校对函数被明确定义，校对规则默认为 BINARY。列定义的 COLLATE 子句被用来替代地为一个列定义校对函数。
为一个二元比较操作符（=、<、>、<=、>=、!=、IS 和 IS NOT）决定使用何种校对函数，按照下面展示的规则顺序进行：

1、如果两个操作数都使用 COLLATE 后缀操作符分配了一个明确的校对函数，那么明确的校对函数被用在比较中，左边操作数的校对函数有较高的优先级。
2、如果两个操作数都是一个列，左边列的校对函数有较高的优先级。前一句的目的是，带有一个或多个一元“+”操作符的列名仍被认为是一个列名。
3、否则，比较将会使用 BINARY 校对函数。

表达式“x BETWEEN y AND z”在逻辑上等价于两个比较“x >= y AND x <= z”，并且与校对函数一起工作，就像这是有两个分开的比较一样。表达式“x IN (SELECT y ...)”与“x = y”的处理方式一样，目的是为了确定校对序列。用在“x IN (y z ...)”格式表达式上的校对序列，就是 x 的校对序列。
ORDER BY 子句是一个 SELECT 语句的一部分，其规则是，可使用 COLLATE 操作符为 SELECT 语句分配一个校对序列，在这种情况下，特定的校对函数被用于排序。否则，如果通过 ORDER BY 子句进行排序的表达式是一个列，列的校对序列将被用来确定排序顺序。如果表达式不是一个列并且没有 COLLATE 子句，则 BINARY 校对序列将被使用。

eg.
CREATE TABLE ORDERS (Order_ID integer primary key,Order_Date datetime,Customer_SID integer references CUSTOMER(SID),Amount double);

CREATE TABLE parent(a PRIMARY KEY, b UNIQUE, c, d, e, f);
CREATE UNIQUE INDEX i1 ON parent(c, d);
CREATE INDEX i2 ON parent(e);
CREATE UNIQUE INDEX i3 ON parent(f COLLATE nocase);
CREATE TABLE child1(f, g REFERENCES parent(a)); -- Ok
CREATE TABLE child2(h, i REFERENCES parent(b)); -- Ok
CREATE TABLE child3(j, k, FOREIGN KEY(j, k) REFERENCES parent(c, d)); -- Ok
CREATE TABLE child4(l, m REFERENCES parent(e)); -- Error!
CREATE TABLE child5(n, o REFERENCES parent(f)); -- Error!
CREATE TABLE child6(p, q, FOREIGN KEY(p, q) REFERENCES parent(b, c)); -- Error!
CREATE TABLE child7(r REFERENCES parent(c)); -- Error!

注：sqlite默认是关闭外来键功能的，在使用该约束之前，需要开外来键功能：PRAGMA foreign_keys = ON（FMDB：[_db executeUpdate:@"PRAGMA foreign_keys = ON"];）

CUSTOMER 表格

栏位名 性质
SID 主键
Last_Name
First_Name

ORDERS表格

栏位名 性质
Order_ID 主键
Order_Date
Customer_SID 外来键
Amount
table-constraint.gif

根据查询数据创建表格，该方法创建的表格没有primary key和其他对表格或数据的限制
CREATE TABLE "表格名" AS SELECT...
eg.create table new as select * from otherTableName;

（1）必须存在primary key，否则报错
（2）不能使用AUTOINCREMENT
（3）使用该约束后，表格的PRIMARY KEY 对应的值不能为NULL

2、CREATE VIEW

视观表(Views)可以被当作是虚拟表格。它跟表格的不同是，表格中有实际储存资料，而视观表是建立在表格之上的一个架构，它本身并不实际储存资料。


create-view-stmt.gif

如果column-name有值，则创建的view用column-name中的值做列名，否则用select-stmt结果做列名

eg.
（1）针对单个表创建视观表

CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition
eg.
有表格：TABLE Customer (First_Name char(50),Last_Name char(50),Address char(50))
创建对应的视观表：CREATE VIEW V_Customer AS SELECT First_Name, Last_Name FROM Customer

（2）针对多个表创建视观表

CREATE VIEW V_REGION_SALES AS SELECT A1.region_name REGION, SUM(A2.Sales) SALES FROM Geography A1, Store_Information A2 WHERE A1.store_name = A2.store_name GROUP BY A1.region_name

Store_Information 表格

store_name Sales Date
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
Los Angeles $300 Jan-08-1999
Boston $700 Jan-08-1999

Geography 表格

region_name store_name
East Boston
East New York
West Los Angeles
West San Diego

V_REGION_SALES结果如下

REGION SALES
East $700
West $2050

3、CREATE INDEX

您可以在表中创建索引，以便更加快速高效地查询数据。
用户无法看到索引，它们只能被用来加速搜索/查询。


create-index-stmt.gif

注释：更新一个包含索引的表需要比更新一个没有索引的表更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引。

4、CREATE TRIGGER

create-trigger-stmt.gif

触发器中DELETE,UPDATE,INSERT语句的一些限制如下：

  • 在DELETE,UPDATE,INSERT语句对应的表的名称必须是绝对的表名。换句话说，必须只使用“表”而不是“数据库.表的”。要修改的表必须存在于与触发器关联的表或视观表的同一数据库中。

通过在创建触发器的时候指定INSTEAD OF，除了可以创建基于普通表的触发器，还可以创建基于视观表（views）的触发器。如果不创建对应的触发器，当对视观表（views）修改的时候，真实表的数据将不会跟随着改变。

二、ALTER TABLE

alter-table-stmt.gif

如果表格有触发器和索引，重命名后，这些触发器和索引依然和表哥链接。但是，如果有视观表定义或通过触发器执行的命令参考了表格，则表格重命名后，不会自动修改表格名。如果有需要，视观表定义和触发器必须删掉重新用新表格名创建。
如果开了外来键，则表格重命名后，外来键定义会对应于新表格名被自动修改

三、DROP

四、INSERT/REPLACE

insert-stmt.gif

如果省略column-name项，则插入每行的值的数量必须和表格的列数目相同。如果使用column-name项，则插入每行的值的数量和column-name清单数量相同。如果column-name清单中没有的列，插入的时候则使用默认值或者NUll。

用于创建一个或多个新行。

用于将select-stmt的结果插入表格中。

插入的行的数值使用默认值，如果没有默认值，则为NULL

五、 UPDATE

update-stmt.gif

六、 DELETE

delete-stmt.gif

七、SELECT

select-stmt.gif

八、TRANSACTION（事务）

1、 begin-stmt


begin-stmt.gif

2、 commit/end-stmt


commit-stmt.gif

3、 rollback-stmt


rollback-stmt.gif

对数据库的修改都是通过事务实现的，除了select外的所有修改数据库的命令都会自动启动一事务处理，完成之后自动关闭事务。此外，事务也可以用BEGIN命令手动开启。当执行commit/end、rollback或者数据库被关闭、设定用rollback解决的冲突（conflict）发生的时候，事务会被关闭。

此为事务的默认类型，对于该类型的事务，直到对数据库进行读写操作的时候才会获取数据库的锁，首次读的时候会获取SHARED锁，首次写的时候会获取RESERVED锁。
当执行commit命令的时候，如果其他线程持有shared锁，则返回结果为SQLITE_BUSY。对于这种commit失败，线程会保持，并可以再次尝试commit

对于该类型的事务，在执行begin命令后就会获取数据库的RESERVED锁。

该类型事务，在执行bigin命令后就会获取数据库的exclusive锁。

sqlite有五种锁：

十、其他

1、ON CONFLICT clause

conflict-clause.gif
  • ROLLBACK
    当发生约束冲突，命令中止正在执行的SQL语句并返回SQLITE_CONSTRAINT错误，同时ROLLBACK当前事务。若当前无活动事务(除了每一条命令创建的默认事务以外)，则该算法与ABORT相同。

2. VACUUM

VACUUM 命令通过复制主数据库中的内容到一个临时数据库文件，然后清空主数据库，并从副本中重新载入原始的数据库文件。这消除了空闲页，把表中的数据排列为连续的，另外会清理数据库文件结构。

VACUUM命令只能工作在主数据库上，不能用来“清扫”一个附加的数据库文件。

VACUUM命令通过如下方式进行工作：首先把数据库内容复制到一个临时数据库文件中，然后再把临时数据库文件中的内容写回到原始数据库文件中（以整理数据库文件）。当将内容重新写回到原始数据库文件时，一个回滚日志或者写前日志WAL文件将会被使用，正如它为其它数据库事务服务一样。这意味着当“清扫”一个数据库时，将需要使用高达两倍于原始数据库文件大小的空闲磁盘空间。

VACUUM命令可能会改变数据库表的行ID（ROWIDs），假如这个表没有一个显示的整形主键（INTEGER PRIMARY KEY）的话。

如果存在一个打开（open）的事务，或者存在一个或多个正在活动（活跃）的（active）SQL语句，那么VACUUM命令将会执行失败。

对于SQLite3.1，可以使用“auto-vacuum模式”代替“VACUUM命令”在删除数据后回收空间，可以通过使用auto_vacuum编译指示来使能auto-vacuum模式。使能auto_vacuum模式之后，一个数据库在删除数据后留下的空闲页就可能会被回收，从而缩小数据库文件，而不用使用VACUUM命令重新构建整个数据库。然而，使用auto_vacuum模式将会导致额外的数据库文件碎片。并且使用auto_vacuum模式不会压缩部分数据库的填充页而VACUUM命令则会（压缩）。

3、expression

expr.gif

这部分有别于其它部分。 本文档的其它大多数部分都在讲特定的 SQL 命令。 本部分所讲的不是一个单一的命令，而是“表达式”， 它经常用作其它命令的一部分来使用。

SQLite 有如下二元运算符，根据其优先级从高到低有：
||
* / %
+ -
<< >> & |
< <= > >=
= == != <> IS IS NOT IN LIKE GLOB MATCH REGEXP
AND
OR
以下是支持的一元运算符：
- + ~ NOT

COLLATE 运算符可以被看作是一个后置的一元运算符，除了“～”，它比一元运算符和二元运算符优先级更高。

"+"是no-op类一元运算符。 它可以被应用于字符串、数字、BLOB或NULL，并永远返回跟它作用的数值相同的结果。

两种变体的等号运算符和不等号运算符。等可以是 = 或 ==。 不等操作符有 != 或 <>。 || 运算符为“连接符”，它将两个字符串连接到一起。 % 输出其左边的数除以右面数后的余数。

除 || 之外， 任何二元操作符的结果都是一个数值型的值或者NULL。 || 返回text类型值或者NULL。

IS和IS NOT运算符，在一个或者两个操作数是NULL的时候，其和＝与!=类似。IS和IS NOT运算符的结果只能是0或1，不会是NULL。

---- ----
?NNN 问号后跟一个数字 NNN 用于存放第 NNN 个参数。NNN 必须在 1 到 SQLITE_MAX_VARIABLE_NUMBER 之间。
? 只有一个问号，它将用于存放紧跟其后的一个未用到的参数。
:AAAA 冒号后面跟一个标志符，用于存放名字为 AAAA 的变量。 命名的变量也会被编号，编号将使用第一个未被使用的编号。 为避免混淆，最好不要混用名字和编号作为参数。
@AAAA 一个 @ 符号等价于一个冒号。
$AAAA 美元符号后跟一个标志符也会存放名字为 AAAA 的参数。 在这种情况下，参数名字中可以出现很多“::”，并且，在“(…)”后缀中可以包含任何文本。 该语法是由 TCL 语言中相同的法而来的。

未使用 sqlite3_bind 来赋值的参数则认为是 NULL。

LIKE 操作符会作一个模式匹配比较。 它右边是一个匹配模式，左边包含被匹配的字符串。 在匹配模式中，百分号 % 会匹配字符串中任意 0 个或多个字符。 一个下划线 _ 符号仅匹配一个任意的字符。 除此之外，其它的任何字符均只匹配它们自己 （在不区分大小写的环境下可能会匹配与之对应的大、小写字母）。 （也就是：SQLite中不区分大小写仅对 7 比特的拉丁字符有效， 对于 8 位的 iso8859 字符集UTF8字符则是区分大小写的）。 例如：表达式 ‘a’ LIKE ‘A’ 是 TRUE 但 ‘æ’ LIKE ‘Æ’ 则是 FALSE)。

若有可选的 ESCAPE 子句，那么 ESCAPE 关键字后的表达式必须是一个单个的字符（叫做转义字符）。 该字符可以用于 LIKE 模式字符串中来体现百分号或下划线。 转义字符后面的百分号或下划线均分别保持它们原来的意思。 中缀的 LIKE 操作符是使用 like(X,Y) 函数实现的。

中缀操作符 LIKE 是通过调用用户函数 like(X,Y) 实现的。

GLOB 操作符与 LIKE 类似，但它使用 Unix 通配符的的文件匹配语法。 并且，与 LIKE 不同， GLOB 是大小写敏感的。GLOB 和 LIKE 都可以用 NOT 关键字对匹配结果取反。 中缀的 GLOB 操作符是通过调用 用户函数 glob(X,Y) 实现的，可以通过该函数对其进行重载。

REGEXP 操作符是使用 regexp() 用户函数的一个特殊语法。 默认情况下， regexp() 用户函数没有定义，所以使用 REGEXP 将会出错。 如果在运行时增加一个名为 regexp 的用户定义函数， 则使用该操作符时将使用此函数来实现 REGEXP 功能。

MATCH 操作符是使用 match() 用户函数的一个特殊语法。 默认的 match() 函数实现只会引发一个异常，从而没多大用处。 但可以通过扩展该函数来实现更有用的逻辑。

一个CASE表达式的规则与IF-THEN-ELSE语法类似

在CASE主键和第一个WHEN主键之间的可选表达式被称为‘base’表达式。CASE有两种基本类型：有‘base’表达式和没‘base’表达式。

如果CASE表达式没有ELSE表达式，且没有WHEN表达式为true，则CASE的结果为NULL

WHEN模块中的结果如果是NULL，表示假。

‘base’表达式和WHEN表达式的比较，COLLAT、亲和类型和NULL处理规则相当于“ ‘base’表达式＝WHEN表达式”

如果‘base’表达式为NULL，则CASE表达式执行ELSE部分，如果没有ELES部分，则CASE表达式返回值为NULL

e.g

  • CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END
Left operand is NULL Right operand contains NULL Right operand is an empty set Left operand found within right operand Result of IN operator Result of NOT IN operator
no no no no false true
does not matter no yes no false true
no does not matter no yes true false
no yes no no NULL NULL
yes does not matter no does not matter NULL NULL
Affinity of type-name Conversion Processing
NONE 值将会被转换为BLOB类型。在这个过程中，先将值转换为数据库编码的TEXT类型，然后再将结果的字节解析成BLOB。
TEXT 将BLOB类型值转换为TEXT类型，字节序列将通过数据库编码成TEXT类型；将INTEGER或REAL值转换为TEXT是通过sqlite3_snprintf()
REAL 当将BLOB值转换为REAL的时候，是先将值转换为TEXT类型；当将TEXT转换为REAL时，可以从TEXT中提取最长的可能被解释为REAL的前缀，忽略其余的值。TEXT中的任何前导空格将被忽略，当从TEXT到REAL。如果没有前缀可以被解释为REAL，转换的结果是0。
INTEGER 当将BLOB值转换为INTEGER的时候，是先将值转换为TEXT类型；当将TEXT转换为INTEGER时，可以从文本值中提取可以被解释为整数的最长可能的前缀，忽略其余的值。当从文本转换为整数时，文本值中的任何前导空格将被忽略。如果没有前缀可以被解释为整数，转换的结果是0。CAST只理解十进制整数，所以十六进制整数只停在“0x”前缀的“X”处，从而使CAST的结果总是零。将一个实值转换为整数，结果是在实值和零之间的整数，该值与实值最接近。如果实值大于最大可能的有符号整数（+ 922337203685477580），则结果是最大可能的有符号整数，如果实值小于可能的有符号整数（- 922337203685477580），则结果是至少可能的有符号整数。
NUMERIC 将TEXT或BLOB转换为NUMERIC(数值)首先将强制转换为REAL，然后当结果仅从REAL到INTEGER转换为无损可逆时，再将结果转换成INTEGER。这是SQLite中NUMERIC和INTEGER亲和表现不同 的唯一语境；将REAL或INTEGER转换为NUMERIC是no-op（无操作）的，即使REAL能无损转换为INTEGER。
上一篇 下一篇

猜你喜欢

热点阅读