MySQLBasicsInPicturesS-計算機考試教程

MySQLBasics

InPictures

byKevinJordan

inpictures

MySQLBasicsInPictures

Copyright

?]

mansRt戲刪口

ThisbookisprovidedunderaCreativeCommonslicenseat:

/licenses/by-nc-nd/2.5/

Youarefreetodownload,copy,andsharethiselectronicbookwithothers.

However,itisillegaltosellthisbook,orchangeitinanyway.

Ifyou'dliketosellorchangeit,justcontactusatcontact@.

TrademarksandDisclaimer

Visibooks?isatrademarkofVisibooks,LLC.Allbrandandproductnamesinthisbook

aretrademarksorregisteredtrademarksoftheirrespectivecompanies.

Visibooks?makeseveryefforttoensurethattheinformationinthisbookisaccurate.

However,Visibooks?makesnowarranty,expressedorimplied,withrespecttothe

accuracy,quality,reliability,orfreedomfromerrorofthisdocumentortheproducts

describedinit.Visibooks?makesnorepresentationorwarrantywithrespecttothis

book'scontents,andspecificallydisclaimsanyimpliedwarrantiesorfitnessforany

particularpurpose.Visibooks?disclaimsallliabilityforanydirect,indirect,

consequential,incidental,exemplary,orspecialdamagesresultingfromtheuseofthe

informationinthisdocumentorfromtheuseofanyproductsdescribedinit.Mentionof

anyproductdoesnotconstituteanendorsementofthatproductbyVisibooks?.Data

usedinexamplesareintendedtobefictional.Anyresemblancetorealcompanies,

people,ororganizationsisentirelycoincidental.

ISBN1597061131

TableofContents

GettingStarted1

InstallMySQLonaLinuxcomputer3

StartMySQL13

Createanewdatabase24

Createatable29

Createarecord35

Runaquery38

AdministeringDatabases43

RestartMySQL44

Backupadatabase47

Deleteatable55

Deleteadatabase57

Restoreadatabase58

WorkingwithTables63

Altertables64

Updaterecords67

Deleterecords71

TABLEOFCONTENTS

RunningQueries...73

Sortqueryresults74

Addquerycriteria84

Securingadatabase91

Addalocaluser92

Addaremoteuser95

Removeauser97

Restrictauser98

Web-enablingDatabases101

PerformaqueryusingPERL102

JointwotablesinPERL116

CreateaCGIscript120

WriteaqueryinaCGIscript129

TABLEOFCONTENTS

GettingStarted

Inthissection,youlllearnhowto:

?InstallMySQLonaLinuxcomputer

?StartMySQL

?Createanewdatabase

?Createatable

?Createarecord

?Runaquery

GETTINGSTARTED

WhatisMySQL?

MySQListheworld'smostpopularopen-sourcedatabaseprogram.

MySQLismorelikeMicrosoftSQLServer(aserver-baseddatabase

program)thanAccess(mainlyfordesktopusers).WithMySQL

runningonaserver,youcaneasilyuseitforbusinesssystemsor

database-drivenwebsites.

Easytouseandconfigure,MySQLisalsocapableofindustrial-

strengthapplications.Dependingonthecomputerit'sinstalledon,

MySQLcanholdseveralterabytesofinformationpertable.

2GETTINGSTARTED

InstallMySQLonaLinuxcomputer

1?ObtainacopyofLinux.

Tip:AgoodversionofLinuxtousewiththisbookisLinspire.It's

veryuser-friendly.

Youcanbuyordownloadacopyat:

GETTINGSTARTED3

2.InstallLinspire.

11icon.

3.OntheLauncherBar,clickthe

WhentheSignInscreenappears,typeyouremailaddressand

passwordintheappropriatefields.

MakesureYes,Ihaveanaccountpasswordischecked,then

clickthe3button.

GETTINGSTARTED

Tip:Ifyouneedtocreateanaccount,typeyouremailaddress

intheE-mailAddressbox.

MakesureNo,Ineedtocreateanewaccountischecked,

thendickthe-button.

Fillintheemail,password,andnamefieldsintheAccount

Informationscreen.ThendicktheI_，'_Ibutton.

To向wr?fhMyooraccountistecixvprivMe.>ndconfirm

yourvnud?ndpaiud.thenefitvfyouriun?eandcbckTortmue*

ConhrmAddrvtt

CotifittnrMtwtMd

