linux運維就業(yè)架構(gòu)零基礎(chǔ)到大神包合集專用隨堂mysql01

主講主講：馬永亮(馬哥QQ群客服QQ：2813150558,InstallingAccessingcommand-lineGUITodescribeanassortmentofproductsandsystemsthathaveincludedanythingfromacollectionoffilestoacomplexstructuremadeupofuserinterfaces,datastorageandaccessmechanisms,andclient/serverAtitssimplest,adatabaseisacollectionofdatathatisusuallyrelatedinsomefashion.Yetadatabaseismorethansimplyacollectionofrelateddata.Thedatamustbeorganizedandclassifiedinastructuredformatthatisdescribedbymetadata,whichisdatathatdescribesthedatabeingstored.TheRelationalTheTheRelationalTheldataadeparturetherigidstructuresofthehierarchicalandApplicationsaccessingahierarchicaldatabaserelyonadefinedimplementationofthatdatabase,andthedatabasestructuremustbehard-codedintotheapplication’sprogramminglanguage.Ifthedatabasechanges,theapplicationmustchange.However,arelationaldatabaseisindependentoftheManagementManagementADBMSismadeupofacomprehensivesetofserverandclienttoolsthatsupportvariousadministrativeanddata-relatedtasksAttheveryleast,aDBMSmuststoredataandallowdatatoberetrievedandmodifiedinawaythatprotectsthedataagainstoperationsthatcouldcorruptorinsertinconsistenciesintothe Managing Maintaining Maintaining Managing Supporting Optimizing Providingback-upandrecovery Processingrequestsfordataretrievaland開數(shù)據(jù)庫開數(shù)據(jù)庫設(shè)代碼設(shè)計：存儲過程、存儲函數(shù)、觸發(fā)管連接管理及優(yōu)數(shù)據(jù)庫軟件安裝及升配置優(yōu)WhatWhatisSQLis,aboveallelse,acomputerlanguageusedtomanageandinteractwithdatainarelationaldatabaseSQLworksinconjunctionwithaRDBMStodefinethestructureofthedatabase,storedatainthatdatabase,manipulatethedata,retrievethedata,controlaccesstothedata,andensuretheintegrityofthedataMySQLisbuiltwithCandC++.Thefunctionsthatsuchanapplicationperformsinordertosupportconnectivity,provideAPIs,enablenetworkaccess,orinteractwithclienttoolsarecarriedoutattheCandC++programminglevel.TheprimarypurposeofSQListoallowtheRDBMStointeractwiththedata.AABriefHistoryofAftertherelationalmodelwasintroducedtothedatabasedevelopmentcommunityintheearly1970s,IBMbeganresearchingwaystoimplementthatmodel.IBM’sresearch,referredtoastheSystem/Rproject,resultedinaprototypeofthefirstRDBMS.AspartoftheSystem/Rproject,IBMproducedthefirstincarnationofarelationaldatabaselanguage,whichwasknownStructuredEnglishQueryLanguageOverthenextfewyears,IBMupdatedtheprototypeandreleasedSEQUEL/2,whichwaslaterrenamedtoANSIANSIBythemid-1980s,relationaldatabasesandSQLhadbecomeanindustrystandardANSIreleasedthefirstpublishedSQLstandard(SQL-86)inANSIupdatedthestandardin1989(SQL-89)andagainin1992(SQL-92)Becauseofthesignificantexpansionofthestandard,SQL-92definedthreelevelsofconformance:Entry:Thislevelrepresentedthemostbasicstageofconformance,andwasprimarilyontheSQL-89standard,withonlyafewimprovements.Intermediate:Althoughthislevelrepresentedsignificantadvancementstotheproduct,itwasbelievedthatmostproductscouldachievecompliance.Full:ARDBMShadtobeincompletecompliancewiththeIn1999,ANSI,alongwiththeInternationalOrganizationforStandardization(ISO)publishedSQL:1999,thefirstcompleteupdatetotheSQLstandardsince1992SQL:1999standardtookadifferentapproachtoconformanceTobeinconformancetothenewstandard,aRDBMShadtobeincompliancewithCoreSQLMostRDBMSs,includingMySQL,conformtotheEntrylevelofSQL-92andachievsomeconformancetoCoreSQLinSQL:1999SQL:2003,introducedsomeadditionalfeaturescomparedSQL:1999,suchasExtensibleMarkupLanguage(XML)范約候選primary范約候選primary（主鍵foreignkey（外鍵檢查性約unique惟一MySQLMySQL主講：馬永亮(馬哥QQ群客服QQ：2813150558,事務(wù)，隔離，并發(fā)控制，用戶和權(quán)監(jiān)索引類型：查備份和恢復(fù)制功集Oracle:9i,10GSQLServerOracle:9i,10GSQLServerMySQLTheMySQLMySQLisarelationaldatabasemanagementsystem(RDBMS)thatrunsasTheMySQLMySQLisarelationaldatabasemanagementsystem(RDBMS)thatrunsasaserverprovidingmulti-useraccesstoanumberofMySQLisofficiallypronounced?l/("MyS-Q-L"),isoftenpronounced/ma?si?kw?l/("MySequel").ItisnamedoriginaldeveloperMichaelWidenius'sdaughterTheMySQLdevelopmentprojecthasmadeitssourcecodeavailableunderthetermsoftheGNUGeneralPublicLicense,aswellasunderavarietyofproprietaryagreementsMySQLisownedandsponsoredbyasinglefor-profitfirm,theSwedishcompanyMySQLAB,nowownedbySunMicrosystems,asubsidiaryofOracleCorporationMont,Mont,TcX,Unireg,Unireg,MySQL96,MySQL,完完全多線查詢緩Scalability（伸縮性EaseofPortabilityandStandardsComplianceMultiuserSupportWideApplicationC,C++,Java,Perl,PHP,Python,JDBC,ODBC,OpenTheTheMySQLTherootsofMySQLserverarefoundinadatabasesystemcalledUniregthatwasdevelopedbyMichael‘‘Monty’’WideniusforaSwedishcompanycalledTcXduringthe1980sTheinitialreleaseofMySQLserverwascreatedin1995whenMontyaddedanSQLinterfacetoUniregDavidandMonty,togetherwithAllanLarsson,foundedMySQLABin1995.InIn2001MySQLbegansupportingtransactionswiththeintegrationoftheBDBandInnoDBenginesOvertheyearsthefeaturesetoftheMySQLserverhasgrowntocoveralmostanyfeatureneededinanenterprisedatabaseserverMySQLServerMySQLServerProductProductMySQLServer(mysqld,MySQLMySQLMySQLMySQLQueryMySQLMySQLMigrationMySQLEmbeddedMySQLDriversandCommunityCommunityandEnterpriseserverInAugustof2007MySQLABbeganofferingtheMySQLserverintwodifferentversions:MySQLMySQLContributetoMySQLMailingUserMySQLMySQLMySQLInstallingandInstallingandAccessingMySQLServer主講：馬永亮(馬哥QQ群客服QQ：2813150558,MySQL’sLogical客戶存儲引MySQL’sLogical客戶存儲引文件系線程優(yōu)化分析緩連接/線程處MySQLMySQLBeforeBeforeSourcecodeinstallationandbinaryfileProblemswithVendor-SuppliedSomevendor-suppliedpackagesmaynotincludeallthefeaturesandbugfixes,becausethepackagingprocessdoesnotnecessarilyincludeallthedocumentedchanges.ChoosingtheMySQLGA(GeneralRC(ReleaseInstallingfromInstallingfromanrpmfileonMySQLRPMpackageonpackageonMySQLServerArchivePackageInitialInitialAfterinstallation,mysqldmustbeinitializedandManyMySQLprograms,includingmysqld,useacentralconfigurationfiletoallowadatabaseadministratortosetvariousparametersthatwillpersistacrossrebootsTheMySQLconfigurationfileisusedformanyMySQLprograms,includingmysqldThiscentralizedconfigurationfileallowscontrolofthebehaviorandcharacteristicsofMySQLDifferentMySQLprogramsuseoptionsfromdifferentdirectivesintheconfigurationfileForexample,mysqldusesoptionsunderthe[mysqld]directive.Clientprogramssuchasmysqlandmysqladminuseoptionsunderthe[client]Thisconfigurationfileisalsoknownasanoptionfile,becauseitcontainsoptionsandtheirvaluesmysqld--Onstartup,mysqldlooksforthisconfigurationfilemysqld--Onstartup,mysqldlooksforthisconfigurationfileinThisisdoneinaspecificorder,OnUnix-basedsystemsfollowingorderofprecedenceis/path/to/filewhen--defaults-extra-file=/path/to/fileEveniftheserverfindsaconfigurationfileinthefirstitwillstillcheckeachlocationforaconfigurationIfmorethanonefileislocated,alloptionfilesareusedbyIfmorethanonefilehasthesameoption,thelastoptionread$MYSQL_HOME$MYSQL_HOMEreferstoanoperatingsystemuserenvironmentvariable.Ifitisnotset,MySQLprogramswillsetittobethesameasthebasedirectory(basedir)bydefault,unlessthereisaffileinthedatadirectory(datadir),inwhichcasethedatadirectorywillbeThedefaults-extra-fileisusedtospecifyanadditionallocationofaconfigurationfile.Itispassedasanargumentwhenbeginningstart-upofthemysqldmysqld_safe--defaults-extra-InitialConfigurationfilesInitialConfigurationfilesonWithWindowsserversthefollowingorderofprecedenceisused:%WINDIR%\my.ini, C:\my.ini,%INSTALLDIR%\my.ini,isMySQLPost-InstallConfigurationonSettinginitialMySQLPost-InstallConfigurationonSettinginitialAllinitialaccountsarecreatedwithoutWithUnix-basedserversatotaloffiveusersarecreatedbythemysql_install_dbscriptTherearethreerootaccounts:root@,root@localhost,andtheroot@hostnameTwoanonymoususeraccounts:‘’@localhostand‘RootuserRootuserpassword#mysqladmin-uroot’new-password’-#mysqladmin-uroot-hthis_host_namepassword’new-AnotherYouhavetwomethodsofdoingthismanually:executingtheSETPASSWORDcommandorexecutingtheappropriateUPDATEstatementfollowedbyaFLUSHPRIVILEGESmysql>SETPASSWORDFOR’root’@’host_name’=mysql>UPDATEmysql.userSETPassword=WHEREUser=’root’andmysql>FLUSHAnonymousAnonymousAnonymoususersareuseraccountswithausernameofanemptystring(’’)Itisabestpracticetodroptheanonymoususersbecausethereisveryrarelyanyvalidreasontoretaintheseaccountsmysql>DROPUSERToensurethatyouhavedroppedallanonymoususers,runthefollowingquery:mysql>SELECTuser,host,passwordFROMmysql.userWHEREAccessingAccessing主講：馬永亮(馬哥QQ群客服QQ：2813150558,GeneralGeneralMySQLMySQLoperatesinanetworkedenvironmentusingaclient/serverarchitectureInotherwords,acentralprogramactsasaserver,andvariousclientprogramsconnecttotheservertomakerequestsAMySQLinstallationhasthefollowingmajorMySQLServer,ormysqld,isthedatabaseserverClientMySQLnon-clientMySQLMySQLMySQLServer,ormysqld,isthedatabaseserverTheservermanagesaccesstotheactualdatabasesondiskandinmemoryMySQLServerismulti-threadedandsupportsmanysimultaneousclientconnectionsClientscanconnectviaseveralconnectionClientClientForcommunicatingwiththeservertomanipulatetheinformationinthedatabasesthattheservermanagesMySQLABprovidesseveralclientprograms.Thefollowinglistdescribesafewofthem:MySQLWorkbenchandMySQLAdministratoraregraphicalfrontendstotheservermysql isacommand-lineprogramthatactsasatext-basedfrontendfortheserver.It'susedforissuingqueriesandviewingtheresultsinteractivelyfromaterminalwindowOthercommand-lineclientsincludemysqlimportforimportingdatafiles,mysqldumpformakingbackups,mysqladminforserveradministration,andmysqlcheckforcheckingtheintegrityofthedatabasefilesMySQLMySQLnon-clientTheseareprogramsthatactindependentlyoftheTheydonotoperatebyfirstestablishingaconnectiontotheserver.myisamchkisanexample.Itperformstablecheckingandrepairoperations.Anotherprograminthiscategoryismyisampack,whichcreatescompressedread-onlyversionsofMyISAMBothutilitiesoperatebyaccessingMyISAMtablefilesdirectly,independentofthemysqlddatabaseserver.AccessingAccessingmysqldwithCommand-LineThesetoolsincludemysql,mysqladmin,andThougheachtoolperformsdifferentfunctions,theyhaveseveralcommonoptionsOftentheseoptionswillhavealongformandashortHelp----CommonOptionsforCommonOptionsforMySQLOptionsforaThe--protocoloption,ifgiven,explicitlyselectsthecommunicationprotocolthattheclientprogramshoulduseforconnectingtotheserverIftherearenoprotocol,socket,host,orportoptionsgiven,Iftherearenoprotocol,socket,host,orportoptionsgiven,socketfilewillbeusedforIfaportoptionisgivenandnohostorprotocoloptionsareasocketfileisusedtoIftheprotocolspecifiedisTCP,theconnectionwillbemadeTCP/IPevenifasocketoptionisIftheprotocolspecifiedisSOCKETandaportisgivenbuthostisgiven,asocketfileisusedtoIftheprotocolspecifiedisSOCKETandthehostoptionisgiven,asocketfileisusedtoIftheprotocolspecifiedisSOCKETandahostoptionotherlocalhostisgiven,anerroroccurs(regardlessofwhetheraoptionisIfthehost,port,andsocketoptionsareallgivenbutprotocoloptionisnot,thehostandportareOtherInadditiontothecommonoptions,themostfrequentlyusedOtherInadditiontothecommonoptions,themostfrequentlyusedoptiontomysqlisthedatabaseoption---OtherCommonOtherCommonOptionsforCommand-LineSetoptionsSetoptionsmysqlwillreadoptionsinthe[client]directiveofconfigurationfilesUsingUsingThemysqlclientprogramenablesyoutosendqueriestotheMySQLserverandreceivetheirresults.Itcanbeusedinteractivelyoritcanreadqueryinputfromafileinbatchmode:Interactivemodeisusefulforday-to-dayusage,forquickone-timequeries,andfortestinghowqueriesworkBatchmodeisusefulforrunningqueriesthathavebeenprewrittenandstoredinafile.It'sespeciallyvaluableforissuingacomplexseriesofqueriesthat'sdifficulttoentermanually,orqueriesthatneedtoberunautomaticallybyajobschedulerwithoutuserAftermysqlAftermysqlconnectstotheMySQLserver,itprintsamysql>prompttoindicatethatit'sreadytoacceptqueriesToissueaquery,enteritattheCompletethequerywithastatementterminator(typicallyaTheterminatortellsmysqlthatthestatementhasbeenenteredcompletelyandshouldbeexecutedWhenmysqlseestheterminator,itsendsthequerytotheserverandthenretrievesanddisplaystheresultThemysqlThemysqlUsefulfeaturesofThemostUsefulfeaturesofThemostusefulfeaturesofmysqlLeavethemysqlinteractiveshell(commandonly,exit,quitSpecifytheusefeaturetochangedatabaseswithinmysql,theshortformis\umysqlkeepsanin-memorylistofthecommandstypedintheSomeshellinterpreterkeysretaintheirfunctiononthemysqlcommandline,Forexample:Ctrl-deletesthepreviouswordandstoresitinaCtrl-yretrieves)thecontentsofbuffersfilledwithCtrl-uorCtrl-udeletestheentirelinepriortothecursorpositionandstoresitinabufferCtrl-amovesthecursortothebeginningofthecurrentCtrl-emovesthecursortotheendofthecurrentOnOnnon-Windowsmachines,mysqlsavescommandsinahistoryOnnon-Windowsmachines,mysqlhasatabcompletionDatabaseobjectsareloadedwhenchangingtotheTodisablethefeature,usingoneofthefollowingToenablethefeaturefornewobjects,usingoneofthefollowingCancelaquerywiththeclearCancelaquerywiththeclearcommand(clearor\dorrcommandfollowedwordcansetanewThedefaultdelimitercanbechangedbyspecifyingtheTosendacommandregardlessofthedelimiter,use\gorgo Bydefault,outputisdisplayedhorizontally.Todisplayoutputvertically,sendthecommandusingthe\GorocommandTochangeTochangethedefaultoutputdisplaytoalwaysbevertical,specifythe-Eorthe--verticaloptiontolOneofthemostuser-friendlyfeaturesisthe--auto-vertical-outputoptiontomysql.Thisoptionwilldisplayoutputhorizontallybydefault.Iftheoutputiswiderthanthewidthofyourterminal,thedisplaywillautomaticallyoutputvertically.Thevaluesofpage_programaresimilartovaluesfromthePAGERoperatingsystemenvironmentvariable.Theyincludemore,less,cut,andgrepOutputwillnowbepaginatedaswhenusingtheoperatingsystem’smorecommand.Bydefault,thepagerissettothePAGERoperatingsystemenvironmentOutputwillnowbepaginatedaswhenusingtheoperatingsystem’smorecommand.Bydefault,thepagerissettothePAGERoperatingsystemenvironmentvariable.Ifnoneisset,thedefaultistoprinttostdout(theterminalwindowyouseeonyourscreen).Toresetthepager,usethenopageror\ncommand,orsetthepagerwithnovalueOnanon-Windowsmachine,youcanescapetoashellandrunonecommandbyusingthesystemor\!UsingScriptUsingScriptFileswithmysqlcanalsoacceptinputfromaAninputfilecontainingSQLstatementstobeexecutedisknownasa"scriptfile"ora"batchfile."AscriptfileshouldbeaplaintextfilecontainingstatementsinthesameformatthatyouwouldusetoenterthestatementsinteractivelyInparticular,eachstatementmustendwithaOnewaytoprocessOnewaytoprocessascriptfileisbyexecutingitwithaSOURCEcommandfromwithinmysql:mysql>NoticethattherearenoquotesaroundthenameoftheThefilemustbelocatedontheclienthostwhereyou'rerunningmysqlTheotherwaytoexecuteascriptfileisbynamingitonthemysqlcommandline.Invokemysqlandusethe<inputredirectionoperatortospecifythefilefromwhichtoreadqueryinput:#<mysqlOutputmysqlOutputBydefault,mysqlproducesoutputinoneoftwoformats,dependingonwhetheryouuseitininteractiveorbatchmodeToselectanoutputformatdifferentfromeitherofthedefaultformats,usetheseoptions:--htmlor-ProduceoutputinHTML--xmlor-ProduceoutputinXMLWhenWhenyouissueanSQLstatementwhilerunningmysql,theprogramsendsthestatementtotheMySQLservertobeexecutedSELECT,INSERT,UPDATE,andDELETEareexamplesofthistypeofinputmysqlalsounderstandsanumberofitsowncommandsthataren'tSQLstatementsQUIT,SOURCE,STATUSandsoAfulllistofmysqlcommandscanbeobtainedusingtheHELPcommandUsingServer-SideUsingServer-Side Themysqlprogramcanaccessserver-sidehelp.Thatis,youcanperformlookupsintheelfraparticulartopic,rightfromthemysql>Thegeneralsyntaxforaccessingserver-sidehelpisHELPkeywordUsingUsingthe--safe-updatesIt'spossibletoinadvertentlyissuestatementsthatmodifymanyrowsinatableorthatreturnextremelylargeresultsetsThe--safe-updatesoptionhelpspreventtheseproblems.TheoptionisparticularlyusefulforpeoplewhoarejustlearningtouseMySQL.--safe-updateshasthefollowingeffects:UPDATEandDELETEstatementsareallowedonlyiftheyincludeaWHEREclausethatspecificallyidentifieswhichrecordstoupdateordeletebymeansofakeyvalue,oriftheyincludeaLIMITclauseOutputfromsingle-tableSELECTstatementsisrestrictedtonomorethan1,000rowsunlessthestatementincludesaLIMITclauseUsedUsedtoperformadministrativeSomeofthetasksincludecreatinganddroppingdatabases,displayingserverconfiguration,currentstatusinformation,changingapassword,andstoppingaserver.mysqladmincanbeveryusefulinscriptingmysqladmin[options]command[arg][command[arg]]mysqladminCommandmysqladminCommandmysqladminmysqladminUsedtoreturnsomebasicinformationfrommysqldincludinguptime,currentnumberofthreads,andqueriespersecondbeingexecutedCancombinethestatuscommandwiththe--sleepoptiontohavemysqladminpauseforthespecifiednumberofsecondsandthendisplayupdatedstatusinformation.Unlessyouspecifythe--countoption(whichonlyworksinconjunctionwiththe--sleepoption)GUIGUINavicatforMySQLMySQLQueryMySQLMySQLTheSQLyo