2023年北京联合大学数据库实验报告

ﻩ ﻩ 北京联合大学《数据库》实验报告ﻩﻩ题目：**＊＊**＊＊＊ﻩ 姓名：*＊＊****ﻩ 学号：*＊＊＊****** ﻩ专业:计算机工程ﻩ 编制时间:****＊*＊＊ﻩ 北京联合大学编制

ﻩ ﻩ 实验一一、实验目的学习MＳSQLSｅｒver2023的安装、启动方法,注册ＳQＬServｅr服务器的方法。熟悉SQLSｅｒvｅr的工作环境掌握使用向导和命令建立数据库的方法掌握数据库常用选项的设立方法掌握建立数据表的方法。二、实验内容ﻩ采用SQＬ语句创建数据库CＲＥATEＤATABASEＯrdeｒMag2ONＰＲIＭARY(NAMＥ=ＯrｄerＭaｇ2＿Data，ＦＩＬＥNＡME＝'C：\ＰrograｍFiles＼MicroｓoｆtＳＱLServeｒ＼MSSQL\dａta\ＯrderMag２_Dａｔa．ndf＇，SIZE=10MＢ,ＭAXSIZE=200MＢ，FＩＬEGＲOWTH=10%)LOＧＯＮ（ＮAME=ＯｒderMaｇ2_Lｏg,FILENＡＭE='C:＼PｒｏgｒaｍＦileｓ＼MicrosoftＳＱLＳerver＼MＳSQL\data\OrdeｒＭag2＿Log．LD',SIZＥ=3MB,MＡXSIZE＝1００ＭB,FILＥGＲOWTH=１0%)GO三回答问题创建Stｏrｅ、Ｃustomer、Order1三个表useOrｄerMag２cｒeaｔetableStore（ﻩＰnｏcｈar(6）pｒimarykeynotnuｌl，ﻩPnamevarchar(20）noｔnull,ﻩPtyｐeｃｈar(2），ﻩPnumint）ﻩcreatetａbｌeCuｓtomeｒ( Cnocｈaｒ(6)primａrｙkeynotｎulｌ, Cnamｅvaｒchar（2０)nｏtｎull， Ctelvarchar（12)nｏtnull,ﻩＣaddｒｖarｃhaｒ(50), Czipｖaｒchar(６))crｅateｔａｂleOrder1（ Onoｃhaｒ(6)primarykｅｙnoｔnuｌl,ﻩCｎochar(6),ﻩPnocｈar(６），ﻩOｎumｉnｔ,ﻩOsumｉｎｔ,ﻩOｄatedａｔetime)alterｔabｌeOrder1ａｄｄconsｔrａintfk_cuｓｔomer_ｉdfoｒeignkｅｙ(Cno)rｅferencesCｕsｔoｍer(Cno);aｌteｒtableOrｄeｒ１ａｄdcｏnstraintfk_Store_idfｏreｉｇnkey(Pnｏ)reｆｅrencesSｔore(Pｎo）;alｔeｒtaｂleOrdeｒ1ａddcｏnsｔraiｎtcheck_Onumｃｈeck（Ｏnum＞=0aｎｄＯｎum<=１00)alｔertａblｅOrdｅr1addｃonsｔrａiｎtcｈecｋ_Oｓumcheck(Osum>０)操作过程中碰到的问题是“oｒdeｒ”是数据库中的一个关键字，不能把它作为表的名字,所以在创建oｒｄeｒ表的时候在其后面加上数字或其他字母来以示区别。四实验小结 通过本次实验学会了MＳSＱLＳｅrver2023的安装、启动方法,注册SQＬServer服务器的方法;和如何使用向导和命令建立数据库和数据库常用选项的设立方法及建立数据表的方法。ﻩ 实验二、数据维护及简朴查询一、实验目的:学习使用查询分析器掌握使用Ｉnserｔ、Ｕｐdａte、Deletｅ命令维护数据表的数据的方法纯熟掌握简朴SQL命令的使用二、实验内容:1．INSERＴ命令输入数据使用Insｅｒｔ命令分别向OrderＭag数据库中的三个表中输入如下５条数据。１)Stoｒｅ(Pnｏ，Pｎamｅ,Ptｙpe,Pnｕm)库存（零件号，零件名称,零件类别,零件数量)PnoPnａｍePtypｅPｎumＰ1齿轮传动2０0P2蜗杆传动3０P３螺栓标准120P４垫圈标准500P５螺母标准10202)Order(Ｏno,Cno,Pno，Oｎum,Osum，Odate)订单(订单号，顾客号,定购零件号,定购数量,订单金额,签订日期）OnｏCnoＰnoOｎumOsｕｍOｓumO１Ｃ2P２1０01０002023-4-8O2C3P３1010020２３-5-2O3C１P220２00２023-4-５Ｏ４Ｃ1P4２20２02３-2－4O5C3P５1102023－3－1３)Custoｍeｒ(Cno,Cnamｅ,Cｔｅｌ,Caｄdr,Cｚip）顾客(顾客号，顾客名称，电话,地址，邮编）CｎoCｎameCtｅlCａｄdrCziｐＣ1北京联合大学北京１00101C２上海贝尔上海C3LＧ深圳４12１00C4华为(北京)北京C５香港航空香港14２553先修改Stｏre表的列Ptyｐe的长度,再进行插入操作,如下ＡLTERＴABＬEStoreALTERCOＬUMNPtyｐecｈaｒ（１0)IＮSERTINＴOStore（Pｎo,Pname，Ptype，Pnｕm)VＡLUES('P1','齿轮＇，'传动'，2０0)ＩNSERＴＩＮＴOStoｒe(Pnｏ,Ｐｎame,Ptyｐe，Pnum）ＶALUES('Ｐ2','蜗杆','传动'，３0)INＳEＲTINTOStorｅ（Pno,Pnamｅ,Pｔｙpe,Ｐnuｍ)VAＬUES（'P3','螺栓','标准'，１2０)INSERTINTOStｏre(Ｐno,Pname,Pｔｙpe,Pnｕｍ）VＡLUＥＳ(＇P４',＇垫圈',＇标准',50０)INSEＲTINTOＳtｏre(Pno，Pname，Ptype,Ｐｎum)VALUＥS（'ｐ5＇,'螺母','标准＇，1020）IＮＳERTINTOＣｕsｔoｍｅr（Ｃnｏ,Cnamｅ,Ctｅl，Caddr,Cｚip)VALＵES(＇Ｃ1',＇北京联合大学＇,'','北京',＇１００１01')IＮSERTINTOCustｏmer（Ｃno,Cname,Ctel，Ｃaddr,Cｚiｐ)VALUES（'C2','上海贝尔＇,'＇,＇上海',''）ＩNSEＲＴＩＮTＯCｕｓtomer(Cno,Ｃｎａmｅ,Ｃtｅl,Cadｄr，Ｃziｐ)VALUES（'C3＇,'LＧ',＇'，'深圳','412１00＇)INSEＲTＩNTOCｕｓtomer(Ｃno,Cnａme,Cｔeｌ,Caddr,Cziｐ)ＶＡＬUES('C4＇,'华为(北京）'，null，'北京','')INSＥＲTINTOＣusｔoｍｅr(Cno,Ｃname，Cteｌ,Caddr,Czｉp）ＶALUES(＇C５'，'香港航空＇,＇'，＇香港＇,'142553')INＳＥRTINTOOrdｅr１(Ono,Cno,Pｎo,Onum,Ｏsum,Odate）ＶＡＬUES（'Ｏ1','C2＇,'Ｐ2'，１00，10００，＇２023-４－8＇)ＩNＳERTＩＮTOOrder1(Onｏ，Cｎo,Pno，Ｏnｕm,Osuｍ,Odate)VALUES('Ｏ2＇,'C3',＇P3'，１0,100，'２023－5-2'）INＳＥRTINTOOrdeｒ１(Ono,Ｃno，Pno，Oｎum,Osuｍ,Odａtｅ)VALUES('O3＇，'Ｃ1','P2'，20,200,＇20２３－４-５')INSERTINTＯOrｄｅr１（Oｎｏ，Cｎo，Ｐno,Onｕm，Ｏsｕｍ,Odate)VALUEＳ（'Ｏ４'，'C1'，'P４',2,２０,'2023-2-4')ＩＮSERＴIＮＴOOrdｅr1(Oｎo,Ｃnｏ,Pno,Onuｍ,Ｏsum，Ｏdaｔe)VALUEＳ（'O5','C3','P5',1，1０,'20２3-3-1')2．把ｓtorｅ表中所有零件的数量增长一倍UPDATＥＳtoreSETPnum=Pｎum*２3.把oｒｄｅr表中签订日期在2023年之前的数据删除。dｅletefｒomorder1wｈereＯｄaｔｅ<'2０２3＇4.向store表中添加一列：零件颜色（Pcolour)；ALTERＴABＬＥStorｅADDPｃolｏurvarcｈar（2０）5.查询三个表中的内容ＳELECT＊FROMStoreSEＬEＣT*FROMCustomｅｒＳELECＴ*FROMOrder16.查询所有订单的金额，并按照金额的降序排列SELECTOｓumFROＭOrｄeｒ１ＯＲDERBYOsuｍDESＣ7.查询签订日期在２０23年的所有订单的信息。SELＥＣT＊FROMOｒder１ＷHEＲＥOｄａte<＇2023'８.查询顾客姓名中具有“北京”的顾客姓名和电话。SELＥCT*FROMＣｕｓｔoｍerWHＥRECnａmeLIKE'%北京%'9．查询库存数量少于１0０的零件号和名称。SELECTPｎo，PnａmeFROＭＳｔorｅＷHEＲEＰnum＜1０010．查询邮编为空的顾客的情况。ﻩＳEＬＥCT*FROMＣｕstomerＷHERECtelisnulｌ三、回答问题1)使用Inｓerｔ命令需要注意哪些问题ﻩ答:1）插入一条记录时最佳一次性插进去,避免主键列报错。或者把主键列设立为自动增长,这样就不用考虑主键那报错了2）尽量不用关键字做列的名子,即使要用也要在其外面加［]。2)Alteｒ与Updatｅ语句有什么区别 答：Alter用于修改表结构，而Upｄaｔe用于修改表中内容四、实验小结通过本实验学会了如何运用Iｎsert、Ｕpｄatｅ、Delete命令对表中内容进行修改。实验中碰到的问题是区别邮编为空(nｕll)的情况和空字符串(’’),。ﻩ ﻩ实验三、ＳQL命令使用（二）ﻩ一、实验目的:(1)学习、掌握分组与汇总的函数的使用（2)学习、掌握在SQL语句中使用函数的方法(3）学习、掌握连接查询的方法（4）学习、掌握子查询的方法ﻩ二、实验内容：1.查询订单金额大于10０的顾客的名称和电话；SELECTCname,ＣtelＦROMCustomer,Oｒdｅr１WＨERＥOrder1.Cno=Cusｔｏmer．CnoＡＮDOrder1．Osｕm>１002.查询所有签订订单的顾客的名称和邮编；selｅcｔCnaｍe,CziｐfrｏｍＣustomerｗｈｅreCｎoin(ｓelｅctｄisｔinctCnofromＯrdｅr1)3.记录每类零件的数量分别为多少；selｅctＰno,sｕｍ(Ｏnｕm）ｆｒoｍOｒｄｅr1ｇｒoupbyPnoﻩ４.记录每个顾客签订订单的次数;sｅｌｅｃtCno,ｃount(*)ｆroｍOrder１ｇroupbyCno5.查询所有顾客签订订单的情况(涉及没有签订订单的顾客)；ｓelecｔ*fｒomＣuｓtomerleftoｕterjoinOrder1oｎ（Ｃｕstｏmｅr.Cnｏ=Ｏrder１.Cno)６.查询没有卖过一次的零件号（没有订单）；ｓelectPnofｒomＳtｏrewherePnoｎotｉn(seｌｅcｔdｉstinｃtＰnofromOrdｅr1)7．查询每个顾客签订订单的金额总数;ｓｅｌｅcｔCno,ｓuｍ（Osｕm）frｏmOrder1gｒoｕｐbyCnｏ8．查询所有订单金额的平均值;sｅｌｅｃtａvg(Osum)ｆｒoｍOｒder19.查询至少签订过两次订单的顾客信息。selectCｎｏ，coｕｎt(*)tｉmeｓfｒｏｍＯrder1ｇrouｐbyCｎｏhaviｎgcoｕnt(*）>＝２ 三、完毕实验报告并回答问题1)外连接与内联接的区别在哪里答：外连接是指把舍弃的元组也保存在结果关系中,而在其他属性上填空值。内连接是指只有满足连接条件的元组才干作为结果输出。ﻩ2）使用子查询需要注意哪些事项答：ａ．一个子查询必须放在圆括号中。ﻩb.子查询的sｅlｅct语句中不能使用ordeｒｂy子句,orｄerby子句只能对最终查询结果排序。 ｃ.带有In谓词的子查询中,当In中为单一值时可用“=”代替，否则不可。ﻩｄ.带有比较运算符的子查询,将子查询放在比较条件的右边以增长可读性。ﻩｆ.子查询返回单值时可以用比较运算符,但返回多值时要用Any或Aｌｌ谓词修饰，而使用Any或Ａll时必须同时使用比较运算符。例>Ａnｙ,！=Alｌﻩg.带有Exists谓词的子查询不返回任何数据,只产生逻辑真值‘Ｔruｅ’或逻辑假值‘Fａlｓe’。使用存在量词Exisｔs后,若内层查询结果非空，则外层的where子句返回真值，否则返回假值；与Exｉsｔs相相应的是NotＥxists，使用ＮotEｘｉsts时,若内层查询结果为空,则外层的wherｅ子句返回真值，否则返回假值。由Eｘiｓｔs引出的子查询,其目的列表达式通常用*，由于带Existｓ的子查询只返回真值或假值,给出列名无实际意义。一些带Exiｓts或ＮotEｘists的子查询不能被其他形式的子查询等价替换，但所有带In谓词，比较运算符,Aｎy和Ａll谓词的子查询都能用带Exiｓts谓词的子查询等价替换。ﻩ四、实验小结ﻩ通过本实验学会了如何使用分组与汇总函数,对连接查询和子查询有了进一步的了解。 ﻩ实验四．视图和索引ﻩ一、实验目的:(1）掌握建立、维护索引的方法（2)掌握建立、使用视图的方法 二、实验内容：1.使用CｒeatｅViｅwWizaｒｄ创建视图通过公司管理器的ＣreateVｉewＷizaｒｄ创建视图1）在公司管理器中,单击菜单“工具”下面的“向导”2)展开“数据库”后，双击下面的“创建视图向导”３)单击“下一步”后,选择数据库OrderMａｇ,选择表orｄeｒ4)选择字段Ｏno,Cno,Pｎｏ,Onuｍ。5）输入条件语句ＷHＥREOnum>10０0，6）输入视图名称v_oｒder8）在公司管理器中的“数据库”OrderMag视图下查看视图v_ordｅr。９)在查询分析器中输入并执行语句SEＬECT*FROMv_ｏｒder10）结果如何?显示的字段是否为前面自己定义的字段?答，显示出满足条件的结果，显示的字段为当前自己定义的字段。11）删除视图ｖ_ｏrdeｒ。 ﻩＤｒopVieｗv_order2．在查询分析器中创建视图1)建立一个视图,涉及订单号、零件名称、顾客名称、订单金额等信息createvｉｅｗis＿osｃ(Ｏｎｏ,Ｐnａｍe,Cnaｍe,Osum)asselecｔOｒdｅr１.Onｏ,Stｏre.Pnａme，Cuｓtomer.Cnａme,Oｒder1．OsuｍfromOrder1，Custｏmer,SｔorｅwhereOｒdｅr1.Ｐno=Sｔore.ＰnoａndOrｄeｒ１．Ｃｎo=Cusｔomｅr.Ｃｎｏ2）建立一个视图,查询订单金额大于１0０00元的大客户信息。createviewis_ｃasSELＥCＴCnamｅ,OsumFROMis_oｓcWHERE(Osuｍ>10０00)3）建立一个视图，查询每个顾客签订订单的总金额ｃｒeatｅvｉewiｓ_sumａｓselecｔCno,sｕm(Osuｍ）SsumｆroｍＯｒder１grｏupbyCｎo3.察看系统提供的示例数据库ｐubs上有哪些索引，是什么类型的,列表记录下来。表名索引名索引类型authorsaunｍiｎｄ唯一eｍployeeemployee_ind唯一royschedtitlｅidind唯一salestitｌeidind唯一titleaｕthorauidind唯一ｔｉtleidind唯一ｔｉｔlｅstｉｔleind唯一4.为OrderMag数据库设计索引,并建立到表上。CrｅａteｕnｉqｕeｉndexｃnoindonCustｏmer(Cｎo);ＣrｅatｅuniqｕｅinｄexｏnｏindonOrdｅｒs（Ono）；CｒｅａｔｅuｎiqueinｄｅｘpnoｉｎdoｎＳtoｒe(pno);三、完毕实验报告并回答问题1）视图的建立有哪些需注意的问题答:Ａ在CREＡTEVIEW语句中，不能涉及ORDＥRBY，ＣOＭPUTＥ或者CＯMＰＵＴEBY子句也不能出现INＴO关键字B创建视图所参考基表的列数最多为10２4列 C创建视图不能参考临时表D尽量避免使用外连接创建视图E在一个批解决语句中，ＣREＡTEVＩEＷ语句不能和其他TRＡＮＳAＣT-SQL语句混合使用

2）索引的作用是什么?有什么分类?每类有什么特点？答:1）创建索引可以大大提高系统的性能。

ﻫ第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

ﻫ第二，可以大大加快

数据的检索速度,这也是创建索引的最重要的因素。

第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别故意义。

ﻫ第四，在使用分组和排序

子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。

第五，通过使用索引,可以在查询的过程中，使用优化隐藏器,提高系统的性能。

2）根据索引的顺序与数据表的物理顺序是否相同,可以把索引提成两种类型。一种是数据表的物理顺序与索引顺序相同的聚簇索引,另一种是数据表的物理顺序与索引顺序不相同的非聚簇索引。

3)聚集索引（1)一方面指出一个误区，主键并不一定是聚集索引，只是在SQＬSEＲVＥR中,未明确指出的情况下,默认将主键定义为聚集,而ORACLE中则默认是非聚集,由于SQLSERVER中的ROＷIＤ未开放使用。（２)聚集索引适合用于需要进行范围查找的列,由于聚集索引的叶子节点存放的是有序的数据行,查询引擎可根据WＨERE中给出的范围,直接定位到两端的叶子节点，将这部分节点页的数据根据链表顺序取出即可;（3）聚集索引尽量建立在值不会发生变更的列上，否则会带来非聚集索引的维护；(4)尽量在建立非聚集索引之前建立聚集索引，否则会导致表上所有非聚集索引的重建；(5）聚集索引应当避免建立在数值单调的列上，否则也许会导致IＯ的竞争，以及B树的不平衡，从而导致数据库系统频繁的维护B树的平衡性。聚集索引的列值最佳可以在表中均匀分布。2、非聚焦索引（１)非聚集索引适合用于需要进行等值查找的列，由于非聚集索引的叶子节点存放的是有序的索引列与书签的映射行，查询引擎可根据ＷHERE中给出的值，得到书签,继而定位到数据行;(2)覆盖索引（CoveriｎgInｄｅx）,是非聚集索引的一种特殊且高效的应用，就是将需要返回的数据列设计成组合索引,在ＳELECT时只查询索引中存在的数据列，这样就能形成索引覆盖,由于索引行中已经包含了想到的数据,不需要再进行书签查找;在SQＬＳERVER２０23及以上版本中，提供了ＩNCＬUDＥD关键字,可以在非聚集索引中包含更多列，也是覆盖索引的一个有效引申;(3)非聚集索引建立在值具有单调性的列上,比如:自增列(单调递增）,可以减少索引的外部碎片及索引结构的维护；四、实验小结 通过本实验，学会了如何建立，删除视图，并对索引有了一定的了解和掌握。实验五．用户自定义函数（2学时)实验目的：学习、掌握用户自定义函数的建立和使用实验内容：1、创建自定义函数创建一个用户自定义函数，并测试、查看函数返回值。1)输入并执行下面语句UＳＥNorthｗｉndGOCＲEATEＦUNCTＩOＮfn_TaxRatｅ(@ＰrodIDＩNT)RETUＲNＳnumeｒｉc（５，4）ASＢＥGINRETUＲＮ（SELECTCASECａtegｏrｙIDWHＥN1THEＮ1．１0WHEＮ2THEN1WＨEＮ３ＴHＥN1.1０WHEＮ4ＴＨＥN1.05WHEＮ5THＥN1WHEN6THEN1.05WHＥN7TＨＥＮ1WHEN8THＥＮ1.０5ＥNDFＲOMＰｒoduｃtsＷHEＲEPｒｏdｕctID＝@PｒｏｄIＤ)ENDＧO2）此函数中输入变量是什么？返回值类型？如何定义的返回值?答:此函数输入变量是@ＰｒｏdID,返回值类型是numeric(5,4)返回值是这样定义的SEＬＥCTCASＥCategoryIDWHEＮ1THＥN1.10WHEＮ２THEＮ1WＨＥN3TＨＥN1.10ＷHEN4THEＮ1.05ＷHEN5ＴHEN1WHＥN６TＨEN1.05ＷHEN7ＴHEN1WHEＮ８THEN1.0５ENＤＦROMProductsWHEREＰrｏductIＤ=@PrｏｄID3）输入并执行语句测试函数SＥＬＥCTPrｏduｃtＮａme,UnitＰrice,Ｎｏrthｗind.dｂｏ.fn_TaxRatｅ（ProductID)ＡSTaxＲａte，UnitＰrice*Nｏrthwind.dｂｏ．fn_TaxRate(ProducｔＩD)ＡSPriceWithTaｘＦROMProduｃｔｓ查看结果注意:函数可以在Selｅct子句后面调用。２、返回值为多值的自定义函数创建函数返回多列多值。1）输入并执行下面语句ＵSＥＮortｈwｉndＧOCREATEＦUNCTＩONfn_LargeFreｉｇhｔ(＠FreighｔAｍｔｍoney)RETURＮSTAＢＬEASＲEＴURN(SELＥCTS.ＳｈipperID,Ｓ．CompanyＮａｍe,O．OrderID,Ｏ.ＳhｉpｐedDate,O.ＦreiｇｈｔFＲＯMShippersASSJＯINOrdｅrsASOＯNS．ＳhipperID=O.ShipViaWHEREO.Frｅight>@FreｉghｔAmt)函数中输入变量是什么？返回值类型？如何定义的返回值？答：函数中输入变量是@ＦrｅｉghtＡmt,返回值类型是moneySＥLECTS.ＳｈiｐｐｅrＩD，Ｓ.CompanｙName,O.ＯｒdｅrID，Ｏ.ShiｐpｅdＤate,O.ＦｒeiｇhｔＦＲOMSｈiｐｐersASSJOIＮＯrｄersASOOＮS．ShiｐperID=O．ShipViaWHERＥO．Freiｇht>＠FreightＡmt2)输入并执行语句测试函数SEＬECＴ*FRＯMfｎ＿LarｇｅFreigｈｔ（6０0)查看结果３、返回值为多值的自定义函数本实验创建的函数也是返回多列多值,注意与上面实验的差别。1)输入并执行下面语句USENorthwindGOＣREAＴEＦＵNCTIONｆn＿ＦindReportｓ(@IｎＥｍpｌoyｅｅIDｃｈaｒ(5))RETURNS@reｐorｔｓTABLEﻩﻩ(EmｐloｙeｅIDcｈar(5)ＰRIMＡRYＫEＹ，ﻩﻩNａmenvarchar(４0）NOTＮULL, Tiｔlenvaｒchar(30),ﻩ MgrEmpｌｏyeeIDｉｎｔ, ﻩｐrocessｅｄｔinｙinｔdefaｕｌt0)ASBEＧINﻩINＳERT@ｒeporｔsﻩSELECTEmploｙｅeID，Name=FｉrsｔNａme＋＇＇＋LaｓｔName,Title，ＲepｏrtsTo,0 FＲOMEMPLOYEＥSＷＨEＲＥRepｏrｔsTｏ=＠ＩnEmploｙeeIDRETURNENDGO此函数中输入变量是什么？返回值类型是什么?如何定义的返回值?答:此函数中输入变量是@InＥmｐｌｏｙｅeID，返回值类型是TABＬE,这样定义的返回值INＳERT@repｏrｔsﻩＳELECTＥｍpｌoｙｅｅＩD，Nａｍｅ＝FirstNamｅ+''+LaｓｔNａｍe,Ｔiｔlｅ,RepoｒtｓＴo,0ﻩＦRＯＭEＭＰLOYＥESWHEREReｐortｓＴo=@InEｍpｌoyeｅＩD3)输入并执行语句测试函数SELEＣＴEmｐloｙeｅID,[Nａme],Titlｅ,MgrEmpｌｏｙeeIＤFＲOＭdbｏ．fｎ_ＦindRepｏrｔs(5)查看结果。4．设计一个函数，在OｒｄeｒＭaｇ数据库中，输入零件类别,返回该类别零件的平均存量、最高存量和该类零件的总数量。ＣRＥATEFUＮＣＴＩＯＮfund_Pno（@Pｐtｙpeｃｈar（１0))RETＵＲNSＴABLEASＲＥTURN(SELEＣTａｖg(Pｎｕｍ）Ａｖgp,ｍax(Pnuｍ)Maxp,suｍ（Pｎum）SumpFROMＳtoｒeWＨEＲEＰtype=@Pｐtyｐe)selｅct*ｆroｍfunｄ_Pnｏ（'传动')5.设计一个函数,在OｒｄerＭag数据库中,输入订单号，返回该订单所涉及的零件名称和类别。ＵSＥOｒdｅrMagGOCREATEＦＵＮCTＩOＮfpｎame(＠fＯnｏvarchaｒ(60)）RETＵRNSTABLＥＡSＲEＴUＲN（ｓｅlectO．Ｏｎo，S．Pnａmｅ,Ｓ．PｔｙpefｒoｍＳtoｒeASSjoinOｒdeｒｓAsOOnO.Pno=S.PnｏwherｅO.Ono=@ｆOnｏ）ｓeleｃt＊froｍfpnaｍe('O１')６.设计一个函数，在OrdeｒＭａｇ数据库中,根据零件库存量的大小，大于5０0的认为是充足，在100-500之间的是均衡，小于１00的为面临缺货。UＳEOrdeｒＭaｇＧＯＣＲEＡＴＥFUNCＴIONｆpnｕm4()RETURNSTabｌeASRETＵRN（ＳＥLECT'ＰnuｍCASEＷＨＥＮPｎｕm>5０0THEN'充足'WHEＮPｎｕmＢETWEEN１00and500THEN'均衡＇WHENPnｕm<１00THＥN'面临缺货＇ＥNDFROMStore)seｌｅcｔ*fｒoｍｆｐｎum4（)7.设计一个函数,根据输入的数值,计算从１加到该数的和（如输入5,则计算1＋2+3＋4+5=15，输出为15)。ｃreatefuｎctionsumn(@ｎuｍｉnｔ）returｎｓintasｂeginｄeclare@iiｎt；declａre@sinｔ;ｓet＠i=1;sｅｔ@ｓ＝0;whｉｌe(@i<=@num)beginset@s=＠s+@i;seｔ@ｉ=@i+1;endrｅｔuｒn@sendprint'从1加到该数的和是:'+caｓt(dｂo.sｕｍn(５)asvarchar)8．完毕实验报告(1）用户自定义函数在定义与使用上有何需要注意的问题？与存储过程有何不同?答：在SQＬSERVER中调用自定义函数时,必须在自定义函数前加上创建此函数的用户存储过程:ﻫ存储过程可以使得对数据库的管理、以及显示关于数据库及其用户信息的工作容易得多。存储过程是SＱL语句和可选控制流语句的预编译集合，以一个名称存储并作为一个单元解决。存储过程存储在数据库内,可由应用程序通过一个调用执行，并且允许用户声明变量、有条件执行以及其它强大的编程功能。ﻫ存储过程可包含程序流、逻辑以及对数据库的查询。它们可以接受参数、输出参数、返回单个或多个结果集以及返回值。

可以出于任何使用SQL语句的目的来使用存储过程，它具有以下优点:

1、可以在单个存储过程中执行一系列SQＬ语句。

2、可以从自己的存储过程内引用其它存储过程，这可以简化一系列复杂语句。

3、存储过程在创建时即在服务器上进行编译,所以执行起来比单个ＳQL语句快。用户定义函数:MicｒｏsｏftSQLSｅrvｅｒ2023允许创建用户定义函数。与任何函数同样,用户定义函数是可返回值的例程。根据所返回值的类型，每个用户定义函数可提成以下三个类别：

1、返回可更新数据表的函数ﻫ假如用户定义函数包含单个SＥLＥＣT语句且该语句可更新,则该函数返回的表格格式结果也可以更新。

2、返回不可更新数据表的函数ﻫ假如用户定义函数包含不止一个ＳELECT语句，或包含一个不可更新的ＳEＬECT语句,则该函数返回的表格格式结果也不可更新。

3、返回标量值的函数

用户定义函数可以返回标量值。四、实验小结 通过本实验掌握了自定义函数的建立及用法 ﻩﻩﻩﻩ实验六．安全性管理(2学时）一、实验目的：(1)了解ＳQＬServer的认证模式，安全管理的重要内容和方法（2)掌握用户管理、访问权限管理的基本方法二、实验内容:ＳQＬＳeｒｖeｒ、数据库、对象等不同级别的安全性设立与管理。1．设立身份验证模式1）在SSＭS中，选择服务器实例2)单击右键，选择“属性”，在属性对话框中选择“安全性”选项卡３）将身份验证模式设定为“仅Windows”４)单击“拟定”,SQLSEＲVEＲ重新启动服务后更改验证模式5）重新启动服务,登陆SSＭS时选择SQLＳEＲVER验证，然后输入sa用户登录6）能否登录？为什么？不能,由于此时的访问权限仅限于Windows7）反复1-3环节,将身份验证模式设定为混合模式。注意:实验完毕后应当将其重新设定为混合认证模式；只有重新启动服务，验证模式才生效。２．添加SQLSEＲVEＲ登录帐号添加SQLＳＥRVＥR登录帐号TesｔSQL。1)在ＳSMS中，展开“安全性”,选择“登录名”，选择“新建登录名”2)选择ＳＱLＳerveｒ身份验证,３）输入用户名TeｓｔSQＬ及密码TestSQL，单击“拟定”4）打开数据库引擎查询页,输入登录帐号TeｓtSQL和密码TeｓtＳQＬ,单击“拟定”，此时数据库引擎查询页是以TesｔSQL身份登录。5)输入并执行查询语句SELＥCT＊FＲOMmａster.ｄbo.sｙsDaｔabaseｓ6）在查询页中输入“ＵseOrderMａg”T-ＳQＬ语句,切换当前数据库。7)切换是否成功?为什么?答：不成功,由于系统没有对TeｓｔSQＬ开放访问数据库的权限。3、添加数据库用户帐号授权SQLＳEＲVEＲ登录帐号ＴeｓtSQL访问数据库OｒderＭag。１）在SＳＭＳ中,展开数据库OｒｄｅrMag中的“安全性”,选择右键单击“用户”，选择“新建用户”２）在“登录名”中选择ＴesｔSQL，然后单击“确认”3）打开数据库引擎查询页，输入登录帐号TesｔSQL和密码TeｓtSQL,单击“确认”。４）此时查询就是以ＴesｔSＱL身份登录5)在查询页中输入“ＯrｄｅrMag”T-SQL语句,切换当前数据库。6）切换是否成功？为什么？答，不成功,由于虽然对用户开放了数据库，但用户对数据库中的表仍没有访问权限。注意:UsｅrＮａmｅ与Login的名称可以相同，也可以不同，但是建议采用相同的名称以方便维护管理。4、给角色分派语句权限基于自定义角色分派语句许可权限。在完毕以上练习的基础上完毕此练习。1）使用TestSQL帐号登录到查询分析器中2）将当前数据库切换到ＯrｄerMag,执行语句ＳELＥCT*ＦRＯMＣustｏｍer3）执行结果为什么?拒绝了对对象'Customer'（数据库'OrderMａｇ＇，所有者'ｄｂo'）的ＳEＬＥCT权限,没有授予TｅsｔSＱL用户对OｒｄeｒMaｇ数据库中表的SＥＬECT权限。4)在SSＭS中，双击角色DＢRｏle,单击“权限”按钮5）在表Ｃｕｓtｏｍer所相应的行中，选择“Select”６）再使用ＴeｓtSQL帐号登录到查询分析器中7）将当前数据库切换到ＯrderMaｇ，执行语句SＥLＥCT*ＦＲOMCuｓｔｏmer8)执行结果?为什么?答：获得Customeｒ表中的信息,由于用户TeｓtSQＬ对表Cuｓtomer有查询的权限5、给登录帐号分派固定服务器角色给帐号分派固定服务器角色,让帐号有管理ＳQＬＳerver权限。1)创建一个新的SecｕrｉｔyAdmiｎ登录帐号2)双击ＳeｃｕｒiｔｙAdｍin帐号,选择“服务器角色”选项卡3）选择SｅｃuｒiｔyAdｍｉnistraｔorｓ固定服务器角色，“拟定”4)SecurityAdmin拥有什么权限?登录,查看masteｒ数据库6、给用户帐号分派固定数据库角色给用户帐号分派固定数据库角色，让帐号有备份数据库的权限。1）创建新的登录帐号BaｃkupAdmiｎ2)在数据库OrderMag上创建用户帐号BackuｐＡｄmin3）BackｕpＡｄmｉｎ帐号,4)选择db＿backuｐｏpeｒaｔor固定数据库角色，单击“拟定”。5)BａｃkupAdmin拥有什么权限?备份数据库权限7．完毕实验报告(１）描述系统的安全机制。ﻩ答:在计算机系统中，安全措施是一级一级层层设立的。例如用户在进入计算机系统时,系统一方面根据输入的用户标记进行身份鉴定，只有合法的的用户才准许进入计算机系统,对已进入系统的用户,DBMＳ还要进行存取控制，只允许用户执行合法操作。操作系统一级也会有自己的保护措施,数据最后还可以以密码形式存储到数据库中。（2)用户帐号的概念,用户帐号与登录帐号的区别。 答：当用户通过身份验证,以某个登录帐号连接到SQＬSeｒver以后，还必须取得相应数据库的“访问许可”,才干使用该数据库。这种用户访问数据库权限的设立是通过用户帐号来实现的。登录帐号是属于服务器的层面。而登录者要使用服务器中的数据库数据时,必须要有用户帐号。就如同在公司门口先刷卡进入大门(登录服务器)，然后再拿钥匙打开自己的办公室门（进入数据库)同样。（3)固定服务器角色的权限、固定数据库角色的权限的概念。答:固定服务器角色的权限针对数据库服务器,固定数据库角色的权限针对具体某一数据库（用户）固定数据库角色是在SQLServer每个数据库中都存在的系统预定义用户组。它们提供了对数据库常用操作的权限。系统管理员可以将用户加入这些角色中，固定数据库角色的成员也可将其他用户添加到本角色中。但固定数据库角色自身不能被添加、修改或删除。四、实验小结通过本实验知道了SQLＳerver的认证模式,安全管理的重要内容和方法及用户管理、访问权限管理的基本方法实验七.备份与恢复（2学时）一、实验目的:（1）了解ＭSSQLServer提供的备份、恢复功能(2）纯熟掌握数据库、日记备份的方法(3)纯熟掌握数据库恢复的方法二、实验内容：在备份设备、文献名上进行完全、差异数据库备份；数据库恢复。1、创建备份设备建立备份设备nｗ1,nw2sp_ａddumpdｅvicｅ'dｉsk＇,'Nw1',＇d：\nw1.baｋ'sｐ_addumpｄevice＇ｄiｓk'，'Ｎw２＇,'ｄ:\nw2．bａk'2、备份数据库使用两种方法进行完全数据库备份。方法一:在SSMS中进行全库备份１）在SSMS中右击Northwind数据库，选择“任务”→“备份数据库”。２)在备份窗口中的“备份”中选择“数据库--－完全”选项。3)在目的中，先单击“添加”,选择“备份设备”中的［Nw1]备份设备。4)单击“拟定”开始备份。方法二：使用T-ＳQＬ语句进行全库备份1）在查询页中输入并执行语句ｂackuｐdａtaｂaｓｅnorthwindｔoｎw2ｗiｔhiｎiｔ2)在资源管理器中查看D：\Nw2.bak是否创建?已创建3、查看备份设备查看备份设备中的备份内容,了解备份信息。1)在SＳMＳ中展开数据库2)单击[备份],添加备份设备Nw13）右击备份设备[Nw1]4)单击[查看内容］来查看备份内容。5)同样的方法查看Nｗ2备份设备。实验是否成功?是４、执行差异备份在SSMS中进行数据库的差异备份1)选择[Nｏrthｗinｄ]数据库,选择“任务”下面的“备份”2)在备份窗口中的备份中选择“数据库---差异”选项3)在备份目的中,先单击“添加”,选择“磁盘上的目的中“备份设备[Nw1]”4)在选项页中选择“追加到媒体集”选项,单击“拟定”开始进行差异备份。5)查看备份设备中的内容5、恢复完全数据库备份运用上述实验建立的备份，使用两种方法从备份设备中恢复完全数据库备份。方法一:在SSMS中进行实验1）在SSＭS中右击Ｎortｈwiｎd数据库创建表tａble12)在SSMS中右击Ｎｏrｔｈwiｎd数据库,选择“任务”下面的“还原”3）在还原窗口中的下方列表中显示两个备份信息，第一个为完全备份,第二个为差异备份。4)只选择第一个完全备份，单击“拟定”。5）表taｂle１是否存在？假如存在，右击Norｔhwiｎd数据库选择“刷新”,这时表Ttaｂle1是否存在?存在,不存在方法二:使用Ｔ-ＳQL语句进行实验1）在SSMS中右击［Nｏrｔhwind]数据库创建表tａblｅ22）在查询分