SQL数据库基础知识

/* 创建没有主数据文件和日志文件的数据库*/

 

use master

go

 

if db_id( 'sales') is not null

drop database sales

go

 

create database sales

go

 

/*

 自定义创建数据库

*/

use master

go

 

if db_id('sales') is not null

drop database sales

go

 

create database sales

on               --创建主数据文件

(

    name = sales_dat ,--逻辑名称

    filename='d:\sales\sales.mdf',--物理路径

    size=10MB,          --初始化大小,单位默认为MB

    maxsize =50,          --数据库的最大容量

    filegrowth=5MB         --文件的增长速度

)

log on

(

     name= sales_log,

     filename='d:\sales\sales.ldf',

     size=5MB,

     maxsize=25,

     filegrowth=5

)

/*    

无限制数据

*/

 

use master

go

if db_id('sales') is not null

drop database sales

go

create database sales

on

(

       name = sales_dat,

       filename = 'd:\database\sales.mdf',

       size = 10mb,

       maxsize = unlimited,

       filegrowth = 5mb

)

log on

(

       name = sales_log,

       filename = 'd:\\database\\sales_log.ldf',

       size = 5mb,

       maxsize = 25,

       filegrowth = 5%

)

--两个日志和两个文件

if db_id('employees') is not null

drop database employees

go

create database employees

on

(

       name = employee1_dat,

       filename = 'd:\database\employee1.mdf',

       size = 25mb,

       maxsize = 50,

       filegrowth = 5

), 

(

       name = employee2_dat,

       filename = 'd:\database\employee2_dat.ndf',

       size  = 10,

       maxsize = 50,

       filegrowth = 10%

)

log on

(

       name = employee1_log,

       filename = "d:\database\employee1_log.ldf",

       size = 15,

       filegrowth = 5

),

(

       name = employee2_log,

       filename = 'd:\database\employee2_log.ldf',

       size = 15,

       filegrowth = 5

)
--自定义文件组

if db_id('employees') is not null

drop database employees

go

create database employees

on

(

       name = employee1_dat,

       filename = 'd:\database\employee1.mdf',

       size = 25mb,

       maxsize = 50,

       filegrowth = 5

),

(

       name = employee2_dat,

       filename = 'd:\database\employee2_dat.ndf',

       size  = 10,

       maxsize = 50,

       filegrowth = 10%

),

filegroup GroupDB1

(

       name = employee3_dat,

       filename = 'd:\database\employee3_dat.ndf',

       size = 25mb,

       maxsize = 50,

       filegrowth = 5

),

(

       name = employee4_dat,

       filename = 'd:\database\employee4_dat.ndf',

       size  = 10,

       maxsize = 50,

       filegrowth = 10%

),

filegroup GroupDB2

(

       name = employee5_dat,

       filename = 'd:\database\employee5_dat.ndf',

       size = 25mb,

       maxsize = 50,

       filegrowth = 5

),

(

       name = employee6_dat,

       filename = 'd:\database\employee6_dat.ndf',

       size  = 10,

       maxsize = 50,

       filegrowth = 10%

)

log on

(

       name = employee1_log,

       filename = "d:\database\employee1_log.ldf",

       size = 15,

       filegrowth = 5

),

(

       name = employee2_log,

       filename = 'd:\database\employee2_log.ldf',

       size = 15,

       filegrowth = 5

)

 

 

--删除数据库

use master --设置当前数据库为master，以便访问sysdatabases表

go

if exists(select * from sysdatabases where name = 'employees')

drop database employees

go

 

if db_id('employees') is not null

drop database employees

go

 

/*

  查看数据库的信息,利用系统存储过程.

   (存储过程分为两类:系统存储过程和用户自定义存储过程)

  利用execute(执行) 调用存储过程,系统的存储过程以sp_开头,系统的存储过程

  可以省略execute 关键字.

*/

execute sp_databases 

--查看当前服务器上所有可以使用的数据库的信息

execute sp_helpdb      --查看数据库的详细信息

execute sp_helpdb 'employees' --后边增加参数(数据库的名称),                        --可以查看指定数据库的详细信息

use  employees

go

