SQL语法详解.docx

1、SQL语法详解SQL语法详解Select用途：从指定表中取出指定的列的数据语法：SELECTcolumn_name(s)FROMtable_name解释：从数据库中选取资料列，并允许从一或多个资料表中，选取一或多个资料列或资料行。SELECT陈述式的完整语法相当复杂，但主要子句可摘要为：SELECTselect_listINTOnew_tableFROMtable_sourceWHEREsearch_conditionGROUPBYgroup_by_expressionHAVINGsearch_conditionORDERBYorder_expressionASC|DESC例：“Persons

2、”表中的数据有LastNameFirstNameAddressCityHansenOlaTimoteivn10SandnesSvendsonToveBorgvn23SandnesPettersenKariStorgt20Stavanger选出字段名”LastName”、”FirstName”的数据SELECTLastName,FirstNameFROMPersons返回结果:LastNameFirstNameHansenOlaSvendsonTovePettersenKari选出所有字段的数据SELECT*FROMPersons返回结果:LastNameFirstNameAddressCity

3、HansenOlaTimoteivn10SandnesSvendsonToveBorgvn23SandnesPettersenKariStorgt20StavangerWhere用途：被用来规定一种选择查询的标准语法：SELECTcolumnFROMtableWHEREcolumncondition&118;alue下面的操作符能被使用在WHERE中：=,=,=,BETWEEN,LIKE注意：在某些SQL的版本中不等号能被写作为!=解释：SELECT语句返回WHERE子句中条件为true的数据例：从”Persons”表中选出生活在”Sandnes”的人SELECT*FROMPersonsWHE

4、RECity=SandnesPersons表中的数据有:LastNameFirstNameAddressCityYearHansenOlaTimoteivn10Sandnes1951SvendsonToveBorgvn23Sandnes1978SvendsonStaleKaivn18Sandnes1980PettersenKariStorgt20Stavanger1960返回结果:LastNameFirstNameAddressCityYearHansenOlaTimoteivn10Sandnes1951SvendsonToveBorgvn23Sandnes1978SvendsonStaleK

5、aivn18Sandnes1980And&Or用途：在WHERE子句中AND和OR被用来连接两个或者更多的条件解释：AND在结合两个布尔表达式时，只有在两个表达式都为TRUE时才传回TRUEOR在结合两个布尔表达式时，只要其中一个条件为TRUE时，OR便传回TRUE例：Persons表中的原始数据:LastNameFirstNameAddressCityHansenOlaTimoteivn10SandnesSvendsonToveBorgvn23SandnesSvendsonStephenKaivn18Sandnes用AND运算子来查找Persons表中FirstName为”Tove”而且La

6、stName为”Svendson”的数据SELECT*FROMPersonsWHEREFirstName=ToveANDLastName=Svendson返回结果:LastNameFirstNameAddressCitySvendsonToveBorgvn23Sandnes用OR运算子来查找Persons表中FirstName为”Tove”或者LastName为”Svendson”的数据SELECT*FROMPersonsWHEREfirstname=ToveORlastname=Svendson返回结果:LastNameFirstNameAddressCitySvendsonToveBorg

7、vn23SandnesSvendsonStephenKaivn18Sandnes你也能结合AND和OR(使用括号形成复杂的表达式),如:SELECT*FROMPersonsWHERE(FirstName=ToveORFirstName=Stephen)ANDLastName=Svendson返回结果:LastNameFirstNameAddressCitySvendsonToveBorgvn23SandnesSvendsonStephenKaivn18SandnesBetweenAnd用途：指定需返回数据的范围语法：SELECTcolumn_nameFROMtable_nameWHEREcol

8、umn_nameBETWEEN&118;alue1AND&118;alue2例：“Persons”表中的原始数据LastNameFirstNameAddressCityHansenOlaTimoteivn10SandnesNordmannAnnaNeset18SandnesPettersenKariStorgt20StavangerSvendsonToveBorgvn23Sandnes用BETWEENAND返回LastName为从”Hansen”到”Pettersen”的数据：SELECT*FROMPersonsWHERELastNameBETWEENHansenANDPettersen返回结

