sql server语法总结

参考慕课网课程SQL Server基础–T-SQL语句

基本概念

（1）row/record, field/cell, column/attribute：

（2）主键、外键：

T-SQL语句

（可以选中某行，按f5执行）

SELECT <table fields list> FROM <table names list>WHERE <row constraints specification>GROUP BY <grouping specification>--集合运算HAVING <grouping selection specification>--集合运算限制条件ORDER BY <order rules specification>--排序

单行注释：–
多行注释：/……/

（1）use：

use [AdventureWorks2012]--database名字go

（2）select…from。order by,isnull,as,+,算数表达式（+ - * /）：

select Top 100 * from [Production].[Product]--表   前100行SELECT * FROM SALES.SALESORDERDETAIL--直接把表拖过来会有[ ]，也可以手动输入select ProductID, Name, ProductNumber, Color, Size, ListPrice from Production.Product

加入order by：

select ProductID, Name, ProductNumber, Color, Size, ListPrice from Production.Productorder by listprice desc --desc=descending order倒序 ; asc=ascending order顺序select ProductID, Name, ProductNumber, Color, Size, ListPrice from Production.Productorder by listprice desc,Nameselect ProductID, Name, ProductNumber, Color, Size, ListPrice from Production.Productorder by 2--按照选出来的第二个进行排列

算数表达式（+ - * /）:

isnull:

select ProductID, Name, ProductNumber, isnull(Color,''), isnull(Size,''), ListPrice --用' '**代替**为NULL的Color,sizefrom Production.Product

as:起别名

select ProductID, Name, ProductNumber, isnull(Color,'') as Color, isnull(Size,'') as Size123, --using an alias ListPrice from Production.Product

+：连接列与字符串

select ProductID, Name as ProductName, --using an alias'The list price for ' + ProductNumber + ' is $ ' + convert(varchar,ListPrice) +'.' ,-- convert(varchar,ListPrice)  varchar'The list price for ' + ProductNumber + ' is $ ' + convert(varchar,ListPrice) +'.' as [Description] --using brackets to let SQL server conside the strin as a column namefrom Production.Product--1 Adjustable Race The list price for AR-5381 is $ 0.00.The list price for AR-5381 is $ 0.00.

算数表达式（+ - * /）:

select BusinessEntityID,rate*40*52 as AnnualSalary,round(rate*40*52,1) as AnnualSalary--四舍五入，小数点之后保留1位（，1）,round(rate*40*52,0) as AnnualSalary from [HumanResources].[EmployeePayHistory]select BusinessEntityID,(rate+5)*40*52 as AnnualSalaryfrom [HumanResources].[EmployeePayHistory]

（3）where：

select * from [Sales].[SalesOrderHeader]select * from [Sales].[SalesOrderHeader]where SalesPersonID=275--使用"="：intselect * from [Sales].[SalesOrderHeader]where SalesOrderNumber='so43670'--string：加' 'select * from [Sales].[SalesOrderHeader]where TotalDue>5000

字符串加单引号

使用and和or：

select SalesOrderID,OrderDate,SalesPersonID,TotalDue as TotalSales from [Sales].[SalesOrderHeader]where SalesPersonID=275 and TotalDue>5000 --=,>,<,>=,<=,<>select SalesOrderID,OrderDate,SalesPersonID,TotalDue as TotalSales from [Sales].[SalesOrderHeader]where SalesPersonID=275 and TotalDue>5000 and Orderdate between '2005-08-01' and '1/1/2006'--between ... and ...  日期表示2种方式select SalesOrderID,OrderDate,SalesPersonID,TotalDue as TotalSales from [Sales].[SalesOrderHeader]where SalesPersonID=275 and TotalDue>5000 and Orderdate >= '2005-08-01' and Orderdate < '1/1/2006'-->=  <           日期表示select * from [Production].[Product]where name ='Mountain-100 Silver, 38'

Like、%、_：（模糊查询）

select * from [Production].[Product]where name like'Mountain'select * from [Production].[Product]where name like'%Mountain%' -- %: any zero or more charactersselect * from [Production].[Product]where name like'mountain%' select * from [Production].[Product]where name like'_ountain%'-- "_" :single character

in、not in：

select * from [Production].[Product]where color in ('red','white','black')select * from [Production].[Product]where size in ('60','61','62')select * from [Production].[Product]where class not in ('H') -- 等同于: <> 'H'（不等于）

is null、is not null：

select * from [Production].[Product]where size is nullselect * from [Production].[Product]where size is not null

例子：

select SalesOrderID,OrderDate,SalesPersonID,TotalDue as TotalSales from [Sales].[SalesOrderHeader]where (SalesPersonID=275 or SalesPersonID=278)  and TotalDue>5000

（4）聚合函数：

count、distinct：

select count(SalesPersonID)--count：数一下有多少行from [Sales].[SalesOrderHeader]where SalesPersonID is not nullselect distinct(SalesPersonID)--distinct：独一无二的：会列出来from [Sales].[SalesOrderHeader]where SalesPersonID is not nullselect count(distinct(SalesPersonID))from [Sales].[SalesOrderHeader]where SalesPersonID is not null

集合运算方式：

select Avg(TotalDue) as AverageTotalSales,Min(TotalDue) as MinimumTotalSales  ,Max(TotalDue) as MaximumTotalSales,Sum(TotalDue) as SummaryTotalSalesfrom [Sales].[SalesOrderHeader]

group by:（普通 列…）

select SalesPersonID,Max(TotalDue) as MaximumTotalSales from [Sales].[SalesOrderHeader] --Error Message: Column 'Sales.SalesOrderHeader.SalesPersonID' is invalid in the select list --because it is not contained in either an aggregate function or the GROUP BY clause.select SalesPersonID,Max(TotalDue) as MaximumTotalSales--针对每一个SalesPersonID的Max(TotalDue) from [Sales].[SalesOrderHeader]where SalesPersonID is not nullgroup by SalesPersonID--------------------------------order by SalesPersonID--例：select SalesPersonID,OrderDate,Max(TotalDue) as MaximumTotalSales from [Sales].[SalesOrderHeader]where SalesPersonID is not nullgroup by SalesPersonID,OrderDate --Remember to put all un-aggregated columns after "group by"!!!order by SalesPersonID

having：

select SalesPersonID,OrderDate,Max(TotalDue) as MaximumTotalSales from [Sales].[SalesOrderHeader]where SalesPersonID is not nullgroup by SalesPersonID,OrderDate having Max(TotalDue)>150000--必须为Max(TotalDue)的限制条件order by SalesPersonID

——————–例子 ———————————————–

select SalesPersonID,OrderDate,Max(TotalDue) as MaximumTotalSales from [Sales].[SalesOrderHeader]where SalesPersonID is not null and OrderDate >='2007/1/1'group by SalesPersonID,OrderDate ------普通columnhaving Max(TotalDue)>150000-------order by OrderDate desc