Mysql (2)

I want to cover some other features of MySQL. Most of these are not localized within MySQL, but are written in MySQL grammar. I will try to compare the MySQL grammar with PostgreSQL one later on.

EXISTS

EXISTS is a command which serves the purpose of checking if there are at least one record which satisfy the given condition.

EXISTS should be used like this:

SELECT "COL_NAME"
FROM "TABLE"
WHERE EXISTS ("CONDITION");

Do keep in mind that the condition must have some sort of connection with the original column

SELECT studentName
FROM Students
WHERE EXISTS (SELECT lectureName FROM Lectures WHERE Lectures.lectureId = Students.favoriteLectureId AND rating > 4.0);

You can also use NOT EXISTS, which will do exactly what EXISTS does: find all columns which do not satsify the given condition.

SELECT productId
FROM Cookies
WHERE NOT EXISTS (SELECT productId FROM Orders WHERE Cookies.productId = Orders.productId AND productName LIKE '%jelly%');

STORED PROCEDURE

In SQL, procedures can be saved and used over and over again like a function.

The syntax of such function can be described as this:

CREATE PROCEDURE "PROCEDURE_NAME"
AS "STATEMENT"
GO;

We can also add parameter to the stored procedure, just like a function!

CREATE PROCEDURE SelectPopulatedClassrooms @BuildingId varchar(20), @Floor int
AS
SELECT ClassName FROM Classrooms WHERE buildingId = @BuildingId AND Floor = @Floor AND population>30
GO;

GO is used to indicate the end of CREATE PROCEDURE.

If you wish to run this stored procedure, you do it like this:

EXEC SelectPopulatedClassrooms @BuildingId = 'Newton', @Floor=3;

User Defined Functions

Although Stored Prodecures work just like a function, they aren’t. Users can create their own functions which can be called within other SQL statements.

Let’s look at the syntax:

CREATE FUNCTION "FUNCTION_NAME"(param "DATA_TYPE") RETURNS "DATA_TYPE" (DETERMINISTIC|NOT DETERMINISTIC)
BEGIN
    DECLARE "VARIABLE" INT;
       "STATEMENT"
    RETURN "VARIABLE";
END;

A function is innately NOT DETERMINISTIC, meaninig that the input and output are not identical every time the function is called. UDF is much more like a “function” than a Stored Procedure in a way that it uses variables and can be called within queries.

An example of UDF is:

CREATE FUNCTION factorial(input INT)
RETURNS INT NOT DETERMINISTIC
BEGIN
    DECLARE result INT DEFAULT 1;
    DECLARE inc INT DEFAULT 1;
    
    WHILE inc <= input DO
        SET result = result * inc;
        SET inc = inc + 1;
    END WHILE;
    
    RETURN result;
END

I was able to implement a factorial within MySQL. (Wouldn’t know if anyone would need it.)

If you want to call the function:

SELECT factorial(studentId)
FROM Students
WHERE studentId BETWEEN 1 AND 10;

Transactions and Indexes

Transactions are commonly used to secure a procedure’s atomicity; when you have several queries to make within a given procedure, and all of them have to be done at the same time, you would be using transactions.

The syntax of transcations is:

START TRANSACTION;
"STATEMENT"
IF "CONDITION" THEN
    COMMIT;
ELSE
    ROLLBACK;
END IF;

One can commit what came between the START and COMMIT, or choose to ROLLBACK if they don’t want the changes to take place.

Then, we have indexes, which ALWAYS hastens the SELECT result, no matter how useful it is. It is a helpful tool to remember.

CREATE INDEX "INDEX_NAME" ON "TABLE"("COLUMN");

Conclusion

I attempted to describe some other advanced database features used in MySQL. It is important that one get acquainted to these attributes other than just simple CRUD functions. This was it for the MySQL posts. I mean there really wasn’t much to go on about it since it’s basically the simplest RDBMS available to normal users. I will try to continue this trend with PostgreSQL.

Reference

https://dev.mysql.com/doc/