《SQL 2005 select 詳盡用法》

      最近一直很忙， 好久沒有更新部落格了，現在就貼上自己在學習SQL 2005 過程中的學習筆記吧，自認為總結的還是不錯的，與君共勉，如有錯誤之處，還請高手們不吝賜教！！

   資料庫查詢是資料庫中一個最重要也是最基本的功能，它是從資料庫中檢索符合條件的資料記錄的選擇過程。SQL Server 2005的資料庫查詢使用T-SQL語言，其基本的查詢語句是SELECT語句。

本章主要介紹SQL Server 2005資料庫查詢的方法及使用。

涉及的知識點:

資料庫查詢

資料彙總

排序

分組

子查詢

集合操作

儲存查詢結果

函式查詢

   練習以上知識點實際上是練習使用select語句和其他語句的組合。

1.資料庫查詢

   

1.1 選擇列

選擇列指的是通過限定返回結果的列組成結果表。

選擇指定列

選擇指定列指的是選擇一個表中的部分列，各列名之間用逗號隔開。

首先做三張表以作下面的演示：（程式碼大家可以直接複製）

Student表：

Course表：

選課表：SC

--1.查詢全體學生的學號與姓名。

SELECT Sno，Sname

FROM Student；

--2. 查詢全體學生的姓名、學號、所在系。

SELECT Sname，Sno，Sdept

FROM Student；

查詢全部列:可以使用"*"簡單代替

--3  查詢全體學生的詳細記錄。

SELECT  Sno，Sname，Ssex，Sage，Sdept 

FROM Student；

或

SELECT  *

FROM Student；

定義列別名:在選擇列的同時給該列賦予新的名字,使用as關鍵字

--4.查詢所有學生的姓名、性別，並將相應的列名換為name、sex。

Select Sname as name,Ssex as sex 

from student

替換結果中資料(結果經過計算)

--5.  查全體學生的姓名及其出生年份。

SELECT Sname,2012-Sage

FROM Student

--使用CASE函式給每個學生的分數設定等級。

SELECT Sno,Cno,Grade,等級=

CASE

  WHEN Grade>=90 THEN '優秀'

  WHEN Grade>=80 THEN '良好'

  WHEN Grade>=70 THEN '中等'

  when Grade>=60 then '及格'

  ELSE '不及格'

END

FROM SC

1.2 選擇行

選擇行指的是通過限定返回結果的行組成結果表。選擇行可以和選擇列一起使用。

1. 消除結果中重複行

在對錶進行查詢時，有時查詢結果有許多重複行。SELECT語句使用DISTINCT關鍵字消除結果中的重複行。其語法格式如下：

DISTINCT column_name [,column_name…]

說明：DISTINCT關鍵字對後面的所有列消除重複行。一個SELECT語句中DISTINCT只能出現一次，而且必須放在所有列名之前。

--7 . 查詢選修了課程的學生學號。

 SELECT Sno FROM SC;

或(預設 ALL)

        SELECT ALL Sno  FROM SC;

/*

--//等價SELECT all Sno FROM SC

SELECT Sno FROM SC

--DISTINCT

SELECT DISTINCT Sno FROM SC 

*/

限制結果返回行數

如果SELECT語句返回結果有很多行，可以使用TOP關鍵字限定返回行數。在SQL Server 2000中，關鍵字TOP後只能常數數值。

在SQL Server 2005中進行了改進，TOP後還可以使用數值表示式。其語法格式如下：

TOP n [PERCENT]

說明：其中n表示返回結果的前n行，n PERCENT表示返回結果的前n%行。

top 後跟數字

--8.查詢課程表中前門課的資訊。

select top 3 Cno from SC

top後跟表示式

--9.查詢學生表中前人的資訊。

declare @i_temp int 

set @i_temp=3

select top (@i_temp) * 

from Student

限制結果返回行的條件

在限定返回結果的行操作時，最重要的就是通過條件限制，SELECT語句中WHERE子句是最常用、最重要的條件子句。

在WHERE子句指出查詢的條件，系統找出符合條件的結果。其語法格式如下：

WHERE < operator 1> [AND < operator 2>…][AND | OR < operator >…]

WHERE子句常用的查詢條件

(1) 表示式比較

   在WHERE子句的<比較條件>中使用比較運算子：

    =，>，<，>=，<=，!= 或 <>，!>，!<，

-- 10.查詢所有年齡在歲以下的學生姓名及其年齡。

select Sname ,Sage 

