四、關系數據庫標準語言SQL_1

主要內容

• 4.1 SQL概述
  • SQL簡介
• 4.2 SOL的系統結構
• 4.3 SQL數據定義
  • SQL的數據定義
• 4.5 SQL數據查詢
• 4.6 SQL數據更新
• 4.7 SQL中的視圖
• 4.8 SQL的數據控制
• 4.9 嵌入式SQL
• 小結

4.1 SQL概述

主要內容

• SQL簡介
• SQL的特點
• SQL的系統結構

SQL簡介

• SQL（Structured Query Language）

  結構化查詢語言

  • 是一種介于關系代數與關系演算之間的語言；
  • 是一個通用的、功能極強的關系數據庫語言；
  • 目前已成為關系數據庫的標準語言，大多數關系數據庫產品都支持SQL語言；
  • 其前身是1974年Boyce和Chamberlin提出的，并在System R上實現的SQURARE語言。
  • 不區分大小寫

• SQL語言的版本包括：

  • SQL-86
  • SQL-89
  • SQL-92（SQL2）
  • SQL:1999（SQL3）增加了面向對象的概念，超1000頁
  • SQL:2003（SQL4）
  • SQL:2008
  • SQL:2011

  2016年12月14日發布了SQL:2016

  2023年6月1日發布了SQL:2023

• SQL語言按功能劃分為四部分：

  • 數據定義：定義表、視圖和索引
  • 數據操縱：查詢、插入、刪除和修改
  • 數據控制：訪問權限管理、事務管理
  • 嵌入式SQL：SQL語句嵌入到程序語言中使用

SQL的特點

• 綜合統一

  • 集數據定義語言DDL、數據操縱語言DML、數據控制語言DCL的于一體，可以完成數據庫生命周期中的全部活動。
  • 關系模型中實體和實體間的聯系都用關系來表示，使得操作符單一，每種操作只使用一個操作符。

• 高度非過程化

  • 使用SQL語言，只需要提出“做什么”，而無需指明“怎么做”，無需了解存取路徑，提高了數據的獨立性。

• 面向集合的操作方式

  • SQL語言采用集合操作方式，查詢、插入、刪除、修改操作的對象都是集合。

• 以同一種語法結構提供兩種使用方式

  • 作為獨立的語言（交互式SQL）

    提供聯機交互工具，在終端鍵盤上直接鍵入SQL命令對數據庫進行操作，由DBMS來進行解釋；

  • 作為嵌入式語言（嵌入式SQL）

    SQL語句能嵌入到高級語言程序中，使應用程序具備利用SQL訪問數據庫的能力。

  • 不同方式下，SQL的語法結構基本上是一致的，提供了極大的靈活性和方便性

• 語言簡潔，易學易用

  • 完成核心功能只用了9個動詞。

  

4.2 SQL的系統結構

• SQL語言支持數據庫的三級模式結構

  • 在SQL中，關系模式稱為基本表（Base Table），基本表的集合形成數據庫模式，對應三級模式結構的模式
  • 基本表在物理上和存儲文件相對應，所有存儲文件的集合為物理數據庫
  • 外模式由**視圖（View）**組成

  

• SQL的表分為兩種：基本表和視圖

• 基本表（Base Table，Table）

  • 獨立存在的表
  • 一個關系模式對應一個基本表

• 視圖（View）

  • 是從一個或多個基本表中導出的表，僅有邏輯上的定義，不實際存儲數據，是一種虛表。
  • 視圖的定義存儲在數據字典中，在使用的時候，根據定義從基本表中導出數據供用戶使用。
  • 視圖可以像基本表一樣進行查詢和某些更新操作。

4.3 SQL的數據定義

主要內容

• SQL的數據定義功能
• SQL模式的定義
• SQL模式的刪除
• 定義基本表
• 修改基本表
• 刪除基本表
• 建立索引
• 刪除索引

SQL的數據定義功能

• 定義表、定義視圖和定義索引
• 在SQL2中還增加了對SQL數據庫模式的定義。

SQL模式

• 現代關系數據庫管理系統提供了一個層次化的數據庫對象命名機制

  • 一個關系數據庫管理系統的實例（Instance）中可以建立多個數據庫

  • 一個數據庫中可以建立多個模式（Schema）

  • 一個模式下通常包括多個表、視圖和索引等數據庫對象

    

    在 MySQL 中，“數據庫” ≈ “模式”（Schema）。也就是說在 MySQL中，“數據庫”和“模式”（Schema）是同一個概念。

SQL模式的定義

• 一個SQL模式（SQL Schema）由模式名、權限標識符和模式中元素的描述符組成。

  • 權限標識符指明擁有該模式的用戶或賬號
  • 模式元素包含一個數據庫應用的表、視圖和索引等

• 屬于同一應用的表、視圖和索引等可以定義在同一模式中。

• 定義模式后，實際上定義了一個命名空間，可以進一步定義該模式包含的數據庫對象，如表、視圖和索引等。

• 在定義模式時可先給出模式名和權限標識符，以后再定義其中的元素，語法格式：

  CREATE SCHEMA <模式名> AUTHORIZATION <用戶名>;
  

• 可以在創建模式的同時在模式定義中進一步創建基本表、視圖、定義授權等

  CREATE SCHEMA <模式名> AUTHORIZATION <用戶名>
  [<表定義子句>|<視圖定義子句>|<授權定義子句>];
  

• 示例：

  例：定義學生數據庫模式 SST ，用戶為 SDBA

  CREATE SCHEMA SST AUTHORIZATION SDBA;
  CREATE SCHEMA AUTHORIZATION WANG;
  

  沒有指定 “模式名”，則默認為用戶名 WANG

  • 上述的語法格式是標準 SQL 或 PostgreSQL的語法，用于：創建一個數據庫模式（Schema），并指定該模式的所有者是某個用戶。

    但是在 MySQL ，這種寫法是不支持的。

    MySQL不適用 AUTHORIZATION 關鍵字，也不支持給 schema指定“擁有者”，因為：在 MySQL中，數據庫（schema）和用戶是完全分開的，靠授權來建立關系，而不是指定所有者

  下面介紹正確的MySQL語法：

  -- 創建用戶 SDBA，密碼為 123456
  CREATE USER 'SDBA'@'localhost' IDENTIFIED BY '123456';-- 創建數據庫 SST
  CREATE DATABASE SST;-- 授權 SDBA 可以操作 SST 庫中的所有對象
  GRANT ALL PRIVILEGES ON SST.* TO 'SDBA'@'localhost';-- 刷新權限，確保生效
  FLUSH PRIVILEGES;
  

  例：創建模式時定義模式元素

  CREATE SCHEMA AUTHORIZATION ross
  CREATE TABLE t1(c1 INT PRIMARY KEY,c2 INT REFERENCES t2(c1))
  CREATE TABLE t2(c1 INT PRIMARY KEY,c2 INT REFERENCES t1(c1));
  

  這個語句說明，創建了數據庫模式 ross，而且用戶名為ross，同時該模式里面有兩張表，t1 和 t2，t1的主鍵是c1，外鍵是 c2（c2參照t2的c1）。t1的主鍵是c1，c2是外鍵（參照t1的c1）。

SQL模式的刪除

• 刪除模式語句：

  DROP SCHEMA <模式名> [CASCADE|RESTRICT]
  

  • CASCADE（級聯式）方式

    在刪除模式的同時把該模式所屬的基本表、視圖和索引等元素全部一起刪除。

  • RESTRICT（限制式）方式

    只有當模式中沒有任何元素時，才能刪除該模式，否則拒絕該刪除操作。

關于模式的補充

• 不同的系統對于 Schema的定義和使用有所不同

• 在SQL Server2005之后的版本中，創建數據庫時會包含一些默認的Schema：dbo，guest，sys，INFORMATIONI_SCHEMA，另外有一些角色Schema等

• 創建數據庫對象（如TABLE），如果沒有指定Schema，則:

  1. 創建在當前登錄用戶默認的Schema上；
  2. 若沒有默認的Schema，則創建在dbo Schema上；
  3. 如果指定了Schema，則按照指定的做。

• Schema 的查找順序

  • 假設有個登錄用戶Sue，默認Schema為Sue，現需查找使用某個表mytable，

    SELECT * FROM mytable;
    

    那么系統查找該表的順序是：

    1. sys.mytabl(sys Schema)
    2. Sue.mytable(Default Schema)
    3. dbo.mytable(dbo Schema)

