SQL語句學習手冊執行個體版

SQL語句學習手冊執行個體版

表操作
例 1   對於表的教學管理資料庫中的表 STUDENTS ，可以定義如下：

程式碼

    Create   TABLE   STUDENTS (SNO   NUMERIC (6, 0) NOT NULL
                                                  SNAME    CHAR (8) NOT NULL
                                                  AGE    NUMERIC(3,0)
                                                  SEX    CHAR(2)
                                                  BPLACE   CHAR(20)
                                                  PRIMARY KEY(SNO))

例 2   對於表的教學管理資料庫中的表 ENROLLS ，可以定義如下：

程式碼

         Create   TABLE   ENROLLS(SNO       NUMERIC(6,0)   NOT NULL
                                                      CNO      CHAR(4)   NOT NULL
                                                      GRADE    INT
                                                      PRIMARY KEY(SNO,CNO)
                                                      FOREIGN KEY(SNO) REFERENCES STUDENTS(SNO)
                                                      FOREIGN KEY(CNO) REFERENCES COURSES(CNO)
                                          CHECK ((GRADE IS NULL) or (GRADE BETWEEN 0 AND 100)))

例 3   根據表的 STUDENTS 表，建立一個只包含學號、姓名、年齡的女學生表。

程式碼

         Create TABLE GIRL AS Select SNO, SNAME, AGE
                                             FROM STUDENTS Where SEX=' 女 ';

例 4   刪除教師表 TEACHER 。

程式碼

         Drop   TABLE   TEACHER

例 5   在教師表中增加住址列。

程式碼

        Alter TABLE TEACHERS ADD (ADDR CHAR(50))

例 6   把 STUDENTS 表中的 BPLACE 列刪除，並且把引用 BPLACE 列的所有視圖和約束也一起刪除。

程式碼

          Alter TABLE STUDENTS Drop BPLACE CASCADE

例 7   補充定義 ENROLLS 表的主關鍵字。

程式碼

        Alter TABLE ENROLLS ADD PRIMARY KEY (SNO,CNO) ;

視圖操作（虛表）
例 9   建立一個只包括教師號、姓名和年齡的視圖 FACULTY 。 ( 在視圖定義中不能包含 orDER BY 子句 )

程式碼

         Create VIEW FACULTY AS Select TNO, TNAME, AGE FROM TEACHERS

例 10   從學生表、課程表和選課表中產生一個視圖 GRADE_TABLE ， 它包括學生姓名、課程名和成績。

程式碼

         Create VIEW GRADE_TABLE AS Select   SNAME,CNAME,GRADE
                                                           FROM   STUDENTS,COURSES,ENROLLS
                                                           Where   STUDENTS.SNO ＝ ENROLLS.SNO AND
                                                                         COURSES.CNO=ENROLLS.CNO

例 11   刪除視圖 GRADE_TABLE

程式碼

Drop VIEW GRADE_TABLE RESTRICT

索引操作
例 12   在學生表中按學號建立索引。

程式碼

         Create   UNIQUE   INDEX   ST ON STUDENTS (SNO,ASC)

例 13   刪除按學號所建立的索引。

程式碼

         Drop INDEX ST

資料庫模式操作
例 14   建立一個簡易教學資料庫的資料庫模式    TEACHING_DB ，屬主為 ZHANG 。

程式碼

         Create SCHEMA TEACHING_DB   AUTHRIZATION   ZHANG

例 15   刪除簡易教學資料庫模式 TEACHING_DB 。
(1)選用 CASCADE ，即當刪除資料庫模式時，則本資料庫模式和其下屬的基本表、視圖、索引等全部被刪除。
(2 )選用 RESTRICT ，即本資料庫模式下屬的基本表、視圖、索引等事先已清除，才能刪除本資料庫模式，否則拒絕刪除。

程式碼

         Drop SCHEMA TEACHING_DB CASCADE

單表操作
例 16   找出 3 個學分的課程號和課程名。

程式碼

          Select CNO, CNAME   FROM 　 COURSES   Where 　 CREDIT ＝ 3

例 17   查詢年齡大於 22 歲的學生情況。

程式碼

          Select   * FROM    STUDENTS   Where   AGE ＞ 22

例 18    找出籍貫為河北的男生的姓名和年齡。

程式碼

          Select SNAME, AGE   FROM 　 STUDENTS
                                                Where 　 BPLACE ＝ ' 河北 '   AND   SEX ＝ ' 男 '

