Database Management Fundamentals

Introduction

Previously, we have covered the process of installing a MySQL server, connecting to it using a client and performing some basic administration tasks.

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.

In your terminal, log in to MySQL as shown. Make sure you have your username and password ready (or the root authentication details if necessary). You will be prompted for a password, enter it.

mysql -u root -p

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;

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;

Exporting a MySQL Database

Making a Script

Another feature of MySQL is that it supports easy importing and exporting of data. For this next exercise, we will need to create a table. The following SQL commands will do this for you. You can either copy and paste the SQL into your MySQL client directly (all at once) or make it into a script and execute it (we will show this below). MAKE SURE THAT YOU EXECUTE USE Hello_World; BEFORE YOU DO THIS.

USE Hello_World;

Create Table Person (
    personID Char(8) NOT NULL,
    firstName Varchar(30) NOT NULL,
    lastName Varchar(20) NOT NULL,
    streetNo Varchar(10) NOT NULL,
    streetName Varchar(20) NOT NULL,
    city Varchar(30) NOT NULL,
    state Varchar(4) NOT NULL,
    postcode Char(4) NOT NULL,
    dateOfBirth Date NOT NULL,
    email Varchar(50) NOT NULL,
    sex Char(1) NOT NULL,
    PRIMARY KEY(personID),
    CONSTRAINT sex_value CHECK (sex IN ('M','F','U')),
    CONSTRAINT email_value CHECK (email LIKE '%___@___%')
);

Insert Into Person Values ('02188873','Rhona','Sierra','31','Argust St','Middle Park','QLD','4207','2014-12-23','ramollin@aol.com','F');
Insert Into Person Values ('26924655','Ainslee','Paretski','135','Universal Dr','Oxenford','QLD','4211','2002-03-08','flaviog@msn.com','F');
Insert Into Person Values ('63173932','Domeniga','Watkins','10-14','Summerville Rd','Southport','QLD','4210','1972-11-21','mahbub@optonline.net','F');
Insert Into Person Values ('12692995','Ashely','Grey','60','Deaton Ave','Perisher Valley','NSW','4127','1998-04-04','alfred@me.com','F');
Insert Into Person Values ('29008529','Agathe','Michaelson','10','Gilbert St','Dubbo','NSW','2830','2012-07-25','rnewman@live.com','F');
Insert Into Person Values ('45610045','Teena','Popolopodus','13','Spit Rd','Mosman','NSW','2088','1996-10-16','ingolfke@icloud.com','F');
Insert Into Person Values ('94679772','Charmain','Zapzeda','61','Brodie St','Holland Park West','QLD','4121','1979-10-09','kiddailey@hotmail.com','F');
Insert Into Person Values ('55535777','Gleda','Jones','30','Arthur St','Sorell','TAS','7172','2017-07-31','jesse@me.com','F');
Insert Into Person Values ('15476925','Mariel','Thompson','365','Esplanade Ave','Scarness','QLD','4655','2004-12-12','dprice@me.com','F');
Insert Into Person Values ('86905538','Mariellen','Ryder','15','Kumnick St','Lobethal','SA','5241','2008-06-27','gemmell@mac.com','F');
Insert Into Person Values ('69967824','Patricia','DeWitt','10','Pinxton Crt','Carine','WA','6020','1967-08-08','gavollink@me.com','F');
Insert Into Person Values ('09405207','Timmie','Perkins','23','Timaru Cres','Eight Mile Plains','QLD','4113','1975-06-14','kingjoshi@outlook.com','F');
Insert Into Person Values ('42353947','Rhianon','Moman','2','Soderlund Drv','Doncaster','VIC','3108','1992-05-07','tamas@optonline.net','F');
Insert Into Person Values ('22447489','Tomi','Johnson','17','Adrian St','Margate','QLD','4019','1987-09-14','jaxweb@icloud.com','F');
Insert Into Person Values ('81987208','Micaela','James','746','Timboon-Colac Rd','Jancourt East','VIC','3266','1999-03-23','overbom@icloud.com','F');
Insert Into Person Values ('74123192','Meg','Carter','55','Cabarita Rd','Avalon','NSW','2107','1957-02-28','naoya@outlook.com','F');
Insert Into Person Values ('62309196','Julianne','Miller','9','Geographe Way','Withers','WA','6230','2000-01-21','chickibabs@hotmail.com','F');
Insert Into Person Values ('59690847','Birgit','Hankey','15','Ocean Ave','Surf Beach','NSW','2536','1985-06-19','bigbeard@live.com','F');

