MSSQL网页设计口试时最常常被问到的成绩（Frenquentl...
为了在某种程序上弥补这一缺陷，许多SQL命令都有一个DELAY_KEY_WRITE项。这个选项的作用是暂时制止MySQL在该命令每插入一条新记录和每修改一条现有之后立刻对索引进行刷新，对索引的刷新将等到全部记录插入/修改完毕之后再进行。成绩DatabasesQuestions&Answers
1.WhataretwomethodsofretrievingSQL?
Answer:
2.Whatcursortypedoyouusetoretrievemultiplerecordsets?
Answer:
3.Whatactiondoyouhavetoperformbeforeretrievingdatafromthenextresultsetofastoredprocedure?
Answer:Movethecursordownonerowfromitscurrentposition.AResultSetcursorisinitiallypositionedbeforethefirstrow.Beforeyoucangettothefirstrow,youwouldneedtoMovethecursordownbyonerow(Forex:injavathefirstcalltonextmakesthefirstrowthecurrentrow;thesecondcallmakesthesecondrowthecurrentrow,andsoon).
4.WhatisthebasicformofaSQLstatementtoreaddataoutofatable?
Answer:SELECT*FROMtable_name;
5.Whatstructurecanyouhavethedatabasemaketospeeduptablereads?
Answer:Thequestionisnotcorrect."Whatstructurecanyouhavethedatabasemaketospeeduptablereads?"Itisnotclearwhatexactlytheterm"structure"meansinthiscase.FollowtherulesofDBtuningwehaveto:
1)properlyuseindexes(differenttypesofindexes)
2)properlylocatedifferentDBobjectsacrossdifferenttablespaces,filesandsoon.
3)Createaspecialspace(tablespace)tolocatesomeofthedatawithspecialdatatypes(forexampleCLOB,LOBand...)
4)...
5)...
6.Whatisa"join"?
Answer:Joinsmergethedataoftworelatedtablesintoasingleresultset,presentingadenormalizedviewofthedata.
7.Whatisa"constraint"?
Answer:Aconstraintallowsyoutoapplysimplereferentialintegritycheckstoatable.Thereare5primarytypesofconstraintsthatarecurrentlysupportedbySQLServer:
PRIMARY/UNIQUE-enforcesuniquenessofaparticulartablecolumn.
DEFAULT-specifiesadefaultvalueforacolumnincaseaninsertoperationdoesnotprovideone.
FOREIGNKEY-validatesthateveryvalueinacolumnexistsinacolumnofanothertable.
CHECK-checksthateveryvaluestoredinacolumnisinsomespecifiedlist
NOTNULL-isaconstraintwhichdoesnotallowvaluesinthespecificcolumntobenull.Andalsoitistheonlyconstraintwhichisnotatablelevelconstraint.
8.Whatisa"primarykey"?
Answer:PrimaryKeyisatypeofaconstraintenforcinguniquenessanddataintegrityforeachrowofatable.AllcolumnsparticipatinginaprimarykeyconstraintmustpossesstheNOTNULLproperty.
9.Whatisa"functionaldependency"?Howdoesitrelatetodatabasetabledesign?
Answer:Whatfunctionaldependenceinthecontextofadatabasemeansisthat:AssumethatatableexistsinthedatabasecalledTABLEwithacompositeprimarykey(A,B)andothernon-keyattributes(C,D,E).Functionaldependencyingeneral,wouldmeanthatanynon-keyattribute-CDorEbeingdependentontheprimarykey(AandB)inourtablehere.
Partialfunctionaldependency,ontheotherhand,isanothercorollaryoftheabove,whichstatesthatallnon-keyattributes-CDorE-ifdependentonthesubsetoftheprimarykey(AandB)andnotonitasawhole.
Example:
----------
FullyFunctionalDependent:CDE-->AB
PartialFunctionaldependency:C-->A,DE-->B
Hopethathelps!
10.Whatisa"trigger"?
Answer:Atriggerisadatabaseobjectdirectlyassociatedwithaparticulartable.Itfireswheneveraspecificstatement/typeofstatementisissuedagainstthattable.Thetypesofstatementsareinsert,update,deleteandquerystatements.Basically,triggerisasetofSQLstatementsthatexecuteinresponsetoadatamodification/retrievaleventonatable.
Otherthantabletriggerstherearealsoschemaanddatabasetriggers.Thesecanbemadetofirewhennewobjectsarecreated,whenauserlogsin,whenthedatabaseshutdownetc.Tableleveltriggerscanbeclassifiedintorowandstatementleveltriggersandthosecanbefurtherbrokendownintobeforeandaftertriggers.Beforetriggerscanmodifydata.
11.Whatis"indexcovering"ofaquery?
Answer:Anonclusteredindexthatincludes(orcovers)allcolumnsusedinaqueryiscalledacoveringindex.WhenSQLservercanuseanonclusteredindextoresolvethequery,itwillprefertoscantheindexratherthanthetable,whichtypicallytakesfewerdatapages.Ifyourqueryusesonlycolumnsincludedintheindex,thenSQLservermayscanthisindextoproducethedesiredoutput.
12.WhatisaSQLview?
Answer:ViewisaprecompliedSQLquerywhichisusedtoselectdatafromoneormoretables.Aviewislikeatablebutitdoesntphysicallytakeanyspace.Viewisagoodwaytopresentdatainaparticularformatifyouusethatqueryquiteoften.
Viewcanalsobeusedtorestrictusersfromaccessingthetablesdirectly.
Aviewotherwiseknownasavirtualtableisamerewindowoverthebasetablesinthedatabase.Thishelpsusgainacoupleofadvantages:
1)Inherentsecurityexposingonlythedatathatisneededtobeshowntotheenduser
2)Viewsareupdateablebasedoncertainconditions.Forexample,updatescanonlybedirectedtooneunderlyingtableoftheview.Aftermodificationiftherowsorcolumnsdontcomplywiththeconditionsthattheviewwascreatedwith,thoserowsdisappearfromtheview.YoucouldusetheCHECKOPTIONwiththeviewdefinition,tomakesurethatanyupdatestomaketherowsinvalidwillnotbepermittedtorun.
3)Viewsarenotmaterialized(givenaphysicalstructure)inadatabase.Eachtimeaviewisqueriedthedefinitionstoredinthedatabaseisrunagainstthebasetablestoretrievethedata.Oneexceptiontothisistocreateaclusteredindexontheviewtomakeitpersistentinthedatabase.Onceyoucreateaclusteredindexontheview,youcancreateanynumberofnon-clusteredindexesontheview.
如果某个数据列里包含许多重复的值，就算为它建立了索引也不会有很好的效果。比如说，如果某个数据列里包含的净是些诸如“0/1”或“Y/N”等值，就没有必要为它创建一个索引。 而写到本地,我又考虑到效率问题.大家来讨论讨论吧,分数不打紧,就给10分,十全十美,没啥对错,各抒己见,但是要有说服力的哦~ 只能告诉你，学好数据库语言和原理，多见识几种数据库软件，比一棵树上吊死要好。 只能告诉你，学好数据库语言和原理，多见识几种数据库软件，比一棵树上吊死要好。 大家注意一点。如下面的例子： Mirror可以算是SQLServer的Dataguard了。但是能不能被大伙用起来就不知道了。 原理很简单，对要求长时间计算某一时间点的报表生成和防用户操作错误很有帮助。但是比起Oracle10g的闪回技术还是细粒度不够。可惜！ 这是一个不错的新特性。虽然索引的附加字段没有索引键值效率高，但是相对映射到数据表中效率还是提高了很多。我做过试验，在我的实验环境中会比映射到表中提高30%左右的效率。 总感觉自己还是不会SQL
页:
[1]