【從Northwind學習資料庫】資料更新

前言

資料更新大概就以下三個函式

具體用法參考下列例題的解題中

以下例題都在Northwind(北風)資料庫的基礎上進行操作,該資料庫資源很多，請自行下載

例題

1.最近新公司新進一名銷售代表（Sales Representative）“Susan Huak”女士，請你為她登記相關資訊

“Andrew”是她的上司，她需要向“Andrew”報告她的銷售業績，

分析題意

公司新來一名員工，那么肯定在員工表進行操作，在一些未知的地方需要進行查詢操作

解題如下

--首先在左側的物件資源管理器中找到要進行操作的表格
--右鍵撰寫表腳本為INSERT到剪貼板，然后粘貼到創建的查詢中
--INSERT INTO 后面的是你插入資訊的表格對應的列，此處可以刪去不插入資訊的列，會自動生成 NULL
--注意，此處沒有對應的主碼資訊，因為單主碼的表格無法人為干預主碼
INSERT INTO [Northwind].[dbo].[Employees]
           ([LastName]
           ,[FirstName]
           ,[Title]
           ,[TitleOfCourtesy]
           ,[BirthDate]
           ,[HireDate]
           ,[Address]
           ,[City]
           ,[Region]
           ,[PostalCode]
           ,[Country]
           ,[HomePhone]
           ,[Extension]
           ,[Photo]
           ,[Notes]
           ,[ReportsTo]
           ,[PhotoPath])
-- VALUES后面是你插入上述列的對應資訊，若有某個資訊當前為空可直接輸入NULL（注意NULL不要有引號）           
     VALUES
           ('Hauk'
           ,'Susan'
		   ,'Sales Representative'
           ,'Mrs.'
           --時間要有引號，不然會進行減法運算，別問我怎么知道的
		   ,'1979-1-8'
		   ,'2013-7-1'
		   ,'3356-9th Ave.S.E'
		   ,'Seattle'
		   --根據之前我們對表格的了解，此處的Region應該與前面的城市相對應，所以進行了以下的查詢
           ,
           (SELECT DISTINCT Region
           FROM Employees
           WHERE City = 'Seattle')
           ,NULL
           ,'USA'
           ,'(206)322-4435'
           ,NULL
           ,NULL
           ,NULL
           --此處的Reports to是指向誰報告，對應的是上司的EmployeeID
           ,
           (SELECT EmployeeID 
			FROM Employees
			WHERE FirstName = 'Andrew'
			OR LastName = 'Andrew')
           ,NULL)
GO

2.下面這筆訂單是由“Andrew”拿到的，要求一個月內完成訂單，并由“United Package”承運，將這條資訊輸入到資料庫

分析題意

首先前兩行的資料在Orders表中，而后兩行的資料卻在Products表中，如何同時插入兩個表格中的資料并保證自動生成的ID能填充到關聯表格中呢？此處就要用到一個全域變數了，那就是 @@INDENTITY

@@IDENTITY：回傳上次插入表格的ID值

解題如下

--解題到一半時你會發現，后面一堆ship開頭的資訊都是老客戶上次的資訊
--如果每個都查詢一遍，那么效率實在太低太低
--因此我們定義一些全域變數，讓那些資訊一次性查詢出來
--nvarchar()是變長的Unicode編碼的字串，多出來的空間會刪去
DECLARE @ShipName nvarchar(50)
DECLARE @ShipAddress nvarchar(50)
DECLARE @ShipCity nvarchar(50)
DECLARE @ShipCountry nvarchar(50)
DECLARE @ShipRegion nvarchar(50)
DECLARE @ShipPostalCode nvarchar(50)
--TOP 1是為了取最近一次的訂單資訊
SELECT TOP 1 
@ShipName = ShipName,
@ShipAddress = ShipAddress,
@ShipCity = ShipCity,
@ShipRegion = ShipRegion,
@ShipPostalCode = ShipPostalCode,
@ShipCountry = ShipCountry
FROM Orders 
WHERE CustomerID IN
(SELECT CustomerID
FROM Customers
WHERE CompanyName = 'Centro comercial Moctezuma')
ORDER BY OrderDate DESC
--插入第一張表格的資訊，相關查詢的注釋略去，請自行理解
INSERT INTO [Northwind].[dbo].[Orders]
           ([CustomerID]
           ,[EmployeeID]
           ,[OrderDate]
           ,[RequiredDate]
           ,[ShippedDate]
           ,[ShipVia]
           ,[Freight]
           ,[ShipName]
           ,[ShipAddress]
           ,[ShipCity]
           ,[ShipRegion]
           ,[ShipPostalCode]
           ,[ShipCountry])
     VALUES
           (
           (SELECT CustomerID
           FROM Customers
           WHERE CompanyName = 	
           'Centro comercial Moctezuma')
           ,
           (SELECT EmployeeID
           FROM Employees
           WHERE LastName = 'Andrew' OR FirstName = 'Andrew')
           ,'2013-6-28'
           ,DATEADD(MONTH,1,'2013-6-28')
           ,NULL
           ,
           (SELECT ShipperID
           FROM Shippers
           WHERE CompanyName = 'United Package')
           ,80
           ,@ShipName
           ,@ShipAddress
           ,@ShipCity
           ,@ShipRegion
           ,@ShipPostalCode
           ,@ShipCountry)
           
