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.
SELECT name, IF(phone IS NULL, 'N/A', phone) from customer;
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)
SELECT concat(lastname, "," , firstname) FROM employee;
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
SELECT * FROM employee WHERE age BETWEEN 20 AND 30; SELECT * FROM employee WHERE age>=20 AND age<=30;
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
SELECT * from employee where age IN (20,30,40);
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.
SELECT count(*) FROM student WHERE grade>=90;
MySQL MAX function, it returns the max value of a column. For example, select the highest sale order from sales table.
SELECT MAX(order_subtotal) from sales;
MySQL MIN function, it returns the min value of a column. For example, select the lowest sale order from the sales table.
SELECT MIN(order_subtotal) from sales;
MySQL AVG function, it returns the average value of a column. For example, get the average sale order from the sales table.
SELECT AVG(order_subtotal) from sales;
MySQL SUM function, it returns the sume of a column. For example, get the total sales from the sales table.
SELECT SUM(order_subtotal) from sales;
Search within Codexpedia
Search the entire web