SQL Server 2012 - 資料更新操作

標籤：ted   table   use   val   value   arch   delete   nihao   rem   

SELECT  *FROM    dbo.Student;--1， 插入資料  Insert ,逗號分隔可以同時插入多條INSERT  dbo.Student        ( StuID, Class, StuName, StuEnName, StuAge, StuBirthday, StuSex,          Height, Remark )VALUES  ( ‘007‘, -- StuID - varchar(10)          3, -- Class - int          N‘呵呵‘, -- StuName - nvarchar(50)          ‘Hehe‘, -- StuEnName - varchar(50)          22, -- StuAge - int          GETDATE(), -- StuBirthday - datetime          N‘男‘, -- StuSex - nvarchar(10)          190, -- Height - int          ‘test‘  -- Remark - text          ),        ( ‘008‘, -- StuID - varchar(10)          3, -- Class - int          N‘你好‘, -- StuName - nvarchar(50)          ‘Nihao‘, -- StuEnName - varchar(50)          25, -- StuAge - int          GETDATE(), -- StuBirthday - datetime          N‘女‘, -- StuSex - nvarchar(10)          190, -- Height - int          ‘test‘  -- Remark - text          );--2， 從一個表網另外一個表中寫入資料INSERT  INTO dbo.Student        SELECT  ‘009‘ ,                Class ,                ‘貓咪‘ ,                ‘Kitty‘ ,                StuAge ,                StuBirthday ,                StuSex ,                Height ,                Remark        FROM    dbo.Student        WHERE   StuID = ‘008‘;-- 3, OUTPUT 拋出寫入的StuIDDECLARE @stuId VARCHAR(10)INSERT  dbo.Student        ( StuID, Class, StuName, StuEnName, StuAge, StuBirthday, StuSex,          Height, Remark )OUTPUT  Inserted.StuIDVALUES  ( ‘010‘, -- StuID - varchar(10)          3, -- Class - int          N‘叮噹‘, -- StuName - nvarchar(50)          ‘DingDang‘, -- StuEnName - varchar(50)          22, -- StuAge - int          GETDATE(), -- StuBirthday - datetime          N‘男‘, -- StuSex - nvarchar(10)          190, -- Height - int          ‘test‘  -- Remark - text          )  --4 ,OUTPUT 拋出寫入的另外一張物理表中（曆史表）DECLARE @stuIdTable  TABLE(id INT)INSERT  dbo.Student        ( StuID, Class, StuName, StuEnName, StuAge, StuBirthday, StuSex,          Height, Remark )OUTPUT  Inserted.StuID INTO @stuIdTable  -- OUTPUT INTO只能寫入表中VALUES  ( ‘012‘, -- StuID - varchar(10)          3, -- Class - int          N‘Vanilla‘, -- StuName - nvarchar(50)          ‘香草‘, -- StuEnName - varchar(50)          22, -- StuAge - int          GETDATE(), -- StuBirthday - datetime          N‘男‘, -- StuSex - nvarchar(10)          190, -- Height - int          ‘test‘  -- Remark - text          )SELECT * FROM @stuIdTable-- 5，更新資料UPDATE dbo.Student SET Height=Height+10 WHERE Height IS NOT NULL-- 6, 根據另外一張表進行資料更新:在兩個表建立關係的情況下進行更新-- Join的方式進行多表更新UPDATE T SET T.Remark = ‘班級01的學生‘ FROM dbo.Student AS T  JOIN dbo.ClassInfo AS CON t.Class=c.ID WHERE C.ID=‘1‘-- Where的方式進行多表更新UPDATE T SET T.Remark = ‘班級02的學生‘ FROM dbo.Student AS T  , dbo.ClassInfo AS Cwhere t.Class=c.ID and C.ID=‘2‘-- 7， OUTPUT查看更新前、後的資料UPDATE dbo.Student SET StuSex=‘女‘ OUTPUT Inserted.StuSex,Deleted.StuSexWHERE StuID=‘007‘-- 8, Update中Set語句後的命令是同時執行的，沒有先後順序UPDATE dbo.Student SET StuSex=‘男‘,StuName=StuName+‘-‘+StuSexWHERE  StuID=‘007‘SELECT * FROM dbo.Student  WHERE  StuID=‘007‘   -- 兩個欄位進行值的互換UPDATE dbo.Student SET StuAge=Height,Height=StuAgeWHERE  StuID=‘007‘SELECT * FROM dbo.Student  WHERE  StuID=‘007‘-- 9, DeleteSELECT * INTO Student_His FROM dbo.Student WHERE 1=2DELETE dbo.Student OUTPUT Deleted.StuID,Deleted.Class,Deleted.StuName,Deleted.StuEnName,Deleted.StuBirthday,Deleted.StuSex,Deleted.Height,Deleted.RemarkINTO dbo.Student_His( StuID,Class,StuName,StuEnName,StuBirthday,StuSex,Height,Remark)WHERE  StuID=‘007‘--10, Truncate Table = delete tablename (不帶任何條件)：清空表，重設自增列，日誌小，操作更快TRUNCATE TABLE dbo.Student_His SELECT *  FROM  [dbo].[Student_His]

　　

SQL Server 2012 - 資料更新操作