T-SQL-数据库（一）学习

骑猪看日落 2021-09-28 18:04 410阅读 0赞

*  创建数据库
 *  修改数据库
 *  删除数据库
 *  数据库的分离和附加
 *  数据库的扩大和收缩

**一：创建数据库**

（1）一个逻辑文件一个日志文件，还创建了一个文件组A\_Group，里面有一个文件

--创建数据库A
    CREATE DATABASE A
    --关键字表示数据库是根据后面的参数来创建的
    ON
    --指定主文件
    --主文件组包含所有数据库系统表,还包含所有未指派给用户文件组的对象
    --主文件组的第一个文件被认为是主数据文件。如果没有PRIMARY项,则在CREATE DATABASE命令中列出的第一个文件将被默认为主文件
    PRIMARY
    (
    --为该文件指定逻辑名称
    NAME=A_DATA,
    --为该文件指定在操作系统中存储的路径名和物理文件名称，注意后缀是MDF
    FILENAME='F:\sqla\A.MDF',
    --指定数据库的初始容量大小，至少要3MB才行
    SIZE=5,
    --指定文件的最大容量。如果没有指定 max_size,则文件可以不断增长直到充满磁盘
    --Unlimited表示文件无容量限制
    MAXSIZE=Unlimited,
    --指定文件每次增容时增加的容量大小，如下面的自动增长量为10%
    FILEGROWTH=10%
    ),
    --创建文件组
    FILEGROUP A_Group
    (
    NAME=A_Group,
    FILENAME='F:\sqla\Ag.NDF',
    SIZE=10,
    MAXSIZE=Unlimited,
    FILEGROWTH=3
    )
    --创建文件组
    
    --指明事务日志文件的明确定义。
    --如果没有 LOG ON选项,则系统会自动产生一个文件名前缀与数据库名相同,大小为数据库中所有数据文件总大小25%的事务日志文件
    LOG ON
    (
    NAME=A_LOG,
    --注意后缀是LDF
    FILENAME='F:\sqla\A.LDF',
    SIZE=1,
    MAXSIZE=10,
    FILEGROWTH=2
    )

![20190528173249509.png][]

（2）多个逻辑文件和多个日志文件，如果没有使用关键字PRIMARY，则第一个文件成为主数据文件，文件组也是可以创建多个的，注意用逗号隔开，最后一个不要逗号

--创建数据库A
    CREATE DATABASE A
    --关键字表示数据库是根据后面的参数来创建的
    ON
    --指定主文件
    --主文件组包含所有数据库系统表,还包含所有未指派给用户文件组的对象
    --主文件组的第一个文件被认为是主数据文件。如果没有PRIMARY项,则在CREATE DATABASE命令中列出的第一个文件将被默认为主文件
    PRIMARY
    (
    --为该文件指定逻辑名称
    NAME=A_DATA,
    --为该文件指定在操作系统中存储的路径名和物理文件名称，注意后缀是MDF
    FILENAME='F:\sqla\A.MDF',
    --指定数据库的初始容量大小，至少要3MB才行
    SIZE=5,
    --指定文件的最大容量。如果没有指定 max_size,则文件可以不断增长直到充满磁盘
    --Unlimited表示文件无容量限制
    MAXSIZE=Unlimited,
    --指定文件每次增容时增加的容量大小，如下面的自动增长量为10%
    FILEGROWTH=10%
    ),
    (
    NAME=B_DATA,
    FILENAME='F:\sqla\B.NDF',
    SIZE=5,
    MAXSIZE=Unlimited,
    FILEGROWTH=10%
    )
    --指明事务日志文件的明确定义。
    --如果没有 LOG ON选项,则系统会自动产生一个文件名前缀与数据库名相同,大小为数据库中所有数据文件总大小25%的事务日志文件
    LOG ON
    (
    NAME=A_LOG,
    --注意后缀是LDF
    FILENAME='F:\sqla\A.LDF',
    SIZE=1,
    MAXSIZE=10,
    FILEGROWTH=2
    ),
    (
    NAME=B_LOG,
    FILENAME='F:\sqla\B.LDF',
    SIZE=1,
    MAXSIZE=10,
    FILEGROWTH=2
    )

引用（只是简单了解一下）：

文件组是什么？

文件组就是一堆文件的一个总称，就像黑种人，白种人，黄种人，是一堆人的总称，但以颜色来标识，这就是逻辑上的分类，文件组也是一样只是逻辑上的分组，一个数据库可以看成很多个文件构成一个逻辑空间，它的数据越存越多，加入新的文件就越来越多，这些文件就可以作为一个组来管理。

