oracle+常用SQL语法手册.doc

《oracle+常用SQL语法手册.doc》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、oracle常用SQL语法手册Select用途： 从指定表中取出指定的列的数据语法：SELECTcolumn_name(s)FROMtable_name解释：从数据库中选取资料列，并允许从一或多个资料表中，选取一或多个资料列或资料行。SELECT陈述式的完整语法相当复杂，但主要子句可摘要为：SELECTselect_list[INTOnew_table]FROMtable_source[WHEREsearch_condition][GROUPBYgroup_by_expression][HAVINGsearch_condition][ORDERBYorder_expr

2、ession[ASC

3、DESC]]例： “Persons”表中的数据有LastNameFirstNameAddressCityHansenOlaTimoteivn10SandnesSvendsonToveBorgvn23SandnesPettersenKariStorgt20Stavanger选出字段名”LastName”、”FirstName”的数据SELECTLastName,FirstNameFROMPersons返回结果:LastNameFirstNameHansenOlaSvendsonTovePettersenKari选出所有字段的数据SELECT*FRO

4、MPersons返回结果: LastNameFirstNameAddressCityHansenOlaTimoteivn10SandnesSvendsonToveBorgvn23SandnesPettersenKariStorgt20StavangerWhere用途：被用来规定一种选择查询的标准语法：SELECTcolumnFROMtableWHEREcolumnconditionvalue下面的操作符能被使用在WHERE中：=,<>,>,<,>=,<=,BETWEEN,LIKE注意：在某些SQL的版本中不等号<>能被写作为!=解释： SELECT语句返回WHERE子

5、句中条件为true的数据例：从”Persons”表中选出生活在”Sandnes”的人SELECT*FROMPersonsWHERECity='Sandnes' "Persons"表中的数据有:LastNameFirstNameAddressCityYearHansenOlaTimoteivn10Sandnes1951SvendsonToveBorgvn23Sandnes1978SvendsonStaleKaivn18Sandnes1980PettersenKariStorgt20Stavanger1960返回结果:LastNameFirstNameAddressCit

6、yYearHansenOlaTimoteivn10Sandnes1951SvendsonToveBorgvn23Sandnes1978SvendsonStaleKaivn18Sandnes1980And&Or用途：在WHERE子句中AND和OR被用来连接两个或者更多的条件解释：AND在结合两个布尔表达式时，只有在两个表达式都为TRUE时才传回TRUE OR在结合两个布尔表达式时，只要其中一个条件为TRUE时，OR便传回TRUE例： "Persons"表中的原始数据:LastNameFirstNameAddressCityHansenOlaTimoteivn10Sand

7、nesSvendsonToveBorgvn23SandnesSvendsonStephenKaivn18Sandnes 用AND运算子来查找"Persons"表中FirstName为”Tove”而且LastName为”Svendson”的数据SELECT*FROMPersonsWHEREFirstName='Tove'ANDLastName='Svendson'返回结果:LastNameFirstNameAddressCitySvendsonToveBorgvn23Sandnes用OR运算子来查找"Persons"表中FirstName为”Tove”或者LastNam

8、e为”Svendson”的数据SELECT*FROMPersonsWHEREfirstname='Tove'ORlastname='Svendson'返回结果:LastNameFirstNameAddressCitySvendsonToveBorgvn23SandnesSvendsonStephenKaivn18Sandnes 你也能结合AND和OR(使用括号形成复杂的表达式),如:SELECT*FROMPersonsWHERE(FirstName='Tove'ORFirstName='Stephen')ANDLastName='Svendson'返