例 19   找出年齡在 20 ～ 23 歲之間的學生的學號、姓名和年齡，並按年齡升序排序。 (ASC （升序）或 DESC （降序）聲明排序的方式，預設為升序。 )

程式碼

          Select SNO, SNAME, AGE    FROM    STUDENTS
                                                      Where   AGE BETWEEN 20 AND 23
                                                      orDER   BY   AGE

例 20   找出年齡小於 23 歲、籍貫是湖南或湖北的學生的姓名和性別。（條件比較子＝、＜ 和邏輯運算子 AND （與），此外還可以使用的運算子有：＞（大於）、＞＝（大於等於）、＜＝（小於等於）、＜＞（不等於）、 NOT （非）、 or （或）等。
謂詞 LIKE 只能與字串聯用，常常是 “ ＜列名＞    LIKE   pattern” 的格式。特殊字元 “_” 和 “%” 作為萬用字元。
謂詞 IN 表示指定的屬性應與後面的集合（括弧中的值集或某個查詢子句的結果）中的某個值相匹配，實際上是一系列的 or （或）的縮寫。謂詞 NOT IN 表示指定的屬性不與後面的集合中的某個值相匹配。
謂詞 BETWEEN 是 “ 包含於 … 之中 ” 的意思。）

程式碼

         Select SNAME, SEX   FROM    STUDENTS
                                               Where   AGE ＜ 23   AND   BPLACE   LIKE' 湖％ '

         或

程式碼

         Select SNAME, SEX   FROM    STUDENTS
                                               Where   AGE ＜ 23   AND   BPLACE   IN   （ ' 湖南 ' ， ' 湖北 ' ）

例 22   找出學生表中籍貫是空值的學生的姓名和性別。（在 SQL 中不能使用條件：＜列名＞＝ NULL 。在 SQL 中只有一個特殊的查詢條件允許查詢 NULL 值：）

程式碼

        Select SNAME, SEX   FROM    STUDENTS    Where   BPLACE IS NULL

多表操作
例 23   找出成績為 95 分的學生的姓名。（子查詢）

程式碼

         Select SNAME    FROM STUDENTS
                                       Where 　 SNO ＝
                           (Select SNO FROM    ENROLLS Where   GRADE ＝ 95)

例 24   找出成績在 90 分以上的學生的姓名。

程式碼

      Select   SNAME    FROM    STUDENTS
                                    Where   SNO IN
                                     (Select SNO   FROM ENROLLS   Where GRADE ＞ 90)

         或

程式碼

     Select   SNAME    FROM    STUDENTS
                                  Where   SNO ＝ ANY
                                  (Select SNO FROM ENROLLS Where GRADE ＞ 90)

例 25   查詢全部學生的學生名和所學課程號及成績。（串連查詢）

程式碼

         Select   SNAME, CNO, GRADE   FROM    STUDENTS, ENROLLS
                                                             Where   STUDENTS.SNO ＝ ENROLLS.SNO

例 26   找出籍貫為山西或河北，成績為 90 分以上的學生的姓名、籍貫和成績。（當構造多表串連查詢命令時，必須遵循兩條規則。第一，串連條件數正好比表數少 1 （若有三個表，就有兩個串連條件 ) ；第二，若一個表中的主關鍵字是由多個列組成，則對此主關鍵字中的每一個列都要有一個串連條件（也有少數例外 情況））

程式碼

         Select   SNAME, BPLACE, GRADE   FROM    STUDENTS, ENROLLS
                           Where   BPLACE IN (‘ 山西 ’ ， ‘ 河北 ’)   AND   GRADE ＞＝ 90   AND                          STUDENTS.SNO=ENROLLS.SNO

例 28   查出課程成績在 80 分以上的女學生的姓名、課程名和成績。（ FROM 子句中的子查詢）

程式碼

         Select   SNAME,CNAME, GRADE    FROM    (Select SNAME, CNAME , GRADE
                           FROM   STUDENTS, ENROLLS,COURSES Where   SEX ＝ ' 女 ')
                           AS TEMP (SNAME, CNAME,GRADE)   Where   GRADE ＞ 80

運算式與函數的使用
例 29   查詢各課程的學時數。（算術運算式由算術運算子＋、－、 * 、／與列名或數值常量所組成。）

程式碼

         Select   CNAME,COURSE_TIME ＝ CREDIT*16   FROM    COURSES

