常用sql语句总结

1.创建表create

create table if not exists ${tablename} (${sqldata})

• 创建一张表，如果表明不存在
• tablename - 表名
• sqldata 表数据结构
  • id integer primary key autoincrement 数字类型主键，自增
  • chatId varchar(255) 255长度字符串

2.查询SELECT

2.1普通查询

SELECT * FROM ${tablename} WHERE id=?

2.2组合过滤AND,OR

SELECT * FROM ${tablename} WHERE chatId=? AND (chatType=? OR chatType=?)

2.3对搜索结果进行排序 ORDER BY

2.3.1降序DESC

SELECT * FROM ${tablename} WHERE chatId=? ORDER BY createTime DESC

2.3.2升序ASC

SELECT * FROM ${tablename} WHERE chatId=? ORDER BY createTime ASC

2.4 模糊搜索 LIKE '%xxx%'

SELECT * FROM ${tablename} WHERE chatId=? AND textContent LIKE '%?%' ORDER BY createTime ASC

2.5 分页搜索 LIMIT

SELECT * FROM ${tablename} WHERE chatId=? ORDER BY createTime ASC LIMIT ${page * limit}, ${limit}

2.6 查找唯一不同值 DISTINCT

SELECT DISTINCT chatId FROM ${tablename}

2.7 查找个数 COUNT(*)

SELECT COUNT(*) FROM ${tablename} WHERE chatId=?

2.8 条件查询 >,<,>=,<=

• SELECT * FROM ${tablename} WHERE chatId=? AND createTime > ? ORDER BY createTime DESC
• SELECT * FROM ${tablename} WHERE chatId=? AND createTime <= ? ORDER BY createTime DESC

3.更新UPDATE

UPDATE ${tablename} SET status=? WHERE chatMsgId=?

4.删除delete

DELETE FROM ${tablename} WHERE chatId=?