數(shù)據(jù)庫技術(shù)及應(yīng)用 課件 第7章 關(guān)系數(shù)據(jù)庫標準語言SQL

DatabaseTechnology&Applications數(shù)據(jù)庫技術(shù)及應(yīng)用SQL概述SQL的功能SQL功能動詞數(shù)據(jù)定義CREATEDROPALTER數(shù)據(jù)更新INSERTUPDATEDELETE數(shù)據(jù)查詢SELECT

數(shù)據(jù)控制GRANTREVOKE

SQL(StructuredQueryLanguage)是1974年由IBM公司的RayBoyce和DonChamberlin依據(jù)Codd關(guān)系數(shù)據(jù)庫的12條準則的數(shù)學(xué)定義提出來的。SQL的特點一體化非過程化面向集合一種語法兩種使用方式簡潔易學(xué)關(guān)系數(shù)據(jù)庫的實現(xiàn)（以openGauss為例）連接數(shù)據(jù)庫創(chuàng)建數(shù)據(jù)庫創(chuàng)建表結(jié)構(gòu)建立表間關(guān)系向表中輸入數(shù)據(jù)連接openGauss數(shù)據(jù)庫創(chuàng)建數(shù)據(jù)庫創(chuàng)建表結(jié)構(gòu)openGauss常用的數(shù)據(jù)類型及其使用方法數(shù)據(jù)類型使用說明大小數(shù)值可用來進行算術(shù)計算的數(shù)字數(shù)據(jù)。設(shè)置“字段大小”屬性定義一個特定的數(shù)字類型。其中NUMERIC、DECIMAL是任意精度類型。1、2、4或8個字節(jié)，與“字段大小”屬性定義有關(guān)。例如NUMERIC[(p[,s])]，DECIMAL[(p[,s])]，p為總位數(shù)，s為小數(shù)位數(shù)日期/時間用于存儲日期和時間值4、8、12、16個字節(jié)字符字符串類型指CHAR、CHARACTER、NCHAR、VARCHAR、CHARACTERVARYING、VARCHAR2、NVARCHAR2、CLOB和TEXT。10MB~1GB-8203字節(jié)布爾即true或false1字節(jié)“學(xué)生”表結(jié)構(gòu)“課程”表結(jié)構(gòu)“選修”表結(jié)構(gòu)建立表間關(guān)系建立表間關(guān)系向表中輸入數(shù)據(jù)——手動輸入數(shù)據(jù)向表中輸入數(shù)據(jù)——從Excel表批量導(dǎo)入數(shù)據(jù)向表中輸入數(shù)據(jù)——從Excel表批量導(dǎo)入數(shù)據(jù)向表中輸入數(shù)據(jù)——從Excel表批量導(dǎo)入數(shù)據(jù)數(shù)據(jù)定義SQL的數(shù)據(jù)對象創(chuàng)建數(shù)據(jù)對象定義撤消更改數(shù)據(jù)庫CREATEDATABASEDROPDATABASEALTERDATABASE基本表CREATETABLEDROPTABLEALTERTABLE視圖CREATEVIEWDROPVIEW

索引CREATEINDEXDROPINDEX

結(jié)構(gòu)定義CREATECREATEDATABASE<數(shù)據(jù)庫模式名>CREATETABLE<表名>（<列名><數(shù)據(jù)類型>[列級完整性約束條件][，<列名><數(shù)據(jù)類型>[列級完整性約束條件]]……[，<表級完整性約束條件>]）其中[]內(nèi)的內(nèi)容是可選項。結(jié)構(gòu)定義CREATE【例7.1】在Navicat中用SQL語句來實現(xiàn)7.1.2節(jié)中通過圖形化界面創(chuàng)建的數(shù)據(jù)庫和表。

