====== Create A Simple Database Schema ======
**General Information**
Creating a simple database in MariaDB.
----
====== Lab Setup ======
The following virtual machines will be used:
* server2.example.com (192.168.1.151) -> Database here
**Previous Section Completed**
* [[linux_wiki:install_and_configure_mariadb|Install and Configure MariaDB]]
----
====== Basic Database Commands ======
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 ======
\\
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 PRIMARY KEY, userFirstName CHAR(50), userLastName CHAR(50), userEmailAddress CHAR(50)) 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
* PRIMARY KEY -> The userid column is the primary sorting key
* userFirstName char(25) -> Named userFirstName and of type characters up to 25 long
* 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;
----