T-SQL查詢語(yǔ)句

嶧城ssl適用于網(wǎng)站、小程序/APP、API接口等需要進(jìn)行數(shù)據(jù)傳輸應(yīng)用場(chǎng)景，ssl證書未來(lái)市場(chǎng)廣闊！成為創(chuàng)新互聯(lián)的ssl證書銷售渠道，可以享受市場(chǎng)價(jià)格4-6折優(yōu)惠！如果有意向歡迎電話聯(lián)系或者加微信：13518219792（備注：SSL證書合作）期待與您的合作！

1. tansact-SQL編程語(yǔ)言

美國(guó)國(guó)家標(biāo)準(zhǔn)協(xié)會(huì)（ANSI）和國(guó)際標(biāo)準(zhǔn)組織（ISO）為SQL定義了標(biāo)準(zhǔn)，微軟通過(guò)用Transact-SQL和ANSI—SQL兼容，Transact-SQL還包含了幾種能夠增強(qiáng)性能的擴(kuò)展。

T-SQL的組成：

 數(shù)據(jù)定義語(yǔ)言（Date Definition Language）語(yǔ)句簡(jiǎn)稱DDL語(yǔ)句

DDL語(yǔ)句用來(lái)建立數(shù)據(jù)庫(kù)，數(shù)據(jù)庫(kù)對(duì)象（create,alter,drop）

createobject_name

alterobject_name

dropobject_name

舉例說(shuō)明DDL語(yǔ)句的使用:

use schoolDB

create tableteacher

(cust_idint ,company varchar(40),contact varchar(30),phone char(12))

go

alter tableteacheradd ageint default30

 數(shù)據(jù)控制語(yǔ)言（Date Control Language）語(yǔ)句

用于改變某個(gè)用戶或角色相關(guān)的權(quán)限

grant

deny

revoke

舉例說(shuō)明：

use schoolDB

grant select on products to public

go

 數(shù)據(jù)操作語(yǔ)言（Date Manipulation Language）語(yǔ)句

操作數(shù)據(jù)庫(kù)中的數(shù)據(jù)?？梢愿臄?shù)據(jù)庫(kù)中的數(shù)據(jù)或查詢數(shù)據(jù)庫(kù)中的信息

舉例說(shuō)明：

select*from dbo.TStudent  //查詢TStudent表中的數(shù)據(jù)

insert dbo.TStudent(StudentID,Sname,sex)values('0000001901','陳英宏','男')//為TStudent表中的studentID,Sname,sex列插入相應(yīng)的記錄

update dbo.TStudentset Sname='張春海',sex='男'where studentid='0000000569'//更新表中studentID為0000000569的姓名修改為'張春海'

delete dbo.TStudentwhere StudentID='0000000020'//刪除studentID為0000000020的記錄

2. Ttansact-SQL語(yǔ)法要素

1. 批處理

go

一個(gè)批處理命令通知SQLServer分析并運(yùn)行一個(gè)批處理的所有指令

實(shí)際上不是 Transact-SQL語(yǔ)句，只是描述一個(gè)批處理。局部變量作用范圍局限在一個(gè)批內(nèi)，必須獨(dú)占一行。

USE schoolDB

go

select * from dbo.TScore--從那張表查找

where mark+9<60

go

2. 表達(dá)式

1. 算數(shù)運(yùn)算符

 + - *  /  %

比較運(yùn)算符 =  <>  >    = <

!= :不等于，等同于<>

BETWEEN :指定值得包含范圍（包含邊界）。使用AND分隔開(kāi)始值和結(jié)束值

IS[NOT]NULL :根據(jù)使用的關(guān)鍵字，指定是否搜索空值或非空值。如果有任何一個(gè)操作數(shù)為NULL，則包含運(yùn)算符或算數(shù)運(yùn)算符的表達(dá)式的計(jì)算結(jié)果為NULL

