sql数据库实例(数据库入门)word版本

1、sql数据库实例数据库入门数据库设计及应用实验一、实验内容创立数据库：包括 Student, Course, Enroll, Statistic藁,表的结构如下：Student (sno, sname, age, sex)Course (cno, cname, credit)Enroll (sno, cno, grade)Statistics (sno, cNumber, creditSum)说明：cNumber是sno学生选修课程的数目；creditSum是sno学生 选修课程的总学分.1、 设计并在MS SQL Server 2000中创立以上表结构,并设置完整性约束.2、 查询所有选修课

2、程的学生的根本信息、课程信息及相应的测试成绩.3、 查询所有学生的信息,假设已选课就还要给出选修课程的信息及测试成绩.4、 查询所有课程的信息,假设课程有学生选修就还要给出选修课程的学生的信息及测试成绩.5、 查询选修名为“数据库的课程的测试成绩最高的学生的信息.6、 对Student的age创立规那么,满足18ge5,并给出验证实例及验证结果7、 创立触发器：当学生选彳一门新的课程后,Statistics表的cNumber自动力口 1,且creditNumber自动增力口新选课程的学分.8、 创立视图：找出所有已修学分超过 6、所修课程平均分不低于60的学生的根本信息、以及所修课程的平均分.

3、根据平均分排 序,假设平均分相同根据学号排序.9、对于如下一组数据操作：(1) select * from course;(2) select * from course where cname瞰据库'(3) select * from course where credit=3(4) select * from course where credit>2 and credit<5(5) update course set credit=3 where credit=2对Course表的credit属性创立索引,并给出上述查询在创建了该索引后的执行方案；去掉Course表的

