数据库课程实验第一部分

1、徐州工程学院管理学院实验报告实验课程名称： 数据库原理及应用实验地 点： 经济管理实验教学中心2013 2014学年（一）学期2013 年9月至2013 年12月专业信息管理与信息系统班级 11信管2班学生姓名 学号指导老师蒋秀辉数据库课程实验第一部分实验一 熟悉SQL Server 2008工作环境实验目的：1 .熟悉SQL Server 2008工作环境。2 .熟悉SQL Server 2008中的主要管理工具3 .熟悉SQL Server 2008系统数据库的结构 实验内容：4 .独立安装 SQL Server 2008 。与 SQL Instalblwn CenterHardwa re

2、 and Scftwdre Require meritsLntJilFaKonView the hardware andrequirement5,5*curiiy Doturrierrtjtian5 .练习使用SQL Server 2008中的主要管理工具T朝建学国四二j m/对器资源营理器迷接，智善一二亘制- 6 14 AQWJTVM WLESO f 1YSQ LSEIinRJ-敷据库fl二泵统数据库±)|ReportSerretrfnYSQLSEmi+1 J R叩合 t3rv=$IIT3BaEIlVER 司 i l端it/i J Lent2土数据庠快照七iJ订货数据库 日。安全性

3、十一j登录名十二胭务器焦色到二凭据国N加密提供程序+1 1市核Li服第找市核规范.+服务黑对象a 口复制庄一管理房哥£乳far串畛E"代晔3.查看系统数据库5uicIkiI LSI"r JorXi s*Lt 酬UTDbn oklD:】sJrr工xt o 赤£ OIZEt二8Scartlist?ewt-l'is'LH cirtlDlackin'，i sPrie*THUH£ 侪 *rlt Yv-1上T 图必编m储全 团义I&E储全 b te表视同可“存安口表视同可se存安b觞 s_ i 1 1 1 ! 1 1 i I

4、 ，1 1 ! ，i 1 In aaaaaaamaH3am m虹 If国国第 3 9 9s 3国国国99田国一 5c 二二 三 祠£库 据 数 例 样 舟 查ordlff rsordsrlDrdtrTn./T unT泪rP510 d 中j力at abuok 寻lr>H?i 土兄t AQklf riLlidi m，Ur j d ecin eccsitA4.TLtLr3 0捕 FbooJscEirtubtirncnbcrZiinewbrrll ?nenberf sssfardi tilth arincrttttTa *t totalPti ce实验二创建数据库和数据表实验目的：1

5、. 了解SQL Server 2008数据库的逻辑结构和物理结构。2 . 了解表的结构特点。3 .熟悉SQL Server的基本数据类型。4 . 了解数据库、数据表、约束等相关概念。5 .创建数据库和表。6 .掌握使用Transact-SQL语句创建数据库和表的方法。实验内容：练习创建数据库和数据表。(1)打开“我的电脑”或“资源管理器”，在磁盘中以自己的姓名或学号建立 文件夹。(2)创建名为“学生-选课”(即名称为JWGL数据库，并存储在第(1)步建 立的文件夹下。(3)在建立的JWG激据库中按照表9-1至9-3所示创建Student、Course和 SC三张关系表。(4)在数据表中输入数据

6、。为表1和表3建立主键约束，并为各表输入数据， 具体记录如表1-3至1-8所示，为后续实验做准备。(表1-3至1-8、9-1至9-3 参见教材)(1)201U51L20B(2)所有著小上 E:20111511Za6(3)日诟浜)C&ffi)Student使用全文索引 数据库文件任):逻辑名称文件类型文件期初自动幡长路径学生一选课行数据TRI. .3赠量为 1MB一 .|7- E:2OL11E1120&学生一选.日志不适用I地量为10%j .)E： 2O1U5L12C5学生一选瞟 观£I学生一选课二味期£SQL Server 口鼻工事b，* ,SQL Serv