9、果:LastNameFirstNameAddressCityHansenOlaTimoteivn10SandnesNordmannAnnaNeset18SandnesPettersenKariStorgt20Stavanger为了显示指定范围之外的数据，也可以用NOT操作符：SELECT*FROMPersonsWHERELastNameNOTBETWEENHansenANDPettersen返回结果:LastNameFirstNameAddressCitySvendsonToveBorgvn23SandnesDistinct用途：DISTINCT关键字被用作返回唯一的值语法：SELECTDIS

10、TINCTcolumn-name(s)FROMtable-name解释：当column-name(s)中存在重复的值时，返回结果仅留下一个例：“Orders”表中的原始数据CompanyOrderNumberSega3412W3Schools2312Trio4678W3Schools6798用DISTINCT关键字返回Company字段中唯一的值：SELECTDISTINCTCompanyFROMOrders返回结果:CompanySegaW3SchoolsTrioOrderby用途：指定结果集的排序语法：SELECTcolumn-name(s)FROMtable-nameORDERBYord

11、er_by_expressionASC|DESC解释：指定结果集的排序，可以按照ASC(递增方式排序，从最低值到最高值)或者DESC(递减方式排序，从最高值到最低值)的方式进行排序，默认的方式是ASC例：“Orders”表中的原始数据:CompanyOrderNumberSega3412ABCShop5678W3Schools2312W3Schools6798按照Company字段的升序方式返回结果集：SELECTCompany,OrderNumberFROMOrdersORDERBYCompany返回结果:CompanyOrderNumberABCShop5678Sega3412W3Scho

12、ols6798W3Schools2312按照Company字段的降序方式返回结果集：SELECTCompany,OrderNumberFROMOrdersORDERBYCompanyDESC返回结果:CompanyOrderNumberW3Schools6798W3Schools2312Sega3412ABCShop5678Groupby用途：对结果集进行分组，常与汇总函数一起使用。语法：SELECTcolumn,SUM(column)FROMtableGROUPBYcolumn例：“Sales”表中的原始数据:CompanyAmountW3Schools5500IBM4500W3School

13、s7100按照Company字段进行分组，求出每个Company的Amout的合计：SELECTCompany,SUM(Amount)FROMSalesGROUPBYCompany返回结果：CompanySUM(Amount)W3Schools12600IBM4500Having用途：指定群组或汇总的搜寻条件。语法：SELECTcolumn,SUM(column)FROMtableGROUPBYcolumnHAVINGSUM(column)condition&118;alue解释：HAVING通常与GROUPBY子句同时使用。不使用GROUPBY时，HAVING则与WHERE子句功能相似。例：

14、“Sales”表中的原始数据:CompanyAmountW3Schools5500IBM4500W3Schools7100按照Company字段进行分组，求出每个Company的Amout的合计在10000以上的数据：SELECTCompany,SUM(Amount)FROMSalesGROUPBYCompanyHAVINGSUM(Amount)10000返回结果：CompanySUM(Amount)W3Schools12600Join用途：当你要从两个或者以上的表中选取结果集时，你就会用到JOIN。例：“Employees”表中的数据如下，（其中ID为主键）:IDName01Hansen,O

15、la02Svendson,Tove03Svendson,Stephen04Pettersen,Kari“Orders”表中的数据如下：IDProduct01Printer03Table03Chair用Employees的ID和Orders的ID相关联选取数据：SELECTEmployees.Name,Orders.ProductFROMEmployees,OrdersWHEREEmployees.ID=Orders.ID返回结果：NameProductHansen,OlaPrinterSvendson,StephenTableSvendson,StephenChair或者你也可以用JOIN关键

16、字来完成上面的操作：SELECTEmployees.Name,Orders.ProductFROMEmployeesINNERJOINOrdersONEmployees.ID=Orders.IDINNERJOIN的语法：SELECTfield1,field2,field3FROMfirst_tableINNERJOINsecond_tableONfirst_table.keyfield=second_table.foreign_keyfield解释：INNERJOIN返回的结果集是两个表中所有相匹配的数据。LEFTJOIN的语法：SELECTfield1,field2,field3FROMfi

