0 main /root/sqlite-autoconf-3080403/tim
sqlite>
sqlite> .tables
test test_auto_incre
sqlite>
3.3，建表
[root@localhostsqlite-autoconf-3080403]# sqlite3 tim
SQLite version 3.8.4.32014-04-03 16:53:12
Enter ".help" forusage hints.
sqlite> .tables
test_auto_incre
sqlite> create tabletest(id integer default 0, username text);
sqlite> .tables
test test_auto_incre
sqlite>
3.4，插入数据记录
sqlite> inser intotest(id,username)values(1,'test');
Run Time: real 0.000 user0.000000 sys 0.000000
Error: near "inser":syntax error
sqlite> insert intotest(id,username)values(1,'test');
Run Time: real 0.102 user0.000000 sys 0.002000
sqlite> select * from test;
id username
---------- ----------
1 test
Run Time: real 0.000 user0.000000 sys 0.000000
sqlite> insert intotest(id,username)values(2,'tman');
Run Time: real 0.070 user0.001000 sys 0.000000
sqlite> select * from test;
id username
---------- ----------
1 test
2 tman
Run Time: real 0.000 user0.000000 sys 0.000000
3.3，删除数据
sqlite> delete from testwhere id=1;
Run Time: real 0.081 user0.000000 sys 0.002000
sqlite> select * from test;
id username
---------- ----------
2 tman
Run Time: real 0.001 user0.001000 sys 0.000000
sqlite>
3.4，更新数据表记录
sqlite> update test setusername='tmanupdate' where id=2;
sqlite>
sqlite>
sqlite> select * from test;
2|tmanupdate
sqlite>
查询SQllite的VIRTUAL Table碰到问题：
CREATE VIRTUAL TABLEfts_message_table_0 USING fts3
(usernameid INTEGER DEFAULT 0,MesLocalID INTEGER, CreateTime INTEGER DEFAULT 0, Message TEXT,
reservedInt INTEGER DEFAULT 0,reservedText TEXT, PRIMARY KEY(usernameid,MesLocalID,CreateTime),tokenize=one_or_binary_tokenizer);
sqlite> select *from fts_message_table_0 where fts_message_table_0 match '27';
Error: unknown tokenizer:one_or_binary_tokenizer
sqlite>
4，修改表结构
4.1 添加一个字段
sqlite> .timer on
sqlite> ALTER TABLE test ADDCOLUMN address varchar(20) ;
Run Time: real 0.068 user0.000000 sys 0.000000
sqlite>
4.2 删除列
添加一列address，然后删除它
sqlite> create table test(idinteger default 0, username text);
sqlite> select * from test;
sqlite> insert into testselect 1,'a';
sqlite> insert into testselect 2,'b';
sqlite> alter table test addcolumn address varchar(20);
sqlite> insert into testselect 3,'c','caddre';
sqlite> select * from test;
1|a|
2|b|
3|c|caddre
sqlite>
sqlite> alter table test dropcolumn address;
Error: near "drop":syntax error
sqlite>
不识别drop操作标示符，怎么办？去官网看到如下信息：
SQLite supports a limited subsetof ALTER TABLE. The ALTER TABLE command in SQLite allows the user to rename atable or to add a new column to an existing table. It is not possible to renamea column, remove a column, or add or remove constraints from a table.
Sqlite 其实是不支持drop column的方法来删除列的
(1).根据原表创建一张新表
create table test_tmp(id integerprimary key, username text default ‘’);这个表没有列address了。
(2).从原来表把数据录入新临时表，
sqlite> insert into test_tmpselect id,username from test;
sqlite> select * fromtest_tmp;
1|a
2|b
3|c
sqlite>
(3).删除原表
sqlite> drop table if existstest;
(4).将新临时表重名为旧表的名称
sqlite> alter table test_tmprename to test;
sqlite> select * from test;
1|a
2|b
3|c
sqlite>
这样就实现了，删除一个字段address，得到了我们想要的drop column的目的了。
4.3，索引管理
创建普通索引
sqlite> create indexidx_username on test(username);
创建复合索引
sqlite> alter table test addcolumn addr varchar(60) not null default '';
sqlite> create index idx_addr