7、er.3 072 KE1,024 KE列名 诩Src赞据典型 d-a-C5允坪'.ul挡 Srvari±arf2DjL5日方引0匚3即3mdlrilWDtptw*rd"»r2D匚Xa<cwd营回匚rS席叫由租二Sea长复2默必值或绅定溥数据关里Cllts批评3借否日幅设）OS 称）Coiirse列名2 j 5 口CnDnoCree t箜据类型 diarjl varchar20) dhar(l) s?na lin:允讲Wil值ih CoUTEfil睡一非聚集ja席鹿）表达式<?T4di>=0 ahA Grade<=100法识【名称）C

8、K_SC说明（4）主键约束如（3）中图所示StudentSnoSrrSexAgeDeptIDCard01姜珊文IS管理学院01：2李思安17鲤济学院02孙浩男21经辫学院0304周强男20和教学院04OS季斌男19人文学院0506黄良安21人文学院翳米AUZNULLWLLCourseCnoCnDrroCredit1物据结构1642株作系统2363数据库原理3E44撷率热计432Z39AU£松£AWSCDr5B900AEW口写5£rtO'； 6bWT双£toOp：6Tzoop®e2TGO,OBTTDao阵实验三基本数据查询实验目的：1 .

9、掌握SELECT-FROM-WHE的的结构及使用，实现在数据库上进行简单查询。2 .掌握各种查询操作方法。实验内容：练习1:简单查询(1)查询全体学生的学号与姓名(2)查询全体学生的姓名及年龄(3)查询全体学生的姓名、年龄及所在学院(4)查询选修了课程的学生学号练习2:条件查询(1)查询所有年龄在20岁以下的学生的姓名及其年龄(2)查询年龄在20到23岁之间的学生的姓名及年龄(3)查询年龄不在2023岁之间的学生的姓名、学院及年龄(4)查询所有姓“刘”的学生的姓名、学号和性别(5)查询全体学生情况，结果按所在学院的编号升序排列，同一学院的学 生按年龄降序排列(6)查询所有不姓李的学生的姓名(7

10、)查询姓“李”且名为一个汉字的学生的姓名(8)查询名字中第2个汉字为“斌”的学生的姓名和学号(9)查询所有不姓“李”的学生的姓名(10)查询全体学生情况，结果按所在学院的编号升序排列，同一学院中的 学生按年龄降序排列练习1:简单查询(1) Select Sno , Sn FROM Student ;I | Sno | Sn |1Fl"'姜珊2 W李思3 03孙皓4 04周强5 比季前6 06黄琪(2) Select Sn, Age FROM Student ;1 箔果匾消息Sn2型i rswi is 41111*411 llil/2 李思 173 孙浩214 周强205 李斌

11、13G 黄原 21(3) Select Sn, Age , Dept FROM Student口结果7消息D«pt1萋珊1E管理学道ri2李思17经济学院3孙浩21爰那学院周强20科教学院李斌19人文学院,5黄琪21人文学院(4) Select Sno FROM SC；二|结果各消息Sno1FoiS lllli-ri IlilTHil2011 i3D2JD3504£W705g0634练习2:条件查询 (1) Select Sn, Age 盅结果限消息SnIAgehrsijJ2'"篆亘i ?2 李斌152 2)SelectSn,Age"结黑消息Sn

12、 %1同商1212周强203 黄琪21FROM StudentFROM StudentWHERE Age <20 ;WHERE Age BETWEEN 20 AND 23(3) Select Sn, Dept , Age FROM Student WHERE Age NOT BETWEEN 20 AND 23 ;结果口消息SnD«pi%*1;新.管理学院1?2李思绎济学院173繇人文学院1 =(4) Select Sn, Sno, Sex FROM Student WHERE Sn LIKE '刘;二1结果3消息5rl Sna Sex3结果_j消息* FROM Stud

