sqlite3常用指令&文法

sqlite資料庫隻用一個檔案就ok，小巧友善，是以是一個非常不錯的嵌入式資料庫，SQLite大量的被用于手機，PDA，MP3播放器以及機頂盒裝置。

Mozilla Firefox使用SQLite作為資料庫。

Mac計算機中的包含了多份SQLite的拷貝，用于不同的應用。

PHP将SQLite作為内置的資料庫。

Skype用戶端軟體在内部使用SQLite。

SymbianOS(智能手機操作平台的領航)内置SQLite。

AOL郵件用戶端綁定了SQLite。

Solaris 10在啟動過程中需要使用SQLite。

McAfee防毒軟體使用SQLite。

iPhones使用SQLite。

Symbian和Apple以外的很多手機生産廠商使用SQLite。

下面就sqlite中的常用指令和文法介紹

http://www.sqlite.org/download.html可下載下傳不同作業系統的相關版本sqlite gedit

也可以使用火狐中的插件sqlite manager

建立資料庫

sqlite3 databasefilename

檢查databasefilename是否存在，如果不存在就建立并進入資料庫（如果直接退出，資料庫檔案不會建立）如果已經存在直接進入資料庫對資料庫進行操作

sqlite中指令:

以.開頭,大小寫敏感（資料庫對象名稱是大小寫不敏感的）

.exit

.help 檢視幫助針對指令

.database 顯示資料庫資訊；包含目前資料庫的位置

.tables 或者 .table 顯示表名稱沒有表則不顯示

.schema 指令可以檢視建立資料對象時的SQL指令；

.schema databaseobjectname檢視建立該資料庫對象時的SQL的指令；如果沒有這個資料庫對象就不顯示内容，不會有錯誤提示

.read FILENAME 執行指定檔案中的SQL語句

.headers on/off 顯示表頭預設off

sqlite> .mode list

sqlite> select * from emp;

7369|SMITH|CLERK|7902|17-12-1980|800||20

7499|ALLEN|SALESMAN|7698|20-02-1981|1600|300|30

如果字段值為NULL 預設不顯示也就是顯示空字元串

sqlite> .mode column

7369 SMITH CLERK 7902 17-12-1980 800 20

7499 ALLEN SALESMAN 7698 20-02-1981 1600 300 30

7521 WARD SALESMAN 7698 22-02-1981 1250 500 30

sqlite> .mode insert

sqlite> select * from dept;

INSERT INTO table VALUES(10,'ACCOUNTING','NEW YORK');

INSERT INTO table VALUES(20,'RESEARCH','DALLAS');

INSERT INTO table VALUES(30,'SALES','CHICAGO');

INSERT INTO table VALUES(40,'OPERATIONS','BOSTON');

sqlite> .mode line

DEPTNO = 10

DNAME = ACCOUNTING

LOC = NEW YORK

DEPTNO = 20

DNAME = RESEARCH

LOC = DALLAS

DEPTNO = 30

DNAME = SALES

LOC = CHICAGO

DEPTNO = 40

DNAME = OPERATIONS

LOC = BOSTON

sqlite> .mode tabs

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

sqlite> .mode tcl

"10" "ACCOUNTING""NEW YORK"

"20" "RESEARCH""DALLAS"

"30" "SALES" "CHICAGO"

"40" "OPERATIONS""BOSTON"

sqlite> .mode csv

10,ACCOUNTING,"NEW YORK"

20,RESEARCH,DALLAS

30,SALES,CHICAGO

40,OPERATIONS,BOSTON

.separator "X" 更改分界符号為X

sqlite> .separator '**'

10**ACCOUNTING**"NEW YORK"

20**RESEARCH**DALLAS

30**SALES**CHICAGO

40**OPERATIONS**BOSTON

.dump ?TABLE? 生成形成資料庫表的SQL腳本

.dump 生成整個資料庫的腳本在終端顯示

.output stdout 将輸出列印到螢幕預設

