sql 数据库的一些基本操作

--普通数据库
create database temp_test
on(
    name = temp_test,
    filename = 'E:\web\sql\sqlserver\testdatabase.mdf',
    size = 6,
    maxsize = 20,
    filegrowth = 10%
)
log on(
    name = temp_test_log,
    filename = 'E:\web\sql\sqlserver\testdatabase.ldf',
    size = 2MB,
    maxsize = 10MB,
    filegrowth = 1MB
)

go

--带有多个文件和文件组的的数据库
create database temp_test2
on primary(
    name = temp_test2,
    filename = 'E:\web\sql\sqlserver\testdatabase2.mdf',
    size = 6,
    maxsize = 20,
    filegrowth = 10%
),

filegroup testdatabasegroup
(
    name = testgroup,
    filename = 'E:\web\sql\sqlserver\testgroup.mdf',
    size = 6,
    maxsize = 20,
    filegrowth = 5
),
(
    name = testgroup2,
    filename = 'E:\web\sql\sqlserver\testgroup2.mdf',
    size = 5,
    maxsize = 30,
    filegrowth = 5
)

log on(
    name = testlog,
    filename = 'E:\web\sql\sqlserver\testdatabase2.ldf',
    size = 3MB,
    maxsize = 20MB,
    filegrowth = 1MB
)

go

--数据库选项
alter database temp_test
set
    recovery full,
    page_verify checksum
go

--查看数据库的基本信息
select * from sys.databases

--查看有关数据库的文件信息
select * from sys.database_files

--查看数据库文件组信息
select * from sys.filegroups

--查看数据库文件的基本信息和状态信息
select * from sys.master_files

--查看指定的数据库属性
select N'数据库的恢复模式' = databasePropertyEX('master' , 'Recovery')

go

--查看数据库空间使用状况
sp_spaceused
go

--使用存储过程　sp_helpdb  查看数据库
sp_helpdb master
go

use temp_test

--修改数据库名 但不推荐修改  很多应用程序都使用了数据库名称  一旦修改  这些应用就都失效了
alter database temp_test
modify
    name = temp_test
go

--为数据库添加新的数据文件来扩充数据库
alter database temp_test2
add file
(
    name = temp_test_add,
    filename = 'E:\web\sql\sqlserver\temp_test_add.ldf',
    size = 3,
    maxsize = 5,
    filegrowth = 10%
)
to filegroup testdatabasegroup
go
--to filegroup 表示把文件加入到指定的文件组内

--修改数据库文件大小来扩充数据库
alter database temp_test
modify
    file(
        name = temp_test_log,
        size = 8
    )
go
--为了避免数据的丢失  此方法修改文件的大小必须确保修改后的文件大小必须大于当前文件大小  否则出错

--有时候数据库设计尺寸过大 或者刚删除完大量的数据  那么数据文件大小就应收缩 以减少磁盘的消耗
--错误做法
alter database temp_test
modify
    file(
        name = temp_test_log,
        size = 8
    )
--这样做是不允许的  理由见上

--正确做法
--１．设置数据库文件自动收缩以减少数据库的大小

alter database temp_test
set auto_shrink on

--２．收缩整个数据库的大小
--函数参数说明: 第一参数为收缩数据库名 第二参数为收缩目标比例(不一定能达到)
dbcc shrinkdatabase (temp_test, 10)

--３．收缩指定的数据库文件大小
dbcc shrinkfile(temp_test_log, 4)

go

--修改文件组设置
use temp_test2

alter database temp_test2
modify filegroup  testdatabasegroup read_only

go

--创建数据库快照
--Express Edition (64-bit) 不支持 Database Snapshot。
use temp_test2

create database temp_snapshot
on
(
    name = temp_test_snapshot,
    filename = 'E:\web\sql\sqlserver\temp_test_snapshot.snp'
)
as snapshot of temp_test2

go

--分离数据库指把数据库从 sql server 数据库实例中删除  但保留所有数据库的文件
--分离的的数据库随时可以附加回数据库实例中
--分离数据库
sp_detach_db temp_test

--附加分离的数据库
create database temp_test
on
(
    filename = 'E:\web\sql\sqlserver\testdatabase.mdf'
)
for attach

go


if DB_ID('temp_test') is not null
begin
    drop database temp_test
end

if DB_ID('temp_test2') is not null
begin
    drop database temp_test2
end