SQLite学习笔记
参考书籍 《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;