commit;

If you wish to make a script, just copy and paste all of this text into a new file in any text editor. Then save it as createTable.sql.

To execute this script, open your MySQL client and execute the following. Note that the second command does NOT HAVE A SEMICOLON AT THE END.

USE Hello_World;
\. <Absolute Path to Script File>

Ensure that you include the ABSOLUTE file path WITHOUT QUOTES on the ends - even if the path contains spaces. A quick way to get the absolute path is to simply click-and-drag the script file from a file browser into your MySQL terminal window - if you do this, remember to remove the quotes from either side of your path (because Linux puts them in automatically, but we need to remove them).

Now, if you execute SHOW TABLES;, you will see that a new table has been created.

SHOW TABLES;

MariaDB [Hello_World]> SHOW TABLES;
+-----------------------+
| Tables_in_Hello_World |
+-----------------------+
| Person                |
+-----------------------+
1 row in set (0.000 sec)

SELECT ... INTO OUTFILE Statement

To export data from a database table, you can use the SELECT ... INTO OUTFILE command.

You can use this command to export the data returned by any query into an external file. Note that it will not work if the output file already exists.

When exporting using this method, the output file must be in a secure location on your system that is designated by MySQL. To look at what your location is, use the following command.

SHOW VARIABLES LIKE "secure_file_priv";

MariaDB [Hello_World]> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv |       |
+------------------+-------+
1 row in set (0.000 sec)

As you can see, the output displays a folder. This is the location in which your import/export files need to be held. If your file is different from the one shown, you will need to alter the path in the following command accordingly.

Execute the following command and if necessary, alter the path.

SELECT * FROM Person INTO OUTFILE 'personData.txt';

MariaDB [Hello_World]> SELECT * FROM Person INTO OUTFILE 'personData.txt';
Query OK, 18 rows affected, 1 warning (0.000 sec)

Now, if you exit your MySQL client (or open a new terminal) and execute the following command. You will see the contents of your file. Note that you may need root privileges on your operating system to do this (Remember, alter the file path as appropriate).

sudo cat /var/lib/mysql/Hello_World/personData.txt

02188873        Rhona   Sierra  31      Argust St       Middle Park     QLD     4207    2014-12-23      ramollin@aol.com        F
09405207        Timmie  Perkins 23      Timaru Cres     Eight Mile Plains       QLD     4113    1975-06-14      kingjoshi@outlook.com   F
12692995        Ashely  Grey    60      Deaton Ave      Perisher Valley NSW     4127    1998-04-04      alfred@me.com   F
15476925        Mariel  Thompson        365     Esplanade Ave   Scarness        QLD     4655    2004-12-12      dprice@me.com   F
22447489        Tomi    Johnson 17      Adrian St       Margate QLD     4019    1987-09-14      jaxweb@icloud.com       F
26924655        Ainslee Paretski        135     Universal Dr    Oxenford        QLD     4211    2002-03-08      flaviog@msn.com F
29008529        Agathe  Michaelson      10      Gilbert St      Dubbo   NSW     2830    2012-07-25      rnewman@live.com        F
42353947        Rhianon Moman   2       Soderlund Drv   Doncaster       VIC     3108    1992-05-07      tamas@optonline.net     F
45610045        Teena   Popolopodus     13      Spit Rd Mosman  NSW     2088    1996-10-16      ingolfke@icloud.com     F
55535777        Gleda   Jones   30      Arthur St       Sorell  TAS     7172    2017-07-31      jesse@me.com    F
59690847        Birgit  Hankey  15      Ocean Ave       Surf Beach      NSW     2536    1985-06-19      bigbeard@live.com       F
62309196        Julianne        Miller  9       Geographe Way   Withers WA      6230    2000-01-21      chickibabs@hotmail.com  F
63173932        Domeniga        Watkins 10-14   Summerville Rd  Southport       QLD     4210    1972-11-21      mahbub@optonline.net    F
69967824        Patricia        DeWitt  10      Pinxton Crt     Carine  WA      6020    1967-08-08      gavollink@me.com        F
74123192        Meg     Carter  55      Cabarita Rd     Avalon  NSW     2107    1957-02-28      naoya@outlook.com       F
81987208        Micaela James   746     Timboon-Colac Rd        Jancourt East   VIC     3266    1999-03-23      overbom@icloud.com      F
86905538        Mariellen       Ryder   15      Kumnick St      Lobethal        SA      5241    2008-06-27      gemmell@mac.com F
94679772        Charmain        Zapzeda 61      Brodie St       Holland Park West       QLD     4121    1979-10-09      kiddailey@hotmail.com   F

