SQL語句學習手冊執行個體

作者:方佳玮     文章來源:網際網路   上傳日期:2006-07-20

<script type=text/javascript> </script>

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