linux运维就业架构零基础到大神包合集专用随堂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开数据库开数据库设代码设计：存储过程、存储函数、触发管连接管理及优数据库软件安装及升配置优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,事务，隔离，并发控制，用户和权监索引类型：查备份和恢复制功集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客户存储引文件系线程优化分析缓连接/线程处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