文章目錄

• • 0.前言
  • 1.思維導(dǎo)圖
  • 2.基本表更新---TABLE
  • • （1）插入數(shù)據(jù)---INSERT INTO
    • • ① 插入元組
      • ② 插入子查詢結(jié)果
    • （2）修改數(shù)據(jù)---UPDATE SET
    • • ① 修改某一個(gè)元組的值
    • （3）刪除數(shù)據(jù) ---DELETE
    • • ① 刪除某一個(gè)元組的值
      • ② 刪除多個(gè)元組的值
      • ③ 帶子查詢的刪除語句
  • 3.視圖---VIEW
  • • （1）定義視圖
    • • ① 建立視圖
      • ② 刪除視圖
    • （2）查詢視圖
    • （3）更新視圖
    • • ① 更新數(shù)據(jù)---UPDATE SET
      • ② 插入數(shù)據(jù)---INSERT INTO
      • ③ 刪除數(shù)據(jù)---DELETE
      • ④ 更新視圖的限制

---

0.前言

• 數(shù)據(jù)庫是SQL Sever
• 數(shù)據(jù)庫管理系統(tǒng)是Navicat Premium 15
• 本片文章所用的表在此篇文章之中：Student/SC/Course表

1.思維導(dǎo)圖

2.基本表更新—TABLE

（1）插入數(shù)據(jù)—INSERT INTO

兩種插入數(shù)據(jù)方式

插入元組

插入子查詢結(jié)果

• 可以一次插入多個(gè)元組

① 插入元組

語句格式

INSERTINTO <表名> [(<屬性列1>[，<屬性列2 >…)]VALUES (<常量1> [，<常量2>] … )

功能:將新元組插入指定表中

INTO子句

• 屬性列的順序可與表定義中的順序不一致
• 沒有指定屬性列
• 指定部分屬性列

VALUES子句

• 提供的值必須與INTO子句匹配
• 值的個(gè)數(shù)
• 值的類型

［例1］ 將一個(gè)新學(xué)生元組（學(xué)號(hào)：200215128；姓名：陳冬；性別：男；所在系：IS；年齡：18歲）插入到Student表中。

INSERTINTO Student (Sno，Sname，Ssex，Sdept，Sage)VALUES ('200215128'，'陳冬'，'男'，'IS'，18)；

［例2］ 將學(xué)生張成民的信息插入到Student表中。

INSERT INTO Student VALUES ('200215126', '張成民', '男',18,'CS');

［例3］ 插入一條選課記錄( ‘200215128’，'1 ')。

INSERT INTO SC(Sno,Cno)//RDBMS將在新插入記錄的Grade列上自動(dòng)地賦空值 VALUES ('200215128','1'); //等價(jià) INSERT INTO SC VALUES ('200215128','1',NULL);

② 插入子查詢結(jié)果

語句格式

INSERT INTO <表名> [(<屬性列1> [，<屬性列2>… )]子查詢；

功能
將子查詢結(jié)果插入指定表中

INTO子句(與插入元組類似)

子查詢

• SELECT子句目標(biāo)列必須與INTO子句匹配
  值的個(gè)數(shù)
  值的類型

[例4] 對每一個(gè)系，求學(xué)生的平均年齡，并把結(jié)果存入數(shù)據(jù)庫。

第一步：建表

CREATE TABLE Dept_age( Sdept CHAR(15), /* 系名*/ Avg_age SMALLINT /*學(xué)生平均年齡*/ );

第二步：插入數(shù)據(jù)

INSERT INTO Dept_age(Sdept,Avg_age) SELECT Sdept,AVG(Sage) FROM Student GROUP BY Sdept;

RDBMS在執(zhí)行插入語句時(shí)會(huì)檢查所插元組是否破壞表上已定義的完整性規(guī)則

• 實(shí)體完整性
• 參照完整性
• 用戶定義的完整性
• NOT NULL約束
• UNIQUE約束
• 值域約束

（2）修改數(shù)據(jù)—UPDATE SET

語句格式

UPDATE <表名> SET <列名>=<表達(dá)式>[，<列名>=<表達(dá)式>]… [WHERE <條件>];

功能
修改指定表中滿足WHERE子句條件的元組

SET子句

• 指定修改方式
• 要修改的列
• 修改后取值

WHERE子句

• 指定要修改的元組
• 缺省表示要修改表中的所有元組

三種修改方式

修改某一個(gè)元組的值

修改多個(gè)元組的值

帶子查詢的修改語句

① 修改某一個(gè)元組的值

[例5] 將學(xué)生201215121的年齡改為22歲

UPDATE Student SET Sage=22 WHERE Sno='201215121';

[例6] 將所有學(xué)生的年齡增加1歲

UPDATE Student SET Sage= Sage+1;

[例7] 將計(jì)算機(jī)科學(xué)系全體學(xué)生的成績置零。

UPDATE SC SET Grade=0 WHERE 'CS'=( SELECT SdeptFROM StudentWHERE Student.Sno = SC.Sno);

RDBMS在執(zhí)行修改語句時(shí)會(huì)檢查修改操作是否破壞表上已定義的完整性規(guī)則

• 實(shí)體完整性
• 主碼不允許修改
• 用戶定義的完整性
• NOT NULL約束
• UNIQUE約束
• 值域約束

（3）刪除數(shù)據(jù) —DELETE

三種刪除方式

刪除某一個(gè)元組的值

刪除多個(gè)元組的值

帶子查詢的刪除語句

① 刪除某一個(gè)元組的值

DELETE FROM Student WHERE Sno= '200215128';

② 刪除多個(gè)元組的值

[例9] 刪除所有的學(xué)生選課記錄。

DELETEFROM SC；//這個(gè)我就不截圖了

③ 帶子查詢的刪除語句

[例10] 刪除計(jì)算機(jī)科學(xué)系所有學(xué)生的選課記錄。

DELETE FROM SC WHERE 'CS'=(SELECT SdeptFROM StudentWHERE Student.Sno=SC.Sno);

3.視圖—VIEW

視圖的作用：

視圖能夠簡化用戶的操作

視圖使用戶能以多種角度看待同一數(shù)據(jù)

視圖對重構(gòu)數(shù)據(jù)庫提供了一定程度的邏輯獨(dú)立性

視圖能夠?qū)C(jī)密數(shù)據(jù)提供安全保護(hù)

適當(dāng)?shù)睦靡晥D可以更清晰的表達(dá)查詢

