sqlite3常用命令&语法
2018-02-27 本文已影响0人
lenozhao
常用命令
新建（打开）数据库
sqlite3 数据库名 如：
# sqlite3 user_setting.db
SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
检查databasefilename是否存在
如果不存在就创建并进入数据库，如果直接退出（即执行 .exti ），数据库文件不会创建
如果已经存在直接进入数据库,对数据库进行操作
显示数据库信息
.database 如：
sqlite> .database
seq name file
--- --------------- ----------------------------------------------------------
0 main /tvdatabase/Database/user_setting.db
sqlite>
显示表名称
.table 或者 .tables
sqlite> .tables
tbl_AbbRatingText
tbl_AndroidConfig
tbl_BlockSysSetting
tbl_BootSetting
.......
查看创建数据库对象时 SQL 语句
.schema
sqlite> .schema
CREATE TABLE [tbl_3DInfo] (
[_id] INTEGER NOT NULL PRIMARY KEY,
[bEnable3D] INTEGER NULL,
[enInput3DMode] INTEGER NULL,
[enOutput3DMode] INTEGER NULL
);
CREATE TABLE [tbl_3DSetting] (
[_id] INTEGER PRIMARY KEY NOT NULL,
[enDisplayMode] INTEGER NULL,
[en3DFormat] INTEGER NULL,
[en2DFormat] INTEGER NULL,
[enAutoStart] INTEGER NULL,
[en3DTimerPeriod] INTEGER NULL
);
.......
是否显示表头
.headers on/off
sqlite> .headers on
sqlite> select * from tbl_MiscSetting;
_id MTSSetting BlockUnratedTV CurrentTVtype
---------- ---------- -------------- -------------
0 1 0 0
sqlite> .headers off
sqlite> select * from tbl_MiscSetting;
0 1 0 0
改变输出格式
.mode list|column|insert|line|tabs|tcl|csv|html
- list
sqlite> .mode list sqlite> select * from tbl_MiscSetting; 0|1|0|0 - column
sqlite> .mode column sqlite> select * from tbl_MiscSetting; _id MTSSetting BlockUnratedTV CurrentTVtype ---------- ---------- -------------- ------------- 0 1 0 0 - insert
sqlite> .mode insert sqlite> select * from tbl_MiscSetting; INSERT INTO table VALUES(0,1,0,0); - line
sqlite> .mode line sqlite> select * from tbl_MiscSetting; _id = 0 MTSSetting = 1 BlockUnratedTV = 0 CurrentTVtype = 0 - tabs
sqlite> .mode tabs sqlite> select * from tbl_MiscSetting; 0 1 0 0 - tcl
sqlite> .mode tcl sqlite> select * from tbl_MiscSetting; "0" "1" "0" "0" - csv
sqlite> .mode csv sqlite> select * from tbl_MiscSetting; 0,1,0,0 - html
sqlite> .mode html sqlite> select * from tbl_MiscSetting; <TR><TD>0</TD> <TD>1</TD> <TD>0</TD> <TD>0</TD> </TR>
更改分界符
.separator "分界符"
sqlite> .separator "==" sqlite> select * from tbl_MiscSetting; 0==1==0==0
dump
- .dump TABLE 生成形成数据库表的SQL脚本
sqlite> .dump tbl_MiscSetting PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE [tbl_MiscSetting] ( [_id] INTEGER NOT NULL PRIMARY KEY, [MTSSetting] INTEGER NULL, [BlockUnratedTV] INTEGER NULL, [CurrentTVtype] INTEGER NULL ); INSERT INTO "tbl_MiscSetting" VALUES(0,1,0,0); COMMIT; - .dump 生成整个数据库的脚本在终端显示
sqlite> .dump PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE SQLITEADMIN_QUERIES(ID INTEGER PRIMARY KEY,NAME VARCHAR(100),SQL TEXT); INSERT INTO "SQLITEADMIN_QUERIES" VALUES(1,'insert','insert into tbl_UserPCModeSetting (_id,u16HorizontalStart,u16VerticalStart,u16HorizontalTotal,u8ModeIndex,u16Phase,u8AutoSign,u8Order,u16UI_HorizontalStart,u16UI_VorizontalStart) values(9,0,0,0,0,0,0,0,0,0);'); INSERT INTO "SQLITEADMIN_QUERIES" VALUES(2,'update','update MS_USER_COLORTEMP_EX set _Name="SVIDEO" where InputSrcType=3;'); CREATE TABLE [tbl_3DInfo] ( [_id] INTEGER NOT NULL PRIMARY KEY, [bEnable3D] INTEGER NULL, [enInput3DMode] INTEGER NULL, [enOutput3DMode] INTEGER NULL ); .......
output
- .output stdout 将输出打印到屏幕 默认
.output filename 将输出打印到文件（.dump .output 结合可将数据库以sql语句的形式导出到文件中）
设置输出的 NULL 字符串
.nullvalue STRING 查询时用指定的串代替输出的NULL串 默认为.nullvalue ''
退出
.exit 如：
sqlite> .exit
查看帮助
.help 如：
sqlite> .help
.backup ?DB? FILE Backup DB (default "main") to FILE
.bail ON|OFF Stop after hitting an error. Default OFF
.databases List names and files of attached databases
.dump ?TABLE? ... Dump the database in an SQL text format
If TABLE specified, only dump tables matching
LIKE pattern TABLE.
.echo ON|OFF Turn command echo on or off
.exit Exit this program
.explain ?ON|OFF? Turn output mode suitable for EXPLAIN on or off.
With no args, it turns EXPLAIN on.
.header(s) ON|OFF Turn display of headers on or off
.help Show this message
.import FILE TABLE Import data from FILE into TABLE
.indices ?TABLE? Show names of all indices
If TABLE specified, only show indices for tables
matching LIKE pattern TABLE.
.log FILE|off Turn logging on or off. FILE can be stderr/stdout
.mode MODE ?TABLE? Set output mode where MODE is one of:
csv Comma-separated values
column Left-aligned columns. (See .width)
html HTML <table> code
insert SQL insert statements for TABLE
line One value per line
list Values delimited by .separator string
tabs Tab-separated values
tcl TCL list elements
.nullvalue STRING Print STRING in place of NULL values
.output FILENAME Send output to FILENAME
.output stdout Send output to the screen
.prompt MAIN CONTINUE Replace the standard prompts
.quit Exit this program
.read FILENAME Execute SQL in FILENAME
.restore ?DB? FILE Restore content of DB (default "main") from FILE
.schema ?TABLE? Show the CREATE statements
If TABLE specified, only show tables matching
LIKE pattern TABLE.
.separator STRING Change separator used by output mode and .import
.show Show the current values for various settings
.stats ON|OFF Turn stats on or off
.tables ?TABLE? List names of tables
If TABLE specified, only list tables matching
LIKE pattern TABLE.
.timeout MS Try opening locked tables for MS milliseconds
.vfsname ?AUX? Print the name of the VFS stack
.width NUM1 NUM2 ... Set column widths for "column" mode
.timer ON|OFF Turn the CPU timer measurement on or off
字段类型
数据库中存储的每个值都有一个类型,都属于下面所列类型中的一种,(被数据库引擎所控制)
- NULL: 这个值为空值
- INTEGER: 值被标识为整数,依据值的大小可以依次被存储为1,2,3,4,5,6,7,8个字节
- REAL: 所有值都是浮动的数值,被存储为8字节的IEEE浮动标记序号.
- TEXT: 文本. 值为文本字符串,使用数据库编码存储(TUTF-8, UTF-16BE or UTF-16-LE).
- BLOB: 值是BLOB数据,如何输入就如何存储,不改变格式.
值被定义为什么类型只和值自身有关,和列没有关系,和变量也没有关系.所以sqlite被称作 弱类型 数据库
数据库引擎将在执行时检查、解析类型，并进行数字存储类型(整数和实数)和文本类型之间的转换.
- SQL语句中部分的带双引号或单引号的文字被定义为文本,
- 如果文字没带引号并没有小数点或指数则被定义为整数,
- 如果文字没带引号但有小数点或指数则被定义为实数,
- 如果值是空则被定义为空值.
- BLOB数据使用符号X'ABCD'来标识.
但实际上，sqlite3也接受如下的数据类型：
- smallint 16位的整数。
- interger 32位的整数。
- decimal(p,s) 精确值p是指全部有几个十进制数,s是指小数点后可以有几位小数。如果没有特别指定，则系统会默认为p=5 s=0 。
- float 32位元的实数。
- double 64位元的实数。
- char(n) n 长度的字串，n不能超过 254。
- varchar(n) 长度不固定且其最大长度为 n 的字串，n不能超过 4000。
- graphic(n) 和 char(n) 一样，不过其单位是两个字节， n不能超过127。这个形态是为了支持两个字节长度的字体，如中文字。
- vargraphic(n) 可变长度且其最大长度为n的双字元字串，n不能超过2000
- date 包含了 年份、月份、日期。
- time 包含了 小时、分钟、秒。
- timestamp 包含了 年、月、日、时、分、秒、千分之一秒
常用函数
时间/日期函数
-
datetime() 产生日期和时间 无参数表示获得当前时间和日期，有字符串参数则把字符串转换成日期
sqlite> select datetime(); 2012-01-07 12:01:32 sqlite> select datetime('2012-01-07 12:01:30'); 2012-01-07 12:01:30 select date('2012-01-08','+1 day','+1 year'); 2013-01-09 select datetime('2012-01-08 00:20:00','+1 hour','-12 minute'); 2012-01-08 01:08:00 select datetime('now','start of year'); 2012-01-01 00:00:00 select datetime('now','start of month'); 2012-01-01 00:00:00 select datetime('now','start of day'); 2012-01-08 00:00:00 select datetime('now','start of week');错误 select datetime('now','localtime'); 结果：2006-10-17 21:21:47 -
date()产生日期
-
time() 产生时间
在时间/日期函数里可以使用如下格式的字符串作为参数：
- YYYY-MM-DD
- YYYY-MM-DD HH:MM
- YYYY-MM-DD HH:MM:SS
- YYYY-MM-DD HH:MM:SS.SSS
- HH:MM
- HH:MM:SS
- HH:MM:SS.SSS
- now (是产生现在的时间)
日期不能正确比较大小,会按字符串比较，日期默认格式 dd-mm-yyyy
-
strftime() 对以上三个函数产生的日期和时间进行格式化
strftime()函数可以把YYYY-MM-DD HH:MM:SS格式的日期字符串转换成其它形式的字符串。 strftime(格式, 日期/时间, 修正符, 修正符, …) select strftime('%d',datetime());
它可以用以下的符号对日期和时间进行格式化：- %d 在该月中的第几天, 01-31
- %f 小数形式的秒，SS.SSS
- %H 小时, 00-23
- %j 算出某一天是该年的第几天，001-366
- %m 月份，00-12
- %M 分钟, 00-59
- %s 从1970年1月1日到现在的秒数
- %S 秒, 00-59
- %w 星期, 0-6 (0是星期天)
- %W 算出某一天属于该年的第几周, 01-53
- %Y 年, YYYY
- %% 百分号
算术函数
- abs(X) 返回给定数字表达式的绝对值。
- max(X,Y[,...]) 返回表达式的最大值。 组函数 max(列名)
- min(X,Y[,...]) 返回表达式的最小值。
- random() 返回随机数。
- round(X[,Y]) 返回数字表达式并四舍五入为指定的长度或精度。
sqlite> select max(2,3,4,5,6,7,12);
12
字符处理函数
- length(X) 返回给定字符串表达式的字符个数。
- lower(X) 将大写字符数据转换为小写字符数据后返回字符表达式。
- upper(X) 返回将小写字符数据转换为大写的字符表达式。
- substr(X,m,n) 返回表达式的一部分。 从m开始读n个字符 m最小值1
- quote(A) 给字符串加引号
条件判断函数
ifnull(X,Y) 如果X为null 返回Y
select ifnull(comm,0) from emp;
0
300
500
0
1400
集合函数
- avg(X) 返回组中值的平均值。
- count(X) 返回组中项目的数量。
- max(X) 返回组中值的最大值。
- min(X) 返回组中值的最小值。
- sum(X) 返回表达式中所有值的和。
其他函数
- typeof(X) 返回数据的类型。
- last_insert_rowid() 返回最后插入的数据的ID。
- sqlite_version() 返回SQLite的版本。
- change_count() 返回受上一语句影响的行数。
基本语法
- 插入记录
insert into table_name values (field1, field2, field3...);
- 查询
select * from table_name;查看table_name表中所有记录；
select * from table_name where field1='xxxxx'; 查询符合指定条件的记录；
select .....
from table_name[,table_name2,...]
where .....
group by....
having ....
order by ...
select .....
from table_name inner join | left outer join | right outer join table_name2
on ...
where .....
group by....
having ....
order by ...
- 子查询
select * from EMP m where SAL > (select avg(SAL) from EMP where DEPTNO=m.DEPTNO);
- case when then
update EMP
set SAL=
(
case
when DEPTNO=10 and JOB='MANAGER' then SAL*1.1
when DEPTNO=20 and JOB='CLERK' then SAL*1.2
when DEPTNO=30 then SAL*1.1
when DEPTNO=40 then SAL*1.2
else SAL
END
);
select ENAME,
case DEPTNO
when 10 then '后勤部'
when 20 then '财务部'
when 30 then '内务部门'
else '其他部门'
end as dept
from EMP;
- 关联子查询
in后面的语法中可以有limit（MySQL不可以）
select *
from emp e
where e.EMPNO in
(
select empno
from EMP
where deptno=e.DEPTNO
order by SAL desc
limit 0,2
);
- 表和表之间的数据合并等操作
union 去重复 union all 不去掉重复
select deptno from emp
union
select deptno from dept
select deptno from emp
union all
select deptno from dept;
在列名前加distinct也是去重复
sqlite> select distinct deptno from emp;
- 删除
delete from table_name where ...
- 删除表
drop table_name; 删除表；
drop index_name; 删除索引；
- 修改
update table_name
set xxx=value[, xxx=value,...]
where ...
- 索引
create index film_title_index on film(name);
意思是针对film资料表的name字段，建立一个名叫film_name_index的索引。这个指令的语法为
CREATE [ UNIQUE ] NONCLUSTERED INDEX index_name
ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )
create index index_name on table_name(field_to_be_indexed);
其他sqlite的特别用法
-
sqlite可以在shell底下直接执行命令：
sqlite3 film.db "select * from emp;" -
输出 HTML 表格：
sqlite3 -html film.db "select * from film;" -
将数据库「倒出来」：
sqlite3 film.db ".dump" > output.sql -
利用输出的资料，建立一个一模一样的数据库（加上以上指令，就是标准的SQL数据库备份了）：
sqlite3 film.db < output.sql
-
在大量插入资料时，你可能会需要先打这个指令：
begin;
- 插入完资料后要记得打这个指令，资料才会写进数据库中：
commit;
-
创建和删除视图
CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition DROP VIEW view_name
