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.
MySQL for Developers
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
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.
Course syllabus
Dive into the detail of the course by looking at the syllabus below.
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
Day one
- 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
Day one
- 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
Day one
- 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
Day one
- Session 5: Querying for data
- The SQL SELECT statement and MySQL differences
- Advanced order by
- ORDER BY and collation
- ORDER BY 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
Day two
- 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
Day two
- Session 8: Connectors
- MySQL client interfaces
- MySQL connectors
- Oracle and community connectors
- Connecting to MySQL server using Java and PHP connectors
- MySQL and NoSQL
- InnoDB integration with memcached
Day two
- 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
Day two
- 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
Day three
- 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 subqueries
Day three
- 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
Day three
- 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, including SUM and AVG
- Window frame clause
- Exercises: Using window functions
Day three
- 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_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
Day four
- 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
Day four
- 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
Day four
- 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 and 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
Day five
- 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
Day five
- 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
Day five
- Session 23: Basic optimisations
- Normalisation of data to third normal form
- Using indexes for optimisation
- General query enhancement
- Using EXPLAIN to analyse queries
- Choosing an InnoDB or MyISAM storage engine
- Using MySQL Enterprise Monitor in query optimisation
- Exercises: Making use of basic optimisations
Day five
- 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