MDF文件,怎麼建立本地查詢和使用?

資料庫為網上下載的,想研究學習使用.之前有知友建立的網站被關閉了.小白一枚,想在本地搭建一個,搜了無數教程,不得其法.懇請大神指教,感激~~

既然是小白，我就手把手教了。

一、下載這個版本的SQL SERVER：

文件名
cn_sql_server_2008_r2_developer_x86_x64_ia64_dvd_522724.iso
SHA1
AAE0E2D4E41AB7591634D53C7BC76A112F31B617
文件大小
4.34GB
發布時間
2010-05-03

ed2k://|file|cn_sql_server_2008_r2_developer_x86_x64_ia64_dvd_522724.iso|4662884352|E436F05BCB0165FDF7E5E61862AB6BE1|/


安裝方法詳見：SQL Server 2008 安裝圖解

二、你下載的是群號資料庫QunInfo（11個）和群成員資料庫GroupData（11個）。

1、首先，解壓縮，得到兩個庫總共22個mdf文件(各11個)。

打開Microsoft SQL Server Management Studio，菜單欄右上角找到「新建查詢按鈕」，打開一個空白頁面，那是跑腳本的窗口。由於沒有log文件，所以採用以下語句附加進資料庫（注意QunInfo01~QunInfo11的命名不要改）：

--附加資料庫(MDF文件路徑可根據需要修改)
sp_attach_single_file_db @dbname= GroupData01,@physname= D:BackupDatabaseQQGroupData1.MDF
go
sp_attach_single_file_db @dbname= GroupData02,@physname= D:BackupDatabaseQQGroupData2.MDF
go
sp_attach_single_file_db @dbname= GroupData03,@physname= D:BackupDatabaseQQGroupData3.MDF
go
sp_attach_single_file_db @dbname= GroupData04,@physname= D:BackupDatabaseQQGroupData4.MDF
go
sp_attach_single_file_db @dbname= GroupData05,@physname= D:BackupDatabaseQQGroupData5.MDF
go
sp_attach_single_file_db @dbname= GroupData06,@physname= D:BackupDatabaseQQGroupData6.MDF
go
sp_attach_single_file_db @dbname= GroupData07,@physname= D:BackupDatabaseQQGroupData7.MDF
go
sp_attach_single_file_db @dbname= GroupData08,@physname= D:BackupDatabaseQQGroupData8.MDF
go
sp_attach_single_file_db @dbname= GroupData09,@physname= D:BackupDatabaseQQGroupData9.MDF
go
sp_attach_single_file_db @dbname= GroupData10,@physname= D:BackupDatabaseQQGroupData10.MDF
go
sp_attach_single_file_db @dbname= GroupData11,@physname= D:BackupDatabaseQQGroupData11.MDF
go
sp_attach_single_file_db @dbname= QunInfo01,@physname= D:BackupDatabaseQQQunInfo1.MDF
go
sp_attach_single_file_db @dbname= QunInfo02,@physname= D:BackupDatabaseQQQunInfo2.MDF
go
sp_attach_single_file_db @dbname= QunInfo03,@physname= D:BackupDatabaseQQQunInfo3.MDF
go
sp_attach_single_file_db @dbname= QunInfo04,@physname= D:BackupDatabaseQQQunInfo4.MDF
go
sp_attach_single_file_db @dbname= QunInfo05,@physname= D:BackupDatabaseQQQunInfo5.MDF
go
sp_attach_single_file_db @dbname= QunInfo06,@physname= D:BackupDatabaseQQQunInfo6.MDF
go
sp_attach_single_file_db @dbname= QunInfo07,@physname= D:BackupDatabaseQQQunInfo7.MDF
go
sp_attach_single_file_db @dbname= QunInfo08,@physname= D:BackupDatabaseQQQunInfo8.MDF
go
sp_attach_single_file_db @dbname= QunInfo09,@physname= D:BackupDatabaseQQQunInfo9.MDF
go
sp_attach_single_file_db @dbname= QunInfo10,@physname= D:BackupDatabaseQQQunInfo10.MDF
go
sp_attach_single_file_db @dbname= QunInfo11,@physname= D:BackupDatabaseQQQunInfo11.MDF
go


