MySQL Cheat Sheet: For Quick Reference

MySQL is a Relational Database Management System (RDBMS) which uses Structured Query Language (SQL). It is the second-highest-used RDBMS worldwide after Oracle. It can run virtually on all the platforms like Windows, Linux, Unix, and Mac. Although MySQL has a variety of applications it is generally used with websites.

In this tutorial we will be covering the highest and most commonly used MySQL Data Types, Functions, and Commands:

Data Types In MySQL

Functions In MySQL

• String Functions In MySQL
• Numeric Functions In MySQL
• Date Time Functions In MySQL
• Information Functions In MySQL

String Functions In MySQL

ASCII(string)

It returns the ASCII value of the leftmost character in the string passed as the argument to this function. If the line is empty it returns 0 and returns NULL if the string is NULL. It works with 8-bit characters.

mysql> SELECT ASCII('7');
        -> 55
mysql> SELECT ASCII(7);
        -> 55
mysql> SELECT ASCII('mysqlcode');
        -> 109Code language: JavaScript (javascript)

BIN(number)

It returns the string representation of the binary value of the number passed the argument to this function. It is the same as writing CONV(N, 10, 2). i.e., the number ‘N’ is converted to a binary number and then represented as a string.

mysql> SELECT BIN(27);
        -> '11011'
mysql> SELECT BIN(13);
        -> '1101'
mysql> SELECT BIN(21);
        -> '10101'Code language: JavaScript (javascript)

BIT_LENGTH(string) 

It returns the length of the string passed as the argument to this function in terms of bits. Returns NULL if the string is NULL.

mysql> SELECT BIT_LENGTH('my');
        -> 16
mysql> SELECT BIT_LENGTH('sql');
        -> 24
mysql> SELECT BIT_LENGTH('code');
        -> 32
Code language: JavaScript (javascript)

UPPER(string)

It takes a string as an argument and converts all its characters to upper-case letters. This doesn’t actually change the actual data in the table, it just presents the data in upper case.

mysql> SELECT UPPER('my');
        -> 'MY'
mysql> SELECT UPPER('sql');
        -> 'SQL'
mysql> SELECT UPPER('code');
        -> 'CODE'
Code language: JavaScript (javascript)

LOWER(string)

It takes a string as an argument and converts all of its characters to lowercase letters. This also doesn’t change the actual data in the table, it just presents the data in lowercase.

mysql> SELECT UPPER('MY');
        -> 'my'
mysql> SELECT UPPER('sQl');
        -> 'sql'
mysql> SELECT UPPER('code');
        -> 'code'
Code language: JavaScript (javascript)

CONCAT(string1, string2,…..)

It is used to append strings that are passed as the argument to this function. Returns the concatenation of all the strings which are passed as the argument. If any of the argument strings is NULL it returns NULL and if there is only one string it is returned as a result.

mysql> SELECT CONCAT('my', 'sql', 'code');
        -> 'mysqlcode'
mysql> SELECT CONCAT('my', NULL, 'code');
        -> NULL
mysql> SELECT CONCAT(3.14);
        -> '3.14'Code language: PHP (php)

LENGTH(string)

It returns the length of the string passed as the argument in terms of bytes. i.e., for a string having 10 2-byte characters, it will return 20 whereas CHAR_LENGTH() returns 10. Returns NULL if the string is NULL.

mysql> SELECT LENGTH('my');
        -> 2
mysql> SELECT LENGTH('sql');
        -> 3
mysql> SELECT LENGTH('code');
        -> 4Code language: JavaScript (javascript)

LEFT(string, number)

It returns the substring of the argument string starting from the leftmost side and having a length equal to the specified number. It returns NULL if any of the arguments are NULL.

mysql> SELECT LEFT('mysqlcode', 5);
        -> 'mysql'
mysql> SELECT LEFT('codeforgeek', 5);
        -> 'codef'
mysql> SELECT LEFT('askpython', 5);
        -> 'askpy'Code language: JavaScript (javascript)

RIGHT(string, number)

It returns the substring of the argument string starting from the rightmost side and having a length equal to the specified number. It returns NULL if any of the arguments are NULL.

