数据库基本知识

数据库

SQL语言

structured Query Language，结构化查询语言。包含三个部分
数据定义语言DDL
create
alter
drop
数据操作语言 DML
insert select update delete
数据控制语言 DCL
grant命令用于授予用户或角色权限集合
deny命令用于显示地限制权限集合
revoke命令用于撤销对象上的权限集合
SQL标准

系统数据库

系统信息数据库：主要存放各种系统运行所必需的数据
模板数据库：创建数据库的模板
临时数据库：用于保存临时对象或中间结果集。

SQL语句

SQL对大小写不敏感
查询和更新指令构成了 SQL 的 DML 部分：

SELECT - 从数据库表中获取数据
UPDATE - 更新数据库表中的数据
DELETE - 从数据库表中删除数据
INSERT INTO - 向数据库表中插入数据

SQL 中最重要的 DDL 语句:

CREATE DATABASE - 创建新数据库
ALTER DATABASE - 修改数据库
CREATE TABLE - 创建新表
ALTER TABLE - 变更（改变）数据库表
DROP TABLE - 删除表
CREATE INDEX - 创建索引（搜索键）
DROP INDEX - 删除索引

select 语句

select 列名称 from 表名称
select lastname,firstname from Person
// 从persons的表中获取名为lastname和firstname的列的内容。

select distinct 列名称 from 表名称
// 仅仅列出不同的值，相同的值只出现一次

创建和维护数据库

文件：数据最终存放在计算机硬盘上的物理文件
文件的最大长度值：文件达到一定程度，不允许继续写入
文件的增量：写满时需要增加多少
数据文件和日志文件
文件分组：默认情况下，文件组为primary，所有的文件都在这里。

数据库的文件类型

• 主要数据类型 .mdf
• 次要数据文件：可以分散到多个磁盘上，如果数据库超过了单个windows文件的最大大小，可以使用次要文件，数据库可以继续增长，后缀为.ndf
• 事务日志
  每个数据库有一个主要文件组，包含主要数据文件和未放入其他文件组的所有次要文件。

删除数据库文件

use stu_db3
go
alter database stu_db3 remove file db3_01
go

修改默认的文件组

alter database stu_db3
modify filegroup fg default

修改文件组的名字

alter database stu_db3
modify filegroup fg name=user_fg

设置只读

alter database stu_db3
modify filegroup fg readonly

删除，只删除空文件组

alter database stu_db3
modify filegroup fg

文件组的填充策略

文件组对组内的所有文件都使用按比例填充策略，写入文件组是sql server 数据库引擎按文件中的可用空间比例将数据写入文件组中的每个文件，而不是写入第一文件直至变满。

数据库优化策略

使用文件和文件组；数据布局

修改数据库选项

go
alter database stu_db3
set auto_close on,auto_shrink on
go

查看数据库

use master
go
select databasepropertyex('stu_db3','isautoshrink')
go

查看数据库大小

use stu_db3
go
exec sp_spaceused
go

查看文件大小

exec sp_helpdb stu_db1

删除数据库

drop database
执行删除操作时，确保连接的数据库上下文不能与要删除的数据库或者数据库快照相同；如果数据库或它的任意一个文件处于脱机状态，则不会删除磁盘文件，只能拿手动删除；删除之前，必须把数据库上的所有数据库快照删除；涉及日志传送操作，在删除之前取消日志传送操作。

修改数据库

重命名时确保没有其他用户使用，然后设置为单用户模式

alter database stu_db3
modify name=onedb

扩展数据库

有三种方法
设置数据库为自动增长方式
增加数据中数据文件和日志文件大小，maxsize属性
为数据增加新的次要数据文件或日志文件
第三种方法比较好

use master
go
alter database stu_db3
add file
(name=stud_db_one,
filename='d:\sql\stud_db_one.ndf'
,size=2mb
,maxsize=unlimited,
filegrowth=10%
)
go
alter database stu_db3
add log file
(name=stud_db_one_log,
filename='D:\sql\stud_db_one.ldf',
size=10mb,
maxsize=20mb,
filegrowth=5%
)
go

