android sqlite数据库的基本操作

作者: 全球顶尖伪极客 | 来源:发表于2018-08-23 11:02 被阅读0次

场景1：

记录A、B、C三种问题类型的数据，需要按照服务端返回的类型分组
将每种类型数据升序排列
可以根据记录问题的任务名字、问题类型、问题描述进行数据筛选模糊查询。
相关表：wd_pz_xj_record t1、wd_pz_xj_checknum_bydianweitype t2。

分析

已知记录的问题在表t1，类型全量表在t2
记录问题的时候可能并非每个类型的问题都全部记录，记录问题顺序也并非ABC，有可能是BBCBAC
再加上可以模糊查询则设计到like % %，而可以存在多个字段相似则均可匹配进行查询，经测试需要用·括号 和or进行。
按照ABC类型排序，则在从服务端返回数据时根据存储生成的id进行升序排序分组，左外连接将t1.taskId=t2.taskId,

SELECT * FROM (( SELECT * FROM wd_pz_xj_record ) t1
LEFT OUTER JOIN ( SELECT id, task_id, checkCategory_id FROM wd_pz_xj_checknum_bydianweitype ) t2 ON t2.checkCategory_id = t1.dianwei_groupid 
AND t1.task_id = t2.task_id 
) 
WHERE
    t1.task_id = ? 
ORDER BY
    t2.id,
    t2.checkCategory_id,
    record_time


 String sql="SELECT * FROM   (( SELECT * FROM wd_pz_xj_record ) t1 " +
 " LEFT OUTER JOIN ( SELECT id, task_id, checkCategory_id FROM wd_pz_xj_checknum_bydianweitype) t2 " +
 " ON t2.checkCategory_id = t1.dianwei_groupid AND t1.task_id= t2.task_id) " +
 " WHERE t1.task_id = ? and t1.issue_type=? and  (t1.dianwei_groupname like '%" + descInfo + "%'  or t1.louceng_name like  '%" + descInfo + "%'   or t1.dianwei_name like   '%" + descInfo + "%' ) " +
 " ORDER BY t2.id , t2.checkCategory_id , record_time ";
cursor = db.rawQuery(sql,new String[]{taskID, wtType});

