Informix SQL培训教材

应用软件开发部培训教材InformixSQL培训教材(文档编号:YR-WD-E-006)方正奥德计算机系统有限公司二零零一年二月文档管理信息表主题InformixSQL培训教材版本V1.0.1内容介绍Informix中常用的SQL语句的用法关键字SQL参考文档提交时间2001年2月22日撰写人宫载军文档修改记录表修改人修改时间修改内容InformixSQL培训教材1目录一与库相关的操作.....................................................................................................................11.1建库................................................................................................................................11.2删库................................................................................................................................11.3选择库............................................................................................................................11.4关闭库............................................................................................................................1二与表相关的操作.....................................................................................................................22.1建表................................................................................................................................22.2删除表............................................................................................................................2三查询操作.................................................................................................................................2四删除操作.................................................................................................................................4五插入操作.................................................................................................................................5六修改操作.................................................................................................................................5七与索引相关的操作.................................................................................................................57.1建索引............................................................................................................................57.2删除索引........................................................................................................................5八与事务相关的操作.................................................................................................................68.1开始事务........................................................................................................................68.2提交事务........................................................................................................................68.3取消事务........................................................................................................................6九载入/载出操作........................................................................................................................69.1载出................................................................................................................................69.2载入................................................................................................................................6InformixSQL培训教材1一与库相关的操作1.1建库格式：CREATEDATABASEdatabase-name[INdbspace-name][WITHLOG]说明：dbspace-name为该库存放数据的dbspace，缺省为rootdbspaceWITHLOG将该库设置为日志模式，如果不指定，则不能使用事务以及与日志有关的操作。示例：CREATEDATABASEdb_testINuser_dbsWITHLOG1.2删库格式：DROPDATABASEdatabase-name说明：该操作无法恢复，执行后将删除该库、库中所有的表、以及所有的数据。示例：DROPTABLEdb_test1.3选择库格式：DATABASEdatabase-name说明：选择可访问的库作为当前库。示例：DATABASEdb_test1.4关闭库格式：CLOSEDATABASEInformixSQL培训教材2说明：使用该语句来关闭当前的数据库。二与表相关的操作2.1建表格式：CREATE[TEMP]TABLEtable-name(column-namedatatype[NOTNULL],…)示例：CREATETABLEbook(namechar(20)NOTNULL,authorchar(20),pricefloat)2.2删除表格式：DROPTABLEtable-name三查询操作格式：SELECT[DISTINCT]select-listFROMtablename[,…][WHEREcondition][GROUPBYcolumn-list][HAVINGcondition][ORDERBYcolumn-list[DESC],…][INTOTEMPtable-name]示例：CREATETABLEcustomer(fnamechar(10)NOTNULL,lnamechar(20)NOTNULL,InformixSQL培训教材3ageinteger,statechar(5))1.SELECTfname,lname,stateFROMcustomerWHEREstate'CA'结果：fnamelnamestateBobShorterNJFredJewellAZJasonWallackDECathyO'BrianNJMarvinHanlonFLChrisPutnumOKJamesHenryMAEileenNeelieCOKimSatiferNYFrankLessorAZ2.SELECTfname,lnameFROMcustomerWHEREstateCAORDERBYlname;结果：fnamelnamestateMarvinHanlonFLJamesHenryMAFredJewellAZFrankLessorAZEileenNeelieCOCathyO'BrianNJChrisPutnumOKKimSatiferNYBobShorterNJ3.SELECTTRIM(lname)||,||fnameFROMcustomer;结果：Watson,GeorgeReam,CharlesQuinn,Donald4.SELECT*FROMcustomerWHERElnameMATCH[J-O]*;结果：fnamelnameagestateFredJewell20AZFrankLessor19AZInformixSQL培训教材4EileenNeelie25COCathyO'Brian45NJ5.SELECTfname,lname,ageFROMcustomerWHEREageBETWEEN20and30;结果：fnamelnameageFredJewell20FredJewell206.SELECTmax(age),min(age)FROMcustomer结果：(max)(min)45197.SELECTfname,lnameFROMcustomerWHEREstateisnull8.SELECTage,count(*)FROMcustomerGROUPBYage结果：age(count(*))2011929.SELECTage,count(*)FROMcustomerGROUPBYageHAVINGcount(*)1结果：age(count(*))19210.SELECTAVERAGE(age)FROMcustomer结果：(avg)27.25四删除操作格式：DELETEFROMtable-name[WHEREcondition]InformixSQL培训教材5五插入操作格式：INSERTINTOtable-name[(colu