MySQL for Developers

Overview Learning objectives What you’ll learn Key facts Module listing FAQs What our customers say Get in touch

Course overview

Build stronger MySQL development capability by learning how to write, assess and refine SQL for demanding application environments. The course covers advanced query techniques, database objects, performance considerations and data movement, with practical exercises that help you apply the learning to day-to-day development work. It also helps you prepare for the official MySQL Developer certification and gives you the confidence to produce efficient, reliable database solutions.

Advanced capability for MySQL developers

This five-day course is for developers who already work with MySQL and want to strengthen how they build, manage and troubleshoot database-driven applications. You’ll develop a deeper understanding of SQL structures, stored logic, data handling, execution behaviour and database performance, with exercises that reflect the demands of real development work. The course is suited to experienced developers looking to move beyond core MySQL use and take on more complex responsibilities across application data, reliability and maintainability.

Learning objectives

Write more structured SQL
Build stored routines
Use triggers and functions
Apply Common Table Expressions
Work with window functions
Manage import and export tasks
Review execution behaviour
Strengthen database maintainability

What you’ll learn

Build the MySQL development skills you need to create clearer SQL, more maintainable database logic and better-performing data processes over this five-day live virtual classroom course. You’ll work across querying, stored routines, functions, triggers, import and export tasks, integration points and execution behaviour, helping you make stronger technical decisions when building or supporting database-driven applications.

SQL structure and control

Develop a stronger command of SQL expressions, functions, ordering, grouping and subqueries, equipping you with the skills that help retrieve and shape data with greater precision. As application features become more data-dependent, these techniques help you produce accurate outputs, predictable logic and queries that other developers can understand, maintain and adapt.

Reusable database logic

Learn how stored routines, procedures, functions, parameters, cursors, triggers and exception handling can support cleaner database development. When repeated rules or data processes are managed consistently inside MySQL, teams can reduce duplication across application code, improve control over business logic and create database components that are easier to test, review and maintain as applications evolve.

Data movement and integration

Work with import and export tasks, command-line utilities, MySQL connectors and integration options. These skills help you move data more confidently between systems, support development workflows and understand how MySQL fits into wider application architectures that may include Java, PHP, NoSQL technologies or external services.

Execution and maintainability

Review how MySQL handles query execution, indexes, joins, storage engines and database structure. You’ll build the judgement needed to improve database behaviour without making changes blindly, helping you support applications that need to remain responsive, reliable and easier to manage as data volumes and usage grow.

What’s included

Instructor-led training over two-days
Live virtual classroom environment
Hands-on exercises and live labs
All relevant course materials

Key facts

Certification

This course supports preparation for MySQL Developer certification and covers 1Z0-909 exam topics.

Who it’s for

This course is for developers and technical professionals with MySQL and SQL experience who want to build advanced database applications.

Prerequisites

This course requires experience using MySQL, including writing queries and basic database management tasks.

Exam information

The course covers 1Z0-909 exam topics, but exam is not included.

Optional extras

This course includes hands-on labs and access to learning materials for continued practice.

Pre-course

No preparation work is required for this course.

Full module listing

Click on each day below to see the breakdown of the modules that you'll cover on the course.

Day one

Day two

Day three

Day four

Day five

Day one

Session 1: CLIENT/SERVER CONCEPTS

MySQL client/server architecture
Server modes
Using client programs
Logging in options
Configuration files
Precedence of logging in options
Exercises: Using client/server

Session 2: THE MYSQL CLIENT PROGRAM

Using MySQL interactively
The MySQL prompts
Client commands and SQL statements
Editing
Selecting a database
Help
Safe updates
Using script files
Using a source file
Redirecting output into a file
Command line execution
Mysql output formats
Overriding the defaults
Html and xml output
MySQL Utilities
Exercises: Using the MySQL client program

Session 3: DATA TYPES

Bit data type
Numeric data types
Auto_increment
Character string data types
Character sets and collation
Binary string data types
Enum and Set data types
Temporal data types
Timezone support
Spatial Datatypes
Handling Missing Or Invalid Data Values
SQL_MODE options
Exercises: Using data types

Session 4: IDENTIFIERS

