By George Reese

Cover | Table of Contents

Table of Contents

Chapter 1: MySQL Pocket Reference

Content preview·Buy reprint rights for this chapter

When I fly across the country, I often pass the hours programming on my PowerBook. If that programming involves MySQL, I inevitably end up lugging around the book I co-wrote, Managing and Using MySQL (O'Reilly). I don't carry around the book to show it off; the problem is that no matter how experienced you are with MySQL, you never know when you will need to look up the exact syntax of an obscure function or SQL statement.

The MySQL Pocket Reference is a quick reference that you can take with you anywhere you go. Instead of racking your brain for the exact syntax of a variant of ALTER TABLE that you generally never use, you can reach into your laptop case and grab this reference. As an experienced MySQL architect, administrator, or programmer, you can look to this reference.

This book does not, however, teach MySQL. I expect that you have learned or are in the process of learning MySQL from a book such as Managing and Using MySQL. Though I start with a reference on MySQL installation, it is designed to help you remember the full process of MySQL installation—not to teach you the process.

I first would like to thank my editor Andy Oram, as always, for helping me along. I would also like to thank the book's strong technical reviewers, Paul Dubois, Justen Stepka, and Tim Allwine. Finally, I would like to thank my co-authors for Managing and Using MySQL, Tim King and Randy Jay Yarger, who helped set the foundation that made this pocket reference possible and necessary.

The following conventions are used in this book:

Constant width

Used to indicate anything that might appear in a program, including keywords, function names, SQL commands, and variable names. This font is also used for code examples, output displayed by commands, and system configuration files.

Constant width bold

Used to indicate user input.

Constant width italic

Used to indicate an element (e.g., a filename or variable) that you supply.

Italic

Used to indicate directory names, filenames, program names, Unix commands, and URLs. This font is also used to introduce new terms and for emphasis.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Introduction

Content preview·Buy reprint rights for this chapter

When I fly across the country, I often pass the hours programming on my PowerBook. If that programming involves MySQL, I inevitably end up lugging around the book I co-wrote, Managing and Using MySQL (O'Reilly). I don't carry around the book to show it off; the problem is that no matter how experienced you are with MySQL, you never know when you will need to look up the exact syntax of an obscure function or SQL statement.

The MySQL Pocket Reference is a quick reference that you can take with you anywhere you go. Instead of racking your brain for the exact syntax of a variant of ALTER TABLE that you generally never use, you can reach into your laptop case and grab this reference. As an experienced MySQL architect, administrator, or programmer, you can look to this reference.

This book does not, however, teach MySQL. I expect that you have learned or are in the process of learning MySQL from a book such as Managing and Using MySQL. Though I start with a reference on MySQL installation, it is designed to help you remember the full process of MySQL installation—not to teach you the process.

I first would like to thank my editor Andy Oram, as always, for helping me along. I would also like to thank the book's strong technical reviewers, Paul Dubois, Justen Stepka, and Tim Allwine. Finally, I would like to thank my co-authors for Managing and Using MySQL, Tim King and Randy Jay Yarger, who helped set the foundation that made this pocket reference possible and necessary.

The following conventions are used in this book:

Constant width

Used to indicate anything that might appear in a program, including keywords, function names, SQL commands, and variable names. This font is also used for code examples, output displayed by commands, and system configuration files.

Constant width bold

Used to indicate user input.

Constant width italic

Used to indicate an element (e.g., a filename or variable) that you supply.

Italic

Used to indicate directory names, filenames, program names, Unix commands, and URLs. This font is also used to introduce new terms and for emphasis.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Installation

Content preview·Buy reprint rights for this chapter

You can install MySQL by compiling the source code with the options that best suit your needs, or by downloading and installing a prebuilt binary. In general, you'll want to use the package management system (such as the BSD ports system) appropriate to your operating system. You can also find both binary and source code at the MySQL web site, http://www.mysql.com.

Before installing using either approach, you need to prepare your operating system for MySQL. Specifically, you should create a mysql user and group under which MySQL will run.

Compiling MySQL requires the following steps:

1. Run configure
   Configure comes with a host of options you can specify using the syntax configure --option[=value]. For example, configure --prefix=/usr/local/mysql tells the installer to use /usr/local/mysql as its installation directory.
2. Run make
   This step performs the actual compilation.
3. Run makeinstall
   This step takes the compiled binaries and installs all components of MySQL in their proper locations.
4. Under Unix, make sure directory owners are all in order.

   chown -R root {INSTALL_DIR}
   chgrp -R mysql {INSTALL_DIR}
   chown -R mysql {DATA_DIR}

MySQL has three different kinds of configuration, both for the server process at server startup and for the client processes when a user executes them. In order of preference, these configuration options include:

1. Command-line options
2. Configuration file options
3. Environment variable options

In other words, if you have the password option specified on the command line, in your configuration file, and in an environment variable, the command-line option wins. Table 1-1 shows a list of configuration options. Each option applies to one or more MySQL tools, depending on the context.

Table 1-1: MySQL configuration options

Option	Description
basedir=directory	Specifies the root directory of your MySQL install.
batch	Executes in batch mode, meaning no command-line prompts or other information is sent to stdout. This is the default mode when used with a pipe.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Command-Line Tools

Content preview·Buy reprint rights for this chapter

You can interact with MySQL entirely from the command line. In general, each MySQL command accepts as an argument any appropriate option from the configuration options listed earlier. You prefix any such option with two dashes:

mysql --password=mypass

In addition, each of these options has a short form:

mysql -p mypass

To see which options apply to individual commands and their short forms, refer to the manpage for the command in question:

[23:00:03] george@firenze$ man -M/usr/local/mysql/man 
mysql
MYSQL(1)                                                 
MYSQL(1)
   
NAME
    mysql  -  text-based client for mysqld, a 
              SQL-based relational database daemon
   
SYNOPSIS
    mysql [-B|--batch] [-#|--debug= logfile] 
    [-T|--debug-info] [-e|--exec=  command] [-f|--force]
    [-?|--help] [-h|--host=hostname] [-n|--unbuffered]
    [-p[pwd]] [--password=[pwd]] [-P|--port=  pnum]
    [-q|--quick] [-r|--raw] [-s|--silent]
    [-S|--socket=  snum] [-u|--user=  uname]
    [-v|--verbose] [-V|--version] [-w|--wait]
   
DESCRIPTION
    The mysql program provides a curses-based interface to
    the SQL-based database server daemon, mysqld(1). Full
    fuller documentation, refer to the HTML documents 
    installed with the package.
   
OPTIONS
    -B|--batch
        Print results with a tab as separator, each row on

MySQL provides the following command-line tools:

isamchk

This tool verifies the integrity of your databases and potentially fixes any problems with them. It should be used only on ISAM tables.

msql2mysql

This utility is handy for people converting applications written for mSQL to MySQL. These days, however, few people need this help.

myisamchk

This tool does for MyISAM tables what isamchk does for ISAM tables.

mysql

The MySQL interactive SQL interpreter. It enables you to execute SQL on the command line. You can span your SQL across any number of lines. The tool executes your SQL when you terminate it with a semi-colon or the escape sequence \g.

mysqladmin

The MySQL administrative interface. Though many of this tool's functions can be accomplished using SQL and the mysql command-line utility, it nevertheless provides a quick way to perform an administrative task straight from the Unix command line without entering an SQL interpreter. You can specifically execute the following administrative commands:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Data Types

Content preview·Buy reprint rights for this chapter

For each data type, the syntax shown uses square brackets ([ ]) to indicate optional parts of the syntax. The following example shows how BIGINT is explained in this chapter:

BIGINT[(display_size)]

This indicates that you can use BIGINT alone or with a display size value. The italics indicate that you do not enter display_size literally, but instead enter your own value. Possible uses of BIGINT include:

BIGINT
BIGINT(20)

In addition to the BIGINT type, many other MySQL data types support the specification of a display size. Unless otherwise specified, this value must be an integer between 1 and 255.

In the following cases, MySQL silently changes the column type you specify in your table creation to something else:

VARCHAR -> CHAR

When the specified VARCHAR column size is less than four characters, it is converted to CHAR.

CHAR -> VARCHAR

When a table has at least one column of a variable length, all CHAR columns greater than three characters in length are converted to VARCHAR.

TIMESTAMP display sizes

Display sizes for TIMESTAMP fields must be an even value between 2 and 14. A display size of 0 or greater than 14 will convert the field to a display size of 14. An odd-valued display size will be converted to the next highest even value.

MySQL supports all ANSI SQL2 numeric data types. MySQL numeric types break down into two groups: integer and floating point. Within each group, the types differ by the amount of storage required for them.

Numeric types allow you to specify a display size, which affects the way MySQL displays results. The display size bears no relation to the internal storage provided by each data type. In addition, the floating types allow you to optionally specify the number of digits that follow the decimal point. In such cases, the digits value should be an integer from 0 to 30 that is at most two less than the display size. If you do make the digits value greater than two less than the display size, the display size will automatically change to two more than the digits value. For instance, MySQL automatically changes

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

SQL

Content preview·Buy reprint rights for this chapter

MySQL fully supports ANSI SQL 92, entry level. A SQL reference for MySQL is thus largely a general SQL reference. Nevertheless, MySQL contains some proprietary enhancements that can help you at the mysql command line. This section thus provides a reference for the SQL query language as it is supported in MySQL.

SQL is a kind of controlled English language consisting of verb phrases. Each of these verb phrases begins with an SQL command followed by other SQL keywords, literals, identifiers, or punctuation.

Case-sensitivity in MySQL depends on a variety of factors, including the token in question and the underlying operating system. Table 1-3 shows the case-sensitivity of different SQL tokens in MySQL.

Table 1-3: The case-sensitivity of MySQL.

Token type	Case-sensitivity
Keywords	Case-insensitive.
Identifiers (databases and tables)	Dependent on the case-sensitivity for the underlying OS. On all UNIX systems except Mac OS X using HFS+, database and table names are case-sensitive. On Mac OS X using HFS+ and Windows, they are case-insensitive.
Table aliases	Case-sensitive
Column aliases	Case-insensitive

Literals come in the following varieties:

String

String literals may be enclosed either by single or double quotes. If you wish to be ANSI compatible, you should always use single quotes. Within a string literal, you may represent special characters through escape sequences. An escape sequence is a backslash followed by another character to indicate to MySQL that the second character has a meaning other than its normal meaning. Table 1-4 shows the MySQL escape sequences. Quotes can also be escaped by doubling them up: 'This is a ''quote'''. However, you do not need to double up on single quotes when the string is enclosed by double quotes: "This is a 'quote'".

Table 1-4: MySQL escape sequences

Escape sequence	Value
\0	NUL
\`	Single quote
\"	Double quote
\b	Backspace
\n	Newline
\r	Carriage return
\t	Tab

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Operators

Content preview·Buy reprint rights for this chapter

MySQL offers three kinds of operators: arithmetic, comparison, and logical.

When your SQL contains complex expressions, the subexpressions are evaluated based on MySQL's rules of precedence. Of course, you may always override MySQL's rules of precedence by enclosing an expression in parentheses.

1. BINARY
2. NOT
3. ^
4. - (unary minus)
5. * / %
6. + -
7. << >>
8. &
9. |
10. < <= > >= = <=> <> IN IS LIKE REGEXP
11. BETWEEN CASE
12. AND
13. OR XOR

Arithmetic operators perform basic arithmetic on two values.

+

Adds two numerical values

-

Subtracts two numerical values

*

Multiplies two numerical values

/

Divides two numerical values

%

Gives the modulo of two numerical values

|

Performs a bitwise OR on two integer values

^

Performs a bitwise exclusive OR on two integer values.

&

Performs a bitwise AND on two integer values

<<

Performs a bitwise left shift on an integer value

>>

Performs a bitwise right shift on an integer value

Comparison operators compare values and return 1 if the comparison is true and 0 otherwise. Except for the <=> operator, NULL values cause a comparison operator to evaluate to NULL.

<> or !=

Match rows if the two values are not equal.

<=

Match rows if the left value is less than or equal to the right value.

<

Match rows if the left value is less than the right value.

>=

Match rows if the left value is greater than or equal to the right value.

>

Match rows if the left value is greater than the right value.

value BETWEEN value1 AND value2

Match rows if value is between value1 and value2, or equal to one of them.

value IN (value1,value2,...)

Match rows if value is among the values listed.

value NOT IN (value1, value2,...)

Match rows if value is not among the values listed.

value1 LIKE value2

Compares value1 to value2 and matches the rows if they match. The righthand value can contain the wildcard '%', which matches any number of characters (including 0), and '_', which matches exactly one character. Its most common use is comparing a field value with a literal containing a wildcard (e.g.,

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Functions

Content preview·Buy reprint rights for this chapter

MySQL provides built-in functions that perform special operations.

Aggregate functions operate on a set of data. These are usually used to perform some action on a complete set of returned rows. For example, SELECT AVG(height) FROM kids would return the average of all the values of the height field in the kids table.

AVG(expression)

Returns the average value of the values in expression (e.g., SELECT AVG(score) FROM tests).

BIT_AND(expression)

Returns the bitwise AND aggregate of all the values in expression (e.g., SELECT BIT_AND(flags) FROM options). A bit will be set in the result if and only if the bit is set in every input field.

BIT_OR(expression)

Returns the bitwise OR aggregate of all the values in expression (e.g., SELECT BIT_OR(flags) FROM options). A bit is set in the result if it is set in at least one of the input fields.

COUNT(expression)

Returns the number of times expression was not null. COUNT(*) will return the number of rows with some data in the entire table (e.g., SELECT COUNT(*) FROM folders).

MAX(expression)

Returns the largest value in expression (e.g., SELECT MAX (elevation) FROM mountains).

MIN(expression)

Returns the smallest value in expression (e.g., SELECT MIN(level) FROM toxic_waste).

STD(expression)/STDDEV(expression)

Returns the standard deviation of the values in expression (e.g., SELECT STDDEV(points) FROM data).

SUM(expression)

Returns the sum of the values in expression (e.g., SELECT SUM(calories) FROM daily_diet).

General functions operate on one or more discrete values. We have omitted a few rarely used functions with very specialized applications.

ABS(number)

Returns the absolute value of number (e.g., ABS(-10) returns "10").

ACOS(number)

Returns the inverse cosine of number in radians (e.g., ACOS(0) returns "1.570796").

ADDDATE(date, INTERVAL, amount, type)

Synonym for DATE_ADD.

ASCII(char)

Returns the ASCII value of the given character (e.g., ASCII(h) returns "104").

ASIN(number)

Returns the inverse sine of number in radians (e.g., ASIN(0) returns "0.000000").

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Table Types

Content preview·Buy reprint rights for this chapter

Table 1-5 lists some of the table types supported in most MySQL installations. For truly atomic database transactions, you should use InnoDB tables.

Table 1-5: MySQL table types

Type	Transactional	Description
BDB	Yes	Transaction-safe tables with page locking
Berkeley_db	Yes	Alias for BDB
HEAP	No	Memory-based table; not persistent
ISAM	No	Obsolete format; replaced by MyISAM
InnoDB	Yes	Transaction-safe tables with row locking
MERGE	No	A collection of MyISAM tables merged as a single table
MyISAM	No	A newer, portable table type to replace ISAM

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Return to MySQL Pocket Reference