17、rst_tableLEFTJOINsecond_tableONfirst_table.keyfield=second_table.foreign_keyfield用”Employees”表去左外联结”Orders”表去找出相关数据：SELECTEmployees.Name,Orders.ProductFROMEmployeesLEFTJOINOrdersONEmployees.ID=Orders.ID返回结果：NameProductHansen,OlaPrinterSvendson,ToveSvendson,StephenTableSvendson,StephenChairPettersen,

18、Kari解释：LEFTJOIN返回”first_table”中所有的行尽管在”second_table”中没有相匹配的数据。RIGHTJOIN的语法：SELECTfield1,field2,field3FROMfirst_tableRIGHTJOINsecond_tableONfirst_table.keyfield=second_table.foreign_keyfield用”Employees”表去右外联结”Orders”表去找出相关数据：SELECTEmployees.Name,Orders.ProductFROMEmployeesRIGHTJOINOrdersONEmployees.I

19、D=Orders.ID返回结果：NameProductHansen,OlaPrinterSvendson,StephenTableSvendson,StephenChair解释：RIGHTJOIN返回”second_table”中所有的行尽管在”first_table”中没有相匹配的数据。Alias用途：可用在表、结果集或者列上，为它们取一个逻辑名称语法：给列取别名：SELECTcolumnAScolumn_aliasFROMtable给表取别名：SELECTcolumnFROMtableAStable_alias例：“Persons”表中的原始数据:LastNameFirstNameAddr

20、essCityHansenOlaTimoteivn10SandnesSvendsonToveBorgvn23SandnesPettersenKariStorgt20Stavanger运行下面的SQL：SELECTLastNameASFamily,FirstNameASNameFROMPersons返回结果：FamilyNameHansenOlaSvendsonTovePettersenKari运行下面的SQL：SELECTLastName,FirstNameFROMPersonsASEmployees返回结果：Employees中的数据有：LastNameFirstNameHansenOlaS

21、vendsonTovePettersenKariInsertInto用途：在表中插入新行语法：插入一行数据INSERTINTOtable_name&118;alueS(&118;alue1,&118;alue2,.)插入一行数据在指定的字段上INSERTINTOtable_name(column1,column2,.)&118;alueS(&118;alue1,&118;alue2,.)例：“Persons”表中的原始数据:LastNameFirstNameAddressCityPettersenKariStorgt20Stavanger运行下面的SQL插入一行数据：INSERTINTOPer

22、sons&118;alueS(Hetland,Camilla,Hagabakka24,Sandnes)插入后”Persons”表中的数据为：LastNameFirstNameAddressCityPettersenKariStorgt20StavangerHetlandCamillaHagabakka24Sandnes运行下面的SQL插入一行数据在指定的字段上：INSERTINTOPersons(LastName,Address)&118;alueS(Rasmussen,Storgt67)插入后”Persons”表中的数据为：LastNameFirstNameAddressCityPetter

23、senKariStorgt20StavangerHetlandCamillaHagabakka24SandnesRasmussenStorgt67Update用途：更新表中原有数据语法：UPDATEtable_nameSETcolumn_name=new_&118;alueWHEREcolumn_name=some_&118;alue例：“Person”表中的原始数据:LastNameFirstNameAddressCityNilsenFredKirkegt56StavangerRasmussenStorgt67运行下面的SQL将Person表中LastName字段为”Rasmussen”的F

24、irstName更新为”Nina”：UPDATEPersonSETFirstName=NinaWHERELastName=Rasmussen更新后”Person”表中的数据为：LastNameFirstNameAddressCityNilsenFredKirkegt56StavangerRasmussenNinaStorgt67同样的，用UPDATE语句也可以同时更新多个字段：UPDATEPersonSETAddress=Stien12,City=StavangerWHERELastName=Rasmussen更新后”Person”表中的数据为：LastNameFirstNameAddressCityNilsenFredKirkegt56StavangerRasmussenNinaStien12StavangerDelete用途：删除表中的数据语法：DELETEFROMtable_nameWHEREcolumn_name=some_&118;alue例：“Person”表中的原始数据:LastNameFirstNameAddressCityNilsenFredKirkegt56StavangerRasmussenNinaStien12Stava