Database

The most top-level logical unit of a database is called (surprise!) database. A database consists of a set of tables. And a table consists of a set of columns.

Usually you don't write SQL queries to create a database or tables manually. Usually you use some GUI tool for that instead. For example, phpMyAdmin is a browser-based tool to manage MySQL databases.

Still, to illustrate how to do that, we'll create a database and a couple of tables with corresponding queries manually. Assume we're going to create some website, and we decided to store its data with MySQL. First thing we need is a database:

create database `website` character set utf8;

Query OK, 1 row affected, 1 warning (0.06 sec)

Oh, there's a warning message. Let's see what's up with show warnings statement:

show warnings;

+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                     |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 3719 | 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Alright, MySQL, we'll keep that in mind.

So, we've just created a database. create database is a statement, website is the database name, utf8 is default character set for our database.

If a database with a name specified is already exists then an error will occur. You can silently ignore such case with if not exists option:

create database if not exists `website` character set utf8;

To delete (or to drop) database you should use drop database statement. We won't do that in real, not this time. This time we just learn how to do that:

drop database `website`;

Same as with create database you can ignore a case when no database exists with a given name:

drop database if exists `website`;

Watch out! When you drop a database — you drop all its tables, and once you've done it — it's gone forever!