sqlite3常用命令及語(yǔ)法.doc

/linchunhua/article/details/7184439sqlite數(shù)據(jù)庫(kù)只用一個(gè)文件就ok，小巧方便，所以是一個(gè)非常不錯(cuò)的嵌入式數(shù)據(jù)庫(kù)，SQLite大量的被用于手機(jī)，PDA，MP3播放器以及機(jī)頂盒設(shè)備。 Mozilla Firefox使用SQLite作為數(shù)據(jù)庫(kù)。 Mac計(jì)算機(jī)中的包含了多份SQLite的拷貝，用于不同的應(yīng)用。 PHP將SQLite作為內(nèi)置的數(shù)據(jù)庫(kù)。 Skype客戶(hù)端軟件在內(nèi)部使用SQLite。 SymbianOS(智能手機(jī)操作平臺(tái)的領(lǐng)航)內(nèi)置SQLite。 AOL郵件客戶(hù)端綁定了SQLite。 Solaris 10在啟動(dòng)過(guò)程中需要使用SQLite。 McAfee殺毒軟件使用SQLite。 iPhones使用SQLite。 Symbian和Apple以外的很多手機(jī)生產(chǎn)廠商使用SQLite。下面就sqlite中的常用命令和語(yǔ)法介紹/download.html可下載不同操作系統(tǒng)的相關(guān)版本sqlite gedit也可以使用火狐中的插件sqlite manager 新建數(shù)據(jù)庫(kù)sqlite3 databasefilename檢查databasefilename是否存在，如果不存在就創(chuàng)建并進(jìn)入數(shù)據(jù)庫(kù)（如果直接退出，數(shù)據(jù)庫(kù)文件不會(huì)創(chuàng)建） 如果已經(jīng)存在直接進(jìn)入數(shù)據(jù)庫(kù) 對(duì)數(shù)據(jù)庫(kù)進(jìn)行操作sqlite中命令:以.開(kāi)頭,大小寫(xiě)敏感（數(shù)據(jù)庫(kù)對(duì)象名稱(chēng)是大小寫(xiě)不敏感的）.exit.help 查看幫助 針對(duì)命令.database 顯示數(shù)據(jù)庫(kù)信息；包含當(dāng)前數(shù)據(jù)庫(kù)的位置.tables 或者 .table 顯示表名稱(chēng) 沒(méi)有表則不顯示.schema 命令可以查看創(chuàng)建數(shù)據(jù)對(duì)象時(shí)的SQL命令；.schema databaseobjectname查看創(chuàng)建該數(shù)據(jù)庫(kù)對(duì)象時(shí)的SQL的命令；如果沒(méi)有這個(gè)數(shù)據(jù)庫(kù)對(duì)象就不顯示內(nèi)容，不會(huì)有錯(cuò)誤提示.read FILENAME 執(zhí)行指定文件中的SQL語(yǔ)句.headers on/off 顯示表頭 默認(rèn)off.mode list|column|insert|line|tabs|tcl|csv 改變輸出格式，具體如下sqlite .mode listsqlite select * from emp;7369|SMITH|CLERK|7902|17-12-1980|800|207499|ALLEN|SALESMAN|7698|20-02-1981|1600|300|30如果字段值為NULL 默認(rèn)不顯示 也就是顯示空字符串sqlite .mode columnsqlite select * from emp;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 insertsqlite 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 linesqlite select * from dept;DEPTNO = 10 DNAME = ACCOUNTING LOC = NEW YORKDEPTNO = 20 DNAME = RESEARCH LOC = DALLASDEPTNO = 30 DNAME = SALES LOC = CHICAGODEPTNO = 40 DNAME = OPERATIONS LOC = BOSTONsqlite .mode tabssqlite select * from dept;10ACCOUNTINGNEW YORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTONsqlite .mode tclsqlite select * from dept;10ACCOUNTINGNEW YORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTONsqlite .mode csvsqlite select * from dept;10,ACCOUNTING,NEW YORK20,RESEARCH,DALLAS30,SALES,CHICAGO40,OPERATIONS,BOSTON.separator X 更改分界符號(hào)為Xsqlite .separator * sqlite select * from dept;10*ACCOUNTING*NEW YORK20*RESEARCH*DALLAS30*SALES*CHICAGO40*OPERATIONS*BOSTON.dump ?TABLE? 生成形成數(shù)據(jù)庫(kù)表的SQL腳本.dump 生成整個(gè)數(shù)據(jù)庫(kù)的腳本在終端顯示.output stdout 將輸出打印到屏幕 默認(rèn).output filename 將輸出打印到文件（.dump .output 結(jié)合可將數(shù)據(jù)庫(kù)以sql語(yǔ)句的形式導(dǎo)出到文件中）.nullvalue STRING 查詢(xún)時(shí)用指定的串代替輸出的NULL串 默認(rèn)為.nullvalue 字段類(lèi)型：數(shù)據(jù)庫(kù)中存儲(chǔ)的每個(gè)值都有一個(gè)類(lèi)型,都屬于下面所列類(lèi)型中的一種,(被數(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ǔ),不改變格式.值被定義為什么類(lèi)型只和值自身有關(guān),和列沒(méi)有關(guān)系,和變量也沒(méi)有關(guān)系.所以sqlite被稱(chēng)作 弱類(lèi)型 數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù)引擎將在執(zhí)行時(shí)檢查、解析類(lèi)型，并進(jìn)行數(shù)字存儲(chǔ)類(lèi)型(整數(shù)和實(shí)數(shù))和文本類(lèi)型之間的轉(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)XABCD來(lái)標(biāo)識(shí).但實(shí)際上，sqlite3也接受如下的數(shù)據(jù)類(lèi)型： 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í)、分、秒、千分之一秒。SQLite包含了如下時(shí)間/日期函數(shù)： datetime() 產(chǎn)生日期和時(shí)間 無(wú)參數(shù)表示獲得當(dāng)前時(shí)間和日期sqlite select datetime();2012-01-07 12:01:32有字符串參數(shù)則把字符串轉(zhuǎn)換成日期sqlite select datetime(2012-01-07 12:01:30); 2012-01-07 12:01:30select date(2012-01-08,+1 day,+1 year);2013-01-09select datetime(2012-01-08 00:20:00,+1 hour,-12 minute);2012-01-08 01:08:00select datetime(now,start of year);2012-01-01 00:00:00select datetime(now,start of month);2012-01-01 00:00:00select datetime(now,start of day);2012-01-08 00:00:00select datetime(now,start of week);錯(cuò)誤select datetime(now,localtime);結(jié)果：2006-10-17 21:21:47date()產(chǎn)生日期 sqlite select date(2012-01-07 12:01:30); 2012-01-07同理 有參和無(wú)參select date(now,start of year);2012-01-01select date(2012-01-08,+1 month);2012-02-08time() 產(chǎn)生時(shí)間 select time();03:14:30select time(23:18:59);23:18:59select time(23:18:59,start of day);00:00:00select time(23:18:59,end of day);錯(cuò)誤在時(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 其中now是產(chǎn)生現(xiàn)在的時(shí)間。日期不能正確比較大小,會(huì)按字符串比較，日期默認(rèn)格式 dd-mm-yyyyselect hiredate from emp order by hiredate;17-11-198117-12-198019-04-198720-02-198122-02-1981strftime() 對(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)select strftime(%Y.%m.%d %H:%M:%S,now); select strftime(%Y.%m.%d %H:%M:%S,now,localtime); 結(jié)果：2006.10.17 21:41:09select 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); 錯(cuò)誤算術(shù)函數(shù) abs(X) 返回給定數(shù)字表達(dá)式的絕對(duì)值。 max(X,Y,.) 返回表達(dá)式的最大值。 組函數(shù) max(列名)sqlite select max(2,3,4,5,6,7,12);12select (abs(strftime(%s,now)-abs(strftime(%s, 1986-03-21 04:04:04)/24/60/60;min(X,Y,.) 返回表達(dá)式的最小值。 random() 返回隨機(jī)數(shù)。sqlite select random();3224224213599993831 round(X,Y) 返回?cái)?shù)字表達(dá)式并四舍五入為指定的長(zhǎng)度或精度。 select round(abs(strftime(%s,now)-abs(strftime(%s, 1986-03-21 04:04:04)/24/60/60,0);select round(3.2345,0);字符處理函數(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,Y,Z) 返回表達(dá)式的一部分。 從Y開(kāi)始讀Z個(gè)字符 Y最小值1sqlite select substr(abcdef,3,3); cdeselect length(aaaa),lower(AAADDDdd),upper(sdfdsfSD);4|aaaddddd|SDFDSFSD23-01-1982substr(hiredate,7,4)substr(hiredate,4,2)substr(hiredate,1,2)| 字符串拼接select empno,ename,substr(hiredate,7,4)|-|substr(hiredate,4,2)|-|substr(hiredate,1,2) as hiredate from emp order by substr(hiredate,7,4) asc,substr(hiredate,4,2) asc,substr(hiredate,1,2) asc;select strftime(%Y-%m-%d）quote(A) 給字符串加引號(hào) sqlite select quote(aaa);aaa條件判斷函數(shù) ifnull(X,Y) 如果X為null 返回Yselect ifnull(comm,0) from emp;030050001400集合函數(shù) 組函數(shù)avg(X) 返回組中值的平均值。 count(X) 返回組中項(xiàng)目的數(shù)量。 max(X) 返回組中值的最大值。 min(X) 返回組中值的最小值。 sum(X) 返回表達(dá)式中所有值的和。 select avg(sal),max(sal),min(sal),count(sal),sum(sal) from emp;select avg(sal),max(sal),min(sal),count(sal),sum(sal),deptnofrom empgroup by deptno;select deptno,avg(sal)from emp/1where sal1000 /2group by deptno /3having deptno10 /4order by deptno desc;select gender， 組函數(shù) min(age) max count(*) avg sumfrom studentwhere age20group by genderhaving gender=男SELECT EMP.ENAME,EMP.DEPTNOFROM EMP;select count(*) AS count from emp AS e;select EE.deptno,EE.AAAfrom (select deptno,avg(sal) AAA,max(sal),min(sal),count(*)from empgroup by deptnohaving deptno30order by deptno asc) EE;SELECT SALFROM EMPWHERE SAL(SELECT AVG(SAL) FROM EMP)SELECT * FROM EMP EWHERE SAL(SELECT AVG(SAL) FROM EMP WHERE EMP.DEPTNO=E.DEPTNO)SELECT * FROM EMP EWHERE SAL(SELECT AVG(SAL) FROM EMP WHERE EMP.DEPTNO=E.DEPTNO);/關(guān)聯(lián)子查詢(xún)select strftime(%s,now)-strftime(%s,1986-03-02 04:04:04);SELECT * FROM EMP EWHERE deptno in (SELECT deptno from emp where sal3000);select emp.empno,emp.ename,emp.sal,emp.deptno,dept.dname,salgrade.gradefrom emp,dept,salgradewhere emp.deptno=dept.deptno and emp.sal= salgrade.losal and emp.sal select typeof(111);integersqlite select typeof(233);textsqlite select typeof(2012-12-12);textsqlite select typeof(223.44);textsqlite select typeof(223.44);reallast_insert_rowid() 返回最后插入的數(shù)據(jù)的ID。 sqlite_version() 返回SQLite的版本。 sqlite select sqlite_version();3.7.9change_count() 返回受上一語(yǔ)句影響的行數(shù)。 last_statement_change_count()create table emp_bak select * from EMP;不能在sqlite中使用插入記錄insert into table_name values (field1, field2, field3.);查詢(xún)select * from table_name;查看table_name表中所有記錄；select * from table_name where field1=xxxxx; 查詢(xún)符合指定條件的記錄；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_name2on .where .group by.having .order by .子查詢(xún)：select * from EMP mwhere SAL(select avg(SAL) from EMP where DEPTNO=m.DEPTNO);支持case when then 語(yǔ)法update EMPset SAL=(casewhen DEPTNO=10 and JOB=MANAGER then SAL*1.1when DEPTNO=20 and JOB=CLERK then SAL*1.2when DEPTNO=30 then SAL*1.1when DEPTNO=40 then SAL*1.2else SALEND);select ENAME, case DEPTNOwhen 10 then 后勤部when 20 then 財(cái)務(wù)部when 30 then 內(nèi)務(wù)部門(mén)else 其他部門(mén)end as deptfrom EMP;支持關(guān)聯(lián)子查詢(xún) in后面的語(yǔ)法中可以有l(wèi)imit（mysql不可以）select *from emp ewhere e.EMPNO in (select empno from EMPwhere deptno=e.DEPTNOorder by SAL desclimit 0,2);支持表和表之間的數(shù)據(jù)合并等操作union 去重復(fù) union all 不去掉重復(fù)select deptno from empunion select deptno from dept;select deptno from empunion allselect deptno from dept;在列名前加distinct也是去重復(fù)sqlite select distinct deptno from emp;刪除delete from table_name where .刪除表drop table_name; 刪除表；drop index_name; 刪除索引；修改update table_nameset xxx=value, xxx=value,.where .建立索引如果資料表有相當(dāng)多的資料，我們便會(huì)建立索引來(lái)加快速度。