select t1.task_id,t1.dianwei_id,t1.dianwei_name,t1.louceng_name,t1.dianwei_type,t1.dianwei_status, dianweiTotalCount ,t2.dianWeiCompletedCountFrom(
SELECT t.task_id,t.dianwei_id,t.dianwei_name,t.louceng_name,t.dianwei_type,t.dianwei_status,COUNT(t.dianwei_id) as dianweiTotalCount from 
android sqlite语句练习···
同步滚动：
select t1.task_id,t1.dianwei_id,t1.dianwei_name,t1.louceng_name,t1.dianwei_type,t1.dianwei_status, dianweiTotalCount ,t2.dianWeiCompletedCountFrom(
SELECT t.task_id,t.dianwei_id,t.dianwei_name,t.louceng_name,t.dianwei_type,t.dianwei_status,COUNT(t.dianwei_id) as dianweiTotalCount from wd_pz_xj_dianwei t ) t1 LEFT join (select DISTINCT t.dianwei_id ,COUNT(t.dianwei_id) as dianWeiCompletedCountFROM wd_pz_xj_dianwei t WHERE t.dianwei_status ='已完成'GROUP BY t.dianwei_type) t2 on t1.dianwei_id=t2.dianwei_id;


SELECT t1.task_id,t1.dianwei_id,t1.dianwei_name,t1.louceng_name,t1.dianwei_type,t1.dianwei_status,dianweiTotalCount,t2.dianWeiCompletedCount  
FROM(SELECT t.task_id,t.dianwei_id,t.dianwei_name,t.louceng_name,t.dianwei_type,t.dianwei_status,COUNT(t.dianwei_id) as dianweiTotalCount FROM wd_pz_xj_dianwei t 
) t1 
LEFT JOIN (SELECT DISTINCT t.dianwei_id,COUNT(t.dianwei_id) as dianWeiCompletedCount FROM wd_pz_xj_dianwei t 
WHERE t.dianwei_status='已完成' GROUP BY t.dianwei_type 
)t2 on t1.dianwei_id=t2.dianwei_id WHERE task_id='2222';

SELECT t1.dianwei_groupid,t1.dianwei_groupname,t1.dianWeiTotalCount

SELECT t1.dianwei_groupid,t1.dianwei_groupname,t1.dianWeiTotalCount,t2.dianWeiCompleteCount FROM
(SELECT t.task_id,t.dianwei_groupid,t.dianwei_groupname, COUNT(t.dianwei_id)  as dianWeiTotalCount 
from wd_pz_xj_dianwei t WHERE task_id=? GROUP BY t.dianwei_groupid
) t1
left join
(SELECT t.task_id,t.dianwei_groupid,t.dianwei_groupname, COUNT(t.dianwei_id)  as dianWeiCompleteCount 
from wd_pz_xj_dianwei t WHERE task_id=? GROUP BY t.dianwei_groupid
) t2
on t1.task_id=t2.task_id and t1.dianwei_groupid=t2.dianwei_groupid WHERE t1.task_id='2222' and t1.dianwei_groupid='10001'

SELECT checkItem_id,checkItem_name,dianwei_type,checkCategory_id,checkCategory_name from wd_pz_xj_checkitem WHERE task_id=? and checkCategory_id=?
SELECT checkItem_id,checkItem_name,checkCategory_id,checkCategory_name,dianwei_type " +
                                "from wd_pz_xj_checkitem WHERE task_id='2222' and checkCategory_id='10001'

SELECT DISTINCT  t.task_id,t.task_name,t.checkCategory_id,t.checkCategory_name,t.checkItem_id,
t.checkItem_name,t.dianwei_type
FROM wd_pz_xj_checkitem t   GROUP BY checkCategory_name

SELECT COUNT( dianwei_id) FROM wd_pz_xj_dianwei WHERE dianwei_type='SITE_WSJ'


SELECT
    t1.*,t2.*,count(t2.dianwei_id)
FROM
    (
SELECT
    t.task_id,
    t.checkitem_id,
    t.checkItem_name,
    COUNT( t.checkItem_id ) AS checkcount,
    t.checkCategory_id,
    t.checkCategory_name ,
    t.dianwei_type
FROM
    wd_pz_xj_checkitem t 
WHERE
    t.task_id = '2222' 
GROUP BY
    t.dianwei_type 
    ) t1
    LEFT JOIN (
SELECT
    t.task_id,
    t.dianwei_id,
    t.dianwei_type,
    t.dianwei_name,
    COUNT( t.dianwei_id ) AS dianWeiTotalCount 
FROM
    wd_pz_xj_dianwei t 
WHERE
    t.task_id = '2222' 
    GROUP BY t.dianwei_type

    ) t2 ON t2.task_id = t1.task_id  and t1.dianwei_type  LIKE '%t2.dianwei_type%';
    
--  ///////////////////////////////////////////////////////////////////////////////////

SELECT t1.checkItem_id,t1.checkItem_name,t1.checkCategory_id,t1.checkCategory_name,t1.dianwei_type
,t2.dianwei_type,count(t2.dianwei_id) as dianweiCount FROM
(
    SELECT * FROM wd_pz_xj_checkitem WHERE task_id='2222' GROUP BY checkCategory_name
) t1 LEFT JOIN
(
    SELECT * FROM wd_pz_xj_dianwei WHERE task_id='2222'
) t2 on t1.task_id=t2.task_id  GROUP BY checkCategory_name;
-- ====================================================================
SELECT t1.checkCategory_id,t1.checkCategory_name,t2.dianweiTotalCount FROM
(SELECT DISTINCT  t.task_id,t.task_name,t.checkCategory_id,t.checkCategory_name,t.checkItem_id,
t.checkItem_name,t.dianwei_type
FROM wd_pz_xj_checkitem t   GROUP BY checkCategory_name
) t1
left join
(select DISTINCT t.task_id,t.dianwei_id,t.dianwei_name,t.louceng_name,t.dianwei_type,t.dianwei_status,COUNT(t.dianwei_id) as dianweiTotalCount FROM wd_pz_xj_dianwei t
GROUP BY dianwei_type
) t2 on t1.task_id=t2.task_id -- WHERE t1.dianwei_type LIKE '%t2.dianwei_type%' ;
-- ////////////////////////////////////////////////////////////////
SELECT t1.checkCategory_id,t1.checkCategory_name,t2.dianweiTotalCount FROM
(SELECT DISTINCT  t.task_id,t.task_name,t.checkCategory_id,t.checkCategory_name,t.checkItem_id,
t.checkItem_name,t.dianwei_type
FROM wd_pz_xj_checkitem t
) t1
left join
(select DISTINCT t.task_id,t.dianwei_id,t.dianwei_name,t.louceng_name,t.dianwei_type,t.dianwei_status,COUNT(t.dianwei_id) as dianweiTotalCount FROM wd_pz_xj_dianwei t  GROUP BY t.dianwei_type
) t2 on t1.task_id=t2.task_id WHERE t1.task_id="2222"  and t1.dianwei_type LIKE '%t2.dianwei_type%'  GROUP BY t1.checkCategory_name

-- /////////////////////////////////////////////////////////////////////
SELECT t1.checkCategory_id,t1.checkCategory_name,t2.dianweiTotalCount,t1.dianwei_type,t2.dianwei_type FROM
(SELECT DISTINCT  t.task_id,t.task_name,t.checkCategory_id,t.checkCategory_name,t.checkItem_id,
t.checkItem_name,t.dianwei_type
FROM wd_pz_xj_checkitem t
) t1
left join
(select DISTINCT t.task_id,t.dianwei_id,t.dianwei_name,t.louceng_name,t.dianwei_type,t.dianwei_status,COUNT(t.dianwei_id) as dianweiTotalCount FROM wd_pz_xj_dianwei t GROUP BY t.dianwei_type
) t2 on t1.task_id=t2.task_id WHERE t1.task_id="2222" GROUP BY t1.checkCategory_name


-- 、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、
SELECT t1.checkCategory_id,t1.checkCategory_name,t1.dianwei_type,t2.dianwei_type FROM
(SELECT DISTINCT  t.task_id,t.task_name,t.checkCategory_id,t.checkCategory_name,t.checkItem_id,
t.checkItem_name,t.dianwei_type
FROM wd_pz_xj_checkitem t
) t1
join
(select DISTINCT t.task_id,t.dianwei_id,t.dianwei_name,t.louceng_name,t.dianwei_type,t.dianwei_status FROM wd_pz_xj_dianwei t
) t2 on t1.task_id=t2.task_id and t1.dianwei_type LIKE '%t2.dianwei_type%' GROUP BY t1.checkCategory_name

-- //////////////////////////////////////////////////////////////
SELECT t1.checkCategory_id,t1.checkCategory_name,t1.dianwei_type ,COUNT(t1.checkCategory_id)
(SELECT t1.checkCategory_id,t1.checkCategory_name,t1.dianwei_type,t2.dianwei_type as dianwei_type2 FROM
(SELECT DISTINCT  t.task_id,t.task_name,t.checkCategory_id,t.checkCategory_name,t.checkItem_id,
t.checkItem_name,t.dianwei_type
FROM wd_pz_xj_checkitem t
) t1
join
(select DISTINCT t.task_id,t.dianwei_id,t.dianwei_name,t.louceng_name,t.dianwei_type,t.dianwei_status FROM wd_pz_xj_dianwei t
) t2 on t1.task_id=t2.task_id and t1.dianwei_type LIKE '%+t2.dianwei_type+%' GROUP BY t1.checkCategory_name
)t1 where t1.dianwei_type=t1.dianwei_type2
-- ............,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
SELECT t1.checkCategory_id,t1.checkCategory_name,t1.dianwei_type,t2.dianwei_type FROM
(SELECT DISTINCT  t.task_id,t.task_name,t.checkCategory_id,t.checkCategory_name,t.checkItem_id,
t.checkItem_name,t.dianwei_type
FROM wd_pz_xj_checkitem t GROUP BY t.dianwei_type
) t1
left join
(select DISTINCT t.task_id,t.dianwei_id,t.dianwei_name,t.louceng_name,t.dianwei_type,t.dianwei_status FROM wd_pz_xj_dianwei t
) t2 on t1.task_id=t2.task_id and t1.dianwei_type LIKE ('%'+t2.dianwei_type+'%')
-- //////////////////////////////////////////////////////////////////////
SELECT t1.checkCategory_id,t1.checkCategory_name,t1.dianwei_type,t2.dianwei_type FROM
(select DISTINCT t.task_id,t.dianwei_id,t.dianwei_name,t.louceng_name,t.dianwei_type,t.dianwei_status FROM wd_pz_xj_dianwei t
) t2
left join
(SELECT DISTINCT  t.task_id,t.task_name,t.checkCategory_id,t.checkCategory_name,t.checkItem_id,
t.checkItem_name,t.dianwei_type
FROM wd_pz_xj_checkitem t GROUP BY t.dianwei_type
) t1
 on t1.task_id=t2.task_id and t1.dianwei_type LIKE ('%'+t2.dianwei_type+'%')





SELECT DISTINCT t1.*,t2.device_modelType as deviceModelType ,t2.dianWeiCompletedCount as dianWeiCompletedCount,t2.dianWeiTotalCount as dianWeiTotalCount
FROM
(
select t.* FROM wd_pz_xj_checknum_bydianweitype t
) t1
left join
(
SELECT t1.task_id,t1.dianwei_groupid,t1.dianwei_groupname,t1.dianWeiTotalCount,t1.device_modelType,t2.dianWeiCompletedCount
FROM 
(SELECT t.task_id,t.dianwei_groupid,t.dianwei_groupname, COUNT(t.dianwei_id)  as dianWeiTotalCount ,t.device_modelType
from wd_pz_xj_dianwei t GROUP BY t.task_id,t.dianwei_groupid 
) t1 
left join 
(SELECT t.task_id,t.dianwei_groupid,t.dianwei_groupname, t.dianwei_status ,COUNT(t.dianwei_id)  as dianWeiCompletedCount 
from wd_pz_xj_dianwei t  WHERE t.dianwei_status='已完成'   GROUP BY t.task_id,t.dianwei_groupid   
)t2  on t1.task_id=t2.task_id and t1.dianwei_groupid=t2.dianwei_groupid 
) t2 on t1.task_id=t2.task_id and t1.checkCategory_id=t2.dianwei_groupid  
WHERE t1.task_id='2c90a7a165381c7401653b896dd72f76'
GROUP BY t1.task_id,t1.checkCategory_id;   


update wd_pz_xj_dianwei set dianwei_status='已完成' ,dianwei_completeTime = '2018' where task_id='2c90a7a165381c7401653b896dd72f76' and dianwei_id='L127968' ;
SELECT t.dianwei_status,t.dianwei_completeTime from wd_pz_xj_dianwei t WHERE t.task_id='2c90a7a165381c7401653b896dd72f76' and t.dianwei_id='L127968' wd_pz_xj_dianwei t ) t1 LEFT join (select DISTINCT t.dianwei_id ,COUNT(t.dianwei_id) as dianWeiCompletedCountFROM wd_pz_xj_dianwei t WHERE t.dianwei_status ='已完成'GROUP BY t.dianwei_type) t2 on t1.dianwei_id=t2.dianwei_id;


