04學習目標（1）掌握結(jié)構(gòu)化查詢語言（SQL）的概念和功能。（2）掌握SQL數(shù)據(jù)查詢語言DQLSelect語句的語法，掌握分組匯總、子查詢、聯(lián)合查詢等查詢方式。（3）掌握數(shù)據(jù)操縱語言DML的使用，包括Insert、Update和Delete語句的使用。（4）熟悉SQL數(shù)據(jù)定義語句DDL的使用，包括Create、Alter與Drop語句的使用。

4.1創(chuàng)建選擇查詢4.1.1

SQL語言主要功能SQL語言按照功能可以分為下列4種：（1）數(shù)據(jù)查詢語言DQL：完成數(shù)據(jù)查詢功能，由Select命令實現(xiàn)。（2）數(shù)據(jù)定義語言DDL：完成數(shù)據(jù)庫結(jié)構(gòu)的定義，包括Create、Alter與Drop三個命令。（3）數(shù)據(jù)操縱語言DML：完成數(shù)據(jù)庫的更新操作，如記錄的插入、刪除、修改，包括Insert、Update、Delete三個命令。（4）數(shù)據(jù)控制語言DCL：實現(xiàn)對數(shù)據(jù)庫的訪問控制、完整性規(guī)則的描述、事務控制等內(nèi)容。包含Grant，Revoke等命令。4.1

SQL語言概述4.1.2

SQL語言特點（1）高度一體化的語言。SQL可以完成數(shù)據(jù)庫應用中的全部工作。（2）高度非過程化的語言。SQL不必告訴計算機“如何”去做，而是描述用戶要求計算機“做什么”。（3）簡單易學。雖然SQL語言功能強大，但它只有為數(shù)不多的幾條命令，SQL語言的語法簡單，接近自然語言，很容易學習掌握。（4）標準化?，F(xiàn)有關(guān)系數(shù)據(jù)庫系統(tǒng)均支持SQL標準，這意味著，當基于不同關(guān)系數(shù)據(jù)庫系統(tǒng)執(zhí)行同樣操作時，所使用的SQL命令幾乎相同。（5）使用靈活。SQL語言可以交互方式和嵌入方式使用。且由于其通用性，用SQL編寫的程序可以移植到不同關(guān)系數(shù)據(jù)庫平臺上。Access各類SQL命令的輸入和執(zhí)行均可在查詢對象的“SQL視圖”下完成。4.2

SQL數(shù)據(jù)查詢命令4.2.1

查詢命令的語法數(shù)據(jù)查詢是SQL語言的核心內(nèi)容，由Select語句實現(xiàn)，Select語句由多個子句構(gòu)成，可以設(shè)置數(shù)據(jù)源、輸出項、篩選條件、分組、排序等，其執(zhí)行結(jié)果以二維表的形式呈現(xiàn)。Select命令的基本語法如下：Select[All|Distinct][Top<Expn>[Percent]]<輸出列1>[AS<列標題1>][，<輸出列2>[AS<列標題2>]…][Into<表名>]From<表1或查詢1>[[AS]<別名1>][，表2或查詢2>[[AS]<別名2>]…][Where[<連接條件>][And<篩選條件>]][GroupBy<分組列1>[，<分組列2>…]][Having<分組篩選條件>][OrderBy<排序列1>[Asc|Desc][,<排序列2>[Asc|Desc]…]][Union<Select命令>]4.2

SQL數(shù)據(jù)查詢命令4.2.1

查詢命令的語法說明如下：（1）Select子句給出查詢的輸出列，用“*”代替時則表示輸出表或查詢中的所有列。子句中As<列標題>表示為所選擇的輸出列設(shè)置標題。保留字All表示顯示查詢結(jié)果的所有行；Distinct表示去掉查詢結(jié)果中的重復行。Top<Expn>表示只保留查詢結(jié)果中排在前面的、由數(shù)值表達式<Expn>確定數(shù)量的記錄行。Into<表名>選項可以將查詢結(jié)果輸出存成一個新表。（2）From子句列出查詢的數(shù)據(jù)源，可以是一個或多個表，也可以是其它查詢。[AS]<別名>表示為表指定別名，As可以省略。（3）Where子句設(shè)置記錄的篩選條件和表的連接條件。篩選條件和連接條件分別實現(xiàn)關(guān)系代數(shù)中的選擇運算和連接運算。4.2

SQL數(shù)據(jù)查詢命令4.2.1