LIKE:模糊查詢，與指定字符串進(jìn)行模糊匹配

IN:是否在數(shù)據(jù)范圍里面

字符串聯(lián)運(yùn)算符 +  空字符不等于空值

表達(dá)式是各種符號(hào)和對(duì)單個(gè)數(shù)據(jù)進(jìn)行操作

select mark+5from dbo.TScorewhere mark<60

2.邏輯運(yùn)算符 and or not

NOT:和其他操作符一起使用，取反的操作

AND:組合兩個(gè)條件，并在兩個(gè)條件都為TRUE時(shí)取值為TRUE

OR:組合兩個(gè)條件，并在兩個(gè)條件之一為TRUE時(shí)取值為TRUE

3. 通配符

'_' ：表示任何單個(gè)字符

sname LIKE '_cc' 查找以cc結(jié)尾的所有三個(gè)字母名字

% ：任意長(zhǎng)度的字符串

sname LIKE '%CC%'查找所有包含cc的名字

[]：括號(hào)中所指定范圍內(nèi)的一個(gè)字符例如sname LIKE '[c-p]ion'將查找Ion結(jié)尾且以介于    c與p之間的任何單個(gè)字符開(kāi)始的名字

通配符經(jīng)常與LIKE關(guān)鍵字一起配合使用完成模糊查詢，可以使用LIKE和通配符來(lái)完成對(duì)表的一些特殊約束。

3. 數(shù)據(jù)查詢

3.1. 使用select語(yǔ)句查詢數(shù)據(jù)

3.1.1. 指定列查詢

查詢所有行

select * from dbo.TStudent

使用where子句指定行

select Sname,sex,Emailfrom dbo.TStudentwhere Sname='田育朋'

3.1.2. 過(guò)濾數(shù)據(jù)

使用比較操作符 =  ><>=   <=  <>

select * from dbo.TScorewhere mark<=60

使用字符比較符 like

% 0個(gè)或多個(gè)字符串

_ 任何單個(gè)的字符

[]在指定區(qū)域或集合內(nèi)的任何單個(gè)字符

[^]不在指定區(qū)域或集合內(nèi)的任何單個(gè)字符

select*from dbo.TStudentwhere snamelike'高%'

select*from dbo.TStudentwhere snamelike'_[明,育]_'

select*from dbo.TStudentwhere snamelike'_[^明,育]_'

3.1.3. 使用邏輯操作符

OR AND NOT使用方法

select*from dbo.TStudentwhere Snamelike'高%'and sex='男'or StudentID='0000000112'

查找不姓高的學(xué)生

select*from dbo.TStudentwhere Snamenot like'高%'

3.1.4. 查找在一定范圍的值

select*from dbo.TScorewhere markbetween 70and 80

等價(jià)于

select*from dbo.TScorewhere mark>=70and mark<=80

不包括70 和80

盡量使用between而不使用and和比較操作符表示的表達(dá)式

如果想返回不在指定區(qū)域的行時(shí)，使用not between 。這樣會(huì)降低數(shù)據(jù)查詢的速度。

select*from dbo.TScorewhere marknot between 70and 80

指定時(shí)間范圍

select * from dbo.TStudentwhere Birthdaybetween'1983-01-01'and'1984-01-01'

3.1.5. 查詢空值

insert dbo.TStudent(StudentID,Sname,sex)values('0000001901','陳英宏','男')

查找班級(jí)不為空的學(xué)生

select * from dbo.TStudentwhere Classis not null

查找班級(jí)為空的學(xué)生

select * from dbo.TStudentwhere Class is null

使用is not null來(lái)查詢指定列中非空的行

3.2. 格式化結(jié)果集

3.2.1. 給數(shù)據(jù)排序

select StudentID,subJectID,markfrom dbo.TScoreorder by 2,3desc

select StudentID,subJectID,markfrom dbo.TScoreorder by subJectID,markdesc

asc升序

desc降序

