SQL Server数据库操作 #

SQL Server 数据库结构
├── 主数据文件（.mdf）
│   └── 数据库启动信息、数据
├── 次要数据文件（.ndf）
│   └── 可选，分散数据
├── 事务日志文件（.ldf）
│   └── 恢复数据库所需日志
└── 文件组
    ├── 主文件组（PRIMARY）
    └── 用户定义文件组

-- 查看系统数据库
SELECT name, database_id, create_date 
FROM sys.databases;

-- 结果
name      | database_id | create_date
----------|-------------|--------------------
master    | 1           | 2003-04-08 09:13:36
tempdb    | 2           | 2024-01-15 10:00:00
model     | 3           | 2003-04-08 09:13:36
msdb      | 4           | 2003-04-08 09:13:36

-- 最简单的创建方式
CREATE DATABASE mydb;

-- 指定数据文件和日志文件
CREATE DATABASE mydb
ON PRIMARY 
(
    NAME = 'mydb_data',
    FILENAME = 'C:\SQLData\mydb.mdf',
    SIZE = 10MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 10%
)
LOG ON
(
    NAME = 'mydb_log',
    FILENAME = 'C:\SQLData\mydb.ldf',
    SIZE = 5MB,
    MAXSIZE = 50MB,
    FILEGROWTH = 5MB
);

CREATE DATABASE database_name
[ CONTAINMENT = { NONE | PARTIAL } ]
[ ON 
    [ PRIMARY ] <filespec> [ ,...n ]
    [ , <filegroup> [ ,...n ] ]
]
[ LOG ON { <filespec> [ ,...n ] } ]
[ COLLATE collation_name ]
[ WITH <option> [ ,...n ] ]
[;]

<filespec> ::=
{
    NAME = logical_file_name,
    FILENAME = { 'os_file_name' | 'filestream_path' }
    [ , SIZE = size [ KB | MB | GB | TB ] ]
    [ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]
    [ , FILEGROWTH = growth_increment [ KB | MB | GB | TB | % ] ]
}

-- 创建完整配置的数据库
CREATE DATABASE company
ON PRIMARY 
(
    NAME = 'company_data',
    FILENAME = 'D:\SQLData\company.mdf',
    SIZE = 50MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10MB
),
FILEGROUP fg_secondary
(
    NAME = 'company_data2',
    FILENAME = 'E:\SQLData\company.ndf',
    SIZE = 50MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10MB
)
LOG ON
(
    NAME = 'company_log',
    FILENAME = 'F:\SQLLog\company.ldf',
    SIZE = 25MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10%
)
COLLATE Chinese_PRC_CI_AS;

-- 创建多文件组数据库
CREATE DATABASE sales
ON PRIMARY 
(
    NAME = 'sales_primary',
    FILENAME = 'D:\SQLData\sales.mdf',
    SIZE = 20MB
),
FILEGROUP fg_2023
(
    NAME = 'sales_2023',
    FILENAME = 'D:\SQLData\sales_2023.ndf',
    SIZE = 50MB
),
FILEGROUP fg_2024
(
    NAME = 'sales_2024',
    FILENAME = 'D:\SQLData\sales_2024.ndf',
    SIZE = 50MB
)
LOG ON
(
    NAME = 'sales_log',
    FILENAME = 'D:\SQLLog\sales.ldf',
    SIZE = 20MB
);

-- 在指定文件组创建表
CREATE TABLE sales.orders_2024
(
    id INT,
    order_date DATE,
    amount DECIMAL(10,2)
) ON fg_2024;

-- 查看所有数据库
SELECT 
    name,
    database_id,
    create_date,
    compatibility_level,
    collation_name,
    state_desc,
    recovery_model_desc
FROM sys.databases;

-- 使用系统存储过程
EXEC sp_databases;
EXEC sp_helpdb;

-- 查看数据库详细信息
EXEC sp_helpdb 'mydb';

-- 查看数据库文件
SELECT 
    name AS [逻辑名],
    physical_name AS [物理路径],
    size/128.0 AS [大小(MB)],
    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS [可用空间(MB)],
    growth AS [增长量],
    is_percent_growth AS [百分比增长]
FROM sys.database_files;

-- 查看文件组
SELECT 
    name AS [文件组名],
    type_desc AS [类型],
    is_default AS [默认]
FROM sys.filegroups;

-- 使用DATABASEPROPERTYEX函数
SELECT 
    DATABASEPROPERTYEX('mydb', 'Status') AS [状态],
    DATABASEPROPERTYEX('mydb', 'Recovery') AS [恢复模式],
    DATABASEPROPERTYEX('mydb', 'Version') AS [版本],
    DATABASEPROPERTYEX('mydb', 'Collation') AS [排序规则],
    DATABASEPROPERTYEX('mydb', 'IsAutoClose') AS [自动关闭],
    DATABASEPROPERTYEX('mydb', 'IsAutoShrink') AS [自动收缩];