例 30   找出教師的最小年齡。（內建函式： SQL 標準中只使用 COUNT 、 SUM 、 AVG 、 MAX 、 MIN 函數，稱之為聚集合函式 （ Set Function ）。 COUNT 函數的結果是該列統計值的總數目， SUM 函數求該列統計值之和， AVG 函數求該列統計值之平均 值， MAX 函數求該列最大值， MIN 函數求該列最小值。）

程式碼

         Select   MIN(AGE)   FROM    TEACHERS

例 31   統計年齡小於等於 22 歲的學生人數。（統計）

程式碼

         Select   COUNT(*)   FROM    STUDENTS   Where   AGE < ＝ 22

例 32   找出學生的平均成績和所學課程門數。

程式碼

        Select   SNO, AVG(GRADE), COURSES ＝ COUNT(*)    FROM    ENROLLS   GROUP   BY SNO

例 34   找出年齡超過平均年齡的學生姓名。

程式碼

         Select SNAME   FROM STUDENTS 
                          Where AGE ＞ (Select   AVG(AGE)   FROM    STUDENTS)

例 35   找出各課程的平均成績，按課程號分組，且只選擇學生超過 3 人的課程的成績。（ GROUP BY 與 HAVING
         GROUP BY 子句把一個表按某一指定列（或一些列）上的值相等的原則分組，然後再對每組資料進行規定的操作。
         GROUP BY 子句總是跟在 Where 子句後面，當 Where 子句預設時，它跟在 FROM 子句後面。
         HAVING 子句常用於在計算出聚集之後對行的查詢進行控制。）

程式碼

       Select CNO, AVG(GRADE), STUDENTS ＝ COUNT(*)   FROM ENROLLS
                       GROUP BY CNO   HAVING COUNT(*) >= 3

相互關聯的子查詢
例 37   查詢沒有選任何課程的學生的學號和姓名。（當一個子查詢涉及到一個來自外部查詢的列時，稱為相互關聯的子查詢（ Correlated Subquery) 。 相互關聯的子查詢要用到存在測試謂詞 EXISTS 和 NOT EXISTS ，以及 ALL 、 ANY （ SOME ）等。）

程式碼

      Select SNO, SNAME    FROM    STUDENTS    Where   NOT EXISTS
                     (Select *   FROM ENROLLS   Where ENROLLS.SNO=STUDENTS.SNO)

例 38    查詢哪些課程只有男生選讀。

程式碼

         Select DISTINCT CNAME   FROM    COURSES C
                          Where ' 男 ' ＝ ALL
                                     (Select SEX FROM   ENROLLS ， STUDENTS Where ENROLLS.SNO=STUDENTS.SNO AND ENROLLS.CNO=C.CNO)

例 39   要求給出一張學生、籍貫列表，該表中的學生的籍貫省份，也是其他一些學生的籍貫省份。

程式碼

         Select   SNAME, BPLACE   FROM    STUDENTS A   Where   EXISTS
                          (Select *   FROM STUDENTS B   Where A.BPLACE=B.BPLACE AND            A.SNO < > B.SNO)

例 40   找出選修了全部課程的學生的姓名。本查詢可以改為：查詢這樣一些學生，沒有一門課程是他不選修的。

程式碼

       Select   SNAME   FROM    STUDENTS Where   NOT EXISTS
                        (Select * FROM COURSES Where   NOT EXISTS
                                         (Select * FROM ENROLLS Where ENROLLS.SNO ＝ STUDENTS.SNO AND ENROLLS.CNO ＝ COURSES.CNO))

關係代數運算
例 41   設有某商場工作人員的兩張表：營業員表 SP_SUBORD 和營銷經理表 SP_MGR ，其關係資料模式如下：
     SP_SUBORD (SALPERS_ID, SALPERS_NAME, MANAGER_ID, OFFICE)
       SP_MGR (SALPERS_ID, SALPERS_NAME, MANAGER_ID, OFFICE)
       其中，屬性 SALPERS_ID 為工作人員的編號 , SALPERS_NAME 為工作人員的姓名 , MANAGER_ID 為所在部門經理的編號 , OFFICE 為工作地點。
若查詢全部商場工作人員，可以用下面的 SQL 陳述式：

程式碼

      (Select * FROM SP_SUBORD)   UNION   (Select * FROM SP_MGR)

或等價地用下面的 SQL 陳述式：

