RHEL7: Create a simple database schema.

Share this link

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).

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

Leave a Reply

6 Comments on "RHEL7: Create a simple database schema."

Notify of
Sort by:   newest | oldest
krz
Member
krz

After installing group mariadb and mariadb-client and updating man with mandb and check documentation on /usr/share/docs/ I cannot find anyplace with SQL syntax examples.

Any advice where to find it during the exam some examples just in case I don’t remember some details of the syntax?

Sam
Member
Sam

The simplest way to get the syntax is well the simplest, simply type help at the MariaDB or MySql command line.

The syntax is not pretty but with a little practice easy to used.
Example
MariaDB : help table;

Note: This is done from memory! It is easy to get confused using this method!

krz
Member
krz
Thanks Sam, I was not aware neither. This is great help, because apart from the syntax, some commands have even examples. MariaDB [inventory]> help grant; Name: ‘GRANT’ Description: Syntax: GRANT priv_type [(column_list)] [, priv_type [(column_list)]] … ON [object_type] priv_level TO user_specification [, user_specification] … [REQUIRE {NONE | ssl_option [[AND] ssl_option] …}] [WITH with_option …] GRANT PROXY ON user_specification TO user_specification [, user_specification] … [WITH GRANT OPTION] object_type: TABLE | FUNCTION | PROCEDURE priv_level: * | *.* | db_name.* | db_name.tbl_name | tbl_name | db_name.routine_name user_specification: user [ IDENTIFIED BY [PASSWORD] ‘password’ | IDENTIFIED WITH auth_plugin [AS ‘auth_string’] ] ssl_option: SSL… Read more »
wpDiscuz

RHCSA7: Task of the day

Allowed time: 5 minutes.
Create a new user account called "bob" with password "redhat" and set expiration in one week.

RHCE7: Task of the day

Allowed time: 15 minutes.
Configure a httpd server with a password protected directory under the /var/www/html/private directory.

Poll for favorite RHEL 7 book

What is your favorite RHEL 7 book to prepare RHCSA & RHCE exams?

View Results

Loading ... Loading ...

Poll for most difficult RHCSA 7 topic

What do you think is the most difficult RHCSA 7 topic?

View Results

Loading ... Loading ...

Poll for most difficult RHCE 7 topic

What do you think is the most difficult RHCE 7 topic?

View Results

Loading ... Loading ...

Recent Comments