用CREATEDATABASE命令創(chuàng)建“選課管理數(shù)據(jù)庫”（EMS）：結(jié)構(gòu)定義CREATE【例7.1】在Navicat中用SQL語句來實現(xiàn)7.1.2節(jié)中通過圖形化界面創(chuàng)建的數(shù)據(jù)庫和表。CREATETABLEpublic.課程(

課程號varchar(35)NOTNULL,

課程名varchar(20)NOTNULL,

學(xué)時int2NOTNULL,

學(xué)分float4NOTNULL,CONSTRAINTCourse_pkeyPRIMARYKEY(課程號));結(jié)構(gòu)定義CREATE【例7.1】在Navicat中用SQL語句來實現(xiàn)7.1.2節(jié)中通過圖形化界面創(chuàng)建的數(shù)據(jù)庫和表。CREATETABLEpublic.學(xué)生(

身份證號varchar(18)NOTNULL,

學(xué)號varchar(12)NOTNULL,

姓名varchar(10)NOTNULL,

性別varchar(1)NOTNULLDEFAULT'男'::charactervarying,

班級varchar(20),

生日dateNOTNULL,CONSTRAINT"學(xué)生_pkey"PRIMARYKEY(學(xué)號),CONSTRAINT"學(xué)生_性別_check"CHECK(性別='男’or性別='女'));結(jié)構(gòu)定義CREATE【例7.1】在Navicat中用SQL語句來實現(xiàn)7.1.2節(jié)中通過圖形化界面創(chuàng)建的數(shù)據(jù)庫和表。CREATETABLEpublic.選修(

課程號varchar(35)NOTNULL,

學(xué)號varchar(12)NOTNULL,

成績int4,CONSTRAINT選修_pkeyPRIMARYKEY(課程號,學(xué)號),CONSTRAINTFK_學(xué)號FOREIGNKEY(學(xué)號)REFERENCESpublic.學(xué)生(學(xué)號)ONDELETENOACTIONONUPDATENOACTION,CONSTRAINTFK_課程號FOREIGNKEY(課程號)REFERENCESpublic.課程(課程號)ONDELETENOACTIONONUPDATENOACTION);結(jié)構(gòu)更新ALTER增加屬性列ALTERTABLE<基本表名>ADD<新列名><數(shù)據(jù)類型>[完整性約束條件]-<表名>是要更新的基本表名稱-ADD子句用于增加新列和新的完整性約束條件【例7.2】在“學(xué)生”表中添加一個新的地址屬性ADDRESS。ALTERTABLE學(xué)生ADDADDRESSVARCHAR（30）；結(jié)構(gòu)更新ALTER刪除屬性列ALTERTABLE<基本表名>DROP<屬性列名>[CASCADE|RESTRAIN]-CASCADE表示在基本表刪除某屬性列時，所有引用到該屬性列的視圖和約束也要一起自動刪除-RESTRAIN表示在沒有視圖或約束引用該屬性列時，才可以在基本表中刪除該列，否則就拒絕刪除操作【例7.3】在“學(xué)生”表中刪除屬性列ADDRESS。ALTERTABLE學(xué)生DROPADDRESSCASCADE;結(jié)構(gòu)更新ALTER修改屬性列ALTERTABLE<基本表名>MODIFY<屬性列名><類型>【例7.4】在“學(xué)生”表中將學(xué)號的長度修改為“12”。ALTERTABLE學(xué)生MODIFY學(xué)號CHAR（12）；結(jié)構(gòu)更新ALTER補充定義主鍵ALTERTABLE<表名>ADDPRIMARYKEY（<列名表>）需要指出，被定義為主鍵的屬性列應(yīng)當是非空和滿足唯一性要求的。結(jié)構(gòu)更新ALTER刪除主鍵ALTERTABLE<表名>DROPPRIMARYKEY（<列名表>）或者ALTERTABLE<表名>DROPCONSTRAINT<主鍵約束名>【例7.5】刪除“學(xué)生”表中主鍵“學(xué)號”的SQL語句如下。ALTERTABLE學(xué)生DROPCONSTRAINT學(xué)生_pkeyCASCADE;結(jié)構(gòu)更新ALTER【例7.6】在“學(xué)生”表中添加主鍵“學(xué)號”以及在“選修”表中添加外鍵的SQL語句如下。ALTERTABLE學(xué)生ADDPRIMARYKEY(學(xué)號);ALTERTABLE選修ADDCONSTRAINTFK_學(xué)號FOREIGNKEY(學(xué)號)REFERENCESpublic.學(xué)生(學(xué)號)ONDELETENOACTIONONUPDATENOACTION;結(jié)構(gòu)撤銷DROP【例7.7】撤銷“學(xué)生”表，但要求只有在沒有視圖或約束引用“學(xué)生”表的屬性列時才能撤銷，否則拒絕撤銷。DROPTABLE學(xué)生RESTRICT；DROPDATABASE<數(shù)據(jù)庫名>DROPTABLE<基本表名>[CASCADE|RESTRICT]-RESTRICT表示沒有視圖或約束引用“學(xué)生”表的屬性列時才能撤銷，否則拒絕撤銷，意味著要先刪除視圖或相關(guān)約束，再來刪除該表。-CASCADE表示刪除表的時候會把相關(guān)的視圖等一并刪除。數(shù)據(jù)查詢SELECT基本語法SELECT[ALL|DISTINCT]<屬性名>[，<屬性名>]…FROM<基本表名或視圖名>[，<基本表名或視圖名>]…[WHERE<邏輯條件式>][GROUPBY<屬性名1>[HAVING<邏輯表達式>]][ORDERBY<屬性名2>[ASC|DESC]]單表查詢【例7.8】在“選課管理數(shù)據(jù)庫”中查詢“課程”表的全部記錄情況。SELECT*FROM課程;單表查詢【例7.9】在“學(xué)生”表中查詢學(xué)生的學(xué)號、姓名和性別。SELECT學(xué)號,姓名,性別FROM學(xué)生;單表查詢【例7.10】（查詢指定屬性列）查詢所有選修了課程的學(xué)生的學(xué)號。SELECT學(xué)號FROM選修；說明：該查詢的結(jié)果中會有重復(fù)的學(xué)號。SELECT后加DISTINCT即可去掉重復(fù)學(xué)號：SELECTDISTINCT學(xué)號FROM選修；單表查詢【例7.11】查詢?nèi)w學(xué)生的分數(shù)都增加10分后的“選修”表。SELECT學(xué)號，成績+10FROM選修；具條件的屬性列查詢運算符含義集合成員運算IN,NOTIN在集合中,不在集合中字符匹配運算LIKE與-和%進行單個、多個字符匹配空值運算ISNULL,ISNOTNULL為空,不能為空比較運算>,>=,<,,=<,=,<>大于,大于等于,小于,小于等于,等于,不等于邏輯運算AND,OR,NOT與,或,非,具條件的屬性列查詢【例7.12】在“選修”表中查找不及格的學(xué)生的學(xué)號。SELECT學(xué)號FROM選修WHERE成績<60;具條件的屬性列查詢【例7.13】在“選修”表中查找課程號為“1026”的不及格的學(xué)生學(xué)號。SELECT學(xué)號FROM選修WHERE成績<60AND課程號='1026';具條件的屬性列查詢【例7.14】在“選修”表中查找成績在[60,80]這個區(qū)間內(nèi)的記錄。SELECT*FROM選修WHERE成績BETWEEN60AND80;具條件的屬性列查詢【例7.15】在“選修”表中查找成績?yōu)閧60,70,80}的學(xué)生。SELECT*FROM選修WHERE成績IN(60,70,80);查詢通配符通配符含

