第5章SQL與JDBC=創(chuàng)新教育基礎(chǔ)與實(shí)踐=大連理工大學(xué)

1、LOGO第5章 SQL與JDBC孫燾孫燾重點(diǎn)：重點(diǎn)：SQL語(yǔ)句概念1select語(yǔ)句2更新操作語(yǔ)句3JDBC接口4難點(diǎn)：難點(diǎn)：連接查詢語(yǔ)句1group by 語(yǔ)句2級(jí)聯(lián)更新語(yǔ)句3JDBC中類的運(yùn)行機(jī)制45.1 SQL概念SQL簡(jiǎn)介簡(jiǎn)介v SQL全稱是全稱是“結(jié)構(gòu)化查詢語(yǔ)言結(jié)構(gòu)化查詢語(yǔ)言(Structured Query Language)”v SQL語(yǔ)句主要完成如下功能：語(yǔ)句主要完成如下功能：(1)數(shù)據(jù)定義功能數(shù)據(jù)定義功能:創(chuàng)建、修改、刪除表、視圖等創(chuàng)建、修改、刪除表、視圖等(2)數(shù)據(jù)操縱功能數(shù)據(jù)操縱功能:數(shù)據(jù)查詢：查詢、統(tǒng)計(jì)、分組、排序數(shù)據(jù)查詢：查詢、統(tǒng)計(jì)、分組、排序等操作；數(shù)據(jù)更新：插入、

2、刪除、修改等操作。等操作；數(shù)據(jù)更新：插入、刪除、修改等操作。(3)數(shù)據(jù)控制功能數(shù)據(jù)控制功能:安全性控制：通過(guò)對(duì)數(shù)據(jù)庫(kù)用戶授權(quán)、收權(quán)實(shí)現(xiàn)存取安全性控制：通過(guò)對(duì)數(shù)據(jù)庫(kù)用戶授權(quán)、收權(quán)實(shí)現(xiàn)存取控制，保證數(shù)據(jù)庫(kù)安全性；控制，保證數(shù)據(jù)庫(kù)安全性；完整性控制：完整性約束條件。完整性控制：完整性約束條件。5.2 SQL語(yǔ)句5.2.1 增刪改語(yǔ)句增刪改語(yǔ)句v練習(xí)練習(xí)1：創(chuàng)建：創(chuàng)建“學(xué)生學(xué)生”表表StudentvCREATE TABLE Student (Sno char(8) NOT NULL,Sname varchar(10),Ssex char(1),Sbirth datetime,Sdept char(3)

3、, CONSTRAINT c1 PRIMARY KEY(Sno)v練習(xí)練習(xí)2：v(1)向向Student表增加表增加“入學(xué)時(shí)間入學(xué)時(shí)間”列列Scome，其數(shù)據(jù)類型為日期型，其數(shù)據(jù)類型為日期型；v(2)將姓名字段將姓名字段Sname長(zhǎng)度改為長(zhǎng)度改為20；v(3)刪除刪除Sbirth列；列；v(4)刪除學(xué)生表；刪除學(xué)生表；v(1)ALTER TABLE Student ADD Scome DATETIMEv(2)ALTER TABLE Student ALTER COLUMN Sname varchar(20)v(3)ALTER TABLE Student DROP COLUMN Sbirthv(

4、4)DROP TABLE Student5.2.2 查詢語(yǔ)句查詢語(yǔ)句SELECT目標(biāo)列組目標(biāo)列組FROM數(shù)據(jù)源數(shù)據(jù)源WHERE元組選擇條件元組選擇條件GROUP BY分列組分列組HAVING 組選擇組選擇條件條件ORDER BY排序列排序列1排序要求排序要求1 ，n；v練習(xí)練習(xí)3：查詢學(xué)號(hào)、姓名：查詢學(xué)號(hào)、姓名 查詢學(xué)生表的所有字段信息查詢學(xué)生表的所有字段信息vSELECT Sno, Sname FROM Studentv有兩種方式：有兩種方式：SELECT Sno, Sname, Ssex, SBirth, Sdept FROM studentSELECT * FROM Studentv練習(xí)

5、練習(xí)4：求選修課號(hào)為：求選修課號(hào)為C1且成績(jī)?cè)谇页煽?jī)?cè)?090之間的之間的學(xué)生學(xué)號(hào)和成績(jī)，并將成績(jī)乘以系數(shù)學(xué)生學(xué)號(hào)和成績(jī)，并將成績(jī)乘以系數(shù)0.8輸出輸出vSELECT Sno, Grade*0.8FROM CSWHERE Cno = C1ANDGrade BETWEEN 80 AND 90說(shuō)明如下：說(shuō)明如下：Grade*0.8表示表示SELECT語(yǔ)句不僅支持字段輸出，而且支持語(yǔ)句不僅支持字段輸出，而且支持表達(dá)式輸出；表達(dá)式輸出；WHERE子句后面是元組選擇條件，是個(gè)邏輯表達(dá)式。子句后面是元組選擇條件，是個(gè)邏輯表達(dá)式。v練習(xí)練習(xí)5：根據(jù)課程表，把所有姓張的老師姓：根據(jù)課程表，把所有姓張的老師姓名

