SQL-T

排序并返回指定WHERE条件的前10（TOP 10）条记录，语句的执行顺序1-2-3-4-。
4-SELECT TOP (10) ID0, SinglePrice4, TradeDate6
1-FROM DealTable
3-WHERE (SinglePrice4 > 500)
2-ORDER BY SinglePrice4 DESC

排序，返回指定WHERE条件的前10（TOP 10）条记录，再按ORDER BY ID0排序10条记录。语句先执行子查询。ORDER BY为最后的语句。
SELECT ID0, SinglePrice4, TradeDate6
FROM (SELECT TOP (10) ID0, SinglePrice4, TradeDate6
        FROM DealTable
        WHERE (SinglePrice4 > 500)
        ORDER BY SinglePrice4 DESC) AS T
ORDER BY ID0
注意：AS是别名关键字，表示给 AS 前面的表，行，临时查询生成的表等命别名。

联接查询

INNER JOIN只返回两个表中联结字段相等的行
SELECT     Orders.OrderID, Orders.OrderDate, [Order Details].OrderID AS Expr1, [Order Details].ProductID
FROM         Orders INNER JOIN
                      [Order Details] ON Orders.OrderID = [Order Details].OrderID
SELECT o.CustomerID, od.OrderID, o.OrderDate, o.ShippedDate,
      SUM(od.Quantity * od.UnitPrice) AS price, c.CompanyName, e.LastName
FROM Orders AS o INNER JOIN
      Customers AS c ON c.CustomerID = o.CustomerID INNER JOIN
      Employees AS e ON e.EmployeeID = o.EmployeeID INNER JOIN
      [Order Details] AS od ON o.OrderID = od.OrderID AND o.OrderID = od.OrderID
WHERE (YEAR(o.OrderDate) = 1997)
GROUP BY o.CustomerID, c.CompanyName, od.OrderID, o.OrderDate, o.ShippedDate,
      e.LastName
HAVING (SUM(od.Quantity) > 30)
ORDER BY o.CustomerID, od.OrderID

LEFT OUTER JOIN(Sql2000为LEFT JOIN)返回包括左表中的所有记录和右表中联结字段相等的记录,右表没有的字段用null代替
SELECT     SH.CompanyName AS 'cm', SU.CompanyName AS 'sucm'
FROM         Shippers AS SH LEFT OUTER JOIN
                      Suppliers AS SU ON SH.ShipperID = SU.SupplierID
export:
Speedy  Express 
United  Package
Leka    NULL
Trading NULL

REIGHT OUTER JOIN(Sql2000为REIGHT JOIN)返回包括右表中的所有记录和左表中联结字段相等的记录,左表没有的字段用null代替
SELECT     SH.CompanyName AS 'cm', SU.CompanyName AS 'sucm'
FROM         Shippers AS SH REIGHT OUTER JOIN
                      Suppliers AS SU ON SH.ShipperID = SU.SupplierID
export:
Speedy  Express 
United  Package
NULL   Tokyo
NULL   Cooperativa

别名AS，别名可用中文，为表命别名不用单引号包括中文字符，为列命别名应加上单引号
SELECT     OrderID AS 'ID', UnitPrice AS 'Price'
FROM         [Order Details]
SELECT     TOP (10) 我.OrderID AS '我ID', 你.ProductID AS '你ID'
FROM         Orders AS 我 CROSS JOIN
                      [Order Details] AS 你
WHERE     (我.OrderID >= 11000)

表名有空格的用[]
SELECT     OrderID, UnitPrice
FROM         [Order Details]

返回多列值相加的一个值
SELECT     TOP (10) ShipName + ' / ' + ShipAddress + ' / ' + ShipCity AS 'Contect'
FROM         Orders

[NOT] BETWEEN AND，表示满足两个表达示之间的值
SELECT     ShipName AS 'NAME'
FROM         Orders
WHERE     (OrderDate BETWEEN '1996-7-4' AND '1996-7-5')
ORDER BY OrderDate
上面的WHERE条件等价于下面的条件
SELECT     ShipName AS 'NAME'
FROM         Orders
WHERE     (OrderDate >= '1996-7-4') AND (OrderDate <= '1996-7-5')
ORDER BY OrderDate

