MYSQL网站制作之Oracle 10g vs PostgreSQL 8 vs MySQL ...

有的时候，一些缺失的功能可以通过别的办法来实现，例如，在MySQL4.1以前，你可以通过使用join方法来替代子查询的功能。在MySQL5.0中，大多数关系型数据库所要求的功能已经都具备。mysql|oracleThisismycomparisonofinstallingandgettingstartedwithOracle10g,PostgreSQL8andMySQL5.ThisiswhatIconsiderthecomparisonofstateoftheartforthreecategoriesofDB:Commercialvs.Academevs.InternetModel.Thisisacomparisonfromtheviewofanewuserwantingtoinstalladatabasetolearn.Imtryingtokeeptheviewpointofahomeorsmallbusinessuser.
FullDisclosure:IamstronglybiasedtowardsOracleandfullyexpectednorealcompetition.
License:Iwillnotgetintoadebateoveropensourceandclosedsource.Oracleisacommercialdatabaseandrequireslicensingtouse.MySQLandPostgreSQLarebothopensourceprojects.Oracleprovidesafreedeveloperslicenseforyouto"testout"andcreateprototypes.
Configuration:Iaminstallingonanoldermachinewith256MegsofRamanda40Gigharddrive.TheCPUisa633MhzCeleron.TheOSisWindows2000Professional.Iwanttoinstallatthelowendofthespectrumtogaugetheabilityforhomeuser-typeinstallations.
OS:IchosetoperformthiscomparisonunderWindows,asIbelievethatthatisstillthemostcommonplatformthatnewpeopletotheworldofdatabaseswilluse.Bythat,Imeanpeopleinstallingathome.Fornewcorporateusers,theoddsaregoodthattheywillnothaveachoiceofeitherOSordatabasesoacomparisonispointless.
Versions:
PostgreSQL8.0-PostgreSQL8.0isaverynewproduct.Ichose8.0asitisthelatestversionanditnativelysupportsWindows.ThisisthefirstversionthatactuallydoessupportWindowswithoutanemulatororthird-partyrecompilation.
MySQL-MySQL5.0isalsoaverynewproduct.Asamatterofafact,theversionIused,v5.0.4,isactuallyabetaversion.IarguedwithmyselfaboutwhetherIshouldusethestablev4orthebetav5.Ichosev5becausev4doesnotcompetewithPostgreSQLorOracle.V4doesnothavestoredprocedures,triggers,views,etc.Withoutthosefeatures,Iwouldnotconsideritasacontender.Theupsidetothatisthatv5doessupportthosefeatures.
Oracle10gCThisisthelatestversionofOracleandwasreleasedlastyear.Inmyopinion,Oracle10gisthegoldstandardofdatabases.Nevertheless,Ialsorecognizethatitisveryexpensiveandrequiresquiteabitofknowledgetosupportinaproductionenvironment.
RankingFactors:

