Day1 初識數(shù)據(jù)庫

1、什么是數(shù)據(jù)庫

從字面上理解，數(shù)據(jù)庫可以定義為存儲數(shù)據(jù)的庫；而從廣義上說，數(shù)據(jù)庫是一個長期存儲在計算機內、有組織的、可共享的、統(tǒng)一管理的大量數(shù)據(jù)的集合。

數(shù)據(jù)庫可分為關系型數(shù)據(jù)庫、非關系型數(shù)據(jù)庫；而SQL則是關系型數(shù)據(jù)庫，并且具有數(shù)據(jù)定義、數(shù)據(jù)操縱和數(shù)據(jù)控制功能，是關系代數(shù)和關系演算的結合，也是關系型數(shù)據(jù)庫的標準語言。因此，學好SQL可以為我們學習其他關系型數(shù)據(jù)庫打好基礎。

2、為什么需要數(shù)據(jù)庫

1、幾乎所有應用軟件的后臺都要用到數(shù)據(jù)庫；

2、數(shù)據(jù)庫存儲數(shù)據(jù)占用空間小，容易持久保存；

3、存儲比較安全；

4、容易維護和升級；

5、數(shù)據(jù)庫可移植性較好；

6、簡化了對數(shù)據(jù)的操作，便于用戶使用等。

關于數(shù)據(jù)庫的優(yōu)點還有很多，總而言之，學好數(shù)據(jù)庫十分重要。

Day2 SQL連接服務器

1、連接服務器

在連接之前一定要把SQLserver的服務打開，SQL的連接方式有兩種：Windows身份驗證和SQLserver身份驗證；Windows身份驗證需要登錄電腦，就可以直接進行連接；而SQLserver身份驗證則需要輸入用戶名和密碼，適用于非本地訪問數(shù)據(jù)庫。

2、連接方式問題

在安裝好的SQL中可能默認的驗證方式只有Windows驗證，如果想啟用SQL驗證方式，需要進行設置：首先用Windows驗證方式連接服務器；然后右鍵服務器，找到屬性，點擊進入，再進入安全性，將設置更改為SQL server和Windows身份驗證模式，并且保存更改即可。

3、SQL驗證問題

如果忘記了SQLserver驗證方式的密碼或者想修改SQLserver驗證方式的密碼，則可以先用Windows驗證方式連接，然后進行如圖操作，就可以修改密碼，修改需要重啟SQL服務才能生效。（ps：不管你的密碼是多少位，查看時顯示都是15位，因為會自動產生一些亂碼，所以不必驚慌）

如果修改密碼并重啟服務后仍然密碼錯誤，那么可能是沒有設置狀態(tài)：如下圖，將狀態(tài)設置為授予連接到數(shù)據(jù)庫、啟用登錄即可。

4、系統(tǒng)數(shù)據(jù)庫

系統(tǒng)數(shù)據(jù)庫是用來維護用戶所建立的數(shù)據(jù)庫，最核心的庫是master庫，因此不要對系統(tǒng)數(shù)據(jù)庫作出任何修改。

Day3 界面操作-數(shù)據(jù)庫、表

1、數(shù)據(jù)庫

1.1、創(chuàng)建數(shù)據(jù)庫

步驟如圖：數(shù)據(jù)庫文件包括后綴名為.mdf和后綴名為.ldf的兩個文件，.ldf文件為日志文件。

1.2、數(shù)據(jù)庫的刪除

1.3、數(shù)據(jù)庫的分離和附加

如果想對數(shù)據(jù)庫進行移動，則要用到分離和附加操作，因為SQL是服務型數(shù)據(jù)庫，當SQL處于服務使用時，不能對其內部的數(shù)據(jù)庫進行移動。

分離：分離時要刪除連接，并且更新系統(tǒng)。

附加：

1.4、生成腳本（架構+數(shù)據(jù)）

