MySQL FAQs - Command-Line End User Interface mysql

By: FYIcenter.com

A collection of 10 FAQs on MySQL command-line interface mysql. Clear answers are provided with tutorial exercises on mysql command option; running SQL and mysql commands; running mysql command files in batch mode; returning query output in HTML and XML formats. Topics included in this collection are:

1. What Is the Command Line End User Interface - mysql?
2. What Are the "mysql" Command Line Options?
3. What Are the "mysql" Command Line Arguments?
4. How Many SQL DDL Commands Are Supported by "mysql"?
5. How Many SQL DML Commands Are Supported by "mysql"?
6. What Are the Non-Standard SQL Commands Supported by "mysql"?
7. How To Get Help Information from the Server?
8. How To Run "mysql" Commands from a Batch File?
9. How To Return Query Output in HTML Format?
10. How To Return Query Output in XML Format?

Please note that all answers and tutorials are based on MySQL 5.0.

What Is the Command Line End User Interface - mysql?

"mysql", official name is "MySQL monitor", is a command-line interface for end users to manage user data objects. "mysql" has the following main features:

• "mysql" is command line interface. It is not a Graphical User Interface (GUI).
• "mysql" supports all standard SQL Data Definition Language (DDL) commands for the server to execute.
• "mysql" supports all standard SQL Data Manipulation Language (DML) commands for the server to execute.
• "mysql" supports many of non-SQL commands that "mysql" will execute by itself.
• "mysql" provides access to the server-side help system.
• "mysql" allows command files to be executed in a batch mode.
• "mysql" allows query output to be formated as HTML tables.
• "mysql" allows query output to be formated as XML elements.

What Are the "mysql" Command Line Options?

"mysql" offers a big list of command line options. Here are some commonly used options:

• "-?" - Displays a help message on how to use "mysql" and terminates the program.
• "-u userName" - Specifies a user name for the server to authenticate when connecting to the server.
• "-p[password]" - Specifies a password for the server to authenticate when connecting to the server.
• "-h hostName" - Specifies a host name where the MySQL server is running. The default host name "localhost".
• "-P portNumber" - Specifies a port number where the MySQL server is listening for connections. The default port number is 3306.
• "-e command" - Executes the specified command and terminates the program.
• "-t" - Specifies that the query output is displayed in text table format. This is the default display format for interactive mode.
• "-H" - Specifies that the query output is displayed in HTML table format.
• "-X" - Specifies that the query output is displayed in XML element format.

Here is a tutorial exercise of how to use the "-?" option with "mysql":

>cd \mysql\bin
>mysql -?
mysql Ver 14.12 Distrib 5.0.24, for Win32 (ia32)
Copyright (C) 2002 MySQL AB
...
Usage: mysql [OPTIONS] [database]
  -?, --help Display this help and exit.
  -I, --help Synonym for -?
  --auto-rehash Enable automatic rehashing. One doesn't
                need to use 'rehash' to get table and field
                completion, but startup and reconnecting may
                take a longer time. Disable with
                --disable-auto-rehash.
  -A, --no-auto-rehash
...


What Are the "mysql" Command Line Arguments?

"mysql" supports only one optional command line argument, "database". But "mysql" allows the operating system to redirect input and output streams at the command line level. Here are some good examples:

• "mysql databaseName" - Starts "mysql" in interactive mode and use the specified database.
• "mysql < fileName" - Starts "mysql" in batch mode and executes all commands in the specified file.
• "mysql < fileName > fileName" - Starts "mysql" in batch mode, executes all commands in the specified file, and write output to the specified file.

Here is a tutorial exercise of how to use the command line argument to specify the database to use:

>cd \mysql\bin
>mysql -u root test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.0.24

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| links          |
+----------------+
1 row in set (0.00 sec)

mysql> quit;
Bye

How Many SQL DDL Commands Are Supported by "mysql"?

There are 4 SQL Data Definition Language (DDL) commands that are supported by "mysql". They are listed below with short descriptions:

• "CREATE dataObjectType dataObjectName" - Creates new databases, tables, views, triggers, indexes, and other data objects.
• "RENAME dataObjectType dataObjectName" - Renames existing databases, tables, views, triggers, indexes, and other data objects.
• "ALTER dataObjectType dataObjectName" - Alters properties of existing databases, tables, views, triggers, indexes, and other data objects.
• "DROP dataObjectType dataObjectName" - Drops existing databases, tables, views, triggers, indexes, and other data objects.

Here is a tutorial exercise of how to use DDL commands to create a database and a table:

>cd \mysql\bin
>mysql -u root

mysql> CREATE DATABASE fyi;
Query OK, 1 row affected (0.50 sec)

mysql> CREATE TABLE articles (name VARCHAR(80));
Query OK, 0 rows affected (0.25 sec)

mysql> DROP DATABASE fyi;
Query OK, 0 rows affected (0.41 sec)

How Many SQL DML Commands Are Supported by "mysql"?

There are 4 SQL Data Manipulation Language (DML) commands that are supported by "mysql". They are listed below with short descriptions:

