Learn advanced SQL, starting with SQL basics, data types, JSON/XML, and spatial data, and covering advanced techniques for dynamic and efficient database management.

Learning Advanced Concepts of SQL using MySQL - I.png

docker.tar.gz

MySQL Server

MySQL-Server-Code-Widget

MySQL-Server-SPA-Widget

MySQL-with-my-config-SPA-widget

MySQL Server (with Timezones)

MySQL-with-my-config-SPA-widget-copy

Dive into MySQL, a powerful database system for developers handling complex data structures. Learning MySQL equips you with skills for diverse applications, from web development to advanced data analysis, enhancing your ability to manage and manipulate data efficiently.

This course starts with MySQL Shell basics, leading to exploring data types and mastery of SQL statements. You will learn about user-defined variables and table expressions and delve into JSON and XML data handling. The course also covers spatial data management and advanced techniques like recursive queries and window functions. Each module builds upon the last, ensuring a comprehensive understanding of MySQL’s capabilities.

After completing this course, you will have a solid grasp of MySQL and be ready to solve intricate data problems. This expertise opens doors to advanced database roles and opportunities, elevating your career in the tech industry.

Mastering Advanced SQL Techniques with MySQL

MySQL provides capabilities for matching regular expressions against some text. For example, using `REGEXP_LIKE()`, we can match the regular expression `(ex|in)terior` against the names of car parts as in our running example:

-- Retrieve all exterior and interior car parts.
SELECT *
FROM CarPart
WHERE REGEXP_LIKE(name, '(ex|in)terior');

However, pattern matching only unfolds its full potential if applied to longer texts. One of the functions provided by MySQL, `REGEXP_SUBSTR()`, is particularly useful in this case. The function returns the $n^{th}$ match of a regular expression in the specified text. While this concept is powerful, it is limited by the strictness of the regular expression syntax. That is, we cannot perform actual fuzzy matching, i.e., searching for matches that are not exact but approximate. Furthermore, functions for regular expression matching do not provide a quality metric that would allow us to rank matches. This is useful for providing Google-like ordering of search results to users in an application. As for these shortcomings, MySQL provides natural language full-text search with support for boolean queries and query expansion.

Learn about methods to search full-text beyond pattern matching in MySQL.

Getting Started

Data Types

SQL Statements

Flow Control Functions

String Functions

Date and Time Functions

Aggregate Functions

Window Functions

Recursion

JSON and XML

Spatial Data

Mastering Advanced SQL Techniques with MySQL

Conclusion

Full-Text Search