3.2.2. 消除重復(fù)的行

distinct

select distinct Classfrom dbo.TStudent

3.2.3. 改變字段的名字

select StudentIDas'學(xué)號(hào)',Snameas'姓名',sexas'性別',cardIDas'×××號(hào)',Birthdayas'生日',Emailas'郵件', Classas'專業(yè)',enterTimeas'錄入時(shí)間'from dbo.Tstudent

等價(jià)于

select StudentID'學(xué)號(hào)',Sname'姓名',sex'性別',cardID  '×××號(hào)',

Birthday  '生日',Email'郵件', Class  '專業(yè)',enterTime  '錄入時(shí)間'from dbo.TStudent

3.2.4. 使用符號(hào)

符號(hào)可能是字母，數(shù)字或標(biāo)識(shí)，在結(jié)果集中，他們被用作特定的值，以增加結(jié)果集的可讀性。

select StudentID'學(xué)號(hào)',Sname'姓名',sex'性別','性別'from dbo.Tstudent

3.2.5. 計(jì)算列

年齡是計(jì)算列

select StudentIDas'學(xué)號(hào)',Snameas'姓名',sexas'性別',cardIDas'×××號(hào)',Birthdayas'生日',Emailas'郵件', Classas'專業(yè)',enterTimeas'錄入時(shí)間',Datediff(yy,Birthday,getdate())as'年齡'from dbo.TStudent

3.3. 使用T-SQL語(yǔ)句實(shí)現(xiàn)多表查詢

前面查詢都是基于單個(gè)數(shù)據(jù)庫(kù)表的查詢，如果一個(gè)查詢需要對(duì)多個(gè)表進(jìn)行操作，就成為連接查詢，連接查詢的結(jié)果集或結(jié)果稱為表之間的連接。

表連接的類型：

內(nèi)連接、外連接、交叉連接

創(chuàng)建多表查詢的練習(xí)環(huán)境

--創(chuàng)建學(xué)生表student

Createtable student

(

studentidint,

snamenvarchar(10),

sexnchar(1)

)

--插入學(xué)生

insert studentvalues(1,'華榮','女')

insert studentvalues(2,'王景正','男')

insert studentvalues(3,'郭淑麗','女')

insert studentvalues(4,'韓旭','女')

insert studentvalues(5,'孟小飛','男')

--創(chuàng)建成績(jī)表

createtable score

(

studentidint,

subjectnamenvarchar(20),

scoredecimal

)

--插入成績(jī)

insert scorevalues(1,'英語(yǔ)',89)

insert scorevalues(1,'數(shù)學(xué)',59)

insert scorevalues(2,'英語(yǔ)',79)

insert scorevalues(2,'數(shù)學(xué)',86)

insert scorevalues(3,'英語(yǔ)',57)

insert scorevalues(3,'數(shù)學(xué)',67)

insert scorevalues(6,'英語(yǔ)',88)

insert scorevalues(6,'數(shù)學(xué)',83)

1． 查詢所有學(xué)生的成績(jī)

select a.*,b.* from student ajoin score bon a.studentid=b.studentid

select sname,subjectname,scorefrom student ajoin score bon a.studentid=b.studentid

select a.studentid,sname,subjectname,scorefrom student ajoin score bon a.studentid=b.studentid

2． 從多個(gè)表中合并數(shù)據(jù)

使用內(nèi)連接

select a.*,b.*from student ajoin score bon a.studentid=b.studentid

等價(jià)于

select a.*,b.*from student ainner join score bon a.studentid=b.studentid

使用外連接

左外連接

select a.studentid,sname,subjectname,scorefrom dbo.student aleftjoin dbo.score bon a.studentid=b.studentid

右外連接

select a.*,b.*from  dbo.student arightjoin dbo.score bon a.studentid=b.studentid

練習(xí):

1．查找不及格同學(xué)姓名和學(xué)科分?jǐn)?shù)