DocumentationandGettingStartedSupport
Easeofinstallation
EaseofVerifyingSuccessfulInstallation
CreationofNon-AdminUser
TimetoRunFirstQuery
ResourceRequirements
RankingValues:Irankeachfactorascoreof1through10,with10beingthebestorhighest.
DocumentationandGettingStartedSupport
PostgreSQL-Ifoundalotofsupportforpreviousversions(underLinux,Cygwin,etc.),butalmostnothingfor8.0underWindows.ThePostgreSQLdocumentationwebsite,http://www.postgresql.org/docs/,providesverygood,verycompletedocumentation.Inmyopinion,oneofPostgreSQLsstrengthsinthepasthasbeenthedocumentation.
Thatwebsiteincludesaverygoodinstallationdocument.However,postinstallationandgettingstarteddocumentationisverylimited.Ihopethatthatwillchangeintime.
Igoogled"PostgreSQLgettingstarted"andgotthousands,mostlyversion7,ofhits.PostgreSQLhassomeUsenetnewsgroupsupport,butInoticedtheywerenotveryactive.
PostgreSQLDocumentationRanking:5
MySQLCBecausev5issonew,thereisnotverymuchdocumentationyet.ThereisaworldofdocumentationforMySQLv3andv4.CheckAmazonorBN.com.MySQLseemstobethechoiceforalotofsmallerInternetandjava-orientedcompanies.
Becausev5isbeta,Iwillnotrankthisfactoraccordingtothedocumentationavailableforthatrelease.V4hasamazingdocumentationandIfullyexpectv5tohavethesamebythetimethesoftwareisreleasedtoproduction.Iwillrankaccordingtov4documentation.
Igoogled"MySQLgettingstarted"andgotmillions,mostlyversion4,ofhits.IcouldfindnoMYSQL-specificUsenetnewsgroupsupport,butInoticedsomepostingsforMySQLsupportonthecomp.databasesgroup.
MySQLDocumentationRanking:8
OracleCOracle,asacommercialproduct,hasalargestaffoffull-timetechnicalwriters.Everyfeatureisspelledoutandmanyfeaturesreceivetheirownbooks.TheinstallationdocumentationprovidedbyOracleissuperiorand,wherethatislacking,itismadeupbythirdpartysitesdedicatedtoOracle.Thedocumentation,aswellasthedocumentationportal,isverymature.
InadditiontoinstallationguidesforjustabouteveryOS,Oracleprovidesa"2dayDBA"guide.Thisdocumentisindispensablefornewusers.
Igoogled"Oraclegettingstarted",andreceivedmillionsofhits.OraclealsohasrobustUsenetnewsgroupsupport.
OracleDocumentationRanking:10
EaseofInstallation
PostgreSQL
IhaventpreviouslyusedPostgreSQLoutsideofCygwin(aUnixenvironmentforWindows).Iwassurprisedatthefeeloftheinstallation.Itseemedverynativeandranflawlessly.Itwasalsoaveryquickinstall.
PostgreSQLinstalledthequickest.Accordingtothedocumentation,youcaninstallPostgreSQLwithoutadministrativerights.AsthisisthefirstversionofPostgreSQLtosupportWindows(withoutCygwin),IwantedtotesttheServicessupport.PostgreSQLinstalledasaserviceandcameupwithoutproblemsafterrebooting.
PostgreSQLInstallationRanking:10
MySQL
Iranawebsiteinthepast,mainlyformyownamusement,andusedMySQLasthedatabase.ThetechnologystackIusedwasJava/Apache/MySQL.IwasfullyexpectingMySQLtowintheinstallationcompetitionbasedonexperience.However,MySQLwastheonlyinstalltofailduringinstallation.Iperformedalittlecleanupandthenre-rantheinstall.Itfailedagain.Icleanedupagain,rebootedandre-rantheinstallation.Thistimeitinstalled.Imnotsurewhatcausedtheerror.Itwasverycrypticbutitwasthesamebothtimesitfailed.Iwasabletobulldozepastthat,though.Remember,thisisabetarelease.
MySQLalsoinstalledasaserviceandonrebootcameupwithnoerrors.Theinstall,whenitrancorrectly,ranquickly.
MySQLInstallationRanking:5
Oracle
Oracleagainprovedthatitisaverymatureproduct.Ofthethree,OracleistheonlyonethatcanNOTbeinstalledwithoutadminrights.Itwouldbeniceforthistobeanoption.
Itdid,however,installeasily.Ireceivednoerrors.Oraclewastheonlyinstalltopointmetowardsthenextstepattheendoftheinstall.Thisisasurefiremethodtosupportnewusersandrequiresalmostnoeffort.Alldatabasesshouldprovidethiskindofsupport.
WhereOraclereallytookahitwasintheamountoftimeittooktoinstall.Thisisarealissueforhomeusers.TherewereacoupleofspotswhereIthoughtthemachinemightbehungasIreceivednofeedbackforanextendedamountoftime.
OracleInstallationRanking:7
EaseofVerifyingSuccessfulInstallation
Toverifythesuccessoftheinstallwasalittledifferent,butallthreeprovidedataaccesstools.Ifollowedtheinstructionsintheprovideddocumentationandwasabletoaccesstheserverinallthreeinstances.
VerificationRankingforall3:10
CreationofNon-AdminUser:
PostgreSQL:PostgreSQLusesthestandardCREATEUSERsyntaxandIwasabletoperformthistaskeasily.Thedocumentationwalkstheuserthroughthistask(whichisrequiredforsecurityreasons).
PostgreSQLCreateUserRanking:10
MySQL:Inv5,MySQLalsousestheCREATEUSERsyntax.Priorreleasesdidnot.Duetothefactthatthedocumentationisnotyetcomplete;itisnotcompletelyobviousthatCREATEUSERisavailable.Foranewuser,thiscanbeconfusing.
MySQLCreateUserRanking:7
Oracle:OracleusesthestandardCREATEUSERsyntaxandIwasabletoperformthistaskeasily.Thedocumentationwalkstheuserthroughthistask.OracleCreateUserRanking:10
TimetoRunFirstQuery
Allthreedatabasedocumentationsetsprovideatutorialforgettingconnectedandrunningyourfirstquery.
AllthreeFirstQueryRanking:10
ResourceRequirements:
PostgreSQLCIwasamazedatPostgreSQLsminimalneedforresources.IperformedsometestslikeopeningwebbrowsersandMS-Wordwhileconnectingremotelyandcouldseenoimpactatallonmytestmachine.
Icouldntfindtheminimumhardwarespecs,but256Megonanoldmachineseemstobemorethanenough.
PostgreSQLResourceRanking:10
MySQLCMySQLsminimalneedforresourcesiswellknown.Thatisoneofitsbiggestsellingpointsandalwayshasbeen.IperformedthesametestthatIpreformedforPostgreSQLandMySQLperformedaswellasPostgreSQL.
Oddlyenough,IcouldntfindtheminimumhardwarespecsforMySQLeither,but256Megonanoldmachineseemstobemorethanenough.
MySQLResourceRanking:10
Oracle-Oracleusesthemostmemory,andOracle10gusesevenmorethanpreviousversions.Itreallywouldrunbetterona512Mmachine,andIwouldrecommend1gig.
Onthetestbox,ittakesforever(well,manylongminutes)forOracletoevenstartup.WhenIopenedWord,Istartedgettingswappingandstalling.Withmultipleremoteconnections,thedatabasecametoastandstill.Oraclewillinstallandrunin256Megs,butIdontrecommendit.
OracleResourceRanking:3
Sohowdoeseachstackup?Herearethefinalrankings:

