MySQL Training: Stored Procedures and Triggers
In 2 days, write and optimize MySQL stored procedures and triggers, choose better alternatives when needed, and use advanced text, date and number functions to clean and process data.
Stored procedures, functions and triggers let you move business logic into the MySQL database, but poorly written code quickly becomes slow and hard to maintain. In 2 days, you review advanced query writing, then learn when to use stored programs, how to write and optimize them, and which alternatives are sometimes more efficient.
You also practise MySQL’s text, number and date functions, regular expressions and cursors to clean, transform and process data. The course ends with a case study that brings all these techniques together.
Updated on
The MySQL Training course at a glance
- Length
- 2 days
- Schedule
- 9:00 a.m. to 4:30 p.m.
- Format
- Live virtual class or in person in Montreal and Quebec City
- Level
- Intermediate
- Audience
- Developers, database administrators, data analysts and report writers who already write SQL queries and want to automate processing and improve performance in MySQL.
- Software
- MySQL 8 (8.0 or 8.4 LTS) and MySQL Workbench; the content also applies largely to MariaDB
- Price
- $919 per participant, or $781 for public bodies and non-profits (plus taxes)
- Language
- English
Doussou Formation and MySQL Training: key facts
- Since 2016Doussou Formation has been delivering professional training in Quebec
- 300+specialized courses in our catalogue
- 97 %satisfaction according to client evaluations
- RAP · CPMTAccredited supplier and recognized training organization
Upcoming MySQL Training sessions
MySQL Training course outline
Introduction and review of SQL query writing
- Overview of MySQL 8 and the course objectives
- Simple and complex queries: WHERE, GROUP BY, HAVING, ORDER BY
- Joins: INNER, LEFT, RIGHT, and emulating a FULL OUTER JOIN with UNION
- Subqueries, nested queries and common table expressions (WITH)
Stored procedures, functions and triggers
- What stored procedures and functions are, and when to use them
- Syntax: parameters (IN, OUT, INOUT), variables, conditions, error handling
- Triggers: BEFORE and AFTER on INSERT, UPDATE and DELETE; auditing and validation
- Advantages and limitations of stored programs
Optimization and alternatives to stored procedures
- Optimization techniques: indexing, reducing nested queries, managing transactions
- Reading an execution plan with EXPLAIN
- Alternatives: prepared statements, summary tables (MySQL has no native materialized views), scheduled events, application-level logic
- Choosing the right approach for each need
Best practices and data management in MySQL
- Table structure and efficient indexing
- Performance diagnostics and slow queries
- Query optimization: selecting only the needed columns, pagination, join techniques
- Text functions: CONCAT(), SUBSTRING(), REPLACE(), TRIM(), cleaning and formatting
- Number functions: SUM(), AVG(), ROUND(), MOD(), statistical and financial calculations
Advanced comparison and data manipulation functions
- Date functions: NOW(), CURDATE(), DATEDIFF(), TIMESTAMPDIFF()
- Combined text and numeric processing
- Regular expressions (REGEXP_LIKE(), REGEXP_REPLACE()) for advanced data cleaning
Cursors and looping techniques
- Cursors: definition, syntax and context of use
- Loops: LOOP, WHILE, REPEAT
- Practical examples: iterating through records and performing repetitive operations
- When a set-based query is the better choice
Conclusion and final case study
- Summary of key concepts
- Integrated case study combining stored procedures, triggers, optimization, advanced functions and cursors
Prices and practical information
Preferred rate
$781 / participant
Public bodies and non-profits · plus taxes
Standard price
$919 / participant
Businesses and individuals · plus taxes
Practical information
- Length: 2 days
- Schedule: 9:00 a.m. to 4:30 p.m.
- Format: Live virtual class or in person in Montreal and Quebec City
- Payment: online (PayPal or card) when registering, or by invoice for organizations
- Funding: Doussou Formation is a training organization accredited by the CPMT. For Quebec employers subject to the Act to promote workforce skills development (payroll over $2M), this course can count toward their 1% training obligation.
- Equipment: A computer on which MySQL and MySQL Workbench can be installed. Online: a computer with a good Internet connection, ideally with two screens.
Corporate MySQL Training training
Train your whole team as a private group, on your own documents, at your premises or in a virtual class, on the dates that suit you.
Participant reviews
97% satisfaction (end-of-course evaluations, 120 evaluations) · Read all testimonials
Frequently asked questions about the MySQL Training course
Do I need to know SQL?
Yes. You should already be able to write SELECT queries with filters and joins. The course starts with a review, then goes further.
Are triggers covered?
Yes. You learn how BEFORE and AFTER triggers work on INSERT, UPDATE and DELETE, typical uses such as auditing and data validation, and their limits on performance and maintenance.
Which version of MySQL is used?
MySQL 8 (8.0 or 8.4 LTS), with MySQL Workbench. Newer features such as common table expressions (WITH) and window functions are shown when they offer a simpler alternative to procedural code.
Does MySQL support materialized views?
Not natively. The course shows how to get a similar result with summary tables refreshed by a procedure, a trigger or a scheduled event.
Can we work on our own database?
In a private group, the exercises can be adapted to your own schema or to a case inspired by your organization.
Why take this MySQL Training course
Objectives: what you will be able to do
- write complex queries with joins, subqueries, grouping and sorting;
- create stored procedures, functions and triggers, and decide when they are the right tool;
- optimize stored programs with indexing, simpler queries and well-managed transactions;
- compare stored procedures with alternatives such as prepared statements, summary tables and application-level logic;
- use text, number and date functions and regular expressions to clean and format data;
- process records one by one with cursors and loops when a set-based query is not enough;
- diagnose slow queries and apply best practices for table structure and indexing.
Prerequisites
Working knowledge of SQL: SELECT queries, filters, joins and basic table creation. The first module provides a quick review.
What is included
- Course materials and exercise files for each participant
- Certificate of attendance
- Optional personalized coaching after the course
- In public in-person sessions:
- Tea and coffee
- Lunch at a nearby restaurant
- Parking (in some cities)
- Wi-Fi access
Concrete result
You leave with a library of stored procedures, functions, triggers and data-cleaning queries tested on a sample database, a checklist of optimization best practices, the course material and the exercise files.