13、entSfioShSex句思D=ep:IDCbMDnoiC5 李斌男13人文学除1 51201美妣女18管理学院113比黄国女21人文学院D6Z4D2李思女17寮褥学院0225Cl如浩男21经法学院33G04周强男期科教学院044(5) SelectOrder BY Dno ASC, Age DESC;口结果二超圭斌Sn T 2(8) SelectSn, Sno FROM StudentWHERE Sn LIKE '斌'(9)Select Sn FROM StudentWHERE Sn NOT LIKE李'ASC, Age DESC(10) Select * FROM

14、Student ORDER BY Dno二I结果_'j消息Sno£SnSexAflGDcpd"iDConJDnob05李斌男19大文学院D51201姜珊女Ifi者当学院D113re童琪女21人文学院062402李思女J7、济学院D22503孙诰男T经谆学院D?3604周强男20科就学院04A实验四数据分组与汇总实验目的：1.掌握数据分组查询的方法。2.掌握统计函数的使用。3.使用GROUP BYl HAVING?句对数据进行分组查询。4.使用相应的运算符或子旬生成汇总数据。实验内容：练习1:使用统计函数(1)查询学生的总人数(2)查询选修课程的学生人数(3)计算选修

15、2号课程的学生的平均成绩(4)查询选修了 3号课程的学生的最高分数(5)求成绩排名前三名的学生号、课程号、成绩(6)求成绩排名前三名的学生号、课程号、成绩(包括成绩并列情况) 练习2:分组查询(1)求有成绩的每个学生的平均成绩(2)求有成绩的每个学生的总成绩(3)求每门课程的平均成绩(4)求平均成绩大于85分的学生的学号及平均成绩(5)求平均成绩大于85分的课程的课程号及课程平均成绩(6)求各个课程号及相应的选课人数练习1:使用统计函数(1) Select COUNT(Sno) as 总人数 FROM Student3结果.j /息息人数1= G =(2) Select COUNT(Sno)

16、as 选修课程的人数FROM Student WHERE Sno in(Select Sno FROM SC)口结果.、重息选修课程的人数(3) Select AVG( Grade ) as 课程号的学生的平均成绩FROM SC WHERE Cno ='2'结果 J消息理程二号的学生的平均成绩(4) Select MAX( Grade ) as 课程号的最高成绩FROM SC WHERE Cno ='3'(5) SElECt top 3 Sno , Cno, Grade FROM SC ORDER BY Grade DESCFROM SC ORDER BY Gr

17、ade DESCn结果qjg息Sno Crio Grade 1血一395.02 C4i91D3 Cl2B3.D练习2:分组查询AS 平均分 FROM SC groupby Sno(1) Select AVG( Grade )口绢果消息I1 "'名迹可2 wssssir'3 7E.KOO4 g.wmj5 7-.OOCDKS S5,D0CtX)D(2) Select SUM( Grade ) as 日结果二消电|I 成吏1 应i 、mr3 76 口4 1JC.05 77.06 旺7(3) Select AVG( Grade ) as但球但消息 各科平均成绩1 正而正5一&

18、quot;72 S2.5fflXKC工啦50Mo0| 4&8.0000(4) Select Sno , AVG( Grade )总成绩 from SC GROUP BY Sno各科平均成绩 FROM SC GROUP BY Cnoas分数大于分的学生平均成绩FROMSC GROUPBY Sno HAVING AVG( Grade )> 85画结果Ji消息苗数丈于三苗的学生平均农绩1 C4 S3.WM0D(5) Select Cno , AVG( Grade ) as 分数大于分的课程平均成绩FROM SCGROUP BY Cno HAVING AVG( Grade )> 8

19、5国结果石消息占一分数大于诬分的真程平均成籁1门; 85,50000024 鸵匕二乳尤FROM SC GROUP BY Cno(6) SELECT Cno, COUNT( Sno) as 选课人数惮冬果商而1 一|I I Cno选课人数1 厂二工2 223 324 42实验五多表连接实验目的：1 . 了解多表连接的作用。2 .掌握使用不同类型的连接方式进行表的连接操作的方法。3 .使用UNIONS令。4 . 了解连接与UNION勺区别。实验内容：(1)查询选修“数据结构”课程的学生的学号(2)查询学生“姜珊”选修的课程的平均分(3)查询选修”数据库原理”课程的学生的平均分(4)查询学生“李斌”

