【数据库】适用于SQLite的SQL语句（一）

一、统计函数

avg(X) ： 计算平均值，针对整数；
count(*)：统计总行数；
count(X)：统计X在组中不为NULL的行数；
group_concat(X)：返回X非NULL值串联成的字符串，以逗号‘,’分隔
group_concat(X,Y)：返回X非NULL值串联成的字符串，以‘Y’分隔
max(X)：返回最大值
min(X)：返回最小值
sum(X)：如果所有非NULL输入均为整数，则sum的结果为整数值。否则返回一个浮点值。如果计算时整数溢出，Sum会抛出“整数溢出”异常；
total(X)：返回的结果始终是浮点值；并且不会抛出“整数溢出”异常。

二、表TABLE

1、创建表CREATE TABLE

CREATE TABLE t(x INTEGER PRIMARY KEY , y, z);
CREATE TABLE t(x INTEGER PRIMARY KEY ASC , y, z);
CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x ASC));
CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x DESC));

2、更改表ALTER TABLE

更改表名：ALTER TABLE 旧表名 RENAME TO 新表名;
更改列名：ALTER TABLE 表名 RENAME COLUMN 旧列名 TO 新列名;
添加列：ALTER TABLE 表名 ADD 列名 类型;

3、删除表DROP TABLE

三、分析表ANALYZE

执行ANALYZE命令，会将分析结果保存在表sqlite_stat1中。

ANALYZE;
SELECT * FROM sqlite_stat1;
tbl|idx|stat
test1||1
tt||2

四、附加数据库 ATTACH DATABASE

ATTACH DATABASE语句将另一个数据库文件添加到当前数据库连接中。可以使用DETACH DATABASE命令分离以前附加的数据库文件。

添加数据库：ATTACH DATABASE 附加数据库文件名 AS 数据库别名;
分离数据库：DETACH DATABASE数据库别名;

例子：

attach 'test1.db' as test1;

如果“附加数据库文件名”存在则连接它，如果不存在则创建；连接后可以通过.database命令查看；

.database
main: /home/workspace/test/sql/test.db
test1: /home/workspace/test/sql/test1.db

使用，通过 数据库别名.表名 来使用附加数据库中的表；

select * from test1.test;
1|Xiao|20
2|Xiao1|21

五、事务

开始事务：BEGIN TRANSACTION
提交事务：END TRANSACTION、COMMIT
回滚事务：ROLLBACK TRANSACTION
保存点：SAVEPOINT 是一种创建事务的方法，类似于 BEGIN和COMMIT，不同之处在于SAVEPOINT和RELEASE可以命名并且可以嵌套。

六、核心函数

abs(X)：返回X列的绝对值；
changes()：返回最近更改（插入、删除、更新）的行数；
char(X1,X2,…,XN)：返回以整数列X1…XN为unicode编码字符串；
注：下面例子中前两个值为18、19，对应的unicode字符不能显示，第三个为88，对应x

select char(age) from tt ;


X

coalesce(X,Y,…)：返回第一个不为NULL的副本；
glob(X,Y)：和LIKE，模糊查找；
hex(X)：将X视为BLOB，并返回一个字符串，该字符串是该blob内容的大写十六进制表示形式；
ifnull(X,Y)：返回XY中第一个不为NULL的副本，等于只有两个参数的coalesce
instr(X,Y)：在字符串X中查找Y
last_insert_rowid()：然会最后插入的行ID；
length(X)：对于字符串返回字符个数；对于BLOB返回字节数；对于整数返回字符串形式的长度；
like(X,Y)：模糊查找
like(X,Y,Z)：模糊查找
likelihood(X,Y)：似然函数，用于统计学，最大似然估计；
likely(X)：优化编译，将X为true时，要执行的代码编译到前面
load_extension(X)
load_extension(X,Y)
lower(X)：小写
ltrim(X)
ltrim(X,Y)：函数返回一个字符串，该字符串是通过从X的左侧删除出现在Y中的任何和所有字符形成的。如果省略Y参数，ltrim 将从X的左侧删除空格。
max(X,Y,…)：最大值
min(X,Y,…)：最小值
nullif(X,Y)：参数相同返回NULL，参数不同，返回第一个
printf(FORMAT,…)：格式化输出

select printf("age = %d", age) from test;
age = 18
age = 19

quote(X)：返回结果的文本格式，如果是字符串返回的文本带单引号；
random()：伪随机函数；
randomblob(N)：返回N个字节伪随机BLOB值；
replace(X,Y,Z)：使用Z替换X中的Y；
round(X)：返回X四舍五入的整数；
round(X,Y)：返回X四舍五入小数点后Y位的浮点数；
rtrim(X)
rtrim(X,Y)：和ltrim类似，这次从右侧删除；
soundex(X)：返回soundex编码？
sqlite_compileoption_get(N)：返回用于构建SQLite的第N个编译时选项
sqlite_compileoption_used(X)：返回是否使用X作为编译选项；
sqlite_offset(X)：没理解
sqlite_source_id()：返回源码版本；
sqlite_version()：SQLite库的版本
substr(X,Y)
substr(X,Y,Z)：从X的第Y个字符开始的Z长度的字符串；
total_changes()：自打开当前数据库连接以来，更改过的行数；
trim(X)
trim(X,Y)：等于ltrim+rtrim，从两端删除；
typeof(X)：返回类型的字符串形式；
unicode(X)：和char相反，返回字符串第一个字符对应的unicode值
unlikely(X)：优化编译，将X为false时，要执行的代码编译到前面
upper(X)：返回大写；
zeroblob(N)：返回N个值为0的BLOB类型值

七、索引INDEX

创建索引是为了给数据库内部使用，加快查询；不是直接给用户使用。

1、创建索引：CREATE INDEX

创建简单索引

CREATE INDEX index_name ON table_name (column_name);

创建唯一索引

CREATE UNIQUE INDEX index_name ON table_name (column_name);

2、查看索引：

.indexes
haha

.headers on
SELECT * FROM sqlite_master WHERE type = 'index';
type|name|tbl_name|rootpage|sql
index|haha|tt|6|CREATE INDEX haha on tt(age)

3、使用索引 INDEXED BY

在DELETE、SELECT或UPDATE上强制使用索引；INDEXED BY短语是SQLite的扩展，不能移植到其他SQL数据库引擎中。

4、删除索引：DROP INDEX

DROP INDEX index_name;

八、触发器TRIGGER

当对数据库执行插入INSERT、更新UPDATE、删除DELETE操作时，可以自动触发关联动作。

1、创建触发器CREATE TRIGGER

官方demo

// 创建更新触发器
CREATE TRIGGER update_customer_address UPDATE OF address ON customers 
  BEGIN
    UPDATE orders SET address = new.address WHERE customer_name = old.name;
  END;

// 执行更新操作
UPDATE customers SET address = '1 Main St.' WHERE name = 'Jack Jones';

// 下面语句会自动执行
UPDATE orders SET address = '1 Main St.' WHERE customer_name = 'Jack Jones';

2、删除触发器DROP TRIGGER

DROP TRIGGER trigger-name;