文件和文件组的含义与关系：

每个数据库有一个主数据文件.和若干个从文件。文件是数据库的物理体现。 文件组可以包括分布在多个逻辑分区的文件，实现负载平衡。文件组允许对文件进行分组，以便于管理和数据的分配／放置。例如，可以分别在三个硬盘驱动器上创建三个文件（Data1.ndf、Data2.ndf和Data3.ndf），并将这三个文件指派到文件组 fgroup1 中。然后，可以明确地在文件组fgroup1 上创建一个表。对表中数据的查询将分散到三个磁盘上，因而性能得以提高。在RAID（磁盘冗余阵列)条带集上创建单个文件也可以获得相同的性能改善。然而，文件和文件组使您得以在新磁盘上轻易地添加新文件。另外，如果数据库超过单个Microsoft Windows 文件的最大大小，则可以使用次要数据文件允许数据库继续增长。

文件、文件组在实践应用中常见的问题：

1、容易导致文件过大。

我们知道，mdf文件是数据库文件，这样的话也就意味着随着数据库的增大mdf就会相应的增大，显然在现在的应用中数据膨胀是太常见的事情了，当你的应用变大后，mdf文件也会变大，然而windows对文件的大小是有要求的，这样的话很容易导致mdf文件达到windows所允许的文件大小的界限(于是数据库就崩溃了)。

2、没有利用到磁盘阵列。

大型的服务器好多都有磁盘阵列，你可以把磁盘阵列简单的假象成n个一块转动的磁盘，磁盘阵列的设计是希望通过多个磁盘的串联来得到更大的读写效率。但是如果你的数据库只有一个mdf文件(ldf文件暂时不考虑)，那么你总是只能够利用这个磁盘阵列里面的一个磁盘而已。那样的话昂贵的磁盘阵列的效率就由并联变成串联了。试想如果我们能够让mdf分散成多个文件，比如说磁盘阵列上的每个磁盘中都分配一个文件，然后把mdf中的数据分散到各个文件中，我在读取的时候就是串联的读取了，这样就充分的利用了磁盘阵的存取效能。

附两篇很好的文章