from Student

where Sage<20

或  

SELECT Sname，Sage 

FROM    Student 

WHERE NOT Sage >= 20

--11.查詢考試成績有不及格的學生學號。

select Sno 

from SC

where Grade<60

(2) 限制範圍

--12.查詢年齡在~23歲之間的學生的姓名、系別和年齡。

-- between a and b 相當於[a,b]

select  Sname , Sdept , Sage 

from Student

where Sage between 18 and 23

--13.查詢年齡不在~23歲之間的學生姓名、系別和年齡。

select  Sname , Sdept , Sage 

from Student

where Sage not between 18 and  23

(3) 確定集合

使用謂詞     IN <值表>,  NOT IN <值表>

          <值表>：用逗號分隔的一組取值

--14.查詢資訊系（IS）、數學系（MA）和電腦科學系（CS）學生的姓名和性別。

select Sname,Ssex

from Student

where Sdept in ('IS','MA')

--同上等價

SELECT Sname,Ssex

FROM   Student

WHERE  Sdept= 'IS' OR Sdept= 'MA' 

--15.查詢既不是資訊系、數學系，也不是電腦科學系的學生的姓名和性別。

SELECT Sname，Ssex

FROM Student

 WHERE Sdept NOT IN ( 'IS'，'MA'，'CS' )

 

 

(4) 字串匹配

 謂詞LIKE可以用來進行字串匹配

[NOT] LIKE  ‘<匹配串>’ [ESCAPE ‘<換碼字元>’]

<匹配串>：指定匹配模板

匹配模板：固定字串或含萬用字元的字串

      當匹配模板為固定字串時，

      可以用 = 運算子取代 LIKE 謂詞

      用 != 或 < >運算子取代 NOT LIKE 謂詞

      

萬用字元:

%: (百分號)  代表任意長度（長度可以為）的字串

  例：a%b表示以a開頭，以b結尾的任意長度的字串。如acb，addgb，ab 等都滿足該匹配串

_ :(下橫線)  代表任意單個字元

  例：a_b表示以a開頭，以b結尾的長度為的任意字串。如acb，afb等都滿足該匹配串

-- 16.查詢學號為的學生的詳細情況。

SELECT *    

FROM  Student  

WHERE  Sno ='95001'

等價於：

    SELECT  * 

      FROM  Student 

      WHERE Sno = '95001'

      

--17.查詢所有姓劉學生的姓名、學號和性別。

select *

from Student

where Sname like '劉%'

--18.查詢所有不姓劉學生的姓名、學號和性別。

select *

from Student

where Sname not like '劉%'

ESCAPE 短語：

當使用者要查詢的字串本身就含有 % 或 _ 時，要使用ESCAPE ‘<換碼字元>’短語對萬用字元進行轉義。

使用換碼字元將萬用字元轉義為普通字元。

--19.查詢姓名中第二個字是‘%’的學生。

select sname 

from student

where Sname like '_/%' ESCAPE '/'

--同以上等價

select sname 

from student

where sname like '_6%' ESCAPE '6'

==>:DSCAPE後的內容是轉義符號，並且該轉移符號只能是單個字元，如下面的寫法是錯誤的：

select sname 

from student

where sname like '_6AB%' ESCAPE '6AB'

--錯誤資訊：

--訊息506，級別16，狀態2，第1 行

--在LIKE 謂詞中指定的轉義符"6AB" 無效。

(5)涉及空值的查詢

使用謂詞 IS NULL 或 IS NOT NULL

 “IS NULL”不能用 “= NULL”代替

 

--20.某些學生選修課程後沒有參加考試，所以有選課記錄，但沒有考試成績。查詢缺少成績的學生的學號和相應的課程號。

select Sno , Cno, Grade 

from SC

where Grade is  null

--21. 查所有有成績的學生學號和課程號。

SELECT Sno，Cno

FROM  SC

WHERE  Grade IS NOT NULL

(6) 多重條件查詢

用邏輯運算子AND和OR來聯結多個查詢條件

AND的優先順序高於OR

可以用括號改變優先順序

可用來實現多種其他謂詞的等價形式

[NOT] IN

[NOT] BETWEEN …  AND  …

-- 22.查詢計算機系年齡在歲以下的學生姓名。

select Sname

from Student

where Sdept='IS' and Sage<20

--改寫.

--14.查詢資訊系（IS）、數學系（MA）和電腦科學系（CS）學生的姓名和性別。

