Last active
March 12, 2023 21:32
-
-
Save sdwh/80e36a81a165095d860626d3e71a0f40 to your computer and use it in GitHub Desktop.
[SQLite - Examples] #sqlite
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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(,) */ | |
) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
conn = sqlite3.connect(dbPath) | |
c = conn.cursor() | |
c.execute(sqlString) | |
c.commit() |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/*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' ) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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') |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WHERE Date('now', '-6 days','weekday 1') <= Date(dateColumn) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WHERE date(columnDateString) = date('now') |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WHERE date(columnDateString) = date('now', 'start of day', '-1 days') |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 | |
) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* 清除暫存容量 */ | |
VACUUM; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment