實驗報告R實踐報告

1、實驗報告團實踐報告口課程名稱： 數(shù)據(jù)庫概論R實驗名稱： 交互式SQL語句的使用實驗地點：行知樓A105教室專業(yè)班級：軟件2016班 學(xué)號：學(xué)生姓名：宋雪姍指導(dǎo)教師：宋曉濤2022年4月20日3、用INSERT插入數(shù)據(jù)到表中。5insertintoStudent valuesC 20100001,李勇，男，20, CS, TOO】)insertintoStudent values C 20100002,劉晨，女，19, CS, 1001)insertintoStudent (Sno, Sname, Ssex, Sage, Sdept, Sclass) values20100021,王敏，女，18

2、, MA1002)insertintoStudent1 Sno, Sname, Ssex, Sage, Sdept, Sclass values 20100031,張立，男，19, IS, 1003insertintoStudent1Sno, Sname, Ssex, Sclass)values C 20100003*,J 劉洋，女，1001)insertintoStudent (Sno, Sname, Ssex, Sage, Sdept, Sclass) values(J 20100010,趙斌，男，19, IS, 1005)insertintoStudent valuesC 2010002

3、2,張明明，男，19, CS, 1002)insertintoCourse Cno, Cname, Cpno, Ccreditvalues Cl,數(shù)據(jù)庫系統(tǒng)原理，5, 4)insertintoCourse Cno, Cname, Cpno, CcreditvaluesC 2,高等數(shù)學(xué),null, 2)insertintoCourse Cno, Cname, Cpno, Ccreditvalues C 3,管理信息系統(tǒng)，1, 4)insertintoCourse Cno, Cname, Cpno, Ccreditvalues4,操作系統(tǒng)原理，飛，3)insertintoCourse Cno, C

4、name, Cpno, Ccreditvalues C 5,數(shù)據(jù)結(jié)構(gòu)，7, 4)insertintoCourse Cno, Cname, Cpno, CcreditvaluesC 6,1 數(shù)據(jù)處理，null, 2)insertintoCourse Cno, Cname, Cpno, Ccreditvalues( 7, C語言,null, 4)insertintoSC valuesC 20100001?,1 V , 92)insertintoSC values (20100001,2, 85)insertintoSC values (20100001,3, 88)insertintoSC val

5、ues (20100002, 1, 90)insertintoSC values (20100002,2, 80)insertintoSC values， 20100003, T, null)insertintoSC valuesC 20100010, 3, null)H結(jié)果事消息CnoCnameCpnoCcreditSnoSnameSsexSageSdeptSclass11數(shù)據(jù)庫系統(tǒng)原理54120100001李勇男20CS100122高等數(shù)學(xué)NULL2220100002劉晨女19CS1001133目理信息系統(tǒng)1i 4220100003劉洋女nullnull1001420100010八 J趙斌

6、男19IS100544操作系統(tǒng)原理63520100021王敏女18MA100255數(shù)據(jù)結(jié)構(gòu)746201000221張明明1男19CS100266數(shù)據(jù)處理NULL2720100031張立男19IS100377C語言NULL4SnoCnoSage20100001 192234567 TOC o 1-5 h z 20100001285201000013 J 882010000219020100002280201000031NULL201000103NULL4、用UPDATE、DELETE修改表中數(shù)據(jù)。Supdate Student set Sage=22 where Sno=，2010000T ;S

