本文介绍如何在不丢失数据的前提下，通过SQL语句实现表名、列名、数据类型的修改，新增或删除列等操作，同时提供了一个Android应用程序实例，演示了数据库版本升级时如何添加新字段。
我们的程序在版本升级的时候可能需要增加某些新的字段供新版本使用，但是又不能修改原版本的数据，而且必须保留，有人可能想到做数据复制，把旧表的数据复制一遍，然后再copy 存入新的数据表，这样做不得不说是一个非常无奈的办法(我不敢说是脑残的办法，因为我自己也想过这么做)。哈哈。记得之前学数据库操作语句时，表是允许添加新字段的，so，百度一下。列出备份查询。
ALTER TABLE [方案名.]OLD_TABLE_NAME RENAME TO NEW_TABLE_NAME;
2.修改列名
ALTER TABLE [方案名.]TABLE_NAME RENAME COLUMN OLD_COLUMN_NAME TO NEW_COLUMN_NAME;
3.修改列的数据类型
ALTER TABLE [方案名.]TABLE_NAME MODIFY COLUMN_NAME NEW_DATATYPE;
4.插入列
ALTER TABLE [方案名.]TABLE_NAME ADD COLUMN_NAME DATATYPE;
5.删除列
ALTER TABLE [方案名.]TABLE_NAME DROP COLUMN COLUMN_NAME;
附上我自己写的例子：
package com.example.android_commonhelper;
import android.app.Activity;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;
import android.view.View;
import android.widget.TextView;
public class DatabaseActivity extends Activity {
private DBHelper mDBHelper;
private SQLiteDatabase mSQLiteDatabase;
private int index;
private TextView mPrintText;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_database);
mPrintText = (TextView) findViewById(R.id.print_text);
}
public void createTable(View v) {
mDBHelper = new DBHelper(this, "DatabaseActivity.db", 1);
mSQLiteDatabase = mDBHelper.getWritableDatabase();
v.setEnabled(false);
}
public void update(View v) {
mDBHelper = new DBHelper(this, "DatabaseActivity.db", 2);
mSQLiteDatabase = mDBHelper.getWritableDatabase();
v.setEnabled(false);
}
public void add1(View v) {
int index = this.index++;
ContentValues values = new ContentValues();
values.put("colu_one", "colu_two_add1_" + String.valueOf(index));
values.put("colu_four", "colu_four_add1_" + String.valueOf(index));
// values.put("colu_three", "column_three_add1_" +
// String.valueOf(index));
mSQLiteDatabase.insert("test_table_name", null, values);
}
public void add2(View v) {
int index = this.index++;
ContentValues values = new ContentValues();
values.put("colu_one", "colu_two_add2_" + String.valueOf(index));
values.put("colu_two", "colu_two_add2_" + String.valueOf(index));
// values.put("colu_three", "column_three_add2_" +
// String.valueOf(index));
mSQLiteDatabase.insert("test_table_name", null, values);
}
public void add3(View v) {
int index = this.index++;
ContentValues values = new ContentValues();
values.put("colu_one", "colu_two_add3_" + String.valueOf(index));
values.put("colu_two", "colu_two_add3_" + String.valueOf(index));
values.put("colu_three", "colu_three_add3_" + String.valueOf(index));
mSQLiteDatabase.insert("test_table_name", null, values);
}
public void clear(View v) {
mPrintText.setText("");
}
public void print(View v) {
int index = 1;
Cursor cursor = mSQLiteDatabase.rawQuery("select * from test_table_name", null);
if (cursor.moveToFirst()) {
String pText = mPrintText.getText() + "\n------------------------------\n";
String[] names = cursor.getColumnNames();
for (int i = 0; i < names.length; i++) {
String name = names[i];
pText += "-" + name + "-";
}
pText += "\n------------------------------\n";
mPrintText.setText(pText);
do {
StringBuffer buffer = new StringBuffer(mPrintText.getText());
buffer.append("\n");
buffer.append(index);
buffer.append("-> ");
for (int i = 0; i < names.length; i++) {
String column = names[i];
String value = cursor.getString(cursor.getColumnIndexOrThrow(column));
buffer.append(column);
buffer.append(":");
buffer.append(value);
buffer.append(", ");
}
mPrintText.setText(buffer.toString());
index++;
} while (cursor.moveToNext());
}
}
// 1.修改数据表名
// ALTER TABLE [方案名.]OLD_TABLE_NAME RENAME TO NEW_TABLE_NAME;
// 2.修改列名
// ALTER TABLE [方案名.]TABLE_NAME RENAME COLUMN OLD_COLUMN_NAME TO
// NEW_COLUMN_NAME;
// 3.修改列的数据类型
// ALTER TABLE [方案名.]TABLE_NAME MODIFY COLUMN_NAME NEW_DATATYPE;
// 4.插入列
// ALTER TABLE [方案名.]TABLE_NAME ADD COLUMN_NAME DATATYPE;
// 5.删除列
// ALTER TABLE [方案名.]TABLE_NAME DROP COLUMN COLUMN_NAME;
public void alertAddColumn(View v) {
mSQLiteDatabase.execSQL("ALTER TABLE test_table_name ADD colu_four TEXT DEFAULT 'four_value'");
}
public void alertDeleColumn(View v) {
mSQLiteDatabase.execSQL("ALTER TABLE test_table_name DROP COLUMN colu_four");
}
static class DBHelper extends SQLiteOpenHelper {
public DBHelper(Context context, String name, int version) {// CursorFactory
// factory,
super(context, name, null, version);
}
@Override
public void onCreate(SQLiteDatabase db) {
String sql = null;
sql = "create table test_table_name ( " + "_id integer primary key AUTOINCREMENT, "
+ "colu_one text not null, colu_two text," + "colu_three text default 'value');";
db.execSQL(sql);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("ALTER TABLE test_table_name ADD colu_four TEXT DEFAULT 'four_value'");
}
}
}
activity_database.xml<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical" >
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content" >
<Button
android:id="@+id/btn_create"
android:layout_width="0dip"
android:layout_height="wrap_content"
android:layout_weight="1"
android:singleLine="true"
android:text="btn_create"
android:onClick="createTable" />
<Button
android:id="@+id/btn_alert_add"
android:layout_width="0dip"
android:layout_height="wrap_content"
android:layout_weight="1"
android:singleLine="true"
android:text="btn_alert_add"
android:onClick="alertAddColumn" />
<Button
android:id="@+id/btn_alert_dele"
android:layout_width="0dip"
android:layout_height="wrap_content"
android:layout_weight="1"
android:singleLine="true"
android:text="btn_alert_dele"
android:onClick="alertDeleColumn" />
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content" >
<Button
android:id="@+id/btn_add_1"
android:layout_width="0dip"
android:layout_height="wrap_content"
android:layout_weight="1"
android:singleLine="true"
android:text="btn_add_1"
android:onClick="add1" />
<Button
android:id="@+id/btn_add_2"
android:layout_width="0dip"
android:layout_height="wrap_content"
android:layout_weight="1"
android:singleLine="true"
android:text="btn_add_2"
android:onClick="add2" />
<Button
android:id="@+id/btn_add_3"
android:layout_width="0dip"
android:layout_height="wrap_content"
android:layout_weight="1"
android:singleLine="true"
android:text="btn_add_3"
android:onClick="add3" />
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content" >
<Button
android:id="@+id/btn_clear"
android:layout_width="0dip"
android:layout_height="wrap_content"
android:layout_weight="1"
android:singleLine="true"
android:text="btn_clear"
android:onClick="clear" />
<Button
android:id="@+id/btn_print"
android:layout_width="0dip"
android:layout_height="wrap_content"
android:layout_weight="1"
android:singleLine="true"
android:text="btn_print"
android:onClick="print" />
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content" >
<Button
android:id="@+id/btn_update"
android:layout_width="0dip"
android:layout_height="wrap_content"
android:layout_weight="1"
android:singleLine="true"
android:text="update"
android:onClick="update" />
</LinearLayout>
<TextView
android:id="@+id/print_text"
android:layout_width="fill_parent"
android:layout_height="0dip"
android:layout_weight="1"
android:text="print_text"
android:textAppearance="?android:attr/textAppearanceSmall" />
</LinearLayout>资料：http://www.techonthenet.com/sql/tables/alter_table.php 仁家写的更全哟
资料：http://www.w3school.com.cn/sql/sql_quickref.asp SQL 快速参考
扫一扫
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
