sqlite相关命令笔记

1、sqlite sql修改/交换列名

引用：https://blog.csdn.net/qq_37059136/article/details/80886110

①修改原来表的名字

alter table HOUSE_DETAILS rename to HOUSE_DETAILS_BAK;

②新建修改列名之后的表

create table if not exists `HOUSE_DETAILS` (ID integer primary key autoincrement,HSID integer unique not null,AREA real,MARKETING date);

③从旧表中查询数据并插入新表，注意select的顺序

insert into HOUSE_DETAILS select ID,HSID,MARKETING,AREA from HOUSE_DETAILS_BAK;

④删除旧表

drop table HOUSE_DETAILS_BAK

 

2、表连接

左连接

select * from HOUSE_INFO left join HOUSE_DETAILS on HOUSE_INFO.HSID=HOUSE_DETAILS.HSID

3、sql计算总计

select sum(AREA) from HOUSE_DETAILS

 

4、sql删除重复的数据

DELETE FROM HOUSE_DETAILS
WHERE 1=1
AND `HSID` in (SELECT * FROM ( (SELECT `HSID` `HSID` FROM HOUSE_DETAILS GROUP BY HSID HAVING COUNT(`HSID`)>1) ) a)
AND id not in (SELECT * FROM ( (SELECT MIN(id) ids FROM HOUSE_DETAILS GROUP BY HSID HAVING COUNT(`HSID`)>1) ) b)