[SQLite - Examples] #sqlite

CreateTable.sql

CREATE TABLE "DocDirectory" (
	"ID"	INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE,
	"DocPath"	TEXT,
	"DocDirType"	TEXT,
	"DocType"	TEXT,
	"DocNumber"	TEXT,
	"DocFileName"	TEXT,
	"DocTitle"	TEXT,
	"DocOwner"	TEXT,
	"DocOwnerDept"	TEXT,
	"DocDept"	TEXT,
	"DocOwnerTitle"	TEXT,
	"DocOwnerTime"	TEXT,
	"RecvId"	INTEGER /* Last Column Must Without Comma(,) */
)

pythonSqliteConnect.py

conn = sqlite3.connect(dbPath)
c = conn.cursor()
c.execute(sqlString)
c.commit()

sqldatetime.sql

/*dateString format string*/

strftime('%Y-%m-%d %H', dateTimeString)

/*dateString manupulate*/

/*ref: http://pro.ctlok.com/2010/08/sqlite-date-time.html*/

datetime('now', 'start of day', '3 hours', '3 days', 'localtime')

/*group by weekly*/

datetime(dateTimeString, 'weekday 0', '-6 days', 'start of day' )

sqlite-datetime.sql

select count(*) from tb where strftime('%Y/%m/%d', RecordDate) = strftime('%Y/%m/%d', 'now')

select strftime('%Y/%m/%d', 'now');
select strftime('%H:%M', RecordDate) from tb where date(RecordDate)
select strftime('%Y/%m/%d', RecordDate) from tb
select strftime('%Y/%m/%d', RecordDate) from tb

select strftime('%H', RecordDate), count(*) from tb Group by strftime('%H', RecordDate)
HAVING strftime('%Y/%m/%d', RecordDate) = strftime('%Y/%m/%d', 'now') 

sqlthisweek.sql

WHERE Date('now', '-6 days','weekday 1') <= Date(dateColumn)

sqltoday.sql

WHERE date(columnDateString) = date('now')

sqlview.sql

CREATE View v_quesInfo
        AS
        select id, 
        CAST(replace(trim(substr(examName, 0, 4)), 'Q', '') as integer) AS year,
        trim(substr(examName, instr(examName, 'Q'), 2)) as quarter, 
        CASE 
            When trim(substr(examName, 8)) like '證券投資與財務分析' Then 'investFin'
            When trim(substr(examName, 8)) like '證券交易相關法規與實務' Then 'law'
            ELSE 'unknown'
        END examName,
        CASE 
            When trim(substr(examName, 8)) like '%高業%' Then 'high'
            When trim(substr(examName, 8)) like '%hand%' Then 'hand'
            ELSE 'basic'
        END level, 
        ans
        from tb_lq
        where ifnull(ans, 1) != 1

sqlyesterday.sql

WHERE date(columnDateString) = date('now', 'start of day', '-1 days')

updateFromJoin.sql

update DocDirectory  set RecvId =
(
select RecvId from 
(select DocDirectory.ID ,DocRecvDirectory.recvId from DocDirectory left outer join DocRecvDirectory on DocDirectory.DocPath = DocRecvDirectory.DocPath)
where DocDirectory.ID = ID
)

vaccum.sql

/* 清除暫存容量 */
VACUUM;