mysql总结.docx

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