sql server 綜合總結

 　　面向抽象編程

　　介面-抽象類別-父類-具體類

　　1、 複習T-SQL

　　-> 資料庫的建立、表的建立和約束(DDL)

　　-> 建庫

　　if(db_id(資料庫名)) is not null

　　drop database 資料庫名;

　　create database 資料庫名;

　　-> 建表

　　if(object_id('表名', 'U')) is null

　　create table 表名

　　(

　　欄位 類型 [約束],

　　欄位 類型 [約束],

　　欄位 類型 [約束]

　　);

　　-> 建約束

　　alter table 表名 add constraint 約束名 約束條件

　　-> 主鍵約束(PK_表名_欄位名)

　　primary key(欄位)

　　-> 預設約束(DF_表名_欄位名)

　　default(值) for 欄位

　　-> 檢查約束(CK_表名_欄位名)

　　check(運算式)

　　-> 唯一約束(UQ_表名_欄位名)

　　unique(欄位)

　　-> 外鍵約束(FK_外鍵表_主鍵表_欄位名)

　　*

　　alter table 外鍵表 add

　　constraint FK_外鍵表_主鍵表_欄位名

　　foreign key(欄位名) references 主鍵表(欄位名);

　　-> 資料庫操作語言(DML)

　　-> 基礎語言

　　增

　　insert into 表名(欄位列表) values(值列表);-- 表是存在的

　　select 欄位列表 into 新表名 from 舊錶; -- 要求新表不存在

　　insert into 表名(欄位列表) select查詢;

　　刪(主外部索引鍵關聯的時候，刪除有時會出問題)

　　delete from 表名 where 條件;

　　truncate table 表名;-- 所有的資料歸零

　　drop database 資料庫;

　　drop table 表名;

　　改

　　update 表名 set 欄位=值， 欄位=值， … where條件;

　　查(***)

　　-> from子句

　　-> where子句

　　-> group by子句

　　-> having子句

　　-> select top distinct 以及運算運算式

　　-> order by子句

　　核心記住流程

　　-> 進階部分

　　case文法

　　-> if else if結構

　　case

　　when 運算式1 then 顯示1

　　when 運算式2 then 顯示2

　　…

　　else 顯示n

　　end

　　-> switch-case結構

　　case 欄位

　　when 值1 then 顯示1

　　when 值2 then 顯示2

　　…

　　else 顯示n

　　end

　　串連

　　-> 交叉聯結

　　ANSI-89的文法：select * from 表1, 表2;

　　ANSI-92的文法：select * from 表1 cross join 表2;

　　-> 內串連

　　ANSI-89的文法：select * from 表1, 表2 on 條件;

　　ANSI-92的文法：select * from 表1 inner join 表2 on 條件;

　　-> 外串連

　　ANSI-92的文法：

　　select * from 表1 left join 表2 on 條件;

　　select * from 表1 right join 表2 on 條件;

　　-> 自串連、多表串連

　　表1

　　inner join

　　表2

　　on 條件

　　inner join

　　表3

　　on 條件

　　子查詢

　　將一個查詢的結果作為另一個查詢的條件

　　分清出內部查詢(子查詢)和外部查詢

　　-> 獨立標量子查詢欄位 = (子查詢)

　　-> 獨立多值子查詢欄位 in (子查詢)

　　表運算式

　　-> 派生表

　　將查詢的"結果集"作為資料來源 --不能使用order by

　　select * from

　　(

　　select

　　row_number() over(order by stuId) as num,

　　*

　　from

　　Student

　　) as tbl

　　where

　　tbl.num between @count * (@page - 1) + 1 and @count * @page;

　　2、 通用資料表運算式(CTE)

　　就是將一個查詢得到的結果集用一個表的形式臨時存放，並用一個變數名指定

　　後面再查詢直接使用變數名即可

　　with 別名

　　as

　　(

　　結果集

　　)

　　查詢

　　3、 考慮派生表和CTE都是臨時的，而且比較臃腫，所以希望將常用的查詢結構定義到資料庫中

　　每次使用的時候，直接使用資料庫中的這個對象，這個對象中記錄了這個複雜的查詢規範

　　由資料庫執行查詢

　　那麼就有了視圖(虛擬表)

　　create view vw_視圖名

　　as

　　查詢

　　注意：視圖不具備儲存資料的能力，但是使用的時候就好像一張表一樣

　　4、 (*介紹)內聯資料表值函式

　　帶有參數的試圖

　　create function fn_名字

　　(@參數 類型， …) returns table

　　as

　　return 查詢

　　5、 SQL中的變數與流程式控制制

　　-> SQL是一個指令碼語言，可以認為基本的編程語句都支援