mysql> SELECT LEFT('mysqlcode', 5);
        -> 'lcode'
mysql> SELECT LEFT('codeforgeek', 5);
        -> 'rgeek'
mysql> SELECT LEFT('askpython', 5);
        -> 'ython'Code language: JavaScript (javascript)

TRIM(string)

It removes leading as well as trailing white spaces from the argument string. Returns NULL if the string is NULL.

mysql> SELECT TRIM('  mysqlcode   ');
        -> 'mysqlcode'
mysql> SELECT TRIM(LEADING 'x' FROM 'xxxmysqlcodexxx');
        -> 'mysqlcodexxx'
mysql> SELECT TRIM(BOTH 'x' FROM 'xxxmysqlcodexxx');
        -> 'mysqlcode'
mysql> SELECT TRIM(TRAILING 'x' FROM 'xxxmysqlcodexxx');
        -> 'xxxmysqlcode'
Code language: JavaScript (javascript)

LTRIM(string)

It removes all the leading white spaces from the argument string. Returns NULL if the string is NULL.

mysql> SELECT LTRIM('  mysqlcode');
        -> 'mysqlcode'
mysql> SELECT LTRIM('  mysqlcode ');
        -> 'mysqlcode 'Code language: JavaScript (javascript)

RTRIM(string)

It removes all the trailing white spaces from the argument string. Returns NULL if the string is NULL.

mysql> SELECT LTRIM('  mysqlcode');
        -> ' mysqlcode'
mysql> SELECT LTRIM('  mysqlcode ');
        -> ' mysqlcode'Code language: JavaScript (javascript)

FORMAT(number, number_of_decimal_places)

It takes a number as input and formats the number as ‘dd,ddd,ddd.dd’ and rounds to a specified number, and then returns the result as a string. If either of the arguments is NULL it returns NULL.

mysql> SELECT FORMAT(9823793.984372, 4);
        -> '9,823,793.9843'
mysql> SELECT FORMAT(9823793.9,4);
        -> '9823793.9000'
mysql> SELECT FORMAT(9823793.9,0);
        -> '9823793'
mysql> SELECT FORMAT(12332.2,2,'de_DE');
        -> '12.332,20'Code language: JavaScript (javascript)

SUBSTRING(string, position, length)

It returns a substring of the argument string starting specified position and of a specified length.

mysql> SELECT SUBSTRING('mysqlcode',5);
        -> 'lcode'
mysql> SELECT SUBSTRING('mysqlcode' FROM 3);
        -> 'sqlcode'
mysql> SELECT SUBSTRING('mysqlcode',4,6);
        -> 'qlcode'
mysql> SELECT SUBSTRING('mysqlcode', -4);
        -> 'code'
mysql> SELECT SUBSTRING('mysqlcode', -5, 3);
        -> 'lco'
mysql> SELECT SUBSTRING('mysqlcode' FROM -4 FOR 2);
        -> 'co'Code language: JavaScript (javascript)

SUBSTRING_INDEX(string, delimiter, number)

It returns a substring before a delimiter occurs for a specified number of times. e.g. Take a string “www.mysqlcode.com“, and say the delimiter is ‘.’, with the help of SUBSTRING_INDEX() we can extract a substring if the delimiter occurs the number of times we specify.

mysql> SELECT SUBSTRING_INDEX('www.mysqlcode.com', '.', 1);
        -> 'www'
mysql> SELECT SUBSTRING_INDEX('www.mysqlcode.com', '.', 2);
        -> 'www.mysqlcode'
mysql> SELECT SUBSTRING_INDEX('www.mysqlcode.com', '.', -2);
        -> 'mysql.com'Code language: JavaScript (javascript)

STRCMP(string1, string2)

It takes two strings as arguments and returns output corresponding to the comparison result of the strings.
It will return 0 if both are equal, -1 if the first string is smaller than the second, and 1 if the first string is greater than the second.

mysql> SELECT STRCMP('askpython', 'mysqlcode');
        -> -1
mysql> SELECT STRCMP('mysqlcode', 'askpython');
        -> 1