exec sp_helpfile   --查看指定库的系统文件的信息

exec Sp_helpfilegroup    --查看当前数据库上所有文件组

 

 

 

/*

   修改数据库

 

*/

exec sp_renamedb 'sales','mysales' --修改数据库的名称

 

Add file <filespec> [,. . . n][TO FILEGROUP FILEGROUP_NAME]:

表示向指定的文件组里增加新的数据文件

  Add log file <filespec>[, . . . n]: 增加新的日志文件

Remove file logical_file_name: 删除某一操作系统文件.

 Add filegroup filegroup_name : 增加某一文件组。

 Modify file<filespec>:修改某操作系统文件的属性.

 

alter database mysales

modify file

(

    name=sales_dat,

    size=20MB     --根据逻辑名称进行存储数据的修改

)

go

sp_helpdb 'mysales'

alter database mysales  

add file                    -- 增加数据文件

(

    name=sales_ndat,

    filename='d:\sales\sales.ndf',   --要求我们的文件夹路径必须存在

    maxsize=50MB,

    size=10MB,                      --初始化大小

    filegrowth=5MB

)

sp_helpfile

 

use employees

sp_helpfile

use master

go

 

alter database employees

add file

(

       name = employee8_dat,

       filename = 'd:\database\employee8_dat.ndf',

       size = 20mb,

       filegrowth = 25

)

to filegroup groupdb1

 

 

alter database employees

add log file

(

       name = employee3_log,

       filename = 'd:\database\employee3_log.ldf',

       size = 20mb,

       filegrowth = 25

)

 

 

alter database employees

add filegroup groupdb3

 

alter database employees

remove filegroup groupdb3

 

sp_helpfilegroup 'groupdb3'

 

alter database employees

remove file employee8_dat

 

alter database employees

remove file employee3_log

/*

   修改数据库的选项

*/

exec sp_helpdb

--1.设置数据库为只读数据库

 

exec sp_dboption 'mysales','read only','true'

exec sp_dboption 'mysales','read only','false'

 

--2.设置数据库为自动压缩

exec sp_dboption 'mysales','autoshrink','true'

 

--3.设置数据库为单用户数据库

 exec sp_dboption 'mysales','single user','true'

 exec sp_dboption 'mysales','single user','false'

--4. 收缩我们的数据库

  dbcc shrinkdatabase ('mysales',10)

dbcc shrinkfile('mysales',5)

  --收缩数据库mysales,留下%的可利用空间.

 

/*

   数据库的分离和附加

*/

 

--分离

 

   exec sp_detach_db 'mysales'

 

--附加

 

  exec sp_attach_db @dbname='mysales',   --数据库的名称

            @filename1='c:\sales\sales.mdf', --数据库的文件物理路径

            @filename2='c:\sales\sales.ndf',

            @filename3='c:\sales\sales.ldf'

 

use master

go

exec sp_detach_db 'sales'

exec sp_attach_db 'sales', 'd:\database\sales.mdf','d:\database\sales_log.ldf'

--northwind数据库的分离和附加

 

exec sp_attach_db 'Northwind','d:\database\northwnd.mdf','d:\database\northwnd.ldf'

exec sp_detach_db 'Northwind'

  将某些数据库行为设置为与指定的 Microsoft® SQL Server™ 的早期版本兼容。

语法

sp_dbcmptlevel [ [ @dbname = ] name ]
    [ , [ @new_cmptlevel = ] version ]

参数

[@name =] name

数据库的名称，将对此数据库的兼容级别进行更改。数据库的名称必须遵循标识符的规则。name 的数据类型为 sysname，默认值为 NULL。

[@new_cmptlevel =] version

数据库要兼容的 SQL Server 版本。version 的数据类型为 tinyint，默认值为 NULL。该值必须为 80、70、65 或 60。

说明  70 和 80 两个级别间的唯一区别是，级别 70 不支持 SQL Server 2000 中引入的一些保留关键字。

返回代码值

0（成功）或 1（失败）

结果集

如果没有指定参数或没有指定 name 参数，sp_dbcmptlevel 将返回下面的消息：

Valid values of database compatibility level are 60, 65, 70, or 80.

