C# 學習筆記（18）操作SQL Server 中

資料庫基礎操作

SQL語法可以參考 菜鳥教程 或者微軟官方的SQL示例
注意SQL不區分大小寫

查

1.基礎查詢

--最基礎的查詢陳述句， select * from table_name
select * from studentTable

2.條件查詢

--條件查詢 select * from table_name where 條件
select * from studentTable where studentTable.gradeID = 5

3.條件查詢 between and

--條件查詢 select * from table_name where 條件
select * from studentTable where studentTable.cityID between 1 and 7  --查找 studentTable.cityID 在 1-7 范圍內的行

4.多條件查詢 and

--多條件查詢 select * from table_name where 條件 and 條件
select * from studentTable where studentTable.cityID between 1 and 7 and studentTable.gradeID = 7

5.字符匹配 like

--多條件查詢 select * from table_name where 條件 and 條件      studentTable.name like '%七%' studentTable.name中含有字符 七  的行   %為通配符 匹配0 - 多個字符 
select * from studentTable where studentTable.cityID between 1 and 7 and studentTable.name like '%七%'

6.按需求提取列

--條件查詢 + 提取需要列  select table_name.列名1, table_name.列名2 from table_name where 條件
select studentTable.name as 姓名, gender as 性別,  address as 地址 from studentTable where studentTable.gradeID = 5

7.聯合查詢

--聯合查詢  兩個表的資料組合  
select * from studentTable
inner join cityTable on cityTable.id = studentTable.cityID  --將cityTable.id 等于 studentTable.cityID的兩個表的行合并成一行

8.多表聯合查詢

--聯合查詢  多個表的資料組合 
select studentTable.id as 學生編號, studentTable.name as 姓名, genderTable.name as 性別, 
gradeTable.name as 班級,  (provinceTable.province + cityTable.cityName + isNull(studentTable.address, '')) as 家庭地址
from studentTable 
inner join cityTable on cityTable.id = studentTable.cityID   
inner join provinceTable on cityTable.provinceID = provinceTable.id
inner join genderTable on genderTable.id = studentTable.gender
inner join gradeTable on gradeTable.id = studentTable.gradeID

9.嵌套查詢

--嵌套查詢   可以在查詢陳述句結果的基礎上多次進行篩選嵌套  
select * from
(select studentTable.id as 學生編號, studentTable.name as 姓名, genderTable.name as 性別, 
gradeTable.name as 班級,  (provinceTable.province + cityTable.cityName + isNull(studentTable.address, '')) as 家庭地址
from studentTable 
inner join cityTable on cityTable.id = studentTable.cityID   
inner join provinceTable on cityTable.provinceID = provinceTable.id
inner join genderTable on genderTable.id = studentTable.gender
inner join gradeTable on gradeTable.id = studentTable.gradeID) as t1 
where 性別 = '男'

10.排序

--對查詢結果排序  order by  列名  
select * from studentTable where cityID  = 7
order by studentTable.id desc  --desc 遞減排序

11.分組

--對查詢結果分組， 然后使用count(*) 統計各個分組的行數
select cityID, count(*) as 數量 from studentTable
group by cityID

group by cityID 會對相同的cityID值的行劃分成一組，如下圖cityID為7的共有4行，這四行劃分成了一組，也就是只能顯示一行，因此這個表中只能顯示cityID列 和 聚合函式生成的列，不能顯示姓名這樣的列(四個姓名顯示誰的？)

--對查詢結果分組， 然后使用count(*) 統計各個分組的行數
select cityID, count(*) as 數量 from studentTable
group by cityID having cityID > 7  --having 限定cityID范圍 只對范圍內的cityID進行分組

索引

查詢時，where后面經常使用的列（非主鍵列）可以將其設定為索引，用空間來換取時間，當資料量大時可以顯著加快查詢時間

聚合函式

微軟檔案

其他聚合函式操作類似

--查詢學生資訊表
select * from studentTable
--查詢學生資訊表行數
select count(cityID) from studentTable
--聚合函式引數前加 distinct 關鍵字后，重復的列只統計一次
select count(distinct cityID) from studentTable

開窗函式

上面的聚合函式查詢出來是一個值，當想要將這個值添加到表中，就需要將這個值變成一列然后添加到表中（不然只有一個值，放到表中的哪一列？），over（）函式也就是開窗函式就是干這個的，將聚合函式查出來的結果變成一列

--開窗
select *, count(*) over() as 總行數 from studentTable

over()函式也可以填入以下三種關鍵字（可以全部用上，也可以只用其中一種）

• PARTITION BY：將查詢結果集分為多個磁區，

--開窗
select *, count(*) over(partition by cityID) as 總行數 from studentTable

先獲取 select * from studentTable 的查詢結果，然后根據cityID對查詢結果分組，最后對每組的行數進行count(*)統計并將資料變成一列添加到查詢結果中

• ORDER BY：定義結果集的每個磁區中行的邏輯順序，

--開窗
select *, count(*) over(order by cityID) from studentTable

先獲取 select * from studentTable 的查詢結果，然后根據 cityID 列的值進行排序分組（order by 有起始行和結束行引數，這里沒有填默認為第一行到當前行），最后對每組的行數進行count(*)統計并將資料變成一列添加到查詢結果中

最常用的就是給結果集加上序號，查詢結果最左側的序號是資料庫管理工具提供的，并不是查詢結果集的內容，id是主鍵，不可以當作行序號（例如身份證號，前面的人去了，排在后面的人身份證號并不會改變，主鍵也是如此，前面資料洗掉后，主鍵號也不會改變，因此主鍵并不一定連續，）

