Informix SQL培训教材

1、Informix SQL培训教材应用软件开发部培训教材Informix SQL培训教材(文档编号:YR-WD-E-006)方正奥德计算机系统有限公司二零零一年二月6文档管理信息表主 题Informix SQL培训教材版 本V1.0.1内 容介绍Informix中常用的SQL语句的用法关 键 字SQL参考文档提交时间2001年2月22日撰 写 人宫载军文档修改记录表修改人修改时间修改内容目 录一与库相关的操作11.1建库11.2删库11.3选择库11.4关闭库1二与表相关的操作22.1建表22.2删除表2三查询操作2四删除操作4五插入操作5六修改操作5七与索引相关的操作57.1建索引57.2删除

2、索引5八与事务相关的操作68.1开始事务68.2提交事务68.3取消事务6九载入/载出操作69.1载出69.2载入6一 与库相关的操作1.1 建库格式：CREATE DATABASE database-name IN dbspace-name WITH LOG说明：dbspace-name为该库存放数据的dbspace，缺省为root dbspaceWITH LOG将该库设置为日志模式，如果不指定，则不能使用事务以及与日志有关的操作。示例：CREATE DATABASE db_test IN user_dbs WITH LOG1.2 删库格式：DROP DATABASE database-na

3、me说明：该操作无法恢复，执行后将删除该库、库中所有的表、以及所有的数据。示例：DROP TABLE db_test1.3 选择库格式：DATABASE database-name说明：选择可访问的库作为当前库。示例：DATABASE db_test1.4 关闭库格式：CLOSE DATABASE说明：使用该语句来关闭当前的数据库。二 与表相关的操作2.1 建表格式：CREATE TEMP TABLE table-name ( column-name datatype NOT NULL,)示例：CREATE TABLE book (namechar(20)NOT NULL,authorchar

4、(20),pricefloat)2.2 删除表格式：DROP TABLE table-name三 查询操作格式：SELECT DISTINCT select-list FROM tablename ,WHERE conditionGROUP BY column-list HAVING conditionORDER BY column-list DESC,INTO TEMP table-name示例：CREATE TABLE customer(fnamechar( 10 )NOT NULL,lnamechar( 20 )NOT NULL,ageinteger,statechar( 5 )1. S

5、ELECT fname,lname,state FROM customer WHERE state <> 'CA'结果：fnamelnamestateBobShorterNJFredJewellAZJasonWallackDECathyO'BrianNJMarvinHanlonFLChrisPutnumOKJamesHenryMAEileenNeelieCOKimSatiferNYFrankLessorAZ2. SELECT fname, lname FROM customer WHERE state <> "CA" ORDE

6、R BY lname;结果：fnamelnamestateMarvinHanlonFLJamesHenryMAFredJewellAZFrankLessorAZEileenNeelieCOCathyO'BrianNJChrisPutnumOKKimSatiferNYBobShorterNJ3. SELECT TRIM(lname) |", "| fname FROM customer;结果：Watson, GeorgeReam, CharlesQuinn, Donald4. SELECT * FROM customer WHERE lname MATCH "

7、;J-O*"结果：fnamelnameagestateFredJewell20AZFrankLessor19AZEileenNeelie25COCathyO'Brian45NJ5. SELECT fname, lname, age FROM customer WHERE age BETWEEN 20 and 30;结果：fnamelnameageFredJewell20FredJewell206. SELECT max(age), min(age) FROM customer结果：(max)(min)45197. SELECT fname, lname FROM custom

8、er WHERE state is null8. SELECT age, count(*) FROM customer GROUP BY age结果：age(count(*)2011929. SELECT age, count(*) FROM customer GROUP BY age HAVING count(*)>1结果：age(count(*)19210. SELECT AVERAGE(age) FROM customer结果：(avg)27.25四 删除操作格式：DELETE FROM table-name WHERE condition五 插入操作格式：INSERT INTO

9、table-name (column-list)VALUES (value-list) | SELECT-statement六 修改操作格式：UPDATE table-nameSET column-name = expression ,.| (col-list) | * = (expr-list)WHERE condition七 与索引相关的操作7.1 建索引格式：CREATE UNIQUE INDEX index-name ON table-name (column-name,)示例：CREATE INDEX cust_idx1 ON customer ( lname )7.2 删除索引格式：DROP INDEX index-name八 与事务相关的操作8.1 开始事务格式：BEGIN WORK8.2 提交事务格式：COMMIT WORK8.3 取消事务格式：ROLLBACK WORK九 载入/载出操作9.1 载出格式：UNLOAD TO file-name select-statement示例：UN