義%包含0個或多個字符_包含1個字符[]指定范圍，如[a-d]代表a、b、c和d[^]不屬于指定的范圍，如[^a-d]代表排除了a、b、c和d以外的其他字符查詢通配符【例7.16】顯示“學(xué)生”表中姓“馬”的同學(xué)的基本信息。SELECT*FROM學(xué)生WHERE姓名LIKE'馬%';查詢通配符【例7.17】若要查找的馬姓同學(xué)的姓名只有兩個字。SELECT*FROM學(xué)生WHERE姓名LIKE'馬_';空值查詢【例7.18】將“選修”表中有成績的記錄顯示出來。SELECT*FROM選修WHERE成績ISNOTNULL;排序查詢【例7.19】在“學(xué)生”表中查詢學(xué)生的基本情況（包括學(xué)生的學(xué)號、姓名和性別），結(jié)果按照學(xué)號的降序排列。SELECT學(xué)號,姓名,性別FROM學(xué)生ORDERBY學(xué)號DESC;若將DESC改成ASC或者不寫，則查詢結(jié)果會根據(jù)ORDERBY后面的字段升序排列。排序查詢【例7.20】在“學(xué)生”表中查詢學(xué)生的基本情況，并按性別的升序顯示結(jié)果；對于性別相同的記錄，再按學(xué)號的降序進行排列。SELECT*FROM學(xué)生ORDERBY性別ASC,學(xué)號DESC;排序查詢【例7.21】在“選修”表中查找課程號為“1026”的成績最高的前三名學(xué)生的學(xué)號。SELECT學(xué)號FROM選修WHERE課程號='1026'ORDERBY成績DESCLIMIT3;連接查詢內(nèi)連接（INNERJOIN）外連接（OUTERJOIN）連接（LEFTJOIN）右連接（RIGHTJOIN）全連接（FULLJOIN）內(nèi)連接基本語法SELECT<屬性或表達式列表>FROM<表名>[INNER]JOIN<表名>ON<連接條件>/USING<字段1>[WHERE<限定條件>]只有滿足給出的連接條件時，相應(yīng)結(jié)果才會出現(xiàn)在結(jié)果關(guān)系表中。內(nèi)連接【例7.22】查詢選修了課程號為“1025”課程的所有學(xué)生學(xué)號與姓名。SELECT學(xué)生.學(xué)號,姓名FROM學(xué)生JOIN選修ON學(xué)生.學(xué)號=選修.學(xué)號WHERE課程號=’1025’；SELECT學(xué)生.學(xué)號,姓名FROM學(xué)生JOIN選修USING(學(xué)號)WHERE課程號='1025'；SELECT學(xué)生.學(xué)號,姓名FROM學(xué)生,選修WHERE學(xué)生.學(xué)號=選修.學(xué)號AND課程號='1025'；內(nèi)連接【例7.23】查詢修讀課程名為“數(shù)據(jù)庫”的所有學(xué)生的姓名。SELECT姓名FROM學(xué)生JOIN選修ON學(xué)生.學(xué)號=選修.學(xué)號JOIN課程ON選修.課程號=課程.課程號WHERE課程名='數(shù)據(jù)庫';內(nèi)連接【例7.24】查詢至少修讀了學(xué)生學(xué)號為“202101231234”所修讀的一門課的學(xué)生學(xué)號。SELECT選修1.學(xué)號FROM選修選修1，選修選修2WHERE選修1.課程號=選修2.課程號AND選修2.學(xué)號=‘202101231234’；外連接【例7.25】查詢所有學(xué)生的基本信息和選課情況。SELECT學(xué)號，姓名，生日，班級，身份證號，課程號，成績FROM學(xué)生JOIN選修ON學(xué)生.學(xué)號=選修.學(xué)號；學(xué)生李敏沒有選修任何課程，由于在“選修”表沒有李敏的相應(yīng)元組，所以查詢結(jié)果中不會出現(xiàn)她的學(xué)號。外連接基本語法SELECT<屬性或表達式列表>FROM<表名>LEFT|RIGHT|FULL[OUTER]JOIN<表名>ON<連接條件>/USING<字段1>[WHERE<限定條件>]外連接【例7.26】查詢所有學(xué)生的基本信息和選課情況。SELECT學(xué)生.學(xué)號,姓名,班級,身份證號,課程號,成績FROM學(xué)生LEFTJOIN選修ON學(xué)生.學(xué)號=選修.學(xué)號;外連接【例7.27】查詢所有課程的基本信息和被選修的情況，包括沒有人選修的也要列出。SELECT課程.課程號，課程名，學(xué)時，學(xué)分，成績FROM選修RIGHTJOIN課程ON學(xué)生.學(xué)號=選修.學(xué)號；嵌套查詢作為WHERE子句中的邏輯表達式，嵌套子查詢有三種形式：字段[NOT]IN子查詢字段θSOME/ANY/ALL子查詢[NOT]EXISTS(子查詢)嵌套查詢【例7.28】查詢修讀課程號為“1025”的所有學(xué)生的姓名。SELECT姓名FROM學(xué)生WHERE學(xué)號IN (SELECT學(xué)號 FROM選修 WHERE課程號=‘1025’)；嵌套查詢【例7.29】查詢沒有修讀課程號為“1025”的所有學(xué)生的姓名。SELECT姓名FROM學(xué)生WHERE學(xué)號NOTIN (SELECT學(xué)號 FROM選修 WHERE課程號=‘1025’)；嵌套查詢【例7.30】查詢修讀課程名為“數(shù)據(jù)庫”的所有學(xué)生的姓名。SELECT姓名FROM學(xué)生 WHERE學(xué)號IN(SELECT學(xué)號FROM選修

WHERE課程號IN (SELECT課程號FROM課程 WHERE課程名=‘數(shù)據(jù)庫’))；嵌套查詢【例7.31】查詢修讀課程號為“1025”的所有學(xué)生的姓名。SELECT姓名FROM學(xué)生WHERE學(xué)號=SOME (SELECT學(xué)號 FROM選修 WHERE課程號=‘1025’)；嵌套查詢【例7.32】查詢沒有修讀課程號為“1025”的所有學(xué)生的姓名。SELECT姓名FROM學(xué)生WHERE學(xué)號<>ALL (SELECT學(xué)號 FROM選修 WHERE課程號=‘1025’)；嵌套查詢【例7.33】查詢課程號為“1025”的最高分的學(xué)生姓名。SELECT姓名FROM學(xué)生INNERJOIN選修USING(學(xué)號)WHERE課程號=‘1025’AND成績>=ALL (SELECT成績

FROM選修

WHERE課程號=‘1025’AND成績ISNOTNULL)；嵌套查詢【例7.34】查詢學(xué)生“賀易”本期選修課程號“1025”的成績。SELECT成績FROM選修WHERE課程號=‘1025’AND學(xué)號=（SELECT學(xué)號 FROM學(xué)生 WHERE姓名=‘賀易’）;嵌套查詢【例7.35】查詢修讀課程號為“1025”的所有學(xué)生的姓名。SELECT姓名FROM學(xué)生WHEREEXISTS (SELECT* FROM選修 WHERE課程號=’1025’AND學(xué)生.學(xué)號=選修.學(xué)號)；嵌套查詢【例7.36】查詢沒有修讀課程號為“1025”的所有學(xué)生的姓名。SELECT姓名FROM學(xué)生WHERENOTEXISTS (SELECT* FROM選修 WHERE課程號=‘1025’AND學(xué)生.學(xué)號=選修.學(xué)號)；嵌套查詢【例7.37】查詢課程號為“1025”的最高分的學(xué)生姓名。SELECT姓名FROM學(xué)生INNERJOIN選修選修1ON學(xué)生.學(xué)號=選修1.學(xué)號WHERE課程號=‘1025’ANDNOTEXISTS

