開發筆記小本本

主要記錄工作中用到的一些開發語言以及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

例如有如下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;

查詢結果如下

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&"')"