視圖的特點(diǎn)

• 虛表，是從一個(gè)或幾個(gè)基本表（或視圖）導(dǎo)出的表
• 只存放視圖的定義，不存放視圖對應(yīng)的數(shù)據(jù)
• 基表中的數(shù)據(jù)發(fā)生變化，從視圖中查詢出的數(shù)據(jù)也隨之改變

基于視圖的操作

• 查詢
• 刪除
• 受限更新
• 定義基于該視圖的新視圖

視圖的UPDATE、DELETE、INSERT INTO(有受限)，與基本表同步。

（1）定義視圖

① 建立視圖

• 語句格式

CREATE VIEW <視圖名> [(<列名> [，<列名>]…)] AS <子查詢> [WITH CHECK OPTION]；

• 組成視圖的屬性列名：全部省略或全部指定

• 子查詢不允許含有ORDER BY子句和DISTINCT短語

• RDBMS執(zhí)行CREATE VIEW語句時(shí)只是把視圖定義存入數(shù)據(jù)字典，并不執(zhí)行其中的SELECT語句。

• 在對視圖查詢時(shí)，按視圖的定義從基本表中將數(shù)據(jù)查出。

[例1] 建立信息系IS學(xué)生的視圖。

CREATE VIEW IS_Student AS SELECT Sno,Sname,Sage FROM Student WHERE Sdept= 'IS';

[例2]建立信息系學(xué)生的視圖，并要求進(jìn)行修改和插入操作時(shí)仍需保證該視圖只有信息系IS的學(xué)生 。
對IS_Student視圖的更新操作：

• 修改操作：自動(dòng)加上Sdept= 'IS’的條件
• 刪除操作：自動(dòng)加上Sdept= 'IS’的條件
• 插入操作：自動(dòng)檢查Sdept屬性值是否為’IS’
  • 如果不是，則拒絕該插入操作
  • 如果沒有提供Sdept屬性值，則自動(dòng)定義Sdept為’IS’

CREATE VIEW IS_Student AS SELECT Sno,Sname,Sage FROM Student WHERE Sdept= 'IS' WITH CHECK OPTION;

基于多個(gè)基表的視圖

[例3] 建立信息系選修了1號(hào)課程的學(xué)生視圖。

CREATE 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.Cno= '1';

基于視圖的視圖

[例4] 建立信息系選修了1號(hào)課程且成績在90分以上的學(xué)生的視圖。

CREATE VIEW IS_S2 AS SELECT Sno,Sname,Grade FROM IS_S1 WHERE Grade>=90;

帶表達(dá)式的視圖

[例5] 定義一個(gè)反映學(xué)生出生年份的視圖。

CREATE VIEW BT_S(Sno,Sname,Sbirth) AS SELECT Sno,Sname,2000-Sage FROM Student;

分組視圖

[例6] 將學(xué)生的學(xué)號(hào)及他的平均成績定義為一個(gè)視圖

CREATE VIEW S_G(Sno,Gavg) AS SELECT Sno,AVG(Grade) FROM SC GROUP BY Sno;

不指定屬性列
[例7]將Student表中所有女生記錄定義為一個(gè)視圖

CREATE VIEW F_Student(F_Sno,name,sex,age,dept) AS SELECT * FROM Student WHERE Ssex='女';

缺點(diǎn)：

• 修改基表Student的結(jié)構(gòu)后，Student表與F_Student視圖的映象關(guān)系被破壞，導(dǎo)致該視圖不能正確工作。

② 刪除視圖

語句的格式:

DROP VIEW <視圖名>;

• 該語句從數(shù)據(jù)字典中刪除指定的視圖定義
• 如果該視圖上還導(dǎo)出了其他視圖，使用CASCADE級(jí)聯(lián)刪除語句，把該視圖和由它導(dǎo)出的所有視圖一起刪除
• 刪除基表時(shí)，由該基表導(dǎo)出的所有視圖定義都必須顯式地使用DROP VIEW語句刪除

