SQL SERVER 基础语法一
目录
主数据文件:*.mdf (有且只有一个)
辅助数据文件:*.ndf (任意)
日志文件:*.ldf (一个或以上)
第一个字母必须是字母或“_”,"@","#"
数据库名称不能是T-SQL保留字
不允许嵌入空格或其它特殊字符
系统数据库:master model msdb tempdb resource
用户数据库
创建数据库
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
注意:
只有数据文件具有文件组,日志文件不存在文件组
主数据文件一定存放在主文件组中
与系统相关的数据信息一定存放在主文件组中
一个数据文件只能存放于一个文件组中,不能同时存放于多个文件组中
使用语句
(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
使用ADD FILE语句
ALTER DATABASE TEST ADD FILE ( NAME=Test_group4, FILENAME='/var/opt/mssql/data/Test_group4.ndf', SIZE=50MB, MAXSIZE=100MB, FILEGROWTH=15% )
DROP DATABASE TEST
注:使用DROP删除数据库不会出现确认信息。
数据库快照像数据库在某一时刻照的照片。如果数据库损坏了,可以通过快照来恢复到源数据库。
(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
(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 是通过使用以下值计算得到的位图。
例如,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
|
||||||||||
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 = 数据库可用于查询。
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)图形界面
分离数据库是指将数据是指将数据库从sqlser ver实例中删除,但是对于该数据库文件和事务日志文件保持不变。此时可以将该数据库附加到其他实例中。
以下几种情况存在不允许分离数据库
- 该数据库中存在快照
- 该数据库已复制并发布
- 数据库处于未知状态
分离语句
EXEC sp_detach_db TEST
附加数据库是指将分离的数据库重新定位到相同的服务器或不同的服务器的数据库中。
注意附加数据库时,所有数据库文件(.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
图形界面操作:右键数据库节点---选择附加---打开附加数据库窗口---单击添加----弹出定位数据库文件窗口中选择要附加的数据库所在的路径
数据库中的每个文件都可以通过删除未使用的页来减小。如果数据文件无需最小分配的大小,可以通过收缩文件来回收这些空间。
界面操作:数据库节点---右键TEST数据库---选择 任务|收缩|数据库 ---打开收缩数据库--TEST窗口。根据需要,可以启用"在释放未使用的空间前重新组织文件,选中此选项可能会影响性能"复选框。再确定。