SQLite学习笔记

SQLite学习笔记

 

(未完成,待修改)

 

 

 

一、无库无表SQL

1. 无库连接sqlite3

(1) Windows提示符

>sqlite3

SQLite version 3.6.22

Enter ".help" for instructions

Enter SQL statements terminated with a ";"

sqlite>

(2) adb shell(模拟器或手机)

>adb shell

# sqlite3

sqlite3

SQLite version 3.6.22

Enter ".help" for instructions

Enter SQL statements terminated with a ";"

sqlite>

2. 计算字段(通过运算符计算得到的字段,不存在于实际表中)

(1) 常量

sqlite> select 1;

1

(2) 四则运算

sqlite> select 3 + 4, 3 - 4, 3 * 4, 3 / 4, 3 % 4;

7|-1|12|0|3

sqlite> select 3 + 4 * 2;

11

sqlite> select (3 + 4) * 2;

14

sqlite> select -(1+0), +(2+0), ~(2+0);

-1|2|-3

sqlite> select -("hello"), +("hello"), ~("hello");

0|hello|-1

(3) 别名(导出列),相当于临时变量的赋值。

sqlite> select 3 * result, 2 + result from (select 2 as result);

6|4

(4) 拼接(相当于MySQL的Concat())

sqlite> select 'hello' || ' ' || 'world!';

hello world!

sqlite> select 3 || 1;

31

sqlite> select LTrim(' hello') || RTrim(' world! ');

hello world!

(5) 算术比较与逻辑与或运算

sqlite> select 3 > 2, 3 < 2, 3 = 3, 3 == 3;

1|0|1|1

sqlite> select 1 != 0, 1 <> 0;

1|1

sqlite> select 1 is null, 1 is not null;

0|1

sqlite> select null is null, null is not null, null == null, null != null;

1|0||

sqlite> select (null == null) is null;

1

sqlite> select length(null) is null;

1

sqlite> select 1 = 1, 1 == 1;

1|1

sqlite> select 3 between 4 and 5, 4 between 4 and 5;

0|1

sqlite> select 4 in (1, 3, 4), 0 in (1, 3, 4), 2 not in (1, 3, 4);

1|0|1

sqlite> select (1 >= 2) and (1 <= 3), (1 >= 2) & (1 <= 3);

0|0

sqlite> select (1 < 2) or (1 > 3), (1 < 2) | (1 > 3);

1|1

sqlite> select 1 < 2, not (1 < 2);

1|0

sqlite> select case when 1 > 2 then 3 else 4 end;

4

sqlite> select case 2 when 1 then 3 else 4 end;

4

 

(6) 使用通配符的字符串匹配(MySQL没有glob。like通配符:%匹配0个或0个以上字符,_匹配1个字符,大小写不敏感;glob通配符:大小写敏感)

sqlite> select 'hello' like 'ell', 'hello' like 'ell%', 'hello' like '%ell%', 'hello' like 'he%', 'hello' like 'h%o';

0|0|1|1|1

sqlite> select 'hello' like '_ello', 'hello' like 'hel_';

1|0

sqlite> select 'Apple' like 'apple', 'Apple' like 'AppL_';

1|1

sqlite> select like('Apple', 'apple');

1

sqlite> select like('App%', 'apple');

1

sqlite> select 'hello' glob 'he*', 'hello' glob 'hell?';

1|1

sqlite> select glob('he*', 'hello'), glob('hell?', 'hello');

1|1

(7) 使用正则表达式的字符串匹配(默认sqlite不实现REGEXP用户函数)

sqlite> select 'a' REGEXP '^a$';

Error: no such function: REGEXP

3. 使用数据处理函数的计算字段(通过函数计算得到的字段,不存在于实际表中)

(1) 文本处理(MySQL使用SubString,不支持MySQL的Soundex(),Left(),Right(),Locate())

sqlite> select str, Upper(str), Lower(str), Length(str) from (select 'Hello, World!' as str);

Hello, World!|HELLO, WORLD!|hello, world!|13

sqlite> select str, LTrim(str), RTrim(str), Trim(str) from (select '  Hello, World!  ' as str);

Hello, World!  |Hello, World!  |  Hello, World!|Hello, World!

sqlite> select str, LTrim(str, 'x'), RTrim(str, 'x'), Trim(str, 'x') from (select 'xxxHello, World!xxx' as str);

xxxHello, World!xxx|Hello, World!xxx|xxxHello, World!|Hello, World!

sqlite> select soundex('Y. Lie');

Error: no such function: soundex