SELECT t1.task_id,t1.dianwei_id,t1.dianwei_name,t1.louceng_name,t1.dianwei_type,t1.dianwei_status,dianweiTotalCount,t2.dianWeiCompletedCount  
FROM(SELECT t.task_id,t.dianwei_id,t.dianwei_name,t.louceng_name,t.dianwei_type,t.dianwei_status,COUNT(t.dianwei_id) as dianweiTotalCount FROM wd_pz_xj_dianwei t 
) t1 
LEFT JOIN (SELECT DISTINCT t.dianwei_id,COUNT(t.dianwei_id) as dianWeiCompletedCount FROM wd_pz_xj_dianwei t 
WHERE t.dianwei_status='已完成' GROUP BY t.dianwei_type 
)t2 on t1.dianwei_id=t2.dianwei_id WHERE task_id='2222';

SELECT t1.dianwei_groupid,t1.dianwei_groupname,t1.dianWeiTotalCount

SELECT t1.dianwei_groupid,t1.dianwei_groupname,t1.dianWeiTotalCount,t2.dianWeiCompleteCount FROM
(SELECT t.task_id,t.dianwei_groupid,t.dianwei_groupname, COUNT(t.dianwei_id)  as dianWeiTotalCount 
from wd_pz_xj_dianwei t WHERE task_id=? GROUP BY t.dianwei_groupid
) t1
left join
(SELECT t.task_id,t.dianwei_groupid,t.dianwei_groupname, COUNT(t.dianwei_id)  as dianWeiCompleteCount 
from wd_pz_xj_dianwei t WHERE task_id=? GROUP BY t.dianwei_groupid
) t2
on t1.task_id=t2.task_id and t1.dianwei_groupid=t2.dianwei_groupid WHERE t1.task_id='2222' and t1.dianwei_groupid='10001'

SELECT checkItem_id,checkItem_name,dianwei_type,checkCategory_id,checkCategory_name from wd_pz_xj_checkitem WHERE task_id=? and checkCategory_id=?
SELECT checkItem_id,checkItem_name,checkCategory_id,checkCategory_name,dianwei_type " +
                                "from wd_pz_xj_checkitem WHERE task_id='2222' and checkCategory_id='10001'

SELECT DISTINCT  t.task_id,t.task_name,t.checkCategory_id,t.checkCategory_name,t.checkItem_id,
t.checkItem_name,t.dianwei_type
FROM wd_pz_xj_checkitem t   GROUP BY checkCategory_name

SELECT COUNT( dianwei_id) FROM wd_pz_xj_dianwei WHERE dianwei_type='SITE_WSJ'


SELECT
    t1.*,t2.*,count(t2.dianwei_id)
FROM
    (
SELECT
    t.task_id,
    t.checkitem_id,
    t.checkItem_name,
    COUNT( t.checkItem_id ) AS checkcount,
    t.checkCategory_id,
    t.checkCategory_name ,
    t.dianwei_type
FROM
    wd_pz_xj_checkitem t 
WHERE
    t.task_id = '2222' 
GROUP BY
    t.dianwei_type 
    ) t1
    LEFT JOIN (
SELECT
    t.task_id,
    t.dianwei_id,
    t.dianwei_type,
    t.dianwei_name,
    COUNT( t.dianwei_id ) AS dianWeiTotalCount 
FROM
    wd_pz_xj_dianwei t 
WHERE
    t.task_id = '2222' 
    GROUP BY t.dianwei_type

    ) t2 ON t2.task_id = t1.task_id  and t1.dianwei_type  LIKE '%t2.dianwei_type%';
    
--  ///////////////////////////////////////////////////////////////////////////////////

SELECT t1.checkItem_id,t1.checkItem_name,t1.checkCategory_id,t1.checkCategory_name,t1.dianwei_type
,t2.dianwei_type,count(t2.dianwei_id) as dianweiCount FROM
(
    SELECT * FROM wd_pz_xj_checkitem WHERE task_id='2222' GROUP BY checkCategory_name
) t1 LEFT JOIN
(
    SELECT * FROM wd_pz_xj_dianwei WHERE task_id='2222'
) t2 on t1.task_id=t2.task_id  GROUP BY checkCategory_name;
-- ====================================================================
SELECT t1.checkCategory_id,t1.checkCategory_name,t2.dianweiTotalCount FROM
(SELECT DISTINCT  t.task_id,t.task_name,t.checkCategory_id,t.checkCategory_name,t.checkItem_id,
t.checkItem_name,t.dianwei_type
FROM wd_pz_xj_checkitem t   GROUP BY checkCategory_name
) t1
left join
(select DISTINCT t.task_id,t.dianwei_id,t.dianwei_name,t.louceng_name,t.dianwei_type,t.dianwei_status,COUNT(t.dianwei_id) as dianweiTotalCount FROM wd_pz_xj_dianwei t
GROUP BY dianwei_type
) t2 on t1.task_id=t2.task_id -- WHERE t1.dianwei_type LIKE '%t2.dianwei_type%' ;
-- ////////////////////////////////////////////////////////////////
SELECT t1.checkCategory_id,t1.checkCategory_name,t2.dianweiTotalCount FROM
(SELECT DISTINCT  t.task_id,t.task_name,t.checkCategory_id,t.checkCategory_name,t.checkItem_id,
t.checkItem_name,t.dianwei_type
FROM wd_pz_xj_checkitem t
) t1
left join
(select DISTINCT t.task_id,t.dianwei_id,t.dianwei_name,t.louceng_name,t.dianwei_type,t.dianwei_status,COUNT(t.dianwei_id) as dianweiTotalCount FROM wd_pz_xj_dianwei t  GROUP BY t.dianwei_type
) t2 on t1.task_id=t2.task_id WHERE t1.task_id="2222"  and t1.dianwei_type LIKE '%t2.dianwei_type%'  GROUP BY t1.checkCategory_name

