General Information

Basic SQL commands.

The following virtual machines will be used:

• server2.example.com (192.168.1.151) → Database here

Previous Sections Completed

• Install and Configure MariaDB
• Create a Simple Database Schema

Connect to the database

mysql -u root -p

Show databases on the system

MariaDB [(none)]> SHOW DATABASES;

Use a specific database called 'emails'

MariaDB [(none)]> USE emails;

Show the tables in the database, emails

MariaDB [emails]> SHOW TABLES;

Show the fields available in a table called 'users'

MariaDB [emails]> DESCRIBE users;

Insert two new rows/entries into a table (two methods)

# Values method
MariaDB [emails]> INSERT INTO users (userFirstName,userLastName,userEmailAddress) VALUES ("Robert","Jones","rjones@example.com");
 
# Set method
MariaDB [emails]> INSERT INTO users set userFirstName="Jane", userLastName="Smith", userEmailAddress="jsmith@example.com";

Show all entries from the users table

MariaDB [emails]> SELECT * FROM users;

Show all entries from the users table where the last name equals “Smith”

MariaDB [emails]> SELECT * FROM users WHERE userLastName="Smith";

Delete the entry where the user id is 2

MariaDB [emails]> DELETE FROM users WHERE userID=2;

Update the entry of Robert Jones to change the email address

MariaDB [emails]> UPDATE users SET userEmailAddress="robert.jones@example.com" WHERE userID=1;