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

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

1、大作业名称：数据库的定义、建立与维护实验目的与要求使用SQL语言实现数据库的创建、删除；基本表的创建、删除、更新工作。必须给出完整的SQL源码，并以截图的形式表现出SQL语句的运行是否成功。二、SQL源码及运行结果1 .定义数据库.-创建数据库1 . CREATE DATABASE library_3119001063;I34 .-切换到 Library_3119001063 数据库 5,USE Library_3119001063;7 Q 127.0.0.1 3306 8.026 USER 0 library_3119001063 16k7 Q 127.0.0.1 3306 8.026 US

2、ER 0 library_3119001063 16kS Library_3119001063.sql .* Active Connection1-查看现有的数据库 ExecuteSHOW Databases;2 -创建数据库 Execute 5 CREATE DATABASE library_3119001063； 6CREATE DATABASE library_3119001063中Q输入以据回CREATE DATABASE library_3119001063Affected Rows: 1127.0.0.1 3306 8.026 USER 0 library_3119001063 1

3、6kS Library_3119001063.sql .5 CREATE DATABASE library_3119001063；67-切换到 Library_3119001063 数据库 Execute 8 USE Library_3119001063;9 一创建表 Execute11 CREATE TABLE bookUSE Library_3119001063中 白输入以搜索数据USE Library_3119001063Affected Rows: 02.定义下列数据库基本表-创建表1. CREATE TABLE book(bno CHAR(8) PRIMARY KEY NOT NUL

4、L,2. category VARCHAR(10) NOT NULL,title VARCHAR(40) NOT NULL,3. publisher VARCHAR(30) NULL,pub_year YEAR NOT NULL,4. author CHAR(20),price DEC工MAL(5,2) NOT NULL 10)；11.12. CREATETABLE card13. (eno CHAR(7) PRIMARY KEY NOT NULL,14. pname CHAR(8) NOT NULL,15. .department VARCHAR(5),16. ctype char(l) N

5、OT NULL CHECK (ctype 工N(TJGJUJF)17. )；18.19. (eno CHAR(7) PRIMARY KEY NOT NULL,20. pname CHAR(8) NOT NULL,21. .department VARCHAR(5),22. ctype char(l) NOT NULL CHECK (ctype 工N(TJGJUJF)23. )；24.25. CREATE TABLE borrow26. (eno CHAR(7),27. bno CHAR(8),28. borrow_date DATE NOT NULL,29. return_date DATE,

6、24.CONSTRAINTC2PRIMARYKEY(cno,bno).25.CONSTRAINTC3FOREIGNKEY(cno)REFERENCEScard(cno),26.CONSTRAINTC4FOREIGNKEY(bno)REFERENCESbook(bno)27.); USERv 0 library_3119001063 16k t = tables (1)10 CO book =views query 照 procedures 。functionsii121314151617181920-创建表 ExecuteCREATE TABLE book(bno CHAR(8) PRIMAR

7、Y KEY NOT NULL,category VARCHAR(10) NOT NULL, title VARCHAR(40) NOT NULL, publisher VARCHAR(30), pub_year YEAR NOT NULL, author CHAR(20), price DECIMAL(5,2) NOT NULL);E3 book XSELECT * FROM book LIMIT 100；中 (9)睦数ES中 (9)睦数ES bno . category . char(8) varchar(10) 个J耗时:6ms 共。条author , price char(20) dec

8、imal author , price char(20) decimal , title . publisher . , pub_year -varchar(40) varchar(30) year V . 127.0.0.1 3306 8.026s Library_3119001063.sql .XOUI XCC 。二 LJMSLl Jj 4/ IMV NULL USER19);v0 library_311900106332k20 = tables (2)A Execute Q book21CREATETABLE card卬 card三口22(eno CHAR(7) PRIMARY KEY

9、NOT NULL,、_ . 123pname CHAR(8) NOT NULL,views24department VARCHAR(5), query25ctype char(l) NOT NULL CHECK (ctype IN(1,G,U,F) 电 procedures26); 0 functions27 Execute 28CREATE TABLE borrow?9(eno CHAR(7).由 cardXSELECT * FROM card LIMIT 100;4*白输入以赚数据由COG)向图个J 耗时:6ms 共。条C x eno . pname department ，ctype .

10、char(7) char(8) varchar(5) char(1) v . 127.0.0.1 3306 8.026S Library_3119001063.sql . USER- Executev 0 library_3119001063 48k/ 28 CREATE TABLE borrowy = tables 29(eno CHAR(7), 印 book阳bn。CHAR(8),. m .一 e31borrow date DATE NOT NULL. ULI borrow= L3上 一二132return date DATE,1 卬 card33CONSTRAINTC2PRIMARYKE

11、Y(cno,bno), =views34CONSTRAINTC3FOREIGNKEY(cno)REFERENCEScard(cno), query35CONSTRAINTC4FOREIGNKEY(bno)REFERENCESbook(bno) 每 procedures36)； 0 functions3738-插入数据-插入Book表数据E2 borrow XSELECT * FROM borrow LIMIT 100;中台愉入以翦嘘。白国个。0耗时:2ms共。条C * eno * bno * borrow_date return_date .char(7) char(8) datedate3.

12、数据输入jl.-插入数据-插入Book表数据INSERT INTO book VALUES(b0001, eng.1, database systems, Springer1, 2016, ,John 28);2. INSERT INTO book VALUES(b0002eng., Operation systemsTaylor, 2018, Michael, 35);INSERT INTO book VALUES(b0003, sci.1, Advanced mathematics, MIT, 2021, Catherine* 15);5.INSERT INTO bookVALUES(b0

13、004sci. , Discrete mathematicsMIT, 2020,Peter, 22);6. INSERT INTO booknon： 40);7.INSERT INTO bookLisa, 19);8. INSERT INTO book25)；9. INSERT INTO bookVALUES(* b0005 eng. , C language, Springer 2017, ShanVALUES(b0006,VALUES(b0007,VALUES(b0008,liter., Harry potter, Elseiver*, 2019,liter.Star wars, Else

14、iver1, 2018, Pimhis.Modern Chinese historyTaylor210. INSERT INTO book VALUES(1b0009,his. The second world war, Taylo21, Mark,32)；11. INSERT INTObook VALUES(50010,eng., JAVA language, MIT, 2022,38)；12.13.-插入数据-插入Card表数据14. INSERT INTOcard VALUES(,T001Li Yuan, auto, T1);15. INSERT INTOcard VALUES(T002

15、,Wang Li, auto, )；16. INSERT INTOcard VALUES(T003,Zhang Qi, cs, T);17. INSERT INTOcard VALUES(,T004,Zhao Gao math, ,)；18. INSERT INTOcard VALUES(16001,Wu Mi, auto, G);19. INSERT INTOcard VALUES(,G002Qian qi, cs, 6);20. INSERT INTOcard VALUESCUeei,Li Fen, auto, U);21. INSERT INTOcard VALUES(,U002,Bai

16、 La, art, U);22. INSERT INTOcard VALUES(F001Zhang Fa, pku F1);23. INSERT INTOcard VALUES(F002,Li Aigu, stu, 1F1);24.25.-插入数据-插入Borrow表数据26. INSERT INTOborrow VALUES(T001,b0001, 12022-01-10 12022-03-041)；27. INSERT INTOborrow VALUES(17001,1b00021, 12022-05-10, 12022-08-01,)；28. INSERT INTOborrow VALU

17、ES(T001,b0005， 2022-01-01, 12022-03-04);29. INSERT INTOborrow VALUES(T001,1b00101, 12022-01-01 2022-03-041)；30. INSERT INTOborrow VALUES(T003,b0001, 12022-05-15 12022-08-04*);31. INSERT INTOborrow VALUES(*T003,b0002, 2022-03-15, 12022-04-04);32. INSERT INTOborrow VALUES(*T003,b0003, 2022-02-15, 1202

18、2-03-041)；33. INSERT INTOborrow VALUES(,T003,b0004, 2021-02-15, 12021-03-04);34. INSERT INTOborrow VALUES(G002,b0005, 2022-05-05, 12022-07-04);35. INSERT INTOborrow VALUES(G002,b0007, ,2022-08-10, NULL);36. INSERT INTOborrow VALUES(G002,b0010, 2022-07-10, NULL)；37. INSERT INTOborrow VALUES(U001,b000

19、9, 2022-02-02, 2022-03-09,)；38. INSERT INTOborrow VALUES(U001,b0008, 2022-02-02, 12022-03-09,)；39. INSERT INTOborrow VALUES(*U002,b0003， 2022-03-06, 12022-03-091)；40. INSERT INTOborrow VALUES(U002,b0004, 2022-04-02, 2022-05-09,)；41. INSERT INTOborrow VALUES(F001,1b00011, ,2022-08-12, 2022-08-15);42.

20、 INSERT INTOborrow VALUES(F001,b0002: 2022-02-12, 12022-03-01,)；43. INSERT INTOborrow VALUES(1F001,b0003, 2022-06-12, 12022-07-11,)；017, Frank1, 32);Sally2044. INSERT INTO borrow VALUES(1F002, b0009, 2022-08-20, NULL);、127.0.0.1 3306 8.026 USER 0 library_3119001063 48k7 三 tables 2 Library_3119001063

21、.sql .3738-插入数据-插入Book表数据 Execute39 INSERT INTO book VALUES(b0001, eng.Database systems, Springer, 2016, John, 28); Execute40 INSERT INTO book VALUES(b0002, eng., Operation systems, Taylor, 2018, Michael, 35); Execute41INSERT INTO book VALUES(b0003, sci.Advanced mathematics, MIT, 2021, Catherine, 15

22、); Execute42INSERT INTO book VALUES(b0004, sci., Discrete mathematics, MIT, 2020, Peter, 22); Q book= 0 H borrow= 0 CD card =views query 侬 procedures。functionsE3 bookSELEC1X * FROM book LIMIT 100；中QQ输入以2 C G)向4图个U 演t： 2ms共10条 bno category .title. publisher .pub_yearauthorpricechar(8) varchar(10) var

23、char(40) varchar(30) yearchar(20)decimal1b0001eng.Database systemsSpringer2016John28.002b0002eng.Operation systemsTaylor2018Michael35.003b0003sci.Advanced mathematics MIT2021Catherine15.004b0004sci.Discrete mathematics MIT2020Peter22.005b0005eng.C languageSpringer2017Shannon40.006b0006liter.Harry po

24、tterElseiver2019Lisa19.007b0007liter.Star warsElseiver2018Jim25.008b0008his.Modern Chinese history Taylor2017Frank32.009b0009his.The second world war Taylor2021Mark32.0010b0010eng.JAVA languageMIT2022Sally38.00p Q 127.0.0.1 3306 8.026 USERv 0 library_3119001063 48k7 = tables (D book U borrow card= 0

25、 =views query Library_3119001063.sql .JU1U LdlU VA-qi , Lb , U ) Execute57 INSERT INTO card VALUES(U001, *Li Fen, auto, U); Execute58 INSERT INTO card VALUES(U002, Bai La, art, U); Execute59 INSERT INTO card VALUES(F001, Zhang Fa, pku, F); Execute 60 INSERT INTO card VALUES(F002, Li Aigu, stu, F)； 6

26、1 隹、扁4B脩 geei注*rtS 每 procedures。functionsEIS cardXSELECT* FROM card LIMIT 100；小 （2）Q输入以除期SQ； COO向费图个。 喇:1ms 共10条* eno* pnamedepartment ctypechar(7)char(8)varchar(5) char(1)1F001Zhang FapkuFF002Li AigustuF3G001Wu MiautoG4G002Qian qicsG5T001Li YuanautoT6T002Wang LiautoT7T003Zhang QicsT8T004Zhao Gaomat

27、hT9U001Li FenautoU10U002Bai LaartU7 0 library_3119001063 48k ExecuteV 三 tables 80 INSERT INTO borrow VALUES(F001, *b0003, 2022-06-12, 2022-07-11)； m book Executee_ e 5 81 INSERT INTO borrow VALUES(F002, ,b0009, 2022-08-20, NULL); LU borrow= _j g 2 LU card、一.E3 borrow =views S query1 SELECT 窥 procedu

28、res functions台QX* FROM borrow LIMIT 100；输入以蟠姬T 1 O =1个J 的t： 2ms共19条eno bno borrow_date return_date -char(7) char(8) datedate1F001F001b0001b00022022-08-122022-08-152022-02-122022-03-013F001b00032022-06-122022-07-114F002b00092022-08-20(NULL)c J6G002G002bOOOSb00072022-05-052022-07-042022-08-10(NULL)7G0

29、02b00102022-07-10(NULL)8T001b00012022-01-102022-03-04910T001T001b0002b00052022-05-102022-08-012022-01-012022-03-0411T001b(X)102022-01-012022-03-04T003b00012022-05-152022-08-0413T003b00022022-03-152022-04-0414T003b00032022-02-152022-03-04T003b00042021-02-152021-03-0416U001b00082022-02-022022-03-0917U

30、001b00092022-02-022022-03-0918U002b00032022-03-062022-03-0919U002b00042022-04-022022-05-094.数据修改1）将Book表中price属性的值全部打8折.-修改数据-将Book表中price属性的值全部打8折1 . UPDATE book13. SET price=price*0.8;y = tables (3)83-修改数据- Execute将Book表中price属性的值全部打8折 d) book=D I borrow84UPDATE book CD card85SET price=price*0.8;

