# sql企业应用
OLTP联机事务处理
增删改查 及时更新数据，用到SQL server数据引擎
OLAP联机分析处理 免费
辅助商业决策。主要组件：SQL server分析处理、报表、整合服务
BI 商业智能
**相关组件：**
## 整合服务：提取、加载、转换： oricle、excel、文本提取
## 分析：数据挖掘
## 报表服务：制成报表
上述三个服务对应OLAP联机分析处理服务
## 默认是C-S结构
也就是 client-server 本地客户端-服务器结构。所以需要登录服务器。
## 实例
每安装一次，就需要配置一个实例。实例就相当于一台sql server服务器。最多安装50个。
## 服务账号
一般 ful-text、browser（浏览器）两个选的是默认的登录身份，其他可以改成我们自己设置的 abc 或其他新身份
# 1. 安装
选自定义安装，就会跳出来相关设置。其他步骤默认即可。
## 设置服务账户：
一般选择本地服务账户，更灵活
1、计算机管理 => 用户和组 => 用户 => 新增添加用户
2、在安装中设置服务账户；或者：
3、在 sql server 中，sql server 服务 => 选中一个实例，比如sql server(mysqlserver) 右击属性 => 登录 设置登录服务账户
## 服务器设置
在sql server 中，如果有多个实例，则就会有多个sql server 等，只不过括号内的内容不一样。
一般公司使用时，都是买一个主机，买一个sql 账号，创建多个不同实例来给不同部门使用。比如有两个实例，一个名字叫mssqlserver，一个叫 test。那么就生成两个 sql server（mssqlserver），sql server(test)。
### 访问服务器
如果需要访问其他服务器，可以在sql native client 配置里面进行。右击新建别名。
### 侦听外部IP地址
可以设置实例所对应的外部IP 地址。
# 2. SSMS
sql server management studio
几种方法都可以登录：
- 如果是默认实例时，连接到服务器，直接输入计算机名，也可以输入默认实例名(比如mssqlserver)，或者是家里的mac的IP地址`192.168.124.9`，或者输入`(LOCAL)`等。
- 如果是命名实例（命名实例就是我们自己命名安装的实例，假设名字是TEST），那么可在“服务器名称”上输入 `".\TEST"`，或者是`(LOCAL)mssqlserver`。
也可以服务器名称输入一个点好“.”，身份验证默认“windows 身份验证”
- 如果命名实例没有全部侦听（在sscs中设置），那么就需要去sscs 中相关实例名字对应的端口号，并且会，再加逗号，比如 `192.168.124.9,45599`
可以新建查询。比如想查询默认实例的名称：
```
use master
select @@servername //查询实例名称（servername 翻译成服务名，其实也就是实例名称）
go
```
得出名字是 CA9A
> 只支持多用户模式，不支持单用户模式。所以如果假设 master 受损需要恢复和重建时，无法使用 ssms。这时候需要用sqlcmd。
## 计算机名 → 实例 → 数据库 的关系
# 3. SQLcmd
一般用 ssms 管理器即可（多用户连接模式），但有时候如果要用到单用户的数据库恢复和连接，则无法使用 ssms，此时必须用 sqlcmd。
支持单用户模式。比如 如果 master 受损需要恢复和重建（必须在单用户模式下才可以恢复和重建），这时候需要在单用户模式下，那就无法使用 ssms 管理工具，而必须使用 sqlcmd 来操作。
在命令行里面输入 `sqlcmd`，就可以进入命令状态。也可以实现上述 ssms 中的功能。如果要退出，则按 `command + c`
- 如果是命名实例，则需输入`sqlcmd -S .\TEST `（四种办法，跟用 ssms的命名实例登录数据库是一样的。）
```
sqlcmd
use master
select @@servername
go
```
如果是连接到网络服务器（或者是默认实例以外的命名实例）可以输入：
```
sqlcmd -s .\xmedi1601.sqlserver.rds.aliyuncs.com,3433
use master //指定服务器中的某个数据库
select @@servername
go
```
查询实例名称：
```
select @@servername
```
显示实例名称（mssqlserver 或者是 sd5917121）
# 4. 数据库
每创建一个实例，则会默认有如下4个主数据库。
## Master（重要）
- 存放实例级对象：登录名、端点、链接服务器、实例配置；
- 存放数据库引导信息，记录所有数据库的文件路径；
- 需要定期备份
## Msdb（重要）
- 存放作业、警报、维护计算、数据库邮件等配置和历史记录；
- 需要定期备份
## Model
- 该实例创建新数据库的模板，包含数据库大小、排序规则、恢复模式、数据库对象等
- 如果在这里修改了以后，那么后续在第一级“数据库”中右击新建数据库以后，就会根据 model 中设置的来创建。
## TempDB
- 存放临时对象或中间结果集；
- 每次重启时自动重建；
- 类似缓存，需要磁盘空间足够，性能良好，可以移动路径
## Resource（隐藏）
- 一般被隐藏起来；
- 每个实例唯一，只读；
- 存放所有系统对象（sys 架构下）；
- 无法使用 sql 备份，但可以直接从其他数据库复制一份
## Distribution（不用太了解）
- 用于复制中的分发服务器角色中的分发数据库
# 5. 数据库文件组成
上述说的Master、Msdb、Model、TempDB、Resource、Distribution 几个数据库，每个数据库中都有以下几种文件。
主数据库文件 .mdf
从数据库文件 .ndf
事务日志文件 .ldf
## 添加数据库
可以在master 主数据库右击，点击添加即可，注意修改文件名，后缀可以命名成.ndf，表示从数据库。也可以设置存储地址。
> 但一般不选择在“系统数据库”中的master中添加。而是直接在第一级 **“数据库”**中右击添加用户数据库。
> 数据库一个区64kb，一个区里面有8页，最多8060b。数据库列长度设置时，最好能被8060整除。因为它同一行是不能跨页的。如果设置了4031，那么剩下的4029就会被浪费，而又需要另一页。
> 如以下语句则无法创建成功：`create table T1 (id int, name char(2000), address char(7000))`。是无法创建成功的，因为每一行加起来（int 默认4个字节+2000+7000+默认内部开销7个字节 = 9011 > 8060），出现以下提示：
`消息 1701，级别 16，状态 1，第 1 行
创建或更改表 'T1' 失败，因为最小行大小是 9011，包括 7 字节的内部开销。而此值超出了允许的 8060 字节的最大表行大小。`
# 6. 数据收缩
将用户经常访问的表放在不同的文件组上面，放在不同的磁盘上，这样保证磁盘有被均匀使用到，提高读写性能。
# 7. 数据库设计
最好将不同文件放在不同文件组，然后不同文件组放在不同磁盘里面。
# 8.重置 ID自增列
### 1、参考
https://my.oschina.net/tianma3798/blog/667632
https://blog.csdn.net/helloword4217/article/details/23562967
https://blog.csdn.net/abbie/article/details/51941594
```
alter table dbo.审核信息 add ID int identity(1,1);//设置 identity ID 编号从1，1 开始
set identity_insert dbo.审核信息 on; //允许使用 id 编号进行操作
insert into dbo.审核信息(项目编号,子公司编号, ID) values (1800232,'18035ZH0',14);//利用ID执行插入操作，但如果按两次则会有两个id为14的记录。
set identity_insert dbo.审核信息 off; // 用完记得关闭
```
### 2、判段一个表是否具有标识列
可以使用 objectPROPERTY 函数确定一个表是否具有 IDENTITY（标识）列,用法:
Select OBJECTPROPERTY(OBJECT_ID('表名'),'TableHASIdentity')
如果有，则返回1,否则返回0
### 3、查询某表标识列的列名
SQL Server中没有现成的函数实现此功能，实现的SQL语句如下
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.columns
WHERE TABLE_NAME='表名' AND COLUMNPROPERTY(
OBJECT_ID('表名'),COLUMN_NAME,'IsIdentity')=1
# 卸载SQL Server 实例
https://support.microsoft.com/zh-cn/help/955499/how-to-uninstall-an-instance-of-sql-server-2008
# 数据库导入excel 数据
在数据库右击 - 任务 - 导入数据。选 excel 即可
注意选择的 excel 版本类型需与文件类型一致。否则出现“未在本地计算机上注册microsoft.ace.oledb.16.0”提供程序，（或者是 12.0等其他信息）
# 创建表
```
create table dbo.阶段评估 (
项目编号 varchar(10) not null,
子公司编号 varchar(20),
项目名称 varchar(200),
……
);
```
# 插入数据 .csv 数据
从本地文件夹“ ”中找到文件，斜号要注意方向。into 表示导入到数据表中。fields terminated 表示用‘，’字符分隔，忽略第一行的标题行。
```
load data local infile 'e:/ '
into table dbo.阶段评估
fields terminated by ','
ignore 1 lines;
```
# delete
如果有多个 where 要满足，则可以用 where 字段名 in ()
`delete from dbo.阶段评估表 where 项目编号PM in ('1000002','1000003')`
