sql的常用技巧

来源：互联网发布：图片格式转换器软件编辑：程序博客网时间：2024/06/15 06:41

将一张表中的数据插入到另一张表中。

insert into 【表1】 select * from 【表2】where 【条件】

附上一段因为字段太长懒得写被领导训的代码

public boolean changeTableDate(String pdate,String planid,String datetype){        String sql = "delete from T_REP_OUT_YHJGFX where pdate='"+pdate+"' and planid='"+planid+"' and datetype='"+datetype+"'";        ArrayList listsql = new ArrayList();        listsql.add(sql);        sql = "insert into T_REP_OUT_YHJGFX\n"                + "        ( \n"                + "                PDATE, \n"                + "                PLANID, \n"                + "                JXID, \n"                + "                NEWSTARTDATE, \n"                + "                NEWENDDATE, \n"                + "                DATETYPE, \n"                + "                OUT_OBJID, \n"                + "                DEPT, \n"                + "                STARTDATE, \n"                + "                ENDDATE, \n"                + "                REPDAYS, \n"                + "                FOREDAY, \n"                + "                LASTDAY, \n"                + "                EQUID, \n"                + "                EQUNAME, \n"                + "                REPLEVEL, \n"                + "                REPTYPE, \n"                + "                REASON, \n"                + "                CANREVISE, \n"                + "                ISREVISE, \n"                + "                REVISEDAYS \n"                + "        )   \n"                + "select \n"                + "        year, \n"                + "        '"+planid+"' as PLANID, \n"                + "        JXID, \n"                + "        startdate as NEWSTARTDATE, \n"                + "        enddate as NEWENDDATE, \n"                + "        '"+datetype+"' as DATETYPE, \n"                + "        OUT_OBJID, \n"                + "        DEPT, \n"                + "        STARTDATE, \n"                + "        ENDDATE, \n"                + "        REPDAYS, \n"                + "        FOREDAY, \n"                + "        LASTDAY, \n"                + "        EQUID, \n"                + "        EQUNAME, \n"                + "        REPLEVEL, \n"                + "        REPTYPE, \n"                + "        REASON, \n"                + "        CANREVISE, \n"                + "        '0' as ISREVISE, \n"                + "        '0' as REVISEDAYS \n"                + "from \n"                + "        T_REP_JXJH_YEAR\n"                + "        where year='"+pdate+"'";        listsql.add(sql);        try {           int[] r= dbMp.executeUpdate(listsql);            return r!=null && r.length>0;        } catch (Exception e) {            e.printStackTrace();            return false;        }    }

创建一个一模一样表结构的表格，并录入数据

create table 【新表】 as select * from 【旧表】

这时只是复制了表结构，表中的数据在使用上面的方法insert into 【表1】 select * from 【表2】插入

注意：这样创建的新表只拥有原先表的数据和结构，原先的主键，默认为空等约束新表是不具备的。

阅读全文

0 0