31、=views86 querybookX 每 procedures 0 functionsSELECT * FROM book：LIMIT 100;于 &输入以搜索数据yree固个xl/ t： 2ms共10条c)bno .char(8) category varchar(10) title.c崎格 ar(40)publisher varchar(30) pub_year year author . char(20) price . decimal 1b0001eng.Database systemsSpringer2016John22.40b0002eng.Operation systemsTa

32、ylor2018Michael28.00b0003sci.Advanced mathematicsMIT2021Catherine12.004b0004sci.Discrete mathematicsMIT2020Peter17.605b0005eng.C languageSpringer2017Shannon32.006b0006liter.Harry potterElseiver2019Lisa15.207b0007liter.Star warsElseiver2018Jim20.008b0008his.Modem Chinese historyTaylor2017Frank25.609b

33、0009his.The second world warTaylor2021Mark25.6010b0010eng.JAVA languageMIT2022Sally30.402)将Card表中cno= FOOV 的姓名改为Li Ai。1 .-修改数据-将Card表中cno/F0。廿的姓名改为，Li Ai，. UPDATE cardR SET pname=,Li Ai4. WHERE cno=F001;=Library_3119001063.sql Xv 127.0.0.1 3306 8.026v 127.0.0.1 3306 8.026s Library_3119001063.sql . U