將上述腳本拷貝到空白窗口，修改MDF文件路徑後點擊工具欄帶紅色感嘆號的「執行」按鈕。每一條語句，就是附加一個MDF文件。這樣，就得到了22個資料庫，QunInfo01~11包含的表如下圖，這樣很不方便查詢。

而GroupData01~11共11個庫有上千張表.

雖然可以查詢使用，但耗時漫長，效率很低，佔用磁碟空間也大，所以我們要優化一下資料庫。

目的是：提高查詢速度跟效率；減少磁碟空間佔用。

2、把QunInfo01~11、GroupData01~11分別合併到兩個庫QunInfo、GroupData，由於數據量太大，用分區表提升性能。

方案如下：

• 重新設計表結構，優化表空間
• 設計分區方案
• 合併資料庫：使用行壓縮，壓縮行數據
• 建立索引，優化查詢速度

最終效果，查詢可以秒出結果。

3、創建新的庫用於合併

創建一個名為QunInfo的資料庫，設置資料庫為簡單恢復模式。

mdf、ldf文件保存路徑可根據需要更改，腳本如下：

USE [master]
GO
CREATE DATABASE [QunInfo] ON PRIMARY
( NAME = NQunInfo, FILENAME = ND:BackupDatabaseQQQunInfo.mdf , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = NQunInfo_log, FILENAME = ND:BackupDatabaseQQQunInfo_log.ldf , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [QunInfo] SET DB_CHAINING OFF
GO


創建一個名字為GroupData的庫，配置同上：

USE [master]
GO
CREATE DATABASE [GroupData] ON PRIMARY
( NAME = NGroupData, FILENAME = ND:BackupDatabaseQQGroupData.mdf , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = NGroupData_log, FILENAME = ND:BackupDatabaseQQGroupData_log.ldf , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [GroupData] SET DB_CHAINING OFF
GO


4、修改各個資料庫中表的名字：把QunList1統一修改為QunList01這樣格式的，這樣做的好處就是在合併數據的時候讀取到的資料庫的數據是按照順序插入到表中的，不會造成數據頁的拆分。

--格式化表名
USE QunInfo01
GO
exec sp_rename QunList1,QunList01
exec sp_rename QunList2,QunList02
exec sp_rename QunList3,QunList03
exec sp_rename QunList4,QunList04
exec sp_rename QunList5,QunList05
exec sp_rename QunList6,QunList06
exec sp_rename QunList7,QunList07
exec sp_rename QunList8,QunList08
exec sp_rename QunList9,QunList09

USE GroupData01
GO
exec sp_rename Group1,Group01
exec sp_rename Group2,Group02
exec sp_rename Group3,Group03
exec sp_rename Group4,Group04
exec sp_rename Group5,Group05
exec sp_rename Group6,Group06
exec sp_rename Group7,Group07
exec sp_rename Group8,Group08
exec sp_rename Group9,Group09


在QunInfo、GroupData資料庫中分別創建一個臨時表：tables，用來保存所有的資料庫與表的信息，提供資料庫合併用。

--創建臨時表
use [QunInfo]
CREATE TABLE [QunInfo].[dbo].[tables](
[db_name] [sysname] NULL,
[table_name] [sysname] NULL,
[status] [bit] default 0
) ON [PRIMARY]

--生成資料庫名稱與表名稱的對應列表
EXEC sp_MSForEachDB USE [?];
--插入表信息
INSERT INTO [GroupData].[dbo].[tables]([table_name])
SELECT name from [?].sys.tables where name like Group% order by name
--更新資料庫名稱
UPDATE [GroupData].[dbo].[tables] SET [db_name] = ? WHERE [db_name] IS NULL


5、對大數據表分區可以加快查詢速度。經過估算，所有表格數據加起來近9千萬行。我們查詢的時候大多用群號欄位，所以用這個群號的欄位[QunNum]作為分區，每一千萬做一個分區，最大的群號為100219998，這樣就有11個分區。以下是分區腳本：

USE [QunInfo]
GO

--5.分區函數的記錄數
SELECT $PARTITION.[Fun_QunList_QunNum](QunNum) AS Partition_num,
MIN(QunNum) AS Min_value,MAX(QunNum) AS Max_value,COUNT(1) AS Record_num
FROM dbo.[QunList]
GROUP BY $PARTITION.[Fun_QunList_QunNum](QunNum)
ORDER BY $PARTITION.[Fun_QunList_QunNum](QunNum);


GroupData01~11裡面的數據大概有15億，以群號作為分區依據，每五百萬為一組，可分為21個文件組：

USE [GroupData]
GO

--5.分區函數的記錄數
SELECT $PARTITION.[Fun_Group_QunNum](QunNum) AS Partition_num,
MIN(QunNum) AS Min_value,MAX(QunNum) AS Max_value,COUNT(1) AS Record_num
FROM dbo.[Group]
GROUP BY $PARTITION.[Fun_Group_QunNum](QunNum)
ORDER BY $PARTITION.[Fun_Group_QunNum](QunNum);


6、接下來在資料庫[QunInfo]新建一個QunList表用於合併QunInfo01~11的所有表格，涉及內容如下：

1) 在QunInfo資料庫中創建分區表QunList，去掉沒有太大意義的ID欄位；

2) 以[QunNum]作為聚集索引，而且是唯一的，這個需要開啟IGNORE_DUP_KEY = ON選項，這樣才可以在批量插入的時候忽略重複值；

