SQL Server数据库的常用语句的分类整理和典型示例-CSDN博客

-- 创建数据库
CREATE DATABASE SalesDB 
ON PRIMARY 
( NAME = SalesDB_Data,
  FILENAME = 'D:\Data\SalesDB.mdf',
  SIZE = 50MB,
  MAXSIZE = UNLIMITED,
  FILEGROWTH = 10% )
LOG ON 
( NAME = SalesDB_Log,
  FILENAME = 'D:\Data\SalesDB.ldf',
  SIZE = 20MB,
  MAXSIZE = 1GB,
  FILEGROWTH = 5MB );
 
-- 修改数据库配置
ALTER DATABASE SalesDB 
MODIFY FILE 
( NAME = SalesDB_Data, SIZE = 100MB );
 
-- 删除数据库
DROP DATABASE SalesDB;

2. 表结构操作

-- 创建表（含约束）
CREATE TABLE Employees (
    EmpID INT PRIMARY KEY IDENTITY(1,1),
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    HireDate DATE DEFAULT GETDATE(),
    Salary DECIMAL(10,2) CHECK (Salary > 0),
    DeptID INT FOREIGN KEY REFERENCES Departments(DeptID)
);
 
-- 修改表结构
ALTER TABLE Employees 
ADD Email NVARCHAR(100) UNIQUE;
 
ALTER TABLE Employees 
ALTER COLUMN Salary DECIMAL(12,2);
 
-- 删除表
DROP TABLE Employees;

3. 索引管理

-- 创建索引
CREATE NONCLUSTERED INDEX IX_Employees_LastName 
ON Employees(LastName)
INCLUDE (FirstName, HireDate);
 
-- 重建索引
ALTER INDEX ALL ON Employees 
REBUILD WITH (FILLFACTOR = 90);
 
-- 删除索引
DROP INDEX Employees.IX_Employees_LastName;

二、数据操作语言 (DML)

1. 数据插入

-- 批量插入
INSERT INTO Employees (FirstName, LastName, Salary, DeptID)
VALUES 
('John', 'Doe', 65000.00, 1),
('Jane', 'Smith', 72000.00, 2);
 
-- 从查询结果插入
INSERT INTO SalesArchive
SELECT * FROM Sales 
WHERE OrderDate < '2023-01-01';

2. 数据更新

-- 条件更新
UPDATE Employees
SET Salary = Salary * 1.10
WHERE DeptID IN (SELECT DeptID FROM Departments WHERE DeptName = 'Sales');
 
-- 使用CTE更新
WITH TopPerformers AS (
    SELECT TOP 10 EmpID, SalesAmount 
    FROM Sales 
    ORDER BY SalesAmount DESC
)
UPDATE Employees
SET Bonus = 5000
WHERE EmpID IN (SELECT EmpID FROM TopPerformers);

3. 数据删除

-- 安全删除（使用事务）
BEGIN TRANSACTION;
DELETE FROM AuditLog 
WHERE LogDate < DATEADD(year, -1, GETDATE());
-- 验证后提交
COMMIT;
-- 回滚示例
-- ROLLBACK;

三、数据查询语言 (DQL)

1. 基础查询

-- 列别名与计算
SELECT 
    FirstName + ' ' + LastName AS FullName,
    Salary * 12 AS AnnualSalary
FROM Employees;
 
-- 条件过滤
SELECT * FROM Products
WHERE Category = 'Electronics' 
  AND UnitPrice BETWEEN 500 AND 2000;

2. 高级查询

-- 窗口函数（排名）
SELECT 
    SalesPerson,
    SUM(OrderAmount) AS TotalSales,
    RANK() OVER (ORDER BY SUM(OrderAmount) DESC) AS SalesRank
FROM Orders
GROUP BY SalesPerson;
 
-- 公用表表达式(CTE)
WITH RegionSales AS (
    SELECT 
        Region,
        SUM(OrderAmount) AS RegionalSales
    FROM Orders
    GROUP BY Region
)
SELECT 
    Region,
    RegionalSales,
    (RegionalSales * 1.0 / SUM(RegionalSales) OVER()) * 100 AS SalesPercentage