20、的所有成绩，显示的列包括姓名，成绩(5)查询学生选修课的考试情况，显示的列名包括学生姓名，课程名称，成绩(6)查询成绩大于70分的学生姓名(1) SELECT Sno FROM SC WHERE Cno IN( SELECT Cno FROM Course WHERE Cn = ' 数据2构)二给果L消息Sna1 而 一口2 "02"(2) SELECT AVG( Grade ) as 姜珊的平均分 FROM SC WHERE Sno IN( SELECT Sno FROM Student WHERE Sn='姜珊)口结果息姜珊的平均分T !l !-，一 !

21、,"!,T! SS £，11!1 = M.OCHM a:aa-BS: &-£uii |3-(3) SELECT AVG( Grade ) as 数据库原理平均分FROM SC WHERECno IN( SELECTCno FROM Course WHERE Cn='数据库原理')口结果以消息啰潢凰|理至电分1 逅5s藏！ ikii *« ) lisa ui* J(4) SELECT Sn, Grade as 成绩 FROMStudent , SC WHEREStudent . Sno=SC. Sno AND Student . S

22、n = '李斌'|困结杲也一消引一&T 成翁 "III1 李斌j 77.0HUB- Jill M.： IWHERE(5) SELECT Sn,Cn, Grade FROM Student , Course , SC Student . Sno =SC. Sno AND Course . Cno = SC. Cnon结果_j消息5Grade1姜珊数据结施SCO2姜城操作茶前鸵口3苏国数据结构S1.04孙浩数据库原理花G5周强颜据库原理瓶。6周强薛赫并SLCJ李斌操作半薪77.0g黄国福军综计限0(6) Select DISTINCT Sn FROM Studen

23、t , SC WHERE Student . Sno =SC. Sno AND Grade >70M结果岛，瓯I | SrTH1L.iivbiii Hnaiiiirii j2姜珊3 李斌4 李思5孙港G周强实验六数据更新操作实验目的：1 .掌握使用Transact-SQL语句对数据库表进行插入、修改和删除的数据操作。2 . 了解Transact-SQL语句对表数据操作的灵活控制能力。实验内容：1 .向Student、Course、SC表增加、修改、删除学生的基本信息和课程及 成绩信息。2. 用SQL®句实现1中的功能。增加学生的基本信息：INSERT INTO Student

24、( Sno , Sn, Sex , Age , Dept , IDCard , Dno)VALUES ( '07，张丹，女，20 ,管理学院，07', '4');Select * FROM Student ;口诘栗匕消息8- rr5n2际"川IDCardOmD101要姗女1E管榷011202TJE女17彝格的0223(J3到皓男21建祥例033AM要里20044505季斌男19内领0516Q6雷虎玄A贡学际0E2707印丹立20管榷074修改学生的基本信息:UPDATE StudentSET Age=20WHERE Sno ='02'S

25、elect * FROM Student二3结果jij消息1SnoSn5班AgeDeptIDCacdDno1| DI姜珊女ie管理学院0112TT李思女20堤格学院(J22303孙浩男21钱辫学院033404周强男20科教学院0 d4%05季斌男13大文学院061g06黄琪女21人文学院062707张丹女20管理学院0P4删除课程信息DELETE FROM CourseWHERE Cn='数据库原理Select * FROM Course实验七索引及其应用实验目的：1 .掌握创建索引的方法。2 . 了解不同索引对查询性能的影响。实验内容：为JWG擞据库中的3张表创建聚集索引和非聚集索引

26、，并能管理和维护所 创建的索引。CREATE UNIQUE INDEX SCI on Student (Sno,Sn)聚集及非聚集Student表id 口索引PK_Stndent 兼集)市5CI保一 J非聚集)CREATE UNIQUE INDEX SSno ON SC( Grade )聚集及非聚集SC表I- _ZI dba. SCS 列/ Sud TR, char OX ruot'Cm 喀 char Cl), not田田田a国 Cirad« (Ifcitr jJ. (4 1 i 健约束kiltS索引离FK_文鼐集,tj SSno (Wt-1 i 非聚宾：)CREATE CL