Using Quotes with identifier naming
Case sensitivity in Identifier naming
Qualifying columns with table and database names
Using reserved words as identifiers
Function names
Exercises: Using identifiers

Session 5: QUERYING FOR DATA

The SQL SELECT statement and MySQL differences
Advanced order by
ORDER BY and collation
ORDER BYy with enum datatype
ORDER BY with Set datatype
Ordering with DISTINCT and GROUP BY
Special features of union
LIMIT and order by clauses
GROUP BY clause
Group_concat
Using ROLLUP in a GROUP BY clause
Exercises: Querying for data

Day two

Session 6: SQL EXPRESSIONS AND FUNCTIONS

Components of expressions
Nulls
Numeric expressions
String expressions
Temporal expressions
Comparison functions
Flow control functions
Numeric functions
String functions
Temporal functions
Exercises: Using expressions and functions

Session 7: UPDATING DATA

Update operations and privileges
Inserting rows
Insert using a set clause
Inserting duplicate values
Replacing rows
Updating rows
Update using the order by and limit clauses
Deleting rows
The delete and truncate statements
Exercise: Inserting, updating, replacing and deleting data

Session 8: CONNECTORS

MySQL client interfaces
MySQL connectors
Oracle and community conectors
Connecting to MySQL server using Java and PHP connectors
MySQL and NoSQL
Innodb integration with memcached

Session 9: OBTAINING DATABASE METADATA

What is metadata?
Using mysqlshow utility with MariaDB
The show and describe commands
Describing tables
The information_schema
Listing tables
Listing columns
Listing views
Listing key_columns_usage
Exercises: Obtaining database metadata

Session 10: DEBUGGING

MySQL error messages
The show statement
Show errors
Show count(*) errors
Show warnings
Show count(*) warnings
Note messages
The perror utility
Exercises: Debugging

Day three

Session 11: IMPORT AND EXPORT

Exporting using SQL
Privileges required to export data
Importing using SQL
Messages when loading data
Privileges required to load data
Exporting from the command line
Mysqldump main options
Importing from the command line
Mysqlimport main options
Exercises: Importing and exporting

Session 12: SUBQUERIES

Types of subquery
Multiple-column subqueries
Correlated subqueries
Using the ANY, ALL and SOME operators
Using the EXISTS operator
Subqueries as scalar expressions
Derived Table
WITH Clause ( Common Table Expression - CTE )
Recursive CTEs
Using subqueries in updates and deletes
Exercises: Coding subqueriess

Session 13: VIEWS

Why views are used
Creating views
View creation restrictions
View algorithms
Updateable views
Altering and dropping views
Displaying information about views
Privileges for views
Exercises: Using views

Session 14: USING WINDOW FUNCTIONS

Description
Non-aggregate window functions
Using RANK, DENSE_RANK, ROW_NUMBER, NTILE
Window partition clause
Using LAG. LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE
Aggregate window functions - SUM, AVG etc
Window frame clause
Exercises: Using Window Functions

Session 15: REGULAR EXPRESSION SUPPORT

Overview of Regular Expression
Regular Expression Notation
The REGEXP_LIKE operator
The REGEXP_INSTR function
The REGEXP_SUBSTR function
The REGEXP_INSTR function
The REGEXP_REPLACE function
Exercises: Regular Expression Support

Day four

Session 16: USER VARIABLES AND PREPARED STATEMENTS

Creating User variables
User variables in a select
Prepared statements
The prepare statement
The execute statement
The deallocate statement
Using prepared statements in code, with connectors
Exercises: Using variables and prepared statements

Session 17: INTRODUCTION TO STORED ROUTINES

Types of stored routines
Benefits of stored routines
Stored routine features
Differences between procedures and functions
Introduction to the Block
Declaring variables and constants
Assigning values to variables
Definer rights and invoker rights
Using SELECT in stored routines
Altering and dropping stored routines
Obtaining stored routine metadata
Stored routine privileges and execution security
Exercises: Writing simple stored routines

Session 18: STORED ROUTINES - PROGRAM LOGIC

The IF .. THEN .. ELSEIF construct
The CASE statement
The basic loop
The while loop
The repeat loop
The iterate statement
Nested loops
Exercises: Writing stored routines with program logic

Session 19: PROCEDURES WITH PARAMETERS

