SQL SERVER 基础语法一

sqlserver存储结构
数据库命名规则
数据库分类
create database
修改数据库名称
添加辅助文件
删除数据库
数据库快照
查看数据库状态
分离数据库
附加数据库
收缩数据库

1.sql server物理存储结构

主数据文件：*.mdf （有且只有一个）

辅助数据文件：*.ndf　　(任意)

日志文件：*.ldf　　　　(一个或以上)

2.数据库命令规则：

第一个字母必须是字母或“_”,"@","#"

数据库名称不能是T-SQL保留字

不允许嵌入空格或其它特殊字符

3.数据库的分类

系统数据库:master model msdb tempdb resource

用户数据库

4.Create database

创建数据库

create database TEST
ON PRIMARY --主文件组
(
	NAME=Test_Data,
	FILENAME='/var/opt/mssql/data/Test_Data.mdf',
	SIZE=100MB,
	MAXSIZE=200MB,
	FILEGROWTH=15%
),
(
	NAME=Test_Data1,
	FILENAME='/var/opt/mssql/data/Test_Data1.ndf',
	SIZE=50MB,
	MAXSIZE=100MB,
	FILEGROWTH=15%
)
LOG ON
(
	NAME=Test_Log,
	FILENAME='/var/opt/mssql/data/Test_Log.ldf',
	SIZE=20MB,
	MAXSIZE=50MB,
	FILEGROWTH=10%
),
(
	NAME=Test_Log1,
	FILENAME='/var/opt/mssql/data/Test_Log1.ldf',
	SIZE=10MB,
	MAXSIZE=30MB,
	FILEGROWTH=8%
)
GO

上面的代码中

ON PRIMARY 指明是主数据文件

NAME 创建的文件名称

FILENAME 文件路径

SIZE指定初始大小

MAXSIZE指定文件最大值

FILEGROWTH 指定文件的增长方式是按照百分比增长。

如果要写多个文件组

需要用FILEGROUP dbgroup_name指定多文件组

示例如下

create database TEST
ON PRIMARY --主文件组
(
	NAME=Test_Data,
	FILENAME='/var/opt/mssql/data/Test_Data.mdf',
	SIZE=100MB,
	MAXSIZE=200MB,
	FILEGROWTH=15%
),
(
	NAME=Test_Data1,
	FILENAME='/var/opt/mssql/data/Test_Data1.ndf',
	SIZE=50MB,
	MAXSIZE=100MB,
	FILEGROWTH=15%
),
FILEGROUP DBGROUP_1
(
	NAME=Test_group1,
	FILENAME='/var/opt/mssql/data/Test_group1.ndf',
	SIZE=50MB,
	MAXSIZE=100MB,
	FILEGROWTH=15%
),
(
	NAME=Test_group2,
	FILENAME='/var/opt/mssql/data/Test_group2.ndf',
	SIZE=50MB,
	MAXSIZE=100MB,
	FILEGROWTH=15%
),
FILEGROUP DBGROUP_2
(
	NAME=Test_group3,
	FILENAME='/var/opt/mssql/data/Test_group3.ndf',
	SIZE=50MB,
	MAXSIZE=100MB,
	FILEGROWTH=15%
),
(
	NAME=Test_group4,
	FILENAME='/var/opt/mssql/data/Test_group4.ndf',
	SIZE=50MB,
	MAXSIZE=100MB,
	FILEGROWTH=15%
)
LOG ON
(
	NAME=Test_Log,
	FILENAME='/var/opt/mssql/data/Test_Log.ldf',
	SIZE=20MB,
	MAXSIZE=50MB,
	FILEGROWTH=10%
),
(
	NAME=Test_Log1,
	FILENAME='/var/opt/mssql/data/Test_Log1.ldf',
	SIZE=10MB,
	MAXSIZE=30MB,
	FILEGROWTH=8%
)
GO

注意:

只有数据文件具有文件组，日志文件不存在文件组

主数据文件一定存放在主文件组中

与系统相关的数据信息一定存放在主文件组中

一个数据文件只能存放于一个文件组中，不能同时存放于多个文件组中

5.修改数据库名称

使用语句

(1)方法一

ALTER DATABASE ...MODIFY.....

alter database database_name modify NAME=newdatabase_name

(2)方法二

使用系统存储过程修改

sp_renamedb存储过程也可以修改数据库名称

