Skip to main content

Posts

Showing posts from March, 2012

Case Sensitivity in String Comparisons

String comparisons are somewhat more complex than numeric or temporal comparisons. A letter in  uppercase may compare as the same or different than the same letter in lowercase, and a letter with one  type of accent may be considered the same or different than that letter with another type of accent. • Character Sets and Collations - String expressions contain characters from a particular character  set, which is associated with one of the collations (sorting orders) available for the character set. Characters may consist of single or multiple bytes. A collation can be case insensitive (lettercase  is not significant) or case sensitive (lettercase is significant). The rules that govern string comparison apply in several ways. They determine the result of comparisons  performed explicitly with operators such as = and <, and comparisons performed implicitly by ORDER BY, GROUP BY, and DISTINCT operations. The default character set and collation for literal strings depend on the val

String Expressions

           Literal strings in expressions are written as quoted values. By default, either single quotes or double quotes  can be used, although single quotes are more standard. Also, if the ANSI_QUOTES SQL mode is enabled, double quotes are interpreted as identifier-quoting characters, so literal strings can be quoted only with  single quotes. The data types for representing strings in tables include CHAR, VARCHAR and the TEXT and BLOB  types. The type chosen to be used depends on factors such as the maximum length of values and whether  fixed-length or variable-length values are required. Direct use of strings in expressions occurs primarily in comparison operations. Otherwise, most string  operations are performed by using functions. The usual comparison operators apply to string values (=, <>, <, BETWEEN, and so forth): Operator Definition <                                   Less than <=                                 Less than or equal to =            

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 dat

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 st

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()          

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       |

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                            |  +--------

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  ƒ

SELECT with DISTINCT

I f a query returns a result that contains duplicate rows, the duplicates can be removed to produce a result set  in which every row is unique.  To do this, include the clause DISTINCT after SELECT and before the  column list.  Caution needs to be present when using DISTINCT due to the fact that DISTINCT will  compare whole rows when processing.  In the case below, the query results in a set that includes duplicate  rows:  mysql> SELECT Continent FROM Country;  +--------------------+  | Continent          |  +--------------------+  | Asia                  |  | Europe             |  | North America |  | Europe             |  | Africa                |  | Oceania           |         ...       | Antarctica        |  | Oceania           |  +--------------------+  239 rows in set (#.## sec)  By adding DISTINCT to the statement, the duplicates are removed and only unique rows are returned:  mysql> SELECT DISTINCT Continent FROM Country;