广东工业大学SQL编程报告4.docx

《广东工业大学SQL编程报告4.docx》由会员分享，可在线阅读，更多相关《广东工业大学SQL编程报告4.docx（9页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、大作业名称：数据库的临时表、视图、存储过程和触发器一、 目的与要求使用SQL语言操作数据库的临时表、视图、存储过程和触发器。必须给出完整的SQL源码，并以截图的形式表现出SQL语句的运行是否成功。二、SQL源码及运行结果1、临时表1.1 查询哪个类别的书出借最多？1. -通过创建临时表及通过与SELECT语句相结合，实现查询哪个类别的书出借最多？2. CREATE TEMPORARY TABLE table_max_borrow_category AS(SELECT category3. FROM book, borrowWHERE book.bno = borrow.bno4. GROUP

2、BY categoryHAVING count(*) = ALL(5. SELECT count(*) FROM book,borrow WHERE book.bno = borrow.bnoGROUP BY category);6. SELECT * FROM table_max_borrow_category;9 table_max_borrow_category XSELECT * FROM tablemaxborrowcategory中（9）输入以稣/卜Jx/ Q category eng.2、视图2.1 查询哪个类别的书出借最多？1. -通过创建视图及通过与SELECT语句相结合，实

3、现查询哪个类别的书出借最多？2. CREATE VIEW view_max_borrow_category AS（SELECT category3. FROM book, borrowWHERE book.bno = borrow.bno4. GROUP BY categoryHAVING count(*) = ALL(5. SELECT count(*) FROM book,borrow WHERE book.bno = borrow.bnoGROUP BY category);6. SELECT * FROM view_max_borrow_category;S view_max_borr

4、ow_category XSELECT * FROM viewmaxborrowcategory. c Free 1中(9)输入以窿姗可 category varchar(10) 1 eng.3、存储过程3. 1利用已有的Book、Card、Borrow表，创建一个带参数的存储过程 sp_BorrBooko该存储过程的作用是：当输入基本表Card中的任意一个“姓名”时，将 从三个表中返回该借阅者的借书证号、借阅的书名、借书时间和还书时间。1. -利用已有的Book、Card、Borrow表，创建一个带参数的存储过程sp_BorrBook。2. -该存储过程的作用是：当输入基本表Card中的任意

5、一个“姓名”时，将从三个表中返回该借阅者 的借书证号、借阅的书名、借书时间和还书时间。3. DELIMITER /CREATE PROCEDURE sp_BorrBook(IN sp_pname CHAR(8), OUT sp_title VARCHAR(40), OU T sp_borrow_date DATE, OUT sp_return_date DATE)4. BEGINSELECT card.eno, book.title, borrow.borrow_dateJ borrow.return_date5. FROM card, book, borrowWHERE card.eno =

6、 o6. AND book.bno = borrow.bnoAND pname = sp_pname;7. END/112. DELIMITER ;3. 2执行sp_BorrBook存储过程,查询“Zhang Qi”的借书证号、借阅的书名和 借阅时间。1. -执行sp_BorrBook存储过程，查询“Zhang Qi”的借书证号、借阅的书名和借阅时间。(课内完 成)SET pname = Zhang Qi;2. CALL sp_BorrBook(pname, title, borrow_date, return_date);S3 view_max_borrow_category XCALL s

7、p BorrBook(pname,borrow date, return date)中白输入以搜索数=1个y Q eno 今 title 今 borrow_date ， return_date :1T003Database syst2022-05-152022-08-042T003Operation sys2022-03-152022-04-043T003Advanced ma2022-02-152022-03-044T003Discrete math2021-02-152021-03-043. 3查看存储过程sp_BorrBook的代码信息。1 .-查看存储过程sp_BorrBook的代码信

8、息. SHOW CREATE PROCEDURE sp_BorrBook；s asSHOW CREATE PROCEDURE $p_Borr8ook4* 合场入lUttKttJB J)O 1=)个。0 徊:1ms 共1 条 Q Procedure # sql.mode 。Create Procedure今 character.si。collation.conncction。 Database Collation 今1 sp.Borrfiook IGNORE.SPACE.ONLY.R CREATE DFINER=*root localhost PROCEDURE sp.BorrC utf8mb4