EXEC sp_dboption 'TEST','SINGLE',true
EXEC sp_renamedb 'TEST','NEWTEST'
EXEC sp_dboption 'TEST','SINGLE',false

6.添加辅助文件

使用ADD FILE语句

ALTER DATABASE TEST
ADD FILE
(
        NAME=Test_group4,
	FILENAME='/var/opt/mssql/data/Test_group4.ndf',
	SIZE=50MB,
	MAXSIZE=100MB,
	FILEGROWTH=15%    
)

7.删除数据库

DROP DATABASE TEST

注：使用DROP删除数据库不会出现确认信息。

8.数据库快照

数据库快照像数据库在某一时刻照的照片。如果数据库损坏了，可以通过快照来恢复到源数据库。

(1)创建数据库快照

create database database_snapshot_name
ON
(
     NAME=snapshot_name,
     FILENAME='path.mdf'          
)
AS SNAPSHOT OF database_name

使用示例如下

create database test_快照1
ON
(
     NAME=TEST_1,
     FILENAME='/var/opt/mssql/data/TEST_1.mdf'          
)
AS SNAPSHOT OF TEST

(2)使用数据库快照

使用语法

RESTORE DATABASE database_name FROM DATABASE_SNAPSHOT=database_snapshot_name

使用如下:

RESTORE DATABASE TEST FROM DATABASE_SNAPSHOT=test_快照1
GO

需要汪意的是，因为需要恢复的是当前数据库，所以，不能使用当前的数据库。

即不能use TEST

最好在使用快照恢复时使用master

use master

然后再使用快照，才不会报错。

(3)删除数据库快照

DROP DATABASE test_快照
GO

9.查看数据库状态

(1).使用目录视图

sys.databse_files 有关数据库文件的信息

sys.filegroups 有关数据库组的信息

sys.master_files 数据库文件的基本信息和状态信息

sys.database 数据库的基本信息

(2)使用函数

DATABASEPROPERTYEX()函数

如

USE TEST
GO
SELECT DATABASEPROPERTYEX('TEST','Status')
AS '当前数据库状态'

属性

说明

返回的值

Collation

数据库的默认排序规则名称。

排序规则名称

NULL = 数据库没有启动。

nvarchar(128)" xml:space="preserve" jquery18206396683571966286="60">基本数据类型：nvarchar(128)

ComparisonStyle

排序规则的 Windows 比较样式。 ComparisonStyle 是通过使用以下值计算得到的位图。

样式	值
忽略大小写	1
忽略重音	2
忽略 Kana	65536
忽略宽度	131072

例如，196609 的默认值是将忽略大小写、忽略 Kana 和忽略宽度选项合并在一起的结果。

返回比较样式。

对所有二进制排序规则均返回 0。

int" xml:space="preserve" jquery18206396683571966286="77">基本数据类型：int

IsAnsiNullDefault

数据库遵循 ISO 规则，允许 Null 值。

1 = TRUE

0 = FALSE

NULL = 输入无效

int" xml:space="preserve" jquery18206396683571966286="83">基本数据类型：int

IsAnsiNullsEnabled

所有与 Null 的比较将取值为未知。

1 = TRUE

0 = FALSE

NULL = 输入无效

int" xml:space="preserve" jquery18206396683571966286="89">基本数据类型：int

IsAnsiPaddingEnabled

在比较或插入前，字符串将被填充到相同长度。

1 = TRUE

0 = FALSE

NULL = 输入无效

int" xml:space="preserve" jquery18206396683571966286="95">基本数据类型：int

IsAnsiWarningsEnabled

如果发生了标准错误条件，则将发出错误消息或警告消息。

1 = TRUE

0 = FALSE

NULL = 输入无效

int" xml:space="preserve" jquery18206396683571966286="101">基本数据类型：int

IsArithmeticAbortEnabled

如果执行查询时发生溢出或被零除错误，则将结束查询。

1 = TRUE

0 = FALSE

NULL = 输入无效

int" xml:space="preserve" jquery18206396683571966286="107">基本数据类型：int

IsAutoClose

在最后一个用户退出后，数据库完全关闭并释放资源。

1 = TRUE

0 = FALSE

NULL = 输入无效

int" xml:space="preserve" jquery18206396683571966286="113">基本数据类型：int

IsAutoCreateStatistics

查询优化器根据需要创建单列统计信息以提高查询性能。