Creating procedures with parameters
Calling Procedures WITH Parameters
Exercises: Writing stored routines with parameters

Day five

Session 20: STORED ROUTINES - EXCEPTION HANDLERS & CURSORS

Dealing with errors using Exception handlers
Cursors
What is a cursor?
Cursor operations
Declaring cursors
Opening and closing cursors
Fetching rows
Status checking
Exercises: Writing stored routines with program logic

Session 21: FUNCTIONS

What is a function?
The create function statement
Executing functions
Executing functions from code
Executing functions from SQL statements
The deterministic and SQL clauses
Exercises: Writing functions

Session 22: TRIGGERS

Trigger creation
Restrictions on triggers
The create trigger statement
Using the old and new qualifiers
Managing triggers
Destroying triggers
Required privileges
Exercises: Writing triggers

Session 23: BASIC OPTIMIZATIONS

Normalisation of data to third normal form
Using indexes for optimization
General query enhancement
Using Explain to analyze queries
Choosing an INNODB or MYISAM storage engine
Using MySQL Enterprise Monitor in query optimization
Exercises: Making use of basic optimizations

Session 24: MORE ABOUT INDEXES

Indexes and joins
Exercises: Investigating indexes and joins

FAQs

This five-day course helps experienced MySQL users strengthen the way they build and support database-driven applications. It covers SQL structures, stored routines, functions, triggers, data movement, connectors and execution behaviour, helping developers make better technical decisions when working with application data.

Is this course suitable for new MySQL users?

No. This course is designed for people who already have basic SQL knowledge and some hands-on experience using MySQL. You should be comfortable writing core SQL statements before joining, as the course moves into more detailed development topics such as subqueries, stored routines, functions, triggers and database behaviour.

What MySQL development skills will I build?

You’ll build skills across SQL expressions, data types, subqueries, Common Table Expressions, window functions, stored routines, procedures, functions and triggers. These areas help you work with more complex database requirements, structure logic more clearly and support applications where data accuracy, maintainability and reliability matter.

Will the course help me write better SQL?

Yes. The course explores how to shape, retrieve and manage data using SQL expressions, functions, grouping, ordering, subqueries and CTEs. This gives you more options when handling complex application requirements, helping you produce SQL that is clearer, more controlled and easier for development teams to maintain.

Does the course cover reusable database logic?

Yes. You’ll learn how stored routines, procedures, functions, parameters, cursors, triggers and exception handling can be used in MySQL. These techniques help you manage repeated rules or data processes more consistently, reducing duplication across application code and supporting cleaner, more maintainable database development.

Will I learn about data movement and integration?

Yes. The course includes import and export tasks, MySQL client interfaces, connectors, Java and PHP connectivity, and MySQL and NoSQL integration. This helps you understand how MySQL fits into wider application architectures and how data can move between systems, tools and development workflows.

Does the course support certification preparation?

Yes, the course supports preparation for MySQL Developer certification and includes coverage of 1Z0-909 exam topics.

What our customers say

‘Good structure to course. Each technical aspect broken down by instructor and theory explained with practical examples, which aided understanding and provided guidance on best practice when applying in real life scenarios. Pace was good - fast enough to keep engaged, and not too overwhelming.’

Ross, Government

'The lecturer was amazing - so passionate and knowledgeable, which made the sessions enjoyable and easy to follow. I learned a lot and felt supported throughout. I would definitely consider returning for future sessions and highly recommend TSG training to my colleagues.’

Kamila, Data Administrator - Retail / Hire

Why study with TSG?

25+ years' experience

Trusted by individuals, businesses and public sector organisations for over 25 years.

20,000+ learners trained

Join a network of learners focused on developing their software testing skills

98% customer satisfaction

TSG trainers are frequently rated as ‘good’ or ‘excellent’ by our learners

Similar courses

MySQL

Course

Introduction to MySQL

£1,255

From £941

MySQL

Course

MySQL Database Administration

£2,365

From £1,774

MySQL

Course

MySQL Database Administration

£2,365

From £1,774

MySQL

Course

Introduction to MySQL

£1,255

From £941

PHP

Course

PHP Developer

£1,780

From £1,335

Python

Course

Python Programming 1

£2,090

From £1,568