常用命令
新建（打開(kāi)）數(shù)據(jù)庫(kù)
sqlite3 數(shù)據(jù)庫(kù)名 如：
# 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是否存在
如果不存在就創(chuàng)建并進(jìn)入數(shù)據(jù)庫(kù)绊起，如果直接退出（即執(zhí)行 .exti ），數(shù)據(jù)庫(kù)文件不會(huì)創(chuàng)建
如果已經(jīng)存在直接進(jìn)入數(shù)據(jù)庫(kù),對(duì)數(shù)據(jù)庫(kù)進(jìn)行操作
顯示數(shù)據(jù)庫(kù)信息
.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
.......
查看創(chuàng)建數(shù)據(jù)庫(kù)對(duì)象時(shí) SQL 語(yǔ)句
.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 生成形成數(shù)據(jù)庫(kù)表的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 生成整個(gè)數(shù)據(jù)庫(kù)的腳本在終端顯示
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 將輸出打印到屏幕 默認(rèn)
.output filename 將輸出打印到文件（.dump .output 結(jié)合可將數(shù)據(jù)庫(kù)以sql語(yǔ)句的形式導(dǎo)出到文件中）
設(shè)置輸出的 NULL 字符串
.nullvalue STRING 查詢時(shí)用指定的串代替輸出的NULL串 默認(rèn)為.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
字段類型
數(shù)據(jù)庫(kù)中存儲(chǔ)的每個(gè)值都有一個(gè)類型,都屬于下面所列類型中的一種,(被數(shù)據(jù)庫(kù)引擎所控制)
- NULL: 這個(gè)值為空值
- INTEGER: 值被標(biāo)識(shí)為整數(shù),依據(jù)值的大小可以依次被存儲(chǔ)為1,2,3,4,5,6,7,8個(gè)字節(jié)
- REAL: 所有值都是浮動(dòng)的數(shù)值,被存儲(chǔ)為8字節(jié)的IEEE浮動(dòng)標(biāo)記序號(hào).
- TEXT: 文本. 值為文本字符串,使用數(shù)據(jù)庫(kù)編碼存儲(chǔ)(TUTF-8, UTF-16BE or UTF-16-LE).
- BLOB: 值是BLOB數(shù)據(jù),如何輸入就如何存儲(chǔ),不改變格式.
值被定義為什么類型只和值自身有關(guān),和列沒(méi)有關(guān)系,和變量也沒(méi)有關(guān)系.所以sqlite被稱作 弱類型 數(shù)據(jù)庫(kù)
數(shù)據(jù)庫(kù)引擎將在執(zhí)行時(shí)檢查燎斩、解析類型虱歪，并進(jìn)行數(shù)字存儲(chǔ)類型(整數(shù)和實(shí)數(shù))和文本類型之間的轉(zhuǎn)換.
- SQL語(yǔ)句中部分的帶雙引號(hào)或單引號(hào)的文字被定義為文本,
- 如果文字沒(méi)帶引號(hào)并沒(méi)有小數(shù)點(diǎn)或指數(shù)則被定義為整數(shù),
- 如果文字沒(méi)帶引號(hào)但有小數(shù)點(diǎn)或指數(shù)則被定義為實(shí)數(shù),
- 如果值是空則被定義為空值.
- BLOB數(shù)據(jù)使用符號(hào)X'ABCD'來(lái)標(biāo)識(shí).
但實(shí)際上，sqlite3也接受如下的數(shù)據(jù)類型：
- smallint 16位的整數(shù)栅表。
- interger 32位的整數(shù)笋鄙。
- decimal(p,s) 精確值p是指全部有幾個(gè)十進(jìn)制數(shù),s是指小數(shù)點(diǎn)后可以有幾位小數(shù)。如果沒(méi)有特別指定怪瓶，則系統(tǒng)會(huì)默認(rèn)為p=5 s=0 局装。
- float 32位元的實(shí)數(shù)。
- double 64位元的實(shí)數(shù)劳殖。
- char(n) n 長(zhǎng)度的字串铐尚，n不能超過(guò) 254。
- varchar(n) 長(zhǎng)度不固定且其最大長(zhǎng)度為 n 的字串哆姻，n不能超過(guò) 4000宣增。
- graphic(n) 和 char(n) 一樣奈惑，不過(guò)其單位是兩個(gè)字節(jié)捡鱼， n不能超過(guò)127帆焕。這個(gè)形態(tài)是為了支持兩個(gè)字節(jié)長(zhǎng)度的字體坡疼，如中文字。
- vargraphic(n) 可變長(zhǎng)度且其最大長(zhǎng)度為n的雙字元字串灵妨，n不能超過(guò)2000
- date 包含了 年份解阅、月份、日期泌霍。
- time 包含了 小時(shí)货抄、分鐘、秒朱转。
- timestamp 包含了 年蟹地、月、日藤为、時(shí)怪与、分、秒缅疟、千分之一秒
常用函數(shù)
時(shí)間/日期函數(shù)
-
datetime() 產(chǎn)生日期和時(shí)間 無(wú)參數(shù)表示獲得當(dāng)前時(shí)間和日期分别，有字符串參數(shù)則把字符串轉(zhuǎn)換成日期
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');錯(cuò)誤 select datetime('now','localtime'); 結(jié)果：2006-10-17 21:21:47 date()產(chǎn)生日期
-
time() 產(chǎn)生時(shí)間
在時(shí)間/日期函數(shù)里可以使用如下格式的字符串作為參數(shù)：
- 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 (是產(chǎn)生現(xiàn)在的時(shí)間)
日期不能正確比較大小,會(huì)按字符串比較，日期默認(rèn)格式 dd-mm-yyyy
-
strftime() 對(duì)以上三個(gè)函數(shù)產(chǎn)生的日期和時(shí)間進(jìn)行格式化
strftime()函數(shù)可以把YYYY-MM-DD HH:MM:SS格式的日期字符串轉(zhuǎn)換成其它形式的字符串存淫。 strftime(格式, 日期/時(shí)間, 修正符, 修正符, …) select strftime('%d',datetime());
它可以用以下的符號(hào)對(duì)日期和時(shí)間進(jìn)行格式化：- %d 在該月中的第幾天, 01-31
- %f 小數(shù)形式的秒茎杂，SS.SSS
- %H 小時(shí), 00-23
- %j 算出某一天是該年的第幾天，001-366
- %m 月份纫雁，00-12
- %M 分鐘, 00-59
- %s 從1970年1月1日到現(xiàn)在的秒數(shù)
- %S 秒, 00-59
- %w 星期, 0-6 (0是星期天)
- %W 算出某一天屬于該年的第幾周, 01-53
- %Y 年, YYYY
- %% 百分號(hào)
算術(shù)函數(shù)
- abs(X) 返回給定數(shù)字表達(dá)式的絕對(duì)值煌往。
- max(X,Y[,...]) 返回表達(dá)式的最大值。 組函數(shù) max(列名)
- min(X,Y[,...]) 返回表達(dá)式的最小值轧邪。
- random() 返回隨機(jī)數(shù)刽脖。
- round(X[,Y]) 返回?cái)?shù)字表達(dá)式并四舍五入為指定的長(zhǎng)度或精度。
sqlite> select max(2,3,4,5,6,7,12);
12
字符處理函數(shù)
- length(X) 返回給定字符串表達(dá)式的字符個(gè)數(shù)忌愚。
- lower(X) 將大寫(xiě)字符數(shù)據(jù)轉(zhuǎn)換為小寫(xiě)字符數(shù)據(jù)后返回字符表達(dá)式曲管。
- upper(X) 返回將小寫(xiě)字符數(shù)據(jù)轉(zhuǎn)換為大寫(xiě)的字符表達(dá)式。
- substr(X,m,n) 返回表達(dá)式的一部分硕糊。 從m開(kāi)始讀n個(gè)字符 m最小值1
- quote(A) 給字符串加引號(hào)
條件判斷函數(shù)
ifnull(X,Y) 如果X為null 返回Y
select ifnull(comm,0) from emp;
0
300
500
0
1400
集合函數(shù)
- avg(X) 返回組中值的平均值院水。
- count(X) 返回組中項(xiàng)目的數(shù)量。
- max(X) 返回組中值的最大值简十。
- min(X) 返回組中值的最小值檬某。
- sum(X) 返回表達(dá)式中所有值的和。
其他函數(shù)
- typeof(X) 返回?cái)?shù)據(jù)的類型螟蝙。
- last_insert_rowid() 返回最后插入的數(shù)據(jù)的ID恢恼。
- sqlite_version() 返回SQLite的版本。
- change_count() 返回受上一語(yǔ)句影響的行數(shù)胰默。
基本語(yǔ)法
- 插入記錄
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 '財(cái)務(wù)部'
when 30 then '內(nèi)務(wù)部門'
else '其他部門'
end as dept
from EMP;
- 關(guān)聯(lián)子查詢
in后面的語(yǔ)法中可以有l(wèi)imit（MySQL不可以）
select *
from emp e
where e.EMPNO in
(
select empno
from EMP
where deptno=e.DEPTNO
order by SAL desc
limit 0,2
);
- 表和表之間的數(shù)據(jù)合并等操作
union 去重復(fù) union all 不去掉重復(fù)
select deptno from emp
union
select deptno from dept
select deptno from emp
union all
select deptno from dept;
在列名前加distinct也是去重復(fù)
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);
意思是針對(duì)film資料表的name字段喧半，建立一個(gè)名叫film_name_index的索引。這個(gè)指令的語(yǔ)法為
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底下直接執(zhí)行命令：
sqlite3 film.db "select * from emp;" -
輸出 HTML 表格：
sqlite3 -html film.db "select * from film;" -
將數(shù)據(jù)庫(kù)「倒出來(lái)」：
sqlite3 film.db ".dump" > output.sql -
利用輸出的資料青责，建立一個(gè)一模一樣的數(shù)據(jù)庫(kù)（加上以上指令挺据，就是標(biāo)準(zhǔn)的SQL數(shù)據(jù)庫(kù)備份了）：
sqlite3 film.db < output.sql
-
在大量插入資料時(shí)，你可能會(huì)需要先打這個(gè)指令：
begin;
- 插入完資料后要記得打這個(gè)指令爽柒，資料才會(huì)寫(xiě)進(jìn)數(shù)據(jù)庫(kù)中：
commit;
-
創(chuàng)建和刪除視圖
CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition DROP VIEW view_name
