Working with MySQL database

Logging in

This mini-tutorial is devoted to working with MySQL database from a Unix/Linux shell. Upon logging into the Linux server, use the following syntax to login into the MySQL server:

mysql -u login -p

where login is your login to MySQL that might be different from your server login. You will need to type in your password to MySQL upon the next request. The mysql> will indicate that your login is success.

Creating databases

Use the following syntax to create a database. The current settings of our MySQL server you will only allow you to create a database with the name identical to your login (sb in my examples).

mysql> CREATE DATABASE sb;
Query OK, 1 row affected (0.02 sec)

An attempt to create an existing database will lead to an error.

Selecting a database

After a database is created, you can start working with it. There might be several databases for which you have an access. To show them use the following command. Make sure to use only capital letters and end it with a semicolon:

mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| sb       |
+----------+
1 row in set (0.08 sec)

To select a particular database (sb in my example), use the following syntax:

mysql> use sb;

After you see the "Database changes" message, type the following command to ensure that you have selected the required database:

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| sb         |
+------------+
1 row in set (0.03 sec)

Deleting a database

To delete a database, use the syntax:

mysql> DROP DATABASE sb;
Query OK, 1 row affected (0.01 sec)

Make sure to launch the SHOW DATABASES command to make sure that the database is indeed deleted.

Logging out

The command is quit:

List of all MySQL commands

Most of the commands you enter in the MySQL monitor are SQL commands. However, the database also supports a number of commands that are not part of the SQL language. To list all these commands, type in help; at the mysql> prompt. this produces the following output:

List of all MySQL commands:
   (Commands must appear first on line and end with ';')

help    (\h)    Display this help.
?       (\?)    Synonym for `help'.
clear   (\c)    Clear command.
connect (\r)    Reconnect to the server. Optional arguments are db and host.
edit    (\e)    Edit command with $EDITOR.
ego     (\G)    Send command to mysql server, display result vertically.
exit    (\q)    Exit mysql. Same as quit.
go      (\g)    Send command to mysql server.
nopager (\n)    Disable pager, print to stdout.
notee   (\t)    Don't write into outfile.
pager   (\P)    Set PAGER [to_pager]. Print the query results via PAGER.
print   (\p)    Print current command.
prompt  (\R)    Change your mysql prompt.
quit    (\q)    Quit mysql.
rehash  (\#)    Rebuild completion hash.
source  (\.)    Execute a SQL script file. Takes a file name as an argument.
status  (\s)    Get status information from the server.
system  (\!)    Execute a system shell command.
tee     (\T)    Set outfile [to_outfile]. Append everything into given
outfile.
use     (\u)    Use another database. Takes database name as argument.

The middle column is a shortcut for each command. Thus, if you wish to exit from the MySQL server, instead of typing quit you can also type just \q;