MariaDB Crash Course - Ben Forta [18]
In dictionary sort order, A is treated the same as a, and that is the default behavior in MariaDB (and indeed most DBMSs). However, administrators can change this behavior if needed. (If your database contains many foreign language characters, this might become necessary.)
The key here is that, if you do need an alternate sort order, you cannot accomplish it with a simple ORDER BY clause. You need to use the CONVERT() function (functions are introduced in Chapter 11, “Using Data Manipulation Functions”) or contact your database administrator if you need the column character set changed.
* * *
Using a combination of ORDER BY and LIMIT, it is possible to find the highest or lowest value in a column. The following example demonstrates how to find the value of the most expensive item:
Input
SELECT prod_price
FROM products
ORDER BY prod_price DESC
LIMIT 1;
Output
+------------+
| prod_price |
+------------+
| 55.00 |
+------------+
Analysis
prod_price DESC ensures that rows are retrieved from most to least expensive, and LIMIT 1 tells MariaDB to just return one row.
* * *
Caution: Position of ORDER BY Clause
When specifying an ORDER BY clause, be sure that it is after the FROM clause. If LIMIT is used, it must come after ORDER BY. Using clauses out of order generates an error message.
* * *
Summary
In this chapter, you learned how to sort retrieved data using the SELECT statement’s ORDER BY clause. This clause, which must be the last in the SELECT statement, can be used to sort data on one or more columns as needed.
6. Filtering Data
In this chapter, you learn how to use the SELECT statement’s WHERE clause to specify search conditions.
Using the WHERE Clause
Database tables usually contain large amounts of data, and you seldom need to retrieve all the rows in a table. More often than not, you want to extract a subset of the table’s data as needed for specific operations or reports. Retrieving just the data you want involves specifying search criteria, also known as a filter condition.
Within a SELECT statement, data is filtered by specifying search criteria in the WHERE clause. The WHERE clause is specified right after the table name (the FROM clause) as follows:
Input
SELECT prod_name, prod_price
FROM products
WHERE prod_price = 2.50;
Analysis
This statement retrieves two columns from the products table, but instead of returning all rows, only rows with a prod_price value of 2.50 are returned, as follows:
Output
+---------------+------------+
| prod_name | prod_price |
+---------------+------------+
| Carrots | 2.50 |
| TNT (1 stick) | 2.50 |
+---------------+------------+
This example uses a simple equality test: It checks to see whether a column has a specified value, and it filters the data accordingly. But SQL enables you to do more than just test for equality.
* * *
Tip: SQL Versus Application Filtering
Data can also be filtered at the application level. To do this, the SQL SELECT statement retrieves more data than is actually required for the client application, and the client code loops through the returned data to extract just the needed rows.
As a rule, this practice is strongly discouraged. Databases are optimized to perform filtering quickly and efficiently. Making the client application (or development language) do the database’s job dramatically impacts application performance and creates applications that cannot scale properly. In addition, if data is filtered at the client, the server has to send unneeded data across the network connections, resulting in a waste of network bandwidth resources.
* * *
* * *
Caution: WHERE Clause Position
When using both ORDER BY and WHERE clauses, make sure ORDER BY comes after the WHERE; otherwise, an error will be generated. (See Chapter 5, “Sorting Retrieved Data,” for more information on using ORDER BY.)
* * *
The WHERE Clause Operators
The first WHERE clause we looked at tests for equality—determining whether a column contains a specific value. MariaDB supports