資料庫系統概論—基礎篇(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視圖作用
-
視圖能簡化用戶操作
-
視圖使用戶能以多種角度看待同同一資料
-
視圖對重構資料庫提供了一定程度的邏輯獨立性
-
視圖能夠對機密資料提供安全保護
-
適當利用視圖可以更清晰地表達查詢
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/551130.html
標籤：MySQL
上一篇：技術干貨｜如何利用 ChunJun 實作資料實時同步？
下一篇：返回列表