This is an old revision of the document!
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
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, 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 this field
- userFirstName char(25) → Named userFirstName and of type characters up to 25 long
- PRIMARY KEY (userid) → The userid field 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;