Databases

In this chapter, we will learn how to create create a new database and to drop databases, which you don't need anymore.
Before we start, we want to see, what databases are available for usage in our system. The command to accomplish this is called "SHOW DATABASES;". The following code shows a typical usage:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| phpmyadmin         |
+--------------------+
3 rows in set (0.03 sec)

mysql> 

Creating a Database

We create a database "company" in the following:

mysql> CREATE DATABASE company;
Query OK, 1 row affected (0.00 sec)

mysql> 

Using the "SHOW DATABASES" command again, we can see, that our new database shows up in the results:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| company           |
| mysql              |
| phpmyadmin         |
+--------------------+
4 rows in set (0.00 sec)

mysql> 

Before we go on using this database, we want to show you how to get rid of a database again. It might seem to early, because we have just created our database, but nevertheless it is necessary. E.g. you might create the database with a misspelling or the wrong name.

Drop a Database

There are many words usually used for getting rid of data or objects in programming languages or database languages, like "remove", "delete", "erase". SQL doesn't use none of those. SQL has found its own way of saying it: "drop" is used or to be exact "DROP DATABASE".
"DROP DATABASE db" drops all tables in the database "db" and deletes the database "db". So it's a command, which should be carefully used. Let's assume, we created a database "compny" instead of "company". We use the drop database command in the following session to get rid of the misspelled database and create the correct one afterwards:

mysql> CREATE DATABASE compny;
Query OK, 1 row affected (0.00 sec)

mysql> DROP DATABASE compny;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE DATABASE company;
Query OK, 1 row affected (0.00 sec)

mysql> 

Using a Database

To be able to use a database, we have to "inform" SQL with the use command. The "USE db" statement tells MySQL to use the db database as the default database for all the subsequent statements. The database remains the default until the end of the session or another USE statement is utilized. Before we can create tables for our previously created database "company", we have to utilize the "use" command:

mysql> use company;
Database changed
mysql> 

If we already know, that we want to work with the database company at the time of starting the SQL client, we can directly choose this database to work with on the command line, as you can see in the following example session:

bernd@venus:~$ mysql -u root -p company
Enter password: 
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 47
Server version: 5.1.61-0ubuntu0.11.04.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>