Managing MySQL from the command line
En Español  

If our hosting service offer us a SSH shell and have MySQL installed, or if we have MySQL installed in our computer, we can administer a database from the Command Line Interface. If we develop programs that require the use of a MySQL database, most likely we are already familiarized with SQL statements. By the use of the mysql command we can send this queries to the database.

Topics
Connecting to the database
Issuing statements to the MySQL shell
Using the editor
Processing a batch file
MySQL statements for manipulating tables

• List existing tables in the database
• Show information of the tables in the database
• Create a new table
• List the fields in a table
• Change the name of a field in a table
• Add a field to a table and make it an index
• Remove an index from a table
• Remove a field from a table
• Add fields after a specified field
• Add fields at the start of the table
• Add multiple fields to the table
• Delete fields in a table
• Rename a table
• Change the comment of a table
• Change the auto increment value of a table
• Repair a table
• Optimize a table
• Delete all the entries in a table
• Delete a table

MySQL statements for manipulating entries in a table

• Show the entries of a table
• Count the entries of a table
• Sum the entries of a table
• Insert an entry
• Update an entry
• Delete an entry

Prepared MySQL statements
Footnotes
Extra resources

We can send statements directly to the MySQL shell, edit this statements in a separate text editor that we define with the EDITOR environment variable, or we can use a file with MySQL statements (a script or batch file) to be executed by the MySQL interpreter.

Connecting to the database

This guide assumes that you already have a database created, as well as a user with the necessary privileges to do the required operations in the database.

The four parameters that we need to establish a connection to the database is the host where the database resides, the username, the password and the name of the database that we are going to manipulate.

This will ask for your password, so it doesn't get stored in the history. E.g.:

You can specify the password in the command by adding the password right next to the -p, do not leave a space between -p and the password if you want to log in this way, however not using the password in the command is recommended, e.g.:

The parameter -D to specify the database to use since we log in is also optional, if you don't use it you can see a list of the available databases by using show databases; and select the one to use with use [database name]; at the mysql command prompt, e.g. use users;

If it worked, we will obtain a result similar to this:

Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -AWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 6324623Server version: 5.1.39-log MySQL ServerCopyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.This software comes with ABSOLUTELY NO WARRANTY. This is free software,and you are welcome to modify and redistribute it under the GPL v2 licenseType 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>

To finish the session type quit. If you are connecting to a database located in an external host, it is recommended to use SSL when connecting to the database, to do this use the --ssl parameter.

Issuing statements to the MySQL shell

Once we are in the MySQL shell, we can issue MySQL statements. To execute them we have to end them with either a semi-colon (;), or a \g, e.g.:

The statement is not executed until the semi-colon is found, this allow us type MySQL statements in multiple lines, e.g.:

If we want to present the results vertically, we need to end the statements with \G instead of a semi-colon or \g

Using the editor

In Unix systems such as Linux, the edit command from within the mysql shell launches the editor that is defined in the EDITOR environment variable. When we use the edit command, if we had previously issued a statement, the editor will be opened with this statement, this is very useful for making corrections in the last statement, otherwise we will just get an empty editor to type whatever we need. Once we are done editing the statement, we save, and quit the editor, and then we use a semi-colon or \g to execute the statement(s) that we just wrote.

To set the EDITOR environment variable, we use export, in this example I set vim as it is my preferred editor, but you can set an easier one such as nano. The default editor is vi:

To check the value of the EDITOR environment variable, we can use:

Processing a batch file

We can execute a batch file of MySQL statements by using:

Or, if we are inside the mysql shell, we can use:

MySQL statements for manipulating tables

Anyone whose job is to create scripts and programs that interact with MySQL is most likely familiarized with this statements, but since I use my own website as a reference I will put in here some common statements.

List existing tables in the database

Show information of the tables in the database

show tables will only show us the names of the tables in the database, to see all the information about the tables, use instead:

The information presented about the tables is:

• Name - The name of the table
• Engine - Engine of the table (MyISAM, InnoDB, Memory, CVS, etc.)
• Version - Version number of the table .frm file
• Row_format - The row storage format (Fixed, Dynamic, Redundant, etc.)
• Rows - Number of rows in the table
• Avg_row_length - Average row length
• Data_length - The length of the data file
• Max_data_length - The maximum length of the data file
• Index_length - The length of the index file
• Data_free - Number of allocated but unused bytes
• Auto_increment - The next auto-increment value
• Create_time - When the table was created
• Update_time - When the data file was last updated
• Check_time - When the table was last checked
• Collation - The tables character set and collation
• Checksum - The live checksum value
• Create_options - Extra options used when the table was created
• Comment - The comment of the table

We can specify from which table do we want to see the information by using:

And we can search in other field for certain value, for instance, to show all the tables that use the MyISAM storage engine, we can use:

