开发笔记小本本

主要记录工作中用到的一些开发语言以及Sql 模板,持续更新

1.Sql相关

1.常用Sql模板

1.1. 可重复执行视图

IF EXISTS ( SELECT *FROM  sysobjects WHERE   id = OBJECT_ID('v_Employee') AND type = 'V' ) 
  DROP VIEW v_Employee
GO
CREATE VIEW v_Employee
AS
    SELECT  * FROM  v_Employee a
GO

1.2. 可重复执行新增表

IF NOT EXISTS(SELECT  *FROM    sysobjects WHERE   id = OBJECT_ID('EmployeeInfo') AND type = 'U' ) 
  CREATE TABLE [EmployeeInfo](
          [ID] [uniqueidentifier] PRIMARY KEY CLUSTERED
      ) ;
GO

1.3.可重复执行存储过程

IF ( SELECT OBJECT_ID('proc_GetEmployeeInfo')) IS NOT NULL 
   DROP PROC proc_GetEmployeeInfo
go

1.4.可重复执行修改字段

IF NOT EXISTS ( SELECT  *FROM    syscolumns WHERE   id = OBJECT_ID('EmployeeInfo') AND name = 'Name' ) 
    ALTER TABLE [EmployeeInfo] ADD [Name] [varchar] (50) not null; 
GO
 ALTER TABLE Table1 ALTER COLUMN column1 VARCHAR(255)

1.5.数据库命令

--查看表结构
desc EmployeeInfo
--查看创建表结构的sql语句
show create table EmployeeInfo;
--删除表中的列数据
alter table EmployeeInfo drop Age;

1.行转列查询

下面左边是主表Emp_DataSet 右边是扩展表Emp_DataSet_Ext

Id	Code	Name		Id	F_id	Ext_column	Value
1	1001	张三		1	1	Hobby	篮球
2	1002	李四		2	1	Age	23

例如有如下2个表Emp_DataSet_Ext为Emp_DataSet的扩展表，存的是扩展字段，如何将扩展表的值，组合到主表的列呢？所以就需要使用将行转为列

SELECT *
FROM [dbo].[Emp_DataSet] a
     INNER JOIN
(
    SELECT id, 
           MAX(CASE
                   WHEN b.name = 'Hobby'
                   THEN b.value
                   ELSE NULL
               END) AS Hobby, 
           MAX(CASE
                   WHEN b.name = 'Age'
                   THEN b.value
                   ELSE NULL
               END) AS Age
    FROM Emp_DataSet_Ext b
    GROUP BY id
) b ON a.Id = b.F_id;

查询结果如下

Id	Code	Name	Hobby	Age
1	1001	张三	篮球	23

2.自增主键插入数据

例如我们需要将测试库Emp_DataSet表和Emp_DataSet关联表的数据，同步到正式库，但是Emp_DataSet表中的id是自增，我们可以使用Sql导出工具导出插入语句，然后使用下面的Sql包裹插入语句就可以实现指定id插入

SET IDENTITY_INSERT [dbo].[Emp_DataSet] ON 
--inset....
SET IDENTITY_INSERT [dbo].[Emp_DataSet] ON 

3.循环数据

在数据库中标准的T-SQL中循环是需要使用游标的，但是到现在为止已经不建议使用游标，触发器之类的了，但是有时候利用Sql处理数据，需要用到一些循环，那该如何实现呢？例如我们需要清空数据库中所有的表数据，思路如下

先查询出当前库的所有表，并且使用ROW_NUMBER()为每条数据给一个id，保证顺序插入临时表

然后使用while循环临时表，利用t-sql拼接删除表数据的语句

SELECT ROW_NUMBER() OVER(ORDER BY Name) AS rowNumber ,Name INTO #t1 FROM SysObjects Where XType='U' orDER BY Name ;

DECLARE @count INT;
DECLARE @index INT=1;
DECLARE @tbname NVARCHAR(40);
DECLARE @sql NVARCHAR(MAX)='';
SELECT @count =COUNT(1) FROM #t1
while @index<=82
BEGIN
SELECT @tbname = name FROM #t1 WHERE rowNumber =@index ;
IF(LEN(@tbname)>0)
BEGIN
PRINT @tbname
SET @sql+='DELETE FROM [dbo].['+@tbname+'];';
END
set @index+=1
END;
PRINT @sql

4.Excel数据处理

有时候人家提供一些Excel数据，需要导入到数据库中，我想你一定会说有直接sql操作Excel啊，但是如果有的数据不好操作呢？或者是Csv文件呢？说白了那种方法用不了了，下面就分享一招利用公式来生成Sql语句,虽然稍微有点笨笨的但是也是总结的一种经验吧。

="INSERT INTO [dbo].[EmployeeInformation] VALUES('"&A1&"','"&B1&"',N'"&C1&"','"&D1&"','"&G1&"','"&H1&"','"&E1&"','"&I1&"','"&J1&"','"&K1&"','"&L1&"','"&M1&"','"&N1&"','"&O1&"','"&P1&"','"&TEXT(Q1,"yyyy-mm-dd")&"','"&R1&"','','','','','','','','','','"&Y1&"','"&TEXT(Z1,"yyyy-mm-dd")&"','"&TEXT(AA1,"yyyy-mm-dd")&"','"&AB1&"')"