MySQL Tutorial

MySQL Tutorial
MySQL Tutorial
Abstract
This is the MySQL Tutorial from the MySQL 5.1 Reference Manual.
Document generated on: 2014-12-08 (revision: 40968)
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 .................................................................................................... 14
4.4.2 Selecting Particular Rows ......................................................................................... 14
4.4.3 Selecting Particular Columns .................................................................................... 15
4.4.4 Sorting Rows ........................................................................................................... 17
4.4.5 Date Calculations ..................................................................................................... 18
4.4.6 Working with NULL Values ....................................................................................... 20
4.4.7 Pattern Matching ...................................................................................................... 21
4.4.8 Counting Rows ........................................................................................................ 23
4.4.9 Using More Than one Table ..................................................................................... 25
5 Getting Information About Databases and Tables ............................................................................ 29

6 Using mysql in Batch Mode ........................................................................................................... 31
7 Examples of Common Queries ....................................................................................................... 33
7.1 The Maximum Value for a Column ...................................................................................... 33
7.2 The Row Holding the Maximum of a Certain Column ............................................................ 34
7.3 Maximum of Column per Group .......................................................................................... 34
7.4 The Rows Holding the Group-wise Maximum of a Certain Column ........................................ 34
7.5 Using User-Defined Variables .............................................................................................. 35
7.6 Using Foreign Keys ............................................................................................................ 36
7.7 Searching on Two Keys ...................................................................................................... 37
7.8 Calculating Visits Per Day ................................................................................................... 37
7.9 Using AUTO_INCREMENT ................................................................................................. 38
8 Using MySQL with Apache ............................................................................................................ 41
A Licenses for Third-Party Components ............................................................................................ 43
A.1 ANTLR 3 License ............................................................................................................... 45
A.2 dtoa.c License .................................................................................................................... 45
A.3 Editline Library (libedit) License ........................................................................................... 46
A.4 FindGTest.cmake License ................................................................................................... 48
A.5 Fred Fish's Dbug Library License ........................................................................................ 49
A.6 getarg License ................................................................................................................... 50
A.7 GNU General Public License Version 2.0, June 1991 ........................................................... 50
A.8 GNU Lesser General Public License Version 2.1, February 1999 .......................................... 56
A.9 GNU Libtool License .......................................................................................................... 64
A.10 GNU Readline License ..................................................................................................... 64
A.11 Google Controlling Master Thread I/O Rate Patch License .................................................. 65
A.12 Google Perftools (TCMalloc utility) License ........................................................................ 65
A.13 Google SMP Patch License .............................................................................................. 66
A.14 lib_sql.cc License ............................................................................................................. 67
A.15 libevent License ................................................................................................................ 67
A.16 Linux-PAM License ........................................................................................................... 69
A.17 md5 (Message-Digest Algorithm 5) License ....................................................................... 70
A.18 memcached License ......................................................................................................... 70
A.19 nt_servc (Windows NT Service class library) License ......................................................... 71
MySQL Tutorial
iv
A.20 OpenPAM License ............................................................................................................ 71
A.21 Paramiko License ............................................................................................................. 71
A.22 Percona Multiple I/O Threads Patch License ...................................................................... 72
A.23 RegEX-Spencer Library License ........................................................................................ 72
A.24 RFC 3174 - US Secure Hash Algorithm 1 (SHA1) License .................................................. 73
A.25 Richard A. O'Keefe String Library License ......................................................................... 73
A.26 SHA-1 in C License .......................................................................................................... 74
A.27 zlib License ...................................................................................................................... 74
v
Preface and Legal Notices
This is the MySQL Tutorial from the MySQL 5.1 Reference Manual.
Legal Notices
Copyright © 1997, 2014, 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.
Legal Notices
vi
This documentation is NOT distributed under a GPL license. Use of this documentation is subject to the
following terms:
You may create a printed copy of this documentation solely for your own personal use. Conversion to other
formats is allowed as long as the actual content is not altered or edited in any way. You shall not publish
or distribute this documentation in any form or on any media, except if you distribute the documentation in
a manner similar to how Oracle disseminates it (that is, electronically for download on a Web site with the
software) or on a CD-ROM or similar medium, provided however that the documentation is disseminated
together with the software on the same medium. Any other use, such as any dissemination of printed
copies or use of this documentation, in whole or in part, in another publication, requires the prior written
consent from an authorized representative of Oracle. Oracle and/or its affiliates reserve any and all rights
to this documentation not expressly granted above.
For more information on the terms of this license, or for details on how the MySQL documentation is built
and produced, please visit MySQL Contact & Questions.
For additional licensing information, including licenses for third-party libraries used by MySQL products,
see Preface and Legal Notices.
For help with using MySQL, please visit either the MySQL Forums or MySQL Mailing Lists where you can
discuss your issues with other MySQL users.
For additional documentation on MySQL products, including translations of the documentation into other
languages, and downloadable versions in variety of formats, including HTML and PDF formats, see the
MySQL Documentation Library.
1
Chapter 1 Tutorial
This chapter provides a tutorial introduction to MySQL by showing how to use the mysql client program
to create and use a simple database. mysql (sometimes referred to as the “terminal monitor” or just
“monitor”) is an interactive program that enables you to connect to a MySQL server, run queries, and view
the results. mysql may also be used in batch mode: you place your queries in a file beforehand, then tell
mysql to execute the contents of the file. Both ways of using mysql are covered here.
To see a list of options provided by mysql, invoke it with the --help option:
shell> mysql --help
This chapter assumes that mysql is installed on your machine and that a MySQL server is available to
which you can connect. If this is not true, contact your MySQL administrator. (If you are the administrator,
you need to consult the relevant portions of this manual, such as MySQL Server Administration.)
This chapter describes the entire process of setting up and using a database. If you are interested only in
accessing an existing database, you may want to skip over the sections that describe how to create the
database and the tables it contains.
Because this chapter is tutorial in nature, many details are necessarily omitted. Consult the relevant
sections of the manual for more information on the topics covered here.
2
3
Chapter 2 Connecting to and Disconnecting from the Server
To connect to the server, you will usually need to provide a MySQL user name when you invoke mysql
and, most likely, a password. If the server runs on a machine other than the one where you log in, you will
also need to specify a host name. Contact your administrator to find out what connection parameters you
should use to connect (that is, what host, user name, and password to use). Once you know the proper
parameters, you should be able to connect like this:
shell> mysql -h host -u user -p
Enter password: ********
host and user represent the host name where your MySQL server is running and the user name of your
MySQL account. Substitute appropriate values for your setup. The ******** represents your password;
enter it when mysql displays the Enter password: prompt.
If that works, you should see some introductory information followed by a mysql> prompt:
shell> mysql -h host -u user -p
Enter password: ********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 25338 to server version: 5.1.73-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
The mysql> prompt tells you that mysql is ready for you to enter commands.
If you are logging in on the same machine that MySQL is running on, you can omit the host, and simply
use the following:
shell> mysql -u user -p
If, when you attempt to log in, you get an error message such as ERROR 2002 (HY000): Can't
connect to local MySQL server through socket '/tmp/mysql.sock' (2), it means that
the MySQL server daemon (Unix) or service (Windows) is not running. Consult the administrator or see the
section of Installing and Upgrading MySQL that is appropriate to your operating system.
For help with other problems often encountered when trying to log in, see Common Errors When Using
MySQL Programs.
Some MySQL installations permit users to connect as the anonymous (unnamed) user to the server
running on the local host. If this is the case on your machine, you should be able to connect to that server
by invoking mysql without any options:
shell> mysql
After you have connected successfully, you can disconnect any time by typing QUIT (or \q) at the mysql>
prompt:
mysql> QUIT
Bye
On Unix, you can also disconnect by pressing Control+D.
Most examples in the following sections assume that you are connected to the server. They indicate this by
the mysql> prompt.
4
5
Chapter 3 Entering Queries
Make sure that you are connected to the server, as discussed in the previous section. Doing so does not in
itself select any database to work with, but that is okay. At this point, it is more important to find out a little
about how to issue queries than to jump right in creating tables, loading data into them, and retrieving data
from them. This section describes the basic principles of entering commands, using several queries you
can try out to familiarize yourself with how mysql works.
Here is a simple command that asks the server to tell you its version number and the current date. Type it
in as shown here following the mysql> prompt and press Enter:
mysql> SELECT VERSION(), CURRENT_DATE;
+-----------------+--------------+
| VERSION() | CURRENT_DATE |
+-----------------+--------------+
| 5.1.2-alpha-log | 2005-10-11 |
+-----------------+--------------+
1 row in set (0.01 sec)
mysql>
This query illustrates several things about mysql:
• A command normally consists of an SQL statement followed by a semicolon. (There are some
exceptions where a semicolon may be omitted. QUIT, mentioned earlier, is one of them. We'll get to
others later.)
• When you issue a command, mysql sends it to the server for execution and displays the results, then
prints another mysql> prompt to indicate that it is ready for another command.
• mysql displays query output in tabular form (rows and columns). The first row contains labels for
the columns. The rows following are the query results. Normally, column labels are the names of the
columns you fetch from database tables. If you're retrieving the value of an expression rather than a
table column (as in the example just shown), mysql labels the column using the expression itself.
• mysql shows how many rows were returned and how long the query took to execute, which gives you
a rough idea of server performance. These values are imprecise because they represent wall clock time
(not CPU or machine time), and because they are affected by factors such as server load and network
latency. (For brevity, the “rows in set” line is sometimes not shown in the remaining examples in this
chapter.)
Keywords may be entered in any lettercase. The following queries are equivalent:
mysql> SELECT VERSION(), CURRENT_DATE;
mysql> select version(), current_date;
mysql> SeLeCt vErSiOn(), current_DATE;
Here is another query. It demonstrates that you can use mysql as a simple calculator:
mysql> SELECT SIN(PI()/4), (4+1)*5;
+------------------+---------+
| SIN(PI()/4) | (4+1)*5 |
+------------------+---------+
| 0.70710678118655 | 25 |
+------------------+---------+
1 row in set (0.02 sec)
The queries shown thus far have been relatively short, single-line statements. You can even enter multiple
statements on a single line. Just end each one with a semicolon:
6
mysql> SELECT VERSION(); SELECT NOW();
+-----------------+
| VERSION() |
+-----------------+
| 5.1.2-alpha-log |
+-----------------+
1 row in set (0.00 sec)
+---------------------+
| NOW() |
+---------------------+
| 2005-10-11 15:15:00 |
+---------------------+
1 row in set (0.00 sec)
A command need not be given all on a single line, so lengthy commands that require several lines are not
a problem. mysql determines where your statement ends by looking for the terminating semicolon, not by
looking for the end of the input line. (In other words, mysql accepts free-format input: it collects input lines
but does not execute them until it sees the semicolon.)
Here is a simple multiple-line statement:
mysql> SELECT
-> USER()
-> ,
-> CURRENT_DATE;
+---------------+--------------+
| USER() | CURRENT_DATE |
+---------------+--------------+
| jon@localhost | 2005-10-11 |
+---------------+--------------+
In this example, notice how the prompt changes from mysql> to -> after you enter the first line of a
multiple-line query. This is how mysql indicates that it has not yet seen a complete statement and is
waiting for the rest. The prompt is your friend, because it provides valuable feedback. If you use that
feedback, you can always be aware of what mysql is waiting for.
If you decide you do not want to execute a command that you are in the process of entering, cancel it by
typing \c:
mysql> SELECT
-> USER()
-> \c
mysql>
Here, too, notice the prompt. It switches back to mysql> after you type \c, providing feedback to indicate
that mysql is ready for a new command.
The following table shows each of the prompts you may see and summarizes what they mean about the
state that mysql is in.
Prompt Meaning
mysql> Ready for new command.
-> Waiting for next line of multiple-line command.
'> Waiting for next line, waiting for completion of a string that began with a single quote (“'”).
"> Waiting for next line, waiting for completion of a string that began with a double quote (“"”).
`> Waiting for next line, waiting for completion of an identifier that began with a backtick (“`”).
7
Prompt Meaning
/*> Waiting for next line, waiting for completion of a comment that began with /*.
Multiple-line statements commonly occur by accident when you intend to issue a command on a single
line, but forget the terminating semicolon. In this case, mysql waits for more input:
mysql> SELECT USER()
->
If this happens to you (you think you've entered a statement but the only response is a -> prompt), most
likely mysql is waiting for the semicolon. If you don't notice what the prompt is telling you, you might sit
there for a while before realizing what you need to do. Enter a semicolon to complete the statement, and
mysql executes it:
mysql> SELECT USER()
-> ;
+---------------+
| USER() |
+---------------+
| jon@localhost |
+---------------+
The '> and "> prompts occur during string collection (another way of saying that MySQL is waiting for
completion of a string). In MySQL, you can write strings surrounded by either “'” or “"” characters (for
example, 'hello' or "goodbye"), and mysql lets you enter strings that span multiple lines. When you
see a '> or "> prompt, it means that you have entered a line containing a string that begins with a “'”
or “"” quote character, but have not yet entered the matching quote that terminates the string. This often
indicates that you have inadvertently left out a quote character. For example:
mysql> SELECT * FROM my_table WHERE name = 'Smith AND age < 30;
'>
If you enter this SELECT statement, then press Enter and wait for the result, nothing happens. Instead
of wondering why this query takes so long, notice the clue provided by the '> prompt. It tells you that
mysql expects to see the rest of an unterminated string. (Do you see the error in the statement? The string
'Smith is missing the second single quotation mark.)
At this point, what do you do? The simplest thing is to cancel the command. However, you cannot just
type \c in this case, because mysql interprets it as part of the string that it is collecting. Instead, enter the
closing quote character (so mysql knows you've finished the string), then type \c:
mysql> SELECT * FROM my_table WHERE name = 'Smith AND age < 30;
'> '\c
mysql>
The prompt changes back to mysql>, indicating that mysql is ready for a new command.
The `> prompt is similar to the '> and "> prompts, but indicates that you have begun but not completed a
backtick-quoted identifier.
It is important to know what the '>, ">, and `> prompts signify, because if you mistakenly enter an
unterminated string, any further lines you type appear to be ignored by mysql—including a line containing
QUIT. This can be quite confusing, especially if you do not know that you need to supply the terminating
quote before you can cancel the current command.
8
9
Chapter 4 Creating and Using a Database
Table of Contents
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 ............................................................................................................ 14
4.4.2 Selecting Particular Rows ................................................................................................. 14
4.4.3 Selecting Particular Columns ............................................................................................ 15
4.4.4 Sorting Rows ................................................................................................................... 17
4.4.5 Date Calculations ............................................................................................................. 18
4.4.6 Working with NULL Values ............................................................................................... 20
4.4.7 Pattern Matching .............................................................................................................. 21
4.4.8 Counting Rows ................................................................................................................ 23
4.4.9 Using More Than one Table ............................................................................................. 25
Once you know how to enter commands, you are ready to access a database.
Suppose that you have several pets in your home (your menagerie) and you would like to keep track of
various types of information about them. You can do so by creating tables to hold your data and loading
them with the desired information. Then you can answer different sorts of questions about your animals by
retrieving data from the tables. This section shows you how to perform the following operations:
• Create a database
• Create a table
• Load data into the table
• Retrieve data from the table in various ways
• Use multiple tables
The menagerie database is simple (deliberately), but it is not difficult to think of real-world situations
in which a similar type of database might be used. For example, a database like this could be used by
a farmer to keep track of livestock, or by a veterinarian to keep track of patient records. A menagerie
distribution containing some of the queries and sample data used in the following sections can be
obtained from the MySQL Web site. It is available in both compressed tar file and Zip formats at http://
dev.mysql.com/doc/.
Use the SHOW statement to find out what databases currently exist on the server:
mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| mysql |
| test |
| tmp |
+----------+
Creating and Selecting a Database
10
The mysql database describes user access privileges. The test database often is available as a
workspace for users to try things out.
The list of databases displayed by the statement may be different on your machine; SHOW DATABASES
does not show databases that you have no privileges for if you do not have the SHOW DATABASES
privilege. See SHOW DATABASES Syntax.
If the test database exists, try to access it:
mysql> USE test
Database changed
USE, like QUIT, does not require a semicolon. (You can terminate such statements with a semicolon if you
like; it does no harm.) The USE statement is special in another way, too: it must be given on a single line.
You can use the test database (if you have access to it) for the examples that follow, but anything you
create in that database can be removed by anyone else with access to it. For this reason, you should
probably ask your MySQL administrator for permission to use a database of your own. Suppose that you
want to call yours menagerie. The administrator needs to execute a command like this:
mysql> GRANT ALL ON menagerie.* TO 'your_mysql_name'@'your_client_host';
where your_mysql_name is the MySQL user name assigned to you and your_client_host is the host
from which you connect to the server.
4.1 Creating and Selecting a Database
If the administrator creates your database for you when setting up your permissions, you can begin using
it. Otherwise, you need to create it yourself:
mysql> CREATE DATABASE menagerie;
Under Unix, database names are case sensitive (unlike SQL keywords), so you must always refer to
your database as menagerie, not as Menagerie, MENAGERIE, or some other variant. This is also true
for table names. (Under Windows, this restriction does not apply, although you must refer to databases
and tables using the same lettercase throughout a given query. However, for a variety of reasons, the
recommended best practice is always to use the same lettercase that was used when the database was
created.)
Note
If you get an error such as ERROR 1044 (42000): Access denied for user
'monty'@'localhost' to database 'menagerie' when attempting to
create a database, this means that your user account does not have the necessary
privileges to do so. Discuss this with the administrator or see The MySQL Access
Privilege System.
Creating a database does not select it for use; you must do that explicitly. To make menagerie the current
database, use this command:
mysql> USE menagerie
Database changed
Your database needs to be created only once, but you must select it for use each time you begin a mysql
session. You can do this by issuing a USE statement as shown in the example. Alternatively, you can select
Creating a Table
11
the database on the command line when you invoke mysql. Just specify its name after any connection
parameters that you might need to provide. For example:
shell> mysql -h host -u user -p menagerie
Enter password: ********
Important
menagerie in the command just shown is not your password. If you want to supply
your password on the command line after the -p option, you must do so with no
intervening space (for example, as -pmypassword, not as -p mypassword).
However, putting your password on the command line is not recommended,
because doing so exposes it to snooping by other users logged in on your machine.
Note
You can see at any time which database is currently selected using SELECT
DATABASE().
4.2 Creating a Table
Creating the database is the easy part, but at this point it is empty, as SHOW TABLES tells you:
mysql> SHOW TABLES;
Empty set (0.00 sec)
The harder part is deciding what the structure of your database should be: what tables you need and what
columns should be in each of them.
You want a table that contains a record for each of your pets. This can be called the pet table, and
it should contain, as a bare minimum, each animal's name. Because the name by itself is not very
interesting, the table should contain other information. For example, if more than one person in your
family keeps pets, you might want to list each animal's owner. You might also want to record some basic
descriptive information such as species and sex.
How about age? That might be of interest, but it is not a good thing to store in a database. Age changes
as time passes, which means you'd have to update your records often. Instead, it is better to store a fixed
value such as date of birth. Then, whenever you need age, you can calculate it as the difference between
the current date and the birth date. MySQL provides functions for doing date arithmetic, so this is not
difficult. Storing birth date rather than age has other advantages, too:
• You can use the database for tasks such as generating reminders for upcoming pet birthdays. (If you
think this type of query is somewhat silly, note that it is the same question you might ask in the context
of a business database to identify clients to whom you need to send out birthday greetings in the current
week or month, for that computer-assisted personal touch.)
• You can calculate age in relation to dates other than the current date. For example, if you store death
date in the database, you can easily calculate how old a pet was when it died.
You can probably think of other types of information that would be useful in the pet table, but the ones
identified so far are sufficient: name, owner, species, sex, birth, and death.
Use a CREATE TABLE statement to specify the layout of your table:
Loading Data into a Table
12
mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
-> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
VARCHAR is a good choice for the name, owner, and species columns because the column values vary
in length. The lengths in those column definitions need not all be the same, and need not be 20. You can
normally pick any length from 1 to 65535, whatever seems most reasonable to you. If you make a poor
choice and it turns out later that you need a longer field, MySQL provides an ALTER TABLE statement.
Several types of values can be chosen to represent sex in animal records, such as 'm' and 'f', or
perhaps 'male' and 'female'. It is simplest to use the single characters 'm' and 'f'.
The use of the DATE data type for the birth and death columns is a fairly obvious choice.
Once you have created a table, SHOW TABLES should produce some output:
mysql> SHOW TABLES;
+---------------------+
| Tables in menagerie |
+---------------------+
| pet |
+---------------------+
To verify that your table was created the way you expected, use a DESCRIBE statement:
mysql> DESCRIBE pet;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| owner | varchar(20) | YES | | NULL | |
| species | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birth | date | YES | | NULL | |
| death | date | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
You can use DESCRIBE any time, for example, if you forget the names of the columns in your table or what
types they have.
For more information about MySQL data types, see Data Types.
4.3 Loading Data into a Table
After creating your table, you need to populate it. The LOAD DATA and INSERT statements are useful for
this.
Suppose that your pet records can be described as shown here. (Observe that MySQL expects dates in
'YYYY-MM-DD' format; this may be different from what you are used to.)
name owner species sex birth death
Fluffy Harold cat f 1993-02-04
Claws Gwen cat m 1994-03-17
Buffy Harold dog f 1989-05-13
Fang Benny dog m 1990-08-27
Bowser Diane dog m 1979-08-31 1995-07-29
Retrieving Information from a Table
13
name owner species sex birth death
Chirpy Gwen bird f 1998-09-11
Whistler Gwen bird 1997-12-09
Slim Benny snake m 1996-04-29
Because you are beginning with an empty table, an easy way to populate it is to create a text file
containing a row for each of your animals, then load the contents of the file into the table with a single
statement.
You could create a text file pet.txt containing one record per line, with values separated by tabs, and
given in the order in which the columns were listed in the CREATE TABLE statement. For missing values
(such as unknown sexes or death dates for animals that are still living), you can use NULL values. To
represent these in your text file, use \N (backslash, capital-N). For example, the record for Whistler the bird
would look like this (where the whitespace between values is a single tab character):
Whistler Gwen bird \N 1997-12-09 \N
To load the text file pet.txt into the pet table, use this statement:
mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;
If you created the file on Windows with an editor that uses \r\n as a line terminator, you should use this
statement instead:
mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet
-> LINES TERMINATED BY '\r\n';
(On an Apple machine running OS X, you would likely want to use LINES TERMINATED BY '\r'.)
You can specify the column value separator and end of line marker explicitly in the LOAD DATA statement
if you wish, but the defaults are tab and linefeed. These are sufficient for the statement to read the file
pet.txt properly.
If the statement fails, it is likely that your MySQL installation does not have local file capability enabled by
default. See Security Issues with LOAD DATA LOCAL, for information on how to change this.
When you want to add new records one at a time, the INSERT statement is useful. In its simplest form,
you supply values for each column, in the order in which the columns were listed in the CREATE TABLE
statement. Suppose that Diane gets a new hamster named “Puffball.” You could add a new record using
an INSERT statement like this:
mysql> INSERT INTO pet
-> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
String and date values are specified as quoted strings here. Also, with INSERT, you can insert NULL
directly to represent a missing value. You do not use \N like you do with LOAD DATA.
From this example, you should be able to see that there would be a lot more typing involved to load your
records initially using several INSERT statements rather than a single LOAD DATA statement.
4.4 Retrieving Information from a Table
The SELECT statement is used to pull information from a table. The general form of the statement is:
Selecting All Data
14
SELECT what_to_select
FROM which_table
WHERE conditions_to_satisfy;
what_to_select indicates what you want to see. This can be a list of columns, or * to indicate “all
columns.” which_table indicates the table from which you want to retrieve data. The WHERE clause
is optional. If it is present, conditions_to_satisfy specifies one or more conditions that rows must
satisfy to qualify for retrieval.
4.4.1 Selecting All Data
The simplest form of SELECT retrieves everything from a table:
mysql> SELECT * FROM pet;
+----------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+--------+---------+------+------------+------------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
| Slim | Benny | snake | m | 1996-04-29 | NULL |
| Puffball | Diane | hamster | f | 1999-03-30 | NULL |
+----------+--------+---------+------+------------+------------+
This form of SELECT is useful if you want to review your entire table, for example, after you've just loaded it
with your initial data set. For example, you may happen to think that the birth date for Bowser doesn't seem
quite right. Consulting your original pedigree papers, you find that the correct birth year should be 1989,
not 1979.
There are at least two ways to fix this:
• Edit the file pet.txt to correct the error, then empty the table and reload it using DELETE and LOAD
DATA:
mysql> DELETE FROM pet;
mysql> LOAD DATA LOCAL INFILE 'pet.txt' INTO TABLE pet;
However, if you do this, you must also re-enter the record for Puffball.
• Fix only the erroneous record with an UPDATE statement:
mysql> UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';
The UPDATE changes only the record in question and does not require you to reload the table.
4.4.2 Selecting Particular Rows
As shown in the preceding section, it is easy to retrieve an entire table. Just omit the WHERE clause from
the SELECT statement. But typically you don't want to see the entire table, particularly when it becomes
large. Instead, you're usually more interested in answering a particular question, in which case you specify
some constraints on the information you want. Let's look at some selection queries in terms of questions
about your pets that they answer.
Selecting Particular Columns
15
You can select only particular rows from your table. For example, if you want to verify the change that you
made to Bowser's birth date, select Bowser's record like this:
mysql> SELECT * FROM pet WHERE name = 'Bowser';
+--------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+-------+---------+------+------------+------------+
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
+--------+-------+---------+------+------------+------------+
The output confirms that the year is correctly recorded as 1989, not 1979.
String comparisons normally are case-insensitive, so you can specify the name as 'bowser', 'BOWSER',
and so forth. The query result is the same.
You can specify conditions on any column, not just name. For example, if you want to know which animals
were born during or after 1998, test the birth column:
mysql> SELECT * FROM pet WHERE birth >= '1998-1-1';
+----------+-------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+-------+
| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |
| Puffball | Diane | hamster | f | 1999-03-30 | NULL |
+----------+-------+---------+------+------------+-------+
You can combine conditions, for example, to locate female dogs:
mysql> SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
The preceding query uses the AND logical operator. There is also an OR operator:
mysql> SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';
+----------+-------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+-------+
| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
| Slim | Benny | snake | m | 1996-04-29 | NULL |
+----------+-------+---------+------+------------+-------+
AND and OR may be intermixed, although AND has higher precedence than OR. If you use both operators, it
is a good idea to use parentheses to indicate explicitly how conditions should be grouped:
mysql> SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm')
-> OR (species = 'dog' AND sex = 'f');
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
4.4.3 Selecting Particular Columns
Selecting Particular Columns
16
If you do not want to see entire rows from your table, just name the columns in which you are interested,
separated by commas. For example, if you want to know when your animals were born, select the name
and birth columns:
mysql> SELECT name, birth FROM pet;
+----------+------------+
| name | birth |
+----------+------------+
| Fluffy | 1993-02-04 |
| Claws | 1994-03-17 |
| Buffy | 1989-05-13 |
| Fang | 1990-08-27 |
| Bowser | 1989-08-31 |
| Chirpy | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim | 1996-04-29 |
| Puffball | 1999-03-30 |
+----------+------------+
To find out who owns pets, use this query:
mysql> SELECT owner FROM pet;
+--------+
| owner |
+--------+
| Harold |
| Gwen |
| Harold |
| Benny |
| Diane |
| Gwen |
| Gwen |
| Benny |
| Diane |
+--------+
Notice that the query simply retrieves the owner column from each record, and some of them appear more
than once. To minimize the output, retrieve each unique output record just once by adding the keyword
DISTINCT:
mysql> SELECT DISTINCT owner FROM pet;
+--------+
| owner |
+--------+
| Benny |
| Diane |
| Gwen |
| Harold |
+--------+
You can use a WHERE clause to combine row selection with column selection. For example, to get birth
dates for dogs and cats only, use this query:
mysql> SELECT name, species, birth FROM pet
-> WHERE species = 'dog' OR species = 'cat';
+--------+---------+------------+
| name | species | birth |
+--------+---------+------------+
| Fluffy | cat | 1993-02-04 |
| Claws | cat | 1994-03-17 |
Sorting Rows
17
| Buffy | dog | 1989-05-13 |
| Fang | dog | 1990-08-27 |
| Bowser | dog | 1989-08-31 |
+--------+---------+------------+
4.4.4 Sorting Rows
You may have noticed in the preceding examples that the result rows are displayed in no particular order. It
is often easier to examine query output when the rows are sorted in some meaningful way. To sort a result,
use an ORDER BY clause.
Here are animal birthdays, sorted by date:
mysql> SELECT name, birth FROM pet ORDER BY birth;
+----------+------------+
| name | birth |
+----------+------------+
| Buffy | 1989-05-13 |
| Bowser | 1989-08-31 |
| Fang | 1990-08-27 |
| Fluffy | 1993-02-04 |
| Claws | 1994-03-17 |
| Slim | 1996-04-29 |
| Whistler | 1997-12-09 |
| Chirpy | 1998-09-11 |
| Puffball | 1999-03-30 |
+----------+------------+
On character type columns, sorting—like all other comparison operations—is normally performed in a
case-insensitive fashion. This means that the order is undefined for columns that are identical except for
their case. You can force a case-sensitive sort for a column by using BINARY like so: ORDER BY BINARY
col_name.
The default sort order is ascending, with smallest values first. To sort in reverse (descending) order, add
the DESC keyword to the name of the column you are sorting by:
mysql> SELECT name, birth FROM pet ORDER BY birth DESC;
+----------+------------+
| name | birth |
+----------+------------+
| Puffball | 1999-03-30 |
| Chirpy | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim | 1996-04-29 |
| Claws | 1994-03-17 |
| Fluffy | 1993-02-04 |
| Fang | 1990-08-27 |
| Bowser | 1989-08-31 |
| Buffy | 1989-05-13 |
+----------+------------+
You can sort on multiple columns, and you can sort different columns in different directions. For example,
to sort by type of animal in ascending order, then by birth date within animal type in descending order
(youngest animals first), use the following query:
mysql> SELECT name, species, birth FROM pet
-> ORDER BY species, birth DESC;
+----------+---------+------------+
| name | species | birth |
+----------+---------+------------+
Date Calculations
18
| Chirpy | bird | 1998-09-11 |
| Whistler | bird | 1997-12-09 |
| Claws | cat | 1994-03-17 |
| Fluffy | cat | 1993-02-04 |
| Fang | dog | 1990-08-27 |
| Bowser | dog | 1989-08-31 |
| Buffy | dog | 1989-05-13 |
| Puffball | hamster | 1999-03-30 |
| Slim | snake | 1996-04-29 |
+----------+---------+------------+
The DESC keyword applies only to the column name immediately preceding it (birth); it does not affect
the species column sort order.
4.4.5 Date Calculations
MySQL provides several functions that you can use to perform calculations on dates, for example, to
calculate ages or extract parts of dates.
To determine how many years old each of your pets is, use the TIMESTAMPDIFF() function. Its
arguments are the unit in which you want the result expressed, and the two date for which to take the
difference. The following query shows, for each pet, the birth date, the current date, and the age in years.
An alias (age) is used to make the final output column label more meaningful.
mysql> SELECT name, birth, CURDATE(),
-> TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
-> FROM pet;
+----------+------------+------------+------+
| name | birth | CURDATE() | age |
+----------+------------+------------+------+
| Fluffy | 1993-02-04 | 2003-08-19 | 10 |
| Claws | 1994-03-17 | 2003-08-19 | 9 |
| Buffy | 1989-05-13 | 2003-08-19 | 14 |
| Fang | 1990-08-27 | 2003-08-19 | 12 |
| Bowser | 1989-08-31 | 2003-08-19 | 13 |
| Chirpy | 1998-09-11 | 2003-08-19 | 4 |
| Whistler | 1997-12-09 | 2003-08-19 | 5 |
| Slim | 1996-04-29 | 2003-08-19 | 7 |
| Puffball | 1999-03-30 | 2003-08-19 | 4 |
+----------+------------+------------+------+
The query works, but the result could be scanned more easily if the rows were presented in some order.
This can be done by adding an ORDER BY name clause to sort the output by name:
mysql> SELECT name, birth, CURDATE(),
-> TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
-> FROM pet ORDER BY name;
+----------+------------+------------+------+
| name | birth | CURDATE() | age |
+----------+------------+------------+------+
| Bowser | 1989-08-31 | 2003-08-19 | 13 |
| Buffy | 1989-05-13 | 2003-08-19 | 14 |
| Chirpy | 1998-09-11 | 2003-08-19 | 4 |
| Claws | 1994-03-17 | 2003-08-19 | 9 |
| Fang | 1990-08-27 | 2003-08-19 | 12 |
| Fluffy | 1993-02-04 | 2003-08-19 | 10 |
| Puffball | 1999-03-30 | 2003-08-19 | 4 |
| Slim | 1996-04-29 | 2003-08-19 | 7 |
| Whistler | 1997-12-09 | 2003-08-19 | 5 |
+----------+------------+------------+------+
To sort the output by age rather than name, just use a different ORDER BY clause:
Date Calculations
19
mysql> SELECT name, birth, CURDATE(),
-> TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
-> FROM pet ORDER BY age;
+----------+------------+------------+------+
| name | birth | CURDATE() | age |
+----------+------------+------------+------+
| Chirpy | 1998-09-11 | 2003-08-19 | 4 |
| Puffball | 1999-03-30 | 2003-08-19 | 4 |
| Whistler | 1997-12-09 | 2003-08-19 | 5 |
| Slim | 1996-04-29 | 2003-08-19 | 7 |
| Claws | 1994-03-17 | 2003-08-19 | 9 |
| Fluffy | 1993-02-04 | 2003-08-19 | 10 |
| Fang | 1990-08-27 | 2003-08-19 | 12 |
| Bowser | 1989-08-31 | 2003-08-19 | 13 |
| Buffy | 1989-05-13 | 2003-08-19 | 14 |
+----------+------------+------------+------+
A similar query can be used to determine age at death for animals that have died. You determine which
animals these are by checking whether the death value is NULL. Then, for those with non-NULL values,
compute the difference between the death and birth values:
mysql> SELECT name, birth, death,
-> TIMESTAMPDIFF(YEAR,birth,death) AS age
-> FROM pet WHERE death IS NOT NULL ORDER BY age;
+--------+------------+------------+------+
| name | birth | death | age |
+--------+------------+------------+------+
| Bowser | 1989-08-31 | 1995-07-29 | 5 |
+--------+------------+------------+------+
The query uses death IS NOT NULL rather than death <> NULL because NULL is a special value
that cannot be compared using the usual comparison operators. This is discussed later. See Section 4.4.6,
“Working with NULL Values”.
What if you want to know which animals have birthdays next month? For this type of calculation, year
and day are irrelevant; you simply want to extract the month part of the birth column. MySQL provides
several functions for extracting parts of dates, such as YEAR(), MONTH(), and DAYOFMONTH(). MONTH()
is the appropriate function here. To see how it works, run a simple query that displays the value of both
birth and MONTH(birth):
mysql> SELECT name, birth, MONTH(birth) FROM pet;
+----------+------------+--------------+
| name | birth | MONTH(birth) |
+----------+------------+--------------+
| Fluffy | 1993-02-04 | 2 |
| Claws | 1994-03-17 | 3 |
| Buffy | 1989-05-13 | 5 |
| Fang | 1990-08-27 | 8 |
| Bowser | 1989-08-31 | 8 |
| Chirpy | 1998-09-11 | 9 |
| Whistler | 1997-12-09 | 12 |
| Slim | 1996-04-29 | 4 |
| Puffball | 1999-03-30 | 3 |
+----------+------------+--------------+
Finding animals with birthdays in the upcoming month is also simple. Suppose that the current month is
April. Then the month value is 4 and you can look for animals born in May (month 5) like this:
mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5;
+-------+------------+
Working with NULL Values
20
| name | birth |
+-------+------------+
| Buffy | 1989-05-13 |
+-------+------------+
There is a small complication if the current month is December. You cannot merely add one to the month
number (12) and look for animals born in month 13, because there is no such month. Instead, you look for
animals born in January (month 1).
You can write the query so that it works no matter what the current month is, so that you do not have to
use the number for a particular month. DATE_ADD() enables you to add a time interval to a given date.
If you add a month to the value of CURDATE(), then extract the month part with MONTH(), the result
produces the month in which to look for birthdays:
mysql> SELECT name, birth FROM pet
-> WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));
A different way to accomplish the same task is to add 1 to get the next month after the current one after
using the modulo function (MOD) to wrap the month value to 0 if it is currently 12:
mysql> SELECT name, birth FROM pet
-> WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;
MONTH() returns a number between 1 and 12. And MOD(something,12) returns a number between 0
and 11. So the addition has to be after the MOD(), otherwise we would go from November (11) to January
(1).
4.4.6 Working with NULL Values
The NULL value can be surprising until you get used to it. Conceptually, NULL means “a missing unknown
value” and it is treated somewhat differently from other values.
To test for NULL, use the IS NULL and IS NOT NULL operators, as shown here:
mysql> SELECT 1 IS NULL, 1 IS NOT NULL;
+-----------+---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+
| 0 | 1 |
+-----------+---------------+
You cannot use arithmetic comparison operators such as =, <, or <> to test for NULL. To demonstrate this
for yourself, try the following query:
mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
+----------+-----------+----------+----------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
+----------+-----------+----------+----------+
| NULL | NULL | NULL | NULL |
+----------+-----------+----------+----------+
Because the result of any arithmetic comparison with NULL is also NULL, you cannot obtain any meaningful
results from such comparisons.
In MySQL, 0 or NULL means false and anything else means true. The default truth value from a boolean
operation is 1.
Pattern Matching
21
This special treatment of NULL is why, in the previous section, it was necessary to determine which
animals are no longer alive using death IS NOT NULL instead of death <> NULL.
Two NULL values are regarded as equal in a GROUP BY.
When doing an ORDER BY, NULL values are presented first if you do ORDER BY ... ASC and last if you
do ORDER BY ... DESC.
A common error when working with NULL is to assume that it is not possible to insert a zero or an empty
string into a column defined as NOT NULL, but this is not the case. These are in fact values, whereas NULL
means “not having a value.” You can test this easily enough by using IS [NOT] NULL as shown:
mysql> SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;
+-----------+---------------+------------+----------------+
| 0 IS NULL | 0 IS NOT NULL | '' IS NULL | '' IS NOT NULL |
+-----------+---------------+------------+----------------+
| 0 | 1 | 0 | 1 |
+-----------+---------------+------------+----------------+
Thus it is entirely possible to insert a zero or empty string into a NOT NULL column, as these are in fact
NOT NULL. See Problems with NULL Values.
4.4.7 Pattern Matching
MySQL provides standard SQL pattern matching as well as a form of pattern matching based on extended
regular expressions similar to those used by Unix utilities such as vi, grep, and sed.
SQL pattern matching enables you to use “_” to match any single character and “%” to match an arbitrary
number of characters (including zero characters). In MySQL, SQL patterns are case-insensitive by default.
Some examples are shown here. You do not use = or <> when you use SQL patterns; use the LIKE or
NOT LIKE comparison operators instead.
To find names beginning with “b”:
mysql> SELECT * FROM pet WHERE name LIKE 'b%';
+--------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+------------+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+
To find names ending with “fy”:
mysql> SELECT * FROM pet WHERE name LIKE '%fy';
+--------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+--------+--------+---------+------+------------+-------+
To find names containing a “w”:
mysql> SELECT * FROM pet WHERE name LIKE '%w%';
+----------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
Pattern Matching
22
+----------+-------+---------+------+------------+------------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
+----------+-------+---------+------+------------+------------+
To find names containing exactly five characters, use five instances of the “_” pattern character:
mysql> SELECT * FROM pet WHERE name LIKE '_____';
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
The other type of pattern matching provided by MySQL uses extended regular expressions. When you
test for a match for this type of pattern, use the REGEXP and NOT REGEXP operators (or RLIKE and NOT
RLIKE, which are synonyms).
The following list describes some characteristics of extended regular expressions:
• “.” matches any single character.
• A character class “[...]” matches any character within the brackets. For example, “[abc]” matches
“a”, “b”, or “c”. To name a range of characters, use a dash. “[a-z]” matches any letter, whereas
“[0-9]” matches any digit.
• “*” matches zero or more instances of the thing preceding it. For example, “x*” matches any number of
“x” characters, “[0-9]*” matches any number of digits, and “.*” matches any number of anything.
• A REGEXP pattern match succeeds if the pattern matches anywhere in the value being tested. (This
differs from a LIKE pattern match, which succeeds only if the pattern matches the entire value.)
• To anchor a pattern so that it must match the beginning or end of the value being tested, use “^” at the
beginning or “$” at the end of the pattern.
To demonstrate how extended regular expressions work, the LIKE queries shown previously are rewritten
here to use REGEXP.
To find names beginning with “b”, use “^” to match the beginning of the name:
mysql> SELECT * FROM pet WHERE name REGEXP '^b';
+--------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+------------+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+
If you really want to force a REGEXP comparison to be case sensitive, use the BINARY keyword to make
one of the strings a binary string. This query matches only lowercase “b” at the beginning of a name:
mysql> SELECT * FROM pet WHERE name REGEXP BINARY '^b';
To find names ending with “fy”, use “$” to match the end of the name:
mysql> SELECT * FROM pet WHERE name REGEXP 'fy$';
Counting Rows
23
+--------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+--------+--------+---------+------+------------+-------+
To find names containing a “w”, use this query:
mysql> SELECT * FROM pet WHERE name REGEXP 'w';
+----------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+------------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
+----------+-------+---------+------+------------+------------+
Because a regular expression pattern matches if it occurs anywhere in the value, it is not necessary in the
previous query to put a wildcard on either side of the pattern to get it to match the entire value like it would
be if you used an SQL pattern.
To find names containing exactly five characters, use “^” and “$” to match the beginning and end of the
name, and five instances of “.” in between:
mysql> SELECT * FROM pet WHERE name REGEXP '^.....$';
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
You could also write the previous query using the {n} (“repeat-n-times”) operator:
mysql> SELECT * FROM pet WHERE name REGEXP '^.{5}$';
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
Regular Expressions, provides more information about the syntax for regular expressions.
4.4.8 Counting Rows
Databases are often used to answer the question, “How often does a certain type of data occur in a table?”
For example, you might want to know how many pets you have, or how many pets each owner has, or you
might want to perform various kinds of census operations on your animals.
Counting the total number of animals you have is the same question as “How many rows are in the pet
table?” because there is one record per pet. COUNT(*) counts the number of rows, so the query to count
your animals looks like this:
mysql> SELECT COUNT(*) FROM pet;
+----------+
| COUNT(*) |
+----------+
Counting Rows
24
| 9 |
+----------+
Earlier, you retrieved the names of the people who owned pets. You can use COUNT() if you want to find
out how many pets each owner has:
mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
+--------+----------+
| owner | COUNT(*) |
+--------+----------+
| Benny | 2 |
| Diane | 2 |
| Gwen | 3 |
| Harold | 2 |
+--------+----------+
The preceding query uses GROUP BY to group all records for each owner. The use of COUNT() in
conjunction with GROUP BY is useful for characterizing your data under various groupings. The following
examples show different ways to perform animal census operations.
Number of animals per species:
mysql> SELECT species, COUNT(*) FROM pet GROUP BY species;
+---------+----------+
| species | COUNT(*) |
+---------+----------+
| bird | 2 |
| cat | 2 |
| dog | 3 |
| hamster | 1 |
| snake | 1 |
+---------+----------+
Number of animals per sex:
mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex;
+------+----------+
| sex | COUNT(*) |
+------+----------+
| NULL | 1 |
| f | 4 |
| m | 4 |
+------+----------+
(In this output, NULL indicates that the sex is unknown.)
Number of animals per combination of species and sex:
mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;
+---------+------+----------+
| species | sex | COUNT(*) |
+---------+------+----------+
| bird | NULL | 1 |
| bird | f | 1 |
| cat | f | 1 |
| cat | m | 1 |
| dog | f | 1 |
| dog | m | 2 |
| hamster | f | 1 |
| snake | m | 1 |
+---------+------+----------+
Using More Than one Table
25
You need not retrieve an entire table when you use COUNT(). For example, the previous query, when
performed just on dogs and cats, looks like this:
mysql> SELECT species, sex, COUNT(*) FROM pet
-> WHERE species = 'dog' OR species = 'cat'
-> GROUP BY species, sex;
+---------+------+----------+
| species | sex | COUNT(*) |
+---------+------+----------+
| cat | f | 1 |
| cat | m | 1 |
| dog | f | 1 |
| dog | m | 2 |
+---------+------+----------+
Or, if you wanted the number of animals per sex only for animals whose sex is known:
mysql> SELECT species, sex, COUNT(*) FROM pet
-> WHERE sex IS NOT NULL
-> GROUP BY species, sex;
+---------+------+----------+
| species | sex | COUNT(*) |
+---------+------+----------+
| bird | f | 1 |
| cat | f | 1 |
| cat | m | 1 |
| dog | f | 1 |
| dog | m | 2 |
| hamster | f | 1 |
| snake | m | 1 |
+---------+------+----------+
If you name columns to select in addition to the COUNT() value, a GROUP BY clause should be present
that names those same columns. Otherwise, the following occurs:
• If the ONLY_FULL_GROUP_BY SQL mode is enabled, an error occurs:
mysql> SET sql_mode = 'ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT owner, COUNT(*) FROM pet;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT()...)
with no GROUP columns is illegal if there is no GROUP BY clause
• If ONLY_FULL_GROUP_BY is not enabled, the query is processed by treating all rows as a single group,
but the value selected for each named column is indeterminate. The server is free to select the value
from any row:
mysql> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT owner, COUNT(*) FROM pet;
+--------+----------+
| owner | COUNT(*) |
+--------+----------+
| Harold | 8 |
+--------+----------+
1 row in set (0.00 sec)
See also MySQL Handling of GROUP BY.
4.4.9 Using More Than one Table
Using More Than one Table
26
The pet table keeps track of which pets you have. If you want to record other information about them,
such as events in their lives like visits to the vet or when litters are born, you need another table. What
should this table look like? It needs to contain the following information:
• The pet name so that you know which animal each event pertains to.
• A date so that you know when the event occurred.
• A field to describe the event.
• An event type field, if you want to be able to categorize events.
Given these considerations, the CREATE TABLE statement for the event table might look like this:
mysql> CREATE TABLE event (name VARCHAR(20), date DATE,
-> type VARCHAR(15), remark VARCHAR(255));
As with the pet table, it is easiest to load the initial records by creating a tab-delimited text file containing
the following information.
name date type remark
Fluffy 1995-05-15 litter 4 kittens, 3 female, 1 male
Buffy 1993-06-23 litter 5 puppies, 2 female, 3 male
Buffy 1994-06-19 litter 3 puppies, 3 female
Chirpy 1999-03-21 vet needed beak straightened
Slim 1997-08-03 vet broken rib
Bowser 1991-10-12 kennel
Fang 1991-10-12 kennel
Fang 1998-08-28 birthday Gave him a new chew toy
Claws 1998-03-17 birthday Gave him a new flea collar
Whistler 1998-12-09 birthday First birthday
Load the records like this:
mysql> LOAD DATA LOCAL INFILE 'event.txt' INTO TABLE event;
Based on what you have learned from the queries that you have run on the pet table, you should be able
to perform retrievals on the records in the event table; the principles are the same. But when is the event
table by itself insufficient to answer questions you might ask?
Suppose that you want to find out the ages at which each pet had its litters. We saw earlier how to
calculate ages from two dates. The litter date of the mother is in the event table, but to calculate her age
on that date you need her birth date, which is stored in the pet table. This means the query requires both
tables:
mysql> SELECT pet.name,
-> (YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5)) AS age,
-> remark
-> FROM pet INNER JOIN event
-> ON pet.name = event.name
-> WHERE event.type = 'litter';
+--------+------+-----------------------------+
Using More Than one Table
27
| name | age | remark |
+--------+------+-----------------------------+
| Fluffy | 2 | 4 kittens, 3 female, 1 male |
| Buffy | 4 | 5 puppies, 2 female, 3 male |
| Buffy | 5 | 3 puppies, 3 female |
+--------+------+-----------------------------+
There are several things to note about this query:
• The FROM clause joins two tables because the query needs to pull information from both of them.
• When combining (joining) information from multiple tables, you need to specify how records in one table
can be matched to records in the other. This is easy because they both have a name column. The query
uses an ON clause to match up records in the two tables based on the name values.
The query uses an INNER JOIN to combine the tables. An INNER JOIN permits rows from either table
to appear in the result if and only if both tables meet the conditions specified in the ON clause. In this
example, the ON clause specifies that the name column in the pet table must match the name column
in the event table. If a name appears in one table but not the other, the row will not appear in the result
because the condition in the ON clause fails.
• Because the name column occurs in both tables, you must be specific about which table you mean when
referring to the column. This is done by prepending the table name to the column name.
You need not have two different tables to perform a join. Sometimes it is useful to join a table to itself, if
you want to compare records in a table to other records in that same table. For example, to find breeding
pairs among your pets, you can join the pet table with itself to produce candidate pairs of males and
females of like species:
mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
-> FROM pet AS p1 INNER JOIN pet AS p2
-> ON p1.species = p2.species AND p1.sex = 'f' AND p2.sex = 'm';
+--------+------+--------+------+---------+
| name | sex | name | sex | species |
+--------+------+--------+------+---------+
| Fluffy | f | Claws | m | cat |
| Buffy | f | Fang | m | dog |
| Buffy | f | Bowser | m | dog |
+--------+------+--------+------+---------+
In this query, we specify aliases for the table name to refer to the columns and keep straight which
instance of the table each column reference is associated with.
28
29
Chapter 5 Getting Information About Databases and Tables
What if you forget the name of a database or table, or what the structure of a given table is (for example,
what its columns are called)? MySQL addresses this problem through several statements that provide
information about the databases and tables it supports.
You have previously seen SHOW DATABASES, which lists the databases managed by the server. To find
out which database is currently selected, use the DATABASE() function:
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| menagerie |
+------------+
If you have not yet selected any database, the result is NULL.
To find out what tables the default database contains (for example, when you are not sure about the name
of a table), use this command:
mysql> SHOW TABLES;
+---------------------+
| Tables_in_menagerie |
+---------------------+
| event |
| pet |
+---------------------+
The name of the column in the output produced by this statement is always Tables_in_db_name, where
db_name is the name of the database. See SHOW TABLES Syntax, for more information.
If you want to find out about the structure of a table, the DESCRIBE statement is useful; it displays
information about each of a table's columns:
mysql> DESCRIBE pet;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| owner | varchar(20) | YES | | NULL | |
| species | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birth | date | YES | | NULL | |
| death | date | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
Field indicates the column name, Type is the data type for the column, NULL indicates whether the
column can contain NULL values, Key indicates whether the column is indexed, and Default specifies the
column's default value. Extra displays special information about columns: If a column was created with
the AUTO_INCREMENT option, the value will be auto_increment rather than empty.
DESC is a short form of DESCRIBE. See DESCRIBE Syntax, for more information.
You can obtain the CREATE TABLE statement necessary to create an existing table using the SHOW
CREATE TABLE statement. See SHOW CREATE TABLE Syntax.
If you have indexes on a table, SHOW INDEX FROM tbl_name produces information about them. See
SHOW INDEX Syntax, for more about this statement.
30
31
Chapter 6 Using mysql in Batch Mode
In the previous sections, you used mysql interactively to enter queries and view the results. You can also
run mysql in batch mode. To do this, put the commands you want to run in a file, then tell mysql to read
its input from the file:
shell> mysql < batch-file
If you are running mysql under Windows and have some special characters in the file that cause
problems, you can do this:
C:\> mysql -e "source batch-file"
If you need to specify connection parameters on the command line, the command might look like this:
shell> mysql -h host -u user -p < batch-file
Enter password: ********
When you use mysql this way, you are creating a script file, then executing the script.
If you want the script to continue even if some of the statements in it produce errors, you should use the --
force command-line option.
Why use a script? Here are a few reasons:
• If you run a query repeatedly (say, every day or every week), making it a script enables you to avoid
retyping it each time you execute it.
• You can generate new queries from existing ones that are similar by copying and editing script files.
• Batch mode can also be useful while you're developing a query, particularly for multiple-line commands
or multiple-statement sequences of commands. If you make a mistake, you don't have to retype
everything. Just edit your script to correct the error, then tell mysql to execute it again.
• If you have a query that produces a lot of output, you can run the output through a pager rather than
watching it scroll off the top of your screen:
shell> mysql < batch-file | more
• You can catch the output in a file for further processing:
shell> mysql < batch-file > mysql.out
• You can distribute your script to other people so that they can also run the commands.
• Some situations do not allow for interactive use, for example, when you run a query from a cron job. In
this case, you must use batch mode.
The default output format is different (more concise) when you run mysql in batch mode than when you
use it interactively. For example, the output of SELECT DISTINCT species FROM pet looks like this
when mysql is run interactively:
+---------+
32
| species |
+---------+
| bird |
| cat |
| dog |
| hamster |
| snake |
+---------+
In batch mode, the output looks like this instead:
species
bird
cat
dog
hamster
snake
If you want to get the interactive output format in batch mode, use mysql -t. To echo to the output the
commands that are executed, use mysql -vvv.
You can also use scripts from the mysql prompt by using the source command or \. command:
mysql> source filename;
mysql> \. filename
See Executing SQL Statements from a Text File, for more information.
33
Chapter 7 Examples of Common Queries
Table of Contents
7.1 The Maximum Value for a Column .............................................................................................. 33
7.2 The Row Holding the Maximum of a Certain Column ................................................................... 34
7.3 Maximum of Column per Group .................................................................................................. 34
7.4 The Rows Holding the Group-wise Maximum of a Certain Column ................................................ 34
7.5 Using User-Defined Variables ...................................................................................................... 35
7.6 Using Foreign Keys .................................................................................................................... 36
7.7 Searching on Two Keys .............................................................................................................. 37
7.8 Calculating Visits Per Day ........................................................................................................... 37
7.9 Using AUTO_INCREMENT ......................................................................................................... 38
Here are examples of how to solve some common problems with MySQL.
Some of the examples use the table shop to hold the price of each article (item number) for certain traders
(dealers). Supposing that each trader has a single fixed price per article, then (article, dealer) is a
primary key for the records.
Start the command-line tool mysql and select a database:
shell> mysql your-database-name
(In most MySQL installations, you can use the database named test).
You can create and populate the example table with these statements:
CREATE TABLE shop (
article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
dealer CHAR(20) DEFAULT '' NOT NULL,
price DOUBLE(16,2) DEFAULT '0.00' NOT NULL,
PRIMARY KEY(article, dealer));
INSERT INTO shop VALUES
(1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),
(3,'C',1.69),(3,'D',1.25),(4,'D',19.95);
After issuing the statements, the table should have the following contents:
SELECT * FROM shop;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0001 | A | 3.45 |
| 0001 | B | 3.99 |
| 0002 | A | 10.99 |
| 0003 | B | 1.45 |
| 0003 | C | 1.69 |
| 0003 | D | 1.25 |
| 0004 | D | 19.95 |
+---------+--------+-------+
7.1 The Maximum Value for a Column
“What is the highest item number?”
The Row Holding the Maximum of a Certain Column
34
SELECT MAX(article) AS article FROM shop;
+---------+
| article |
+---------+
| 4 |
+---------+
7.2 The Row Holding the Maximum of a Certain Column
Task: Find the number, dealer, and price of the most expensive article.
This is easily done with a subquery:
SELECT article, dealer, price
FROM shop
WHERE price=(SELECT MAX(price) FROM shop);
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0004 | D | 19.95 |
+---------+--------+-------+
Other solutions are to use a LEFT JOIN or to sort all rows descending by price and get only the first row
using the MySQL-specific LIMIT clause:
SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.price < s2.price
WHERE s2.article IS NULL;
SELECT article, dealer, price
FROM shop
ORDER BY price DESC
LIMIT 1;
Note
If there were several most expensive articles, each with a price of 19.95, the LIMIT
solution would show only one of them.
7.3 Maximum of Column per Group
Task: Find the highest price per article.
SELECT article, MAX(price) AS price
FROM shop
GROUP BY article;
+---------+-------+
| article | price |
+---------+-------+
| 0001 | 3.99 |
| 0002 | 10.99 |
| 0003 | 1.69 |
| 0004 | 19.95 |
+---------+-------+
7.4 The Rows Holding the Group-wise Maximum of a Certain Column
Task: For each article, find the dealer or dealers with the most expensive price.
Using User-Defined Variables
35
This problem can be solved with a subquery like this one:
SELECT article, dealer, price
FROM shop s1
WHERE price=(SELECT MAX(s2.price)
FROM shop s2
WHERE s1.article = s2.article);
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0001 | B | 3.99 |
| 0002 | A | 10.99 |
| 0003 | C | 1.69 |
| 0004 | D | 19.95 |
+---------+--------+-------+
The preceding example uses a correlated subquery, which can be inefficient (see Correlated Subqueries).
Other possibilities for solving the problem are to use an uncorrelated subquery in the FROM clause or a
LEFT JOIN.
Uncorrelated subquery:
SELECT s1.article, dealer, s1.price
FROM shop s1
JOIN (
SELECT article, MAX(price) AS price
FROM shop
GROUP BY article) AS s2
ON s1.article = s2.article AND s1.price = s2.price;
LEFT JOIN:
SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price
WHERE s2.article IS NULL;
The LEFT JOIN works on the basis that when s1.price is at its maximum value, there is no s2.price
with a greater value and the s2 rows values will be NULL. See JOIN Syntax.
7.5 Using User-Defined Variables
You can employ MySQL user variables to remember results without having to store them in temporary
variables in the client. (See User-Defined Variables.)
For example, to find the articles with the highest and lowest price you can do this:
mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0003 | D | 1.25 |
| 0004 | D | 19.95 |
+---------+--------+-------+
Note
It is also possible to store the name of a database object such as a table or a
column in a user variable and then to use this variable in an SQL statement;
Using Foreign Keys
36
however, this requires the use of a prepared statement. See SQL Syntax for
Prepared Statements, for more information.
7.6 Using Foreign Keys
In MySQL, InnoDB tables support checking of foreign key constraints. See The InnoDB Storage Engine,
and Foreign Keys.
A foreign key constraint is not required merely to join two tables. For storage engines other than InnoDB,
it is possible when defining a column to use a REFERENCES tbl_name(col_name) clause, which has
no actual effect, and serves only as a memo or comment to you that the column which you are currently
defining is intended to refer to a column in another table. It is extremely important to realize when using this
syntax that:
• MySQL does not perform any sort of CHECK to make sure that col_name actually exists in tbl_name
(or even that tbl_name itself exists).
• MySQL does not perform any sort of action on tbl_name such as deleting rows in response to actions
taken on rows in the table which you are defining; in other words, this syntax induces no ON DELETE or
ON UPDATE behavior whatsoever. (Although you can write an ON DELETE or ON UPDATE clause as part
of the REFERENCES clause, it is also ignored.)
• This syntax creates a column; it does not create any sort of index or key.
You can use a column so created as a join column, as shown here:
CREATE TABLE person (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(60) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE shirt (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
PRIMARY KEY (id)
);
INSERT INTO person VALUES (NULL, 'Antonio Paz');
SELECT @last := LAST_INSERT_ID();
INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', @last),
(NULL, 'dress', 'white', @last),
(NULL, 't-shirt', 'blue', @last);
INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');
SELECT @last := LAST_INSERT_ID();
INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', @last),
(NULL, 'polo', 'red', @last),
(NULL, 'dress', 'blue', @last),
(NULL, 't-shirt', 'white', @last);
SELECT * FROM person;
+----+---------------------+
| id | name |
+----+---------------------+
| 1 | Antonio Paz |
| 2 | Lilliana Angelovska |
+----+---------------------+
SELECT * FROM shirt;
+----+---------+--------+-------+
| id | style | color | owner |
+----+---------+--------+-------+
Searching on Two Keys
37
| 1 | polo | blue | 1 |
| 2 | dress | white | 1 |
| 3 | t-shirt | blue | 1 |
| 4 | dress | orange | 2 |
| 5 | polo | red | 2 |
| 6 | dress | blue | 2 |
| 7 | t-shirt | white | 2 |
+----+---------+--------+-------+
SELECT s.* FROM person p INNER JOIN shirt s
ON s.owner = p.id
WHERE p.name LIKE 'Lilliana%'
AND s.color <> 'white';
+----+-------+--------+-------+
| id | style | color | owner |
+----+-------+--------+-------+
| 4 | dress | orange | 2 |
| 5 | polo | red | 2 |
| 6 | dress | blue | 2 |
+----+-------+--------+-------+
When used in this fashion, the REFERENCES clause is not displayed in the output of SHOW CREATE TABLE
or DESCRIBE:
SHOW CREATE TABLE shirt\G
*************************** 1. row ***************************
Table: shirt
Create Table: CREATE TABLE `shirt` (
`id` smallint(5) unsigned NOT NULL auto_increment,
`style` enum('t-shirt','polo','dress') NOT NULL,
`color` enum('red','blue','orange','white','black') NOT NULL,
`owner` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
The use of REFERENCES in this way as a comment or “reminder” in a column definition works with MyISAM
tables.
7.7 Searching on Two Keys
An OR using a single key is well optimized, as is the handling of AND.
The one tricky case is that of searching on two different keys combined with OR:
SELECT field1_index, field2_index FROM test_table
WHERE field1_index = '1' OR field2_index = '1'
This case is optimized. See Index Merge Optimization.
You can also solve the problem efficiently by using a UNION that combines the output of two separate
SELECT statements. See UNION Syntax.
Each SELECT searches only one key and can be optimized:
SELECT field1_index, field2_index
FROM test_table WHERE field1_index = '1'
UNION
SELECT field1_index, field2_index
FROM test_table WHERE field2_index = '1';
7.8 Calculating Visits Per Day
Using AUTO_INCREMENT
38
The following example shows how you can use the bit group functions to calculate the number of days per
month a user has visited a Web page.
CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL,
day INT(2) UNSIGNED ZEROFILL);
INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),
(2000,2,23),(2000,2,23);
The example table contains year-month-day values representing visits by users to the page. To determine
how many different days in each month these visits occur, use this query:
SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1
GROUP BY year,month;
Which returns:
+------+-------+------+
| year | month | days |
+------+-------+------+
| 2000 | 01 | 3 |
| 2000 | 02 | 2 |
+------+-------+------+
The query calculates how many different days appear in the table for each year/month combination, with
automatic removal of duplicate entries.
7.9 Using AUTO_INCREMENT
The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows:
CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;
INSERT INTO animals (name) VALUES
('dog'),('cat'),('penguin'),
('lax'),('whale'),('ostrich');
SELECT * FROM animals;
Which returns:
+----+---------+
| id | name |
+----+---------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
+----+---------+
No value was specified for the AUTO_INCREMENT column, so MySQL assigned sequence numbers
automatically. You can also explicitly assign 0 to the column to generate sequence numbers. If the column
is declared NOT NULL, it is also possible to assign NULL to the column to generate sequence numbers.
InnoDB Notes
39
You can retrieve the most recent AUTO_INCREMENT value with the LAST_INSERT_ID() SQL function or
the mysql_insert_id() C API function. These functions are connection-specific, so their return values
are not affected by another connection which is also performing inserts.
Use the smallest integer data type for the AUTO_INCREMENT column that is large enough to hold the
maximum sequence value you will need. When the column reaches the upper limit of the data type, the
next attempt to generate a sequence number fails. Use the UNSIGNED attribute if possible to allow a
greater range. For example, if you use TINYINT, the maximum permissible sequence number is 127. For
TINYINT UNSIGNED, the maximum is 255. See Integer Types (Exact Value) - INTEGER, INT, SMALLINT,
TINYINT, MEDIUMINT, BIGINT for the ranges of all the integer types.
Note
For a multiple-row insert, LAST_INSERT_ID() and mysql_insert_id() actually
return the AUTO_INCREMENT key from the first of the inserted rows. This enables
multiple-row inserts to be reproduced correctly on other servers in a replication
setup.
To start with an AUTO_INCREMENT value other than 1, set that value with CREATE TABLE or ALTER
TABLE, like this:
mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;
InnoDB Notes
For InnoDB tables, be careful if you modify the column containing the auto-increment value in the middle
of a sequence of INSERT statements. For example, if you use an UPDATE statement to put a new, larger
value in the auto-increment column, a subsequent INSERT could encounter a “Duplicate entry” error.
The test whether an auto-increment value is already present occurs if you do a DELETE followed by more
INSERT statements, or when you COMMIT the transaction, but not after an UPDATE statement.
MyISAM Notes
• For MyISAM tables, you can specify AUTO_INCREMENT on a secondary column in a multiplecolumn
index. In this case, the generated value for the AUTO_INCREMENT column is calculated as
MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you
want to put data into ordered groups.
CREATE TABLE animals (
grp ENUM('fish','mammal','bird') NOT NULL,
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (grp,id)
) ENGINE=MyISAM;
INSERT INTO animals (grp,name) VALUES
('mammal','dog'),('mammal','cat'),
('bird','penguin'),('fish','lax'),('mammal','whale'),
('bird','ostrich');
SELECT * FROM animals ORDER BY grp,id;
Which returns:
+--------+----+---------+
| grp | id | name |
+--------+----+---------+
| fish | 1 | lax |
Further Reading
40
| mammal | 1 | dog |
| mammal | 2 | cat |
| mammal | 3 | whale |
| bird | 1 | penguin |
| bird | 2 | ostrich |
+--------+----+---------+
In this case (when the AUTO_INCREMENT column is part of a multiple-column index), AUTO_INCREMENT
values are reused if you delete the row with the biggest AUTO_INCREMENT value in any group. This
happens even for MyISAM tables, for which AUTO_INCREMENT values normally are not reused.
• If the AUTO_INCREMENT column is part of multiple indexes, MySQL generates sequence values using
the index that begins with the AUTO_INCREMENT column, if there is one. For example, if the animals
table contained indexes PRIMARY KEY (grp, id) and INDEX (id), MySQL would ignore the
PRIMARY KEY for generating sequence values. As a result, the table would contain a single sequence,
not a sequence per grp value.
Further Reading
More information about AUTO_INCREMENT is available here:
• How to assign the AUTO_INCREMENT attribute to a column: CREATE TABLE Syntax, and ALTER
TABLE Syntax.
• How AUTO_INCREMENT behaves depending on the NO_AUTO_VALUE_ON_ZERO SQL mode: Server
SQL Modes.
• How to use the LAST_INSERT_ID() function to find the row that contains the most recent
AUTO_INCREMENT value: Information Functions.
• Setting the AUTO_INCREMENT value to be used: Server System Variables.
• AUTO_INCREMENT and replication: Replication and AUTO_INCREMENT.
• Server-system variables related to AUTO_INCREMENT (auto_increment_increment and
auto_increment_offset) that can be used for replication: Server System Variables.
41
Chapter 8 Using MySQL with Apache
There are programs that let you authenticate your users from a MySQL database and also let you write
your log files into a MySQL table.
You can change the Apache logging format to be easily readable by MySQL by putting the following into
the Apache configuration file:
LogFormat \
"\"%h\",%{%Y%m%d%H%M%S}t,%>s,\"%b\",\"%{Content-Type}o\", \
\"%U\",\"%{Referer}i\",\"%{User-Agent}i\""
To load a log file in that format into MySQL, you can use a statement something like this:
LOAD DATA INFILE '/local/access_log' INTO TABLE tbl_name
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'
The named table should be created to have columns that correspond to those that the LogFormat line
writes to the log file.
42
43
Appendix A Licenses for Third-Party Components
Table of Contents
A.1 ANTLR 3 License ....................................................................................................................... 45
A.2 dtoa.c License ............................................................................................................................ 45
A.3 Editline Library (libedit) License .................................................................................................. 46
A.4 FindGTest.cmake License ........................................................................................................... 48
A.5 Fred Fish's Dbug Library License ................................................................................................ 49
A.6 getarg License ........................................................................................................................... 50
A.7 GNU General Public License Version 2.0, June 1991 ................................................................... 50
A.8 GNU Lesser General Public License Version 2.1, February 1999 .................................................. 56
A.9 GNU Libtool License .................................................................................................................. 64
A.10 GNU Readline License ............................................................................................................. 64
A.11 Google Controlling Master Thread I/O Rate Patch License ......................................................... 65
A.12 Google Perftools (TCMalloc utility) License ................................................................................ 65
A.13 Google SMP Patch License ...................................................................................................... 66
A.14 lib_sql.cc License ..................................................................................................................... 67
A.15 libevent License ........................................................................................................................ 67
A.16 Linux-PAM License ................................................................................................................... 69
A.17 md5 (Message-Digest Algorithm 5) License ............................................................................... 70
A.18 memcached License ................................................................................................................. 70
A.19 nt_servc (Windows NT Service class library) License ................................................................. 71
A.20 OpenPAM License .................................................................................................................... 71
A.21 Paramiko License ..................................................................................................................... 71
A.22 Percona Multiple I/O Threads Patch License .............................................................................. 72
A.23 RegEX-Spencer Library License ................................................................................................ 72
A.24 RFC 3174 - US Secure Hash Algorithm 1 (SHA1) License ......................................................... 73
A.25 Richard A. O'Keefe String Library License ................................................................................. 73
A.26 SHA-1 in C License .................................................................................................................. 74
A.27 zlib License .............................................................................................................................. 74
The following is a list of the libraries we have included with the MySQL Server source and components
used to test MySQL. We are thankful to all individuals that have created these. Some of the components
require that their licensing terms be included in the documentation of products that include them. Cross
references to these licensing terms are given with the applicable items in the list.
• Bjorn Benson
For his safe_malloc (memory checker) package which is used in when you build MySQL using one of the
BUILD/compile-*-debug scripts or by manually setting the -DSAFEMALLOC flag.
• GroupLens Research Project
The MySQL Quality Assurance team would like to acknowledge the use of the MovieLens Data Sets (10
million ratings and 100,000 tags for 10681 movies by 71567 users) to help test MySQL products and to
thank the GroupLens Research Project at the University of Minnesota for making the data sets available.
MySQL 5.1
• Section A.2, “dtoa.c License”
• Section A.3, “Editline Library (libedit) License”
MySQL Cluster 7.1
44
• Section A.4, “FindGTest.cmake License”
• Section A.5, “Fred Fish's Dbug Library License”
• Section A.6, “getarg License”
• Section A.7, “GNU General Public License Version 2.0, June 1991”
• Section A.9, “GNU Libtool License”
• Section A.10, “GNU Readline License”
• Section A.11, “Google Controlling Master Thread I/O Rate Patch License”
• Section A.13, “Google SMP Patch License”
• Section A.14, “lib_sql.cc License”
• Section A.17, “md5 (Message-Digest Algorithm 5) License”
• Section A.19, “nt_servc (Windows NT Service class library) License”
• Section A.22, “Percona Multiple I/O Threads Patch License”
• Section A.23, “RegEX-Spencer Library License”
• Section A.24, “RFC 3174 - US Secure Hash Algorithm 1 (SHA1) License”
• Section A.25, “Richard A. O'Keefe String Library License”
• Section A.27, “zlib License”
MySQL Cluster 7.1
• Section A.1, “ANTLR 3 License”
• Section A.2, “dtoa.c License”
• Section A.3, “Editline Library (libedit) License”
• Section A.4, “FindGTest.cmake License”
• Section A.5, “Fred Fish's Dbug Library License”
• Section A.6, “getarg License”
• Section A.7, “GNU General Public License Version 2.0, June 1991”
• Section A.8, “GNU Lesser General Public License Version 2.1, February 1999”
• Section A.9, “GNU Libtool License”
• Section A.10, “GNU Readline License”
• Section A.11, “Google Controlling Master Thread I/O Rate Patch License”
• Section A.12, “Google Perftools (TCMalloc utility) License”
• Section A.13, “Google SMP Patch License”
• Section A.14, “lib_sql.cc License”
• Section A.15, “libevent License”
ANTLR 3 License
45
• Section A.16, “Linux-PAM License”
• Section A.17, “md5 (Message-Digest Algorithm 5) License”
• Section A.18, “memcached License”
• Section A.19, “nt_servc (Windows NT Service class library) License”
• Section A.20, “OpenPAM License”
• Section A.21, “Paramiko License”
• Section A.22, “Percona Multiple I/O Threads Patch License”
• Section A.23, “RegEX-Spencer Library License”
• Section A.25, “Richard A. O'Keefe String Library License”
• Section A.26, “SHA-1 in C License”
• Section A.27, “zlib License”
A.1 ANTLR 3 License
The following software may be included in this product:
ANTLR 3
ANTLR 3 License
[The BSD License]
Copyright (c) 2003-2007, Terence Parr
All rights reserved.
Redistribution and use in source and binary forms, with or without
modification, are permitted provided that the following conditions
are met:
* Redistributions of source code must retain the above copyright
notice, this list of conditions and the following disclaimer.
* Redistributions in binary form must reproduce the above copyright
notice, this list of conditions and the following disclaimer in
the documentation and/or other materials provided with the
distribution.
* Neither the name of the author nor the names of its contributors
may be used to endorse or promote products derived from this
software without specific prior written permission.
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
"AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS
FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE
COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,
INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN
ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
POSSIBILITY OF SUCH DAMAGE.
A.2 dtoa.c License
The following software may be included in this product:
Editline Library (libedit) License
46
dtoa.c
The author of this software is David M. Gay.
Copyright (c) 1991, 2000, 2001 by Lucent Technologies.
Permission to use, copy, modify, and distribute this software for
any purpose without fee is hereby granted, provided that this entire
notice is included in all copies of any software which is or includes
a copy or modification of this software and in all copies of the
supporting documentation for such software.
THIS SOFTWARE IS BEING PROVIDED "AS IS", WITHOUT ANY EXPRESS OR
IMPLIED WARRANTY. IN PARTICULAR, NEITHER THE AUTHOR NOR LUCENT
MAKES ANY REPRESENTATION OR WARRANTY OF ANY KIND CONCERNING THE
MERCHANTABILITY OF THIS SOFTWARE OR ITS FITNESS FOR ANY PARTICULAR
PURPOSE.
A.3 Editline Library (libedit) License
The following software may be included in this product:
Editline Library (libedit)
Some files are:
Copyright (c) 1992, 1993
The Regents of the University of California. All rights reserved.
This code is derived from software contributed to
Berkeley by Christos Zoulas of Cornell University.
Redistribution and use in source and binary forms,
with or without modification, are permitted provided
that the following conditions are met:
1. Redistributions of source code must retain the
above copyright notice, this list of conditions
and the following disclaimer.
2. Redistributions in binary form must reproduce the
above copyright notice, this list of conditions and
the following disclaimer in the documentation and/or
other materials provided with the distribution.
3. Neither the name of the University nor the names of
its contributors may be used to endorse or promote
products derived from this software without specific
prior written permission.
THIS SOFTWARE IS PROVIDED BY THE REGENTS AND CONTRIBUTORS
"AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING,
BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO
EVENT SHALL THE REGENTS OR CONTRIBUTORS BE LIABLE FOR ANY
DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED
AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING
IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED
OF THE POSSIBILITY OF SUCH DAMAGE.
Some files are:
Editline Library (libedit) License
47
Copyright (c) 2001 The NetBSD Foundation, Inc.
All rights reserved.
This code is derived from software contributed to The NetBSD Foundation
by Anthony Mallet.
Redistribution and use in source and binary forms,
with or without modification, are permitted provided
that the following conditions are met:
1. Redistributions of source code must retain the
above copyright notice, this list of conditions
and the following disclaimer.
2. Redistributions in binary form must reproduce the
above copyright notice, this list of conditions and the
following disclaimer in the documentation and/or
other materials provided with the distribution.
THIS SOFTWARE IS PROVIDED BY THE NETBSD FOUNDATION, INC.
AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED
WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A
PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL
THE FOUNDATION OR CONTRIBUTORS BE LIABLE FOR ANY
DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE
OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH
DAMAGE.
Some files are:
Copyright (c) 1997 The NetBSD Foundation, Inc.
All rights reserved.
This code is derived from software contributed to The NetBSD Foundation
by Jaromir Dolecek.
Redistribution and use in source and binary forms,
with or without modification, are permitted provided
that the following conditions are met:
1. Redistributions of source code must retain the
above copyright notice, this list of conditions
and the following disclaimer.
2. Redistributions in binary form must reproduce
the above copyright notice, this list of conditions
and the following disclaimer in the documentation
and/or other materials provided with the distribution.
THIS SOFTWARE IS PROVIDED BY THE NETBSD FOUNDATION, INC.
AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED
WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A
PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL
THE FOUNDATION OR CONTRIBUTORS BE LIABLE FOR ANY
DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
FindGTest.cmake License
48
USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE
OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH
DAMAGE.
Some files are:
Copyright (c) 1998 Todd C. Miller <Todd.Miller@courtesan.com>
Permission to use, copy, modify, and distribute this
software for any purpose with or without fee is hereby
granted, provided that the above copyright notice and
this permission notice appear in all copies.
THE SOFTWARE IS PROVIDED "AS IS" AND TODD C. MILLER
DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE
INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY
AND FITNESS. IN NO EVENT SHALL TODD C. MILLER BE LIABLE
FOR ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL
DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM
LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION
OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION,
ARISING OUT OF OR IN CONNECTION WITH THE USE OR
PERFORMANCE OF THIS SOFTWARE.
A.4 FindGTest.cmake License
The following software may be included in this product:
FindGTest.cmake helper script (part of CMake)
Copyright 2009 Kitware, Inc.
Copyright 2009 Philip Lowman
Copyright 2009 Daniel Blezek
Distributed under the OSI-approved BSD License (the "License");
see accompanying file Copyright.txt for details.
This software is distributed WITHOUT ANY WARRANTY; without even the
implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
See the License for more information.
==========================================================================
(To distributed this file outside of CMake, substitute the full
License text for the above reference.)
Thanks to Daniel Blezek for the GTEST_ADD_TESTS code
Text of Copyright.txt mentioned above:
CMake - Cross Platform Makefile Generator
Copyright 2000-2009 Kitware, Inc., Insight Software Consortium
All rights reserved.
Redistribution and use in source and binary forms, with or without
modification, are permitted provided that the following conditions
are met:
* Redistributions of source code must retain the above copyright
notice, this list of conditions and the following disclaimer.
* Redistributions in binary form must reproduce the above copyright
notice, this list of conditions and the following disclaimer in the
Fred Fish's Dbug Library License
49
documentation and/or other materials provided with the distribution.
* Neither the names of Kitware, Inc., the Insight Software Consortium,
nor the names of their contributors may be used to endorse or promote
products derived from this software without specific prior written
permission.
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
"AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
(INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
A.5 Fred Fish's Dbug Library License
The following software may be included in this product:
Fred Fish's Dbug Library
N O T I C E
Copyright Abandoned, 1987, Fred Fish
This previously copyrighted work has been placed into the public
domain by the author and may be freely used for any purpose,
private or commercial.
Because of the number of inquiries I was receiving about the use
of this product in commercially developed works I have decided to
simply make it public domain to further its unrestricted use. I
specifically would be most happy to see this material become a
part of the standard Unix distributions by AT&T and the Berkeley
Computer Science Research Group, and a standard part of the GNU
system from the Free Software Foundation.
I would appreciate it, as a courtesy, if this notice is left in
all copies and derivative works. Thank you.
The author makes no warranty of any kind with respect to this
getarg License
50
product and explicitly disclaims any implied warranties of merchantability
or fitness for any particular purpose.
The dbug_analyze.c file is subject to the following notice:
Copyright June 1987, Binayak Banerjee
All rights reserved.
This program may be freely distributed under the same terms and
conditions as Fred Fish's Dbug package.
A.6 getarg License
The following software may be included in this product:
getarg Function (getarg.h, getarg.c files)
Copyright (c) 1997 – 2000 Kungliga Tekniska Högskolan
(Royal Institute of Technology, Stockholm, Sweden).
All rights reserved.
Redistribution and use in source and binary forms, with
or without modification, are permitted provided that the
following conditions are met:
1. Redistributions of source code must retain the above
copyright notice, this list of conditions and the
following disclaimer.
2. Redistributions in binary form must reproduce the above
copyright notice, this list of conditions and the following
disclaimer in the documentation and/or other materials
provided with the distribution.
3. Neither the name of the Institute nor the names of its
contributors may be used to endorse or promote products
derived from this software without specific prior written
permission.
THIS SOFTWARE IS PROVIDED BY THE INSTITUTE AND CONTRIBUTORS
"AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING,
BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO
EVENT SHALL THE INSTITUTE OR CONTRIBUTORS BE LIABLE FOR ANY
DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE
GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY,
WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING
NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF
THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH
DAMAGE.
A.7 GNU General Public License Version 2.0, June 1991
The following applies to all products licensed under the GNU General
Public License, Version 2.0: You may not use the identified files
except in compliance with the GNU General Public License, Version
2.0 (the "License.") You may obtain a copy of the License at
http://www.gnu.org/licenses/gpl-2.0.txt. A copy of the license is
also reproduced below. Unless required by applicable law or agreed
to in writing, software distributed under the License is distributed
on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND,
either express or implied. See the License for the specific language
GNU General Public License Version 2.0, June 1991
51
governing permissions and limitations under the License.
GNU GENERAL PUBLIC LICENSE
Version 2, June 1991
Copyright (C) 1989, 1991 Free Software Foundation, Inc.,
51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
Everyone is permitted to copy and distribute verbatim
copies of this license document, but changing it is not
allowed.
Preamble
The licenses for most software are designed to take away your
freedom to share and change it. By contrast, the GNU General Public
License is intended to guarantee your freedom to share and change free
software--to make sure the software is free for all its users. This
General Public License applies to most of the Free Software
Foundation's software and to any other program whose authors commit to
using it. (Some other Free Software Foundation software is covered by
the GNU Lesser General Public License instead.) You can apply it to
your programs, too.
When we speak of free software, we are referring to freedom, not
price. Our General Public Licenses are designed to make sure that you
have the freedom to distribute copies of free software (and charge for
this service if you wish), that you receive source code or can get it
if you want it, that you can change the software or use pieces of it
in new free programs; and that you know you can do these things.
To protect your rights, we need to make restrictions that forbid
anyone to deny you these rights or to ask you to surrender the rights.
These restrictions translate to certain responsibilities for you if you
distribute copies of the software, or if you modify it.
For example, if you distribute copies of such a program, whether
gratis or for a fee, you must give the recipients all the rights that
you have. You must make sure that they, too, receive or can get the
source code. And you must show them these terms so they know their
rights.
We protect your rights with two steps: (1) copyright the software,
and (2) offer you this license which gives you legal permission to
copy, distribute and/or modify the software.
Also, for each author's protection and ours, we want to make certain
that everyone understands that there is no warranty for this free
software. If the software is modified by someone else and passed on,
we want its recipients to know that what they have is not the original,
so that any problems introduced by others will not reflect on the
original authors' reputations.
Finally, any free program is threatened constantly by software
patents. We wish to avoid the danger that redistributors of a free
program will individually obtain patent licenses, in effect making the
program proprietary. To prevent this, we have made it clear that any
patent must be licensed for everyone's free use or not licensed at all.
The precise terms and conditions for copying, distribution and
modification follow.
GNU GENERAL PUBLIC LICENSE
TERMS AND CONDITIONS FOR COPYING, DISTRIBUTION AND MODIFICATION
0. This License applies to any program or other work which contains
a notice placed by the copyright holder saying it may be distributed
under the terms of this General Public License. The "Program", below,
GNU General Public License Version 2.0, June 1991
52
refers to any such program or work, and a "work based on the Program"
means either the Program or any derivative work under copyright law:
that is to say, a work containing the Program or a portion of it,
either verbatim or with modifications and/or translated into another
language. (Hereinafter, translation is included without limitation in
the term "modification".) Each licensee is addressed as "you".
Activities other than copying, distribution and modification are not
covered by this License; they are outside its scope. The act of
running the Program is not restricted, and the output from the Program
is covered only if its contents constitute a work based on the
Program (independent of having been made by running the Program).
Whether that is true depends on what the Program does.
1. You may copy and distribute verbatim copies of the Program's
source code as you receive it, in any medium, provided that you
conspicuously and appropriately publish on each copy an appropriate
copyright notice and disclaimer of warranty; keep intact all the
notices that refer to this License and to the absence of any warranty;
and give any other recipients of the Program a copy of this License
along with the Program.
You may charge a fee for the physical act of transferring a copy, and
you may at your option offer warranty protection in exchange for a fee.
2. You may modify your copy or copies of the Program or any portion
of it, thus forming a work based on the Program, and copy and
distribute such modifications or work under the terms of Section 1
above, provided that you also meet all of these conditions:
a) You must cause the modified files to carry prominent notices
stating that you changed the files and the date of any change.
b) You must cause any work that you distribute or publish, that in
whole or in part contains or is derived from the Program or any
part thereof, to be licensed as a whole at no charge to all third
parties under the terms of this License.
c) If the modified program normally reads commands interactively
when run, you must cause it, when started running for such
interactive use in the most ordinary way, to print or display an
announcement including an appropriate copyright notice and a
notice that there is no warranty (or else, saying that you provide
a warranty) and that users may redistribute the program under
these conditions, and telling the user how to view a copy of this
License. (Exception: if the Program itself is interactive but
does not normally print such an announcement, your work based on
the Program is not required to print an announcement.)
These requirements apply to the modified work as a whole. If
identifiable sections of that work are not derived from the Program,
and can be reasonably considered independent and separate works in
themselves, then this License, and its terms, do not apply to those
sections when you distribute them as separate works. But when you
distribute the same sections as part of a whole which is a work based
on the Program, the distribution of the whole must be on the terms of
this License, whose permissions for other licensees extend to the
entire whole, and thus to each and every part regardless of who wrote it.
Thus, it is not the intent of this section to claim rights or contest
your rights to work written entirely by you; rather, the intent is to
exercise the right to control the distribution of derivative or
collective works based on the Program.
In addition, mere aggregation of another work not based on the Program
with the Program (or with a work based on the Program) on a volume of
a storage or distribution medium does not bring the other work under
GNU General Public License Version 2.0, June 1991
53
the scope of this License.
3. You may copy and distribute the Program (or a work based on it,
under Section 2) in object code or executable form under the terms of
Sections 1 and 2 above provided that you also do one of the following:
a) Accompany it with the complete corresponding machine-readable
source code, which must be distributed under the terms of Sections
1 and 2 above on a medium customarily used for software
interchange; or,
b) Accompany it with a written offer, valid for at least three
years, to give any third party, for a charge no more than your
cost of physically performing source distribution, a complete
machine-readable copy of the corresponding source code, to be
distributed under the terms of Sections 1 and 2 above on a medium
customarily used for software interchange; or,
c) Accompany it with the information you received as to the offer
to distribute corresponding source code. (This alternative is
allowed only for noncommercial distribution and only if you
received the program in object code or executable form with such
an offer, in accord with Subsection b above.)
The source code for a work means the preferred form of the work for
making modifications to it. For an executable work, complete source
code means all the source code for all modules it contains, plus any
associated interface definition files, plus the scripts used to
control compilation and installation of the executable. However, as
a special exception, the source code distributed need not include
anything that is normally distributed (in either source or binary
form) with the major components (compiler, kernel, and so on) of the
operating system on which the executable runs, unless that component
itself accompanies the executable.
If distribution of executable or object code is made by offering
access to copy from a designated place, then offering equivalent
access to copy the source code from the same place counts as
distribution of the source code, even though third parties are not
compelled to copy the source along with the object code.
4. You may not copy, modify, sublicense, or distribute the Program
except as expressly provided under this License. Any attempt
otherwise to copy, modify, sublicense or distribute the Program is
void, and will automatically terminate your rights under this License.
However, parties who have received copies, or rights, from you under
this License will not have their licenses terminated so long as such
parties remain in full compliance.
5. You are not required to accept this License, since you have not
signed it. However, nothing else grants you permission to modify or
distribute the Program or its derivative works. These actions are
prohibited by law if you do not accept this License. Therefore, by
modifying or distributing the Program (or any work based on the
Program), you indicate your acceptance of this License to do so, and
all its terms and conditions for copying, distributing or modifying
the Program or works based on it.
6. Each time you redistribute the Program (or any work based on the
Program), the recipient automatically receives a license from the
original licensor to copy, distribute or modify the Program subject to
these terms and conditions. You may not impose any further
restrictions on the recipients' exercise of the rights granted herein.
You are not responsible for enforcing compliance by third parties to
this License.
7. If, as a consequence of a court judgment or allegation of patent
GNU General Public License Version 2.0, June 1991
54
infringement or for any other reason (not limited to patent issues),
conditions are imposed on you (whether by court order, agreement or
otherwise) that contradict the conditions of this License, they do not
excuse you from the conditions of this License. If you cannot
distribute so as to satisfy simultaneously your obligations under this
License and any other pertinent obligations, then as a consequence you
may not distribute the Program at all. For example, if a patent
license would not permit royalty-free redistribution of the Program by
all those who receive copies directly or indirectly through you, then
the only way you could satisfy both it and this License would be to
refrain entirely from distribution of the Program.
If any portion of this section is held invalid or unenforceable under
any particular circumstance, the balance of the section is intended to
apply and the section as a whole is intended to apply in other
circumstances.
It is not the purpose of this section to induce you to infringe any
patents or other property right claims or to contest validity of any
such claims; this section has the sole purpose of protecting the
integrity of the free software distribution system, which is
implemented by public license practices. Many people have made
generous contributions to the wide range of software distributed
through that system in reliance on consistent application of that
system; it is up to the author/donor to decide if he or she is willing
to distribute software through any other system and a licensee cannot
impose that choice.
This section is intended to make thoroughly clear what is believed to
be a consequence of the rest of this License.
8. If the distribution and/or use of the Program is restricted in
certain countries either by patents or by copyrighted interfaces, the
original copyright holder who places the Program under this License
may add an explicit geographical distribution limitation excluding
those countries, so that distribution is permitted only in or among
countries not thus excluded. In such case, this License incorporates
the limitation as if written in the body of this License.
9. The Free Software Foundation may publish revised and/or new
versions of the General Public License from time to time. Such new
versions will be similar in spirit to the present version, but may
differ in detail to address new problems or concerns.
Each version is given a distinguishing version number. If the Program
specifies a version number of this License which applies to it and
"any later version", you have the option of following the terms and
conditions either of that version or of any later version published by
the Free Software Foundation. If the Program does not specify a
version number of this License, you may choose any version ever
published by the Free Software Foundation.
10. If you wish to incorporate parts of the Program into other free
programs whose distribution conditions are different, write to the
author to ask for permission. For software which is copyrighted by the
Free Software Foundation, write to the Free Software Foundation; we
sometimes make exceptions for this. Our decision will be guided by the
two goals of preserving the free status of all derivatives of our free
software and of promoting the sharing and reuse of software generally.
NO WARRANTY
11. BECAUSE THE PROGRAM IS LICENSED FREE OF CHARGE, THERE IS NO
WARRANTY FOR THE PROGRAM, TO THE EXTENT PERMITTED BY APPLICABLE LAW.
EXCEPT WHEN OTHERWISE STATED IN WRITING THE COPYRIGHT HOLDERS AND/OR
OTHER PARTIES PROVIDE THE PROGRAM "AS IS" WITHOUT WARRANTY OF ANY KIND,
EITHER EXPRESSED OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
GNU General Public License Version 2.0, June 1991
55
WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
THE ENTIRE RISK AS TO THE QUALITY AND PERFORMANCE OF THE PROGRAM IS
WITH YOU. SHOULD THE PROGRAM PROVE DEFECTIVE, YOU ASSUME THE COST OF
ALL NECESSARY SERVICING, REPAIR OR CORRECTION.
12. IN NO EVENT UNLESS REQUIRED BY APPLICABLE LAW OR AGREED TO IN
WRITING WILL ANY COPYRIGHT HOLDER, OR ANY OTHER PARTY WHO MAY MODIFY
AND/OR REDISTRIBUTE THE PROGRAM AS PERMITTED ABOVE, BE LIABLE TO YOU
FOR DAMAGES, INCLUDING ANY GENERAL, SPECIAL, INCIDENTAL OR
CONSEQUENTIAL DAMAGES ARISING OUT OF THE USE OR INABILITY TO USE THE
PROGRAM (INCLUDING BUT NOT LIMITED TO LOSS OF DATA OR DATA BEING
RENDERED INACCURATE OR LOSSES SUSTAINED BY YOU OR THIRD PARTIES OR A
FAILURE OF THE PROGRAM TO OPERATE WITH ANY OTHER PROGRAMS), EVEN IF
SUCH HOLDER OR OTHER PARTY HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH
DAMAGES.
END OF TERMS AND CONDITIONS
How to Apply These Terms to Your New Programs
If you develop a new program, and you want it to be of the greatest
possible use to the public, the best way to achieve this is to make it
free software which everyone can redistribute and change under these terms.
To do so, attach the following notices to the program. It is safest
to attach them to the start of each source file to most effectively
convey the exclusion of warranty; and each file should have at least
the "copyright" line and a pointer to where the full notice is found.
<one line to give the program's name and a brief idea of what it
does.>
Copyright (C) <year> <name of author>
This program is free software; you can redistribute it and/or
modify it under the terms of the GNU General Public License as
published by the Free Software Foundation; either version
2 of the License, or (at your option) any later version.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
02110-1301 USA.
Also add information on how to contact you by electronic and paper mail.
If the program is interactive, make it output a short notice like this
when it starts in an interactive mode:
Gnomovision version 69, Copyright (C) year name of author
Gnomovision comes with ABSOLUTELY NO WARRANTY; for details
type 'show w'. This is free software, and you are welcome
to redistribute it under certain conditions; type 'show c'
for details.
The hypothetical commands 'show w' and 'show c' should show the
appropriate parts of the General Public License. Of course, the
commands you use may be called something other than 'show w' and
'show c'; they could even be mouse-clicks or menu items--whatever
suits your program.
You should also get your employer (if you work as a programmer) or your
school, if any, to sign a "copyright disclaimer" for the program, if
GNU Lesser General Public License Version 2.1, February 1999
56
necessary. Here is a sample; alter the names:
Yoyodyne, Inc., hereby disclaims all copyright interest in the
program 'Gnomovision' (which makes passes at compilers) written
by James Hacker.
<signature of Ty Coon>, 1 April 1989
Ty Coon, President of Vice
This General Public License does not permit incorporating your program
into proprietary programs. If your program is a subroutine library,
you may consider it more useful to permit linking proprietary
applications with the library. If this is what you want to do, use
the GNU Lesser General Public License instead of this License.
A.8 GNU Lesser General Public License Version 2.1, February 1999
The following applies to all products licensed under the
GNU Lesser General Public License, Version 2.1: You may
not use the identified files except in compliance with
the GNU Lesser General Public License, Version 2.1 (the
"License"). You may obtain a copy of the License at
http://www.gnu.org/licenses/lgpl-2.1.html. A copy of the
license is also reproduced below. Unless required by
applicable law or agreed to in writing, software distributed
under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express
or implied. See the License for the specific language governing
permissions and limitations under the License.
GNU LESSER GENERAL PUBLIC LICENSE
Version 2.1, February 1999
Copyright (C) 1991, 1999 Free Software Foundation, Inc.
51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
Everyone is permitted to copy and distribute verbatim copies
of this license document, but changing it is not allowed.
[This is the first released version of the Lesser GPL. It also counts
as the successor of the GNU Library Public License, version 2, hence
the version number 2.1.]
Preamble
The licenses for most software are designed to take away your
freedom to share and change it. By contrast, the GNU General Public
Licenses are intended to guarantee your freedom to share and change
free software--to make sure the software is free for all its users.
This license, the Lesser General Public License, applies to some
specially designated software packages--typically libraries--of the
Free Software Foundation and other authors who decide to use it. You
can use it too, but we suggest you first think carefully about whether
this license or the ordinary General Public License is the better
strategy to use in any particular case, based on the explanations below.
When we speak of free software, we are referring to freedom of use,
not price. Our General Public Licenses are designed to make sure that
you have the freedom to distribute copies of free software (and charge
for this service if you wish); that you receive source code or can get
it if you want it; that you can change the software and use pieces of
it in new free programs; and that you are informed that you can do
these things.
To protect your rights, we need to make restrictions that forbid
GNU Lesser General Public License Version 2.1, February 1999
57
distributors to deny you these rights or to ask you to surrender these
rights. These restrictions translate to certain responsibilities for
you if you distribute copies of the library or if you modify it.
For example, if you distribute copies of the library, whether gratis
or for a fee, you must give the recipients all the rights that we gave
you. You must make sure that they, too, receive or can get the source
code. If you link other code with the library, you must provide
complete object files to the recipients, so that they can relink them
with the library after making changes to the library and recompiling
it. And you must show them these terms so they know their rights.
We protect your rights with a two-step method: (1) we copyright the
library, and (2) we offer you this license, which gives you legal
permission to copy, distribute and/or modify the library.
To protect each distributor, we want to make it very clear that
there is no warranty for the free library. Also, if the library is
modified by someone else and passed on, the recipients should know
that what they have is not the original version, so that the original
author's reputation will not be affected by problems that might be
introduced by others.
Finally, software patents pose a constant threat to the existence of
any free program. We wish to make sure that a company cannot
effectively restrict the users of a free program by obtaining a
restrictive license from a patent holder. Therefore, we insist that
any patent license obtained for a version of the library must be
consistent with the full freedom of use specified in this license.
Most GNU software, including some libraries, is covered by the
ordinary GNU General Public License. This license, the GNU Lesser
General Public License, applies to certain designated libraries, and
is quite different from the ordinary General Public License. We use
this license for certain libraries in order to permit linking those
libraries into non-free programs.
When a program is linked with a library, whether statically or using
a shared library, the combination of the two is legally speaking a
combined work, a derivative of the original library. The ordinary
General Public License therefore permits such linking only if the
entire combination fits its criteria of freedom. The Lesser General
Public License permits more lax criteria for linking other code with
the library.
We call this license the "Lesser" General Public License because it
does Less to protect the user's freedom than the ordinary General
Public License. It also provides other free software developers Less
of an advantage over competing non-free programs. These disadvantages
are the reason we use the ordinary General Public License for many
libraries. However, the Lesser license provides advantages in certain
special circumstances.
For example, on rare occasions, there may be a special need to
encourage the widest possible use of a certain library, so that it
becomes a de-facto standard. To achieve this, non-free programs
must be allowed to use the library. A more frequent case is that
a free library does the same job as widely used non-free libraries.
In this case, there is little to gain by limiting the free library
to free software only, so we use the Lesser General Public License.
In other cases, permission to use a particular library in non-free
programs enables a greater number of people to use a large body of
free software. For example, permission to use the GNU C Library in
non-free programs enables many more people to use the whole GNU
operating system, as well as its variant, the GNU/Linux operating
system.
GNU Lesser General Public License Version 2.1, February 1999
58
Although the Lesser General Public License is Less protective of the
users' freedom, it does ensure that the user of a program that is
linked with the Library has the freedom and the wherewithal to run
that program using a modified version of the Library.
The precise terms and conditions for copying, distribution and
modification follow. Pay close attention to the difference between a
"work based on the library" and a "work that uses the library". The
former contains code derived from the library, whereas the latter must
be combined with the library in order to run.
GNU LESSER GENERAL PUBLIC LICENSE
TERMS AND CONDITIONS FOR COPYING, DISTRIBUTION AND MODIFICATION
0. This License Agreement applies to any software library or other
program which contains a notice placed by the copyright holder or
other authorized party saying it may be distributed under the terms of
this Lesser General Public License (also called "this License").
Each licensee is addressed as "you".
A "library" means a collection of software functions and/or data
prepared so as to be conveniently linked with application programs
(which use some of those functions and data) to form executables.
The "Library", below, refers to any such software library or work
which has been distributed under these terms. A "work based on the
Library" means either the Library or any derivative work under
copyright law: that is to say, a work containing the Library or a
portion of it, either verbatim or with modifications and/or translated
straightforwardly into another language. (Hereinafter, translation is
included without limitation in the term "modification".)
"Source code" for a work means the preferred form of the work for
making modifications to it. For a library, complete source code means
all the source code for all modules it contains, plus any associated
interface definition files, plus the scripts used to control
compilation and installation of the library.
Activities other than copying, distribution and modification are not
covered by this License; they are outside its scope. The act of
running a program using the Library is not restricted, and output from
such a program is covered only if its contents constitute a work based
on the Library (independent of the use of the Library in a tool for
writing it). Whether that is true depends on what the Library does
and what the program that uses the Library does.
1. You may copy and distribute verbatim copies of the Library's
complete source code as you receive it, in any medium, provided that
you conspicuously and appropriately publish on each copy an
appropriate copyright notice and disclaimer of warranty; keep intact
all the notices that refer to this License and to the absence of any
warranty; and distribute a copy of this License along with the
Library.
You may charge a fee for the physical act of transferring a copy,
and you may at your option offer warranty protection in exchange for a
fee.
2. You may modify your copy or copies of the Library or any portion
of it, thus forming a work based on the Library, and copy and
distribute such modifications or work under the terms of Section 1
above, provided that you also meet all of these conditions:
a) The modified work must itself be a software library.
b) You must cause the files modified to carry prominent notices
GNU Lesser General Public License Version 2.1, February 1999
59
stating that you changed the files and the date of any change.
c) You must cause the whole of the work to be licensed at no
charge to all third parties under the terms of this License.
d) If a facility in the modified Library refers to a function or a
table of data to be supplied by an application program that uses
the facility, other than as an argument passed when the facility
is invoked, then you must make a good faith effort to ensure that,
in the event an application does not supply such function or
table, the facility still operates, and performs whatever part of
its purpose remains meaningful.
(For example, a function in a library to compute square roots has
a purpose that is entirely well-defined independent of the
application. Therefore, Subsection 2d requires that any
application-supplied function or table used by this function must
be optional: if the application does not supply it, the square
root function must still compute square roots.)
These requirements apply to the modified work as a whole. If
identifiable sections of that work are not derived from the Library,
and can be reasonably considered independent and separate works in
themselves, then this License, and its terms, do not apply to those
sections when you distribute them as separate works. But when you
distribute the same sections as part of a whole which is a work based
on the Library, the distribution of the whole must be on the terms of
this License, whose permissions for other licensees extend to the
entire whole, and thus to each and every part regardless of who wrote
it.
Thus, it is not the intent of this section to claim rights or contest
your rights to work written entirely by you; rather, the intent is to
exercise the right to control the distribution of derivative or
collective works based on the Library.
In addition, mere aggregation of another work not based on the Library
with the Library (or with a work based on the Library) on a volume of
a storage or distribution medium does not bring the other work under
the scope of this License.
3. You may opt to apply the terms of the ordinary GNU General Public
License instead of this License to a given copy of the Library. To do
this, you must alter all the notices that refer to this License, so
that they refer to the ordinary GNU General Public License, version 2,
instead of to this License. (If a newer version than version 2 of the
ordinary GNU General Public License has appeared, then you can specify
that version instead if you wish.) Do not make any other change in
these notices.
Once this change is made in a given copy, it is irreversible for
that copy, so the ordinary GNU General Public License applies to all
subsequent copies and derivative works made from that copy.
This option is useful when you wish to copy part of the code of
the Library into a program that is not a library.
4. You may copy and distribute the Library (or a portion or
derivative of it, under Section 2) in object code or executable form
under the terms of Sections 1 and 2 above provided that you accompany
it with the complete corresponding machine-readable source code, which
must be distributed under the terms of Sections 1 and 2 above on a
medium customarily used for software interchange.
If distribution of object code is made by offering access to copy
from a designated place, then offering equivalent access to copy the
source code from the same place satisfies the requirement to
GNU Lesser General Public License Version 2.1, February 1999
60
distribute the source code, even though third parties are not
compelled to copy the source along with the object code.
5. A program that contains no derivative of any portion of the
Library, but is designed to work with the Library by being compiled or
linked with it, is called a "work that uses the Library". Such a
work, in isolation, is not a derivative work of the Library, and
therefore falls outside the scope of this License.
However, linking a "work that uses the Library" with the Library
creates an executable that is a derivative of the Library (because it
contains portions of the Library), rather than a "work that uses the
library". The executable is therefore covered by this License.
Section 6 states terms for distribution of such executables.
When a "work that uses the Library" uses material from a header file
that is part of the Library, the object code for the work may be a
derivative work of the Library even though the source code is not.
Whether this is true is especially significant if the work can be
linked without the Library, or if the work is itself a library. The
threshold for this to be true is not precisely defined by law.
If such an object file uses only numerical parameters, data
structure layouts and accessors, and small macros and small inline
functions (ten lines or less in length), then the use of the object
file is unrestricted, regardless of whether it is legally a derivative
work. (Executables containing this object code plus portions of the
Library will still fall under Section 6.)
Otherwise, if the work is a derivative of the Library, you may
distribute the object code for the work under the terms of Section 6.
Any executables containing that work also fall under Section 6,
whether or not they are linked directly with the Library itself.
6. As an exception to the Sections above, you may also combine or
link a "work that uses the Library" with the Library to produce a
work containing portions of the Library, and distribute that work
under terms of your choice, provided that the terms permit
modification of the work for the customer's own use and reverse
engineering for debugging such modifications.
You must give prominent notice with each copy of the work that the
Library is used in it and that the Library and its use are covered by
this License. You must supply a copy of this License. If the work
during execution displays copyright notices, you must include the
copyright notice for the Library among them, as well as a reference
directing the user to the copy of this License. Also, you must do one
of these things:
a) Accompany the work with the complete corresponding
machine-readable source code for the Library including whatever
changes were used in the work (which must be distributed under
Sections 1 and 2 above); and, if the work is an executable linked
with the Library, with the complete machine-readable "work that
uses the Library", as object code and/or source code, so that the
user can modify the Library and then relink to produce a modified
executable containing the modified Library. (It is understood
that the user who changes the contents of definitions files in the
Library will not necessarily be able to recompile the application
to use the modified definitions.)
b) Use a suitable shared library mechanism for linking with the
Library. A suitable mechanism is one that (1) uses at run time a
copy of the library already present on the user's computer system,
rather than copying library functions into the executable, and (2)
will operate properly with a modified version of the library, if
the user installs one, as long as the modified version is
GNU Lesser General Public License Version 2.1, February 1999
61
interface-compatible with the version that the work was made with.
c) Accompany the work with a written offer, valid for at
least three years, to give the same user the materials
specified in Subsection 6a, above, for a charge no more
than the cost of performing this distribution.
d) If distribution of the work is made by offering access to copy
from a designated place, offer equivalent access to copy the above
specified materials from the same place.
e) Verify that the user has already received a copy of these
materials or that you have already sent this user a copy.
For an executable, the required form of the "work that uses the
Library" must include any data and utility programs needed for
reproducing the executable from it. However, as a special exception,
the materials to be distributed need not include anything that is
normally distributed (in either source or binary form) with the major
components (compiler, kernel, and so on) of the operating system on
which the executable runs, unless that component itself accompanies
the executable.
It may happen that this requirement contradicts the license
restrictions of other proprietary libraries that do not normally
accompany the operating system. Such a contradiction means you cannot
use both them and the Library together in an executable that you
distribute.
7. You may place library facilities that are a work based on the
Library side-by-side in a single library together with other library
facilities not covered by this License, and distribute such a combined
library, provided that the separate distribution of the work based on
the Library and of the other library facilities is otherwise
permitted, and provided that you do these two things:
a) Accompany the combined library with a copy of the same work
based on the Library, uncombined with any other library
facilities. This must be distributed under the terms of the
Sections above.
b) Give prominent notice with the combined library of the fact
that part of it is a work based on the Library, and explaining
where to find the accompanying uncombined form of the same work.
8. You may not copy, modify, sublicense, link with, or distribute
the Library except as expressly provided under this License. Any
attempt otherwise to copy, modify, sublicense, link with, or
distribute the Library is void, and will automatically terminate your
rights under this License. However, parties who have received copies,
or rights, from you under this License will not have their licenses
terminated so long as such parties remain in full compliance.
9. You are not required to accept this License, since you have not
signed it. However, nothing else grants you permission to modify or
distribute the Library or its derivative works. These actions are
prohibited by law if you do not accept this License. Therefore, by
modifying or distributing the Library (or any work based on the
Library), you indicate your acceptance of this License to do so, and
all its terms and conditions for copying, distributing or modifying
the Library or works based on it.
10. Each time you redistribute the Library (or any work based on the
Library), the recipient automatically receives a license from the
original licensor to copy, distribute, link with or modify the Library
subject to these terms and conditions. You may not impose any further
restrictions on the recipients' exercise of the rights granted herein.
GNU Lesser General Public License Version 2.1, February 1999
62
You are not responsible for enforcing compliance by third parties with
this License.
11. If, as a consequence of a court judgment or allegation of patent
infringement or for any other reason (not limited to patent issues),
conditions are imposed on you (whether by court order, agreement or
otherwise) that contradict the conditions of this License, they do not
excuse you from the conditions of this License. If you cannot
distribute so as to satisfy simultaneously your obligations under this
License and any other pertinent obligations, then as a consequence you
may not distribute the Library at all. For example, if a patent
license would not permit royalty-free redistribution of the Library by
all those who receive copies directly or indirectly through you, then
the only way you could satisfy both it and this License would be to
refrain entirely from distribution of the Library.
If any portion of this section is held invalid or unenforceable under
any particular circumstance, the balance of the section is intended
to apply, and the section as a whole is intended to apply in other
circumstances.
It is not the purpose of this section to induce you to infringe any
patents or other property right claims or to contest validity of any
such claims; this section has the sole purpose of protecting the
integrity of the free software distribution system which is
implemented by public license practices. Many people have made
generous contributions to the wide range of software distributed
through that system in reliance on consistent application of that
system; it is up to the author/donor to decide if he or she is willing
to distribute software through any other system and a licensee cannot
impose that choice.
This section is intended to make thoroughly clear what is believed to
be a consequence of the rest of this License.
12. If the distribution and/or use of the Library is restricted in
certain countries either by patents or by copyrighted interfaces, the
original copyright holder who places the Library under this License
may add an explicit geographical distribution limitation excluding
those countries, so that distribution is permitted only in or among
countries not thus excluded. In such case, this License incorporates
the limitation as if written in the body of this License.
13. The Free Software Foundation may publish revised and/or new
versions of the Lesser General Public License from time to time.
Such new versions will be similar in spirit to the present version,
but may differ in detail to address new problems or concerns.
Each version is given a distinguishing version number. If the Library
specifies a version number of this License which applies to it and
"any later version", you have the option of following the terms and
conditions either of that version or of any later version published by
the Free Software Foundation. If the Library does not specify a
license version number, you may choose any version ever published by
the Free Software Foundation.
14. If you wish to incorporate parts of the Library into other free
programs whose distribution conditions are incompatible with these,
write to the author to ask for permission. For software which is
copyrighted by the Free Software Foundation, write to the Free
Software Foundation; we sometimes make exceptions for this. Our
decision will be guided by the two goals of preserving the free status
of all derivatives of our free software and of promoting the sharing
and reuse of software generally.
NO WARRANTY
GNU Lesser General Public License Version 2.1, February 1999
63
15. BECAUSE THE LIBRARY IS LICENSED FREE OF CHARGE, THERE IS NO
WARRANTY FOR THE LIBRARY, TO THE EXTENT PERMITTED BY APPLICABLE LAW.
EXCEPT WHEN OTHERWISE STATED IN WRITING THE COPYRIGHT HOLDERS AND/OR
OTHER PARTIES PROVIDE THE LIBRARY "AS IS" WITHOUT WARRANTY OF ANY
KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE
IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
PURPOSE. THE ENTIRE RISK AS TO THE QUALITY AND PERFORMANCE OF THE
LIBRARY IS WITH YOU. SHOULD THE LIBRARY PROVE DEFECTIVE, YOU ASSUME
THE COST OF ALL NECESSARY SERVICING, REPAIR OR CORRECTION.
16. IN NO EVENT UNLESS REQUIRED BY APPLICABLE LAW OR AGREED TO IN
WRITING WILL ANY COPYRIGHT HOLDER, OR ANY OTHER PARTY WHO MAY MODIFY
AND/OR REDISTRIBUTE THE LIBRARY AS PERMITTED ABOVE, BE LIABLE TO YOU
FOR DAMAGES, INCLUDING ANY GENERAL, SPECIAL, INCIDENTAL OR
CONSEQUENTIAL DAMAGES ARISING OUT OF THE USE OR INABILITY TO USE THE
LIBRARY (INCLUDING BUT NOT LIMITED TO LOSS OF DATA OR DATA BEING
RENDERED INACCURATE OR LOSSES SUSTAINED BY YOU OR THIRD PARTIES OR A
FAILURE OF THE LIBRARY TO OPERATE WITH ANY OTHER SOFTWARE), EVEN IF
SUCH HOLDER OR OTHER PARTY HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH
DAMAGES.
END OF TERMS AND CONDITIONS
How to Apply These Terms to Your New Libraries
If you develop a new library, and you want it to be of the greatest
possible use to the public, we recommend making it free software that
everyone can redistribute and change. You can do so by permitting
redistribution under these terms (or, alternatively, under the terms
of the ordinary General Public License).
To apply these terms, attach the following notices to the library.
It is safest to attach them to the start of each source file to most
effectively convey the exclusion of warranty; and each file should
have at least the "copyright" line and a pointer to where the full
notice is found.
<one line to give the library's name and a brief idea of what it does.>
Copyright (C) <year> <name of author>
This library is free software; you can redistribute it and/or
modify it under the terms of the GNU Lesser General Public
License as published by the Free Software Foundation; either
version 2.1 of the License, or (at your option) any later version.
This library is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
Lesser General Public License for more details.
You should have received a copy of the GNU Lesser General Public
License along with this library; if not, write to the Free Software
Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
02110-1301 USA
Also add information on how to contact you by electronic and paper mail.
You should also get your employer (if you work as a programmer) or your
school, if any, to sign a "copyright disclaimer" for the library, if
necessary. Here is a sample; alter the names:
Yoyodyne, Inc., hereby disclaims all copyright interest in the
library `Frob' (a library for tweaking knobs) written by James
Random Hacker.
<signature of Ty Coon>, 1 April 1990
Ty Coon, President of Vice
GNU Libtool License
64
That's all there is to it!
A.9 GNU Libtool License
The following software may be included in this product:
GNU Libtool (The GNU Portable Library Tool)
If you are receiving a copy of the Oracle software in
source code, you are also receiving a copy of two files
(ltmain.sh and ltdl.h) generated by the GNU Libtool in
source code. If you received the Oracle software under
a license other than a commercial (non-GPL) license,
then the terms of the Oracle license do NOT apply to
these files from GNU Libtool; they are licensed under
the following licenses, separately from the Oracle
programs you receive.
Oracle elects to use GNU General Public License version
2 (GPL) for any software where a choice of GPL or GNU
Lesser/Library General Public License (LGPL) license
versions are made available with the language indicating
that GPL/LGPL or any later version may be used, or where
a choice of which version of the GPL/LGPL is applied is
unspecified.
From GNU Libtool:
ltmain.sh - Provide generalized library-building support
services.
NOTE: Changing this file will not affect anything until
you rerun configure.
Copyright (C) 1996, 1997, 1998, 1999, 2000, 2001, 2003, 2004,
2005, 2006, 2007 Free Software Foundation, Inc.
Originally by Gordon Matzigkeit, 1996
This program is free software; you can redistribute it
and/or modify it under the terms of the GNU General
Public License as published by the Free Software Foundation;
either version 2 of the License, or (at your option) any
later version.
This program is distributed in the hope that it will be
useful, but WITHOUT ANY WARRANTY; without even the implied
warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR
PURPOSE. See the GNU General Public License for more
details. You should have received a copy of the GNU General
Public License along with this program; if not, write to
the Free Software Foundation, Inc., 51 Franklin Street,
Fifth Floor, Boston, MA 02110-1301, USA.
As a special exception to the GNU General Public License,
if you distribute this file as part of a program that
contains a configuration script generated by Autoconf,
you may include it under the same distribution terms that
you use for the rest of that program.
This component is licensed under Section A.7, “GNU General Public License Version 2.0, June 1991”
A.10 GNU Readline License
The following software may be included in this product:
Google Controlling Master Thread I/O Rate Patch License
65
GNU Readline Library
GNU Readline Library
With respect to MySQL Server/Cluster software licensed
under GNU General Public License, you are receiving a
copy of the GNU Readline Library in source code. The
terms of any Oracle license that might accompany the
Oracle programs do NOT apply to the GNU Readline Library;
it is licensed under the following license, separately
from the Oracle programs you receive. Oracle elects to
use GNU General Public License version 2 (GPL) for any
software where a choice of GPL license versions are
made available with the language indicating that GPLv2
or any later version may be used, or where a choice of
which version of the GPL is applied is unspecified.
This component is licensed under Section A.7, “GNU General Public License Version 2.0, June 1991”
A.11 Google Controlling Master Thread I/O Rate Patch License
The following software may be included in this product:
Google Controlling master thread I/O rate patch
Copyright (c) 2009, Google Inc.
All rights reserved.
Redistribution and use in source and binary forms, with or without
modification, are permitted provided that the following conditions
are met:
* Redistributions of source code must retain the above copyright
notice, this list of conditions and the following disclaimer.
* Redistributions in binary form must reproduce the above copyright
notice, this list of conditions and the following disclaimer in the
documentation and/or other materials provided with the distribution.
* Neither the name of the Google Inc. nor the names of its contributors
may be used to endorse or promote products derived from this software
without specific prior written permission.
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
"AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS
FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE
COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,
INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN
ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
POSSIBILITY OF SUCH DAMAGE.
A.12 Google Perftools (TCMalloc utility) License
The following software may be included in this product:
Google Perftools (TCMalloc utility)
Copyright (c) 1998-2006, Google Inc.
All rights reserved.
Google SMP Patch License
66
Redistribution and use in source and binary forms, with or
without modification, are permitted provided that the following
conditions are met:
* Redistributions of source code must retain the above
copyright notice, this list of conditions and the following
disclaimer.
* Redistributions in binary form must reproduce the above
copyright notice, this list of conditions and the following
disclaimer in the documentation and/or other materials provided
with the distribution.
* Neither the name of Google Inc. nor the names of its
contributors may be used to endorse or promote products
derived from this software without specific prior written
permission.
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
"AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
(INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
A.13 Google SMP Patch License
The following software may be included in this product:
Google SMP Patch
Google SMP patch
Copyright (c) 2008, Google Inc.
All rights reserved.
Redistribution and use in source and binary forms, with or without
modification, are permitted provided that the following conditions
are met:
* Redistributions of source code must retain the above copyright
notice, this list of conditions and the following disclaimer.
* Redistributions in binary form must reproduce the above copyright
notice, this list of conditions and the following disclaimer in the
documentation and/or other materials provided with the distribution.
* Neither the name of the Google Inc. nor the names of its contributors
may be used to endorse or promote products derived from this software
without specific prior written permission.
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
"AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS
FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE
COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,
INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN
ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
POSSIBILITY OF SUCH DAMAGE.
lib_sql.cc License
67
A.14 lib_sql.cc License
The following software may be included in this product:
lib_sql.cc
Copyright (c) 2000
SWsoft company
This material is provided "as is", with absolutely no warranty
expressed or implied. Any use is at your own risk.
Permission to use or copy this software for any purpose is hereby
granted without fee, provided the above notices are retained on
all copies. Permission to modify the code and to distribute modified
code is granted, provided the above notices are retained, and a
notice that the code was modified is included with the above copyright
notice.
This code was modified by the MySQL team.
A.15 libevent License
The following software may be included in this product:
libevent
Copyright (c) 2000-2007 Niels Provos <provos@citi.umich.edu>
All rights reserved.
Redistribution and use in source and binary forms, with or without
modification, are permitted provided that the following conditions
are met:
1. Redistributions of source code must retain the above copyright
notice, this list of conditions and the following disclaimer.
2. Redistributions in binary form must reproduce the above copyright
notice, this list of conditions and the following disclaimer in the
documentation and/or other materials provided with the distribution.
3. The name of the author may not be used to endorse or promote products
derived from this software without specific prior written permission.
THIS SOFTWARE IS PROVIDED BY THE AUTHOR ``AS IS'' AND ANY EXPRESS OR
IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.
IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY DIRECT, INDIRECT,
INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT
NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
(INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF
THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE
==
Parts developed by Adam Langley
==
==
log.c
Based on err.c, which was adapted from OpenBSD libc *err*warncode.
Copyright (c) 2005 Nick Mathewson
Copyright (c) 2000 Dug Song
Copyright (c) 1993 The Regents of the University of California.
libevent License
68
All rights reserved.
Redistribution and use in source and binary forms, with or without
modification, are permitted provided that the following conditions
are met:
1. Redistributions of source code must retain the above copyright
notice, this list of conditions and the following disclaimer.
2. Redistributions in binary form must reproduce the above copyright
notice, this list of conditions and the following disclaimer in
the documentation and/or other materials provided with the
distribution.
3. Neither the name of the University nor the names of its
contributors may be used to endorse or promote products derived
from this software without specific prior written permission.
THIS SOFTWARE IS PROVIDED BY THE REGENTS AND CONTRIBUTORS "AS IS"
AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED
TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A
PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE REGENTS
OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED
AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING
IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF
THE POSSIBILITY OF SUCH DAMAGE.
==
==
min_heap.h
Copyright (c) 2006 Maxim Yegorushkin
All rights reserved.
Redistribution and use in source and binary forms, with or without
modification, are permitted provided that the following conditions
are met:
1. Redistributions of source code must retain the above copyright
notice, this list of conditions and the following disclaimer.
2. Redistributions in binary form must reproduce the above copyright
notice, this list of conditions and the following disclaimer in
the documentation and/or other materials provided with the
distribution.
3. The name of the author may not be used to endorse or promote
products derived from this software without specific prior
written permission.
THIS SOFTWARE IS PROVIDED BY THE AUTHOR "AS IS" AND ANY EXPRESS
OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY
DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE
GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER
IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR
OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN
IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
==
==
win32.c
Copyright 2000-2002 Niels Provos
Copyright 2003 Michael A. Davis
All rights reserved.
Linux-PAM License
69
Redistribution and use in source and binary forms, with or without
modification, are permitted provided that the following conditions
are met:
1. Redistributions of source code must retain the above copyright
notice, this list of conditions and the following disclaimer.
2. Redistributions in binary form must reproduce the above copyright
notice, this list of conditions and the following disclaimer in
the documentation and/or other materials provided with the
distribution.
3. The name of the author may not be used to endorse or promote
products derived from this software without specific prior
written permission.
THIS SOFTWARE IS PROVIDED BY THE AUTHOR "AS IS" AND ANY EXPRESS
OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY
DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE
GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER
IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR
OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN
IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
==
A.16 Linux-PAM License
The following software may be included in this product:
Linux-PAM (pam-devel, Pluggable authentication modules for Linux)
Copyright Theodore Ts'o, 1996. All rights reserved.
(For the avoidance of doubt, Oracle uses and distributes this
component under the terms below and elects not to do so under
the GPL even though the GPL is referenced as an option below.)
Redistribution and use in source and binary forms, with or
without modification, are permitted provided that the following
conditions are met:
1. Redistributions of source code must retain the above copyright
notice, and the entire permission notice in its entirety,
including the disclaimer of warranties.
2. Redistributions in binary form must reproduce the above copyright
notice, this list of conditions and the following disclaimer in
the documentation and/or other materials provided with the
distribution.
3. The name of the author may not be used to endorse or promote
products derived from this software without specific prior
written permission.
ALTERNATIVELY, this product may be distributed under the terms
of the GNU Public License, in which case the provisions of the
GPL are required INSTEAD OF the above restrictions. (This clause
is necessary due to a potential bad interaction between the GPL
and the restrictions contained in a BSD-style copyright.)
THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESS OR IMPLIED
WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
DISCLAIMED. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY DIRECT,
INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
md5 (Message-Digest Algorithm 5) License
70
(INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR
SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION)
HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT,
STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED
OF THE POSSIBILITY OF SUCH DAMAGE.
A.17 md5 (Message-Digest Algorithm 5) License
The following software may be included in this product:
md5 (Message-Digest Algorithm 5)
This code implements the MD5 message-digest algorithm.
The algorithm is due to Ron Rivest. This code was
written by Colin Plumb in 1993, no copyright is claimed.
This code is in the public domain; do with it what you wish.
Equivalent code is available from RSA Data Security, Inc.
This code has been tested against that, and is equivalent,
except that you don't need to include two pages of legalese
with every copy.
The code has been modified by Mikael Ronstroem to handle
calculating a hash value of a key that is always a multiple
of 4 bytes long. Word 0 of the calculated 4-word hash value
is returned as the hash value.
A.18 memcached License
The following software may be included in this product:
memcached
Copyright (c) 2003, Danga Interactive, Inc.
All rights reserved.
Redistribution and use in source and binary forms, with or without
modification, are permitted provided that the following conditions
are met:
* Redistributions of source code must retain the above copyright
notice, this list of conditions and the following disclaimer.
* Redistributions in binary form must reproduce the above
copyright notice, this list of conditions and the following disclaimer
in the documentation and/or other materials provided with the
distribution.
* Neither the name of the Danga Interactive nor the names of its
contributors may be used to endorse or promote products derived from
this software without specific prior written permission.
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
"AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
nt_servc (Windows NT Service class library) License
71
(INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
A.19 nt_servc (Windows NT Service class library) License
The following software may be included in this product:
nt_servc (Windows NT Service class library)
Windows NT Service class library
Copyright Abandoned 1998 Irena Pancirov - Irnet Snc
This file is public domain and comes with NO WARRANTY of any kind
A.20 OpenPAM License
The following software may be included in this product:
OpenPAM
Copyright (c) 2002-2003 Networks Associates Technology, Inc.
Copyright (c) 2004-2007 Dag-Erling Smørgrav
All rights reserved.
This software was developed for the FreeBSD Project by
ThinkSec AS and Network Associates Laboratories, the
Security Research Division of Network Associates, Inc.
under DARPA/SPAWAR contract N66001-01-C-8035 ("CBOSS"),
as part of the DARPA CHATS research program.
Redistribution and use in source and binary forms,
with or without modification, are permitted provided
that the following conditions are met:
1. Redistributions of source code must retain the above
copyright notice, this list of conditions and the
following disclaimer.
2. Redistributions in binary form must reproduce the
above copyright notice, this list of conditions and
the following disclaimer in the documentation and/or
other materials provided with the distribution.
3. The name of the author may not be used to endorse or
promote products derived from this software without
specific prior written permission.
THIS SOFTWARE IS PROVIDED BY THE AUTHOR AND CONTRIBUTORS
"AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING,
BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN
NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS BE LIABLE FOR
ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED
AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN
IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
A.21 Paramiko License
The following software may be included in this product:
Percona Multiple I/O Threads Patch License
72
Paramiko
You are receiving a copy of Paramiko in both source and object code. The
terms of the Oracle license do NOT apply to the Paramiko program; it is
licensed under the following license, separately from the Oracle
programs you receive. If you do not wish to install this program, you
may delete the Paramiko folder and all its contents.
This component is licensed under Section A.8, “GNU Lesser General Public License Version 2.1, February
1999”.
A.22 Percona Multiple I/O Threads Patch License
The following software may be included in this product:
Percona Multiple I/O threads patch
Copyright (c) 2008, 2009 Percona Inc
All rights reserved.
Redistribution and use of this software in source and binary forms,
with or without modification, are permitted provided that the
following conditions are met:
* Redistributions of source code must retain the above copyright
notice, this list of conditions and the following disclaimer.
* Redistributions in binary form must reproduce the above copyright
notice, this list of conditions and the following disclaimer in the
documentation and/or other materials provided with the distribution.
* Neither the name of Percona Inc. nor the names of its contributors
may be used to endorse or promote products derived from this software
without specific prior written permission of Percona Inc.
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
"AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS
FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE
COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,
INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN
ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
POSSIBILITY OF SUCH DAMAGE.
A.23 RegEX-Spencer Library License
The following software may be included in this product: Henry Spencer's Regular-Expression Library
(RegEX-Spencer)
Copyright 1992, 1993, 1994 Henry Spencer. All rights reserved.
This software is not subject to any license of the American Telephone
and Telegraph Company or of the Regents of the University of California.
Permission is granted to anyone to use this software for any purpose on
any computer system, and to alter it and redistribute it, subject
to the following restrictions:
1. The author is not responsible for the consequences of use of this
software, no matter how awful, even if they arise from flaws in it.
RFC 3174 - US Secure Hash Algorithm 1 (SHA1) License
73
2. The origin of this software must not be misrepresented, either by
explicit claim or by omission. Since few users ever read sources,
credits must appear in the documentation.
3. Altered versions must be plainly marked as such, and must not be
misrepresented as being the original software. Since few users
ever read sources, credits must appear in the documentation.
4. This notice may not be removed or altered.
A.24 RFC 3174 - US Secure Hash Algorithm 1 (SHA1) License
The following software may be included in this product:
RFC 3174 - US Secure Hash Algorithm 1 (SHA1)
RFC 3174 - US Secure Hash Algorithm 1 (SHA1)
Copyright (C) The Internet Society (2001). All Rights Reserved.
This document and translations of it may be copied and furnished to
others, and derivative works that comment on or otherwise explain it
or assist in its implementation may be prepared, copied, published
and distributed, in whole or in part, without restriction of any
kind, provided that the above copyright notice and this paragraph are
included on all such copies and derivative works. However, this
document itself may not be modified in any way, such as by removing
the copyright notice or references to the Internet Society or other
Internet organizations, except as needed for the purpose of
developing Internet standards in which case the procedures for
copyrights defined in the Internet Standards process must be
followed, or as required to translate it into languages other than
English.
The limited permissions granted above are perpetual and will not be
revoked by the Internet Society or its successors or assigns.
This document and the information contained herein is provided on an
"AS IS" basis and THE INTERNET SOCIETY AND THE INTERNET ENGINEERING
TASK FORCE DISCLAIMS ALL WARRANTIES, EXPRESS OR IMPLIED, INCLUDING
BUT NOT LIMITED TO ANY WARRANTY THAT THE USE OF THE INFORMATION
HEREIN WILL NOT INFRINGE ANY RIGHTS OR ANY IMPLIED WARRANTIES OF
MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE.
Acknowledgement
Funding for the RFC Editor function is currently provided by the
Internet Society.
A.25 Richard A. O'Keefe String Library License
The following software may be included in this product:
Richard A. O'Keefe String Library
The Richard O’Keefe String Library is subject to the following notice:
These files are in the public domain. This includes getopt.c, which
is the work of Henry Spencer, University of Toronto Zoology, who
says of it "None of this software is derived from Bell software. I
had no access to the source for Bell's versions at the time I wrote
it. This software is hereby explicitly placed in the public domain.
SHA-1 in C License
74
It may be used for any purpose on any machine by anyone." I would
greatly prefer it if *my* material received no military use.
The t_ctype.h file is subject to the following notice:
Copyright (C) 1998, 1999 by Pruet Boonma, all rights reserved.
Copyright (C) 1998 by Theppitak Karoonboonyanan, all rights reserved.
Permission to use, copy, modify, distribute and sell this software and its
documentation for any purpose is hereby granted without fee, provided that the above
copyright notice appear in all copies.
Smaphan Raruenrom and Pruet Boonma makes no representations about
the suitability of this software for any purpose. It is provided
"as is" without express or implied warranty.
A.26 SHA-1 in C License
The following software may be included in this product:
SHA-1 in C
SHA-1 in C
By Steve Reid <steve@edmweb.com>
100% Public Domain
A.27 zlib License
The following software may be included in this product:
zlib
Oracle gratefully acknowledges the contributions of Jean-loup Gailly and Mark Adler in creating the zlib
general purpose compression library which is used in this product.
zlib.h -- interface of the 'zlib' general purpose compression library
Copyright (C) 1995-2004 Jean-loup Gailly and Mark Adler
zlib.h -- interface of the 'zlib' general purpose compression library
version 1.2.3, July 18th, 2005
Copyright (C) 1995-2005 Jean-loup Gailly and Mark Adler
zlib.h -- interface of the 'zlib' general purpose compression library
version 1.2.5, April 19th, 2010
Copyright (C) 1995-2010 Jean-loup Gailly and Mark Adler
This software is provided 'as-is', without any express or implied warranty.
In no event will the authors be held liable for any damages arising from the
use of this software. Permission is granted to anyone to use this software
for any purpose,including commercial applications, and to alter it and
redistribute it freely, subject to the following restrictions:
1. The origin of this software must not be misrepresented; you must not
claim that you wrote the original software. If you use this software
in a product, an acknowledgment in the product documentation would
be appreciated but is not required.
2. Altered source versions must be plainly marked as such, and must not
be misrepresented as being the original software.
3. This notice may not be removed or altered from any source distribution.
Jean-loup Gailly jloup@gzip.org
Mark Adler madler@alumni.caltech.edu

Share on Google Plus

About Unknown

This is a short description in the author block about the author. You edit it by entering text in the "Biographical Info" field in the user admin panel.