DDL - MySQL (DATABASE)

Use to define database, tables, index, ...

Shows all database (SHOW)

Syntax

SHOW DATABASES;

Usage

mysql> SHOW DATABASES;

Output:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
6 rows in set (0.06 sec)

Create a Database (CREATE)

Syntax

CREATE DATABASE IF NOT EXISTS <db_name>;

• IF NOT EXISTS can safely create a database, as it will only create the database if it's not exist.
• If we re-create an existed database without IF NOT EXISTS, we will get error message.

Example Usage

mysql> CREATE DATABASE IF NOT EXISTS testing1;

Output:

Query OK, 1 row affected (0.01 sec)

Validate if database is created

mysql> show databases;

Output:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| testing1           |
| world              |
+--------------------+
7 rows in set (0.00 sec)

INFO

Database testing1 has been created.

Delete (Drop) a database (DROP)

Syntax

DROP DATABASE IF EXISTS <db_name>;

• IF EXISTS can safely drop a database, as it only run drop database operation if the database is existed.
• If we try drop an non-exists database without IF EXISTS, we will get error message.

Example Usage

mysql> DROP DATABASE IF EXISTS testing1;

Output:

Query OK, 0 rows affected (0.01 sec)

Validate if database is dropped

mysql> show databases;

Output:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
6 rows in set (0.00 sec)

INFO

Database testing1 has been dropped (removed/deleted).

Use a Database (USE)

Syntax

USE <db_name>;

Usage

• Create a test database.

CREATE DATABASE IF NOT EXISTS test;

• Use test database

mysql> USE test;

Output:

Database changed

Select Database (SELECT)

• We using select database(); when we forget what current database has been use now.

Syntax

SELECT DATABASE();

Usage

• Continue to the step we use test;.

mysql> select database();

Output:

+------------+
| database() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)