Mastering Advanced SQL Techniques with MySQL/

...

/

Prepared Statements

Prepared Statements

Build on the fundamentals of user-defined variables by learning about prepared statements.

We'll cover the following...

With our running example, we have put into practice our knowledge of user-defined variables in MySQL and how they support us in accessing repeatedly used values. Meanwhile, we have iteratively built a database that records car models and the assembly of their parts:

MySQL
-- Generate a table for sample car models.
DROP TABLE IF EXISTS CarModel;
CREATE TABLE CarModel
(
    id           INT auto_increment,
    manufacturer TEXT DEFAULT NULL,
    name         TEXT DEFAULT NULL,
    `power (kW)` INT  DEFAULT NULL,
    PRIMARY KEY (id)
);
-- 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)
);

Recording car models in CarModel or their assembled parts in CarPart requires us to repeatedly use the same SQL statements over time, e.g., INSERT INTO. However, only the values used within these statements vary like the different names of car models or their parts:

MySQL
-- Register the assembly date for car parts over time.
UPDATE CarPart SET built_at = '2022-03-29' WHERE model = 4 and built_at IS NULL;
UPDATE CarPart SET built_at = '2022-03-29' WHERE model = 6 and built_at IS NULL;
UPDATE CarPart SET built_at = '2022-03-30' WHERE model = 7 and built_at IS NULL;
UPDATE CarPart SET built_at = '2022-03-31' WHERE model = 8 and built_at IS NULL;

With our knowledge of other programming languages like C++, Python, or JavaScript, we already have a feeling about how we should proceed in a situation like this. Typically, we would extract duplicated code into functions or ...