34、SER86v 0 library_3119001063 48k = tables (3)I -y 88 CD bookRQ CO borrow9 (D card三 口 =views 修改数据-将Card表中cno=，F00j的姓名改为，Li Ai* Execute UPDATE cardSET pname=Li AiWHERE cno=F001；-删除热樨.瓶除Rcrrcw去中rnc=FtVP 的借书汨录 queryE5 card X procdures1 SELECT * FROM card LIMIT 100; Q functions中 Q输入以搜索数据$cOG)向5n百个耗时:2ms1共

35、10条5.数据删除Qeno，pnamedepartmentchar(7)char(8)varchar(5)* ctype char(1) 1F001Li AipkuFF002Li AigustuF3G001Wu MiautoG4G002Qian qicsG5T001Li YuanautoT002Wang LiautoTT7T003Zhang QicsT8T004Zhao GaomathT9U001Li FenautoU10U002Bai LaartU1)删除Borrow表中eno=F002的借书记录。1 .-删除数据，删除Borrow表中eno二F002的借书记录2 . DELETEp. FR

36、OM borrow4. WHERE cno=,F002,;DATABASE O 3)O +v V 127.0.0.1 3306 8.026 USER 0 library_3119001063 48k = tables Q book CO borrow= D card =views query 侬 procedures Q functionsS Library_3119OO1O63.sql X Library_3119001063.sql .& UKUAIt CdCU89SET pname=Li Ai90WHERE cno=F001;9192删除数据，删除80什0表中010=中0。21 Exec

