DML_Data Modification_UPDATE

DML_Data Modification_UPDATE
写不进去，不能专注了......

/*

*/
----------------------------------------------------------------------------------------------------------------------
SELECT *
FROM Northwind.dbo.Orders

SELECT *
FROM Northwind.dbo.[Order Details]
----------------------------------------------------------------------------------------------------------------------
--先复制：Northwind.[Order Details],    Orders，2个表(表结构+数据)到testdb库下
--测试数据已经准备好
if OBJECT_ID('dbo.[Order Details]','U') IS NOT NULL DROP TABLE dbo.[Order Details];      --最讨厌这种表名中间还有个空格，还要用方括号括起来
if OBJECT_ID('dbo.Orders','U') IS NOT NULL DROP TABLE dbo.Orders;

SELECT * INTO testdb.dbo.[Order Details] FROM Northwind.dbo.[Order Details]
SELECT * INTO testdb.dbo.Orders FROM Northwind.dbo.Orders

ALTER TABLE dbo.Orders ADD
    CONSTRAINT PK_Orders PRIMARY KEY(OrderID)

ALTER TABLE dbo.[Order Details] ADD
   CONSTRAINT PK_OrderDetails PRIMARY KEY(OrderID,ProductID),
   CONSTRAINT PK_OrderDetails_Orders FOREIGN KEY(OrderID)
         REFERENCES dbo.Orders(OrderID)                                 
-------------------------------------------------------------------------------
--UPDATE

--更改前查询一下
SELECT *
FROM testdb.dbo.[Order Details]
WHERE ProductID = '51' 


--基础普通的更新
UPDATE dbo.[Order Details]
   SET Discount = Discount + 0.05
WHERE ProductID = '51' 

SELECT *
FROM testdb.dbo.[Order Details]
WHERE ProductID = '51' AND OrderID = '10250'    --更改前Discount = 0.15
---------------------------------
--使用复合赋值运算符
UPDATE dbo.[Order Details]
   SET Discount += 0.05
WHERE ProductID = '51' 
---------------------------------
--“同时操作”，就是同一时刻更新，不看还好，一看还模糊了(本来就是如此么)，又不是i++，i--
SELECT *
FROM testdb.dbo.[Order Details]

UPDATE dbo.[Order Details]
SET Quantity = Quantity -10,Discount = Quantity-0.01

SELECT *
FROM testdb.dbo.[Order Details]

----------------------------------------------------------
select * from T1
SELECT * INTO T3 FROM T1 WHERE 1=1
select * from T3

ALTER TABLE T3 ADD    --drop table T3      --这样新增加列后的字段值全为 Null，增加 WITH VALUES值就带进去了
                                col1 smallint default 0 WITH VALUES,
                                col2 smallint default 0 WITH VALUES,
                                col3 tinyint default 0 WITH VALUES 
                                --col4 single default 0,
                                --col5 double default 0
select * from T3

UPDATE dbo.T3                               --更新数据也有提示，但值还是为 Null   ？？？
   SET col1 = col1+10, col2 = col1+10, col3=col1+10

select * from T3
----------------------------------------------------------
--基于联接的UPDATE
SELECT *
FROM [Order Details]

SELECT *
FROM Orders

UPDATE OD
   SET discount = discount + 0.03
FROM dbo.[Order Details] AS OD
    JOIN dbo.Orders AS O
    ON OD.OrderID = O.OrderID
WHERE CustomerID = 'TOMSP';

--查询基于联接后的更改结果
SELECT *
FROM dbo.[Order Details] AS OD
    JOIN dbo.Orders AS O
    ON OD.OrderID = O.OrderID
WHERE CustomerID = 'TOMSP';

--下面这样更新，没效果，还需试试...

 --没有增加 WITH VALUES时：

--增加 WITH VALUES  之后：

 

 无意中瞟到了这个地方 WITH VALUES