Operator
|
Description
|
!=
|
Tests two expressions not being equal to each other.
|
!>
|
Tests that the left condition is not greater than the expression to the right.
|
!<
|
Tests that the right condition is not greater than the expression to the right.
|
<
|
Tests the left condition as less than the right condition.
|
<=
|
Tests the left condition as less than or equal to the right condition.
|
<>
|
Tests two expressions not being equal to each other.
|
=
|
Tests equality between two expressions.
|
>
|
Tests the left condition being greater than the expression to the right.
|
>=
|
Tests the left condition being greater than or equal to the expression to the right.
|
ALL
|
When used with a comparison operator and subquery, if all retrieved values
satisfy the search condition, the rows will be retrieved.
|
ANY
|
When used with a comparison operator and subquery, if any retrieved values satisfy the search condition, the rows will be retrieved.
|
BETWEEN
|
Designates an inclusive range of values. Used with the AND clause between the beginning and ending values.
|
CONTAINS
|
Does a fuzzy search for words and phrases.
|
ESCAPE
|
Takes the character used prior to a wildcard character to designate that the literal value of the wildcard character should be searched, rather than use the character as a wildcard.
|
EXISTS
|
When used with a subquery, EXISTS tests for the existence of rows in the subquery.
|
FREETEXT
|
Searches character-based data for words using meaning, rather than literal values.
|
IN
|
Provides an inclusive list of values for the search condition.
|
IS NOT NULL
|
Evaluates if the value is NOT null.
|
IS NULL
|
Evaluates whether the value is null.
|
LIKE
|
Tests character string for pattern matching
|
NOT BETWEEN
|
Specifies a range of values NOT to include. Used with the AND clause between the beginning and ending values.
|
NOT IN
|
Provides a list of values for which NOT to return rows for.
|
NOT LIKE
|
Tests character string, excluding those with pattern matches.
|
SOME
|
When used with a comparison operator and subquery, if any retrieved values satisfy the search condition, the rows will be retrieved.
|
-- Using BETWEEN for Date Range Searches
SELECT SalesOrderID, ShipDate
FROM Sales.SalesOrderHeader
WHERE ShipDate BETWEEN '7/28/2002' AND '7/29/2002'
--Using Comparisons
SELECT ProductID,Name,StandardCost
FROM Production.Product
WHERE StandardCost < 110.0000
--Checking for NULL Values
SELECT ProductID,Name,Weight
FROM Production.Product
WHERE Weight IS NULL
--Returning Rows Based on a List of Values
SELECT ProductID,Name,Color
FROM Production.Product
WHERE Color IN ('Silver', 'Black', 'Red')
-- Wildcard Usage
-- % Represents a string of zero or more characters.
-- _ Represents a single character.
-- [] Specifies a single character, from a selected range or list.
-- [^] Specifies a single character not within the specified range.
SELECT ProductID,Name
FROM Production.Product
WHERE Name LIKE 'B%'
SELECT ProductID, Name
FROM Production.Product
WHERE Name LIKE '%/_%' ESCAPE '/'
--Using the ORDER BY Clause
SELECT p.Name,h.EndDate,h.ListPrice
FROM Production.Product p
INNER JOIN
Production.ProductListPriceHistory h
ON
p.ProductID = h.ProductID
ORDER BY p.Name, h.EndDate
SELECT p.Name,h.EndDate,h.ListPrice
FROM Production.Product p
INNER JOIN
Production.ProductListPriceHistory h
ON
p.ProductID = h.ProductID
ORDER BY p.Name DESC, h.EndDate DESC
--Using the TOP Keyword with Ordered Results
SELECT TOP 10 v.Name, v.CreditRating
FROM Purchasing.Vendor v
ORDER BY v.CreditRating DESC, v.Name
--Using the GROUP BY Clause
-- This error is raised because any column that is not used in an aggregate function in the SELECT
-- list must be listed in the GROUP BY clause.
SELECT OrderDate, SUM(TotalDue) TotalDueByOrderDate
FROM Sales.SalesOrderHeader
WHERE OrderDate BETWEEN '7/1/2001' AND '7/31/2001'
GROUP BY OrderDate
--Using GROUP BY ALL
-- In the results returned by the GROUP BY ALL example, notice that
-- TotalDueByOrderDate was NULL for those order dates not included
-- in the WHERE clause. This does not mean they have zero rows, but
-- instead, that data is not displayed for them.
SELECT OrderDate,
SUM(TotalDue) TotalDueByOrderDate
FROM Sales.SalesOrderHeader
WHERE OrderDate BETWEEN '7/1/2001' AND '7/31/2001'
GROUP BY ALL OrderDate
--Selectively Querying Grouped Data Using HAVING
-- The HAVING clause is used after the GROUP BY clause.
-- The WHERE clause, in contrast, is used to qualify
-- the rows that are returned before the data is aggregated
-- or grouped. HAVING qualifies the aggregated
-- data after the data has been grouped or aggregated.
SELECT s.Name,
COUNT(w.WorkOrderID) Cnt
FROM Production.ScrapReason s
INNER JOIN
Production.WorkOrder w
ON
s.ScrapReasonID = w.ScrapReasonID
GROUP BY s.Name
HAVING COUNT(*)>50
--Using DISTINCT to Remove Duplicate Values
SELECT DISTINCT HireDate
FROM HumanResources.Employee
--Using DISTINCT in Aggregate Functions
SELECT AVG(DISTINCT ListPrice)
FROM Production.Product
--Using Column Aliases
SELECT Color AS 'Grouped Color',
AVG(DISTINCT ListPrice) AS 'Average Distinct List Price',
AVG(ListPrice) 'Average List Price'
FROM Production.Product
GROUP BY Color
--Performing String Concatenation
SELECT 'The ' +
p.Name +
' is only ' +
CONVERT(varchar(25),p.ListPrice) +'!'
FROM Production.Product p
WHERE p.ListPrice between 100 AND 120
ORDER BY p.ListPrice
--Creating a Comma Delimited List Using SELECT
DECLARE @Shifts varchar(20)
SET @Shifts = ''
SELECT @Shifts = @Shifts + s.Name + ','
FROM HumanResources.Shift s
ORDER BY s.EndTime
SELECT @Shifts
--Using the INTO Clause
SELECT CustomerID,
Name,
SalesPersonID,
Demographics
INTO Store_Archive
FROM Sales.Store
--Using Subqueries to Check for the Existence of Matches
SELECT DISTINCT s.PurchaseOrderNumber
FROM Sales.SalesOrderHeader s
WHERE EXISTS ( SELECT SalesOrderID
FROM Sales.SalesOrderDetail
WHERE UnitPrice BETWEEN 1000 AND 2000 AND
SalesOrderID = s.SalesOrderID)
SELECT SalesPersonID,
SalesQuota CurrentSalesQuota
FROM Sales.SalesPerson
WHERE SalesQuota
IN
(SELECT MAX(SalesQuota)
FROM Sales.SalesPerson)
--Using INNER Joins
SELECT p.Name,
s.DiscountPct
FROM Sales.SpecialOffer s
INNER JOIN Sales.SpecialOfferProduct o
ON s.SpecialOfferID = o.SpecialOfferID
INNER JOIN Production.Product p
ON o.ProductID = p.ProductID
WHERE p.Name = 'All-Purpose Bike Stand'
--Using OUTER Joins
SELECT s.CountryRegionCode,
s.StateProvinceCode,
t.TaxType,
t.TaxRate
FROM Person.StateProvince s
INNER JOIN Sales.SalesTaxRate t
ON s.StateProvinceID = t.StateProvinceID
SELECT s.CountryRegionCode,
s.StateProvinceCode,
t.TaxType,
t.TaxRate
FROM Person.StateProvince s
LEFT OUTER JOIN Sales.SalesTaxRate t
ON s.StateProvinceID = t.StateProvinceID
--Using CROSS Joins: S.Num * T.Num
SELECT s.CountryRegionCode,
s.StateProvinceCode,
t.TaxType,
t.TaxRate
FROM Person.StateProvince s
CROSS JOIN Sales.SalesTaxRate t
--Performing Self-Joins
SELECT e.EmployeeID,
e.Title,
m.Title AS ManagerTitle
FROM HumanResources.Employee e
LEFT OUTER JOIN HumanResources.Employee m
ON e.ManagerID = m.EmployeeID
--Using Derived Tables
SELECT DISTINCT s.PurchaseOrderNumber
FROM Sales.SalesOrderHeader s
INNER JOIN (SELECT SalesOrderID
FROM Sales.SalesOrderDetail
WHERE UnitPrice BETWEEN 1000 AND 2000) d
ON
s.SalesOrderID = d.SalesOrderID
--Combining Result Sets with UNION
-- The UNION operator is used to append the results of two or more SELECT statements into a single
-- result set. Each SELECT statement being merged must have the same number of columns, with the
-- same or compatible data types in the same order, as this example demonstrates:
SELECT SalesPersonID, GETDATE() QuotaDate, SalesQuota
FROM Sales.SalesPerson
WHERE SalesQuota > 0
UNION
SELECT SalesPersonID, QuotaDate, SalesQuota
FROM Sales.SalesPersonQuotaHistory
WHERE SalesQuota > 0
ORDER BY SalesPersonID DESC, QuotaDate DESC
分享到:
相关推荐
介绍了各种SQL语句与使用实例 Transact-SQL语句 语法大全2008-01-17 10:33语句 ----功能 --数据操作 SELECT --从数据库表中检索数据行和列 INSERT --向数据库表添加新数据行 DELETE --从数据库表中删除...
一些基本的T-SQL语句,经过本人的整理,希望对初学者有所帮助。
《Microsoft SQL Server 2008技术内幕:T-SQL语言基础》是Microsoft SQL Server 2008系列中的一本。书中全面深入地介绍了T-SQL的基本元素,以及SQL Server 2008中新增加的一些特性。主要包括SQL的基础理论、逻辑...
8、SQL 2008视频教程-T-SQL语句3 ? 9、SQL 2008视频教程-T-SQL语句4 ? 10、T-SQL语句5 ? 11、SQL 2008视频教程-T-SQL语句6 ? 12、SQL 2008视频教程-T-SQL语句7 ? 13、SQL 2008视频教程-T-SQL...
《Microsoft SQL Server 2008技术内幕:T-SQL语言基础》是Microsoft SQL Server 2008系列中的一本。书中全面深入地介绍了T-SQL的基本元素,以及SQL Server 2008中新增加的一些特性。主要包括SQL的基础理论、逻辑...
作为一本讲述T-SQL高级查询的专业图书,《Microsoft SQL Server 2008技术内幕:T-SQL查询》旨在结合实践中的各种常见问题,教读者如何用SQL作为语言工具来思考问题,揭示基于集合查询的强大威力。《Microsoft SQL ...
自己总结的一些关于SQL语句中Select语句常用的几种方法。里面有相信的说明,下载后查阅!
ABAP中SQL语法详细介绍,包括基础语法和各种聚合函数,如MAX,MIN,AVG,COUNT,SUM,DIV,MOD,CEIL,FLOOR,DIVSION,ROUND,FOR ALL ENTRIES IN.除了各种聚合函数还有字段拆分,连接,子语句查询,字段类型转换,分情况赋值...
T-SQL语法 一、数据库存储结构 二、数据库表存储结构 三、约 束 四、规 则 五、默认 六、索 引 七、视图 八、触发器 九、select,delete,update,insert语句使用方法 十、函数数据整理 十一、游标 十二、存储过程
这是本人精心收集的17套SQL试题,其中有对应答案!希望对您有用!
sql之select语句,sql之select语句,sql之select语句,sql之select语句
T-SQL建库建表的同时加约束,调用DOS cmdshell命令建文件夹, if exists(select * from sysobjects where name ='Result') drop table Result create table Result ( ExamDate datetime default getdatet() not null,...
作为一本讲述T-SQL高级查询的专业图书,《Microsoft SQL Server 2008技术内幕:T-SQL查询》旨在结合实践中的各种常见问题,教读者如何用SQL作为语言工具来思考问题,揭示基于集合查询的强大威力。《Microsoft SQL ...
作为一本讲述T-SQL高级查询的专业图书,《Microsoft SQL Server 2008技术内幕:T-SQL查询》旨在结合实践中的各种常见问题,教读者如何用SQL作为语言工具来思考问题,揭示基于集合查询的强大威力。《Microsoft SQL ...
PREPARE --为动态执行准备SQL 语句 EXECUTE --动态地执行SQL 语句 DESCRIBE --描述准备好的查询 ---局部变量 declare @id char(10) --set @id = '10010001' select @id = '10010001' ---全局变量 ...
MS-SQL 50条常用select语句(以学生表为例)
SQL语句 SELECT LIKE like用法详解SQL语句 SELECT LIKE like用法详解SQL语句 SELECT LIKE like用法详解
子查询是一个嵌套在select、insert、update或delete语句或其他子查询中的查询。任何允许使用表达式的地方都可以使用子查询。子查询也称为内部查询或内部选择,而包含子查询的语句也成为外部查询或外部选择。 # ...
(上述代码中的多行 SELECT 语句是一个 SQL 语句。)在同一语句中开始新行时,使 SQL 关键字右对齐。将代码编辑器配置为使用空格,而不是使用制表符[每个制表符对应为4个空格,请在SQL编辑器中进行设置。]。这样,...
动态sql语句基本语法 1 :普通SQL语句可以用Exec执行 例: Select * from tableName Exec('select * from tableName') Exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N 2:字段名,...