27、USTERED INDEX CourseCno ON Course (Cno ASC) 聚集及非聚集Course表 - J dbo. Cooirs日口列因 Crto (ehartl)no 1. null)= Cik (varchar C20), not nuLl置 Dno (出近(1 J/ null)国 Credit (smallint> null)田口碑田一约束3 K触发器-3索引Jl CcmlhU唯一，非霰集).国 CiurseCrw 改集)U.I b谢白实验八实现视图实验目的：1 .掌握创建视图的方法。2 .掌握视图加密技术。3 .学会通过视图来修改数据 实验内容：1 .创建和检验

28、视图2 .修改视图3 .通过视图修改数据1、CREATE VIEW ST AS Select Sno , Sn, Dept FROM Student日LJ祖国l+ ,_j系好视图列I133 口-Q 国j dbo.SI3tlq (ch4r(5), not null) Sil char (20) , not null) Dipt (vureKii' CZOX null2、CREATE VIEW S AS SelectSno , Cno, GradeFrom SCINSERT INTO S( Sno, Cno) VALUES ('07' ,'1')E 一(视图

29、t _j系统视图曰 Ej dbfi. S曰L_J列ih Sno (char (5), not mill).Cm Cchar U not null)i =1 Gfade (d£cin&l (4, 1), null)3、f I鼬幅黑a后心*urAttdC3376 iD05 ni C2 06 0 (H工 1 J +2 +77 0 bojo 01 0 05 jO eeo 9L.004395 0邛AAjllMEAiULUPDATE ST SET Sn ='张宾 WHERE Sno ='05'SnoSrCept01姜珊管理学院。之李思鳗济学院03孙浩资需学院04周

30、强科熟学院k05有乂文学画：it黄琅人文学院07阪丹管理学院米A/itlAtiZ实验九实现存储过程实验目的：1 .掌握创建存储过程的方法。2 .掌握带参数和不带参数的存储过程的创建方法。3 .掌握显示存储过程信息的方法。实验内容：1 .创建不带参数的简单存储过程。2 .创建带输入参数的存储过程。3 .创建如下带输出参数的存储过程，并写一个调用该过程的例子。 根据输入的学号、课程号，输出其成绩。1、CREATE PROCEDURE Student_info AS Select Sno , Cno FROM SC WHERE Grade BETWEEN 60 AND 84 ;s 口系统存赭过程-

31、目 dto.日口参数踣返回mt eer由伫3函数2、CREATE PROCEDURE Grade_info Sno char (5) AS Delete FROM SC WHERE Sno =SnoB U存储过程 kt d系既存储过程 - "ibd行号日片_山£。 尸口蝴国SSno (char，输入,无默认值) 重遢回 mte ger-+ 匡 Jlbo- S tqdentinfo citri3、CREATE PROCEDURE there_info Sno char (5), Cno char ( 1),Grade char (9) output AS Select Gra

32、de FROM SC浮诘过程WHERE SC. Sno =Sno AND SC. Cno=Cno;回LJ±系境存储过楞+ S dt«r(Jrade_inf?+同 dto. S Lndent_irtfo哥(H。- there_in£oIB 口参数密flSno (chwr工输入无弑认信疯Cnc (ckar (1),辎入，无默认值)那ale (ckar (9输入/输出，无默*B 逅回 int*cir实验十实现触发器实验目的：1 .创建维护数据完整性的触发器2 .创建强制复杂业务规则的触发器实验内容：针对学生表（Student）、课程表（Course）、学生选课表（S。和

33、表9-4, 实现如下实验：创建一个名为stu_insert的触发器，当向学生表 Student插入记录时，自 动更新班级表中的Class中的学生人数Number表9-4班级表（Class ）ClnoSpecialityNumber95311r信息管理295312财务管理2-J Jbcn Class 日口列IT Cino MzharQQL null)目 Specity Gichur(10 mill) Humber (inf, null)i+ J i 独CREATE TRIGGER stu_insert ON StudentFOR INSERT, UPDATEASSelect * FROM Stu

34、dent , insertedBEGINUPDATE Class SET Number =Number +1WHERE Clno =( SELECT Clno FROM inserted ) END圜国国国S -d , S tulent列键妁束触发器ra国口索引 + 1 纬计孱身实验十一SQL Server 的安全管理实验目的：1. 掌握的 SQL Server 安全性机制。2. 熟练掌握实现权限授予和回收的方法。3. 掌握设计和实现安全保护机制的方法。实验内容：1. 设置 SQL Server 2008 登录身份验证模式。2. 添加 SQL Server 身份验证登录账户。3. 服务器角色的

35、管理。（ 1）查找所有服务器角色。（ 2）尝试添加或删除服务器角色（ 3）尝试修改服务器角色的权限（ 4）将账户CreateDB加入到Dbceator角色成员中（写出其SQL®旬）。4. 添加和删除数据库用户角色。（1）使用图形化工具和SQL语句两种方法，为JWG散据库添加TestDB, 并在默认状态下查看其数据库角色类型。（ 2）授予CreateDB操作JWG擞据库的所有权限。（ 3）尝试为数据库新增数据库角色。（ 4）了解服务器角色和数据库角色的区别。5. 授权和回收权限。（1）授予TestDB对Student表执行SELECT勺权限。（2）授予TestDB对SC表执行SELEC

36、T INSERT勺权限，并允许其将该权 限授予其他用户。（3）收回 TestDB 的 SELECT口 INSERT限。1、a要果点性-ibis选择页理常规.S脚本始郡助厢方舞角色堂用户映射 F安全对象 宜状态服务器能色用于向用户授予服给岩范国内的安仝挣松口月演器角色圾)；0OOO®in>ib ere at or di skadjnin proc«i5&dnbin publicsecnri tyadmin 5 er r admin3.(1)查找所有服务器角色。(2)尝试添加或删除服务器角色(3)尝试修改服务器角色的权限（4）将账户CreateDB加入到Dbcea

37、tor角色成员中（写出其SQL®句）| SELECT DEPAKmMTJIAME AVH鼬GE_J*URTfRLM HEf.SAUEY, MS. MPmMENTS DEFWHERE DEPJAIAR. DEPJJUMEHT IB=DEP. rETR.TMENT,_iaW）心宓以0_1虹猛丫为血：田N的哥芟睛 畲detarthektjimieAVWE-SALART11 Finance-8601.353335333332 M&k&timg则。3 Futli e E«1 iti or.5 -loaao4 Extent ivt,19333. 33333333335

38、人co皿tin宣3548 Nuinw sourees -a-BECIO4.添加和删除数据库用户角色。(1)使用图形化工具和SQL语句两种方法，为JWG散据库添加TestDB, 并在默认状态下查看其数据库角色类型。fea»e rp_>ddl«f if. 1 IurttiB-,/JftLSQL Serve-?理学鼻慢"EeDBi常枫|南黄法鼻邑低调库向可|可胞B蟀陶尸11.<Ncrii. 而H.r «n«Ml nn-db zjubatwnpcb 5 女口日UK（fcjtWierrfr accesMtifw ct_3RLFi3drTin de edadon(2)授予CreateDB操作JWG擞据库的所有权限(3)尝试为数据库新增数据库角色。Li.MZPZTEST；角色分限信息直*色利甲产相ZPZTEST行配年户型何尸阻名由电PERSEMTWBG dtr PtrfonaltntvdcklvngPOSTp。钮为GESTION DE PRODUCTI