- substr(x, y) 获取截取后的字符串substr,
例如: 表data_info 中包含[2DCode] ,[Rand] 两个字段,截取[2Dcode]部分字符串—
select substr([2DCode],37) ,[Rand]
from data_info - instr(x,y)返回某个字符在字符串中的位置instr,可与substr(x,y)连用。
例如:
select substr([2DCode], instr([2DCode],’=’)+1) ,[Rand] from data_info - distinct 关键字 distinct
- count( distinct column名 ) 聚合函数与distinct 连用count(distinct) ,在任何只有一个参数的聚合函数中,可以在这个参数前使用distinct 关键字。例如:获取表suitData—_info 表中的去重后的boxUII的数量—select count(distinct [boxUII]) from suitData_info
- 查询db文件中所有表的索引名
SELECT DISTINCT m.name || ‘.’ || ii.name AS ‘indexed-columns’
FROM sqlite_master AS m,
pragma_index_list(m.name) AS il,
pragma_index_info(il.name) AS ii
WHERE m.type=‘table’
ORDER BY 1;
6. select 数据表中的重复数据:having
select 字段 from 表 where 字段 in (Select 字段 from 表
where 条件
Group by 字段
Having count(字段)>1)
例如: select bottle_id from bottle_in_box
where bottle_id in (Select bottle_id from bottle_in_box
where pro_bill=‘E20190812309’
Group by bottle_id
Having count(bottle_id )>1);
或者是:
select column1 from table1 where column1
in (Select column1 from table1
where 条件
Group by column1
Having count(column1)>1)
7. 查询两张表:使用 as 关键字
as
select b.bill,(A.numc*B.spec) as result from
(select count(distinct bottle_id) as numc from bottle_in_box where pro_bill=‘E2019081923’ ) A,
(
select p.product_bill as bill, p.PRODUCT_SPEC as spec from plan_history p where p.product_bill=‘E2019081923’
)B
- IN 操作符允许我们在 WHERE 子句中规定多个值
例如: SELECT * FROM Persons
WHERE LastName IN (‘Adams’,‘Carter’);
批量更新时,也可以使用
update table1 set column1 =0 where column2 in (values1,values2,…) ;
注: 可下载SQLite.Net文件查阅,点击sqlite.helper下载