• 系統默認的Schema不能刪除

定義基本表

CREATE TABLE <表明>
(<列明> <數據類型>[ <列級完整性約束條件> ]
[,<列名> <數據類型>[ <列級完整性約束條件> ]]……[,<表級完整性約束條件>]);

• <表名>：所要定義的基本表的名字
• <列名>：組成該表的各個屬性（列）
• <列級完整性約束條件>：涉及相應屬性列的完整性約束條件
• <表級完整性約束條件>：涉及一個或多個屬性列的完整性約束條件

【例】建立學生表Student，表中屬性有：學號Sno，姓名Sname，年齡Sage，性別Ssex，學生所在系Sdept

CREATE TABLE Student(Sno CHAR(6) NOT NULL UNIQUE,Sname CHAR(8),Sage INT,Ssex CHAR(2),Sdept CHAR(12),CONSTRAINT C1 CHECK (Ssex IN ('男','女')),CONSTRAINT S_PK PRIMARY KEY(Sno)
);

• CONSTRAINT子句定義列級或表級約束，格式為

  CONSTRAINT <約束名> <約束>

• SQL常用的數據類型

  SMALLINT 短整數

  INTEGER或INT 長整數

  REAL 浮點數

  DOUOBLE PRECITION 雙精度浮點數

  FLOAT(n) 浮點數，精度為n位

  NUMBER(P[,q]) 定點數，共p位，其中小數點后有q位

  CHAR(n) 長度為n的定長字符串

  VARCHAR(n) 最大長度為n的變長字符串

  BIT(n) 長度為n的二進制位串

  BIT VARCHAR(n) 最大長度為n的二進制位串

  DATE 日期型，格式為YYYY-MM-DD

  TIME 時間型，格式位HH：MM：SS

  TIMESTAMP 日期加時間

• SQL2中增加了定義域的語句，可以用域名代替指定列的數據類型。

• 如果有一個或多個表的屬性的域是相同的，通過對域的修改可以很容易地改變屬性的數據類型。

• 域定義語句的格式為：

  CREATE DOMAIN <域名> <數據類ixing>
  

  • 【例】

    CREATE DOMAIN Sdept_TYPE CHAR(12);
    

    域Sdept_TYPE創建后，定義學生表時，對列Sdept的類型定義可以用域名代替：Sdept Sdept_TYPE

  • 【例】

    CREATE TABLE Course(Cno CHAR(6) NOT NULL,Cname CHAR(20),Ccredit INT,CONSTRAINT C_PK PRIMARY KEY (Cno));
    CREATE TABLE SC(Sno CHAR(6) NOT NULL,Cno CHAR(6) NOT NULL,Grade INT CHECK (Grade BETWEEN 0 AND 100),CONSTRAINT SC_PK PRIMARY KEY (Sno,Cno),CONSTRAINT SC_FK1 FOREIGN KEY (Sno) REFERENCES Student(Sno),CONSTRAINT SC_FK2 FOREIGN KEY (Cno) REFERENCES Course(Cno));
    

    也可以如下寫

    CREATE TABLE Course(Cno CHAR(6) NOT NULL,Cname CHAR(20),Ccredit INT,PRIMARY KEY (Cno));
    CREATE TABLE SC(Sno CHAR(6) NOT NULL,Cno CHAR(6) NOT NULL,Grade INT CHECK (Grade BETWEEN 0 AND 100)PRIMARY KEY (Sno,Cno),FOREIGN KEY (Sno) REFERENCES Student(Sno),FOREIGN KEY (Cno) REFERENCES Course(Cno));
    

• 在定義基本表時，表所屬的數據庫模式一般被隱式指定，也可以顯示地在定義表時指定表所屬的數據庫模式名。

  • 如下語句在定義學生表時，同時指出學生表所在的模式為學生數據庫模式SST。

    CREATE TABLE SST.Student(Sno CHAR(6) NOT NULL UNIQUE,……);
    

• 在創建模式語句中同時創建表

  在數據庫中，我們可以在創建數據庫模式（Schema）的時候，順便創建這個模式下的表（Table），而不是先建模式、再建表。

  CREATE TABLE SST.Student(Sno CHAR(6) NOT NULL UNIQUE,……);
  

• 常用完整性約束

  • 主鍵約束：PRIMARY KEY
  • 唯一性約束：UNIQUE
  • 非空值約束：NOT NULL
  • 參照完整性約束：就是上述例子里的一些外鍵

修改基本表

ALTER TABLE <表名>[ADD <列名> <數據類型> [<完整性約束>]][DROP <列名> [CASCADE|RESTRICT]][ALTER <列名> <數據類型>];

• ADD子句用于增加新列，包括列名、數據類型和列級完整性約束
• DROP子句用于刪除指定的列名，
  • CASCADE表示刪除列時自動刪除引用該列的視圖和約束
  • RESTRICT表示沒有視圖和約束引用時才能刪除該列，否則拒絕刪除操作
  • 但在MYSQL一般直接刪除即可，因為MySQL 不支持 在 DROP COLUMN 后使用 CASCADE 或 RESTRICT。如果刪除列有依賴（如被視圖、索引、外鍵引用），MySQL 會自動報錯，提示不能刪，需要你手動先刪除依賴對象。
• ALTER子句用于修改列的定義，如修改列的數據類型或修改列的寬度等

【例】在學生表Student增加一列，列名為班級。

ALTER TABLE StudentADD Class CHAR(8);

• 不論基本表中原來是否已有數據，新增加的列一律為空值；不能在其上指定NOT NULL（除非配合DEFAULT有默認值）

【例】修改學生表Student中姓名列的長度為20。

ALTER TABLE Student ALTER Sname CHAR(20);

或者（ 下面是MySQL 的正確寫法。上面是標準SQL）

ALTER TABLE Student MODIFY Sname CHAR(20);

• 修改原有的列定義有可能會破壞已有數據

ALTER 語句用于修改基本表的結構，例如添加、刪除或修改表中的列（如：ALTER TABLE table_name ADD COLUMN column_name datatype）。

DELETE （后續會提到）語句用于刪除基本表中的行（如：DELETE FROM table_name WHERE condition）。

因此：ALTER 修改的是表的定義，影響的是表的結構。DELETE 刪除的是表中的數據，影響的是表的內容。

刪除基本表

DROP TABLE <表名> [RESTRICT|CASCADE];

• 若選擇RESTRICT，則刪除的基本表不能被其他表的約束所引用（如有CHECK,FOREIGN KEY等約束），不能有視圖，不能有觸發器，不能有存儲過程或函數等。如果存在這些依賴該表的對象，則此表不嫩被刪除。
• 若選擇CASCADE，則該表的刪除沒有限制條件。在刪除基本表的同時，相關的依賴對象，例如視圖等都將被一起刪除。
• 一般在缺省的情況下默認為RESTRICT，與具體實現有關。
• 在MySQL中則直接DROP TABEL <表名>，類似DROP TABLE <表名> RESTRICT。如果有依賴，那么MySQL會自動報錯。

建立索引

