sqlServer学习笔记

------------------------安装--------------------------

 

------------------------开启服务--------------------------

未开启数据库服务,会报错:未找到或无法访问服务器!  所以打开sqlserver数据库的前提是启动该服务,且是手动启动,具体见下:

方法一: 计算机管理---->服务---->sqlserver(mssqlserver)---->启动

方法二: 安装目录---->配置工具---->sqlserver配置管理器----->服务----->sqlserver(mssqlserver)---->启动

方法三: cmd---->net start mssqlserver    //开启服务

                 ---->net stop mssqlserver    //停止服务

------------------------身份验证--------------------------

 (1)window验证   //不需要用户名和密码

(2)sql server用户验证    //需要用户名和密码----->在数据库中创建用户密码,见下:

                                                                       

数据库中创建用户密码: open数据库----->数据库服务器名(localhost)----->安全性---->登录名(创建用户,或者查看默认账号)

------------------------常见报错代码--------------------------

18456: 登录数据库,选择sqlserver验证,报错18456,无法连接到服务器;

          原因: 安装数据库时,身份验证应该选择混合模式 || 密码错误也会报这个错

          解决: window验证登录数据库, 右键服务器名---->属性---->安全性---->身份验证---->sqlserver验证和window验证

                  数据库服务器名---->重新启动--->断开连接---->连接 (这一步是重启数据库服务)

18470: 上面这个错解决后,再次选择sqlserver验证登录, 报错该账户被禁用,代码18470;

            原因: 需要启用该账户

            解决:window验证登录数据库----->数据库服务器名(localhost)----->安全性---->登录名---->该用户右键属性---->状态--->登录--->选择启用

                   数据库服务器名---->重新启动--->断开连接---->连接 (这一步是重启数据库服务)

------------------------物理存储结构--------------------------

主数据文件: .mdf    //只有一个主文件

辅助数据文件: .ndf    //可有多个辅助文件,存储主数据文件中没有的数据和数据对象

日志文件: .ldf    //只有一个日志文件, 增删改会被记录在内, 数据库损坏用此文件修复

文件组:主文件组(存放.mdf文件)和辅助文件组(存放.ndf文件), .ldf不属于任何文件组

------------------------数据库命名规则--------------------------

1) 第一个字符必须是:字母 或 _ 或 @ 或 #开头

2) 不能是sql关键字

3)不能有其他特殊字符和空格

------------------------数据库分类--------------------------

----->系统数据库

          ------>master    

          ------>model     //数据库的结构模版

          ------>msdb      //数据库代理,备份,还原

          ------>tempdb     //存放临时表,最后会清空

          ------>resource     //只读,隐藏, 在所有数据库用户sys下,存储表对象

----->用户自己创建的数据库

------------------------标识--------------------------

80: sqlserver2000

90: sqlserver2005

100: sqlserver2008

------------------------兼容|限制访问--------------------------

数据库名---->右键----->属性----->选项----->选哪个版本就会兼容哪个版本的数据库

                                                        ----->限制访问:multi-user(正常状态)/single-user(数据库维护)/restrited-user(限制用户)

                                                        ----->只读:true/false

------------------------视图创建数据库--------------------------

数据库服务器名------>数据库------>右键----->创建数据库 (主数据文件大小最小3M,否则不能创建数据库model)

------------------------sql创建数据库--------------------------

use master
go
--创建数据库
create database market
on primary( --主文件组
name='market_data',
filename='D:\Java\SQLServer2008_project\market_data.mdf',
size=5mb,
maxsize=100mb,
filegrowth=15%
),
filegroup fg( --辅助文件组
name='market2_data',
filename='D:\Java\SQLServer2008_project\market2_data.ndf',
size=3mb,
maxsize=200mb,
filegrowth=10%
)
log on( --日志
name='market_log',
filename='D:\Java\SQLServer2008_project\market_log.ldf',
size=5mb,
filegrowth=0
),(
name='market2_log',
filename='D:\Java\SQLServer2008_project\market2_log.ldf',
size=5mb,
filegrowth=0
)
go