程式碼

      Select *   FROM (TABLE SP_SUBORD UNION TABLE SP_MGR)

(2) INTERSECT   (Select * FROM SP_SUBORD)
       INTERSECT   (Select * FROM SP_MGR)
或等價地用下面的 SQL 陳述式：
     Select * FROM (TABLE SP_SUBORD INTERSECT TABLE SP_MGR)
或用帶 ALL 的 SQL 陳述式：
        (Select * FROM SP_SUBORD) INTERSECT ALL (Select * FROM SP_MGR)
或
      Select * FROM (TABLE SP_SUBORD INTERSECT ALL TABLE SP_MGR)
(3) EXCEPT   (Select * FROM SP_MGR)
       EXCEPT   (Select * FROM SP_SUBORD)
或等價地用下面的 SQL 陳述式：
     Select *   FROM (TABLE SP_MGR EXCEPT TABLE SP_ SUBORD)
或用帶 ALL 的 SQL 陳述式：
     (Select * FROM SP_MGR)   EXCEPT   ALL   (Select * FROM SP_SUBORD)
例 42   查詢籍貫為四川、課程成績在 80 分以上的學生資訊及其成績。（自然串連）
      (Select * FROM STUDENTS   Where BPLACE=‘ 四川 ’)   NATURAL JOIN
         (Select * FROM ENROLLS     Where GRADE >=80)
例3.43           列出全部教師的姓名及其任課的課程號、班級。（外串連與外部並外串連允許在結果表中保留非匹配元組，空缺部分填以 NULL 。外串連的作用是在做串連操作時避免丟失資訊。
外串連有 3 類：
(1)左外串連（ Left Outer Join ）。串連運算謂詞為 LEFT [OUTER] JOIN ，其結果表中保留左關係的所有元組。
(2)右外串連（ Right Outer Join ）。串連運算謂詞為 RIGHT [OUTER] JOIN ，其結果表中保留右關係的所有元組。
(3)全外串連（ Full Outer Join ）。串連運算謂詞為 FULL [OUTER] JOIN ，其結果表中保留左右兩關係的所有元組。）

程式碼

       Select TNAME, CNO, CLASS   FROM TEACHERS LEFT OUTER JOIN TEACHING USING (TNO)

SQL 的資料操縱
例 44   把教師李映雪的記錄加入到教師表 TEACHERS 中。（插入）

程式碼

      Insert INTO TEACHERS   VALUES(1476 ， ' 李映雪 ' ， 44 ， ' 副教授 ')

例 45   成績優秀的學生將留下當教師。

程式碼

      Insert INTO TEACHERS (TNO ， TNAME)   Select DISTINCT SNO ， SNAME
                              FROM    STUDENTS ， ENROLLS
                              Where STUDENTS.SNO ＝ ENROLLS.SNO AND GRADE ＞＝ 90

例 47   把所有學生的年齡增加一歲。（修改）

程式碼

      Update STUDENTS    SET AGE ＝ AGE+1

例 48   學生張春明在資料庫課考試中作弊，該課成績應作零分計。

程式碼

      Update ENROLLS    SET GRADE ＝ 0   Where CNO ＝ 'C1' AND
                      ' 張春明 ' ＝ (Select SNAME   FROM STUDENTS    Where STUDENTS.SNO=ENROLLS.SNO)

例 49   從教師表中刪除年齡已到 60 歲的退休教師的資料。（刪除）

程式碼

      Delete FROM TEACHERS   Where   AGE ＞＝ 60

SQL 的資料控制
例 50   授予 LILI 有對錶 STUDENTS 的查詢權。（表／視圖特權的授予   一個 SQL 特權允許一個被授權者在給定的資料庫物件上進行特定的操 作。授權操作的資料庫物件包括：表 / 視圖、列、域等。授權的操作包括： Insert 、 Update 、 Delete 、 Select 、 REFERENCES 、 TRIGGER 、 UNDER 、 USAGE 、 EXECUTE 等。其中 Insert 、 Update 、 Delete 、 Select 、 REFERENCES 、 TRIGGER 有對錶做相應操作的許可權，故稱為表特權。）

程式碼

      GRANT Select ON STUDENTS   TO LILI   WITH GRANT OPTION

例 51   取消 LILI 的存取 STUDENTS 表的特權。

程式碼

      REVOKE ALL   ON   STUDENTS    FROM LILI CASCADE