First Look: MySQL 8 for Developers

2h 6mIntermediate2019-09-13

Authors

Ami Levin

Data Educator, Speaker, and Practitioner, SQL and RDM enthusiast

Course details

Looking to make the jump to MySQL 8? In this course, get a first look at all that MySQL 8 has to offer. Instructor Ami Levin highlights the new capabilities introduced in MySQL 8, showing how to leverage these enhancements to build robust, elegant, performant, scalable, and secure applications in less time and with drastically reduced costs. Learn about common table expressions (CTE) and how to use them to make your queries more concise. Explore window functions and how they impact query processing. Plus, learn how to work with lateral derived tables to solve real-world SQL challenges, leverage InnoDB enhancements, and work with features that make it easier to tighten security and boost performance.

Learning objectives
Aggregate, ranking, analytic, and offset window functions
Using common table expressions (CTE)
The benefits of lateral derived tables
InnoDB latest enhancements
Using security roles
Enhancements to password and encryption management
Improving query performance with new index types

Skills covered

MySQLDatabase DevelopmentDatabase ManagementOpen SourceSoftware DevelopmentOne-Off

Concepts

0. Introduction

01 - Better applications with MySQL 8
02 - What you should know

1. Unleashing the Power of Window Functions

03 - Windows and the OVER clause
04 - Window frames
05 - Rows vs. range
06 - Aggregate window functions
07 - Ranking window functions
08 - Offset window functions
09 - Analytic window functions
10 - Challenge - Window functions
11 - Solution - Window functions

2. Using Common Table Expressions (CTE)

12 - Derived tables, views, and common table expressions (CTEs)
13 - CTE demo
14 - Recursive CTE
15 - Challenge - Use CTEs
16 - Solution - Use CTEs

3. Understanding Lateral Derived Tables

17 - SQL joins
18 - Lateral derived tables
19 - Challenge - Use lateral derived tables
20 - Solution - Use lateral derived tables

4. InnoDB Enhancements

21 - Changes to AUTO INCREMENT
22 - Data dictionary and atomic DDL
23 - MySQL Document Store
24 - Structured vs. unstructured data

5. Tightening Security

25 - Using roles
26 - Password and encryption enhancements

6. Improving Performance

27 - Descending order indexes
28 - Invisible indexes
29 - Functional key indexes
30 - Resource management

Conclusion

31 - Next steps