[NOT] IN()返回IN左右表达式中相等的值
SELECT     ShipName AS 'NAME'
FROM         Orders
WHERE     (OrderDate IN ('1996-7-4', '1996-7-8','1997-7-5','1998-1-12'))
ORDER BY OrderDate
SELECT     ShipName AS 'NAME'
FROM         Orders
WHERE     ('1996-7-5' IN (OrderDate, '1997-7-8'))
ORDER BY OrderDate

LIKE,用于字符数据的查询，查询的字符不区分大小写
有4个通配符：1.% 表示多个字符，2._表示一个字符，3.[]表示指定的范围，4.[^]表示不在指定范围
SELECT     ShipName AS 'NAME'
FROM         Orders
WHERE     (ShipName LIKE 'AL%')
ORDER BY OrderDate
下面的语句查询开头字母不包含V和Y的值
SELECT     ShipName AS 'NAME'
FROM         Orders
WHERE     (ShipName LIKE '[^VY]%')
ORDER BY OrderDate

[NOT] IS NULL 查询返回是空值的对象
SELECT     COUNT(*) AS 'ORID'
FROM         Orders
WHERE     (ShipRegion IS NULL)
SELECT     COUNT(*) AS 'ORID'
FROM         Orders
WHERE     (NOT (ShipRegion IS NULL))

ADN,OR,NOT，与，或，非。SQL不能识别NOT的条件，应避免使用NOT条件
SELECT     COUNT(*) AS 'count'
FROM         Orders
WHERE     (OrderID <= 10271) AND (OrderDate BETWEEN '1996-7-4' AND '1996-7-25') OR
          (OrderDate BETWEEN '1996-7-4' AND '1996-7-25') AND (EmployeeID <= 5)

DISTINCT根据SELECT右边所有列比较在结果中返回绝对唯一的值，下面两个查询返回相同的结果。
SELECT DISTINCT OrderID AS 'ID', EmployeeID AS 'EID'
FROM         Orders
SELECT DISTINCT EmployeeID AS 'EID', OrderID AS 'ID'
FROM         Orders

ORDER BY排序最多可以支持16个列，ASC升序（默认），DESC降序。
SELECT DISTINCT TOP (10) OrderID AS 'ID', OrderDate AS 'ODATE', ShippedDate AS 'SDATE', RequiredDate AS 'RDATE'
FROM         Orders
ORDER BY 'ODATE' DESC, 'SDATE', 'RDATE' DESC
下面的排序语句可用数字代替列名，索引从1开始，而不是0。但不推荐使用数字。
SELECT DISTINCT TOP (10) OrderID AS 'ID', OrderDate AS 'ODATE', ShippedDate AS 'SDATE', RequiredDate AS 'RDATE'
FROM         Orders
ORDER BY 2,3,4

GROUP BY 分组查询，对表达式分组，值相同的为一组。GROUP BY语句中有多个列时，按顺序分组再分组。
对分组排序的列要出现在SELECT语句和GROUP BY语句的一个中。
SELECT SUM(Freight) AS 'f'
FROM Orders
GROUP BY ShipVia
ORDER BY 'f'
SELECT SUM(Freight) AS 'f'
FROM Orders
GROUP BY ShipVia, EmployeeID
ORDER BY 'f'

HAVING组或聚合的搜索条件,和WHERE相同。HAVING通常在GROUP BY后面作为搜索条件。
在 HAVING 子句中不能使用 text、image 和 ntext 数据类型。下面的HAVING语句设置搜索条件时，
SUM(Freight)已经在SELECT里定义了别名，但在其下HAVING语句里不能使用定义的别名，这是因为语句有执行顺序。
SELECT SUM(Freight) AS 'f'
FROM Orders
WHERE (OrderDate < '1998-1-1')
GROUP BY ShipVia, EmployeeID
HAVING (SUM(Freight) < $2000)
ORDER BY 'f'

COMPUTE [BY]
SELECT OrderID AS 'ID', ShipVia AS 'sv', Freight AS 'fr'
FROM Orders
WHERE (ShippedDate BETWEEN '1996-7-20' AND '1997-12-30')
ORDER BY 'ID', ShipName
COMPUTE COUNT(ShipVia), SUM(ShipVia), AVG(Freight) BY 'ID', ShipName
－－－－－－－－－－－－－－聚合函数－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
聚合函数包含COUNT(),AVG(),SUM(),MAX(),MIN()，这些函数都返回一个整数。

