SQL Server總結

1.資料庫管理系統(DBMS)是一種系統軟體,由一個互聯關聯的資料集合和一組訪問資料的程式結構.

2.在資料庫系統中,資料重複的現象就是資料冗餘.

3.SQL Server中的資料庫按照用途可以劃分為兩種.(系統資料庫和使用者資料庫)

4.SQL Server系統資料庫:

(1)Master資料庫:記錄SQL Server系統的所有系統層級資訊.(1.所有的登入賬戶和系統配置設定.2.所有其他的資料庫及資料庫檔案的位置.3.SQL Server的初始化資訊.)

(2)Tempdb資料庫:儲存所有的暫存資料表和暫存預存程序,以及臨時產生的工作表.

(3)Model資料庫:在系統上建立的所有資料庫的模板.

(4)Msdb資料庫:供SQL Server代理程式調度警報,作業以及記錄操作時使用.

5.訪問限制:指定哪些使用者可以訪問該資料庫.可能值有以下三種:

(1)Multiple:資料的正常狀態,允許多個使用者同事訪問該資料庫.

(2)Single:用於維護操作的狀態,一次只允許一個使用者訪問該資料庫.

(3)Restricted:只有管理員角色或者特定的成員才能使用該資料庫.

6.SQL Server啟動的時候,資料庫檔案是不能複製,粘貼和移動的.

7.資料完整性

（1）實體完整性：要求表中的每一行資料都反映不同的實體,不能存在相同的資料.(索引、唯一約束、主鍵約束或識別欄位屬性)

（2）值域完整性：給定列的輸入有效性(限制資料類型、檢查約束、輸入格式、外鍵約束、預設值、非空約束)

（3）參考完整性：在輸入或刪除資料行時，引用完整約束來儲存表之劍已定義的關係（主鍵與外鍵的參考關聯性來實現）。

（4）自訂完整性：定義特定的規則（資料庫的規則、預存程序、觸發器對象來進行約束）

8.T-SQL支援邏輯運算子有：And、Or、和Not。

9.使用Insert插入資料行

（1）插入單行資料

文法：insert [into] <表名> [values] <值列表>

例：insert into students (name,age,sex) values('Harry',20,'男')

（2）插入多行資料

文法：insert <表名> [values]  Select <值列表> union  Select<值列表> 例：insert students(name,age,sex)Select '張三',20,'男' unionSelect '李四',18,'女'

（3）通過 insert select語句將現有表中的資料添加到新表中

文法：insert [into] <表1><values>  Select vaules from <表2>例：insert into student1(姓名,年齡,性別)  Select name,age,sex from student2

10.使用update更新資料

文法：update <表名> set <列名=更新值>[where <更新條件>]例：update student set sex='男' where studentid=2

11.使用delete刪除資料

文法：delete from <表名> [where <刪除條件>]例：delete from student where studentid=2

12.使用turncate table刪除表中的所有行資料

文法：turncate table <表名>例：turncate table student

13.簡單查詢語句

例1：select * from students例2：select studentID,name,age,sex from students例3：select studentID,name,age,sex from students where studentID=10例4：select name from students where sex is null例5：select 姓名=name,年齡=age,性別=sex'河北新龍' as 學校名稱 例6：select top 5 name,age,sex from students where objectid=3 order by score desc(或者ASC升序排列)

14.使用like進行模糊查詢

例：select * from students where name like '張%'

15.使用between在某個範圍內進行查詢

例：selecr * from student where score between 60 and 80

16.彙總函式

Sum:求和Avg:平均數Max:最大值Min:最小值Count:求非空行的行數

17.分組查詢

（1）使用group by進行分組查詢例：select courseID，avg(score) as 課程平均成績 from score group by courseID（2）使用having子句進行篩選例：select courseID，avg(score) as 課程平均成績 from score group by courseID having 

18.where--->group by--->having

19.多表串連查詢

（1）內聯結：inner join（2）外聯結（1）左外聯結：left join 或者 left outer join（2）右外聯結：right join 或者 right outer join（3）完整外聯結：full join 或者 full outer join

20.設計資料庫步驟

（1）收集資訊

（2）標識實體

（3）標識每個實體需要儲存的詳細資料（屬性）

（4）標識實體之間的關係

21.資料庫3大範式

（1）第一範式（1NF）：列不可再分

（2）第二範式（2NF）：每個表只描述一件事情

（3）第三範式（3NF）：沒有傳遞依賴

T-SQL語句

1.使用T—SQL語句建立和刪除資料庫