mysql> SELECT STRCMP('mysqlcode', 'mysqlcode');
        -> 0Code language: JavaScript (javascript)

POSITION(string IN substring)

It returns the position of the specified substring in the argument string. Returns 0 if the substring doesn’t exist in the string. And NULL if either of the arguments is NULL

mysql> SELECT POSITION('sql' IN  'mysqlcode');
        -> 3
mysql> SELECT POSITION('python' IN 'mysqlcode');
        -> 0Code language: JavaScript (javascript)

REVERSE(string)

It takes a string as input and reverses it. Returns NULL if the string is NULL.

mysql> SELECT REVERSE('mysqlcode');
        -> 'edoclqsym'
mysql> SELECT REVERSE('askpython');
        -> 'nohtypksa'
mysql> SELECT REVERSE('codeforgeek');
        -> 'keegrofedoc'Code language: JavaScript (javascript)

LOAD_FILE(file_name)

It returns the content of a file as a string. In order to use this function the file must exist on the server host and we must specify the full path of the file and the file must be readable.

mysql> UPDATE t
            SET blob_col=LOAD_FILE('/tmp/picture');
            WHERE id=1;Code language: JavaScript (javascript)

Numeric Functions In MySQL

ABS(number)

It returns the absolute value of the number passed as the argument. e.g., ABS(-3.14597) returns 3.14597.

mysql> SELECT ABS(27);
        -> 27
mysql> SELECT ABS(-22);
        -> 22

TRUNCATE(number, number_of_decimal_places)

It trims a number to a specified number of decimal places passed to it as the argument. e.g., TRUNCATE(3.14592, 3) returns 3.145.

mysql> SELECT TRUNCATE(3.141592, 1);
        -> 3.1
mysql> SELECT TRUNCATE(3.141592, 2);
        -> 3.14
mysql> SELECT TRUNCATE(3.141592, 0);
        -> 3
mysql> SELECT TRUNCATE(-3.141592, 1);
        -> -3.1
mysql> SELECT TRUNCATE(127,-2);
       -> 100
mysql> SELECT TRUNCATE(10.24*100,0);
       -> 1024Code language: CSS (css)

ROUND(number, number_of_decimal_places)

It rounds the number passed as the argument to a specified number of decimal places. e.g., ROUND(3.14597, 3) returns 3.146.

mysql> SELECT ROUND(-3.141592);
        -> -3
mysql> SELECT ROUND(-3.641592);
        -> -4
mysql> SELECT ROUND(3.141592);
        -> 3
mysql> SELECT ROUND(3.141592, 1);
        -> 3.1
mysql> SELECT ROUND(3.141592, 0);
        -> 3
mysql> SELECT ROUND(31.41592, -1);
        -> 30
mysql> SELECT ROUND(.12345678901234567890123456789012345, 35);
        -> 0.123456789012345678901234567890Code language: CSS (css)

MOD(number1, number2)

It returns the remainder of a number after dividing it by another number. e.g., MOD(24, 6) returns 4. Returns NULL if either of the numbers is NULL.

mysql> SELECT MOD(27, 22);
        -> 5
mysql> SELECT 231 % 7;
        -> 0
mysql> SELECT MOD(49, 9);
        -> 4
mysql> SELECT 29 MOD 9;
        -> 2

CEIL(number)

It returns the integer value which is equal to or is the next greater integer value of the specified number. e.g., CEIL(3.5) – Returns 4.

mysql> SELECT CEIL(27.27);
        -> 28
mysql> SELECT CEIL(-27.27);
        -> -27
mysql> SELECT CEIL(22.7);
        -> 23
Code language: CSS (css)

CONV(number, current_base, required_base)

It converts a number of one base to another base. e.g., CONV(27, 10, 2) will return 11011, in this example 27 is a decimal number that is converted to a binary number i.e., 11011. it returns NULL if either of its arguments is NULL.

mysql> SELECT CONV(1011, 2, 10);
        -> '11'
mysql> SELECT CONV(127, 8, 10);
        -> '87'
mysql> SELECT CONV(AFE, 16, 10);
        -> '2814'Code language: JavaScript (javascript)

FLOOR(number)

