sqlite3修改列(字段)的名字

由于sqlite3数据库没有提供直接修改列名的语句,不过sqlite3可以用一张表给另一张表赋值(复制表)

由此可以想到通过复制表的方法修改表中列(字段)的名字

修改字段名可以分为以下几步:(假设我原有一张表List,里面有字段id,name,age,现改age为agenum)

①修改原来表的名字

alter table List rename to Listold

②新建修改列名之后的表

create table List ( id int primary key,name text,agenum text)

③从旧表中查询数据并插入新表

insert into List select id,name,age from Listold

④删除旧表

drop table Listold

下面我演示下代码

首先代码

#include "sqlite3.h"
#include <iostream>
#include "string.h"
#include <Windows.h>


#pragma comment(lib,"sqlite_win32_dll.lib")

using namespace std;

int main()
{ 
    sqlite3 * datb1;
	int ret = sqlite3_open("628demo.db",&datb1);
	if(ret == SQLITE_OK)
	{
		cout << "demo.db打开成功!" << endl;
	}
	else
	{
		cout << "demo.db打开失败!" << endl;
		return 0;
	}
	char * errmsg;                             //要定义全局变量
	char **dbresult;
	int j,nrow,ncolumn,index = 0;
	char str3[1024] = {"create table "};
	char * str11 = "Listqwe";
	strcat(str3,str11);
	char *str2 = "(id int primary key,name text,age text )";
	strcat(str3,str2);
	char sql[1024] = {0};
	sprintf(sql,"drop table if exists %s",str11);
	sqlite3_exec(datb1,sql,NULL,NULL,&errmsg);  //在创建表时,如果表存在,则删除
	
	ret = sqlite3_exec(datb1,str3,NULL,NULL,&errmsg);

	
	if(ret == SQLITE_OK)
	{
		cout << "Listqwe表创建成功!" << endl;
	}
//     ret = sqlite3_exec(datb1,"select * from Listqwe",NULL,NULL,&errmsg);
//
//;
	const char * str7 = "insert into Listqwe values('1','zhang','12')";
	const char * str8 = "insert into Listqwe values(2,'li','13')";
	const char * str9 = "insert into Listqwe values(3,'wang','14')";
	const char * str10 = "insert into Listqwe values(4,'cheng','15')";;
	sqlite3_exec(datb1,str7,NULL,NULL,&errmsg);
	sqlite3_exec(datb1,str8,NULL,NULL,&errmsg);
	sqlite3_exec(datb1,str9,NULL,NULL,&errmsg);
	sqlite3_exec(datb1,str10,NULL,NULL,&errmsg);

	 ret = sqlite3_get_table(datb1,"select * from Listqwe",&dbresult,&nrow,&ncolumn,&errmsg);
	 if(ret == SQLITE_OK)
	 	{
	 		cout << "查询到 " << nrow << " 行结果" << endl;
	 		//index = ncolumn;
	 		index = 0;
	 		for(int i = 0;i <= nrow; i++)
	 		{
	 			//printf("[%2i]",i);
	 			for(j = 0;j < ncolumn;j++)
	 			{
	 				printf(" %-9s",dbresult[index]);
	 				index++;
	 			}
	 			printf("\n");
	 		}
			cout << "行数:" << nrow << endl;
			cout << "列数:" << ncolumn << endl;
	 	    cout << "*********************" << endl;
	 		sqlite3_free_table(dbresult);
	 	}


	  sqlite3_exec(datb1,"drop table if exists Listqweold",NULL,NULL,&errmsg);  //在创建表时,如果表存在,则删除 
      ret = sqlite3_exec(datb1,"alter table Listqwe rename to Listqweold",NULL,NULL,&errmsg);
	  if(ret == SQLITE_OK)
		  {
		  	cout << "1" << endl;
		  }
	  else
	  {
            cout << "-1" << endl;
			cout << errmsg << endl;
	  }


	  ret = sqlite3_exec(datb1,"create table Listqwe (id int primary key,name text,agenum text)",NULL,NULL,&errmsg);
	  if(ret == SQLITE_OK)
	  {
		  cout << "2" << endl;
	  }
	  ret = sqlite3_exec(datb1,"insert into Listqwe select id,name,age from Listqweold",NULL,NULL,&errmsg);
	  if(ret == SQLITE_OK)
	  {
		  cout << "3" << endl;
	  }
	  else
	  {
		  cout << "-3" << endl;
		  cout << errmsg << endl;
	  }
	  ret = sqlite3_get_table(datb1,"select * from Listqwe",&dbresult,&nrow,&ncolumn,&errmsg);
	  if(ret == SQLITE_OK)
	 	{
			cout << "查询到 " << nrow << " 行结果" << endl;
			//index = ncolumn;
			index = 0;
			for(int i = 0;i <= nrow; i++)
			{
				//printf("[%2i]",i);
				for(j = 0;j < ncolumn;j++)
				{
					printf(" %-9s",dbresult[index]);
					index++;
				}
				printf("\n");
			}
			cout << "行数:" << nrow << endl;
			cout << "列数:" << ncolumn << endl;

			sqlite3_free_table(dbresult);
	 	}

	  ret = sqlite3_exec(datb1,"drop table Listqweold",NULL,NULL,&errmsg);
	  if(ret == SQLITE_OK)
	  {
		  cout << "4" << endl;
	  }
	  else
	  {
		  cout << "-4" << endl;
		  cout << errmsg << endl;
	  }
}

运行结果: