mysql reference manual

MySQL Reference Manual

MySQL Reference Manual

Table of Contents

Preface ..............................................................................................................xxi

1. General Information ........................................................................................... 1

1.1. About This Manual .................................................................................. 1

1.2. Conventions Used in This Manual .............................................................. 2

1.3. Overview of MySQL AB .......................................................................... 3

1.4. Overview of the MySQL Database Management System ................................ 4

1.5.1. MySQL 4.0 in a Nutshell ...............................................................12

1.5.2. MySQL 4.1 in a Nutshell ...............................................................14

1.5.3. MySQL 5.0: The Next Development Release ....................................15

1.6. MySQL Information Sources ....................................................................15

1.6.1. MySQL Mailing Lists ...................................................................15

1.6.2. MySQL Community Support on IRC (Internet Relay Chat) ..................22

1.6.3. MySQL Community Support at the MySQL Forums ..........................22

1.7. MySQL Standards Compliance .................................................................23

1.7.1. What Standards MySQL Follows ....................................................23

1.7.2. Selecting SQL Modes ...................................................................23

2.1.1. Operating Systems Supported by MySQL .........................................36

2.1.2. Choosing Which MySQL Distribution to Install .................................38

2.1.3. How to Get MySQL .....................................................................49

2.1.4. Verifying Package Integrity Using MD5 Checksums or GnuPG ............49

2.1.5. Installation Layouts ......................................................................52

2.2. Standard MySQL Installation Using a Binary Distribution .............................53

2.3. Installing MySQL on Windows .................................................................54

2.3.1. Windows System Requirements ......................................................54

2.3.2. Choosing An Installation Package ...................................................55

2.3.3. Installing MySQL with the Automated Installer .................................55

2.3.10. Starting the Server for the First Time .............................................66

2.3.11. Starting MySQL from the Windows Command Line .........................67

2.3.12. Starting MySQL as a Windows Service ..........................................68

2.3.13. Testing The MySQL Installation ...................................................70

2.3.14. Troubleshooting a MySQL Installation Under Windows ....................71

2.3.15. Upgrading MySQL on Windows ...................................................72

2.3.16. MySQL on Windows Compared to MySQL on Unix ........................73

2.4. Installing MySQL on Linux ......................................................................76

2.5. Installing MySQL on Mac OS X ...............................................................78

2.6. Installing MySQL on NetWare .................................................................80

2.8.4. Dealing with Problems Compiling MySQL .......................................94

2.8.5. MIT-pthreads Notes .....................................................................97

2.8.6. Installing MySQL from Source on Windows .....................................99

2.8.7. Compiling MySQL Clients on Windows ........................................ 102

2.9. Post-Installation Setup and Testing .......................................................... 102

2.9.1. Windows Post-Installation Procedures ........................................... 103

2.9.2. Unix Post-Installation Procedures ................................................. 104

2.9.3. Securing the Initial MySQL Accounts ............................................114

2.10. Upgrading MySQL ............................................................................. 117

2.10.1. Upgrading from Version 4.1 to 5.0 .............................................. 118

2.10.8. Copying MySQL Databases to Another Machine ........................... 134

2.11. Downgrading MySQL ......................................................................... 135

2.11.1. Downgrading to 4.1 .................................................................. 136

2.11.2. Downgrading to 4.0 .................................................................. 136

2.12. Operating System-Specific Notes ........................................................... 137

2.12.1. Linux Notes ............................................................................ 137

2.12.2. Mac OS X Notes ...................................................................... 144

2.12.3. Solaris Notes ........................................................................... 144

2.12.4. BSD Notes .............................................................................. 148

2.12.5. Other Unix Notes .....................................................................151

3. MySQL Tutorial ............................................................................................. 169

3.1. Connecting to and Disconnecting from the Server ....................................... 169

3.2. Entering Queries .................................................................................. 170

3.3. Creating and Using a Database ................................................................ 173

3.3.1. Creating and Selecting a Database .................................................174

3.3.2. Creating a Table ........................................................................174

3.3.3. Loading Data into a Table ........................................................... 175

3.3.4. Retrieving Information from a Table ............................................. 177

3.4. Getting Information About Databases and Tables ....................................... 190

3.5. Using mysql in Batch Mode ................................................................. 191

3.6.6. Using Foreign Keys ................................................................... 195

3.6.7. Searching on Two Keys .............................................................. 196

3.6.8. Calculating Visits Per Day ........................................................... 197

3.6.9. Using AUTO_INCREMENT .......................................................... 197

3.7. Queries from the Twin Project ................................................................ 199

3.7.1. Find All Non-distributed Twins .................................................... 199

3.7.2. Show a Table of Twin Pair Status ................................................. 201

3.8. Using MySQL with Apache ................................................................... 201

4. Using MySQL Programs .................................................................................. 203

4.1. Overview of MySQL Programs ............................................................... 203