广东工业大学SQL编程报告1.docx
大作业名称：数据库的定义、建立与维护实验目的与要求使用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> USER> 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 16kS 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 NULL,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) NOT NULL CHECK (ctype 工N('T'JG'JU'JF')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('T'JG'JU'JF')23. )；24.25. CREATE TABLE borrow26. (eno CHAR(7),27. bno CHAR(8),28. borrow_date DATE NOT NULL,29. return_date DATE,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) PRIMARY 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 <1>共。条author , price char(20) decimal 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 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 < 1 >共。条C x eno . «pname department ，ctype .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 > 卬 card33CONSTRAINTC2PRIMARYKEY(cno,bno),> =views34CONSTRAINTC3FOREIGNKEY(cno)REFERENCEScard(cno),> </> query35CONSTRAINTC4FOREIGNKEY(bno)REFERENCESbook(bno)> 每 procedures36)；> 0 functions3738-插入数据-插入Book表数据E2 borrow XSELECT * FROM borrow LIMIT 100;中台愉入以翦嘘。白国个。0耗时:2ms<1>共。条C * eno * bno * borrow_date return_date .char(7) char(8) datedate3.数据输入jl.-插入数据-插入Book表数据INSERT INTO book VALUES('b0001', 'eng.1, database systems', 'Springer1, 2016, ,John 28);2. INSERT INTO book VALUES('b0002''eng.', 'Operation systems''Taylor', 2018, 'Michael', 35);INSERT INTO book VALUES('b0003', 'sci.1, Advanced mathematics', 'MIT', 2021, 'Catherine* 15);5.INSERT INTO bookVALUES('b0004''sci. ', 'Discrete mathematics''MIT', 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', 'Elseiver1, 2018, Pim'his.''Modern Chinese history''Taylor'210. 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(,T001'Li Yuan', 'auto', 'T1);15. INSERT INTOcard VALUES('T002','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(,G002'Qian qi', 'cs', 6);20. INSERT INTOcard VALUESCUeei','Li Fen', 'auto', 'U');21. INSERT INTOcard VALUES(,U002,'Bai La', 'art', U);22. INSERT INTOcard VALUES('F001'Zhang 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 VALUES('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', 12022-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','b0009', '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. 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.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); 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< 1 >共10条» bno »category .»title. publisher .«pub_yearauthor«pricechar(8) varchar(10) varchar(40) varchar(30) yearchar(20)decimal1b0001eng.Database systemsSpringer2016John28.002b0002eng.Operation systemsTaylor2018Michael35.003b0003sci.Advanced mathematics MIT2021Catherine15.004b0004sci.Discrete mathematics MIT2020Peter22.005b0005eng.C languageSpringer2017Shannon40.006b0006liter.Harry potterElseiver2019Lisa19.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> =views> </> query£ Library_3119001063.sql > .JU1U Ldl'U 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')； 61 隹、扁4B脩 geei注*r'tS> 每 procedures>。functionsEIS cardXSELECT* FROM card LIMIT 100；小 （2）Q输入以除期SQ； COO向费图个。> 喇:1ms < 1 >共10条* eno* pnamedepartment «ctypechar(7)char(8)varchar(5) char(1)1F001Zhang FapkuFF002Li AigustuF3G001Wu MiautoG4G002Qian qicsG5T001Li YuanautoT6T002Wang LiautoT7T003Zhang QicsT8T004Zhao GaomathT9U001Li 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> 窥 procedures> functions台QX* FROM borrow LIMIT 100；输入以蟠姬T 1 O ® ® ©=1个J >的t： 2ms<1>共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)7G002b00102022-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-0917U001b00092022-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;> =views86> </> querybookX> 每 procedures> 0 functionsSELECT * FROM book：LIMIT 100;于 &输入以搜索数据<yree®固个xl/ » t： 2ms<1>共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 systemsTaylor2018Michael28.00b0003sci.Advanced mathematicsMIT2021Catherine12.004b0004sci.Discrete mathematicsMIT2020Peter17.605b0005eng.C languageSpringer2017Shannon32.006b0006liter.Harry potterElseiver2019Lisa15.207b0007liter.Star warsElseiver2018Jim20.008b0008his.Modem Chinese historyTaylor2017Frank25.609b0009his.The second world warTaylor2021Mark25.6010b0010eng.JAVA languageMIT2022Sally30.402)将Card表中cno= FOOV 的姓名改为Li Ai'。1 .-修改数据-将Card表中cno/F0。廿的姓名改为，Li Ai，. UPDATE cardR SET pname=,Li Ai'4. WHERE cno='F001'=Library_3119001063.sql Xv 127.0.0.1 3306 8.026v 127.0.0.1 3306 8.026s Library_3119001063.sql > .> USER86v 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 Ai'WHERE cno="F001'；-删除热樨.瓶除Rcrrcw去中rnc='FtVP '的借书汨录> </> queryE5 card X> ® procdures1 SELECT * FROM card LIMIT 100;> Q functions中 Q输入以搜索数据$cOG)向5n百个耗时:2ms1>共10条5.数据删除Q«eno，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. FROM 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 Ai'90WHERE cno='F001'9192删除数据，删除80什0»表中010=中0。2'1 Execute>/ 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-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. WHERE bno IN （SELECT bno from book WHERE publisher='MIT'）;I?. DELETEFROM book8. WHERE publisher='MIT,不能直接删除，因为borrow表中有book表的bno外键约束，所以要删除book表中 'MIT'出版社的书籍，必须先删除borrow表中关于'MLT书籍的数据。小aQ'I轮入以搜索数据»eno 一char(7) « bno - > char(8) </Reeconsolete今)o0向return_date . dateF001b00012022-08-122022-08-15F001b00022022-02-122022-03-01AG002G002b0005b00072022-05-052022-08-102022-07-04(NULL)56T001T001b0001b00022022-01-102022-05-102022-03-042022-08-017T001b00052022-01-012022-03-04T003b00012022-05-152022-08-049T003b00022022-03-152022-04-0410U001b00082022-02-022022-03-091U001b00092022-02-022022-03-09SELECT * FROM borrow LIMIT 100;图个J 耗时:1ms 1共11条v 三 tables > CD book> 03 borrowv 三 tables > CD book> 03 borrow969798>>>></>J cardviewsquery将 procedures 0 functi
