YOUR WEBSITE

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

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

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               Defin...

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. 

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

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

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)  E...

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

            There are many clauses, and combinations thereof, that can be used with a SELECT statement to yield  particular table query results.  They range from very basic, commonly-used options to very specialized and  complex.  The following basic, optional clauses will be covered in this section:  Clauses                   Definition DISTINCT                Eliminates duplicate row data  FROM                      Specifies what table(s) to retrieve data from  WHERE                   Decides what data to show  ORDER BY             Sorts data by specified order criteria  LIMIT                        Reduces the amount of records r...

The  SELECT statement is primarily used to retrieve zero or more  rows from one or more tables in a  database.  In MySQL,  SELECT is the most commonly used DML (Data Manipulation Language)  command.  In specifying a SELECT query, the user specifies a description of the desired result set.  It is  built with optional clauses that specify how and what data to retrieve.  The general command syntax is  shown below;  SELECT [<clause options>] <column list> [FROM] <table>  [<clause options>];  Basic SELECT statement example using world database (and the result data):  mysql> SELECT Name FROM Country;  +-------------------------------------------------+  | Name                                                    |  +-----------------------...

The FOREIGN KEY clause has several parts:   •  It names the column in the referring table (country_code).   •  It names the code column in the country_parent table as the referenced column. This column is  the “foreign” key.   •  It specifies what actions to take if records are modified in the referenced table. The foreign key  definition shown specifies the  CASCADE action for both  UPDATE and  DELETE operations. This  means that changes in the parent table are cascaded down to the child table. If a  code value is  changed in the  country_parent table, InnoDB changes any corresponding  city_child  records with that value in the country_code column to match. If a country_parent record is  deleted, InnoDB also deletes any  city_child records with the same country code. (InnoDB  supports actions other than CASCADE, but they are not covered here.   Instead of  ...

A foreign key relationship allows for the declaration of an index in one table to be related to an index in  another.  It also allows for the placing of constraints on what may be done to the tables in the relationship.   The database enforces the rules of this relationship to maintain referential integrity.  Using Foreign Keys with InnoDB  The InnoDB storage engine supports the use of foreign keys (unlike other storage engines).  This capability  enables the declaration of relationships between columns in different tables, and InnoDB maintains  integrity between the tables by prohibiting operations that violate those relationships.  For example, the  following restrictions must be addressed:   •  A table must contain only records with ID values that are known in another reference table.   •  If an ID in the referenced table is changed, the ID in all matching records in the referencing table  must be ...

To view the status of indexes in a table use the following statement;  SHOW INDEX FROM <table>;  Example:  mysql> SHOW INDEX FROM countrylanguage\G  *************************** 1. row ***************************         Table: countrylanguage  Non_unique: 0     Key_name: PRIMARY  Seq_in_index: 1  Column_name: CountryCode     Collation: A  Cardinality: NULL     Sub_part: NULL       Packed: NULL         Null:  Index_type: BTREE       Comment:  *************************** 2. row ***************************         Table: countrylanguage  Non_unique: 0     Key_name: PRIMARY  Seq_in_index: 2  Column_name: Language     Collation: A  Cardinality: 984     Sub_part: NULL    ...