/** 創(chuàng)建表*/
CREATE TABLE IF NOT EXISTS table_student (
    primaryId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    name TEXT NOT NULL,
    age INTEGER,
    height REAL,
    createTime datetime default (datetime('now', 'localtime')),
    updateTime datetime default (datetime('now', 'localtime')),
    kv BLOB
);

/** 刪除表*/
DROP TABLE table_student;
/** 刪除表中所有數(shù)據*/
DELETE FROM table_student;

/** 表新增列／表重命名*/
ALTER TABLE table_student
ADD COLUMN school TEXT;

ALTER TABLE table_student
ADD COLUMN school TEXT DEFAULT '默認值' NOT NULL;

ALTER TABLE table_student
RENAME TO new_table_student;

/** 增*/
INSERT INTO table_student(name, age, height)
VALUES ('zhangsan', 15, 1.63);

INSERT INTO first_table_name(column1, column2, ... , columnN)
SELECT column1, column2, ..., columnN FROM second_table_name;

/** 刪*/
DELETE FROM table_name WHERE {CONDITION};

/** 改*/
UPDATE table_name
SET column1 = value1, column2 = value2, ..., columnN = valueN
WHERE {CONDITION};

UPDATE table_student
SET name = 'fuck', age = 25, updateTime = datetime('now', 'localtime')
WHERE name = 'lisi';

/** 查*/
SELECT COUNT(*) FROM table_name WHERE {CONDITION};
/** 查詢表中column_name字段對應值非NULL的數(shù)量*/
SELECT COUNT(column_name) FROM table_name WHERE {CONDITION};
/** 1.查詢表中所有數(shù)據*/
SELECT * FROM table_student;
/** 2.查詢表中name以‘li’開頭的所有數(shù)據，大小寫敏感
    星號（*）代表零個、一個或多個數(shù)字或字符；
    問號（?）代表一個單一的數(shù)字或字符
*/
SELECT * FROM table_student WHERE name GLOB 'li*';
/** 3.查詢表中name以‘li’開頭的所有數(shù)據，大小寫不敏感
    百分號（%）代表零個、一個或多個數(shù)字或字符；
    下劃線（_）代表一個單一的數(shù)字或字符
*/
SELECT * FROM table_student WHERE name LIKE 'li%';
/** 4.查詢所有在數(shù)據庫中創(chuàng)建的表*/
SELECT tbl_name
FROM sqlite_master
WHERE type = 'table';
/** 5.查詢表完整信息(如：創(chuàng)建表sql語句)*/
SELECT sql
FROM sqlite_master
WHERE type = 'table'
AND tbl_name = 'table_student';
/** 6.獲取當前本地時間*/
SELECT datetime('now', 'localtime');
/** 7.Limit子句限制由SELECT語句返回的數(shù)據數(shù)量*/
SELECT * FROM table_name LIMIT 10;
SELECT * FROM table_name LIMIT 10 OFFSET 10;
/** 8.ORDER BY子句是用來基于一個或多個列按升序或降序順序排列數(shù)據*/
SELECT *
FROM table_name
WHERE conditions
ORDER BY column1, column2, ..., columnN
{ASC|DESC};
/** 9.GROUP BY子句用于與SELECT語句一起使用，來對相同的數(shù)據進行分組
    GROUP BY子句放在WHERE子句之后，放在ORDER BY子句之前
*/
SELECT *
FROM table_name
WHERE conditions
GROUP BY column1, column2, ..., columnN
ORDER BY column1, column2, ..., columnN;
/** 10.HAVING子句允許指定條件來過濾將出現(xiàn)在最終結果中的分組結果
    HAVING 子句必須放在GROUP BY子句之后，必須放在ORDER BY子句之前
*/
SELECT *
FROM table_name
WHERE conditions
GROUP BY column1, column2
HAVING conditions
ORDER BY column1, column2;

SELECT * FROM table_student
GROUP BY name
HAVING count(name) > 2;
/** 11.DISTINCT關鍵字與SELECT語句一起使用，來消除所有重復的記錄，并只獲取唯一一次記錄*/
SELECT DISTINCT column1
FROM table_name
WHERE [condition];

SELECT DISTINCT name FROM table_student;

/** 獲取時間*/
/** 獲取當前月最后一天*/
SELECT date('now','start of month','+1 month','-1 day');
/** 當前的UNIX時間戳(即從1970-01-01 00:00:00算起的秒數(shù))
    %s 從1970-01-01 00:00:00算起的秒數(shù)
*/
SELECT strftime('%s','now');
/** 當前本地時間(2017-12-28 15:42:30)*/
SELECT datetime('now', 'localtime');