Exporting Table as Raw Data

Using the SELECT ... INTO OUTFILE command exports a query as raw data. You can also use the mysqldump binary to do the same task for the contents of an entire table. This binary calls SELECT ... INTO OUTFILE internally, however the difference is that it can be used outside of the MySQL client. The general syntax of this command is outlined below. Note that using the --no-create-info tag makes the command export raw data (like before).

mysqldump -u <username> -p --no-create-info --tab=<Export file directory> <Database name> <Table name>

Again, because SQL is so secure, data can only be exported to the special directory designated by MySQL. Again, if executing the following, make sure you specify the correct path. On a Unix system, you may need root (sudo) privileges to do this as well.

sudo mysqldump -u root -p --no-create-info --tab=/var/lib/mysql/Hello_World/ Hello_World Person

Now, you can preview the contents of the file.

sudo cat /var/lib/mysql/Hello_World/Person.txt

Exporting Table Contents/Definitions in SQL

You can also export data as an .sql file which is basically an SQL script that contains the complete instructions on recreating the table and adding all the data back into it. Fortunately, this can be created in any folder without needing special permissions. This is also done using mysqldump.

mysqldump -u <username> -p <database name> <table name> > <output file>

Try the following command.

cd ~/project
mysqldump -u root -p Hello_World Person > createPerson.sql
cat createPerson.sql

Importing a Database

Before the next couple of tutorials, we will need to copy one of our other raw data files into our current directory (to make access easier). Run the following command (replacing the file target if you must).

cd ~/project
sudo cp /var/lib/mysql/Hello_World/Person.txt .

This will have copied one of your raw data files into the current directory for easier use in the next few steps.

The following process (of importing data) is extremely useful. It can be used to restore a backup your data (created using an export), as well as transfer it between databases and even different host servers.

Importing Data with LOAD DATA

Log back into your MySQL client and run the following.

mysql -u root -p

USE Hello_World;
DELETE FROM Person;

This will have successfully deleted all records from the Person table. Run the following to verify that the table is empty.

SELECT * FROM Person;

MariaDB [Hello_World]> SELECT * FROM Person;
Empty set (0.000 sec)

As you can see, there are no records in our Person table anymore.

Now, log out of your MySQL client and run the following command.

mysql --local-infile -u root -p

Then, run the following.

USE Hello_World;
LOAD DATA LOCAL INFILE 'Person.txt' INTO TABLE Person;

The LOAD DATA statement is basically a bulk data loader. It basically reads a file that you specify (in this case 'Person.txt' and adds it to a table you specify). Using LOCAL in this command will allow you to specify relative paths for a raw input file.

MariaDB [(none)]> USE Hello_World;
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 [Hello_World]> LOAD DATA LOCAL INFILE 'Person.txt' INTO TABLE Person;
Query OK, 18 rows affected (0.001 sec)
Records: 18  Deleted: 0  Skipped: 0  Warnings: 0

As you can see, the command has imported 18 records. To verify that our data is in the Person table again, run the following query.

SELECT * FROM Person;

Success! It has worked.

Importing Data with mysqlimport

Similar to exporting data using mysqldump, you can also import data outside of the MySQL client using the mysqlimport binary. As expected, this binary will implicitly call LOAD DATA.

Remove all records from the 'Person' table again.

DELETE FROM Person;
exit

Good, now log out of your MySQL client and execute the following command.

mysqlimport -u root -p --local Hello_World Person.txt

labex:project/ $ mysqlimport -u root -p --local Hello_World Person.txt
Enter password:
Hello_World.Person: Records: 18  Deleted: 0  Skipped: 0  Warnings: 0

Good, as you can see, all 18 rows have been imported. You can log into your MySQL client and will find that the data is now in the 'Person' table just as before.

Importing Data from Complete Schema

When we were importing, we have been using raw data files. However, recall that we also exported non-raw files that contained the table schema as well as the data. You won't have success importing these files using the tools shown above.

Instead, you can simply run these files because they are scripts. We did this briefly when we first added the data to the table.

When you're logged into your MySQL client and are working in the 'Hello_World' database, run the following commands (the first will drop the table).

cd ~/project
mysql -u root -p

USE Hello_World;
DROP TABLE Person;
\. ./createPerson.sql

As you can see from the output, the table has been created from the data in createPerson.txt. It didn't matter that the file didn't have the .sql extension, it still acted like a script.

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.