PostgreSQL:55
MySQL:50
Oracle:50
So,thatistheinstallandfirstuseranking.Thatisnotatallwhatmyexpectationswere.Ithinkitsgreatthatallthreeratedsohigh.IwasnotexpectingthatorthatOracleandMySQLwouldtie.IwasdefinitelynotexpectingPostgreSQLtocomeinfirst.
However,installationisonlyasmallpieceofusingadatabase.Thereislanguagesupport,administration,additionalusabilityfeatures,securityandthird-partysupport.Inthenearfuture,Iwillwriteupacomparisonofthesecomponents.

Oracle10gMySQL5.0PostgreSQL8.0文档和进修撑持1085易于安装性7510易于查验乐成安装101010创立非admin权限用户10710实行第一次查询的工夫101010资本占用31010
固然postgresql的文档只得5分，不外我以为postgreSQL官方文档也很全，只是由于还不敷提高以是履历之谈得材料很少，我信任今后会多.DBaaS向客户提供了许多与其他云服务相类似的优势：一个灵活的、可扩展的MySQL学习教程、按需服务的平台，它以自助服务和便捷管理为导向，可以对环境中的资源进行调配。

理解了存储结构，再阅读下性能优化的章节基本上会对sqlserver有个清晰地认识

另一个是把SQL语句写到服务器端,就是所谓的SP(存储过程);

你可以简单地认为适合的就是好，不适合就是不好。

光写几个SQL实在叫无知。

如果是将来做数据库的开发设计，就应该详细学习T-SQL的各种细节，包括T-SQL的程序设计、存储过程、触发器以及具体使用某个开发语言来访问数据库。

入门没那么困难，精通没那么容易

不过话说回来了，绝大多数的性能优化准则与对sqlserver存储的结构理解息息相关