Database Management Fundamentals

Introduction

In this lab, we will look at managing databases. This will involve creating databases, dropping databases and selecting databases. We will also look at retrieving database metadata and importing/exporting databases.

Learning Objective

• Concepts about database management: creating, listing, selecting and dropping databases.
• Practice operations above through various MySQL clients, command line and PHP scripts.
• Export data from a database table.
• Import data into a database table.

Skills Graph

Creating a Database Using the Command Line

Perhaps the most straightforward way to create a new database is to use the MySQL client directly.

Open a terminal window and start the MySQL client:

mysql -u root -p

Press Enter.

Usually, you will be prompted to enter the password. In LabEx VM, you don't need to enter a password, so just press Enter.

Listing Available Databases

Before we create a database, if you would like to list the available databases on your MySQL server, you can do so using the show databases; command. Try it now (remember, it's 'database++s++', as in plural).

SHOW DATABASES; or show databases; are all valid commands. In SQL, commands are not case-sensitive.

SHOW DATABASES;

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.000 sec)

As you can see, the command has listed all the databases available on the server.

Now, back to creating databases.

Creating a Database Using the MySQL client

You can use the create database command to accomplish this task. Try it now and then use the show databases command again to see the difference.

CREATE DATABASE Hello_World;
SHOW DATABASES;

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

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| Hello_World        |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.000 sec)

As you can see, you have successfully created a new database called 'Hello_World'.

Case-Sensitivity of Database Names

While SQL itself is a case-insensitive language, in MySQL, database names are case-sensitive, as well as table names. To demonstrate, make the following database.

CREATE DATABASE Hello_World;

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

As you can see, it failed because there is already a database called 'Hello_World'.

However, try to create another database as follows.

CREATE DATABASE hello_world;
SHOW DATABASES;

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

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| Hello_World        |
| hello_world        |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.000 sec)

As you can see, we were successful, because database names are case-sensitive. 'Hello_World' and 'hello_world' are two different databases.

Creating a Database Using mysqladmin Binary

You can also create a database using the mysqladmin tool, directly from the command line. Exit MySQL using either quit or exit (you don't need to worry about the semi-colon), then run the following command.

mysqladmin -u root -p create hello_world2
mysql -u root -p

Then, log back into your MySQL client and view the database list.

SHOW DATABASES;

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| Hello_World        |
| hello_world        |
| hello_world2       |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
7 rows in set (0.000 sec)

As you can see, you have successfully created the 'hello_world2' database using the mysqladmin binary. This is probably the quickest way to make a new database if you need it.

Dropping a MySQL Database

Now, we don't need all of these different databases. The only one that we want to keep is 'Hello_World'.

So now, what we need to do is drop all of the databases that we don't want. This process is very similar to creating databases, hence this part of the lab will be more fast-paced.

As you may have guessed, 'dropping' is the SQL equivalent of deleting something.

Dropping a Database Using the Command Line

You can drop a database using the MySQL client CLI. Log in if you aren't already and execute the following command (remember, database names are case-sensitive). Try it now and then look at the database lists.

DROP DATABASE hello_world;
SHOW DATABASES;

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

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| Hello_World        |
| hello_world2       |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.000 sec)

As you can see, the database 'hello_world' is gone.

This process is somewhat permanent, so be careful when using this command because the client won't ask you if you're sure you want to drop a database.

Dropping a Database using mysqladmin

This method of dropping a database is considered the 'safest' (you'll find out why in a minute).

Exit the MySQL client and use the following command to utilize the mysqladmin binary for dropping a table.

mysqladmin -u root -p drop hello_world2

Enter password:
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'hello_world2' database [y/N] y
Database "hello_world2" dropped

Note that you were prompted before the delete took place. This is why this is considered the 'safest' way to drop a table.
If you log into your MySQL client again and look at the database lists, you will see that the 'hello_world2' table has been removed successfully.

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| Hello_World        |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.000 sec)

Perfect! Log into your MySQL client and view the database list to confirm your success.

Again, this method will not prompt you before dropping a database, so be careful!

Selecting a MySQL Database

So far, we have created and destroyed databases using many different methods.

Is there any point to this though? What can we do with these databases?