.output filename 将輸出列印到檔案（.dump .output 結合可将資料庫以sql語句的形式導出到檔案中）

.nullvalue STRING 查詢時用指定的串代替輸出的NULL串預設為.nullvalue ''

字段類型：

資料庫中存儲的每個值都有一個類型,都屬于下面所列類型中的一種,(被資料庫引擎所控制)

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 包含了年、月、日、時、分、秒、千分之一秒。

SQLite包含了如下時間/日期函數：

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');

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()産生日期

sqlite> select date('2012-01-07 12:01:30');

2012-01-07

同理有參和無參

select date('now','start of year');

2012-01-01

select date('2012-01-08','+1 month');

2012-02-08

time() 産生時間

select time();

03:14:30

select time('23:18:59');

23:18:59

select time('23:18:59','start of day');

00:00:00

select time('23:18:59','end of day');錯誤

在時間/日期函數裡可以使用如下格式的字元串作為參數：

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

其中now是産生現在的時間。

日期不能正确比較大小,會按字元串比較，日期預設格式 dd-mm-yyyy

select hiredate from emp order by hiredate;

17-11-1981

17-12-1980

19-04-1987

20-02-1981

22-02-1981

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

%% 百分号

select strftime('%Y.%m.%d %H:%M:%S','now');

select strftime('%Y.%m.%d %H:%M:%S','now','localtime');

結果：2006.10.17 21:41:09

select hiredate from emp

order by strftime('%Y.%m.%d %H:%M:%S',hiredate); 正确

select strftime('%Y.%m.%d %H:%M:%S',hiredate) from emp

order by strftime('%Y.%m.%d %H:%M:%S',hiredate); 錯誤

算術函數

abs(X) 傳回給定數字表達式的絕對值。

max(X,Y[,...]) 傳回表達式的最大值。組函數 max(列名)

sqlite> select max(2,3,4,5,6,7,12);

min(X,Y[,...]) 傳回表達式的最小值。

random() 傳回随機數。

sqlite> select random();

3224224213599993831

round(X[,Y]) 傳回數字表達式并四舍五入為指定的長度或精度。

字元處理函數

length(X) 傳回給定字元串表達式的字元個數。

lower(X) 将大寫字元資料轉換為小寫字元資料後傳回字元表達式。

upper(X) 傳回将小寫字元資料轉換為大寫的字元表達式。

substr(X,Y,Z) 傳回表達式的一部分。從Y開始讀Z個字元 Y最小值1

sqlite> select substr('abcdef',3,3);

cde

quote(A) 給字元串加引号

sqlite> select quote('aaa');

'aaa'

條件判斷函數

ifnull(X,Y) 如果X為null 傳回Y

select ifnull(comm,0) from emp;

300

500

1400

集合函數

avg(X) 傳回組中值的平均值。

count(X) 傳回組中項目的數量。

max(X) 傳回組中值的最大值。

min(X) 傳回組中值的最小值。

sum(X) 傳回表達式中所有值的和。

其他函數

typeof(X) 傳回資料的類型。

sqlite> select typeof(111);

integer

sqlite> select typeof('233');

text

sqlite> select typeof('2012-12-12');

sqlite> select typeof('223.44');

sqlite> select typeof(223.44);

real

last_insert_rowid() 傳回最後插入的資料的ID。

sqlite_version() 傳回SQLite的版本。

sqlite> select sqlite_version();

3.7.9

change_count() 傳回受上一語句影響的行數。

last_statement_change_count()

create table emp_bak select * from EMP;不能在sqlite中使用

插入記錄

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 ...

from table_name inner join | left outer join | right outer join table_name2

on ...

子查詢：

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;

union all

在列名前加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(title);

意思是針對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);

一旦建立了索引，sqlite3會在針對該字段作查詢時，自動使用該索引。這一切的操作都是在幕後自動發生的，無須使用者特别指令。

其他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;

sqlite> begin;

sqlite> insert into aaaa values('aaa','333');

sqlite> select * from aaaa;

2|sdfds

sdfsd|9

