DML_Data Modification_DELETE

DML_Data Modification_Delete
删除记录、表结构之类的比较简单,但是需要特别注意,一不小心,就变成了 “从删库到跑路“ 就掉的大了
最好还是将它注释掉:    --     /*  ...  */     ,避免一溜烟的执行就挂了


/*
    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 < '19970801'

--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'
)


--对于非标准的删除写法,也好理解(如右边),   先查询--->后条件--->最后删除

 

 

 

 

 

 

 



运行结果:

      略

/*
    Orders表在PIVOT、UNPIVOT中已经做了示例,下面表为OrdersA为区别
    Microsoft SQL Server 2008 T-SQL Fundamentals_CN(Chapter 8 Inserting Data)
*/
posted @ 2020-06-09 09:35  CDPJ  阅读(181)  评论(0编辑  收藏  举报