• "INSERT INTO tableName ..." - Inserts new data rows into the specified table.
• "DELETE FROM tableName ..." - Deletes existing data rows from the specified table.
• "UPDATE tableName SET ..." - Updates existing data rows in the specified table.
• "SELECT expressionList FROM ..." - Selects rows from tables and returns rows of values resulted from the specified expression list.

Here is a tutorial exercise of how to use DML commands to insert and select data rows:

>cd \mysql\bin
>mysql -u root test

mysql> CREATE TABLE links (id INTEGER, name VARCHAR(80));
Query OK, 0 rows affected (0.10 sec)

mysql> INSERT INTO links VALUES (1, 'dba.fyicenter.com');
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM links;
+------+-------------------+
| id   | name              |
+------+-------------------+
|    1 | dba.fyicenter.com |
+------+-------------------+
1 row in set (0.04 sec)

What Are the Non-Standard SQL Commands Supported by "mysql"?

There are many non-standard SQL commands that are supported by "mysql". Here is short list of some commonly used commands:

• "SHOW infoName" - Shows basic information of based on the specified information name.
• "SHOW infoName" - Shows basic information of based on the specified information name.
• "SET ..." - Sets new values to server or connection session variables.
• "GRANT ..." - Grants access privileges to users.
• "REVOKE ..." - Revokes access privileges from users.
• "CHECK TABLE tableName" - Checks the specified table for errors.
• "ANALYZE TABLE tableName" - Analyzes the specified table.
• "REPAIR TABLE tableName" - Repairs the specified table.
• "BACKUP TABLE tableName" - Backs up the specified table.
• "RESTORE TABLE tableName" - Restores the specified table.
• "USE databaseName" - Uses the specified database as the current database.
• "HELP topicName" - Returns help information on the specified topic.

Here is a tutorial exercise of how to use SHOW, USE and ANALYZE commands in "mysql":

>cd \mysql\bin
>mysql -u root

mysql> USE test;
Database changed

mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| articles       |
| links          |
+----------------+
2 rows in set (0.00 sec)

mysql> ANALYZE TABLE links;
+----------+-------+--------+---------------------------+
|Table     |Op     |Msg_type|Msg_text                   |
+----------+-------+--------+---------------------------+
|test.links|analyze|status  |Table is already up to date|
+----------+-------+--------+---------------------------+
1 row in set (0.14 sec)

How To Get Help Information from the Server?

While you are at the "mysql>" prompt, you can get help information from the server by using the "HELP" command. The tutorial exercise below shows sevearal examples:

>cd \mysql\bin
>mysql -u root

mysql> HELP;
...
List of all MySQL commands:
Note that all text commands must be end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear command.
connect   (\r) Reconnect to the server. 
...

mysql> HELP SHOW;
Name: 'SHOW'
Description:
SHOW has many forms that provide information about 
databases, tables, columns, or status information about 
the server. This section describes those following:

SHOW CREATE DATABASE db_name
SHOW CREATE FUNCTION funcname
SHOW CREATE PROCEDURE procname
SHOW CREATE TABLE tbl_name
SHOW DATABASES [LIKE 'pattern']
SHOW ENGINE engine_name {LOGS | STATUS }
...

mysql> HELP CREATE TABLE;
Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_option ...]
...

How To Run "mysql" Commands from a Batch File?

If you have group of "mysql" commands that need to be executed repeatedly, you can put them into a file, and run them from the file in "mysql" batch mode. Here is a batch file, \temp\links.sql, contains following commands:

USE test;
INSERT INTO links VALUES (10, 'dba.fyicenter.com');
SELECT * FROM links;

To run this batch file, you need to follow this tutorial:

>cd \mysql\bin
>mysql -u root < \temp\links.sql
id      name
1       dba.fyicenter.com
10      dba.fyicenter.com

How To Return Query Output in HTML Format?

By default, "mysql" returns query output in text table format. If you want to receive query output in HTML format, you need to use the "-H" command option. Here is a good tutorial exercise:

>cd \mysql\bin
>mysql -u root -H test

mysql> SELECT * FROM links;
<TABLE BORDER=1><TR><TH>id</TH><TH>name</TH></TR>
<TR><TD>1</TD><TD>dba.fyicenter.com</TD></TR>
<TR><TD>10</TD><TD>dba.fyicenter.com</TD></TR></TABLE>
2 rows in set (0.00 sec)

How To Return Query Output in XML Format?

By default, "mysql" returns query output in text table format. If you want to receive query output in XML format, you need to use the "-X" command option. Here is a good tutorial exercise:

>cd \mysql\bin
>mysql -u root -X test

mysql> SELECT * FROM links;
<?xml version="1.0"?>
<resultset statement="SELECT * FROM links">
  <row>
        <field name="id">1</field>
        <field name="name">dba.fyicenter.com</field>
  </row>
  <row>
        <field name="id">10</field>
        <field name="name">dba.fyicenter.com</field>
  </row>
</resultset>
2 rows in set (0.00 sec)