資料庫查詢的例子及SQL語句

drop database if exists SS;create database SS;use SS;create table Student ( Sno char(9) primary key, Sname char(20) unique, Ssex char(2), Sage smallint, Sdept char(20));create table Course(  Cno char(4) primary key,  Cname char(40),  Cpno char(4) references Course(Cno),  Ccredit smallint  );create table SC(    Sno char(9) references Student(Sno),   Cno char(4) references Course(Cno),   Grade smallint,   primary key(Sno,Cno));

 

-------------------------------------------------------------------------------------------------

例一）：查詢全體學生的姓名及其出生年月
   問題分析：
          要查詢的資料是：Sname, 出生年份(表中沒有此列，不過可以用計算求得)
          從哪些表可以得到要查詢的資料：Sname 是Student的屬性，Sage也是Student的屬性，所以說本體要查詢
                      的結果在Student中就可以得到，其中出生年月用現在的年份減去年齡即可  
         查詢語句： select Sname,20012 - Sage 
                           from Student;
---------------------------------------------------------------------------------
例二）：查詢選修了課程的學生的學號
     問題分析：
          要查詢的資料:Sno
          分析：選修了課程，換句話說就是課程號Cno不為空白的那些元祖對應的Sno，
                   SC表中已經含有了Cno資訊同時包含了學號,同時要注意去掉重複的資料
                    因為多個Cno可以對應於一個Sno1
          從哪些表可以得到要查詢的資料：Sno 和Cno是SC的屬性列，所以在SC中即可獲得所需資訊
          查詢語句：select distinct Sno
                          from SC;
---------------------------------------------------------------------------------
例三）：查詢院系在CS,MA,IS學生的姓名和性別
       問題分析：
             要查詢的資料：Sname,Ssex
             從哪些表中可以得到要查詢的資料：Sname 和Ssex 是Student的屬性列，因此在Student表裡查詢即可
             查詢語句：
                  1）select Sname,Ssex
                        from Student
                         where Sdept = 'CS' or Sdept = 'MA' or Sdept = 'IS';
                用謂詞in也可以尋找屬性屬於指定集合的元祖,所以有查詢2
                 2）select Sname,Ssex
                       from Student
                       where Sdept in ('CS','MA','IS');
----------------------------------------------------------------------------------------------
例四）：查詢“各個”課程號（Cno）以及相應(也就是說課程號對應的)的選課人數
        問題分析：
               要查詢的資料：Cno ，選課人數
               查詢用到的表、：Cno 以及Sno是SC的屬性列
               分析：由於一個Cno可以對應於多個Sno，，所以可以把具有相同Cno的值用group by分為一組，
                     然後對每一組用 count計算,即計算相同的一組有多少行，用count（Cno）試試看
               查詢語句：
                    先分組
                     1）：select Cno，Sno from SC group by Cno,Sno
                         /*注意不能寫成select Cno，Sno from SC group by Cno，因為group by中有一個原則
                               就是select 後面的所有列中，沒有使用彙總函式的列必須出現在group by後面*/
                    2）：select Cno,count(Sno) as num/*count(Sno)是計算相同的一組中有多少個Sno或者說有多少個Sno為一組*/
                         from 
                         group by Cno

                需要特別注意的是對group by的目的是為了細化聚集合函式的作用對象。如果沒有對查詢
                結果進行分組，那麼聚集合函式講作用於整個查詢結果，例如select count(*) from Student,count(*)
                講會計算得出所有元組的個數，也就是學生的總人數；而分組後聚集合函式將作用於每一組，也就是每一組都有一個
                函數值，所以上面count(Sno)是計算Cno為某一個數值a時對應多少個Sno,而不是全部查詢結果的Sno
------------------------------------------------------------------------------------------------------------------------------------
例五）：查詢選修了2門以上的課程的學生學號
       問題分析：
              查詢的資料：Sno
              查詢用到的表：由於課程資訊在SC中，所以只需用SC表即可
              分析：由於一個Sno可以對應於多個Cno,所以可以把就有相同Sno的值用group by分為一組
                    就可以得到一個Sno對應的選擇課程情況，然後用count（*）對每一組計數,，此處用having 短語來進行條                                                        件篩選
            查詢語句：select Sno from SC group by Sno having count(*)>2;
---------------------------------------------------------------------------------------------------------------------------
例六）:查詢每個學生的(學號）Sno.（姓名）Sname,Cname(課程名),Grade（成績）
     問題分析：
             查詢資料：Sno,Sname，Cname,Grade
             涉及的表：應為Sno和Sname是Student的屬性列，Cname是Course的屬性列，Grade是SC的屬性列，所以用到Student，Course,SC三個表
             分析：三個表是根據Sno,Cno聯絡起來的
             查詢語句：select Student.Sno,Sname,Cname,Grade
                       from Student,SC,Course
                       where Student.Sno = SC.Sno and SC.Cno = Course.Cno;
--------------------------------------------------------------------------------------------------------------------------
例七）：查詢與“劉晨”在同一個系學習的學生
        問題分析：
            查詢資料：學生資訊，限定條件是與劉晨同一個系，
            未知資料：劉晨所在的系
            已知資料：學生的名字劉晨
            涉及的表：Student
            分析：可以根據已知的資料來查詢未知的資料
            查詢語句：
               1）確定劉晨所在的系
                    select Sdept from student where Sname = "劉晨"
               2）尋找在所在系與1查詢結果相同的學生資訊
                    select * from Student where Sdept = 'CS';
               綜合起來就是：select * from Student where Sdept in (select Sdept from student where Sname = "劉晨");
               由於每個學生只能有一個系，所以1中的查詢結果只有一個，所以也可以寫成下面的語句
               ：select * from Student where Sdept = (select Sdept from student where Sname = "劉晨");
               其實，也可以用自身串連來查詢：把Student看成兩個表來A和B,那麼該題的可以敘述成如下所示：
                   查詢A的學生資訊：要求A所在的系與B中名字叫劉晨的同學所在的系相同，那麼可以寫成如下
                   select * from Student as A where Sdept = (select Sdept from student as B where Sname = "劉晨");
                  進而改進為
                       select A.* from Student A,Student B where A.Sdept = B.Sdept and B.Sname = "劉晨";
-----------------------------------------------------------------------------------------------------------------------------
例八）：找出每個學生超過他選修的課程的平均成績的課程號
     
            
     問題分析：
              先換個問題求：求每個學生的成績超過全班平均成績的Sno，Cno
              查詢語句：select Sno,Cno from SC where Grade > (select AVG(Grade) from SC)
             所有對比著所換的那個問題，很容易寫出下面的語句
             select Sno,Cno from SC as A where Grade >=(select avg(Grade) from SC as B where A.Sno = B.Sno);  

----------------------------------------------------------------------------------------------------------------------------             

例九）：將電腦系所有的同學的成績設定為零
      問題分析：
          涉及的表：成績的表在SC中，院系Sdept在Student表中，因此涉及到兩個表SC和Student
 

     查詢語句1）：   UPDATE SC SET Grade = 100
               WHERE 'CS'= (select Sdept from Student where Student.Sno = SC.Sno);                                      

      查詢語句2）：   UPDATE SC set Grade = 0
        where SC.Sno  in (select Student.Sno from Student where Sdept = 'CS');