6、打印出來(lái)，并要求記錄不要重復(fù)名打印出來(lái)，并要求記錄不要重復(fù)SELECT DISTINCT CTeacherFROM CourseWHERE CTeacher like 張張%說(shuō)明如下：說(shuō)明如下：DISTINCT關(guān)鍵字表示去除重復(fù)記錄；關(guān)鍵字表示去除重復(fù)記錄；like是是SQL語(yǔ)言實(shí)現(xiàn)模糊匹配的保留字，語(yǔ)言實(shí)現(xiàn)模糊匹配的保留字，%為通配符。為通配符。v練習(xí)練習(xí)6：打印課號(hào)為：打印課號(hào)為C1的選課學(xué)生學(xué)號(hào)，成績(jī)轉(zhuǎn)換的選課學(xué)生學(xué)號(hào)，成績(jī)轉(zhuǎn)換為為ABCD等級(jí)輸出等級(jí)輸出SELECT Sno, case when grade=85 then Awhen grade=75 then Bwhen grade

7、=60 then C else Dend as levelFROM CSv練習(xí)練習(xí)7：列出所有學(xué)生的學(xué)號(hào)、姓名、課號(hào)：列出所有學(xué)生的學(xué)號(hào)、姓名、課號(hào)、成績(jī)、成績(jī),并按成績(jī)降序排列并按成績(jī)降序排列vSELECT Student.sno, Student.Sname, CS.cno,CS.GradeFROM Student,CSWHERE Student.sno = CS.snoORDER BY CS.Grade DESCv練習(xí)練習(xí)8：列出所有學(xué)生的學(xué)號(hào)、姓名、課號(hào)：列出所有學(xué)生的學(xué)號(hào)、姓名、課號(hào)、成績(jī)、成績(jī),并按成績(jī)降序排列；要求沒(méi)有成績(jī)的學(xué)生并按成績(jī)降序排列；要求沒(méi)有成績(jī)的學(xué)生名單仍要顯示，成

8、績(jī)一欄輸出名單仍要顯示，成績(jī)一欄輸出0vSELECT Student.Sno, Student.Sname, CS.cno,isNull(CS.Grade,0) GradeFROM ( Student Left Outer Join CSon Student.sno = CS.sno) v若一個(gè)關(guān)系中的元組在另一個(gè)關(guān)系中沒(méi)有相匹配的若一個(gè)關(guān)系中的元組在另一個(gè)關(guān)系中沒(méi)有相匹配的元組，則這些元組會(huì)在連接結(jié)果中出現(xiàn)，并在另一個(gè)關(guān)系元組，則這些元組會(huì)在連接結(jié)果中出現(xiàn)，并在另一個(gè)關(guān)系的其他屬性位置上放上的其他屬性位置上放上NullNull。v練習(xí)練習(xí)9：將上一練習(xí)的查詢：將上一練習(xí)的查詢“固化固化”為視

9、圖為視圖vCREATE VIEW CS_View ASvSELECT Student.Sno, Student.Sname, CS.cno,isNull(CS.Grade,0) GradeFROM ( Student Left Outer Join CSon Student.sno = CS.sno) v練習(xí)練習(xí)10：列出得過(guò)：列出得過(guò)100分的學(xué)生的學(xué)號(hào)、姓名分的學(xué)生的學(xué)號(hào)、姓名vSELECT sno,sname FROM StudentWHERE Exists ( SELECT 1 FROM CSWHERE CS.sno = Student.sno ANDgrade = 100)v練習(xí)練習(xí)

