INSERT [ INTO]
{ table_name WITH ( < table_hint_limited > [ ...n ] )
| view_name
| rowset_function_limited
} { [ ( column_list ) ]
{ VALUES
( { DEFAULT | NULL | expression } [ ,...n] )
| derived_table
| execute_statement
}
}
| DEFAULT VALUES < table_hint_limited > ::=
{ FASTFIRSTROW
| HOLDLOCK
| PAGLOCK
| READCOMMITTED
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| TABLOCK
| TABLOCKX
| UPDLOCK
}
{ table_name WITH ( < table_hint_limited > [ ...n ] )
| view_name
| rowset_function_limited
} { [ ( column_list ) ]
{ VALUES
( { DEFAULT | NULL | expression } [ ,...n] )
| derived_table
| execute_statement
}
}
| DEFAULT VALUES < table_hint_limited > ::=
{ FASTFIRSTROW
| HOLDLOCK
| PAGLOCK
| READCOMMITTED
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| TABLOCK
| TABLOCKX
| UPDLOCK
}
{
table_name WITH ( < table_hint_limited > [ ...n ] )
| view_name
| rowset_function_limited
}
SET
{ column_name = { expression | DEFAULT | NULL }
| @variable = expression
| @variable = column = expression } [ ,...n ] { { [ FROM { < table_source > } [ ,...n ] ] [ WHERE
< search_condition > ] }
|
[ WHERE CURRENT OF
{ { [ GLOBAL ] cursor_name } | cursor_variable_name }
] }
[ OPTION ( < query_hint > [ ,...n ] ) ] < table_source > ::=
table_name [ [ AS ] table_alias ] [ WITH ( < table_hint > [ ,...n ] ) ]
| view_name [ [ AS ] table_alias ]
| rowset_function [ [ AS ] table_alias ]
| derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]
| < joined_table > < joined_table > ::=
< table_source > < join_type > < table_source > ON < search_condition >
| < table_source > CROSS JOIN < table_source >
| < joined_table > < join_type > ::=
[ INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } ]
[ < join_hint > ]
JOIN < table_hint_limited > ::=
{ FASTFIRSTROW
| HOLDLOCK
| PAGLOCK
| READCOMMITTED
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| TABLOCK
| TABLOCKX
| UPDLOCK
} < table_hint > ::=
{ INDEX ( index_val [ ,...n ] )
| FASTFIRSTROW
| HOLDLOCK
| NOLOCK
| PAGLOCK
| READCOMMITTED
| READPAST
| READUNCOMMITTED
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| TABLOCK
| TABLOCKX
| UPDLOCK
} < query_hint > ::=
{ { HASH | ORDER } GROUP
| { CONCAT | HASH | MERGE } UNION
| {LOOP | MERGE | HASH } JOIN
| FAST number_rows
| FORCE ORDER
| MAXDOP
| ROBUST PLAN
| KEEP PLAN
}
[ FROM ]
{ table_name WITH ( < table_hint_limited > [ ...n ] )
| view_name
| rowset_function_limited
} [ FROM { < table_source > } [ ,...n ] ] [ WHERE
{ < search_condition >
| { [ CURRENT OF
{ { [ GLOBAL ] cursor_name }
| cursor_variable_name
}
] }
}
]
[ OPTION ( < query_hint > [ ,...n ] ) ] < table_source > ::=
table_name [ [ AS ] table_alias ] [ WITH ( < table_hint > [ ,...n ] ) ]
| view_name [ [ AS ] table_alias ]
| rowset_function [ [ AS ] table_alias ]
| derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]
| < joined_table > < joined_table > ::=
< table_source > < join_type > < table_source > ON < search_condition >
| < table_source > CROSS JOIN < table_source >
| < joined_table > < join_type > ::=
[ INNER | { { LEFT | RIGHT | FULL } [OUTER] } ]
[ < join_hint > ]
JOIN < table_hint_limited > ::=
{ FASTFIRSTROW
| HOLDLOCK
| PAGLOCK
| READCOMMITTED
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| TABLOCK
| TABLOCKX
| UPDLOCK
} < table_hint > ::=
{ INDEX ( index_val [ ,...n ] )
| FASTFIRSTROW
| HOLDLOCK
| NOLOCK
| PAGLOCK
| READCOMMITTED
| READPAST
| READUNCOMMITTED
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| TABLOCK
| TABLOCKX
| UPDLOCK
} < query_hint > ::=
{ { HASH | ORDER } GROUP
| { CONCAT | HASH | MERGE } UNION
| FAST number_rows
| FORCE ORDER
| MAXDOP
| ROBUST PLAN
| KEEP PLAN
}
A. 使用 SELECT 检索行和列
下例显示三个代码示例。第一个代码示例从 pubs 数据库内的 authors 表中返回所有行（没有指定 WHERE 子句）和所有列（使用 *）。USE pubs
SELECT *
FROM authors
ORDER BY au_lname ASC, au_fname ASC-- Alternate way.
USE pubs
SELECT authors.*
FROM customers
ORDER BY au_lname ASC, au_fname ASC下例从 pubs 数据库内的 authors 表中返回所有行（没有指定 WHERE 子句）和列的一个子集（au_lname、au_fname、phone、city、state）。另外，还添加列标题。USE pubs
SELECT au_fname, au_lname, phone AS Telephone, city, state
FROM authors
ORDER BY au_lname ASC, au_fname ASC下例只返回居住在加利福尼亚州且不姓 McBadden 的作者列。USE pubs
SELECT au_fname, au_lname, phone AS Telephone
FROM authors
WHERE state = 'CA' and au_lname <> 'McBadden'
ORDER BY au_lname ASC, au_fname ASCB. 在列标题和列计算中使用 SELECT
这些示例返回 titles 内的所有行。第一个示例返回本年度截止到现在的销售总额以及应付给每个作者和出版商的金额。在第二个示例中，计算每本书的总收入。USE pubs
SELECT ytd_sales AS Sales,
authors.au_fname + ' '+ authors.au_lname AS Author,
ToAuthor = (ytd_sales * royalty) / 100,
ToPublisher = ytd_sales - (ytd_sales * royalty) / 100
FROM titles INNER JOIN titleauthor
ON titles.title_id = titleauthor.title_id INNER JOIN authors
ON titleauthor.au_id = authors.au_id
ORDER BY Sales DESC, Author ASC下面是结果集：Sales Author ToAuthor ToPublisher
----------- ------------------------- ----------- -----------
22246 Anne Ringer 5339 16907
22246 Michel DeFrance 5339 16907
18722 Marjorie Green 4493 14229
15096 Reginald Blotchet-Halls 2113 12983
8780 Cheryl Carson 1404 7376
4095 Abraham Bennet 409 3686
4095 Akiko Yokomoto 409 3686
4095 Ann Dull 409 3686
4095 Burt Gringlesby 409 3686
4095 Dean Straight 409 3686
4095 Marjorie Green 409 3686
4095 Michael O'Leary 409 3686
4095 Sheryl Hunter 409 3686
4072 Johnson White 407 3665
3876 Michael O'Leary 387 3489
3876 Stearns MacFeather 387 3489
3336 Charlene Locksley 333 3003
2045 Albert Ringer 245 1800
2045 Anne Ringer 245 1800
2032 Innes del Castillo 243 1789
375 Livia Karsen 37 338
375 Stearns MacFeather 37 338
375 Sylvia Panteley 37 338
111 Albert Ringer 11 100
NULL Charlene Locksley NULL NULL(25 row(s) affected)下面是用于计算每本书的总收入的查询：USE pubs
SELECT 'Total income is', price * ytd_sales AS Revenue,
'for', title_id AS Book#
FROM titles
ORDER BY Book# ASC下面是结果集：Revenue Book#
--------------- --------------------- ---- ------
Total income is 81859.0500 for BU1032
Total income is 46318.2000 for BU1111
Total income is 55978.7800 for BU2075
Total income is 81859.0500 for BU7832
Total income is 40619.6800 for MC2222
Total income is 66515.5400 for MC3021
Total income is NULL for MC3026
Total income is 201501.0000 for PC1035
Total income is 81900.0000 for PC8888
Total income is NULL for PC9999
Total income is 8096.2500 for PS1372
Total income is 22392.7500 for PS2091
Total income is 777.0000 for PS2106
Total income is 81399.2800 for PS3333
Total income is 26654.6400 for PS7777
Total income is 7856.2500 for TC3218
Total income is 180397.2000 for TC4203
Total income is 61384.0500 for TC7777(18 row(s) affected)C. 将 DISTINCT 与 SELECT 一起使用
下例使用 DISTINCT 防止检索重复的作者 ID 号：USE pubs
SELECT DISTINCT au_id
FROM authors
ORDER BY au_idD. 使用 SELECT INTO 创建表
第一个示例在tempdb 中创建一个名为 #coffeetabletitles 的临时表。为使用该表，始终用下面显示的精确名称（包括井号 (#)）引用它。 USE pubs
DROP TABLE #coffeetabletitles
GO
SET NOCOUNT ON
SELECT * INTO #coffeetabletitles
FROM titles
WHERE price < $20
SET NOCOUNT OFF
SELECT name
FROM tempdb..sysobjects
WHERE name LIKE '#c%'下面是结果集：name
------------------------------------------------------------------------
#coffeetabletitles__________________________________________________________________________________________________000000000028(1 row(s) affected)CHECKPOINTing database that was changed.(12 row(s) affected)name
------------------------------------------------------------------------
newtitles(1 row(s) affected)CHECKPOINTing database that was changed.第二个示例创建一个名为 newtitles 的永久表。USE pubs
IF EXISTS (SELECT table_name FROM INFORMATION_SCHEMA.TABLES
WHERE table_name = 'newtitles')
DROP TABLE newtitles
GO
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'
USE pubs
SELECT * INTO newtitles
FROM titles
WHERE price > $25 OR price < $20
SELECT name FROM sysobjects WHERE name LIKE 'new%'
USE master
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'false'下面是结果集：name
------------------------------
newtitles (1 row(s) affected)E. 使用相关子查询
下例显示在语义上相当的查询并说明使用 EXISTS 关键字和 IN 关键字的区别。下面是两个示例，显示一个有效子查询检索书名为商业书籍的每个出版商名称，还检索 titles 表和 publishers 表之间相匹配的出版商 ID 号。USE pubs
SELECT DISTINCT pub_name
FROM publishers
WHERE EXISTS
(SELECT *
FROM titles
WHERE pub_id = publishers.pub_id
AND type = 'business')-- Or
USE pubs
SELECT distinct pub_name
FROM publishers
WHERE pub_id IN
(SELECT pub_id
FROM titles
WHERE type = 'business')下例在一个相关（或重复）子查询中使用 IN，该查询的值取决于外部查询。它被重复执行，为外部查询可能选择的每行各执行一次。该查询在 titleauthor 表中检索每个版税为 100% 且作者标识号在 titleauthor 表和 authors 中相匹配的作者的名和姓。USE pubs
SELECT DISTINCT au_lname, au_fname
FROM authors
WHERE 100 IN
(SELECT royaltyper
FROM titleauthor
WHERE titleauthor.au_id = authors.au_id)不能独立于外部查询对上述语句中的子查询取值。它需要一个 authors.au_id 值，但是该值随 Microsoft® SQL Server™ 检查 authors 中的不同行而改变。相关子查询还可以用于外部查询的 HAVING 子句。下例查找那些预付款最大金额是组平均值两倍以上的书籍类型。USE pubs
SELECT t1.type
FROM titles t1
GROUP BY t1.type
HAVING MAX(t1.advance) >= ALL
(SELECT 2 * AVG(t2.advance)
FROM titles t2
WHERE t1.type = t2.type)