7、update Student set Sage=Sage+l;Supdate SC set Sage=85 where Sno=，2010000T and Cno=，3JSupdate SC set Sage=Sage+5 where CS=( select Sdept from Student where Student. Sno=SC. Sno：;Supdate SCI set Sage=80，where Cno=2and Sno in(select Sno from Student where Student. Sno=SC. Sno and Sname=，劉晨J; Hupdate St

8、udentset Sno=，20100025 where Sno=，2010002T select* from Student select* from Course select* from SCEdeletefrom Studentwhere Sno/ 20100022,-select* into tmpSCfrom SCEdeletefrom tmpSCwhere Sno=，2010000T and Cno=，Tdeletefrom tinpSCwhere Sno=2010002, deletefrom tmpSCwhere CS=(select Sdept from Student w

9、here Student. Sno-tinpSC. Sno deletefrom tmpSCselect* from Studentselect* from Courseselect* from SC結(jié)果或消息SnoSnameSsexSageSdeptSclassi 20100001 ； ! !李勇男23CS100120100002劉晨女21CS100120100003劉洋女NULLNULL100120100010趙斌男21IS100520100022張明明男21CS100220100025王敏女20MA100220100031張立男21IS1003Cno Cname)1 I數(shù)據(jù)庫系統(tǒng)原理高等

10、數(shù)學(xué)管理信息系統(tǒng)操作系統(tǒng)原理數(shù)據(jù)結(jié)構(gòu)數(shù)據(jù)處理C語言Cpno Ccredit54NULL 21434NULL 2NULL 4結(jié)果或消息SnoSnameSsexSageSdeptSclassi 20100001 ； ! !李勇男23CS100120100002劉晨女21CS100120100003劉洋女NULLNULL100120100010趙斌男21IS100520100022張明明男21CS100220100025王敏女20MA100220100031張立男21IS1003Cno Cname)1 I數(shù)據(jù)庫系統(tǒng)原理高等數(shù)學(xué)管理信息系統(tǒng)操作系統(tǒng)原理數(shù)據(jù)結(jié)構(gòu)數(shù)據(jù)處理C語言Cpno Ccredit54

11、NULL 21434NULL 2NULL 4結(jié)果或消息SnoSnameSsexSageSdeptSclassi 20100001 ； ! !李勇男23CS100120100002劉晨女21CS100120100003劉洋女NULLNULL100120100010趙斌男21IS100520100022張明明男21CS100220100025王敏女20MA100220100031張立男21IS1003Cno Cname)1 I數(shù)據(jù)庫系統(tǒng)原理高等數(shù)學(xué)管理信息系統(tǒng)操作系統(tǒng)原理數(shù)據(jù)結(jié)構(gòu)數(shù)據(jù)處理C語言Cpno Ccredit54NULL 21434NULL 2NULL 4SnoCnoSage2010000

12、12952010000139020100002110020100002280201000031NULL201000103NULLra結(jié)果靠消息SnoSnameSsexSageSdeptSclass120100001李勇男23CS1001220100002劉晨女21CS1001320100003劉洋女NULLNULL1001420100010趙斌男21IS1005520100025王敏女20MA1002620100031張立男21IS1003CnoCnameCpnoCcredit11i數(shù)據(jù)庫系統(tǒng)原理5422高等數(shù)學(xué)NULL233管理信息系統(tǒng)1444操作系統(tǒng)原理6355數(shù)據(jù)結(jié)構(gòu)7466數(shù)據(jù)處理NU