It returns the integer value which is equal to or is the next smaller integer value of the specified number. e.g., FLOOR(3.14597) – Returns 3. Returns NULL if the input number is NULL.

mysql> SELECT FLOOR(3.14)
        -> 3
mysql> SELECT FLOOR(-3.14);
        -> -4Code language: CSS (css)

PI():

It returns the value of PI. In order to get the value of PI to a higher decimal place, all we need to do is add “0.00…” zeros after the decimal will decide the decimal places of PI.

mysql> SELECT PI();
        -> 3.141593
mysql> SELECT PI()+0.000000000000000000;
        -> 3.141592653589793116Code language: CSS (css)

RAND()

It returns a random floating-point number within the range of 0 – 1.

mysql> SELECT i, RAND(3) FROM t;
        -> 0.90576975597606,  0.37307905813035, 0.14808605345719 Code language: CSS (css)

SIGN()

It returns the sign of the number passed as the argument. e.g., SIGN(-221) will return -1.

mysql> SELECT SIGN(-27);
        -> -1
mysql> SELECT SIGN(0);
        -> 0
mysql> SELECT SIGN(29);
        -> 1

SQRT(number)

It returns the square root of the number passed as the argument. e.g., SQRT(64) will return 8. Returns NULL if the number is NULL or negative.

mysql> SELECT SQRT(169);
        -> 13
mysql> SELECT SQRT(2);
        -> 2.414
mysql> SELECT SQRT(-16);
        -> NULLCode language: CSS (css)

RADIANS(number)

It returns the number, in terms of radians. e.g. RADIANS(60) = 1.047198 = PI/3.

mysql> SELECT RADIANS(90);
        -> 1.57079
mysql> SELECT RADIANS(60);
        -> 1.04719
mysql> SELECT RADIANS(180);
        -> 3.14592Code language: CSS (css)

Date Time Functions In MySQL

TIMEDIFF(expression1, expression2)

It calculates the difference between two time/date-time expressions.

mysql> SELECT TIMEDIFF('2000-01-01 00:00:00',
 '2000-01-01 00:00:00.000001');
        -> '-00:00:00.000001'
mysql> SELECT TIMEDIFF('2008-12-31 23:59:59.000001',
 '2008-12-30 01:01:01.000002');
        -> '46:58:57.999999'Code language: JavaScript (javascript)

TIMESTAMPDIFF(unit,expression1,expression2)

It calculates the difference between two date/date-time expressions

mysql> SELECT TIMESTAMP('2023-02-18');
        -> '2023-02-18 00:00:00'
mysql> SELECT TIMESTAMP('2023-02-18 12:00:00','12:00:00');
        -> 2023-02-19 00:00:00Code language: JavaScript (javascript)

CURDATE()

It returns the current date.

mysql> SELECT CURDATE();
        -> '2023-02-18'
mysql> SELECT CURDATE() + 0;
        -> 20230218Code language: JavaScript (javascript)

NOW()

It returns the date time at the time of statement execution.

mysql> SELECT NOW();
        -> '2023-02-18 08:46:15'
mysql> SELECT NOW() + 0;
        -> 20230218084638Code language: JavaScript (javascript)

DATADIFF(date1, date2)

It returns the number of days between two dates.

mysql> SELECT DATEDIFF('2023-02-18 23:59:59','2023-03-23');
        -> -33
mysql> SELECT DATEDIFF('2023-03-23 23:59:59','2023-02-18');
        -> 33Code language: JavaScript (javascript)

DATE_FORMAT(date, format)

It reformats the date in the specified format.

mysql>  SELECT DATE_FORMAT('2023-02-18 22:23:00', '%W %M %Y');
        -> 'Saturday February 2023'
mysql> SELECT DATE_FORMAT('2023-02-18 22:23:00', '%H:%i:%s');
        -> '22:23:00'
mysql>  SELECT DATE_FORMAT('2023-02-18 22:23:00', '%D %y %a %d %m %b %j');

        -> '18th 23 Sat 18 02 Feb 049'
mysql> SELECT DATE_FORMAT('2023-02-18 10:11:00', '%H %k %I %r %T %S %w');
        -> '10 10 10 10:11:00 AM 10:11:00 00 6'