查詢命令的語法說明如下：（4）GroupBy子句設(shè)置查詢的分組方式，分組可以實現(xiàn)分類匯總。（5）Having子句完成對分組的篩選，即只在查詢結(jié)果中保留符合條件的分組。（6）OrderBy子句控制查詢結(jié)果中記錄的排序。（7）Union子句的功能實現(xiàn)聯(lián)合查詢，即將兩個Select命令的執(zhí)行結(jié)果進行并運算，輸出合并后的查詢結(jié)果。SQL命令書寫格式說明：（1）命令涉及兩個以上表的數(shù)據(jù)時，共有字段名前面必須冠以“.”分隔的表名作為前綴，例如“student.id”和“score.id”。（2）命令書寫不區(qū)分大小寫。4.2

SQL數(shù)據(jù)查詢命令4.2.2

基本查詢基本查詢命令包含Select子句、From子句，還可以有Where子句和Order子句，且數(shù)據(jù)源只有一個。【例4.1】查詢xsgl數(shù)據(jù)庫中student表的所有數(shù)據(jù)。Select*Fromstudent【例4.2】查詢xsgl數(shù)據(jù)庫student表所有學生的學號、姓名、年齡、籍貫信息。Selectid,name,Year(now())–Year(birthday)As年齡,nativeplaceFromstudent說明：輸出列“Year(now())–Year(birthday)As年齡”表示計算年齡并定義列標題為“年齡”。4.2

SQL數(shù)據(jù)查詢命令4.2.2

基本查詢基本查詢命令包含Select子句、From子句，還可以有Where子句和Order子句，且數(shù)據(jù)源只有一個?！纠?.3】查詢xsgl數(shù)據(jù)庫的student表中年齡大于等于22歲的男學生的學號、姓名、性別、年齡、電話號碼信息。命令： Selectid,name,sex,Year(now())–Year(birthday)As年齡,p_numberFromstudentWhereYear(now())–Year(birthday)>=22Andsex="男“【例4.4】查詢xsgl數(shù)據(jù)庫student表中山東籍學生的學號、姓名、性別、出生日期、籍貫信息，按照出生日期升序排列，只顯示前5名。SelectTop5id,name,sex,birthday,nativeplaceFromstudentWhereleft(nativeplace,2)="山東"OrderBybirthdayAsc4.2

SQL數(shù)據(jù)查詢命令4.2.3

連接查詢SQL實現(xiàn)連接查詢有兩種方法，分別是利用Where子句和From…Join…子句。1.Where子句實現(xiàn)連接格式：Select<輸出列>From<數(shù)據(jù)源列表>Where<連接條件>And<查詢條件>【例4.5】查詢xsgl數(shù)據(jù)庫中所有2017年入學的學生的學號、姓名、課程代碼、課程成績信息。Selectstudent.id,name,c_code,scoreFromstudent,scoreWherestudent.id=score.idAndLeft(student.id,4)="2017"說明：① 本例中Where子句中的“student.id=score.id”是連接條件② Student表中id字段前四位代表入學年份，故篩選條件為：Left(student.id,4)="2017"4.2

SQL數(shù)據(jù)查詢命令4.2.3

連接查詢【例4.6】查詢xsgl數(shù)據(jù)庫中所有的學生的學號、姓名、性別、所在學院名稱、專業(yè)名稱信息。分析：本例題的數(shù)據(jù)源包括student和college、major表，要使用連接查詢。Student表中id字段的5至8位是專業(yè)代碼，所以student表和major表的連接條件為：mid（student.id,5,4）=m_code命令如下： Selecta.id,name,sex,name_c,m_nameFromstudenta,collegeAsb,majorWherea.code=b.codeandmid(a.id,5,4)=m_code說明：本例中的From子句為表student、college分別指定了別名，別名可以簡化命令的輸入，別名定義時“As”可以省略。一旦指定了表的別名，在Select、Where等子句中必須使用別名，不能再使用表的本名。4.2

SQL數(shù)據(jù)查詢命令4.2.3

連接查詢2.From…Join…子句實現(xiàn)連接格式：Select<輸出列>From<數(shù)據(jù)源1>Inner|Left|RightJoin<數(shù)據(jù)源2>On<連接條件>[Where<篩選條件>]其中，保留字Inner、Left、Right只能選其中之一，含義如下：（1） InnerJoin內(nèi)部連接，查詢結(jié)果中只包含滿足連接條件的記錄。（2） LeftJoin左連接，是外部連接，查詢結(jié)果中不僅包含滿足連接條件的記錄，還包含左表中不滿足連接條件的記錄，這些記錄對應右表的相應字段為空值。（3） RightJoin右連接，是外部連接，查詢結(jié)果中包含滿足連接條件的所有記錄，同時還包含右表中不滿足連接條件的記錄，這些記錄對應左表的相應字段為空值。4.2