--開窗
select *, ROW_NUMBER() over(order by id) as 行序號 from studentTable

• ROWS/RANGE：通過指定磁區中的起點和終點來限制磁區中的行數， 它需要 ORDER BY 引數，如果指定了ORDER BY 引數，則默認值是從磁區起點到當前元素

沒用過

增

添加資訊時注意外鍵，例如cityID就是一個外鍵，添加時一定要確保城市資訊表中存在該序號的主鍵，否則會添加失敗，

--向表 studentTable 中插入資料 資料順序 (gradeID, name, gender, cityID, address)
insert into studentTable(gradeID, name, gender, cityID, address)
Values(8,'法外狂徒張三', 1, 1, '寡婦村')

--向表 studentTable 中插入多條資料 資料順序 (gradeID, name, gender, cityID, address)
insert into studentTable(gradeID, name, gender, cityID, address)
Values(8,'法外狂徒張三', 1, 1, '寡婦村'),
(5,'法外狂徒李四', 0, 1, '鰥夫村')

刪

洗掉時需要注意外鍵問題，比如要洗掉cityTable中的一個城市資訊，則需要保證外鍵（學生資訊表中cityID沒有使用對應的城市），否則不能洗掉，需要先洗掉學生資訊表使用該城市的學生資訊，然后才能洗掉cityTable中的城市資訊，其實還是為了確保學生資訊表中cityID列中所有城市都可以在cityTable中查詢到，

--洗掉 studentTable 表中 名字是 法外狂徒張三的列
delete from studentTable where studentTable.name = '法外狂徒張三'

改

--將studentTable 表中 studentTable.name = '法外狂徒李四' 的行的 gender更新為1
update studentTable set studentTable.gender = 1 where studentTable.name = '法外狂徒李四'

添加視圖

在將列和表設計為最小狀態后，查詢需要的資料時不可避免的需要多個表聯合查詢，每次都要聯合查詢很是麻煩，SQL提供了視圖來解決這個問題

--聯合查詢  多個表的資料組合 
select studentTable.id as 學生編號, studentTable.name as 姓名, genderTable.name as 性別, 
gradeTable.name as 班級,  (provinceTable.province + cityTable.cityName + isNull(studentTable.address, '')) as 家庭地址
from studentTable 
inner join cityTable on cityTable.id = studentTable.cityID   
inner join provinceTable on cityTable.provinceID = provinceTable.id
inner join genderTable on genderTable.id = studentTable.gender
inner join gradeTable on gradeTable.id = studentTable.gradeID

這種經常使用但是又臭又長查詢就可以做出視圖

--創建視圖 studentInfoTable
create view studentInfoTable
as
select studentTable.id as 學生編號, studentTable.name as 姓名, genderTable.name as 性別, 
gradeTable.name as 班級,  (provinceTable.province + cityTable.cityName + isNull(studentTable.address, '')) as 家庭地址
from studentTable 
inner join cityTable on cityTable.id = studentTable.cityID   
inner join provinceTable on cityTable.provinceID = provinceTable.id
inner join genderTable on genderTable.id = studentTable.gender
inner join gradeTable on gradeTable.id = studentTable.gradeID

創建視圖后，就可以對視影像表一樣操作，但是視圖并不是重新創建了一張表，只是把上面那一坨陳述句的查詢結果當作了表，

如果想要修改視圖，將創建視圖的關鍵字 create 改為 alter 即可

添加存盤程序

存盤程序和函式類似，可以像呼叫函式一樣呼叫存盤程序

--創建存盤程序 該存盤程序有一個引數 @gradeName 型別為 nvarchar(20)
create proc GetStudentsInfoFromGrade
	@gradeName nvarchar(20)  --引數 班級名稱
as
begin
    --查詢指定班級的學生資訊
	select * from studentInfoTable where 班級 = @gradeName
end

--呼叫存盤程序
exec GetStudentsInfoFromGrade '一年級一班'

如果想要修改存盤程序，將創建存盤程序的關鍵字 create 改為 alter 即可

資料透視

資料透視，起始就是行列轉換，例如想要看每個班級中男女個數，可以將性別 男 女作為列名，班級作為行名，

--資料透視
select 班級, count(case when 性別 = '男' then 學生編號 end) as 男, 
count(case when 性別 = '女' then 學生編號 end) as 女 from studentInfoTable
group by 班級        --分組
order by 班級 desc   --排序

資料分頁

當資料量比較大時，由于記憶體大小有限，就需要對查詢結果進行分頁處理

進行分頁前，首先要對資料進行排序添加序號

--排序并添加序號 
select *, ROW_NUMBER() over(order by id) as 行序號 from studentTable

查詢結果最左側的序號是資料庫管理工具提供的，并不是查詢結果集的內容，id是主鍵，不可以當作行序號（例如身份證號，前面的人去了，排在后面的人身份證號并不會改變，主鍵也是如此，前面資料洗掉后，主鍵號也不會改變，因此主鍵并不一定連續，）

創建一個存盤程序

--獲取一頁學生資訊
create proc GetStudentInfoPage
	@pageIndex int, --要查詢的頁序號
	@pageSize  int  --頁大小
as
begin
	select * from (select *, ROW_NUMBER() over(order by id) as 行序號 from studentTable) as t1  --嵌套查詢
	where 行序號 between (@pageIndex-1)*@pageSize+1 and @pageIndex*@pageSize   --根據頁序號和頁大小確定篩選條件
end

--查詢第二頁  一頁兩個資料
exec GetStudentInfoPage 2, 2