SQL Server數據庫基礎

一、安裝SQLServer數據庫

setup.exe->安裝->全新SQLServer獨立安裝或向現有安裝添加功能->輸入序列號->下一步->默認實例->混合模式->輸入sa密碼->實例賬號添加當前賬號即可。

如果想要其他機器訪問這臺機器的sqlserver，需要使用windows防火墻中的入站規則，端口寫1433.

二、SQL Server配置管理器

包含各個sql服務，連接數據庫時，務必保證sqlserver服務已開啟.

三、系統數據庫

1、master數據庫

主系統數據庫，丟失該庫，sqlserver將無法啟動。

2、tempdb數據庫

用來創建臨時對象，包括臨時表，存儲過程，表變量，全局臨時表以及游標等。

3、model數據庫

sqlserver實例中創建的全部數據庫的模型。

4、msdb數據庫

主要用于sqlserver代理的支撐后臺。

5、resource數據庫

資源數據庫，隱藏的制度數據庫。主要用于改進sqlserver版本升級時的進程功能。

6、distribution數據庫

分發數據庫，只有將實例配置為復制的分發者時，該庫才存在。復制中所有的元數據與各種類型的歷史記錄都存儲在此數據庫中。

四、數據庫的組成

數據庫主要由文件和文件組組成。數據庫中的所有數據和對象（表、存儲過程和觸發器等）都被存儲在文件中。

1、文件

主要數據文件：存放數據和數據庫的初始化信息。每個數據庫只有一個主要數據文件，默認擴展名是.mdf。次要數據文件：存放除主要數據文件意外的所有數據文件。有些數據庫可能沒有次要數據文件，也可能有多個次要數據文件，默認擴展名是.ndf。事物日志文件：存放用于恢復數據庫的所有日志信息。每個數據庫至少有一個事物日志文件，也可以有多個事物日志文件，默認擴展名是.ldf。

2.文件組主文件組：包含主要數據文件和任何沒有明確指派給其他文件組的文件。系統表的所有頁都分配在主文件組中。用戶定義文件組：主要是在create database 或者alter database 語句中，使用filegroup關鍵字指定的文件組。

五、創建數據庫創建數據庫時包括常規、選項和文件組 常規用于設置數據庫的名稱大小位置等，選項和文件組中定義數據庫的一些選項，顯示文件和文件組的統計信息，默認即可。SQL Server 默認創建了一個PRimary文件組，用于存放若干個數據文件，但日志沒有文件組。SQL Server數據庫的數據文件分邏輯名稱和屋里名稱。邏輯名稱是在sql 語句中引用文件時所使用的名稱；物理名稱用于操作管理系統。

維護SQLServer數據庫

一、脫機與聯機

數據庫->右鍵脫機，copy之后右鍵聯機->完成

二、分離與附加

分離不是刪除數據庫而是從服務器中移除。 exec sp_detach_db @dbname='webDB'

數據庫->右鍵分離->需要時再右鍵附加上。create database webDB on (filename='c:/webDB.mdf'),(c:/webDB.ldf) for attach

三、導入和導出

數據庫->右鍵導入->選擇數據源->完成。

數據庫->右鍵導出->輸入服務器名稱，賬號密碼->選擇導出方式->完成。

四、備份和恢復

數據庫->右鍵備份->完整備份->完成。

數據庫->右鍵恢復->選擇bak文件->完全覆蓋->完成。

五、收縮數據庫和文件

因為數據庫空間分配采用的是現分配、后使用的機制，所以數據庫使用過程中就可能會存在多余的空間。收縮數據庫功能允許對數據庫的每個文件進行收縮。數據庫和日志文件都可以收縮，可以手動收縮額自動收縮。自動收縮：數據庫->右鍵屬性->選項->自動收縮->完成。手動收縮：數據庫->右鍵任務收縮->數據庫/文件->確定。

六、生成與執行SQL腳本

將數據庫生成SQL腳本：數據庫->編寫腳本->create database ->完成。將數據表生成SQL腳本：數據表->編寫腳本->create table->完成。執行SQL腳本：新建查詢->輸入腳本->執行。

數據表

一個數據庫由多張數據表組成，每個數據表由行和列組成

一、數據類型

1、基本數據類型

精確數字類型：bigint、int、smallint、tinyint、bit、decimal、numeric、money、smallmoney。

浮點數據類型：freal、float。

字符數據類型：text、ntext、char、nchar(n)、varchar、nvarchar(n)。

二進制數據類型：binary、varbinary、image。

