MySQL Functions and Operators: Enhancing Data Manipulation

Built-in tools for calculations, text formatting, date handling, and conditional logic in your queries.

MySQL functions

Functions are built-in operations for calculations, formatting, and transforming data as you retrieve it.

String functions

Useful for working with text:

• CONCAT: joins two or more strings together.

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

That combines first_name and last_name into a full name.

• UPPER and LOWER: convert text to upper or lower case.

SELECT UPPER(first_name) FROM users;

All first names in uppercase.

Numeric functions

For maths in your queries:

• ROUND: rounds a number to a set number of decimal places.

SELECT ROUND(price, 2) FROM products;

Price rounded to two decimal places.

• ABS: returns the absolute value of a number.

SELECT ABS(change) FROM transactions;

The absolute value of each change in transactions.

Date and time functions

Common when working with dates:

• CURDATE and CURTIME: current date and time.

SELECT CURDATE(), CURTIME();

• DATEDIFF: difference in days between two dates.

SELECT DATEDIFF('2022-12-31', '2022-01-01') AS days_diff;

Number of days between the two dates.

Operators

Operators handle arithmetic, comparisons, and logic in WHERE clauses and elsewhere.

Arithmetic operators

Addition (+), subtraction (-), multiplication (*), and division (/) work in queries. A 10% price increase:

SELECT price * 1.1 AS new_price FROM products;

Comparison operators

Equals (=), not equals (!= or <>), greater than (>), less than (<), and so on. Products cheaper than a set price:

SELECT * FROM products WHERE price < 20;

Logical operators

AND, OR, and NOT for building conditions. Cheap products that are in stock:

SELECT * FROM products WHERE price < 20 AND in_stock = TRUE;

CASE and IFNULL

• CASE: conditional logic inside a query.

SELECT name, 
       CASE 
           WHEN price < 20 THEN 'cheap'
           WHEN price BETWEEN 20 AND 50 THEN 'moderate'
           ELSE 'expensive'
       END AS price_category
FROM products;

• IFNULL: substitute a value when NULL is encountered.

SELECT name, IFNULL(description, 'No description') FROM products;

A few practical tips

1. Readability over cleverness: a clear query beats a one-liner that nobody can follow.
2. Watch out for NULL: functions and operators often behave differently when values are NULL.
3. Check performance: some functions in WHERE clauses can slow queries down on large tables.

Wrapping up

Functions and operators save you from doing work in application code that MySQL can handle directly. Try a few on your own tables – CONCAT, ROUND, and CASE come up often once you start using them.