• 索引是一種數據結構

  • 索引技術是數據庫管理系統的核心問題之一；

  • 在表上建立索引，可以提供不同的存取路徑，可以加快查詢速度。

  • 可根據需要在一個表上建立一個或多個索引；

  • DBA或表的創建者有權建立和刪除索引；

  • 索引的更新和維護是由DBMS自動完成的；

  • 系統在存取數據時會自動選擇是否使用索引，或者是以合適的索引作為存取路徑，用戶不必也不能選擇索引；

  • 有些DBMS能自動在某些特殊屬性列上建立索引

    • PRIMARY KEY
    • UNIQUE

    下面給出例子，在該表中

    1. 主鍵 = 聚焦索引（Clusterd Index）

       • Col2 是主鍵，所以它也是這張表的聚集索引
       • 聚集索引結構為 B+樹
       • 葉子節點中存儲的是整行數據（整行！）

       所以從主鍵查數據，不需要“回表”，因為數據已經就在葉子節點里了。

    2. 數據與聚集索引的存儲方式

       • 表數據與聚集索引是同一份結構（不是分開存，是在同一磁盤同一片區域）
       • 聚集索引的 B+樹 本質上就是表的數據存儲順序
       • 圖中 B+ 樹的葉子節點：5 → 22 → 23 → 34 → 77 → 89 → 91 是實際數據的主鍵順序

    3. 二級索引（輔助索引）

       • 你又創建了一個二級索引，比如對 Col1 建立唯一索引或普通索引
       • 二級索引也是 B+樹結構，但它的葉子節點不存整行數據
       • 二級索引的葉子節點中只存兩樣東西：
         1. 被索引的列值（比如 Col1 的值）
         2. 對應記錄的主鍵值（Col2）

    4. 回表

       比如執行語句：

       SELECT * FROM table WHERE Col1 = 5;
       

       步驟如下：

       1. 去 Col1 的二級索引 B+樹中查找值為 5
       2. 找到了，對應的主鍵值是 22
       3. 再去 主鍵（Col2）的聚集索引 B+樹中找 22
       4. 找到整行數據：(Col1=5, Col2=22)

       這就是回表機制：二級索引中找到主鍵，再通過主鍵找數據。

    

    • 左邊藍色表格：
      • 數據庫中的一張表，包含兩列Col1和Col2。
      • 第一列是地址（比如0x07），第二列是實際數據指（比如34）
    • 右邊橙色結點的樹形結構：
      • 表示某列（如Col2）上的索引結構
      • 樹的結構類似于B+樹或二叉查找樹；
      • 結點中的數值對應Col2的內容（如34，23，5，23……）；
      • 每個葉子節點通過箭頭指向表中對應的元組的行位置
    • 假設我們要在Col2上查找值為77的記錄：
      1. 數據庫先走索引樹，從根節點開始查找：
         • $找到34 \rightarrow 小于 \rightarrow 到89 \rightarrow 大于 \rightarrow 到77 $
      2. 訪問葉子節點 77，箭頭指向表中地址 0x56 這一行
      3. 最終從表中讀取出完整行。

    這就是索引帶來的效率提升：避免從頭到尾掃描整張表。（可能這個示例沒體現出來）

  • 格式

    CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名> (<列名>[<次序>][,<列名>[<次序>]]……)
    

    • <表名>指定要建立索引的基本表名字
    • 索引可以建立在該表的一列或多列上，各列名之間用逗號分隔
    • <次序>指定索引值的排列次序，升序ASC，降序DESC。缺省值（默認）：ASC（也就是說索引里面是按照值升序或降序）
    • UNIQUE表明此索引的每一個索引值只對應唯一的數據記錄（強制某一列（或列組合）的值不能重復，保證數據唯一性，可以有NULL）
    • CLUSTER表示要建立的索引是聚焦索引（Cluster Index）

• 【例】在學生表Student的學號列上按升序建立惟一索引。

  CREATE UNIQUE INDEX S_SNO ON Student(Sno);
  

• 【例】在表Student上按班級降序、年齡升序建立索引。

  CREATE INDEX SCLASS_AGE ON Student(Class DESC,Sage ASC);
  

• 唯一值索引（UNIQUE）

  • 對于已含重復值的屬性列不能建立UNIQUE索引

  • 對某個列建立UNIQUE索引后，插入新記錄時DBMS會自動檢查新紀錄在該列上是否取了重復值。

    這相當于增加了一個UNIQUE約束。

  • 【例】

    CREATE UNIQUE INDEX SnoIdx ON Student(Sno);
    CREATE UNIQUE INDEX CnoIdx ON Course(Cno);
    CREATE UNIQUE INDEX SCNO ON SC(Sno ASC,Cno DESC);
    

• 聚集索引（Cluster Index）

  • 索引次序與基本表中元組的物理次序一致的索引；建立聚集索引后，基表中數據也需要按指定的聚集屬性值的升序或降序存放，即：聚集索引的索引項順序與表中記錄的物理順序一致，二者在一塊存儲區域

  • 例：

    CREATE CLUSTER INDEX Stusname ON Student(Sname ASC);
    

    在Student的Sname（姓名）列上建立了一個聚焦索引，而且Student表中的記錄將按照Sname值的升序存放。

    MySQL 中不支持手動創建“聚集索引”！

    在 MySQL 的 InnoDB 存儲引擎中：

    • 聚集索引是自動創建的，不允許手動指定為 CLUSTER INDEX
    • 聚集索引 ≈ 主鍵（PRIMARY KEY）
      • 若表有主鍵，聚集索引就是主鍵；
      • 若無主鍵，則找一個唯一非空索引；
      • 都沒有，InnoDB 自動生成一個隱藏主鍵（row_id）

刪除索引

• DROP INDEX <索引名> ON <表名>

  • 刪除索引時，系統會從數據字典中刪除有關該索引的描述。

  • 例：刪除學生表上建立的S_SNO索引

    DROP INDEX S_SNO ON Student;
    

索引的選擇

• 索引為性能所帶來的好處是有代價的。
  • 對某個屬性建立索引，能極大提高對該屬性上的值的檢索效率；在使用該屬性進行連接操作時，還可以加快連接速度。
  • 帶索引的表在數據庫中會占據更多的空間。
  • 索引的維護需要一些額外的計算代價。對數據進行插入、刪除和更新操作時，所花費的時間會更長。
• 應根據數據的性質和基于表的查詢性質，來決定是否創建索引，應確保對性能的提高程度大于在存儲空間和處理資源方面的額外開銷。

索引是關系數據庫的內部實現技術，屬于內模式的范疇。不做多的描述。

4.5 SQL數據查詢

主要內容

• 1.查詢語句的一般格式
• 2.單表查詢
• 3.連接查詢
• 4.嵌套查詢
• 5.集合查詢
• 6.基于派生表的查詢

1.查詢語句的一般格式

SELECT [ALL|DISTINCT] <目標表達式> [,<目標列表達式>]……FROM <表明或視圖名>[,<表名或視圖名>]……[WHERE <條件式1>][GROUP BY <列名>[HAVING <條件表達式2>]][ORDER BY <列名>[ASC|DESC]]

• **SELECT子句：**指定要顯示的屬性列
• **FROM子句：**指定查詢對象（基本表或視圖）
• **WHERE子句：**指定查詢條件
• **GROUP BY子句：**對查詢結果按指定列的值分組，按屬性列值相等的元組一個組。通常會在每組中使用聚集函數。
• **HAVING短語：**篩選出滿足條件的元組。
• **ORDER BY子句：**對查詢結果表按指定列值的升序或降序排序。
• DISTINCT表示去掉重復元組，ALL則容許重復數組。

查詢語句的基本結構

SELECT A1,A2,……,An
FROM R1,R2,……,Rm
WHERE P

等價于

${\Pi }_{A1,A2,\dots \dots ,An}({\sigma }_p(R_1\times R_2\times \dots \dots \times R_m))$

示例數據庫：學生——課程數據庫（下面的舉例都用到該表）

• 學生表：

  $Student(Snp,Sname,Sage,Ssex,Sdept)$

  Sno	Sname	Sage	Ssex	Sdept
  200413	劉敏	21	女	數學
  200701	劉明亮	19	男	計算機系
  200702	李和平	20	男	外語
  200703	王茵	21	女	計算機系
  200704	張小芳	21	女	數學
  200705	李斌	21	男	計算機系

• 課程表：

  $Course(Cno,Cname,Credit,Cpno)$

  Cno	Cname	Ccredit
  1	C語言	4
  2	英語	3
  3	數據庫	4
  4	數學	4
  5	數據結構	4
  6	數據處理	2
  7	PASCAL語言	4

• 學生選課表：

  $SC(Sno,Cno,Grade)$

  Sno	Cno	Grade
  200701	1	98
  200701	2	92
  200701	4	88
  200702	1	81
  200703	1	89
  200703	2	90
  200705	6	87

2.單表查詢

• 查詢僅涉及一個表，是一種最簡單的查詢操作
  • 選擇表中的若干列
  • 選擇表中的若干元組
  • 對查詢結果排序
  • 使用聚焦函數
  • 對查詢結果分組

單表查詢示例

• 【例】查詢學生的學號和姓名。

  SELECT Sno,Sname FROM Student;
  

• 【例】查詢計算機系學生的學號和姓名。

  SELECT Sno,Sname FROM StudentWHERE Sdept = '計算機系';
  

• 【例】查詢年齡在18歲到25歲之間的學生信息

  SELECT * FROM Student WHERE Sage BETWEEN 18 AND 25;
  