COUNT()求不含NULL的值的个数，另一个用法COUNT(*)用于求表中有多少行
SELECT     COUNT(OrderID) AS 'OCOUNT', COUNT(Freight) AS 'FCOUNT', COUNT(ShipPostalCode) AS 'SPCCOUNT'
FROM         Orders

AVG()求平均值，只能对数字型的数据计算结果
SELECT     COUNT(OrderID) AS 'OCOUNT', AVG(Freight) AS 'FCOUNT', MIN(ShipPostalCode) AS 'SPCCOUNT'
FROM         Orders

SUM()求和，等于所有查询的值相加，并且只能对数字型的数据计算结果
SELECT     COUNT(OrderID) AS 'OCOUNT', SUM(Freight) AS 'FCOUNT', MIN(ShipPostalCode) AS 'SPCCOUNT'
FROM         Orders

MIN(),MAX()求最小值和最大值。除对数字型，还可以计算日期型，字符型。但不能用于 bit 型。
SELECT     COUNT(OrderID) AS 'OCOUNT', MIN(Freight) AS 'FCOUNT', MAX(ShipPostalCode) AS 'SPCCOUNT'
FROM         Orders
SELECT     COUNT(OrderID) AS 'OCOUNT', MAX(ShipAddress) AS 'FCOUNT', MIN(OrderDate) AS 'SPCCOUNT'
FROM         Orders
－－－－－－－－－－－－－－算述运算函数－－－－－－－－－－－－－－－－－－－－－－－－－－－
标准算述
SELECT     OrderID, UnitPrice * 1.1 AS 'Price10%'
FROM         [Order Details]

舍入函数ROUND()，参数2为返回小数点后的位数，如果查询字段是货币类型默认返回小数点后4位，如：ROUND(5.123132,2)返回5.1200。PI()返回常量3.14159...
SELECT     OrderID, ROUND(UnitPrice * 1.1 / PI(), 2) AS Expr1
FROM         [Order Details]
SELECT     OrderID AS 'ID', UnitPrice AS 'Price', ROUND(Discount, 1) AS 'Num'
FROM         [Order Details]

RAND(3)随机数,3为基值
SELECT     OrderID, RAND(3) AS Expr1
FROM         [Order Details]
SELECT     OrderID, ROUND(UnitPrice * 1.1 / RAND(), 2) AS Expr1
FROM         [Order Details]

ASB()绝对值,返回正负数的绝对正数
SELECT     OrderID, ROUND(ABS(RAND() * - 5), 2) AS Expr1
FROM         [Order Details]

CEILING()返回大于或等于表达式的最小整数
SELECT     OrderID, CEILING(UnitPrice * Quantity / PI()) AS Expr1
FROM         [Order Details]
ORDER BY OrderID

FLOOR()返回小于或等于表达式的最大整数
SELECT     OrderID, FLOOR(UnitPrice * Quantity / PI()) AS Expr1
FROM         [Order Details]
ORDER BY OrderID

SIGN()正，零，负返回1,0,-1
SELECT     OrderID, SIGN(ROUND(UnitPrice % 2, 0) - 1) AS Expr1
FROM         [Order Details]
ORDER BY OrderID

－－－－－－－－－－－－－－字符函数－－－－－－－－－－－－－－－－－－－－－－－－－－－
ASCII()返回第一个字符或数字对应的ASCII值，UNICODE()使用方法同ASCII(),返回Unicode值
SELECT     TOP (10) od.OrderID AS 'ID', ASCII(od.UnitPrice) AS 'Price', ASCII(o.CustomerID) AS 'CustomerASCII'
FROM         [Order Details] AS od INNER JOIN
                      Orders AS o ON od.OrderID = o.OrderID
WHERE     (o.EmployeeID = 5)