如果不想移動數(shù)據(jù)庫文件，可以生成腳本，腳本文件后綴名是.sql，可以用記事本打開，用于存放操作數(shù)據(jù)庫的代碼，在其他電腦上直接運行某數(shù)據(jù)庫的腳本代碼即可得到與原數(shù)據(jù)庫一模一樣的數(shù)據(jù)庫，就可以不用移動原數(shù)據(jù)庫文件了。（在生成腳本時要在高級選項中將要編寫腳本的數(shù)據(jù)的類型設置為架構與數(shù)據(jù)（見下圖），腳本僅架構則僅生成創(chuàng)建表的代碼，架構和數(shù)據(jù)則生成創(chuàng)建表和添加數(shù)據(jù)的代碼）

2、表

2.1、表的新建

2.2、唯一標識

每個表都包含唯一標識，用來保證表中數(shù)據(jù)的唯一性，為了便于維護標識，我們將標識交給SQL進行維護；我們以將UserId作為標識為例，如下圖設置標識（數(shù)據(jù)類型是int時才可以設置為標識），默認增量標識增量為1，起始值標識種子為1，在添加數(shù)據(jù)到該表中時，SQL自動維護標識的值。

2.3、主鍵

一般將唯一標識設置為主鍵。因為標識唯一，因此如果值重復將會報錯；并且可以提高檢索速度，因為鍵也屬于索引，但是不強制設置。

2.4、第一個表

輸入好數(shù)據(jù)，保存并輸入表名，一個表就創(chuàng)建好了。

3、常用字段類型

3.1、字符串類型

SQL中沒有string類型，SQL的字符串類型有：char/varchar/nvarchar；

是否含n的區(qū)別：

char和varchar表示采用非unicode編碼，即如果是英文或阿拉比數(shù)字則占一個字節(jié)，如果是中文則占兩個字節(jié)；nvarchar表示采用unicode編碼，即無論是英文還是中文都占一個字節(jié)；一般來說數(shù)據(jù)中含中文就使用nvarchar。

是否含var的區(qū)別：

char表示長度固定，不可變，如果數(shù)據(jù)長度不夠，會在末尾補空格；varchar和nvarchar則長度可變，即使數(shù)據(jù)長度不夠，也不會補空格。

3.2、常用類型

浮點數(shù)類型decimal(a,b)：a用來設置小數(shù)的位數(shù)（不包括小數(shù)點），b用來設置精確度；由于decimal比較靈活，因此使用較多。

時間類型datetime：用來存放日期和時間，也可以分開存放日期、時間。

位類型bit：只能存放0和1，比如可以使用bit來表示男女性別存放到數(shù)據(jù)庫。

Day4 數(shù)據(jù)庫的約束

數(shù)據(jù)庫的一個重要特點就是能夠保證數(shù)據(jù)的有效性，而約束就是用來實現(xiàn)數(shù)據(jù)的有效性檢查。

1、主鍵

主鍵的值用于唯一的標識表中的某一條記錄；在兩個表的關系中，主鍵用來在一個表中引用來自于另一個表中的特定記錄；并且主鍵不能為空。

2、非空

非空即不允許值為空。

3、唯一

唯一即要求存入的數(shù)據(jù)不能重復；如果在設置了唯一鍵的那一列添加重復數(shù)據(jù)將會報錯。

設置唯一鍵：

4、默認

默認即設置一個默認值，如果設置了默認值的那一列沒有數(shù)據(jù)填入，將自動填入設置好的默認值。

5、檢查

檢查用來檢查插入的數(shù)據(jù)是否滿足要求。

設置檢查步驟：約束表達式中的返回值是布爾類型，true則滿足約束，可以插入，反之，不可以插入。（ps：year(regDate)用來取regDate中的年份）

6、外鍵

外鍵表示兩個關系之間的聯(lián)系，用來檢查關系的有效性。

設置外鍵：

外鍵表即被決定的那個表，主鍵表則是起決定作用的那個表。