The answer to this question will be explored in the next few labs. However, in this lab, we will briefly learn how to select/switch the database that we want to work on.

The process is really simple.

Select MySQL Database from the Command Prompt

The command used to move between different databases on your MySQL server is outlined below (where <Database Name> is the name of the database you wish to move to).

USE <Database Name>

Try it! Log into your MySQL client and execute the following commands. Remember, you can list all the available tables using the SHOW TABLES; command.

SHOW DATABASES;
USE Hello_World;
SHOW TABLES;

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| Hello_World        |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.000 sec)

MariaDB [(none)]> USE Hello_World;
Database changed
MariaDB [Hello_World]> SHOW TABLES;
Empty set (0.000 sec)

MariaDB [Hello_World]>

As you can see, we have moved into our 'Hello_World' database. The show tables command simply lists the tables found in a database. It returned 'Empty set' because we haven't created any tables yet.

Now switch to a different database, then list the tables there.

mysql> USE mysql;
mysql> SHOW TABLES;

MariaDB [Hello_World]> USE mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> SHOW TABLES;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| column_stats              |
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| global_priv               |
| gtid_slave_pos            |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| index_stats               |
| innodb_index_stats        |
| innodb_table_stats        |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| roles_mapping             |
| servers                   |
| slow_log                  |
| table_stats               |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| transaction_registry      |
| user                      |
+---------------------------+
31 rows in set (0.000 sec)

You can see the difference in the output of show tables that you have moved into a different database on your server.

Obtain and Use MySQL Metadata

In MySQL (or any other database management system), the primary goal of a query is to retrieve data from your database. For example, you may want the names and phone numbers of anybody who lives in a particular city.

However, there is also other information that you can retrieve from MySQL. This information can be referred to as metadata - data about the data.

Obtain the Number of Rows Affected by a Query

Open your MySQL client and run the following query on the MySQL database (note that you do not need to specify which database you are using, the query already does this).

SELECT user, host FROM mysql.user;

This command will list all users for the server.

MariaDB [mysql]> SELECT user, host FROM mysql.user;
+-------------+-----------+
| User        | Host      |
+-------------+-----------+
| mariadb.sys | localhost |
| mysql       | localhost |
| root        | localhost |
+-------------+-----------+
3 rows in set (0.001 sec)

As you can see, the query should list all users for the server (and their hosts).

Perfect!

Getting Server Metadata

There are also a number of in-built MySQL commands that can be used to retrieve metadata about your MySQL server. Open your MySQL client and try them yourself.

Server Version

The following MySQL command can be used to return the server version.

SELECT VERSION();

MariaDB [(none)]> SELECT VERSION();
+----------------------------------+
| VERSION()                        |
+----------------------------------+
| 10.6.12-MariaDB-0ubuntu0.22.04.1 |
+----------------------------------+
1 row in set (0.000 sec)

Selected Database

The following MySQL command can be used to return the current database that you are working in. Try using it before and after you have entered your 'Hello_World' database.

SELECT DATABASE();
USE Hello_World;
SELECT DATABASE();

MariaDB [(none)]> SELECT VERSION();
+----------------------------------+
| VERSION()                        |
+----------------------------------+
| 10.6.12-MariaDB-0ubuntu0.22.04.1 |
+----------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| NULL       |
+------------+
1 row in set (0.000 sec)

MariaDB [(none)]> USE Hello_World;
Database changed
MariaDB [Hello_World]> SELECT DATABASE();
+-------------+
| DATABASE()  |
+-------------+
| Hello_World |
+-------------+
1 row in set (0.000 sec)

Current User

The following MySQL command can be used to return details about the user that you are currently using. Try it!

SELECT USER();

MariaDB [Hello_World]> SELECT USER();
+----------------+
| USER()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.000 sec)

Status Indicators and Configuration Variables

The output of the following commands will not be shown due to their output length, but you can test them in your environment.

The following MySQL command can be used to show all server status indicators.

SHOW STATUS;

Finally, the following MySQL command can be used to show all server configuration variables.

SHOW VARIABLES;

Summary

Congratulations on finishing Lab 2! In the next lab, we will be looking at the different data types that are available in MySQL and how we can use them to store our data.