Numeric Expressions

Numbers can be exact-value literals or approximate-value literals.  Exact-value literals are used just as given in SQL statements when possible and thus are not subject to the inexactness produced by rounding error.  On the other hand, approximate-value literals are subject to rounding error and may not necessarily be used exactly as given.  

Exact-value literals are written with no exponent. Approximate-value literals are written in scientific notation with an exponent.  For example, the numeric values -43, 368.93, and .00214 are exact values, whereas -4.3E1, 3.6893E2, and 2.14E-3 are approximate values.  Even though the two sets of numbers look like they have the same values, internally they are represented in different ways:  

•  Exact-value numbers - 

                      These numbers are integer values with no fractional part after the decimal point or decimal values with a fractional part.  They're represented internally like an integer or DECIMAL data type.  Operations on integers are performed with the precision of BIGINT values (that is, 64 bits). Operations on decimal values have a precision of up to 64 decimal digits.  Currently, the scale for decimal values allows up to 30 decimal digits after the decimal point.  

•  Approximate-value literals -

                      These numbers are represented as floating-point numbers (like the DOUBLE data type) and have a mantissa and exponent.  The mantissa allows up to 53 bits of precision, which is about 15 decimal digits. 

•  Null Values -

                       Any numerical expression including  NULL, will return a null value (with the 

exception of some NULL -safe operations) Exact/Approximate Values When numbers are used in an arithmetic or comparison operation, the result of the operation may depend on whether it involves exact or approximate values.  Consider the following two comparisons:  

mysql> SELECT 1.1 + 2.2 = 3.3, 1.1E0 + 2.2E0 = 3.3E0; 

+--------------------+-----------------------------------+ 

| 1.1 + 2.2 = 3.3 | 1.1E0 + 2.2E0 = 3.3E0     | 

+--------------------+-----------------------------------+ 

|               1          |                 0                           | 

+--------------------+------------------------------------+ 

In the first expression, exact values are used, so the comparison involves exact calculations. In the second expression, approximate values are used and rounding error is possible.  This illustrates that when approximate values are used in comparisons, do  not expect exact-value precision.  The internal representation of floating-point numbers inherently allows for the possibility of a rounding error. 'Numeric Strings' If numbers are mixed with strings in numeric context, MySQL converts the strings to numbers and performs a numeric operation:  

mysql> SELECT 1 + '1', 1 = '1'; 

+---------+---------+ 

| 1 + '1'  | 1 = '1'  | 

+---------+---------+ 

|       2    |       1    | 

+---------+---------+ 

SQL Expressions

Expressions are a common element of SQL statements,  and they occur in many contexts.  For example, expressions often occur in the  WHERE clause of  SELECT,  DELETE, or  UPDATE statements to identify which records to retrieve, delete, or update.  But  expressions may be used in many other places; for example, in the output column list of a SELECT statement, or in ORDER BY or GROUP BY clauses.  Terms of expressions consist of constants (literal numbers, strings, dates, and times),  NULL values, references to table columns, and function calls.  Terms may be combined using operators into more complex expressions.  Many types of operators are available, such as those for arithmetic, comparison,logical, and pattern-matching operations.  Here are some examples of expressions:  

•  The following statement refers to table columns to select country names and populations from the Country table:  


SELECT Name, Population FROM Country;

•  Literal data values that aren't stored in a table can be worked with directly.  The following statement refers to several literal values: an integer, an exact-value decimal value, an approximate-value floating-point value in scientific notation, and a string value:  

SELECT 14, -312.82, 4.32E-03, 'I am a string'; 

•  Another way to produce data values is by invoking functions. This statement calls functions that return the current date and a server version string:  

SELECT CURDATE(), VERSION(); 

All these types of values can be combined into more complex expressions to produce other values of interest.  The following statement demonstrates this:

mysql> SELECT Name, TRUNCATE(Population/SurfaceArea,2) AS 'people/sq. km', 
          -> IF(GNP > GNPOld,'Increasing','Not increasing') AS 'GNP Trend' 
          -> FROM Country ORDER BY Name LIMIT 10; 