sqlite> select SubStr('Hello, World!', 2, 3);

ell

sqlite> select SubStr('Hello, World!', -1, 4);

!

sqlite> select SubStr('Hello, World!', 2, -2);

H

(2) 日期与时间处理(MySQL使用Now())

sqlite> select DateTime('now');

2012-05-07 05:34:36

sqlite> select strftime('%s', 'now');

1336369355

(3) 数值处理(MySQL使用Rand(),不支持MySQL的Sin(),Cos(),Tan(),Exp(),Mod(),Pi(),Sqrt())

sqlite> select Abs(-1.2);

1.2

sqlite> select random();

1872328235102936735

(4) 聚集函数(只有Min()和Max()可用于不同列,AVG(),COUNT(),SUM()则不可以用在这里)

sqlite> select min(5, 6, 7), max(5, 6, 7);

5|7

(5) like()与glob()的字符串匹配

见前,略。

 

 

 

(TODO:)

 

 

 

X:其它:

(8) 全文搜索和MATCH(MySQL则需要用FULLTEXT()在CREATE TABLE中指定列,用Match()指定被搜索列,用Against()指定表达式)

>sqlite3 test2.sqlite

SQLite version 3.6.22

Enter ".help" for instructions

Enter SQL statements terminated with a ";"

sqlite> .tables

sqlite> CREATE VIRTUAL TABLE mail USING fts3(subject, body);

sqlite> .tables

mail           mail_content   mail_segdir    mail_segments

sqlite> INSERT INTO mail(docid, subject, body) VALUES(1, 'software feedback', 'found it too slow');

sqlite> INSERT INTO mail(docid, subject, body) VALUES(2, 'software feedback', 'no feedback');

sqlite> INSERT INTO mail(docid, subject, body) VALUES(3, 'slow lunch order',  'was a software problem');

sqlite> SELECT * FROM mail WHERE subject MATCH 'software';

software feedback|found it too slow

software feedback|no feedback

 
 
-----------------------------
需要注意的问题:
 
* 命令行命令可以缩略,如.sc等效于.schema
* 命令行命令千万不能以分号结束,如.schema words不能写成.schema words;,得到的结果是不同的
* 主键约束(主键primary key必须是唯一的)可以写在列中,也可以单独写在一个列的位置:
CREATE TABLE t3(id integer primary key, a integer);
CREATE TABLE t2(id integer, a integer, primary key(id) );
但对于自增长的主键,只能用前者的语法:
create table t5(id integer primary key autoincrement, a integer);
* 全文搜索的ft3表使用primary key autoincrement是不起作用的(可以在CREATE TABLE中指定,但插入数据后总为null)。可考虑用rowid或docid代替。
 
----------------------------- 
(20140903)
Android 4 collation问题
Android 4支持一种特殊的排序方式COLLATE LOCALIZED,这种方式在低版本的Android和sqlite官方版(JDBC驱动)会报错(可能是因为需要加载某个特定的扩展)。
CREATE TABLE test (id INTEGER PRIMARY KEY, data TEXT COLLATE LOCALIZED);
SELECT data FROM test ORDER BY data COLLATE LOCALIZED ASC
 
  其他高级用法:
请参考tutorialspoint的教程:
 
 
 
 
------------------------------
 
参考链接收集
1. 2010年SQLite学习笔记之一
(20131211)
2. sqlite查询优化
 
(20140704)
3. Sqlite语法
http://www.cnblogs.com/helloandroid/articles/2150272.html
4. Sqlite基础教程与高级用法
http://www.tutorialspoint.com/sqlite/index.htm
 
 
 ------------------------------------------
使用技巧摘录(可能只适用于jdbc版不适用于Android版)
(20141211)
一、 调节数据库缓存大小
	private void setPragmaCacheSize(Connection connection) {
	    Statement s = null;
	    try {
	    	s = connection.createStatement();
	    	s.execute("PRAGMA cache_size = " + DB_CACHE_SIZE + ";");
	    } catch (SQLException e) {
	    	LogUtil.error(logger, "PRAGMA cache_size error", e);
	    } finally {
	    	try {
	    		if (s != null) {
	    			s.close(); 
	    		}
	    	} catch (SQLException e2) { 
	    		e2.printStackTrace(); 
	    	}
	    }
	}
使用:
			con = DriverManager.getConnection(url);
			setPragmaCacheSize(con);
  
参考自:
* jawjaw
https://code.google.com/p/jawjaw/
 
 
 
 

(TODO:)

 

 

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值