資料庫原理概論Ⅱ-SQL
結構化查詢語言（Structured Query Language）是關系資料庫的一種通用語言。
SQL是在關系代數更進階的抽象，可以用比較類似英文的文法實作我們要查詢的資料等等，是以一條SQL可以翻成不同的關系代數，而功能是相同的，這也就造成了不同SQL的寫法效率相差很多。
在本文中我們并不考慮SQL效率的問題，而隻總結常用的SQL的用法。
借用書上的例子來講解，首先先定義一個學生-課程模式，包含以下三個表
- 學生表：Student(Sno, Sname, Ssex, Sage, Sdept)
- 課程表：Course(Cno, Cname, Cpno, Ccredit)
- 學生選課表：SC(Sno, Cno, Grade)
Student:
| 學号Sno | 姓名Sname | 性别Ssex | 年齡Sage | 所在系Sdept |
|---|---|---|---|---|
| 201215121 | 李勇 | 男 | 20 | CS |
| 201215122 | 劉晨 | 女 | 19 | CS |
| 201215123 | 王敏 | 女 | 18 | MA |
| 201215125 | 張立 | 男 | 19 | IS |
Course:
| 課程号Cno | 課程名Cname | 先修課Cpno | 學分Ccredit |
|---|---|---|---|
| 1 | 資料庫 | 5 | 4 |
| 2 | 數學 | 2 | |
| 3 | 資訊系統 | 1 | 4 |
| 4 | 作業系統 | 6 | 3 |
| 5 | 資料結構 | 7 | 4 |
| 6 | 資料處理 | 2 | |
| 7 | PASCAL語言 | 6 | 4 |
SC:
| 學号Sno | 課程号Cno | 成績Grade |
|---|---|---|
| 20125121 | 1 | 92 |
| 20125121 | 2 | 85 |
| 20125121 | 3 | 88 |
| 20125122 | 2 | 90 |
| 20125122 | 3 | 80 |
一、資料定義
1.1 模式的定義和删除
一個資料庫可以建立多個模式，一個模式下通常包括多個表、視圖和索引等資料庫對象
定義一個模式實際上是定義了一個命名空間，在該命名空間下可以定義表，視圖等
1.定義模式
CREATE SCHEMA <模式名> AUTHORIZATION <使用者名>；
例 ：為使用者ZHANG建立一個模式TEST，并在其中定義一個表TAB1
CREATE SCHEMA TEST AUTHORIZATION ZHANG
CREATE TABLE TAB1( COL1 SMALLINT,
COL2 INT,
COL3 CHAR(20),
)；
2.删除模式
DROP SCHEMA <模式名> <CASADE | RESTRICT>；
選擇級聯（CASADE）在删除模式的同時删除該模式下所有的表、視圖等
選擇限制（RESTRICT）隻有在模式下沒有任何對象時才可以删除
例：删除模式TEST，并且同時删除定義的表TAB1
DROP SCHEMA ZHANG CASCADE；
1.2 基本表的定義、删除與修改
1.定義基本表
CREATE TABLE <表明> ( <列名> <資料類型> [列級完整性限制條件]
[, <列名><資料類型>[列級完整性限制條件]]
...
[,<表級完整性限制條件>])；
例：定義學生選課表SC，并且設定列級、表級完整性限制
CREATE TABLE SC
(Sno CHAR(9),
Cno CHAR(4),
Grade SMALLINT,
PRIMARY KEY (Sno, Cno), /*主碼由兩個屬性構成， 表級限制*/
FOREIGN KEY(Sno) REFERENCES Stuent(Sno),/*Sno是外碼， 被參照表是Student中的Sno屬性*/
FOREIGN KEY(Cno) REFERENCES Course(Cno),
);
2.修改基本表
ALTER TABLE <表明>
[ADD [COLUMN] <新列名> <資料類型>[完整性限制]] // 添加新的列
[ADD <表級完整性限制>] // 添加新的限制
[DROP [COLUMN] <列名> [CASCADE|RESTRICT]] // 删除列
[DROP CONSTRAINT <完整性限制名> [RESTRICT | CASCADE]] // 删除限制
[ALTER COLUMN <列名><資料類型>]； // 修改表的定義
3.删除基本表
DROP TABLE <表名> [RESTRICT | CASCADE]
RESTRICT删除的基本表不能被其他表删除
CASCADE删除的表會将相關的視圖等一并删除
1.3 索引的建立和删除
當表的資料量比較大的時候，查詢會比較耗時，這時建立相應的索引會加快查詢速度。
資料庫索引包括順序檔案的索引，B+樹索引、散列索引、位圖索引。
順序檔案索引:按照指定屬性值升序或者降序，在該屬性上建立順序索引。
B+樹索引：将屬性組織成B+樹的形式，B+樹的葉節點為屬性值和相應的指針
散列索引：建立若幹個桶，将索引屬性按照散列函數值映射到相應的桶中。桶中包括屬性值和指針。
位圖索引：用位向量記錄索引屬性中可能出現的值。
使用者不能顯示地選擇索引形式，因為索引結構屬于内模式。
1.建立索引
CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名> (<列名> [<次序>] ...)
<表名> ：要建立索引基本表的名字，索引可以建立在表的多列上
<次序>： ASC升序 或 DESC降序
例：為前面的表按學号和課程号升序建立唯一索引
CREATE UNIQUE INDEX SCno On SC(Sno ASC, Cno DESC);
2.修改索引
ALTER INDEX <舊索引名> RENAME TO <新索引名>
3.删除索引
DROP INDEX <索引名>
二、資料查詢
資料查詢是資料庫的核心操作，基本形式如下：
SELECT [ALL | DISTINCT] <目标清單達式> [, <目标清單達式>] ...
FROM <表名> [, <表名>...] | (<SELECT語句>) [AS] <别名>
[WHERE <條件表達式>]
[GROUP BY <列名1> [HAVING <條件表達式>]]
[ORDER BY <列名2> [ASC | DESC]];
GROUP BY : 結果按<列名1>的值進行分組，屬性相同的元組為一個組，HAVING指定條件的組才輸出。
ORDER BY：結果按<列名2>進行升序和降序的排列。
2.1 單表查詢
1.查詢全體學生的姓名、出生年份、所在院系，要求用小寫字母表示系名
可以在屬性後面跟上别名，使新的關系有新的名字
SELECT Sname NAME, 'Year of Birth:' BIRTH, 2014-Sage BIRTHADAY, LOWER(Sdept) DEPARTMENT FROM Student;
| NAME | BIRTH: | BIRTHADAY | DEPARTMENT |
|---|---|---|---|
| 李勇 | Year of Birth | 1994 | cs |
| 劉晨 | Year of Birth | 1995 | cs |
| 王敏 | Year of Birth | 1996 | ma |
| 張立 | Year of Birth | 1995 | is |
2.查詢考試成績不及格的學生的學号
使用WHERE進行條件限定
使用DISTINCT去除重複的資料
SELECT DISTINCT Sno FROM SC WHERE Grade < 60;
3.查詢CS、MA、IS系的學生
使用IN查詢指定集合的元組
SELECT Sname, Ssex FROM Student WHERE Sdept IN ('CS','MA','IS');
4.字元比對查詢：查詢DB_Design課程的課程号和學分
_ 代表任意單個字元
% 代表任意長度的字元串
ESCAPE ‘’ 表示 \ 是轉義字元
SELECT Cno, Ccredit FROM Course WHERE Cname LIKE 'DB\_Design' ESCAPE '\';
5.查詢選修3号課程的學生的學号及其成績，查詢結果按分數的降序排列
ORDER BY <屬性>:按照該屬性升序、降序排列
SELECT Sno, Gade FROM SC WHERE Cno='3' ORDER BY Grade DESC;
6.聚集函數
- COUNT(*)：統計元組個數
- COUNT([DISTING|ALL] <列名>)：統計一列中 值得個數
- SUM([DISTING|ALL] <列名>)： 求該列的總和
- AVG([DISTING|ALL] <列名>) : 求該列的平均值
- MAX([DISTING|ALL] <列名>): 求該列的最大值
- MIN([DISTING|ALL] <列名>)：求該列的最小值
注意：聚集函數隻能作用于SELECT子句和GROUP BY的HAVING子句
查詢學生201215012選修課程的總學分數
SELECT SUM (Ccredit) FROM SC,Course WHERE Sno = '201215012' AND SC.Cno = Course.Cno;
7.GROUP BY 子句
GROUP BY 子句将查詢結果按某一列分組，值相等的分為一組。
分組的目的是為了細化聚集函數的作用範圍。分組後聚集函數的作用于每一個分組，即每一組有一個函數值。
例：求各個課程号以及對應的選課人數
SELECT Cno, COUNT(Sno)
FROM SC
GROUP BY Cno;
先對Cno分組，相同Cno的為一組，然後對每一組中進行COUNT，算出每一組中的人數
例：查詢選修了三門以上課程的學号
SELECT Sno
FROM SC
GROUP BY Sno
HAVING COUNT(*) > 3;
首先先對Snoopy分組，
然後使用HAVING 對每個分組内的選課數目大于3的進行篩選。
注意：WHERE子句的作用範圍是基本表，HAVING作用于組，從中選擇滿足條件的組
例：查詢平均成績大于等于90分的學生學号和平均成績
SELECT Sno, AVG(Grade)
FROM SC
GROUP BY(Sno)
HAVING AVG(Grade) >= 90;
注意：WHERE子句不能以聚集函數作為條件，是以不能寫為WHERE AVG(Grade>=90)
2.2 連接配接查詢
1.等值與非等值的連接配接查詢
當查詢同時涉及兩個以上的表稱為連接配接查詢。連接配接查詢使用WHERE子句連接配接連個表的條件。
例：查詢每個學生以及選課情況
分析： 學生情況存放在Student表中，學生情況表放在SC表中，二者的連接配接通過公共屬性Sno連接配接
SELECT Student.* SC.*
FROM Student, SC
WHERE Student.Sno = SC.Sno;
連接配接之後的表如下
| Student.Sno | Sname | Ssex | Sage | Sdept | SC.Sno | Cno | Grade |
|---|---|---|---|---|---|---|---|
| 201215121 | 李勇 | 男 | 20 | CS | 201215121 | 1 | 92 |
| 201215121 | 李勇 | 男 | 20 | CS | 201215121 | 2 | 85 |
| 201215121 | 李勇 | 男 | 20 | CS | 201215121 | 3 | 88 |
| 201215122 | 劉晨 | 女 | 19 | CS | 201215122 | 2 | 90 |
| 201215122 | 劉晨 | 女 | 19 | CS | 201215122 | 3 | 80 |
2.外連接配接
在上一個連接配接中，隻有滿足條件的元組才能輸出，是以沒有選課的學生的資訊就被丢棄了，是以要保留NULL的資料就要采用上一章講的關系代數中的外連接配接，外連接配接使用OUTER JOIN，分為左外連接配接和右外連接配接。
例：顯示所有學生的選課資訊，包括沒有選課的學生
SELECT Student.* SC.*
FROM Student
LEFT OUTER JOIN SC ON (Student.Sno = SC.Sno);
連接配接之後的表如下
| Student.Sno | Sname | Ssex | Sage | Sdept | SC.Sno | Cno | Grade |
|---|---|---|---|---|---|---|---|
| 201215121 | 李勇 | 男 | 20 | CS | 201215121 | 1 | 92 |
| 201215121 | 李勇 | 男 | 20 | CS | 201215121 | 2 | 85 |
| 201215121 | 李勇 | 男 | 20 | CS | 201215121 | 3 | 88 |
| 201215122 | 劉晨 | 女 | 19 | CS | 201215122 | 2 | 90 |
| 201215122 | 劉晨 | 女 | 19 | CS | 201215122 | 3 | 80 |
| 201215123 | 王敏 | 女 | 18 | MA | 201215122 | NULL | NULL |
| 201215125 | 張立 | 男 | 19 | IS | 201215122 | NULL | NULL |
3.多表連接配接
例：查詢每個學生的學号、姓名、選修的課程名及成績。
多個表連接配接使用AND
SELECT Student.Sno, Sname, Cname, Grade
FROM Student, SC, Course
WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno;
2.3 嵌套查詢
1.帶有IN謂詞的子查詢
例：查詢與“劉晨”在同一個系的學生
首先先查詢劉晨在哪個系(内層查詢)，然後再查詢在該系的學生(外層查詢)，将二者嵌套起來。
使用IN進行集合的查詢
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept IN
(SELECT Sdept
FROM Student
WHERE Sname = '劉晨');
這類查詢子查詢跟外層查詢沒有關系，稱為不相關子查詢。
2.帶有比較運算符的子查詢
例：查詢每個學生超過自己選修課程平均成績的課程号
SELECT Sno,Cno
FROM SC x
WHERE Grade >= (
SELECT AVG(Grade)
FROM SC y
WHERE y.Sno = x.Sno);
這種内層查詢依賴于外層的結果的查詢叫做相關子查詢
3.帶有ANY(SOME)或ALL謂詞的子查詢
例：查詢非計算機系中比計算機系中任意一個學生年齡小的學生姓名和年齡
SELECT Sname, Sage
FROM Student
WHERE Sage > ANY(SELECT Sage
FROM Student
WHERE Sdept = 'CS')
AND Sdept <> 'CS';
4.帶有EXISTS謂詞的子查詢
EXISTS表示存在，帶有EXISTS的子查詢不傳回任何資料，隻産生邏輯true或者false
例：查詢所有選擇了1号課程的學生姓名
SELECT Sname
FROM Student
WHERE EXISTS
(SELECT *
FROM SC
WHERE Sno = Student.Sno AND Cno='1');
使用EXISTS後，若内層查詢結果非空，外層的WHERE子句傳回真值
EXIXTS之後的子查詢一般SELECT後都用*代替，因為沒有傳回值，隻有true 或者false
查詢過程：首先先取外層查詢Student中的Sno，如果與内層的Sno一樣，且内層Cn=1，傳回true，外層輸出Sname。
三、資料更新
3.1插入資料
1.插入元組
INSERT INTO <表名> [(<屬性列1> [,<屬性列2>]...)] VALUES(<常量> [,<常量2>...]);
例：插入一條選課記錄
INSERT INTO S(Sno,Cno) VLALUES('201215128','1',NULL);
2.插入子查詢結果
SELECT語句嵌套在INSERT中可以批量插入資料
例：建立一個新表，存在系名和學生平均成績，然後将每個系的資料插入。
CREATE TABLE Dept_age(Sdept CHAR(15) Avg_age SMALLINT);
INSERT INTO Dept_age(Sdept, Avg_age)
SELECT Sdept, AVG(Sage)
FROM Student
GROUP BY Sdept;
3.2 修改操作
UPDATE <表名> SET <列名> = <表達式> [ <列名> = <表達式> ]...[WHERE <條件>];
例：将計算機專業全體同學的成績置零
UPDATE SC
SET GRADE = 0
WHERE Sno IN
(SELETE Sno
FROM Student
WHERE Sdept = 'CS')；
3.3 删除操作
DELETE FROM <表名> [WHERE <條件>]；
例：删除計算機專業所有學生的選課記錄
DELETE
FROM SC
WHERE Sno IN
(SELETE Sno
FROM Student
WHERE Sdept = 'CS');
四、視圖
視圖是資料庫中表的一個視窗，本身并不是真實存在于資料庫中的表，而是提供給使用者看的一個虛表，其本身并不存放資料，資料本身仍然存在于原來的表中。
3.1 定義視圖
CREATE VIEW <視圖名> [(<列名> [,<列名>]...)]
AS <子查詢>
[WITH CHECK OPTION]
子查詢可以是任意的SELECT
WITH CHECK OPTION 表示對視圖進行UPDATE INSERT DELETE 操作要滿足的條件
例：建立資訊系選修了1号課程的學生的視圖（包括學号、姓名、成績）
CRATE VIEW IS_S1(Sno,Sname,Grade)
AS
SELECT Student.Sno,Sname,Grade
FROM Student,SC
WHERE Sdept = 'IS' AND Student.Sno = SC.Sno AND SC.Sno = 1;
3.2 删除視圖
DROP VIEW <視圖名> [CASCADE]；
3.3 查詢視圖
例：在資訊系學生的視圖中查找小于20歲的學生
SELECT Sno.Sage
FROM IS_Student
WHERE Sage < 20;
由于視圖并不儲存資料，在進行查詢的時候，取出視圖的定義，把視圖中的子查詢和本次查詢的條件結合起來，再對基本表進行查詢。也叫作視圖消解(view resolution)
是以最終的查詢語句是下面這樣:
SELECT Sno,Sage
FROM Student
WHERE Sdept = 'IS' AND Sage < 20;
在視圖中，我們一般不做更新和删除，是以在此不再叙述
![oracle中的start with connect by用法[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)