CHAR()只能执行数字型数据，返回当数字对应的ASCII字符，没有对应返回空值。操作非数字型数据出错。NCHAR()使用方法同CHAR(),返回Unicode字符
SELECT     TOP (5) OrderID AS 'ID', CHAR(EmployeeID) AS 'EmployeeIDChar', CHAR(ShipVia) AS 'ShipViarChar'
FROM         Orders
WHERE     (OrderID >= 11000)
SELECT     TOP (5) OrderID AS 'ID', CHAR(UnitPrice) AS 'UP'
FROM         [Order Details]
WHERE     (OrderID >= 11000)

QUOTENAME()为结果最左右加上符号，可以的符号包括：单双引号，大中小括号，默认用中括号
SELECT     TOP (5) OrderID AS 'ID', QUOTENAME(UnitPrice, '[]') AS 'UP'
FROM         [Order Details]
WHERE     (OrderID >= 11000)
SELECT     TOP (5) OrderID AS 'ID', QUOTENAME(UnitPrice, '""') AS 'UP'
FROM         [Order Details]
WHERE     (OrderID >= 11000)
SELECT     TOP (5) OrderID AS 'ID', QUOTENAME(UnitPrice, '()') AS 'UP'
FROM         [Order Details]
WHERE     (OrderID >= 11000)
SELECT     TOP (5) OrderID AS 'ID', QUOTENAME(UnitPrice, '{}') AS 'UP'
FROM         [Order Details]
WHERE     (OrderID >= 11000)

STR()数字转字符
SELECT     TOP (10) STR(UnitPrice, LEN(UnitPrice), 1) AS 'Price'
FROM         [Order Details]
WHERE     (OrderID >= 11000)

LEN()返回不含尾部空格的字符长度
SELECT     LEN(UnitPrice) AS 'Price'
FROM         [Order Details]
WHERE     (OrderID >= 11000)

LEFT()从字符串左边返回指定个数字符,RIGHT()同LEFT().
SELECT     TOP (10) CHAR(LEFT(OrderID, 2)) AS 'Price'
FROM         [Order Details]
WHERE     (OrderID >= 11000)
SELECT     TOP (10) RIGHT(OrderID, 2) AS 'Price'
FROM         [Order Details]
WHERE     (OrderID >= 11000)

LTRIM()删除字符串左边空格，RTRIM()删除右边
SELECT     TOP (10) LTRIM(OrderID) AS Expr1
FROM         Orders
WHERE     (OrderID >= 11000)
ORDER BY 'Stuff'

STUFF()删除并替换删除的内容，如：STUFF('abcdef',2,1,'bc')从'abcdef'第2个字符开始删除1个字符并用'bc'替换删除的字符
SELECT     TOP (10) STUFF(LTRIM(CustomerID), 3, 2, RTRIM(OrderID)) AS 'Stuff'
FROM         Orders
WHERE     (OrderID >= 11000)
ORDER BY 'Stuff'

LOWER()大写字母转小写，UPPER()相反
SELECT     TOP (10) LOWER(CustomerID) AS Expr1
FROM         Orders
WHERE     (OrderID >= 11000)
ORDER BY 'Stuff'

PATINDEX()查找字符串中首次出现要找字符的位置,前后必须使用通配符%（查找第一个或最后一个字符时除外）。
SELECT     TOP (10) PATINDEX('%AF%', CustomerID) AS Expr1
FROM         Orders
WHERE     (OrderID <= 11000) AND (OrderID >= 10990)
ORDER BY 'Stuff'
SELECT     TOP (10) PATINDEX('Q%', CustomerID) AS Expr1
FROM         Orders
WHERE     (OrderID <= 11000) AND (OrderID >= 10990)
ORDER BY 'Stuff'
SELECT     TOP (10) PATINDEX('%K', CustomerID) AS Expr1
FROM         Orders
WHERE     (OrderID <= 11000) AND (OrderID >= 10990)
ORDER BY 'Stuff'

－－－－－－－－－－－－－－日期时间函数－－－－－－－－－－－－－－－－－－－－－－－－－－－
YEAR(),MONTH(),DAY()返回int类型的年，月，日。
SELECT TOP (5) YEAR(OrderDate) AS 'Year', MONTH(OrderDate) AS 'Month',
      DAY(OrderDate) AS 'Day'
FROM Orders
ORDER BY OrderID