　　-> 定義變數

　　declare @變數名 類型名;

　　-> 賦值

　　set @變數名 = 值;

　　-> 補充

　　使用select進行賦值

　　select @變數=欄位 from 表名…(支援多值查詢)

　　使用子查詢進行賦值

　　set @變數 = (單值子查詢); (只支援單值查詢)

　　>sql 2008 新的文法

　　declare @name varchar(5)='大鑫';

　　select @name;

　　-> 判斷

　　if(bool運算式) begin end就等於程式中的大括弧()

　　begin

　　-- 語句

　　end

　　else if(運算式)

　　begin

　　-- 語句

　　end

　　else

　　begin

　　-- 語句

　　end

　　-> 迴圈

　　while(bool運算式)

　　begin

　　-- 語句

　　end

　　迴圈練習

　　--求1到100的和

　　declare @jkSum int;

　　declare @i int;

　　set @jkSum = 0;

　　set @i = 1;

　　while (@i <= 100)

　　begin

　　set @jkSum = @jkSum + @i;

　　--set @i = @i + 1;

　　****--sql裡面迴圈執行後要把@i+1 賦值然後執行下一次迴圈提供的新值

　　set @i += 1;

　　end

　　select @jkSum;

　　go

　　6、 為什麼要有事務，事務是什麼

　　事務，就是SQL中一個獨立執行過程

　　這結果是會影響到資料庫的資料結果的

　　這個事務(執行過程)與其他的執行過程無關

　　事務執行的時候是一個整體，要麼全部成功，也麼全部失敗

　　事務的原子性、持久性、隔離性、一致性(不能違反約束)

　　SQL中每一個SQL語句實際上都是一個事務(隱含交易)

　　事務就是一個最小的執行單位

　　常見的全域變數 @@version, @@error(最後一個錯誤的T_SQL的資訊)

　　@@identity (最後的一個插入表的自增id)

　　//查詢中文的錯誤資訊試圖

　　select * from sys.messages where where language_id=2052;

　　顯示聲明事務

　　begin transaction -- 簡寫begin tran

　　事務內部

　　-- 提交事務或復原事務

　　commit transaction-- 表示事務內部的所有事情執行成功

　　rollback transaction-- 表示撤銷事務內部所有的事情

　　-> 使用try-catch塊處理事務中的異常錯誤

　　begin try

　　end try

　　begin catch

　　end catch

　　7、 預存程序就是將一些列的執行步驟記錄到資料庫中，有點類似於方法

　　-> 無參數，無傳回值的預存程序

　　-> 有參數，無傳回值的預存程序

　　-> 帶有預設參數的預存程序

　　-> 帶有預設參數與參數傳回值的預存程序

　　聲明一個帶預設參數@stuNameTemp nvarchar(20)='all'

　　---> []可以有，可以無

　　---> 多條語句必須要加begin end 如果是一條語句可以省略begin end

　　CREATE PROC[EDURE] USP_預存程序名

　　@參數1 資料類型 [= 預設值] [OUTPUT],

　　@參數n 資料類型 [= 預設值] [OUTPUT]

　　AS

　　BEGIN

　　SQL語句

　　END

　　exec 預存程序 參數;

　　exec 預存程序 參數名=參數;

　　exec 預存程序 參數，參數n output;

　　8、 使用ADO.net執行SQL的方法

　　-> 沒有傳回值的預存程序

　　-> 帶有參數傳回值的預存程序

　　-> 連接字串

　　-> SQL語句(預存程序的名字)

　　-> 建立參數

　　如果是執行參數傳回值的預存程序

　　定義要返回的參數，不要賦值，設定Direction

　　string sql = "usp_TransMoney";

　　SqlParameter[] ps =

　　{

　　new SqlParameter("@from",from)，

　　new SqlParameter("@to", to)，

　　new SqlParameter("@money",money)，

　　new SqlParameter("@isAccess", SqlDbType.Int)

　　};

　　ps[3].Direction = ParameterDirection.Output;

　　-> 串連通道SqlConnection

　　-> 建立執行對象SqlCommand(設定CommandType)

　　-> 加入參數

　　-> 開啟串連

　　-> 執行方法

　　9.觸發器

　　->特殊的預存程序。 inserted表 和 deleted表 2個暫存資料表

　　insert into back(cid,balance) output inserted.*

　　values('0004',10000);

　　for 或者 after 觸發器 是執行完才能觸發

　　instead of 代替

　　--添加一個觸發器，刪除所有資料後，在把資料插回來

　　create trigger tr_DelUseInfo on 表名(bank)

　　for delete

　　as

　　insert into bank select * from deleted;

　　go