ETL学习之SQL Server数据库常用SQL语句

在数据仓库和ETL（提取、转换、加载）过程中，SQL Server 数据库是一个广泛使用的平台。ETL工程师需要熟练掌握SQL语句，以便有效地从源系统中提取数据，进行转换，并将其加载到目标数据库中。本文将介绍一些ETL工程师常用的SQL Server数据库SQL语句。

1. 数据提取（Extract）

查询特定列

SELECT Column1, Column2, Column3 FROM YourTable;

查询特定行

SELECT * FROM YourTable WHERE Condition;

使用JOIN连接多个表

SELECT Table1.Column, Table2.Column FROM Table1 JOIN Table2 ON Table1.JoinColumn = Table2.JoinColumn;

使用子查询

SELECT * FROM YourTable WHERE Column IN (SELECT Column FROM AnotherTable);

2. 数据转换（Transform）

数据类型转换

SELECT CAST(Column AS DataType) AS NewColumnName FROM YourTable;

字符串处理

• 大小写转换

SELECT UPPER(Column) AS UpperCase, LOWER(Column) AS LowerCase FROM YourTable;

• 字符串拼接

SELECT CONCAT(Column1, Column2) AS ConcatenatedColumn FROM YourTable;

• 字符串截取

SELECT LEFT(Column, Length) AS LeftSubstring, RIGHT(Column, Length) AS RightSubstring FROM YourTable;

日期处理

• 日期格式化

SELECT FORMAT(Column, 'yyyy-MM-dd') AS FormattedDate FROM YourTable;

• 日期加减

SELECT DATEADD(day, Number, Column) AS NewDate FROM YourTable;

3. 数据加载（Load）

插入新数据

INSERT INTO YourTable (Column1, Column2) VALUES (Value1, Value2);

更新现有数据

UPDATE YourTable SET Column1 = Value1, Column2 = Value2 WHERE Condition;

删除数据

DELETE FROM YourTable WHERE Condition;

批量插入数据

INSERT INTO YourTable (Column1, Column2) VALUES (Value1a, Value2a), (Value1b, Value2b), (Value1c, Value2c);

4. 性能优化

使用索引

CREATE INDEX IndexName ON YourTable (ColumnName);

分析查询性能

SET STATISTICS TIME ON; SELECT * FROM YourTable; SET STATISTICS TIME OFF;

5. 其他实用语句

查看表结构

EXEC sp_columns YourTable;

查看索引信息

EXEC sp_helpindex YourTable;

查看数据库文件大小

SELECT
    DB_NAME(database_id) AS DatabaseName,
    Name AS FileName,
    type_desc AS FileType,
    size/128.0 AS CurrentSizeMB,
    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS FreeSpaceMB
FROM sys.master_files
WHERE database_id = DB_ID('YourDatabaseName');