参考书籍 《SQLite 权威指南 第二版》
Windows获取SQLite
1.主页: www.sqlite.org
2.下载 Precompiled Binaries For Windows
3.设置系统环境PATH
使用
打开cmd,输入sqlite3
命令 .help 帮助
.exit 退出程序
数据库管理
//创建数据库,
sqlite3 test.db
//创建一个表,此时才会创建数据库
create table test (id integer primary key, value text);
//向表中插入几行数据
insert into test (id ,value ) values(1,'eenie');
insert into test (id ,value ) values(2,'meenie');
insert into test (value) values ('miny');
insert into test (value) values ('mo');
//返回插入内容
.mode column
.headers on
select * from test;
//获得最后插入的自动增量值
select last_insert_rowid();
//添加一个索引和视图
create index test_idx on test (value);
create view schema as select *from sqlite_master;
//退出
.exit
获得数据库的Schema信息
//返回所有表和视图
sqlite> .tables
//显示一个表的索引
sqlite> .indices test
//返回所有数据库对象的创建语句
sqlite> .schema
//返回特定数据库对象的创建语句
sqlite> .schema test
//查询当前数据库的sqlite_master表
//首先设置字段格式和标题
sqlite> .mode col
sqlite> .headers on
sqlite> select type,name,tbl_name,sql from sqlite_master order by types;
sqlite_master 表结构 | ||
编号 | 字段名 | 说明 |
1 | type | 对象类型(table,index,trigger,view) |
2 | name | 对象名称 |
3 | tbl_name | 对象关联的表 |
4 | Rootpage | 对象根页面在数据库的索引(开始编号) |
5 | sql | 对象的SQL定义(DDL) |
导出数据
sqlite> .output file.sql
sqlite> .dump
sqlite> .output stdout
导入数据
1.导入sql语句
sqlite> .mode column
sqlite> .headers on
sqlite> select * from test;
//移除已经存在的数据库对象(test表和schema视图)
sqlite> drop table test;
sqlite> drop view schema;
sqlite> select * from test;
//导入sql语句
sqlite> .read file.sql
sqlite> select * from test;
2.import 导入数据 sqlite> .import [file][table]
格式化
//改变CLP的Shell提示符
sqlite> .prompt 'sqlite3>'
//.mode 命令可以设置结果数据的几种输出格式
//csv,column,html,insert,line,list,tabs,tcl
//默认值 list
//CSV格式输出一个表的数据
sqlite3> .output file.csv
sqlite3> .mode csv
sqlite3> .separator ,
sqlite3> select * from test;
sqlite3> .output stdout
导出带分隔符的数据
//导出test表中以字母m开始的值,并以逗号分隔
sqlite> .output test.csv
sqlite> .separator ,
sqlite> select * from test where value like 'm%';
sqlite> .output stdout
//将CSV数据导入到与test表结构体类似的表(test2)中
sqlite> create table test2(id integer primary key,value text);
sqlite> .import text.csv test2
执行无人值守维护
1.提供SQL命令或SQLite shell命令 如 .dump和.schema
//从命令行转存test.db数据库
sqlite3 test.db .dump >test.sql
//从test.sql中创建新的数据库test2.db
sqlite3 test2.db < test.sql
2.使用init选项
sqlite3 -init test.sql test3.db .exit
备份数据库
1.命令行
sqlite3 test.db .dump > test.Sql
2.Shell中 如 导出数据
3.备份二进制数据库(二进制文件没有备份SQL移植性好)
sqlite3 test.db vacuum
cp test.db test.Backup(Linux)
书中例子:
运行示例
sqlite3 foods.db < test.sql
//使数据内容更具有可读性
.echo on
.mode column
.headers on
.nullvalue NULL
//查找
sqlite> select *
...> from foods
...> where name = 'JujyFruit'
...> and type_id = 9;
//对较长的查询,我们以代码形式显示SQL
select f.name name,types.name type
from foods f
inner join (
select *
from food_types
where id=6) types
on f.type_id = types.id;
语法
select id from foods where name = 'JujyFruit'
动词 主语 谓语
命令 SQl由命令组成,每条命令以分号(;)结束
常量 constants 表示确切的值 3种类型
字符串常量、数字常量和二进制常量
如果字符串本身包含单引号 kenny's chicken -> 'kenny''s chicken'
关键字和标识符 字符常量值 -> 大小写敏感
注释 -- 单行注释
/* */ 多行注释
创建数据库
创建表 create [temp|temporary] table table_name ( column_definitions [,constranits=]);
修改表 alter table table { rename to name | add column column_def }
数据库查询
关系操作
基本操作
- Restriction (限制)
- Projection(投影)
- Cartesian Product(笛卡尔积)
- Union(联合)
- Different(差)
- Rename(重命名)
附加操作
- Intersection(交叉)
- Natural Join(自然连接)
- Assign(赋值)
扩展操作
- Generalized Projection(广义投影)
- Left Outer Join(左外连接)
- Right Outer Join(右外连接)
- Full Outer Join(全外连接)
例如 select name from (select name,type_id from (select * from foods));
select 命令与操作管道
过滤
select * from dogs where color = 'purple' and grin = 'toothy';
值:代表了真是世界的某种数据
操作符
二元操作符
逻辑操作符
LIKE与GLOB操作符
LIKE的作用与相等(=)
查询表foods中所有名称以字符“J”开始的食品
select id,name from foods where name like 'J%';
模式中百分号(%)可与任意0个或多个字符匹配
百分号是贪婪匹配,在最左边或者最右边(将匹配字符的另外一边)
下画线(_)可以与任意单个字符匹配
select id , name from foods where name like '%ac'
select id , name from foods where name like '%ac%'
select id , name from foods where name like '%ac%p%'
使用NOT否定某些模式
select id , name from foods where name like '%ac%P%' and name not like '%Sch%'
GLOB 有些像UNIX/Linux
select id ,name from foods where name glob 'Pnie*';
SQLite也识别match和regexp正则表达式断言
目前不提供自身实现
开发自己的sqlite_create_function()调用
限定和排序
limit和offset 关键字限定结果集的大小和范围
limit 数据数量 offset 起点位置
select * from food_types order by id limit 1 offset 1;
order by 使记录集在返回之前按一个或多个字段的值进行排序
asc(默认的升序)或desc(降序)
select * from foods where name like 'B%' order by type_id desc, name limit 10;