10、11：列出沒(méi)有選：列出沒(méi)有選C01課程的學(xué)生的學(xué)號(hào)課程的學(xué)生的學(xué)號(hào)、姓名、姓名vSELECT sno,sname FROM StudentWHERE Not Exists ( SELECT 1 FROM CSWHERE CS.sno = Student.sno Andcno = C01)v練習(xí)練習(xí)12：統(tǒng)計(jì)各系人數(shù)：統(tǒng)計(jì)各系人數(shù)vSELECT sdept，COUNT(*) as stu_countFROM StudentGROUP BY sdeptvGROUP BY 語(yǔ)句實(shí)現(xiàn)將查詢結(jié)果集按某一語(yǔ)句實(shí)現(xiàn)將查詢結(jié)果集按某一列或多列的值分組，值相等的為一組，一個(gè)分組列或多列的值分組，值相等的為一組，

11、一個(gè)分組以一個(gè)元組的形式出現(xiàn)。需要注意的是，只有出以一個(gè)元組的形式出現(xiàn)。需要注意的是，只有出現(xiàn)在現(xiàn)在GROUP BY子句中的屬性，才可出現(xiàn)在子句中的屬性，才可出現(xiàn)在Select子句中。子句中。COUNT為聚集函數(shù)，聚集函數(shù)共為聚集函數(shù)，聚集函數(shù)共5種：種：(1) 平均值：平均值：AVG(2) 總和：總和： SUM(3) 最小值：最小值：MIN(4) 最大值：最大值：MAX計(jì)數(shù)：計(jì)數(shù)： COUNTv練習(xí)練習(xí)1313：列出具有兩門(mén)（含）以上不及格的：列出具有兩門(mén)（含）以上不及格的學(xué)生的學(xué)號(hào)、不及格的課目數(shù)。學(xué)生的學(xué)號(hào)、不及格的課目數(shù)。vSELECT sno，COUNT(sno) FROM CSWH

12、ERE grade = 2vHAVING子句和子句和WHERE子句都有條件選擇子句都有條件選擇的功能，但兩者還是有很大區(qū)別的：的功能，但兩者還是有很大區(qū)別的：Where 決定決定哪些元組被選擇參加運(yùn)算，作用于關(guān)系中的元組哪些元組被選擇參加運(yùn)算，作用于關(guān)系中的元組；Having 決定哪些分組符合要求，作用于分組決定哪些分組符合要求，作用于分組。因此聚合函數(shù)的條件關(guān)系必須用。因此聚合函數(shù)的條件關(guān)系必須用Having，Where中不應(yīng)出現(xiàn)聚合函數(shù)。中不應(yīng)出現(xiàn)聚合函數(shù)。5.2.3 插入語(yǔ)句插入語(yǔ)句v練習(xí)練習(xí)1414：新增一個(gè)學(xué)生信息：新增一個(gè)學(xué)生信息 07001, 07001, 張三張三, , 198

13、8-03-09, F, CS1988-03-09, F, CSvINSERT INTO Student(Sno,Sname,Sbirth,Ssex,Sdept) VALUES (07001, 張三張三,1988-03-09, F, CS)v練習(xí)練習(xí)1515：給：給CSCS系的學(xué)生開(kāi)設(shè)必修課系的學(xué)生開(kāi)設(shè)必修課C05C05，建立，建立選課信息選課信息vINSERT INTO CSSELECT sno, cno, null FROM Student, Course WHERE sdept = CSand cno =C05 5.2.4 刪除語(yǔ)句刪除語(yǔ)句vDELETE語(yǔ)句的一般形式：語(yǔ)句的一般形式：DE