SQL數(shù)據(jù)查詢命令4.2.3

連接查詢【例4.7】查詢xsgl數(shù)據(jù)庫中所有學院的代碼、名稱及其下屬各專業(yè)的代碼、名稱信息。分析：本例題使用內(nèi)部連接實現(xiàn)，其數(shù)據(jù)源包括college、major表，連接條件是“college.code=major.code”。命令如下： Selectname_c,college.code,m_code,m_nameFromcollegeInnerJoinmajorOncollege.code=major.code【例4.8】用左連接查詢xsgl數(shù)據(jù)庫中所有學生的學號、姓名、課程代碼、課程成績信息。命令如下：Selectstudent.id,name,c_code,scoreFromstudentLeftJoinscoreOnstudent.id=score.id4.2

SQL數(shù)據(jù)查詢命令4.2.4

分組查詢GroupBy子句實現(xiàn)分組查詢，使用SQL聚類函數(shù)實現(xiàn)數(shù)據(jù)匯總，其基本語法格式為：GroupBy<分組列1>[，<分組列2>……][Having<組篩選條件>]其中，其中分組列1、分組列2……是分組所依據(jù)的字段名。多個分組列可形成多級分組。Having子句的作用是對分組進行篩選，Having子句不能單獨使用，必須與GroupBy子句同時使用?！纠?.9】查詢xsgl數(shù)據(jù)庫中每個學生的學號、姓名、專業(yè)名稱、平均成績信息。分析：本例題的數(shù)據(jù)源包括student和score、major表。查詢每個學生的平均成績，分組列是學生學號，使用的聚類函數(shù)包括Avg、First。由于ID字段5至8位是專業(yè)代碼，故student和major表的連接條件為：Mid(student.id,5,4)=m_code。命令如下：Selectstudent.id,First()as姓名,First(m_name)as專業(yè),Avg(score)as平均成績Fromstudent,score,majorWherestudent.id=score.idAndMid(student.id,5,4)=m_codeGroupBystudent.id4.2

SQL數(shù)據(jù)查詢命令4.2.4

分組查詢【例4.10】查詢xsgl數(shù)據(jù)庫中查詢每門課程、每個專業(yè)學生的平均分，輸出列包括課程名稱、專業(yè)名稱、平均成績信息。分析：本例題數(shù)據(jù)源包括student、course和score、major表。查詢每門課程、每個專業(yè)學生的平均分，因此分組列是課程編號和專業(yè)編號，實現(xiàn)兩級分組。由于major表無法和score、course直接連接，加入student表可與實現(xiàn)將4個表正確連接。平均分使用Round函數(shù)取舍。命令如下：SelectFirst(c_name)As課程名,First(m_name)As專業(yè),Round(Avg(score),2)As平均分Fromstudent,score,course,majorWherestudent.id=score.idAndMid(student.id,5,4)=m_codeAndcourse.c_code=score.c_codeGroupBycourse.c_code,m_code4.2

SQL數(shù)據(jù)查詢命令4.2.4

分組查詢【例4.11】查詢xsgl數(shù)據(jù)庫中已獲學分超過40分的學生的學號、姓名、入學年份、總學分。分析：本例題的數(shù)據(jù)源包括student和score、course表，分組列是學生學號，學分計算要用到函數(shù)Sum，需要用Having子句篩選出獲學分超過40分的學生。成績及格方能獲得學分。命令：Selectstudent.id,First()As姓名,First(Left(student.id,4))As入學年份,Sum(credit)As總學分數(shù)Fromstudent,score,courseWherestudent.id=score.idAndcourse.c_code=score.c_codeAndscore>=60GroupBystudent.idHavingSum(credit)>=404.2

SQL數(shù)據(jù)查詢命令4.2.4

分組查詢【例4.12】在xsgl數(shù)據(jù)庫中查詢每門課程、每個年級學生的最高分、最低分，輸出列包括課程名稱、入學年份、最高成績、最低成績信息。分析：本例題的數(shù)據(jù)源包括student、course和score表。分組列是課程編號和入學年份。聚類函數(shù)使用Max和Min。命令如下：SelectFirst(c_name)As課程名稱,Left(student.id,4)As入學年份,max(score)As最高成績,Min(score)As最低成績Fromstudent,score,courseWherestudent.id=score.idandcourse.c_code=score.c_codeGroupbycourse.c_code,Left(student.id,4)4.2

SQL數(shù)據(jù)查詢命令4.2.5