Day 5 腳本創(chuàng)建數(shù)據(jù)庫（SQL語句）

1、前言

DDL：數(shù)據(jù)定義語言，用于進行各種數(shù)據(jù)庫對象的創(chuàng)建，主要操作包括create、alter、drop；

DML：數(shù)據(jù)管理語言，用于對表數(shù)據(jù)的添加、修改、刪除、查詢，主要操作包括insert、update、delete、select；

DCL：數(shù)據(jù)控制語言，用于進行權限分配等。

注釋：--單行注釋、/**/多行注釋。

2、創(chuàng)建數(shù)據(jù)庫

Day6 SQL基本常用命令

1、創(chuàng)建表

示例:在數(shù)據(jù)庫dbtest中創(chuàng)建表StudentInfo，主鍵primary key為sId，標識identity為sId，外鍵foreign key為cid。

2、數(shù)據(jù)插入insert

單一列插入、某幾列插入、全部列插入、一次性插入多行：

ps：
into可以省略不寫；
插入的列名數(shù)與數(shù)據(jù)數(shù)要一致。
示例多行插入：在表ClassInfo中插入四行數(shù)據(jù)

3、數(shù)據(jù)修改update

修改所有行的列數(shù)據(jù)、修改滿足條件的行的列數(shù)據(jù)：

4、數(shù)據(jù)刪除delete

ps：from可以省略不寫。
示例標識的不連續(xù)：刪除表ClassInfo中cId=4的那一行，再插入的一行標識cId為5

5、清空truncate

清空即重置表。

6、數(shù)據(jù)查詢

6.1、查詢列select（列篩選）

6.2、查詢前n部分的數(shù)據(jù)top（行篩選）

6.3、排序order by

排序方法：
asc：從小到大
desc：從大到小

示例：查詢表StudentInfo所有列的數(shù)據(jù)，結果按照cid從大到小排列；如果值重復，則按照sId從小到大排列

結果：

6.4、消除重復行distinct

示例：表StudentInfo中的數(shù)據(jù)為：

查詢StudentInfo的列cid，并且去除重復行：

結果：

6.5、條件查詢where（行篩選）

查詢結果滿足where后面的條件表達式，即表達式要返回true。

比較運算符：=、>、>=、<、<=、!=或<>；
邏輯運算符：and、or、not；
在一個連續(xù)的范圍內：between...and...（閉區(qū)間）；
在一個非連續(xù)的范圍內：in。
連續(xù)范圍與不連續(xù)范圍示例：

6.6、模糊查詢like

模糊查詢：不知道具體值時的查詢，用于處理字符串類型的值。

示例1：在表StudentInfo中查詢姓楊的同學

示例2：在表StudentInfo中查詢姓楊的，并且名字兩個字的同學

示例3：在表StudentInfo中查詢sPhone是連續(xù)范圍[1-3]中的數(shù)據(jù)開頭的所有列

示例4：在表StudentInfo中查詢sPhone是非連續(xù)范圍[576]中的數(shù)據(jù)開頭的所有列

示例5：在表StudentInfo中查詢sPhone不以連續(xù)范圍[1-3]中的數(shù)據(jù)開頭的所有列

7、補充null、優(yōu)先級

