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 data types with five categories of data: numeric, date and time, string, spatial, and JSON. Out of the eight distinct data types for strings, `TEXT` is one that we already use in our running example for representing the names of parts for car models:

-- Generate a table for sample car model parts.
DROP TABLE IF EXISTS CarPart;
CREATE TABLE CarPart
(
    model    INT,
    name     TEXT,
    built_at DATE DEFAULT NULL,
    PRIMARY KEY (model, name(256)),
    FOREIGN KEY (model) REFERENCES CarModel (id)
);

-- Inspect the properties of the table for car parts.
DESCRIBE CarPart;

As we can observe, `CarPart` has three columns, `model`, `name`, and `built_at`, of which the second represents the name of a car part using the data type `TEXT`. If we filter for specific car parts, e.g., `'exterior'`, it might feel intuitive to use the comparison operator `=` on the column `name`:

-- Retrieve all exterior car parts.
SELECT *
FROM CarPart
WHERE name = 'exterior';

While the comparison operator `=` is sufficient in this case, matching strings based on criteria other than equality is *not* possible with this operator. Referring to other programming languages like JavaScript or Python, we know that the comparison operator `=` evaluates its operands based on strict equality. Likewise, in (My)SQL, both strings subject to the comparison operator `=` have to be identical:

-- Inspecting different use cases of the string comparison operator `=`.
SELECT 'bar' = 'bar'  AS `Equality`,
       'bar' = 'bar ' AS `Trailing Spaces`,
       'bar' = 'Bar'  AS `Case Sensitivity`,
       'bar' = 'barr' AS `Inequality`;

Despite a comparison for equality, the `=` operator allows for two exceptions on MySQL by default, namely trailing spaces and case sensitivity. A string comparison using `=` yields different results depending on the character set used. On MySQL, the default character set disregards trailing spaces such that the comparison `'bar' = 'bar '` yields `1` (i.e., a truthy value) in the above example. For the second exception, case sensitivity, the `=` operator compares its operands without regard to their case. To respect the case during comparison, use the `BINARY` attribute as in `'bar' = BINARY 'Bar'`.

Learn about pattern matching and comparison of strings 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

Introduction to String Functions