Table of Contents

Perform Simple SQL Queries Against A Database

General Information

Basic SQL commands.

Lab Setup

The following virtual machines will be used:

Previous Sections Completed

Basics of SQL: Connect and Navigate

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;

Basics of SQL: Add,Remove,Modify


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;