Create a new table

This is an example of the command to create a table, I added a lot of different fields for the reference of how to declare them.

List the fields in a table

Change the name of a field in a table

Add a field to a table and make it an index

Remove an index from a table

Remove a field from a table

Add fields after a specified field

Add fields at the start of the table

Add multiple fields to the table

Delete fields in a table

Rename a table

Change the comment of a table

Change the auto increment value of a table

Repair a table

Optimize a table

Delete all the entries in a table

Delete a table

MySQL statements for manipulating entries in a table

I am going to use the following two fictitious tables for the examples that I am going to be using concerning the manipulation of entries.

Table of states (states)

state_id	state_name
1	Jalisco
2	Guanajuato
3	Hidalgo

Table of cities (cities)

city_id	city_name	city_population	state_id
1	Guadalajara	1494134	1
2	Tequila	33155	1
3	Zapopan	1243538	1
4	Tonalá	408729	1
5	Tlaquepaque	563006	1
6	Guanajuato	171709	2
7	Celaya	468064	2
8	León	1436733	2
9	Pachuca	275578	3
10	Tizayuca	100562	3

Show the entries of a table

The number of combinations that we can do with the select command is enormous, so I will try to cover some common uses in the following examples. The fields that we use following the select are the fields that will be shown, and the order in which we specify them is the order in which they will be shown. After this we specify the table or tables where we are searching.

By defining the where we can specify multiple conditions separated by spaces. If we are using more than one table in the search, we need to do an equal comparison between the fields that link both tables. The last examples are about doing this queries in more than one table.

The conditions may be given with comparison operators, such as =, <, >, <=, >=, <> or !=. The LIKE expression allows to do simple pattern matching, we can use the % as a wild-card character. between ... and ... allow us to specify a range of values. And we can specify more than one condition by using the logical operators AND or &&, OR or ||, NOT or !, or XOR.

We can order the result by using order by and specifying the field that we want to use for order, and whether we want the order to be ascending (asc) or descending (desc), we can use more that one field to do the ordering, in which case the first field specified will be used primarily for the order, and the second field will be used when the first used field have more than one instance.

And finally, the limit value defines the entry from which we will start to show, and how many entry will be shown.

Hopefully in the following examples this will become much clearer, take the two tables as reference to see the results of each of the commands.

+----------+------------+| state_id | state_name |+----------+------------+|      1 | Jalisco    ||      2 | Guanajuato ||      3 | Hidalgo    |+----------+------------+3 rows in set (5.14 sec)

+---------+-----------+-----------------+----------+| city_id | city_name | city_population | state_id |+---------+-----------+-----------------+----------+|     3 | Zapopan   |       1243538 |      1 |+---------+-----------+-----------------+----------+1 row in set (0.90 sec)

+-------------+-----------------+| city_name   | city_population |+-------------+-----------------+| Tequila     |         33155 || Tizayuca    |        100562 || Guanajuato  |        171709 || Pachuca     |        275578 || Tonalá    |        408729 || Celaya    |        468064 || Tlaquepaque |        563006 || Zapopan     |       1243538 || León      |       1436733 || Guadalajara |       1494134 |+-------------+-----------------+10 rows in set (0.04 sec)

+------------+| city_name  |+------------+| León    || Guanajuato || Celaya     |+------------+3 rows in set (0.85 sec)

+---------+-------------+-----------------+----------+| city_id | city_name   | city_population | state_id |+---------+-------------+-----------------+----------+|     3 | Zapopan     |       1243538 |      1 ||     4 | Tonalá    |        408729 |      1 ||     5 | Tlaquepaque |        563006 |      1 |+---------+-------------+-----------------+----------+3 rows in set (0.06 sec)

+-------------+| city_name   |+-------------+| Guadalajara || Guanajuato  |+-------------+2 rows in set (0.04 sec)

+---------+-------------+-----------------+----------+| city_id | city_name   | city_population | state_id |+---------+-------------+-----------------+----------+|     5 | Tlaquepaque |        563006 |      1 |+---------+-------------+-----------------+----------+1 row in set (0.04 sec)

Note: While both statements would return the same entries, use between is faster than use two comparisons, so if you are dealing with a range of values, always use between.

+-------------+------------+| city_name   | state_name |+-------------+------------+| Celaya    | Guanajuato || Guadalajara | Jalisco    || Guanajuato  | Guanajuato || León      | Guanajuato || Pachuca     | Hidalgo    || Tequila     | Jalisco    || Tizayuca    | Hidalgo    || Tlaquepaque | Jalisco    || Tonalá    | Jalisco    || Zapopan     | Jalisco    |+-------------+------------+10 rows in set (0.06 sec)

