/*

    Microsoft SQL Server 2008 T-SQL Fundamentals_CN（Chapter 8 Inserting Data）

*/

----------------------------------------------------------------------------------------------------------------------

----先不用曾经测试过的表做实验，还需要实验+看数据+回顾，不小心已经破坏了Orders表

--SELECT *

--FROM Orders

----------------------------------------------------------------------------------------------------------------------

--先复制：Northwind.Customers,    Orders，2个表(表结构+数据)到testdb库下

if OBJECT_ID('dbo.Customers','U') IS NOT NULL DROP TABLE dbo.Customers;

if OBJECT_ID('dbo.Orders','U') IS NOT NULL DROP TABLE dbo.Orders;

SELECT * INTO testdb.dbo.Customers FROM Northwind.dbo.Customers

SELECT * INTO testdb.dbo.Orders FROM Northwind.dbo.Orders

ALTER TABLE dbo.Customers ADD

    CONSTRAINT PK_Customers PRIMARY KEY(CustomerID)

ALTER TABLE dbo.Orders ADD

   CONSTRAINT PK_Orders PRIMARY KEY(OrderID),

   CONSTRAINT PK_Orders_Customers FOREIGN KEY(CustomerID)

         REFERENCES dbo.Customers(CustomerID)

-------------------------------------------------------------------------------

--检查，表结构+数据，都复制到 testdb 库下了

SELECT * FROM Customers

SELECT * FROM Orders

--

--SELECT * FROM Northwind.dbo.Customers

SELECT * FROM Northwind.dbo.Orders

        left join Northwind.dbo.Customers

        on Orders.CustomerID=Customers.CustomerID

WHERE ShipCountry = 'UK'                             -- 有 56个，为下面删除 连接...的参考

--------------

SELECT * FROM Northwind.dbo.Orders

        left join Northwind.dbo.Customers

        on Orders.CustomerID=Customers.CustomerID

WHERE ShipCountry = 'USA'                            -- 有 122个，为下面删除 连接...的参考

--------------

SELECT * FROM Northwind.dbo.Orders

        left join Northwind.dbo.Customers

        on Orders.CustomerID=Customers.CustomerID

WHERE ShipCountry = 'France'                         -- 有 77个，为下面删除 连接...的参考

-------------------------------------------------------------------------------

--   DELETE（没有过滤条件时，是删除所有行）

SET NOCOUNT OFF          --NOCOUNT 为 ON 时，只会报告命令完成，不会报告数量

DELETE

FROM dbo.Orders

WHERE orderdate < ''

--TRUNCATE 没有过滤条件，删除所有行

--区别：  TRUNCATE以最小模式记录日志(效率高)，DELETE以完整模式记录日志

--创建一个 dummy table（让它包含一个指向产品表的外键，这样就可保护产品表了，此处还可多理解一下

TRUNCATE TABLE Orders

-------------------------------------------------------------------------------

-- 基于连接的 DELETE 不是标注的SQL语句（知道有这么一种写法就可），平时尽量采用标准的SQL语句

DELETE FROM O

FROM dbo.Orders as O

    JOIN dbo.Customers AS C

    ON O.CustomerID = C.CustomerID

WHERE C.Country = 'UK'

--删除完满足条件的纪录后，再查，确证删除

--SELECT * FROM Customers

SELECT * FROM Orders

-----------------------------

--下面是标准的SQL语句

DELETE FROM dbo.Orders

WHERE EXISTS

(SELECT *

 FROM dbo.Customers AS C

 WHERE Orders.CustomerID = C.CustomerID

      AND Orders.ShipCountry ='USA'

)

--------

--以连接形式的写法，然后再删除

DELETE FROM dbo.Orders

WHERE EXISTS

(SELECT *

 FROM dbo.Customers AS C

 LEFT JOIN Orders AS O

 ON O.CustomerID = C.CustomerID

  WHERE  Orders.ShipCountry ='France'

)