[Sqlite]-->嵌入式数据库的安装、建库、建表、更新表结构以及
引子: 1,Sqlite在Windows、Linux 和 Mac OS X 上的安装过程 简介: SQLite是实现了SQL 92标准的一个大子集的嵌入式数据库.其以在一个库中组合了数据库引擎和接口,能将所有数据存储于单个文件中而著名.我觉得SQLite的功能一定程度上居于MySQL和PostgreSQL之间.尽管如此,在性能上面,SQLite常常快2-3倍(甚至更多).这利益于其高度调整了的内部架构,因为它除去了服务器端到客户端和客户端到服务器端的通信。 而令人印象深刻的特点是你可将你的整个数据库系统放在其中.利用非常高效的内存组织,SQLite只需在很小的内存中维护其很小的尺寸,远远比其它任何数据库系统都小.这些特点使得其成为在需要高效地应用数据库的任务中一个非常方便的工具. SQLite优势: 一.除了速度和效率,SQLite还有其它好多的优势使得其能成为许多任务中一个理想的解决方案.因为SQLite的数据库都是简单文件,因此无须一个管理队伍花时间来构造复杂的权限结构来保护用户的数据库.因为权限通过文件系统自动进行.这也同时意味着(数据库空间的大小只与环境有关,与本身无关)无段特殊的规则来了解用户磁盘空间.用户可以从创建他们想要的任意多的数据库和对其对这些数据库的绝对控制权而得到好处. 二.数据库就是一个文件的事实使用SQLite可以轻易地在服务器间移动.SQLite也除去了需要大量内存和其它系统资源的伺候进程.即使当数据库在大量地使用时也是如此.
下载地址:Wgethttp://www.sqlite.org/2014/sqlite-autoconf-3080403.tar.gz 开始安装: tar xvfzsqlite-autoconf-3080403.tar.gz cd sqlite-autoconf-3080403 ./configure --prefix=/usr/local make make install 2,登录 不过因为一般centos的linux系统自带了版本比较低的sqlite,所以你不安装,直接运行sqlite3就可以登录进去: [root@mysqlvm2 ~]# sqlite3 SQLite version 3.3.6.32014-04-03 16:53:12 Enter ".help" forinstructions Enter SQL statements terminatedwith a ";" sqlite> .exit [root@mysqlvm2 ~]# 为了使用新版本,我们这里用自己的全路径或者在设置PATH路径 [root@mysqlvm2 ~]#/root/sqlite-autoconf-3080403/sqlite3 SQLite version 3.8.4.32014-04-03 16:53:12 Enter ".help" forusage hints. Connected to a transientin-memory database. Use ".open FILENAME"to reopen on a persistent database. sqlite> 3,创建数据库和表 3.1,默认登录 默认登录时,临时操作的是临时内存库,等退出的时候,创建的临时库以及表以及数据会被释放的,使用.databases命令展示的时候会看到file列下面是null的没有数据库记录,如下所示: [root@localhostsqlite-autoconf-3080403]# sqlite3 SQLite version 3.8.4.32014-04-03 16:53:12 Enter ".help" forusage hints. Connected to a transientin-memory database. Use ".open FILENAME"to reopen on a persistent database. sqlite> .databases seqnamefile --- ------------------------------------------------------------------------- 0main sqlite> sqlite> .tables sqlite> create table t1(idint); sqlite> insert into t1 select1; sqlite> insert into t1 select2; sqlite> select * from t1; 1 2 sqlite> .table t1 sqlite> 我们创建了一张表t1,并且录入了数据,退出后重新登录,表t1已经不存在了,如下所示: [root@localhostsqlite-autoconf-3080403]# sqlite3 SQLite version 3.8.4.32014-04-03 16:53:12 Enter ".help" forusage hints. Connected to a transientin-memory database. Use ".open FILENAME"to reopen on a persistent database. sqlite> .table sqlite> .tables sqlite> select * from t1; Error: no such table: t1 sqlite> 3.2,创建数据库 直接登录时候带上数据库名tim,就表示建库tim,注意:这时tim数据库确实已经创建好了注意:这时test.db数据库确实已经创建好了但是这时还是看不到这个数据库,还是个临时的,所以要输入SQL命令 ,等表格创建完后关闭sqlite3退出来的时候,就可以看到这个库了。 [root@localhost ~]# sqlite3 tim SQLite version 3.8.4.32014-04-03 16:53:12 Enter ".help" forusage hints. sqlite> .schema sqlite> CREATE TABLEtest_auto_incre(id INT AUTO_INCREMENT primary key,msg_to VARCHAR(20),TYPECHAR(1),create_date DATE ); sqlite> .schema CREATE TABLE test_auto_incre(idINT AUTO_INCREMENT,TYPE CHAR(1),create_date DATE,PRIMARYKEY (id) ); sqlite> .tables test_auto_incre sqlite> sqlite> exit ...> ; Error: near "exit":syntax error sqlite> 用.exit或者.quit退出来,或者使用CTRL+D按钮退出sqlite命令行操作界面。 [root@localhostsqlite-autoconf-3080403]# ll -t total 11152 -rw-r--r-- 1 rootroot 3072 Aug 27 16:52 tim 再次登录进去查看已经存在的库以及表: sqlite> .databases seqnamefile --- ------------------------------------------------------------------------- 0main/root/sqlite-autoconf-3080403/tim sqlite> sqlite> .tables testtest_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 testtest_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,'test'); Run Time: real 0.102 user0.000000 sys 0.002000 sqlite> select * from test; idusername ---------- ---------- 1test 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; idusername ---------- ---------- 1test 2tman 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; idusername ---------- ---------- 2tman 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_addron test(addr,username); 创建唯一键索引 sqlite> create unique indexidx_unaddr on test(addr); 测试是不是真的唯一约束,添加addr字段值都为’a1’,第一次成功,第二次失败,查看执行结果,如下: sqlite> insert into testselect 4,'a','a1'; sqlite> insert into testselect 5,'a1'; Error: UNIQUE constraint failed:test.addr sqlite> 报错了,验证了,唯一约束idx_undaddr已经生效。 PS:Sqlite不支持聚集索引,android默认需要一个_id字段,这保证了你插入的数据会按“_id”的整数顺序插入,这个integer类型的主键就会扮演和聚集索引一样的角色。所以不要再在对于声明为:INTEGER PRIMARY KEY的主键上创建索引。 4.4,自增主键 CREATE TABLE test_auto_incre(idINT AUTO_INCREMENT,PRIMARYKEY (id) ); 4.5查看所有表结构 方法一: sqlite> .schema CREATE TABLE "test"(idinteger primary key,username text default ‘’,addr varchar(60) not null default''); CREATE INDEX idx_username ontest(username); CREATE INDEX idx_addr ontest(addr,username); CREATE UNIQUE INDEX idx_unaddron test(addr); CREATE TABLE test_auto_incre(idINT AUTO_INCREMENT,PRIMARYKEY (id) ); sqlite> 方法二: sqlite> .header on –加上显示的表头列名 sqlite> select * fromsqlite_master; type|name|tbl_name|rootpage|sql table|test|test|4|CREATE TABLE"test"(id integer primary key,addrvarchar(60) not null default '') index|idx_username|test|2|CREATEINDEX idx_username on test(username) index|idx_addr|test|5|CREATEINDEX idx_addr on test(addr,username) index|idx_unaddr|test|6|CREATEUNIQUE INDEX idx_unaddr on test(addr) table|test_auto_incre|test_auto_incre|7|CREATETABLE test_auto_incre(id INT AUTO_INCREMENT,PRIMARY KEY (id) ) index|sqlite_autoindex_test_auto_incre_1|test_auto_incre|8| sqlite> 4.6查看当前库下所有的表名字 sqlite> .table testtest_auto_incre sqlite> .tables testtest_auto_incre 4.7查看某一张表的结构,比如test_auto_incre表: 方法一:查询系统表 select * from sqlite_masterwhere name="test_auto_incre"; sqlite> .header on –加上显示的表头列名 sqlite> select * fromsqlite_master where name="test_auto_incre"; type|name|tbl_name|rootpage|sql table|test_auto_incre|test_auto_incre|7|CREATETABLE test_auto_incre(id INT AUTO_INCREMENT,PRIMARY KEY (id) ) sqlite> 方法二:使用.schema命令 sqlite> .schematest_auto_incre CREATE TABLE test_auto_incre(idINT AUTO_INCREMENT,PRIMARYKEY (id) ); sqlite> sqlite> .schema test CREATE TABLE "test"(idinteger primary key,addr varchar(60) not nulldefault ''); CREATE INDEX idx_username ontest(username); CREATE INDEX idx_addr ontest(addr,username); CREATE UNIQUE INDEX idx_unaddron test(addr); sqlite> 4.8创建虚拟表 CREATE VIRTUAL TABLEfts_message_table_0_tmp USING fts3(usernameid INTEGER DEFAULT 0,MesLocalIDINTEGER,reservedInt INTEGERDEFAULT 0,PRIMARYKEY(usernameid,CreateTime)); 4.9创建视图 CREATE TABLE test(id INTAUTO_INCREMENT,PRIMARY KEY(id) ); INSERT INTOtest(msg_to,TYPE,create_date) SELECT 'm1','2014-08-18 12:12:23'; INSERT INTOtest(msg_to,'b','2014-08-18 13:12:23'; INSERT INTOtest(msg_to,'2014-08-18 10:12:23'; INSERT INTOtest(msg_to,create_date) SELECT 'm2','2014-08-20 12:12:23'; INSERT INTOtest(msg_to,create_date) SELECT 'm3','2014-08-19 12:12:23';
5 Sqlite数据导出导入操作 5.1数据导入.import命令 命令:.import FILE TABLE Import data from FILEinto TABLE sqlite> .schema test CREATE TABLE test(id integerdefault 0,username text); sqlite> 注意1:不要忘了开头的点.import 注意2:这条语句不能用分号结束.非SQL不需要分号结束. 注意3:需要查看默认的分隔符separator.必须一致,查看分隔符使用命令.show,如果不一致可能导致sqlite字段分割错误,利用”.separator”命令转换sqlite默认分隔符,比如.separator ","这一句就将分隔符改变为逗号,与预导入数据一致才能顺利导入,如下所示: sqlite> .show echo:off eqp: off explain: off headers: off mode:list nullvalue: "" output: stdout separator: "|" stats: off width: sqlite> 分隔符不是”|”,而文件test.csv的分隔符是”,”,所以要先转换分隔符,在导入: sqlite> .separator "," sqlite> .table t2testtest_auto_incre sqlite> .import test.csv test 导入成功,查看结果 sqlite> select * from test; 1,a1 2,a2 3,a3 4,a4 sqlite> .separator "|" sqlite> select * from test; 1|a1 2|a2 3|a3 4|a4 sqlite> 命令:.output FILENAME Send outputto FILENAME
然后输入sql语句,查询出要导的数据.查询后,数据不会显示在屏幕上,而直接写入文件. 结束后,输入 sqlite> .output stdout 将输出重定向至屏幕,如下所示: [root@localhostsqlite-autoconf-3080403]# [root@localhostsqlite-autoconf-3080403]# sqlite3 ti SQLite version 3.8.4.32014-04-03 16:53:12 Enter ".help" forusage hints. sqlite> sqlite> .output test.sql sqlite> select * from test; sqlite> .exit [root@localhostsqlite-autoconf-3080403]# more test.sql 1|a1 2|a2 3|a3 4|a4 [root@localhostsqlite-autoconf-3080403]# 5.3使用5.2导出的文件恢复test表 先登录删除test表 [root@localhostsqlite-autoconf-3080403]# sqlite3 ti SQLite version 3.8.4.32014-04-03 16:53:12 Enter ".help" forusage hints. sqlite> select * from test; 1|a1 2|a2 3|a3 4|a4 sqlite> delete from test; sqlite> select * from test; sqlite> 再导入test.sql文件的数据,如下所示 sqlite> .show echo:off eqp: off explain: off headers: off mode:list nullvalue: "" output: stdout separator: "|" stats: off width: sqlite> .import test.sql test sqlite> select * from test; 1|a1 2|a2 3|a3 4|a4 sqlite> 查询数据,显示导入表test成功。 参考文档:http://www.w3cschool.cc/sqlite/sqlite-installation.html (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |