Managing MySQL from the command line

Friday, March 18, 2011 | 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

MySQL statements for manipulating entries in a table

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.

mysql -h [host] -D [database] -u [username] -p

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

mysql -h dbserver.jveweb.net -D database_name -u juan -p

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

mysql -h dbserver.jveweb.net -D database_name -u juan -psomepassword

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 names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6324623
Server version: 5.1.39-log MySQL Server

Copyright (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 license

Type '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.:

show tables;

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

show
tables
;

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:

export EDITOR=vim

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

echo $EDITOR

Processing a batch file

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

mysql -u user -ppass -h host -D database_name < batch_file.sql

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

source batch_file.sql

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

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:

show table status;

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:

show table status like 'name_of_the_table';

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:

show table status where `Engine` like 'MyISAM';

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.

create table `database_name`.`test_table` (
`field_id` int( 11 ) unsigned not null auto_increment comment 'the primary key',
`field_index1` int( 11 ) unsigned not null comment 'an index',
`field_index2` int( 11 ) unsigned not null comment 'an index',
`field_index3` int( 11 ) unsigned not null comment 'an index',
`field_unique1` int( 11 ) unsigned not null comment 'a unique field',
`field_unique2` int( 11 ) unsigned not null comment 'a unique field',
`field_unique3` int( 11 ) unsigned not null comment 'a unique field',
`field_varchar` varchar( 100 ) not null comment 'a varchar field',
`field_date` date not null comment 'a date field',
`field_datetime` datetime not null comment 'a datetime field',
`field_float` float not null comment 'a float field',
`field_longtext` longtext not null comment 'a longtext field',
`field_bool` bool not null comment 'a boolean field',
`field_char` char( 1 ) not null comment 'a char field',
`field_tinyint` tinyint not null comment 'a tinyint field',
primary key ( `field_id` ) ,
index ( `field_index1` , `field_index2`, `field_index3` ) ,
unique ( `field_unique1` , `field_unique2`, `field_unique3`)
) engine = myisam character set utf8 collate utf8_general_ci comment =
'table comments';

List the fields in a table

show columns from `test_table`;

Change the name of a field in a table

alter table `test_table` change `field_index1` `new_field_name` int(11) unsigned not null;

Add a field to a table and make it an index

alter table `test_table` add `new_index_field` int(11) unsigned not null, add index(`new_index_field`);

Remove an index from a table

alter table `test_table` drop index `new_index_field`;

Remove a field from a table

alter table `test_table` drop `index_new`;

Add fields after a specified field

alter table `test_table` add `for_deletion` varchar(12) not null after `field_date`;

Add fields at the start of the table

alter table `test_table` add `for_deletion_2` varchar(12) not null first;

Add multiple fields to the table

alter table `test_table` add `for_deletion_3` varchar(12) not null after `for_deletion`, add `for_deletion_4` varchar(12) not null after `for_deletion_3`;

Delete fields in a table

alter table `test_table` drop `for_deletion`, drop `for_deletion_2`, drop `for_deletion_3`, drop `for_deletion_4`;

Rename a table

rename table `database_name`.`original_name` to `database_name`.`new_name`;

Change the comment of a table

alter table `test_table` comment='The comments';

Change the auto increment value of a table

alter table `test_table` auto_increment=3;

Repair a table

repair table `test_table`;

Optimize a table

optimize table `test_table`;

Delete all the entries in a table

truncate table `test_table`;

Delete a table

drop table `test_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

select [fields] from `table_name` [where conditions] [order by order1 asc/desc,order2 asc/desc] [limit start,limit];

select [x.field,y.field] from `table1` x, `table2` y where y.`id`=x.`index` [extra conditions] [order by x.field,y.field] [limit start,limit];

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.

select * from `states`;

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

select * from `cities` where `city_id` = '3';

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

select `city_name`,`city_population` from `cities` order by `city_population` asc;

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

select `city_name` from `cities` where `state_id` = '2' order by `city_name` desc;

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

select * from `cities` limit 2,3;

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

select `city_name` from `cities` where `city_name` like 'G%';

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

select * from `cities` where `city_population` between '500000' and '1000000';

select * from `cities` where `city_population`>='500000' and `city_population`<='1000000';

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

select c.`city_name`,s.`state_name` from `states` s, `cities` c where c.`state_id`=s.`state_id` order by c.`city_name`;

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

select c.`city_name`,s.`state_name` from `states` s, `cities` c where c.`state_id`=s.`state_id` and c.`city_name` like 'G%';

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

select c.`city_name`,s.`state_name`,c.`city_population` from `states` s,`cities` c where s.`state_id`=c.`state_id`;

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

select c.`city_name`,s.`state_name`,c.`city_population` from `states` s, `cities` c where s.`state_id`=c.`state_id` order by s.`state_name` desc,c.`city_population` asc;

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

select count(*) from `table_name` [where conditions];

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

select count(*) from `cities`;

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

Sum the entries of a table

select sum(`city_population`) from `cities`;

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

Insert an entry

insert into `states` (`state_name`) values ( "Oaxaca");

insert into `cities` (`city_name`,`city_population`,`state_id`) values ('Oaxaca','258008',LAST_INSERT_ID());

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:

insert into `states` (`state_name`) values( "Oaxaca");

insert into `cities` (`city_name`,`city_population`,`state_id`) values ('Oaxaca','258008',LAST_INSERT_ID()),
(`Salina Cruz`,`76219`,LAST_INSERT_ID()),
(`Zaragoza`,`85869`,LAST_INSERT_ID());

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:

insert into `states` (`state_name`,`creation_time`) values ('Sonora', NOW());

Update an entry

update `cities` set `city_name`='Some Name',`city_population`='1000000' where `city_id`='5';

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:

update `accounts` set `active`='1' where `creation_date` between '2010-02-12 00:00:00' and '2010-02-12 23:59:59';

Delete an entry

delete from `states` where `state_id`='8';

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:

select c.`city_name`,s.`state_name` from `states` s, `cities` c where c.`state_id`=s.`state_id` and c.`city_id` = ?;

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:

prepare show_city from "select c.`city_name`,s.`state_name` from `states` s, `cities` c where c.`state_id`=s.`state_id` and c.`city_id` = ?";

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

set @a_city = "2";

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

execute show_city using @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

Categories: Commands, FOSS, Linux, MySQL