SQL增删改查等操作

增加

• 增加一整条完整记录

--增加一整条完整数据，可以省略各个字段名
INSERT INTO [Test].[dbo].[Test.Source]
VALUES (N'11',N'21',N'k',N'女',N'1','2020-11-01 00:00:00.000')

• 结果：

• 增加多条完整记录

--增加多条完整数据
INSERT INTO [Test].[dbo].[Test.Source]
VALUES (N'12',N'22',N'l',N'女',N'1','2020-11-01 00:00:00.000')
	  ,(N'13',N'23',N'm',N'男',N'2','2019-08-01 00:00:00.000')
	  ,(N'14',N'24',N'n',N'女',N'3','2018-01-01 00:00:00.000')

• 结果：

• 增加指定字段的记录

--增加指定字段的数据
INSERT INTO [Test].[dbo].[Test.Source] ([id]
									   ,[工号]
									   ,[姓名]
									   ,[性别]
  )
VALUES (N'15',N'25',N'O',N'男')

• 结果：

• 增加字段

ALTER TABLE [表名] ADD [字段名] DECIMAL(38, 0) NULL DEFAULT 0

删除

• 删除数据库

DROP DATABASE 数据库名称

• 删除数据库表

1. DELETE

--只删除表里的数据，表的结构不受影响，自动编号不会恢复为初始值
DELETE FROM 表名
--表名后还可以根据需求接WHERE子句，限定删除条件
DELETE FROM 表名 WHERE [CreateTime] < N'2020-01-01 00:00:00.000'

2. TRUNCATE

--删除表里的全部数据，无法接WHERE子句限定删除条件，自动编号恢复为初始值
--由于TRUNCATE采取按最小方式记录日志，所以删除效率高于DELETE，且不触发DELETE触发器
TRUNCATE TABLE 表名

3. DROP

--删除表本身，表中的数据以及表的结构（列、键、约束、视图）全部删除
DROP TABLE 表名

• 删除字段

ALTER TABLE [表名] DROP COLUMN [字段名]

修改

• SQL 修改表名

EXEC SP_RENAME '原表名', '新表名'

• SQL 字段操作

1. 修改字段

 --修改字段名
EXEC SP_RENAME '表名.原列名','新列名'

2. 修改字段数据类型

--修改字段数据类型
ALTER TABLE [表名] MODIFY (COLUMNName 数据类型)
ALTER TABLE [表名] ALTER COLUMN [列名] DECIMAL(38, 0) NULL

• 修改数据

UPDATE [表名] SET [修改字段]=修改内容 WHERE 修改限定条件
--举例：将[Test].[dbo].[Test.Source]表中工号为20的员工工龄从“1”改为“2”
UPDATE [Test].[dbo].[Test.Source] SET [工龄]=N'2' WHERE [工号]=N'20'

• 修改前：
  
• 修改后：
  

查询

--查询语句
SELECT * FROM 表名
--查询指定列
SELECT 表名.指定列名称1, 表名.指定列名称2 FROM 表名
--查询条件
SELECT * FROM 表名 WHERE 条件
--模糊查询
SELECT * FROM 表名 WHERE 列名 LIKE 模糊值
--查询以A作为起始的数据
SELECT * FROM 表名 WHERE 列名 LIKE 'A%'
--查询A*的数据
SELECT * FROM 表名 WHERE 列名 LIKE 'A_'
--查询A**的数据
SELECT * FROM 表名 WHERE 列名 LIKE 'A__'
--查询包含A的数据
SELECT * FROM 表名 WHERE 列名 LIKE '%A%'
--查询包含A或B的数据
SELECT * FROM 表名 WHERE 列名 LIKE '[A,B]%'
--查询除包含A和B以外的全部数据
SELECT * FROM 表名 WHERE 列名 LIKE '[^A,B]%'
--查询字段值为空的数据
SELECT * FROM 表名 WHERE 列名 IS NULL
--查询字段值非空的数据
SELECT * FROM 表名 WHERE 列名 IS NOT NULL
--范围查询
SELECT * FROM 表名 WHERE 列名 BETWEEN MIN AND MAX
--查询前10条记录
SELECT TOP 10 * FROM 表名
--允许脏读查询
SELECT * FROM 表名 WITH(NOLOCK)

SQL 将查询的数据插入到另一张表中

• 查询源表中性别为“男”的所有行数据插入到目标表中

1. 操作前：
   
2. 操作后：
   

SQL 将JSON格式的数据转换后解析成SQL数据

DECLARE @input1 NVARCHAR(MAX)='{"AssociatedConditionDTO":[{"associateDimensionName":"dim_ghs1","id":"0","relevancy":"dim_ghs3.rowguid=dim_ghs1.rowguid "},{"associateDimensionName":"dim_ghs2","id":"1","relevancy":"dim_ghs3.salestaxrateid=dim_ghs2.salestaxrateid and dim_ghs3.stateprovinceid=dim_ghs2.stateprovinceid "}]}'
CREATE TABLE #1(output1 NVARCHAR(MAX))
DECLARE @sqlStr NVARCHAR(MAX)
SET @sqlStr='SELECT output1='''+REPLACE(@input1,'}]}','}]')+''''
PRINT (@sqlStr)
INSERT INTO #1 EXEC(@sqlStr)
DECLARE @json1 NVARCHAR(MAX)
SELECT * FROM #1
SELECT SUBSTRING(output1, CHARINDEX(':[',output1)+1,LEN(output1)-CHARINDEX('":',output1)) FROM #1
SET @json1=(SELECT SUBSTRING(output1, CHARINDEX(':[',output1)+1,LEN(output1)-CHARINDEX('":',output1)) FROM #1)
SELECT * FROM OPENJSON(@json1)
WITH (   
    rename VARCHAR(50)   '$.associateDimensionName',   
    id VARCHAR(15)   '$.id',
 relevancy VARCHAR(150) '$.relevancy'
)
DROP TABLE #1

• 结果：
  
• 可以看出我们的代码有些冗长，接下来我们对代码进行优化。

DECLARE @input NVARCHAR(MAX)='{"AssociatedConditionDTO":[{"associateDimensionName":"dim_ghs1","id":"0","relevancy":"dim_ghs3.rowguid=dim_ghs1.rowguid "},{"associateDimensionName":"dim_ghs2","id":"1","relevancy":"dim_ghs3.salestaxrateid=dim_ghs2.salestaxrateid and dim_ghs3.stateprovinceid=dim_ghs2.stateprovinceid "}]}'
--把最后的大括号去掉
SET @input=REPLACE(@input,'}]}','}]')
SELECT @input
--从中括号出现的位置开始截取，到最后一位
SET @input=SUBSTRING(@input,CHARINDEX('[',@input),1000)
SELECT @input
PRINT (@input)
--解析json
SELECT * FROM OPENJSON(@input)
WITH (   
    rename VARCHAR(50)   '$.associateDimensionName',   
    id VARCHAR(15)   '$.id',
    relevancy VARCHAR(150) '$.relevancy'
)

• 优化后得到相同的输出结果：
  

未完待续…