14、LETE FROM .FROM WHERE v練習(xí)練習(xí)1616：在學(xué)生表中刪除出生日期在：在學(xué)生表中刪除出生日期在1988-1988-01-0101-01之前的學(xué)生之前的學(xué)生vDELETE FROM Student WHERE Sbirth1988-01-01v練習(xí)練習(xí)1717：在學(xué)生表中刪除課程：在學(xué)生表中刪除課程C01C01沒(méi)有成績(jī)的沒(méi)有成績(jī)的學(xué)生學(xué)生vDELETE Student FROM Student, CS WHERE CS.Cno =C01 and isnull(CS.Grade,0)=0 and CS.Sno=Student.Sno5.2.5 更新語(yǔ)句更新語(yǔ)句vUPDATE語(yǔ)句

15、的一般形式如下：語(yǔ)句的一般形式如下：UPDATE .SET , FROM ,WHERE 從從SQLSERVER2000的的UPDATE語(yǔ)法形式上看，語(yǔ)法形式上看，UPDATE可以接可以接FROM子句（但子句（但Oracle等就不可以）。等就不可以）。v練習(xí)練習(xí)19：將課程：將課程C01成績(jī)大于等于成績(jī)大于等于95的學(xué)生的學(xué)生轉(zhuǎn)入轉(zhuǎn)入PH系系vUPDATE StudentSET Student.Sdept=PH2FROM Student, CSWHERE CS.Cno=C01 and CS.Grade=60 and CS.Sno=Student.Sno5.3 存儲(chǔ)過(guò)程：存儲(chǔ)過(guò)程：v存儲(chǔ)過(guò)程就是將

