Mysql (1)

MySQL is the most famous non-commercial RDBMS available to use. This means that the said software is free to use. MySQL’s ease of use and low cost caused it to rank 2nd in the RDBMS ranking. I want to take a cursory summary of some “advanced” features of MySQL, other than just the CRUD functions.

Trigger

It runs a predefined ‘Action’ automatically when a certain event takes place. It generally runs when an INSERT, UPDATE, or DELETE command is executed. The columns produced before and after the change are called OLD and NEW accordingly.

Trigger’s syntax looks something like this:

CREATE TRIGGER "TRIGGER_NAME" AFTER "EVENT" ON "TABLE"
FOR EACH ROW
"ACTION"

If I were to give an example of utilizing trigger:

CREATE TRIGGER TBL_USER_KR_UPDATE AFTER UPDATE ON TBL_USER_KR
FOR EACH ROW
BEGIN
    INSERT INTO TBL_USER_LOD(USER_SEQ,USER_ID, USER_PASS, USER_NAME, USER_EMAIL, ACTION) VALUES(NEW.USER_SEQ, NEW.USER_ID, NEW.USER_PASS, NEW.USER_NAME, NEW.USER_EMAIL,'U');
END
;;
DELIMITER ;

User

In MySQL, the users are granted privileges. One can endow privileges with ‘GRANT’ and depriving them with ‘REVOKE’. However, the changes in privileges are not applied directly and are rather cached in a temporary storage for the time being; in order to apply these changes one must run the ‘FLUSH PRIVILEGES’ command, which reloads the privleges from the grant table.

Syntax of GRANT:

GRANT "PRIVILEGES" ON "DATABASE"."TABLE" TO "USER"@"HOST" IDENTIFIED BY "PASSWORD";

Example:

GRANT SELECT,INSERT ON ssafy_database.* TO 'ssafy_sun'@'localhost' IDENTIFIED BY '0000';

Syntax of REVOKE:

REVOKE "PRIVILEGES" ON "DATABASE"."TABLE" FROM "USER"@"HOST";

Example:

REVOKE ALL ON ssafy_database.students FROM 'ssafy_sun'@'localhost';

Window Function

Window functions are aggregate functions, just like GROUP BY! Then, why would one choose to use it over GROUP BY? Window function displays the aggregate result alongside the original table, whilst GROUP BY only allows the former.

The syntax of Window Function looks like:

aggregate_function(column_name) OVER (PARTITION BY grouping_column ORDER BY column_name)

Using the function will look something like this:

SELECT name,category,price,
SUM(price) OVER(PARTITION BY category) ORDER BY 4 AS category_price 
FROM product

Conclusion

This was a very simple overview of some of the advanced functions of MySQL. Since this RDBMS is a very well known, popular one, I won’t cover the details of the simpler functions. Please keep in mind that this post serves the purpose of preparing for the PostgreSQL article.

Reference

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