# 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')`