SELECT Sname，Ssex

FROM Student

WHERE Sdept IN ( 'IS'，'MA'，'CS' )

可改寫為：

SELECT Sname，Ssex

FROM   Student

WHERE  Sdept= ' IS ' OR Sdept= ' MA' OR Sdept= ' CS '

改寫.

--12查詢年齡在~23歲（包括歲和歲）之間的學生的姓名、系別和年齡。ÿ    SELECT Sname，Sdept，Sage

FROM Student

WHERE Sage BETWEEN 20 AND 23；

可改寫為：

SELECT Sname，Sdept，Sage

FROM Student

WHERE Sage>=20 AND Sage<=23

2.資料彙總（使用集函式）

5類主要集函式

(1)計數

COUNT（[DISTINCT|ALL] *）

COUNT（[DISTINCT|ALL] <列名>）

(2)計算總和

SUM（[DISTINCT|ALL] <列名>）

(3)計算平均值

AVG（[DISTINCT|ALL] <列名>）

(4)求最大值

MAX（[DISTINCT|ALL] <列名>）

(5)求最小值

MIN（[DISTINCT|ALL] <列名>）

DISTINCT短語：在計算時要取消指定列中的重複值

ALL短語：不取消重複值

ALL為預設值

--25.查詢學生總人數。

SELECT COUNT(*)

FROM  Student

--26.查詢選修了課程的學生人數。

SELECT COUNT(DISTINCT Sno)

FROM SC；

注：用DISTINCT以避免重複計算學生人數

--27.計算號課程的學生平均成績。

SELECT AVG(Grade)

FROM SC

WHERE Cno= ' 2 '

--28.查詢選修號課程的學生最高分數。

SELECT MAX(Grade)

FROM SC

WHER Cno= ' 2 '

3.排序

使用ORDER BY子句可以按一個或多個屬性列排序升序：ASC；降序：DESC；預設值為升序當排序列含空值時

ASC：排序列為空值的元組最前顯示

DESC：排序列為空值的元組最後顯示 

 

--23查詢選修了號課程的學生的學號及其成績，查詢結果按分數升序排列。

select Sno , Grade

from SC

where Cno='3' 

ORDER BY Grade asc

--24查詢全體學生情況，查詢結果按所在系的系號升序排列，同一系中的學生按年齡降序排列。

SELECT  *

FROM  Student

ORDER BY Sdept,Sage DESC

4.對查詢結果分組 

GROUP BY子句用於分組 

分組：按指定的一列或多列值分組，值相等的為一組

--29.查詢CS表中選課學生的學號。

select  sno  

from SC 

--group by sno 

--方法同下亦可

select DISTINCT sno  

from SC 

分組的目的：細化集函式的作用物件,未對查詢結果分組時，集函式將作用於整個查詢結果,

對查詢結果分組後，集函式將分別作用於每個組，即每個組都有一個函式值。

--30.求各個課程號及相應的選課人數

select Cno, count(Sno) as 選課人數

from SC

group by Cno

注意：使用GROUP BY子句後，SELECT子句的列名列表中只能出現分組屬性和集函式

使用HAVING短語篩選最終輸出結果

只有滿足HAVING短語指定條件的組才輸出

HAVING短語與WHERE子句的區別：作用物件不同

WHERE子句作用於表或檢視，從中選擇滿足條件的元組。

HAVING短語作用於組，從中選擇滿足條件的組。

--31.查詢選修了門以上課程的學生學號。

select Sno

from SC

group by Sno

having  count(Sno)>2

--32.☆☆☆查詢有門以上課程是分以上的學生的學號及（分以上的）課程數

SELECT  Sno   ,count(grade) as '80分課程數'

FROM   SC

WHERE Grade>=80

group by Sno

having  COUNT(*)>=3

--注:對於數字在前的別名，需要別名上加上' '，不然數字不能夠識別，導致錯誤

【連線】

【連線】指的是通過限定返回結果，將多個表的資料組成結果表，即用一個SELECT語句可以完成從多個表中查詢資料。連線對結果沒有特別的限制，具有很大的靈活性。

T-SQL提供了兩種連線方式：傳統連線方式和SQL連線方式。

1. 傳統連線方式

傳統連線方式是指使用FROM…WHERE連線多表。其語法格式如下：

SELECT column_name [,column_name,…          

FROM table_name [,table_name,…]

WHERE condition 

--33.查詢每個學生選修課程的資訊，輸出學生姓名和課程名。

select