翻译文献-这章主要介绍sql及如何执行sql

CHAPTERFOURTHISCHAPTERISACOMPLETEINTRODUCTIONTOSQLINGENERAL,ANDSQLITESIMPLEMENTATIONOFITINPARTICULARITASSUMESNOPREVIOUSEXPERIENCEWITHEITHERSQLORTHERELATIONALMODELIFYOUARENEWTOSQL,SQLITESHOULDSERVEASANEXCELLENTSPRINGBOARDTOENTERINGTHEWORLDOFRELATIONALDATABASESITWILLGIVEYOUAGOODGROUNDINGINTHEFUNDAMENTALSWHILETHEPREVIOUSCHAPTERONTHERELATIONALMODELWASALITTLETHEORETICALANDSTUFFY,THISCHAPTERISMUCHMORERELAXEDANDPRACTICALWERENOTHERETOPROVETHEORIESWEREHERETOGETTHINGSDONESOIFYOUDIDNTREADTHEPREVIOUSCHAPTER,THATSPERFECTLYFINEYOUSHOULDSTILLHAVENOTROUBLEWITHTHEMATERIALCOVEREDINTHISCHAPTERANUNDERSTANDINGOFTHERELATIONALMODELISEDIFYING,BUTNOTNECESSARYTOLEARNSQLSQLISTHESOLEANDALMOSTUNIVERSALMEANSBYWHICHTOCOMMUNICATEWITHARELATIONALDATABASEITISALANGUAGEEXCLUSIVELYDEVOTEDTOINFORMATIONPROCESSINGITISDESIGNEDFORSTRUCTURING,READING,WRITING,SORTING,FILTERING,PROTECTING,CALCULATING,GENERATING,GROUPING,AGGREGATING,ANDINGENERALMANAGINGINFORMATIONSQLISANINTUITIVE,USERFRIENDLYLANGUAGEITCANBEFUNTOUSEANDISQUITEPOWERFULONEOFTHEFUNTHINGSABOUTSQLISTHATREGARDLESSOFWHETHERYOUAREANEXPERTORANOVICE,ITSEEMSTHATYOUCANALWAYSCONTINUETOLEARNNEWWAYSOFDOINGTHINGSFORBETTERORWORSETHEREAREOFTENMANYWAYSTOTACKLEAGIVENPROBLEM,ANDYOUMAYFINDYOURSELFTAKINGDELIGHTINTRYINGTOFINDMOREEFFICIENTWAYSTOGETWHATYOUNEED,EITHERTHROUGHMORECOMPACTEXPRESSIONSORMOREELEGANTAPPROACHES,ASIFSOLVINGAPUZZLEANDYOUCANCONTINUETOEXPLOREDUSTYCORNERSOFTHELANGUAGEYOUNEVERTOOKNOTICEOFBEFOREWITHWHICHTOFURTHERHONEYOURSKILLS,NOMATTERYOUREXPERIENCETHEGOALOFTHISCHAPTERISTOTEACHYOUTOUSESQLWELLTOEXPOSEYOUTOGOODTECHNIQUES,ANDPERHAPSAFEWCHEAPTRICKSALONGTHEWAYASYOUCANALREADYTELL,THEREAREMANYDIFFERENTASPECTSTOSQLTHISCHAPTERBREAKSTHEMDOWNINTODISCRETEPARTSANDPRESENTSTHEMINALOGICALORDERTHATSHOULDBERELATIVELYEASYTOFOLLOWNEVERTHELESS,SQLISABIGSUBJECTANDTHEREISALOTOFGROUNDTOCOVERITWILLTAKESOMETIMEANDMORETHANONECUPOFCOFFEETOGETTHROUGHTHISCHAPTERBUTBYTHETIMEYOUAREDONE,YOUSHOULDBEWELLEQUIPPEDTOPUTADENTINADATABASETHERELATIONALMODELASYOULLREMEMBERFROMCHAPTER3,SQLISACONSEQUENCEOFTHERELATIONALMODEL,WHICHWASORIGINALLYPROPOSEDBYEFCODDIN1969THERELATIONALMODELREQUIRESTHATRELATIONALDATABASESPROVIDEAQUERYLANGUAGE,ANDOVERTHEYEARSSQLROSETOBECOMETHELINGUAFRANCATHERELATIONALMODELCONSISTSOFTHREEESSENTIALPARTSFORM,FUNCTION,ANDCONSISTENCYFORMREFERSTOTHESTRUCTUREOFINFORMATIONTHEREISBUTONESINGLEDATASTRUCTUREUSEDTOREPRESENTALLINFORMATIONTHISSTRUCTUREISCALLEDARELATIONKNOWNINSQLASATABLE,WHICHISMADEUPOFTUPLESKNOWNINSQLASROWS,WHICHINTURNAREMADEUPOFATTRIBUTESKNOWNINSQLASCOLUMNSTHERELATIONALMODELSFORMISALOGICALREPRESENTATIONOFINFORMATIONTHISLOGICALREPRESENTATIONISAPRISTINE,ABSTRACTVIEWOFINFORMATIONUNAFFECTEDBYANYTHINGOUTSIDEOFITITISLIKEAMATHEMATICALCONCEPTCLEANANDCONSISTENT,GOVERNEDBYAWELLDEFINEDSETOFDETERMINISTICRULESTHATARENOTSUBJECTTOCHANGETHELOGICALREPRESENTATIONISCOMPLETELYINDEPENDENTOFTHEPHYSICALREPRESENTATION,WHICHREFERSTOHOWDATABASESOFTWARESTORESTHISINFORMATIONONTHEPHYSICALLEVELEG,DISKTHETWOREPRESENTATIONSARETHUSDISTINCTNOTHINGTHATOCCURSINTHEPHYSICALLEVELCANCHANGEORAFFECTANYTHINGATTHELOGICALLEVELTHELOGICALLEVELISUNINHIBITEDBYHARDWARE,SOFTWARE,VENDOR,ORTECHNOLOGYTHESECONDESSENTIALPARTOFTHEMODELTHEFUNCTIONALPARTISALSOCALLEDTHEMANIPULATIVECOMPONENTITDEFINESWAYSOFOPERATINGONINFORMATIONATTHELOGICALLEVELTHISWASFORMALLYINTRODUCEDINCODDS1972PAPERTITLED“RELATIONALCOMPLETENESSOFDATABASESUBLANGUAGES”ITADDEDTHEFUNCTIONALPARTTOTHERELATIONALMODELBYDEFININGRELATIONALALGEBRAANDRELATIONALCALCULUSTHESEARETWOFORMAL,OR“PURE,”QUERYLANGUAGESWITHAHEAVYBASISINMATHEMATICSRELATIONS,ASDESCRIBEDINTHEDATAMODEL,AREMATHEMATICALSETSWITHAFEWADDITIONALPROPERTIESRELATIONALALGEBRAANDCALCULUS,INTURN,BUILDONTHISMODELBYADDINGOPERATIONSFROMSETTHEORYANDFORMALLOGIC,THUSFORMINGTHEFUNCTIONALCOMPONENTOFTHERELATIONALMODELSOBOTHTHEFORMANDFUNCTIONPRESCRIBEDINTHERELATIONALMODELCOMEDIRECTLYFROMCONCEPTSINMATHEMATICSEACHDERIVATIVE,HOWEVER,ADDSALITTLESOMETHINGTOBETTERADAPTITTOCOMPUTERSANDINFORMATIONPROCESSINGQUERYLANGUAGESAQUERYLANGUAGECONNECTSTHEOUTSIDEWORLDWITHTHEABSTRACTLOGICALREPRESENTATION,ANDALLOWSTHETWOTOINTERACTITPROVIDESAWAYTORETRIEVEANDMODIFYINFORMATIONITISTHEDYNAMICPARTOFTHERELATIONALMODELCODDINTENDEDRELATIONALALGEBRAANDCALCULUSTOSERVEASABASELINEFOROTHERQUERYLANGUAGESRELATIONALALGEBRAANDCALCULUSEMPLOYAHIGHLYMATHEMATICALNOTATIONTHAT,WHILEGOODFORDEFININGTHEORY,ISNOTTERRIBLYUSERFRIENDLYINPRACTICETHEIRPURPOSEWASTHUSTODEFINETHEMATHEMATICALORRELATIONALREQUIREMENTSTHATAMOREUSERFRIENDLYQUERYLANGUAGESHOULDSUPPORTQUERYLANGUAGESTHATMETTHESEMINIMUMREQUIREMENTSWERECALLEDRELATIONALLYCOMPLETETHISUSERFRIENDLYQUERYLANGUAGETHENPROVIDESAMORETRACTABLEANDINTUITIVEWAYFORAPERSONTOWORKWITHRELATIONALDATA,WHILEATTHESAMETIMEADHERINGTOASOUNDTHEORETICALBASISTHEGROWTHOFSQLPERHAPSTHEFIRSTSUCHQUERYLANGUAGETHATWASTOBESOEMPLOYEDWASTHATOFIBMSSYSTEMR,ARELATIONALDATABASERESEARCHPROJECTTHATWASADIRECTOUTGROWTHOFCODDSPAPERSITWASORIGINALLYCALLEDSEQUEL,WHICHSTANDSFOR“STRUCTUREDENGLISHQUERYLANGUAGE”ITWASLATERSHORTENEDTOSQL,OR“STRUCTUREDQUERYLANGUAGE”OTHERCOMPANIESSUCHASORACLEFOLLOWEDSUITINFACT,ORACLEBEATIBMTOMARKETWITHTHEFIRSTSQLPRODUCT,ANDPRETTYSOONSQLWASTHEDEFACTOSTANDARDTHEREWEREOTHERQUERYLANGUAGES,SUCHASINGRESSQUEL,BUTINTIMESQLWONOUTTHEREASONSSQLEMERGEDASTHESTANDARDMAYHAVEHADMORETODOWITHTHEDYNAMICSOFTHEARKETPLACETHANANYTHINGELSEBUTTHEREASONSWHYITISSPECIALTODAYAREPERHAPSALITTLECLEARERTHEREAREAMONGOTHERSSTANDARDIZATION,WIDEADOPTION,ANDGENERALEASEOFUSESQLHASBEENACCEPTEDASTHESTANDARDLANGUAGEFORRELATIONALDATABASESBYTHEAMERICANNATIONALSTANDARDSINSTITUTEANSI,THEINTERNATIONALSTANDARDSORGANIZATIONISO,ANDTHEINTERNATIONALELECTROTECHNICALCOMMISSIONIECITHASAWELLDEFINEDSTANDARDTHATSPECIFIESWHATSQLISANDDOES,ANDTHISSTANDARDHASCONTINUEDTOEVOLVEOVERTHEPASTTWODECADESTODATE,FIVEVERSIONSOFTHESTANDARDHAVEBEENPUBLISHEDOVERTHEYEARS1986,1989,1992,1999,AND2003THESTANDARDSARECUMULATIVEEACHVERSIONOFTHESTANDARDHASBUILTONTHEPREVIOUSONE,ADDINGNEWFEATURESSOINEFFECT,THEREISONLYONESTANDARDTHATHASCONTINUEDTOGROWANDEVOLVEOVERTIMETHEFIRSTVERSIONSSQL86ANDSQL89ARECOLLECTIVELYREFERREDTOASSQL1SQL92ISCOMMONLYREFERREDTOASSQL2,ANDSQL99ASSQL3THEANSISTANDARD,ASAWHOLE,ISVERYLARGETHESQL92STANDARDALONEISOVER600PAGESNOONEDATABASEPRODUCTCONFORMSTOTHEENTIRESTANDARDNEVERTHELESS,THESTANDARDGOESALONGWAYINBRINGINGAGREATDEALOFUNIFORMITYTORELATIONALATABASESSQLSWIDEADOPTIONTODAYISPATENTLYOBVIOUSORACLE,MICROSOFTSQLSERVER,DB2,INFORMIX,SYBASE,POSTGRESQL,MYSQL,FIREBIRD,TERADATA,INTERSYSTEMSCACH,ANDSQLITEAREBUTSOMEOFTHERELATIONALDATABASESTHATUSESQLASTHEIRQUERYLANGUAGETHEEXAMPLEDATABASEBEFOREDIVINGINTOSYNTAX,LETSGETSITUATEDWITHTHEOBLIGATORYEXAMPLEDATABASETHEDATABASEUSEDINTHISCHAPTERANDTHERESTOFTHISBOOKFORTHATMATTERCONSISTSOFALLTHEFOODSINEVERYEPISODEOFSEINFELDIFYOUVEEVERWATCHEDSEINFELD,YOUCANTHELPBUTNOTICEASLIGHTPREOCCUPATIONWITHFOODTHEREAREMORETHAN412DIFFERENTFOODSMENTIONEDINTHE180EPISODESOFITSHISTORYACCORDINGTOTHEDATAIFOUNDONTHEINTERNETTHATSOVERTWONEWFOODSEVERYSHOWSUBTRACTCOMMERCIALTIMEANDTHATSVIRTUALLYANEWFOODINTRODUCEDEVERY10MINUTESASITTURNSOUT,THISPREOCCUPATIONWITHFOODWORKSOUTNICELYASITMAKESFORAATABASETHATILLUSTRATESALLTHEREQUISITECONCEPTSTHEDATABASETABLESARESHOWNINFIGURE41FIGURE41THESEINFELDFOODDATABASETHEDATABASESCHEMA,ASDEFINEDINSQLITE,ISDEFINEDASFOLLOWSCREATETABLEEPISODESIDINTEGERPRIMARYKEY,SEASONINT,NAMETEXTCREATETABLEFOODSIDINTEGERPRIMARYKEY,TYPE_IDINTEGER,NAMETEXTCREATETABLEFOOD_TYPESIDINTEGERPRIMARYKEY,NAMETEXTCREATETABLEFOODS_EPISODESFOOD_IDINTEGER,EPISODE_IDINTEGERTHEMAINTABLEISFOODSEACHROWINFOODSCORRESPONDSTOADISTINCTFOODITEM,THENAMEOFWHICHISSTOREDINTHENAMEATTRIBUTETHETYPE_IDATTRIBUTEREFERENCESTHEFOOD_TYPESTABLE,WHICHSTORESTHEVARIOUSFOODCLASSIFICATIONSEG,BAKEDGOODS,DRINKS,ORJUNKFOODFINALLY,THEFOODS_EPISODESTABLELINKSFOODSINFOODSWITHTHEEPISODESINEPISODESINSTALLATIONTHEFOODDATABASEISLOCATEDINTHEEXAMPLESZIPFILEACCOMPANYINGTHISBOOKITISAVAILABLEONTHEAPRESSWEBSITEWWWAPRESSCOMINTHESOURCECODESECTIONTOCREATETHEDATABASE,FIRSTLOCATETHEFOODSSQLFILEINTHEROOTDIRECTORYOFTHEUNPACKEDZIPFILETOCREATEANEWDATABASEFROMSCRATCHFROMTHECOMMANDLINE,NAVIGATETOTHEEXAMPLESDIRECTORYANDRUNTHEFOLLOWINGCOMMANDSQLITE3FOODSDBSELECTFROMFOODSWHERENAMEJUJYFRUITANDTYPE_ID9IDTYPE_IDNAME2449JUJYFRUITOCCASIONALLY,ASINTHEPREVIOUSEXAMPLE,ITAKETHELIBERTYOFADDINGANEXTRALINEBETWEENTHECOMMANDANDITSOUTPUTINTHESHELL,THEOUTPUTIMMEDIATELYFOLLOWSTHECOMMANDFORLONGERQUERIES,ISHOWJUSTTHESQLINCODEFORMATSEPARATEDFROMTHERESULTSBYGRAYLINES,ASINTHEFOLLOWINGEXAMPLESELECTFNAMENAME,TYPESNAMETYPEFROMFOODSFINNERJOINSELECTFROMFOOD_TYPESWHEREID6TYPESONFTYPE_IDTYPESIDNAMETYPEGENERICASAMEALDIPGOODDIPDIPGUACAMOLEDIPDIPHUMMUSDIPSYNTAXSQLSDECLARATIVESYNTAXREADSALOTLIKEANATURALLANGUAGESTATEMENTSAREEXPRESSEDINTHEIMPERATIVEMOOD,BEGINNINGWITHTHEVERBDESCRIBINGTHEACTIONFOLLOWINGITARETHESUBJECTANDPREDICATE,ASILLUSTRATEDINFIGURE42FIGURE42GENERALSQLSYNTAXSTRUCTUREASYOUCANSEE,ITREADSLIKEANORMALSENTENCESQLWASDESIGNEDSPECIFICALLYWITHNONTECHNICALPEOPLEINMIND,ANDWASTHUSMEANTTOBEVERYSIMPLEANDEASYTONDERSTANDPARTOFSQLSEASEOFUSECOMESFROMITSBEINGFORTHEMOSTPARTADECLARATIVELANGUAGE,ASOPPOSEDTOANIMPERATIVELANGUAGESUCHASCORPERLADECLARATIVELANGUAGEISONEINWHICHYOUDESCRIBEWHATYOUWANTWHEREASANIMPERATIVELANGUAGEISONEINWHICHYOUSPECIFYHOWTOGETITFOREXAMPLE,CONSIDERTHEPROCESSOFORDERINGACHEESEBURGERASACUSTOMER,YOUUSEADECLARATIVELANGUAGETOARTICULATEYOURORDERTHATIS,YOUSIMPLYDECLARETOTHEPERSONBEHINDTHECOUNTERWHATYOUWANTGIVEMEADOUBLEMEATWHATABURGERWITHJALAPEOSANDCHEESE,HOLDTHEMAYOTHEORDERISPASSEDBACKTOCHEFWHO,ONTHEOTHERHAND,FULFILLSTHEORDERUSINGAPROGRAMWRITTENINANIMPERATIVELANGUAGETHERECIPEHEFOLLOWSASERIESOFWELLDEFINEDSTEPSTHATMUSTBEEXECUTEDINASPECIFICORDERTOCREATETHECHEESEBURGERPERYOURDECLARATIVESPECIFICATIONS1GETGROUNDBEEFFROMTHETHIRDREFRIGERATORONTHELEFT2MAKEAPATTY3COOKFORTHREEMINUTES4FLIP5COOKTHREEMOREMINUTES6REPEATSTEPS15FORSECONDPATTY7ADDMUSTARDTOTOPBUN8ADDPATTIESTOBOTTOMBUN9ADDCHEESE,LETTUCE,TOMATOES,ONIONS,ANDJALAPEOSTOBURGER,BUTNOTMAYO10COMBINETOPANDBOTTOMBUNS,ANDWRAPINYELLOWPAPERASYOUCANSEE,DECLARATIVELANGUAGESTENDTOBEMORESUCCINCTTHANIMPERATIVEONESINTHISEXAMPLE,ITTOOKTHEDECLARATIVEBURGERLANGUAGEDBLONESTEPTOMATERIALIZETHECHEESEBURGER,WHILEITTOOKTHEIMPERATIVECHEFLANGUAGEICL10STEPSDECLARATIVELANGUAGESDOMOREWITHLESSINFACT,SQLSEASEOFUSEISNOTFARFROMTHISEXAMPLEASUITABLESQLEQUIVALENTTOTHEDBLSTATEMENTABOVEMIGHTBESOMETHINGALONGTHELINESOFSELECTBURGERFROMKITCHENWHEREPATTIES2ANDTOPPINGSJALOPENOSANDCONDIMENTMAYOLIMIT1PRETTYSIMPLEASWEVEMENTIONED,SQLWASDESIGNEDTOBEAUSERFRIENDLYLANGUAGEINTHEEARLYDAYS,SQLWASTARGETEDSPECIFICALLYFORENDUSERSFORTASKSSUCHASADHOCQUERIESANDREPORTGENERATIONUNLIKETODAYWHEREITISALMOSTEXCLUSIVELYTHEDOMAINOFDEVELOPERSANDDATABASEADMINISTRATORSCOMMANDSSQLISMADEUPOFCOMMANDSCOMMANDSARETYPICALLYTERMINATEDBYASEMICOLON,WHICHMARKSTHEENDOFTHECOMMANDFOREXAMPLE,THEFOLLOWINGARETHREEDISTINCTCOMMANDSSELECTID,NAMEFROMFOODSINSERTINTOFOODSVALUESNULL,WHATABURGERDELETEFROMFOODSWHEREID413NOTETHESEMICOLON,ORCOMMANDTERMINATOR,ISASSOCIATEDPRIMARILYWITHINTERACTIVEPROGRAMSDESIGNEDFORLETTINGUSERSEXECUTEQUERIESAGAINSTADATABASEWHILETHECOMMANDTERMINATORISCOMMONLYASEMICOLON,ITNEVERTHELESSVARIESBYBOTHDATABASESYSTEMANDQUERYPROGRAMSOMESYSTEMS,FOREXAMPLE,USEGOREVENTHEWORDGOSQLITE,HOWEVER,USESTHESEMICOLONASITSCOMMANDTERMINATORINITSCOMMANDLINEPROGRAMANDINTHECAPICOMMANDS,INTURN,ARECOMPOSEDOFASERIESOFTOKENSTOKENSCANBELITERALS,KEYWORDS,IDENTIFIERS,EXPRESSIONS,ORSPECIALCHARACTERSTOKENSARESEPARATEDBYWHITESPACE,SUCHASSPACES,TABS,ANDNEWLINESLITERALSLITERALS,ALSOCALLEDCONSTANTS,DENOTEEXPLICITVALUESTHEREARETHREEKINDSSTRINGCONSTANTS,NUMERICCONSTANTS,ANDBINARYCONSTANTSSTRINGCONSTANTSAREONEORMOREALPHANUMERICCHARACTERSSURROUNDEDBYSINGLEQUOTESEXAMPLESINCLUDEJERRYNEWMANJUJYFRUITSTRINGVALUESAREDELIMITEDBYSINGLEORDOUBLEQUOTESIFSINGLEQUOTESAREPARTOFTHESTRINGVALUE,THEYMUSTBEREPRESENTEDASTWOSUCCESSIVESINGLEQUOTESFOREXAMPLE,KENNYSCHICKENWOULDBEEXPRESSEDASKENNYSCHICKENNUMERICCONSTANTSAREREPRESENTEDININTEGER,DECIMAL,ORSCIENTIFICNOTATIONEXAMPLESINCLUDE1314260221415E23BINARYVALUESAREREPRESENTEDUSINGTHENOTATIONX0000,WHEREEACHDIGITISAHEXADECIMALVALUEBINARYVALUESMUSTBEEXPRESSEDINBYMULTIPLESOF2HEXADECIMALVALUES8BITSHEREARESOMEEXAMPLESX01X0FFFX0F0EFFX0F0EFFABKEYWORDSANDIDENTIFIERSKEYWORDSAREWORDSTHATHAVEASPECIFICMEANINGINSQLTHESEINCLUDEWORDSLIKESELECT,UPDATE,INSERT,CREATE,DROP,ANDBEGINIDENTIFIERSREFERTOSPECIFICOBJECTSWITHINTHEDATABASE,SUCHASTABLESORINDEXESKEYWORDSARERESERVEDWORDS,ANDMAYNOTBEUSEDASIDENTIFIERSSQLISCASEINSENSITIVEWITHRESPECTTOKEYWORDSANDIDENTIFIERSTHEFOLLOWINGAREEQUIVALENTSTATEMENTSSELECTFROMFOOSELECTFROMFOOTHROUGHOUTTHISCHAPTER,ALLSQLKEYWORDSAREREPRESENTEDINUPPERCASEANDALLIDENTIFIERSINLOWERCASEFORCLARITYBYDEFAULT,SQLITEISCASESENSITIVEWITHRESPECTTOSTRINGVALUES,SOTHEVALUEMIKEISNOTTHESAMEASTHEVALUEMIKECOMMENTSCOMMENTSINSQLAREDENOTEDBYTWOCONSECUTIVEHYPHENS,WHICHCOMMENTTHEREMAININGLINE,ORBYTHEMULTILINECSTYLENOTATION/,WHICHCANSPANMULTIPLELINESFOREXAMPLETHISISACOMMENTONONELINE/THISISACOMMENTSPANNINGTWOLINES/CREATINGADATABASEBEFOREYOUCANDOANYTHING,YOUHAVETOUNDERSTANDTABLESIFYOUDONTHAVEATABLE,YOUHAVENOTHINGTOWORKONTHETABLEISTHESTANDARDUNITOFINFORMATIONINARELATIONALDATABASEEVERYTHINGREVOLVESAROUNDTABLESTABLESARECOMPOSEDOFROWSANDCOLUMNSANDWHILETHATSOUNDSSIMPLE,THESADTRUTHISTHATTABLESARENOTSIMPLETABLESBRINGALONGWITHTHEMALLKINDSOFOTHERCONCEPTS,CONCEPTSTHATCANTBENICELYSUMMARIZEDINAFEWTIDYPARAGRAPHSINFACT,ITTAKESALMOSTTHEWHOLECHAPTERSOWHATWEAREGOINGTODOHEREISTHE2MINUTEOVERVIEWOFTABLESJUSTENOUGHFORYOUTOCREATEASIMPLETABLE,ANDGETRIDOFITIFYOUWANTTOANDONCEWEHAVETHATOUTOFTHEWAY,ALLOFTHEOTHERPARTSOFTHISCHAPTERWILLHAVESOMETHINGTOBUILDON译文第四章这章主要介绍SQL及如何执行SQL。假设你没有SQL和相关模块的任何经验，SQL能像一个跳板一样让你很快进入数据库相关的世界。你会有个好的基础。而上一章有些相关的模块太过理论性，也太乏味，这一章节联系得东西就比较多，会很轻松。我们不是为了证明理论而是做出东西来。所以如果不读上一章，那也无妨。你对这章的理解也不会有障碍。对相关的模块很了解有助于SQL，但不是学SQL所必需的。SQL是与相关数据库交互的核心。是专门用于信息处理的语言。它可以排序、读/写、分类、过滤、保护、计算、生成、分组、管理数据。SQL是直观的、友好的语言。很容易使用，功能很强大。SQL的优点之一是不管你是专家还是初学者，你都能学到新的学习方法。也有很多解决问题的方法，你会发现你很沉醉于找到所欲要的更有效的解决方法，用更有效的表达方式或者更好的方法来解决问题。而且你可以学习以前没有碰触过的领域，不用考虑以前的经验。这章的主要目的是教你更好的使用SQL，教你好的技术、为你扫除将来的麻烦。因为SQL有很多问题和方面，这章把琐碎的知识点分离，并以逻辑顺序排列出来，更容易接受。然而，SQL是个大学问，需要你花费很大时间来学习，如果你学完了，应该把电脑装配好点。相关模块第三章介绍了，SQL是相关模块的产物，最原始是由EFCODD在1969年提出的。相关的模块要求相关的数据库提供一种查询语言，通过这么多年的验证，SQL变成了主流。相关的模块由三个潜在部分组成形式、功能、一致性。形式涉及到信息结构。但只有一种数据结构用于描述所有的信息。这个结构就是由TUPLES组成的RELATION。而TUPLES是由ATTRIBUTES组成的。相关的模块信息是一种逻辑的信息描述。这种逻辑描述是简易的，不复杂的抽象的信息理解。它就像数学概念，干净的、相容的，由一系列良好定义的决定规则支配，而不容易改变。这个逻辑描述是完全独立于以前数据库软件怎样把信息存到物理磁盘上这样的物理描述。这两种描述是截然不同的，物理磁盘上的改变或影响都影响不到逻辑的。逻辑的层次是不受硬件、软件、技术的控制的。第二个模块的潜在部分（功能性部分）也叫做控制组件。它在逻辑层上定义操作方法。这点在CODD的1972年的论文中被正式介绍过，标题是“RELATIONALCOMPLETENESSOFDATABASESUBLANGUAGES”。它通过定义相关的数学和微积分学在相关模块上添加功能型部件。这些事2个正常的，要么“纯粹的”基于数学的查询语言。在数据模型中描述的“关系”是数学的组件。相关的代数学和微积分学依次通过从组件理论和常规逻辑上增加操作来附属在模块上，因此来形成相关模块的关键组件。所以相关模块的形式和功能都是直接以数学的形式来的。但是每个派生的东西，都被添加了一些东西来更好的适应电脑和信息处理。查询语言一个查询语言要能包含抽象的逻辑描述以及外在的世界，并使两者可以交互。查询语言提供一种方法来找回和修改信息。也是相关模块动态的一部分。CODD故意把相关的代数学和微积学当作另一个查询语言的基线来用。相关的代数学和微积学使用一个高度数学化的符号，而这个符号有益于定义理论，在实验时并不是太友好。因此他们的目的是想把数学化的或相关的要求定义成一个友好的查询语言可以支持。满足这些要求的查询语言就叫做相关的完善。这款友好的查询语言能够提供更简单的、直觉的方法来让使用者操作相关数据，然而与此同时坚持基于理论。SQL的成长或许像这样的第一个查询语言是IBM的R系统，这个数据库相关的查询项目就是直接出自CODD的论文。最开始叫做“SEQUEL”“STRUCTUREDENGLISHQUERYLANGUAGE”。最后缩略成SQL（STRUCTUREDQUERYLANGUAGE）。其他的像ORACLE这样的公司也模仿了，很快SQL就成为DEFACTO标准。也有像INGRES的QUEL这样的查询语言，最后SQL胜出。就SQL作为标准的出现，更应该考虑的是动态的市场位置而不应该把精力放在其他上。但是为什么今天仍然很独特的原因已经很清晰了。因为包含了标准化、很强的适应性以及简易上手。SQL作为相关数据库德标准语言而被ANSI（美国国际标准化协会）、ISO（国际标准化组织）、IEC（国际化电器协会）所接受。SQL包含一个很好定义的标准来解释什么是SQL以及干什么，并且这个标准将持续20年。迄今为止，五个版本的标准分别在1986、1989、1992、1999和2003年公布。这些标准在逐步完善。每个标准的版本都在前一个基础上建立，并且有所完善，添加了新特点。所以事实上，只有一个标准在持续。第一个版本SQL86和SQL89都属于SQL1。SQL92通常属于SQL2。SQL99属于SQL3。ANSI的标准作为一个整体，是很大的。单独的SQL92标准就超过了600页。没有一个数据库产品能够做到如此贴近国际标准。然而，该标准也花了很长时间来统一性。今天SQL被广泛接受是显而易见的ORACLE、MICROSOFTSQLSERVER、DB2、INFORMIX、SYBASE、POSTGRESQL、MYSQL、FIREBIRD、TERADATA、INTERSYSTEMSCACHE、以及SQLITE都使用。但是有些相关的数据库仍然在使用SQL作为查询语句。数据库实例在考虑语法之前，让我们先看一个经典的例子。这个例子是这章将要使用到的，包含SEINFELD的各个部分。如果你以前接触过DEINFELD，那你不得不注意FOOD的细微。在SQL的发展史180个连续中有超过412个不同的FOOD。除去商业上的时间每十秒钟就引入一个新的FOOD。因为这样表现的，所以当务之急就是生产一个能正确阐述所有要求的数据库。这个数据库表正如下图41所示。正如在SQLITE中定义的数据库计划，如下所示主要表格就是FOOD。FOODS中的每一行符合单独的FOOD项目，名字则存在名字属性中。TYPE_ID属性涉及到FOOD_TYPES表，里面存储着大量FOOD的分类。最后FOODS_EPISODES表把FOODS中的FOODS和EPISODES中的EPISODES连接。软件的安装FOOD数据库是在ZIP文件样例之中，随本手册。它的源码也可以在APRESS网页上找到（WWWAPRESSCOM）。若要穿件数据库，首先在没打包的ZIP文件的根目录下安装FOODSSQL文件。从命令行创建一个新的数据库，控制例子目录并且运行下列命令这样就创建了一个叫做FOODSDB的数据库文件。如果你对SQLITE的命令行程序还是不太熟练，那么请参照第二章。运行样例为了简便，所有这章的SQL样例在样例根目录下都能找到。所以不用逐字打出来，简易的打开文件并安装你想要试的例子就OK了。一个运行更长的查询句子的简便方法就是，把他们拷贝下来，并保存在另一个文件中，这样可以在命令行中运行。例如，在TESTSQL中拷贝一个长的查询语句来实验。你可以用同样方法来运行，就像之前创建数据库一样。结果就在屏幕上显示出来。这样使得操作