sqlite3常用命令&语法

作者: lenozhao | 来源:发表于2018-02-27 14:50 被阅读0次

常用命令

新建（打开）数据库

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

改变输出格式

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

参考 http://blog.csdn.NET/linchunhua/article/details/7184439