General Information

Creating a simple database in MariaDB.

The following virtual machines will be used:

• server2.example.com (192.168.1.151) → Database here

Previous Section Completed

• Install and Configure MariaDB

Connect to the database

mysql -u root -p

Show databases available

MariaDB [(none)]> SHOW DATABASES;

Open a database

MariaDB [(none)]> USE mysql;

Display tables in the database

MariaDB [(mysql)]> SHOW TABLES;

Display all (SELECT *) contents of the table 'host'

MariaDB [(mysql)]> SELECT * FROM host;

Show details of the table 'host'

MariaDB [(mysql)]> DESCRIBE host;

Create a database called 'emails'

MariaDB [(none)]> CREATE DATABASE emails;

Open the new database

MariaDB [(none)]> USE emails;

Create a new table in the emails database

MariaDB [emails]> CREATE TABLE users (userID INT AUTO_INCREMENT, userFirstName CHAR(50), userLastName CHAR(50), userEmailAddress CHAR(50), PRIMARY KEY (userid)) AUTO_INCREMENT = 100;

• When you create a table, you must define the names of the columns and the type of data they should contain
• Column Definitions
  • userID INT → Named userID and of type integer
    • AUTO_INCREMENT → Auto increment the entries in this column
  • userFirstName char(25) → Named userFirstName and of type characters up to 25 long
  • PRIMARY KEY (userid) → The userid column is the primary sorting key
• Table Options
  • AUTO_INCREMENT = 100 → Start userid at 100

Show the tables in the emails DB, display contents of the users table, show the data types in the users table.

MariaDB [emails]> SHOW TABLES;
MariaDB [emails]> SELECT * FROM users;
MariaDB [emails]> DESCRIBE users;