oracle+常用SQL语法手册

1oracle常用SQL语法手册Select用途：从指定表中取出指定的列的数据语法：SELECTcolumn_name(s)FROMtable_name解释：从数据库中选取资料列，并允许从一或多个资料表中，选取一或多个资料列或资料行。SELECT陈述式的完整语法相当复杂，但主要子句可摘要为：SELECTselect_list[INTOnew_table]FROMtable_source[WHEREsearch_condition][GROUPBYgroup_by_expression][HAVINGsearch_condition][ORDERBYorder_expression[ASC|DESC]]例：“Persons”表中的数据有LastNameFirstNameAddressCityHansenOlaTimoteivn10SandnesSvendsonToveBorgvn23SandnesPettersenKariStorgt20Stavanger选出字段名”LastName”、”FirstName”的数据SELECTLastName,FirstNameFROMPersons返回结果:LastNameFirstNameHansenOlaSvendsonTovePettersenKari选出所有字段的数据2SELECT*FROMPersons返回结果:LastNameFirstNameAddressCityHansenOlaTimoteivn10SandnesSvendsonToveBorgvn23SandnesPettersenKariStorgt20StavangerWhere用途：被用来规定一种选择查询的标准语法：SELECTcolumnFROMtableWHEREcolumnconditionvalue下面的操作符能被使用在WHERE中：=,,,,=,=,BETWEEN,LIKE注意：在某些SQL的版本中不等号能被写作为!=解释：SELECT语句返回WHERE子句中条件为true的数据例：从”Persons”表中选出生活在”Sandnes”的人SELECT*FROMPersonsWHERECity='Sandnes'Persons表中的数据有:LastNameFirstNameAddressCityYearHansenOlaTimoteivn10Sandnes1951SvendsonToveBorgvn23Sandnes1978SvendsonStaleKaivn18Sandnes1980PettersenKariStorgt20Stavanger1960返回结果:LastNameFirstNameAddressCityYear3HansenOlaTimoteivn10Sandnes1951SvendsonToveBorgvn23Sandnes1978SvendsonStaleKaivn18Sandnes1980And&Or用途：在WHERE子句中AND和OR被用来连接两个或者更多的条件解释：AND在结合两个布尔表达式时，只有在两个表达式都为TRUE时才传回TRUEOR在结合两个布尔表达式时，只要其中一个条件为TRUE时，OR便传回TRUE例：Persons表中的原始数据:LastNameFirstNameAddressCityHansenOlaTimoteivn10SandnesSvendsonToveBorgvn23SandnesSvendsonStephenKaivn18Sandnes用AND运算子来查找Persons表中FirstName为”Tove”而且LastName为”Svendson”的数据SELECT*FROMPersonsWHEREFirstName='Tove'ANDLastName='Svendson'返回结果:LastNameFirstNameAddressCitySvendsonToveBorgvn23Sandnes用OR运算子来查找Persons表中FirstName为”Tove”或者LastName为”Svendson”的数据SELECT*FROMPersonsWHEREfirstname='Tove'ORlastname='Svendson'4返回结果:LastNameFirstNameAddressCitySvendsonToveBorgvn23SandnesSvendsonStephenKaivn18Sandnes你也能结合AND和OR(使用括号形成复杂的表达式),如:SELECT*FROMPersonsWHERE(FirstName='Tove'ORFirstName='Stephen')ANDLastName='Svendson'返回结果:LastNameFirstNameAddressCitySvendsonToveBorgvn23SandnesSvendsonStephenKaivn18SandnesBetween…And用途：指定需返回数据的范围语法：SELECTcolumn_nameFROMtable_nameWHEREcolumn_nameBETWEENvalue1ANDvalue2例：“Persons”表中的原始数据LastNameFirstNameAddressCityHansenOlaTimoteivn10SandnesNordmannAnnaNeset18SandnesPettersenKariStorgt20StavangerSvendsonToveBorgvn23Sandnes用BETWEEN…AND返回LastName为从”Hansen”到”Pettersen”的数据：SELECT*FROMPersonsWHERELastName5BETWEEN'Hansen'AND'Pettersen'返回结果:LastNameFirstNameAddressCityHansenOlaTimoteivn10SandnesNordmannAnnaNeset18SandnesPettersenKariStorgt20Stavanger为了显示指定范围之外的数据，也可以用NOT操作符：SELECT*FROMPersonsWHERELastNameNOTBETWEEN'Hansen'AND'Pettersen'返回结果:LastNameFirstNameAddressCitySvendsonToveBorgvn23SandnesDistinct用途：DISTINCT关键字被用作返回唯一的值语法：SELECTDISTINCTcolumn-name(s)FROMtable-name解释：当column-name(s)中存在重复的值时，返回结果仅留下一个例：“Orders”表中的原始数据CompanyOrderNumberSega3412W3Schools2312Trio4678W3Schools6798用DISTINCT关键字返回Company字段中唯一的值：SELECTDISTINCTCompanyFROMOrders6返回结果:CompanySegaW3SchoolsTrioOrderby用途：指定结果集的排序语法：SELECTcolumn-name(s)FROMtable-nameORDERBY{order_by_expression[ASC|DESC]}解释：指定结果集的排序，可以按照ASC(递增方式排序，从最低值到最高值)或者DESC(递减方式排序，从最高值到最低值)的方式进行排序，默认的方式是ASC例：“Orders”表中的原始数据:CompanyOrderNumberSega3412ABCShop5678W3Schools2312W3Schools6798按照Company字段的升序方式返回结果集：SELECTCompany,OrderNumberFROMOrdersORDERBYCompany返回结果:CompanyOrderNumberABCShop56787Sega3412W3Schools6798W3Schools2312按照Company字段的降序方式返回结果集：SELECTCompany,OrderNumberFROMOrdersORDERBYCompanyDESC返回结果:CompanyOrderNumberW3Schools6798W3Schools2312Sega3412ABCShop5678Groupby用途：对结果集进行分组，常与汇总函数一起使用。语法：SELECTcolumn,SUM(column)FROMtableGROUPBYcolumn例：“Sales”表中的原始数据:CompanyAmountW3Schools5500IBM4500W3Schools7100按照Company字段进行分组，求出每个Company的Amout的合计：SELECTCompany,SUM(Amount)FROMSalesGROUPBYCompany返回结果：8CompanySUM(Amount)W3Schools12600IBM4500Having用途：指定群组或汇总的搜寻条件。语法：SELECTcolumn,SUM(column)FROMtableGROUPBYcolumnHAVINGSUM(column)conditionvalue解释：HAVING通常与GROUPBY子句同时使用。不使用GROUPBY时，HAVING则与WHERE子句功能相似。例：“Sales”表中的原始数据:CompanyAmountW3Schools5500IBM4500W3Schools7100按照Company字段进行分组，求出每个Company的Amout的合计在10000以上的数据：SELECTCompany,SUM(Amount)FROMSalesGROUPBYCompanyHAVINGSUM(Amount)10000返回结果：CompanySUM(Amount)W3Schools12600Join用途：当你要从两个或者以上的表中选取结果集时，你就会用到JOIN。9例：“Employees”表中的数据如下，（其中ID为主键）:IDName01Hansen,Ola02Svendson,Tove03Svendson,Stephen04Pettersen,Kari“Orders”表中的数据如下：IDProduct01Printer03Table03Chair用Employees的ID和Orders的ID相关联选取数据：SELECTEmployees.Name,Orders.ProductFROMEmployees,OrdersWHEREEmployees.ID=Orders.ID返回结果：NameProductHansen,OlaPrinterSvendson,StephenTableSvendson,StephenChair或者你也可以用JOIN关键字来完成上面的操作：SELECTEmployees.Name,Orders.ProductFROMEmployees10INNERJOINOrdersONEmployees.ID=Orders.IDINNERJOIN的语法：SELECTfield1,field2,field3FROMfirst_tableINNERJOINsecond_tableONfirst_table.keyfield=second_table.foreign_keyfield解释：INNERJOIN返回的结果集是两个表中所有相匹配的数据。LEFTJOIN的语法：SELECTfield1,field2,field3FROMfirst_tableLEFTJOINsecond_tableONfirst_table.keyfield=second_table.foreign_keyfield用”Employees”表去左外联结”Orders”表去找出相关数据：SELECTEmployees.Name,Orders.Produc