Create database 資料庫名On [primary](<資料檔案參數>[,......n] [<檔案組參數>])[log on]({<記錄檔參數>  [,......n]})檔案的具體參數如下：Name=邏輯檔案名稱Filename=物理檔案名稱Size=檔案大小Maxsize=最大容量Filegrowth=增長量例：--調用CMD命令建立檔案夾exec sp_configure 'show advanced options',1goreconfiguregoexec sp_configure 'xp_cmdshell',1goreconfiguregoexec xp_cmdshell 'mkDir E:\Project'goif exists(select * from sysdatabases where name='MySchool')--判斷是否存在MySchool資料庫drop database MySchool--刪除資料庫Gocreate database MySchool--建立資料庫--主檔案on(name='MySchool_data',filename='E:\project\MySchool_data.mdf',size=10,filegrowth=20%)--記錄檔log on(name='MySchool_log',filename='E:\project\MySchool_log.ldf',size=3,maxsize=20,filegrowTh=1)Go

2.使用T—SQL建立和刪除表

Create table 表名(列1 資料類型 列的特徵,列2 資料類型 列的特徵......）例：use MySchoolgoif exists (select * from sysobjects where name='Subject')--判斷是否存在Subject表drop table Subject gocreate table Subject--建立Subject表(SubjectId int identity(1,1) not null,--identity自動成長列subjectName nvarchar(50) not null,ClassHour int not null,GradeId int not null)go

 

3.使用T—SQL語句建立和刪除約束

主鍵約束（Primary Key Constraint）：要求主鍵列資料唯一，並且不允許為空白，如學號能唯一確定一名學生。

非空約束（Not Null）：要求列不能存在空值，如學生的姓名不可為空。

唯一約束（Unique Constraint）：要求該列的值必須唯一，允許為空白，但只能出現一個空值。

檢查約束（Check Constraint）：某列取值範圍限制，格式限制等，如有關年齡的約束。

預設約束（Default Constraint）：某列的預設值，如我們的男性學生較多，性別預設為男。

外鍵約束（Foreign Key Constraint）：用於兩表之間建立關係，需要指定引用主表的哪一列。

例：

（1）主鍵約束（PK_）if exists (select * from sysobjects where name ='pk_SubjectId')alter table Subjectdrop constraint pk_SubjectIdgoalter table Subjectadd constraint pk_SubjectId primary key (SubjectId)go（2）唯一約束(UQ_)if exists (select * from sysobjects where name ='uq_SubjectName')alter table Subjectdrop constraint uq_SubjectNamegoalter table Subjectadd constraint uq_SubjectName unique (SubjectName)go（3）檢查約束(CK_)if exists (select * from sysobjects where name ='ck_ClassHour')alter table Subjectdrop constraint ck_ClassHourgoalter table Subjectadd constraint ck_ClassHour check (ClassHour>0)go（4）預設約束(DF_)if exists (select * from sysobjects where name ='df_Exa')alter table Resultdrop constraint df_Exagoalter table Resultadd constraint df_Exa default (getdate()) for ExamDatego （5）外鍵約束(FK_表1_表2)if exists (select * from sysobjects where name ='fk_Subject_Grade_GradeId')alter table Subjectdrop constraint fk_Subject_Grade_GradeIdgoalter table Subjectadd constraint fk_Subject_Grade_GradeId foreign key (gradeId) references Grade(GradeId)Go

SQL編程

1.局部變數

Declare @variable_name DataType例：declare @name varchar(20)--聲明存放姓名變數name，最多可以儲存20個字元

2.給變數賦值

Set @variable_name = value或Select @variable_name=value

3.print語句和select語句

Print 局部變數或字串select 局部變數 as 自訂欄名例：Print '伺服器的名稱：'+@@servernameSelect @@servername as '伺服器的名稱'

 

 

4.資料類型轉換（Cast（）和Convert（）函數）

Cast(運算式 as 資料類型)Convert(資料類型[長度] ,長度[,樣式])例：Cast(@Result as varchar(10))Convert(varchar(20),@@error)

 

 

5.Begin-End語句

Begin語句或語句塊End

 

 

6.IF-ELSE語句

IF(條件)語句或語句塊1ELSE語句或語句塊2

 

 

7.WHILE語句

WHILE(條件)Begin語句或語句塊[break|continue]End

 

 

8.CASE多分支語句

CASEWHEN 條件1 THEN 結果1WHEN 條件2 THEN 結果2[ELSE 其他結果]END

 

 

進階查詢

1.子查詢Select ........ From 表1 where 列1>(子查詢)例：Select * from student where age>(select avg(age) from student)2.IN 和 NOT IN子查詢3.EXISTS 和 NOT EXISTS子查詢4.使用UNION聯集查詢

 

 

事務、視圖、索引、預存程序

1.事務（Transaction）

a) 原子性：不可分割。