1 = TRUE

0 = FALSE

NULL = 输入无效

int" xml:space="preserve" jquery18206396683571966286="119">基本数据类型：int

IsAutoShrink

可以定期自动收缩数据库文件。

1 = TRUE

0 = FALSE

NULL = 输入无效

int" xml:space="preserve" jquery18206396683571966286="125">基本数据类型：int

IsAutoUpdateStatistics

当查询使用现有统计信息并且该统计信息可能过期时，查询优化器将更新该统计信息。

1 = TRUE

0 = FALSE

NULL = 输入无效

int" xml:space="preserve" jquery18206396683571966286="131">基本数据类型：int

IsCloseCursorsOnCommitEnabled

提交事务时打开的游标已关闭。

1 = TRUE

0 = FALSE

NULL = 输入无效

int" xml:space="preserve" jquery18206396683571966286="137">基本数据类型：int

IsFulltextEnabled

支持对数据库进行全文和语义索引。

1 = TRUE

0 = FALSE

NULL = 输入无效

int" xml:space="preserve" jquery18206396683571966286="143">基本数据类型：int

注意
此属性的值无效。用户数据库始终启用全文搜索。 SQL Server 的未来版本中将删除此列。请不要在新的开发工作中使用此列，并尽快修改当前还在使用任何这些列的应用程序。

IsInStandBy

数据库以只读方式联机，并允许还原日志。

1 = TRUE

0 = FALSE

NULL = 输入无效

int" xml:space="preserve" jquery18206396683571966286="153">基本数据类型：int

IsLocalCursorsDefault

游标声明默认为 LOCAL。

1 = TRUE

0 = FALSE

NULL = 输入无效

int" xml:space="preserve" jquery18206396683571966286="159">基本数据类型：int

IsMergePublished

如果安装了复制，则可以发布数据库表供合并复制。

1 = TRUE

0 = FALSE

NULL = 输入无效

int" xml:space="preserve" jquery18206396683571966286="165">基本数据类型：int

IsNullConcat

Null 串联操作数产生 NULL。

1 = TRUE

0 = FALSE

NULL = 输入无效

int" xml:space="preserve" jquery18206396683571966286="171">基本数据类型：int

IsNumericRoundAbortEnabled

表达式中缺少精度时将产生错误。

1 = TRUE

0 = FALSE

NULL = 输入无效

int" xml:space="preserve" jquery18206396683571966286="177">基本数据类型：int

IsParameterizationForced

PARAMETERIZATION 数据库 SET 选项为 FORCED。

1 = TRUE

0 = FALSE

NULL = 输入无效

IsQuotedIdentifiersEnabled

可对标识符使用英文双引号。

1 = TRUE

0 = FALSE

NULL = 输入无效

int" xml:space="preserve" jquery18206396683571966286="188">基本数据类型：int

IsPublished

如果安装了复制，可以发布数据库表供快照复制或事务复制。

1 = TRUE

0 = FALSE

NULL = 输入无效

int" xml:space="preserve" jquery18206396683571966286="194">基本数据类型：int

IsRecursiveTriggersEnabled

已启用触发器递归触发。

1 = TRUE

0 = FALSE

NULL = 输入无效

int" xml:space="preserve" jquery18206396683571966286="200">基本数据类型：int

IsSubscribed

数据库已订阅发布。

1 = TRUE

0 = FALSE

NULL = 输入无效

int" xml:space="preserve" jquery18206396683571966286="206">基本数据类型：int

IsSyncWithBackup

数据库为发布数据库或分发数据库，并且在还原时不用中断事务复制。

1 = TRUE

0 = FALSE

NULL = 输入无效

int" xml:space="preserve" jquery18206396683571966286="212">基本数据类型：int

IsTornPageDetectionEnabled

SQL Server 数据库引擎检测到因电力故障或其他系统故障造成的不完全 I/O 操作。

1 = TRUE

0 = FALSE

NULL = 输入无效

int" xml:space="preserve" jquery18206396683571966286="218">基本数据类型：int

LCID

排序规则的 Windows 区域设置标识符 (LCID)。

LCID 值（十进制格式）。

int" xml:space="preserve" jquery18206396683571966286="222">基本数据类型：int

Recovery

数据库的恢复模式。

FULL = 完整恢复模式

BULK_LOGGED = 大容量日志记录模型

SIMPLE = 简单恢复模式