+---------------------------+--------------------+--------------------+ 
| Name                         | people/sq. km | GNP Trend      | 
+---------------------------+--------------------+---------------------+ 
| Afghanistan                          34.84      | Not increasing | 
| Albania                        |         118.31   | Increasing         | 
| Algeria                         |         13.21     | Increasing         | 
| American Samoa       |         341.70   | Not increasing  | 
| Andorra                        |        166.66   | Not increasing  | 
| Angola                         |        10.32      | Not increasing  | 
| Anguilla                        |        83.33      | Not increasing  | 
| Antarctica                    |        0.00        | Not increasing  | 
| Antigua and Barbuda |       153.84    | Increasing          | 
| Argentina                     |       13.31      | Increasing          | 
+-----------------------------+------------------+---------------------+ 

The expressions in the preceding statement uses these types of values:  

•  Table columns: Name, Population, SurfaceArea, GNP, and GNPOld. (“GNP” means “gross 

national product.”)  

•  Literal values: 'Increasing', 'Not increasing', and the column aliases are all string constants.  

•  Functions: The numeric function TRUNCATE() formats the population/area ratio to two decimal places, and the logical function IF() tests the expression in its first argument and returns its second or third argument depending on whether the expression is true or false.   

Aggregating Query Results

A SELECT statement can produce a list of rows that match a given set of conditions.  This list provides the details about the selected rows. However, to know about the overall characteristics of the rows, a summary can be more valuable. Aggregate functions (also known as Summary functions) perform summary operations on a set of values, such as counting, averaging, or finding minimum or maximum values.  They calculate a single value based on a group of values. However, the resulting value is based only on non NULL values from the selected rows (with the exception that COUNT(*) counts all rows).  


Functions such as AVG() that calculate summary values for groups are known as “aggregate” functions because they are based on aggregates or groups of values.  There are several aggregate functions.  Some of the most common are as follows; 


Aggregate Functions              Definition 


       MIN()                                  Find the smallest value 
       MAX()                                Find the largest value 
       SUM()                                Summarize numeric value totals 
       AVG()                                Summarize numeric value averages 
       STD()                                 Returns the standard deviation 
       COUNT()                           Counts rows, non-null values, or the number of distinct values 
       GROUP_CONCAT()       Concatenates a set of strings to produce a single string 