select sname,subjectname,scorefrom dbo.student ajoin dbo.score bon a.studentid=b.studentidwhere score<60

自連接

查找到重名的學(xué)生

insert studentvalues(6,'韓旭','男')

select a.*,b.* from dbo.student ajoin dbo.student bon a.sname=b.snamewhere a.studentid<>b.studentid

3.4. 數(shù)據(jù)分組和匯總

3.4.1. 用TOP  n列出前n個(gè)記錄

結(jié)合Order by找出滿足條件的前幾條記錄

1． 年齡最大的前5名學(xué)生

select top 5 * from dbo.TStudent order by Birthday

2． 年齡最小的前5名學(xué)生

select top 5* from dbo.TStudent order by Birthday desc

查找網(wǎng)絡(luò)班年齡最小的前5名學(xué)生

select top 5* from dbo.Tstudent where Class='網(wǎng)絡(luò)班'orderby Birthday desc

3.4.2. 使用聚集函數(shù)

可以在Select 語(yǔ)句中單獨(dú)使用聚集函數(shù)，也可以與語(yǔ)句group by聯(lián)合使用

除了count（*）函數(shù)，如果沒(méi)有滿足where子句的記錄，則所有的聚集函數(shù)都將返回空值，Count（*）返回0

Count(*)Count（列）Sum  Min maxAvg

統(tǒng)計(jì)表中有多少行

select count(*) from dbo.TStudent

插入一條沒(méi)有班級(jí)的記錄

insert dbo.TStudent(studentID,Sname,sex)values('0000001901','王敬正','男')

select count(Class) from dbo.TStudent

求平均值

select avg(mark) from dbo.TScore

求最大值和最小值

select max(mark)from dbo.TScore

select min(mark)from dbo.TScore

求總和

select sum(mark)from dbo.TScore

3.4.3. 使用GROUP BY基礎(chǔ)

如果使用聚集函數(shù)，則將對(duì)表中的所有記錄的某個(gè)字段進(jìn)行匯總，然后生成單個(gè)的值。如果想生成多個(gè)匯總值，同時(shí)使用聚集函數(shù)和group by 語(yǔ)句，聯(lián)合使用having和group by子句能夠使結(jié)果集只包含滿足條件的記錄。

聯(lián)合使用group by子句與having子句

分組匯總

1. 統(tǒng)計(jì)每個(gè)班有多少個(gè)學(xué)生

select Class,count(*)from dbo.TStudentgroupby Class

2. 統(tǒng)計(jì)男生女生數(shù)量

select sex,count(*) from dbo.TStudent group by  sex

3. 統(tǒng)計(jì)每科平均分

selectsubJectName,avg(mark)fromdbo.TSubjectajoindbo.TScorebona.subJectID=b.subJectID

groupbysubJectName

4. 統(tǒng)計(jì)每個(gè)學(xué)生的平均分

selecta.StudentID,avg(mark)fromdbo.TStudentajoindbo.TScorebona.StudentID=b.StudentID

groupbya.StudentIDorderbyavg(mark)

5. 查找平均分大于95的學(xué)生

selecta.StudentID,avg(mark)fromdbo.TStudentajoindbo.TScorebona.StudentID=b.StudentID

groupbya.StudentIDhavingavg(mark)>95

Having相當(dāng)于條件

4. 數(shù)據(jù)修改

4.1. 插入數(shù)據(jù)

insert dbo.TStudentvalues('0000001902','李維偉','男','132302197506055634','1984-3-4','liweiwei@bdqn.com','網(wǎng)絡(luò)班',getdate())

4.2. 插入部分?jǐn)?shù)據(jù)

插入學(xué)生學(xué)號(hào) ;姓名性別其他列為空

insert dbo.TStudent(StudentID,sex,Sname)values('0000001903','男','張國(guó)強(qiáng)')

4.3. 將查詢記錄插創(chuàng)建的新表

