iOS sqlite3语句学习

/** 创建表*/
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;
/** 删除表中所有数据*/
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的数量*/
SELECT COUNT(column_name) FROM table_name WHERE {CONDITION};
/** 1.查询表中所有数据*/
SELECT * FROM table_student;
/** 2.查询表中name以‘li’开头的所有数据，大小写敏感
    星号（*）代表零个、一个或多个数字或字符；
    问号（?）代表一个单一的数字或字符
*/
SELECT * FROM table_student WHERE name GLOB 'li*';
/** 3.查询表中name以‘li’开头的所有数据，大小写不敏感
    百分号（%）代表零个、一个或多个数字或字符；
    下划线（_）代表一个单一的数字或字符
*/
SELECT * FROM table_student WHERE name LIKE 'li%';
/** 4.查询所有在数据库中创建的表*/
SELECT tbl_name
FROM sqlite_master
WHERE type = 'table';
/** 5.查询表完整信息(如：创建表sql语句)*/
SELECT sql
FROM sqlite_master
WHERE type = 'table'
AND tbl_name = 'table_student';
/** 6.获取当前本地时间*/
SELECT datetime('now', 'localtime');
/** 7.Limit子句限制由SELECT语句返回的数据数量*/
SELECT * FROM table_name LIMIT 10;
SELECT * FROM table_name LIMIT 10 OFFSET 10;
/** 8.ORDER BY子句是用来基于一个或多个列按升序或降序顺序排列数据*/
SELECT *
FROM table_name
WHERE conditions
ORDER BY column1, column2, ..., columnN
{ASC|DESC};
/** 9.GROUP BY子句用于与SELECT语句一起使用，来对相同的数据进行分组
    GROUP BY子句放在WHERE子句之后，放在ORDER BY子句之前
*/
SELECT *
FROM table_name
WHERE conditions
GROUP BY column1, column2, ..., columnN
ORDER BY column1, column2, ..., columnN;
/** 10.HAVING子句允许指定条件来过滤将出现在最终结果中的分组结果
    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算起的秒数)
    %s 从1970-01-01 00:00:00算起的秒数
*/
SELECT strftime('%s','now');
/** 当前本地时间(2017-12-28 15:42:30)*/
SELECT datetime('now', 'localtime');