日期/時間數據類型：time、date、smalldatetime、datetime、datetime2、datetimeoffset。

2、用戶自定義數據類型

2.1 可編程性->類型->用戶自定義數據類型->右鍵新增數據類型。

2.2 exec sp_addtype code_name,'char(8)','not null'

二、數據表的數據完整性

表中的每一列都有一組屬性，如名稱、數據類型、數據長度和是否為空等。

1.空與非空值（null/not null）：定義是否允許為空

2.默認值（default）：默認指定值。

3.標識屬性（identity）：自動為表中插入新行生成連續遞增的編號，因標識值通常唯一，所以標識列通常定義為主鍵（整型數據類型，標識列不能為空）。

4.約束：用來定義自動強制數據庫完整性的方式，約束優先于使用觸發器、規則和默認值。

非空：用戶必須輸入一個值。可以有多個非空約束。

檢查：用來指定一個布爾操作，限制輸入到表中的值。

唯一性：用戶的應用程序向列中輸入一個唯一的值，值不能重復，但可以為空。

主鍵：可以保證實體完整性，一個表只能有一個主鍵，主鍵不能為空。

外鍵：控制存儲在外鍵表中的數據。一個表可以有多個外鍵。

三、企業管理器管理數據表

創建表：數據庫->表->右鍵新建表->輸入列名->選擇數據類型->是否允許空->保存輸入表名。

修改表：數據庫->表->右鍵設計。

重命名：數據庫->表->f2。

刪除表：數據庫->表->右鍵刪除。

非空約束->列->列屬性->允許NULL -> 是/否。

主鍵約束->列->右鍵->設為主鍵->完成。

唯一約束->列->索引/鍵->唯一->是->完成。

檢查約束->列->右鍵->check約束->表達式->完成。

默認約束->列->列屬性->默認值。

外鍵約束->列->右鍵->關系->表和列規范->選擇外鍵表列。

四、關系的創建與維護

SQL Server 是一個關系數據庫管理系統（Relational Datable Management System，RDBMS），當數據庫中包含多個表時，需要通過主關鍵字來建立表之間的關系。

主表user 從表work 主表wid右鍵->關系->主鍵work id 外鍵 user wid。

五、計算列

列屬性->計算列規范->公式。

由同一表中的其他列的表達式計算結果來生成列。一般用來對數據表進行擴展或增強。

T-SQL

一、T-SQL概述

T-SQL(Transact Structured Query Language)是標準的Microsoft SQL Server的擴展，是標準的SQL程序設計語言的增強版，程序與SQL Server溝通的主要語言。

SQL是關系數據庫系統的標準語言，標準的SQL可以運用在所有的關系型數據庫上。但T-SQL是SQL Server系統產品獨有的。

二、T-SQL語言的組成

數據定義語言（Data Definition Language，DDL）：用于在數據庫系統中對數據庫、表、視圖、索引等數據庫對象進行創建和管理。

數據控制語言（Data Control Language，DCL）：用于實現對數據庫中數據的完整性、安全性等控制。

數據操縱語言（Data Mainpulation Language，DML）：用于插入、修改、刪除和查詢數據庫中的數據。

三、T-SQL語句結構

每條SQL語句由一個謂詞開始，該謂詞描述這條語句要產生的動作，如select或update關鍵字。謂詞后緊接著一個或多個子句，子句中給出了被謂詞作用的數據或提供謂詞動作的詳細信息，每一條子句都由一個關鍵字開始。

select 子句[into 子句] from子句 [where 子句] [group by 子句][having 子句][order by 子句]

四、T-SQL語句分類

變量說明語句：用來說明變量的命令。

數據定義語句：用來簡歷數據庫、數據庫對象和定義列，如create table、create view、drop table等。

數據操縱語句：用來操縱數據庫中數據的命令，如select、insert、update、delete和cursor等。

數據控制語句：用來控制數據庫組件的存取許可、存取權限等，如grant、revoke等。

內嵌函數：說明變量的命令。

其他命令：嵌于命令中使用的標準函數。

五、常量

數據在內存中存儲始終不變化的量叫常量，常量，也稱為文字值或標量值，是標識一個特定數據值的符號。格式區別與它所標識的值的類型。

1.數字常量：包括整數常量、小數常量以及浮點常量。【 1 2 3】

2.字符串常量：括在單引號內并且包含字母數字字符（a~z、A~Z、0~9）以及特殊字符，如”！、@、#“。 【‘a’ ‘asd’】

3.日期和時間常量：SQL規定日期、時間和時間間隔的常量值被指定為日期和時間常量。【'2014-08-01'】