使用SELECT  INTO創(chuàng)建表。

將學(xué)生表中開(kāi)發(fā)班的從學(xué)生查詢到一個(gè)新表

select StudentID,Sname,sex,Emailinto TDefrom dbo.TStudentwhere Class='開(kāi)發(fā)班'

4.4. 刪除數(shù)據(jù)

刪除學(xué)號(hào)是0000000020的學(xué)生

delete dbo.TStudentwhere StudentID='0000000020'

刪除1982年以前出生的學(xué)生

deletedbo.TStudentwhereBirthday<'1982-1-1'andclass='網(wǎng)絡(luò)班'

更新數(shù)據(jù)

更改學(xué)生學(xué)號(hào)是0000000569的學(xué)生姓名為張海明性別改成男

updatedbo.TStudentsetSname='張海明',sex='男'wherestudentid='0000000569'

5. 事物的概念

執(zhí)行事物的語(yǔ)法：

開(kāi)始事物： BEGIN TRANSACTION

提交事物： COMMIT TRANSACTION

回滾撤銷事物：ROLLBACK TRANSACTION

事物應(yīng)用案例

創(chuàng)建表

CREATETABLEdbo.bank (

customerNamechar(10)NOTNULL,

currentMoneymoneyNOTNULL

)

為bank表的currentMoneymoney創(chuàng)建check約束，currentMoney的賬戶余額不能少于1元

插入記錄

insertintobank(customerName,currentMoney)values('張三',1000)

insertintobank(customerName,currentMoney)values('李四',1)

select*frombank

updatebanksetcurrentMoney=currentMoney-1000wherecustomerName='張三'

updatebanksetcurrentMoney=currentMoney-1000wherecustomerName='李四'

select*frombank

執(zhí)行以上命令后李四賬戶增加了1000，但是張三賬戶余額仍然是1000，這樣的轉(zhuǎn)賬結(jié)果肯定是不可取，我們可以通過(guò)以下方法來(lái)解決。

首先轉(zhuǎn)賬過(guò)程就是一個(gè)事物，它需要兩條UPDATE語(yǔ)句來(lái)完成，這兩條語(yǔ)句是一個(gè)整體。如果其中任何一條出現(xiàn)錯(cuò)誤，則整個(gè)轉(zhuǎn)賬業(yè)務(wù)也應(yīng)該取消，兩個(gè)賬戶的余額應(yīng)該恢復(fù)到原來(lái)的狀態(tài)。

通過(guò)以下語(yǔ)句來(lái)解決上述轉(zhuǎn)賬的問(wèn)題

print'查看轉(zhuǎn)帳事務(wù)前的余額'select*frombank

go

begintransaction

declare@errorSumint

set@errorSum=0

updatebanksetcurrentMoney=currentMoney-1000wherecustomerName='張三'

set@errorSum=@errorSum+@@ERROR

updatebanksetcurrentMoney=currentMoney+1000wherecustomerName='李四'

set@errorSum=@errorSum+@@ERROR

print'查看轉(zhuǎn)帳事務(wù)前的余額'select*frombank

if@errorSum<>0

begin

print'交易失敗，回滾事務(wù)'

rollbacktransaction

end

else

begin

print'交易成功，提交事務(wù)，寫入硬盤，永久的保存'

committransaction

end

go

print'查看轉(zhuǎn)帳事務(wù)前的余額'select*frombank

在事物處理過(guò)程中使用@@ERROR全局變量來(lái)，檢查判斷當(dāng)前T-SQL語(yǔ)句執(zhí)行是否有錯(cuò)誤。如果有錯(cuò)誤則返回非零值，而@errorSum變量用來(lái)累計(jì)兩個(gè)UPDATE命令執(zhí)行之后@@ERROR的值，只要其中一條UPDATE語(yǔ)句有錯(cuò)誤@errorSum的值將不再為零。

如果將轉(zhuǎn)賬余額修改成500