+-------------+------------+| city_name   | state_name |+-------------+------------+| Guadalajara | Jalisco    || Guanajuato  | Guanajuato |+-------------+------------+2 rows in set (0.05 sec)

+-------------+------------+-----------------+| city_name   | state_name | city_population |+-------------+------------+-----------------+| Guadalajara | Jalisco    |       1494134 || Tequila     | Jalisco    |         33155 || Zapopan     | Jalisco    |       1243538 || Tonalá    | Jalisco    |        408729 || Tlaquepaque | Jalisco    |        563006 || Guanajuato  | Guanajuato |        171709 || Celaya    | Guanajuato |        468064 || León      | Guanajuato |       1436733 || Pachuca     | Hidalgo    |        275578 || Tizayuca    | Hidalgo    |        100562 |+-------------+------------+-----------------+10 rows in set (0.05 sec)

+-------------+------------+-----------------+| city_name   | state_name | city_population |+-------------+------------+-----------------+| Tequila     | Jalisco    |         33155 || Tonalá    | Jalisco    |        408729 || Tlaquepaque | Jalisco    |        563006 || Zapopan     | Jalisco    |       1243538 || Guadalajara | Jalisco    |       1494134 || Tizayuca    | Hidalgo    |        100562 || Pachuca     | Hidalgo    |        275578 || Guanajuato  | Guanajuato |        171709 || Celaya    | Guanajuato |        468064 || León      | Guanajuato |       1436733 |+-------------+------------+-----------------+10 rows in set (0.15 sec)

Count the entries of a table

The conditions are optional, and they may have the same format of the conditions that we use in select statements, this will simple return us the number of entries. e.g..

+----------+| count(*) |+----------+|     10 |+----------+1 row in set (0.06 sec)

Sum the entries of a table

+------------------------+| sum(`city_population`) |+------------------------+|            6453216 |+------------------------+1 row in set (0.05 sec)

Insert an entry

In this case I add a new state, and a new city, notice than in the field state_id I am using as value the function LAST_INSERT_ID(), which gives me the value of the last inserted ID. If I wanted to insert more than one entry with this ID, we can use the same insert statement for the insertion of more than one field. I am going to take the past statement and insert instead three new entries:

Another useful function that I utilize for filling a value is the function NOW() in fields of the type datetime, I use this a lot for handle the creation or modification time of entries. For example, assuming that we had a field called creation_time, we could use:

Update an entry

When we are updating an entry, we need to specify which entry is it that we want to update, usually the primary key is used for this purpose due to it's inherent uniqueness. Of course, we can modify many entries in the same statement if more than one entry matches the condition. E.g. lets say that all the entries created on February 12, 2010 were to become "active" by changing the value of a field called active from '0' to '1', here is what we would do:

Delete an entry

The delete statement is simpler than an update statement but fairly similar, any and all entries that match the condition(s) given will be deleted. Be very careful with this statement, if you are targeting specific entries, always use the primary key so you don't hit another entry by accident.

Prepared MySQL statements

The reason that I learned about prepared statements was because of the security that they offer when I am using PHP to perform the queries to the database in a website. However, the use of prepared statements from the command line offer us the ability of define a statement once and then call it as many times as we want, changing only the parameter that we use. For example, for show an entry from the table of cities, showing the state name and not the state_id field, would always have the same format:

In the example, we are using the ? as a place holder for the ID of the entry that we want to show in this manner. This is useful as well when we are using a query like this from PHP and we need to use a value that we received from someone else. Before the existence of prepared statements, we needed to filter very carefully the input from a user in order to prevent a sql injection that could potentially wipe out our database or grant access to an unauthorized person. By separating the MySQL logic from the data we avoid this problem, as MySQL does not parse the parameter, it simply manages it as data. Another advantage is that it is faster to use prepared MySQL statements.

As far as I know, the prepared statements only work with SELECT, INSERT, UPDATE, REPLACE, DELETE and CREATE TABLE. Lets see an example using the previous statement. First of all, we create the prepared statement and name it show_city:

Then we set the parameter, named a_city in this case:

And we execute the prepared statement show_city using the parameter a_city:

+-----------+------------+| city_name | state_name |+-----------+------------+| Tequila   | Jalisco    |+-----------+------------+1 row in set (0.04 sec)

Footnotes

Remember to mark as index the fields that you intend to use frequently for searches, this will speed up the queries to the database.

All the usage examples, specially the prepared statements at the end, are in preparation of a series of post about how to use MySQL from PHP and python, as those are the languages that I have been using the most lately, and ash but that is another story.

Extra resources

List of functions and operators of MySQL
http://dev.mysql.com/doc/refman/5.0/en/func-op-summary-ref.html

The script used to create the tables used as example.
Change 'db_name' for the name of your database.
Download Script