1、MySQL數(shù)據(jù)庫技術(shù)與實驗指導(dǎo)（第1版）錢雪忠、王燕玲、張平 編著清華大學(xué)出版社2011.09實驗1 數(shù)據(jù)庫系統(tǒng)基礎(chǔ)操作進入MySQL的官方下載頁面：/downloads/如果想找舊的發(fā)布版本，可進入頁面：/archives.php例11 有一個腳本文件（test.sql），文件內(nèi)容：Show databases;Create database test;Use test;Create table table_1( I int ) ENGINE = MyISAM;執(zhí)行之。解：C: mysql -h loca

2、lhost -u root -p source c:test.sql實驗2 MySQL數(shù)據(jù)庫基礎(chǔ)操作例21 創(chuàng)建jxgl數(shù)據(jù)庫。解：mysqlcreate database jxgl;例22 查看本機服務(wù)器上數(shù)據(jù)庫。解：mysqlshow databases;或：mysqlshow databases like my%;例23 進入jxgl數(shù)據(jù)庫。 解：mysql USE jxgl;例24 在命令行環(huán)境中，創(chuàng)建和刪除數(shù)據(jù)庫jxgl：解：創(chuàng)建數(shù)據(jù)庫：C: mysqladmin -h localhost -u root -p create jxgl刪除數(shù)據(jù)庫：C: mysqladmin -h lo

3、calhost -u root -p drop jxgl實驗3 表、ER圖、索引與視圖的基礎(chǔ)操作CREATE TABLE IF NOT EXISTS jxgl.sc( sno CHAR(7) NOT NULL , cno CHAR(2) NOT NULL , grade INT NULL , PRIMARY KEY (sno,cno),INDEX sc_ibfk_1(sno ASC),INDEX sc_ibfk_2(cno ASC) , CONSTRAINT sc_ibfk_1 FOREIGN KEY(sno) REFERENCES jxgl. student(sno) ON DELETE R

4、ESTRICT ON UPDATE RESTRICT, CONSTRAINT sc_ibfk_2 FOREIGN KEY(cno) REFERENCES jxgl.course (cno) ON DELETE RESTRICT ON UPDATE RESTRICT) ENGINE = InnoDB;例31 列出jxgl數(shù)據(jù)庫中所有表。解：mysql use jxgl;mysql show tables;或：C:mysqlshow -h localhost -u root -p jxgl例32 列出jxgl數(shù)據(jù)庫中表student的列。解：mysql use jxgl;mysql show co

5、lumns from student;或：mysql show columns from jxgl.student;或：C:mysqlshow -h localhost -u root -p jxgl student例33 列出jxgl數(shù)據(jù)庫中表的詳細信息。解：mysql use jxgl;mysql show table status;或：C:mysqlshow -status -h localhost -u root -p jxgl例34 列出jxgl數(shù)據(jù)庫中表sc的索引。解：mysql use jxgl;mysql show index from sc;或：mysql show inde

