首页 > 代码库 > SQL Server 2012 - 数据更新操作
SQL Server 2012 - 数据更新操作
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 抛出写入的StuID DECLARE @stuId VARCHAR(10) INSERT dbo.Student ( StuID, Class, StuName, StuEnName, StuAge, StuBirthday, StuSex, Height, Remark ) OUTPUT Inserted.StuID VALUES ( ‘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 C ON t.Class=c.ID WHERE C.ID=‘1‘ -- Where的方式进行多表更新 UPDATE T SET T.Remark = ‘班级02的学生‘ FROM dbo.Student AS T , dbo.ClassInfo AS C where t.Class=c.ID and C.ID=‘2‘ -- 7, OUTPUT查看更新前、后的数据 UPDATE dbo.Student SET StuSex=‘女‘ OUTPUT Inserted.StuSex,Deleted.StuSex WHERE StuID=‘007‘ -- 8, Update中Set语句后的命令是同时执行的,没有先后顺序 UPDATE dbo.Student SET StuSex=‘男‘,StuName=StuName+‘-‘+StuSex WHERE StuID=‘007‘ SELECT * FROM dbo.Student WHERE StuID=‘007‘ -- 两个字段进行值的互换 UPDATE dbo.Student SET StuAge=Height,Height=StuAge WHERE StuID=‘007‘ SELECT * FROM dbo.Student WHERE StuID=‘007‘ -- 9, Delete SELECT * INTO Student_His FROM dbo.Student WHERE 1=2 DELETE dbo.Student OUTPUT Deleted.StuID,Deleted.Class,Deleted.StuName,Deleted.StuEnName,Deleted.StuBirthday,Deleted.StuSex,Deleted.Height,Deleted.Remark INTO 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 - 数据更新操作
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。