13、LL277c語言NULL4SnoCnoSage120100001110222010000129532010000139042010000211005201000022806201000031NULL7201000103NULL5、用SELECT進行選擇、投影操作。select* from Student,100% I- SELECT Sname,1 Year of Birth：?, 2004 Sage, LOVER(Sdept, FROM Student, t SELECT Sname NAME, * Year of Birt” BIRTH, 2000-Sage BIRTHDAY, LOVER

14、(Sdept) DEPARTMENTSnoSnameSsexSageSdeptSclass120100001李勇男23CS1001220100002劉晨女21CS1001320100003劉洋女NULLNULL1001420100010趙斌男21IS1005520100025王敏女20MA1002620100031張立男21IS1003FROM Student |100% 劇結(jié)果南消息Sname（無列名）（無列名）（無列名）i|李勇Year of Birth:1981CS2劉晨Year of Birth:1983CS3劉洋Year of Birth:NULLNULL4趙斌Year of Bir

15、th:1983is5王敏Year of Birth:1984ma6張立Year of Birth:1983isNAMEBIRTHBIRTHDAYDEPARTMENT1李勇Year of Birth1977CS2劉晨Year of Birth1979CS3劉洋Year of BirthNULLNULL4趙斌Year of Birth1979is5王敏Year of Birth1980ma6張立Year of Birth1979isE SELECT Sname FROM Student WHERE Sdept=，CS ;SELECT Sname Sdept, Sage FROM Student W

16、HERE Sage BETWEEN 20 AND 23SELECT Sname. Ssex FROM Student WHERE Sdept IN ( IS, MA, CS);SELECT Sname. Sno. Ssex FROM Student WHERE Sname LIKE 劉 ;SELECT Sno,Cno FROM SC WHERE Sage IS NULL;SELECT Sname FROM Student WHERE Sdept=，CS AND Sage 23;SELECT Sno. Sname, Sage, Sdept FROM Student WHERE Sage 二 17

17、,1& 20,23J,SELECT Sname, Sdept, Sage FROM Student WHERE Sage NOT BETWEEN 21 AND 24100% 劇結(jié)果價消息學(xué)號姓名性別年齡所在系120100001李勇男23CS220100002劉晨女21CS320100003劉洋女NULLNULL420100010趙斌男21IS520100025王敏女20MA620100031張立男21ISSno SnameSnoSnameSageSdept120100001李勇23CS220100025王敏20MASname Sdept Sage1IM ma206、進行多表的連接查詢F SEL

18、ECT Student. Sno, Sname. Ssex, Student. Sage, Sdept, Cno, SC. SageFROM Student, SCWHERE Student. Sno=SC. Sno,bi SELECT FIRST. Cno, SECOND. CpnoFROM Course FIRST,Course SECONDWHERE FIRST.Cpno=SECOND. Cno;100% F SELECT Student. Sno, Sname. Ssex, Student. Sage, Sdept, Cno, SC. SageFROM Student, SCWHERE

19、 Student. Sno=SC. Sno,bi SELECT FIRST. Cno, SECOND. CpnoFROM Course FIRST,Course SECONDWHERE FIRST.Cpno=SECOND. Cno;100% F SELECT Student. Sno, Sname. Ssex, Student. Sage, Sdept, Cno, SC. SageFROM Student, SCWHERE Student. Sno=SC. Sno,bi SELECT FIRST. Cno, SECOND. CpnoFROM Course FIRST,Course SECOND

20、WHERE FIRST.Cpno=SECOND. Cno;100% -SELECT Student. Sno, Sname. Ssex. Student. Sage. Sdept, Cno, SC. Sage FROM Student JOIN SC ON Student. Sno=SC. Sno);SnoSnameSsexSageSdeptCnoSage1j 20100001|李勇男23CS1102220100001李勇男23CS295320100001李勇男23CS390420100002劉晨女21CS1100520100002劉晨女21CS280620100003劉洋女NULLNULL1

21、NULL720100010趙斌男21IS3NULL劇結(jié)果小消息SnoSnameSsexSageSdeptCnoSage120100001李勇男23CS1102220100001李勇男23CS295320100001李勇男23CS390420100002劉晨女21CS1100520100002劉晨女21CS280620100003劉洋女NULLNULL1NULL720100010趙斌男21IS3NULL100% 結(jié)果第消息Cno Cpno 產(chǎn)一：23534NULL45NULL111 7h SELECT Student. Sno, Sname Ssex, Student. Sage, Sdept,

22、 Cno, SC. Sage FROM Student LEFT OUTER JOIN SCON1 Student. Sno=SC. Sno1;JtL100% -I SELECT Student. Sno, Sname FROM Student, SCWHERE Student. Sno=SC. Sno ANDHI |cno=J 2JAND SC. Sage| 90;100 % 劇結(jié)果或消息Sno Sname1 I 20100001 I 李勇220100001 李勇SnoSnameSsexSageSdeptCnoSage120100001 !李勇男23CS1102220100001李勇男23C

23、S295320100001李勇男23CS390420100001李勇男23CS1102520100001李勇男23CS295620100001李勇男23CS390720100002劉晨女21CS1100820100002劉晨女21CS280920100002劉晨女21CS11001020100002劉晨女21CS2801120100003劉洋女NULLNULL1NULL1220100003劉洋女NULLNULL1NULL1320100010趙斌男21IS3NULL1420100010趙斌男21IS3NULL1520100025王敏女20MANULLNULL1620100031張立男21ISNU

