MySQL Tutorial
MySQL Tutorial
Abstract
This is the MySQL Tutorial from the MySQL 5.1 Reference Manual.
Document generated on: 2013-08-07 (revision: 35855)
iii
Table of Contents
Preface and Legal Notices .................................................................................................................. v
1. Tutorial ........................................................................................................................................... 1
2. Connecting to and Disconnecting from the Server ............................................................................ 3
3. Entering Queries ............................................................................................................................ 5
4. Creating and Using a Database ...................................................................................................... 9
4.1. Creating and Selecting a Database .................................................................................... 10
4.2. Creating a Table ................................................................................................................ 11
4.3. Loading Data into a Table .................................................................................................. 12
4.4. Retrieving Information from a Table .................................................................................... 13
4.4.1. Selecting All Data ................................................................................................... 13
4.4.2. Selecting Particular Rows ........................................................................................ 14
4.4.3. Selecting Particular Columns ................................................................................... 15
4.4.4. Sorting Rows .......................................................................................................... 16
4.4.5. Date Calculations .................................................................................................... 17
4.4.6. Working with NULL Values ...................................................................................... 19
4.4.7. Pattern Matching ..................................................................................................... 20
4.4.8. Counting Rows ....................................................................................................... 22
4.4.9. Using More Than one Table .................................................................................... 25
5. Getting Information About Databases and Tables ........................................................................... 27
6. Using mysql in Batch Mode ......................................................................................................... 29
7. Examples of Common Queries ...................................................................................................... 31
7.1. The Maximum Value for a Column ..................................................................................... 31
7.2. The Row Holding the Maximum of a Certain Column ........................................................... 32
7.3. Maximum of Column per Group .......................................................................................... 32
7.4. The Rows Holding the Group-wise Maximum of a Certain Column ........................................ 32
7.5. Using User-Defined Variables ............................................................................................. 33
7.6. Using Foreign Keys ........................................................................................................... 34
7.7. Searching on Two Keys ..................................................................................................... 35
7.8. Calculating Visits Per Day .................................................................................................. 35
7.9. Using AUTO_INCREMENT ................................................................................................... 36
8. Using MySQL with Apache ........................................................................................................... 39
A. Licenses for Third-Party Components ............................................................................................ 41
A.1. Ant-Contrib License ........................................................................................................... 45
A.2. ANTLR 3 License .............................................................................................................. 46
A.3. ANTLR 3.3 License ........................................................................................................... 46
A.4. Boost Library License ........................................................................................................ 47
A.5. Bouncy Castle 1.7 License ................................................................................................ 47
A.6. c3p0 JDBC Library License ............................................................................................... 48
A.7. Django 1.5.1 License ......................................................................................................... 48
A.8. dtoa.c License ................................................................................................................ 48
A.9. Editline Library (libedit) License .................................................................................... 49
A.10. FindGTest.cmake License ............................................................................................ 51
A.11. Fred Fish's Dbug Library License ..................................................................................... 52
A.12. getarg License .............................................................................................................. 53
A.13. GLib License (for MySQL Proxy) ...................................................................................... 53
A.14. GNU General Public License Version 2.0, June 1991 ........................................................ 54
A.15. GNU Lesser General Public License Version 2.1, February 1999 ....................................... 59
A.16. GNU Libtool License ........................................................................................................ 67
A.17. GNU Readline License .................................................................................................... 68
A.18. Google Controlling Master Thread I/O Rate Patch License ................................................. 68
A.19. Google Perftools (TCMalloc utility) License ....................................................................... 69
MySQL Tutorial
iv
A.20. Google SMP Patch License ............................................................................................. 69
A.21. jboss-common-jdbc-wrapper.jar License ............................................................................ 70
A.22. lib_sql.cc License ...................................................................................................... 70
A.23. libevent License .......................................................................................................... 71
A.24. Libiconv License .............................................................................................................. 73
A.25. libintl License ............................................................................................................ 73
A.26. Linux-PAM License .......................................................................................................... 74
A.27. LPeg Library License ....................................................................................................... 74
A.28. Lua (liblua) License ......................................................................................................... 75
A.29. LuaFileSystem Library License ..................................................................................... 75
A.30. md5 (Message-Digest Algorithm 5) License ...................................................................... 76
A.31. memcached License ........................................................................................................ 76
A.32. nt_servc (Windows NT Service class library) License ........................................................ 76
A.33. OpenPAM License ........................................................................................................... 77
A.34. OpenSSL v1.0 License .................................................................................................... 77
A.35. Paramiko License ............................................................................................................ 79
A.36. PCRE License ................................................................................................................. 79
A.37. Percona Multiple I/O Threads Patch License ..................................................................... 80
A.38. RegEX-Spencer Library License ....................................................................................... 81
A.39. RFC 3174 - US Secure Hash Algorithm 1 (SHA1) License ................................................. 81
A.40. Richard A. O'Keefe String Library License ........................................................................ 82
A.41. SHA-1 in C License ......................................................................................................... 82
A.42. Simple Logging Facade for Java (SLF4J) License ............................................................. 82
A.43. zlib License .................................................................................................................. 83
A.44. ZLIB.NET License ........................................................................................................... 83
v
Preface and Legal Notices
This is the MySQL Tutorial from the MySQL 5.1 Reference Manual.
Legal Notices
Copyright © 1997, 2013, Oracle and/or its affiliates. All rights reserved.
This software and related documentation are provided under a license agreement containing restrictions
on use and disclosure and are protected by intellectual property laws. Except as expressly permitted
in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast,
modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any
means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for
interoperability, is prohibited.
The information contained herein is subject to change without notice and is not warranted to be error-free.
If you find any errors, please report them to us in writing.
If this software or related documentation is delivered to the U.S. Government or anyone licensing it on
behalf of the U.S. Government, the following notice is applicable:
U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and
technical data delivered to U.S. Government customers are "commercial computer software" or
"commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-specific
supplemental regulations. As such, the use, duplication, disclosure, modification, and adaptation shall be
subject to the restrictions and license terms set forth in the applicable Government contract, and, to the
extent applicable by the terms of the Government contract, the additional rights set forth in FAR 52.227-19,
Commercial Computer Software License (December 2007). Oracle USA, Inc., 500 Oracle Parkway,
Redwood City, CA 94065.
This software is developed for general use in a variety of information management applications. It is not
developed or intended for use in any inherently dangerous applications, including applications which
may create a risk of personal injury. If you use this software in dangerous applications, then you shall be
responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure the safe
use of this software. Oracle Corporation and its affiliates disclaim any liability for any damages caused by
use of this software in dangerous applications.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. MySQL is a trademark of Oracle
Corporation and/or its affiliates, and shall not be used without Oracle's express written authorization. Other
names may be trademarks of their respective owners.
This software and documentation may provide access to or information on content, products, and services
from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all
warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and
its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of
third-party content, products, or services.
This document in any form, software or printed matter, contains proprietary information that is the exclusive
property of Oracle. Your access to and use of this material is subject to the terms and conditions of your
Oracle Software License and Service Agreement, which has been executed and with which you agree
to comply. This document and information contained herein may not be disclosed, copied, reproduced,
or distributed to anyone outside Oracle without prior written consent of Oracle or as specifically provided
below. This document is not part of your license agreement nor can it be incorporated into any contractual
agreement with Oracle or its subsidiaries or affiliates.