mysql> SELECT DATE_FORMAT('2023-02-18', '%X %V');
        -> '2023 07'
mysql> SELECT DATE_FORMAT('2023-02-18', '%d');
        -> '18'Code language: JavaScript (javascript)

DAY(date)

It returns the day corresponding to the specified date.

mysql> SELECT DAY('2023-02-18');
        -> 18
mysql> SELECT DAY('2023-02-23');
        -> 23
mysql> SELECT DAY('2023-10-24');
        -> 24
Code language: JavaScript (javascript)

DAYNAME(date)

It returns the day name corresponding to the specified date.

mysql> SELECT DAYNAME('2023-02-18');
        -> Saturday
mysql> SELECT DAYNAME('2023-02-23');
        -> Thursday
mysql> SELECT DAYNAME('2023-10-24');
        -> TuesdayCode language: JavaScript (javascript)

DAYOFWEEK(date)

It returns the day of the week index for a specified date.

mysql> SELECT DAYOFWEEK('2023-02-18');
          -> 7
mysql> SELECT DAYOFWEEK('2023-02-23');
          -> 5
mysql> SELECT DAYOFWEEK('2023-10-24');
          -> 3Code language: JavaScript (javascript)

STR_TO_DATE(string, format)

It converts a string into date-time based on a specified format.

mysql> SELECT STR_TO_DATE('23,02,2023','%d,%m,%Y');
        -> '2023-02-23'
mysql> SELECT STR_TO_DATE('Oct 24, 2023','%M %d,%Y');
        -> '2023-10-24'
Code language: JavaScript (javascript)

SYSDATE()

It returns the system-configured date.

mysql> SELECT SYSDATE();
         ->2023-02-18 09:17:46Code language: CSS (css)

WEEK(date, firstdayofweek)

It returns the week number of a specified date.

mysql> SELECT WEEK('2023-02-18');
        -> 7
mysql> SELECT WEEK('2023-02-23',0);
        -> 8
mysql> SELECT WEEK('2023-02-18',1);
        -> 7
mysql> SELECT WEEK('2023-10-24',1);
        -> 43Code language: JavaScript (javascript)

WEEKDAY()

It returns the index of the weekday of a specified date.

mysql> SELECT WEEKDAY('2023-02-18');
          -> 5
mysql> SELECT WEEKDAY('2023-02-23');
          -> 3
mysql> SELECT WEEKDAY('2023-10-24');
          -> 1Code language: JavaScript (javascript)

YEAR()

It returns the year of the specified date.

mysql> SELECT YEAR('2023-02-18');
          -> 2023
mysql> SELECT YEAR('2001-02-23');
          -> 2001
mysql> SELECT YEAR('2003-10-24');
          -> 2003Code language: JavaScript (javascript)

Information Functions In MySQL

CONNECTION_ID()

It returns the connection id of a particular connection.

mysql> SELECT CONNECTION_ID();
        -> 813

CURRENT_USER()

It returns the combination of the username and the hostname used by the MySQL account server to authenticate the current user.

mysql> SELECT CURRENT_USER();
        -> 'user_3yvs82cj5_3yy9nfdx5@%'Code language: JavaScript (javascript)

DATABASE()

It returns the default database name.

mysql> SELECT DATABASE();
        -> 'db_3yvs82drq_3yy9njdt3'Code language: JavaScript (javascript)

FOUND_ROWS()

It returns the number of rows found by a query without actually running the query. FOUND_ROWS() is not replicated reliably using statement-based replication. This function is automatically replicated using row-based replication.

mysql> SELECT * FROM tbl_name WHERE id > 100 LIMIT 10;
mysql> SELECT COUNT(*) FROM tbl_name WHERE id > 100;

ROW_COUNT()

It returns the number of affected rows after the execution of a statement. Returns -1 if none of the rows are affected.

mysql> SELECT ROW_COUNT();
       ->  -1

LAST_INSERT_ID()

It returns a BIGINT UNSIGNED (64bit) value which is an automatically generated value that has been successfully inserted for an AUTO_INCREMENT column. This value remains unchanged if no new rows are inserted successfully.