16、常用的或很復(fù)雜的工作存儲(chǔ)過(guò)程就是將常用的或很復(fù)雜的工作，預(yù)先用，預(yù)先用SQL語(yǔ)句寫(xiě)好并用一個(gè)指定的名語(yǔ)句寫(xiě)好并用一個(gè)指定的名稱存儲(chǔ)起來(lái)，當(dāng)執(zhí)行存儲(chǔ)過(guò)程時(shí)，只需調(diào)稱存儲(chǔ)起來(lái)，當(dāng)執(zhí)行存儲(chǔ)過(guò)程時(shí)，只需調(diào)用用execute , 即可自動(dòng)完成命令即可自動(dòng)完成命令優(yōu)點(diǎn)：優(yōu)點(diǎn)：v1.存儲(chǔ)過(guò)程只在創(chuàng)造時(shí)進(jìn)行編譯，以后每次存儲(chǔ)過(guò)程只在創(chuàng)造時(shí)進(jìn)行編譯，以后每次執(zhí)行存儲(chǔ)過(guò)程都不需再重新編譯，執(zhí)行存儲(chǔ)過(guò)程都不需再重新編譯，v2.當(dāng)對(duì)數(shù)據(jù)庫(kù)進(jìn)行復(fù)雜操作時(shí)當(dāng)對(duì)數(shù)據(jù)庫(kù)進(jìn)行復(fù)雜操作時(shí)(如對(duì)多個(gè)表如對(duì)多個(gè)表進(jìn)行進(jìn)行Update,Insert,Query,Delete時(shí)），可時(shí)），可將此復(fù)雜操作用存儲(chǔ)過(guò)程封裝起來(lái)與數(shù)據(jù)庫(kù)提供將此

17、復(fù)雜操作用存儲(chǔ)過(guò)程封裝起來(lái)與數(shù)據(jù)庫(kù)提供的事務(wù)處理結(jié)合一起使用。的事務(wù)處理結(jié)合一起使用。v3.存儲(chǔ)過(guò)程可以重復(fù)使用存儲(chǔ)過(guò)程可以重復(fù)使用,4.安全性高安全性高,可設(shè)可設(shè)定只有某此用戶才具有對(duì)指定存儲(chǔ)過(guò)程的使用權(quán)定只有某此用戶才具有對(duì)指定存儲(chǔ)過(guò)程的使用權(quán)語(yǔ)法格式語(yǔ)法格式Create procedure procedue_nameparameter data_typeoutputwithrecompile|encryptionassql_statement procedue_name：為過(guò)程名稱：為過(guò)程名稱parameter：為參數(shù)名稱：為參數(shù)名稱 data_type：為參數(shù)的類型：為參數(shù)的類型out

18、put：表示此參數(shù)是可傳回的：表示此參數(shù)是可傳回的with recompile|encryptionrecompile:表示每次執(zhí)行此存儲(chǔ)過(guò)程時(shí)都重新編譯一次表示每次執(zhí)行此存儲(chǔ)過(guò)程時(shí)都重新編譯一次encryption:所創(chuàng)建的存儲(chǔ)過(guò)程的內(nèi)容會(huì)被加密所創(chuàng)建的存儲(chǔ)過(guò)程的內(nèi)容會(huì)被加密v練習(xí)練習(xí)20：一個(gè)簡(jiǎn)單的存儲(chǔ)過(guò)程，查詢學(xué)生表：一個(gè)簡(jiǎn)單的存儲(chǔ)過(guò)程，查詢學(xué)生表中的第一條記錄中的第一條記錄vCREATE PROCEDURE Student_sAS SELECT TOP 1 Sno, Sname, Ssex, Sbirth, Sdeptfrom StudentGOv練習(xí)練習(xí)2121：查詢姓名為張三，課程

19、名為計(jì)算機(jī)：查詢姓名為張三，課程名為計(jì)算機(jī)的存儲(chǔ)過(guò)程的存儲(chǔ)過(guò)程vCREATE PROCEDURE Student_score(param1 varchar(10),param2 varchar(50) AS Select Student.Sname,Course.Cname,CS.Grade from Student,Course,CS where Sname=param1 and Cname=param2 and Student.Sno=CS.Sno and CS.Cno=Course.CnoGO然后執(zhí)行存儲(chǔ)過(guò)程然后執(zhí)行存儲(chǔ)過(guò)程Student_score：Exec Student_score

20、 張三張三,計(jì)算機(jī)計(jì)算機(jī)v練習(xí)練習(xí)2222：加入一條記錄到表：加入一條記錄到表Department,Department,并查詢以及輸并查詢以及輸出此表中院系總數(shù)出此表中院系總數(shù)vCreate proc insert_Departmentparam1 char(3),param2 varchar(50),param3 int outputWith encryption -加密加密as insert into Department(Dno,Dname) Values(param1,param2) select param3=count(Dno) from Departmentgo然后執(zhí)行存儲(chǔ)過(guò)程

21、然后執(zhí)行存儲(chǔ)過(guò)程insert_Department：declare countDno int - 變量的聲明變量的聲明exec insert_Department 001,創(chuàng)新學(xué)院創(chuàng)新學(xué)院,countDno outputprint countDno5.4 觸發(fā)器：觸發(fā)器：v 觸發(fā)器是一種特殊的存儲(chǔ)過(guò)程，當(dāng)觸發(fā)器是一種特殊的存儲(chǔ)過(guò)程，當(dāng)表中數(shù)據(jù)被修改時(shí)，表中數(shù)據(jù)被修改時(shí)，SQL Server將將自動(dòng)執(zhí)行觸發(fā)器。使用觸發(fā)器可以實(shí)自動(dòng)執(zhí)行觸發(fā)器。使用觸發(fā)器可以實(shí)施較為復(fù)雜的數(shù)據(jù)完整性約束。施較為復(fù)雜的數(shù)據(jù)完整性約束。觸發(fā)器優(yōu)點(diǎn)：觸發(fā)器優(yōu)點(diǎn)：v觸發(fā)器能夠?qū)嵤┑臋z查和操作比主鍵和外鍵觸發(fā)器能夠?qū)嵤┑臋z查

22、和操作比主鍵和外鍵約束、約束、CHECK約束和規(guī)則對(duì)象等更為復(fù)雜。約束和規(guī)則對(duì)象等更為復(fù)雜。v觸發(fā)器建立在表一級(jí)，它與特定的數(shù)據(jù)修改觸發(fā)器建立在表一級(jí)，它與特定的數(shù)據(jù)修改事件相對(duì)應(yīng)。事件相對(duì)應(yīng)。vCREATE TRIGGER trigger_nameON table_nameFOR INSERT,UPDATE,DELETEWITH ENCRYPTIONAS sql_statementsv解釋：解釋：v trigger_name：指定觸發(fā)器的名稱，它必須遵守：指定觸發(fā)器的名稱，它必須遵守SQL Server的命名規(guī)的命名規(guī)則，而且同一個(gè)數(shù)據(jù)庫(kù)中不允許出現(xiàn)觸發(fā)器名稱相同的情況。則，而且同一個(gè)數(shù)據(jù)庫(kù)