• 【例】查全體學生的姓名及其出生年份

  SELECT Sname,2025 - Sage FROM Student;
  

  

• 【例】查詢已經選修了課程的學生學號，并按學號升序排列。

  SELECT DISTINCT SnoFROM SCORDER BY Sno ASC;
  

• 【例】查詢每門課的選修人數。

  SELECT Cno,COUNT(*)FROM SCGROUP BY Cno;
  

  

  1. COUNT(*)是什么？

     • 作用：COUNT(*)是一種 聚合函數，用于統計某個分組中“行的數量”。COUNT(*) 的行為要結合 GROUP BY 一起看。
     • 在該語句中：它表示“每門課程(Cno)被多少學生選修了”。因為GROUP BY Cno;，所以分組的是Cno，所以統計的是每組Cno有多個學生。

  2. GROUP BY是什么？

     • 作用：GROUP BY 用于將查詢結果按某個字段進行分組，每組只輸出一行。
     • 在該語句中：按照Cno來分組，也就是說：
       • 同一個課程編號的所有成績記錄（即多個學生選這門課）被歸到一組。
       • 每個Cno組統計一次 COUNT(*)。

  3. 什么是聚合函數？

     聚合函數是一類對一組值進行計算，并返回單個結果值的函數，常用于統計分析。

• 【例】查詢平均成績在85分以上的學生的學號和平均成績

  SELECT Sno,AVG(Grade)FROM SCGROUP BY SnoHAVING AVG(Grade) > 85;
  

  • HAVING子句是用于對分組結果進行篩選的，作用類似于 WHERE，但它是作用在 GROUP BY 之后的結果上。

  • HAVING和WHERE的區別：

    關鍵字	作用對象	是否能用聚合函數
    WHERE	原始表的行記錄	不能
    HAVING	GROUP BY后的分組	可以

• 【例】查詢成績在75~85分之間的學生的學號和成績

  SELECT Sno,Grade FROM SCWHERE Grade >=75 AND Grade <=85;
  

• 【例】查詢年齡為19歲的所有姓李的學生姓名

  SELECT Sname FROM StudentWHERE Sname LIKE '李%' AND Sage = 20;
  

  1. LIKE是什么？

     • 功能：LIKE是 SQL 中用于模糊匹配字符串的操作符。

       它常用于 WHERE 子句，搭配 通配符 來進行部分匹配查詢。

  2. 統配符說明

     通配符	含義	示例
     %	表示任意個（0個或多個）字符	%李表示以“李”開頭。
     _	表示任意單個字符	李_表示“李”后跟一個字

  3. '李%'的含義

     • 李%表示：以“李”開頭的字符串

• 【例】查詢缺考學生的學號和課程號

  SELECT Sno,Cno FROM SCWHERE Grade = NULL;
  

查詢經過計算的值

• SELECT子句的<目標列表達式>不僅可以是表中的屬性列，也可以是表達式

  • 算術表達式
  • 字符串常量
  • 函數
  • 列別名等

• 例：查詢全體學生的姓名、出生年份和所在系，要求用小寫字母表示所在系名。

  SELECT Sname,'Year of Birth:',2025 - Sage,LOWER(Sdept) FROM Student;
  

  

  我這里所在系名稱是中文，所以體現不出LOWER()函數“用小寫字母表示所在系名”

使用列別名改變查詢結果的列標題

SELECT Sname AS NAME,'Year of Birth:' BIRTH,2000 - Sage BIRTYEAR,LOWER(Sdept) DEPARTMENTFROM Student;

查詢結果：

1. Sname AS NAME
   • 將學生姓名列Sname重名為NAME(即：列標題改為 NAME,可以省略AS)
   • 該列輸出的是學生的姓名
2. Year of Birth:
   • 這是一個 常量字符串，不是表里的字段
   • 這個常量值有個別名叫 BIRTH
   • 所以這一列每一行都會顯示 Year of Birth:，列名是BIRTH
3. 后面兩列同理。

使用DISTINCT短語消除取值重復的行

假設SC表中有下列數據

進行如下SQL查詢語句

SELECT DISTINCT Sno FROM SC;

結果：

進行如下SQL查詢語句

SELECT Sno FROM SC;

或

SELECT ALL Sno FROM SC;

結果：

• 注意DISTINCT短語的作用范圍是所有目標列

  錯誤寫法

  SELECT DISTINCT Cno,DISTINCT Grade FROM SC;
  

  正確寫法

  SELECT DISTINCT Cno,Grade FROM SC;
  

WHERE子句常用的查詢條件

關鍵字	說明
比較表達式	<列名1> 比較算符 <列名2（或常量）> 比較運算符：=、>、>=、<、<=、<>(或!=)
邏輯表達式	<條件表達式1> 邏輯算符 <條件表達式2> 邏輯算符：AND、OR、NOT
BETWEEN	<列名1> （NOT）BETWEEN <常量1或列名2> AND <常量2或列名3>
IN	<列名1> （NOT）IN（常量、表列 或 SELECT語句）
LIKE	<列名> （NOT）LIKE ‘匹配字符串’ 匹配符：“_”表示匹配一個字符，“%”表示匹配任意字符串
NULL	<列名> IS （NOT）NULL
EXISTS	（NOT）EXISTS（SELECT語句）

確定范圍

• 使用謂詞 BETWEEN……AND…… 或 NOT BETWEEN……AND……

• 【例】查詢年齡在20~23歲之間的學生的姓名、系別和年齡。

  SELECT Sname,Sdept,Sage FROM Student WHERE Sage BETWEEN 20 AND 23;
  

• 【例】查詢年齡不在20~23歲之間的學生姓名、系別和年齡

  SELECT Sname,Sdept,Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23;
  

確定集合

• 使用謂詞 IN <值表> ，NOT IN <值表>

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

• 【例】查詢信息系（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';
  

字符串匹配

• [NOT] LIKE '匹配串' [EXCAPE '<換碼字符>']

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

  • 匹配模板：固定字符串或含通配符的字符串

    • 當匹配模板為固定字符串時，可用=運算符取代LIKE謂詞，用!=或<>運算符取代NOT LIKE。

  • 通配符

    • %代表任意長度（長度可以為0）的字符串

    • _（下劃線）代表任意單個字符

    • 例：

      a%b表示以a開頭，以b結尾的任意長度的字符串；

      a_b表示以a開頭，以b結尾的長度為3的任意字符串。

  • ESCAPE短語：

    • 當用戶要查詢的字符串本身就含有%或_時，要使用ESCAPE '<轉碼字符>'短語對通配符進行轉義。

字符串匹配示例

• 匹配模板為固定字符串

  SELECT * FROM Student WHERE Sno LIKE '95001';
  

  等價于

  SELECT * FROM Student WHERE Sno = '95001';
  

• 匹配模板為含通配符的字符串

  【例】查詢所有姓劉學生的姓名、學號和性別。

  SELECT Sname,Sno,Ssex FROM Student WHERE Sname LIKE '劉%';
  

  【例】查詢姓“歐陽”且全名為三個漢字的學生姓名

  SELECT Sname FROM Student WHERE Sname LIKE '歐陽_';
  

• 使用換碼字符將通配符轉義為普通字符

  【例】查詢DB_Design課程的課程號和學分。

  SELECT Cno,Credit FROM Course WHERE Cname LIKE 'DB\_Design' ESCAPE '\'; 
  

  【例】查詢以“DB_”開頭，且倒數第三個字符為i的課程的詳細情況

  SELECT * FROM Course WHERE Cname LIKE 'DB\__i__' ESCAPE '\';
  

涉及空值的查詢

• 使用謂詞 IS NULL 或 IS NOT NULL

• IS NULL不能使用 = NULL代替

  【例】某些學生選秀課程后沒有參加考試，所以有選課記錄，但沒有考試成績。查詢缺少成績的學生和相應的課程號。

  SELECT Sno,Cno FROM SC WHERE Grade IS NULL;
  

  【例】查所有有成績的學生學號和課程號。

  SELECT Sno,Cno FROM SC WHERE Grade IS NOT NULL;
  

空值

• SQL允許屬性有一個特殊值NULL稱作空值。

  • 未知值：有值但是不知道是什么，例如未知生日
  • 不適用的值：例如配偶的名字
  • 保留的值：無權知道的值，例如未公布的電話號碼