null的判斷：使用is null、is not null（select 1+null結果為null，因為null表示不知道，而不是沒有）
函數(shù)ISNULL(列名,字段）
示例：

優(yōu)先級：小括號>not>比較運算符>邏輯運算符

Day7 SQL常用命令續(xù)集

1、連接查詢join...on

連接查詢適用于當需要的結果來自多張表時。
內連接：inner join，查詢兩表中完全匹配的數(shù)據(jù)；
左外連接：left outer join，查詢兩表中完全匹配的數(shù)據(jù)，以及左表特有的數(shù)據(jù)；
右外連接：right?outer join，查詢兩表中完全匹配的數(shù)據(jù)，以及右表特有的數(shù)據(jù)；
完全外連接：full outer join，查詢兩表中完全匹配的數(shù)據(jù)，以及左表特有的數(shù)據(jù)，還有右表特有的數(shù)據(jù)；

示例：

2、聚合函數(shù)

聚合函數(shù)用于對行數(shù)據(jù)進行合并，一般是對數(shù)字類型的列進行操作，一條查詢中可以寫多個聚合函數(shù)（null不參與運算）。
常用聚合函數(shù)：sum（求和）、avg（求平均值）、count（計數(shù)）、max（求最大值）、min（求最小值）。

sum示例：計算表StudentInfo中列cid的和，并取別名為sum

avg示例：計算表StudentInfo中列cid的平均值

count示例：計算StudentInfo表中一共多少行，如果存在某行的值全為null，則不計數(shù)

max示例：查詢表StudentInfo的列cid的最大值

min示例：查詢表StudentInfo的列cid的最小值

3、開窗函數(shù)over()

開窗函數(shù)和聚合函數(shù)結合使用，用于將聚合結果還原至原數(shù)據(jù)，便于將聚合結果與原數(shù)據(jù)進行對比。
開窗函數(shù)還可以和排名函數(shù)ROW_NUMBER()結合使用。

結合聚合函數(shù)示例：計算表StudentInfo的列cid的平均值，并利用開窗函數(shù)與原cid進行對比

結果：

結合排名函數(shù)示例：將表StudentInfo中sGender=1的數(shù)據(jù)進行排序，并且進行排號

結果：

4、分組group by

聚合函數(shù)一般結合分組使用，進行分組內的數(shù)據(jù)統(tǒng)計；分組依據(jù)值相同的示例在一組，在結果列中只能出現(xiàn)分組依據(jù)列和聚合列。
ps：group by與where共存時，group by寫where后面。

單一列依據(jù)分組示例：將表studentInfo按照sGender進行分組，并計算每組數(shù)量

結果：

多列分組示例：將表StudentInfo按照sGender和cid進行分組，并計算每組數(shù)量

結果：

5、分組結果篩選having

示例：將表StudentInfo按照cid進行分組，并且計算每組數(shù)量，篩選出數(shù)量大于1的組

6、聯(lián)合查詢union

聯(lián)合查詢：將多個查詢的結果集合并成一個結果集。
聯(lián)合要求：
結果集列數(shù)一致；
對應列的類型一致。

關鍵字：
union：將多個結果集的數(shù)據(jù)進行合并，并且消除重復行，按照第一列從小到大排序；
union all：將多個結果集進行合并，但不消除重復行，也不排序；
except：差集，A except B表示A結果集中的數(shù)據(jù)但不包括B結果集中的數(shù)據(jù)；
intersect：交集，結果集中都有的數(shù)據(jù)。

union示例：

結果：

union all示例：

結果：

except示例：

結果：

intersect示例：

結果：

7、快速備份

向未有表備份：select 列名 into 備份表名 from 源表名。
（備份表如果不存在將新建表，表的結構完全一致，但是不包含約束，如果想只包含結構不包含數(shù)據(jù)，可以加個top 0）
示例：將表ClassInfo的結構和數(shù)據(jù)快速備份到表test1，將自動新建表test1

向已有表備份：insert into 備份表名 select 列名 from 源表名。
示例：向已有表test2中備份表ClassInfo的列cTitle

8、內置函數(shù)

8.1、類型轉換函數(shù)

cast(expression as date_type)：將任意類型轉到任意類型
convert(date_type ,expression[,style])：將任意類型轉到任意類型，如果目標類型是字符串，則style可以設置樣式
示例：將89.0000轉換成89.0

8.2、字符串函數(shù)

ascii：求字符的ascii碼值；
char：根據(jù)ascii碼轉到字符；
left：自左開始往右截取字符串；
right：自右開始往左截取字符串；
substring：從任意位置開始截取字符串，函數(shù)參數(shù)：字符串、開始索引、截取數(shù)量（索引從1開始）；
len：返回字符串的長度；
lower：轉小寫；
upper：轉大寫；
ltrim：去除左側空格；
rtrim：去除右側空格；

示例：

8.3、日期函數(shù)

getDate：獲取當前日期時間；
dateAdd：日期加；
dateDiff：日期差；
datePart：取日期的某部分
year：取年；
month：取月；
day：取日。
（返回值都是int類型）

示例：

Day8 SQL語句尾篇

1、視圖

視圖：將一個復雜的select語句進行封裝，以更方便使用，實現(xiàn)了代碼復用。
視圖中存儲的是select語句，而不是結果集數(shù)據(jù)；視圖可以進行增、刪、改，但是盡量不要對視圖進行這些操作，視圖主要進行查詢操作。
語法：create/alter view 名稱 as select ...

示例：創(chuàng)建視圖，名稱為StudentInfo_ClassInfo；使用、刪除視圖

2、子查詢

將一個查詢語句嵌入另一個查詢語句中，稱這種查詢?yōu)樽硬樵儭?br>常用運算符：= in exists
in與exists的差別：數(shù)據(jù)比較大時，exists效率比in高。
示例：查詢表ClassInfo中的列cId與表StudentInfo中cid匹配的數(shù)據(jù)