-- /////////////////////////////////////////////////////////////////////
SELECT t1.checkCategory_id,t1.checkCategory_name,t2.dianweiTotalCount,t1.dianwei_type,t2.dianwei_type FROM
(SELECT DISTINCT  t.task_id,t.task_name,t.checkCategory_id,t.checkCategory_name,t.checkItem_id,
t.checkItem_name,t.dianwei_type
FROM wd_pz_xj_checkitem t
) t1
left join
(select DISTINCT t.task_id,t.dianwei_id,t.dianwei_name,t.louceng_name,t.dianwei_type,t.dianwei_status,COUNT(t.dianwei_id) as dianweiTotalCount FROM wd_pz_xj_dianwei t GROUP BY t.dianwei_type
) t2 on t1.task_id=t2.task_id WHERE t1.task_id="2222" GROUP BY t1.checkCategory_name


-- 、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、
SELECT t1.checkCategory_id,t1.checkCategory_name,t1.dianwei_type,t2.dianwei_type FROM
(SELECT DISTINCT  t.task_id,t.task_name,t.checkCategory_id,t.checkCategory_name,t.checkItem_id,
t.checkItem_name,t.dianwei_type
FROM wd_pz_xj_checkitem t
) t1
join
(select DISTINCT t.task_id,t.dianwei_id,t.dianwei_name,t.louceng_name,t.dianwei_type,t.dianwei_status FROM wd_pz_xj_dianwei t
) t2 on t1.task_id=t2.task_id and t1.dianwei_type LIKE '%t2.dianwei_type%' GROUP BY t1.checkCategory_name

-- //////////////////////////////////////////////////////////////
SELECT t1.checkCategory_id,t1.checkCategory_name,t1.dianwei_type ,COUNT(t1.checkCategory_id)
(SELECT t1.checkCategory_id,t1.checkCategory_name,t1.dianwei_type,t2.dianwei_type as dianwei_type2 FROM
(SELECT DISTINCT  t.task_id,t.task_name,t.checkCategory_id,t.checkCategory_name,t.checkItem_id,
t.checkItem_name,t.dianwei_type
FROM wd_pz_xj_checkitem t
) t1
join
(select DISTINCT t.task_id,t.dianwei_id,t.dianwei_name,t.louceng_name,t.dianwei_type,t.dianwei_status FROM wd_pz_xj_dianwei t
) t2 on t1.task_id=t2.task_id and t1.dianwei_type LIKE '%+t2.dianwei_type+%' GROUP BY t1.checkCategory_name
)t1 where t1.dianwei_type=t1.dianwei_type2
-- ............,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
SELECT t1.checkCategory_id,t1.checkCategory_name,t1.dianwei_type,t2.dianwei_type FROM
(SELECT DISTINCT  t.task_id,t.task_name,t.checkCategory_id,t.checkCategory_name,t.checkItem_id,
t.checkItem_name,t.dianwei_type
FROM wd_pz_xj_checkitem t GROUP BY t.dianwei_type
) t1
left join
(select DISTINCT t.task_id,t.dianwei_id,t.dianwei_name,t.louceng_name,t.dianwei_type,t.dianwei_status FROM wd_pz_xj_dianwei t
) t2 on t1.task_id=t2.task_id and t1.dianwei_type LIKE ('%'+t2.dianwei_type+'%')
-- //////////////////////////////////////////////////////////////////////
SELECT t1.checkCategory_id,t1.checkCategory_name,t1.dianwei_type,t2.dianwei_type FROM
(select DISTINCT t.task_id,t.dianwei_id,t.dianwei_name,t.louceng_name,t.dianwei_type,t.dianwei_status FROM wd_pz_xj_dianwei t
) t2
left join
(SELECT DISTINCT  t.task_id,t.task_name,t.checkCategory_id,t.checkCategory_name,t.checkItem_id,
t.checkItem_name,t.dianwei_type
FROM wd_pz_xj_checkitem t GROUP BY t.dianwei_type
) t1
 on t1.task_id=t2.task_id and t1.dianwei_type LIKE ('%'+t2.dianwei_type+'%')





SELECT DISTINCT t1.*,t2.device_modelType as deviceModelType ,t2.dianWeiCompletedCount as dianWeiCompletedCount,t2.dianWeiTotalCount as dianWeiTotalCount
FROM
(
select t.* FROM wd_pz_xj_checknum_bydianweitype t
) t1
left join
(
SELECT t1.task_id,t1.dianwei_groupid,t1.dianwei_groupname,t1.dianWeiTotalCount,t1.device_modelType,t2.dianWeiCompletedCount
FROM 
(SELECT t.task_id,t.dianwei_groupid,t.dianwei_groupname, COUNT(t.dianwei_id)  as dianWeiTotalCount ,t.device_modelType
from wd_pz_xj_dianwei t GROUP BY t.task_id,t.dianwei_groupid 
) t1 
left join 
(SELECT t.task_id,t.dianwei_groupid,t.dianwei_groupname, t.dianwei_status ,COUNT(t.dianwei_id)  as dianWeiCompletedCount 
from wd_pz_xj_dianwei t  WHERE t.dianwei_status='已完成'   GROUP BY t.task_id,t.dianwei_groupid   
)t2  on t1.task_id=t2.task_id and t1.dianwei_groupid=t2.dianwei_groupid 
) t2 on t1.task_id=t2.task_id and t1.checkCategory_id=t2.dianwei_groupid  
WHERE t1.task_id='2c90a7a165381c7401653b896dd72f76'
GROUP BY t1.task_id,t1.checkCategory_id;   


update wd_pz_xj_dianwei set dianwei_status='已完成' ,dianwei_completeTime = '2018' where task_id='2c90a7a165381c7401653b896dd72f76' and dianwei_id='L127968' ;
SELECT t.dianwei_status,t.dianwei_completeTime from wd_pz_xj_dianwei t WHERE t.task_id='2c90a7a165381c7401653b896dd72f76' and t.dianwei_id='L127968' 









select t1.task_id,t1.dianwei_id,t1.dianwei_name,t1.louceng_name,t1.dianwei_type,t1.dianwei_status, dianweiTotalCount ,t2.dianWeiCompletedCountFrom(
SELECT t.task_id,t.dianwei_id,t.dianwei_name,t.louceng_name,t.dianwei_type,t.dianwei_status,COUNT(t.dianwei_id) as dianweiTotalCount from wd_pz_xj_dianwei t ) t1 LEFT join (select DISTINCT t.dianwei_id ,COUNT(t.dianwei_id) as dianWeiCompletedCountFROM wd_pz_xj_dianwei t WHERE t.dianwei_status ='已完成'GROUP BY t.dianwei_type) t2 on t1.dianwei_id=t2.dianwei_id;


SELECT t1.task_id,t1.dianwei_id,t1.dianwei_name,t1.louceng_name,t1.dianwei_type,t1.dianwei_status,dianweiTotalCount,t2.dianWeiCompletedCount  
FROM(SELECT t.task_id,t.dianwei_id,t.dianwei_name,t.louceng_name,t.dianwei_type,t.dianwei_status,COUNT(t.dianwei_id) as dianweiTotalCount FROM wd_pz_xj_dianwei t 
) t1 
LEFT JOIN (SELECT DISTINCT t.dianwei_id,COUNT(t.dianwei_id) as dianWeiCompletedCount FROM wd_pz_xj_dianwei t 
WHERE t.dianwei_status='已完成' GROUP BY t.dianwei_type 
)t2 on t1.dianwei_id=t2.dianwei_id WHERE task_id='2222';