select * from sysdatabases    --show databases;
go

--创建文件组,并添加文件
use market
alter database market add filegroup fg2
go
alter database market add file(
  name='market3_data',
  filename='D:\Java\SQLServer2008_project\market3_data.ndf',
  size=3mb,
  maxsize=400mb,
  filegrowth=13%
)to filegroup fg2
go
--将文件组设置为默认
alter database market modify filegroup fg2 default
go
--给已有文件组添加文件
alter database market add file(
  name='market4_data',
  filename='D:\Java\SQLServer2008_project\market4_data.ndf',
  size=5mb,
  filegrowth=10%,
  maxsize=100mb
)to filegroup fg2
go

------------------------删除数据库--------------------------

视图删除: 右键数据库名称----->删除----->确定(两个勾选上,如下图)

sql删除: 

if exists(select * from sysdatabases where name='market')
drop database market

 ------------------------建立数据库用户--------------------------

--->默认用户sa是SQLserver的超级管理员

--->每个登录SQLserver的账号都是一个用户

--->每个数据库可以有多个用户,每个用户可以同时访问多个数据库

创建数据库登录名(只能登录,不能操作里面的数据库)
  数据库服务器---->安全性---->登录名--->右键新建用户
    (1) windows身份验证登录的用户
          前提是需要在本地计算机用户账户中添加用户,再在sqlserver的添加用户中指定本地账户名即可
    (2) sqlserver验证登录的用户
          有密码策略等根据需要勾选

创建数据库用户(用户名可以和登录名相同,给哪些数据库添加用户就能访问哪个数据库)
(创建登录名的同时指定能够访问的数据库)
数据库服务器名--->安全性--->登录名--->右键新建登录名--->输入登录名--->用户映射--->勾选可以访问的数据库---->确定

 ------------------------赋予用户权限--------------------------

sa超级管理员登录----->具体的数据库----->安全性---->用户---->用户名----->右键属性---->安全对象---->搜索---->(见下)

特定对象             //具体到哪一张table
特定类型的对象        //数据库的子级(表,视图,...)
属于该架构的所有对象   //哪个数据库

----->下面的权限中勾选权限---->确定

 ------------------------sql创建登录名,赋予权限--------------------------

use master
go

--创建登录名testlogin 密码123456
create login testlogin with password='123456'
go

--为数据库market创建用户testlogin
use market
create user testlogin for login testlogin
go

--为用户testlogin添加权限
use market
grant select,insert,update on testtable to testlogin
go

--撤销用户权限
use market
revoke update on testtable to testlogin
go

  ------------------------创建数据库角色--------------------------

角色分两种: 针对数据库本身操作的权限集合(服务器角色)
          针对数据库中的数据的操作权限集合(数据角色)

登录名sa在每个数据库中的用户名是dbo

sa登录,安全性---->登录名---->双击指定的登录名---->服务器角色---->勾选需要的角色权限(参考下图:)

 

创建自定义数据库角色:

  sa登录---->指定的数据库---->安全性--->角色---->右键创建数据库角色

给数据库用户添加角色:
 
   sa登录---->指定的数据库---->安全性--->用户--->属性---->勾选角色成员(见下图,是系统自带的角色,也可以自定义数据库角色)

 

  ------------------------查询数据库状态--------------------------

F1调用帮助文档

状态  定义  
ONLINE
 可以对数据库进行访问。即使可能尚未完成恢复的撤消阶段，主文件组仍处于在线状态。
 
OFFLINE
 数据库无法使用。数据库由于显式的用户操作而处于离线状态，并保持离线状态直至执行了其他的用户操作。例如，可能会让数据库离线以便将文件移至新的磁盘。然后，在完成移动操作后，使数据库恢复到在线状态。
 
