一、常用命令
.help .head on .mode column .tables .database .schema 【表名】 .q .read
二、資料型別
BLOB DEFAULT CHECK UNIQUE COLLATE
三、常用SQL語句
1、/usr/bin/sqlite3 mydatabase.db
2、CREATE TABLE stuInfo /*-建立學員資訊表-*/
(
stuNo CHAR(6) PRIMARY KEY, --學號，非空（必填）
stuName VARCHAR(20) NOT NULL , --姓名，非空（必填）
stuAge INT NOT NULL, --年齡，INT型別預設為4個位元組
stuID NUMERIC(18,0) (18:整數位數, 0:小數位數), --身份證號
stuSeat SMALLINT IDENTITY (1,1)（從1開始自增長）, --座位號，自動編號
stuAddress TEXT --住址，允許為空，即可選輸入
) ;
3、DROP table 表名;
4、ALTER table 表名 ADD 列名 ;
5、ATTACH DATABASE 庫檔案 AS 庫名
6、DETACH 庫名;
7、
INSERT INTO Students (SName,SAddress,SGrade,SEmail,SSEX)
VALUES ('張青裁','上海松江',6,'[email protected]',0)
8、
INSERT INTO TongXunLu ('姓名','地址','電子郵件')
SELECT SName,SAddress,SEmail
FROM Students
9、UPDATE 表名 SET 列名=1 WHERE stu_id=2;
10、DELETE FROM 表名 WHERE ....；
11、SELETC ()/* FROM 表名 WHERE ORDER BY ...DESC(降序)/ASC(升序)
12、SELECT *FROM 表名 WHERE name LIKE '%l%';
匹配一個字元 LIKE 'C_'
匹配任意長度的字串 LIKE 'CO_%'
匹配括號中所指定範圍內的一個字元 LIKE '9W0[1-2]'
匹配不在括號中所指定範圍內的一個字元 LIKE ‘%[A-D][^1-2]'
13、SELECT user_id DISTINCT FROM 表名; --將重複的行合併
14、SELECT *FROM 表名 WHERE score BETWEEN 18 AND 25;
15 、內連線
(1)、
SELECT a.stu_id，stu_name，course，score FROM tbl_stu a,tbl_score b WHERE a.stu_id=b.stu_id;
(2)、
SELECT *FROM tbl_stu INNER JOIN tbl_score ON a.stu_id=b.stu_id;
16、左連線
SELECT *FROM tbl_stu LEFT OUTER JOIN tbl_score ON a.stu_id=b.stu_id;
17、左自連線
SELECT *FROM tbl_emp a LEFT OUTER JOIN tbl_emp b ON a.epid=b.eid;
18、整理資料庫
VACUUM
四、查詢技巧
1、巢狀子查詢
SELECT * FROM 表名 WHERE id > SELECT 只能一個列 FROM 表名
2、IN子查詢
SELECT * FROM tab_student WHERE stu_id IN (SELECT stu_id FROM tab_score);
3、EXISTS語句
SELECT * FROM t_student a WHERE NOT EXISTS (SELECT sno FROM t_score b WHERE a.sno=b.sno)
4、集合操作-並UNION
用於合併兩個或多個SELECT語句的結果集。
UNION和UNION ALL（把重複的一併顯示）
SELECT * FROM tab_student WHERE ssex=‘女’
UNION
SELECT * FROM tab_student WHERE ssex=‘男’
5、GROUP BY語句
SELECT sno, SUM(sscore) AS total_score FROM t_score GROUP BY sno HAVING SUM(sscore)>=150
6、分頁查詢
SELECT *FROM 表名 LIMIT 5 OFFSET 5;
7、建立檢視
CREATE VIEW 檢視名 AS SELECT 語句
8、建立索引
CREATE INDEX index_mytable_categoryid ON mytable (category_id);
五、常用函式
1、總和 SUM()
2、平均數 AVG()
3、總計數 COUNT()
4、列中最大數 MAX()
5、列中最小數 MIN()
六、程式中使用資料庫
使用這些函式前要include
1、int sqlite3_open(const char *,sqlite3 **db)
2、int sqlite3_close(sqlite3 *db)
3、int sqlite3_get_table(
sqlite3*, const char *sql, char ***presult, int *nrow, int *ncolumn, char **errmsg)
4、sqlite3_free_table(char **presult);
七、事物
1、SQL語句
BEGIN [ DEFERRED IMMEDIATE EXCLUSIVE ] [TRANSACTION [name]]
END [TRANSACTION [name]]
COMMIT [TRANSACTION [name]]
ROLLBACK [TRANSACTION [name]]
2、鎖
SHARED鎖 RESERVED鎖 PENDING鎖 EXCLUSIVE鎖
3、觸發器
CREATE TRIGGER tbl_aaa AFTER UPDATE of user_id ON Tbl_user
BEGIN
SELECT RAISE(ROLLBACK, "員工號不能被修改！") FROM Tbl_user;
END;
4、RAISE 函式
(1) ROLLBACK
RAISE ( ROLLBACK, error-message )
當發生約束衝突，立即ROLLBACK，即結束當前事務處理，命令中止並返回SQLITE_CONSTRAINT程式碼。若當前無活動事務(除了每一條命令建立的預設事務以外)，則該演算法與ABORT相同。
(2) ABORT
RAISE ( ABORT, error-message )
當發生約束衝突，命令收回已經引起的改變並中止返回SQLITE_CONSTRAINT。但由於不執行ROLLBACK，所以前面的命令產生的改變將予以保留。預設採用這一行為。
(3) FAIL
RAISE ( FAIL, error-message )
當發生約束衝突，命令中止返回SQLITE_CONSTRAINT。但遇到衝突之前的所有改變將被保留。例如，若一條UPDATE語句在100行遇到衝突100th，前99行的改變將被保留，而對100行或以後的改變將不會發生。
(4) IGNORE
RAISE ( IGNORE )
當發生約束衝突，發生衝突的行將不會被插入或改變。但命令將照常執行。在衝突行之前或之後的行將被正常的插入和改變，且不返回錯誤資訊。
(5) REPLACE
RAISE (REPLACE, error-message )
當發生UNIQUE約束衝突，先存在的，導致衝突的行在更改或插入發生衝突的行之前被刪除。這樣，更改和插入總是被執行。命令照常執行且不返回錯誤資訊。當發生NOT NULL約束衝突，導致衝突的NULL值會被欄位預設值取代。若欄位無預設值，執行ABORT演算法。
5、NEW和Old關鍵字
CREATE TRIGGER update_customer_address
UPDATE OF address ON customers
BEGIN
UPDATE orders SET address = new.address
WHERE customer_name = old.name;
END;
八、時間/日期函式
DATETIME().......................產生日期和時間
date()...........................產生日期
time()...........................產生時間
strftime().......................對以上三個函式產生的日期和時間進行格式化
DATETIME ()的用法是：DATETIME (日期/時間,修正符,修正符...)
date()和time()的語法與DATETIME ()相同。
在時間/日期函式裡可以使用如下格式的字串作為引數：
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是產生現在的時間。
舉例（寫這個筆記的時間是2006年10月17日晚8點到10點，測試環境：SQLite 2.8.17，WinXP，北京時間）：
例1.
SELECT DATETIME ('now');
結果：2006-10-17 12:55:54
例2.
SELECT DATETIME ('2006-10-17');
結果：2006-10-17 12:00:00
例3.
SELECT DATETIME ('2006-10-17 00:20:00','+1 hour','-12 minute');
結果：2006-10-17 01:08:00
例4.
SELECT DATE('2006-10-17','+1 day','+1 year');
結果：2007-10-18
例5.
SELECT DATETIME ('now','start of year');
結果：2006-01-01 00:00:00
例6.
SELECT DATETIME ('now','start of month');
結果：2006-10-01 00:00:00
例7.
SELECT DATETIME ('now','start of day');
結果：2006-10-17 00:00:00
例8.
SELECT DATETIME ('now','+10 hour','start of day','+10 hour');
結果：2006-10-17 10:00:00
例9.
SELECT DATETIME ('now','localtime');
結果：2006-10-17 21:21:47
例10.
SELECT DATETIME ('now','+8 hour');
結果：2006-10-17 21:24:45
例3中的+1 hour和-12 minute表示可以在基本時間上（datetime函式的第一個引數）增加或減少一定時間。
例5中的start of year表示一年開始的時間。
從例8可以看出，儘管第2個引數加上了10個小時，但是卻被第3個引數“start of day”把時間歸零到00:00:00，隨後的第4個引數在00:00:00
的基礎上把時間增加了10個小時變成了10:00:00。
例9把格林威治時區轉換成本地時區。
例10把格林威治時區轉換成東八區。
strftime()函式可以把YYYY-MM-DD HH:MM:SS格式的日期字串轉換成其它形式的字串。
strftime()的語法是strftime(格式, 日期/時間, 修正符, 修正符, ...)
它可以用以下的符號對日期和時間進行格式化：
%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
%% 百分號
strftime()的用法舉例如下：
例11.
SELECT strftime('%Y.%m.%d %H:%M:%S','now','localtime');
結果：2006.10.17 21:41:09
例11用圓點作為日期的分隔附，並把時間轉換為當地的時區的時間。
更多關於SQLite日期時間函式方面的內容，可以參考Chris Newman寫的《SQLite》（ISBN：0-672-32685-X）中的《Working with Dates and
Times》一文。
今天的
SELECT time>= DATETIME ('now','start of day','+0 day') AND time< DATETIME ('now','start of day','+1 day') from 表
昨天的
SELECT time>= DATETIME ('now','start of day','-1 day') AND time< DATETIME ('now','start of day','+0 day') from 表
本週
SELECT time>=DATETIME ('now','start of day','-6 day','weekday 1') AND time
本月
SELECT Time>= DATETIME ('now','start of month','+0 month','-0 day') AND Time < datetime('now','start of month','+1 month','0 day') from 表
上一月
SELECT Time>= DATETIME ('now','start of month','-1 month','-0 day') AND Time < DATETIME ('now','start of month','+0 month','-1 day') from 表
九. 手冊