子查詢在很多情況下，一個查詢的條件中需要用到另一個查詢的結(jié)果。SQL語言允許在Where子句中嵌入另外一個Select語句，這種嵌套查詢稱為子查詢，外層查詢稱為主查詢或父查詢。嵌套查詢執(zhí)行時先執(zhí)行子查詢，然后根據(jù)子查詢返回的結(jié)果計算主查詢的條件表達式?！纠?.13】從xsgl數(shù)據(jù)庫中查詢從未被學生選修過的課程名稱。分析：從score表中可以找出所有被學生選修過的課程編號集合，凡course表中課程編號不在此集合內(nèi)的即為從未被學生選修過的課程。本例題利用子查詢找出被選修過的課程編號集合，并據(jù)此構(gòu)成主查詢的條件表達式。命令：Selectc_nameFromcourseWherec_codeNotIn(Selectc_codeFromscore)4.2

SQL數(shù)據(jù)查詢命令4.2.5

子查詢【例4.14】從xsgl數(shù)據(jù)庫中查詢從未考試不及格的學生，輸出列包括學號、姓名、專業(yè)名稱。分析：從score表中可以找到有課程不及格的學生學號集合，凡student表中學號不在此集合內(nèi)的學生即為從未考試不及格的學生。由于要顯示專業(yè)名稱，student表要與major表連接。命令：Selectid,name,m_nameFromstudent,majorWhereMid(student.id,5,4)=m_codeAndidNotIn(SelectIdFromscoreWherescore<60)4.2

SQL數(shù)據(jù)查詢命令4.2.5

子查詢【例4.15】從xsgl數(shù)據(jù)庫中查詢同時選修了“中級財務會計”和“審計學”的學生名單。分析：符合查詢條件的學生要同時存在于選修“中級財務會計”課程的集合和選修“審計學”課程的集合中。命令：SelectnameFromstudentWhereidIn(SelectIdFromscore,courseWherecourse.c_code=score.c_codeAndc_name="中級財務會計")AndidIn(SelectIdFromscore,courseWherecourse.c_code=score.c_codeAndc_name="審計學")4.2

SQL數(shù)據(jù)查詢命令4.2.6

聯(lián)合查詢聯(lián)合查詢對應集合的并運算，SQL通過運算符“Union”實現(xiàn)并運算。聯(lián)合查詢要求兩個Select命令具有同數(shù)量的輸出列，且對應輸出列的數(shù)據(jù)類型完全相同。【例4.16】從xsgl數(shù)據(jù)庫中查詢沒有考試不及格或平均分高于80分學生名單。分析：實現(xiàn)方式是找出沒有考試不及格學生集合與平均分高于80分學生集合進行并運算。命令如下：SelectnameFromstudentWhereidNotIn(SelectIdFromscoreWherescore<60)UnionSelectnameFromstudentWhereidIn(SelectIdFromscoreGroupbyidHavingAvg(score)>=80)4.2

SQL數(shù)據(jù)查詢命令4.2.7SQL生成表查詢SQLSelect子句中的Into選項可以將查詢結(jié)果生成新表。【例4.17】連接表college和major，并將所有信息保存成新表collegemajor。命令：Selectcollege.*,major.*IntocollegemajorFromcollege,majorWherecollege.code=major.code4.3