3、分頁

已知：頁大小、頁索引
起始索引：(pageIndex-1) × pageSize+1
結束索引：pageIndex × pageSize
示例：已知頁大?。?，頁索引：2，則起始頁：5，結束頁：8

4、數(shù)據(jù)透視case

數(shù)據(jù)透視完成數(shù)據(jù)的行列轉換。
示例：將視圖StudentView中列cid對應的男、女列出來，如果值為null，則列出無，并且按照cid從小到大排序

結果：

5、Transact-SQL編程

t-sql編程是只適用于SQL server的語句，即SQL特有語句。

5.1、變量

聲明：declare 變量名 類型（變量名要求以@開頭）
設置：set/select 變量名=值
輸出：print/select 變量名
示例：

內置全局變量：使用雙@符號
@@version：數(shù)據(jù)庫版本
@@identity：進行插入后調用，返回最近的insert語句的標識值
@@servername：服務器名稱
@@error：返回執(zhí)行的上一個Transact-SQL語句的錯誤號，如果沒有返回0
@@rowcount：返回受上一句語句影響的行數(shù)

5.2、選擇語句if

語法：
if 條件
begin
--滿足條件執(zhí)行
end
else begin
--不滿足條件執(zhí)行
end
示例：輸出OK

5.3、循環(huán)語句while

語法：
while 邏輯表達式
begin
循環(huán)體
end
示例：

5.4、異常處理語句

語法：
begin try
--可能出錯的語句
end
begin catch
--捕獲后執(zhí)行的語句
end catch
示例：

5.5、事務

事物保證了數(shù)據(jù)的準確性、有效性和可恢復性；當一個事件有多種操作，而其中一個操作出現(xiàn)了問題，那么事務可以讓事件回到做之前的狀態(tài)，保證了事件所有操作正確完成。
只有數(shù)據(jù)改變（增加、修改、刪除）時才會引發(fā)事務，查詢不會引發(fā)事務。

分類：
顯式事務（需要手動控制）
隱式事務（不需要手動控制，默認采用此方式）
顯式事務語法：
begin transaction --開始事務
commit transaction --提交，沒錯后執(zhí)行
rollback transaction --回滾，出錯后執(zhí)行
示例：因為操作2出錯，因此回滾事務，所以回到最開始，操作1也不會執(zhí)行

鎖模型

鎖：當對數(shù)據(jù)進行修改操作時，將自動添加鎖，防止多個人員同時操作數(shù)據(jù)產生數(shù)據(jù)錯誤。