关于SQL语句 贴出代码求解决问题 彻底懵了。。。

public class Cloud implements Runnable {
SQLiteDatabase db;
private final String DATABASE_PATH = "/storage/sdcard0/Upan";
private String DATABASE_FILENAME = "song.db";
private String PATH = "*************";

@Override
public void run() {
    String databaseFilename = DATABASE_PATH + "/" + DATABASE_FILENAME;
    db = SQLiteDatabase.openOrCreateDatabase(databaseFilename, null);
    db.execSQL("DROP TABLE IF EXISTS minfo");
    db.execSQL("CREATE TABLE minfo (id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR, age SMALLINT)");


    ContentValues cv2 = new ContentValues();
    cv2.put("ver", 6);
    cv2.put("name", "ooom");
    db.insert("minfo", null, cv2);
    ContentValues cv3 = new ContentValues();
    cv3.put("ver", 40);
    cv3.put("name", "ognm");
    db.insert("minfo", null, cv3);
    ContentValues cv4 = new ContentValues();
    cv4.put("ver", 22);
    cv4.put("name", "fake");
    db.insert("minfo", null, cv4);


    seventeen();

}
     public void seventeen() {
    String input = "17";
    try {
        URL url = new URL(PATH + input);
        HttpURLConnection connection = (HttpURLConnection) url.openConnection();
        connection.setConnectTimeout(5000);
        connection.setReadTimeout(8000);
        connection.setRequestMethod("GET");
        connection.setUseCaches(false);
        connection.setDoOutput(true);
        connection.setDoInput(true);
        OutputStream outputStream = connection.getOutputStream();
        outputStream.flush();
        connection.getResponseCode();

        InputStream in = connection.getInputStream();
        BufferedReader reader = new BufferedReader(new InputStreamReader(in));
        StringBuilder response = new StringBuilder();
        String line;
        while ((line = reader.readLine()) != null) {
            response.append(line);
        }
        Log.i("返回的数据是 ", response.toString());
        try {

            JSONObject obj = new JSONObject(response.toString());
            JSONArray arr = obj.getJSONArray("info");
            for (int i = 0; i < arr.length(); i++) {
                JSONObject subObj = arr.getJSONObject(i);
                String id = subObj.getString("id");
                String name = subObj.getString("name");
                String ver = subObj.getString("ver");
                Log.i("解析后的数据 ", "id=" + id + ",name=" + name + ",ver=" + ver);
            }

            // db.execSQL("insert into minfo select * from info");
            new Thread(new Runnable() {
                @Override
                public void run() {
                    db.execSQL("DROP TABLE minfo");
                 //   db.execSQL("INSERT INTO minfo SELECT * FROM info ");
                    db.execSQL("CREATE TABLE minfo as SELECT * FROM info ");
               //     db.execSQL("CREATE TABLE minfo SELECT * FROM info");
                    Cursor c = db.rawQuery("SELECT * FROM  minfo", null);
                    while (c.moveToNext()) {
                        String w = c.getString(c.getColumnIndex("WordNum"));
                        String p = c.getString(c.getColumnIndex("PyCode"));
                        String songname = c.getString(c.getColumnIndex("name"));
                        int id = c.getInt(c.getColumnIndex("id"));
                        int ver = c.getInt(c.getColumnIndex("ver"));
                        Log.i("本地数据库minfo表中的数 ", "id=" + id + ",name=" + songname + ",ver=" + ver + ",WordNum=" + w + ",PyCode=" + p);
                    }
                }
            }).start();
        } catch (JSONException e) {
            e.printStackTrace();
        }
    } catch (IOException e) {
        e.printStackTrace();

    }

如果我db.execSQL("DROP TABLE minfo");db.execSQL("CREATE TABLE minfo as SELECT * FROM info ");的话都出来的数据是:
id=1,name=aaaa,ver=6,WordNum=6,PyCode=KD
本地数据库minfo表中的数: id=2,name=ooom,ver=6,WordNum=null,PyCode=null
本地数据库minfo表中的数: id=3,name=ognm,ver=40,WordNum=null,PyCode=null本地数据库minfo表中的数: id=4,name=fake,ver=22,WordNum=null,PyCode=null
本地数据库minfo表中的数: id=5,name=xx,ver=23,WordNum=null,PyCode=null
本地数据库minfo表中的数: id=6,name=xx,ver=23,WordNum=null,PyCode=null

然而info表中的数据是
id=1,name=1111xxx,ver=132
id=5,name=xx,ver=23

要是我把db.execSQL("CREATE TABLE iinfo as SELECT * FROM info ");中的as去掉 会报错SELECT语法错误。。请问是怎么回事》还有就是如果我 db.execSQL("INSERT INTO iinfo SELECT * FROM info ")会报错table iinfo has 3 columns but 25 values were supplied
求帮忙解决这两个问题呀~~~有点慌.........

1个回答

1.要使用insert into table1 select * from table2,必须注意,这两个表的表结构一致,否者就得指定插入表及选择表的字段:
insert into table1(column1) select column1 from table2;
2.使用create table table1 select * from table2,必须注意,所创建的表table1与table2一模一样,可以理解为table1是table2的复制。

参考自:
MySQL insert用法详解 http://www.data.5helpyou.com/article421.html

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