MySQL for Developer
MySQL for Developer is a course in using the current MySQL release as a developer, from database design and queries with joins, CTEs, window functions and JSON to making queries faster with indexes and writing stored procedures, triggers and transactions. It suits web and app developers, junior programmers and students who build systems on MySQL.
- Duration18 hours · 3 days
- FormatOnsite / live online
- Next roundOn request
- CertificateIncluded
Course overview
MySQL is one of the databases web and app developers use most, yet many systems still write queries the old way: pulling data into code to loop over it, building SQL by concatenating strings and so risking SQL injection, or relying on deprecated features. As data grows the system slows down and becomes hard to maintain, even though current MySQL offers CTEs, window functions, JSON and query analysis tools that let this work happen in the database itself.
This course takes developers through MySQL 9.7 LTS: designing databases, choosing data types and constraints, writing queries with functions, CASE, joins and UNION, summarising with GROUP BY, writing subqueries, CTEs and window functions, working with JSON, and using views and indexes with EXPLAIN. It then moves into programming in the database with transactions, stored procedures, parameters, loops, triggers, events and error handling, and finishes by connecting from an application safely.
What you’ll gain
- Design databases, choose data types and define constraints that suit the application
- Write queries fluently with functions, CASE, joins, UNION and aggregation
- Solve real problems with subqueries, CTEs, window functions and JSON
- Read EXPLAIN output and design indexes that make queries faster
- Write stored procedures, functions, triggers and events with error handling
- Use transactions and connect to the database from applications safely
Who this course is for
- Web and application developers who use MySQL as their main database
- Junior programmers who want to write SQL beyond the basic statements
- Students building serious projects on MySQL
- IT teams, teachers and lecturers who maintain or teach database development
Prerequisites
- Able to write basic SQL such as SELECT, INSERT, UPDATE and DELETE
- Some experience of building websites or apps on MySQL, for example in PHP or Node.js (basic guidance is given if not)
- Able to install and uninstall software and use the internet
- A laptop that can run Docker Desktop or MySQL, with permission to install software
Curriculum
Course Details
This course is the 2026 edition of MySQL for Developer, rewritten for MySQL 9.7 LTS as the companion to the refreshed MySQL Administrator course. It runs for 3 days, 6 hours per day (18 hours in total, 09:00-16:00), as lectures with labs on a sample online store database. Intermediate level. Day one covers database design, constraints and multi-table queries. Day two covers aggregation, CTEs, window functions, JSON, indexes and EXPLAIN. Day three covers transactions, stored procedures, functions, triggers and events, and connecting from applications safely. The course focuses on developer work; installation, backup and server administration are covered in MySQL Administrator. Learners take home the SQL scripts for every lab and the Docker files.
Day 1 Database Design and Multi-Table Queries
Section 1: Lab: Setting Up MySQL 9.7 LTS
- LTS and Innovation releases, and which to develop against in 2026
- Running MySQL in Docker and connecting with Workbench, VS Code and MySQL Shell
- The utf8mb4 character set and collations for Thai data
- Strict sql_mode and its effect on stored data
- Lab: create the sample online store database in Docker
Section 2: Database Design for Developers
- ER diagrams and normalisation to the level used in practice
- Choosing data types: INT, DECIMAL, VARCHAR, DATETIME versus TIMESTAMP, and JSON
- AUTO_INCREMENT primary keys versus UUIDs with UUID_TO_BIN
- Generated columns and when to denormalise
- Lab: design the customer, product, order and order item tables in Workbench
Section 3: Lab: Constraints and Data Integrity
- Primary keys, UNIQUE, NOT NULL and DEFAULT
- Foreign keys with ON DELETE and ON UPDATE
- CHECK constraints for business rules
- INSERT ... ON DUPLICATE KEY UPDATE with a row alias instead of the deprecated VALUES()
- Lab: enforce price and stock rules with constraints and test them with bad data
Section 4: Lab: Columns, Expressions, Scalar Functions and CASE
- Commonly used string, numeric and date functions
- CAST, CONVERT and formatting dates with DATE_FORMAT
- Simple and searched CASE expressions
- COALESCE, IFNULL and handling NULL values
- Lab: an order report showing delivery status and order age
Section 5: Lab: Merging Data with Joins and Unions
- INNER, LEFT, RIGHT and CROSS JOIN
- SELF JOIN and joining many tables
- UNION versus UNION ALL
- INTERSECT and EXCEPT for comparing two data sets
- Lab: merge customer data from two systems and find customers who have never ordered
Day 2 Aggregation, Modern SQL and Performance
Section 6: Lab: Aggregating Data
- COUNT, SUM, AVG, MIN and MAX with GROUP BY
- HAVING and the order in which a query is processed
- WITH ROLLUP and the GROUPING function
- GROUP_CONCAT, and paging with LIMIT plus COUNT instead of SQL_CALC_FOUND_ROWS
- Lab: monthly sales by product category with subtotals
Section 7: Lab: Subqueries and Common Table Expressions
- Subqueries in SELECT, FROM and WHERE
- Correlated subqueries, EXISTS and NOT EXISTS
- CTEs with WITH for breaking a query into steps
- Recursive CTEs for hierarchical data
- Lab: multi-level product categories and customers who buy again within 30 days
Section 8: Lab: Window Functions
- OVER, PARTITION BY and ORDER BY
- ROW_NUMBER, RANK and DENSE_RANK
- Running totals and moving averages
- LAG and LEAD for comparing with the previous period
- Lab: the top 5 products per category and growth against the previous month
Section 9: Lab: Working with JSON
- The JSON data type and validation
- Reading values with -> and ->> and JSON_EXTRACT
- Building and changing JSON with JSON_OBJECT, JSON_ARRAYAGG and JSON_SET
- Turning JSON into rows with JSON_TABLE, and multi-valued indexes
- Lab: store flexible product attributes and search them as JSON
Section 10: Lab: Views and Indexes for Developers
- Creating views to avoid repeating queries and to limit what data is exposed
- Composite, covering and functional indexes
- Reading execution plans with EXPLAIN and EXPLAIN ANALYZE
- Queries that stop indexes being used, and how to fix them
- Lab: speed up product search and sales report queries, then measure the result
Day 3 Transactions and Programming in the Database
Section 11: Lab: Transactions and Locking
- ACID, START TRANSACTION, COMMIT and ROLLBACK
- InnoDB isolation levels and their visible effects
- SELECT ... FOR UPDATE, NOWAIT and SKIP LOCKED
- How deadlocks happen and writing code that retries safely
- Lab: reduce stock for many concurrent orders without overselling
Section 12: Lab: Stored Procedures and Parameters
- Creating stored procedures and calling them with CALL
- IN, OUT and INOUT parameters
- Variables with DECLARE and SET
- DEFINER versus INVOKER security
- Lab: a procedure that creates an order and returns its number and total
Section 13: Lab: Loops, Cursors and Stored Functions
- IF, CASE and LOOP, WHILE and REPEAT loops
- Cursors, and when to use set-based statements instead
- Stored functions and the DETERMINISTIC characteristic
- An overview of JavaScript stored programs in Enterprise Edition
- Lab: a shipping cost function and a procedure that adjusts prices by rule
Section 14: Lab: Triggers, Events and Error Handling
- BEFORE and AFTER triggers with OLD and NEW values
- The Event Scheduler for timed jobs
- DECLARE HANDLER for different kinds of exception
- SIGNAL, RESIGNAL and GET DIAGNOSTICS
- Lab: a price history trigger, an event that clears abandoned carts and a procedure that rolls back on error
Section 15: Workshop: Connecting from Applications Safely
- Prepared statements in PHP PDO or Node.js mysql2 to prevent SQL injection
- Connection pools and calling stored procedures from code
- Efficient paging and multi-criteria search
- Workshop: complete an ordering system on the database designed in class
- Presenting the result and reviewing the queries together
Schedule & training options
For individuals — public rounds
No public rounds are open right now. Join the waiting list and we will contact you first when the next round opens, or ask us on LINE. Or call 02-570-8449 or 088-807-9770
For organisations — in-house / private
- Tailor the content to your team’s tools and projects
- Your dates, at your office or live online
- Quotation with tax ID for procurement
Instructors
Frequently asked questions
What is the difference between JOIN and UNION in SQL?
JOIN combines tables side by side by matching related rows, such as the customer ID in an orders table and a customers table, giving you columns from both. UNION stacks the results of several SELECT statements on top of each other, and each must return the same number of compatible columns. UNION removes duplicate rows, while UNION ALL keeps them and runs faster. Current MySQL also offers INTERSECT and EXCEPT for finding rows that two sets share or do not share.
What is a stored procedure in MySQL and how do you create one?
A stored procedure is a named set of SQL statements saved in the database and run with CALL, which reduces repeated code and round trips between the application and the database. To create one, temporarily change the DELIMITER, write CREATE PROCEDURE with a name and IN, OUT or INOUT parameters, put the statements between BEGIN and END, then restore the delimiter. Add a DECLARE HANDLER and a transaction when the procedure changes several tables.
What are triggers in MySQL used for?
A trigger is code the database runs automatically when an INSERT, UPDATE or DELETE happens on a given table, either BEFORE or AFTER the event. Typical uses are checking or adjusting values before saving, writing change history to a log table or updating stock when an order is placed, using OLD and NEW to refer to the values. Triggers reduce application code, but too many make problems hard to trace.
Who is the MySQL for Developer course for, and what should I know beforehand?
It suits web and app developers who use MySQL, junior programmers, students building projects on MySQL and IT teams who look after database systems. You should be able to write basic SQL such as SELECT, INSERT, UPDATE and DELETE and have some experience of building websites or apps on MySQL; basic guidance is given if not. You also need a laptop that can run Docker or MySQL.
What will I be able to do after the MySQL for Developer course?
You will be able to design databases with constraints, write queries with joins, UNION, GROUP BY, subqueries, CTEs and window functions, work with JSON data, read EXPLAIN output and design indexes that speed up queries, write stored procedures, functions, triggers and events with error handling, use transactions to protect data, and connect from applications with prepared statements. Participants receive a Certificate of Completion.
Can the MySQL for Developer course be run in-house for our team?
Yes. It can be run in-house for your team, with examples adapted to your own work, such as practising on database structures similar to your systems or reviewing the team's slow queries. You can request a quotation from this page, or contact us on LINE @itgenius or by phone on 02-570-8449.
Related articles
Articles are published in Thai.