MariaDB Crash Course - Ben Forta [33]
Input
SELECT cust_id, order_num
FROM orders
WHERE Date(order_date) = '2011-09-01';
* * *
Tip: If You Mean Date Use Date()
It’s a good practice to use Date() if what you want is just the date, even if you know that the column only contains dates. This way, if somehow a date time value ends up in the table in the future, your SQL won’t break. Oh, and yes, there is a Time() function, too, and it should be used when you want the time.
* * *
Now that you know how to use dates to test for equality, using all the other operators (introduced in Chapter 6, “Filtering Data”) should be self-explanatory.
But one other type of date comparison warrants explanation. What if you wanted to retrieve all orders placed in September 2011? A simple equality test does not work as it matches the day of the month, too. There are several solutions, one of which follows:
Input
SELECT cust_id, order_num
FROM orders
WHERE Date(order_date) BETWEEN '2011-09-01' AND '2011-09-30';
Output
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
| 10001 | 20005 |
| 10003 | 20006 |
| 10004 | 20007 |
+---------+-----------+
Analysis
Here a BETWEEN operator is used to define 2011-09-01 and 2011-09-30 as the range of dates to match.
Here’s another solution (one that won’t require you to remember how many days are in each month, or worry about February in leap years):
Input
SELECT cust_id, order_num
FROM orders
WHERE Year(order_date) = 2011 AND Month(order_date) = 9;
Analysis
Year() is a function that returns the year from a date (or a date time). Similarly, Month() returns the month from a date. WHERE Year(order_date) = 2011 AND Month(order_date) = 9 thus retrieves all rows that have an order_date in year 2011 and in month 9.
* * *
Note: Support For Microseconds
MariaDB 5.3 adds support for microseconds when working with date and time values.
* * *
Numeric Manipulation Functions
Numeric manipulation functions do just that—manipulate numeric data. These functions tend to be used primarily for algebraic, trigonometric, or geometric calculations and, therefore, are not as frequently used as string or date and time manipulation functions.
The ironic thing is that of all the functions found in the major DBMSs, the numeric functions are the ones that are most uniform and consistent. Table 11.3 lists some of the more commonly used numeric manipulation functions.
Table 11.3 Commonly Used Numeric Manipulation Functions
Summary
In this chapter, you learned how to use SQL’s data manipulation functions and paid special attention to working with dates.
12. Summarizing Data
In this chapter, you learn what the SQL aggregate functions are and how to use them to summarize table data.
Using Aggregate Functions
It is often necessary to summarize data without actually retrieving it all, and MariaDB provides special functions for this purpose. Using these functions, MariaDB queries are often used to retrieve data for analysis and reporting purposes. Examples of this type of retrieval are
• Determining the number of rows in a table (or the number of rows that meet some condition or contain a specific value)
• Obtaining the sum of a group of rows in a table
• Finding the highest, lowest, and average values in a table column (either for all rows or for specific rows)
In each of these examples, you want a summary of the data in a table, not the actual data itself. Therefore, returning the actual table data would be a waste of time and processing resources (not to mention bandwidth). To repeat, all you really want is the summary information.
To facilitate this type of retrieval, MariaDB features a set of aggregate functions, some of which are listed in Table 12.1. These functions enable you to perform all the types of retrieval just enumerated.
Table 12.1 SQL Aggregate Functions
* * *
Note: Aggregate functions
Functions that operate on a set of rows to calculate and return a single value.
* * *
The use of each of these functions is explained in the following sections.