資料庫系統概論—基礎篇(2) 三、關係資料庫標準語言SQL 1、數據定義 1.1基本表的定義、刪除與修改 定義基本表 #建立學生表 CREATE TABLE Student( Sno CHAR(9) PRIMARY KEY, Sname CHAR(20) UNIQUE, Ssex CHAR(2), ...
資料庫系統概論—基礎篇(2)
三、關係資料庫標準語言SQL
1、數據定義
1.1基本表的定義、刪除與修改
- 定義基本表
#建立學生表
CREATE TABLE Student(
Sno CHAR(9) PRIMARY KEY,
Sname CHAR(20) UNIQUE,
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20)
);
- 修改基本表
ALTER TABLE 表名
ADD 列名 數據;#增
或
DROP 列名 CASCADE/RESTRICT;#刪
或
ALTER 列名 類型 #改
- 刪除基本表
DROP TABLE 表名 CASCADE/RESTRICT;
CASCADE：與其相關的全部刪除，比如視圖，索引等
RESTRICT：如果有依賴關係或是建立索引等，就不刪除
但是不同的數據管理系統的執行不同
1.2索引的建立與刪除
- 建立索引
CREATE UNIQUE/CLUSTER INDEX 索引名
ON 表名 (列名1 順序1(ASC/DESC),列名2 順序2...);
- 刪除索引
DROP INDEX 索引名;
2、數據查詢
- 基本結構
SELECT ALL/DISTINCT 列名/目標表達式
FROM 表名/視圖名
WHERE 條件#選擇的條件
GROUP BY 列名 HAVING 條件#分組後根據條件選擇，分組：可以理解成大表分為好幾個小表
ORDER BY 列名 順序(ASC/DESC);#查詢結構排序
2.1單表查詢
利用上述的基本結構進行查.
註意：
- 字元型要加' '號
- Distinct可以消除重覆的列
- 如果要顯示一列字元，在SELECT後+’ ‘號
- 模糊查詢中%表示任意長度，_表示一個字元
- ESCAPE後+'轉義字元'
- 與空值有關要用IS NULL或是IS NOT NULL
- ORDER BY子查詢不能使用
- WHERE中不能用聚集函數
- CROUP BY和HAVING同時出現
2.2連接查詢
- 等值連接和非等值連接
運算符為 = 稱為等值連接(列名不同)，其餘為非等值連接
取消重覆列則為自然連接
eg:查詢選修2號課程且成績在90及分以上的所有學生的學號和姓名
Select S1.Sno,S1.Sname
From STUDENT S1,SC S2
Where S1.Sno = S2.Sno and S2.Cno = '2'and S2.Grade >= 90;
- 自身連接
兩個相同的表自己連接，註意起別名
- 外連接
#外連接
From STUDENT OUTER SC join on(STUDENT.Sno = SC.Sno);
#左外連接
From STUDENT left OUTER SC join on(STUDENT.Sno = SC.Sno);
#右外連接
From STUDENT right OUTER SC join on(STUDENT.Sno = SC.Sno);
2.3嵌套查詢
分為不相關查詢(父查詢和子查詢無關)和相關查詢(父查詢和子查詢有關)
- 帶IN的子查詢(不相關子查詢)
子查詢結果是一個屬性的集合
eg:查詢與"劉晨"所在同一個系的的學生的學號
Select Sno
Fron STUDENT
Where Sdept in(
Select Sdept
Fron STUDENT
Where Sname = '劉晨')；
- 有比較運算符的子查詢
查詢順序：先在父查詢中選擇第一個個元組的信息，將其傳入子查詢中計算結果，再進行比較
子查詢的結果和父查詢傳入的數據有關
eg:查詢每個學生超過自己選修課平均成績的學號和課程號
Select Sno,Cno
From SC X
Where Grade >= (Select AVG(Grade)
From SC Y
Where Y.Sno = X.Sno);
也可以先建立一個新的表：學號、成績，再查詢這裡就是基於派生表的查詢
Select Sno,Cno
From SC,(Select Sno AVG_Sno,AVG(Crade) AVG_Grade
From SC
GROUP BY Sno) AS AVG_SG
Where SC.Sno = AVG_SG.AVG_Sno AND SC.Crade >=AVG_SG.AVG_Grade;
2.4帶有EXISTS的查詢
EXISTS代表存在量詞，其子查詢只返回“true”或是“false”
將父查詢中的每一條記錄代入到子查詢中去試,要有一個連接的條件像，STUDENT.Sno = SC.Sno
- EXISTS
將父查詢中的每一條記錄代入到子查詢中去試，滿足則輸出，不滿足則查詢下一條
使用EXISTS時子查詢的Select後面的目標列使用*
eg：查詢選擇1號課程的學生
Select Sno
From STUDENT
Where EXISTE
(Select *
From SC
Where STUDENT.Sno = SC.Sno AND Cno = '1');
- NOT EXISTS
將父查詢中的每一條記錄代入到子查詢中去試，滿足則查詢下一條；不滿足則輸出
eg:查詢沒用選擇1號課程的學生
Select Sno
From STUDENT
Where NOT EXISTE
(Select *
From SC
Where STUDENT.Sno = SC.Sno AND Cno = '1');
- 不同形式查詢的替換
並不是所有帶有EXISTS的子查詢都可以替換；帶有IN,比較運算符，ANY和ALL的子查詢可以被帶有EXISTS的子查詢替換
- 代替全稱量詞
eg:查詢選修了全部課程的學生姓名
#等價於沒有一門課程是他不選的
Select Name From STUDENT1
Where NOT EXISTS
(Select * From COURSE
Where NOT EXISTS
(Select * From SC
Where STUDENT1.Sno = SC.Sno and COURSE.Cno = SC.Cno));
相當於兩個for迴圈，從學生表中取出第一個數據，然後遍歷課程表並於SC表進行比較，若都可以對應上則經過兩次取反後輸出，否則從學生表中選取下一條記錄
- 實現邏輯蘊含
Select DISTINCT Sno
From SC S1
Where NOT EXISTS
(Select * From SC S2
Where S2.Sno = '201215122' AND NOT EXISTS
(Select * From SC S3
Where S3.Sno = S1.Sno and S2.Cno = S3.Cno));
2.5集合查詢
-
交:INTERSECT
-
並:UN1ON
-
差:EXCEPT
3.數據更新
3.1插入數據
- 插入元組
INSERT INTO 表名 (列名1，列名2...)
VALUES (值1，值2...);
- 插入子查詢結果
將子查詢結果插入指定表中
INSERT INTO 表名 (列名1，列名2...)
子查詢;
3.2修改數據
修改滿足Where條件的元組
UPDATE 表名
SET (列名1 = 值1/表達式1,列名2 = 值2/表達式2...)
Where...;
- 帶有子查詢的修改語句
UPDATE 表名
SET (列名1 = 值1/表達式1,列名2 = 值2/表達式2...)
Where 條件 關係 子查詢;
3.3刪除數據
刪除滿足Where條件的元組，若無篩選條件，則刪除全部數據,表還在
DELETE FROM 表名
Where 條件;
4.空值的處理
NULL：當前不知具體值；不該有值；不便填寫。不清楚，不知道
註：
- 有NOT NULL / 加了UNIQUE / 碼屬性不能為空值
- NULL的算術運算結果為NULL
- NULL的比較運算結果為UNKNOWN
5.視圖
- 是一個虛表
- 資料庫中只存放視圖的定義，而不存放視圖對應的數據
- 基本表中數據變化，視圖中數據也隨之變化
5.1定義視圖
CREAT VIEW 視圖名(列名1，列名二...)
AS 子查詢
WITH CHECK OPTION;#對於視圖的操作都有上述子查詢的Where約束
註：視圖後的列名要不不寫，要不全寫
創建的結果是把視圖的定義存在數據字典，不執行SELECT語句
基於一個表的視圖叫做行列子集視圖
5.2刪除視圖
DROP VIEW 視圖名;
#CASCADE：強制刪除，可選
5.3查詢視圖
和上面的數據查詢差不多，將表名換成視圖名即可
5.4更新視圖
和上述的數據更新差不多...
不是所有的視圖都可以更新，行列子集視圖可以更新，其他視情況而定
5.5視圖作用
-
視圖能簡化用戶操作
-
視圖使用戶能以多種角度看待同同一數據
-
視圖對重構資料庫提供了一定程度的邏輯獨立性
-
視圖能夠對機密數據提供安全保護
-
適當利用視圖可以更清晰地表達查詢
