分类： SQLite/嵌入式数据库
2011-07-22 21:51:17
SQLite是一个自包含的(self-contained ),服务开销小、(serverless),零配置(zero-configration),支持事务(transactional)的数据库引擎，是一个源代码公开软件库。
安装步骤
下载SQLite源代码
sqlite.rar
复制sqlite-3.6.23.1.tar.gz到linux上的/usr/src目录
解压源代码tar -xvzf sqlite-3.6.23.1.tar.gz
进入源代码目录cd sqlite-3.6.23.1
配置编译与安装参数./configure --disable-tcl --prefix=/usr/local/sqlite
编译make
安装make install
sqlite3是一个实用工具 用来直接在数据库上输入与执行sql语句。可以使用sqlite3工具加上一个文件名连接一个数据库，如果文件名不存在，将自动创建一个数据库,然后出现sqlite3提示符，输入sql语言（使用分号结尾）,按回车可以执行sql语句。
每个sql语句使用分号(;)为结束符，如果省略分号，sqlite3将继续等待输入文本，并认为是一个sql语句。
大多数时候sqlite3是读取输入，分析输入，并执行sql语句。但是如果你的输入以.开始的命令，则是执行sqlite3自己的程序。“dot commands”常常用来改变查询输出的格式或者执行一个预先确定的sql语句可以输入.help来获取这些命令
sqlite系统的命令
格式：.commandname
.help
.tables --查看所有表名
.databse --查看数据库
.output filename --把查询结果输出到文件
.schema tablename --查表结构看
.show --查看 mode headers echo的值
.mode column
.headers on
Sqlite3可以使用不同的格式输出查询结果(如csv,column,html,insert,line,list,tabs,tcl),默认为list。
改变输出格式 各个字段的默认分隔符为”|”也可以是.separator命令改变各个字段的分隔符。
输出查询结果到文件默认情况下，查询输出为标准输出，可以使用.output,改变输出方向
查询数据库结构
.tables [tablename]显示数据库中的表名
.schema [tablename]显示数据库中表的结构
sqlite使用sql语句
sqlite创建表时可以不指定字段的数据类型，根据插入第一笔值来决定该字段数据类型
sqlite是一个轻量级数据库
sqlite字段的数据类型
null ：空类型 该字段可以不填充数据
数字 ：integer
float
sqlite中的表的主键一般要求是integer类型数据
如果integer类型字段作为主键，在插入值的时，可以不需要给出该字段的值，系统会根据上一笔记录该字段的值来确定本记录该字段的值
字符 ：char(n) text 文本类型
日期 ：datetime
BLOB ：大字段类型（一般存取图片 视频）
数据类型的亲和性
123
‘123’
可以将两种不同数据类型的字段相互赋值，系统会自动转换
数字字符
整数
blob
常用语句
create table tablname(id inetger primary key,name text,pid integer unique);
insert into tablname values(1,'aaa',123);
insert into tablname(name,pid) values('aaa',123);
select * from tablname
select * from tablname where id=1
select * from tablname where id==1
select * from tablname where id='1'
select * from tablname where id="1"
select * from tablname where id=='1'
select * from tablname where id=="1"
update tablname set name=newvalue
where id=2
delete from tablname where id=2
常用的函数
select abs(-1)
select max(pid) from tt2;
select min(pid) from tt2;
select avg(pid) from tt2;
select sum(pid) from tt2;
select count(*) from tt2;
select random();
select round(123.456,2);
select length('aaaa');
select substr('abcdefg',2,3);
select lower('SSSSS');
select upper('aaaa');
select typeof(id),typeof(name),typeof(pid) from tt2;
select typeof(1);
select last_insert_rowid()
select sqlite_version();
create table if not exists tt3(f2);
如果表tt3存在不需要再创建
时间函数
select date('now') --获取当前的日期
select date('2011-07-09');
select date('now','start of month','+1 month','-1 day');
--月份改变以后，是按照新的月份第一天计算的
select date('now','start of month','+1 month','+1 day');
select date('now','start of month','+3 month','+1 day');
select time('now','localtime');--获取本地当前时间
select datetime('now','localtime');
select strftime('%Y','now','localtime');--年
select strftime('%m','now','localtime');--月
select strftime('%d','now','localtime');--日
select strftime('%H','now','localtime');--小时
select strftime('%M','now','localtime');--分钟
select strftime('%S','now','localtime');--秒
select julianday('now')-julianday('2001-06-09');
备份与恢复
.backup [db] file备份数据库到一个文件
.restore [db] file从一个文件中恢复数据库
sqlite3 test.db 'select * from emp';
sqlite3 test.db .dump student
sqlite3 test.db '.dump' > emp.sql;--数据库备份
sqlite3 test.db < /home/lizhanglin/workspace/sqlite/emp.sql ;--数据库还原
sqlite3 test.db ".dump" | sqlite3 test2.db--等价于上面的两句话的效果
sqlite3 -html test.db "select * from emp";
SQLite的数据导入与导出
通常情况下，数据导入的基本步骤可分为三步：
1.确定导入的数据源，按固定格式存储的文本文件或SQL文件
vim student.txt
1,tom,23
2,jarry,34
3,jim,32
4,hand,23
5,merrym,32
2.依照导入的文件格式，确定目标数据表
sqlite3 test.db
create table student(id char(4),name char(10),age char(4));
3.执行.import导入命令，将数据导入到数据表中
.separator ","
.import student.txt student
注意事项：
1.用户准备导入的文件必须具备有一定的格式才可以，而且最重要的是文件的名字必须同表的名字一摸一样
2.在数据导入之前，先要根据数据的具体格式，设置数据导入的间隔符
作业：
创建一个表学生表，根据查询需要插入一下数据,表结构如下;
create table tbl_student(
no integer primary key,
name varchar(20),
score real,
birthday date
)
实现以下查询：
查询编号为5的学生
查询分数大于80的学生
查询分数大于80并且出生日期为90以后的学生
查询分数在70到80之间的学生
查询姓名中包含’d’学生
查询最高分，最低分，平均成绩
