In MySQL, we can use the REPLACE statement to insert data if we think that some rows need to replace existing rows.
The REPLACE statement works just like the INSERT statement, except that if it contains rows with the same PRIMARY KEY or UNIQUE index as those in the table, then it replaces those rows in the table. It does this by deleting the old row and then inserting the new row.
To use the REPLACE statement, we must have both the INSERT and DELETE privileges for the table.
MySQL 8.0.32 introduced the explain_format system variable, which can be used to provide a default output format when using the EXPLAIN statement, or its synonyms DESCRIBE and DESC.
More specifically, it determines the default output format used by this statement in the absence of a FORMAT option when displaying a query execution plan.
MySQL 8.3 introduced the explain_json_format_version system variable that allows us to specify which JSON output format should be used when we use the EXPLAIN statement with FORMAT=JSON.
It also applies when we omit the FORMAT argument and the explain_format system variable is set to JSON (because explain_format specifies the default format to be used by EXPLAIN).
In MySQL we can use the EXPLAIN statement to get information about SQL queries that we run against the database. We can also get information about tables and views.
EXPLAIN is a synonym for DESCRIBE (and its short form DESC). Even though these statements all work exactly the same, there’s a common convention amongst MySQL developers to use DESCRIBE for certain tasks and EXPLAIN for others. Typically, DESCRIBE is often used to get information about tables and views, whereas EXPLAIN is used to get information about queries, such as query execution plans.
Most of the major RDBMSs provide us with the ability to perform set operations.
A set operation is one that combines the results of multiple query blocks into one. For example, we could combine the result of two separate SELECT statements. The results are displayed in a single result set as though we’d run a single query.
In MySQL we can use the DESCRIBE statement to get information about table structure in the database, or to obtain a query execution plan for a query. For example, we can get information about column names, their data types, default values, etc.
The DESC and EXPLAIN statements are synonyms of DESCRIBE, so we can alternatively use those to do the same thing.
In MySQL we can use the SHOW COLUMNS statement to get information about the columns in a table or view.
We can narrow the columns down to just a certain table and database. We can also filter by column name using a pattern.
The SHOW COLUMNS statement only shows information about columns that we have some privilege.
In MySQL 8.0.23 and later, we have the ability to create invisible columns when creating or altering a table.
An invisible column is one that is hidden to certain SQL queries, but can be accessed if explicitly referenced.
Basically, invisible columns are hidden to queries that use the asterisk wildcard (*) in their SELECT list. But they can be accessed by explicitly referencing the column by its name.
Invisible tables are hidden from the TABLE statement, as this statement provides no way of selecting individual columns.
When combining multiple query blocks in MySQL, we can use the EXCEPT clause to return just those rows from the first block that are not present in the second block.
When combining multiple query blocks in MySQL, we can use the INTERSECT clause to return just those rows that are common to both query blocks.
It’s a bit like the UNION clause, except that it excludes rows that aren’t present in both queries.