（SELECT* FROM選修選修2WHERE課程號=‘1025’AND選修1.成績<選修2.成績）；嵌套查詢【例7.38】查詢學(xué)生“賀易”選修課程號“1025”的成績。SELECT成績FROM選修WHERE課程號=‘1025’ANDEXISTS

（SELECT* FROM學(xué)生 WHERE姓名=‘賀易’AND選修.學(xué)號=學(xué)生.學(xué)號）；聚合函數(shù)COUNT函數(shù)ICOUNT（[DISTINCT|ALL]*）統(tǒng)計關(guān)系中元組個數(shù)。COUNT函數(shù)IICOUNT（[DISTINCT|ALL]<列名>）統(tǒng)計關(guān)系中給定列中屬性值個數(shù)。SUM函數(shù)SUM（[DISTINCT|ALL]<列名>）計算關(guān)系中數(shù)值型屬性值總和。AVG函數(shù)AVG（[DISTINCT|ALL]<列名>）計算關(guān)系中數(shù)值型屬性值平均值。MAX函數(shù)MAX（[DISTINCT|ALL]<列名>）計算關(guān)系中給定屬性列中數(shù)值型屬性值的最大者。MIN函數(shù)MIN（[DISTINCT|ALL]<列名>）計算關(guān)系中給定屬性列中數(shù)值型屬性值的最小者。聚合函數(shù)【例7.39】查詢?nèi)w學(xué)生人數(shù)。SELECTCOUNT(*)FROM學(xué)生；聚合函數(shù)【例7.40】查詢學(xué)生張怡修讀的課程數(shù)。SELECTCOUNT(*)FROM學(xué)生INNERJOIN選修USING(學(xué)號)WHERE姓名=’張怡’；聚合函數(shù)【例7.41】查詢學(xué)號為“202101231234”的學(xué)生所修讀課程的平均成績。SELECTAVG(成績)FROM選修WHERE學(xué)號=‘202101231234’；聚合函數(shù)【例7.42】查詢可供學(xué)生選修的課程門數(shù)。SELECTCOUNT(*)FROM課程；聚合函數(shù)【例7.43】查詢已有學(xué)生選修的課程門數(shù)。SELECTCOUNT(DISTINCT課程號)FROM選修；分組基本語法SELECT[ALL|DISTINCT]<屬性名>[，<屬性名>]…FROM<基本表名或視圖名>[，<基本表名或視圖名>]…[WHERE<邏輯條件式>][GROUPBY<屬性名1>[HAVING<邏輯表達式>]][ORDERBY<屬性名2>[ASC|DESC]]分組【例7.44】給出每門課程的平均成績。SELECT課程號,AVG(成績)FROM選修GROUPBY課程號；分組【例7.45】給出每個學(xué)生修讀課程的門數(shù):SELECT學(xué)號,COUNT(課程號)FROM選修GROUPBY學(xué)號；分組【例7.46】例7.33選修課程號為“1025”的最高分的學(xué)生姓名的查詢也可以寫為：SELECT姓名FROM學(xué)生INNERJOIN選修ON學(xué)生.學(xué)號=選修.學(xué)號WHERE課程號=‘1025’AND成績=SOME