［例8］ 刪除視圖IS_S1

DROP VIEW IS_S1;

• 關(guān)于級(jí)聯(lián)刪除CASCADE，不知道為什么，刪除模式、刪除表、刪除視圖，有約束卻刪除不了，有待解決，先放這里。

（2）查詢視圖

用戶角度：查詢視圖與查詢基本表相同

RDBMS實(shí)現(xiàn)視圖查詢的方法

• 視圖消解法（View Resolution）
  • 進(jìn)行有效性檢查
  • 轉(zhuǎn)換成等價(jià)的對基本表的查詢
  • 執(zhí)行修正后的查詢

關(guān)系數(shù)據(jù)庫管理系統(tǒng)執(zhí)行對視圖的查詢時(shí)，首先進(jìn)行有效性檢查，檢查查詢中涉及的表、視圖等是否存在。如果存在，則從數(shù)據(jù)字典中取出視圖的定義，把定義中的子查詢和用戶的查詢結(jié)合起來，轉(zhuǎn)換成等價(jià)的對基本表的查詢，然后再執(zhí)行修正了的查詢。這一轉(zhuǎn)換過程稱為視圖消解(view resolution)。

[例9] 在信息系學(xué)生的視圖中找出年齡小于等于20歲的學(xué)生。

SELECT Sno,Sage FROM IS_Student WHERE Sage<=20;

IS_Student視圖的定義 (參見視圖定義例1)


視圖消解轉(zhuǎn)換后的查詢語句為：

SELECT Sno，Sage FROM StudentWHERE Sdept= 'IS' AND Sage<=20；

[例10] 查詢選修了1號(hào)課程的信息系學(xué)生

SELECT IS_Student.Sno,Sname FROM IS_Student,SC WHERE IS_Student.Sno =SC.Sno AND SC.Cno= '1';

視圖消解法的局限

• 有些情況下，視圖消解法不能生成正確查詢。

[例11]在S_G視圖中查詢平均成績在90分以上的學(xué)生學(xué)號(hào)和平均成績

SELECT * FROM S_G WHERE Gavg>=90;

S_G視圖的子查詢定義：

CREATE VIEW S_G (Sno,Gavg) AS SELECT Sno,AVG(Grade) FROM SC GROUP BY Sno;

錯(cuò)誤： 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;

（3）更新視圖

① 更新數(shù)據(jù)—UPDATE SET

[例12] 將信息系學(xué)生視圖IS_Student中學(xué)號(hào)201215125的學(xué)生姓名改為“劉辰”。

UPDATE IS_Student SET Sname= '劉辰' WHERE Sno= '201215125'; 轉(zhuǎn)換后的語句： //基本表和視圖表同步更新 UPDATE Student SET Sname= '劉辰' WHERE Sno= '201215125' AND Sdept= 'IS';

② 插入數(shù)據(jù)—INSERT INTO

[例13] 向信息系學(xué)生視圖IS_Student中插入一個(gè)新的學(xué)生記錄：200215111，趙新，20歲

視圖IS_Student的定義 CREATE VIEW IS_Student AS SELECT Sno,Sname,Sage FROM Student WHERE Sdept= 'IS'; //如果加了WITH CHECK OPTION，則視圖不能進(jìn)行插入數(shù)據(jù)操作 CREATE VIEW IS_Student AS SELECT Sno,Sname,Sage FROM Student WHERE Sdept= 'IS' WITH CHECK OPTION;//插入數(shù)據(jù) INSERT INTO IS_Student VALUES('200215111','趙新',20); //插入后基本表Student字段Sdept為空，視圖表IS_Student無數(shù)據(jù) 轉(zhuǎn)換為對基本表的更新： INSERT INTO Student(Sno,Sname,Sage,Sdept) VALUES('200215129','趙新2',20,'IS');

③ 刪除數(shù)據(jù)—DELETE

[例14]刪除信息系學(xué)生視圖IS_Student中學(xué)號(hào)為200215129的記錄

DELETE FROM IS_Student WHERE Sno= '200215129'; 轉(zhuǎn)換為對基本表的更新： DELETE FROM Student WHERE Sno= '200215129' AND Sdept= 'IS';

④ 更新視圖的限制

• 更新視圖的限制：一些視圖是不可更新的，因?yàn)閷@些視圖的更新不能唯一地有意義地轉(zhuǎn)換成對相應(yīng)基本表的更新

例：視圖S_G為不可更新視圖。

UPDATE S_G SET Gavg=90 WHERE Sno= '200215121';

這個(gè)對視圖的更新無法轉(zhuǎn)換成對基本表SC的更新

• 允許對行列子集視圖進(jìn)行更新

• 對其他類型視圖的更新不同系統(tǒng)有不同限制

總結(jié)

以上是生活随笔為你收集整理的3.5 《数据库系统概论》之基本表更新（INSERT、UPDATE、ALTER、DELETE）与视图VIEW（定义、查询、更新）的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。