3) 對原表的[MastQQ]欄位從int類型變成smallint ，[CreateDate]欄位從varchar(10)類型變為date，數據類型修改是為了減少表佔用的空間；

4) 使用剛剛創建好的分區方案，之後創建的索引進行索引對齊；

5) 對錶使用行壓縮，減少資料庫佔用空間。

腳本如下：

--創建優化後的QunList表
use [QunInfo]
CREATE TABLE [dbo].[QunList](
[QunNum] [int] NOT NULL,
[MastQQ] [smallint] NULL,
[CreateDate] [date] NULL,
[Title] [varchar](22) NULL,
[Class] [varchar](38) NULL,
[QunText] [varchar](80) NULL,
CONSTRAINT [PK_QunList2] PRIMARY KEY CLUSTERED
(
[QunNum] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION = ROW) ON [Sch_QunList_QunNum]([QunNum])
) ON [Sch_QunList_QunNum]([QunNum])
GO


同理對GroupData進行同樣的操作，新建一個Group表：

use GroupData
CREATE TABLE [dbo].[Group](
[QunNum] [int] NOT NULL,
[QQNum] [int] NOT NULL,
[Nick] [varchar](20) NULL,
[Age] [tinyint] NULL,
[Gender] [tinyint] NULL,
[Auth] [tinyint] NULL,
CONSTRAINT [PK_Group] PRIMARY KEY CLUSTERED
(
[QunNum] ASC,
[QQNum] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION = ROW) ON [Sch_Group_QunNum]([QunNum])
) ON [Sch_Group_QunNum]([QunNum])
GO


7、數據表合併

把11個資料庫都合併到新創建的QunInfo的QunList表中（根據電腦性能，大概要跑一兩個小時）：

--合併數據
DECLARE @tablename sysname
DECLARE @dbname sysname
DECLARE @sql NVARCHAR(max)

--運行插入腳本時，可以查看進度
SELECT * from [QunInfo].[dbo].[tables]


把GroupData01~11里的數據合併到GroupData庫里的Group表（大概跑幾個小時，老爺機另算）：

--合併數據
DECLARE @tablename sysname
DECLARE @dbname sysname
DECLARE @sql NVARCHAR(max)

CLOSE @itemCur
DEALLOCATE @itemCur


為GroupData的Group表裡的QQnum新建一個索引：

--索引行壓縮
CREATE NONCLUSTERED INDEX [IX_Group_QQNum] ON [dbo].[Group]
(
[QQNum] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION = ROW) ON [Sch_Group_QunNum]([QunNum])
GO


8、上面數據插入時已經做了行壓縮，接下來對QunInfo進行頁壓縮：

--頁壓縮
ALTER TABLE [QunList]
REBUILD WITH (DATA_COMPRESSION = PAGE );


對GroupData進行頁壓縮：

--索引頁壓縮
CREATE NONCLUSTERED INDEX [IX_Group_QQNum] ON [dbo].[Group]
(
[QQNum] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION = PAGE) ON [Sch_Group_QunNum]([QunNum])
GO


9、到此一切優化操作結束。查查資料庫佔用空間：

Use [QunInfo]
EXEC sp_spaceused [QunList]
Use [GroupData]
EXEC sp_spaceused [GroupData]


結果如下：

