PPTslinux视频MySQL Chapter 2

DevelopingWithMySQL,2020/6/15,RHELClusteringitisnotvalidforanyoftheTEXTdatatypes.TheDEFAULTattributecausesacharacterstringtohaveadefaultvaluewhenavalueisnotspecified,2020/6/15,RHELClusteringbinarystringsareorderedbyeachbytesvalue,2020/6/15,RHELClusteringSELECTglobal.sql_mode;SELECTVariable_name,Variable_valueFROMINFORMATION_SCHEMA.GLOBAL_VARIABLESWHEREVariable_name=sql_mode;Toseethevalueofthesessionservervariable,useoneofthefollowingmethods:SHOWSESSIONVARIABLESLIKEsql_mode;SELECTsession.sql_mode;SELECTVariable_name,Variable_valueFROMINFORMATION_SCHEMA.SESSION_VARIABLESWHEREVariable_name=sql_mode;,2020/6/15,RHELClusteringifitisnotdesired,settheappropriatesql_modetothrowanerror:,2020/6/15,RHELClusteringSETGLOBALsql_mode=TRADITIONAL;SELECTSESSION.sql_mode;,2020/6/15,RHELClusteringIfyoutrytocreateadatabasethatalreadyexists,anerroroccurs.Thefollowingcanavoiderror:CREATEDATABASEIFNOTEXISTSdb_name;TheCREATEDATABASEstatementhastwooptionalclauses,CHARACTERSETandCOLLATE,thatassignadefaultcharactersetandcollationforthedatabase.Ifgiven,theyappearattheendofthestatementfollowingthedatabasename:CREATEDATABASEdb_nameCHARACTERSETchar_setCOLLATEcollate;Tomakethenewdatabasethedefaultdatabase,issueaUSEstatement:USEdb_name;,2020/6/15,RHELClusteringthatis,CHARACTERSETandCOLLATE:ALTERDATABASEdb_nameCOLLATEcollate;ALTERDATABASEdb_nameCHARACTERSETcharset;Ifnodatabaseisnamed,thestatementchangestheoptionsforthedefaultdatabase.Thisrequiresthattherebeacurrentlyselecteddatabase.Otherwise,anerroroccursYoucannotuseALTERDATABASEtorenameadatabase,2020/6/15,RHELClusteringItisanerrorifthedatabasedoesnotexist.Tocauseawarninginstead,includeanIFEXISTSclause:DROPDATABASEIFEXISTSdb_name;AnywarninggeneratedwhenIFEXISTSisusedcanbedisplayedwithSHOWWARNINGSDROPDATABASEdoesnotrequirethedatabasetobeempty.Beforedroppingthedatabase,MySQLremovesanyobjectsthatitcontains,suchastables,storedroutines,andtriggers,TablesandIndexes,2020/6/15,RHELClusteringonedatabasecannotcontainanother,2020/6/15,RHELClustering,2020/6/15,RHELClustering,2020/6/15,RHELClusteringTheALTERTABLEstatementallowsyouconvertatablefromonestorageenginetoanother:ALTERTABLEusersENGINE=MyISAM;IfaCREATETABLEstatementincludesnoENGINEoption,MySQLcreatesthetableusingthedefaultstorageengine,whichisdeterminedfromthevalueofthestorage_enginesystemvariable,2020/6/15,RHELClusteringAnyclientcanchangeitsowndefaultstorageenginebyissuingeitherofthesestatements:SETSESSIONstorage_engine=engine_name;SETstorage_engine=engine_name;,2020/6/15,RHELClusteringAnotherwaytorenameatableisbyusingtheRENAMETABLEstatement:RENAMETABLEold_nameTOnew_name;RENAMETABLEcanperformmultipletablerenamesinasingleoperationForTEMPORARYtables,RENAMETABLEdoesnotwork,2020/6/15,RHELClusteringInMySQL,asingleDROPTABLEstatementcannameseveraltablestobedroppedsimultaneously:DROPTABLEt1,t2,t3;Topreventanerrorfromoccurringifatabledoesnotexistwhenyouattempttodropit,addanIFEXISTSclausetothestatementDROPTABLEt1IFEXISTS;,2020/6/15,RHELClusteringaUNIQUEindexcan.Ifaunique-valuedindexmustbeallowedtocontainNULLvalues,youmustuseaUNIQUEindex,notaPRIMARYKEYEachtablemayhaveonlyoneindexdefinedasaPRIMARYKEY.(TheinternalnameforaPRIMARYKEYisalwaysPRIMARY,andtherecanbeonlyoneindexwithagivenname.)ItspossibletohavemultipleUNIQUEindexesforatableItfollowsfromtheprecedingdescriptionthataPRIMARYKEYisatypeofunique-valuedindex,butaUNIQUEindexisntnecessarilyaprimarykeyunlessitdisallowsNULLvalues,2020/6/15,RHELClustering,2020/6/15,RHELClusteringGROUP_CONCAT()addsstringstotheresultintheorderinwhichthedatabaseserverreadsthem.Tochangetheconcatenationorder,addanORDERBYclause,2020/6/15,RHELClusteringotherwise,thecommentwillbeleftasacommentandignoredbyotherdatabasesandmysqldversions-:thestandardSQLsimplecommentintroducer/*/:allowpartiallineandmulti-linecommenting/*!*/:MySQLspecificcode,andthemysqldserverwillparseit,2020/6/15,RHELClustering&StorageManagement,page:114,Case-sensitivity,Ingeneral,mysqldiscase-insensitivewithrespecttonamesoffields,indexes,storedroutinesandeventsCase-sensitivityfornamesisdeterminedbywhetherornotthefilesystemiscase-sensitiveforthefollowing:DatabasesTablespacesTablesViewsAliasesTriggersLogfilegroups,2020/6/15,RHELClustering&StorageManagement,page:115,Escapecharacters,Theescapecharacteristhebackslash()Anescapesequenceisabackslashfollowedbyonecharactertoprintthecharacter.toprintthecharacter,evenifthestringisquotedwith.toprintthecharacter,evenifthestringisquotedwith._printsthe_character.ThiscanbeusedtosearchfortheactualvaluewhenusingLIKE.If_isnotescaped,itisusedasthewildcardcharacterforonecharacter.%printsthe%character.Thiscanbeusedtosearchfortheactualvalue%whenusingLIKE.If%isnotescaped,itisusedasthewildcardcharacterforoneormorecharacters.bprintsabackspacecharacterrandnprintacarriagereturnandanewline,respectivelytprintsatabseparator0andZprintASCII0(NUL)andASCII26(Ctrl-Z),respectively.InWindows,Ctrl-Zisaspecialcharactermarkinganendofafile,andonMacOSXandUnix,Ctrl-Zisthespecialkeysequencethatwillsuspendthecurrentforegroundprocess.,2020/6/15,RHELClustering&StorageManagement,page:116,Naminglimitationsandquoting,Identifiersarenamesof:databases,tables,views,fields,indexes,tablespaces,storedroutines,triggers,events,servers,logfilegroups,andaliases(specifiedwiththeASkeyword).Identifiersarealllimitedto64charactersexceptaliases,whicharelimitedto255charactersIdentifierscanbealmostanything.However,identifiersmaynotendwithoneormorespacesIdentifiernamescanbereservedwordsornumbers,andincludepunctuation.However,tobeparsedcorrectly,suchidentifiersneedtobequotedOntheotherhand,numberswillbeparsedasnumbers.Inordertoparseanumberasanidentifier,itmustbequoted,2020/6/15,RHELClustering&StorageManagement,page:117,ModifyingSELECTBehavior,SQL_CACHESQL_NO_CACHESQL_BUFFER_RESULTSQL_HIGH_PRIORITY,2020/6/15,RHELClustering&StorageManagement,page:118,ViewingDatabase,Table,andFieldInformation,SHOWCREATETABLESHOWINDEXSHOWENGINESSHOWPROCESSLISTSHOWERRORSSHOWWARNINGSSHOWSTATUSSHOWTABLESTATUSSHOWCHARACTERSETSHOWCOLLATION,IndexandView,2020/6/15,RHELClustering&StorageManagement,page:120,Explain,idTheSELECTidentifier.ThisisthesequentialnumberoftheSELECTwithinthequery.,2020/6/15,RHELClustering&StorageManagement,page:121,select_type,2020/6/15,RHELClustering&StorageManagement,page:122,tableThetabletowhichtherowofoutputrefers.typeThejointypesystemThetablehasonlyonerow(=systemtable)constThetablehasatmostonematchingrow,whichisreadatthestartofthequeryeq_refOnerowisreadfromthistableforeachcombinationofrowsfromtheprevioustablesOtherthanthesystemandconsttypes,thisisthebestpossiblejointypeItisusedwhenallpartsofanindexareusedbythejoinandtheindexisaPRIMARYKEYorUNIQUENOTNULLindex,2020/6/15,RHELClustering&StorageManagement,page:123,ThejointyperefAllrowswithmatchingindexvaluesarereadfromthistableforeachcombinationofrowsfromtheprevioustablesindexThisjointypeisthesameasALL,exceptthatonlytheindextreeisscannedALLAfulltablescanisdoneforeachcombinationofrowsfromtheprevioustables,2020/6/15,RHELClustering&StorageManagement,page:124,possible_keysThepossible_keyscolumnindicateswhichindexesMySQLcanchoosefromusetofindtherowsinthistablekeyThekeycolumnindicatesthekey(index)thatMySQLactuallydecidedtouseIfMySQLdecidestouseoneofthepossible_keysindexestolookuprows,thatindexislistedasthekeyvaluekey_lenindicatesthelengthofthekeythatMySQLdecidedtouse,2020/6/15,RHELClustering&StorageManagement,page:125,refshowswhichcolumnsorconstantsarecomparedtotheindexnamedinthekeycolumntoselectrowsfromthetablerowsIndicatesthenumberofrowsMySQLbelievesitmustexa