GETDATE()返回系统当前时间的SQL格式：2009-2-9 13:02:24
SELECT GETDATE() AS 'Date'

CURRENT_TIMESTAMP,与GETDATE()相同，但此函数不带括号
SELECT CURRENT_TIMESTAMP AS 'Date'

DATEADD()返回加指定部分的日期，如：DATEADD(YY,2,'2009-5-5')返回'2011-5-5',参数2可为负
SELECT DATEADD(YEAR, - 1, GETDATE()) AS 'Date', GETDATE() AS 'Date2'

DATEDIFF()返回两个日期间指定部分的差额,如：DATEDIFF(S, '1992-5-6 12:33:19', GETDATE())返回两个日期中S（秒）部分差额，
计算方式为1992-5-6 12:33:1到GETDATE()当前系统时间相差多少秒。
SELECT TOP (10) DATEDIFF(D, OrderDate, RequiredDate) AS 'prit'
FROM Orders
SELECT TOP (10) DATEDIFF(S, OrderDate, GETDATE()) AS 'prit'
FROM Orders

DATEPART()返回日期指定部分的int格式
SELECT TOP (10) DATEPART(D, OrderDate) AS 'prit'
FROM Orders

DATENAME()返回日期指定部分的string格式
SELECT TOP (10) DATENAME(D, OrderDate) AS 'prit'
FROM Orders

－－－－－－－－－－－－－－系统和安全函数－－－－－－－－－－－－－－－－－－－－－－－－－－－
DATALENGTH()返回字符实际的字节长度，一个字母为两个字节，一般为字符型数据。如果字符为NULL，返回NULL而不是0。
SELECT TOP (2) OrderDate AS 'Date', DATALENGTH(ShipName) AS 'DateLen'
FROM Orders
ORDER BY CustomerID

COALESCE(),返回表达式中第一个非空值
此示例从表中选择客户优先的一种联系方式，优先级为从左到右的参数。
SELECT TOP (20) Address AS 'address', COALESCE (Region, Fax, HomePage) AS 'LEVE'
FROM Suppliers
此示例中有一个员工年薪信息表，包括三个列：hourly_wage、salary 和 commission。但一个员工只能接收
一种报酬类型。若要确定支付给所有员工的工资总额，可以使用 COALESCE 函数以便只接收 hourly_wage、
salary 和 commission 三个列中的非空值。
SELECT CONVERT(money, COALESCE(hourly_wage * 40 * 52, salary, commission * num_sales)) AS "Total Salary" FROM wages

ISDATE()检查值是否为有效的日期格式，是返回1,不是返回0。检查对象类型可以是字符串，列。
SELECT TOP(10) ISDATE(OrderDate) AS 'isDate', ISDATE(OrderID) AS 'isID'
FROM Orders

ISNULL()检查值是否为空，并为空值添加新的值。
SELECT TOP (10) ISNULL(ShipRegion, '') AS 'ISNull'
FROM Orders
SELECT TOP (10) ISNULL(ShipRegion, 'NULL') AS 'ISNull'
FROM Orders

ISNUMERIC()确定表达式是否为有效的数值类型，包括int,money等。是返回1,不是返回0。
SELECT TOP (10) ISNUMERIC(ShipPostalCode) AS 'ISNumerec'
FROM Orders
ORDER BY OrderID

NEWID()创建一个 uniqueidentifier 类型的唯一值。如：b53d5947-351f-46a2-bd5f-1e1e1fbc96f6
SELECT TOP (10) OrderID AS 'ID', NEWID() AS 'isID'
FROM Orders

NULLIF()当两个表达示结果相同时返回第一个表达式类型的NULL值,否则返回第一个表达式的值。
SELECT TOP (10) NULLIF (DAY(OrderDate), DAY(ShippedDate)) AS 'sameDate'
FROM Orders
SELECT AVG(NULLIF(COALESCE(current_year,previous_year), 0.00)) AS 'Average Budget'
FROM budgets

-------------------用户，主机，安全函数---------------------
CURRENT_USER,返回当前数据库用户名，此参数不带括号
SELECT CURRENT_USER AS 'Date'

HOST_NAME()返回当前主机名，如：LNTF158。HOST_ID()返回当前主机ID。
SELECT HOST_NAME() AS 'Date'