nvarchar(128)" xml:space="preserve" jquery18206396683571966286="228">基本数据类型：nvarchar(128)

SQLSortOrder

SQL Server 早期版本中支持的 SQL Server 排序顺序 ID。

0 = 数据库使用的是 Windows 排序规则

>0 = SQL Server 排序顺序 ID

NULL = 输入无效或数据库未启动

tinyint" xml:space="preserve" jquery18206396683571966286="234">基本数据类型：tinyint

Status

数据库状态。

ONLINE = 数据库可用于查询。

注意

当数据库处于打开状态但尚未恢复时，可能返回 ONLINE 状态。 DATABASEPROPERTYEX." xml:space="preserve" jquery18206396683571966286="239">要确定数据库何时可以接受连接，可以查询 DATABASEPROPERTYEX 的 Collation 属性。在数据库排序规则返回非 Null 值之后，数据库就可以接受连接了。对于 AlwaysOn 数据库，可以查询 sys.dm_hadr_database_replica_states 的 database_state 或 database_state_desc 列。

OFFLINE = 数据库已被显式置于脱机状态。

RESTORING = 正在还原数据库。

RECOVERING = 正在恢复数据库，尚不能用于查询。

SUSPECT = 数据库未恢复。

EMERGENCY = 数据库处于紧急只读状态。只有 sysadmin 成员可进行访问。

nvarchar(128)" xml:space="preserve" jquery18206396683571966286="248">基本数据类型：nvarchar(128)

Updateability

指示是否可以修改数据。

READ_ONLY = 可读取但不能修改数据。

READ_WRITE = 可读取和修改数据。

nvarchar(128)" xml:space="preserve" jquery18206396683571966286="253">基本数据类型：nvarchar(128)

UserAccess

指示哪些用户可以访问数据库。

SINGLE_USER = 每次只有一个 db_owner、dbcreator 或 sysadmin 用户

RESTRICTED_USER = 仅限 db_owner、dbcreator 和 sysadmin 角色的成员

MULTI_USER = 所有用户

nvarchar(128)" xml:space="preserve" jquery18206396683571966286="259">基本数据类型：nvarchar(128)

Version

用于创建数据库的 SQL Server 代码的内部版本号。标识为仅供参考。不提供支持。不保证以后的兼容性。

版本号 = 数据库处于打开状态。

NULL = 数据库没有启动。

int" xml:space="preserve" jquery18206396683571966286="265">基本数据类型：int

(3)使用存储过程

sp_spaceused存储过程

sp_helpdb存储过程

(4)图形界面

10.分离数据库

分离数据库是指将数据是指将数据库从sqlser ver实例中删除，但是对于该数据库文件和事务日志文件保持不变。此时可以将该数据库附加到其他实例中。

以下几种情况存在不允许分离数据库

该数据库中存在快照
该数据库已复制并发布
数据库处于未知状态

分离语句

EXEC sp_detach_db  TEST

11.附加数据库

附加数据库是指将分离的数据库重新定位到相同的服务器或不同的服务器的数据库中。

注意附加数据库时，所有数据库文件(.mdf和.ldf文件)都必须可用。如果任何数据文件的路径与创建数据库或上次附加数据库时的路径不同，则必须指定文件的当前路径。

使用FOR ATTACH语句

CREATE DATABASE TEST
ON
(
    FILENAME='/var/opt/mssql/data/test_1.mdf'
)
LOG ON
(    
   FILENAME='/var/opt/mssql/data/test_1.Ldf'   
)
FOR ATTACH

图形界面操作:右键数据库节点---选择附加---打开附加数据库窗口---单击添加----弹出定位数据库文件窗口中选择要附加的数据库所在的路径

12.收缩数据库

数据库中的每个文件都可以通过删除未使用的页来减小。如果数据文件无需最小分配的大小，可以通过收缩文件来回收这些空间。

界面操作:数据库节点---右键TEST数据库---选择任务|收缩|数据库 ---打开收缩数据库--TEST窗口。根据需要，可以启用"在释放未使用的空间前重新组织文件，选中此选项可能会影响性能"复选框。再确定。

posted @ 2021-02-03 23:14 云中翱翔的鹏鸟阅读(295) 评论(0) 收藏举报

刷新页面返回顶部

风来雨去雨去风来来来去去

让专注更加沉浸！

SQL SERVER 基础语法一

公告