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:
- server1.example.com (192.168.1.150) → Perform any client testing here
- server2.example.com (192.168.1.151) → Install the database here
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, userFirstName char(25), userLastName char(30), userEmailAddress char(50))
- When you create a table, you must define the names of the columns and the type of data they should contain
- userID INT → Named userID and of type integer
- userFirstName char(25) → Named userFirstName and of type characters up to 25 long
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;