• 空值的運算

  • 空值不同于空白或零值。沒有兩個相等的空值。空值和任何值進行算術運算，結果仍為空值。
    • 執行計算時消除空值很重要，因為包含空值列的某些計算（如平均值）會不準確。
  • 當使用邏輯運算符和比較運算符，有可能返回結果UNKNOWN（與TRUE和FALSE相同的布爾值）

  

空串

• 空串是指長度為零的字符串
  • 當 m 為 0 或 負數時，RIGHT('123',m)返回空字符串RIRIM('')返回空字符串

多重條件查詢

• 用邏輯運算符AND和OR來連接多個查詢條件

  • AND的優先級高于OR
  • 可以用括號改變優先級

• 可用來實現多種其他謂詞

  • [NOT] IN
  • [NOT] BETWEEN …… AND ……

  【例】查詢計算機系年齡在20歲以下的學生姓名。

  SELECT Sname FROM Student WHERE Sdept = '計算機系' AND Sage < 20;
  

對查詢結果排序

• 使用ORDER BY子句

  • 可以按一個或多個屬性列排序
  • 升序：ASC；降序：DESC；缺省值（默認）為升序

• 空值將作為最大值排序

  • ASC：排序列為空值的元組最后顯示
  • DESC：排序列為空值的元組最后顯示

• 【例】查詢選修了1號課程的學生的學號及其成績，查詢結果按分數降序排列。

  SELECT Sno,Grade FROM SC WHERE Cno = '1' ORDER BY Grade DESC;
  

  

使用聚集函數（聚合函數）

• SQL提供了許多聚合函數，用來實現統計查詢

  • 計數

    COUNT([DISTINCT|ALL]*)
COUNT([DISTINCT|ALL]<列名>)

  • 計算總和 SUM([DISTINCT|ALL]<列名>)

  • 計算平均值 AVG([DISTINCT|ALL]<列名>)

  • 求最大值 MAX([DISTINCT|ALL]<列名>)

  • 求最小值 MIN([DISTINCT|ALL]<列名>)

• 選項DISTINCT表示在計算時要取消指定列中的重復值；ALL表示不取消重復值；默認為ALL。

  【例】查詢學生總人數。

  SELECT COUNT(*) FROM Student;
  

  【例】查詢選修了課程的學生人數

  SELECT COUNT(*) FROM SC;
  

  【例】計算1號課程的學生平均成績。

  SELECT AVG(Grade) FROM SC WHERE Cno = '1';
  

  【例】查詢1號課程的最高分數。

  SELECT MAX(Grade) FROM SC WHERE Cno = '1';
  

對查詢結果分組

• 使用GROUP BY子句分組

• 細化聚合函數的作用對象

  • 未對查詢結果分組時（沒有使用GROUP BY），聚集函數將作用于整個查詢結果。
  • 對查詢結果分組后（使用GROUP BY），聚集函數將分別作用于每個組（會和GROUP BY結合起來）。

• 分組方法

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

• 使用GROUP BY子句后，SELECT子句的列名列表中只能出現分組屬性和聚合函數。

• GROUP BY子句的作用對象是查詢的中間結果表

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

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

  【例】求各個課程號及相應的選課人數。

  SELECT Cno,COUNT(*)	-- COUNT(Sno)FROM SC GROUP BY Cno;
  

  【例】查詢選修了3門以上課程的學生學號

  SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) > 3;
  

  【例】查詢平均成績大于等于90分的學生學號和平均成績

  錯誤示范：

  SELECT Sno,AVG(Grade) FROM SC WHERE AVG(Grade) >= 90 GROUP BY Sno;
  

  正確示范：

  SELECT Sno,AVG(Grade) FROM SC GROUP BY Sno HAVING AVG(Grade) >= 90;
  

  說明：

  1. 錯誤點：WHERE是在 分組（GROUP BY）之前執行 的，此時還沒進行GROUP BY分組，也就是沒有辦法計算每個學生的AVG(Grade)。AVG(Grade)是在GROUP BY之后才出現的東西，WHERE根本還看不到它。

  2. 用HAVING正確處理：因為我們想篩選的是“每個學生的平均成績”，這是聚合后的結果，所以我們要在GROUP BY后使用HAVING來處理。

  3. 用法對比	WHERE	HAVING
     執行時機	在GROUP BY之前	在GROUP BY和聚合函數之后
     能否使用聚合函數	不能使用聚合函數（AVG，SUM等）	可以使用聚合函數
     用于篩選	原始記錄	聚合后的分組

  4. 聚合之后用 HAVING，聚合之前用 WHERE。

  【例】查詢有3門以上及3門課程是90分以上及90分的學生的學號及（90分以上的）課程數

  SELECT Sno,COUNT(*) '90及90分以上的課程數' FROM SC WHERE Grade >= 90GROUP BY SnoHAVING COUNT(*) >= 3;
  

• HAVING短語于WHERE子句的區別

  • 作用對象不同
    • WHERE子句作用于基表或視圖的原始記錄，從中選擇滿足條件的元組（tuple）。
    • HAVING短語作用于組，和GROUP BY搭配，從中選擇滿足條件的組（group）。
  • WHERE子句中不能使用聚集函數；而HAVING短語中可以使用聚集函數。

• 分組查詢補充示例

  假設有下面的表 orderhist

  

  執行語句

  SELECT productid,SUM(quantity) AS total_quantityFROM orderhistWHERE orderhistGROUP BY productid;
  

  結果：

  執行語句

  SELECT productid,SUM(quantity) AS total_quantityFROM orderhistWHERE productid = 2GROUP BY productid;
  

  結果：

  執行語句

  SELECT productid,SUM(quantity) AS total_quantityFROM orderhistGROUP BY productidHAVING SUM(quantity)>=30;
  

  結果：

3.連接查詢

• 同時涉及兩個或兩個以上表的查詢稱為連接查詢

  • 用來連接兩個表的條件稱為連接條件或連接謂詞
  • 連接謂詞中的列名稱為連接字段
  • 連接條件中各連接字段的類型必須是可比的，但不必是相同的。

• 連接條件的一般格式

  • [<表名1>.]<列名> <比較運算符> [<表名2>.]<列名2>

    其中比較運算符為：=、>、<、>=、<=、!=

• SQL中連接查詢的主要類型

  • 廣義笛卡爾積
  • 等值（含自然連接）
  • 非等值連接查詢
  • 自身連接查詢
  • 外連接查詢
  • 復合條件連接查詢

廣義笛卡爾積

• 不帶連接謂詞（即沒有WHERE子句）。

• 廣義笛卡爾積是兩表元組的交叉乘積，其連接的結果會產生沒有意義的元組， 實際上很少使用。

  【例】

  SELECT Student.*,SC.* FROM Stuodent,SC;
  

等值連接

• 連接運算符=的連接操作

  • [<表名1>.]<列名1> = [<表名2>.]<列名2>
  • 任何子句中引用表1和表2中同名屬性時，都必須加表名前綴。引用唯一屬性名時可以省略表名。

  【例】

  假設有如下表

  

  執行語句

  SELECT Student.*,SC.*FROM Student,SCWHERE Student.Sno = SC.Sno;
  

  結果：

  

自然連接

• 等值連接的一種特殊情況，把目標列中重復屬性去掉

  【例】假設有如下表

  

  執行語句

  SELECT * FROM Student NATURAL JOIN SC;
  

  結果：

  

非等值連接查詢

• 連接運算符不是=的連接操作

  [<表名1>.]<列名1> <比較運算符> [<表名2>.]<列名2>

• 比較運算符：>、<、>=、<=、!=

自身連接

• 一個表與其自己進行連接，稱為表的自身連接

• 需要給表起別名以示區別

• 由于所有屬性名都是同名屬性，因此必須使用別名前綴

  【例】假設有如下表

  

  查詢每一門課的間接先修課（即先修課的先修課）

  SELECT FIRTST.Cno,SECOND.CpnoFROM Course AS FIRST,Course AS SECONDWHERE FIRST.Cno = Course.Cno;
  

  

  結果：

  

內連接

• 典型的連接運算，使用像=或<>（!=）之類的比較運算符

• 只保留兩個表中滿足連接條件的元組（行），不滿足條件的會被過濾掉，不出現在結果中。

• 內連接包括等值連接和自然連接