4.符號常量：除了用戶定義的常量外， SQL包含幾個特定的符號常量，這些常量代表不同的常用數據值。【current_date標識當前的日期】

六、變量

數據在內存中存儲可以變化的量叫變量。用戶必須指定存儲信息的單元，并為該存儲單元命名。T-SQL可以使用兩種變量，局部變量和全局變量。

1.局部變量

局部變量是用戶可自定義的變量，作用范圍僅在程序內部，局部變量名必須以“@”開頭。

1.1聲明局部變量：使用declare語句聲明變量。

declare @a char(10)

1.2局部變量賦值：一種select，一種set。

select : select @a = 1set : set @a = 'i love sql'/*定義變量 查詢結果*/declare @title varchar(10)set @title = 'xxx'select * from article where title = @title

輸出語句

select： select @a as ‘A’返回帶列明的變量值

print ： print @a 輸出變量

2.全局變量

系統內部事先定義好的變量稱為全局變量，不用用戶定義，任何程序均可隨時調用。全局變量以“@@”開頭。

@@identity： 最后一次自增的值。@@rowcount： 影響行數@@cursor_rows： 返回連接上打開的游標當前限定的數目@@error：返回錯誤號。若執行成功則返回0。@@connections：記錄最后一次服務器啟動以來，所有針對這臺服務器進行的鏈接數目，包括沒有鏈接成功的嘗試。@@cup_busy：記錄上次啟動以來嘗試的連接數，無論鏈接成功還是失敗，都以ms為單位的cpu工作時間@@dbts：返回當前數據庫中timestamp數據類型的當前值。@@fetch_status：返回上一次使用游標fetch操作所返回的狀態值，并且返回值為整型。0成功，-1失敗或不存在，-2被提取的行不存在。@@idle：返回以ms為單位計算SQL Server服務器自最近一次啟動以來處于停頓狀態的時間。@@io_busy：返回以ms為單位計算的SQL Server服務器自最近一次啟動以來花在輸入和輸出上的時間。@@lock_timeout：返回當前對數據鎖定的超時設置。@@pack_received：返回SQL Server服務器自最近一次啟動以來一共從網絡上接收數據分組的數目。@@pack_sent：返回SQL Server服務器自最近一次啟動以來一共向網絡上發送數據分組的數目。@@procid：返回當前存儲過程ID標識。@@remserver：返回在登陸記錄中記在遠程SQL Server服務器的名字@@spid：返回當前服務器進程的ID標識。@@total_errors：返回自服務器啟動以來，所遇到讀寫錯誤的總數。@@total_read：返回自服務器啟動以來，讀磁盤的次數。@@total_write：返回自服務器啟動以來，寫磁盤的次數。@@trancount：返回當前鏈接中，處于活動狀態事務的數目。@@version：返回當前服務器的安裝日期、版本、一級處理器的類型。

七、運算符

代碼注釋：/*此處注視*/

1、算數運算符

+、-、*、/。加減乘除。1+1。

2、賦值運算符

=為賦值運算符，將 等號右側的值賦給左側變量。

3、比較運算符

>、=、<=、<>、!=、!>、! 比較運算符測試兩個表達式是否相同

4、邏輯運算符

• all：如果一個比較集中全部都是true，則值為true。
• and：如果兩個布爾值表達式均為true，賊值為true。
• any：如果一個比較集合中任何一個為true，則值為true。
• between：如果操作數是在某個范圍內，則值為true。
• exists：如果子查詢包含該行，則返回true。
• in：如果操作數與一個表達式列表中的某個相等，則值為true。
• like：如果操作數匹配某個模式，則值為true。
• not：對任何其他布爾運算符的值相反。
• or：如果任何一個表達式是true，則值為true。
• some：如果一個比較集中的某些true，則值為true。
• not and or 同意表達式中，優先級是not>and>or。

5、位運算符

&（AND） |（OR） ^（互斥OR） ~（NOT）

6、連接運算符

使用"+"用于連接兩個或者兩個以上的字符串。'123'+'asd'

7、運算符優先級

• 1.+（加） -（減） ~（位反）
• 2. *（乘） /（除） %（余）
• 3.+（加） +（字符串連接） -（減）
• 4.=、>、=、<=、<>、!=、!>、! >、!
• 5.^（按位異或） &（按位與） | （按位或）
• 6. NOT
• 7. AND
• ALL ANY BETWEEN IN LIKE OR SOME（邏輯運算符）。
• =（賦