RESTORING
 正在还原主文件组的一个或多个文件，或正在脱机还原一个或多个辅助文件。数据库不可用。
 
RECOVERING
 正在恢复数据库。恢复进程是一个暂时性状态，恢复成功后数据库将自动处于在线状态。如果恢复失败，数据库将处于可疑状态。数据库不可用。
 
RECOVERY PENDING
 SQL Server 在恢复过程中遇到了与资源相关的错误。数据库未损坏，但是可能缺少文件，或系统资源限制可能导致无法启动数据库。数据库不可用。需要用户另外执行操作来解决问题，并让恢复进程完成。
 
SUSPECT
 至少主文件组可疑或可能已损坏。在 SQL Server 启动过程中无法恢复数据库。数据库不可用。需要用户另外执行操作来解决问题。
 
EMERGENCY
 用户更改了数据库，并将其状态设置为 EMERGENCY。数据库处于单用户模式，可以修复或还原。数据库标记为 READ_ONLY，禁用日志记录，并且仅限 sysadmin 固定服务器角色的成员访问。EMERGENCY 主要用于故障排除。例如，可以将标记为“可疑”的数据库设置为 EMERGENCY 状态。这样可以允许系统管理员对数据库进行只读访问。只有 sysadmin 固定服务器角色的成员才可以将数据库设置为 EMERGENCY 状态。
 

 两种查询数据库状态方法:

--sql查询数据库状态
select state_desc from sys.databases where name='market'
go

--使用函数查看数据库状态
select DATABASEPROPERTYEX('aaa','status')
go

在脱机offline下可以对数据库的文件进行复制粘贴, online下不能复制粘贴(也就是备份工作)

指定数据库名---->任务---->脱机/联机

  ------------------------数据库的分离与附加--------------------------

分离数据库的目的: 便于在公司将指定的数据库与数据库软件脱离关系(并不会删除,在客户端看不见该数据库), 拷贝到u盘便于在家进行维护,方便了数据库管理人员,次日再将分离的数据库附加到软件(在客户端能看见了)

指定数据库---->右键---->任务----->分离

数据库---->右键----->附加----->添加分离的数据库---->确定

脱机与分离的区别: 脱机后在数据库服务器上还显示,只是不能用
                分离是直接从服务器上删除的意思
两者的共同点:就是为了对数据库的复制和粘贴操作

   ------------------------数据库的收缩--------------------------

数据库创建时,会对每个文件指定大小, 对存储空间是一种浪费,

收缩是指: 将数据库中已经分配大小的文件,对空闲的空间的删除

收缩方式:自动  手动

指定数据库---->右键属性---->选项----->自动收缩----->true    //自动收缩默认是25%的空闲空间

手动收缩:
指定数据库---->右键任务----->收缩----->数据库/文件

    ------------------------数据库的备份与恢复--------------------------

数据库备份类型有四种:

1.数据库完整备份
2.文件组或文件备份
3.事务日志备份
4.差异备份   //只备份上次完整备份后数据更改的部分

备份: 指定数据库名---->右键任务----->备份---->(将默认保存路径删除后添加新路劲)----->确定    //若是备份后,还原前需要删除原数据库

还原: 数据库----->右键还原数据库----->目标数据库:手动填写待还原数据库名称----->源设备:添加xxx.bak备份文件----->确定

上面是完整备份, 下面是差异备份(必须先完整备份,再差异备份, 还原必须先完整还原,再差异还原)

若要恢复7点数据,首先还原4点的完整备份1, 再还原7点的差异备份2

备份
1.完整备份参考上面步骤
2.指定数据库--->任务---->备份---->备份类型:差异备份--->修改保存路劲--->确定

还原:(操作之前删除待还原数据库)
 数据库--->还原数据库---->步骤同上---->确定之前在选项---->勾选如下图---->确定

右键显示正在还原的数据库----->任务---->还原---->数据库---->选择备份差异文件--->确定

注:如果没有下图的操作,差异备份会报错!