第一章 資料庫的設計
1.1 資料庫設計
資料庫中建立的資料庫結構的種類，以及在資料實體之間建立的複雜關系是決定資料庫系統效率的重要因素。
糟糕的資料庫設計表現在以下兩個方面：
*效率低下
*更新和檢索資料時會出現許多問題
*資料操作異常、修改複雜、資料備援
*程式性能受到影響
良好的資料庫設計表現在以下幾方面：
*效率高
*便于進一步擴充
*使得應用程式的開發變得更容易
1.2 設計資料庫的步驟
*需求分析階段：分析客戶的業務和資料處理需求
*概要設計階段：繪制資料庫的Ｅ－Ｒ圖，用于在項目團隊内部、設計人員和客戶之間進行溝通，确認需求資訊的正确性和完整性
*詳細設計階段：将Ｅ－Ｒ圖轉換成多張表，進行邏輯設計，确認主外鍵關系，應用資料庫設計的三大範式稽核。之後選擇具體的資料庫（ＳＱＬ Ｓｅｒｖｅｒ等）實體實作。建立完畢後開始進入代碼編寫階段，開發前端應用程式。
1.3 概念設計---繪制E-R圖
1.實體
實體就是指現實世界中具有區分其他事務的特征或屬性并與其他實體有聯系的實體
例如：酒店管理系統中的客房（如1008客房，1018客房等），客人（如張三，李四，王五等）等。
實體一般是名詞，它對應表中的一行資料，嚴格地說，實體是指表中一行特定資料，但我們在開發時，也常常把整個表稱為一個實體
2. 屬性
屬性可以了解為實體的特征，屬性對應表中的列
3.聯系
聯系是兩個或多個實體之間的關聯關系
實體用矩形表示，屬性用橢圓表示，聯系用菱形表示
4. 映射基數
映射基數
一對一 １：１ 一對多 １：Ｎ
多對一 Ｎ：１ 多對多 Ｍ：Ｎ
1.4 資料規範化
不規範的設計：
資訊重複、更新異常、插入異常（無法表示某些資訊）、删除異常（删除有用資訊）
規範設計 在資料庫設計時，有一些專門的規則，成為資料庫的設計範式
第一範式
確定每列的原子性，如果每列或者每個屬性值都是不可再分的最小資料單元，則滿足第一範式
例：學生的電話和住址
作為一個列可以再分為電話和住址兩列 故該表不滿足第一範式
第二範式
確定表中的每列都和主鍵相關。如果滿足第一範式，并且除了主鍵以外的其他列都依賴于該主鍵，則滿足第二範式
例：學生表中有一個考試日期列
而考試日期與學生編号并無關聯 故不滿足第二範式
第三範式
是在第二範式的基礎上更近一層，確定每列都和主鍵列直接相關，而不是間接相關。如果一個關系滿足第二範式，并且除主鍵外的其他列都隻能依賴于主鍵列，列和列之間不 存在互相依賴關系，則滿第三範式
例：科目表包含了科目編号，
名稱和對應年級 學生表包含了學号…………學生學習的科目列
科目和學号有關聯 但是科目和年級也有關聯 是以不符合第三範式
注意：
資料庫滿足的範式越高，其資料通路性能越低
通常按照資料庫的第三設計範式稽核資料庫模型圖中試圖的結構
分析需求收集資訊→繪制ＥＲ圖→交流溝通→繪制資料庫模型圖
第二章 資料庫的實作
1.1 T-SQL語句（增，删，改，查）
1.增(添加語句)
insert into 表名(列名1,列名2,列名3……) values (值1,值2,值3……)
2.删(删除資料)
delete from 表名 where(條件)
3.改(修改資料)
update 表名 set 列1=值1,列2=值2,……where(條件)
4.查(查詢資料)
select 列1,列2,……from 表名 where (條件) order by (列名)
2.1 使用SQL語句建立和删除資料庫
資料庫檔案由以下3部分組成
1.主資料檔案：*.mdf
2.次要資料檔案：*.ndf
3.日志檔案：*.ldf
每個資料庫至少要包含兩個檔案：一個資料檔案和一個日志檔案，資料檔案中包含了資料庫的資料和對象，如表，視圖和索引等；
日志檔案中包含了用于恢複資料庫所需的資訊。建立資料庫時，次要檔案可選。一個資料庫可以有多個資料庫檔案和多個日志檔案。
2.2 建立，删除資料庫
文法：
use master --設定目前資料庫為master,以便通路 sysdatabases 表
go
if exists (select * from sysdatabases where name='資料庫名') --查詢是否有該資料庫
drop database 資料庫名 --如果有則删除
create database 資料庫名 --建立資料庫
on primary
(
--主資料檔案的具體描述
name='資料庫名_data'， --邏輯檔案名
filename='D:\project\資料庫名_data.mdf'， --實體檔案名
size=5MB , --大小
maxsize=100MB, --最大容量
filegrowth=15% --增長量
)
log on
(
--日志檔案的具體描述
name='資料庫名_log', --日志檔案名
filename='D:\project\資料庫名_data.log' , --日志檔案位址
size=2MB, --大小
filegrowth=1MB --日志檔案增長量
)
go
2.2 建立，删除資料庫
use 資料庫名 --設定目前資料庫為已建立的資料庫,以便在該資料庫中建表
go
if exists (select * from sysobjects where name='表名') --查詢在該資料庫中是否有該表
drop table 表名 --如果有則删除
create table 表名 --建立資料庫
（
列名 資料類型 列的特征
StudentNo int identity(1,1) not null, --學号，整數型，自增（從1開始，依次加1）非空（必填）
Phone nvarchar(50) null --聯系電話，字元串型，可以為空
）
go
2.3 使用SQL語句建立和删除限制
--添加主鍵限制，要求主鍵列資料唯一，并且不允許為空(将studentNo作為主鍵)
alter table student
add constraint PK_studentNo primary key (studentNo)
--添加唯一限制，要求該列的值必須唯一，允許為空(身份證号唯一,因為每個人的身份證号全國唯一)
alter table student
add constraint UQ_identityCard unique (identityCard)
--添加預設限制(如果不填定位址,則預設為”位址不詳”)
alter table student
add constraint DF_address defatult (‘位址不詳’) for address
--添加檢查限制(要求出生日期在1980年1月1日之後)
alter table student
add constraint CK_bornDate check (bornDate>=‘1980-01-01’)
--添加外鍵限制(主表student和從表result建立關系關聯列為studentNo)
alter table result
add constraint FK_studentNo foreign key (studentNo) references student(studentNo) go
--删除限制
alter table 表名
drop constraint 限制名
例，删除Student表中位址列預設限制的語句如下
alter table student
drop constraint DF_Address
第三章 SQL程式設計
1.1 局部變量
T-SQL 中，局部變量的名稱必須以标記@作為字首。
文法，
set @變量名 變量類型
declare @變量名 變量類型
例，
set @num int --聲明一個存放學号的變量
declare @name varchar(8) --聲明存放姓名變量名，最多可以存儲8個字元
示例：
--查找李文才的資訊
declare @name varchar(8) --學生姓名
set @name='李文才' --使用set指派
select StudentNo,StudentName,BornDate,Address from Student
where [email protected]
--查找與李文才學号相鄰的學生資訊
declare @StudentNo int --學号
--使用select 指派
select @StudentNo=StudentNo from Student where [email protected]
select StudentNo,StudentName,BornDate,Address from Student
where ([email protected]+1) or ([email protected])
go
從以上示例可以看出，局部變量可用于在上下語句中傳遞資料
set和select的差別
1. set一次隻能為一個變量指派,而select能同時為多個變量指派
2. set隻能賦一個固定的值,而select能動态的指派(值來自于表内)
select的作用
3. 當表達式傳回多個值時，set 将會報錯，select 将傳回的最後一個值賦給變量
4. 當表達式傳回值時，set 變量被指派為NULL，select 變量保持原值
示例：
declare @addr nvarchar(100),@name nvarchar(100) --聲明多個局部變量
set @addr =' ', @name='張三' --發生文法錯誤
select @addr='北京', @name='張三' --為兩個局部變量@addr和@name指派
set @addr=(select Address from Student) --發生文法錯誤
select @addr=Address from Student --最後一條記錄的Adress列值
set @addr=(select Address from Student where 1<0)
--查詢無結果時，@addr被指派為Null
select @addr='北京'
select @addr=Address from Student where 1<0
--查詢無結果時，@addr保持原值
1.2 全局變量
SQL Server 中所有的全局變量都用兩個@@符号作為字首
@@error --最後一個T-SQl錯誤的錯誤号
@@identity --最後一次插入的辨別值
@@servername --本地伺服器名稱
@@version --SQL Server的版本資訊
1.3 輸出語句
文法：
print 局部變量或字元串
select 局部變量 as 自定義列名
使用print 語句要求以單個變量或字元串表達式作為參數，而“+”運算符作為連接配接兩個字元串的連結符，
要求“+”運算符兩側的操作數的資料類型必須一緻
print '目前錯誤号'+convert(varchar(5),@@error)
2.1 資料類型轉換
文法：
cast (表達式 as 資料類型)
cast(@studentno as nvarchar(32))
convert(資料類型，表達式)
convert(nvarchar(32),@studentno)
兩者的不同:
convert()可以轉換日期格式，而cast()不可以
convert(nvarchar(32),'2016-08-01',120)
2.2 邏輯控制語句
1. if-else語句
聲明變量number,并指派,然後判斷是偶數還是奇數,結果如下:
目前值為11,它是一個奇數
declare @number int
set @number=12
if(@number%2=0)
print '該數為偶數'
else
print '該數為奇數'
2. while 語句
輸出九九次'我愛你'
declare @i int=1
while(@i<=99)
begin
print '第'+convert(varchar,@i)+'我愛你'
set @i+=1
end
不停的提高學生筆試成績2分,讓所有學生的筆試成績都及格
declare @count int --用來記錄不及格的人數
while(1=1)
begin
--計算不及格的人數
select @count=COUNT(*) from StuExam
where writtenExam<60
--判斷
if(@count=0)
break --退出死循環
else
update StuExam set writtenExam+=2
end
select * from StuExam
2.3 case 多分支語句
文法：
case
when 條件1 then 結果1
when 條件2 then 結果2
end
set @result=case
when @age<12 then '國小生'
when @age<17 then '國中生'
when @age<22 then '高中生'
when @age<28 then '大學生'
else '超人'
end
--輸出
print @name+'是一個'[email protected]
第四章 進階查詢
1.1 IN 和 NOT IN 子查詢
1.IN 子查詢
IN:确定給定的值是否與子查詢或清單中的值相比對,如果比對則傳回真，可以傳回多條記錄。
使用方法： 在需要子查詢傳回多資料時使用。
文法：
select 清單 from 表名 where 列名 in（子查詢）
2.NOT IN子查詢
NOT IN:确定給定的值是否與子查詢或清單中的值相比對,如果不比對則反回真。
使用方法： 在需要子查詢傳回多資料時使用。
文法：
select 清單 from 表名 where 列名 not in（子查詢）
3.EXISTS 子查詢
EXISTS: exists 關鍵字能夠檢測資料是否存在，如果存在傳回真。
文法 if exists(子查詢) 語句
示例： 查詢本校學生有沒有叫張三的
if exists( select * from student where studentname='張三' )
begin
print '有叫張三的'
end
4.NOT EXISTS 子查詢
NOT EXISTS: exists 關鍵字能夠檢測資料是否存在，如果不存在傳回真
文法
if not exists(子查詢) 語句 示例： 查詢本校學生是不是沒有叫張三的
if not exists( select * from student where studentname='張三' )
begin
print '沒有叫張三的'
end
2.1 相關子查詢
1：非相關子查詢是獨立于外部查詢的子查詢，子查詢總共執行一次，執行完畢後将值傳遞給外部查詢。
2：相關子查詢的執行依賴于外部查詢的資料，外部查詢執行一行，子查詢就執行一次。資料是否存在，如果不存在傳回真
示例：檢索出在work表中每一個部門的最高基本工資的職工資料
select * from work a where 基本工資=(select max(基本工資) from work b where a.部門名稱=b.部門名稱)
第六章 事務，視圖和索引
1.事務
事務時作為單個邏輯工作單元執行的一系列操作，一個邏輯單元必須有4個屬性
即：
1.原子性
2.一緻性
3.隔離性
4.持久性
執行事務
begin transaction --開始事務
commit transaction --送出事務
rollback transaction --復原事務
2.1 視圖
建立視圖
文法：
create view vw_name
as
<select 語句>
删除視圖
文法：
drop view vw——name
示例：
if exists(select * from sysobjects where name='vw_result') --檢測視圖是否存在，視圖記錄在系統表sysobjects中
drop view vw_result --如果有則删除
go
create view vw_result --建立視圖
as
<select 語句>
go
select * from vw_result --檢視視圖結果
使用視圖注意事項：
每個視圖可以使用多個表
與查詢相似，一個視圖可以嵌套另一個視圖，但最好不要超過3層
視圖定義中的select 語句不能包括以下内容
*order by 子句，除非在select 語句的選擇清單中也有一個top子句
*into 關鍵字
*引用臨時表或表變量
2. 索引
索引分類：
1.唯一索引 (unique) --不允許具有相同的索引值
2.主鍵索引 --要求主鍵中的每一個值是非空，唯一的，當在查詢總使用主鍵索引時，它還允許快速通路資料
3.聚集索引 (clustered) --表中各行的實體順序與鍵值的邏輯（索引）順序相同
4.非聚集索引 (nonclustered) --一個表隻能建立一個聚集索引，但可以有多個非聚集索引，若設定某列為主鍵，則該列預設為聚集索引
--填充因子（fillfactor）
建立索引
文法：
create 索引類型 index 索引名
on 表名（列名）
示例：
--檢測是否存在該索引（索引放在系統表sysindexes中）
if exists(select name from sysindexes where name='ix_studentname' )
drop index Student.ix_studentname --删除索引（一定要在表名後顯示該索引）
--建立非聚集索引：填充因子為30%
create nonclustered index ix_studentname
on Student(StudentName)
with fillfactor=30 --填充因子30%
go
第七章 存儲過程
1.1 存儲過程的概念
存儲過程是在資料庫管理系統儲存的，預先編譯的，能實作某種功能的SQL程式，
它是資料庫應用中運用比較廣泛的一種資料對象。
1.2 為什麼需要存儲過程？
1.存儲過程隻在創造時進行編譯，以後每次執行存儲過程都不需再重新編譯，而一般SQL語句每執行一次就編譯一次,是以使用存儲過程可提高資料庫執行速度。
2.當對資料庫進行複雜操作時，可将此複雜操作用存儲過程封裝起來與資料庫提供的事務處理結合一起使用。
3.存儲過程可以重複使用,可減少資料庫開發人員的工作量。
4.安全性高,可設定隻有某些使用者才具有對指定存儲過程的使用權
1.3 存儲過程的優點：
1.子產品化程式設計
2.執行速度塊，效率高
3.減少網絡流量
4.具有良好的安全性
2.1 系統存儲過程
SQL_SERVER 提供系統存儲過程，它們是一組預編譯的T-SQL語句，系統存儲過程提供了管理資料庫和更新表的機制，
并充當從系統表中檢索資訊的快捷方式。
-----------常用的系統存儲過程-------------
exec sp_databases
不帶參數,列出伺服器 上的所有資料庫資訊,包括資料庫名稱和資料庫大小
exec sp_helpdb Result
報告有關指定資料庫或所有資料庫的資訊
exec sp_renamedb '舊資料庫名稱','新資料庫名稱'
更改資料庫名稱
exec sp_tables Student
傳回目前環境下可查詢的表或視圖的資訊
exec sp_columns Result
傳回某個表或視圖的列資訊,包括列的資料類型和長度等
exec sp_help student
檢視某個資料庫對象的資訊,如列名,主鍵,限制,外鍵,索引等
exec sp_helpconstraint Student
檢視某個表的限制
exec sp_helpindex
檢視某個表的索引
exec sp_stored_procedures Student
顯示存儲過程的清單
exec sp_password
添加或修改登入賬戶的密碼
exec sp_helptext
顯示預設值,未加密的存儲過程,使用者定義的存儲過程,觸發器或視圖的實際文本
3.1 使用者自定義的存儲過程
1.建立不帶參數的存儲過程
Create proc usp_selectstu
As
Select StudentName,Gender,GradeId,Phone from dbo.Student
調用存儲過程：exec usp_selectstu
2.建立帶入參數的存儲過程
Create proc usp_stuInfo @gradeid int=2 （預設）
As
Select * from student where [email protected]
調用存儲過程：exec usp_stuInfo 2
3.建立帶出參數的存儲過程
create proc usp_selectGrade @name nvarchar(10),@gradeid int output
As
Select @gradeid=gradeid from student where [email protected]
print @gradeid
調用存儲過程：
declare @id int
exec usp_selectGrade '李小龍',@id output
4.帶通配符參數存儲過程
Create proc usp_one @name nvarchar(10)
as
select * from dbo.Student where StudentName like @name
exec usp_one '李%'
5.不緩存存儲過程
緩存就是資料交換的緩沖區(稱作Cache)，當某一硬體要讀取資料時，會首先從緩存中查找需要的資料，
如果找到了則直接執行，找不到的話則從記憶體中找。由于緩存的運作速度比記憶體快得多，故緩存的作用就是幫助硬體更快地運作。
Sql Server系統記憶體管理在沒有配置記憶體最大值，很多時候我們會發現運作Sql Server的系統記憶體往往居高不下。這是由于他對于記憶體
使用的政策是有多少閑置的記憶體就占用多少，直到記憶體使用慮達到系統峰值時（預留記憶體根據系統預設預留使用為準，至少4M），才會
清除一些緩存釋放少量的記憶體為新的緩存騰出空間。
這些記憶體一般都是Sql Server運作時候用作緩存的，例如你運作一個select語句， 執行個存儲過程，調用函數；
1. 資料緩存：執行個查詢語句，Sql Server會将相關的資料頁（Sql Server操作的資料都是以頁為機關的）加載到記憶體中來，
下一次如果再次請求此頁的資料的時候，就無需讀取磁盤了，大大提高了速度。
2.執行指令緩存：在執行存儲過程，自定函數時，Sql Server需要先二進制編譯再運作，編譯後的結果也會緩存起來，
再次調用時就無需再次編譯。
create proc proc_temp
with recompile
as
select * from student
exec proc_temp
6.加密存儲過程
exec sp_helptext 儲存過程名 可以檢視儲存過程代碼
create proc proc_temp_encryption
with encryption
as
select * from student;
go
--存儲過程的内容不會被輕易看到（雖然解密也是有可能的）。
--應用這個，我們可以對某些關鍵的存儲過程進行加密。
--但此時，存儲過程仍然能被execute、alter和drop。
exec proc_temp_encryption;
exec sp_helptext 'proc_temp'
exec sp_helptext 'proc_temp_encryption'
（注意：加密存儲過程前應該備份原始存儲過程，且加密應該在部署到生産環境前完成。）
轉載于:https://www.cnblogs.com/ckwblogs/p/5767913.html
![ACS基本配置-權限等級管理[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)