sqlite 表的列增加，修改，删除及表的重构和去重操作

sql 表的列增加，修改，删除

--增加一列
alter table song_hot_local add songNameDec TEXT
--增加多列
alter table one.dbo.StuInfo add Column_A varchar(10),Column_B varchar(10)
--修改列类型
alter table one.dbo.StuInfo alter column Column_A int
--修改列的名称(sp_rename 是一个存储过程)
EXEC sp_rename 'Age','Column_C'
--设置某个字段为无用状态
alter table 表名 set unuesed column 字段名
--删除列
alter table one.dbo.StuInfo drop column Column_A
--修改数据
UPDATE 表名称 SET 列名称1 = 新值，列名称2 = 新值 WHERE 列名称 = 某值

--查询库的版本号
pragma user_version
--升级库的版本号
PRAGMA user_version=22024

--去重并排序
select max(clickNum) gId,* from song_hot_local group by songName order by clickNum DESC

//新表创建步骤
create table song_single_local_temp(gId,songID, songName, songType, singerID, singer,leftChannel, rightChannel, volume,firstLetter,languageCode,hot,
clickTime,firstLetterLength, songPath, songScore, languageIntl, videoType, addDate, clickNum,isBan,reserve1,reserve2,songNameDec);

insert into song_single_local_temp (gId,songID, songName, songType, singerID, singer,leftChannel, rightChannel, volume,firstLetter,languageCode,hot,
clickTime,firstLetterLength, songPath, songScore, languageIntl, videoType, addDate, clickNum,isBan,reserve1,reserve2,songNameDec)
select max(clickNum) gId,* from song_hot_local where isBan = 0 group by songName order by clickNum DESC

CREATE TABLE song_single_local (songID INT PRIMARY KEY,songName TEXT,songType TEXT,singerID INT,singer TEXT,leftChannel INT,rightChannel INT,volume INT,firstLetter TEXT,languageCode TEXT,hot,clickTime INT,firstLetterLength,songPath TEXT,songScore,languageIntl TEXT,videoType TEXT,addDate TEXT,clickNum INT,isBan BOOLEAN DEFAULT 0,reserve1 TEXT,reserve2 TEXT,songNameDec TEXT,isSingle BOOLEAN DEFAULT 0);

insert into song_single_local (songID, songName, songType, singerID, singer,leftChannel, rightChannel, volume,firstLetter,languageCode,hot,clickTime,firstLetterLength, songPath, songScore, languageIntl, videoType, addDate, clickNum,isBan,reserve1,reserve2,songNameDec)
select songID, songName, songType, singerID, singer,leftChannel, rightChannel, volume,firstLetter, languageCode, hot, clickTime, firstLetterLength, songPath, songScore, languageIntl, videoType, addDate, clickNum, isBan, reserve1, reserve2, songNameDec from song_single_local_temp order by clickNum DESC

drop table if exists song_single_local_temp
//新表创建步骤end
//字段名更新去特殊字符
update song_hot_local set songName=substr(songName,1,instr(songName,'(' )-1) where songName like "%(%";

//字段名提取特殊字段并存储
update song_hot_local set songNameDec=substr(songName,instr(songName,'(' ),length(songName)) where songName like "%(%";

//去除字段中空格
update song_hot_local set songName=replace(songName,' ','')

//索引配置
CREATE INDEX idx_song_new_songname ON song_hot_local (songName ASC);

//设置去重表中重复
create table song_single_temp as select songID, songName,songType, singerID, singer,leftChannel, rightChannel, volume,firstLetter,languageCode,hot,clickTime,firstLetterLength, songPath, songScore, languageIntl, videoType, addDate, clickNum,isBan,reserve1,reserve2,songNameDec from song_hot_local where isBan = 0 and (song_hot_local.songID) not in (select songID from song_single_local);

update song_single_local set isSingle = '1' where(song_single_local.songName) in (select songName from song_single_temp);
//设置去重表中重复end