Create A Simple Database Schema

General Information

Creating a simple database in MariaDB.

Lab Setup

The following virtual machines will be used:

  • ( → Database here

Previous Section Completed

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;

  • linux_wiki/create_a_simple_database_schema.txt
  • Last modified: 2019/05/25 23:50
  • (external edit)