• 內連接使用比較運算符根據每個表共有的列的值匹配兩個表中的行

• 【例】假設有如下表

  

  執行語句

  SELECT buyer_name,,sales.buyer_id,qtyFROM buyers INNER JOIN salsON buyers.buyer_id = sals.buyer_id;
  

  結果：

  

外連接

• 外連接概念：外連接是相對于“內連接”而言的，它不僅包含兩個表中滿足連接條件的元組，還包括某一方（或兩方）中不滿足條件的元組，這些不匹配的部分會用 NULL 補齊。

• 外連接與內連接的區別

  • 內連接操作只輸出滿足連接條件的元組
  • 外連接操作以指定表為連接主體，將主體表中不滿足連接條件的元組一并輸出

• 外連接分類

  • 左外連接**（LEFT OUTER JOIN）**
  • 右外連接**（RIGHT OUTER JOIN）**
  • 全外連接**（FULL OUTER JOIN）**
  • SQL2支持的外連接

  【例】有如下表，查詢每個學生的選課情況，包括沒有選課的學生

  

  執行語句

  SELECT Student.Sno,Student.Sname,Ssex,Ssage,Sdept,Cno,GradeFROM Student LEFT OUTER JOIN SC ON (Student.Sno = SC.Sno)
  

  結果：
  

復合條件連接

• WHERE子句鐘含多個連接條件時，稱為復合條件連接。

  • 復合條件連接可以看作（普通）連接后得到的關系（表）又進行一次選擇運算

  【例】查詢選修2號課程且成績在90分以上的所有學生的學號、姓名

  SELECT Student.Sno,Sname FROM Student,SCWHERE Student.Sno = SC.Sno AND SC.Cno = '2' AND Grade > 90;
  

多表連接

• 連接操作涉及到兩個以上的表的連接

  【例】查詢每個學生的學號、姓名、選修的課程名及成績。

  SELECT Student.Sno,Sname,Cname,Grade FROM Student,SC,CourseWHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno;
  

  結果：
  

4.嵌套查詢

• 嵌套查詢概述
• 嵌套查詢分類與求解方法
• 引出子查詢的謂詞
  • 帶有IN謂詞的子查詢
  • 帶有比較運算符的子查詢
  • 帶有ANY或ALL謂詞的子查詢
  • 帶有EXISTS謂詞的子查詢

嵌套查詢概述

• 一個SELECT-FROM-WHERE語句稱為一個查詢塊

• 將一個查詢塊嵌套在另一個查詢塊的WHERE子句或HAVING短語的條件中的查詢稱為嵌套查詢

• 【例】

  SELECT Sname		-- 這是外層查詢也叫做父查詢FROM StudentWHERE Sno IN(SELECT Sno 	-- 這是內層查詢也叫做子查詢FROM SC WHERE Cno = '2');			
  

• 嵌套查詢的實現

  一般是從里到外，先進行子查詢，再把其結果用于父查詢作為條件

• 層層嵌套方式反映了 SQL語言的結構化

• 有些嵌套查詢可以用連接運算代替

• 子查詢的限制：不能使用ORDER BY子句

嵌套查詢的分類與求解方法

• 不相關子查詢

  • 概念：子查詢的查詢條件不依賴于父查詢
  • 由里向外逐層處理。每個子查詢在上一級查詢處理之前求解，子查詢的結果用于建立其父查詢的查找條件。

• 相關子查詢

  • 概念：子查詢的查詢條件依賴于父查詢
  • 先去外層查詢中表的第一個元組，根據它與內層查詢相關的屬性值處理內層查詢，若WHERE子句返回值為真，則取此元組放入結果表；然后再取外層表的下一個元組；重復這一過程，直至外層表全部檢查完為止。

• 【例】找出每個學生所選修課程成績超過該門課成平均成績的課程號。

  SELECT Sno,Cno  -- 外層查詢/父查詢FROM SC x -- x是SC的別名WHERE Grade >= (SELECT AVG(Grade) FROM SC yWHERE y.Cno = x.Cno);
  

  說明：

  1. 該嵌套查詢時相關子查詢，因為子查詢中使用了外層查詢的x.Cno值，即子查詢依賴于父查詢中當前元組的課程編號。

  2. **外層查詢：**從成績表SC（別名為x）中選取每條記錄的Sno（學生編號）和Cno（課程編號）。

  3. **內層子查詢：**針對外層每條記錄的x.Cno，計算該課程的平均成績AVG(Grade)。

  4. **條件比較：**將當前記錄的Grade與該課程的平均成績作比較，如果 當前成績 >= 平均成績，則保留該記錄。

  5. 這里提供一個示例以供理解，假設SC表如下

     Sno	Cno	Grade
     01	C1	90
     02	C1	70
     03	C1	80
     01	C2	85
     02	C2	88

     那么，平均成績：

     • C1的平均成績是(90+70+80)/3 = 80
     • C2 的平均成績是 (85+88)/2 = 86.5

     保留的記錄是：

     • C1中成績 ≥ 80 的有：01,03
     • C2中成績 ≥ 86.5 的有：02

     因此返回的結果表為：

     Sno	Cno
     01	C1
     03	C1
     02	C2

  6. 子查詢中沒有顯示使用 GROUP BY Cno，那它是如何按課程號計算平均成績的。

     實際上確實沒有 GROUP BY，這就是相關子查詢的妙用

     觀察代碼

     SELECT Sno, Cno
     FROM SC x
     WHERE Grade >= (SELECT AVG(Grade)FROM SC yWHERE y.Cno = x.Cno
     );

     雖然子查詢中沒有 GROUP BY，但子查詢里有：

     WHERE y.Cno = x.Cno;
     

     這意味著：

     • 每當外層取到一條記錄（例如x.Sno='01'且x.Cno = 'C1'），
     • 子查詢就會被“重新執行一次”，
     • 這一次子查詢只計算 SC表中課程號等于C1 的所有 Grade 的平均值。

     也就是說，不是全表平均，而是“按當前課程號”去算的平均。

     可以理解為下面這個過程：

     “我拿出SC表的第一行，發現是課程C1，那我現在就去SC表中找所有課程是C1的成績，求平均，回來比較一下當前這行成績是否 ≥ 這個平均值。”

     整個過程依賴外層的x.Cno，這就造成了“按Cno分類”的效果，雖然沒有寫GROUP BY，但邏輯上是“每個Cno分別計算一次”。

     如果真的寫了 AVG(Grade) 而沒有這個 WHERE y.Cno = x.Cno 呢？
     那結果就是計算整張SC表所有成績的平均，不區分課程了，那就錯了。

  7. 總結：子查詢雖然沒有 GROUP BY，但依靠 WHERE y.Cno = x.Cno 實現了“對每門課程分別求平均”的效果，是“相關子查詢”自動按上下文變量進行篩選的結果。

帶有IN謂詞的子查詢

【例】

查詢與“劉晨”在同一個系學習的學生。

• 此查詢要求可以分布來完成

  1. 確定“劉晨”所在系名

     SELECT Sdept FROM StudentWHERE Sname = '劉晨';
     

     結果：

     

  2. 查找所有在IS系學習的學生。

     SELECT Sno,Sname,SdeptFROM StudentWHERE Sdept = 'IS';
     

     結果：

     

• 構造嵌套查詢

  將第一步查詢嵌入到第二步查詢的條件中

  SELECT Sno,Sname,SdeptFROM StudentWHERE Sdept IN(SELECT Sdept FROM StudetnWHERE Sname = '劉晨');
  

• 父查詢和子查詢中的表均可以使用別名

  SELECT Sno,Sname,Sdept FROM Student S1WHERE S1.Sdept IN (SELECT Sdept FROM Student S2WHERE S2.Sname = '劉晨');
  

• 用自身連接完成本查詢要求

  SELECT S1.Sno,S1.Sname,S1.SdeptFROM Student.S1,Student S2WHERE S1.Sdept = S2.Sdept AND S2.Sname = '劉晨';
  

【例】查詢選修了“C語言”課程的學生的學號和姓名

SELECT Sno,SnameFROM StudentWHERE Sno IN(SELECT SnoFROM SC WHERE Cno IN(SELECT Cno FROM CourseWHERE Cname = 'C語言'));

• 或者用連接查詢

  SELECT Student.Sno,Student.SnameFROM Student,SC,CourseWHERE Student.Sno = SC.SnoAND SC.Cno = Course.CnoAND Course.Cname = 'C語言';
  