2012-12-12|13:13:13

aaa|333

sqlite> rollback;

建立和删除視圖

CREATE VIEW view_name AS

SELECT column_name(s)

FROM table_name

WHERE condition

DROP VIEW view_name

create view e as

select avg(SAL) avgsal,DEPTNO

group by DEPTNO;

select ENAME,EMP.DEPTNO,SAL,avgsal

from EMP inner join e

on EMP.DEPTNO=e.DEPTNO

where SAL>avgsal;

練習員工表：

PRAGMA foreign_keys=OFF;

BEGIN TRANSACTION;

CREATE TABLE DEPT

DEPTNO int(2) not null,

DNAME varchar(14),

LOC varchar(13)

INSERT INTO "DEPT" VALUES(10,'ACCOUNTING','NEW YORK');

INSERT INTO "DEPT" VALUES(20,'RESEARCH','DALLAS');

INSERT INTO "DEPT" VALUES(30,'SALES','CHICAGO');

INSERT INTO "DEPT" VALUES(40,'OPERATIONS','BOSTON');

CREATE TABLE EMP

EMPNO int(4) not null,

ENAME varchar(10),

JOB varchar(9),

MGR int(4),

HIREDATE date,

SAL int(7 ),

COMM int(7 ),

DEPTNO int(2)

INSERT INTO "EMP" VALUES(7369,'SMITH','CLERK',7902,'17-12-1980',800,NULL,20);

INSERT INTO "EMP" VALUES(7499,'ALLEN','SALESMAN',7698,'20-02-1981',1600,300,30);

INSERT INTO "EMP" VALUES(7521,'WARD','SALESMAN',7698,'22-02-1981',1250,500,30);

INSERT INTO "EMP" VALUES(7566,'JONES','MANAGER',7839,'02-04-1981',2975,NULL,20);

INSERT INTO "EMP" VALUES(7654,'MARTIN','SALESMAN',7698,'28-09-1981',1250,1400,30);

INSERT INTO "EMP" VALUES(7698,'BLAKE','MANAGER',7839,'01-05-1981',2850,NULL,30);

INSERT INTO "EMP" VALUES(7782,'CLARK','MANAGER',7839,'09-06-1981',2450,NULL,10);

INSERT INTO "EMP" VALUES(7788,'SCOTT','ANALYST',7566,'19-04-1987',3000,NULL,20);

INSERT INTO "EMP" VALUES(7839,'KING','PRESIDENT',NULL,'17-11-1981',5000,NULL,10);

INSERT INTO "EMP" VALUES(7844,'TURNER','SALESMAN',7698,'08-09-1981',1500,0,30);

INSERT INTO "EMP" VALUES(7876,'ADAMS','CLERK',7788,'23-05-1987',1100,NULL,20);

INSERT INTO "EMP" VALUES(7900,'JAMES','CLERK',7698,'03-12-1981',950,NULL,30);

INSERT INTO "EMP" VALUES(7902,'FORD','ANALYST',7566,'03-12-1981',3000,NULL,20);

INSERT INTO "EMP" VALUES(7934,'MILLER','CLERK',7782,'23-01-1982',1300,NULL,10);

CREATE TABLE SALGRADE

GRADE int,

LOSAL int,

HISAL int

INSERT INTO "SALGRADE" VALUES(1,700,1200);

INSERT INTO "SALGRADE" VALUES(2,1201,1400);

INSERT INTO "SALGRADE" VALUES(3,1401,2000);

INSERT INTO "SALGRADE" VALUES(4,2001,3000);

INSERT INTO "SALGRADE" VALUES(5,3001,9999);

COMMIT;

本文轉自einyboy部落格園部落格，原文連結：http://www.cnblogs.com/einyboy/archive/2013/03/12/2955378.html，如需轉載請自行聯系原作者。

sqlite3常用指令&文法

繼續閱讀

set define off關閉替代變量功能

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述

sqlite3常用指令&amp;文法

繼續閱讀

sqlite3常用指令&文法