4、credit属性上创立的索引,并给出上述查 询的执行方案.对上述有无索引个查询执行的情况进行比照分析,总结出 什么时候索引有效10、 将表Student Course Enroll作内连接的结果发布为 HTML 网页格式.实验步骤及分析过程1、设计并在MS SQL Server 2000中创立以上表结构,并设置完整性 约束.(1)创立数据库SC.SQL语句为：create database SC 创立后的数据库如图1.图i创立数据库的结(2)创立表创立学生表:create table Student(sno varchar(10) not null primary key, sname var

5、char(50) not null, age int, sex varchar(2) not null创立课程表:create table Course(cno varchar(10) not null primary key, cname varchar(50) not null,credit int not null创立注册表:create table Enroll(sno varchar(10) not nullreferences Student(sno), cno varchar(10) not nullreferences Course(cno), grade int, prima

6、ry key (sno,cno) )创立选课情况表：create table Statisticss(sno varchar(10) not null primary key references Student(sno),cNumber int not null, creditSum int not null, )创立后的表如图223 Coursedbo月户2021/7/5 21:36:53目 dtp口 pert 借dbo僦201W22 10:54:55国 Enrolldbo用手2021/7/5 21；40：01Statistics-sdbo_理1/仍 21:44*1H Studentdbo

7、一一'丽一一一2021/7/5 2133:04图2创立表的结果(3)插入数据Student 表:李贵斌',22,'男') 冉从宝',21,'男') 杨文学',20,'男') 杨璐',22,'女') 李小萌',20,'女')insert into Student values ('2021001', insert into Student values ('2021002', insert into Student values (

8、9;2021003', insert into Student values ('2021004', insert into Student values ('2021005',Course 表：insert into Course values ('001','数据库',4)insert into Course values ('002','java',3)insert into Course values ('003','操作系统',5)insert in

9、to Course values ('004','软件工程',4)insert into Course values ('005','计算机英语',2)Enroll 表:insert into Enroll values ('2021001','001',89) insert into Enroll values ('2021002','003',98) insert into Enroll values ('2021003','002'

10、;,85) insert into Enroll values ('2021001','004',88) insert into Enroll values ('2021004','001',89) insert into Enroll values ('2021002','001',90)insert into Enroll values ('2021003','003',78) insert into Enroll values ('2021002'

11、;,'004',79)(4)查看表的内容select * from Student结果如图 3.soosname age sex1200S0012 200S00232021003«?0080045 1 2021005斌宝学 萌 贵州文璐小 李由杨杨李2 10 2 02 2 2 2 2居里男女友图3表Student中的select * from Course 结果如图 4.cnacnajieGredit1001敬据族q200233003操作系疏54叩q软件工程q&005计算机英诺2图4表Course中的select * from Enroll 结果如图 5.sn

12、oenograde12006001001SS220210010048fe320210020019C42021002口 gB85202100200419620210030028E7202100300378S20210040D185图5表Enroll中的2、查询所有选修课程学生的根本信息、课程信息及相应的测试成绩.SQL语句为：select a.sno,a.sname,a.age,a.sex,b o,b ame,b.credit,c.gradefrom Student a,Course b,Enroll cwhere a.sno=c.sno and b o=c o220DE001李贵斌22004软

13、件工程4883200S002再从宝21001数提库 49042021002再从宝21003操作系统59852006002再从宝21004软件工程4792003003杨文学20002java.385120Q5QO1李贵斌22 R U01数据库 4 甜抗囊僻辘r普息执行结果如图6所示.snosiLOJueagesexCTLOcnaiLecreditgrade3、查询调6裴悟榭箕1®甯庭要给出选修课程的信息及考试成绩.SQL语句为：select a.sno,a.sname,a.sex,a.age,d o,d ame,d.credit,d.gradefrom Student a left o

14、uter join(select b o,b ame,b.credit,c.grade,c.snofrom Course b,Enroll c where b o=c o)don a.sno=d.sno执行结果如图7所示.sno sname sex 咫已 eno cnaine credit grade12021001李贵斌勇22021001李贵弑奥32000002冉从宝里42006002冉从宝男52021002冉从宝奥62000003极文字勇72021OD3瓶文学里2021004桶瑞女92021005李小萌女22 1022 00421 001r-i 必21 00420 002库工库系工a据件据

15、作件va数软敷操投ja20003操作系统522001 数掘屋 420NULL NULL NULL7S e& MULL9 8 0 8 9 58 8 9 5 78图7所有学生选课情况及成绩信息执行结果分析：不但列出选了课程的学生而且还列出来没选课程的学生信息4、查询所有课程的信息,假设课程有学生选修就还要给出选修课程的 学生的信息及测试成绩.SQL语句为：select a o,a ame,a.credit,d.sno,d.sname,d.sex,d.age,d.gradefrom Course a left outer join(select b.sno,b.sname,b.age,b.s

16、ex,c.grade,c ofrom Student b,Enroll c where b.sno=c.sno)don a o=d o执行结果如图8所示.end cnanecredrtsnosrLanme s =agegrade10C1数据库420(3001李贵斌用22892001数据库42003002冉吠宝»21303001数据摩42021004杨璐文2289to002 java3200B003梯交学更2D855_CIO 3操作系统52000002冉从宝里2198003操作系疏E20218003秘文学里20787004软件工程4200S001李震威更22888004软件工程4200

17、S002再从宝勇2170执行统民错舸英膏但列出神岫课程偏危息种派生选课慵跑而且还列出来没被选课程的信息.图8所有课程、选课的学生及成绩5、查询选修名为“数据库的课程的测试成绩最高的学生的信息(1)先查看选了数据库课程的学生信息.SQL语句为：select a.sno,a.sname,a.sex,a.age,b ame,c.gradefrom Student a,Course b,Enroll cwhere a.sno=c.sno and b o=c o and b ame=' 数据库执行结果如图9所示ST1OsnameaeGnamegrade12021001李贵斌里22致据库8S22D

18、OSOD2冉从宝男21数据库9032003004玄22数据库89图9选修“数据库课程的学生信息执行结果分析：有三位学生选修了数据库课程.(2)查询选修“数据库课程的测试成绩最高的学生的信息.SQL语句为：select a.sno,a.sname,a.sex,a.age,d ame,d.gradefrom Student a ,(select b o,b ame,c.grade,c.sno from Course b,Enroll cwhere b o=c o and b ame=' 数据库')dwhere a.sno=d.sno and d.grade=(select max(

19、grade)from (select b o,b ame,c.gradefrom Course b,Enroll c where b o=c o)dwhere d ame='数据库')执行结果如图10所示.| knoHex age cnane trade |120口80口2冉从宝国 2；数据屋口图10选修“数据库课程成绩最高学执行结果分析：与图9结合比拟,图10 了列出了选修“数据库课程成绩最高 学生信息.6、对Student的age创立规那么,满足183gg25,并给出验证实例及验证结果.1创立规那么并且绑定它.SQL语句为：create rule age_rule asag

20、e>=18 and age<=25sp_bindrule age_rule,'Student.age'2插入数据检验.insert into Student values '2021006',' 李志锋',20,'男'所影响的行数为1行insert into Student values '2021007',' 李小龙',32,'男'insert into Student values '2021008',' 杨一民',17,'男&

21、#39;效劳器：消息513,级别16,状态1,行1列的插入或更新与先前的CREATE RULE语句所强制的规那么冲突.该语句已终止.冲突发生于数据库SC',表Student',列'age'.语句已终止.执行结果分析：规那么设置成功.7、创立触发器：当学生选修一门新的课程后,Statistics表的cNumber自动力口 1,且creditNumber自动增力口新选课程的学分.SQL语句为：create trigger insert_statisticss on Enrollfor insert asbeginupdate a setcNumber=cNumber

22、+d.countCno,creditSum=creditSum+d.sumCredit from Statisticss a inner join(select b.sno,count(b o) as countCno,sum(c.credit) as sumCredit from inserted binner join Course c on b o=c o group by b.sno)d on a.sno=d.snoinsert Statisticss select b.sno,count(b o) as countCno,sum(c.credit) as sumCreditfrom

23、inserted b inner join Course c on b o=c owhere sno not in (select distinct sno from Statisticss) group by b.sno end没有在Enroll表插入任何数据时表为空,如图11所示.SHOcNuuibercreditSi_un图11表Statisticss的初始信在Enroll表插入以下数据：insert into Enroll values ('2021001','005',70)insert into Enroll values ('2021006

24、','001',59)插入数据后的结果如图12所示.snocUujutercredit Sum120D3001122M0S00614图12表Statisticss的信息 执行结果分析：当在Enroll表中插入选课情况时,在表上建立的触发器insert_statisticss就被触发,进行 cNumber自动力口 1和creditNumber自动增力口新选课程的学分8、创立视图：找出所有已修学分超过 6、所修课程平均分不低于60的学生的根本信息、以及所修课程的平均分.根据平均分排序,假设平均分相同根据学号排序.SQL语句为：create view Transcript a

25、sselect top 10 d.sno,d.sname,d.sex,d.age,d.avg_grade,d.creditSumfrom (select a.sno,a.sname,a.age,a.sex,avg(grade)as avg_grade,sum(credit) as creditSumfrom Student a,Course b,Enroll cwhere a.sno=c.sno and b o=c ogroup by a.sno,a.sname,a.sex,a.age)dwhere avg_grade>=60 and creditSum>6order by avg

26、_grade,d.snoselect *from Transcript执行结果如图13所示snoEnajne seaagecredit Stun12006003顿文字国如81822C0EOO1男22821032021002冉从宝国21即1342006004栩璐 女22的8图13 Transcript视图表执行结果分析：列出的学生都按平均成绩大小从低到高排列且平均分都大于60.当平均分相同时,表中冉从宝和杨璐就根据学号大小从小低到高排列.9、对照对Course表的credit属性创立索引和去掉 Course表的credit属性上创立的索引查询的执行方案.对Course表的credit属性创立索引

27、为:create index credit_index on Course(credit)(1) 比照有无索引credit_index情形下,Select * from Course的查询的执行方案分别如图14、图15所示.Clustered Index Scan扫壶爰典赛引,可以扫病全部内容,也可以口扫强 一个范围.Cluftered IhWok扫盘陵索引可以扫描至部内容r蛔以同扫搐TE围,物理操世 重辑锲作：林计行大小： 预计IfO本钱： 预计本钱; 预计执行次数二 顿计或本： 预计子树或本二Clustered Inde Scan Clustered Index Scan 547 0.03

28、75 Q.OOOOW1.00.037662(100%U.D376物强操作： 霞峰： 解十：中数： 颈计行大小： 藤计IfC礴本： 预计CPU或本; 预计执行次麒 犊计本钱： 预计子树本钱：Clustered index ScanClurtered Index Scan547 0,0375 0.WWJS41.0O.-0 37652( 100%)0.0376拳数：QBJECT；CSq.dbQJCQur5clPK_Course_1920BF5Cj学数；OBJECTt(SC,dbodCour5e.pK_Cour5e_192DBF5CD图14有索引情形下的查图15无索引情形下的查结果分析：由图14,、图

29、15可知当查询是针对整个表进行,创立索引和不创立索引,查询的执行方案都是一样的.(2) 比照有无索弓I credit_index情形下,select * from course where cname据库的查询的执行方案分别如图16、图17所示Clust«r&d Index: Scan扫描翳集索引.可以扫痛全部内容也可以R扫括 圆Clustered Index Scan扫空髅美蓄VL可以扫描全笠内客,也可心扫芍 围.懂理噪作 设辑操作： 期计行计数： 陵计行大小： 谕fU.本钱：计cpu本钱: 陵计执行次数： 速计本钱； 计子树本钱：Clustered 1 rid ex Sc

30、arCIustereci Index Scan1270.0375O.ODODS41.00.07662(100%)0.0376博建操作： 矍辑操作； 预计彳曾数； 顿计¥送小： 顿计I/O本钱： 解十CPU成积 预i+执行次数； 预计本钱： 预计子树本钱：Clustered inctex Scan Clustered Ind年篮 Stan 1270.03750,0000941.00.376屋口00% 0.D376OBJECTSq,db0jCourSe,PK_Cour5?_l&20BF5Cj. WHERE;(Course.crame = ®1季致：口 BJEES,gbM

31、8 b 50附_8"牌920BF5C)r WHERE：(Cour5t,cname=©l)图16有索引情形下的查图17无索引情形下的查结果分析：由图16,、图17可知当条件针对的字段不是创立了索引的那个字段 时,索引是无效的.有索引和无索引,查询的执行方案都是一样的(3) 比照有无索弓I credit_index情形下,select * from course where credit=3的查询的执行方案分别如图18、图19所示Index 5t«k曲聚暖案素弓I中的特定范围的行.Clus.tcred Index Scan扫描聚集素引r可以扫描全部内客r也可以门扫痘

32、一围.物理探作： 巡辑操相 肉十行TH群 预计行太小； 项计U 口本钱： 函十CPU本钱: 预计就疗次触 陵i+本钱； 侵计子树本钱：Indfrx kIndey W-k13S 0 00532 0.0000301,00.006408(15%)000&4Q物理厚作： 建辑操作： 预计行i十数： 计行大小： 候计“口本钱： 颈计CPU成翻 程计执行次数： 丽+成为 预计子树本钱：Clustered Index SeanClustered index Scar145 0.0375 ),0000&41,0.打 662(100%)0.0376券勘OBJ ECT；(&C, dboJJ

33、Couee.匚redJtjnd ex), SEEK;(Coursecredit-Convertn 2J ORDERED FORWARD叁数：OBJECT;(SQ4dbo.Cojr5e4P<_Ccurse_ig 20BF5Q1 WHERE:(Course,credit=Converta 1)结果分析：由图18,、图19可知where条件为credit的属性,索引起效.有索 引的查询效率比没有索引的查询效率高数十倍.(4) 比照有无索弓I credit_index情形下,select * from course where credit>2and credit<5的查询的执行方案

34、分别如图20、图21所示.Index £.仙拧捕非嘉集宝引中的得定范围的行口Clustered Index Scan 郎深集素引可以扫括全部内容,也可以只扫描 一个壬围,物理糅作： 逻辑锲相颈计行计条 预计行大伙 陨计本钱： 颈计CPU本钱: 预计执疗次蠹 预计本钱： 预计子树本钱；Index Seek Index -3 翔 0.00632 0.0000821.00.00&41051%)0,00641物瞠操作： ,哂 颈计用十数： 瘦计彳亍大小t 预计I/口本钱: 预计CPU就本; 预计执行次数： 赖计本钱 预计子财威本：Clustered ndK Scan Clustere

35、d In det Scan345 L0375 0,0000841.00.037662C100% 0.0376MfcOBJECTiSq4dba40iurseJeditJnd 曰kR £ E E K: ('C ou rs e. cred it > C&mvert( ©1) AND Course,credit < Convert口 a2m ORDERED FORWARD叁斑OejECTitCJ.tdbol4CQLirsel.pKcursl 20BF5qI. WHERE;(Coursellcredit<Converia 2) AND Course.

36、(credlfl>CiurvertU©1)图20有索引情形下的查图21无索引情形下的查结果分析：由图20,、图21可知where条件为credit的属性,索引起效.有索引的查询效率比没有索引的查询效率高数十倍o(5) 比照有无索弓I credit_index情形下, update course set credit=3 whereIndex Seek扫括三座集案引白的特定范围的行.credit=2的查询的执行方案分别如图22、图23所示Clustered Index Scan扫描聚集素引.可中扫猫全部内容r也可以以扫搭 一个通围.物理理作： 遭备操佗预计行大小： 陨计UCi本钱

37、二 要计CPU版本: 预计执行欠瓢 速计环 葡十子树本钱：Index Seek Index1380.00632O.OOOOBD1,00 做5408(51%)0.00&4D拗理樨作 逑辑操相 战十沅HK； 预计行大小： 预计I/O本钱： 预计CFU本钱: 预计执行次覆 预计本钱： 预计子酎本钱：Ctustered Inde-x ic anCluter&d Inde-K Srari133 0,0375 0.0000341.00加 7662(77%)QG376券数：OBJ £ CT: (S-C,(d bo j-Course, cred it_ind 网).SEEK;(Cou

38、rse.credit = Convert 1) ORDERED FORWARD叁散：.£三匚 TJlSCJJdbu1: C oursej, _Coursel 20BF5q), WHERE：(Course.credit = ConvedQ 2) ORDERED图22有索引情形下的查图23无索引情形下的查结果分析：由图20,、图21可知where条件为credit的属性,索引起效.有索 引的查询效率比没有索引的查询效率高数十倍o索引小结:索引是对数据库表中的一列或多列的值进行排序的结构,有助于更快地获取 信息.只有当经常查询索引列中的数据时,才需要创立索引.折衷：索引提升查询速度vs.索

39、引占用磁盘空间,降低数据更新执行速度.假设应用中大多数是数据更新操作(插入、删除和修改).那么应限制索引的数 量.10、将表Student Course. Enroll作内连接的结果发布为 HTML网页格式.(1)在企业治理器的限制台中选中 SC数据库,“工具 “向导 “治理Web助手向导如图24所示.Wub 助手向导一(local).*稣 I次迎使用Web助手向导本向易可群助您将SL Serv匹表中的麴据发布琳科曲页 上"使用本向号,麻可i：从运行SQL Server的脂畀器上将数据发布到Web页什0指定W时页更新的频率,指定所发布页的格式“在使用同他助手向导ZS,保证在Web站点

40、上要引用的所有SQL Ser/&f数据库初存储过程均已创立.下一步切 >| 取消 图24启动Web助手向导(2)点击“下一步,选择数据库SC如图25所示Web动手向导 一flora lj选择题据库选探包含要发布到页的数据表和列的数据库.要从哪个数据库选择表和列 数据库名称.上一步(S) I下一我习图25选择数据库SC(3)在选择SC数据库后,单击“下一步,选择和设置如图26所示图26 Web作业命名和设(4)单击“下一步,在弹出的“编写ransact-SQL查询对的话框中的文本框中输入表Student、Course Enroll作内连接的查t®语句,如图 27所示.编写Transact-SQL直询需写 TranzajctSQL音海假设句,吼在运行后避回结果逑,在史本字符串两 边加单引导作ransact-SQL ：belect a sno.a sname a.age a sex b eno b cname b credit c grade from Student a Ccurse b Enrol cwhere a sno-c sno d d b cno=c cno|4