SELECT t1.dianwei_groupid,t1.dianwei_groupname,t1.dianWeiTotalCount

SELECT t1.dianwei_groupid,t1.dianwei_groupname,t1.dianWeiTotalCount,t2.dianWeiCompleteCount FROM
(SELECT t.task_id,t.dianwei_groupid,t.dianwei_groupname, COUNT(t.dianwei_id)  as dianWeiTotalCount 
from wd_pz_xj_dianwei t WHERE task_id=? GROUP BY t.dianwei_groupid
) t1
left join
(SELECT t.task_id,t.dianwei_groupid,t.dianwei_groupname, COUNT(t.dianwei_id)  as dianWeiCompleteCount 
from wd_pz_xj_dianwei t WHERE task_id=? GROUP BY t.dianwei_groupid
) t2
on t1.task_id=t2.task_id and t1.dianwei_groupid=t2.dianwei_groupid WHERE t1.task_id='2222' and t1.dianwei_groupid='10001'

SELECT checkItem_id,checkItem_name,dianwei_type,checkCategory_id,checkCategory_name from wd_pz_xj_checkitem WHERE task_id=? and checkCategory_id=?
SELECT checkItem_id,checkItem_name,checkCategory_id,checkCategory_name,dianwei_type " +
                                "from wd_pz_xj_checkitem WHERE task_id='2222' and checkCategory_id='10001'

SELECT DISTINCT  t.task_id,t.task_name,t.checkCategory_id,t.checkCategory_name,t.checkItem_id,
t.checkItem_name,t.dianwei_type
FROM wd_pz_xj_checkitem t   GROUP BY checkCategory_name

SELECT COUNT( dianwei_id) FROM wd_pz_xj_dianwei WHERE dianwei_type='SITE_WSJ'


SELECT
    t1.*,t2.*,count(t2.dianwei_id)
FROM
    (
SELECT
    t.task_id,
    t.checkitem_id,
    t.checkItem_name,
    COUNT( t.checkItem_id ) AS checkcount,
    t.checkCategory_id,
    t.checkCategory_name ,
    t.dianwei_type
FROM
    wd_pz_xj_checkitem t 
WHERE
    t.task_id = '2222' 
GROUP BY
    t.dianwei_type 
    ) t1
    LEFT JOIN (
SELECT
    t.task_id,
    t.dianwei_id,
    t.dianwei_type,
    t.dianwei_name,
    COUNT( t.dianwei_id ) AS dianWeiTotalCount 
FROM
    wd_pz_xj_dianwei t 
WHERE
    t.task_id = '2222' 
    GROUP BY t.dianwei_type

    ) t2 ON t2.task_id = t1.task_id  and t1.dianwei_type  LIKE '%t2.dianwei_type%';
    
--  ///////////////////////////////////////////////////////////////////////////////////

SELECT t1.checkItem_id,t1.checkItem_name,t1.checkCategory_id,t1.checkCategory_name,t1.dianwei_type
,t2.dianwei_type,count(t2.dianwei_id) as dianweiCount FROM
(
    SELECT * FROM wd_pz_xj_checkitem WHERE task_id='2222' GROUP BY checkCategory_name
) t1 LEFT JOIN
(
    SELECT * FROM wd_pz_xj_dianwei WHERE task_id='2222'
) t2 on t1.task_id=t2.task_id  GROUP BY checkCategory_name;
-- ====================================================================
SELECT t1.checkCategory_id,t1.checkCategory_name,t2.dianweiTotalCount FROM
(SELECT DISTINCT  t.task_id,t.task_name,t.checkCategory_id,t.checkCategory_name,t.checkItem_id,
t.checkItem_name,t.dianwei_type
FROM wd_pz_xj_checkitem t   GROUP BY checkCategory_name
) t1
left join
(select DISTINCT t.task_id,t.dianwei_id,t.dianwei_name,t.louceng_name,t.dianwei_type,t.dianwei_status,COUNT(t.dianwei_id) as dianweiTotalCount FROM wd_pz_xj_dianwei t
GROUP BY dianwei_type
) t2 on t1.task_id=t2.task_id -- WHERE t1.dianwei_type LIKE '%t2.dianwei_type%' ;
-- ////////////////////////////////////////////////////////////////
SELECT t1.checkCategory_id,t1.checkCategory_name,t2.dianweiTotalCount FROM
(SELECT DISTINCT  t.task_id,t.task_name,t.checkCategory_id,t.checkCategory_name,t.checkItem_id,
t.checkItem_name,t.dianwei_type
FROM wd_pz_xj_checkitem t
) t1
left join
(select DISTINCT t.task_id,t.dianwei_id,t.dianwei_name,t.louceng_name,t.dianwei_type,t.dianwei_status,COUNT(t.dianwei_id) as dianweiTotalCount FROM wd_pz_xj_dianwei t  GROUP BY t.dianwei_type
) t2 on t1.task_id=t2.task_id WHERE t1.task_id="2222"  and t1.dianwei_type LIKE '%t2.dianwei_type%'  GROUP BY t1.checkCategory_name

-- /////////////////////////////////////////////////////////////////////
SELECT t1.checkCategory_id,t1.checkCategory_name,t2.dianweiTotalCount,t1.dianwei_type,t2.dianwei_type FROM
(SELECT DISTINCT  t.task_id,t.task_name,t.checkCategory_id,t.checkCategory_name,t.checkItem_id,
t.checkItem_name,t.dianwei_type
FROM wd_pz_xj_checkitem t
) t1
left join
(select DISTINCT t.task_id,t.dianwei_id,t.dianwei_name,t.louceng_name,t.dianwei_type,t.dianwei_status,COUNT(t.dianwei_id) as dianweiTotalCount FROM wd_pz_xj_dianwei t GROUP BY t.dianwei_type
) t2 on t1.task_id=t2.task_id WHERE t1.task_id="2222" GROUP BY t1.checkCategory_name


-- 、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、
SELECT t1.checkCategory_id,t1.checkCategory_name,t1.dianwei_type,t2.dianwei_type FROM
(SELECT DISTINCT  t.task_id,t.task_name,t.checkCategory_id,t.checkCategory_name,t.checkItem_id,
t.checkItem_name,t.dianwei_type
FROM wd_pz_xj_checkitem t
) t1
join
(select DISTINCT t.task_id,t.dianwei_id,t.dianwei_name,t.louceng_name,t.dianwei_type,t.dianwei_status FROM wd_pz_xj_dianwei t
) t2 on t1.task_id=t2.task_id and t1.dianwei_type LIKE '%t2.dianwei_type%' GROUP BY t1.checkCategory_name