-- 方式1：使用ALTER DATABASE
ALTER DATABASE mydb MODIFY NAME = mydb_new;

-- 方式2：使用sp_renamedb
EXEC sp_renamedb 'mydb', 'mydb_new';

-- 添加数据文件
ALTER DATABASE mydb
ADD FILE 
(
    NAME = 'mydb_data2',
    FILENAME = 'D:\SQLData\mydb_data2.ndf',
    SIZE = 20MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
) TO FILEGROUP PRIMARY;

-- 添加日志文件
ALTER DATABASE mydb
ADD LOG FILE
(
    NAME = 'mydb_log2',
    FILENAME = 'D:\SQLLog\mydb_log2.ldf',
    SIZE = 10MB,
    MAXSIZE = 50MB,
    FILEGROWTH = 10%
);

-- 添加文件组
ALTER DATABASE mydb ADD FILEGROUP fg_index;

-- 向文件组添加文件
ALTER DATABASE mydb
ADD FILE 
(
    NAME = 'mydb_index',
    FILENAME = 'D:\SQLData\mydb_index.ndf',
    SIZE = 20MB
) TO FILEGROUP fg_index;

-- 设置默认文件组
ALTER DATABASE mydb MODIFY FILEGROUP fg_index DEFAULT;

-- 修改文件大小
ALTER DATABASE mydb
MODIFY FILE 
(
    NAME = 'mydb_data',
    SIZE = 100MB
);

-- 修改文件增长
ALTER DATABASE mydb
MODIFY FILE 
(
    NAME = 'mydb_data',
    FILEGROWTH = 20MB
);

-- 修改最大大小
ALTER DATABASE mydb
MODIFY FILE 
(
    NAME = 'mydb_data',
    MAXSIZE = 500MB
);

-- 设置恢复模式
ALTER DATABASE mydb SET RECOVERY FULL;
ALTER DATABASE mydb SET RECOVERY SIMPLE;
ALTER DATABASE mydb SET RECOVERY BULK_LOGGED;

-- 设置自动选项
ALTER DATABASE mydb SET AUTO_CLOSE ON;
ALTER DATABASE mydb SET AUTO_SHRINK ON;
ALTER DATABASE mydb SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE mydb SET AUTO_UPDATE_STATISTICS ON;

-- 设置只读
ALTER DATABASE mydb SET READ_ONLY;
ALTER DATABASE mydb SET READ_WRITE;

-- 设置单用户模式
ALTER DATABASE mydb SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE mydb SET MULTI_USER;

-- 设置兼容级别
ALTER DATABASE mydb SET COMPATIBILITY_LEVEL = 160;  -- SQL Server 2022

-- 修改数据库排序规则
ALTER DATABASE mydb 
COLLATE Chinese_PRC_CI_AS;

-- 常用排序规则
-- Chinese_PRC_CI_AS: 中文，不区分大小写
-- Chinese_PRC_CS_AS: 中文，区分大小写
-- SQL_Latin1_General_CP1_CI_AS: 西文，不区分大小写

-- 删除数据库
DROP DATABASE mydb;

-- 安全删除（SQL Server 2016+）
DROP DATABASE IF EXISTS mydb;

-- 检查数据库是否存在
IF DB_ID('mydb') IS NOT NULL
BEGIN
    -- 设置单用户模式，断开所有连接
    ALTER DATABASE mydb SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE mydb;
    PRINT '数据库已删除';
END
ELSE
BEGIN
    PRINT '数据库不存在';
END

-- 创建快照
CREATE DATABASE mydb_snapshot ON
(
    NAME = 'mydb_data',
    FILENAME = 'D:\SQLSnapshot\mydb_snapshot.ss'
)
AS SNAPSHOT OF mydb;

-- 删除快照
DROP DATABASE mydb_snapshot;

-- 切换数据库
USE mydb;

-- 查看当前数据库
SELECT DB_NAME() AS [当前数据库];

-- 查看数据库ID
SELECT DB_ID('mydb') AS [数据库ID];

-- 批处理中切换
USE master;
GO
SELECT name FROM sys.databases;
GO

USE mydb;
GO
SELECT name FROM sys.tables;
GO

-- 创建数据库快照
CREATE DATABASE mydb_snapshot_20240115 ON
(
    NAME = 'mydb_data',
    FILENAME = 'D:\SQLSnapshot\mydb_snapshot_20240115.ss'
),
(
    NAME = 'mydb_data2',
    FILENAME = 'D:\SQLSnapshot\mydb_snapshot_20240115_2.ss'
)
AS SNAPSHOT OF mydb;

-- 查看数据库快照
SELECT 
    name,
    database_id,
    source_database_id
FROM sys.databases
WHERE source_database_id IS NOT NULL;

