SQL乾貨篇

愛撒謊的男孩發表於2017-04-09
SQL

單表查詢

只在一個表中查詢資料

SELECT * FROM student where Sno=`13143`;   //根據學號查詢資料

多表查詢

同時查詢多個表

SELECT student.Sno,student.Sname,Grade
FROM student,SC where Grade>=90;

說明：這是在學生表student和成績表SC中查詢成績大於90的學生姓名和學號,因為這裡Sno,Sname在兩個表中都存在，因此要指定查詢哪一個表中的資料，而Grade只在SC表中出現，因此不用指明哪張表

模糊查詢

說明：模糊查詢用like和not like進行查詢

  • SELECT * FROM student where Sname like `劉%`;查詢所有姓劉的學生

  • SELECT * FROM student where Sname like `%加%`查詢名字中含有加字的學生資訊，不固定加字的位置，在任意位置都能查到，這裡一般搜尋引擎都用是用這種模糊查詢的方法來匹配搜尋項

  • SELECT * FROM student where Sname like `歐陽_`;查詢以姓歐陽並且名字為三個字的學生資訊

  • SELECT * FROM student WHERE Sname like `_陽`;查詢所有姓名為兩個字並且第二個字為陽的學生資訊

  • SELECT * FROM student where Sname like `_陽%`;查詢所有姓名中第二個字為陽的學生資訊

  • SELECT * FROM Course where Cname like `\_IS` ESCAPE ``;查詢課程名字為_is的課程資訊，這裡如果要查詢的字串本省就含有萬用字元”%“或者”_“，這時就要使用ESCAPE<轉碼字元>來對字元進行轉義了，這裡的轉碼字元可以是任意的，但是我們通常都是用,上面的查詢語句中的就是轉碼字元

空值查詢

判斷資料是否為空用is not null和is null

SELECT * FROM student where Sname is null;查詢姓名為空的學生資訊

多重條件的查詢

多重條件的查詢用AND和OR,其區別不用多說了

SELECT Sname FROM student where Sdept=`IS` and Sage>20;查詢院系是IS並且年齡超過20歲的學生姓名

ORDER BY子句(排序)

使用者可以使用ORDER BY子句對資料進行升序(ASC)或者降序(DESC)排列

  • SELECT * FROM student where Sage>20 ORDER BY Sno DESC;查詢年齡超過20歲的學生資訊，並且按照降序排列輸出

  • SELECT * FROM student ORDER BY Sdept,Sage DESC;查詢全體學生情況，查詢結果按照所在系的系號升序排列，同一系的按照年齡降序排列

聚集函式

  • COUNT(*) 統計元組個數

  • COUNT(DISTINCT|ALL <列名>) 計算一列中值的個數，其中DISTINCT表示去除重複的元素，ALL則保留所有的元素

  • SUM(DISTINCT|ALL <列名>) 計算一列值的總和

  • AVG(DISTINCT|ALL<列名>) 計算一列中的平均值

  • MAX(DISTINCT|ALL<列名> ) 求一列中的最大值

  • MIN(DISTINCT|ALL<列名> ) 求一列中的最小值

例項：

  • select count(*) from student; 查詢學生總數

  • select count(DISTINCT Sdept); 查詢總共有多少系

  • select AVG(Grade) from SC; 查詢學生的平均分

  • select SUM(Grade) from SC; 查詢學生的總分

  • select MAX(Grade) from SC where Cno=`1`; 查詢課程1的最高分

  • select MIN(Grade) from SC where Cno=`1`; 查詢課程1的最低分

注意：where子句中不能用聚集函式，只有在select子句和Group by子句中才能使用聚集函式

GROUP BY子句

  • GROUP BY子句將查詢結果按某一列或者多列的值分組，值相等的為一組。

  • 對查詢結果分組的目的是為了細化聚集函式的物件。如果未對查詢結果進行分組，那麼聚集函式將會作用於整個查詢結果，分組後聚集函式將會作用於每一組，即每一組都有一個函式值

例項：

  • select Cno,Count(Sno) from SC Group by Cno; 求各個課程號以及相應的選課人數

  • select Cno as `課程號`,count(Sno) as `選課人數` from sc group by Cno;求各個課程號以及相應的選課人數

  • select Cno,count(Sno),AVG(Grade) from sc group by Cno Having AVG(Grade)>80; 查詢課程平均分大於80分的課程號和所選學生人數,這裡是先分組後然後對這些組進行篩選就用Having子句進行條件篩選，不能使用where子句進行篩選,當然這裡的sleect子句中的AVG(Grade)可以去掉,可以寫成select Cno,count(Sno) from sc group by Cno Having AVG(Grade)>80;

  • select Sno from sc Group by Sno having count(*)>2; 查詢選修了兩門以上課程的學生學號

  • select Sno,AVG(Grade) from sc Group by Sno; 查詢每一個學生選修課程的平均成績,這裡先按照學號進行分組，然後對每一個分組進行求平均成績

注意：這裡的如果使用了聚集函式，那麼select子句中出現的選項一定要在聚集函式或者Group by子句中出現，否則就會出現錯誤，如：select Sno,count(Cno) from sc;這條語句就是錯誤的，因為Sno沒有出現在聚集函式或者Group by子句中，如果改成select Sno,count(Cno) from SC Group by Sno;就正確了,因為Sno出現在了Group by子句中了