如果指定了 version 而没有指定 name，SQL Server 将显示一条有关该数据库的兼容性设置的消息。

注释

在 SQL Server 2000 中，master 数据库的兼容级别为 80，此级别不能改变。

对于安装 SQL Server 2000 的所有实例，所有数据库的默认级别为 80。对于从 SQL Server 7.0 升级到 SQL Server 2000，所有数据库的默认级别为 80。对于从 SQL Server 6.5 和 SQL Server 6.0 升级到 SQL Server 2000，保持现有默认兼容级别。

用 sp_dbcmptlevel 提供临时迁移帮助。由 sp_dbcmptlevel 的兼容级别设置控制的 SQL Server 7.0 版或 SQL Server 2000 行为中的不同如果影响了现有 SQL Server 6.x 版应用程序，请使用此过程设置早期版本行为，直到能够用 SQL Server 2000 兼容级别将该应用程序转变为正常运行为止。sp_dbcmptlevel 不能还复完全向后兼容。

sp_dbcmptlevel 将影响指定数据库的行为，但不会影响整个服务器。当在 USE 语句中将数据库作为当前数据库，或者当数据库作为登录的默认数据库时，数据库的兼容性设置将生效。当执行存储过程时，将使用在其中定义过程的数据库的当前兼容性级别。当此数据库中的兼容性设置更改时，所有存储过程将重新编译。

60 = SQL Server 6.0

65 = SQL Server 6.5

70 = SQL Server 7.0

80 = SQL Server 2000

90 = SQL Server 2005

 

use master

go

exec dbo.sp_dbcmptlevel 'northwind',90

go

 

更改当前数据库的所有者。

语法

sp_changedbowner [ @loginame = ] 'login'
    [ , [ @map = ] remap_alias_flag ]

参数

[@loginame =] 'login'

当前数据库新所有者的登录 ID。login 的数据类型为 sysname，没有默认值。login 必须是已存在的 Microsoft® SQL Server™ 登录或 Microsoft Windows NT® 用户。如果 login 通过当前数据库内的现有别名或用户安全帐户已拥有访问该数据库的权限，则不能成为该数据库的所有者。为了避免这种情况，应先除去当前数据库中的别名或用户。

[@map =] remap_alias_flag

值为 true 或 false，表示旧数据库所有者 (dbo) 的现有别名是映射到当前数据库的新所有者还是要除去。remap_alias_flag 的数据类型为 varchar(5)，默认值为 NULL，表示旧 dbo 的任何现有别名均映射到当前数据库的新所有者。false 表示除去旧数据库所有者的现有别名。

返回代码值

0（成功）或 1（失败）

注释

执行 sp_changedbowner 之后，新所有者称为数据库中的 dbo 用户。dbo 拥有执行数据库中所有活动的暗示性权限。

不能更改 master、model 或 tempdb 系统数据库的所有者。

若要显示有效 login 值的列表，请执行 sp_helplogins 存储过程。

执行只有 login 参数的 sp_changedbowner 会将数据库所有权改为 login，并将先前别名为 dbo 的用户别名映射到新数据库所有者。

权限

只有 sysadmin 固定服务器角色成员的成员或当前数据库的所有者才能执行 sp_changedbowner。

示例

下面的示例使用户 Albert 成为当前数据库的所有者，并将旧数据库所有者的现有别名映射到 Albert。

EXEC sp_changedbowner 'Albert'

 

use northwind

exec   sp_changedbowner   'sa'

 

/*

   数据库的备份(完全备份)

*/

 

--(1)添加备份设备

   use master

   go

 

   exec sp_addumpdevice 'disk','mybackup','c:\backup\backup.bak'

     --'disk'进行磁盘备份

     -- mybackup ，备份的逻辑名称

     --'c:\backup\backup.bak' 备份的物理路径。

--(2)将数据库备份写入备份设备

  backup database mysales to mybackup

 

 --(3)还原备份数据库

  restore database mysales from mybackup

 --(4)删除备份设备

  exec sp_dropdevice 'mybackup'

 

转载于:https://www.cnblogs.com/Wolfmanlq/archive/2012/08/30/2663606.html