9、 utf8mb4.unicode.ci utf8mb4_0900_ai.d3.4创建一个存储过程“sp_jlnxs”。当执行该存储过程时，将返回Card表中“T” 类型借阅者的所有信息。1. 一创建一个存储过程“sp_jmxs”。当执行该存储过程时，将返回Card表中“T”类型借阅者的所有信 息(2.DELIMITER /3. CREATE PROCEDURE sp_jmxs(OUT sp_cno CHAR(7), OUT sp_pname CHAR(8), OUT sp_depa rtment VARCHAR(5), OUT sp_ctype CHAR(l)BEGIN4. SELECT *

10、FROM cardWHERE card.ctype = T;5. END/DELIMITER ;3.5执行sp_jinxs存储过程,查看“T”类型的借阅者的情况。(可以课外完成). CALL sp_jmxs(sp_cno, sp_pname, sp_department, sp_ctype);E3数据CALL sp_jmxs(gsp eno, gsppname, gspdepartment, sp_ctype);DROP TRIGGER IF EXISTS CardBorrow；+ (?)输入 L燎左娓.C ?=)Qeno 今pname 今department 今 ctype 今T001Li

11、YuanautoTT002Wang LiautoTT003Zhang QicsT4T004Zhao GaomathT3. 6删除sp_jmxs存储过程。1. 一删除sp_jmxs存储过程2. DROP PROCEDURE sp_jmxs；鎏数据DROPPROCEDURE spjmxs台输入以搜索数据,JXDROP PROCEDURE spjmxsAffected Rows: 04、触发器2.1 创建一个触发器，如果某个人的借书证被取消了，在删除其个人信息的同时， 也删除他的借阅记录。1 .-创建一个触发器，如果某个人的借书证被取消了，在删除其个人信息的同时，也删除他的借阅记 录。3 . DEL

12、IMITER .CREATE TRIGGER tr_del_borrow4 4.BEFORE DELETE ON CardFOR EACH ROW5. BEGINDELETE FROM borrow6. WHERE eno = OLD.eno;END 7. DELIMITER ;8. -验证：删除F001的借书证DELETE FROM Card WHERE eno = F001；删除F001的借书证前:台输入以疫案政浜Q G char(7)彳C向3国个 J 0 崩t： 3ms 丽J： 2ms M 19 8., boo - bonow.date retum.date .char(7) char(

13、8) datedate1F001b00012022-08-122022 08-152F001b00022022-02-122022 03 013F001bOOOJ2022gl22022 07-114F002b00092022-08-20(NULL)5G002bOOOS2022-05-052022-07-046G002b00072022-08-10(NULL)1G002b00102022-07-10(NULL)81001bOOOI2022-01-102022 03-0491001b00022022-05-102022 08 01T001bOOOS2022-01-012O22-O3(M111001

14、bOOlO2022-01-012022-03-0412T003bOOOI2022-05-152022 08 0413T003b(X)022022-03-152022 04 04141003b00032022-02-152022-03 0415T003WXKM2021)2-152021 03 0416U001bOOOfi2022-02-022022 03 0917U001b00092022-02-022022 03-09删除F001的借书证后:&N 1 o0向图个。0 朝：5ms1共9条Q，eno char(7) pname char(8),department , vdrchar(S) cty

15、pc o char(1)iF002Li AigustuF2G001Wu MiautoGG002Qian qicsGT001 YuanautoTT002Wang LiautoTT003Zhang QicsTT004Zhao GaomathTU001Li FenautoUU002Bai laartU6输入以授余政据向因个。l的t:2ms1共16条Q eno char(7). bno char(8)一 borrow.date dateretucn_date . dateF002b00092022 08 20(NULL)G002b(XX)52022-05-052022-07 04G002b000720

16、22-08-10(NULL)4G002bOOlO2022-07-10(NULL)T001bOOOl2022-01-102022c394T001b00022022-05-102022-08-01T001b00052022-01-012022-03-04T001bOOlO2022-01-012022-03 04T003bOOOl2022-05-152022-08-0410T003b00022022-03-152022-04-0411TOO?bOOOJ2022 02-152022 03 0412T003b(XXM2021-02-152021-03-04U001b,* pub.yeac year楸寸：

17、12ms- author char(20)共10条 price , decimal Q bno char(8)b0001eng.Database systemsSpringer2016John28.00b0002eng.Operation systemsTaykx2018Michael35.00b0003sci.Advanced mathematicsMIT2021Catherine15.00b0004sci.Discrete mathematicsMIT2020Peter22.00bOOOSeng.C languageSpringer2017Shannon40.00b0006liter.Ha

18、ny potterElseivef2019Lisa19.00b0007liter.Star warsElseiver2018Jim2S.00b0008his.Modern Chinese histotyTaykx2017Frank32.00b0009his.The second world warTayicw2021Mark32.00bOOlOeng.JAVA languageMIT2022Sally38.00插入后:M以搜索政据 category varchar(IO) tide.varchar(40)一血国publisher varchar(30)个J t pub./ear year4ms

19、 - author . () 1共10条Q bno char(8)* price decimal bOOOIeng.Database systemsSpringer2020John28.00b0002eng.Operation systemsTaylor2018Michael3SXX)b0003sd.Advanced mathematicsMIT2021Catherine15.00b0004sd.Discrete mathematicsMIT2020Peter22.00b0005eng.C languageSpringer2017Shannon40.00b0006liter.Harry pot

20、terElseiver2019Lisa19.00b0007liter.Star warsElseiver2018Jim25.00b0008hts.Modem Chinese histexyTaylor2017Frank328b0009his.The second world warTaylor2021Mark32.00bOOlOeng.JAVA languageMIT2022Sally3&004.3创建一个触发器，当借书证的类型修改为F，时，自动删除其原有的借书记录。1. 一创建一个触发器，当借书证的类型修改为吓，时，自动删除其原有的借书记录DELIMITER 2. CREATE TRIGGE

21、R tr_del_recordingAFTER UPDATE ON card3. FOR EACH ROWBEGIN4. IF NEW.ctype = F THENDELETE FROM borrow WHERE o = NEW.eno;5. END IF；END 6. DELIMITER;7. 一验证:修改G002的借书证类型为FUPDATE card8. SET ctype = FWHERE eno = G002；修改前:80人以际政娼6向国个1O3ms共20条Q* enoa - bno borrow.date -retum.datechar(7) char(8)datedateF001b

22、OOOI2022-08-122022-08-15F001b00022022-02-122022-03-01F001b00032022-06-122022-07-11F002b00012022-09-20(NULL)F002b00092022-08-20(NULL)G002bOOOS2022-05-052022-07-04G002b00072022-08-10(NULL)G002bOOlO2022 07-10(NULL)T001b00012022-01-102022-03 04T001b00022022-05-102022-0801111001bOOOS2022-01-012022-03-04T

23、001bOOlO2022 01 012022 03 0413T003b00012022-05-152022 08 0414T003b00022022-03-152022-04 041ST003b00032022-02-152022-03-04T003b00042021 02 152021-03 04修改后:&收入以搜案姆8 bno char(d)J ! O o P因个J 0耗时:1ms 共17条Q-eno char borrow.datedate一return.date dateF001bOOOI2022-08-122022-08-15F001b00022022-02-122022-03 01

24、F001b00032022-06-122022-07-114F002bOOOI2022-09-20(NULL)5F002b00092022-08-20(NULL)6T001bOOOI2022-01-102022-03-04T001b00022022-05-102022-08 01T001bOOOS2022-01-012022-03 049T001T003b0010bOOOI2022-01-012022-05-152022-03-042022-08-0411T003b00022022 03-152022 (M 0412T003b00032022-02-152022-03-04T003U001b(

25、XXMb00082021 02-152022-02-022021-03 042022-03-0915U001U002b0009b00032022-02-022022-03-062022-03-092022-03-09U002bO0042022-04 022022-05 09三、思考题1、分析比较临时表和视图的特点。视图只存在于单个查询当中，每次使用该视图名，其虚拟表就会根据现有的数据重 新被创建。临时表存在于它被创建的整个数据库会话过程中。只有那些符合视图可更新能力标准的视图才可用于数据修改。当使用视图进行更新 时，该更新会永久被传递至底层基表上。由于临时表都是基表，因此所有的临时表都是 可更

26、新的。不过，这些更新对于这些表来说是临时性的。由于视图的内容是在该视图每次被使用的时候才生成，因此视图当中的数据几乎每 次都是最新的。临时表当中的数据所反应的是数据库在该表被装载进数据库时的状态。 如果临时表所装载的源表中的数据在临时表检索这些数据之后发生了改变，那么临时表 当中的内容有可能与数据库当中的其他部分的数据脱离同步状态。2、分析比较视图和存储过程的特点。视图可以看做是一张表，包含了一张表的部分数据或者多个表的综合数据，视图的 使用和普通表一样；存储过程是事先存储好的SQL语句，放在数据库端，需要使用时直接调用存储过程 就可以执行相应的SQL语句。存储过程可带参数，也可返回结果。视图仅仅用来查询，而存储过程可以用来增删改查。视图是数据库的一张虚拟表，可以像表一样使用，而存储过程本质来说还是在执行 SQL语句。3、分析比较存储过程与触发滞的特点。存储过程是在大型数据库系统中，一组为了完成特定功能的SQL语句集，存储在数 据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并 给出参数（如果该存储过程带有参数）来执行它。触发器是特殊的存储过程，存储过程需要程序调用，而触发器会自动执行。它又不 同于存储过程，触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储 过程名字而被直接调用。