37、ute/ 93 DELETE94 FROM borrow95 WHERE cno=F002；96S3 borrow XSELECT * FROM borrow LIMIT 10G;共18条共18条于 Q扬z烟剽*CO0向图个xb 耗时:2msQ* eno * bno 、* borrow.date return.date char(7) char(8) datedateF001b00012022-08-122022-08-15F001b00022022-02-122022-03-013F001b00032022-06-122022-07-114G002b00052022-05-052022-07

38、-046G002b00072022-08-10(NULL)G002b00102022-07-10(NULL)7T001b00012022-01-102022-03-04T001b00022022-05-102022-08-019T001b00052022-01-012022-03-0410T001b00102022-01-012022-03-0411T003b00012022-05-152022-08-042）删除 小rr出版社的书籍（能够删除么？请说明原因）。1. -删除数据，删除囹工出版社的书籍-先删除borrow表中的关于，MIT1的数据2. DELETEFROM borrow3. WH

39、ERE bno IN （SELECT bno from book WHERE publisher=MIT）;I?. DELETEFROM book8. WHERE publisher=MIT,不能直接删除，因为borrow表中有book表的bno外键约束，所以要删除book表中 MIT出版社的书籍，必须先删除borrow表中关于MLT书籍的数据。小aQI轮入以搜索数据eno 一char(7) bno - char(8) CD book 03 borrowv 三 tables CD book 03 borrow969798J cardviewsquery将 procedures 0 functi