MySQL function cheat sheet
MySQL IF statement, IF(expr,if_true_expr,if_false_expr). For example, you want a list of customers with their name and phone number, but some customer doesn’t have phone number in your database, so you want to replace NULL with N/A in the result set from the select statement.
[code language=”sql”]
SELECT name, IF(phone IS NULL, ‘N/A’, phone) from customer;
[/code]
MySQL concat function, it takes 2 or more string and concatenate them into one string. For example, you want to select first name and last name from the employee table and concatenate them with lastname,firstname (Washington,George)
[code language=”sql”]
SELECT concat(lastname, "," , firstname) FROM employee;
[/code]
MySQL BETWEEN Clause, it is a short hand for greater than equal and less than equal. For example you want to select employees who age are between 20 and 30 including 20 and 30
[code language=”sql”]
SELECT * FROM employee WHERE age BETWEEN 20 AND 30;
SELECT * FROM employee WHERE age>=20 AND age<=30;
[/code]
MySQL IN Clause, it can be used with a MySQL query to specify a condition. For example, select
employees from employee table whose age is equal to 20, 30, 40
[code language=”sql”]
SELECT * from employee where age IN (20,30,40);
[/code]
MySQL COUNT function, it is used to count the number of records returned by a query. For example, you want to get the number students whose grade is greater than or equal to 90 from the student table.
[code language=”sql”]
SELECT count(*) FROM student WHERE grade>=90;
[/code]
MySQL MAX function, it returns the max value of a column. For example, select the highest sale order from sales table.
[code language=”sql”]
SELECT MAX(order_subtotal) from sales;
[/code]
MySQL MIN function, it returns the min value of a column. For example, select the lowest sale order from the sales table.
[code language=”sql”]
SELECT MIN(order_subtotal) from sales;
[/code]
MySQL AVG function, it returns the average value of a column. For example, get the average sale order from the sales table.
[code language=”sql”]
SELECT AVG(order_subtotal) from sales;
[/code]
MySQL SUM function, it returns the sume of a column. For example, get the total sales from the sales table.
[code language=”sql”]
SELECT SUM(order_subtotal) from sales;
[/code]
Search within Codexpedia

Search the entire web