6、x from jxgl.sc;例35使用SQL語句創(chuàng)建示例數(shù)據(jù)庫（jxgl）：其中，學(xué)生表要求學(xué)號為主鍵，性別默認為男，取值必須為男或女，年齡取值在15到45之間。課程表（course）要求主鍵為課程編號，外鍵為先修課號，參照課程表的主鍵（cno）。選修表（sc）要求主鍵為（學(xué)號，課程編號），學(xué)號為外鍵，參照學(xué)生表中的學(xué)號，課程編號為外鍵，參照課程表中的課程編號；成績不為空時必須在0到100之間。解：Create Table Student( Sno CHAR(7) NOT NULL ,Sname VARCHAR(16),Ssex CHAR(2) DEFAULT 男 CHECK (Ssex=男

7、 OR Ssex=女),Sage SMALLINT CHECK(Sage=15 AND Sageuse jxgl -先選擇jxgl數(shù)據(jù)庫為當前數(shù)據(jù)庫 Database changedmysql create view v(sname,cname, grade) as select sname,cname,grade from student,course,sc - where student.sno=sc.sno and o=o;例311 顯示數(shù)據(jù)庫jxgl中視圖v創(chuàng)建的信息。解：mysql SHOW CREATE VIEW v;實驗4 SQL語言SELECT查詢

8、操作例41 查詢考試成績大于等于90的學(xué)生學(xué)號。解：SELECT DISTINCT SNOFROM SCWHERE GRADE=90;例42 查年齡大于18，并且不是信息系（IS）與數(shù)學(xué)系（MA）的學(xué)生姓名和性別。解：SELECT SNAME, SSEXFROM STUDENT WHERE SAGE18 AND SDEPT NOT IN (IS, MA);例43 查以“MIS_”開頭，且倒數(shù)第二個漢字為“導(dǎo)”字的課程的詳細信息。解：SELECT * FROM COURSE WHERE CNAME LIKE MIS#_%導(dǎo)_ ESCAPE #;例44 查詢選修計算機系（CS）選修了2門及以上課程

9、的學(xué)生學(xué)號。解：SELECT STUDENT.SNOFROM STUDENT, SCWHERE SDEPT=CS AND STUDENT.SNO=SC.SNOGROUP BY STUDENT.SNO HAVING COUNT(*)=2;例45 查詢student表與sc表的廣義笛卡爾積。解：SELECT STUDENT.*, SC.*FROM STUDENT CROSS JOIN SC;例46 查詢student表與sc表基于學(xué)號sno的等值連接。解：SELECT * FROM STUDENT, SC WHERE STUDENT.SNO=SC.SNO;例47 查詢student表與sc表基于學(xué)

10、號sno的自然連接。解：SELECT STUDENT.*, SC.CNO, SC.GRADEFROM STUDENT, SCWHERE STUDENT.SNO=SC.SNO;例48 查詢課程號的間接先修課程號。解：SELECT FIRST.CNO, SECOND.CNOFROM COURSE FIRST, COURSE SECONDWHERE FIRST.CPNO=SECOND.CNO;例49 查詢學(xué)生及其課程、成績等情況（不管是否選課，均需列出學(xué)生信息）。解：SELECT STUDENT.SNO, SNAME, SSEX, SAGE, SDEPT, CNO, GRADEFROM STUDEN

11、T LEFT OUTER JOIN SC ON STUDENT.SNO=SC.SNO;例410 查詢學(xué)生及其課程成績與課程及其學(xué)生選修成績的明細情況（要求學(xué)生與課程均全部列出）。解：SELECT STUDENT.SNO, SNAME, SSEX, SAGE, SDEPT, COURSE.CNO, GRADE, CNAME, CPNO, CCREDIT FROM STUDENT LEFT OUTER JOIN SCON STUDENT.SNO=SC.SNO FULL OUTER JOIN COURSE ON SC.CNO=COURSE.CNO; 說明：因MySQL不支持“FULL OUTER J

12、OIN”，為此上命令運行會出錯的?？梢园选癋ULL OUTER JOIN”用“LEFT OUTER JOIN UNION RIGHT OUTER JOIN”來變通實現(xiàn)，為此，查詢命令可改為：SELECT a.SNO, a.SNAME, a.SSEX, a.SAGE, a.SDEPT, C.CNO, b.GRADE, c.CNAME, c.CPNO, c.CREDIT FROM STUDENT a LEFT OUTER JOIN SC b ON a.SNO=b.SNO LEFT OUTER JOIN COURSE c ON b.CNO=C.CNO UNION SELECT a2.SNO, a2.

13、SNAME, a2.SSEX, a2.SAGE, a2.SDEPT, c2.CNO, b2.GRADE, c2.CNAME, c2.CPNO, c2.CREDIT FROM STUDENT a2 LEFT OUTER JOIN SC b2 ON a2.SNO=b2.SNO RIGHT OUTER JOIN COURSE c2 ON b2.CNO=C2.CNO;例411 查詢性別為男、課程成績及格的學(xué)生信息及課程號、成績。解：SELECT STUDENT.* , CNO, GRADEFROM STUDENT INNER JOIN SC ON STUDENT.SNO=SC.SNOWHERE SSE

14、X=男 AND GRADE=60;例412 查詢與“錢橫”在同一系學(xué)習(xí)的學(xué)生信息。解：SELECT * FROM STUDENTWHERE SDEPT IN (SELECT SDEPT FROM STUDENT WHERE SNAME=錢橫)；例413 找出同系、同年齡、同性別的學(xué)生。解：SELECT T.* FROM STUDENT AS TWHERE (T.sdept, T.SAGE, T.SSEX) IN( SELECT SDEPT, SAGE, SSEXFROM STUDENT AS SWHERE S.SNOT.SNO);例414 查詢選修了課程名為“數(shù)據(jù)庫系統(tǒng)”的學(xué)生學(xué)號，姓名和所在

15、系。解：SELECT SNO, SNAME, SDEPT FROM STUDENT WHERE SNO IN ( SELECT SNO FROM SCWHERE CNO IN (SELECT CNO FROM COURSE WHERE CNAME=數(shù)據(jù)庫系統(tǒng));或SELECT STUDENT.SNO, SNAME, SDEPTFROM STUDENT INNER JOIN SC ON STUDENT.SNO=SC.SNOINNER JOIN COURSE ON SC.CNO=COURSE.CNO; 例415 檢索至少不學(xué)2和4課程的學(xué)生學(xué)號和姓名。解：SELECT SNO, SNAME FRO

16、M STUDENTWHERE SNO NOT IN (SELECT SNO FROM SC WHERE CNO IN (2, 4);例416 查詢其他系中比信息系IS所有學(xué)生年齡均大的學(xué)生名單，并排序輸出。解：SELECT SNAME FROM STUDENTWHERE SAGEALL(SELECT SAGE FROM STUDENT WHERE SDEPT=IS) AND SDEPTISORDER BY SNAME;例417 查詢選修了全部課程的學(xué)生姓名（為了有查詢結(jié)果，自己可以調(diào)整表的內(nèi)容）。解：SELECT SNAME FROM STUDENTWHERE NOT EXISTS( SELE

17、CT * FROM COURSEWHERE NOT EXISTS( SELECT * FROM SC WHERE SNO=SC.SNO AND CNO=COURSE.CNO);例418 查詢至少選修了學(xué)生“”選修的全部課程的學(xué)生號碼。解：SELECT SNO FROM STUDENT SXWHERE NOT EXISTS( SELECT * FROM SC SCYWHERE SCY.SNO= AND NOT EXISTS( SELECT * FROM SC SCZWHERE SCZ.SNO=SX.SNO AND SCZ.CNO=SCY.CNO);例419 查詢平均成績大于85分的學(xué)號，姓名和平

18、均成績。解：SELECT STUDENT.SNO, SNAME, AVG(GRADE)FROM STUDENT, SCWHERE STUDENT.SNO=SC.SNOGROUP BY STUDENT.SNO, SNAME HAVING AVG(GRADE)85;實驗5 SQL語言數(shù)據(jù)更新操作例51 向jxgl數(shù)據(jù)庫中表student添加數(shù)據(jù) (,李濤,男,19,IS)。解：mysql use jxgl;mysql insert into student values (,李濤,男,19,IS);或：mysqlinsert into student set sno=, sname=李濤,ssex

19、=男,sage=19,sdept=IS;例52 向jxgl數(shù)據(jù)庫中表student添加數(shù)據(jù)(,陳高,女,21,AT),(,張杰,男,17,AT)。解：Mysqlinsert into student values (,陳高,女,21,AT),(,張杰,男,17,AT);例53 在數(shù)據(jù)庫中先創(chuàng)建表：tbl_name1（sn,sex,dept），現(xiàn)從student表把數(shù)據(jù)轉(zhuǎn)入tb1_name1。解：mysqlcreate table tbl_name1(sn,sex,dept) select sname sn,ssex sex,sdept dept from where 1=2; -先創(chuàng)建表tbl

20、_name1；mysqlinsert into tbl_name1(sn,sex,dept) select sname,ssex,sdept from student;例54 向jxgl數(shù)據(jù)庫中表sc添加數(shù)據(jù)(,5,80)。解：mysqlreplace sc values (,5,80);注意這些規(guī)則意味著一個像“./myfile.txt”給出的文件是從服務(wù)器的數(shù)據(jù)目錄讀取，而作為“myfile.txt”給出的一個文件是從當前數(shù)據(jù)庫的數(shù)據(jù)庫目錄下讀取。也要注意，對于下列那些語句，對db1文件從數(shù)據(jù)庫目錄讀取，而不是db2： mysql USE db1;mysql LOAD DATA INFIL

21、E ./data.txt INTO TABLE db2.my_table; 例55 在student表中，我們發(fā)現(xiàn)陳高的性別沒有指定，因此我們可以這樣修改這個記錄。解：mysql update student set ssex=女 where sname=陳高;例56 在sc表中，刪除陳高選修課程信息。解：mysql delete from sc where sno=(select sno from student where sname=陳高);例57 刪除所有學(xué)生選課記錄解：mysqldelete from sc;實驗6 嵌入式SQL應(yīng)用表名與屬性名對應(yīng)由英文表示，則關(guān)系模式為：1) st

22、udent(sno、sname、ssex、sage、sdept)2) course(cno、cname、cpno、ccredit)3) sc(sno、cno、grade)4) users(uno、uname、upassword、uclass)創(chuàng)建數(shù)據(jù)庫及其表結(jié)構(gòu)的SQL命令：CREATE TABLE student ( sno char(5) NOT null primary key, sname char(6) null ,ssex char(2) null ,sage int null ,sdept char(2) null) ENGINE = MyISAM/InnoDB;-MyISAM/

23、InnoDB選其一CREATE TABLE sc (sno char(5) NOT null,cno char(1) NOT null,grade int null,primary key(sno,cno),foreign key(sno) references student(sno),foreign key(cno) references course(cno) ENGINE = MyISAM/InnoDB;CREATE TABLE course (cno char(1) NOT null primary key,cname char(10) null ,cpno char(1) null

24、 ,ccredit int null) ENGINE = MyISAM/InnoDB;CREATE TABLE users(uno char(6) NOT NULL PRIMARY KEY,uname VARCHAR(10) NOT NULL,upassword VARCHAR(10) NULL,uclass char(1) DEFAULT A) ENGINE = MyISAM/InnoDB;三、MS-DOS窗口中編譯、連接與運行利用VC+6.0 C編譯器直接在MS-DOS窗口中編譯、連接與運行，也是簡單便捷的方法。設(shè)VC+6.0 C編譯器相關(guān)文件（如BIN含可執(zhí)行程序，INCLUDE含頭文件

25、，LIB含庫文件）放在C:VC98目錄中。可以把C語言源程序（如CC.C）放在某目錄中如C:esqlc-mysql。(1)啟動“MS-DOS”窗口，執(zhí)行如下命令，使當前盤為C，當前目錄為esqlc-mysqlC:cdesqlc-mysql(2)設(shè)置系統(tǒng)環(huán)境變量值，執(zhí)行如下批處理命令：setenv-mysql(3)編譯、連接嵌入SQL的C語言程序（例如：CC.C）,執(zhí)行如下批處理命令(有語法語義錯時可修改后重新運行)：run-mysql CC(4)運行生成的應(yīng)用程序（CC.exe），輸入程序名即可：（如圖6-18所示）CC說明：(a)嵌入SQL的C語言程序的可用任意文本編輯器進行編輯修改（如記事

26、本、WORD等）。(b)你的數(shù)據(jù)庫中應(yīng)有student、sc、course等所需的表（或通過嵌入SQL C語言運行時執(zhí)行創(chuàng)建功能）。(c)你需要有VC+6.0的C程序編譯器cl.exe及相關(guān)的動態(tài)連接庫與庫文件等。(d) setenv-mysql.bat文件內(nèi)容（根據(jù)VC+6.0安裝目錄及MySQL安裝目錄需做相應(yīng)修改的）：echo offecho Use SETENV to set up the appropriate environment forecho building Embedded SQL for C programsset path=C:Program FilesMySQLMy

27、SQL Server 5.5bin;c:vc98binset INCLUDE=C:Program FilesMySQLMySQL Server 5.1Include;c:VC98Include;%include%set LIB=C:Program FilesMySQLMySQL Server 5.5libdebug;c:VC98Lib;%lib%(e)嵌入SQL的C語言程序編譯環(huán)境要求(即SETENV-mysql.BAT文件內(nèi)容)：需VC安裝目錄下的bin、include、lib子目錄；MySQL安裝后子目錄binn、include、libdebug等。為此SETENV-mysql.BAT文件

28、目錄情況應(yīng)按照實際目錄情況調(diào)整。(f) run-mysql.bat文件內(nèi)容為：cl /c /W3 /D_x86_ /Zi /od /D_DEBUG %1.c link /NOD /subsystem:console /debug:full /debugtype:cv %1.obj kernel32.lib libcmt.lib libmysql.lib說明：%1.c代表C源程序，連接中用到的庫文件在VC安裝子目錄及MySQL安裝子目錄中能找到。(g)以上實驗的運行環(huán)境為Windows XP+ MySQL 5.5.9+VC+6.0，在其它環(huán)境下批處理文件內(nèi)容應(yīng)有變動，編譯、連接、運行中可能要用到

29、動態(tài)連接庫文件如：mspdb60.dll、sqlakw32.dll、libmysql.dll等（需要時復(fù)制它們到編譯、運行環(huán)境中去）。要說明的是：解決漢子顯示問題，C源程序中如下命令相關(guān)的：mysql_query(&mysql,SET NAMES latin1;); /支持處理漢字SET NAMES GBK|Gb2312|utf8|latin1; 可根據(jù)具體要求選擇不同字符集以支持漢字的顯示。實驗7 數(shù)據(jù)庫存儲和優(yōu)化例71 多表連接查詢分析，及其改進。解：mysqlEXPLAIN SELECT student.sname, ame ,grade From student,co

30、urse,sc WHERE student.sno=sc.sno and o=o and sdept=cs;在教學(xué)管理系統(tǒng)（jxgl）中，創(chuàng)建表test，并插入8萬條記錄。在mysql命令行提示符下錄入如下程序并運行之。/*創(chuàng)建表*/Create table test(id int unique AUTO_INCREMENT,rg datetime null,srq varchar(20) null,hh smallint null, mm smallint null, ss smallint null,num numeric(12,3),primary key(i

31、d) AUTO_INCREMENT = 1 engine = MyISAM;/*創(chuàng)建存儲過程生成表中數(shù)據(jù)*/DELIMITER /CREATE PROCEDURE p1()beginset i=1;WHILE i delimiter /mysql CREATE PROCEDURE simpleproc(OUT param1 INT)-BEGIN- SELECT COUNT(*) INTO param1 FROM student;-END/Query OK, 0 rows affected (0.00 sec)例82創(chuàng)建帶輸入?yún)?shù)的存儲過程，根據(jù)學(xué)生學(xué)號（sno）查詢該學(xué)生所學(xué)課程的課程編號（c

32、no）和成績（grade）。解：mysql delimiter /mysqlCREATE PROCEDURE proc_sc_findById(in n int)-BEGIN- SELECT sno,cno,grade FROM sc where sno=n;-END/例83 刪除Error! Reference source not found.創(chuàng)建的存儲過程。解：mysqldrop PROCEDURE IF EXISTS proc_sc_findById;例84 查看Error! Reference source not found.創(chuàng)建的存儲過程。解：mysqlshow create P

33、ROCEDURE simpleproc;例85 查看在jxgl中創(chuàng)建的所有存儲過程。解：mysqlshow PROCEDURE status;例86 調(diào)用在Error! Reference source not found.中創(chuàng)建的simpleproc存儲過程（帶輸出參數(shù)）。解：mysqlcall simpleproc(count);實驗9 觸發(fā)器的基本操作例如，下述語句將創(chuàng)建1個表和1個INSERT觸發(fā)程序。觸發(fā)程序?qū)⒉迦氡碇心骋涣械闹导釉谝黄穑篊REATE TABLE account (acct_num INT, amount DECIMAL(10,2);CREATE TRIGGER in

34、s_sum BEFORE INSERT ON accountFOR EACH ROW SET sum = sum + NEW.amount;要測試觸發(fā)器的執(zhí)行情況，可以運行如下代碼：set sum=0;insert into account values(1,100.1);select sum;例91在表sc上定義1個UPDATE觸發(fā)程序，用于檢查更新每一行時，grade位于0100的范圍內(nèi)，否則回退。解：mysql delimiter /mysql CREATE TRIGGER upd_check BEFORE UPDATE ON sc-FOR EACH ROW-BEGIN- IF NEW.

35、grade 100 THEN- Set NEW.grade=OLD.grade;- END IF;-END;/mysql delimiter ;調(diào)用觸發(fā)器：Mysqlupdate sc set grade=110 where sno= and cno=1;實驗10 數(shù)據(jù)庫安全性例101 在MySQL數(shù)據(jù)庫中新建用戶“dba”，密碼為：“sqlstudy”。解：mysqlCREATE USER dba IDENTIFIED BY sqlstudy; 例102把用戶dba改名為hello。mysqlrename user dba to hello;例103把用戶hello的密碼改為1234。mys

36、qlset password for hello = password(1234);例104刪除 MySQL 數(shù)據(jù)庫用戶hello，也最好顯式指定 hostname。mysqldrop user hello;等價于：drop user hello%例105 顯示一個用戶admin的權(quán)限：mysqlSHOW GRANTS FOR adminlocalhost;其顯示結(jié)果為當時創(chuàng)建該用戶的GRANT授權(quán)語句：GRANT RELOAD, SHUTDOWN, PROCESS ON *.* TO adminlocalhost IDENTIFIED BY PASSWORD 28e89ebc62d6e19a

37、上面命令中密碼是加密后的形式。例106 先把數(shù)據(jù)庫jxgl的所有權(quán)限授予給用戶kitelocalhost，接著再把權(quán)限從用戶kitelocalhost處收回。解：授權(quán)：mysqlGRANT ALL ON jxgl.* TO kitelocalhost IDENTIFIED BY ruby;刪除數(shù)據(jù)庫授權(quán)：mysqlREVOKE ALL ON jxgl.* FROM kitelocalhost;但是，kitelocalhost用戶仍舊留在user表中，可以查看：mysqlSELECT * FROM mysql.user;例107 將jxgl數(shù)據(jù)庫的變更權(quán)限賦給def用戶，并顯示所授權(quán)限。解：授權(quán)

38、語句如下：mysql GRANT ALTER ON jxgl.* TO deflocalhost;進入test數(shù)據(jù)庫，顯示授權(quán)信息：Mysql SHOW GRANTS FOR deflocalhost;例108 將jxgl數(shù)據(jù)庫的刪除表結(jié)構(gòu)權(quán)限賦給def用戶，并顯示所授權(quán)限。解：mysql USE jxgl;Database changedmysql GRANT DROP ON * TO deflocalhost;mysql SHOW GRANTS FOR deflocalhost;例109 將jxgl數(shù)據(jù)庫的創(chuàng)建表權(quán)限賦給def和abc用戶，并顯示所授權(quán)限。解：mysql grant cre

39、ate on jxgl.* to abclocalhost,deflocalhost;mysql SHOW GRANTS FOR deflocalhost;mysql SHOW GRANTS FOR abclocalhost;例1010 把在jxgl數(shù)據(jù)庫的sc表上建立索引權(quán)限授權(quán)給abc用戶。解：mysql GRANT INDEX ON jxgl.sc TO abclocalhost;例1011 把在student表的sno和sname的選擇權(quán)限賦給abc用戶。解：mysql GRANT SELECT(sno,sname) ON jxgl.student TO abclocalhost ;m

40、ysql SHOW GRANTS FOR abclocalhost;例1012 把在jxgl數(shù)據(jù)庫執(zhí)行存儲過程權(quán)限賦給abc用戶。解：mysql GRANT EXECUTE ON jxgl.* to abclocalhost;mysql grant all on test.t2 to abc;Query OK, 0 rows affected (0.00 sec)mysql grant all on perf.* to abc;Query OK, 0 rows affected (0.00 sec)mysql show grants for abc;實驗11 數(shù)據(jù)庫完整性例如：多列CHECK約

41、束可以用來約束性別與年齡的關(guān)系，命令如下：Create Table Student( Sno CHAR(7) NOT NULL ,Sname VARCHAR(16),Ssex CHAR(2) DEFAULT 男 CHECK (Ssex=男 OR Ssex=女),Sage SMALLINT CHECK(Sage=15 AND Sage=45),Sdept CHAR(2),PRIMARY KEY(Sno),CONSTRAINT CHK_SEX_AGE CHECK(SSEX=男 AND SAGE=50 OR (SSEX=女 AND SAGE=0 or igrade delimiter /mysql

42、START TRANSACTION;-SELECT A:=SUM(grade) FROM sc WHERE sno=;-UPDATE sc SET grade=40 WHERE sno= and cno=1;-COMMIT;/例122 帶保存點的事務(wù)示例。解：mysqlselect * from sc where sno= and (cno=1 or cno=2);mysql delimiter /mysql START TRANSACTION;UPDATE sc SET grade = 100 WHERE sno = and cno=1;SAVEPOINT c1_sal;UPDATE sc

43、SET grade = 20 WHERE sno = and cno=2;SAVEPOINT c2_sal;SELECT SUM(grade) FROM sc where sno= ;ROLLBACK ; UPDATE sc SET grade = 80 WHERE sno = and cno=2;ROLLBACK TO SAVEPOINT c2_sal; COMMIT;/mysql delimiter ;mysqlselect * from sc where sno= and (cno=1 or cno=2);CREATE DEFINER=rootlocalhost PROCEDURE mo

44、di_a()BEGIN Set i=200; WHILE i 0 DO SET i = i - 1; start transaction; set grade =NULL; select grade:=grade from sc where sno= and cno=1; update sc set grade=grade + 1 where sno = and cno=1; select * from sc where sno= and cno=1; commit; END WHILE; ENDCREATE DEFINER=rootlocalhost PROCEDURE modi_m()BEGIN Set i=200; WHILE i 0 DO SET i = i - 1; start transaction; set grade =NULL; select grade:=grade from sc where sno= and cno=1; update sc set grade=grade-1 where sno = and cno=1; select * fr