1、SQL语法祥解SQL语法详解 例: “Persons” 表中的数据有LastName FirstName Address CityHansen Ola Timoteivn 10 SandnesSvendson Tove Borgvn 23 SandnesPettersen Kari Storgt 20 StavangeSelect用途:从指定表中取出指定的列的数据语法:SELECT column_name(s) FROM table_name解释:从数据库中选取资料列,并允许从一或多个资料表中,选取一或多个资料列或资料行。SELECT 陈述式的完整语法相当复杂,但主要子句可摘要为:SELECT
2、 select_list INTO new_table FROM table_source WHERE search_condition GROUP BY group_by_expression HAVING search_condition ORDER BY order_expression ASC | DESC 选出字段名” LastName”、” FirstName” 的数据SELECT LastName,FirstName FROM Persons 返回结果:LastNameFirstNameHansenOlaSvendsonTovePettersenKa选出所有字段的数据SELECT
3、 * FROM Persons返回结果:LastNameFirstNameAddressCityHansenOlaTimoteivn 10SandnesSvendsonToveBorgvn 23SandnesPettersenKariStorgt 20StavangWhere用途:被用来规定一种选择查询的标准语法:SELECT column FROM table WHERE column condition &118;alue下面的操作符能被使用在WHERE中:=,=,=,BETWEEN,LIKE注意: 在某些SQL的版本中不等号能被写作为!=解释:SELECT语句返回WHERE子句中条件为t
4、rue的数据例:从” Persons”表中选出生活在” Sandnes” 的人SELECT * FROM Persons WHERE City=Sandnes返回结果:LastName FirstName Address City YearHansen Ola Timoteivn 10 Sandnes 1951Svendson Tove Borgvn 23 Sandnes 1978Svendson Stale Kaivn 18 Sandnes 1980And & Or用途:在WHERE子句中AND和OR被用来连接两个或者更多的条件解释:AND在结合两个布尔表达式时,只有在两个表达式都为 TRU
5、E 时才传回 TRUEOR在结合两个布尔表达式时,只要其中一个条件为 TRUE 时,OR便传回 TRUE例:Persons 表中的原始数据:LastName FirstName Address CityHansen Ola Timoteivn 10 SandnesSvendson Tove Borgvn 23 SandnesSvendson Stephen Kaivn 18 Sandnes用AND运算子来查找Persons 表中FirstName为”Tove”而且LastName为” Svendson”的数据SELECT * FROM PersonsWHERE FirstName=ToveAN
6、D LastName=Svendson返回结果:LastName FirstName Address CitySvendson Tove Borgvn 23 Sandnes用OR运算子来查找Persons 表中FirstName为”Tove”或者LastName为” Svendson”的数据SELECT * FROM PersonsWHERE firstname=ToveOR lastname=Svendson返回结果:LastName FirstName Address CitySvendson Tove Borgvn 23 SandnesSvendson Stephen Kaivn 18
7、Sandnes你也能结合AND和OR (使用括号形成复杂的表达式),如:SELECT * FROM Persons WHERE(FirstName=Tove OR FirstName=Stephen)AND LastName=Svendson返回结果:LastName FirstName Address CitySvendson Tove Borgvn 23 SandnesSvendson Stephen Kaivn 18 SandnesBetweenAnd用途:指定需返回数据的范围语法:SELECT column_name FROM table_nameWHERE column_nameBE
8、TWEEN &118;alue1 AND &118;alue2例:“Persons”表中的原始数据LastNameFirstNameAddressCityHansenOlaTimoteivn 10SandnesNordmannAnnaNeset 18SandnesPettersenKariStorgt 20StavangerSvendsonToveBorgvn 23Sandnes用BETWEENAND返回LastName为从”Hansen”到”Pettersen”的数据:SELECT * FROM Persons WHERE LastName BETWEEN Hansen AND Petter
9、sen返回结果:LastNameFirstNameAddressCityHansenOlaTimoteivn 10SandnesNordmannAnnaNeset 18SandnesPettersenKariStorgt 20Stavanger为了显示指定范围之外的数据,也可以用NOT操作符:SELECT * FROM Persons WHERE LastName NOT BETWEEN Hansen AND Pettersen返回结果:LastName FirstName Address CitySvendson Tove Borgvn 23 SandnesDistinct用途:DISTIN
10、CT关键字被用作返回唯一的值语法:SELECT DISTINCT column-name(s) FROM table-name解释:当column-name(s)中存在重复的值时,返回结果仅留下一个例:“Orders”表中的原始数据CompanyOrderNumberSega3412W3Schools2312Trio4678W3Schools679用DISTINCT关键字返回Company字段中唯一的值:SELECT DISTINCT Company FROM Orders返回结果:Company SegaW3Schools TrioOrder by用途:指定结果集的排序语法:SELECT c
11、olumn-name(s) FROM table-name ORDER BY order_by_expression ASC | DESC 解释:指定结果集的排序,可以按照ASC(递增方式排序,从最低值到最高值)或者DESC(递减方式排序,从最高值到最低值)的方式进行排序,默认的方式是ASC例:“Orders”表中的原始数据:CompanyOrderNumberSega3412ABC Shop5678W3Schools2312W3Schools6798按照Company字段的升序方式返回结果集:SELECT Company, OrderNumber FROM OrdersORDER BY Co
12、mpany返回结果:CompanyOrderNumberABC Shop5678Sega3412W3Schools6798W3Schools2312按照Company字段的降序方式返回结果集:SELECT Company, OrderNumber FROM OrdersORDER BY Company DESC返回结果:CompanyOrderNumberW3Schools6798W3Schools2312Sega3412ABC Shop5678Group by用途:对结果集进行分组,常与汇总函数一起使用。语法:SELECT column,SUM(column) FROM table GROU
13、P BY column例:“Sales”表中的原始数据:CompanyAmountW3Schools5500IBM4500W3Schools7100按照Company字段进行分组,求出每个Company的Amout的合计:SELECT Company,SUM(Amount) FROM SalesGROUP BY Company返回结果:CompanySUM(Amount)W3Schools12600IBM4500Having用途:指定群组或汇总的搜寻条件。语法:SELECT column,SUM(column) FROM tableGROUP BY columnHAVING SUM(colum
14、n) condition &118;alue解释:HAVING 通常与 GROUP BY 子句同时使用。不使用 GROUP BY 时,HAVING 则与 WHERE 子句功能相似。例:“Sales”表中的原始数据:CompanyAmountW3Schools5500IBM4500W3Schools7100按照Company字段进行分组,求出每个Company的Amout的合计在10000以上的数据:SELECT Company,SUM(Amount) FROM SalesGROUP BY Company HAVING SUM(Amount)10000返回结果:Company SUM(Amoun
15、t)W3Schools 12600Join用途:当你要从两个或者以上的表中选取结果集时,你就会用到JOIN。例:“Employees”表中的数据如下,(其中ID为主键):IDName01Hansen, Ola02Svendson, Tove03Svendson, Stephen04Pettersen, Kar“Orders”表中的数据如下:IDProduct01Printer03Table03Chai用Employees的ID和Orders的ID相关联选取数据:SELECT Employees.Name, Orders.ProductFROM Employees, OrdersWHERE Em
16、ployees.ID = Orders.ID返回结果:NameProductHansen, OlaPrinterSvendson, StephenTableSvendson, StephenChair或者你也可以用JOIN关键字来完成上面的操作:SELECT Employees.Name, Orders.ProductFROM EmployeesINNER JOIN OrdersON Employees.ID = Orders.IDINNER JOIN的语法:SELECT field1, field2, field3FROM first_tableINNER JOIN second_table
17、ON first_table.keyfield = second_table.foreign_keyfield解释:INNER JOIN返回的结果集是两个表中所有相匹配的数据。LEFT JOIN的语法:SELECT field1, field2, field3FROM first_tableLEFT JOIN second_tableON first_table.keyfield = second_table.foreign_keyfield用”Employees”表去左外联结”Orders”表去找出相关数据:SELECT Employees.Name, Orders.ProductFROM
18、EmployeesLEFT JOIN OrdersON Employees.ID = Orders.ID返回结果:NameProductHansen, OlaPrinterSvendson, ToveSvendson, StephenTableSvendson, StephenChairPettersen, Kari解释:LEFT JOIN返回”first_table”中所有的行,尽管在” second_table”中没有相匹配的数据。RIGHT JOIN的语法:SELECT field1, field2, field3FROM first_tableRIGHT JOIN second_tab
19、leON first_table.keyfield = second_table.foreign_keyfield用”Employees”表去右外联结”Orders”表去找出相关数据:SELECT Employees.Name, Orders.ProductFROM EmployeesRIGHT JOIN OrdersON Employees.ID = Orders.ID返回结果:NameProductHansen, OlaPrinterSvendson, StephenTableSvendson, StephenChai解释:RIGHT JOIN返回” second_table”中所有的行尽
20、管在”first_table”中没有相匹配的数据。Alias用途: 可用在表、结果集或者列上,为它们取一个逻辑名称语法:给列取别名:SELECT column AS column_alias FROM table给表取别名:SELECT column FROM table AS table_alias例:“Persons”表中的原始数据:LastName FirstName Address CityHansen Ola Timoteivn 10 SandnesSvendson Tove Borgvn 23 SandnesPettersen Kari Storgt 20 Stavanger运行下
21、面的SQL:SELECT LastName AS Family, FirstName AS NameFROM Persons返回结果:FamilyNameHansenOlaSvendsonTovePettersenKar运行下面的SQL:SELECT LastName, FirstNameFROM Persons AS Employees返回结果:Employees中的数据有:LastNameFirstNameHansenOlaSvendsonTovePettersenKariInsert Into用途: 在表中插入新行语法:插入一行数据INSERT INTO table_namevalues
22、 (&118;alue1, &118;alue2,.)插入一行数据在指定的字段上INSERT INTO table_name (column1, column2,.)values (&118;alue1, &118;alue2,.)例:“Persons”表中的原始数据:LastName FirstName Address CityPettersen Kari Storgt 20 Stavanger运行下面的SQL插入一行数据:INSERT INTO Personsvalues (Hetland, Camilla, Hagabakka 24, Sandnes)插入后”Persons”表中的数据为
23、:LastName FirstName Address CityPettersen Kari Storgt 20 StavangerHetland Camilla Hagabakka 24 Sandnes运行下面的SQL插入一行数据在指定的字段上:INSERT INTO Persons (LastName, Address)values (Rasmussen, Storgt 67)插入后”Persons”表中的数据为:LastName FirstName Address CityPettersen Kari Storgt 20 StavangerHetland Camilla Hagabakk
24、a 24 SandnesRasmussen Storgt 67Update用途:更新表中原有数据语法:UPDATE table_name SET column_name = new_&118;alueWHERE column_name = some_&118;alue例:“Person”表中的原始数据:LastName FirstName Address CityNilsen Fred Kirkegt 56 StavangerRasmussen Storgt 67运行下面的SQL将Person表中LastName字段为”Rasmussen”的FirstName更新为”Nina”:UPDATE
25、Person SET FirstName = NinaWHERE LastName = Rasmussen更新后”Person”表中的数据为:LastName FirstName Address CityNilsen Fred Kirkegt 56 StavangerRasmussen Nina Storgt 67同样的,用UPDATE语句也可以同时更新多个字段:UPDATE PersonSET Address = Stien 12, City = StavangerWHERE LastName = Rasmussen更新后”Person”表中的数据为:LastName FirstName A
26、ddress CityNilsen Fred Kirkegt 5 StavangerRasmussen Nina Stien 12 StavangerDelete用途:删除表中的数据语法:DELETE FROM table_name WHERE column_name = some_&118;alue例:“Person”表中的原始数据:LastName FirstName Address CityNilsen Fred Kirkegt 56 StavangerRasmussen Nina Stien 12 Stavanger删除Person表中LastName为”Rasmussen”的数据:D
27、ELETE FROM Person WHERE LastName = Rasmussen执行删除语句后”Person”表中的数据为:LastName FirstName Address CityNilsen Fred Kirkegt 56 StavangerCreate Table用途:建立新的资料表。语法:CREATE TABLE table_name(column_name1 data_type,column_name2 data_type,.)例:创建一张叫“Person”的表,该表有4个字段LastName, FirstName, Address, Age:CREATE TABLE P
28、erson(LastName varchar,FirstName varchar,Address varchar,Age int)如果想指定字段的最大存储长度,你可以这样:CREATE TABLE Person(LastName varchar(30),FirstName varchar(30),Address varchar(120),Age int(3)下表中列出了在SQL的一些数据类型:Data TypeDescriptioninteger(size)int(size)smallint(size)tinyint(size)Hold integers only. The maximum number of digits are specified in parenthesis.decimal(size,d)numeric(size,d)Hold numbers with fractions. The maximum number of digits are specified in size. The maximum number of d
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1