--
--2.6节示例
--
--IN操作符的使用
IF(2 IN (1,2,3,4))--在条件语句中使用
PRINT 'ok'
-------------------------------
SELECT a.*
FROM Person.Address a
WHERE a.StateProvinceID NOT IN (
SELECT sp.StateProvinceID
FROM Person.StateProvince sp
WHERE sp.CountryRegionCode='US'
)--在WHERE子句使用
--ANY、SOME操作符的使用
IF(5>SOME(SELECT AddressTypeID
FROM Person.AddressType)
)--因为5>1所以该表达式为真
PRINT 'ok'
-----------------------------
IF(7<=ANY(SELECT AddressTypeID
FROM Person.AddressType)
)--因为7>1,2,3,4,5,6所以该表达式为假
PRINT 'ok'
--ALL操作符的使用
IF(5>=ALL(SELECT AddressTypeID
FROM Person.AddressType)
)--因为5<6所以该表达式为假
PRINT 'ok'
--BETWEEN操作符的使用
---------以下是between的字符串比较-------
SELECT *
FROM Person.AddressType
WHERE [Name] NOT BETWEEN 'A' AND 'C'
---------以下是between的数字比较---------
IF(12 BETWEEN 1 AND 20)
PRINT 'between 1 and 20'
---------以下是between的日期比较---------
SELECT *
FROM HumanResources.Employee
WHERE BirthDate BETWEEN '1980-1-1' AND '2000-12-31'
--EXISTS操作符的使用
SELECT a.*
FROM Person.Address a
WHERE NOT EXISTS( --是否返回行
SELECT sp.StateProvinceID
FROM Person.StateProvince sp
WHERE sp.CountryRegionCode='US' AND
a.StateProvinceID=sp.StateProvinceID
)
--IS NULL操作符的使用
SELECT *
FROM HumanResources.EmployeeDepartmentHistory
WHERE EndDate IS NOT NULL
--LIKE操作符的使用
SELECT *
FROM HumanResources.Department
WHERE Name LIKE '%Pro%' --模糊匹配
