liquibase操作文档v1.0

张丽鹏liquibase操作文档liquibase安装1，下载：，本文档基于liquibase-3.0.5-bin.zip测试编写3，安装只要解压在任意盘符下即可。同时需要安装jdk，需要jdbc的驱动，jdbc驱动的jar包只需放在liquibase目录中lib目录下即可。一，用generateChangeLog参数生成数据库的xml格式的changelog，类似数据库的基线liquibase--driver=oracle.jdbc.OracleDriver--changeLogFile=db.changelog.xml--url=jdbc:oracle:thin:@10.249.27.31:1521:bjh_crm--username=scott--password=tigergenerateChangeLog该命令会生成一个db.changelog.xml基础文件，包含了数据库的所有表结构以及索引、视图、sequence等信息。不包含存储过程等信息。二，用update参数对数据库对象进行管理liquibase--driver=oracle.jdbc.OracleDriver--changeLogFile=database.changelog.xml--url=jdbc:oracle:thin:@10.249.27.31:1521:bjh_crm--username=scott--password=tigerupdate1，增加字段Oracle：ALTERTABLEt_zhangADDaddressVARCHAR(255)NULL;对应的changelog.xml的数据集如下：changeSetauthor=zhanglipeng(update)id=1380973251540-10addColumntableName=t_zhangcolumnname=addresstype=varchar(255)//addColumn/changeSet张丽鹏2，给列设置默认值Oracle：ALTERTABLET_ZHANGMODIFYnamedefault‘SomethingElse’;3，增加外键Oracle：ALTERTABLET_LIPENGADDCONSTRAINTfk_lipeng_zhangFOREIGNKEY(lp_id)REFERENCESt_zhang(id)ONdeletecascadedeferrableinitiallydeferred;changeSetauthor=zhanglipeng(update)id=1380973251540-11addDefaultValuecolumnDataType=varchar2columnName=namedefaultValue=SomethingElseschemaName=scotttableName=t_zhang//changeSetchangeSetauthor=zhanglipeng(update)id=1380973251540-12addForeignKeyConstraintbaseColumnNames=lp_idbaseTableName=t_lipengconstraintName=fk_lipeng_zhangdeferrable=trueinitiallyDeferred=trueonDelete=CASCADEonUpdate=RESTRICTreferencedColumnNames=idreferencedTableName=t_zhang//changeSet张丽鹏4，增加非空约束Oracle：ALTERTABLET_ZHANGMODIFYactiveNOTNULL;5，增加主键Oracle：ALTERTABLET_ZHANGADDPRIMARYKEY(id,name);6，删除主键Oracle：ALTERTABLET_ZHANGDROPCONSTRAINTPK_TZHANGCASCADE;changeSetauthor=zhanglipeng(update)id=1380973251540-13addNotNullConstraintcolumnDataType=numbercolumnName=activedefaultNullValue=AStringtableName=t_zhang//changeSetchangeSetauthor=zhanglipeng(update)id=1380973251540-14addPrimaryKeycolumnNames=id,nameconstraintName=pk_tzhangtableName=t_zhangtablespace=users//changeSet张丽鹏7，增加唯一约束8，导入csv文件到数据库changeSetauthor=zhanglipeng(update)id=1380973251540-16addUniqueConstraintcolumnNames=id,nameconstraintName=const_namedeferrable=truedisabled=trueinitiallyDeferred=truetableName=persontablespace=AString//changeSetchangeSetauthor=zhanglipeng(update)id=1380973251540-15dropPrimaryKeyconstraintName=pk_tzhangtableName=t_zhang//changeSetchangeSetauthor=zhanglipeng(update)id=1380973251540-17loadUpdateDataencoding=GBKfile=TD_M_CARDSALEPRICE_BK_LIQUI.csvquotchar=’”’separator=,tableName=TD_M_CARDSALEPRICE_BK_LIQUI//changeSet张丽鹏9，修改sequenceALTERSEQUENCEseq_idINCREMENTBY371717MINVALUE371717MAXVALUE371717ORDER;10，创建索引oracle：CREATEUNIQUEINDEXidx_addressONt_zhang(address);11，创建存储过程changeSetauthor=zhanglipeng(update)id=1380973251540-18alterSequenceincrementBy=1maxValue=371717minValue=1ordered=truesequenceName=seq_id//changeSetchangeSetauthor=zhanglipeng(update)id=1380973251540-19createIndexindexName=idx_addresstableName=t_zhangtablespace=AStringunique=truecolumnname=addresstype=varchar(255)//createIndex/changeSet张丽鹏Oracle：CREATEORREPLACEPROCEDUREtestHelloasbegindbms_output.put_line('hello');end;/12，创建sequenceOracle：CREATESEQUENCEseq_tzhang_idSTARTWITH5INCREMENTBY2MINVALUE10MAXVALUE1000ORDERCYCLE;changeSetauthor=zhanglipeng(update)id=1380973251540-20createProcedureCREATEORREPLACEPROCEDUREtestHelloasbegindbms_output.put_line('hello');end;/createProcedure/changeSetchangeSetauthor=zhanglipeng(update)id=1380973251540-21createSequencecycle=trueincrementBy=2maxValue=1000minValue=5ordered=truesequenceName=seq_tzhang_idstartValue=5//changeSet张丽鹏13，创建表createtablet_test(addressvarchar2(255))tablespaceTBS_CRM_DEF;commentontablet_testis'测试';14，创建视图CREATEORREPLACEVIEWvi_zhangASselectid,namefromt_zhangwhereid10;15，删除表数据DELETEFROMt_zhangWHEREid100;changeSetauthor=zhanglipeng(update)id=1380973251540-22createTableremarks=测试tableName=t_testtablespace=TBS_CRM_DEFcolumnname=addresstype=varchar(255)//createTable/changeSetchangeSetauthor=zhanglipeng(update)id=1380973251540-23createViewreplaceIfExists=trueviewName=vi_zhangselectid,namefromt_zhangwhereid10/createView/changeSetchangeSetauthor=zhanglipeng(update)id=1380973251540-24deletetableName=t_zhangwhereid100/where/delete/changeSet张丽鹏16，删除所有外键约束17，删除列ALTERTABLEt_lipengDROPCOLUMNloc;18，删除外键约束ALTERTABLET_LIPENGdropconstraintFK_LIPENG_ZHANG;19，删除列的默认值ALTERTABLET_ZHANGmodifynamedefaultnull;changeSetauthor=zhanglipeng(update)id=1380973251540-25dropAllForeignKeyConstraintsbaseTableName=t_lipeng//changeSetchangeSetauthor=zhanglipeng(update)id=1380973251540-26dropColumncolumnName=loctableName=t_lipeng//changeSetchangeSetauthor=zhanglipeng(update)id=1380973251540-27dropForeignKeyConstraintbaseTableName=t_lipengconstraintName=fk_lipeng_zhang//changeSetchangeSetauthor=zhanglipeng(update)id=1380973251540-28dropDefaultValuecolumnDataType=varchar2(255)c