《oracle+常用SQL语法手册》由会员分享，可在线阅读，更多相关《oracle+常用SQL语法手册（22页珍藏版）》请在金锄头文库上搜索。
1、oracle 常用SQL语法手册 Select用途：从指定表中取出指定的列的数据语法： SELECT column_name(s) FROM table_name解释：从数据库中选取资料列，并允许从一或多个资料表中，选取一或多个资料列或资料行。SELECT 陈述式的完整语法相当复杂，但主要子句可摘要为：SELECT select_list INTO new_table FROM table_source WHERE search_condition GROUP BY group_by_expression HAVING search_condition ORDER BY order_expre
2、ssion ASC | DESC 例：“Persons” 表中的数据有LastNameFirstNameAddressCityHansenOlaTimoteivn 10SandnesSvendsonToveBorgvn 23SandnesPettersenKariStorgt 20Stavanger选出字段名” LastName”、” FirstName” 的数据SELECT LastName,FirstName FROM Persons返回结果:LastNameFirstNameHansenOlaSvendsonTovePettersenKari选出所有字段的数据SELECT * FROM
3、Persons返回结果:LastNameFirstNameAddressCityHansenOlaTimoteivn 10SandnesSvendsonToveBorgvn 23SandnesPettersenKariStorgt 20StavangerWhere用途：被用来规定一种选择查询的标准语法： SELECT column FROM table WHERE column condition value下面的操作符能被使用在WHERE中：=,=,=,BETWEEN,LIKE注意： 在某些SQL的版本中不等号能被写作为!=解释：SELECT语句返回WHERE子句中条件为true的数据例：从
4、” Persons”表中选出生活在” Sandnes” 的人SELECT * FROM Persons WHERE City=SandnesPersons 表中的数据有:LastNameFirstNameAddressCityYearHansenOlaTimoteivn 10Sandnes1951SvendsonToveBorgvn 23Sandnes1978SvendsonStaleKaivn 18Sandnes1980PettersenKariStorgt 20Stavanger1960返回结果:LastNameFirstNameAddressCityYearHansenOlaTimote
5、ivn 10Sandnes1951SvendsonToveBorgvn 23Sandnes1978SvendsonStaleKaivn 18Sandnes1980And & Or用途：在WHERE子句中AND和OR被用来连接两个或者更多的条件解释： AND在结合两个布尔表达式时，只有在两个表达式都为 TRUE 时才传回 TRUE OR在结合两个布尔表达式时，只要其中一个条件为 TRUE 时，OR便传回 TRUE例：Persons 表中的原始数据:LastNameFirstNameAddressCityHansenOlaTimoteivn 10SandnesSvendsonToveBorgvn
6、23SandnesSvendsonStephenKaivn 18Sandnes用AND运算子来查找Persons 表中FirstName为”Tove”而且LastName为” Svendson”的数据SELECT * FROM PersonsWHERE FirstName=ToveAND LastName=Svendson返回结果: LastNameFirstNameAddressCitySvendsonToveBorgvn 23Sandnes用OR运算子来查找Persons 表中FirstName为”Tove”或者LastName为” Svendson”的数据SELECT * FROM Pe
7、rsonsWHERE firstname=ToveOR lastname=Svendson返回结果: LastNameFirstNameAddressCitySvendsonToveBorgvn 23SandnesSvendsonStephenKaivn 18Sandnes你也能结合AND和OR (使用括号形成复杂的表达式),如:SELECT * FROM Persons WHERE(FirstName=Tove OR FirstName=Stephen)AND LastName=Svendson返回结果:LastNameFirstNameAddressCitySvendsonToveBorg
8、vn 23SandnesSvendsonStephenKaivn 18SandnesBetweenAnd用途：指定需返回数据的范围语法： SELECT column_name FROM table_nameWHERE column_nameBETWEEN value1 AND value2例： “Persons”表中的原始数据LastNameFirstNameAddressCityHansenOlaTimoteivn 10SandnesNordmannAnnaNeset 18SandnesPettersenKariStorgt 20StavangerSvendsonToveBorgvn 23S
9、andnes用BETWEENAND返回LastName为从”Hansen”到”Pettersen”的数据：SELECT * FROM Persons WHERE LastName BETWEEN Hansen AND Pettersen返回结果:LastNameFirstNameAddressCityHansenOlaTimoteivn 10SandnesNordmannAnnaNeset 18SandnesPettersenKariStorgt 20Stavanger为了显示指定范围之外的数据，也可以用NOT操作符：SELECT * FROM Persons WHERE LastName N
10、OT BETWEEN Hansen AND Pettersen返回结果:LastNameFirstNameAddressCitySvendsonToveBorgvn 23SandnesDistinct用途：DISTINCT关键字被用作返回唯一的值语法： SELECT DISTINCT column-name(s) FROM table-name解释：当column-name(s)中存在重复的值时，返回结果仅留下一个例： “Orders”表中的原始数据CompanyOrderNumberSega3412W3Schools2312Trio4678W3Schools6798用DISTINCT关键字返
11、回Company字段中唯一的值：SELECT DISTINCT Company FROM Orders返回结果:CompanySegaW3SchoolsTrioOrder by用途：指定结果集的排序语法： SELECT column-name(s) FROM table-name ORDER BY order_by_expression ASC | DESC 解释：指定结果集的排序，可以按照ASC(递增方式排序，从最低值到最高值)或者DESC(递减方式排序，从最高值到最低值)的方式进行排序，默认的方式是ASC例： “Orders”表中的原始数据:CompanyOrderNumberSega34
12、12ABC Shop5678W3Schools2312W3Schools6798按照Company字段的升序方式返回结果集：SELECT Company, OrderNumber FROM OrdersORDER BY Company返回结果: CompanyOrderNumberABC Shop5678Sega3412W3Schools6798W3Schools2312按照Company字段的降序方式返回结果集：SELECT Company, OrderNumber FROM OrdersORDER BY Company DESC返回结果:CompanyOrderNumberW3School
13、s6798W3Schools2312Sega3412ABC Shop5678Group by用途：对结果集进行分组，常与汇总函数一起使用。语法： SELECT column,SUM(column) FROM table GROUP BY column例： “Sales”表中的原始数据:CompanyAmountW3Schools5500IBM4500W3Schools7100按照Company字段进行分组，求出每个Company的Amout的合计：SELECT Company,SUM(Amount) FROM SalesGROUP BY Company返回结果：CompanySUM(Amoun
14、t)W3Schools12600IBM4500Having用途：指定群组或汇总的搜寻条件。语法： SELECT column,SUM(column) FROM tableGROUP BY columnHAVING SUM(column) condition value解释： HAVING 通常与 GROUP BY 子句同时使用。不使用 GROUP BY 时，HAVING 则与 WHERE 子句功能相似。例： “Sales”表中的原始数据:CompanyAmountW3Schools5500IBM4500W3Schools7100按照Company字段进行分组，求出每个Company的Amout的合计在
