mysql

commands

2010-01-13T22:02:00.000-08:00

$ mysqldump -u root --all-databases > alldatabases.sql
$ mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql (Dump one database for backup.)
$ mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql (Dump a table from a database)
$ mysql -u root < alldatabases.sql
$ mysql -u root [databasename] < youfile.sql

$ [mysql dir]/bin/mysql -h hostname -u root -p

$ show databases;
$ create database [databasename];
$ drop database [database name];
$ use [db name];

$ show tables;
$ create table [table name] (column1 integer, column2 char(10), column3 VARCHAR(20), datestamp DATE, timestamp time);
$ create table [table name] (personid int(50) not null auto_increment primary key,firstname varchar(35),middlename varchar(50),lastnamevarchar(50) default 'bato');
$ describe [table name];
$ desc [table name];
$ show columns from [table name];

$ INSERT INTO [table name] (Host,User,Password) VALUES ('%', 'user', PASSWORD('password'));

$ SELECT * FROM [table name];
$ SELECT * FROM [table name] WHERE [field name] = "whatever";
$ SELECT * FROM [table name] WHERE name = "Bob" AND phone_number = '3444444';
$ SELECT * FROM [table name] WHERE name != "Bob" AND phone_number = '3444444' order by phone_number;
$ SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444';
$ SELECT DISTINCT [column name] FROM [table name];
$ SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC | ASC;
$ SELECT COUNT(*) FROM [table name];

$ SELECT * FROM [table name] WHERE rec RLIKE "^a$";
$ SELECT * FROM [table name] WHERE rec RLIKE "^a$"; (Use a regular expression to find records. Use "REGEXP BINARY" to force case-sensitivity. This finds any record beginning with a.)

$ alter table [table name] add column [new column name] varchar (20), add column [new column name 2] time;
$ alter table [table name] drop column [column name];
$ alter table [table name] change [old column name] [new column name] varchar (50);
$ alter table [table name] add unique ([column name]);
$ alter table [table name] modify [column name] VARCHAR(3);
$ alter table [table name] drop index [colmn name];

$ LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3); (Load a CSV file into a table.)

$ DELETE from [table name] where [field name] = 'whatever';

$ UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where [field name] = 'user';

$ drop table [table name];

$ SELECT SUM(*) FROM [table name];

$ select lookup.illustrationid, lookup.personid,person.birthday from lookup
left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;

$ FLUSH PRIVILEGES; (Update database permissions/privilages.)

--- switch to mysql db

