sqlserver數據庫的sql語句使用

             T-SQL查詢語句

1. tansact-SQL編程語言

美國國家標準協會（ANSI）和國際標準組織（ISO）為 SQL定義了標準，微軟通過用Transact-SQL和ANSI—SQL兼容，Transact-SQL還包含了幾種能夠增強性能的擴展。

T-SQL的組成：

 數據定義語言（Date Definition Language）語句簡稱DDL語句

DDL語句用來建立數據庫，數據庫對象（create,alter,drop）

create object_name

alter object_name

drop object_name

舉例說明DDL語句的使用:

use schoolDB

create table teacher

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

go

alter table teacher add ageint default 30

 數據控制語言（Date Control Language）語句

用于改變某個用戶或角色相關的權限

grant

deny

revoke

舉例說明：

use schoolDB

grant select on products to public

go

 數據操作語言（Date Manipulation Language）語句

操作數據庫中的數據。可以更改數據庫中的數據或查詢數據庫中的信息

舉例說明：

select*from dbo.TStudent  //查詢TStudent表中的數據

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

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

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

2. Ttansact-SQL語法要素

1. 批處理

go

一個批處理命令通知SQLServer分析并運行一個批處理的所有指令

實際上不是 Transact-SQL語句，只是描述一個批處理。局部變量作用范圍局限在一個批內，必須獨占一行。

USE schoolDB

go

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

where mark+9<60

go

2. 表達式

1. 算數運算符

 + - *  /  %

比較運算符 =   <>  >    =  <

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

BETWEEN :指定值得包含范圍（包含邊界）。使用AND分隔開始值和結束值

IS[NOT]NULL :根據使用的關鍵字，指定是否搜索空值或非空值。如果有任何一個操作數為NULL，則包含運算符或算數運算符的表達式的計算結果為NULL

LIKE:模糊查詢，與指定字符串進行模糊匹配

IN:是否在數據范圍里面

字符串聯運算符 +  空字符不等于空值

表達式是各種符號和對單個數據進行操作

select mark+5 from dbo.TScore where mark<60

2. 邏輯運算符 and or not

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

AND:組合兩個條件，并在兩個條件都為TRUE時取值為TRUE

OR:組合兩個條件，并在兩個條件之一為TRUE時取值為TRUE

3. 通配符

'_' ：表示任何單個字符

sname LIKE '_cc' 查找以cc結尾的所有三個字母名字

% ：任意長度的字符串

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

[]：括號中所指定范圍內的一個字符例如sname LIKE '[c-p]ion'將查找Ion結尾且以介于    c 與p之間的任何單個字符開始的名字

通配符經常與LIKE關鍵字一起配合使用完成模糊查詢，可以使用LIKE和通配符來完成對表的一些特殊約束。

3. 數據查詢

3.1. 使用select語句查詢數據

3.1.1. 指定列查詢

查詢所有行

select * from dbo.TStudent

使用where子句指定行

select Sname,sex,Email from dbo.TStudent where Sname='田育朋'

3.1.2. 過濾數據

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

select * from dbo.TScore where mark<=60

使用字符比較符 like

% 0個或多個字符串

_ 任何單個的字符

[]在指定區域或集合內的任何單個字符

[^]不在指定區域或集合內的任何單個字符

select*from dbo.TStudent where sname like'高%'

select*from dbo.TStudent where sname like'_[明,育]_'

select*from dbo.TStudent where sname like'_[^明,育]_'

3.1.3. 使用邏輯操作符

OR AND NOT使用方法

select*from dbo.TStudent where Sname like'高%'and sex='男'or StudentID='0000000112'

查找不姓高的學生

select*from dbo.TStudent where Sname not like'高%'

3.1.4. 查找在一定范圍的值

select*from dbo.TScore where mark between 70 and 80

等價于

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

不包括70 和80

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

如果想返回不在指定區域的行時，使用not between 。這樣會降低數據查詢的速度。

select*from dbo.TScore where mark not between 70 and 80

指定時間范圍