哎喲，碉堡了，QunInfo只佔用了4.5G，優化之前佔用8.5G。

GroupData原來的mdf文件大概有83GB，優化壓縮之後mdf文件只佔34GB。

兩個庫所有空間加起來大概65GB左右。

再看看每個分區的數據：

SELECT
partition = $PARTITION.Fun_QunList_QunNum([QunNum])
,rows = COUNT(*)
,min = MIN([QunNum])
,max = MAX([QunNum])
FROM [dbo].[QunList]
GROUP BY $PARTITION.Fun_QunList_QunNum([QunNum])
ORDER BY PARTITION


10、新建一個存儲過程便於查詢使用：

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Qun]
(
@QunNum int
)
AS
SELECT a.[QunNum]
,[QQNum]
,[Nick]
,[Age]
,(case when [Gender]=0 then 男 else 女 end) 性別
,(case when [Auth]=1 then 成員 when [Auth]=4 then 群主 when [Auth]=2 then 管理 end) 身份
,[MastQQ]
,[CreateDate]
,[Title]
,[Class]
,[QunText]
FROM [GroupData].[dbo].[Group] a
left join [QunInfo].[dbo].[QunList] b
on a.[QunNum]=b.QunNum
where a.[QunNum]=@QunNum order by [Auth] desc
GO

use GroupData
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Qun]
(
@QunNum int
)
AS
SELECT a.[QunNum]
,[QQNum]
,[Nick]
,[Age]
,(case when [Gender]=0 then 男 else 女 end) 性別
,(case when [Auth]=1 then 成員 when [Auth]=4 then 群主 when [Auth]=2 then 管理 end) 身份
,[MastQQ]
,[CreateDate]
,[Title]
,[Class]
,[QunText]
FROM [GroupData].[dbo].[Group] a
left join [QunInfo].[dbo].[QunList] b
on a.[QunNum]=b.QunNum
where a.[QunNum]=@QunNum order by [Auth] desc
GO
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[QQ]
(
@QQNum int
)
AS
SELECT a.[QunNum] 群號碼
,[Title] 群名
,[QQNum] QQ號碼
,[Nick] 昵稱
,[Age]
,(case when [Gender]=0 then 男 else 女 end) 性別
,(case when [Auth]=1 then 成員 when [Auth]=4 then 群主 when [Auth]=2 then 管理 end) 身份
,[MastQQ]
,[CreateDate]
,[QunText]
FROM [GroupData].[dbo].[Group] a
left join [QunInfo].[dbo].[QunList] b
on a.[QunNum]=b.QunNum
where [QQNum]=@QQNum order by CreateDate
GO
USE [QunInfo]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[QQ]
(
@QQNum int
)
AS
SELECT a.[QunNum] 群號碼
,[Title] 群名
,[QQNum] QQ號碼
,[Nick] 昵稱
,[Age]
,(case when [Gender]=0 then 男 else 女 end) 性別
,(case when [Auth]=1 then 成員 when [Auth]=4 then 群主 when [Auth]=2 then 管理 end) 身份
,[MastQQ]
,[CreateDate]
,[QunText]
FROM [GroupData].[dbo].[Group] a
left join [QunInfo].[dbo].[QunList] b
on a.[QunNum]=b.QunNum
where [QQNum]=@QQNum order by CreateDate
GO
USE GroupData
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[QQ]
(
@QQNum int
)
AS
SELECT a.[QunNum] 群號碼
,[Title] 群名
,[QQNum] QQ號碼
,[Nick] 昵稱
,[Age]
,(case when [Gender]=0 then 男 else 女 end) 性別
,(case when [Auth]=1 then 成員 when [Auth]=4 then 群主 when [Auth]=2 then 管理 end) 身份
,[MastQQ]
,[CreateDate]
,[QunText]
FROM [GroupData].[dbo].[Group] a
left join [QunInfo].[dbo].[QunList] b
on a.[QunNum]=b.QunNum
where [QQNum]=@QQNum order by CreateDate
GO


以後，查詢一個群的資料跑這條腳本就行了：

exec Qun [群號碼]

查詢一個QQ好的腳本：

exec QQ [QQ號碼]

結果都是秒出。

如圖：

接下來可以右鍵刪除這22個庫了：QunInfo01~11、GroupData01~11，只留下QunInfo庫和GroupData庫。

以上。