[https://www.cnblogs.com/kissdodog/p/3156166.html][https_www.cnblogs.com_kissdodog_p_3156166.html]

[https://www.cnblogs.com/weiguoaa/p/9054347.html][https_www.cnblogs.com_weiguoaa_p_9054347.html]

**二：修改数据库**

使用ALTER DATABASE命令，可以在数据库中添加，修改，删除文件或者文件组（**注意：这里的删除不是删除数据库**）

（1）向数据库中添加文件

ALTER DATABASE A
    --指定要添加的文件
    ADD FILE
    (
    NAME=C_DATA,
    FILENAME='F:\sqla\C.NDF',
    SIZE=5MB,
    MAXSIZE=30MB,
    FILEGROWTH=2MB
    )

（2）向数据库中添加由两个文件组成的文件组

ALTER DATABASE A
    --指定要添加的文件组
    ADD FILEGROUP B_Group
    GO
    ALTER DATABASE A
    --指定要添加的文件
    ADD FILE
    (
    NAME=B_Group,
    FILENAME='F:\sqla\Bg.NDF',
    SIZE=5MB,
    MAXSIZE=30MB,
    FILEGROWTH=2MB
    ),
    (
    NAME=C_Group,
    FILENAME='F:\sqla\Cg.NDF',
    SIZE=5MB,
    MAXSIZE=30MB,
    FILEGROWTH=2MB
    )
    --指定要将文件添加到的文件组
    TO FILEGROUP B_Group
    GO
    ALTER DATABASE A
    --指定要修改的文件组和所需的改动，这里是将文件组属性改为(默认)'DEFAULT' 
    MODIFY FILEGROUP B_Group DEFAULT
    GO

![20190528175315680.png][]

GO不是 [Transact-SQL][] 语句，而是 osql 和 isql 实用工具及 SQL Server 查询分析器才能识别的命令，它用来通知执行GO之前的一个或者多个SQL语句

GO命令和T-SQL语句不能在同一行

由于Create table 语句不能在同一批处理中与其他语句同时编译，因此使用GO将Use语句和Create table语句分隔到两个批处理中（**我的理解就是比如你要创建一个文件组a，又要添加文件到a中，可是如果a没有创建，那就无法执行把文件添加到a中，意思就是执行了上面的语句之后才能执行下面的语句**）

（3）向数据库中添加日志文件（可以多个，逗号隔开就行）

ALTER DATABASE A
    --指定要将文件添加到的文件组
    ADD LOG FILE
    (
    NAME=B_LOG,
    FILENAME='F:\sqla\B.LDF',
    SIZE=5MB,
    MAXSIZE=30MB,
    FILEGROWTH=2MB
    )
    GO

（4）修改现有文件（下面是A\_Data文件，不分大小写）的容量,改为11MB

**注意：修改的时候指定容量的大小要大于当前容量的大小**

ALTER DATABASE A
    --指定要更改给定的文件及文件属性
    MODIFY FILE
    (
    NAME=A_Data,
    SIZE=11MB
    )

（5）修改数据库文件名称

ALTER DATABASE A
    --重命名数据库
    MODIFY NAME=B

注意：在对数据库的名称进行修改前,应保证当前没有人使用该数据库,同时将要修改名称的数据库的访问选项设为单用户模式(single user mode)并关闭数据库。

（6）还有其他语法

REMOVE FILE 名字：从系统中删除文件描述和物理文件

REMOVE  FILEGROUP 名字：从系统中删除文件组

**三：删除数据库**

（1）比如删除H和F数据库（逗号隔开就行）

DROP DATABASE H,F

**四：数据库的分离和附加**

SQL Server 2008允许分离数据库的数据文件和事务日志文件,然后将其重新附加到同一台或另一台服务器上

（1）数据库的分离

数据库的分离就是将用户的数据库从 SQL Server的列表中删除,即从 SQL Server服务器中分离出来,但是保持组成该数据的数据文件和事务日志文件中的数据完好无损,即数据库文件仍保留在磁盘上。在实际工作中,分离数据库作为对数据库的一种备份来使用

Use A
    Go
    sp_detach_db 'A'
    Go

效果：数据库删除了，但在系统里面我发现那些文件还在

注意：我用语句删除不了，说什么无法分离 数据库'A'，因为它当前正在使用。也没去查怎么回事，我直接用这种方法

![20190528225440106.png][]

直接点确定

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjcxNzkyOA_size_16_color_FFFFFF_t_70][]

这样是不行的，因为有活动在链接，要这样做

![2019052822571514.png][]

（2）数据库的附加

附加数据库的工作是分离数据库的逆操作,通过附加数据库,可以将没有加入 SQL Server服务器的数据库文件添加到服务器中。还可以很方便地在 SQL Server服务器之间利用分离后的数据文件和事务日志文件组成新的数据库,即附加数据库时对数据库进行更名。

（1）将A数据库附加到SQL Server服务器中

Use master
    Go
    CREATE DATABASE A
    ON(FILENAME='F:\sqlb\A.MDF')
    FOR ATTACH
    Go

如果不用Use master Go，就是又恢复成原来的样子

**五：数据库的扩大和收缩**

由于 SQL Server 2008对数据库空间分配采取“先分配、后使用”的机制,所以在 SQL Server 2008系统中,如果数据库的数据量不断膨胀,可以根据需要扩大数据库的大小。同理,对于数据库的设计过大,或者删除了数据库中的大量数据,这时数据库会白白耗费大量的磁盘资源。则可使用 SQL Server2008提供的收缩数据库功能,对数据库进行收缩,还可以对数据库中的每个文件进行收缩,直至收缩到没有剩余的可用空间为止。

（1）数据库的扩大

扩大数据库空间有3种方法:

①设置数据库为自动增长方式,可以在创建数据库时设置

②直接修改数据库的数据文件或日志文件的大小

③在数据库中添加新的次要数据文件或日志文件

（2）数据库的收缩

①对数据库设置，可以按照指定的间隔自动收缩

②也可以手动去收缩

[20190528173249509.png]: /images/20210923/adf96eadd1944c1780f54e346c0c4673.png
[https_www.cnblogs.com_kissdodog_p_3156166.html]: https://www.cnblogs.com/kissdodog/p/3156166.html
[https_www.cnblogs.com_weiguoaa_p_9054347.html]: https://www.cnblogs.com/weiguoaa/p/9054347.html
[20190528175315680.png]: /images/20210923/71f7cb7dd95d474faf2582a9594a1599.png
[Transact-SQL]: https://www.baidu.com/s?wd=Transact-SQL&tn=SE_PcZhidaonwhc_ngpagmjz&rsv_dl=gh_pc_zhidao
[20190528225440106.png]: /images/20210923/0d8533565dff42df8853de671a39bca2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjcxNzkyOA_size_16_color_FFFFFF_t_70]: /images/20210923/0fdcb7f419544be6bee3f0ddfa8517fd.png
[2019052822571514.png]: /images/20210923/10af33b7308e47d499e463e5a5444f85.png