APP_NAME()返回当前会话的应用程序名称
SELECT APP_NAME() AS 'Orders'

SESSION_NAME,返回当前会话的数据库用户名，此函数不带括号
SELECT SESSION_USER AS 'Orders'

SYSTEM_USER返回当前登入用户,如ln-t\liao
SELECT SYSTEM_USER AS 'Date'

USER_NAME()从用户ID中返回用户名
SELECT USER_NAME() AS 'Date'
SELECT USER_NAME(4) AS 'Date'

USER,USER_ID()返回当前用户的数据库用户名，返回数据库用户的标识号。
SELECT USER AS 'USER', USER_ID() AS 'ID'

IS_NEMBER()判断当前用户是否为指定的域组成员或数据库成员,是1，不是0,无效NULL。
SELECT IS_MEMBER('LN-TF\Domain Users') AS 'IS'

PARSENAME()返回SQL标识符对象名称，指定部分的对象名不存在返回NULL,此名称命名规则参见"使用标识符作为对象名称 "。
SELECT PARSENAME('server.database.schema_name.object_name', 4) AS 'Orders' 返回server
SELECT PARSENAME('server.database.schema_name.object_name', 3) AS 'Orders' 返回database
SELECT PARSENAME('server.database.schema_name.object_name', 2) AS 'Orders' 返回schema_name
SELECT PARSENAME('server.database.schema_name.object_name', 1) AS 'Orders' 返回object_name

SUSER_ID(),SUSER_NAME()返回用户的登录标识号，返回用户的登录标识名。此 ID 不等同于 SUSER_SID 返回
的登录名的 SID。如果 login 是 SQL Server 登录名，则 SID 映射到 GUID。如果 login 是 Windows 登录名或
Windows 组，则 SID 映射到 Windows 安全标识符。
SELECT SUSER_ID() AS 'ID', SUSER_NAME() AS 'NAME'
SELECT SUSER_ID(SUSER_NAME()) AS 'ID', SUSER_NAME() AS 'NAME'

SUSER_SID(),SUSER_SNAME()返回指定登录名的安全标识号 (SID)，返回与安全标识号 (SID) 关联的登录名。
SELECT SUSER_SNAME(SUSER_SID()) AS 'ID', SUSER_SNAME() AS 'NAME'
－－－－－－－－－－－－－－类型转换函数－－－－－－－－－－－－－－－－－－－－－－－－－－－
CAST()数据类型转换
SELECT CAST(CAST(0x41 AS nvarchar) AS varbinary) AS Expr1

CONVERT()数据类型转换，同CAST()作用一样，但用法不同。
SELECT CONVERT(varbinary, CAST(0x41 AS nvarchar)) AS Expr1
SELECT CONVERT(varbinary, CONVERT(nvarchar,0x41)) AS Expr1

COL_NAME(),OBJECT_ID()根据指定的表标识号和列标识号返回列的名称，返回数据库中表的对应标识号。
SELECT COL_NAME(OBJECT_ID('Orders'), 1) AS 'colName'

OBJECT_NAME(),OBJECT_ID()根据指定的表标识号返回表名，根据指定的表名返回标识号。
SELECT OBJECT_NAME(OBJECT_ID('Orders')) AS 'colName'

DB_ID(),DB_NAME()根据指定或默认（不指定）的表返回对应标识号，根据对应DB_ID返回带绝对路径的数据库名
如：E:\0A\DBCONNECTION\DBCONNECTION\DB\SQL SERVER 2000 SAMPLE DATABASES\NORTHWND.MDF
SELECT DB_NAME(DB_ID()) AS 'colName'

FILE_IDEX,FILE_NAME()根据指定数据库或日志的文件名返回对应的标识号，相反
SELECT FILE_IDEX(FILE_NAME(1)) AS 'File ID'
SELECT FILE_IDEX('Northwind') AS 'File ID'
SELECT FILE_IDEX('Northwind_Log') AS 'File ID'

FILEGROUP_ID(),FILEGROUP_NAME(),返回指定文件组名称的文件组标识 (ID) 号，相反
SELECT FILEGROUP_NAME(1) AS 'File ID'
SELECT FILEGROUP_ID('PRIMARY') AS 'File ID'