mysql常用命令大全_最完整版

mysqlnetstartmySql;mysql-uroot-p/mysql-hlocalhost-uroot-pdatabaseName;showdatabases;usedatabaseName;showtablesshowcolumnsfromtableNamesourcefileName.txt;_;altertabletabelNameaddcolumnfieldNamedateType;altertabletabelNameaddcolumnfieldName1dateType,addcolumnsfieldName2dateType;:;;grantallon*.*touser@localhostidentifiedbypassword;';''\g'selectnow();selectuser();selectversion();selectdatabase();1student_coursestudentsrm-fstudent_course/students.*2(test)mysqldump-uroot-ptestc:\test.txt(testmytable)mysqldump-uroot-ptestmytablec:\test.txt(test)mysql-uroot-ptest3(zengchao)createtemporarytablezengchao(namevarchar(10));4createtableifnotexistsstudents(,,);5createtabletable2select*fromtable1where11;6createtabletable2select*fromtable1;7altertabletable1renameastable2;8altertabletable1modifyidintunsigned;//idintunsignedaltertabletable1changeidsidintunsigned;//idsidintunsigned9altertabletable1addindexind_id(id);createindexind_idontable1(id);createuniqueindexind_idontable1(id);//10dropindexidx_idontable1;altertabletable1dropindexind_id;11(id:name=)selectconcat(id,':',name,'=')fromstudents;12limit(1020)0select*fromstudentsorderbyidlimit9,10;13MySQL14MySQL,=,=,,=,between,in,%_like151)2316SQLexplain1table2type(ALL/Range/Ref)ref3possible_keys4key5key_len6refconst7rowsMySQL8extraMySQL1712acharvarcharbcdMyISAM18notnullenumnotnullMySQLnullenumMySQLenum19optimizetablebloboptimizetableoptimizetableMyISAMBDBmysqldump20procedureanalyse()procedureanalyse()selectprocedureanalyse()select*fromstudentsprocedureanalyse();select*fromstudentsprocedureanalyse(16,256);procedureanalyse()16256enum211select2query_cache_type301selectsql_no_cache2selectsql_cachequery_cache_size2212I/O310:53(1442)(1):coolmist2007-10-301.Mysql1.1.MySQLMySQL1.2.1.3.MysqlE:\mysqldosmysqlmysql1.4.:\mysqlmysql-h10.4.3.188-uptsdb-pE:\mysqlmysql--host=10.4.3.188--user=ptsdb--passwordE:\mysqlmysql-uroot-pE:\mysqlmysql--user=root-password:urootpathmysqlbinC:\ProgramFiles\MySQL\MySQLServer5.0\bin2.2.mysqladminmysqladmin-u-ppassword1E:\mysqlmysqladmin-urootpasswordrootroot-p2rootroot123E:\mysqlmysqladmin-uroot-prootpasswordroot123usermysqlUPDATEuserSETpassword=PASSWORD(test123)WHEREuser='test';mysqlFLUSHPRIVILEGES;mysqlSETPASSWORDFORtest=PASSWORD('test123');mysqlFLUSHPRIVILEGES;grantgranton.|to@IDENTIFIEDBY1testlocalhost(GRANTOPTION)test(test)mysqlgrantallon*.*totest@localhostidentifiedbytest;mysqlgrantallon*.*totest@localhostidentifiedbyPASSWORD*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29;2testabctestrootMYSQLmysqlgrantselect,insert,update,deleteontest.*totest@%Identifiedbyabc;mysql.usertesttesttestmysqlrevokeinsert,update,deleteontest.*fromtest@%mysqlREVOKEALLPRIVILEGES,GRANTOPTIONFROMtest@%mysqlFLUSHPRIVILEGES;TestmysqlDeletefromuserwhereuser='test'andhost='%'mysqlFLUSHPRIVILEGES;2testinternetmysqltest()mysqlgrantselect,insert,update,deleteontest.*totest@IPIdentifiedbyabc;2.3.:mysqlshowdatabases;mysqlshowschemas;--mysql5.0.2mysqlshowtablesfrommydb;MysqlSHOWTABLESTATUS;:mysqlSHOWCHARACTERSET;:mysqlshowcreatetablequote;:mysqlSHOWGRANTSFOR'test'@'localhost';mysqlSHOWGRANTS;mysqlSHOWGRANTSFORCURRENT_USER;mysqlSHOWGRANTSFORCURRENT_USER();index:mysqlSHOWINDEXFROMmydb.mytable;:mysqldescmydb.tablename;mysqlshowcolumnsfrommydb.tablename;MySQLmysqlselectversion();mysqlSelect*frommysql.func;mysqlSelect*frommysql.proc;mysqlSHOWENGINES;:mysqlSHOWVARIABLES;:MysqlSHOWSTATUS;MysqlSHOWPROCESSLISTINNODBMysqlSHOWINNODBSTATUSMysqlSHOWSTATUSLIKE'%CONNECT%';MysqlSHOWSTATUSLIKE'%THREAD%';..2.4...2.4.1.CREATE{DATABASE|SCHEMA}[IFNOTEXISTS]db_name[create_specification[,create_specification]...]create_specification:[DEFAULT]CHARACTERSETcharset_name|[DEFAULT]COLLATEcollation_name:CREATEDATABASEIFNOTEXISTSddd--,.CHARACTERSET'ujis'--COLLATE'ujis_japanese_ci';2.4.2.CREATE[TEMPORARY]TABLE[IFNOTEXISTS]tbl_name[(create_definition,...)][table_options][select_statement]:CREATETABLEifnotexists`Admin_User`(`id`int(11)NOTNULLauto_increment,--PRIMARYKEY`livedoorId`varchar(255)NOTNULLdefault'',`password`varchar(255)NOTNULLdefault'',`auth`int(11)default'0',PRIMARYKEY(`id`)--)ENGINE=MyISAMDEFAULTCHARSET=ujis?ENGINE=MyISAMThebinaryportablestorageenginethatisthedefaultstorageengineusedbyMySQLMyISAM,3FilePurposetbl_name.frmTableformat(definition)filetbl_name.MYDDatafiletbl_name.MYIIndexfileENGINE=InnoDBTransaction-safetableswithrowlockingandforeignkeys.ENGINE=BDBTransaction-safetableswithpagelocking.MEMORYARCHIVEISAM2.4.3.CREATE[UNIQUE|FULLTEXT|SPATIAL]INDEXindex_name[USINGindex_type]ONtbl_name(index_col_name,...)index_col_name:col_name[(length)][ASC|DESC]customernameCREATEINDEXpart_of_nameONcustomer(name(10));MYSQL5.0MyISAM,InnoDB,orBDB,nullMyISAM,InnoDB,orBDB,BLOBTEXTMyISAM,CHAR,VARCHAR,andTEXTFULLTEXTStorageEngineAllowableIndexTypesMyISAMBTREEInnoDBBTREEMEMORY/HEAPHASH,BTREEExample:CREATETABLEtesttable(idINT)ENGINE=MEMORY;CREATEINDEXid_indexUSINGBTREEONtesttable(id);2.4.4.ALTER[IGNORE]TABLEt