select * from dbo.TStudent where Birthday between'1983-01-01'and'1984-01-01'

3.1.5. 查詢空值

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

查找班級不為空的學生

select * from dbo.TStudent where Class is not null

查找班級為空的學生

select * from dbo.TStudent where Class is null

使用is not null來查詢指定列中非空的行

3.2. 格式化結果集

3.2.1. 給數據排序

select StudentID,subJectID,mark from dbo.TScore order by 2,3 desc

select StudentID,subJectID,mark from dbo.TScore order by subJectID,mark desc

asc升序

desc降序

3.2.2. 消除重復的行

distinct

select distinct Class from dbo.TStudent

3.2.3. 改變字段的名字

select StudentID as'學號',Sname as'姓名',sex as'性別',cardID as'×××號',Birthday as'生日',Email as'郵件', Class as'專業',enterTime as'錄入時間'from dbo.Tstudent

等價于

select StudentID '學號',Sname '姓名',sex '性別',cardID  '×××號',

Birthday  '生日',Email '郵件', Class  '專業',enterTime  '錄入時間'from dbo.TStudent

3.2.4. 使用符號

符號可能是字母，數字或標識，在結果集中，他們被用作特定的值，以增加結果集的可讀性。

select StudentID'學號',Sname'姓名',sex'性別','性別'from dbo.Tstudent

3.2.5. 計算列

年齡是計算列

select StudentID as'學號',Sname as'姓名',sex as'性別',cardID as'×××號',Birthday as'生日',Email as'郵件', Class as'專業',enterTime as'錄入時間',Datediff(yy,Birthday,getdate())as'年齡'from dbo.TStudent

3.3. 使用T-SQL語句實現多表查詢

前面查詢都是基于單個數據庫表的查詢，如果一個查詢需要對多個表進行操作，就成為連接查詢，連接查詢的結果集或結果稱為表之間的連接。

表連接的類型：

內連接、外連接、交叉連接

創建多表查詢的練習環境

--創建學生表 student

Createtable student

(

studentid int,

sname nvarchar(10),

sex nchar(1)

)

--插入學生

insert student values(1,'華榮','女')

insert student values(2,'王景正','男')

insert student values(3,'郭淑麗','女')

insert student values(4,'韓旭','女')

insert student values(5,'孟小飛','男')

--創建成績表

createtable score

(

studentid int,

subjectname nvarchar(20),

score decimal

)

--插入成績

insert score values(1,'英語',89)

insert score values(1,'數學',59)

insert score values(2,'英語',79)

insert score values(2,'數學',86)

insert score values(3,'英語',57)

insert score values(3,'數學',67)

insert score values(6,'英語',88)

insert score values(6,'數學',83)

1． 查詢所有學生的成績

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

select sname,subjectname,score from student a join score b on a.studentid=b.studentid

select a.studentid,sname,subjectname,score from student a join score b on a.studentid=b.studentid

2． 從多個表中合并數據

使用內連接

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

等價于

select a.*,b.*from student a inner join score b on a.studentid=b.studentid

使用外連接

左外連接

select a.studentid,sname,subjectname,score from dbo.student a leftjoin dbo.score b on a.studentid=b.studentid

右外連接

select a.*,b.*from  dbo.student a rightjoin dbo.score b on a.studentid=b.studentid

練習:

1．查找不及格同學姓名和學科分數

select sname,subjectname,score from dbo.student a join dbo.score b on a.studentid=b.studentid where score<60

自連接

查找到重名的學生

insert student values(6,'韓旭','男')

select a.*,b.* from dbo.student a join dbo.student b on a.sname=b.sname where a.studentid<>b.studentid

3.4. 數據分組和匯總

3.4.1. 用TOP  n列出前n個記錄

結合Order by找出滿足條件的前幾條記錄

1． 年齡最大的前5名學生

select top 5 * from dbo.TStudent order by Birthday

2． 年齡最小的前5名學生

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

查找網絡班年齡最小的前5名學生

select top 5 * from dbo.Tstudent where Class='網絡班'orderby Birthday desc