24、LLNULL劇結(jié)果消息7、7、E3S1FlJLECT Student. Sno, Sname, Cname, SC. SagqSnoCno CpameMOM Student, SC, CourseJ單擊可選擇所有網(wǎng)格單元，據(jù)庫系統(tǒng)原理WHERE Student. Sno = SC. Sno AND SC. Cno = Course. Cno;220100001 2高等數(shù)學(xué)。9A1 nnnm 2告神隹自立母100 % H結(jié)果扉消息420100002 1數(shù)據(jù)庫系統(tǒng)原理520100002 2高等數(shù)學(xué)1Sno20100001Sname CnameSage1李勇 數(shù)據(jù)庫系統(tǒng)原理102620100003

25、1數(shù)據(jù)庫系統(tǒng)瓊理720100010 3管理信息系統(tǒng)220100001李勇高等數(shù)學(xué)95320100001李勇管理信息系統(tǒng)90420100002劉晨數(shù)據(jù)庫系統(tǒng)原理100520100002劉晨高等數(shù)學(xué)80620100003劉洋數(shù)據(jù)庫系統(tǒng)原理NULL720100010趙斌管理信息系統(tǒng)NULLSnoSage1120100001 i 102!I220100001953201000019042010000210052010000280620100003NULL720100010NULL7、進行子查詢。F SELECT Sno, SnameFROM StudentWHERE Sno IN (SELECT Sn

26、o FROM SC WHERE Cno=，2,); ,cmrT rnm n nn m n100% a結(jié)果靠消息220100002 劉晨SnoSname1:20100001I|李勇B SELECT Sno, Sname, SdeptFROM StudentWHERE Sdept IN (SELECT SdeptFROM StudentWHERE Sname?劉晨); 100 % 屬結(jié)果 事消息 副客戶端統(tǒng)計信息SdeptCSCS屬結(jié)果 事消息 副客戶端統(tǒng)計信息SdeptCSCSSdeptCSCSSdeptCSCSSno Sname20100001 | 李勇20100002 劉晨0 SELECT

27、Sno, Cno FROM SC AS xWHERE Sage= SELECT AVG |：|Sage FROM SC AS y WHERE y. Sno=x. Sno；100% 劇結(jié)果 小消息”客戶端統(tǒng)計信息8、創(chuàng)建視圖并進行查詢。SnoCno11 20100001I 122010000123201000011420100001252010000216201000021SQLQuery2.sql -.M60QJ86152GOE CREATE VIEW Stud巳nt_VIEW l學(xué)號，姓名，性別，年齡，系，班級)AS SELECT Sno, Sname, Ssex, Sage, Sdept,

28、 Sclass FROM Student; GOBCREATE VIEW S_G(Sno, Gavg)AS SELECT Sno?AVG(Sage) FROM SC GROUP BY Sno;GO-ICREATE VIEW XK VIEWAS SELECT Student.*, Course. *, SC. SageFROM Student, SC, CourseWHERE Student. Sno=SC. Sno AND SC. Cno=Course. Cno;,G0BSELECT * FROM Student_VIEVB SELECT SC. Cno, Cno, SageFROM SCS_

29、GWHERE SC. Sno=S_G. Sno 丫二 Sage =S_G. GavgBSELECT Sno, Sname, Cname, SageFROM XK_VIEW劇結(jié)果靠消息03客戶端統(tǒng)計信息學(xué)號姓名性別年齡系班級120100001李勇男23CS1001220100002劉晨女21CS1001320100003劉洋女NULLNULL1001420100010趙斌男21IS1005520100025王敏女20MA1002620100031張立男21IS1003CnoCnoSage1311022229531110242295511100611100SnoSnameCnameSage1201

30、00001李勇數(shù)據(jù)庫系統(tǒng)原理23220100001李勇高等數(shù)學(xué)23320100001李勇管理信息系統(tǒng)23420100002劉晨數(shù)據(jù)庫系統(tǒng)原理21520100002劉晨高等數(shù)學(xué)21620100003劉洋數(shù)據(jù)庫系統(tǒng)原理NULL720100010趙斌管理信息系統(tǒng)21心得體會學(xué)習(xí)數(shù)據(jù)庫和數(shù)據(jù)表創(chuàng)建和修改時，了解到表是建立關(guān)系數(shù)據(jù)庫的基本結(jié)構(gòu)，用 來存儲數(shù)據(jù)具有已定義的屬性，在表的操作過程中，有查看表信息、查看表屬性、 修改表中的數(shù)據(jù)、刪除表中的數(shù)據(jù)及修改表和刪除表的操作。表是數(shù)據(jù)最重要的 一個數(shù)據(jù)對象，表的創(chuàng)建好壞直接關(guān)系到數(shù)數(shù)據(jù)庫的成敗，表的內(nèi)容是越具體越 好，但是也不能太繁瑣，以后在實際應(yīng)用中多使

