MySQL® is the open source community's most popular Relational Database Management System offering and serves as a key component of the LAMP stack (Linux, Apache, MySQL, PHP, Perl, Python). Many Fortune 500 companies rely on MySQL to benefit from its open source flexibility, platform independence, and ability to significantly reduce database Total Cost of Ownership by up to 90 percent. This comprehensive course encourages you to explore database fundamentals and discover MySQL's extensive feature set. You will learn the basics of MySQL usage and master the programming of stored routines, with this course covering MySQL 8.0 in depth.
Target Audience: Application developers and web developers who want to build database-driven applications.
Prerequisites: Prior experience installing software and programming in any language, such as HTML, is recommended but not required.
Course Outline:
Introduction to Relational Databases and SQL
- Introduction to client and server systems architecture
- The relational database model and how it organizes data
- SQL and SQL-based database management systems
- Core SQL statements and their functions
- Using SQL from within application programs
Using MySQL Workbench and Development Tools
- Introduction to the MySQL Workbench environment
- Running SQL statements through MySQL Workbench
- Navigating the MySQL Reference Manual
- Using the MySQL Command Line Client for direct queries
Retrieving Data from a Single Table
- Introduction to the SELECT statement and data retrieval
- Coding the SELECT clause for column specification
- Coding the WHERE clause for row filtering
- Coding the ORDER BY clause for sorting results
- Coding the LIMIT clause to restrict result sets
Retrieving Data from Multiple Tables
- Working with inner joins to combine matching records
- Working with outer joins to include non-matching records
- Advanced join techniques and considerations
- Using unions to combine results from multiple queries
Inserting, Updating, and Deleting Data
- Creating test tables for development and practice
- Inserting new data rows into tables
- Updating existing row values
- Deleting rows from tables
Coding Summary Queries
- Working with aggregate functions for data summarization
- Grouping and summarizing data by categories
- Coding aggregate window functions for advanced analysis
Working with Subqueries
- Introduction to subqueries and their applications
- Coding subqueries in the WHERE clause
- Using the ALL keyword with subqueries
- Coding subqueries in other clauses
- Working with complex queries and nested subqueries
- Using common table expressions for query organization
Working with Data Types
- Overview of available MySQL data types
- Converting between different data types
Using Functions in Queries
- Working with string data and string functions
- Working with numeric data and mathematical functions
- Working with date and time data and temporal functions
- Other important functions you should know about
Creating Databases, Tables, and Indexes
- Working with databases and creating new schemas
- Working with tables and table structures
- Creating and managing indexes for performance
- Using MySQL Workbench for database design
- Working with character sets and collation options
- Working with storage engines for different use cases
Creating Views for Data Abstraction
- Introduction to database views and their benefits
- Creating and managing views
Language Skills for Stored Programs
- Introduction to stored programs and procedures
- Writing procedural code for advanced functionality
Using Transactions and Locking
- Working with transactions for data consistency
- Working with concurrency and locking mechanisms
Creating and Using Stored Procedures and Functions
- Coding stored procedures for encapsulated logic
- Coding stored functions for reusable calculations
- Using Workbench with procedures and functions
