mysql总结.docx
- 文档编号:18622858
- 上传时间:2023-08-20
- 格式:DOCX
- 页数:13
- 大小:20.18KB
mysql总结.docx
《mysql总结.docx》由会员分享,可在线阅读,更多相关《mysql总结.docx(13页珍藏版)》请在冰点文库上搜索。
mysql总结
目录
一、怎样建立mysql服务1
二、关于mysql服务的其它常用命令6
1、怎样去除服务6
2、怎样启动服务及关闭服务7
三、mysql的一些常用命令7
1、怎样连接mysql7
2、怎样修改密码7
3、怎样增加用户7
四、关于数据库方面的一些操作8
首先以root用户进入mysql命令提示符下8
1、显示数据库列表8
2、显示数据库中的数据表8
3、显示数据表的结构8
4、建库8
5、建表8
6、删库和删表9
7、清空纪录和显示纪录9
8、9
一、怎样建立mysql服务
1、在根目录下需要my.ini文件。
内容如下:
#MySQLServerInstanceConfigurationFile
#----------------------------------------------------------------------
#GeneratedbytheMySQLServerInstanceConfigurationWizard
#
#
#InstallationInstructions
#----------------------------------------------------------------------
#
#OnLinuxyoucancopythisfileto/etc/ftosetglobaloptions,
#mysql-data-dir/ftosetserver-specificoptions
#(@localstatedir@forthisinstallation)orto
#~/ftosetuser-specificoptions.
#
#OnWindowsyoushouldkeepthisfileintheinstallationdirectory
#ofyourserver(e.g.C:
\ProgramFiles\MySQL\MySQLServer4.1).To
#makesuretheserverreadstheconfigfileusethestartupoption
#"--defaults-file".
#
#Torunruntheserverfromthecommandline,executethisina
#commandlineshell,e.g.
#mysqld--defaults-file="C:
\ProgramFiles\MySQL\MySQLServer4.1\my.ini"
#
#ToinstalltheserverasaWindowsservicemanually,executethisina
#commandlineshell,e.g.
#mysqld--installMySQL41--defaults-file="C:
\ProgramFiles\MySQL\MySQLServer4.1\my.ini"
#
#Andthenexecutethisinacommandlineshelltostarttheserver,e.g.
#netstartMySQL41
#
#
#Guildlinesforeditingthisfile
#----------------------------------------------------------------------
#
#Inthisfile,youcanusealllongoptionsthattheprogramsupports.
#Ifyouwanttoknowtheoptionsaprogramsupports,starttheprogram
#withthe"--help"option.
#
#Moredetailedinformationabouttheindividualoptionscanalsobe
#foundinthemanual.
#
#
#CLIENTSECTION
#----------------------------------------------------------------------
#
#ThefollowingoptionswillbereadbyMySQLclientapplications.
#NotethatonlyclientapplicationsshippedbyMySQLareguaranteed
#toreadthissection.IfyouwantyourownMySQLclientprogramto
#honorthesevalues,youneedtospecifyitasanoptionduringthe
#MySQLclientlibraryinitialization.
#
[client]
port=3306
[mysql]
default-character-set=gb2312
#SERVERSECTION
#----------------------------------------------------------------------
#
#ThefollowingoptionswillbereadbytheMySQLServer.Makesurethat
#youhaveinstalledtheservercorrectly(seeabove)soitreadsthis
#file.
#
[mysqld]
#TheTCP/IPPorttheMySQLServerwilllistenon
port=3306
#Pathtoinstallationdirectory.Allpathsareusuallyresolvedrelativetothis.
basedir="f:
/dgnxt/mysql/"
#Pathtothedatabaseroot
datadir="f:
/dgnxt/mysql/Data/"
#Thedefaultcharactersetthatwillbeusedwhenanewschemaortableis
#createdandnocharactersetisdefined
default-character-set=gb2312
#Thedefaultstorageenginethatwillbeusedwhencreatenewtableswhen
default-storage-engine=INNODB
#SettheSQLmodetostrict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
#ThemaximumamountofconcurrentsessionstheMySQLserverwill
#allow.Oneoftheseconnectionswillbereservedforauserwith
#SUPERprivilegestoallowtheadministratortologinevenifthe
#connectionlimithasbeenreached.
max_connections=100
#QuerycacheisusedtocacheSELECTresultsandlaterreturnthem
#withoutactualexecutingthesamequeryonceagain.Havingthequery
#cacheenabledmayresultinsignificantspeedimprovements,ifyour
#havealotofidenticalqueriesandrarelychangingtables.Seethe
#"Qcache_lowmem_prunes"statusvariabletocheckifthecurrentvalue
#ishighenoughforyourload.
#Note:
Incaseyourtableschangeveryoftenorifyourqueriesare
#textuallydifferenteverytime,thequerycachemayresultina
#slowdowninsteadofaperformanceimprovement.
query_cache_size=0
#Thenumberofopentablesforallthreads.Increasingthisvalue
#increasesthenumberoffiledescriptorsthatmysqldrequires.
#Thereforeyouhavetomakesuretosettheamountofopenfiles
#allowedtoatleast4096inthevariable"open-files-limit"in
#section[mysqld_safe]
table_cache=256
#Maximumsizeforinternal(in-memory)temporarytables.Ifatable
#growslargerthanthisvalue,itisautomaticallyconvertedtodisk
#basedtableThislimitationisforasingletable.Therecanbemany
#ofthem.
tmp_table_size=5M
#Howmanythreadsweshouldkeepinacacheforreuse.Whenaclient
#disconnects,theclient'sthreadsareputinthecacheiftherearen't
#morethanthread_cache_sizethreadsfrombefore.Thisgreatlyreduces
#theamountofthreadcreationsneededifyouhavealotofnew
#connections.(Normallythisdoesn'tgiveanotableperformance
#improvementifyouhaveagoodthreadimplementation.)
thread_cache_size=8
#***MyISAMSpecificoptions
#ThemaximumsizeofthetemporaryfileMySQLisallowedtousewhile
#recreatingtheindex(duringREPAIR,ALTERTABLEorLOADDATAINFILE.
#Ifthefile-sizewouldbebiggerthanthis,theindexwillbecreated
#throughthekeycache(whichisslower).
myisam_max_sort_file_size=100G
#Ifthetemporaryfileusedforfastindexcreationwouldbebigger
#thanusingthekeycachebytheamountspecifiedhere,thenpreferthe
#keycachemethod.Thisismainlyusedtoforcelongcharacterkeysin
#largetablestousetheslowerkeycachemethodtocreatetheindex.
myisam_max_extra_sort_file_size=100G
#Ifthetemporaryfileusedforfastindexcreationwouldbebigger
#thanusingthekeycachebytheamountspecifiedhere,thenpreferthe
#keycachemethod.Thisismainlyusedtoforcelongcharacterkeysin
#largetablestousetheslowerkeycachemethodtocreatetheindex.
myisam_sort_buffer_size=8M
#SizeoftheKeyBuffer,usedtocacheindexblocksforMyISAMtables.
#Donotsetitlargerthan30%ofyouravailablememory,assomememory
#isalsorequiredbytheOStocacherows.Evenifyou'renotusing
#MyISAMtables,youshouldstillsetitto8-64Masitwillalsobe
#usedforinternaltemporarydisktables.
key_buffer_size=8M
#SizeofthebufferusedfordoingfulltablescansofMyISAMtables.
#Allocatedperthread,ifafullscanisneeded.
read_buffer_size=64K
read_rnd_buffer_size=256K
#ThisbufferisallocatedwhenMySQLneedstorebuildtheindexin
#REPAIR,OPTIMZE,ALTERtablestatementsaswellasinLOADDATAINFILE
#intoanemptytable.Itisallocatedperthreadsobecarefulwith
#largesettings.
sort_buffer_size=212K
#***INNODBSpecificoptions***
#UsethisoptionifyouhaveaMySQLserverwithInnoDBsupportenabled
#butyoudonotplantouseit.Thiswillsavememoryanddiskspace
#andspeedupsomethings.
#skip-innodb
#AdditionalmemorypoolthatisusedbyInnoDBtostoremetadata
#information.IfInnoDBrequiresmorememoryforthispurposeitwill
#starttoallocateitfromtheOS.Asthisisfastenoughonmost
#recentoperatingsystems,younormallydonotneedtochangethis
#value.SHOWINNODBSTATUSwilldisplaythecurrentamountused.
innodb_additional_mem_pool_size=2M
#Ifsetto1,InnoDBwillflush(fsync)thetransactionlogstothe
#diskateachcommit,whichoffersfullACIDbehavior.Ifyouare
#willingtocompromisethissafety,andyouarerunningsmall
#transactions,youmaysetthisto0or2toreducediskI/Otothe
#logs.Value0meansthatthelogisonlywrittentothelogfileand
#thelogfileflushedtodiskapproximatelyoncepersecond.Value2
#meansthelogiswrittentothelogfileateachcommit,butthelog
#fileisonlyflushedtodiskapproximatelyoncepersecond.
innodb_flush_log_at_trx_commit=1
#ThesizeofthebufferInnoDBusesforbufferinglogdata.Assoonas
#itisfull,InnoDBwillhavetoflushittodisk.Asitisflushed
#oncepersecondanyway,itdoesnotmakesensetohaveitverylarge
#(evenwithlongtransactions).
innodb_log_buffer_size=1M
#InnoDB,unlikeMyISAM,usesabufferpooltocachebothindexesand
#rowdata.ThebiggeryousetthisthelessdiskI/Oisneededto
#accessdataintables.Onadedicateddatabaseserveryoumaysetthis
#parameterupto80%ofthemachinephysicalmemorysize.Donotsetit
#toolarge,though,becausecompetitionofthephysicalmemorymay
#causepagingintheoperatingsystem.Notethaton32bitsystemsyou
#mightbelimitedto2-3.5Gofuserlevelmemoryperprocess,sodonot
#setittoohigh.
innodb_buffer_pool_size=8M
#Sizeofeachlogfileinaloggroup.Youshouldsetthecombinedsize
#oflogfilestoabout25%-100%ofyourbufferpoolsizetoavoid
#unneededbufferpoolflushactivityonlogfileoverwrite.However,
#notethatalargerlogfilesizewillincreasethetimeneededforthe
#recoveryprocess.
innodb_log_file_size=10M
#NumberofthreadsallowedinsidetheInnoDBkernel.Theoptimalvalue
#dependshighlyontheapplication,hardwareaswellastheOS
#schedulerproperties.Atoohighvaluemayleadtothreadthrashing.
innodb_thread_concurrency=8
2、需要注意修改的地方:
basedir="f:
/dgnxt/mysql/"
#Pathtothedatabaseroot
datadir="f:
/dgnxt/mysql/Data/"
注意:
basedir与datadir必须修改成相应的绝对路径
3、在dos下进入bin目录下,运行mysqld-nt–install命令即可建立mysql服务。
二、关于mysql服务的其它常用命令
1、怎样去除服务
在dos下进入bin目录,运行mysqld-nt–remove命令即可去处mysql服务。
2、怎样启动服务及关闭服务
首先在dos下进入bin目录
启动:
netstartmysql
停止:
netstopmysql
三、mysql的一些常用命令
1、怎样连接mysql
连接格式:
mysql-h主机地址-u用户名-p用户密码
例子:
1、在dos下进入bin目录
2、mysql>mysql–h192.168.0.11–uroot–p0
2、怎样修改密码
格式:
mysqladmin-u用户名-p旧密码password新密码
例子:
1、在dos下进入bin目录
2、mysql>mysqladmin–uroot–ppass
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- mysql 总结