（SELECTMAX(成績) FROM選修

WHERE課程號=‘1025’）;分組【例7.47】給出有10個或10個以上學(xué)生所修讀課程的課程號和學(xué)生數(shù)。SELECT課程號,COUNT(學(xué)號）FROM選修GROUPBY課程號HAVINGCOUNT(*)>=10；數(shù)據(jù)更新數(shù)據(jù)更新功能刪除插入修改基本語句DELETEFROM<表名>[WHERE<條件>]INSERTINTO<表名>[屬性列]…VALUES(屬性值1，屬性值2，…)或子查詢UPDATE<表名>SET屬性名=屬性值，…[WHERE<條件>]基本語義刪除元組插入元組修改屬性值數(shù)據(jù)刪除基本語法DELETEFROM<基本表名>WHERE<條件>刪除多個元組的值【例7.48】刪除學(xué)生“劉玉”的記錄。DELETEFROM學(xué)生WHERE姓名=‘劉玉’；帶子查詢刪除語句【例7.49】刪除21醫(yī)學(xué)院全體學(xué)生的選課記錄。DELETEFROM選修WHERE‘21醫(yī)學(xué)院’=(SELECT班級

FROM學(xué)生 WHERE學(xué)生.學(xué)號=選修.學(xué)號）；數(shù)據(jù)插入基本語法INSERTINTO<表名>(字段列表)VALUES(值列表)或INSERTINTO<表名>(字段列表)子查詢數(shù)據(jù)插入【例7.50】往“課程”表添加入一條課程號為“1032”，課程名為“大學(xué)物理”，學(xué)時為64，學(xué)分為3的記錄。INSERTINTO課程(課程號,課程名,學(xué)時,學(xué)分)VALUES('1032','大學(xué)物理',64,3);或INSERTINTO課程VALUES('1032','大學(xué)物理',64,3);數(shù)據(jù)插入【例7.51】將一個學(xué)生新記錄（身份證號學(xué)號202101231255,姓名：陳靜，性別：女，生日：2001-12-29）插入到“學(xué)生”表中。INSERTINTO學(xué)生(身份證號,學(xué)號,姓名,性別,生日)VALUES(,'202101231255','陳靜','女','2001-12-29')；數(shù)據(jù)插入【例7.52】創(chuàng)建一張新表“學(xué)生平均分”，有兩個字段（學(xué)號，成績），將每個學(xué)生選修的平均分放至該表中。CREATETABLE學(xué)生平均分(學(xué)號varchar(12)NOTNULL,成績int4)；INSERTINTO學(xué)生平均分SELECT學(xué)號,AVG(成績)FROM選修GROUPBY學(xué)號；數(shù)據(jù)修改基本語法UPDATE<基本表名>SET<列名>=表達式[，<列名>=表達式]…WHERE<邏輯條件>數(shù)據(jù)修改