帶有比較運算符的子查詢

• 當能確切知道內層查詢返回單值時，可用比較運算符（>、<、=、>=、<=、!=或<>）

• 【例】假設一個學生只能在一個系學習，并且必須屬于一個系，則在左例可以用=代替IN。

  則下面兩個SQL語句等價

  SELECT Sno,Sname,SdeptFROM Student S1WHERE S1.Sdept IN(SELECT Sdept S2FROM StudentWHERE S2.Sname = '劉晨');
  

  SELECT Sno,Sname,SdeptFROM Student S1WHERE S1.Sdept =(SELECT Sdept S2FROM StudentWHERE S2.Sname = '劉晨');
  

• 子查詢一定要跟在比較符之后

• 錯誤的例子

  SELECT Sno,Sname,SdeptFROM StudentWHERE (SELECT SdeptFROM StudentWHERE Student = '劉晨') = Sdept;
  

帶有ANY或ALL謂詞的子查詢

• 謂詞語義

  • ANY 任意一個值
  • ALL 所有值

• 配合比較運算符使用

  > ANY 大于子查詢結果中的某個值

  > ALL 大于子查詢結果中的所有值

  < ANY 小于子查詢結果中的某個值

  < ALL 小于子查詢結果中的所有值

  >= ANY 大于等于子查詢結果中的某個值

  >= ALL 大于等于子查詢結果中的所有值

  <= ANY 小于等于子查詢結果中的某個值

  <= ALL 小于等于子查詢結果的所有值

  = ANY 等于子查詢結果的某個值

  = ALL 等于子查詢結果中的所有值（通常沒有實際意義）

  !=(<>) ANY 不等于子查詢結果中的某個值

  !=(<>) ALL 不等于子查詢結果中的任何一個值

• ANY 和 ALL 謂詞有時可以用**聚集函數（聚合函數）**來實現

  • 用聚集函數實現子查詢通常比直接用ANY或ALL查詢效率要高，因為前者通常能夠減少比較次數。

  • ANY與ALL與聚合函數的對應關系如下：

    

【例】查詢其他系中比CS系任意一個（某個）學生年齡小的學生姓名和年齡

• 用ANY謂詞實現

  SELECT Sname,SageFROM StudentWHERE Sage < ANY(SELECT SageFROM StudentWHERE Sdept = 'CS')AND Sdept != 'CS';
  

• 用聚集函數實現

  SELECT Sname,SageFROM StudentWHERE Sage < (SELECT MAX(Sage)FROM StudentWHERE Sdept = 'CS')AND Sdept != 'CS';
  

【例】查詢其他系中比CS系所有學生年齡都小的學生姓名及年齡

• 用ALL謂詞實現

  SELECT Sname,SageFROM StudetnWHERE Sage < ALL(SELECT Sage FROM StudentWHERE Sdept = 'CS')AND Sdept != 'CS';
  

• 用聚集函數實現

  SELECT Sname,SageFROM Student WHERE Sage <(SELECT MIN(Sage)FROM StudentWHERE Sdept = 'CS')AND Sdept != 'CS';
  

帶有EXISTS謂詞的子查詢

1. EXISTS謂詞
2. NOT EXISTS謂詞
3. 不同形式的查詢間的替換
4. 用EXISTS或NOT EXISTS實現全稱量詞
5. 用EXISTS或NOT EXISTS實現邏輯蘊含

1.EXISTS謂詞（存在量詞$?$）

• 帶有EXISTS謂詞的子查詢不返回任何數據，只產生邏輯真值“true”或邏輯假值“false”；
• 若內層查詢結果非空，則返回TRUE；NOT EXISTS相反
• 若內層查詢結果為空，則返回FALSE；NOT EXISTS相反
• 由EXISTS引出的子查詢，其目標列表達式通常用*，因為帶EXISTS的子查詢只返回TRUE或FALSE，給出列名無實際意義。

2.NOT EXISTS謂詞

【例】查詢所有選修1號課程的學生姓名

• 思路分析：在Student中依次取每個元組的Sno值，用此值去檢查SC關系；若SC中存在這樣的元組，即其Sno值等于此Student.Sno值，且Cno = '1'，則取此Student.Sname送入結果關系

  用嵌套查詢

  SELECT SnameFROM StudentWHERE EXISTS(SELECT *FROM SCWHERE Student.Sno = SC.SnoAND SC.Cno = '1');
  

  或用連接運算

  SELECT Sname FROM Student,SCWHERE Student.Sno = SC.Sno AND Cno = '1';
  

【例】查詢沒有選修1號課程的學生姓名

SELECT SnameFROM StudentWHERE NOT EXISTS(SELECT *FROM SCWHERE Student.Sno = SC.SnoAND Cno = '1');

思路分析：從Student表中一條一條地取出學生元組，也就是說，我們對每個學生執行一次NOT EXISTS條件判斷。比如，先從Student表中取出Sno='1' ，然后去SC表中查找這個學生是否選修了課程1號，如果查到記錄，說明他選了，那么返回值為FALSE，不輸出該學生；如果沒查到記錄，說明他沒選，那么返回值為TRUE，輸出該學生。

3. 不同形式的查詢間的替換

• 一些帶有EXISTS或NOT EXISTS謂詞的子查詢不能被其他形式的子查詢等價替換。
• 所有帶IN謂詞、比較運算符、ANY和ALL謂詞的子查詢都能用帶EXISTS謂詞的子查詢等價替換。
• 帶有EXISTS謂詞的相關子查詢只關心內層查詢是否有返回值，不需要查具體值，效率不低于相關子查詢。

【例】查詢與“劉晨”在同一個系學習的學生。可以用帶EXISTS謂詞的子查詢替換

SELECT Sno,Sname,Sdept FROM StudentWHERE Sdept IN(SELECT SdeptFROM StudentWHERE Sname = '劉晨');

等價于

SELECT Sno,Sname,SdeptFROM Student S1WHERE EXISTS(SELECT *FROM Student S2WHERE S1.Sdept = S2.Sdept AND S2.Sname = '劉晨');

4. 用EXISTS/NOT EXISTS實現全稱量詞

• SQL語言中沒有全稱量詞$?$（For all）

• 可以把帶有全稱量詞的謂詞轉換為等價的帶有存在量詞的謂詞：

  $(?x)P\equiv ?(?x(?P))$

  解釋：“所有人都滿意”=“不存在一個人不滿意”

【例】查詢選修了全部課程的學生姓名

SELECT SnameFROM StudentWHERE NOT EXISTS(SELECT *FROM CourseWHERE Cno NOT EXISTS(SELECT * FROM SCWHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno));

說明：

• 外層：Student WHERE NOT EXISTS(……)

  意思是：不存在某些課程……

• 內層：Course WHERE Cno NOT EXISTS

  意思是：該學生沒有選這門課。

  所以合起來就是

  對某個學生來說，不存在一門課，他 沒有選 ——
  換句話說：所有的課，他都選了！

• 思路分析：

  1. 先從Student表中挑選一行元組；

  2. 然后在SC中找出滿足Student.Sno = SC.Sno條件的n行元組（表示該學生選了n門課）；

  3. 然后從Course里面每一行元組（即每一門課）：

     • 每次從Course表中取出一個Cno（課程編號）；
     • 然后在上述n行SC元組中查找是否存在滿足SC.Cno = Course.Cno的記錄：
       • 如果找到，說明該學生選了這門課，則最內層子查詢返回FALSE，在Course（只是過程的一個臨時表）中刪掉 找到的Cno 對應的課程元組。
       • 如果沒找到，說明該學生沒選這門課，則最內層子查詢返回TRUE，在Course不刪掉 找到的Cno 對應的課程元組。

  4. 所有課程都遍歷完后，我們觀察Course表（只是過程的一個臨時表）中被保留下來的記錄：

     • 如果 存在一門課沒被刪掉（即，存在沒選的課），那么外層 NOT EXISTS(...) 為 False → 該學生被排除
     • 如果 所有的課都被刪掉了（即，該學生選了所有課程）那么外層 NOT EXISTS(...) 為 True → 該學生被選中。

  5. 然后再從Student表中挑選下一行元組，從2.繼續開始