mysql> SELECT LAST_INSERT_ID();
        -> 0

USER()

It returns MySQL username and hostname.

mysql> SELECT USER();
        -> 'user_3yvs82drq_3yy9pqnu8@172.17.0.11'Code language: JavaScript (javascript)

VERSION()

It returns the MySQL version number.

Smysql> SELECT VERSION();
        -> '8.0.27'Code language: JavaScript (javascript)

CHARSET()

It returns the character set of the string argument.

mysql> SELECT CHARSET('abc');
        -> 'utf8mb3'
mysql> SELECT CHARSET('1243');
        -> 'utf8mb3'
mysql> SELECT CHARSET(CONVERT('abc' USING latin1));
        -> 'latin1'
Code language: JavaScript (javascript)

MD5()

It calculates an MD5 128-bit checksum for the string and returns a string of 32 hexadecimal digits.

mysql> SELECT MD5('password');
        -> '5f4dcc3b5aa765d61d8327deb882cf99'Code language: JavaScript (javascript)

COMPRESS(string)

This command returns a binary string by compressing the argument string.

mysql> SELECT COMPRESS(REPEAT('a',1000));
        -> �\0\0x�KL�`w\0\0��z�
mysql> SELECT COMPRESS('');
        -> 
mysql>SELECT COMPRESS('a');
        -> \0\0\0x�K\0\0b\0b
mysql> SELECT COMPRESS('mysqlcode');
        -> \t\0\0\0x�˭,.�I�OI\0��Code language: PHP (php)

UNCOMPRESS()

This command is used to extract the original string from a binary string compressed by COMPRESS(). If the argument is not a compressed value, the result is NULL.

mysql> SELECT UNCOMPRESS(COMPRESS('mysqlcode'));
        -> mysqlcodeCode language: JavaScript (javascript)

Working With Tables In MySQL

CREATE

It is used to create a table.

CREATE TABLE (<table_name> <field1(type)>, <field2, (type)>…);Code language: HTML, XML (xml)

INSERT

It is used to insert rows in the table.

INSERT INTO
<table_name>(field1, field2, …)
VALUES(value1, value2,…);Code language: HTML, XML (xml)

UPDATE

It is used to update existing data in the table.

UPDATE <table_name>
SET
field_name1 = value1,
field_name2 = value2;Code language: HTML, XML (xml)

SELECT

It is used to select a list of values from the table

SELECT <field_list> FROM <table_name>;Code language: HTML, XML (xml)

SELECT DISTINCT

It is used to select only unique values from a list.

SELECT DISTINCT <field_list> FROM <table_name>;Code language: HTML, XML (xml)

WHERE

This clause allows us to select values that meet the specified conditions.

SELECT <filed_list> FROM <table_name> WHERE <condition>;Code language: HTML, XML (xml)

ORDER BY

This clause is used to sort the queried result set either in ascending or descending order. By default, it sorts in ascending order.

SELECT <field_list> FROM <table_name> ORDER BY <field_name> [ASC|DESC];Code language: HTML, XML (xml)

AND

This is a logical operator. With this, the query statement executes only if both expressions are true.

… expression1 AND expression2

OR

This is also a logical operator. With this operator, the query statement is executed when either of the expressions is true.

… expression_1 OR expression_2

IN

This operator is generally used with the WHERE clause. It enables us to check if a specified value in a list matches another set of values.

SELECT <field_list> FROM <table_name> 
WHERE <expression|column_1> 
IN (value1, value2, …);Code language: HTML, XML (xml)

BETWEEN

This operator is also used with the WHERE clause. It is used to check if a value is in a specified range or not.

… expression [NOT] 
BETWEEN expression_1 
AND expression_2Code language: CSS (css)

LIMIT

This clause is used with the SELECT statement to specify the number of rows to be returned.

IS NULL

It is used to test if a value is NULL or not. If it is NULL, the expression returns true, else false.

SELECT <field_list> 
FROM <table_name>
LIMIT <first_row_offset>
<number of rows to be returned>;
Value IS NULLCode language: HTML, XML (xml)

INNER JOIN

This is a filter clause that matches each row in one table with each row in the other table. Which enables the to query only those rows that have corresponding columns from both tables.

SELECT <field_list>
FROM <table1_name>
INNER JOIN <table2_name>
ON <join_condition>Code language: HTML, XML (xml)

LEFT JOIN

It allows us to query data from multiple tables. It matches each row from the first table to each row in the second table as a condition.

SELECT <field_names>
FROM <table1_name>
LEFT JOIN <table2_name>
ON join_condition;Code language: HTML, XML (xml)

RIGHT JOIN

It is the same as LEFT JOIN except in this case table manipulation is in reverse order.

SELECT <field_names>
FROM <table1_name>
LEFT JOIN <table2_name>
ON join_condition;Code language: HTML, XML (xml)

CROSS JOIN

It returns the cartesian product of rows from the joined tables.

SELECT * FROM <table1_name>
CROSS JOIN <table2_name>;Code language: HTML, XML (xml)

GROUP BY

This enables us to group rows into subgroups based on column or expression values.

SELECT <field1, field2, field3…>
FROM <table1_name>
WHERE <condition/expression>
GROUP BY <field1, field2, field3…>Code language: HTML, XML (xml)

HAVING

It is generally used with the GROUP BY clause. It is used to specify filter conditions for a group of rows.

SELECT <field1, field2, field3…>
FROM <table1_name>
WHERE <condition/expression>
GROUP BY <field1, field2, field3…>
HAVING <group_condition>

ROLL UP

Used to generate the subtotals as well as grand totals of field values.

SELECT <field_name1>,
SUM(column_name) <field_name2>
FROM <table_name>
GROUP BY <field_name1>WITH ROLLUP;Code language: HTML, XML (xml)

EXISTS

It is a Boolean operator which returns either true or false. It is generally used to check if a query has returned any number of rows or not.

SELECT <field_list>
FROM <table_list>
WHERE [NOT] EXISTS(subquery);Code language: HTML, XML (xml)

INTERSECT

This is a set operator which returns only distinct rows of two or more queries.

(SELECT <field_list> FROM <table1_name>) INTERSECT
(SELECT <field_list> FROM <table2_name>)Code language: HTML, XML (xml)

UNION

This command combines two or more result sets from multiple SELECT queries and returns a single result set.

SELECT <field_list>
UNION [DISTINCT | ALL]
SELECT <field_list>
UNION [DISTINCT | ALL]Code language: HTML, XML (xml)

UPDATE JOIN

When the JOIN clause is used with an UPDATE statement, it is called UPDATE JOIN.

UPDATE <table1>, <table2>.
[INNER JOIN | LEFT JOIN]
table1.common_field = table2.common_field
SET table1.field1 = newvalues …
WHERE <condition>Code language: HTML, XML (xml)

DELETE

This command is used to delete existing data from a table.

DELETE FROM <table_name>
WHERE <condition>Code language: HTML, XML (xml)

DELETE JOIN

This command is used to delete data from multiple tables using the JOIN statement.

DELETE <field1>,<field2>
FROM <table1>
INNER JOIN <table2>
ON table1.key = table2.key
WHERE <condition>;Code language: HTML, XML (xml)

ON DELETE CASCADE

This command enables deleting data from the child table automatically when data is deleted from the parent table.

SELECT <table_name>
FROM <referential_constraints>
WHERE <constraint_schema = 'database_name'>
AND referenced_table_name = 'parent_table'
AND delete_rule = 'CASCADE'Code language: HTML, XML (xml)

Replace

This command is used to update data in a table.

REPLACE <table_name>(<field1>, <field2>,…)
VALUES (<value1>, <value2>, …)Code language: HTML, XML (xml)

Summary

MySQL is the second highest used RDBMS worldwide. It is highly scalable, customizable, and can run on different platforms. MySQL is widely supported by other software, including programming languages, web servers, and content management systems. It helps in learning and implementing DBMS/RDBMS concepts in real projects. This cheat sheet will give you a brief introduction to the journey ahead and it will also help in revising the MySQL and database concepts.

References

• https://dev.mysql.com/
• https://docs.oracle.com/