b) 一致性：當事務完成時，資料必須處於一致狀態。

c) 隔離性：對資料進行修改的所有並發事務是彼此隔離的。

d) 持久性：不管系統是否發生了故障，交易處理的結果都是永久的。

文法：

Begin transaction    --顯示的標記一個事務的起始點

Commit transaction              --這個語句標誌一個事務成功結束。

Rollback transaction      --將資料狀態復原到事務起始點，並釋放由事務控制的資源。

例：

Use myschoolGoSet nocount no --不顯示受影響的行數Print '查看轉賬事務前的餘額'Select * from bankGo--開始事務Bengin transactionDeclare @errorNum=0Update bank set currentMoney=currentMoney-1000 where customerName='張三'Set @errorNum=@errorNum+@@errorUpdate bank set currentMoney=currentMoney+1000 where customerName='李四'Set @errorNum=@errorNum+@@errorPrint '查看轉賬事務過程中的餘額'Select * from bankGo--根據是否有錯誤，確定是提交還是撤銷If @@errorNum>0BeginPrint '交易失敗，復原事務'Rollback transactionEndElseBeginPrint '交易成功'Commit transactionEndGoPrint '查看轉賬事務後的餘額'Select * from bankgo

 

 

2.視圖（View）

建立視圖文法：Create view view_nameAs <select 語句>刪除視圖文法：Drop view view_name例：If exists(select 1from sysobjects where name='vw_CardCostomer')Drop view vw_CardCostomerGo--建立視圖create view vw_CardCostomerasselect CardID 卡號, c.CustomerName 客戶姓名, PassWord 密碼, MoneyType 貨幣, bs.TypeName 儲蓄種類,OpenCardDate 開戶日期, OpenCardMoney 開戶金額, case when IsActive ='是' then '掛失'when IsActive ='否' then '未掛失' end 是否掛失 from BankCard b inner join Customer c on c.CustomerID=b.CustomerID inner join BankSaveGetType bs on bs.TypeID=b.SaveTypeID Go

3.預存程序（procedure）

優點：

A)模組化程式設計

B)執行速度快，效率高

C)減少網路流量

D)具有良好的安全性

分類：系統預存程序（sp_）、使用者自訂的預存程序(up_)

常用系統預存程序

A）Exec sp_databases--列出當前系統中的資料庫B）Exec sp_renamedb 'mybank','bank'--改變資料庫名稱C）Exec sp_tables--當前資料庫中可查詢對象的列表D）Exec sp_colums student --查看錶student中列的資訊E）Exec sp_help student --查看student的所有資訊F）Exec sp_helpconstraint student --查看student表中的約束G）Exec sp_helptext 'view_student_result' --查看視圖的語句文本H）Exec sp_stored_procedures --返回當前資料庫中的預存程序列表

建立和刪除預存程序

 

註：只要預存程序後面有output關鍵字，表示此參數為輸入參數，否則視為輸入參數，輸入參數還可以設定為預設值。

文法：

Create proc[edure] 預存程序名[{@參數1 資料類型}[=預設值][output],..................................................{@參數n 資料類型}[=預設值][output]]As SQL語句例：--存取錢預存程序if exists(select 1 from sys.objects where name='up_ChangeMoney')drop proc up_ChangeMoneygocreate proc up_ChangeMoney@CardID varchar(32),@ChangeDate datetime,@ChangeMoney money,@ChangeTypeName varchar(10),@ChangeNote varchar(100)as begin trandeclare @ErrorSum int=0declare @ChangeTypeID intset @ChangeTypeID=(select TypeID from ChangeType where TypeName=@ChangeTypeName)if (@ChangeTypeName='存入')beginprint '交易進行中中.....'update BankCard set Banlance=Banlance+@ChangeMoney where CardID=@CardIDset @ErrorSum=@ErrorSum+@@errorinsert into ChangeMsg  values(@CardID,@ChangeDate,@ChangeMoney,@ChangeTypeID,@ChangeNote)set @ErrorSum=@ErrorSum+@@errorendelsebeginupdate BankCard set Banlance=Banlance-@ChangeMoney where CardID=@CardIDset @ErrorSum=@ErrorSum+@@errorif(@ErrorSum>0)beginRAISERROR ('交易失敗！餘額不足！',16,1)endInsert into ChangeMsg  values(@CardID,@ChangeDate,@ChangeMoney,@ChangeTypeID,@ChangeNote)set @ErrorSum=@ErrorSum+@@errorendif(@errorSum=0)begin print @ChangeTypeName+'金額成功'commit tranendelsebegin print '操作失敗'rollback tranendGo