23、中不允許出現(xiàn)觸發(fā)器名稱相同的情況。vtable_name：指定觸發(fā)器所在的表名：指定觸發(fā)器所在的表名vINSERT,UPDATE,DELETE：指定觸發(fā)條件：指定觸發(fā)條件vENCRYPTION：SQL Server將觸發(fā)器的定義文本保存在系統(tǒng)分類表將觸發(fā)器的定義文本保存在系統(tǒng)分類表 syscomments中，選用此項(xiàng)要求中，選用此項(xiàng)要求SQL Server對(duì)該觸發(fā)器定義文本進(jìn)行加密對(duì)該觸發(fā)器定義文本進(jìn)行加密存儲(chǔ)，以防止第三方用戶從存儲(chǔ)，以防止第三方用戶從syscomments表中讀取該觸發(fā)器定義文本的內(nèi)表中讀取該觸發(fā)器定義文本的內(nèi)容。容。vsql_statement：定義觸發(fā)器在觸發(fā)事件發(fā)生時(shí)

24、：定義觸發(fā)器在觸發(fā)事件發(fā)生時(shí)(被激活時(shí)被激活時(shí))所要執(zhí)行的操所要執(zhí)行的操作。作。5.5 JDBC和數(shù)據(jù)庫(kù)和數(shù)據(jù)庫(kù)vJDBC：Java DataBase Connectivity（Java 數(shù)據(jù)庫(kù)連接技術(shù)），它是數(shù)據(jù)庫(kù)連接技術(shù)），它是 將將Java與與SQL結(jié)合結(jié)合且獨(dú)立于特定的數(shù)據(jù)庫(kù)系統(tǒng)的應(yīng)用程序編程接口（且獨(dú)立于特定的數(shù)據(jù)庫(kù)系統(tǒng)的應(yīng)用程序編程接口（API它是一種可用于執(zhí)行它是一種可用于執(zhí)行SQL語(yǔ)句的語(yǔ)句的Java API，即由一組用即由一組用Java語(yǔ)言編寫(xiě)的類與接口所組語(yǔ)言編寫(xiě)的類與接口所組 成）。成）。vJDBC的主要功能如下：的主要功能如下：(1)創(chuàng)建與數(shù)據(jù)庫(kù)的連接；創(chuàng)建與數(shù)據(jù)庫(kù)的連

25、接；(2)發(fā)送發(fā)送SQL語(yǔ)句到任何關(guān)系型數(shù)據(jù)庫(kù)中；語(yǔ)句到任何關(guān)系型數(shù)據(jù)庫(kù)中；(3)處理數(shù)據(jù)并查詢結(jié)果。處理數(shù)據(jù)并查詢結(jié)果。頭文件：頭文件：vimport java.sql.DriverManager;vimport java.sql.Statement;vimport java.sql.ResultSet;vimport java.sql.Connection;vimport java.sql.SQLException;連接數(shù)據(jù)庫(kù)：連接數(shù)據(jù)庫(kù)：vprotected String driverName = com.microsoft.jdbc.sqlserver.SQLServerDriver;

26、vprotected String userName = sa; /訪問(wèn)數(shù)據(jù)庫(kù)的用戶名訪問(wèn)數(shù)據(jù)庫(kù)的用戶名vprotected String userPwd = password; /訪問(wèn)數(shù)據(jù)庫(kù)的密碼訪問(wèn)數(shù)據(jù)庫(kù)的密碼vprotected String dbName = MyDB; /數(shù)據(jù)庫(kù)實(shí)例名稱數(shù)據(jù)庫(kù)實(shí)例名稱vprotected String url = jdbc:microsoft:sqlserver:/:1433;databaseName= + dbName; /訪問(wèn)數(shù)據(jù)庫(kù)訪問(wèn)數(shù)據(jù)庫(kù)URLvprotected Connection conn = null;vprotected Statement sm = null;打開(kāi)數(shù)據(jù)庫(kù)連接vprotected void ConnectDB() throws SQLException,InstantiationException, IllegalAccessException, ClassNotFoundException Class.forName(driverName);conn = DriverManager.getConnection(url, userName, userPwd);sm = conn.createStatement();簡(jiǎn)單查詢：簡(jiǎn)單查詢：vCl