M-Myhrstname

ListNwrrwMylastname

?As■impzwrttoiMroyeu

.nformrdofrrnijr>0happomnginq^jrf▲ndttfrwctft

▲edbe>rve/ouPte

Chabet.gidontwx>(lo

GETTINGSTARTED

Aftertheaccountiscreated,youneedtoregisterfortheCNR

Service.Intheleftnavigationpane,dickGetMembership.

.?y

?.TAnnouncemenH&NM%

11M.

J>CHR|ImunSo<tw&?]—

一

?<Wdrtbouoe

OSvotchWar0houMi

ON?wArrnrAte

OChckNBuySoftwara

?V

?°Audo&MP3

■C)Bu?nesi<FtnanM

?ODeUtcpEnMncenwn

卜.,-Ganwi

?Educabon

?心Etnet

?O*?ign

?,Soh".O?gkx^M

■QUMe.

?QWobAuthonnq

?AibntpirvCommurvtyForuflB

?OS^rvices

SurfSM，

%WU9S?!?

*

I?(SuvwnlAATWOUDODWI

???

6GETTINGSTARTED

WhentheLinspireShoppingNavigatorscreenappears,dickthe

buttonundertheCNRServiceofyourchoice.

OmtUmhtofMid置■-softwarewwfor

imprrwumthMmakrtt?MytomttifltmuB

Wtthth?CMSmwcyowcmmt?ltM2.000truasoftwMeUtei

directfromtheC0WhouK.*Mh問■>mgkcAci

G(MX.IKC0SEKOQthe?zeMtoLmuxWt?",S<e>rcMthe

tohwwe.1MamtM?cmymrcompare6dnr”dyto2

AndZCMl$mir?giwsRUmor*thanjmione-dteiKC<I%totons<tIrw

Mftwar*

▼x*oget?”?Fut?f??ytoman”，/ar8tr?MHWJT*Ur?r>>1"?

<EEu/WmJ??youcmm35Ew?qroupidwftiia

Completethecheckoutprocess.Youarenowloggedinasa

CNRmember.

GETTINGSTARTED7

5?IntheCNRwindow,type:

MySQL

intheSearchbox.

6.ClicktheQbutton.

8GETTINGSTARTED

7.Whenthesearchresultsappear,scrolldownandclickthe口

iconnexttoMvSQL-Server.

<I<MMut

〉”?OR51fWi0?iIBMO19

1Xf

■■““

MHLlm

??.<DM9M

?31

AM9r?CMWW

OS?MM

MySQLServerisinstalled.

GETTINGSTARTED9

8?ScrollbacktothetopoftheCNRwindow.Type:

Apache

intheSearchbox.

9?Clickthe口button.

10GETTINGSTARTED

10?Whenthesearchresultsappear,clickthe□iconnextto

Apache.

OCWMMTWI

TheApacheWebServerisinstalled.

GETTINGSTARTED11

ClosetheCNRwindow.

12?Restartyourcomputer.

12GETTINGSTARTED

StartMySQL

Clickthe■期Ibutton,thenRunPrograms,thenUtilities,

thenTerminalProgram(Konsole).

"Desktop

.iAtchivmgSZIPTod(Ark)

□QptoordTMH(Kkppef)

OCWR

.FoxSHy(心gntFaa)

FaxV^war(KFAX)

(WcsntlyUN

Cchlr<MCanterFloppyFormatt"(KFioppy)

AmUniaUoaii▲Doaonsnis

JKDESystwnGuE

?Audbo&MP3KkxkAdrrwiTool

KM/OocwwisQBuuneas8Franc*NwtwomCorvwctranStalun

RecentDocufflirtti■O￡>??MapEnhancewwnfi

、FindDoa^nmtsGdnmScr,5iJptur.便詢

ComputBrGHtenei.

S?n>n0B?UMutmadmSDetignr/VPNMana90K

小ktaipCenUM?fWvtUefMTWNl>Ayu>g^n^nlTQ^L

ORunCommand

八TmimnaUApplcafionOWebAuthonng

LJLodeSaoen

?loQoa/TurnCNI

GETTINGSTARTED13

2?WhentheKonsolewindowopens,itshouldlooklikethis:

?--X

￡?t■刖，間冊5ip

71allearn:-1)

*

*

4B

Tip:InLinspire,thepromptisfollowedbya#

Visilearn:?#

asyouseeabove.

#meansyou'regivingcommandsastheRootuser.Thedefault

userinLinspireistheRootuser.

14GETTINGSTARTED

OnotherLinuxdistributionstheterminalpromptisfollowedbya

$.

$meansyou'regivingLinuxcommandsasaregularuser.

Givingthesucommandallowsyoutogivecommandsasthe

“SuperUser,“orRootuser,ofthecomputer.

IfyourterminalpromptisfollowedbyaS,type

su

attheprompt.

ThenpresstheENTERkeyonyourkeyboard.

GETTINGSTARTED15

AtthePasswordprompt,type:

YourRootuserpassword

Notthisparticularstring,ofcourse,buttheactualRootpassword

fortheLinuxcomputer.

ThenpresstheENTERkey.

Noticetheprompthaschangedfrom

[yourusername@localhostyourusername]$

to

[root@localhostyourusername]#

-$ticIIKcntfllr

￡WtD&oUnBrkiScttinyvHelp

youru?Q

?;u

4eUTQra

rooQl◎

There'snowa#attheendoftheprompt.Thismeansyouare

nowgivingcommandsastheRootuser.AstheRootuser,you

canadd/delete/modifyanyfileonthecomputer.

16GETTINGSTARTED

3?Type:

/etc/init.d/mysqlstart

,■?八Kgs”一—X

VisAnlt.d/ayaql*

ThenpressENTER.

Thewindowshouldlooklikethis:

??ifI-一—.

5at?on&MViewBMMMISettrngt

■■:：…、：「,一■Ct

StartingMySQLdatataMserver:ay?qld.

rbockingtorcribbedKy9QLtableamtheZcKground.

*

*Bhe1

ThisstartstheMySQLserver-theprogrammysqlinthe

/etc/init.d/directory.

GETTINGSTARTED17

Tip:IfyouarenotsurewhetherornottheMySQLServeris

running,type:

/etc/init.d/mysqlstatus

Ifit'srunning,thewindowwilllooklikethis:

&M?onSocMoMhi

Vlvlle4rn:-I/etc/in11.d/mysq1

/utr/blfi/ayttqladftlnVer8.40Diatrlb4.O.12*torpc-linuxon1386

Uopyxxqht(CjNU。。HySQLAB&KySOLFinUndAB4TC*XDutmKonfiultAE

ThlJieoftv&ieccaetwithABSOLUTCLYNOWARRANTY.ThK14freesoftware<

Andyouarewelcooetoauxiityandro<]iatribut?ItundertheCPL1icenae

Sarverversion4.0.22-loq

Frotooolversion10

ConnectionLocalhootUNIXcocket

im：xGockwt/VAT/run/myvqld/iwysqld.sock

Uptise:1M1D36MC

ThraAdai1QuoctloneiSlawqueriaai0Opunci14PlimhtAbleas1Op^n

tablM：10Queriespersecond?vg:0.271

710i)oarn:-l|

18GETTINGSTARTED

Tip:IfyouhadtologinastheSuperUserearlier,type:

exit

ThenpressENTER.

Theprompthasnowchangedto:

[yourusername@localhostyourusername]$

LinuxRootprivilegeswereonlyneededtostartMySQL,so

you'veloggedoutastheLinuxcomputer'sSuper(Root)User.

GETTINGSTARTED19

4?Attheprompt,type:

mysql-urootmysql

ThenpressENTER.

Thewindowshouldlooklikethis,withamysql>prompt:

20GETTINGSTARTED

Here'swhatthisstringofcommandsmeans:

?mysql

mysql-urootmysql

ThisfirstmysqlstartstheMySQLclient.

MySQLismadeupoftwoparts:theMySQLserver

programandaMySQLclientprogram.

TheMySQLserverprogramhandlesthestorageofthe

data.

TheMySQLclientprogramallowsyoutogive

commandstotheMySQLserver.

YouneedbothpartstomakeMySQLwork.

?-uroot

mysql-urootmysql

The-ucommandtellstheMySQLclientthatyouwantto

logintotheMySQLserverasaparticularuser,root

denotestherootuseroftheMySQLserver.

You'renotloggingintotheLinuxcomputerastheRoot

user;you'reloggingintotheMySQLserverasits,root

user.ThisgivesyoutotalcontrolovertheMySQLserver.

GETTINGSTARTED21

?mysql

mysql-urootmysql

Thislastmysqlreferstoadatabasecalledmysqlthat

you'lluseinitially.Thisdatabaseisincludedbydefaultin

theMySQLserver.

Thedatabasemysqlhasseveraltables,includingone

thatdescribeswhocanusetheMySQLserver.

5?Type:

SETPASSWORDFOR

root@localhost=PASSWORD('textbookz;

ThenpressENTER.

Thewindowshouldlooklikethis:

Thisstringofcommandssetsthepasswordfortherootuseron

theMySQLservertotextbook.

22GETTINGSTARTED

Tip:BoththeMySQLserverandtheLinuxcomputeritselfcan

haverootuserswhocanadd/detete/modifyanything.The

passwordsforeachareindependent,however.

textbookisnottheRootaccountpasswordofyourLinux

computer.It'stherootpasswordfortheMySQLserver.

Inthepreviousstringofcommands,youloggedintotheMySQL

serverasitsrootuser,sothepasswordtextbookappliestothe

MySQLserver.

Youcannowgivecommandstoadd/detete/modifyanythingin

theMySQLserver,butnottheLinuxcomputeritrunson.

GETTINGSTARTED23

Createanewdatabase

1■Atthemysql>prompt,type:

CREATEDATABASEus_presidents;

ThenpressENTER.

24GETTINGSTARTED

Thewindowshouldlooklikethis:

GETTINGSTARTED25

Tip:Nowthatyou'reloggedintotheMySQLserver,you're

givingMySQLcommands.

UnlikeLinuxcommands,MySQLcommandsneeda

semicolon（；）ontheendtoexecute.

TheCREATEDATABASEcommandcreatedadatabase

calledus_presidentsintheMySQLserver.

Ifeveryoumistakenlyendacommandstringwitha

characterotherthanasemicolon...

CREATEDATABASEus^residents

...thenpressENTER,thereisnowayto"fix"that

command.

JustaddasemicolontothenewUneyouareon:

CREATEDATBASEus_presidents

Ifthecommandisvalid,itwillexecute.

Iftherewasanerrorinthecommandstringandit'sinvalid,

addingasemicolonherewillexecuteitandMySQLwill

giveanerror.

26GETTINGSTARTED

2?Type:

SHOWDATABASES;

thenpressENTER.

Thewindowshouldlooklikethis:

ThisshowsthedatabasesonyourMySQLserver:mysql,test,

andus_presidents.

ThemysqldatabaseisusedbytheMySQLservertostore

informationaboutusers,permissions,etc.

ThetestdatabaseisoftenusedasaworkplaceforMySQL

userstotestandtrythings-thisisusefulinaworkenvironment

wheremanypeopleareworkingwithcriticalinformation.

GETTINGSTARTED27

Tip:MySQLcommandsdon'thavetoZ?eUPPER-CASE.

Inthisbook,commandsareputinUPPER-CASEtomakethem

easiertodistinguish.

Ifyou'dtypedthecommandinlower-case:

showdatabases;

thatwouldhavebeenfine.

28GETTINGSTARTED

Createatable

Type:

USEus_presidents;

thenpressENTER.

Thewindowshouldlooklikethis:

TheUSEcommandallowsyoutostartusingthedatabase

GETTINGSTARTED29

Displayingtext

Sometimesastringofcommandsistoowidetofitonthepagesof

thisbook.Inthosecases,anarrowisaddedthattellsyoutocontinue

typinginthesameline.

Forinstance,thiscommand:

rpm-iMySQL-3.23.51-1.i386.rpmMySQL-client-

3.23.51-1.i386.rpm

couldbedisplayedthisway:

rpm-iMySQL-3.23.51-1.i386.rpm??

MySQL-client-3.23.51-1.i386.rpm

30GETTINGSTARTED

2?Type:

CREATETABLEnameAA

(idINTNOTNULLPRIMARYKEYA

AUTO_INCREMENTz??

firstCHAR(25),lastCHAR(25));

thenpressENTER.

Thewindowshouldlooklikethis:

SratcmEtMVuwvBOOMUNHISMtwtQi

nyeql>CRBATBDATABASEutapresidentc：

6wry1rowattccted(U.U7aec)

nycql>SHOWM7ABASS0;

OatAbaM

I

I

|u*_pcealdenia|

USBus_pr^Bidont?;

Mt&Zxchanged

CUAnTABLK30(idHITWOTHULLPRIMARYKXYMITO」NCRBHEUT.fit

Btl“tCHAR(2SH；

OucryOK.0tew?ffeeted(0.06?cc)

Bysql>，

ThisstringofcommandsisusedtoCREATEaTABLEcalled

namewiththreefields:id,first,andlast.

GETTINGSTARTED31

Herearethedatatypesandpropertiesforthesefields:

?INT

CREATETABLEname

(idINTNOTNULLPRIMARYKEY

AUTO_工NCREMENT,

firstCHAR(25),lastCHAR(25));

TheINTdatatypefortheidfieldensuresitwillcontain

onlyintegers—numbers,nottext.

?NOTNULL

CREATETABLEname

(idINTNOTNULLPRIMARYKEY

AUTO_INCREMENT,

firstCHAR(25),lastCHAR(25));

TheNOTNULLpropertyensurestheidfieldcannotbe

leftblank.

32GETTINGSTARTED

?PRIMARYKEY

CREATETABLEname

(idINTNOTNULLPRIMARYKEY

AUTO_INCREMENT,

firstCHAR(25),lastCHAR(25));

ThePRIMARYKEYpropertymakesidthekeyfieldin

thetable.

Inanydatabasetable,onefieldshouldbethekeyfield一

afieldthatcancontainnoduplicates.Inthistable,name,

theidfieldisthekeyfieldbecauseitcontainsthe

PRIMARYKEYproperty.

Thismeansthenametablecan'thavetworecordswith

anidof35.

?AUTO_INCREMENT

CREATETABLEname

(idINTNOTNULLPRIMARYKEY

AUTO_INCREMENT,

firs~CHAR(25),lastCHAR(25));

TheAUTO_INCREMENTpropertyautomaticallyassignsa

valuetotheidfield,increasingthepreviousidnumber

byoneforeachnewfield.

ThisensuresthattheNOTNULL(can'tbeblank)andthe

PRIMARYKEY(can'thaveduplicates)propertiesofthe

idfieldarebothsatisfied.

GETTINGSTARTED33

?CHAR

CREATETABLEname

(idINTNOTNULLPRIMARYKEY

AUTO_工NCREMENT,

firstCHAR(25),lastCHAR(25));

TheCHARdatatypeforthefirstandlastfieldslimitsthe

lengthofentriesto25characterseach.

Intheus_jjresidentsdatabase,you'vecreateda

tablecallednamethafsorganizedlikethis:

FieldDatatypeProperties

primarykey,notnull,

idINT

autoincrement

firstCHAR(25)

lastCHAR(25)

34GETTINGSTARTED

Createarecord

1.Type:

INSERTINTOname(id,first,last)?>

VALUES(NULL,'George','Washington');

thenpressENTER.

Thewindowshouldlooklikethis:

Thiscommandstringcreatesthefirstrecordinthetablename.It

readsmuchlikeasentence:

INSERTINTOthetablename(whichhasthefieldsid,first,

andlast)thecorrespondingVALUESNULL,George,and

Washington.

GETTINGSTARTED35

Sincetheidfieldcan'tbeblank(ithasaNOTNULLproperty),

puttingaNULLvalueinitforcesMySQLtoautomaticallynumber

therecord(becausetheidfieldalsohastheproperty

AUTO_INCREMENT).

Thedatainthetablenameisnoworganizedlikethis:

Fields:idfirstlast

Record:1GeorgeWashington

Tip:TextisenclosedwithinsinglequotestoletMySQLknow

thatit'sjusttext,notacommand.

Ifthephrase

'Whatisthefirstnameofthepresidentnamed

Washingtonwhosevalueskepthimfromcutting

downthecherrytree?’

wasnotenclosedinsinglequotes,MySQLmightinterpretthe

wordsnameacdvaluesascommands,andgetconfused.

Intheseexamples,single-quotesareused.Double-quotes

performthesamefunction.

36GETTINGSTARTED

2?Type:

INSERTINTOname(id,first,last)AA

VALUES??

(NULL,'John','Adams'),AA

(NULL,'Thomas',*Jefferson'),?

(NULL,*James*,'Madison');

thenpressENTER.

Thisaddsthreerecordstothetablename：onerecordeachfor

presidentsJohnAdams,ThomasJefferson,andJamesMadison.

Thedatainthetablenamearenoworganizedlikethis:

Fields:idfirstlast

Records:1GeorgeWashington

2JohnAdams