$ INSERT INTO [table name] (Host,User,Password) VALUES('%','user',PASSWORD('password'));
$ mysqladmin -u root -h hostname.blah.org -p password 'new-password' (from unix shell)
$ SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere'); (from mysql prompt)
$ grant usage on *.* to bob@localhost identified by 'passwd';
$ INSERT INTO [table name] (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('%','databasename','username','Y','Y','Y','Y','Y','N');

OR

$ grant all privileges on databasename.* to username@localhost;

upgrade mysql 4.1.15 to mysql 4.1.22

2008-01-13T22:05:00.000-08:00

Download and install MySql 4.1.22

File name: mysql-noinstall-4.1.22-win32.zip

b. Just unzip it under 'C:\dev\tools'

Expoprt all existsitng databases (mysqldump -u root --all-databases > alldatabases.sql) Shut down mysql server

Copy my.cnf file from to Edit the copied my.cnf file and replace all directory references to point to the new Modify !MySql server start-up and shut-down shortcuts to point to the new

Test Startup/Shutdown scripts (Allow it in the firewall!!!)

Import all databases (mysql -u root < alldatabases.sql)

Restart Mysql server

Update computer's Environment path (if mysql command is to be global) (Allow it in firewall!!!)

Using JDBC with MySQL 3

2007-04-15T22:34:00.000-07:00

JDBC Programs

With all of the above as preparation, it is now time to learn how to write JDBC programs to administer and manipulate the data on the MySQL database server.

Three separate programs

I will explain three programs. The first program, named Jdbc11 shows how to:

Log onto the server as the administrator named root.

Create a new database named JunkDB.

Register a new user named auser on the database named JunkDB with six different privileges and a password of drowssap.

The second program named Jdbc12 shows how to:

Log onto the server as the administrator named root.

Revoke the privileges of and remove the user named auser.

Delete the database named JunkDB.

The third program named Jdbc10 shows how to log onto the server as the user named auser and to manipulate the database named JunkDB in a variety of ways.

I will break each of these programs down into fragments and discuss the fragments. Complete listings of all the programs are shown in Listings 40, 41, and 42 near the end of the lesson.

Jdbc11 - Create a database and make a new user

The purpose of the program named Jdbc11 is to log onto the master database named mysql as the default administrator named root whose password is blank in order to perform the following updates on the MySQL database server:

Create a new database named JunkDB.

Create a new user named auser with a password of drowssap with six different privileges on the database named JunkDB.

The output, or lack thereof

These two operations produce no visible output when successful. However, they produce error messages in the output when unsuccessful.

(Note, however, that print statements in the program produce several lines of output that are independent of the operations being performed on the database server.)

Server must be running

The MySQL server must be running on localhost before this program is started. Instructions for starting and stopping the database server were provided earlier (see Listing 1, Figure 1, Listing 2, and Figure 2).

The program was tested using Java SDK 1.4.2 under WinXP, MySQL version 4.0.21-win, and JDBC connector version mysql-connector-java-3.0.15-ga.

Critical steps in using JDBC

There are five critical steps in using JDBC to manipulate a database:

Load and register the JDBC driver classes (programming interface) for the database server that you intend to use.

Get a Connection object that represents a connection to the database server (analogous to logging onto the server).

Get one or more Statement objects for use in manipulating the database.

Use the Statement objects to manipulate the database.

Close the connection to the database.

I will highlight these five steps in the discussion of the sample program that follows.

Beginning of class definition for Jdbc11

The first program fragment for the program named Jdbc11 is shown in Listing 3. The entire program is shown in Listing 40 near the end of the lesson.

public class Jdbc11 {
 public static void main(String args[]){
   System.out.println(
                 "Copyright 2004, R.G.Baldwin");
   try {
     Statement stmt;

Listing 3

The code in Listing 3 is straightforward, showing the beginning of the class, the beginning of the main method, and a print statement.

Listing 3 also declares a local variable of type Statement. I will have more to say about the Statement interface later.

Register the JDBC driver for MySQL

Listing 4 shows the statement that implements the first critical step listed earlier (load and register the JDBC driver classes). This statement registers the MySQL driver classes with the Java program, making it possible for this program to manipulate data on the MySQL server.

      Class.forName("com.mysql.jdbc.Driver");

Listing 4

Reference to the driver class

The following statement appears in the MySQL Connector documentation, Section 2.2.1. entitled Setting the CLASSPATH (For Standalone Use).

"If you are going to use the driver with the JDBC DriverManager, you would use "com.mysql.jdbc.Driver" as the class that implements java.sql.Driver."

This information is also provided in the connector documentation in Section 2.2.2. entitled Driver Class Name and JDBC URL Format.

The Driver interface

Note the reference to the Driver interface in the above quotation. Here is some of what Sun has to say about the Driver interface:

"The interface that every driver class must implement.

The Java SQL framework allows for multiple database drivers.

Each driver should supply a class that implements the Driver interface.

The DriverManager will try to load as many drivers as it can find and then for any given connection request, it will ask each driver in turn to try to connect to the target URL. ...

When a Driver class is loaded, it should create an instance of itself and register it with the DriverManager. This means that a user can load and register a driver by calling

Class.forName("foo.bah.Driver")"

The name of the driver class

In order to use a JDBC program with a specific database server, you must obtain the name of this critical driver class from the database vendor (or from some third party that supports the database server). You must then cause your program to load the class.

This is the class that connects the other classes in the connector package to the Java program. Without it, the Java program would be unable to communicate successfully with the database server.

Loading the driver class

The statement in Listing 4 causes this class to be loaded as described in the Sun documentation quoted above.

(If you are unfamiliar with the use of the forName method of the class named Class, see The Essence of OOP using Java: Static Members for a brief introduction to the class named Class. Then open your Google search engine, set the number of results to 100, and search for all of the keywords java forname richard baldwin. This should point you to several previous lessons that I have published that discuss this topic. If you don't find what you need there, click on the link on the bottom of the last Google page that reads repeat the search with the omitted results included to see even more lessons.)

Specification of Driver class as a String

There are several alternative ways to register the Driver class, only one of which is shown in Listing 4. The statement in Listing 4 makes it possible to specify the Driver class as a String. The primary advantage of this approach is that this string can be obtained by the program in a variety of ways at runtime.

Although the string was hard coded into this simple program, that is not a requirement. For a more general program intended to be used with two or more database servers, this string would most likely be provided as some form of user input.

The URL of the database server

MySQL and other similar database engines behave as servers on a network. They are identified by a URL much as other types of servers (such as HTTP servers and FTP servers) are identified. The next fragment defines the URL for the MySQL database server that I used in this sample program.

The code in Listing 5 defines the URL of the master database named mysql on the MySQL database server residing on localhost and servicing the default port number 3306.

(I could have omitted the default port number from the URL, but I decided to include it to remind me to mention it. Note that the "//" characters shown to the right of "mysql:" form part of the URL. They are not comment indicators.)

      String url =
           "jdbc:mysql://localhost:3306/mysql";

Listing 5

The URL format is provided in the connector documentation in Section 2.2.2. entitled Driver Class Name and JDBC URL Format. The URL format contains several optional elements. This URL will be referenced in the statement in Listing 6 for the purpose of getting a connection to the database.

Get a connection to the database

The code in Listing 6 implements the second critical step listed earlier (get a connection object).

Listing 6 gets a connection to the database at the specified URL (mysql on localhost port 3306) for a user named root with a blank password. As you are already aware, this user is the default administrator having full privileges to do anything, including creating new databases and registering new users on those databases.

      Connection con =
                    DriverManager.getConnection(
                                url,"root", "");

Listing 6

In effect, Listing 6 logs the JDBC program onto the MySQL database server in a manner that is analogous to the first line in Figures 3, 4, and 5 as well as the statements in the batch files shown in Listings 32, 34, 36, and 38.

The getConnection method

The getConnection method is a static method of the DriverManager class. When getConnection is invoked, the DriverManager will attempt to locate a suitable driver from among those loaded at initialization and those loaded explicitly using the same classloader as the current applet or application.

There are several overloaded versions of the getConnection method. The version used in Listing 6 attempts to establish a connection to the given database URL for a specific user with a specific password.

If the attempt to get a connection to the database server is successful, the method returns an object of type Connection. In this program, a reference to the Connection object is stored in the reference variable named con.

If the attempt is not successful, an exception of type SQLException will be thrown. Information pertinent to the nature of the problem will be encapsulated in the SQLException object.

As you will see later, SQL statements are executed and results are returned within the context of a connection.

Display some information

The code in Listing 7 is not critical to the program. This code simply displays information about the URL and the connection.

      System.out.println("URL: " + url);
     System.out.println("Connection: " + con);

Listing 7

Get a Statement object

The code in Listing 8 implements the third critical step listed earlier (get one or more Statement objects).

This code invokes the createStatement method of the Connection interface to get an object of type Statement.

      stmt = con.createStatement();

Listing 8

Recall that con is a reference to an object of type Connection. A Connection object defines a connection (session) with a specific database. SQL statements are executed and results are returned within the context of a connection.

According to Sun, a Statement object is:

"... used for executing a static SQL statement and returning the results it produces."

The results that are returned

The results, if any, are returned in the form of a ResultSet object. I will have more to say about the ResultSet interface later in conjunction with the discussion of the program named Jdbc10.

(The SQL commands used in this program don't return any results.)

Methods of the Statement interface

The Statement interface declares many methods that can be used to access the database server and to manipulate the data in the database. One of those methods is executeUpdate, which will be used in this program.

The executeUpdate method has a single String parameter. This parameter must be a valid SQL command. The method is used to execute SQL INSERT, UPDATE or DELETE statements. In addition, other SQL statements that return nothing can be executed using this method.

Create the new database

The code in Listing 9 implements the fourth critical step in the list provided earlier, (use of a Statement object to manipulate the database).

Listing 9 invokes the executeUpdate method on the Statement object to create the new database named JunkDB.

      stmt.executeUpdate(
                      "CREATE DATABASE JunkDB");

Listing 9

The executeUpdate method

Sun has this to say about the executeUpdate method:

"public int executeUpdate(String sql) throws SQLException

Executes the given SQL statement, ...

Parameters: sql - an SQL INSERT, UPDATE or DELETE statement or an SQL statement that returns nothing

Returns: either the row count for INSERT, UPDATE or DELETE statements, or 0 for SQL statements that return nothing."

The method parameter is an SQL command

Note the similarity of the method parameter in Listing 9 to the interactive input shown in Figure 4 and the contents of the text file shown in Listing 33.

In all three cases, an SQL command is invoked on the database server to cause a new database named JunkDB to be created. This SQL command returns nothing, so it is suitable for use with the executeUpdate method described above.

In all three cases, the SQL command is invoked by the default administrator named root who has the ability to create new databases.

Make a new user

The code in Listing 10 also implements the fourth critical step listed earlier, (use of a Statement object to manipulate the database).

The code in Listing 10 invokes the executeUpdate method once again to make a new user named auser who is capable of accessing the database named JunkDB from localhost using the password drowssap with six different privileges.

      stmt.executeUpdate(
         "GRANT SELECT,INSERT,UPDATE,DELETE," +
         "CREATE,DROP " +
         "ON JunkDB.* TO 'auser'@'localhost' " +
         "IDENTIFIED BY 'drowssap';");
Listing 10

Another SQL command

Once again, note the similarity of the executeUpdate method parameter in Listing 10 to the interactive input shown in Figure 5 and to the contents of the text file shown in Listing 35.

Figure 5, Listing 10, and Listing 35 illustrate three different ways for the default administrator named root to invoke the same SQL command on the database server.

Close the connection and terminate the program

Listing 11 implements the fifth critical step listed earlier, closing the connection and terminating the program.

      con.close();
   }catch( Exception e ) {
     e.printStackTrace();
   }//end catch
 }//end main
}//end class Jdbc11

Listing 11

The results of running the program named Jdbc11

Once this program has been run successfully, the MySQL database server contains a database named JunkDB, as well as a user named auser, having various privileges relative to that database with a password of drowssap.

At this point, it is possible to execute JDBC programs by which the user named auser manipulates the contents of the database named JunkDB. That will be the purpose of the program named Jdbc10, which I will explain later.

First, however, I am going to show you how to write a JDBC program to remove the user named auser and to delete the database named JunkDB from the MySQL database server.

JDBC12 - Remove a user and delete a database

The purpose of the program named Jdbc12 is to log onto the master database named mysql as the default administrator named root whose password is blank in order to perform the following updates on the MySQL database server:

Revoke the privileges of and remove a user named auser.

Delete a database named JunkDB.

The output

These two operations produce no visible output when successful. However, they produce error messages in the output when unsuccessful.

(Print statements in the program do produce output that is unrelated to the operations listed above.)

A reversal

This program is the reverse of the program named Jdbc11, discussed earlier, which creates the database named JunkDB and registers the user named auser on that database.

Server must be running

Testing

This program was tested using Java SDK 1.4.2 under WinXP, MySQL version 4.0.21-win, and JDBC connector version mysql-connector-java-3.0.15-ga.

Beginning of the Jdbc12 class

Listing 12 shows the beginning of the class definition and the beginning of the main method for the program named Jdbc12. A complete listing of the program is provided in Listing 41

The code in Listing 12 is the same as the code discussed earlier for the program named Jdbc11. Therefore, I won't repeat that discussion.

public class Jdbc12 {
 public static void main(String args[]){
   System.out.println(
                 "Copyright 2004, R.G.Baldwin");
   try {
     Statement stmt;

     //Register the JDBC driver for MySQL.
     Class.forName("com.mysql.jdbc.Driver");

     //Define URL of database server for
     // database named mysql on the localhost
     // with the default port number 3306.
     String url =
           "jdbc:mysql://localhost:3306/mysql";

     //Get a connection to the database for a
     // user named root with a blank password.
     // This user is the default administrator
     // having full privileges to do anything.
     Connection con =
                    DriverManager.getConnection(
                                url,"root", "");

     //Display URL and connection information
     System.out.println("URL: " + url);
     System.out.println("Connection: " + con);

     //Get a Statement object
     stmt = con.createStatement();

Listing 12

Revoke privileges and remove user named auser

Listing 13 invokes the executeUpdate method four times in succession to cause the database server to revoke privileges for and to remove the user named auser. The SQL commands required to accomplish this were discussed earlier and illustrated in Listings 36 and 37.

      stmt.executeUpdate(
         "REVOKE ALL PRIVILEGES ON *.* " +
         "FROM 'auser'@'localhost'");
     stmt.executeUpdate(
         "REVOKE GRANT OPTION ON *.* " +
         "FROM 'auser'@'localhost'");
     stmt.executeUpdate(
         "DELETE FROM mysql.user WHERE " +
         "User='auser' and Host='localhost'");
     stmt.executeUpdate("FLUSH PRIVILEGES");

Listing 13

Delete the database named JunkDB

Listing 14 invokes the executeUpdate method to delete the database named JunkDB, using an SQL command discussed earlier and illustrated in Listings 38 and 39.

      stmt.executeUpdate(
                       "DROP DATABASE JunkDB");

Listing 14

Closing the connection and terminating the program

Listing 15 closes the connection and terminates the program.

      con.close();
   }catch( Exception e ) {
     e.printStackTrace();
   }//end catch
 }//end main
}//end class Jdbc12

Listing 15

Jdbc10 - Manipulating the data in the database

The behavior of each of the two previous programs has been in the nature of administering or managing the database server. In particular, those programs added and removed databases and users from the database server.

The user perspective

The purpose of the program named Jdbc10 is to illustrate the ability to use JDBC to access a MySQL database server on localhost and to manipulate the data stored in that database.

Server must be running

The MySQL server must be running on localhost before the program named Jdbc10 is started. Instructions for starting and stopping the MySQL database server were provided earlier (see Listing 1, Figure 1, Listing 2, and Figure 2).

Server must be prepared

In addition, a database named JunkDB must have been created on the server and a user named auser must have been registered on that database with a password of drowssap before this program is started.

The user named auser must have privileges that allow for the creation of tables in the database and the insertion of data into the tables. In addition the user must be allowed to perform SELECT queries on the tables in the database.

Creating the database and adding the user

The database may have been created and the user may have been added in at least three different ways:

Performing manual data entry with the monitor program as illustrated in Figures 4 and 5.

Using the monitor program coupled with data input derived from a text file as illustrated in Listings 32, 33, 34, and 35.

Running the program named Jdbc11 discussed earlier.

Behavior of Jdbc10 program

This program:

Logs in as auser with a password of drowssap.

Accesses the database named JunkDB.

Creates a table named myTable.

Puts five rows of data into the table named myTable.

Accesses the data in the table named myTable.

Displays the data.

Deletes the table named myTable.

Two different approaches are used to display the contents of the table. The first approach displays all of the data in the table. The second approach displays only the data in a specific row in the table.

A precaution

As a precaution, before attempting to create the new table, the program attempts to delete a table having the same name. If a table having the same name already exists as residue from a previous run, it is deleted.

If it doesn't already exist when the attempt is made to delete it, an exception is thrown. This exception is caught, displayed, and ignored.

The program output

This program produces the output shown in Figure 6 under normal conditions where the table named myTable does not exist when the program is started (the specifics regarding the Connection object may vary from one run to the next).

Copyright 2004, R.G.Baldwin
URL: jdbc:mysql://localhost:3306/JunkDB
Connection: com.mysql.jdbc.Connection@1430b5c
java.sql.SQLException: Base table or view not
found message from server: "Unknown table
'mytable'"No existing table to delete
Display all results:
test_id= 1 str = One
test_id= 2 str = Two
test_id= 3 str = Three
test_id= 4 str = Four
test_id= 5 str = Five
Display row number 2:
test_id= 2 str = Two

Figure 6

Testing

This program was tested using Java SDK 1.4.2 under WinXP, MySQL version 4.0.21-win, and JDBC connector version mysql-connector-java-3.0.15-ga.

Beginning of the class definition

The class definition begins in Listing 16. A complete listing of the program is shown in Listing 42 near the end of the lesson.

public class Jdbc10 {
 public static void main(String args[]){
   System.out.println(
                 "Copyright 2004, R.G.Baldwin");
   try {
     Statement stmt;
     ResultSet rs;

     //Register the JDBC driver for MySQL.
     Class.forName("com.mysql.jdbc.Driver");

Listing 16

Except for the declaration of a variable of type ResultSet, the code in Listing 16 is the same as the code in the two previous programs. I will have more to say about the ResultSet type later.

Define the database URL

Listing 17 defines the URL of the database server and the database that will be accessed later by this program.

      String url =
           "jdbc:mysql://localhost:3306/JunkDB";

Listing 17

Note that the previous two programs accessed the master database named mysql, whereas this program accesses the user database named JunkDB. Otherwise, the code in Listing 17 is the same as in the two previous programs.

Get a connection to the database

Listing 18 gets a connection to the database.

      Connection con =
                    DriverManager.getConnection(
                       url,"auser", "drowssap");

Listing 18

Recall that the code in Listing 18 is analogous to a specific user logging onto a specific database using a specific password.

The previous two programs got a connection to the master database named mysql for the default administrator named root whose password was blank.

This program gets a connection to the database named JunkDB for the user named auser whose password is drowssap.

Display some information and get a statement object

As in the previous two programs, Listing 19 displays some information about the URL and the connection, and then gets a Statement object.

      //Display URL and connection information
     System.out.println("URL: " + url);
     System.out.println("Connection: " + con);

     //Get a Statement object
     stmt = con.createStatement();

Listing 19

Delete the table named myTable if it exists

A table named myTable may already exist in the database named JunkDB for some reason such as the premature ending of a previous run of this program. If the table already exists, it will not be possible to create a new empty table having that name. A requirement of this program is to create an empty table named myTable in a particular format.

Therefore, as a precaution, before attempting to create the new table, the code in Listing 20 attempts to delete a table named myTable. If a table having that name already exists, it is deleted.

If it doesn't already exist when the attempt is made to delete it, an exception is thrown. This exception is simply caught, displayed, and ignored.

      try{
       stmt.executeUpdate("DROP TABLE myTable");
     }catch(Exception e){
       System.out.print(e);
       System.out.println(
                 "No existing table to delete");
     }//end catch

Listing 20

The code in Listing 20 invokes the executeUpdate method, with which you are already familiar. Only the syntax of the SQL command provided as a parameter to the method is new to this program.

Create the new table named myTable

The code in Listing 22 invokes the executeUpdate method to create a new table named myTable. I will refer you to an SQL book for a full understanding of the SQL command that is passed as a parameter to the method.

      stmt.executeUpdate(
           "CREATE TABLE myTable(test_id int," +
                 "test_val char(15) not null)");

Listing 22

Briefly, the new table will have two columns. The first column will be named test_id and will be designed to contain integer data.

The second column will be named test_val and will be designed to contain character data up to 15 characters in length (in Java, we might refer to that as String data, except that there is no limit to the length of a String in Java).

Insert some values into the table

Listing 23 invokes the executeUpdate method to insert one value into each column of the first row in the table.

      stmt.executeUpdate(
               "INSERT INTO myTable(test_id, " +
                   "test_val) VALUES(1,'One')");

Listing 23

The integer value 1 is inserted into the column named test_id. The three characters, One, are inserted into the column named test_val.

Insert values into four more rows

Listing 24 invokes the executeUpdate method four more times in succession to insert values into the columns in rows 2 through 5. You should be able to examine the SQL commands and determine the values inserted into the table.

      stmt.executeUpdate(
               "INSERT INTO myTable(test_id, " +
                   "test_val) VALUES(2,'Two')");
     stmt.executeUpdate(
               "INSERT INTO myTable(test_id, " +
                 "test_val) VALUES(3,'Three')");
     stmt.executeUpdate(
               "INSERT INTO myTable(test_id, " +
                  "test_val) VALUES(4,'Four')");
     stmt.executeUpdate(
               "INSERT INTO myTable(test_id, " +
                  "test_val) VALUES(5,'Five')");

Listing 24

The table is populated

At this point, the first five rows in the table named myTable have been populated with data. The remaining code in the program will:

Access and display all of the data in the table.

Display the data in a specific row in the table.

Delete the table from the database.

Get another Statement object

The code in Listing 25 gets a different Statement object, initialized as shown by the parameters that are passed to the createStatement method.

      stmt = con.createStatement(
              ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);

Listing 25

I won't try to explain the meaning of the initialization parameters. Rather, I will let you look them up in the Sun documentation for the ResultSet interface, and then perhaps do further research in an SQL book.

The ResultSet interface provides about ten symbolic constants that can be used as parameters to this method. The values of the parameters exercise control over the behavior of the ResultSet object returned by a subsequent query based on the Statement object.

Query the database

All of the database operations to this point have been based on the use of the executeUpdate method of the Statement interface. Recall that I told you earlier that the executeUpdate method can be used to execute SQL commands that don't return anything.

We have now reached the point where we want to execute an SQL command that does return something. For this, we will invoke the executeQuery method on the Statement object.

What does Sun have to say?

Here is part of what Sun has to say about this method:

"public ResultSet executeQuery(String sql) throws SQLException

Executes the given SQL statement, which returns a single ResultSet object.

Parameters: sql - an SQL statement to be sent to the database, typically a static SQL SELECT statement

Returns: a ResultSet object that contains the data produced by the given query; never null"

In other words, this method will execute the SQL command on the database and encapsulate the returned values in an object of type ResultSet.

A ResultSet object

A ResultSet object provides access to an encapsulated table of data. The object maintains a cursor pointing to its current row of data. Initially the cursor is positioned before the first row. The next method moves the cursor to the next row (similar to an iterator or an enumerator in Java).

Once the results are encapsulated in a ResultSet object, the ResultSet interface provides several methods that can be used to extract the information from the object.

Methods of a ResultSet object

The get methods (such as getString) retrieve column values for the current row. You can retrieve values using either the index number of the column or the name of the column. I have read that using the column index is more efficient but I can't give you a reference on that.

(Columns are numbered beginning with 1, not with 0.)

For the get methods, the JDBC driver attempts to convert the underlying data to the specified Java type and returns a suitable Java value.

The life of a ResultSet object

A ResultSet object is automatically closed by the Statement object that generated it when that Statement object is closed, re-executed, or used to retrieve the next result from a sequence of multiple results.

Perform the query

Listing 26 invokes the executeQuery method to perform the query on the database selecting all columns in all rows, and sorting the results in order by the values in the column named test_id. The results are encapsulated in the ResultSet object referred to by the reference variable named rs.

      rs = stmt.executeQuery("SELECT * " +
               "from myTable ORDER BY test_id");

Listing 26

Display all of the results in the ResultSet object

Listing 27 uses a while loop to:

Iterate on the ResultSet object one row at a time.

Invoke the getInt method to get and save the value in the column named test_id for each row.

Invoke the getString method to get and save the value in the column named test_val for each row.

Display the two values on a new output line on the screen.

      System.out.println("Display all results:");
     while(rs.next()){
       int theInt= rs.getInt("test_id");
       String str = rs.getString("test_val");
       System.out.println("\ttest_id= " + theInt
                            + "\tstr = " + str);
     }//end while loop

Listing 27

The output

The output produced by the code in Listing 27 is shown in Figure 7.

Display all results:
test_id= 1 str = One
test_id= 2 str = Two
test_id= 3 str = Three
test_id= 4 str = Four
test_id= 5 str = Five

Figure 7

Display the data in row number 2

The code in Listing 28 invokes the absolute method on the same ResultSet object to get, save, and display the data in the two columns of row number 2.

      System.out.println(
                       "Display row number 2:");
     if( rs.absolute(2) ){
       int theInt= rs.getInt("test_id");
       String str = rs.getString("test_val");
       System.out.println("\ttest_id= " + theInt
                            + "\tstr = " + str);
     }//end if

Listing 28

(Note that row numbers begin with 1 and do not begin with 0 as would be the case in most Java contexts. Although not demonstrated here, the same is true for column numbers. Apparently this is the norm in database work.)

The output

Figure 8 shows the output produced by the code in Listing 28.

Display row number 2:
test_id= 2 str = Two

Figure 8

Delete the table, close the connection, and terminate

Listing 29 invokes the executeUpdate method to delete the table named myTable from the database named JunkDB.

      stmt.executeUpdate("DROP TABLE myTable");
     con.close();
   }catch( Exception e ) {
     e.printStackTrace();
   }//end catch
 }//end main
}//end class Jdbc10

Listing 29

Then Listing 29 closes the connection (logs off the database) and terminates the program.

Run the Programs

I encourage you to download and install the MySQL database server and the MySQL connector as described in this lesson.

Then copy the code from the listings near the end of this lesson. Execute the batch files. Compile and execute the programs. Experiment with the files and the programs, making changes, and observing the results of your changes.

Summary

I showed you how to download, install, and prepare a MySQL database as a localhost server on a Windows platform for use with JDBC.

Then I showed you how to write three simple JDBC programs to administer the database server and to manipulate data stored in a MySQL database.

Complete Program Listings

A complete listing of each of the programs, batch files, and text files discussed in this lesson is shown below.

C:\mysql\bin\mysqld-opt --console
pause

Listing 30 Contents of MySqlStart.bat

C:\mysql\bin\mysqladmin -u root shutdown
pause

Listing 31 Contents of MySqlStop.bat

c:\mysql\bin\mysql --user=root mysql < MySqlCreateDatabase01.txt
pause

Listing 32 MySqlCreateDatabase01.bat

CREATE DATABASE JunkDB;
\q

Listing 33 MySqlCreateDatabase01.txt

c:\mysql\bin\mysql --user=root mysql < MySqlMakeUser01.txt
pause

Listing 34 MySqlMakeUser01.bat

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON JunkDB.*
TO 'auser'@'localhost'
IDENTIFIED BY 'drowssap';
\q

Listing 35 MySqlMakeUser01.txt

c:\mysql\bin\mysql --user=root mysql < MySqlRemoveUser01.txt
pause

Listing 36 MySqlRemoveUser01.bat

REVOKE ALL PRIVILEGES ON *.* FROM 'auser'@'localhost';
REVOKE GRANT OPTION ON *.* FROM 'auser'@'localhost';
DELETE FROM mysql.user WHERE User='auser' and Host='localhost';
FLUSH PRIVILEGES;
\q

Listing 37 MySqlRemoveUser01.txt

c:\mysql\bin\mysql --user=root mysql < MySqlDropDatabase01.txt
pause

Listing 38 MySqlDropDatabase01.bat

DROP DATABASE JunkDB;
\q

Listing 39 MySqlDropDatabase01.txt

/*File Jdbc11.java
Copyright 2004, R.G.Baldwin
Rev 09/18/04

The purpose of this program is to log onto
the master database named mysql as the default
administrator named root whose password is blank
in order to perform the following updates:

1. Create a new database named JunkDB.
2. Create a new user named auser with a password
  of drowssap with broad access to the
  database named JunkDB.

These two operations produce no visible output
when successful.  However, they produce error
messages in the output when unsuccessful.

This program is the reverse of the program named
Jdbc12, which deletes the database JunkDB and
removes the user named auser.

The MySQL server must be running on localhost
before this program is started.

It is necessary to manually start the MySQL
database server running on localhost. See the
documentation sections 2.2.1.5,Starting the
Server for the First Time and 2.2.1.4 Selecting a
Windows Server. This is accomplished by executing
the following command at the command prompt:

C:\mysql\bin\mysqld-opt --console

Similarly, it is necessary to manually stop the
MySQL database server.  See the documentation
Section 2.2.1.6 Starting MySQL from the Windows
Command Line.  This is accomplished by executing
the following command at the command prompt:

C:\mysql\bin\mysqladmin -u root shutdown

To install the JDBC interface classes, I  copied
the jar file named
mysql-connector-java-3.0.15-ga-bin.jar into the
jre\lib\ext folder of my Java installation.  I
did this to avoid having to make changes to the
classpath.

I am currently running SDK v1.4.2.  When I
upgrade to a newer version of the SDK, it will be
necessary for me to copy the JDBC jar file into
the jre\lib\ext folder for the new version of the
SDK.

This program produces the following output as
a result of a successful run:

Copyright 2004, R.G.Baldwin
URL: jdbc:mysql://localhost:3306/mysql
Connection: com.mysql.jdbc.Connection@1430b5c

Tested using SDK 1.4.2 under WinXP, MySQL
version 4.0.21-win, and JDBC connector
version mysql-connector-java-3.0.15-ga.
************************************************/
import java.sql.*;

public class Jdbc11 {
 public static void main(String args[]){
   System.out.println(
                 "Copyright 2004, R.G.Baldwin");
   try {
     Statement stmt;

     //Register the JDBC driver for MySQL.
     Class.forName("com.mysql.jdbc.Driver");

     //Define URL of database server for
     // database named mysql on the localhost
     // with the default port number 3306.
     String url =
           "jdbc:mysql://localhost:3306/mysql";

     //Get a connection to the database for a
     // user named root with a blank password.
     // This user is the default administrator
     // having full privileges to do anything.
     Connection con =
                    DriverManager.getConnection(
                                url,"root", "");

     //Display URL and connection information
     System.out.println("URL: " + url);
     System.out.println("Connection: " + con);

     //Get a Statement object
     stmt = con.createStatement();

     //Create the new database
     stmt.executeUpdate(
                      "CREATE DATABASE JunkDB");
     //Register a new user named auser on the
     // database named JunkDB with a password
     // drowssap enabling several different
     // privileges.
     stmt.executeUpdate(
         "GRANT SELECT,INSERT,UPDATE,DELETE," +
         "CREATE,DROP " +
         "ON JunkDB.* TO 'auser'@'localhost' " +
         "IDENTIFIED BY 'drowssap';");
     con.close();
   }catch( Exception e ) {
     e.printStackTrace();
   }//end catch
 }//end main
}//end class Jdbc11

Listing 40 Jdbc11.java

/*File Jdbc12.java
Copyright 2004, R.G.Baldwin
Rev 09/18/04

The purpose of this program is to log onto
the master database named mysql as the default
administrator named root whose password is blank
in order to perform the following updates:

1. Remove a user named auser.
2. Delete a database named JunkDB.

These two operations produce no visible output
when successful.  However, they produce error
messages in the output when unsuccessful.

This program is the reverse of the program named
Jdbc11, which creates the database named JunkDB
and registers the user named auser on that
database.

The MySQL server must be running on localhost
before this program is started.

It is necessary to manually start the MySQL
database server running on localhost. See the
documentation sections 2.2.1.5,Starting the
Server for the First Time and 2.2.1.4 Selecting a
Windows Server. This is accomplished by executing
the following command at the command prompt:

C:\mysql\bin\mysqld-opt --console

Similarly, it is necessary to manually stop the
MySQL database server.  See the documentation
Section 2.2.1.6 Starting MySQL from the Windows
Command Line.  This is accomplished by executing
the following command at the command prompt:

C:\mysql\bin\mysqladmin -u root shutdown

To install the JDBC interface classes, I  copied
the jar file named
mysql-connector-java-3.0.15-ga-bin.jar into the
jre\lib\ext folder of my Java installation.  I
did this to avoid having to make changes to the
classpath.

I am currently running SDK v1.4.2.  When I
upgrade to a newer version of the SDK, it will be
necessary for me to copy the JDBC jar file into
the jre\lib\ext folder for the new version of the
SDK.

This program produces the following output as
a result of a successful run:

Copyright 2004, R.G.Baldwin
URL: jdbc:mysql://localhost:3306/mysql
Connection: com.mysql.jdbc.Connection@1430b5c

Tested using SDK 1.4.2 under WinXP, MySQL
version 4.0.21-win, and JDBC connector
version mysql-connector-java-3.0.15-ga.
************************************************/
import java.sql.*;

public class Jdbc12 {
 public static void main(String args[]){
   System.out.println(
                 "Copyright 2004, R.G.Baldwin");
   try {
     Statement stmt;

     //Register the JDBC driver for MySQL.
     Class.forName("com.mysql.jdbc.Driver");

     //Define URL of database server for
     // database named mysql on the localhost
     // with the default port number 3306.
     String url =
           "jdbc:mysql://localhost:3306/mysql";

     //Get a connection to the database for a
     // user named root with a blank password.
     // This user is the default administrator
     // having full privileges to do anything.
     Connection con =
                    DriverManager.getConnection(
                                url,"root", "");

     //Display URL and connection information
     System.out.println("URL: " + url);
     System.out.println("Connection: " + con);

     //Get a Statement object
     stmt = con.createStatement();

     //Remove the user named auser
     stmt.executeUpdate(
         "REVOKE ALL PRIVILEGES ON *.* " +
         "FROM 'auser'@'localhost'");
     stmt.executeUpdate(
         "REVOKE GRANT OPTION ON *.* " +
         "FROM 'auser'@'localhost'");
     stmt.executeUpdate(
         "DELETE FROM mysql.user WHERE " +
         "User='auser' and Host='localhost'");
     stmt.executeUpdate("FLUSH PRIVILEGES");

     //Delete the database
     stmt.executeUpdate(
                      "DROP DATABASE JunkDB");

     con.close();
   }catch( Exception e ) {
     e.printStackTrace();
   }//end catch
 }//end main
}//end class Jdbc12

Listing 41 Jdbc12.java

/*File Jdbc10.java
Copyright 2004, R.G.Baldwin
Rev 09/16/04

The purpose of this program is to test the
ability to use JDBC to access a MySQL database
server on localhost.

The MySQL server must be running on localhost
before this program is started.  In addition, a
database named JunkDB must have been created and
a user named auser must have been registered on
that database with a password of drowssap before
this program is started.

It is necessary to manually start the MySQL
database server running on localhost. See the
documentation sections 2.2.1.5,Starting the
Server for the First Time and 2.2.1.4 Selecting a
Windows Server. This is accomplished by executing
the following command at the command prompt:

C:\mysql\bin\mysqld-opt --console

Similarly, it is necessary to manually stop the
MySQL database server.  See the documentation
Section 2.2.1.6 Starting MySQL from the Windows
Command Line.  This is accomplished by executing
the following command at the command prompt:

C:\mysql\bin\mysqladmin -u root shutdown

You can prepare MySQL for use in three
alternative ways:
1. Using the control program named mysql located
in folder C:\mysql\bin in interactive mode.
2. Using a batch file to start the mysql program
along with a text file to provide the input.
3. By running the Java programs named Jdbc11 and
Jdbc12.

See the MySQL documentation Section 3.5, entitled
Using mysql in Batch Mode, for an explanation of
how to access the MySQL database server using
batch files and text files.

There is one bat file and one txt file used in
each case. The bat file logs into the mysql
monitor program as root having administrator
privileges and points to the txt file, which
provides the commands that are executed by the
monitor program.

The following two files create a new database
named JunkDB. See the documentation Section 3.3,
entitled Creating and Using a Database.

MySqCreateDatabase01.bat
MySqlCreateDatabase01.txt

The following two files make a new user named
auser registered on the database named JunkDB
with a password of drowssap. This user can
access the JunkDB database, but only from
localhost. See the documentation Section 5.6.2,
entitled Adding New User Accounts to MySQL.

MySqlMakeUser01.bat
MySqlMakeUser01.txt

Another way to create the database and register
a user named auser on that database is to run
the Java program named Jdbc11.

The following two files delete the database named
JunkDB. See documentation Section 14.2.8, DROP
DATABASE Syntax.

MySqDropDatabase01.bat
MySqlDropDatabase01.txt

The following two files remove the user named
auser. See the documentation Section 5.6.3,
Removing User Accounts from MySQL.

MySqlRemoveUser01.bat
MySqlRemoveUser01.txt

Another way to delete a database named JunkDB
and remove the user named auser is to run the
program named Jdbc12.

This program:
Accesses the database named JunkDB,
Creates a table named myTable,
Puts five rows of data into the table,
Displays the data,
Deletes the table.

Two different approaches are used to display the
contents of the table. The first approach
displays all of the data in the table. The
second approach displays only the data in a
specific row in the table.

As a precaution, before attempting to create the
new table, the program attempts to delete a table
having the same name. If a table having the same
name already exists as residue from a previous
run, it is deleted. If it doesn't already exist
when the attempt is made to delete it, an
exception is thrown. This exception is simply
caught and ignored.

To install the JDBC interface classes, I copied
the jar file named
mysql-connector-java-3.0.15-ga-bin.jar into the
jre\lib\ext folder of my Java installation. I
did this to avoid having to make changes to the
classpath.

I am currently running SDK v1.4.2. When I
upgrade to a newer version of the SDK, it will be
necessary for me to copy the JDBC jar file into
the jre\lib\ext folder for the new version of the
SDK.

This program produces the following output under
normal conditions where the table named myTable
does not exist when the program is started (the
specifics regarding the Connection object may
vary from one run to the next):

Copyright 2004, R.G.Baldwin
URL: jdbc:mysql://localhost:3306/JunkDB
Connection: com.mysql.jdbc.Connection@1430b5c
java.sql.SQLException: Base table or view not
found message from server: "Unknown table
'mytable'"No existing table to delete
Display all results:
test_id= 1 str = One
test_id= 2 str = Two
test_id= 3 str = Three
test_id= 4 str = Four
test_id= 5 str = Five
Display row number 2:
test_id= 2 str = Two

Tested using SDK 1.4.2 under WinXP, MySQL
version 4.0.21-win, and JDBC connector
version mysql-connector-java-3.0.15-ga.
************************************************/
import java.sql.*;

public class Jdbc10 {
 public static void main(String args[]){
   System.out.println(
                 "Copyright 2004, R.G.Baldwin");
   try {
     Statement stmt;
     ResultSet rs;

     //Register the JDBC driver for MySQL.
     Class.forName("com.mysql.jdbc.Driver");

     //Define URL of database server for
     // database named JunkDB on the localhost
     // with the default port number 3306.
     String url =
           "jdbc:mysql://localhost:3306/JunkDB";

     //Get a connection to the database for a
     // user named auser with the password
     // drowssap, which is password spelled
     // backwards.
     Connection con =
                    DriverManager.getConnection(
                       url,"auser", "drowssap");

     //Display URL and connection information
     System.out.println("URL: " + url);
     System.out.println("Connection: " + con);

     //Get a Statement object
     stmt = con.createStatement();

     //As a precaution, delete myTable if it
     // already exists as residue from a
     // previous run.  Otherwise, if the table
     // already exists and an attempt is made
     // to create it, an exception will be
     // thrown.
     try{
       stmt.executeUpdate("DROP TABLE myTable");
     }catch(Exception e){
       System.out.print(e);
       System.out.println(
                 "No existing table to delete");
     }//end catch

     //Create a table in the database named
     // myTable.
     stmt.executeUpdate(
           "CREATE TABLE myTable(test_id int," +
                 "test_val char(15) not null)");

     //Insert some values into the table
     stmt.executeUpdate(
               "INSERT INTO myTable(test_id, " +
                   "test_val) VALUES(1,'One')");
     stmt.executeUpdate(
               "INSERT INTO myTable(test_id, " +
                   "test_val) VALUES(2,'Two')");
     stmt.executeUpdate(
               "INSERT INTO myTable(test_id, " +
                 "test_val) VALUES(3,'Three')");
     stmt.executeUpdate(
               "INSERT INTO myTable(test_id, " +
                  "test_val) VALUES(4,'Four')");
     stmt.executeUpdate(
               "INSERT INTO myTable(test_id, " +
                  "test_val) VALUES(5,'Five')");

     //Get another statement object initialized
     // as shown.
     stmt = con.createStatement(
              ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);

     //Query the database, storing the result
     // in an object of type ResultSet
     rs = stmt.executeQuery("SELECT * " +
               "from myTable ORDER BY test_id");

     //Use the methods of class ResultSet in a
     // loop to display all of the data in the
     // database.
     System.out.println("Display all results:");
     while(rs.next()){
       int theInt= rs.getInt("test_id");
       String str = rs.getString("test_val");
       System.out.println("\ttest_id= " + theInt
                            + "\tstr = " + str);
     }//end while loop

     //Display the data in a specific row using
     // the rs.absolute method.
     System.out.println(
                       "Display row number 2:");
     if( rs.absolute(2) ){
       int theInt= rs.getInt("test_id");
       String str = rs.getString("test_val");
       System.out.println("\ttest_id= " + theInt
                            + "\tstr = " + str);
     }//end if

     //Delete the table and close the connection
     // to the database
     stmt.executeUpdate("DROP TABLE myTable");
     con.close();
   }catch( Exception e ) {
     e.printStackTrace();
   }//end catch
 }//end main
}//end class Jdbc10

Listing 42 Jdbc10.java

Using JDBC with MySQL 2

2007-04-15T21:54:00.000-07:00

Creating a New Database using the Monitor Program

Now that you know how to start the MySQL database server, it's time to learn how to:

Create a database that can be manipulated using JDBC in a Java program.

Create a new user having the necessary privileges to manipulate the database using JDBC in a Java program.

Three different approaches

I'm going to show you three different ways to accomplish this:

Using a command-line program named mysql coupled with manual data entry at runtime. (I will refer to this as the monitor program for reasons that will become self-evident later.)

Using the monitor program coupled with data input derived from a text file.

Using JDBC in a Java program.

I will illustrate the first approach in this and the later section entitled Creating a New User using the Monitor Program .

I will illustrate the second approach in the section entitled Administering the Database Server using Text Files.

I will illustrate the third approach in the section entitled Discussion and Sample Code, which shows how to use JDBC to manage and manipulate the database server.

The monitor program and manual data entry

The monitor program is named mysql.exe. It is located in c:\mysql\bin. This program makes it possible to log onto the database server and to enter commands at the command line to:

Create databases

Add new users

Modify databases

Perform ad-hoc queries, etc.

(Unless you really enjoy typing, the monitor program is not a lot of fun to use.)

To really learn MySQL ...

In order to really learn how to use MySQL, you will need to study the MySQL database server documentation in detail and probably some good SQL books as well.

The purpose of this lesson is to teach you just enough to get you started. When you finish this lesson, you should be able to successfully write and execute simple JDBC programs that will manipulate database tables on the MySQL database server.

The default administrative user

As I understand it, when the MySQL database server is first installed, there is a default user named root with full administrative privileges and no password. At this point, the server is totally wide open and insecure.

(The server documentation provides various suggestions as to what you should do to add security to the server.)

The user named root has the ability to create new databases as well as to create new users and to register those users on the databases.

Existing databases at MySQL installation

Also, as I understand it, there are two existing databases on the server when it is first installed. There is a database named test, which is wide open with no password requirements. Any user can access this database.

There is also a database named mysql, which is apparently used to keep track of things such as databases, users, etc. I believe that this database is accessible only by users having administrative privileges.

Adding a new database using the monitor program

The first step in adding a new database using the monitor program is to log onto the database server as the administrative user named root with access to the database named mysql. Until a password is assigned to the root user, login can be accomplished by entering the following command at the command prompt:

c:\mysql\bin\mysql --user=root mysql

(In case it isn't clear on your display, the word user is preceded by two minus sign characters.)

The screen output

Assuming that the MySQL database server is running, the screen output produced by entering this command is shown in Figure 3.

(Note that in Figure 3, and several of the figures that follow, it was necessary for me to manually enter line breaks in the screen output to force the material to fit in this narrow publication format.)

C:\jnk>c:\mysql\bin\mysql --user=root mysql
Welcome to the MySQL monitor. Commands end with ;
or \g.
Your MySQL connection id is 26 to server version:
4.0.21

Type 'help;' or '\h' for help. Type '\c' to clear
the buffer.

mysql>

Figure 3 Monitor output for administrator login.

The monitor program

Note that this program refers to itself as the MySQL monitor. That explains why I refer to it as the monitor or the monitor program.

The purpose of this program is to make it possible for you to enter SQL database commands from the keyboard. Note in particular the prompt shown in boldface at the end of Figure 3, which reads:

mysql>

This is not a command-line prompt, which typically looks something like:

C:\jnk>

Rather, this is a program-generated prompt where the monitor program is requesting input from the user.

SQL command terminators

As indicated in Figure 3, SQL commands end with either a semicolon character or \g (note the difference in typeface for the character g in this text relative to that shown in Figure 3).

SQL commands are often quite long. You can enter successive portions of SQL commands at successive program prompts.

(Later, we will see that the monitor program uses a different syntax for continuation prompts.)

It is not until you enter a semicolon character or a \g that the program responds to and attempts to execute the entire SQL command.

Terminating the monitor program

You can terminate the monitor program by entering a \q at the program prompt.

(Note that this is a q as in quit and is not a g as in good.)

Creating a new database named JunkDB

Figure 4 shows the screen output for the use of the monitor program by the user named root to create a new database named JunkDB and then to terminate the monitor program.

C:\jnk>c:\mysql\bin\mysql --user=root mysql
Welcome to the MySQL monitor. Commands end with ;
or \g.
Your MySQL connection id is 27 to server version:
4.0.21

Type 'help;' or '\h' for help. Type '\c' to clear
the buffer.

mysql> CREATE DATABASE JunkDB;
Query OK, 1 row affected (0.13 sec)

mysql> \q
Bye

C:\jnk>

Figure 4 Creating database named JunkDB

The new material in Figure 4 is shown in boldface in the bottom half of the figure. The material in the top half of Figure 4 is a repeat of the material shown in Figure 3.

Using a batch file and a text file

Shortly, I will show you how to create a new database using a Windows batch file and an associated text file. Later on, I will show you how to create a new database using a Java JDBC program.

Creating a New User with the Monitor Program

Figure 5 shows the screen output for the use of the monitor program by the user named root to add a new user named auser.

The new material is shown in boldface in the lower half of the figure.

C:\jnk>c:\mysql\bin\mysql --user=root mysql
Welcome to the MySQL monitor. Commands end with ;
or \g.
Your MySQL connection id is 30 to server version:
4.0.21

Type 'help;' or '\h' for help. Type '\c' to clear
the buffer.

mysql> GRANT SELECT,INSERT,UPDATE,
-> DELETE,CREATE,DROP
-> ON JunkDB.*
-> TO 'auser'@'localhost'
-> IDENTIFIED BY 'drowssap';
Query OK, 0 rows affected (0.01 sec)

mysql> \q
Bye

C:\jnk>

Figure 5 Adding new user named auser.

A longer SQL command

This SQL command is much longer than the command used to create the new database. This command requires several continuation lines to complete to prevent it from exceeding the screen width.

(Note the difference in the syntax of new program prompts and continuation program prompts. The prompts that look like an arrow are the continuation prompts.)

I won't try to explain the SQL command is detail. I will simply refer you to the MySQL database documentation and a good SQL book for that purpose. However, the SQL command is relatively self explanatory.

The meaning of the SQL command

This SQL command grants a list of six different privileges on the database named JunkDB to a user named auser who will be accessing the database from localhost.

(Granting access to the same user from a different machine on the network would require a different syntax.)

The user named auser will be allowed to access the database named JunkDB using the password drowssap, (which is password spelled backwards to make it easy for me to remember).

Administering the Database Server using Text Files

As mentioned earlier, unless you really enjoy typing, using the monitor program in manual data entry mode is not a fun way to work with the database. For example, if you make a typing error, you must go back and retype the entire command from the beginning.

Fortunately, there is a better approach. That approach is to provide the commands to the monitor program using a text file as input. Then if you make an error, you can simply edit the text file and rerun the process.

How does it work?

To make a long story short, you start the monitor program by redirecting the input so that the input is derived from a text file instead of from the keyboard. This process is described in the database server documentation, Section 3.5 entitled Using mysql in Batch Mode.

Creating a new database

There are probably several ways to set this process up. I elected to use a combination of a batch file and a text file. The batch file starts the monitor program, logging in as root, and redirects input to the associated text file.

For example, the files used to create a new database named JunkDB are shown in Listing 32 and Listing 33 near the end of the lesson.

Making a new user

The files used to make a new user named auser are shown in Listing 34 and Listing 35.

Compare the contents of these two files with the manual data entry shown earlier in Figure 5. The new user is granted six different privileges on the database named JunkDB from localhost with a password of drowssap.

Removing the user named auser

The files used to remove the user named auser are shown in Listing 36 and Listing 37. The procedure for revoking a user's privileges and removing the user is explained near the end of Section 14.5.1.1 entitled DROP USER Syntax in the database server documentation.

Deleting the database named JunkDB

The files used to delete the database named JunkDB are shown in Listing 38 and Listing 39. The procedure for deleting a database is explained in the database server documentation, Section 14.2.8 entitled DROP DATABASE Syntax.

Using JDBC with MySQL 1

2007-04-15T21:18:00.000-07:00

Preface

Purpose

The purpose of this lesson is to get you beyond the initial hurdles involved in:

Downloading and installing a MySQL database server.

Preparing that database for use with JDBC.

Writing and testing your first JDBC programs to administer the database and to manipulate the data stored in the MySQL database.

What is JDBC?

JDBC technology is an API (included in both J2SE and J2EE) that provides cross-DBMS connectivity to a wide range of SQL databases and access to other tabular data sources, such as spreadsheets or flat files.

What is MySQL?

The MySQL database server is probably the world's most popular open source database software, with more than five million active installations as of September 2004.

The database server software from MySQL is available under a "dual licensing" model. Under this model, users may choose to use MySQL products under the free software/open source GNU General Public License (commonly known as the "GPL") or under a commercial license.

A powerful combination

Simply stated, JDBC makes it possible to write platform independent Java programs that can be used to manipulate the data in a wide range of SQL databases without the requirement to modify and/or recompile the Java programs when moving from platform to platform or from DBMS to DBMS.

MySQL is available for a wide variety of platforms.

Since both JDBC and MySQL are freely available for many purposes, the combination of JDBC and MySQL is a powerful combination that should be of interest for a wide variety of applications.

Preview

Download, install, and prepare the database server

It is often possible to use the same Java program to manipulate the data in a wide variety of SQL databases without a requirement to modify and/or recompile the Java program.

However, the installation and preparation procedures for different SQL databases vary widely. A large part of the battle in using JDBC with a particular database is getting the database installed and properly prepared for use with JDBC.

In this lesson, I will show you how to download, install, and prepare a MySQL database as a localhost server on a Windows platform for use with JDBC. Then I will show you how to write three simple JDBC programs to administer the database server and to manipulate data stored on the database server after it is installed.

Installation and Preparation of MySQL

Getting things up and running

As a minimum, getting up and running with MySQL and JDBC involves at least the following steps:

Download and install the appropriate release of the MySQL database server software (several different releases are available).

Download and install the MySQL Connector/J -- for connecting to a MySQL database server from Java.

Download and install the documentation for the MySQL database server.

Download and install the documentation for the Connector, which is a separate documentation package from the database server documentation.

Write and test one or more JDBC programs that will act as a database administrator, creating one or more users and possibly one or more databases on the database server. (I will show you three different ways to accomplish this.)

Write and test a JDBC program that will log in as a user and manipulate data stored in one of those databases.

Additional MySQL software

Beyond the minimum, there are a variety of additional software packages, (such as GUI administrator packages) that can be downloaded from MySQL and installed on your computer.

Since the main thrust of this lesson has to do with JDBC rather than database administration, I won't get into that. Rather, I will show you how to use a command-line monitor program that is included with the MySQL database software to perform the minimal database administrative tasks required to satisfy the objectives of this lesson.

Documentation

MySQL database server documentation

The MySQL Reference Manual can be downloaded from http://dev.mysql.com/doc/. In addition, there is an online searchable version of the Reference Manual available at http://dev.mysql.com/doc/mysql/en/Reference.html.

The downloadable version is available in several different formats, including:

HTML, one page per chapter, ZIP

HTML, all on one page, ZIP

You would probably do well to have both of these formats locally available on your computer if you have sufficient disk space.

The one-page-per-chapter formatted manual

The first format in the above list consists of a large number of HTML files. There is one HTML file for the table of contents plus about thirty-three additional files containing the text of the reference manual.

This format has a major advantage over the second format in terms of speed. It is relatively fast to click on a hyperlink in the table of contents and to see that material appear in the browser window.

There are a couple of downsides to this format, however. One downside is that this format is not very useful for searching the entire manual for keywords, (using your browser) because it is broken down into a large number of separate HTML files.

A second downside is that even though MySQL 4.0 is the recommended release in September of 2004, this manual contains information up through version 5.0.1-alpha. Sometimes information about the newer versions tends to obscure information about version 4.0.

The all-on-one-page formatted manual

This format is very useful for searching (using your browser) because all of the text is in a single HTML file.

(There are actually two HTML files. One file contains a hyperlinked table of contents. The second file contains the text of the entire manual.)

The primary downside to this format is speed, or lack thereof. The HTML file containing the text of the manual is about four megabytes in size. On my machine, navigating this manual in a browser is a very slow process.

The downloadable version in this format also contains information up through version 5.0.1-alpha, resulting in the same disadvantage mentioned earlier.

Included in the software distribution

When you download and install the currently recommended version of MySQL, (which is version 4.0.21), the Docs folder in the installation tree structure will contain a copy of the manual in the all-on-one-page format that purports to be for version 4.0.21. Thus, you don't need to download this format separately. You will get it when you download the software.

(Even though this version purports to be for version 4.0.21, it also contains a lot of information about later versions. It may be exactly the same as the version that can be downloaded separately except that the title page is different.)

Installation of the all-on-one-page formatted manual

As mentioned above, you don't need to do anything special to install this format of the manual. It will be installed automatically when you install the MySQL 4.0.21 version of the database.

(Presumably, later versions of the software will also contain a copy of the current manual in this format.)

Once you have installed the database, the Docs folder of the installation tree will contain the files named manual_toc.html and manual.html. The first file contains a hyperlinked table of contents, and the second file contains the entire text of the manual.

Using both formats

Because I have plenty of space on my disk, I have both formats installed on my computer with an icon on the desktop for each. I occasionally open the version that contains the entire manual in a single HTML file when I need to search the entire document for something. Most of the time, however, I open and use the multi-file version due to its increased speed.

MySQL Connector/J documentation

I did not find a separate downloadable version of the connector documentation at the MySQL site. However, I did find an online version at http://dev.mysql.com/doc/connector/j/en/index.html. I was able to save the connector documentation locally by selecting the Save Page As... item on the File menu of my Netscape 7.2 browser.

(I was unable to save the page locally using Internet Explorer version 6 for some reason. However, I also discovered later that essentially the same documentation is contained in the downloadable zip file for the connector software in a file named mysql-connector-java-3.0.15-ga\docs\index.html.)

Saving the page in Netscape 7.2 resulted in a local file named index.html and an associated folder named index_files. The file contains the text of the connector documentation. The folder contains style sheets and other related material.

Installation of the connector documentation consisted simply of saving this material locally and creating a desktop icon linked to the file named index.html.

Downloading the MySQL Database Server

The download page

The main download page for both the database server and the connector as of September 2004 is http://dev.mysql.com/downloads/. Hopefully, this URL will also remain intact as MySQL releases later versions of the software.

Several different versions of the database server are available for downloading as of September 2004, including:

MySQL 4.0 -- Generally Available (GA) release (recommended)

MySQL 4.1 -- Gamma release (use this for new development)

MySQL 5.0 -- Alpha release (use this for previewing and testing new features)

MySQL 5.0.1 -- Snapshot release (use this for previewing and testing new features)

Older releases -- older releases (only recommended for special needs)

Snapshots -- source code snapshots of the development trees

This list can be expected to change over time as new versions of the database server are released. Thus, the links in the above list will become obsolete. When that happens, you should revert back to the download page at http://dev.mysql.com/downloads/ and download the version that best suits your needs at that time.

The different versions of the database server

As of September 2004, the database server documentation has this to say about these different versions:

MySQL 5.0 is the newest development release series and is under very active development for new features. Alpha releases have been issued to allow more widespread testing.

MySQL 4.1 is in gamma status, soon moving to production status.

MySQL 4.0 is the current stable (production-quality) release series. New releases are issued for bugfixes. No new features are added that could diminish the code stability.

MySQL 3.23 is the old stable (production-quality) release series. This series is retired, so new releases are issued only to fix critical bugs.

I elected MySQL 4.0

I elected to download MySQL 4.0 since it is the stable production quality version as of September 2004. This resulted in the downloading of a distribution file named mysql-4.0.21-win.zip.

Installing MySQL Database Server

Installation instructions

Installation instructions for the database server are provided in the database server documentation, Section 2, entitled Installing MySQL.

Since I was installing on Windows XP and had no desire to deal with source code, I quickly skipped down to Section 2.2.1.2 entitled Installing a Windows Binary Distribution.

In my case, installation was easy

Because I did not have an earlier version of MySQL installed and I was logged onto Windows as an administrator, all that I needed to do was to execute the following instructions from the database server documentation to install the MySQL database server on my computer.

Unzip the distribution file to a temporary directory.

Run the setup.exe program to begin the installation process. If you want to install MySQL into a location other than the default directory (`C:\mysql'), use the Browse button to specify your preferred directory. If you do not install MySQL into the default location, you will need to specify the location whenever you start the server. The easiest way to do this is to use an option file, as described in Section 2.2.1.3 Preparing the Windows MySQL Environment.

Because I didn't want to deal with option files, I elected to allow the software to be installed in the default directory, C:\mysql.

Testing the installation

After completing the installation, I performed some of the procedures shown in the database server documentation, Section 2.4.1 entitled Windows Post-Installation Procedures. Although I didn't get exactly the same results as those shown in the documentation, my results were close enough to convince me that the MySQL database server was correctly installed on my computer.

Not installed as a Windows service

Section 2.2.1.7 of the database server documentation entitled Starting MySQL as a Windows Service contains the following:

"On the NT family (Windows NT, 2000, or XP), the recommended way to run MySQL is to install it as a Windows service. Then Windows starts and stops the MySQL server automatically when Windows starts and stops."

I have no desire for the MySQL database server to start running every time I start Windows running. I already waste enough time waiting for Windows XP to become ready for use on my laptop each time I start it.

Therefore, I did not install the database server as a service. I will explain how I manually start and stop the database server whenever I need to use it later in this lesson.

Downloading MySQL Connector/J

What is MySQL Connector/J?

For those who don't know, let me begin by explaining the purpose of MySQL Connector/J.

The JDBC API is designed to make it possible for you to write a single Java program and to use it to manipulate the data in a variety of different SQL database servers without a requirement to modify and/or recompile the program. In order to do this, it is necessary for you to:

Inform the Java program as to the URL of the database server. You can accomplish this with input data when you start the program.

Provide the Java program with a programming interface to the specific database server that you intend to use. Assuming that the programming interface has been installed on your computer, you can also accomplish this with input data when you run the program.

The programming interface

The programming interface deals with the interface peculiarities of the different database servers.

Sun refers to the process of providing this information to the program as registering the database server with the Java program. You will see how this is done in the sample programs later in this lesson.

The connector download page

The download page for MySQL Connector/J is http://dev.mysql.com/downloads/index.html. As of September 2004, the following versions are available for downloading from this page:

MySQL Connector/J 3.1 -- development release

MySQL Connector/J 3.0 -- production release

Older releases -- older releases (only recommended for special needs)

Snapshots -- source code snapshots of the development trees

As with the MySQL database server software, these individual links are likely to become obsolete as new versions of the software are released. Hopefully the link to http://dev.mysql.com/downloads/index.html will remain intact.

The MySQL Connector/J 3.0 distribution file

Because I was very interested in stability, I elected to download and install MySQL Connector/J 3.0, identified above as the production release. This resulted in the download of a file named mysql-connector-java-3.0.15-ga.zip.

This zip file encapsulates 194 individual files in different folders including source code files, class files, pdf files, xml files, jar files, license files, files with no extensions, a manifest file, HTML files, and other file types not listed here.

The zip file also contains several java programs in a folder named testsuite that can be used to test your installation. You may find them useful for that purpose. In addition, these programs illustrate a variety of database operations using JDBC, so you may find them useful as example programs as well.

Fortunately, as I will explain below, all but one of these files can be ignored insofar as installation of the connector software is concerned.

Installing MySQL Connector/J

General installation instructions

The following statement appears in the connector documentation Section 2.2.1. entitled Setting the CLASSPATH (For Standalone Use).

"Once you have un-archived the distribution archive, you can install the driver in one of two ways: Either copy the "com" and "org" subdirectories and all of their contents to anywhere you like, and put the directory holding the "com" and "org" subdirectories in your classpath, or put mysql-connector-java-[version]-bin.jar in your classpath, either by adding the FULL path to it to your CLASSPATH environment variable, or by copying the .jar file to $JAVA_HOME/jre/lib/ext."

My installation

Actually, the above quotation describes three options instead of just two. To make a long story short, I elected the third option. I extracted the jar file named mysql-connector-java-3.0.15-ga-bin.jar from the zip file and copied it into the folder named c:\j2sdk1.4.2\jre\lib\ext, which is the installation directory tree for the currently installed version of Java on my computer.

The advantage of doing it this way was that I didn't have to modify the classpath environment variable. The disadvantage is that the next time I upgrade to a new version of Java, I must remember to save the MySQL connector jar file and copy it into the directory tree for my new Java installation.

Your installation

If you prefer the first option, the connector documentation contains a wealth of information to help you perform the necessary steps to modify the classpath, etc.

Testing the installation

I didn't use any of the test programs mentioned above in the folder named testsuite. Rather, I tested my installation using JDBC programs that I had developed earlier using a different SQL database server.

You can use the test programs mentioned earlier in the testsuite folder to test your installation. Also, I will provide and explain three sample JDBC programs later in this lesson that you can use to test your installation. Before you can test the installation, however, you must start the MySQL database server running.

Starting the database server

At this point, all of the software necessary to use the database server in a JDBC program should be installed on your computer ready for use. The next step is to confirm that you can start the database server running.

Selecting a Windows server

I found it necessary to pull together several pieces of information from the database server documentation to determine the best way to start the server from a command line. For example, the following table is found in the database server documentation, Section 2.2.1.4 entitled Selecting a Windows Server.

Binary	Description
mysqld	Compiled with full debugging and automatic memory allocation checking, symbolic links, and InnoDB and BDB tables.
mysqld-opt	Optimized binary. From version 4.0 on, InnoDB is enabled. Before 4.0, this server includes no transactional table support.
mysqld-nt	Optimized binary for Windows NT, 2000, and XP with support for named pipes.
mysqld-max	Optimized binary with support for symbolic links, and InnoDB and BDB tables.
mysqld-max-nt	Like mysqld-max, but compiled with support for named pipes.

Explaining the different types of servers

The following explanation follows the table:

"We have found that the server with the most generic name (mysqld) is the one that many users are likely to choose by default. However, that is also the server that results in the highest memory and CPU use due to the inclusion of full debugging support. The server named mysqld-opt is a better general-use server choice to make instead if you don't need debugging support and don't want the maximal feature set offered by the -max servers or named pipe support offered by the -nt servers."

Changes in MySQL 4.1.2

This is followed by another explanation indicating that beginning with MySQL 4.1.2, the server names were changed eliminating the server name mysqld-opt and replacing the debug version (mysqld) with mysqld-debug. Therefore, if you are installing MySQL 4.1.2 or a later version, you should use the syntax mysqld instead of mysqld-opt to start the server running from an optimized binary file.

Starting MySQL 4.0.21

Since I am running MySQL 4.0.21 and need to make certain that what I am doing is compatible with a large number of students having different operating systems, I concluded that I should start the server running by using the syntax mysqld-opt.

Section 2.2.1.5 of the database server documentation entitled Starting the Server for the First Time indicates that the following command should be used at the command prompt to start the server running:

C:\mysql\bin\mysqld --console

As I understand it, the purpose of --console is to cause error messages to be displayed on the standard error device (typically the screen) rather than to be entered into an error log file. This is what I want to happen.

Combining the two pieces of information given above, I concluded that I should start the MySQL database server by entering the following command at a command prompt:

C:\mysql\bin\mysqld-opt --console

Encapsulated in a batch file

Therefore, I created a batch file named MySqlStart.bat and linked that file to an icon on the desktop for convenience. The batch file contains the two commands shown in Listing 1 and repeated later in Listing 30 near the end of the lesson.

C:\mysql\bin\mysqld-opt --console
pause

Listing 1 Contents of MySqlStart.bat

The startup screen output

Figure 1 shows the screen output following the execution of the batch file named MySqlStart.bat.

(Note that it was necessary for me to manually enter a line break ahead of the word port to cause the screen output to fit in this narrow publication format.)

C:\mysql>C:\mysql\bin\mysqld-opt --console
040918 13:59:47 InnoDB: Started
C:\mysql\bin\mysqld-opt: ready for connections.
Version: '4.0.21' socket: ''
port: 3306 Source distribution

Figure 1 MySQL database server startup sequence

The process window shown in Figure 1 remains open and active until the server is stopped. It should be possible to connect to the server using JDBC during this period.

Stopping the database server

It is probably a good idea to shut down the server before shutting down the computer. Section 2.2.1.6 of the database server documentation entitled Starting MySQL from the Windows Command Line states that you can stop the MySQL server by executing the following command:

C:\mysql\bin\mysqladmin -u root shutdown

Therefore, I created a batch file named MySqlStop.bat and linked that file to an icon on my desktop to make it convenient to stop the server. The batch file contains the two commands shown in Listing 2 and repeated in Listing 31 near the end of the lesson.

C:\mysql\bin\mysqladmin -u root shutdown
pause

Listing 2 Contents of MySqlStop.bat

The screen output at server shutdown

Figure 2 shows the screen output in the server process window when the file named MySqlStop.bat is executed.

040918 14:00:02 C:\mysql\bin\mysqld-opt:
Normal shutdown

040918 14:00:03 InnoDB: Starting shutdown...
040918 14:00:05 InnoDB: Shutdown completed
040918 14:00:05 C:\mysql\bin\mysqld-opt:
Shutdown Complete


C:\mysql>pause
Press any key to continue . . .

Figure 2 Screen output when MySQL server is
        stopped.

(As before, it was necessary for me to manually enter line breaks in Figure 2 to cause the screen output to fit in this narrow publication format.)

Once the server is shut down, attempts to connect to the server from JDBC will fail.

Oracle basic training

2007-04-11T00:05:00.000-07:00

- What is Oracle?
a relational database management system (rdbms) produced by Oracle corporation in the late seventies.

- What is a database?
a mechanism for storage, retrieval, and maintenance of data or related information.

- What is a relational database?
a series of separate (but relatable) tables of information

- what is table?
a set of closely related data items comprised of rows and columns.

- what is a row?
a record of information which is comprised of logicaly related data itmes.

- What is a column?
a specific type or category of information.

- Each table contains data about some major item of inerest.
typical tables may contain data on students or animals.
tables are frequently referred to as entities.

- Each individual row in a table represents one unique occurrence of that item or enity.
a row in the student table represents a unique student.
a row in the animal table represents a unique animal.

- Each column in the table contains a specific piece of information for each row.
the last name column will contain different values (last name) for each row (or student) in the student table.

- All rdbms's provide a method to query (i.e., extract and display) data contained in tables.
this is refered to as the "Query Language."

- many rdbms's have chosen to standardize on a query language called SQL (pronounced SEE-QUIL).
SQL stands for "Structured Query Language."

- SQL provides many different ways to manipulate and display data in tables.

- SQL allows specifie columns and rows from a table to be displayed.

- Data are frequently required from two or more seperate tables on the same report.
this requires that the tables be "joined" together to obtain the desired output.
this requires that the tables have a common column to match rows.

- To join tables, an rdbms follows these steps:
1. retrieve a row from one table.
2. match the value found in a specified column to the values in a similar column for all rows in the second tables.
3. if a matching value is found, retrieve that row from the second table and return (display) the rows from both tables.

- The Oracle rdbms is comprised of several discrete pieces:
1. the database data: the actual tables containing the raw data.
2. the kernel: a software program that contains all access to the database.
2. the Oracle products (tools): seperate programs that perform various tasks and functions. each product communicates with the database through the kernel.

- SQL *Plus is the primary query tool in Oracle. an ansi standard strucured query language "plus" additional commands.

- SQL *Form is used to create data entry screens and menus.

- SQL *Menu is used to create menu.

- SQL *ReportWriter is used to create complex reports.

- Oracle *Forms is used to create data entry screens and menus in a GUI environment.

- Oracle *Reports is used to create complex reports in a GUI environment.

- Oracle *Graphics is used to create applications incorporating charts and displays in a GUI environment.

- Procedure Builder is used to develop PL/SQL program units.

Chapter 5. Database Administration

2007-04-09T23:10:00.000-07:00

5.1. Overview of Server-Side Programs

The MySQL server, mysqld, is the main program that does most of the work in a MySQL installation. The server is accompanied by several related scripts that perform setup operations when you install MySQL or that assist you in starting and stopping the server. This section provides an overview of the server and related programs. The following sections provide more detailed information about each of these programs.

Each MySQL program takes many different options. Most programs provide a --help option that you can use to get a description of the program's different options. For example, try mysqld --help.

You can override default option values for MySQL programs by specifying options on the command line or in an option file.

The following list briefly describes the MySQL server and server-related programs:

mysqld

The SQL daemon (that is, the MySQL server). To use client programs, mysqld must be running, because clients gain access to databases by connecting to the server. See Section 5.2, “mysqld — The MySQL Server”.
mysqld_safe

A server startup script. mysqld_safe attempts to start mysqld. See Section 5.3.1, “mysqld_safe — MySQL Server Startup Script”.
mysql.server

A server startup script. This script is used on systems that use System V-style run directories containing scripts that start system services for particular run levels. It invokes mysqld_safe to start the MySQL server. See Section 5.3.2, “mysql.server — MySQL Server Startup Script”.
mysqld_multi

A server startup script that can start or stop multiple servers installed on the system. See Section 5.3.3, “mysqld_multi — Manage Multiple MySQL Servers”. As of MySQL 5.0.3 (Unix-like systems) or 5.0.13 (Windows), an alternative to mysqld_multi is mysqlmanager, the MySQL Instance Manager. See Section 5.4, “mysqlmanager — The MySQL Instance Manager”.
mysqlmanager

The MySQL Instance Manager, a program for monitoring and managing MySQL servers. See Section 5.4, “mysqlmanager — The MySQL Instance Manager”.

There are several other programs that are related to MySQL installation or upgrading:

comp_err

This program is used during the MySQL build/installation process. It compiles error message files from the error source files. See Section 5.5.1, “comp_err — Compile MySQL Error Message File”.
make_binary_distribution

This program makes a binary release of a compiled MySQL. This could be sent by FTP to /pub/mysql/upload/ on ftp.mysql.com for the convenience of other MySQL users.
make_win_bin_dist

This program is used on Windows. It packages a MySQL distribution for installation after the source distribution has been built. See Section 5.5.2, “make_win_bin_dist — Package MySQL Distribution as ZIP Archive”.
mysql_fix_privilege_tables

This program is used after a MySQL upgrade operation. It updates the grant tables with any changes that have been made in newer versions of MySQL. See Section 5.5.4, “mysql_fix_privilege_tables — Upgrade MySQL System Tables”.

Note: As of MySQL 5.0.19, this program has been superseded by mysql_upgrade.
mysql_install_db

This script creates the MySQL database and initializes the grant tables with default privileges. It is usually executed only once, when first installing MySQL on a system. See Section 2.4.15.2, “Unix Post-Installation Procedures”, and Section 5.5.5, “mysql_install_db — MySQL Data Directory Initialization Script”.
mysql_secure_installation

This program enables you to improve the security of your MySQL installation. SQL. See Section 5.5.6, “mysql_secure_installation — Improve MySQL Installation Security”.
mysql_tzinfo_to_sql

This program loads the time zone tables in the mysql database using the contents of the host system zoneinfo database (the set of files describing time zones). SQL. See Section 5.5.7, “mysql_tzinfo_to_sql — Load the Time Zone Tables”.
mysql_upgrade

This program is used after a MySQL upgrade operation. It checks tables for incompatibilities and repairs them if necessary, and updates the grant tables with any changes that have been made in newer versions of MySQL. See Section 5.5.8, “mysql_upgrade — Check Tables for MySQL Upgrade”.
make_win_src_distribution

This program is used on Unix or Unix-like systems to create a MySQL source distribution that can be compiled on Windows. See Section 2.4.14.6.4, “Creating a Windows Source Package from the Latest Development Source”, and Section 5.5.3, “make_win_src_distribution — Create Source Distribution for Windows”.

Chapter 4. Using MySQL Programs

2007-04-09T22:33:00.000-07:00

This chapter provides a brief overview of the command-line programs provided by MySQL AB and discusses the general syntax for specifying options when you run these programs. Most programs have options that are specific to their own operation, but the option syntax is similar for all of them. Later chapters provide more detailed descriptions of individual programs, including which options they recognize.

MySQL AB also provides three GUI client programs for use with MySQL Server:

MySQL Administrator: This tool is used for administering MySQL servers, databases, tables, and user accounts.
MySQL Query Browser: This graphical tool is provided by MySQL AB for creating, executing, and optimizing queries on MySQL databases.
MySQL Migration Toolkit: This tool helps you migrate schemas and data from other relational database management systems for use with MySQL.

These GUI programs each have their own manuals that you can access at http://dev.mysql.com/doc/.

4.1. Overview of MySQL Programs

MySQL AB provides several types of programs:

The MySQL server and server startup scripts:
- mysqld is the MySQL server.
- mysqld_safe, mysql.server, and mysqld_multi are server startup scripts.
- mysql_install_db initializes the data directory and the initial databases.
- MySQL Instance Manager monitors and manages MySQL Server instances.
Client programs that access the server:
- mysql is a command-line client for executing SQL statements interactively or in batch mode.
- mysqladmin is an administrative client.
- mysqlcheck performs table maintenance operations.
- mysqldump and mysqlhotcopy make database backups.
- mysqlimport imports data files.
- mysqlshow displays information about databases and tables.
Utility programs that operate independently of the server:
- myisamchk performs table maintenance operations.
- myisampack produces compressed, read-only tables.
- mysqlbinlog is a tool for processing binary log files.
- perror displays the meaning of error codes.

Most MySQL distributions include all of these programs, except for those programs that are platform-specific. (For example, the server startup scripts are not used on Windows.) The exception is that RPM distributions are more specialized. There is one RPM for the server, another for client programs, and so forth. If you appear to be missing one or more programs, see Chapter 2, Installing and Upgrading MySQL, for information on types of distributions and what they contain. It may be that you have a distribution that does not include all programs and you need to install something else.

4.2. Invoking MySQL Programs

To invoke a MySQL program from the command line (that is, from your shell or command prompt), enter the program name followed by any options or other arguments needed to instruct the program what you want it to do. The following commands show some sample program invocations. “shell>” represents the prompt for your command interpreter; it is not part of what you type. The particular prompt you see depends on your command interpreter. Typical prompts are $ for sh or bash, % for csh or tcsh, and C:\> for the Windows command.com or cmd.exe command interpreters.

shell> mysql -u root test
shell> mysqladmin extended-status variables
shell> mysqlshow --help
shell> mysqldump --user=root personnel

Arguments that begin with a single or double dash (‘-’, ‘--’) are option arguments. Options typically specify the type of connection a program should make to the server or affect its operational mode.

Non-option arguments (arguments with no leading dash) provide additional information to the program. For example, the mysql program interprets the first non-option argument as a database name, so the command mysql -u root test indicates that you want to use the test database.

Later sections that describe individual programs indicate which options a program understands and describe the meaning of any additional non-option arguments.

Some options are common to a number of programs. The most common of these are the --host (or -h), --user (or -u), and --password (or -p) options that specify connection parameters. They indicate the host where the MySQL server is running, and the username and password of your MySQL account. All MySQL client programs understand these options; they allow you to specify which server to connect to and the account to use on that server.

Other connection options are --port (or -P) to specify a TCP/IP port number and --socket (or -S) to specify a Unix socket file on Unix (or named pipe name on Windows).

The default hostname is localhost. For client programs on Unix, the hostname localhost is special. It causes the client to connect to the MySQL server through a Unix socket file. This occurs even if a --port or -P option is given to specify a port number. To ensure that the client makes a TCP/IP connection to the local server, use --host or -h to specify a hostname value of 127.0.0.1, or the IP address or name of the local server. You can also specify the connection protocol explicitly, even for localhost, by using the --protocol=tcp option.

You may find it necessary to invoke MySQL programs using the pathname to the bin directory in which they are installed. This is likely to be the case if you get a “program not found” error whenever you attempt to run a MySQL program from any directory other than the bin directory. To make it more convenient to use MySQL, you can add the pathname of the bin directory to your PATH environment variable setting. That enables you to run a program by typing only its name, not its entire pathname. For example, if mysql is installed in /usr/local/mysql/bin, you'll be able to run it by invoking it as mysql; it will not be necessary to invoke it as /usr/local/mysql/bin/mysql.

Consult the documentation for your command interpreter for instructions on setting your PATH variable. The syntax for setting environment variables is interpreter-specific.

4.3. Specifying Program Options

There are several ways to specify options for MySQL programs:

List the options on the command line following the program name. This is most common for options that apply to a specific invocation of the program.
List the options in an option file that the program reads when it starts. This is common for options that you want the program to use each time it runs.
List the options in environment variables. This method is useful for options that you want to apply each time the program runs. In practice, option files are used more commonly for this purpose. However, Section 5.12.2, “Running Multiple Servers on Unix”, discusses one situation in which environment variables can be very helpful. It describes a handy technique that uses such variables to specify the TCP/IP port number and Unix socket file for both the server and client programs.

MySQL programs determine which options are given first by examining environment variables, then by reading option files, and then by checking the command line. This means that environment variables have the lowest precedence and command-line options the highest.

Because options are processed in order, if an option is specified multiple times, the last occurrence takes precedence. The following command causes mysql to connect to the server running on localhost:

shell> mysql -h example.com -h localhost

If conflicting or related options are given, later options take precedence over earlier options. The following command runs mysql in “no column names” mode:

shell> mysql --column-names --skip-column-names

An option can be specified by writing it in full or as any unambiguous prefix. For example, the --compress option can be given to mysqldump as --compr, but not as --comp because that is ambiguous:

shell> mysqldump --comp
mysqldump: ambiguous option '--comp' (compatible, compress)

Be aware that the use of option prefixes can cause problems in the event that new options are implemented for a program. A prefix that is unambigious now might become ambiguous in the future.

You can take advantage of the way that MySQL programs process options by specifying default values for a program's options in an option file. That enables you to avoid typing them each time you run the program, but also allows you to override the defaults if necessary by using command-line options.

4.3.1. Using Options on the Command Line

Program options specified on the command line follow these rules:

Options are given after the command name.
An option argument begins with one dash or two dashes, depending on whether it has a short name or a long name. Many options have both forms. For example, -? and --help are the short and long forms of the option that instructs a MySQL program to display its help message.
Option names are case sensitive. -v and -V are both legal and have different meanings. (They are the corresponding short forms of the --verbose and --version options.)
Some options take a value following the option name. For example, -h localhost or --host=localhost indicate the MySQL server host to a client program. The option value tells the program the name of the host where the MySQL server is running.
For a long option that takes a value, separate the option name and the value by an ‘=’ sign. For a short option that takes a value, the option value can immediately follow the option letter, or there can be a space between: -hlocalhost and -h localhost are equivalent. An exception to this rule is the option for specifying your MySQL password. This option can be given in long form as --password=pass_val or as --password. In the latter case (with no password value given), the program prompts you for the password. The password option also may be given in short form as -ppass_val or as -p. However, for the short form, if the password value is given, it must follow the option letter with no intervening space. The reason for this is that if a space follows the option letter, the program has no way to tell whether a following argument is supposed to be the password value or some other kind of argument. Consequently, the following two commands have two completely different meanings:
```
shell> mysql -ptest
shell> mysql -p test 
```
The first command instructs mysql to use a password value of test, but specifies no default database. The second instructs mysql to prompt for the password value and to use test as the default database.

Some options control behavior that can be turned on or off. For example, the mysql client supports a --column-names option that determines whether or not to display a row of column names at the beginning of query results. By default, this option is enabled. However, you may want to disable it in some instances, such as when sending the output of mysql into another program that expects to see only data and not an initial header line.

To disable column names, you can specify the option using any of these forms:

--disable-column-names
--skip-column-names
--column-names=0

The --disable and --skip prefixes and the =0 suffix all have the same effect: They turn the option off.

The “enabled” form of the option may be specified in any of these ways:

--column-names
--enable-column-names
--column-names=1

If an option is prefixed by --loose, a program does not exit with an error if it does not recognize the option, but instead issues only a warning:

shell> mysql --loose-no-such-option
mysql: WARNING: unknown option '--no-such-option'

The --loose prefix can be useful when you run programs from multiple installations of MySQL on the same machine and list options in an option file, An option that may not be recognized by all versions of a program can be given using the --loose prefix (or loose in an option file). Versions of the program that recognize the option process it normally, and versions that do not recognize it issue a warning and ignore it.

Another option that may occasionally be useful with mysql is the --execute or -e option, which can be used to pass SQL statements to the server. When this option is used, mysql executes the statements and exits. The statements must be enclosed by quotation marks. For example, you can use the following command to obtain a list of user accounts:

shell> mysql -u root -p --execute="SELECT User, Host FROM user" mysql
Enter password: ******
+------+-----------+
| User | Host      |
+------+-----------+
|      | gigan     |
| root | gigan     |
|      | localhost |
| jon  | localhost |
| root | localhost |
+------+-----------+
shell>

Note that the long form (--execute) is followed by an equals sign (=).

If you wish to use quoted values within a statement, you will either need to escape the inner quotes, or use a different type of quotes within the statement from those used to quote the statement itself. The capabilities of your command processor dictate your choices for whether you can use single or double quotation marks and the syntax for escaping quote characters. For example, if your command processor supports quoting with single or double quotes, you can double quotes around the statement, and single quotes for any quoted values within the statement.

In the preceding example, the name of the mysql database was passed as a separate argument. However, the same statement could have been executed using this command, which specifies no default database:

mysql> mysql -u root -p --execute="SELECT User, Host FROM mysql.user"

Multiple SQL statements may be passed on the command line, separated by semicolons:

shell> mysql -u root -p -e "SELECT VERSION();SELECT NOW()"
Enter password: ******
+------------+
| VERSION()  |
+------------+
| 5.0.19-log |
+------------+
+---------------------+
| NOW()               |
+---------------------+
| 2006-01-05 21:19:04 |
+---------------------+

The --execute or -e option may also be used to pass commands in an analogous fashion to the ndb_mgm management client for MySQL Cluster.

4.3.2. Using Option Files

Most MySQL programs can read startup options from option files (also sometimes called configuration files). Option files provide a convenient way to specify commonly used options so that they need not be entered on the command line each time you run a program. For the MySQL server, MySQL provides a number of preconfigured option files.

To determine whether a program reads option files, invoke it with the --help option (--verbose and --help for mysqld). If the program reads option files, the help message indicates which files it looks for and which option groups it recognizes.

Note: Option files used with MySQL Cluster programs are covered in Section 15.4, “MySQL Cluster Configuration”.

On Windows, MySQL programs read startup options from the following files:

Filename	Purpose
`WINDIR\my.ini`	Global options
`C:\my.cnf`	Global options
`INSTALLDIR\my.ini`	Global Options
`defaults-extra-file`	The file specified with `--defaults-extra-file=path`, if any

WINDIR represents the location of your Windows directory. This is commonly C:\WINDOWS or C:\WINNT. You can determine its exact location from the value of the WINDIR environment variable using the following command:

C:\> echo %WINDIR%

INSTALLDIR represents the installation directory of MySQL. This is typically C:\PROGRAMDIR\MySQL\MySQL 5.0 Server where PROGRAMDIR represents the programs directory (usually Program Files on English-language versions of Windows), when MySQL 5.0 has been installed using the installation and configuration wizards. See Section 2.3.6.1.1, “The MySQL Server Configuration Wizard on Windows”.

On Unix, MySQL programs read startup options from the following files:

Filename	Purpose
`/etc/my.cnf`	Global options
`$MYSQL_HOME/my.cnf`	Server-specific options
`defaults-extra-file`	The file specified with `--defaults-extra-file=path`, if any
`~/.my.cnf`	User-specific options

MYSQL_HOME is an environment variable containing the path to the directory in which the server-specific my.cnf file resides. (This was DATADIR prior to MySQL version 5.0.3.)

If MYSQL_HOME is not set and you start the server using the mysqld_safe program, mysqld_safe attempts to set MYSQL_HOME as follows:

Let BASEDIR and DATADIR represent the pathnames of the MySQL base directory and data directory, respectively.
If there is a my.cnf file in DATADIR but not in BASEDIR, mysqld_safe sets MYSQL_HOME to DATADIR.
Otherwise, if MYSQL_HOME is not set and there is no my.cnf file in DATADIR, mysqld_safe sets MYSQL_HOME to BASEDIR.

In MySQL 5.0, use of DATADIR as the location for my.cnf is deprecated. BASEDIR is a better location.

Typically, DATADIR is /usr/local/mysql/data for a binary installation or /usr/local/var for a source installation. Note that this is the data directory location that was specified at configuration time, not the one specified with the --datadir option when mysqld starts. Use of --datadir at runtime has no effect on where the server looks for option files, because it looks for them before processing any options.

MySQL looks for option files in the order just described and reads any that exist. If an option file that you want to use does not exist, create it with a plain text editor.

If multiple instances of a given option are found, the last instance takes precedence. There is one exception: For mysqld, the first instance of the --user option is used as a security precaution, to prevent a user specified in an option file from being overridden on the command line.

Note: On Unix platforms, MySQL ignores configuration files that are world-writable. This is intentional, and acts as a security measure.

Any long option that may be given on the command line when running a MySQL program can be given in an option file as well. To get the list of available options for a program, run it with the --help option.

The syntax for specifying options in an option file is similar to command-line syntax, except that you omit the leading two dashes. For example, --quick or --host=localhost on the command line should be specified as quick or host=localhost in an option file. To specify an option of the form --loose-opt_name in an option file, write it as loose-opt_name.

Empty lines in option files are ignored. Non-empty lines can take any of the following forms:

#comment, ;comment

Comment lines start with ‘#’ or ‘;’. A ‘#’ comment can start in the middle of a line as well.
[group]

group is the name of the program or group for which you want to set options. After a group line, any option-setting lines apply to the named group until the end of the option file or another group line is given.
opt_name

This is equivalent to --opt_name on the command line.
opt_name=value

This is equivalent to --opt_name=value on the command line. In an option file, you can have spaces around the ‘=’ character, something that is not true on the command line. You can enclose the value within single quotes or double quotes, which is useful if the value contains a ‘#’ comment character or whitespace.

For options that take a numeric value, the value can be given with a suffix of K, M, or G (either uppercase or lowercase) to indicate a multiplier of 1024, 1024² or 1024³. For example, the following command tells mysqladmin to ping the server 1024 times, sleeping 10 seconds between each ping:

mysql> mysqladmin --count=1K --sleep=10 ping

Leading and trailing blanks are automatically deleted from option names and values. You may use the escape sequences ‘\b’, ‘\t’, ‘\n’, ‘\r’, ‘\\’, and ‘\s’ in option values to represent the backspace, tab, newline, carriage return, backslash, and space characters.

Because the ‘\\’ escape sequence represents a single backslash, you must write each ‘\’ as ‘\\’. Alternatively, you can specify the value using ‘/’ rather than ‘\’ as the pathname separator.

If an option group name is the same as a program name, options in the group apply specifically to that program. For example, the [mysqld] and [mysql] groups apply to the mysqld server and the mysql client program, respectively.

The [client] option group is read by all client programs (but not by mysqld). This allows you to specify options that apply to all clients. For example, [client] is the perfect group to use to specify the password that you use to connect to the server. (But make sure that the option file is readable and writable only by yourself, so that other people cannot find out your password.) Be sure not to put an option in the [client] group unless it is recognized by all client programs that you use. Programs that do not understand the option quit after displaying an error message if you try to run them.

Here is a typical global option file:

[client]
port=3306
socket=/tmp/mysql.sock

[mysqld]
port=3306
socket=/tmp/mysql.sock
key_buffer_size=16M
max_allowed_packet=8M

[mysqldump]
quick

The preceding option file uses var_name=value syntax for the lines that set the key_buffer_size and max_allowed_packet variables.

Here is a typical user option file:

[client]
# The following password will be sent to all standard MySQL clients
password="my_password"

[mysql]
no-auto-rehash
connect_timeout=2

[mysqlhotcopy]
interactive-timeout

If you want to create option groups that should be read by mysqld servers from a specific MySQL release series only, you can do this by using groups with names of [mysqld-4.1], [mysqld-5.0], and so forth. The following group indicates that the --new option should be used only by MySQL servers with 5.0.x version numbers:

[mysqld-5.0]
new

Beginning with MySQL 5.0.4, it is possible to use !include directives in option files to include other option files and !includedir to search specific directories for option files. For example, to include the /home/mydir/myopt.cnf file, use the following directive:

!include /home/mydir/myopt.cnf

To search the /home/mydir directory and read option files found there, use this directive:

!includedir /home/mydir

There is no guarantee about the order in which the option files in the directory will be read.

Note: Currently, any files to be found and included using the !includedir directive on Unix operating systems must have filenames ending in .cnf. On Windows, this directive checks for files with the .ini or .cnf extension.

Write the contents of an included option file like any other option file. That is, it should contain groups of options, each preceded by a [group] line that indicates the program to which the options apply.

While an included file is being processed, only those options in groups that the current program is looking for are used. Other groups are ignored. Suppose that a my.cnf file contains this line:

!include /home/mydir/myopt.cnf

And suppose that /home/mydir/myopt.cnf looks like this:

[mysqladmin]
force

[mysqld]
key_buffer_size=16M

If my.cnf is processed by mysqld, only the [mysqld] group in /home/mydir/myopt.cnf is used. If the file is processed by mysqladmin, only the [mysqldamin] group is used. If the file is processed by any other program, no options in /home/mydir/myopt.cnf are used.

The !includedir directive is processed similarly except that all option files in the named directory are read.

If you have a source distribution, you can find sample option files named my-xxxx.cnf in the support-files directory. If you have a binary distribution, look in the support-files directory under your MySQL installation directory. On Windows, the sample option files may be located in the MySQL installation directory (see earlier in this section or Chapter 2, Installing and Upgrading MySQL, if you do not know where this is). Currently, there are sample option files for small, medium, large, and very large systems. To experiment with one of these files, copy it to C:\my.cnf on Windows or to .my.cnf in your home directory on Unix.

Note: On Windows, the .cnf or .ini option file extension might not be displayed.

Most MySQL programs that support option files handle the following options. They affect option-file handling, so they must be given on the command line and not in an option file. To work properly, each of these options must immediately follow the command name, with the exception that --print-defaults may be used immediately after --defaults-file or --defaults-extra-file. Also, you should avoid the use of the ‘~’ shell metacharacter when specifying filenames because it might not be interpreted as you expect.

--no-defaults

Don't read any option files.
--print-defaults

Print the program name and all options that it gets from option files.
--defaults-file=file_name

Use only the given option file. file_name is the full pathname to the file. If the file does not exist or is otherwise inaccessible, the program will exit with an error.
--defaults-extra-file=file_name

Read this option file after the global option file but (on Unix) before the user option file. file_name is the full pathname to the file. As of MySQL 5.0.6, if the file does not exist or is otherwise inaccessible, the program will exit with an error.
--defaults-group-suffix=str

If this option is given, the program reads not only its usual option groups, but also groups with the usual names and a suffix of str. For example, the mysql client normally reads the [client] and [mysql] groups. If the --default-group-suffix=_other option is given, mysql also reads the [client_other] and [mysql_other] groups. This option was added in MySQL 5.0.10.

In shell scripts, you can use the my_print_defaults program to parse option files and see what options would be used by a given program. The following example shows the output that my_print_defaults might produce when asked to show the options found in the [client] and [mysql] groups:

shell> my_print_defaults client mysql
--port=3306
--socket=/tmp/mysql.sock
--no-auto-rehash

Note for developers: Option file handling is implemented in the C client library simply by processing all options in the appropriate group or groups before any command-line arguments. This works well for programs that use the last instance of an option that is specified multiple times. If you have a C or C++ program that handles multiply specified options this way but that doesn't read option files, you need add only two lines to give it that capability. Check the source code of any of the standard MySQL clients to see how to do this.

Several other language interfaces to MySQL are based on the C client library, and some of them provide a way to access option file contents. These include Perl and Python. For details, see the documentation for your preferred interface.

4.3.2.1. Preconfigured Option Files

MySQL provides a number of preconfigured option files that can be used as a basis for tuning the MySQL server. Look in your installation directory for files such as my-small.cnf, my-medium.cnf, my-large.cnf, and my-huge.cnf, which you can rename and copy to the appropriate location for use as a base configuration file. Regarding names and appropriate location, see the general information provided in Section 4.3.2, “Using Option Files”. On Windows, those files have a .ini rather than a .cnf extension.

4.3.3. Using Environment Variables to Specify Options

To specify an option using an environment variable, set the variable using the syntax appropriate for your command processor. For example, on Windows or NetWare, you can set the USER variable to specify your MySQL account name. To do so, use this syntax:

SET USER=your_name

The syntax on Unix depends on your shell. Suppose that you want to specify the TCP/IP port number using the MYSQL_TCP_PORT variable. Typical syntax (such as for sh, bash, zsh, and so on) is as follows:

MYSQL_TCP_PORT=3306
export MYSQL_TCP_PORT

The first command sets the variable, and the export command exports the variable to the shell environment so that its value becomes accessible to MySQL and other processes.

For csh and tcsh, use setenv to make the shell variable available to the environment:

setenv MYSQL_TCP_PORT 3306

The commands to set environment variables can be executed at your command prompt to take effect immediately, but the settings persist only until you log out. To have the settings take effect each time you log in, place the appropriate command or commands in a startup file that your command interpreter reads each time it starts. Typical startup files are AUTOEXEC.BAT for Windows, .bash_profile for bash, or .tcshrc for tcsh. Consult the documentation for your command interpreter for specific details.

Section 2.4.19, “Environment Variables”, lists all environment variables that affect MySQL program operation.

4.3.4. Using Options to Set Program Variables

Many MySQL programs have internal variables that can be set at runtime. Program variables are set the same way as any other long option that takes a value. For example, mysql has a max_allowed_packet variable that controls the maximum size of its communication buffer. To set the max_allowed_packet variable for mysql to a value of 16MB, use either of the following commands:

shell> mysql --max_allowed_packet=16777216
shell> mysql --max_allowed_packet=16M

The first command specifies the value in bytes. The second specifies the value in megabytes. For variables that take a numeric value, the value can be given with a suffix of K, M, or G (either uppercase or lowercase) to indicate a multiplier of 1024, 1024² or 1024³. (For example, when used to set max_allowed_packet, the suffixes indicate units of kilobytes, megabytes, or gigabytes.)

In an option file, variable settings are given without the leading dashes:

[mysql]
max_allowed_packet=16777216

Or:

[mysql]
max_allowed_packet=16M

If you like, underscores in a variable name can be specified as dashes. The following option groups are equivalent. Both set the size of the server's key buffer to 512MB:

[mysqld]
key_buffer_size=512M

[mysqld]
key-buffer-size=512M

Note: Before MySQL 4.0.2, the only syntax for setting program variables was --set-variable=option=value (or set-variable=option=value in option files). This syntax still is recognized, but is deprecated as of MySQL 4.0.2.

Many server system variables can also be set at runtime.

3.8. Using MySQL with Apache

2007-04-09T22:31:00.001-07:00

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.

3.7. Queries from the Twin Project

2007-04-09T22:13:00.000-07:00

At Analytikerna and Lentus, we have been doing the systems and field work for a big research project. This project is a collaboration between the Institute of Environmental Medicine at Karolinska Institutet Stockholm and the Section on Clinical Research in Aging and Psychology at the University of Southern California.

The project involves a screening part where all twins in Sweden older than 65 years are interviewed by telephone. Twins who meet certain criteria are passed on to the next stage. In this latter stage, twins who want to participate are visited by a doctor/nurse team. Some of the examinations include physical and neuropsychological examination, laboratory testing, neuroimaging, psychological status assessment, and family history collection. In addition, data are collected on medical and environmental risk factors.

More information about Twin studies can be found at: http://www.mep.ki.se/twinreg/index_en.html

The latter part of the project is administered with a Web interface written using Perl and MySQL.

Each night all data from the interviews is moved into a MySQL database.

3.7.1. Find All Non-distributed Twins

The following query is used to determine who goes into the second part of the project:

SELECT
   CONCAT(p1.id, p1.tvab) + 0 AS tvid,
   CONCAT(p1.christian_name, ' ', p1.surname) AS Name,
   p1.postal_code AS Code,
   p1.city AS City,
   pg.abrev AS Area,
   IF(td.participation = 'Aborted', 'A', ' ') AS A,
   p1.dead AS dead1,
   l.event AS event1,
   td.suspect AS tsuspect1,
   id.suspect AS isuspect1,
   td.severe AS tsevere1,
   id.severe AS isevere1,
   p2.dead AS dead2,
   l2.event AS event2,
   h2.nurse AS nurse2,
   h2.doctor AS doctor2,
   td2.suspect AS tsuspect2,
   id2.suspect AS isuspect2,
   td2.severe AS tsevere2,
   id2.severe AS isevere2,
   l.finish_date
FROM
   twin_project AS tp
   /* For Twin 1 */
   LEFT JOIN twin_data AS td ON tp.id = td.id
             AND tp.tvab = td.tvab
   LEFT JOIN informant_data AS id ON tp.id = id.id
             AND tp.tvab = id.tvab
   LEFT JOIN harmony AS h ON tp.id = h.id
             AND tp.tvab = h.tvab
   LEFT JOIN lentus AS l ON tp.id = l.id
             AND tp.tvab = l.tvab
   /* For Twin 2 */
   LEFT JOIN twin_data AS td2 ON p2.id = td2.id
             AND p2.tvab = td2.tvab
   LEFT JOIN informant_data AS id2 ON p2.id = id2.id
             AND p2.tvab = id2.tvab
   LEFT JOIN harmony AS h2 ON p2.id = h2.id
             AND p2.tvab = h2.tvab
   LEFT JOIN lentus AS l2 ON p2.id = l2.id
             AND p2.tvab = l2.tvab,
   person_data AS p1,
   person_data AS p2,
   postal_groups AS pg
WHERE
   /* p1 gets main twin and p2 gets his/her twin. */
   /* ptvab is a field inverted from tvab */
   p1.id = tp.id AND p1.tvab = tp.tvab AND
   p2.id = p1.id AND p2.ptvab = p1.tvab AND
   /* Just the screening survey */
   tp.survey_no = 5 AND
   /* Skip if partner died before 65 but allow emigration (dead=9) */
   (p2.dead = 0 OR p2.dead = 9 OR
    (p2.dead = 1 AND
     (p2.death_date = 0 OR
      (((TO_DAYS(p2.death_date) - TO_DAYS(p2.birthday)) / 365)
       >= 65))))
   AND
   (
   /* Twin is suspect */
   (td.future_contact = 'Yes' AND td.suspect = 2) OR
   /* Twin is suspect - Informant is Blessed */
   (td.future_contact = 'Yes' AND td.suspect = 1
                              AND id.suspect = 1) OR
   /* No twin - Informant is Blessed */
   (ISNULL(td.suspect) AND id.suspect = 1
                       AND id.future_contact = 'Yes') OR
   /* Twin broken off - Informant is Blessed */
   (td.participation = 'Aborted'
    AND id.suspect = 1 AND id.future_contact = 'Yes') OR
   /* Twin broken off - No inform - Have partner */
   (td.participation = 'Aborted' AND ISNULL(id.suspect)
                                 AND p2.dead = 0))
   AND
   l.event = 'Finished'
   /* Get at area code */
   AND SUBSTRING(p1.postal_code, 1, 2) = pg.code
   /* Not already distributed */
   AND (h.nurse IS NULL OR h.nurse=00 OR h.doctor=00)
   /* Has not refused or been aborted */
   AND NOT (h.status = 'Refused' OR h.status = 'Aborted'
   OR h.status = 'Died' OR h.status = 'Other')
ORDER BY
   tvid;

Some explanations:

CONCAT(p1.id, p1.tvab) + 0 AS tvid

We want to sort on the concatenated id and tvab in numerical order. Adding 0 to the result causes MySQL to treat the result as a number.
column id

This identifies a pair of twins. It is a key in all tables.
column tvab

This identifies a twin in a pair. It has a value of 1 or 2.
column ptvab

This is an inverse of tvab. When tvab is 1 this is 2, and vice versa. It exists to save typing and to make it easier for MySQL to optimize the query.

This query demonstrates, among other things, how to do lookups on a table from the same table with a join (p1 and p2). In the example, this is used to check whether a twin's partner died before the age of 65. If so, the row is not returned.

All of the above exist in all tables with twin-related information. We have a key on both id,tvab (all tables), and id,ptvab (person_data) to make queries faster.

On our production machine (A 200MHz UltraSPARC), this query returns about 150-200 rows and takes less than one second.

The current number of records in the tables used in the query:

Table	Rows
`person_data`	71074
`lentus`	5291
`twin_project`	5286
`twin_data`	2012
`informant_data`	663
`harmony`	381
`postal_groups`	100

3.7.2. Show a Table of Twin Pair Status

Each interview ends with a status code called event. The query shown here is used to display a table over all twin pairs combined by event. This indicates in how many pairs both twins are finished, in how many pairs one twin is finished and the other refused, and so on.

SELECT
       t1.event,
       t2.event,
       COUNT(*)
FROM
       lentus AS t1,
       lentus AS t2,
       twin_project AS tp
WHERE
       /* We are looking at one pair at a time */
       t1.id = tp.id
       AND t1.tvab=tp.tvab
       AND t1.id = t2.id
       /* Just the screening survey */
       AND tp.survey_no = 5
       /* This makes each pair only appear once */
       AND t1.tvab='1' AND t2.tvab='2'
GROUP BY
       t1.event, t2.event;

3.6. Examples of Common Queries

2007-04-03T22:53:00.000-07:00

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 |
+---------+--------+-------+

3.6.1. The Maximum Value for a Column

“What's the highest item number?”

SELECT MAX(article) AS article FROM shop;
+---------+ | article | +---------+ | 4 | +---------+

3.6.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);

Another solution is to sort all rows descending by price and get only the first row using the MySQL-specific LIMIT clause:

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.

3.6.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 |
+---------+-------+

3.6.4. The Rows Holding the Group-wise Maximum of a Certain Field

Task: For each article, find the dealer or dealers with the most expensive price.

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 Section 13.2.8.7, “Correlated Subqueries”). Other possibilities for solving the problem are to use a non-correlated subquery in the FROM clause or a LEFT JOIN:

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; SELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < class="title">3.6.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 Section 9.4, “User-Defined Variables”.)

For example, to find the articles with the highest and lowest price you can do this

3.6.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)
);

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 |
+----+---------+

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.

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 allows multiple-row inserts to be reproduced correctly on other servers in a replication setup.

For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column 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)
);

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     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+

Note that 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 will generate 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.

To start with an AUTO_INCREMENT value other than 1, you can set that value with CREATE TABLE or ALTER TABLE, like this:

mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;

More information about AUTO_INCREMENT is available here:

How to assign the AUTO_INCREMENT attribute to a column: Section 13.1.5, “CREATE TABLE Syntax”, and Section 13.1.2, “ALTER TABLE Syntax”.
How AUTO_INCREMENT behaves depending on the SQL mode: Section 5.2.6, “SQL Modes”.
Find the row that contains the most recent AUTO_INCREMENT value: Section 12.2.3, “Comparison Functions and Operators”.
Set the AUTO_INCREMENT value to be used: Section 13.5.3, “SET Syntax”.
AUTO_INCREMENT and replication: Section 6.7, “Replication Features and Known Problems”.
Server-system variables related to AUTO_INCREMENT (auto_increment_increment and auto_increment_offset) that can be used for replication: Section 5.2.3, “System Variables”.

Previous / Next / Up / Table of Contents


*************************** 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

3.6.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 from MySQL 5.0.0. See Section 7.2.6, “Index Merge Optimization”.

You can also solve the problem efficiently by using a UNION that combines the output of two separate SELECT statements. See Section 13.2.7.3, “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';

3.6.8. Calculating Visits Per Day

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<

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.

3.6.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)
);

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 |
+----+---------+

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)
);

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     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+

To start with an AUTO_INCREMENT value other than 1, you can set that value with CREATE TABLE or ALTER TABLE, like this:

mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;

3.5. Using mysql in Batch Mode

2007-04-03T22:33:00.000-07:00

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 allows 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:

+---------+
| 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



How to measure total batch running time for several SQLs:

# at start of your script file
SET @start=UNIX_TIMESTAMP();

# great job
...
...
...

# at bottom of your script file
SET
 @s=@seconds:=UNIX_TIMESTAMP()-@start,
 @d=TRUNCATE(@s/86400,0), @s=MOD(@s,86400),
 @h=TRUNCATE(@s/3600,0), @s=MOD(@s,3600),
 @m=TRUNCATE(@s/60,0), @s=MOD(@s,60),
 @day=IF(@d>0,CONCAT(@d,' day'),''),
 @hour=IF(@d+@h>0,CONCAT(IF(@d>0,LPAD(@h,2,'0'),@h),' hour'),''),
 @min=IF(@d+@h+@m>0,CONCAT(IF(@d+@h>0,LPAD(@m,2,'0'),@m),' min.'),''),
 @sec=CONCAT(IF(@d+@h+@m>0,LPAD(@s,2,'0'),@s),' sec.');

SELECT
 CONCAT(@seconds,' sec.') AS seconds,
 CONCAT_WS(' ',@day,@hour,@min,@sec) AS elapsed;

# enjoy :)

p.s. Tested & works
p.p.s. No fractions of seconds :(

Example of a Korn Shell Script

#!/bin/ksh
mysql --user= --password= -h  < /etc/security/mysqlpassword
chmod 200 /etc/security/mysqlpassword

Then in your script:

echo "update tablex set x=1 where a=2;" | mysql mydb --user=batchdb --password=`cat /etc/security/mysqlpassword`

This
assumes you have created a user called "batchdb" with that password and

When using mysql in batch mode you can use pipes to write an
interactive but pre-scripted shell script. Be aware that you need to
use the "-n" command line option to flush the buffer otherwise your

N.B. On most Unix systems, by placing the password in the command line
with --password (even the above method for using a password file) you
are making the password visible to local users, who can see the command
string with a "ps" or "w" command.

Whilst some systems can be
set to block this, and others would let you wrap the command in
something that would overwrite what users could see as your command,
the best way to do any automations like this is to create a specific
unix user for the job (or use a user that is already secure) and place
the password in the .my.cnf file for that user - making sure the

For newbies like me:  This exact command allowed me to run a script from outside MySQL (using the DOS command line in Win98):

C:\WINDOWS\Desktop>c:\mysql\bin\mysql -u root -p < "c:\mysql\scripts\20060416_ShowInnodbstatusscript.txt" | more  Note: My text file had two commands:      Use db_name;     Show InnoDB Status;  FYI: I had been plagued by a foreign key error, but was unable figure out how to see the result of my Show Innodb Status command due to my 50-line DOS screen limitation. To get around this problem I    a) created the simple script shown above   b) created a foreign key error while logged into my MySQL user account, then   c) opened an additional DOS window to execute the command shown above.  Running the command outside of MySQL essentially creates a way to view command results one page at a time for folks administering MySQL at the command   line (using Windows). permissions are set so that only the owner can read it read will hang. Here is a code sample in ksh:  ------------------------------------------------- #!/bin/ksh mysql -u username -ppassword -D dbname -ss -n -q |& print -p -- "select count(*) from some_table;" read -p get_row_count1 print -p -- "select count(*) from some_other_table;" read -p get_row_count2 print -p exit ; # echo $get_row_count1 echo $get_row_count2 # exit ------------------------------------------------- (The -q option is optional)  Note:  If you dislike using "-n" then make sure all your read statements are after the exit. the correct access rights to the database called "mydb". Example: ===file petquery.sh=== #!/bin/sh #  This is a comment mysql -t <

3.4. Getting Information About Databases and Tables

2007-04-03T22:22:00.000-07:00

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 |
+---------------------+

If you want to find out about the structure of a table, the DESCRIBE command 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.

If you have indexes on a table, SHOW INDEX FROM tbl_name produces information about them.

mysql> SHOW CREATE TABLE ;

The "Extra" field records special information about columns.
If you have selected the "auto_increment" functionality for a column, for example, that would show up in the "Extra" field when doing a "describe".

3.3. Creating and Using a Database

2007-04-02T21:22:00.000-07:00

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:

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      |
+----------+

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 Section 13.5.4.8, “SHOW DATABASES Syntax”.

If the test database exists, try to access it:

mysql> USE test
Database changed

Note that 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.

3.3.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, our 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 Section 5.7, “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 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: ********

Note that 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.

3.3.2. Creating a Table

Creating the database is the easy part, but at this point it's 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's 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's 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:

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. (Note: Prior to MySQL 5.0.3, the upper limit was 255.) 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 Chapter 11, Data Types.

3.3.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
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 command:

mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;

Note that if you created the file on Windows with an editor that uses \r\n as a line terminator, you should use:

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 Section 5.6.4, “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);

Note that 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.

3.3.4. Retrieving Information from a Table

The SELECT statement is used to pull information from a table. The general form of the statement is:

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.

3.3.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 dataset. 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.

3.3.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.

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  |
+-------+--------+---------+------+------------+-------+

3.3.4.3. Selecting Particular Columns

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 |
| Buffy  | dog     | 1989-05-13 |
| Fang   | dog     | 1990-08-27 |
| Bowser | dog     | 1989-08-31 |
+--------+---------+------------+

3.3.4.4. Sorting Rows

You may have noticed in the preceding examples that the result rows are displayed in no particular order. It's 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      |
+----------+---------+------------+
| 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 |
+----------+---------+------------+

Note that the DESC keyword applies only to the column name immediately preceding it (birth); it does not affect the species column sort order.

3.3.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, compute the difference in the year part of the current date and the birth date, then subtract one if the current date occurs earlier in the calendar year than the birth date. The following query shows, for each pet, the birth date, the current date, and the age in years.

mysql> SELECT name, birth, CURDATE(),
 -> (YEAR(CURDATE())-YEAR(birth))
 -> - (RIGHT(CURDATE(),5)
 -> 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 |
+----------+------------+------------+------+

Here, YEAR() pulls out the year part of a date and RIGHT() pulls off the rightmost five characters that represent the MM-DD (calendar year) part of the date. The part of the expression that compares the MM-DD values evaluates to 1 or 0, which adjusts the year difference down a year if CURDATE() occurs earlier in the year than birth. The full expression is somewhat ungainly, so an alias (age) is used to make the output column label more meaningful.

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(),
 -> (YEAR(CURDATE())-YEAR(birth))
 -> - (RIGHT(CURDATE(),5)
 -> 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:

mysql> SELECT name, birth, CURDATE(),
 -> (YEAR(CURDATE())-YEAR(birth))
 -> - (RIGHT(CURDATE(),5)
 -> 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,
 -> (YEAR(death)-YEAR(birth)) - (RIGHT(death,5)
 -> 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 3.3.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;
+-------+------------+
| 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() allows 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;

Note that 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).

3.3.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, you cannot use the arithmetic comparison operators such as =, <, or <>. To demonstrate this for yourself, try the following query:

mysql> SELECT 1 = NULL, 1 <> NULL, 1 <> NULL;
+----------+-----------+----------+----------+
| 1 = NULL | 1 <> NULL | 1 <> NULL |
+----------+-----------+----------+----------+
|     NULL |      NULL |     NULL |     NULL |
+----------+-----------+----------+----------+

Clearly you get no meaningful results from these comparisons. Use the IS NULL and IS NOT NULL operators instead:

mysql> SELECT 1 IS NULL, 1 IS NOT NULL;
+-----------+---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+
|         0 |             1 |
+-----------+---------------+

Note that in MySQL, 0 or NULL means false and anything else means true. The default truth value from a boolean operation is 1.

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 Section B.1.5.3, “Problems with NULL Values”.

3.3.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 allows 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. Note that 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      |
+----------+-------+---------+------+------------+------------+
| 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).

Some characteristics of extended regular expressions are:

‘.’ 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$';
+--------+--------+---------+------+------------+-------+
| 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  |
+-------+--------+---------+------+------------+-------+

Section 12.4.2, “Regular Expressions”, provides more information about the syntax for regular expressions.

3.3.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(*) |
+----------+
|        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 |
+--------+----------+

Note the use of GROUP BY to group all records for each owner. Without it, all you get is an error message:

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

COUNT() and GROUP BY are useful for characterizing your data in various ways. 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 |
+---------+------+----------+

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 |
+---------+------+----------+

3.3.4.9. Using More Than one Table

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 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's easiest to load the initial records by creating a tab-delimited text file containing the 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)
   -> remark
   -> FROM pet INNER JOIN event
   ->   ON pet.name = event.name
   -> WHERE event.type = 'litter';
+--------+------+-----------------------------+
| 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               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 allows for               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.



3.2. Entering Queries
2007-04-02T21:06:00.000-07:00
       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's okay. At this point, it's 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's 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.0.7-beta-Max | 2005-07-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's 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:     
 mysql> SELECT VERSION(); SELECT NOW();
+----------------+
| VERSION()      |
+----------------+
| 5.0.7-beta-Max |
+----------------+
1 row in set (0.00 sec)

+---------------------+
| NOW()               |
+---------------------+
| 2005-07-11 17:59:36 |
+---------------------+ 
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's a simple multiple-line statement:     
 mysql> SELECT
   -> USER()
   -> ,
   -> CURRENT_DATE;
+---------------+--------------+
| USER()        | CURRENT_DATE |
+---------------+--------------+
| jon@localhost | 2005-07-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               (‘`’).  
  /*>  Waiting for next line, waiting for completion of a comment that began               with /*.  
 
 
        In the MySQL 5.0 series, the /*> prompt was       implemented in MySQL 5.0.6.     
        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 <>
   '>
        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       quote 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 <>
   '> '\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.     



3.1. Connecting to and Disconnecting from the Server
2007-04-02T21:01:00.000-07:00
       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.0.38-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       that MySQL server daemon (Unix) or service (Windows) is not       running. Consult the administrator or see the section of       Chapter 2, Installing and Upgrading MySQL that is appropriate to your operating       system.     
        For help with other problems often encountered when trying to log       in, see Section B.1.2, “Common Errors When Using MySQL Programs”.     
        Some MySQL installations allow 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.

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 (‘`’).
`/*>`	Waiting for next line, waiting for completion of a comment that began with `/*`.

mysql

commands

upgrade mysql 4.1.15 to mysql 4.1.22

Using JDBC with MySQL 3

JDBC Programs

Run the Programs

Summary

Complete Program Listings

Using JDBC with MySQL 2

Creating a New Database using the Monitor Program

Creating a New User with the Monitor Program

Administering the Database Server using Text Files

Using JDBC with MySQL 1

Preface

Documentation

Downloading the MySQL Database Server

Installing MySQL Database Server

Downloading MySQL Connector/J

Oracle basic training

Chapter 5. Database Administration

5.1. Overview of Server-Side Programs

Chapter 4. Using MySQL Programs

4.1. Overview of MySQL Programs

4.2. Invoking MySQL Programs

4.3. Specifying Program Options

4.3.1. Using Options on the Command Line

4.3.2. Using Option Files

4.3.2.1. Preconfigured Option Files

4.3.3. Using Environment Variables to Specify Options

4.3.4. Using Options to Set Program Variables

3.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.

3.7. Queries from the Twin Project

3.7.1. Find All Non-distributed Twins

3.7.2. Show a Table of Twin Pair Status

3.6. Examples of Common Queries

3.6.1. The Maximum Value for a Column

3.6.2. The Row Holding the Maximum of a Certain Column

3.6.3. Maximum of Column per Group

3.6.4. The Rows Holding the Group-wise Maximum of a Certain Field

3.6.9. Using AUTO_INCREMENT

3.6.7. Searching on Two Keys

3.6.8. Calculating Visits Per Day

3.6.9. Using AUTO_INCREMENT

3.5. Using mysql in Batch Mode

3.4. Getting Information About Databases and Tables

3.3. Creating and Using a Database

3.3.1. Creating and Selecting a Database

3.3.2. Creating a Table

3.3.3. Loading Data into a Table

3.3.4. Retrieving Information from a Table

3.3.4.1. Selecting All Data

3.3.4.2. Selecting Particular Rows

3.3.4.3. Selecting Particular Columns

3.3.4.4. Sorting Rows

3.3.4.5. Date Calculations

3.3.4.6. Working with NULL Values

3.3.4.7. Pattern Matching

3.3.4.8. Counting Rows

3.3.4.9. Using More Than one Table

3.2. Entering Queries

3.1. Connecting to and Disconnecting from the Server

3.6.9. Using `AUTO_INCREMENT`

3.6.9. Using `AUTO_INCREMENT`

3.3.4.6. Working with `NULL` Values