SQL數(shù)據(jù)操縱命令4.3.1插入數(shù)據(jù)三種基本的DML命令是Insert、Update、DeleteSQL插入命令的格式如下：InsertInto<表名>[(<字段名1>,<字段名2>…，<字段名n>)]Values(<值1>，<值2>，……,<值n>)上述格式中，如果只向幾個特定字段輸入值，要列出字段名表，若向全部字段輸入值，則可省略字段名表，在Values后面列出對應每一字段的值。字段名表中字段數(shù)據(jù)類型必須與值列表中的數(shù)據(jù)類型一一對應?？梢允褂肗ull為字段賦值?！纠?.18】向表college中插入一條記錄。命令：InsertIntocollegeValues("06","法學院")說明：向college表全部字段輸入值，故省略字段名表。【例4.19】向student表插入一條新記錄，只為字段id,name,sex,code,birthday賦值。命令：InsertIntostudent(id,name,sex,code,birthday)Values("2017050210","習悅","女","05",#1998-01-08#)說明：向college表部分字段輸入值，故要列出字段名表。4.3

SQL數(shù)據(jù)操縱命令4.3.2更新數(shù)據(jù)Update命令可按照某個更新規(guī)則批量更新表中的數(shù)據(jù)。命令格式如下：Update<表名>Set<字段名1>＝<表達式1>[,<字段名2>＝<表達式2>，……][Where<條件表達式>]<表名>指定了要更新的表，<字段名1>、<字段名2>等表示要更新的字段，<表達式1>、<表達式2>定義了更新方式，Where子句說明了更新記錄的篩選條件，若缺省Where子句，將更新表中所有記錄。【例4.20】將course表中學分帶小數(shù)位的課程學分四舍五入變成整數(shù)命令：UpdatecourseSetcredit=Round(credit)【例4.21】score表中平均成績超過90分的學生，將獲得一等獎學金，將student表中這些學生的resume字段末尾增加字符串，內(nèi)容是當天日期并連接字符串“獲一等獎學金”。分析：使用子查詢找出平均成績超過90分的學號集合。UpdatestudentSetresume=resume&date()&"獲一等獎學金"WhereidIn(SelectidFromscoreGroupByidHavingAvg(score)>=90)4.3

SQL數(shù)據(jù)操縱命令4.3.3刪除數(shù)據(jù)Delete命令用于刪除表中的記錄，刪除的數(shù)據(jù)不能恢復。命令格式為：DeleteFrom<表名>[Where<條件>]上述命令格式中，如果省略條件子句，則刪除表中的所有記錄?！纠?.22】刪除college表中學院名稱為Null的記錄。命令：DeleteFromcollegeWherename_cIsNull4.4

SQL數(shù)據(jù)定義命令4.4.1創(chuàng)建表標準SQL的數(shù)據(jù)定義功能一般包括創(chuàng)建數(shù)據(jù)庫，創(chuàng)建表、視圖和存儲過程，創(chuàng)建規(guī)則、索引等，本節(jié)將介紹Access支持的創(chuàng)建表、修改表結(jié)構(gòu)、刪除表等，數(shù)據(jù)定義命令包括：Create、Drop、Alter。1.創(chuàng)建表結(jié)構(gòu)

SQL的CreateTable命令可以創(chuàng)建表，格式如下：CreateTable<表名>(<字段名1><字段類型1>[(<字段大小1>)][NOTNULL][PrimaryKey|Unique][References<表名>(字段名)][,<字段名2><字段類型2>[(<字段大小2>)][NOTNULL][PrimaryKey|Unique][References<表名>(字段名)]][,…])說明：（1） PrimaryKey表示將本字段創(chuàng)建為主鍵，Unique為唯一索引。（2） NotNull定義本字段不可以為空。（3） References<表名>(字段名)定義與另一表建立關(guān)系。（4）創(chuàng)建表時，字段的數(shù)據(jù)類型標識符如表4-1所示。4.4

SQL數(shù)據(jù)定義命令4.4.1創(chuàng)建表數(shù)據(jù)類型標識數(shù)據(jù)類型標識短文本Text整型SmallInt長整型Int|Integer字節(jié)Byte單精度型Real|Single雙精度型Float自動編號Counter貨幣Money|currency日期時間DateTime|Date是否Logical|Bit長文本MemoOLE對象OLEObject表4-1字段數(shù)據(jù)類型標識4.4

SQL數(shù)據(jù)定義命令4.4.1創(chuàng)建表【例4.23】圖書表的結(jié)構(gòu)為：books(no,title,writer,press,price，foreign_L)，用CreateTable命令創(chuàng)建此表。命令：CreateTablebooks(notext(20),titleText(50),writerText(20),pressText(40),priceMoney,foreign_LBit)如果要在創(chuàng)建表時同時定義主鍵等，命令可以寫成下面的形式：CreateTablebooks(notext(20)PrimaryKeynotnull,titleText(50)notnull,writerText(20),pressText(40),priceMoney,foreign_LBit)4.4

SQL數(shù)據(jù)定義命令4.4.1創(chuàng)建表2．創(chuàng)建索引用CreateIndex命令可以創(chuàng)建表的索引，命令格式如下：Create[Unique]Index<索引名>On<表名>(<索引字段1>[Asc|Desc][,<索引字段2>[Asc|Desc][,…]])[WithPrimary]上述格式中，WithPrimary表示將本索引創(chuàng)建為主鍵，Unique為唯一索引。【例4.24】為books表創(chuàng)建以writer為關(guān)鍵字的索引，索引名為w1。命令：CreateIndexw1Onbooks(writer)【例4.25】為books表創(chuàng)建以press和price降序排列的索引,索引名為p1。命令：CreateIndexp1Onbooks(press,priceDesc)4.4

SQL數(shù)據(jù)定義命令4.4.1創(chuàng)建表3. 創(chuàng)建表間關(guān)系CreateTable命令在創(chuàng)建表的同時，還可以使用References選項創(chuàng)建表間關(guān)系?！纠?.26】書評表的結(jié)構(gòu)為：review(id,book_no,reviewer,review)，用CreateTable命令創(chuàng)建