FROM RegionSales;

3. 性能优化

-- 强制索引提示
SELECT * FROM Employees WITH (INDEX(IX_Employees_LastName))
WHERE LastName LIKE 'S%';
 
-- 查询执行计划
EXPLAIN 
SELECT * FROM Orders 
WHERE CustomerID = 1001;

四、数据控制语言 (DCL)

1. 权限管理

-- 创建登录名
CREATE LOGIN SalesUser 
WITH PASSWORD = 'SecurePass123!';
 
-- 分配数据库角色
USE SalesDB;
CREATE USER SalesUser FOR LOGIN SalesUser;
ALTER ROLE db_datareader ADD MEMBER SalesUser;
 
-- 权限细化控制
GRANT SELECT, INSERT ON Orders TO SalesUser;
DENY UPDATE ON Employees TO SalesUser;

五、事务与并发控制

- 显式事务
BEGIN TRY
    BEGIN TRANSACTION;
    UPDATE Accounts 
    SET Balance = Balance - 1000 
    WHERE AccountID = 1001;
    
    UPDATE Accounts 
    SET Balance = Balance + 1000 
    WHERE AccountID = 2002;
    COMMIT;
END TRY
BEGIN CATCH
    ROLLBACK;
    THROW;
END CATCH;
 
-- 事务隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
SELECT * FROM Orders WHERE OrderID = 5001;
COMMIT;

六、维护与监控

1. 备份恢复

-- 完整备份
BACKUP DATABASE SalesDB 
TO DISK = 'D:\Backup\SalesDB_Full.bak' 
WITH INIT, STATS = 10;
 
-- 差异备份
BACKUP DATABASE SalesDB 
TO DISK = 'D:\Backup\SalesDB_Diff.bak' 
WITH DIFFERENTIAL, STATS = 10;
 
-- 时间点恢复
RESTORE DATABASE SalesDB 
FROM DISK = 'D:\Backup\SalesDB_Full.bak' 
WITH FILE = 1,
NORECOVERY;
 
RESTORE LOG SalesDB 
FROM DISK = 'D:\Backup\SalesDB_Log.trn' 
WITH FILE = 2,
STOPAT = '2023-10-01T09:00:00',
RECOVERY;

2. 性能监控

-- 查询活动会话
SELECT 
    session_id,
    status,
    command,
    text AS QueryText
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle);
 
-- 缺失索引建议
SELECT 
    mig.index_group_handle,
    mid.index_handle,
    statement AS [Table Name],
    column_id,
    column_name,
    column_usage
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs 
    ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid 
    ON mig.index_handle = mid.index_handle
ORDER BY migs.user_seeks DESC;

七、高级功能

1. JSON支持

- 解析JSON数据
DECLARE @json NVARCHAR(MAX) = 
N'{
    "OrderID": 5001,
    "Items": [
        {"ProductID": 101, "Quantity": 2},
        {"ProductID": 102, "Quantity": 1}
    ]
}';
 
SELECT 
    OrderID = JSON_VALUE(@json, '$.OrderID'),
    ProductIDs = (
        SELECT ProductID 
        FROM OPENJSON(@json, '$.Items')
        WITH (ProductID INT '$.ProductID')
    );

2. 内存优化表

-- 创建内存优化表
CREATE TABLE dbo.HighFrequencyTrades (
    TradeID BIGINT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
    Symbol NVARCHAR(10) NOT NULL,
    TradeTime DATETIME2(7) NOT NULL,
    Price DECIMAL(18,4) NOT NULL,
    Quantity INT NOT NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
 
-- 本地编译存储过程
CREATE PROCEDURE dbo.ProcessTrade
    @Symbol NVARCHAR(10),
    @Price DECIMAL(18,4),
    @Quantity INT
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    INSERT INTO HighFrequencyTrades (Symbol, TradeTime, Price, Quantity)
    VALUES (@Symbol, SYSDATETIME(), @Price, @Quantity);
END;