31、用表，對表的規(guī)劃和理解就會越 深亥IJ。實驗二 交互式SQL語句的使用一、實驗?zāi)康暮鸵螅耗康模?1)掌握數(shù)據(jù)庫對象的操作過程，包括創(chuàng)建、修改、刪除(2)熟悉表的各種操作，包括插入、修改、刪除、查詢(3)熟練掌握常用SQL語句的基本語法要求：掌握SQL創(chuàng)建數(shù)據(jù)庫，創(chuàng)建表、視圖、索引等數(shù)據(jù)庫對象；熟練掌握SQL 描述及創(chuàng)建關(guān)系模式。能熟練使用SQL進行插入、修改和刪除操作，掌握各種 查詢操作。二、實驗平臺：MicroSoft SQL Server2019三、主要實驗內(nèi)容及結(jié)果：實驗內(nèi)容：1、用CREATE創(chuàng)建表相關(guān)表。(1)程序代碼：1)創(chuàng)建Student表create table Studen

32、t(Sno char(8) primary key,Sname char(8) not null,Ssex char(2) not null,Sage smallint check (Sage8012)統(tǒng)計每個同學(xué)的學(xué)號、選課數(shù)、平均成績和最高成績SELECT COUNT (DISTINCT Sno)學(xué)號數(shù)，Count (DISTINCT Cno)選課數(shù),AVG (Grade) 平均成績,MAX (Grade)最高成績FROM SC6、進行多表的連接查詢1)查詢每個參加選課的學(xué)生信息及其選修課程的情況SELECT , Sname, Ssex, Sage, Sdept, Cno, GradeFR

33、OM Student JOIN SC ON (=SC. Sno)2)查詢選修2號課程且成績在90分以上的所有學(xué)生SELECT , SnameFROM Student JOIN SC ON(=SC. Sno)WHERE SC.Cno=2AND SC. Grade90;3)查詢每個學(xué)生的學(xué)號、姓名、選修的課程名及成績SELECT ,Sname, Cname, GradeFROM Student, SC, CourseWHERE =SC.Sno AND SC.Cno=;4)查詢選修了數(shù)據(jù)庫系統(tǒng)原理的同學(xué)的學(xué)號和姓名和成績SELECT ,Sname, GradeFROM Student,SC, Cou

34、rseWHERE =SC. Sno AND SC. Cno= AND =數(shù)據(jù)庫系統(tǒng)原理;7、進行子查詢。1)由In引出的子查詢：查詢與“劉晨”在同一系學(xué)習(xí)的學(xué)生SELECT Sno,Sname, Sdept FROM StudentWHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname=劉晨);2)由比較運算符引出的子查詢：找出每個學(xué)生超過他選修課程平均成績的課程 號SELECT Sno,Cno FROM SC AS xWHERE Grade=(SELECT AVG(Grade) FROM SC AS y WHERE y. Sno=x. Sn

35、o);3)由EXISTS引出的子查詢：查詢所有選修了 1號課程的學(xué)生姓名SELECT Sname FROM StudentWHERE EXISTS （SELECT * FROM SC WHERE Sno= AND Cno=1）;8、創(chuàng)建視圖并進行查詢。1）建立學(xué)生基本信息視圖GOCREATE VIEW Student_VIEW（學(xué)號，姓名，性別，年齡，系，班級）AS SELECT Sno, Sname, Ssex, Sage, Sdept, Sclass FROM Student;2）建立學(xué)生均分視圖GOCREATE VIEW S_G（Sno, Gavg）AS SELECT Sno, AVG （Grade） FROM SC GROUP BY Sno;3）建立包含如下信息的視圖GOCREATE VIEW X