-- //////////////////////////////////////////////////////////////
SELECT t1.checkCategory_id,t1.checkCategory_name,t1.dianwei_type ,COUNT(t1.checkCategory_id)
(SELECT t1.checkCategory_id,t1.checkCategory_name,t1.dianwei_type,t2.dianwei_type as dianwei_type2 FROM
(SELECT DISTINCT  t.task_id,t.task_name,t.checkCategory_id,t.checkCategory_name,t.checkItem_id,
t.checkItem_name,t.dianwei_type
FROM wd_pz_xj_checkitem t
) t1
join
(select DISTINCT t.task_id,t.dianwei_id,t.dianwei_name,t.louceng_name,t.dianwei_type,t.dianwei_status FROM wd_pz_xj_dianwei t
) t2 on t1.task_id=t2.task_id and t1.dianwei_type LIKE '%+t2.dianwei_type+%' GROUP BY t1.checkCategory_name
)t1 where t1.dianwei_type=t1.dianwei_type2
-- ............,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
SELECT t1.checkCategory_id,t1.checkCategory_name,t1.dianwei_type,t2.dianwei_type FROM
(SELECT DISTINCT  t.task_id,t.task_name,t.checkCategory_id,t.checkCategory_name,t.checkItem_id,
t.checkItem_name,t.dianwei_type
FROM wd_pz_xj_checkitem t GROUP BY t.dianwei_type
) t1
left join
(select DISTINCT t.task_id,t.dianwei_id,t.dianwei_name,t.louceng_name,t.dianwei_type,t.dianwei_status FROM wd_pz_xj_dianwei t
) t2 on t1.task_id=t2.task_id and t1.dianwei_type LIKE ('%'+t2.dianwei_type+'%')
-- //////////////////////////////////////////////////////////////////////
SELECT t1.checkCategory_id,t1.checkCategory_name,t1.dianwei_type,t2.dianwei_type FROM
(select DISTINCT t.task_id,t.dianwei_id,t.dianwei_name,t.louceng_name,t.dianwei_type,t.dianwei_status FROM wd_pz_xj_dianwei t
) t2
left join
(SELECT DISTINCT  t.task_id,t.task_name,t.checkCategory_id,t.checkCategory_name,t.checkItem_id,
t.checkItem_name,t.dianwei_type
FROM wd_pz_xj_checkitem t GROUP BY t.dianwei_type
) t1
 on t1.task_id=t2.task_id and t1.dianwei_type LIKE ('%'+t2.dianwei_type+'%')



SELECT DISTINCT t1.*,t2.device_modelType as deviceModelType ,t2.dianWeiCompletedCount as dianWeiCompletedCount,t2.dianWeiTotalCount as dianWeiTotalCount
FROM (select t.* FROM wd_pz_xj_checknum_bydianweitype t) t1
left join
(SELECT t1.task_id,t1.dianwei_groupid,t1.dianwei_groupname,t1.dianWeiTotalCount,t1.device_modelType,t2.dianWeiCompletedCount
FROM
(SELECT t.task_id,t.dianwei_groupid,t.dianwei_groupname, COUNT(t.dianwei_id)  as dianWeiTotalCount ,t.device_modelType
from wd_pz_xj_dianwei t 
) t1 
left join 
(SELECT t.task_id,t.dianwei_groupid,t.dianwei_groupname, t.dianwei_status ,COUNT(t.dianwei_id)  as dianWeiCompletedCount
from wd_pz_xj_dianwei t  WHERE t.dianwei_status='已完成'   
)t2  on t1.task_id=t2.task_id and t1.dianwei_groupid=t2.dianwei_groupid 
) t2 on t1.task_id=t2.task_id and t1.checkCategory_id=t2.dianwei_groupid 
WHERE t1.task_id='ff8080816668e0bd01666b2092bb4053'
GROUP BY t1.task_id,t1.checkCategory_id 

select t.* FROM wd_pz_xj_checknum_bydianweitype t WHERE t.task_id='ff8080816668e0bd01666b2092bb4053'

select t.* FROM wd_pz_xj_dianwei t WHERE t.task_id='ff8080816668e0bd01666b2092bb4053' GROUP BY t.dianwei_groupid

SELECT DISTINCT t1.*,t2.device_modelType as deviceModelType ,t2.dianWeiCompletedCount as dianWeiCompletedCount,t2.dianWeiTotalCount as dianWeiTotalCount
FROM (select t.* FROM wd_pz_xj_checknum_bydianweitype t) t1
left join
(SELECT t1.task_id,t1.dianwei_groupid,t1.dianwei_groupname,t1.dianWeiTotalCount,t1.device_modelType,t2.dianWeiCompletedCount
FROM
(SELECT t.task_id,t.dianwei_groupid,t.dianwei_groupname, COUNT(t.dianwei_id)  as dianWeiTotalCount ,t.device_modelType
from wd_pz_xj_dianwei t  WHERE t.task_id='ff8080816668e0bd01666b2092bb4053' GROUP BY t.task_id,t.dianwei_groupid
) t1 
left join 
(SELECT t.task_id,t.dianwei_groupid,t.dianwei_groupname, t.dianwei_status ,COUNT(t.dianwei_id)  as dianWeiCompletedCount
from wd_pz_xj_dianwei t  WHERE t.dianwei_status='已完成'   GROUP BY t.task_id,t.dianwei_groupid 
)t2  on t1.task_id=t2.task_id and t1.dianwei_groupid=t2.dianwei_groupid 
) t2 on t1.task_id=t2.task_id and t1.checkCategory_id=t2.dianwei_groupid 
WHERE t1.task_id='ff8080816668e0bd01666b2092bb4053'
GROUP BY t1.task_id,t1.checkCategory_id ORDER BY t1.id



select dianwei_groupid,dianwei_groupname,louceng_name ,COUNT(id) as louceng_name_num 
FROM wd_pz_xj_dianwei WHERE dianwei_status='已完成' and task_id='ff80808165f9e61b016611073e932682' group by dianwei_groupid,louceng_name


SELECT t3.dianwei_groupid,t3.dianwei_groupname ,t3.louceng_name,t3.louceng_name_num,t4.completeLoucengCount FROM
(SELECT t1.dianwei_groupid,t1.dianwei_groupname, t2.louceng_name,t2.louceng_name_num FROM
(select dianwei_groupid,dianwei_groupname from wd_pz_xj_dianwei 
WHERE task_id='ff8080816618ee07016618fa64a3003e' group by dianwei_groupid) t1
left JOIN 
(select dianwei_groupid,dianwei_groupname ,louceng_name ,COUNT(id) as louceng_name_num 
from wd_pz_xj_dianwei 
WHERE task_id='ff8080816618ee07016618fa64a3003e' group by dianwei_groupid,louceng_name) t2
on t1.dianwei_groupid=t2.dianwei_groupid
) t3
LEFT JOIN (select dianwei_groupid,dianwei_groupname ,louceng_name ,COUNT(id) as completeLoucengCount 
from wd_pz_xj_dianwei 
WHERE task_id='ff8080816618ee07016618fa64a3003e' and dianwei_status='已完成' group by dianwei_groupid,louceng_name) t4

on t3.dianwei_groupid=t4.dianwei_groupid and t3.louceng_name=t4.louceng_name;


