--
--2.2.1节示例
--
SELECT StuName
FROM StudentScore
WHERE Score BETWEEN 60 AND 90 --WHERE条件查询
--典型数据查询
SELECT cr.Name AS CountryRegion,sp.Name StateProvinceName,a.*
FROM Person.Address a
INNER JOIN Person.StateProvince sp --内联接
ON sp.StateProvinceID = a.StateProvinceID
INNER JOIN Person.CountryRegion cr
ON cr.CountryRegionCode = sp.CountryRegionCode
WHERE a.City='Bothell'
--
--2.2.2节示例
--
--使用INSERT插入数据
USE AdventureWorks
GO
INSERT INTO Person.ContactType(Name,ModifiedDate)
VALUES('Test1','2008-1-1')
GO
INSERT INTO Person.ContactType --不带列名的插入
VALUES('Test2','2008-1-1')
GO
INSERT INTO Person.ContactType(ModifiedDate,Name) --重新排列了列名的插入
VALUES('2008-1-1','Test3')
--INSERT和SELECT结合
INSERT INTO Person.ContactType
(
Name,
ModifiedDate
)
SELECT at.Name,at.ModifiedDate
FROM Person.AddressType at
--将查询出的结果插入到表中
--带TOP的INSERT语句
INSERT TOP(3) INTO Person.ContactType --插入查询出的前3行结果
(
Name,
ModifiedDate
)
SELECT a.AddressLine1,a.ModifiedDate
FROM Person.Address a
--或者
INSERT INTO Person.ContactType
(
Name,
ModifiedDate
)
SELECT TOP 3 a.AddressLine1,a.ModifiedDate
FROM Person.Address a
--
--2.2.3节示例
--
--UPDATE更新语句
UPDATE Person.ContactType
SET Name='--'+Name
WHERE ContactTypeID>20 --只更新前面插入的数据，原数据不变
--使用包含FROM子句的UPDATE语句
INSERT TOP(3) INTO Person.ContactType --插入3行数据
(
Name,
ModifiedDate
)
SELECT at.Name,at.ModifiedDate
FROM Person.AddressType at
GO
UPDATE Person.ContactType --将AddressType的rowguid更新到ContactType的Name中
SET Name = at.rowguid
FROM Person.ContactType ct
INNER JOIN Person.AddressType at
ON ct.Name=at.Name
--使用带TOP的UPDATE语句
UPDATE TOP(2) Person.ContactType --只修改2行数据
SET
Name = ContactTypeID
WHERE ContactTypeID>20
--
--2.2.4节示例
--
--在DELETE中使用FROM子句
DELETE FROM Person.ContactType
FROM Person.ContactType ct
INNER JOIN Person.AddressType at --通过内联接后再删除
ON ct.Name=at.Name
--使用带TOP的DELETE语句
DELETE TOP(50) --只删除匹配的头50条数据
FROM Person.ContactType
WHERE ContactTypeID>20
