Working with databases in mysql

The following is a brief tutorial on how to login to mysql shell from Linux (Ubuntu 11.10).

First of all mysql-server must be installed in your system. To enter the mysql commands from the shell we need to first change into mysql shell from the default unix shell window. To do this,

• Find the mysql directory in your local computer. (by default this is /usr/bin if you installed mysql from the terminal via apt-get install). Now we need to get into mysql shell.

• Enter the following command in a terminal window.

         $ [mysql-directory]/bin/mysql host -u root -p

•  In my system this is,

          $ /usr/bin/mysql mydb_1 -u root -p where host is replaced by the corresponding database name i need to connect. This tells the system to connect the database as root by demanding the password (-p). Once you get connected the following text would appear and mysql shell would be opened.

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 151
Server version: 5.1.62-0ubuntu0.11.10.1 (Ubuntu)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Now we can enter any valid mysql command and proceed with database queries. Following are some of the common and important commands. Always keep in mind that each command must be finished with ; or \g

1. To view the 'help' entries
mysql> help;

2. To view the list of existing databases

mysql> show databases;

The output will be something like,

mysql> show databases \g
+------------------------------------+
 | Database                    |
+----------------------------------+
 | information_schema |
| mysql                        |
+-------------------------------+
2 rows in set (0.00 sec)
 

3. To create a new database

mysql> create database mynew_db;

4. To drop a database

mysql> drop database mynew_db;

The output will be like,

Query OK, 102 rows affected (23.71 sec)
 

5. To quit or exit from connection

mysql> exit; or  mysql> quit;