連線查詢

如果一個查詢涉及兩個以上的表則稱之為連線查詢，連線查詢包括等值連線查詢，自然連線查詢，自身連線查詢，非等值連線查詢，外連線查詢，複合條件查詢

等值和非等值連線查詢

當連線運算子為=時為等值連線查詢，否則為非等值連線查詢

例項：

  • select student.*,SC.* from student,SC where student.Sno=SC.Sno;查詢每個學生及其選修課程的情況

自然連線查詢

在等值連線的基礎上去掉相等的屬性組就是自然連線查詢

例項：
select student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade from student,SC where student.Sno=SC.Sno;

巢狀查詢

在SQL語言中一個SELECT-FROM-WHERE語句稱為一個查詢塊，將一個查詢塊巢狀在另外一個查詢塊的WHERE子句或HAVING短語的條件查詢稱之為巢狀查詢
例項：

SELECT SNAME FROM STUDENT WHERE SNO IN    /*外層查詢*/
(SELECT SNO FROM SC WHERE CNO=`2`);    /*內層查詢或者子查詢*/

注意:

  • 這裡的查詢條件Sno只能有一個，並且外層查詢的where子句中出現的Sno屬性要和內層查詢select語句中的Sno屬性要對應。

  • 子查詢中不能使用ORDER BY子句，ORDER BY子句只能對最終的查詢結果排序

帶有IN謂詞的巢狀查詢

例項：

SELECT Sno,Sname,Sdept from student where Sdept IN
(SELECT Sdept From student Where Sname=`劉晨`);

查詢與劉晨在同一個系的學生資訊,當然本例中也可以用自身連線查詢來完成，如下：

select first.Sno,first.Sname,first.Sdept 
from student first,student second
where first.Sdept=second.Sdept and second.Sname=`劉晨`;

帶有比較運算子的子查詢

select Sno,Cno from sc x where Grade >
(select AVG(Grade) from sc y where x.Sno=y.Sno);

查詢了所有學生成績超過選修課程平均成績的課程號

帶有ANY或者ALL的謂詞子查詢

ANY表示查詢條件只要滿足其中一個即可，而ALL表示查詢條件要滿足所有的才行
例項：

  • SELECT SNAME,SAGE FROM STUDENT WHERE SAGE<ANY(SELECT SAGE FROM STUDENT WHERE SDEPT=`CS`) AND SDEPT!=`CS`;查詢非計算機系的比計算機系任意一個學生年齡小的學生姓名和年齡,這裡只要滿足比一個學生的年齡小即可

  • SELECT SNAME,SAGE FROM STUDENT WHERE SAGE<ALL(SELECT SAGE FROM STUDENT WHERE SDEPT=`CS`) AND SDEPT!=`CS`;查詢非計算機系的比計算機系的所有學生年齡小的學生資訊，這裡要滿足比所有的學生資訊都要小，就是比計算機系年齡最小的都要小

帶有EXISTS謂詞的子查詢

EXISTS表示存在的意思，帶有EXISTS的子查詢步返回任何的資料，只產生邏輯真或者假

  • SELECT Sname From student where EXISTS (SELECT Sname from SC where Sno=student.Sno and Cno=`2`);查詢選擇課程2的學生姓名，這裡只判斷是否存在這樣的學生，如果子查詢中沒有找到課程2這項，那麼查到的就是空,子查詢只判斷是否為true or false,當然還有NOT EXISTS

集合查詢

集合操作包括並操作UNION、交操作INTERSECT、差操作EXCEPT

例項：

  • select * from student where Sdept= `CS` UNION select * from student where Sage>19;查詢計算機系的學生以及年齡不大於19歲的學生資訊，這裡UNION會自動去掉重複的元組，如果想要保留全部的資料需要用UNION ALL

  • select Sno from SC where Cno=`1` UNION select Sno from SC where Cno=`2`;查詢選修課程1或者選修課程2的學生學號，這裡並集就是去掉重複的元組，使用UNION ALL 可以保留

  • select Sno from SC where Cno=`1` Intersect select Sno from SC where Cno=`2`;查詢同時選修課程1和課程2的學生學號

基於派生表的查詢

select Sno,Cno from SC,(select Sno,AVG(Grade) from SC Group by Sno) 
AS AVG_SC(avg_Sno,avg_grade)
where SC.Sno=AVG_SC.avg_Sno and SC.Grade>=AVG_SC.avg_grade;

這裡的From子句中將會派生出一個AVG_SC表,該表由avg_Sno、avg_grade組成，主查詢將SC表和AVG_SC表進行連線，選出修課成績大於其平均成績的課程號

注意：如果子查詢中沒有聚集函式，那麼派生表不用指定屬性列，子查詢後面的列名為其屬性，如下：

select Sname from student,(select Sno From SC where Cno=`1`) AS SCI where student.Sno=SCI.Sno;這裡的SCI預設的列屬性名是Sno，AS關鍵詞可以省略，但是必須要為派生表指定一個別名。

本人部落格

相關文章