/** 创建表*/
CREATE TABLE IF NOT EXISTS table_student (
primaryId INTEGER PRIMARY KEY AUTOINCREMENT NOTNULL,
name TEXT NOTNULL,
age INTEGER,
height REAL,
createTime datetimedefault(datetime('now','localtime')),
updateTime datetimedefault(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'默认值'NOTNULL;
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 LIMIT10;
SELECT * FROM table_name LIMIT10OFFSET10;
/** 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');
网友评论