sql reference - MySQL 5.1 Reference Manual Including MySQL Cluster NDB 6.X\/7.X Reference Guide Abstract This is the MySQL Reference Manual It documents

sql reference - MySQL 5.1 Reference Manual Including MySQL...

This preview shows page 1 out of 3192 pages.

You've reached the end of your free preview.

Want to read all 3192 pages?

Unformatted text preview: MySQL 5.1 Reference Manual Including MySQL Cluster NDB 6.X/7.X Reference Guide Abstract This is the MySQL™ Reference Manual. It documents MySQL 5.1 through 5.1.73, as well as MySQL Cluster releases based on versions 6.1, 6.2, 6.3, 7.0, and 7.1 of NDBCLUSTER through 5.1.15-ndb-6.1.23, 5.1.51-ndb-6.2.19, 5.1.73ndb-6.3.55, 5.1.73-ndb-7.0.42, and 5.1.73-ndb-7.1.35. End of Product Lifecycle. Active development for MySQL Database Server version 5.1 has ended. Oracle offers various support offerings which may be of interest. For details and more information, see the MySQL section of the Lifetime Support Policy for Oracle Technology Products ( index.html). Please consider upgrading to a recent version. MySQL 5.1 features. This manual describes features that are not included in every edition of MySQL 5.1; such features may not be included in the edition of MySQL 5.1 licensed to you. If you have any questions about the features included in your edition of MySQL 5.1, refer to your MySQL 5.1 license agreement or contact your Oracle sales representative. For notes detailing the changes in each release, see the MySQL 5.1 Release Notes. For legal information, see the Legal Notices. Document generated on: 2015-03-22 (revision: 42242) General Administrators MySQL Enterprise Developers & Functionality Connectors & APIs HA/Scalability Tutorial Installation & Upgrades MySQL Enterprise » MySQL Edition Workbench Connectors and APIs » HA/Scalability Guide Server Administration » MySQL Installer MySQL Enterprise Globalization Monitor Connector/J MySQL Cluster NDB 6.3/7.0/7.1 (GA) SQL Syntax » Security MySQL Enterprise Optimization Backup Connector/ODBC MySQL and DRBD Storage Engines » Startup / Shutdown MySQL Enterprise Functions and Security Operators Connector/Net Server Option / Variable Reference » Backup and Recovery Overview MySQL Enterprise Views and Stored Connector/Python » memcached Audit Programs with MySQL Cluster » Release Notes » MySQL Utilities MySQL Thread Pool memcached Partitioning PHP MySQL and Virtualization » MySQL Version » Linux/Unix Reference Platform Guide Precision Math C API MySQL Proxy FAQs » Windows Platform Guide Information Schema Connector/C Replication » OS X Platform Guide Spatial Extensions Connector/C++ » Solaris Platform Guide Restrictions and Limitations » Building from Source » MySQL for Excel » MySQL Cluster API Developer Table of Contents Preface and Legal Notices ............................................................................................................... xxi 1 General Information ........................................................................................................................ 1 1.1 About This Manual ............................................................................................................... 2 1.2 Typographical and Syntax Conventions ................................................................................. 3 1.3 Overview of the MySQL Database Management System ........................................................ 4 1.3.1 What is MySQL? ....................................................................................................... 4 1.3.2 The Main Features of MySQL .................................................................................... 6 1.3.3 History of MySQL ...................................................................................................... 9 1.4 What Is New in MySQL 5.1 .................................................................................................. 9 1.5 MySQL Development History .............................................................................................. 11 1.6 MySQL Information Sources ............................................................................................... 12 1.6.1 MySQL Mailing Lists ................................................................................................ 12 1.6.2 MySQL Community Support at the MySQL Forums ................................................... 15 1.6.3 MySQL Community Support on Internet Relay Chat (IRC) .......................................... 15 1.6.4 MySQL Enterprise ................................................................................................... 15 1.7 How to Report Bugs or Problems ....................................................................................... 16 1.8 MySQL Standards Compliance ........................................................................................... 20 1.8.1 MySQL Extensions to Standard SQL ........................................................................ 21 1.8.2 MySQL Differences from Standard SQL ................................................................... 24 1.8.3 How MySQL Deals with Constraints ......................................................................... 29 1.9 Credits ............................................................................................................................... 32 1.9.1 Contributors to MySQL ............................................................................................ 32 1.9.2 Documenters and translators ................................................................................... 37 1.9.3 Packages that support MySQL ................................................................................. 38 1.9.4 Tools that were used to create MySQL ..................................................................... 39 1.9.5 Supporters of MySQL .............................................................................................. 39 2 Installing and Upgrading MySQL ................................................................................................... 41 2.1 General Installation Guidance ............................................................................................. 44 2.1.1 Choosing Which MySQL Distribution to Install ........................................................... 44 2.1.2 How to Get MySQL ................................................................................................. 47 2.1.3 Verifying Package Integrity Using MD5 Checksums or GnuPG ................................... 48 2.1.4 Installation Layouts .................................................................................................. 57 2.1.5 Compiler-Specific Build Characteristics ..................................................................... 57 2.2 Installing MySQL on Unix/Linux Using Generic Binaries ....................................................... 57 2.3 Installing MySQL on Microsoft Windows .............................................................................. 60 2.3.1 MySQL Installation Layout on Microsoft Windows ...................................................... 62 2.3.2 Choosing the Installation Package for Microsoft Windows .......................................... 62 2.3.3 MySQL Notifier ........................................................................................................ 65 2.3.4 Installing MySQL on Microsoft Windows Using an MSI Package ................................. 77 2.3.5 Using the MySQL Server Instance Config Wizard ...................................................... 84 2.3.6 Installing MySQL on Microsoft Windows Using a noinstall Zip Archive ....................... 101 2.3.7 Troubleshooting a Microsoft Windows MySQL Server Installation .............................. 110 2.3.8 Windows Postinstallation Procedures ...................................................................... 112 2.3.9 Upgrading MySQL Server on Microsoft Windows ..................................................... 114 2.4 Installing MySQL on OS X ................................................................................................ 116 2.4.1 General Notes on Installing MySQL on OS X .......................................................... 116 2.4.2 Installing MySQL on OS X Using Native Packages .................................................. 117 2.4.3 Installing a MySQL Launch Daemon ....................................................................... 121 2.4.4 Installing the MySQL Startup Item .......................................................................... 122 2.4.5 Installing and Using the MySQL Preference Pane .................................................... 126 2.4.6 Using the Bundled MySQL on OS X Server ............................................................ 128 iii MySQL 5.1 Reference Manual 2.5 Installing MySQL on Linux ................................................................................................ 2.5.1 Installing MySQL on Linux Using RPM Packages .................................................... 2.5.2 Installing MySQL on Linux Using Native Package Managers .................................... 2.6 Installing MySQL on Solaris and OpenSolaris .................................................................... 2.6.1 Installing MySQL on Solaris Using a Solaris PKG .................................................... 2.6.2 Installing MySQL on OpenSolaris Using IPS ........................................................... 2.7 Installing MySQL on IBM AIX ........................................................................................... 2.7.1 General Notes on Installing MySQL on AIX ............................................................. 2.8 Installing MySQL on HP-UX .............................................................................................. 2.8.1 General Notes on Installing MySQL on HP-UX ........................................................ 2.8.2 Installing MySQL on HP-UX Using DEPOT Packages .............................................. 2.9 Installing MySQL on FreeBSD .......................................................................................... 2.10 Installing MySQL on i5/OS .............................................................................................. 2.11 Installing MySQL from Source ......................................................................................... 2.11.1 MySQL Layout for Source Installation ................................................................... 2.11.2 Installing MySQL Using a Standard Source Distribution .......................................... 2.11.3 Installing MySQL Using a Development Source Tree ............................................. 2.11.4 MySQL Source-Configuration Options ................................................................... 2.11.5 Dealing with Problems Compiling MySQL .............................................................. 2.11.6 Compiling and Linking an Optimized mysqld Server ............................................... 2.11.7 Installing MySQL from Source on Windows ........................................................... 2.11.8 Notes on Installing MySQL on Solaris from Source ................................................ 2.11.9 Notes on Installing MySQL on AIX from Source ..................................................... 2.11.10 Notes on Installing MySQL on HP-UX from Source .............................................. 2.12 Postinstallation Setup and Testing ................................................................................... 2.12.1 Initializing the Data Directory ................................................................................ 2.12.2 Starting the Server .............................................................................................. 2.12.3 Testing the Server ............................................................................................... 2.12.4 Securing the Initial MySQL Accounts .................................................................... 2.12.5 Starting and Stopping MySQL Automatically .......................................................... 2.13 Upgrading or Downgrading MySQL ................................................................................. 2.13.1 Upgrading MySQL ............................................................................................... 2.13.2 Downgrading MySQL ........................................................................................... 2.13.3 Checking Whether Tables or Indexes Must Be Rebuilt ........................................... 2.13.4 Rebuilding or Repairing Tables or Indexes ............................................................ 2.13.5 Copying MySQL Databases to Another Machine .................................................... 2.14 Environment Variables .................................................................................................... 2.15 Perl Installation Notes ..................................................................................................... 2.15.1 Installing Perl on Unix .......................................................................................... 2.15.2 Installing ActiveState Perl on Windows ................................................................. 2.15.3 Problems Using the Perl DBI/DBD Interface .......................................................... 3 Tutorial ....................................................................................................................................... 3.1 Connecting to and Disconnecting from the Server .............................................................. 3.2 Entering Queries .............................................................................................................. 3.3 Creating and Using a Database ........................................................................................ 3.3.1 Creating and Selecting a Database ........................................................................ 3.3.2 Creating a Table ................................................................................................... 3.3.3 Loading Data into a Table ...................................................................................... 3.3.4 Retrieving Information from a Table ........................................................................ 3.4 Getting Information About Databases and Tables ............................................................... 3.5 Using mysql in Batch Mode .............................................................................................. 3.6 Examples of Common Queries ......................................................................................... 3.6.1 The Maximum Value for a Column ......................................................................... 3.6.2 The Row Holding the Maximum of a Certain Column ............................................... iv 129 129 133 137 138 139 140 141 141 141 141 142 143 147 148 148 151 153 163 166 167 171 173 174 174 175 179 182 184 188 189 189 202 205 207 208 209 211 211 212 213 215 215 216 219 221 221 223 224 238 239 240 241 241 MySQL 5.1 Reference Manual 3.6.3 Maximum of Column per Group ............................................................................. 3.6.4 The Rows Holding the Group-wise Maximum of a Certain Column ............................ 3.6.5 Using User-Defined Variables ................................................................................ 3.6.6 Using Foreign Keys ............................................................................................... 3.6.7 Searching on Two Keys ......................................................................................... 3.6.8 Calculating Visits Per Day ...................................................................................... 3.6.9 Using AUTO_INCREMENT .................................................................................... 3.7 Using MySQL with Apache ............................................................................................... 4 MySQL Programs ....................................................................................................................... 4.1 Overview of MySQL Programs .......................................................................................... 4.2 Using MySQL Programs ................................................................................................... 4.2.1 Invoking MySQL Programs .................................................................................... 4.2.2 Connecting to the MySQL Server ........................................................................... 4.2.3 Specifying Program Options ................................................................................... 4.2.4 Using Options on the Command Line ..................................................................... 4.2.5 Program Option Modifiers ...................................................................................... 4.2.6 Using Option Files ................................................................................................. 4.2.7 Command-Line Options that Affect Option-File Handling .......................................... 4.2.8 Using Options to Set Program Variables ................................................................. 4.2.9 Option Defaults, Options Expecting Values, and the = Sign ...................................... 4.2.10 Setting Environment Variables .............................................................................. 4.3 MySQL Server and Server-Startup Programs ..................................................................... 4.3.1 mysqld — The MySQL Server .............................................................................. 4.3.2 mysqld_safe — MySQL Server Startup Script ...................................................... 4.3.3 mysql.server — MySQL Server Startup Script .................................................... 4.3.4 mysqld_multi — Manage Multiple MySQL Servers .............................................. 4.4 MySQL Installation-Related Programs ............................................................................... 4.4.1 comp_err — Compile MySQL Error Message File .................................................. 4.4.2 make_win_bin_dist — Package MySQL Distribution as Zip Archive ..................... 4.4.3 mysqlbug — Generate Bug Report ....................................................................... 4.4.4 mysql_fix_privilege_tables — Upgrade MySQL System Tables .................... 4.4.5 mysql_install_db — Initialize MySQL Data Directory ......................................... 4.4.6 mysql_secure_installation — Improve MySQL Installation Security ................ 4.4.7 mysql_tzinfo_to_sql — Load the Time Zone Tables ......................................... 4.4.8 mysql_upgrade — Check and Upgrade MySQL Tables ......................................... 4.5 MySQL Client Programs ................................................................................................... 4.5.1 mysql — The MySQL Command-Line Tool ............................................................ 4.5.2 mysqladmin — Client for Administering a MySQL Server ....................................... 4.5.3 mysqlcheck — A Table Maintenance Program ...................................................... 4.5.4 mysqldump — A Database Backup Program .......................................................... 4.5.5 mysqlimport — A Data Import Program ............................................................... 4.5.6 mysqlshow — Display Database, Table, and Column Information ............................ 4.5.7 mysqlslap — Load Emulation Client .................................................................... 4.6 MySQL Administrative and Utility Programs ....................................................................... 4.6.1 innochecksum — Offline InnoDB File Checksum Utility .......................................... 4.6.2 myisam_ftdump — Display Full-Text Index information .......................................... 4.6.3 ...
View Full Document

  • Spring '13
  • MYSQL , .........

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern

Stuck? We have tutors online 24/7 who can help you get unstuck.
A+ icon
Ask Expert Tutors You can ask You can ask You can ask (will expire )
Answers in as fast as 15 minutes