Basic Language "MySQL Query" #3 : Creating and Deleting Database

What will I learn?

• Creating a Database
• Delete the Database

Requirements

• MySQL
• You have basic about MySQL

Difficulty

• Intermediate

Tutorial Contents

In the RDBMS system (such as MySQL and MariaDB), the database is a collection of interconnected tables. An application usually requires only one database. But one database can contain hundreds to thousands of tables (depending on the complexity of the application you want to create). In this database the tables will be stored.

To create a database, the query format is as follows:

CREATE DATABASE [IF NOT EXISTS] name_database;

Note:
Commands marked with square brackets, such as "[IF NOT EXISTS]" are optional commands (may not be written). This is the way of writing used in the official MySQL guide: MySQL Manual4.

Let's create an "indonesia" database:

MariaDB [(none)]> CREATE DATABASE indonesia;
Query OK, 1 row affected (0.04 sec)

Additional queries [IF NOT EXISTS] work to prevent MySQL from displaying error messages if the database already exists. For example if I try to recreate database "indonesia", there will be error:

MariaDB [(none)]> CREATE DATABASE indonesia;
ERROR 1007 (HY000): Can not create database 'indonesia'; database exists

This error message is useful for identifying errors. If we make long and executable code queries (which often come from PHP), this error message can cause the entire query to fail to run.

The [IF NOT EXISTS] command will create a database if the database is not present before.
If it already exists, the CREATE DATABASE query will not do anything (the old database will not be overwritten).

MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS indonesia;
Query OK, 0 rows affected, 1 warning (0.00 sec)

The difference is, now there is an additional 1 warning. In MySQL (and most programming languages), errors are different from warnings. The error occurred because of a fatal error and made the process stop. While the warning is only a minor error (minor) where the process can still be continued. For example using a deprecated function in PHP will display a warning.

To view warning messages from executed SQL commands, use the command:
SHOW WARNINGS;

Here's an example:
MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS indonesia;
Query OK, 0 rows affected, 1 warning (0.00 sec)

To see what database is available on MySQL server, can use SHOW DATABASES command. Here's an example:

It appears that in addition to the Indonesian database, MariaDB from XAMPP also has several built-in databases (wherever possible not to delete or change these databases) To delete a database, use the following command:

DROP DATABASE [IF EXISTS] database_name;

To note, DROP DATABASE queries will delete the database, including all tables and contents of the table. Please always be careful with this command, because in MySQL / MariaDB there is no "undo" button. If the database has been deleted, it can not be restored.

Just like the query on database creation, the [IF EXISTS] option is used to remove error messages if in case the database does not exist.
To delete the "indonesia" database, the query is as follows:

MariaDB [(none)]> DROP DATABASE indonesia;
Query OK, 0 rows affected (0.09 sec)

Now, database "indonesia" has been successfully deleted. Let's try again:

MariaDB [(none)]> DROP DATABASE indonesia;
ERROR 1008 (HY000): Can not drop database 'indonesia'; database does not exist

Will appear error message because database 'indonesia' is gone. The solution, can add IF EXISTS command:

MariaDB [(none)]> DROP DATABASE IF EXISTS indonesia;
Query OK, 0 rows affected, 1 warning (0.00 sec)

In addition to using keyword DATABASE, MySQL / MariaDB also supports keyword SCHEMA. That will also create a database.

Seen that in MySQL, schema is the same as database. But in other RBDMS systems like ORACLE and SQL Server, schema is a separate part of the database.

Important!!
Be careful in typing the query, the wrong query will make an error

The last query relating to the database is to select the default database. The command format is:
USE nama_database;

If you want to select database Indonesia, then the query is:

MariaDB [(none)]> USE indonesia;
Database changed

MariaDB [indonesia]>

Notice there is a cursor change from MariaDB [(none)]> to MariaDB [indonesia]>. That is, every query command that we type, will use database "indonesia" by default.

Curriculum

• Basic Language "MySQL Query" #1 : Creating Tables, Removing Tables, And Adding Data Into Tables.
• Basic Language "MySQL Query" #2 : Change Data Tables, Delete Data Tables, And Display Data Table

---

Finished already the tutorial above, may be useful.

---

Posted on Utopian.io - Rewarding Open Source Contributors

---