The Structured Query Language (or SQL) is how we both define and manipulate data within a traditional Relational Database Management System (or RDBMS). In this course, we are going to learn how to structure SQL statements that do just that. Learn how to create databases, manage access, create tables, insert and update data, create indexes and more. We will demonstrate SQL functions and talk about how they differ from the more traditional SQL statements. All of this will be accomplished at a SQL command line within MySQL, but will apply equally to SQL compliant RDBMS systems in general.
Creation and Setup
In this section we will create our database instance and set it up as well as introduce SQL as a language.
This is a brief introduction to SQL and the course itself
Learn how to install a MySQL database and then create one we can use. NOTE: Since the download page referenced in the video has changed, use this command to get the RPM file:wget https://dev.mysql.com/get/mysql80-community-release-el7-2.noarch.rpm
Now that we have a database, let's create a table we can use to learn with.
Create Table With Constraints
Now that we have created a table, let's learn about how we can apply constraints to control what is contained within each column.
Oracle MySQL Installation: Ubuntu
Learn how to install Oracle's MySQL Server in Ubuntu
Oracle MySQL Secure Setup: Ubuntu
Learn how to secure the MySQL instance in Ubuntu
Oracle MySQL Installation: CentOS
Learn how to install Oracle MySQL in CentOS
Oracle MySQL Secure Setup: CentOS
Learn how to secure Oracle MySQL instance in CentOS
MariaDB Installation: Ubuntu
Learn how to install the MySQL drop in replacement MariaDB in Ubuntu
MariaDB Secure Setup: Ubuntu
Learn how to secure the MariaDB instance in Ubuntu
MariaDB Installation: CentOS
Learn how to install the MySQL drop in replacement MariaDB server in CentOS
MariaDB Secure Setup: CentOS
Learn how to secure MariaDB instance in CentOS
Exercise: Install and Configure MySQL Database Server
SQL Functions and Statements
This is the main section. We will walk through examples covering the major SQL statemetns and functions, pointing out those that are standard SQL compliant as well as mentioning those that are known to be different across different RDBMS.
In this video we will learn how to insert records into our tables using SQL.
We have values in our tables now, how do we view their values? Watch this video and find out!
Whoops! We made some mistakes, lets learn how to alter our tables and add columns after its been created.
Now we need to remove some stuff without destroying all of our work.
Our database may start to suffer from slow downs. Before we go any further, lets talk about indexes.
Well, sometimes the only way to get rid of your mistakes is to start over. Let's drop our table or index and start again.
Hang on! Maybe we don't need to start completely over. Let's just dump the data and start with a clean slate.
When developing IDs that you can use for indexing or keys, let's be sure we don't have to keep track!
Let's start learning about functions in SQL - how to view and grab our data without making any direct changes.
Average and Sum Functions
Again, a function that tells us stuff about our data without storing or making any changes
Sometimes you don't know what your don't know, so let's figure out how to find information when we only know part of the story.
As much as we might like to live with functions, sometimes you access information enough that you just want to store it for later. Let's create a view into our data that we can treat like a table.
Learn about joins in general and the most common one, the Join (sometimes called Inner Join).
Left Outer Joins show records from the left table even if they do not have a matching relational record in the right.
Right Joins will show all records from the Right table even if there is no matching relational record in the left table.
A Full Join will show all records on both the left and right tables regardless of matching relational records in either.
A Union is the concatenation/combination of multiple select statements from one or more tables. Learn also how to use it to generate a full join in those DBs that do not support it directly.
Learn how to sort records in ascending or descending order depending on what and how you want to see the results. Use this to mimc certain SQL functions that are not supported universally.
Minimum and Maximum Values
Use built in SQL Functions to determine the maximum and minimum values from within our records sets.
Upper and Lower Case Conversions
Again using SQL functions to provide different views of our data without changing any of the underlying values or structures.
See how the Now() function works and how we can use it when creating data views to maintain historical change records for our products table.
Exercise: Create Database, Database User and Tables
Exercise: Creating Tables with Constraints
Exercise: Altering Table Structures
Exercise: Insert and Select Records in a Table
Exercise: Count Records and Execute Functions in a Table
Exercise: Use Joins to View Multi-Table Data
Exercise: Sort Records and Determine Min/Max Values
Exercise: SQL Functions (Average and Sum)