linux_wiki:create_a_simple_database_schema [Owlbear Consulting

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== 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
<code bash>
mysql -u root -p
</code>

\\
Show databases available
<code bash>
MariaDB [(none)]> SHOW DATABASES;
</code>

\\
Open a database
<code bash>
MariaDB [(none)]> USE mysql;
</code>

\\
Display tables in the database
<code bash>
MariaDB [(mysql)]> SHOW TABLES;
</code>

\\
Display all (SELECT *) contents of the table 'host'
<code bash>
MariaDB [(mysql)]> SELECT * FROM host;
</code>

\\
Show details of the table 'host'
<code bash>
MariaDB [(mysql)]> DESCRIBE host;
</code>

----

====== Create a Database ======

\\
Create a database called 'emails'
<code bash>
MariaDB [(none)]> CREATE DATABASE emails;
</code>

\\
Open the new database
<code bash>
MariaDB [(none)]> USE emails;
</code>

\\
Create a new table in the emails database
<code bash>
MariaDB [emails]> CREATE TABLE users (userID INT AUTO_INCREMENT PRIMARY KEY, userFirstName CHAR(50), userLastName CHAR(50), userEmailAddress CHAR(50)) AUTO_INCREMENT = 100;
</code>
  * 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.
<code bash>
MariaDB [emails]> SHOW TABLES;
MariaDB [emails]> SELECT * FROM users;
MariaDB [emails]> DESCRIBE users;
</code>

----