All the functions can be used with the DISTINCT keyword (although it's useless for MAX() and MIN()). In the following example the COUNT(*) function is being used to get a count of all rows in the Country table of the world database; 


mysql> SELECT COUNT(*) FROM Country; 
+--------------+ 
| COUNT(*) | 
+--------------+ 
|     239        | 
+--------------+ 
1 row in set (#.## sec) 




Now specifying a count of Capital yields a different result, due to the fact that not every country has a Capital associated with it.  Therefore, the NULL values are not included in the count; 





mysql> SELECT COUNT(Capital) FROM Country; 
+-----------------------+ 
| COUNT(Capital) | 
+-----------------------+ 
|           232             | 
+-----------------------+ 
1 row in set (#.## sec) 

SELECT with MySQL Query Browser

The primary purpose of the MySQL Query Browser is to provide a graphical user interface for performing table queries, such as those covered in this section. All the above SELECT queries can also be executed from the Query Browser GUI window. 

SELECT with LIMIT

When a query returns many rows, but only a few of them needs to be seen, add a LIMIT clause.  This is a MySQL option that allows the output of a query to be  limited to the first rows of the result that would otherwise be returned. 

The LIMIT clause may be given with either one or two arguments:

ƒ LIMIT row_count

ƒ LIMIT skip_count, row_count

limit,h

Each argument must be represented as an integer constant.  Expressions, user variables, and so forth can not be used.

First Rows from the Beginning When followed by a single integer, row_count, LIMIT returns the first row_count rows from the beginning of the result set. To select just the first 8 rows of a result set, use LIMIT 8;

mysql> SELECT ID, Name FROM City LIMIT 8;

+----+-------------------+

| ID | Name               |

+----+-------------------+

|  1 | Kabul                |

|  2 | Qandahar         |

|  3 | Herat                 |

|  4 | Mazar-e-Sharif |

|  5 | Amsterdam       |

|  6 | Rotterdam         |

|  7 | Haag                  |

|  8 | Utrecht               |

+----+--------------------+

8 rows in set (#.## sec)

Skipping Rows 

When followed by two integers, skip_count and row_count, LIMIT skips the first  skip_count 

rows from the beginning of the result set, and then returns the next row_count rows.  To skip the first 20 rows of a result set and then retrieve the next 8, use the following; 

mysql> SELECT ID, Name FROM City LIMIT 20,8; 

+----+-------------------------+ 

| ID | Name                       | 

+----+-------------------------+ 

| 21 | Amersfoort             |  

| 22 | Maastricht              |  

| 23 | Dordrecht               |  

| 24 | Leiden                    |  

| 25 | Haarlemmermeer |  

| 26 | Zoetermeer           |  

| 27 | Emmen                  |  

| 28 | Zwolle                    |  

+----+------------------------+ 

8 rows in set (#.## sec) 

Limit with ORDER BY 

LIMIT is especially useful in conjunction with the ORDER BY clause, to put the rows in a particular order. When they are used together, MySQL applies ORDER BY first and then LIMIT.  One common use for this is to find the row containing the smallest or largest values in a particular column.  For example, to find a row table t containing the smallest id value, use this statement; 

SELECT * FROM t ORDER BY id LIMIT 1;  

The following query selects the first 5 highest country populations; 

mysql> SELECT Name, Population FROM Country 

         -> ORDER BY population DESC LIMIT 5; 

+--------------------+------------------+ 

| name                 | population     | 

+--------------------+------------------+ 

| China                | 1277558000 | 

| India                  | 1013662000 | 

| United States   |  278357000  | 

| Indonesia          |  212107000  | 

| Brazil                 |  170115000  | 

+--------------------+-------------------+ 

5 rows in set (#.## sec) 

SELECT with ORDER BY

          By default, the rows in the result set produced by a SELECT statement are returned by the server to the client in no particular order.  When a query is issued, the server is free to return the rows in any convenient order.  This order can be affected by factors such as the order in which the rows are actually stored in the table, or which indexes are used to process the query.  If the output rows need to be returned in a specific order, include an ORDER BY clause that indicates how to sort the results. There is no guarantee about the order in which the server returns rows, unless the order is specified.  To do so, add an ORDER BY clause to the statement that defines the sort order desired. Single Column Sorts The following example returns country names (in the Country table of the world database) alphabetically by Country Name; 




mysql> SELECT Name FROM Country ORDER BY Name; 


+------------------------------+ 
| Name                            | 
+------------------------------+ 
| Afghanistan                  | 
| Albania                         | 
| Algeria                          | 
| American Samoa        | 
| Andorra                        | 
| Angola                          | 
| Anguilla                        | 
| Antarctica                    | 
| Antigua and Barbuda | 
| Argentina                     | 
          ... 





Ordering the ORDER BY 

Ascending order is the default sort order in an ORDER BY  clause.  The order in which the results are ordered can be controlled with the keywords ASC (Ascending) or DESC (Descending) following the column names. Ascending means that the lowest value first. The statement below sorts the names of the countries in order by descending alphabetical order; 

mysql> SELECT Name FROM Country ORDER BY Name DESC; 

+--------------------------+ 

| Name                       | 

+--------------------------+ 

| Zimbabwe                | 

| Zambia                     | 

| Yugoslavia                | 

| Yemen                       | 

| Western Sahara       | 

| Wallis and Futuna    | 

| Virgin Islands, U.S.  | 

           ... 

Multiple Column Sorts 
Multiple columns can also be sorted simultaneously with differing sorting orders.  This is accomplished by separating the column names with a comma, as in the following example; 

mysql> SELECT Name, Continent FROM Country 

         -> ORDER BY Continent DESC, Name ASC; 

+------------------+---------------------+ 

| Name             | Continent           | 

+------------------+----------------------+ 

| Argentina        | South America | 

| Bolivia             | South America | 

| Brazil               | South America | 

| Chile                | South America | 

                 ... 

| Uzbekistan      | Asia                  | 

| Vietnam           | Asia                  | 

| Yemen             | Asia                   | 

+-------------------+---------------------+ 

239 rows in set (#.## sec) 

Note: In the above example, the Continent column is data type ENUM, therefore it is ordered by number of placement in list, not alphabetically. 

SELECT with WHERE

The WHERE clause is the mechanism for filtering out unwanted rows from the result set.  Rows can be selected by looking for column values that satisfy various criteria, and different types of values can be filtered out.  In the statement below, the query is selecting information from three columns,  from a given table with a clause  where a specific city name is to be used. In other words, it is answering the question…"What is the ID and district for the city of New York?", as follows; 

mysql> SELECT ID, Name, District FROM City 

         -> WHERE Name = 'New York'; 

+-------+--------------+-------------+ 

| ID      | Name        | District     | 

+-------+--------------+--------------+ 

| 3793 | New York | New York  | 

+-------+--------------+--------------+ 

1 row in set (#.## sec) 

Expressions in WHERE clauses can use the following types of operators; 

ƒ         

                    Arithmetic -- for calculation 

ƒ            Comparison – for comparison of column and row data, needed for each (smart) WHERE condition

ƒ                    Logical – to combine different WHERE conditions 

Arithmetic Operators 


Perform mathematical operations on column entity values. 


Operator        Definition 
     +                 Addition 
     -                  Subtraction 
     *                  Multiplication 
     /                  Division 
   DIV               Integer Division 
    %                 Modulo (remainder after division) 


Comparison Operators 
Compare column entity values. 


Operator              Definition 
     <                       Less than 
     <=                     Less than or equal to 
      =                      Equal to 
     <=>                   Equal to (works even for NULL values) 
     <> or !=            Not equal to 
     >=                     Greater than or equal to 
     >                       Greater than 
    BETWEEN       Indicate a range of numerical 
     x AND Y            values 

Logical Operators 


Combine different conditions. 


Operator      Definition 


    AND        Logical AND 
    OR           Logical OR 
    XOR        Logical exclusive-OR 
    NOT        Logical negation 




Additional options in WHERE expressions; 

• ƒ  IN is equivalent to a list of comparisons with OR 

• ƒ  NULL values can be filtered by using IS NULL (WHERE column_name IS NULL) 

• ƒ  Parenthesis can also be used to group parts of an expression 

• ƒ  Operators can be performed using constants, table columns and function call

• ƒ  It is also possible to search for combinations of values 

The example below uses a few of the operators and techniques mentioned above (on the Country table): 



mysql> SELECT Name, Population FROM Country  
         -> WHERE Population > 50000000 AND  
         -> (Continent = 'Europe' OR Code ='USA'); 


+---------------------------+-----------------+ 
| Name                        |   Population  | 
+---------------------------+-----------------+ 
| United Kingdom       |   59623400  | 
| Italy                             |   57680000  | 
| France                       |   59225700  | 
| Germany                    |   82164700  | 
| Ukraine                      |   50456000  | 
| Russian Federation |  146934000 | 
| United States            |  278357000 | 
+---------------------------+------------------+ 
7 rows in set (#.## sec) 





This example demonstrates the use of the IN clause is a WHERE expression to apply the query to a list of entities; 

mysql> SELECT ID, Name, District FROM city 

         -> WHERE Name IN ('New York', 'Rochester', 'Syracuse'); 

+-------+--------------+--------------+ 

| ID      | Name        | District      | 

+-------+--------------+--------------+ 

| 3793 | New York  | New York | 

| 3871 | Rochester | New York | 

| 3935 | Syracuse  | New York | 

+-------+--------------+--------------+ 

3 rows in set (#.## sec)