RHEL7: Create a simple database schema.

Note: This is an RHCE 7 exam objective.

Presentation of MariaDB Database Schema

A database schema consists of:

• a database name that groups all the objects together,
• one or several users with their associated access rights,
• a list of tables storing records,
• other objects like indexes, views, triggers, etc.

Note: A single MariaDB instance can host several databases.

Prerequisites

First, you need to install a MariaDB database.

Procedure

Then, you have to connect to the server with the password you created previously:

# mysql -u root -p
Enter password: your password
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 5.5.35-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>

Then, you can create a database (here called test):

MariaDB [(none)]> create database test;
Query OK, 1 row affected (0.00 sec)

Note: Use the drop database command to remove a database.

Give permissions to the user called user:

MariaDB [(none)]> grant all on test.* to user@localhost identified by 'your password';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Note: There is a password associated with the root account required during the server installation process. Then, there is another password linked to the database owner (here user). It’s easier if both are the same but they don’t need to.

Exit the MariaDB command line:

MariaDB [(none)]> quit
Bye

Now, you can connect to your own database directly:

# mysql -u user -p test
Enter password: your password
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 5.5.35-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [test]>

To get the list of all the available databases, type:

MariaDB [test]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)

Note: You can go inside a database with the use command. For example, type use test; to go to your new database.

To create a table called addresses, type:

MariaDB [test]> create table addresses(id int(10) unsigned, name varchar(20), address varchar(40));
Query OK, 0 rows affected (0.14 sec)

Note: Use the drop table command to remove a table.

To get the list of all the tables created in your database, type:

MariaDB [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| addresses      |
+----------------+
1 row in set (0.00 sec)

To get a description of a particular table (here addresses), type:

MariaDB [test]> desc addresses;
+---------+------------------+------+-----+---------+-------+
| Field   | Type             | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| id      | int(10) unsigned | YES  |     | NULL    |       |
| name    | varchar(20)      | YES  |     | NULL    |       |
| address | varchar(40)      | YES  |     | NULL    |       |
+---------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

To get the create table statement associated with the addresses table, type:

MariaDB [test]> show create table addresses;
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                    |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| addresses | CREATE TABLE `addresses` (
  `id` int(10) unsigned DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `address` varchar(40) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Useful Tip

At the MariaDB prompt, you can get some information about the syntax by using the help command:

MariaDB [(none)]> help drop table
Name: 'DROP TABLE'
Description:
Syntax:
DROP [TEMPORARY] TABLE [IF EXISTS]
    tbl_name [, tbl_name] ...
    [RESTRICT | CASCADE]

DROP TABLE removes one or more tables. You must have the DROP privilege
for each table. All table data and the table definition are removed, so
be careful with this statement! If any of the tables named in the
argument list do not exist, MySQL returns an error indicating by name
which nonexisting tables it was unable to drop, but it also drops all
of the tables in the list that do exist.
...

Additional Resources

You can also watch Andrew Mallett‘s video about Creating a MariaDB Database (6min/2015).