• 假設有如下三個表

  1. 學生表（Student）

     Sno	Sname
     S1	張三
     S2	李四
     S3	王五

  2. 課程表（Course）

     Cno	Cname
     C1	數據庫
     C2	算法
     C3	操作系統

  3. 選課表（SC）

     Sno	Cno
     S1	C1
     S1	C2
     S1	C3
     S2	C1
     S2	C2
     S3	C1

  查詢目標：找出那些把全部課程都選了的學生姓名。

  那么按照SQL語句解釋

  • 學生S1（張三）

    1. 從Student表中取出S1對應的一行元組

    2. 在SC表中找到S1的選課記錄：C1、C2、C3（共三條記錄）

    3. 遍歷Course表（臨時）中的課程：

    課程	是否出現在 SC(S3) 中？	最內層 NOT EXISTS	是否從臨時Course中刪掉
    C1	是	False	刪掉
    C2	是	False	刪掉
    C3	是	False	刪掉

    4. 所有課程都被刪掉 $\to$ Course子查詢為空 $\to$ 外層 NOT EXISTS(...) 為 True
    5. 張三被選中，加入最終結果

  • 學生 S2（李四）

    1. 從 Student 表中取出 S2

    2. 在 SC 表中找到 S2 的選課記錄：C1、C2

    3. 遍歷 Course 表（臨時）中的課程：

       課程	是否出現在 SC(S2) 中？	最內層 NOT EXISTS	是否從臨時Course中刪掉
       C1	是	False	刪掉
       C2	是	False	刪掉
       C3	否	True	保留

    4. 還有 C3 沒刪掉 → 有課沒選 → 外層 NOT EXISTS(...) 為 False

    5. 李四被排除

  • 學生 S3（王五）

    1. 從 Student 表中取出 S3

    2. 在 SC 表中找到 S3 的選課記錄：C1

    3. 遍歷 Course 表中的課程：

       課程	是否出現在 SC(S3) 中？	最內層 NOT EXISTS	是否從臨時Course中刪掉
       C1	是	False	刪掉
       C2	否	True	保留
       C3	否	True	保留

    4. 還剩 C2、C3 → 有多門課沒選 → 外層 NOT EXISTS(...) 為 False

    5. 王五被排除

5. 用EXISTS/NOT EXISTS實現邏輯蘊含

• SQL語言中沒有蘊含（Implication）邏輯運算

• 可以利用謂詞演算將邏輯蘊含謂詞等價轉換為：

  ? $p\to q\equiv ?p\vee q$

【例】查詢至少選修了學生95001選修的全部課程的學生的學號。

• 解題思路：

  • 用邏輯蘊含表達：查詢學號為x的學生，對所有的課程y，只要95001學生選修了課程y，則x也選修了y。

  • 形式化表示：

    用$p$表示謂詞 “學生95001選修了課程y”

    用$q$表示謂詞 “學生x選修了課程y”

    則上述查詢： $(?y)p\to q$

  • 等價變化

    $$\begin{gathered} (?y)p\to q\equiv ?(?y)(p\to q) \\ \equiv ?(?(?(?p\vee q)))\equiv ?(?y(p\vee ?q)) \end{gathered}$$

  • 變化后語義：：對于學生x，不存在這樣的課程y，學生95001選修了y，而學生x沒有選。

  解：

  SELECT DISTINCT SnoFROM SCWHERE NOT EXISTS(SELECT *FROM SC SCYWHERE SCY.Sno = '95001'AND NOT EXISTS(SELECT * FROM SC SCZWHERE SCZ.Cno = SCY.CnoAND SCX.Sno = SCZ.Sno));
  

5. 集合查詢

• 標準SQL直接支持的集合操作種類
  • 并操作（UNION）
• 一般商用數據庫支持的集合操作種類
  • 并（UNION）
  • 交（INTERSECT）
  • 差（MINUS，EXCEPT）

并操作

• 語法形式

  <查詢塊>
  UNION [ALL]
  <查詢塊>
  

• 兩個查詢結果的屬性列個數相同，對應項的數據類型必須能通過隱式轉換相互兼容。

• 使用UNION合并多個結果集是，系統會自動去掉重復元組。

• 使用UNION ALL操作符，可以保留重復元組。

【例】查詢計算機系的學生或者年齡不大于19歲的學生。

SELECT *FROM StudentWHERE Sdept = '計算機系'
UNION
SELECT *FROM StudetnWHERE Sage <= 19;

等價于

SELECT DISTINCT *FROM StudentWHERE Sdept = '計算機系'OR Sage <= 19;

【例】設數據庫中有一教師表Teacher(Tno,Tname,……)。查詢學校中所有師生的姓名。

SELECT SnameFROM Student;
UNION
SELECT TnameFROM Teacher;

UNION結果集中的列名與UNION運算中的第一個SELECT語句的 結果集 中的列名相同，其他的SELECT語句的結果集列名將被忽略。所以這個執行語句的結果集中的列名是Sname

交操作

• 標準SQL中沒有提供集合交操作，但可用其他法間接實現。

  【例】查詢計算機系的學生與年齡不大于19歲的學生的交際。

  本例實際上就是查詢計算機系中年齡不大于19歲的學生。

  SELECT DISTINCT *FROM StudentWHERE Sdept = '計算機系'AND Sage <= 19;
  

  等價于

  SELECT *FROM StudentWHERE Sdept = '計算機系'
  INTERSECT
  (SELECT *FROM StudentWHERE Sage <= 19);
  

  【例】查詢選修課程1的學生集合與選修課程2的學生的交集。

  本例實際上是查詢既選修了課程1又選修了課程2的學生。

  SELECT Sno FROM SC WHERE Cno = '1' ANDSno IN (SELECT Sno FROM SC WHERE Cno = '2';
  

  【例】查詢學生姓名與教師姓名的交集。查詢學校中與教師同名的學生姓名。

  SELECT DISTINCT SnameFROM StudentWHERE Sname IN(SELECT TnameFROM Teacher);
  

查操作

• 標準SQL沒有提供集合差操作，但可用其他方法間接實現。

【例】查詢學生姓名與教師姓名的差集。實際上是查詢學校中未與教師同名的學生姓名。

SELECT SnameFROM Student
EXCEPT
SELECT TnameFROM Teacher;

等價于

SELECT DISTINCT Sname FROM StudentWHERE Sname NOT IN(SELECT TnameFROM Teacher);

【例】查詢選修課程1但沒有選修課程2的學生學號

SELECT SnoFROM SCWHERE Cno = '1'AND Sno NOT IN(SELECT SnoFROM SCWHERE Cno = '2'); 

對集合操作結果的排序

• 在執行集合操作是，默認按照最后結果表中第一列數據的升序方式排列記錄了。
• 各SELECT子句不能含有ORDER BY子句，但是可以將ORDER BY子句放在最后的SELECT語句后面，以便對最后的結果表排序。
• ORDER BY子句只能用于對最終查詢結果排序，不能對中間結果排序。
• 任何情況下，ORDER BY子句只能出現在最后。
• 對集合操作結果排序時，ORDER BY子句最好用數據指定排序的列屬性，以免出錯。

錯誤寫法：

SELECT *FROM StudentWHERE Sdept = '計算機系'ORDER BY Sno
UNION
SELECT *FROM StudentWHERE Sage <=19ORDER BY Sno;

正確寫法：

SELECT *FROM StudentWHERE Sdept = '計算機系'
UNION
SELECT *FROM StudentWHERE Sdept <=19
ORDER BY 1 ASC;

集合查詢小結

• 參與集合運算的中間結果集的屬性列個數必須一致，且對應屬性的類型必須兼容；
• 參與運算的屬性名不一定相同；
• 最終結果集采用第一個中間結果集的屬性名；
• 默認自動刪除結果中的重復元組；
• ORDER BY子句要求放在整個子句的最后；
• 標準SQL沒有提供集合交、差操作，但可用其他方法間接實現。

6.基于派生表的查詢

• 子查詢出現在FROM子句中，這是子查詢生成的臨時派生表（Derived Table）成為主查詢的查詢對象

  【例】找出每個學生超過他自己選修課程平均成績的課程號。

  SELECT Sno,CnoFROM SC,(SELECT Sno,AVG(Grade) avg_grade FROM SC GROUP BY Sno) AS Avg_scWHERE SC.Sno = Avg_sc.SnoAND SC.Grade >= Avg_sc.avg_grade;