3.4.2. 使用聚集函數

可以在Select 語句中單獨使用聚集函數，也可以與語句group by聯合使用

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

Count(*)Count（列）Sum  Min max Avg

統計表中有多少行

select count(*) from dbo.TStudent

插入一條沒有班級的記錄

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基礎

如果使用聚集函數，則將對表中的所有記錄的某個字段進行匯總，然后生成單個的值。如果想生成多個匯總值，同時使用聚集函數和group by 語句，聯合使用having和group by子句能夠使結果集只包含滿足條件的記錄。

聯合使用group by子句與having子句

分組匯總

1. 統計每個班有多少個學生

select Class,count(*)from dbo.TStudent groupby Class

2. 統計男生女生數量

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

3. 統計每科平均分

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

groupbysubJectName

4. 統計每個學生的平均分

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

groupbya.StudentIDorderbyavg(mark)

5. 查找平均分大于95的學生

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

groupbya.StudentIDhavingavg(mark)>95

Having相當于條件

4. 數據修改

4.1. 插入數據

insert dbo.TStudent values('0000001902','李維偉','男','132302197506055634','1984-3-4','liweiwei@bdqn.com','網絡班',getdate())

4.2. 插入部分數據

插入學生學號 ;姓名性別其他列為空

insert dbo.TStudent (StudentID,sex,Sname)values('0000001903','男','張國強')

4.3. 將查詢記錄插創建的新表

使用SELECT  INTO創建表。

將學生表中開發班的從學生查詢到一個新表

select StudentID,Sname,sex,Email into TDe from dbo.TStudent where Class='開發班'

4.4. 刪除數據

刪除學號是0000000020的學生

delete dbo.TStudent where StudentID='0000000020'

刪除1982年以前出生的學生

deletedbo.TStudentwhereBirthday<'1982-1-1'andclass='網絡班'

更新數據

更改學生學號是0000000569的學生姓名為張海明性別改成男

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

5. 事物的概念

執行事物的語法：

開始事物： BEGIN TRANSACTION

提交事物： COMMIT TRANSACTION

回滾撤銷事物：ROLLBACK TRANSACTION

事物應用案例

創建表

CREATETABLEdbo.bank (

customerNamechar(10)NOTNULL,

currentMoneymoneyNOTNULL

)

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

插入記錄

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

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

select*frombank

updatebanksetcurrentMoney=currentMoney-1000 wherecustomerName='張三'

updatebanksetcurrentMoney=currentMoney-1000 wherecustomerName='李四'

select*frombank

執行以上命令后李四賬戶增加了1000，但是張三賬戶余額仍然是1000，這樣的轉賬結果肯定是不可取，我們可以通過以下方法來解決。

首先轉賬過程就是一個事物，它需要兩條UPDATE語句來完成，這兩條語句是一個整體。如果其中任何一條出現錯誤，則整個轉賬業務也應該取消，兩個賬戶的余額應該恢復到原來的狀態。

通過以下語句來解決上述轉賬的問題

print'查看轉帳事務前的余額'select*frombank

go

begintransaction

declare@errorSumint

set@errorSum=0

updatebanksetcurrentMoney=currentMoney-1000 wherecustomerName='張三'

set@errorSum=@errorSum+@@ERROR

updatebanksetcurrentMoney=currentMoney+1000 wherecustomerName='李四'

set@errorSum=@errorSum+@@ERROR

print'查看轉帳事務前的余額'select*frombank

if@errorSum<>0

begin

print'交易失敗，回滾事務'

rollbacktransaction

end

else

begin

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

committransaction

end

go

print'查看轉帳事務前的余額'select*frombank

在事物處理過程中使用@@ERROR全局變量來，檢查判斷當前T-SQL語句執行是否有錯誤。如果有錯誤則返回非零值，而@errorSum變量用來累計兩個UPDATE命令執行之后@@ERROR的值，只要其中一條UPDATE語句有錯誤@errorSum的值將不再為零。

如果將轉賬余額修改成500