SELECT t1.*,t2.qiandao_way as qiandao_way t2.qian FROM
(SELECT * FROM wd_pz_xj_record t  
WHERE t.task_id='ff8080816618ee07016618fa64a3003e') t1 LEFT JOIN 
(SELECT * FROM wd_pz_xj_dianwei WHERE task_id='ff8080816618ee07016618fa64a3003e' ) t2
on t1.dianwei_id=t2.dianwei_id and t1.dianwei_groupid=t2.dianwei_groupid;

SELECT * from wd_pz_xj_record WHERE task_id='ff8080816618ee07016618fa64a3003e' and (dianwei_groupname like '%吊顶%' or louceng_name like  '%吊顶%'  or dianwei_name like  '%吊顶%' )

SELECT t.* FROM wd_pz_xj_record t  WHERE t.task_id='ff8080816618ee07016618fa64a3003e' and t.issue_type='N' and  t.dianwei_groupname like '%吊顶%'  or t.louceng_name like  '%吊顶%'   or t.dianwei_name like   '%吊顶%'


SELECT * from wd_pz_xj_record WHERE task_id=? and  dianwei_groupid=? and dianwei_id=? and checkItem_id=?



select distinct dianwei_id,dianwei_name,louceng_name,dianwei_status,dianwei_type from wd_pz_xj_dianwei where task_id=? and dianwei_groupid = ? and louceng_name=? and device_modelType=?  and dianwei_name like '%xx%' ORDER BY dianwei_status DESC , louceng_name ASC,dianwei_name ASC

selectSql=select distinct dianwei_id,dianwei_name,louceng_name,dianwei_status,dianwei_type from wd_pz_xj_dianwei;

 WHERESql1=task_id=? and dianwei_groupid = ?
WHERESql2=and louceng_name=? 
 WHERESql3=and device_modelType=? 
WHERESql4=and dianwei_id=?
 
 String orderSql= dianwei_status DESC , louceng_name ASC,dianwei_name ASC
 
 SELECT selectSql WHERE WHERESql1+WHERESql2+WHERESql3+WHERESql4 ORDER BY orderSql
 
 
 
:
你把一条sql语句拆分成各个子句，where条件的，order条件的
:
单独写逻辑
:
最后拼接到一起
:
sql = select子句+where子句+。。。

 where task_id=? and dianwei_groupid = ? and louceng_name=? and device_modelType=? 
 String s="task_id=? and dianwei_groupid = ?"
 String s1="and louceng_name=? ";
 String s2="and device_modelType=? ";
 String s3="and dianwei_id=?"
 ORDER BY dianwei_status DESC , louceng_name ASC,dianwei_name ASC

String orderStr="ORDER BY dianwei_status DESC , louceng_name ASC,dianwei_name ASC"









SELECT DISTINCT t1.*,t2.device_modelType as deviceModelType ,t2.dianWeiCompletedCount as dianWeiCompletedCount,t2.dianWeiTotalCount as dianWeiTotalCount
FROM (select t.* FROM wd_pz_xj_checknum_bydianweitype t) t1
left join
(SELECT t1.task_id,t1.dianwei_groupid,t1.dianwei_groupname,t1.dianWeiTotalCount,t1.device_modelType,t2.dianWeiCompletedCount
FROM
(SELECT t.task_id,t.dianwei_groupid,t.dianwei_groupname, COUNT(t.dianwei_id)  as dianWeiTotalCount ,t.device_modelType
from wd_pz_xj_dianwei t GROUP BY t.task_id,t.dianwei_groupid 
) t1 
left join 
(SELECT t.task_id,t.dianwei_groupid,t.dianwei_groupname, t.dianwei_status ,COUNT(t.dianwei_id)  as dianWeiCompletedCount
from wd_pz_xj_dianwei t  WHERE t.dianwei_status='已完成'   GROUP BY t.task_id,t.dianwei_groupid 
)t2  on t1.task_id=t2.task_id and t1.dianwei_groupid=t2.dianwei_groupid 
) t2 on t1.task_id=t2.task_id and t1.checkCategory_id=t2.dianwei_groupid 
WHERE t1.task_id=''
GROUP BY t1.task_id,t1.checkCategory_id ORDER BY t1.id;









insert into wd_photo (id,record_id) values ('1','dsds')

select count(dianwei_id) FROM wd_pz_xj_dianwei WHERE dianwei_status='已完成' and task_id='' and dianwei_groupid='' and dianwei_id='';
select count(dianwei_id) as dianweiCompleteCount FROM wd_pz_xj_dianwei WHERE dianwei_status='已完成' and task_id=? and dianwei_groupid=? and dianwei_id=?

SELECT DISTINCT t1.*,t2.device_modelType as deviceModelType ,t2.dianWeiCompletedCount as dianWeiCompletedCount,t2.dianWeiTotalCount as dianWeiTotalCount
FROM
(
select t.* FROM wd_pz_xj_checknum_bydianweitype t
) t1
left join
(
SELECT t1.task_id,t1.dianwei_groupid,t1.dianwei_groupname,t1.dianWeiTotalCount,t1.device_modelType,t2.dianWeiCompletedCount
FROM 
(SELECT t.task_id,t.dianwei_groupid,t.dianwei_groupname, COUNT(t.dianwei_id)  as dianWeiTotalCount ,t.device_modelType
from wd_pz_xj_dianwei t GROUP BY t.task_id,t.dianwei_groupid 
) t1 
left join 
(SELECT t.task_id,t.dianwei_groupid,t.dianwei_groupname, t.dianwei_status ,COUNT(t.dianwei_id)  as dianWeiCompletedCount 
from wd_pz_xj_dianwei t  WHERE t.dianwei_status='已完成'   GROUP BY t.task_id,t.dianwei_groupid   
)t2  on t1.task_id=t2.task_id and t1.dianwei_groupid=t2.dianwei_groupid 
) t2 on t1.task_id=t2.task_id and t1.checkCategory_id=t2.dianwei_groupid  
WHERE t1.task_id='2c90a7a165381c7401653b896dd72f76'
GROUP BY t1.task_id,t1.checkCategory_id;   

select distinct dianwei_id,dianwei_name,louceng_name,dianwei_status from wd_pz_xj_dianwei where task_id='2c90a7a165381c7401653b896dd72f76' and dianwei_groupid = '2c90a7a165370e0901653712acd40004'  ORDER BY dianwei_status DESC , louceng_name ASC,dianwei_name ASC


select dianwei_groupid,dianwei_groupname,louceng_name ,COUNT(id) as louceng_name_num 
FROM wd_pz_xj_dianwei WHERE dianwei_status='已完成' and task_id='ff80808165f9e61b016611073e932682' group by dianwei_groupid

select dianwei_groupid,dianwei_groupname
FROM wd_pz_xj_dianwei WHERE dianwei_status='已完成' and task_id='ff80808165f9e61b016611073e932682' group by dianwei_groupid





SELECT * FROM
 (( SELECT * FROM wd_pz_xj_record WHERE task_id = 'ff808081669b18610166a9eceada6325') t1
 LEFT OUTER JOIN ( SELECT id, task_id, checkCategory_id FROM wd_pz_xj_checknum_bydianweitype) t2
 ON t2.checkCategory_id = t1.dianwei_groupid AND t1.task_id= t2.task_id)
 ORDER BY t2.id asc, t2.checkCategory_id asc , record_time desc

