Course: MySQL DML - Querying Data
LESSON 1 - SELECT Basics
This video covers very basic MySQL SELECT syntax. In this lesson you will learn the syntax for a simple SELECT query. We will be using the MySQL Workbench and learning features of this software as part of the course.
This is part two of the first lesson of the course "MySQL Foundations - DML - Querying Data." This lesson concludes by covering syntax errors and how to create alias names for your columns.
LESSON 2 - Intro to Data Analysis
This lesson covers some basic tips and tricks in analyzing data. I also offer up some advice to keep you out of trouble if you are working on a system with large volumes of data.
LESSON 3 - Row Restriction (WHERE clause)
As we begin learning about row restriction using the WHERE clause I introduce you to querying numeric data using the greater than and less than operators. You will also be introduced to creating compound row restriction using the AND operator.
This part of the lesson on row restriction addresses how to deal with character data types. Here I introduce you to the UPPER() and LOWER() functions and to the LIKE operator.
This lesson covers how to deal with NULL values in the WHERE clause (row restriction).
Here I show you how to row restrict based upon the date portion of a datetime data type. You are introduced to the DATE() function.
I finish up this segment by showing you how to row restrict on the time facet of the datetime data type. Use of the TIME() function is introduced. Also some of the quirks with working with MySQL datetime are discussed.
I conclude the lesson on row restriction by introducing the OR operator (IN is also covered). I delve into the importance of using parenthesis and cover some of the nuances and gotcha's associated with OR.
LESSON 4 - Query Efficiency/Performance
In this lesson you will learn how to analyze explain plans to determine if your query is efficient.
LESSON 5 - Functions
In this lesson you will learn how to use numeric functions.
In this lesson you will learn how to use string functions. TRIM(), CONCAT(), SUBSTRING(), LENGTH(), LOWER(), UPPER()
In this lesson you will learn how to use datetime functions.
Date(), Monthname(), Dayname(), Month(), Date(), Now(), Year(), Adddate(), Datediff(), Time(), Hour(), Minute(), Second(), Addtime()
LESSON 6 - CASE Statements
In this lesson you will be introduced to using case logic in MySQL (SQL's version of conditional logic). CASE statements can have a variety of uses.
LESSON 7 - Joining Tables
In this first segment I introduce you to joining and left joining tables in MySQL. I do this from the perspective of the data to better reinforce the mechanics of the syntax.
This lessons continues on by covering table cardinality and showing how to use E-R diagrams as a means of joining MySQL tables.
In this lesson we dive into more complex ER diagrams and setup some scenarios for join multiple tables together in a query.
Learn how to join multiple MySQL tables in a query.
In this lesson you learn how to resolve multiple foreign keys back to a single table.
This lesson addresses why for performance reasons that you should not left join all your tables.
In this lesson we explore one to many table relationships.
This lesson cover many to many relationships and how they are accomplished through the use of associative tables
This lesson covers explain plans as they apply to queries that join tables.
LESSON 8 - Summarizing Data
This lesson covers the use of GROUP BY in order to summarize and aggregate data.
This lesson covers use of some common data aggregation functions.
Using HAVING to row restrict data that has been rolled up or aggregated.