GO
DECLARE @OrderID nvarchar(50)
SET @OrderID=@@IDENTITY
INSERT INTO [Northwind].[dbo].[Order Details]
           ([OrderID]
           ,[ProductID]
           ,[UnitPrice]
           ,[Quantity]
           ,[Discount])
     VALUES
           (@OrderID
           ,
           (SELECT ProductID
           FROM Products
           WHERE ProductName = 'Gravad lax')
           ,25.80
           ,50
           ,0.15)
           ,@OrderID
           ,
           (SELECT ProductID
           FROM Products
           WHERE ProductName = 'Chocolade')
           ,13.00
           ,300
           ,0.20)
GO

3.由于南部地區（Region）的銷售人員人手不夠，將負責東部地區業績最好的員工派往南區負責，并且將新來的“Susan Huak”也派往南部地區負責

分析題意

首先我們需要查詢出東部地區業績最好的員工，接著先洗掉業績最好的員工負責的地區，再插入讓業績最好的員工負責南部地區，同時讓新員工也來負責南部地區，至于為什么先洗掉后插入而不是通過資料更新來處理，那就應該從地區這張表來說起了，負責人負責某個地區指的是負責整個地區的所有區域，而不是其中的某個區域，這里的東部地區和南部地區可能區域數會不同，所以先刪再插入，

解題如下

--同樣的，我們需要先查詢出東部業績最好的員工ID放到后面用，所以先定義全域變數
DECLARE @EmployeeID nvarchar(50)
SELECT TOP 1 @EmployeeID=E.EmployeeID
FROM Employees E
JOIN Orders O ON E.EmployeeID = O.EmployeeID
JOIN [Order Details] OD ON O.OrderID = OD.OrderID
WHERE City IN 
		(SELECT TerritoryDescription FROM Territories
		WHERE RegionID = 
		(SELECT RegionID FROM Region
		WHERE RegionDescription = 'Western'))
--此處我們需要讓查詢出來的那個人不是新員工（雖然這并不實際，但上司轉給她的實在是太多了）
AND FirstName != 'Susan'
GROUP BY E.EmployeeID
ORDER BY SUM(UnitPrice*Quantity*(1-Discount))DESC
--洗掉業績最好的員工的負責區域資訊
DELETE
FROM EmployeeTerritories
WHERE EmployeeID = @EmployeeID
--插入業績最好的員工的負責區域資訊
--這里我細說一下，員工負責區域這張表為雙主碼的表，所以當兩個主碼都相同時才能確定一條資訊
--也就是說，這個主碼是可以更改的，只不過改后不能兩行的兩個主碼都相同
INSERT INTO [Northwind].[dbo].[EmployeeTerritories]
           ([EmployeeID]
           ,[TerritoryID])
     --這里是批量插入，需要原表和插入表的資訊格式相同
     --下面我使用查詢來把所需資訊變成一張新的表格，并且查詢出我想要的資訊
     SELECT E,TerritoryID
     FROM (SELECT @EmployeeID AS E,TerritoryID
           FROM Territories
           WHERE RegionID IN
           (SELECT RegionID
           FROM Region
           WHERE RegionDescription = 'Southern'))AS A
GO
--以下為插入新員工的負責區域資訊，操作基本同上
DECLARE @EmployeeID nvarchar(50)
SELECT @EmployeeID = EmployeeID FROM Employees
WHERE FirstName = 'Susan'

INSERT INTO [Northwind].[dbo].[EmployeeTerritories]
           ([EmployeeID]
           ,[TerritoryID])
		   SELECT EI,TerritoryID
		   FROM (SELECT @EmployeeID EI,TerritoryID
		   		FROM Territories
		   		WHERE RegionID IN
		   		(SELECT RegionID
		   		FROM Region
		   		WHERE RegionDescription = 'Southern'))AS B
		
GO

結尾

一家之言
如有疏漏，還請指出，希望一同進步