细节：
1，什么是主键：
用来唯一标识一张表中的某一条数据，所有的表都必须有自己的主键，主键可以是整型，一般都用自增，也可以是字符串类型，如果是字符串类型的话一般使用uuid（是用java语言生成的）。一般使用主键来唯一查询某一条记录，或者更新删除某一条记录。

2，order by永远放到最后面，语法规范！

3，函数
函数count：用来统计数量，一般统计行数有几行
函数max: 用来计算某列中的最大值，列的类型一定是整型（一般情况下遇到类似年龄，分数等将来可能会计算最大值的或者本身就是数字，类型设置为整型）。
函数sum: 类似max，列的字段类型一定是整型

4，左连接：左边表全部显示，右边显示匹配成功的。
5，全连接：左右两边完全匹配成功。
6，sqlite 无右连接

-------------------表的插入------------
insert into students (id,name,age,sex)values('3','Mike','21','女');--add
insert into students (id,name,age,sex)values('4','Lisa','21','女');--add
insert into students (id,name,age,sex)values('5','Raro','21','女');--add
insert into students (id,num,name,age,sex,c_id)values(6,'201107014303','Jame','21','女','')
------------条件删除 ---------------------
delete from students where name='Jame';--delete
select *from students;
select *from students where name='Mike' and age='12';
select * from students where name like 's%' collate nocase;
------------修改表名------------
alter table student rename to students--修改表名
alter table students {rename to student|add column course text}--不识别 {}:表示为可选项，但是运行失败

------------添加列---------------------
alter table students add column tel text not null default '' collate nocase

------------条件修改数据-----------
update students set tel='18500111111' where name='lisa' collate nocase
update students set num='201107014301'
update students set age='20' where name='Mike'

----------创建表，id，course两列
create table course (id integer primary key autoincrement, course text);
------------表删除--，只能删除表，不能删除列----------
drop table course;--删除一张表（没有关联关系）

-----------如果想忽略大小写，即 case-insensitive，需要用到COLLATE NOCASE ：-----------
update students set age='12' where name='mike' collate nocase;

----------执行以下语句查看所有用户的用户名和年龄：
select name,age from students;
select *from cqx_db where type='table' and name='teacher';

--查看一张表中字段name没有重复的条目
select distinct name from students;

select * from students group by age having count(*)>1;

-------------limit a offset b或者limit b ,a ---表示调过b项，返回a项数据--------
select * from students where num like '_011%' order by age asc, name limit 1,2;--如果相同，则根据名字的首字母排序
select * from students where num like '2011%' order by age asc -- num 以2011结尾的所有学生升序，默认为升序
select * from students where num like '2011%' order by age desc, age limit 2 offset 1;--根据年龄排序
select * from students where num like '2011%' limit 2 offset 1;

--------------------'%a' :以a结尾(_a),'a%':以a开头 '%a%':包含a-------------------------------
select * from students where num like '%01' order by age desc; 以01结尾的匹配
select * from students where num not like '%02%';选取不包含02的num

--函数upper（ name ）大写，lower( name ) 小写，count()统计数量大小，sum(age) 求和,max(age)求最大值
select upper(name) ,length(name) from students where num like '2011%'
select lower(name) ,length(name) from students where num like '2011%'
select upper(name) ,length(name) from students where num like '%01' and length(name)<5
----------分组group by- 并采用函数计算租大小---------
select * ,count()from students group by num;--按照num进行分组
select count(*) from students where num like '%01';
-------------------------去掉重复 distinct-----------------------
select distinct num from students ;

---------------多表连接 join-------------------------

insert into teacher (id,t_name,t_course) values(1,'a','语文')
insert into teacher (id,t_name,t_course) values(2,'a','语文')
insert into teacher (id,t_name,t_course) values(3,'a','语文')

update teacher set c_id='1'
update students set c_id=12 where num like '%01';
update teacher set c_id=1;
--添加列 not null时 default ''
alter table teacher add column c_id text not null default '';
alter table teacher drop column c_course;
select teacher.t_name,students.name from teacher ,students where teacher.c_id=students.c_id;

------------------------内连接 inner join--------------
select * from teacher inner join students on teacher.c_id=students.c_id;
alter table course add column id integer;
insert into course values(1,1,'语文');
insert into course values(1,2,'语文');
select * from students inner join course on students.c_id=course.c_id;
---------------交叉连接-----------------
select * from students,course;
----------左外连接未能匹配以左为基准，右为null--，无右连接------------
select * from students left outer join teacher on students.c_id=teacher.c_id;

--------------------别名-类似表名.相同的列名，from +原名别名------------------------
select s.name,t.name from students s,teacher t where s.c_id=t.c_id limit 3;

--------------null 不等于任何值是确实信息的占位符
--三种逻辑运算
select null is null;--结果1 非0的任何值都表示为真
select null is not null;--结果0 非0的任何值都表示为真

-------------coalesce函数表示为将一组值输入并返回其中第一个非null的值------
select coalesce(null,7,null,4);--结果7

-------------nullif函数相反，表示为2个值输入，如果相同则返回null,否则返回第一个参数----
select nullif (1,1);--结果null
select nullif (1,2);--结果1

-------子查询-------------
select teacher.name from teacher where teacher.c_id in(select students.c_id from students where num like '2011%')
select count(name) from students where students.[c_id] in(select teacher.c_id from teacher )
select * from students group by num like '%01'

---------==================考核练习================================-----
---查询
select * from students where name='Lisa';
select name,sex from students where name like '%am%'; --查询包含am的所有name,sex
-----分组-------
select sex, count(id) from students group by sex; --根据性别sex分组，查询sex和对用的数量
select sex,age,count(id) from students group by sex, age; --根据性别和年龄分组，查询性别和年龄以及数量
select name,age from students order by age desc ; --根据年龄降序查询显示姓名和年龄

------------------------------函数max: 用来计算某列中的最大值，列的类型一定是整型（一般情况下遇到类似年龄，分数等将来可能会计算最大值的或者本身就是数字，类型设置为整型）。-------------------------------------------
select max(age) from students ; --从学生表中选取年龄最大值

--从学生表中选取年龄最大值的姓名和对应年龄
select name, age from students where age in (select max(age) from students)
--计算学生表中的年龄和
select sum(age) from students ;

--按照性别分组并显示性别和对应的和
select sex, count(id) from students group by sex;

--采用别名的方式全连接，别名规则为from+原表名别名，别的地方使用到表名则采用别名
select t.t_name,s.name from teacher t,students s where t.c_id=s.c_id;

--左连接通过学生和教师表查询学生和教师名称 sqlite无右连接
select students.name,teacher.t_name from students left join teacher on students.c_id=teacher.c_id;
--通过别名的方式左连接
select s.name,t.t_name from students s left join teacher t on s.c_id=t.c_id;
--通过左连接查询性别为女和年龄大于21的
select s.name,t.t_name,s.age from students s left join teacher t on s.c_id=t.c_id where sex='女' and age>21 ;

android sqlite数据库的基本操作

场景1：

分析

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读