• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

The Urban Penguin

The Urban Penguin - Linux Training

  • Home
  • About
  • Programming
    • Master Editing Text Files Using VIM
    • Learn Shell Scripting with BASH
    • PERL Scripting in Linux
    • Ruby Scripting in Linux
    • Scripting with PowerShell
    • Learn C Programming using Linux and the Raspberry Pi
    • General Java Tutorials
    • Java 7 OCA Exam 1ZO-803
  • OS Tutorials
    • Red Hat and CentOS Training
      • Red Hat Enterprise Linux System Administration 1 – RH124
      • RHCSA – System Admin 2 – RH134
      • RHCE – EX294 – Automation With Ansible
    • Learning Ubuntu
    • LPI Training
      • LPI Linux Essentials
      • LPIC-1 Linux Administrator
      • LPIC-2 Certified Linux Engineer
      • LPIC-3 Senior Level Certification
        • LPIC-3 Exam 300 : Mixed Environments
        • LPIC-3 Exam 303 : Security
        • LPIC-3 Exam 304 : Virtualization and High Availability
    • Linux Technologies
      • Apache HTTPD Server
      • Learning PHP
      • Learning PUPPET
      • Learning SAMBA
      • Linux File-Systems
      • Monitoring with Nagios Core
      • MYSQL
      • openLDAP Directories on Linux
You are here: Home / Linux / RH254 / RHCE – Creating a New Database in MariaDB

RHCE – Creating a New Database in MariaDB

June 23, 2015 by The Urban Penguin

RHCE Login to MariaDB

We have now installed MariaDB onto the RHEL 7 or 7.1 system; however,The only MariaDB account we have is root. We can test the login to the database server using the client, mysql.

$ mysql -u root -p

We should be prompted for the MariaDB root account password. When entered correctly we should be presented with as mysql prompt. From here we can use exit or quit to leave the client session.

Create a MariaDB Database on RHEL 7.1

Other than the system databases we do not have any storage containers on the new database server. To display databases on the server we can use the commands

$ mysql -u root -p
MariaDB> SHOW DATABASES;

We, obviously, need to login to the server first and then run the second command. MariaDB and MySQL commands are not case sensitive but often keywords are written in upper case. The end of a line in the SQL language is terminated with a semi-colon (;).

We can see from the output that we have three system databases on the database server. It is an easy task to create a new database:

MariaDB > CREATE DATABASE staff;
MaraDB > USE staff;
MariaDB [staff]>

The USE command is very similar to a cd command in the file system, changing your context to the new database, staff in this case. The MariaDB prompt shows the database name reminding us of the context.

Databases can be thought of as logical containers organizing content, much as with folders in the file system, databases themselves do not hold the data but they group tables which are the primary data storage mechanism. As well as tables, other objects such as indices store data.

Create a Simple Table

Within the staff database we will create a table to store organizational departments to which staff members belong. The table will be kept to a very simple design ensuring that we do not lose track of our design process with too much detail. The table itself is not the data but allows for the data to be stored by defining columns.

From within the staff database we can create the table with the following SQL code:

MariaDB [staff]> CREATE TABLE department (
did TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
dname VARCHAR(20),PRIMARY KEY(did));

We can see that the table definition is enclosed within he parenthesis and each column is delimited using the comma (,). We have also defined a Primary Key constraint which is defined on the did column. The constraint ensures uniqueness of each value stored within the column. The data-type for this column is TINYINT. This takes 1 byte of storage for each value and subsequently allows values from -128 to +127; however, adding the additional UNSIGNED attribute allows for only positive values 0-255 which suits the needs of our Department ID.

If we wanted top create the table from a context other than the staff database we could provide a full name for the table. In this case the code would start:

MariaDB > CREATE TABLE staff.department

I would strongly advise keeping object names simple but should you need to have spaces in the table or column you will need to quote them with back-ticks (`). On a UK keyboard layout this if found on the top left of the keyboard.

MariaDB > CREATE TABLE `staff`.`department name`

Note that when using dotted names the dot must not make up part of the quoted text.

We can show the schema or layout of the table using the DESCRIBE command in SQL:

MariaDB [staff]> DESCRIBE department;

For more detailed information and the code to recreate the table you can use:

MariaDB [staff]> SHOW CREATE TABLE department;

Share this:

  • Click to share on Twitter (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Reddit (Opens in new window)
  • Click to share on Pinterest (Opens in new window)
  • Click to share on Tumblr (Opens in new window)
  • Click to print (Opens in new window)

Filed Under: RH254

Primary Sidebar

Newest Video

The Urban Penguin On Youtube

Categories

Pages

  • About The Urban Penguin
  • Contact Us
  • Linux Technologies
    • Apache HTTPD Server
    • Learning PHP
    • Learning PUPPET
    • Learning SAMBA
    • Linux File-Systems
    • Monitoring with Nagios Core
    • MYSQL
    • openLDAP Directories on Linux
  • LPI Training from The Urban Penguin
    • Complete Linux Essentials
    • Live and Pluralsight hosted courses
    • LPI Linux Essentials
    • LPI Linux Essentials for Raspberry Pi
    • LPIC-1 Linux Administrator
    • LPIC-2 Certified Linux Engineer
    • LPIC-3 Senior Level Certification
      • LPIC-3 Exam 300 : Mixed Environments
      • LPIC-3 Exam 303 : Security
      • LPIC-3 Exam 304 : Virtualization and High Availability
  • Online Instructor-led Courses
    • Bash Scripting Masterclass
    • Nftables Masterclass
    • Red Hat Enterprise Linux System Administration 1 – RH124
    • SELinux Masterclass
  • OpenStack
    • Citrix Videos
    • Pluralsight
    • Raspberry Pi Tutorials
    • Udemy
  • Operating System Tutorials
    • Learning SUSE
    • Learning Ubuntu
    • Linux Foundation Training
    • Red Hat and CentOS Training
      • RHCE – EX294 – Automation With Ansible
      • RHCSA – System Admin 1 – RH124
      • RHCSA – System Admin 2 – RH134
    • Solaris 11 OCA 1ZO-821
  • Scripting – the power of repetition!
    • General Java Tutorials
    • Java 7 OCA Exam 1ZO-803
    • Learn C Programming using Linux and the Raspberry Pi
    • Learn Shell Scripting with BASH
    • Master Editing Text Files Using VIM
    • PERL Scripting in Linux
    • Ruby Scripting in Linux
    • Scripting with PowerShell

© 2022 The Urban Penguin · All Rights Reserved