sqlite和MySQL一些常用命令_一些常用sqlite语句

1,如果表不存在就新建一个

CComBSTR bstrCreatBat(L”CREATE TABLE IF NOT EXISTS tb_Name (\

rowIdIndex  INTEGER PRIMARY KEY ASC AUTOINCREMENT,\

richContent TEXT,\

type        INTEGER,\

userId      INTEGER\

);”);

2,查询表是否存在

swprintf (buffexist, 511, L”SELECT COUNT(*) AS existence FROM sqlite_master WHERE type = ‘table’ and name = ‘%s';”,m_strTbName);

3,查询行数

swprintf(StrSQLCount, 1023,L”select count(*)  as sum from %s where userId = %I64d and subUserId = %I64d ;” ,

m_strTbName, userid, subid);

4,查询第m条到n条的数据

swprintf (StrSQL, 1023,

L”select * from %s where userId = %I64d and subUserId = %I64d order by sysTime limit %d, %d ;”,

m_strTbName, userid, subid, (iPage-1)*COUNT_ON_A_PAGE, COUNT_ON_A_PAGE);

5,查询id值最小的一条数据

swprintf (StrSQL, 1023,

L”select * from %s where id in (select min(id) from %s group by userid , subUserId) ;”,

m_strTbName, m_strTbName);