-- 从快照恢复数据库
USE master;
GO
ALTER DATABASE mydb SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
RESTORE DATABASE mydb FROM DATABASE_SNAPSHOT = 'mydb_snapshot_20240115';
GO
ALTER DATABASE mydb SET MULTI_USER;
GO

-- 分离数据库
EXEC sp_detach_db 'mydb', 'true';

-- 带选项分离
EXEC sp_detach_db 
    @dbname = 'mydb',
    @skipchecks = 'true',     -- 跳过更新统计信息
    @keepfulltextindexfile = 'true';  -- 保留全文索引文件

-- 附加数据库（简单方式）
CREATE DATABASE mydb ON
(
    FILENAME = 'D:\SQLData\mydb.mdf'
)
LOG ON
(
    FILENAME = 'D:\SQLLog\mydb.ldf'
)
FOR ATTACH;

-- 使用sp_attach_db（已废弃，不推荐）
EXEC sp_attach_db 'mydb', 
    'D:\SQLData\mydb.mdf',
    'D:\SQLLog\mydb.ldf';

-- 附加多个文件
CREATE DATABASE mydb ON
(
    FILENAME = 'D:\SQLData\mydb.mdf'
),
(
    FILENAME = 'D:\SQLData\mydb_data2.ndf'
)
LOG ON
(
    FILENAME = 'D:\SQLLog\mydb.ldf'
)
FOR ATTACH;

-- 启用自动收缩
ALTER DATABASE mydb SET AUTO_SHRINK ON;

-- 收缩数据库
DBCC SHRINKDATABASE (mydb, 10);  -- 保留10%空闲空间

-- 收缩到最小
DBCC SHRINKDATABASE (mydb, 0);

-- 收缩单个文件
DBCC SHRINKFILE (mydb_data, 10);  -- 收缩到10MB

-- 清空文件（准备删除）
DBCC SHRINKFILE (mydb_data2, EMPTYFILE);

-- 先备份日志（如果恢复模式为FULL）
BACKUP LOG mydb TO DISK = 'NUL';

-- 收缩日志文件
DBCC SHRINKFILE (mydb_log, 10);

-- 如果是简单恢复模式
ALTER DATABASE mydb SET RECOVERY SIMPLE;
DBCC SHRINKFILE (mydb_log, 10);
ALTER DATABASE mydb SET RECOVERY FULL;

-- SSMS中右键数据库 → 任务 → 生成脚本
-- 可以生成整个数据库的创建脚本

-- 使用PowerShell
$scripter = New-Object Microsoft.SqlServer.Management.Smo.Scripter
$scripter.Server = $server
$scripter.Script($database.Tables)

SSMS操作：
1. 右键数据库 → 任务 → 复制数据库
2. 选择源服务器和目标服务器
3. 选择要复制的数据库
4. 选择复制方法（分离/附加 或 SMO）
5. 配置目标数据库选项
6. 执行复制

-- 查看数据库大小
EXEC sp_spaceused;

-- 查看所有数据库大小
SELECT 
    DB_NAME(database_id) AS [数据库名],
    SUM(size * 8.0 / 1024) AS [大小(MB)]
FROM sys.master_files
GROUP BY database_id
ORDER BY [大小(MB)] DESC;

-- 查看空间使用情况
SELECT 
    name AS [文件名],
    size/128.0 AS [总大小(MB)],
    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS [可用空间(MB)],
    CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS [已用空间(MB)],
    CAST(CAST(FILEPROPERTY(name, 'SpaceUsed') AS float)/size * 100 AS DECIMAL(5,2)) AS [使用率%]
FROM sys.database_files;

-- 统计各类型对象数量
SELECT 
    type_desc AS [类型],
    COUNT(*) AS [数量]
FROM sys.objects
GROUP BY type_desc
ORDER BY [数量] DESC;

-- 查看表数量和记录数
SELECT 
    SCHEMA_NAME(schema_id) AS [架构],
    name AS [表名],
    SUM(p.rows) AS [行数]
FROM sys.tables t
INNER JOIN sys.partitions p ON t.object_id = p.object_id
WHERE p.index_id IN (0, 1)
GROUP BY SCHEMA_NAME(schema_id), name
ORDER BY [行数] DESC;

文件配置最佳实践：
├── 数据文件和日志文件分开存放
│   └── 数据文件：高速存储
│   └── 日志文件：顺序写入优化存储
├── 合理设置初始大小
│   └── 避免频繁自动增长
├── 文件增长设置
│   ├── 数据文件：固定大小增长（如64MB）
│   └── 日志文件：固定大小增长（如16MB）
└── 使用文件组
    ├── 分离索引和数据
    └── 分区表支持

数据库命名规范：
├── 使用有意义的名称
├── 避免使用特殊字符
├── 统一大小写风格
└── 示例：
    ├── 公司数据库：CompanyDB
    ├── 项目数据库：ProjectManagement
    └── 测试数据库：TestDB_20240115