收缩数据库

设置数据库为自动收缩，设置auto_shrink
手动执行dbccshrinkdatabase
手动执行dbccshrinkfile来手动收缩数据库中文件的大小

alter database Stu_db3
set auto_shrink on

收缩操作可能会增加数据库的碎片，只有在数据库引擎检查到数据库文件空间中超过25%未使用，会执行收缩.

DBCC shrinkdatabase('要收缩的数据库名',可用空间比例)
DBCC shrinkdatabase('要收缩的数据库名',数据库的大小)

数据库快照

数据库快照是数据库的只读、静态视图，通过将allow_snapshot_isolation数据库选项设置为on而创建的。
应用：维护历史数据以生成报表；使用为了实现可用性目标而维护的镜像数据库来减轻报表负载。
数据库快照不是冗余存储，定期备份才是王道。

原理

管理快照

标准版不支持数据库快照，要企业版才可以。

use master
go
create database stu_db3_snp_201607011344
on(
name =stu_db3_data
,filename='d:\data\stu_db3.snp'
)
as snapshot of stu_db3
go

恢复快照

restore database test2 from database_snapshot='test2_snp'

分离和附加数据库

分离数据库

数据库中存在数据库快照，必须首先是喊出所有数据库快照
已复制并发布数据库，如果进行复制，数据库必须是未发布的，必须通过运行sp_splicationdboption禁用发布后，才能分离数据库。
该数据库正在某个数据库镜像会话中进行镜像，需要进行终止。
数据库处于可疑状态

user master
go 
exec sp_detach_db  stu_db3

附加数据库

无法在早期版本的sql server中附件由较新版本的sql server创建的数据库
分离再重新附加只读数据库后，会丢失差异基准信息。

use master
go 
create database stu_db3 on
(filename=N'D:\stu_db3.mdf')
for attach
go

分离数据库后日志文件无法使用。可以使用forattach_rebulid_log关键字指定系统重建日志文件。

create database stu_db3 on
(filename='数据库文件所在路径\dtu.mdf')
for attach_rebuild_log
go

移动数据库文件

在执行文件引动运行，状态设置为offline

alter database stu_db3 set offline

对于已移动的每个文件，运行下列语句

alter database database_name
modify file(name=文件逻辑名
，filename='新路径\windows中的文件名'
)

移动和复制数据库

SQL Server代理的服务器必须启动

备份数据库

完整备份：整个数据库的完整备份
差异备份：数据库中所有文件的备份

use master
go
backup database stu_db3
to disk ='D:\data\backup\db-cy.bak'
with differential,
description='数据库差异备份'，
init

事务日志备份。

局部变量

局部变量，在编程过程中临时保存单个特点数据类型的值的对象，
声明对象必须用declare
为变量指定名称，而且名称前必须有一个@符号
必须为变量指定数据类型和长度
默认情况下，变量的值为null
可以在一个declare语句中声明多个变量，之间用逗号间隔
变量的赋值有两种方式，一种是用set关键字，还有一种直接用select检索来赋值

全局变量

sql server 系统定义并赋值的一系列变量，无法定义和赋值，例如
@@servername 返回运行sql server本地服务器的名称
T-SQL注释有两种形式：块注释和行内注释，块注释一般用于头块：一般格式为
脚本对象的名字
设计人员和程序员的名字
创建日期
修改日期与注解
对象的作用和调用方式等信息
验证测试与批准注解
--短的注释

函数

使用set为变量赋值

declare @mynumber int,@myresult int
set @mynumber=1
set @myresult=sqrt($mynumber)
select @myresult

用select为变量赋值
对变量在赋值前要先声明，可以在一个操纵内同时对多个变量赋值
对变量的赋值不能和数据检索操作同时进行。