Category: Misc

Partitioning in MySQL is used to split or partition the rows of a table into separate tables in different locations, but still, it is treated as a single table. It distributes the portions of the table’s data across a file system based on the rules we have set as our requirement. The rule that we…

A transaction in MySQL is a sequential group of statements, queries, or operations such as select, insert, update or delete to perform as a one single work unit that can be committed or rolled back. If the transaction makes multiple modifications into the database, two things happen: In other words, a transaction cannot be successful without…

MySQL cheat sheet provides a single page that contains all of the most commonly used commands and statements. This one-page cheat sheet helps us to work with MySQL database more effectively and quickly. MySQL is an open-source widely used RDBMS database that provides high performance and scalable web-based and embedded database applications for the customers. It has many useful…

UPSERT is one of the essential features of DBMS software for managing the database. This operation allows the DML users to insert a new record or update existing data into a table. An UPSERT is made up of a combination of two words named UPDATE and INSERT. The first two letters, i.e., UP stands for UPDATE while the SERT…

ON DELETE CASCADE clause in MySQL is used to automatically remove the matching records from the child table when we delete the rows from the parent table. It is a kind of referential action related to the foreign key. Suppose we have created two tables with a FOREIGN KEY in a foreign key relationship, making both tables a…

In MySQL, every statement or query produces a temporary result or relation. A common table expression or CTE is used to name those temporary results set that exist within the execution scope of that particular statement, such as CREATE, INSERT, SELECT, UPDATE, DELETE, etc. Some of the key point related to CTE are: MySQL CTE Syntax The syntax of MySQL CTE…

The LEAD and LAG is a window function in MySQL used to access the preceding and succeeding value of specified rows from the current row within its partition. These functions are the kind of non-aggregate function. The Window functions in MySQL are used to perform operations or calculations for each row within the partition or window. These functions produce the result…

A UUID is a Universal Unique Identifier specified by RFC 4122 (It is a Universally Unique Identifier URN Namespace) and 128-bit long value. It is designed in such a way that it generates a number which is unique globally according to space and time. If we call two UUID successively, we will get two different…

A derived table in MySQL is a virtual table that returned from the SELECT…FROM statement. In other words, it is an expression, which generates a table under the scope of the FROM clause in the SELECT statement. This concept is similar to the temporary table. However, the derived table is simpler than a temporary table because…

A subquery in MySQL is a query, which is nested into another SQL query and embedded with SELECT, INSERT, UPDATE or DELETE statement along with the various operators. We can also nest the subquery with another subquery. A subquery is known as the inner query, and the query that contains subquery is known as the outer query.…